<?php

/***************************************************************************
*                            Dolphin Smart Community Builder
*                              -------------------
*     begin                : Mon Mar 23 2006
*     copyright            : (C) 2007 BoonEx Group
*     website              : http://www.boonex.com
* This file is part of Dolphin - Smart Community Builder
*
* Dolphin is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the
* Free Software Foundation; either version 2 of the
* License, or  any later version.
*
* Dolphin is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License for more details.
* You should have received a copy of the GNU General Public License along with Dolphin,
* see license.txt file; if not, write to marketing@boonex.com
***************************************************************************/

require_once( BX_DIRECTORY_PATH_CLASSES . 'BxDolModuleDb.php' );

class BxPollDb extends BxDolModuleDb 
{	
    var $_oConfig;
    var $_sTable;
    var $sTablePrefix;

	/*
	 * Constructor.
	 */
	function BxPollDb(&$oConfig) {
		parent::BxDolModuleDb();		

        $this -> _oConfig       = $oConfig;
        $this -> _sTable        = $oConfig -> sTableName;
        $this -> sTablePrefix   = $oConfig -> sTablePrefix;
	}

    /**
     * Function will return all active pools ;
     *
     * @param   : $sLimit (string) - contain part of SQL query;
     * @param   : $iMemberID (integer) - logged member's Id ;
     * @param   : $bAllPolls (boolean) - return all polls aprroved and not ;
     * @return  : (array) - array with all active pools;
                    [ id_poll ]     - (integer) poll's Id;
                    [ id_profile ]  - (integer) poll's owner Id;
                    [ poll_date ]    - (string)  poll's date creation;
                    [ sec ]         - (integer) poll's date creation in seconds;
                    [ poll_approval ] - (integer) poll's status aproved or not;
     */
    function getAllPolls($sLimit, $iMemberID = 0, $bAllPolls = false, $iApproval = 1, $sExtraSql = null, $sOrder = '`poll_date` DESC')
    {
    	$sLimit = $this -> escape($sLimit);
    	$iMemberID = (int) $iMemberID;
		$iApproval = (int) $iApproval;
		$sExtraSql = $this -> escape($sExtraSql);
		$sOrder	= $this -> escape($sOrder);

        // ** init some needed variables ;

        $sAddonSql       = null;
        $aWhereCondition = array();

        $aWhereCondition[0] = " AND `poll_status`   = 'active' ";
        $aWhereCondition[1] = " AND `poll_approval` = {$iApproval}";
        $aWhereCondition[2] = " AND `id_profile`    = {$iMemberID} ";

        // if need the only member's pools ;
        if ( !$bAllPolls ) {
            if ( $iMemberID  ) {
                $sAddonSql  = $aWhereCondition[1] . $aWhereCondition[2];
            }
            else {
                $sAddonSql  = $aWhereCondition[0] . $aWhereCondition[1];
            }
        }
        
        $sQuery = 
        "
			SELECT 
                `id_poll`, `id_profile`, 
                `poll_date`, (UNIX_TIMESTAMP() - `poll_date`) AS 'sec', 
                `poll_approval`, `poll_featured`
			FROM 
                `{$this -> _sTable}`
			WHERE 
                1
                {$sAddonSql}
                {$sExtraSql}
            ORDER BY
                {$sOrder}
            {$sLimit}
		";

        return $this -> getAll($sQuery);
    }

    /**
     * Function will get number of featured polls
     *
     * @param  : $iStatus (integer) - poll's featured status;
     * @param  : $bOnlyPublic (boolean) - if isset this param than will return only public polls; 
     * @return : (integer) - number of featured polls;
     */
    function getFeaturedCount($iStatus = 1, $bOnlyPublic = false)
    {
    	$iStatus = (int) $iStatus;
		settype($bOnlyPublic, 'boolean');

        $sExtraSql = $bOnlyPublic ? ' AND `allow_view_to` = ' . BX_DOL_PG_ALL : null;
        $sQuery = "SELECT COUNT(*) FROM  `{$this -> _sTable}` WHERE `poll_featured` = {$iStatus} {$sExtraSql}";
        return $this -> getOne($sQuery);
    }

    /**
     * Function will generate all featured polls;
     *
     * @param  : $bOnlyPublic (boolean) - if isset this param than will return only public polls; 
     * @param  : $sLimit (string) - sql query (limit of returning rows)
     */
    function getAllFeaturedPolls($sLimit, $iStatus = 1, $bOnlyPublic = false)
    {
    	$sLimit = $this -> escape($sLimit);
    	$iStatus = (int) $iStatus;
    	settype($bOnlyPublic, 'boolean');

        $sExtraSql = $bOnlyPublic ? ' AND `allow_view_to` = ' . BX_DOL_PG_ALL : null;
        $sQuery = "SELECT * FROM `{$this -> _sTable}` WHERE `poll_featured` = {$iStatus} {$sExtraSql} ORDER BY `poll_date` DESC {$sLimit}";
        return $this -> getAll($sQuery);
    }

