<?
/***************************************************************************
*                            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
***************************************************************************/

bx_import('BxDolDb');
bx_import('BxDolEmailTemplates');

class BxDolSubscriptionQuery extends BxDolDb {
    var $_sPrefix;
    var $_oSubscription;

	/**
	 * constructor
	 */
	function BxDolSubscriptionQuery(&$oSubscription) {
	    parent::BxDolDb();

	    $this->_oSubscription = &$oSubscription;
	    $this->_sPrefix = 'sys_sbs_';
	}
	function isSubscribed($aParams) {
	    $iType = BX_DOL_SBS_TYPE_MEMBER;
	    if(!isset($aParams['user_id']) || (int)$aParams['user_id'] == 0) {
	    	$aParams['user_id'] = 0;
	    	if(!empty($aParams['name']) && !empty($aParams['email']))
	        	$aParams['user_id'] = (int)(int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `name`='" . $aParams['name'] . "' AND `email`='" . $aParams['email'] . "' LIMIT 1");

	        $iType = BX_DOL_SBS_TYPE_VISITOR;
	    }

        $sSql = "SELECT
                    `tse`.`id` AS `id`
                FROM `" . $this->_sPrefix . "entries` AS `tse`
                LEFT JOIN `" . $this->_sPrefix . "types` AS `tst` ON `tse`.`subscription_id`=`tst`.`id`
                WHERE
                	`tst`.`unit`='" . $aParams['unit'] . "' AND
                	" . (!empty($aParams['action']) ? "`tst`.`action`='" . $aParams['action'] . "' AND " : "") . "
                    `tse`.`subscriber_id`='" . $aParams['user_id'] . "' AND
                    `tse`.`subscriber_type`='" . $iType . "'" .
                    ((int)$aParams['object_id'] != 0 ? " AND `tse`.`object_id`='" . $aParams['object_id'] . "'" : "");
        return !empty($aParams['user_id']) && (int)$this->getOne($sSql) > 0;
	}
	function getSubscription($sUnit, $sAction) {
	    $sSql = "SELECT
	           `id` AS `id`,
	           `unit` AS `unit`,
	           `action` AS `action`,
	           `template` AS `template`,
	           `params` AS `params`
            FROM `" . $this->_sPrefix . "types`
            WHERE `unit`='" . $sUnit . "' AND `action`='" . $sAction . "'
            LIMIT 1";
        return $this->getRow($sSql);
	}
	function getSubscriptions($sUnit, $sAction = '') {
	    $sSql = "SELECT
	           `id` AS `id`,
	           `unit` AS `unit`,
	           `action` AS `action`,
	           `template` AS `template`,
	           `params` AS `params`
            FROM `" . $this->_sPrefix . "types`
            WHERE `unit`='" . $sUnit . "'" . (!empty($sAction) ? " AND `action`='" . $sAction . "'" : "");
        return $this->getAll($sSql);
	}
    function getSubscriptionsByUser($iUserId) {
	    $sSql = "SELECT
	           `tt`.`id` AS `id`,
	           `tt`.`unit` AS `unit`,
	           `tt`.`action` AS `action`,
	           `tt`.`params` AS `params`,
	           `te`.`id` AS `entry_id`,
	           `te`.`object_id` AS `object_id`
            FROM `" . $this->_sPrefix . "entries` AS `te`
            LEFT JOIN `" . $this->_sPrefix . "types` AS `tt` ON `te`.`subscription_id`=`tt`.`id`
            WHERE `te`.`subscriber_id`='" . $iUserId . "' AND `te`.`subscriber_type`='" . BX_DOL_SBS_TYPE_MEMBER . "'
            ORDER BY `tt`.`unit`, `te`.`object_id`";
        return $this->getAll($sSql);
	}
	function addSubscription($aParams) {
	    switch($aParams['type']) {
	        case BX_DOL_SBS_TYPE_VISITOR:
	            $sUserName = process_db_input($aParams['user_name'], BX_TAGS_STRIP);
	            $sUserEmail = process_db_input($aParams['user_email'], BX_TAGS_STRIP);
	            if(empty($sUserName) || empty($sUserEmail) || !(bool)preg_match('/^([a-z0-9\+\_\-\.]+)@([a-z0-9\+\_\-\.]+)$/i', $sUserEmail))
	            	return array('code' => 4, 'message' => _t('_sys_txt_sbs_empty_name_email'));

	            $iUserId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `email`='" . $sUserEmail . "' LIMIT 1");
	            if($iUserId != 0)
                    break;

	            $mixedResult = $this->query("INSERT INTO `" . $this->_sPrefix . "users`(`name`, `email`, `date`) VALUES('" . $sUserName . "', '" . $sUserEmail . "', UNIX_TIMESTAMP())");
	            if($mixedResult === false)
                    return array('code' => 1, 'message' => _t('_sys_txt_sbs_cannot_save_visitor'));

	            $iUserId = (int)$this->lastId();
	            break;
	        case BX_DOL_SBS_TYPE_MEMBER:
	            $aProfileInfo = getProfileInfo((int)$aParams['user_id']);

	            $iUserId = $aProfileInfo['ID'];
	            $sUserName = $aProfileInfo['NickName'];
	            $sUserEmail = $aProfileInfo['Email'];
	            break;
	    }

	    $aSubscriptions = $this->getSubscriptions($aParams['unit'], $aParams['action']);
	    if(!is_array($aSubscriptions) || empty($aSubscriptions))
            return array('code' => 2, 'message' => _t('_sys_txt_sbs_cannot_find_subscription'));

        $aTemplateParams = array();
        $aResults = array();
        foreach($aSubscriptions as $aSubscription) {
            if($aSubscription['action'] == $aParams['action'] && !empty($aSubscription['params'])) {
                $oFunction = create_function('$arg1, $arg2, $arg3', $aSubscription['params']);
                $aUnitParams = $oFunction($aParams['unit'], $aParams['action'], $aParams['object_id']);
            }

            if(empty($aSubscription['action']))
                continue;

            $iEntryId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $aParams['type'] . "' AND `subscription_id`='" . $aSubscription['id'] . "' AND `object_id`='" . (int)$aParams['object_id'] . "' LIMIT 1");
			if(!empty($iEntryId))
				return array('code' => 3, 'message' => _t('_sys_txt_sbs_already_subscribed'));

            $iResult = (int)$this->query("INSERT INTO `" . $this->_sPrefix . "entries`(`subscriber_id`, `subscriber_type`, `subscription_id`, `object_id`) VALUES('" . $iUserId . "', '" . $aParams['type'] . "', '" . $aSubscription['id'] . "', '" . (int)$aParams['object_id'] . "')");
            if($iResult > 0)
                $aResults[] = $this->lastId();
        }

        if(count($aResults) > 0) {
            $oEmailTemplate = new BxDolEmailTemplates();
            $aTemplateParams = array (
                'RealName' => $sUserName,
                'SysUnsubscribeLink' => $this->_oSubscription->_getUnsubscribeLink($aResults)
            );
            if(isset($aUnitParams['template']))
                $aTemplateParams = array_merge($aTemplateParams, $aUnitParams['template']);

            $aMail = $oEmailTemplate->parseTemplate('t_Subscription', $aTemplateParams);
            sendMail($sUserEmail, $aMail['subject'], $aMail['body']);

            $aResult = array('code' => 0, 'message' => _t('_sys_txt_sbs_success_subscribe'));
        }
        else
            $aResult = array('code' => 5, 'message' => _t('_sys_txt_sbs_error_occured'));

        return $aResult;
	}
	function deleteSubscription($aParams) {
	    switch($aParams['type']) {
	        case BX_DOL_SBS_TYPE_VISITOR:
                if(isset($aParams['user_id']))
                    $iUserId = (int)$aParams['user_id'];
                else if(isset($aParams['user_name']) && isset($aParams['user_email']))
                    $iUserId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `name`='" . process_db_input($aParams['user_name'], BX_TAGS_STRIP) . "' AND `email`='" . process_db_input($aParams['user_email'], BX_TAGS_STRIP) . "' LIMIT 1");

	            $iUserType = BX_DOL_SBS_TYPE_VISITOR;
	            break;
	        case BX_DOL_SBS_TYPE_MEMBER:
	            $iUserId = (int)$aParams['user_id'];
	            $iUserType = BX_DOL_SBS_TYPE_MEMBER;
	            break;
	    }

	    $iResult = 0;
	    //--- Unsubscribe when the button is clicked ---//
        if(isset($aParams['unit']) && isset($aParams['action'])) {
            $aSubscriptions = $this->getSubscriptions($aParams['unit'], $aParams['action']);
            if(!is_array($aSubscriptions) || empty($aSubscriptions))
                return array('code' => 2, 'message' => _t('_sys_txt_sbs_cannot_find_subscription'));

            foreach($aSubscriptions as $aSubscription)
                $iResult += (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $iUserType . "' AND `subscription_id`='" . $aSubscription['id'] . "'" . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . $aParams['object_id'] . "'" : ""));
        }
        //--- Unsubscribe when the object is deleted ---//
        else if(isset($aParams['unit']) && isset($aParams['object_id'])) {
            $aSubscriptions = $this->getSubscriptions($aParams['unit']);
            if(is_array($aSubscriptions) && !empty($aSubscriptions)) {
                foreach($aSubscriptions as $aSubscription)
                    $aIds[] = $aSubscription['id'];

                $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscription_id` IN ('" . implode("','", $aIds) . "') AND `object_id`='" . $aParams['object_id'] . "'");
            }
            else
                $iResult = 0;
        }
        //--- Unsubscribe when the link with SID is clicked ---//
        else if(isset($aParams['sid'])) {
            $aIds = explode(",", base64_decode(urldecode($aParams['sid'])));
            if(is_array($aIds) && !empty($aIds)) {
                list($iUserId, $iUserType) = $this->getRow("SELECT `subscriber_id`, `subscriber_type` FROM `" . $this->_sPrefix . "entries` WHERE `id`='" . $aIds[0] . "' LIMIT 1", MYSQL_NUM);

                $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `id` IN ('" . implode("','", $aIds) . "')");
            }
        }
        //--- Unsubscribe the user from all subscriptions ---//
        else
            $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $iUserType . "'");

	    if($iUserType == BX_DOL_SBS_TYPE_VISITOR || (isset($aParams['unit']) && isset($aParams['object_id']))) {
	        $iSbsEntries = (int)$this->getOne("SELECT COUNT(`id`) FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . BX_DOL_SBS_TYPE_VISITOR . "' LIMIT 1");
            if($iSbsEntries == 0)
                $this->query("DELETE FROM `" . $this->_sPrefix . "users` WHERE `id`='" . $iUserId . "' LIMIT 1");
	    }

	    return $iResult > 0 ? array('code' => 0, 'message' => _t('_sys_txt_sbs_success_unsubscribe')) : array('code' => 4, 'message' => _t('_sys_txt_sbs_already_unsubscribed'));
	}
	function sendDelivery($aParams) {
	    $iQueued = 0;

	    $oEmailTemplates = new BxDolEmailTemplates();
        $aSubscription = $this->getSubscription($aParams['unit'], $aParams['action']);

        if(!empty($aSubscription['params'])) {
    	    $oFunction = create_function('$arg1, $arg2, $arg3', $aSubscription['params']);
    	    $aUnitParams = $oFunction($aParams['unit'], $aParams['action'], $aParams['object_id']);
        }

        if(isset($aUnitParams['skip']) && $aUnitParams['skip'] === true)
            return $iQueued;

	    $aSubscribers = $this->getAll("SELECT `id` AS `subscription_id`, `subscriber_id` AS `id`, `subscriber_type` AS `type` FROM `" . $this->_sPrefix . "entries` WHERE `subscription_id`='" . (empty($aSubscription['id']) ? 0 : $aSubscription['id']) . "'" . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . $aParams['object_id'] . "'" : ""));
	    foreach($aSubscribers as $aSubscriber) {
            switch($aSubscriber['type']) {
                case BX_DOL_SBS_TYPE_VISITOR:
                    $sSql = "SELECT '0' AS `id`, `name`, `email` FROM `" . $this->_sPrefix . "users` WHERE `id`='" . $aSubscriber['id'] . "' LIMIT 1";
                    break;
                case BX_DOL_SBS_TYPE_MEMBER:
                    $sSql = "SELECT `ID` AS `id`, `NickName` AS `name`, `Email` AS `email` FROM `Profiles` WHERE `ID`='" . $aSubscriber['id'] . "' LIMIT 1";
                    break;
            }
            $aUser = $this->getRow($sSql);

            //--- Parse message ---//
            $sSql = "SELECT
                        `tse`.`id` AS `id`
                    FROM `" . $this->_sPrefix . "entries` AS `tse`
                    LEFT JOIN `" . $this->_sPrefix . "types` AS `tst` ON `tse`.`subscription_id`=`tst`.`id` AND `tst`.`unit`='" . $aParams['unit'] . "' AND `tst`.`action`<>''
                    WHERE `tse`.`subscriber_id`='" . $aSubscriber['id'] . "' AND `tse`.`subscriber_type`='" . $aSubscriber['type'] . "'"  . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . $aParams['object_id'] . "'" : "");
            $aEntries = $this->getColumn($sSql);

            $aTemplateParams = array(
                'RealName' => $aUser['name'],
                'Email' => $aUser['email'],
                'ObjectId' => $aParams['object_id'],
                'UnsubscribeLink' => $this->_oSubscription->_getUnsubscribeLink((int)$aSubscriber['subscription_id']),
                'UnsubscribeAllLink' => $this->_oSubscription->_getUnsubscribeLink($aEntries),
            );
            if(isset($aUnitParams['template']))
                $aTemplateParams = array_merge($aTemplateParams, $aUnitParams['template']);

            $aMail = $oEmailTemplates->parseTemplate($aSubscription['template'], $aTemplateParams, (int)$aUser['id']);

            $iQueued += (int)$this->query("INSERT INTO `" . $this->_sPrefix . "queue`(`email`, `subject`, `body`) VALUES('" . $aUser['email'] . "', '" . process_db_input($aMail['subject'], BX_TAGS_NO_ACTION, BX_SLASHES_NO_ACTION) . "', '" . process_db_input($aMail['body'], BX_TAGS_NO_ACTION, BX_SLASHES_NO_ACTION) . "')");
	    }

	    return $iQueued;
	}
	function getSubscribersCount($iType) {
	    switch($iType) {
	        case BX_DOL_SBS_TYPE_VISITOR:
	            $sSql = "SELECT
	                       COUNT(DISTINCT `tsu`.`id`) AS `count`
                        FROM `" . $this->_sPrefix . "users` AS `tsu`
                        INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`id`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . BX_DOL_SBS_TYPE_VISITOR . "'
                        WHERE 1
                        LIMIT 1";
	            break;
	        case BX_DOL_SBS_TYPE_MEMBER:
	            $sSql = "SELECT
	                       COUNT(DISTINCT `tsu`.`ID`) AS `count`
                        FROM `Profiles` AS `tsu`
                        INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`ID`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . BX_DOL_SBS_TYPE_MEMBER . "'
                        WHERE 1
                        LIMIT 1";
	            break;
	    }
	    return (int)$this->getOne($sSql);
	}
	function getSubscribers($iType, $iStart, $iCount) {
	    switch($iType) {
	        case BX_DOL_SBS_TYPE_VISITOR:
	            $sSql = "SELECT
	                       `tsu`.`id` AS `id`,
	                       `tsu`.`name` AS `name`,
	                       `tsu`.`email` AS `email`
                        FROM `" . $this->_sPrefix . "users` AS `tsu`
                        INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`id`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . BX_DOL_SBS_TYPE_VISITOR . "'
                        WHERE 1
                        GROUP BY `tsu`.`id`
                        LIMIT " . $iStart . "," . $iCount;
	            break;
	        case BX_DOL_SBS_TYPE_MEMBER:
	            $sSql = "SELECT
	                       `tsu`.`ID` AS `id`,
	                       `tsu`.`NickName` AS `name`,
	                       `tsu`.`Email` AS `email`
                        FROM `Profiles` AS `tsu`
                        INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`ID`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . BX_DOL_SBS_TYPE_MEMBER . "'
                        WHERE 1
                        GROUP BY `tsu`.`ID`
                        LIMIT " . $iStart . "," . $iCount;
	            break;
	    }
	    return $this->getAll($sSql);
	}
}