    /**
     * Function will return the number of active pools;
     *
     * @param  : $iMemberID (integer) - logged member's Id ;
     * @param  : $bAllPools (boolean) - if isset this param that will find all 
                        member's poll's (approved and not) ;
     * @return : (integer) - number of active pools;
     */
    function getPollsCount($iMemberID = 0, $bAllPools = false, $iApproval = 1, $sExtraSql = null)
    {
    	$iMemberID = (int) $iMemberID;
    	$bOnlyPublic = (bool) $bOnlyPublic;
		$iApproval = (int) $iApproval;
		$sExtraSql = $this -> escape($sExtraSql);

        // ** init some needed variables ;

        $sAddonSql       = null;
        $aWhereCondition = array();

        $aWhereCondition[0] = " AND `poll_status`   = 'active' ";
        $aWhereCondition[1] = " AND `poll_approval` = {$iApproval} ";
        $aWhereCondition[2] = " AND `id_profile`    = {$iMemberID} ";

        // if need the only member's pools ;
        $sIsEmpty = true;
        if ( $iMemberID  ) {
            if ( !$bAllPools ) {
                // only approved  member's polls ;
                $sAddonSql  = $aWhereCondition[1] . $aWhereCondition[2];
            }
            else {
                $sAddonSql  = $aWhereCondition[2];
            }
        }
        else {
            if ( !$bAllPools ) {
                $sAddonSql  = $aWhereCondition[0] . $aWhereCondition[1];
            }
        }

        $sQuery = 
        "
        SELECT 
            COUNT(*)
        FROM 
            `{$this -> _sTable}`
        WHERE
            1
            {$sAddonSql}
            {$sExtraSql}
		";

        return $this -> getOne($sQuery);
    }


    /**
     * Function will get count of unapproved polls;
     *
     * @param  : $iProfileId (integer) - profile's Id;
     * @return : (integer) - number of polls;
     */
    function getUnApprovedPolls($iProfileId)
    {
    	$iProfileId = (int) $iProfileId;

        $sQuery = "SELECT COUNT(*) FROM `{$this -> _sTable}` WHERE `poll_approval` = 0 AND `id_profile` = {$iProfileId}";
        return $this -> getOne($sQuery);
    }

    /**
     * Function will return array with all poll's questions ;
     *
     * @param   : $iPollId (integer) - poll's Id ;
     * @return  : (array) array with all questions ;
                    [ id_poll ]     - (integer) poll's Id;
                    [ id_profile ]  - (integer) poll's owner Id;
                    [ poll_date ]    - (string)  poll's date creation;
                    [ sec ]         - (integer) poll's date creation in seconds;
     */
    function getPollInfo($iPollId)
    {
    	$iPollId = (int) $iPollId;

        $sQuery = 
        "
            SELECT 
                *
            FROM
               `{$this -> _sTable}`
            WHERE
                `id_poll` = {$iPollId}
        ";

        return $this -> getAll($sQuery);
    }

    /**
     * Function will set the number of votes ;
     *
     * @param   : $iPollId (integer)     - poll's Id;
     * @param   : $sVotes (string)       - votes string;
     * @param   : $iVotesCount (integer) - number of all votes;
     * @return  : (integer) - number of affected rows ;
     */
    function setVotes( $iPollId, $sVotes, $iVotesCount )
    {
    	$iPollId = (int) $iPollId;
		$sVotes = process_db_input($sVotes, BX_TAGS_STRIP);
		$iVotesCount = (int) $iVotesCount;

        $sQuery = 
        "
            UPDATE 
                `{$this -> _sTable}`
            SET 
                `poll_results`      = '{$sVotes}', 
                `poll_total_votes`  = {$iVotesCount} 
            WHERE 
                `id_poll` = {$iPollId}
        ";

        return $this -> query($sQuery);
    }

    /**
     * Function will set poll's status ;
     *
     * @param  : $iPollId (integer) - poll's Id ;
     * @param  : $iStatus (integer) - poll's status (1 - as approved, 0 - not approved) ;
     * @return : (integer) - number of affected rows ;
     */
    function setStatus($iPollId, $iStatus) 
    {
    	$iPollId = (int) $iPollId;
    	$iStatus = (int) $iStatus;

        $sQuery = 
        "
            UPDATE
                `{$this -> _sTable}`
            SET
                `poll_approval` = {$iStatus}
            WHERE
               `id_poll` = {$iPollId} 
        ";

        return $this -> query($sQuery);
    }

    /**
     * Function will set poll's status ;
     *
     * @param  : $iPollId (integer) - poll's Id ;
     * @param  : $iStatus (integer) - poll's status (1 - as featured, 0 - not featured) ;
     * @return : (integer) - number of affected rows ;
     */
    function setFeatured($iPollId, $iStatus) 
    {
    	$iPollId = (int) $iPollId;
    	$iStatus = (int) $iStatus;

        $sQuery = 
        "
            UPDATE
                `{$this -> _sTable}`
            SET
                `poll_featured` = {$iStatus}
            WHERE
               `id_poll` = {$iPollId} 
        ";

        return $this -> query($sQuery);
    }

    /**
     * Function will create new poll ;
     *
     * @param   : $aPoolInfo (array) - contain some poll's information;
                    [ owner_id ] - (integer) poll's owner Id ;
                    [ question ] - (string)  poll's question ;
                    [ answers ]  - (string)  poll's answers (string separated by tag '<delim>');
                    [ results ]  - (string)  poll's results (string separated by ';');
                    [ tags ]     - (string)  poll's tags (string separated by 'space');
     * $isAdmin : (boolean) - is isset this value that is admin mode;
     * @return  : (integer)      - code of operation;
     */
    function createPoll($aPoolInfo, $isAdmin = false)
    {
    	$isAdmin = (bool) $isAdmin;

    	if( !is_array($aPoolInfo) ) {
    		return;
    	}

    	// process recived array
    	foreach($aPoolInfo as $sKey => $sValue) 
    	{
            $aPoolInfo[$sKey] = process_db_input($sValue, BX_TAGS_NO_ACTION
                , BX_SLASHES_AUTO);
    	}

        // ** init some needed variables ;

        $iPollsCount = -1;
        $iAutoActive =  1;
        $iResponse   =  0;

        if ($aPoolInfo['owner_id']) {
            $iPollsCount = $this -> getPollsCount( $aPoolInfo['owner_id'], true );
            $iAutoActive = ($this -> _oConfig -> iAutoActivate) ? 1 : 0;

            if (!$this -> _oConfig -> iAlowMembersPolls) {
                return 0;
            }    
        }

        if ($iPollsCount < $this -> _oConfig -> iAlowPollNumber || $isAdmin) {
            $iCurrentDate = date('U');
            $sQuery = 
            "
                INSERT INTO
                    `{$this -> _sTable}`
                SET
                    `id_profile`        = {$aPoolInfo['owner_id']},
                    `poll_question`     = '{$aPoolInfo['question']}',
                    `poll_answers`      = '{$aPoolInfo['answers']}',
                    `poll_results`      = '{$aPoolInfo['results']}',
                    `poll_status`       = 'active',
                    `poll_approval`     = {$iAutoActive},
                    `poll_date`         = {$iCurrentDate},
                    `poll_tags`         = '{$aPoolInfo['tags']}',
                    `allow_comment_to`  = {$aPoolInfo['allow_comment']},
                    `allow_vote_to`     = {$aPoolInfo['allow_vote']},
                    `poll_categories`   = '{$aPoolInfo['category']}',
                    `allow_view_to`     = '{$aPoolInfo['allow_view']}'
            ";

            $this -> query($sQuery);
            $iResponse = 1;
        }
        else {
            $iResponse = 2;
        }

        return $iResponse;
    }

    /**
     * Function will edit poll ;
     *
     * @param   : $aPoolInfo (array) - contain some poll's information;
                    [ question ] - (string)  poll's question ;
                    [ answers ]  - (string)  poll's answers (string separated by tag '<delim>');
                    [ status ]   - (string)  poll's status as active or not;
                    [ approve ]  - (boolean) poll's approve or not;
                    [ Id ]       - (integer) poll's Id;
                    [ tags ]     - (string)  poll's tags (string separated by 'space');
     * @return  : (integer) - number of affected rows;
     */
    function editPoll($aPoolInfo)
    {
    	if( !is_array($aPoolInfo) ) {
    		return;
    	}

    	// procces recived array
    	foreach($aPoolInfo as $sKey => $sValue) 
    	{
    		if($sKey != 'answers' ) {
    			$aPoolInfo[$sKey] = process_db_input($sValue, BX_TAGS_STRIP);	
    		}
    		else {
    			$aPoolInfo[$sKey] = process_db_input($sValue);
    		}
    	}

        // ** init some neede variables ;

        $sAddonSql = null;

        if ( isset($aPoolInfo['approve']) ) {
            $sAddonSql = ( $aPoolInfo['approve'] )
                ? ',`poll_approval` = 1'
                : ',`poll_approval` = 0';
        }

        $sQuery = 
        "
            UPDATE
              `{$this -> _sTable}`
            SET
                `poll_question`     = '{$aPoolInfo['question']}',
                `poll_answers`      = '{$aPoolInfo['answers']}',
                `poll_status`       = '{$aPoolInfo['status']}',
                `poll_tags`         = '{$aPoolInfo['tags']}',
                `allow_comment_to`  = {$aPoolInfo['allow_comment']},
                `allow_vote_to`     = {$aPoolInfo['allow_vote']},
                `poll_categories`   = '{$aPoolInfo['category']}',
                `allow_view_to`     = '{$aPoolInfo['allow_view']}'
                {$sAddonSql}
            WHERE
                `id_poll` = {$aPoolInfo['Id']}
        ";

       return $this -> query($sQuery);
    }

    /**
     * Function will delete poll ;
     *
     * @param  : $iPollId (integer) - poll's Id;
     * @return : (integer) - number of affected rows;
     */
    function deletePoll($iPollId)
    {
    	$iPollId = (int) $iPollId;
        $this -> clearAttachetData($iPollId);
        $sQuery = "DELETE FROM  `{$this -> _sTable}` WHERE `id_poll` = {$iPollId}";
        return $this -> query($sQuery);
    }

    /**
     * Function will delete all profile's polls;
     * 
     * @param : $iProfile (integer) - profile's Id;
     */
    function deleteProfilePolls($iProfile)
    {
    	$iProfile = (int) $iProfile;

        $sQuery = "SELECT `id_poll` FROM `{$this -> sTablePrefix}data` WHERE `id_profile` = {$iProfile}";
        $aPolls = $this -> getAll($sQuery);

        foreach($aPolls as $iKey => $aItems)
        {
            $this -> clearAttachetData($aItems['id_poll']);
            $this -> deletePoll($aItems['id_poll']);
        }
    }

    /**
     * Function will delete all poll's attachet data;
     * from cmts, votings....
     *
     * @iPollId (integer) - poll's id;
     */
    function clearAttachetData($iPollId)
    {
    	$iPollId = (int) $iPollId;

        // delete from comments;
        $sQuery = 
        "
            DELETE  
                `{$this -> sTablePrefix}cmts`,
                `{$this -> sTablePrefix}cmts_track`
            FROM
                `{$this -> sTablePrefix}cmts`
            LEFT JOIN
                `{$this -> sTablePrefix}cmts_track`
            ON
                `{$this -> sTablePrefix}cmts_track`.`cmt_id` = `{$this -> sTablePrefix}cmts`.`cmt_id`
            WHERE
                `{$this -> sTablePrefix}cmts`.`cmt_object_id` = {$iPollId}
        ";
        db_res($sQuery);

        // delete from votings;
        $sQuery = 
        "
            DELETE  
                `{$this -> sTablePrefix}rating`,
                `{$this -> sTablePrefix}voting_track`
            FROM
                `{$this -> sTablePrefix}rating`
            LEFT JOIN
                `{$this -> sTablePrefix}voting_track`
            ON
                `{$this -> sTablePrefix}voting_track`.`id` = `{$this -> sTablePrefix}rating`.`id`
            WHERE
                `{$this -> sTablePrefix}rating`.`id` = {$iPollId}
        ";
        db_res($sQuery);
    }

    /**
     * Function will find all polls by month;
     *
     * @param  : $iYear      (integer) - needed year;
     * @param  : $iMonth     (integer) - needed month;
     * @param  : $iNextYear  (integer) - next year;
     * @param  : $iNextMonth (integer) - next month;
     *
     * @return (array);
     */
    function getPollsByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth) {
    	$iYear = (int) $iYear;
    	$iMonth = (int) $iMonth;
    	$iNextYear = (int) $iNextYear;
    	$iNextMonth = (int) $iNextMonth;

        return $this->getAll ("SELECT `id_poll`, DAYOFMONTH(FROM_UNIXTIME(`poll_date`)) AS `Day`
            FROM `{$this -> _sTable}`
            WHERE `poll_date` >= UNIX_TIMESTAMP('{$iYear}-{$iMonth}-1') AND `poll_date` < UNIX_TIMESTAMP('{$iNextYear}-{$iNextMonth}-1') AND `poll_approval` = 1");
    }

    /**
     * Function will return global category number;
     *
     * @return : (integer) - category's number;
     */
    function getSettingsCategory($sCatName)
    {
    	$sCatName = process_db_input($sCatName, BX_TAGS_STRIP);
        return $this -> getOne('SELECT `kateg` FROM `sys_options` WHERE `Name` = "' . $sCatName . '"');
    }
}