<?php
/***************************************************************************
 *																		  *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *																		  *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.													*
 *																		  *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.			*
 ****************************************************************************/
//
//$Id: DailyProductMailModel.php, v 1.0 2012/02/14 20:52:11
//  
class DailyProductMailModel extends Model {
    //Email tracking Report Start
    //Function For List all Campaigns
    //@usage Admin -> Campaigns Report
    public function listCampaigns($limitFrom = 0, $limitTo = '', $date, $cities = '', $title = '', $type = 'product', $businessID = 0) {
        if ($date) {
            if (strpos($date, 'to') > 0) {
                $queryString = explode('to', $date);
                $fromDate    = $queryString[0] . ' 00:00:00';
                $toDate      = $queryString[1] . ' 23:59:59';
            } else {
                $fromDate = $date;
                $toDate   = $date;
            }
        }
        $sql = " SELECT ";
        if ($limitTo) {
            $sql .= " SQL_CALC_FOUND_ROWS ";
        }
        $sql .= " cam.* 
						FROM 
							" . DB_PREFIX . "campaigns cam";
        $sql .= " WHERE 
						DATE_FORMAT(cam.createdDate, '%Y-%m-%d') 
						BETWEEN '" . $fromDate . "' AND '" . $toDate . "'";
        if ($type == 'business') {
            if ($businessID > 0) {
                $sql .= " AND cam.businessID='" . $businessID . "' ";
            }
            $sql .= " AND cam.campaignType='business'";
        } else if ($type == 'product') {
            $sql .= " AND cam.campaignType='product'";
        } else if ($type == 'cart') {
            $sql .= " AND cam.campaignType='cart'";
        }
        if ($title != '') {
            $title = $this->escape($title);
            $sql .= " AND cam.title LIKE '%" . $this->escape($title, false) . "%'";
        }
        $sql .= " GROUP BY 
					cam.campaignID 
				ORDER BY 
					campaignID desc ";
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        $resSelectCampaigns = $this->ObjDatabase->readValues($sql);
        return $resSelectCampaigns;
    }
    // Function For List top five clicked and opend Campaigns
    //@Usage Admin -> Campaigns Report Side box
    public function listTopFiveCampaigns() {
        $sql = " SELECT 
						cam.campaignID,
						cam.title,
						cam.clickCount 
					FROM 
						" . DB_PREFIX . "campaigns cam 
					WHERE 1";
        $sql .= "  AND cam.clickCount>0
					AND cam.campaignType!='cart'  
					GROUP BY cam.campaignID 
					ORDER BY 
						cam.clickCount DESC,
						cam.createdDate DESC 
					LIMIT 0,5";
        $resClicked = $this->ObjDatabase->readValues($sql);
        $sql        = " SELECT 
						cam.campaignID,
						cam.title,
						cam.openCount 
					FROM 
						" . DB_PREFIX . "campaigns cam
					WHERE 1 ";
        $sql .= " AND cam.openCount>0
					AND cam.campaignType!='cart' 
					GROUP BY cam.campaignID  
					ORDER BY 
						cam.openCount DESC , 
						cam.createdDate DESC 
					LIMIT 0,5";
        $resOpend = $this->ObjDatabase->readValues($sql);
        return array(
            $resClicked,
            $resOpend
        );
    }
    // Function For Get overview of all Campaigns
    public function getCampaignsOverView() {
        $sql         = " SELECT 
						cam.openCount,
						cam.clickCount,
						cam.bounceCount,
						cam.unsubscribedCount,
						cam.userCount 
					FROM 
						" . DB_PREFIX . "campaigns cam 
					WHERE cam.campaignType!='cart' 	";
        $resOverView = $this->ObjDatabase->readValues($sql);
        return $resOverView;
    }
    //Function For getting complete details of a selected Campaigns
    //@Usage Admin -> Campaigns detailed view
    public function getCampaign($languageIDs, $campaignID = 0) {
        $sql         = " SELECT 
						* 
					FROM 
						" . DB_PREFIX . "campaigns 
					WHERE 
						campaignID='" . $campaignID . "' ";
        $rescampaign = $this->ObjDatabase->readValue($sql);
        return $rescampaign;
    }
    //Function to getting all active business names and business ID or for a particular business Name and ID
    //@usage Admin Campaigns Report -> business name text box auto fill
    public function getAllBusiness($businessID = 0, $languageIDs = '') {
        if ($languageIDs != '') {
            $currentLanguageID = $languageIDs['Current'];
        }
        if ($businessID > 0) {
            $sql = " SELECT 
							bus.businessID,
							busDet.businessName 
						FROM 
							" . DB_PREFIX . "business bus
						LEFT JOIN 
							" . DB_PREFIX . "business_details busDet 
							ON (busDet.businessID=bus.businessID 
							AND busDet.languageID='" . $currentLanguageID . "')
						WHERE 
							bus.businessID=" . $businessID;
        } else {
            $sql = " SELECT 
							bus.businessID,
							busDet.businessName 
						FROM 
							" . DB_PREFIX . "business bus
						LEFT JOIN 
							" . DB_PREFIX . "business_details busDet 
							ON (busDet.businessID=bus.businessID 
							AND busDet.languageID='" . $currentLanguageID . "')
			 			WHERE 
			 				businessStatus!='Deleted'";
        }
        $resBusiness = $this->ObjDatabase->readValues($sql);
        return $resBusiness;
    }
    //Function for save the sheduled Campaigns
    //@Usage Admin -> Email and Subscribers -> Shedule
    //@usage (Business followers mail,Normal Product mail,Other product type mail)
    //@usage For both system mail and third Party Service
    public function saveCampaign($campaignID, $campaignName = '', $title = '', $content = '', $campaignDate = '', $productTypeKey = '', $listID = array(), $businessID = 0, $recipients = 0, $languageId = 0, $sendEmails = '', $campaignType = '', $extra = array()) {
        $sendEmails = $this->escape($sendEmails, false, false);
        $content    = '<html><title></title></head><body>' . $content . '</body></html>';
        // New email template dont have starting and ending tags thatrs why added here 
        $content    = $this->escape($content, false);
        $title      = $this->escape($title, false);
        if ($campaignType == 'cart') {
            $extra = $this->escape(serialize($extra), false, false);
            $sql   = "INSERT INTO  " . DB_PREFIX . "campaigns						   
						(
							thirdPartyCampaignID,
							title,
							createdDate,
							scheduledDate,
							content,
							openCount,
							clickCount,
							bounceCount,
							uniqueOpenedCount,
							uniqueClickCount,
							unsubscribedCount,
							forwardsCount,
							userCount,
							businessID,
							sendEmails,
							campaignType,
							purchaseCount,
							extra
						)
						VALUES
						(
							'0',
							'" . $title . "',
							NOW(),
							NOW(),
							'" . $content . "',
							0,
							0,
							0,
							0,
							0,
							0,
							0,
							'" . $recipients . "',
							'" . $businessID . "',
							'" . $sendEmails . "',
							'cart',
							0,
							'" . $extra . "'
						)";
            $this->ObjDatabase->setQuery($sql);
        } else if ($businessID > 0) //if its a business product mail
            {
            $sql = "INSERT INTO  " . DB_PREFIX . "campaigns						   
						(
							thirdPartyCampaignID,
							title,
							createdDate,
							scheduledDate,
							content,
							openCount,
							clickCount,
							bounceCount,
							uniqueOpenedCount,
							uniqueClickCount,
							unsubscribedCount,
							forwardsCount,
							userCount,
							businessID,
							sendEmails,
							campaignType,
							purchaseCount
						)
						VALUES
						(
							'0',
							'" . $title . "',
							NOW(),
							'" . $campaignDate . "',
							'" . $content . "',
							0,
							0,
							0,
							0,
							0,
							0,
							0,
							'" . $recipients . "',
							'" . $businessID . "',
							'" . $sendEmails . "',
							'business',
							0
						)";
            $this->ObjDatabase->setQuery($sql);
        } else //If its a product mail.
            {
            if ($campaignName == '') //if mail sending using system mail
                {
                $sql = " INSERT INTO  " . DB_PREFIX . "campaigns						   
							(
								thirdPartyCampaignID,
								title,
								createdDate,
								scheduledDate,
								content,
								openCount,
								clickCount,
								bounceCount,
								uniqueOpenedCount,
								uniqueClickCount,
								unsubscribedCount,
								forwardsCount,
								userCount,
								businessID,
								campaignType,
								purchaseCount
							)
							VALUES
							(
								'0',
								'" . $title . "',
								NOW(),
								'" . $campaignDate . "',
								'" . $content . "',
								0,
								0,
								0,
								0,
								0,
								0,
								0,
								'" . $recipients . "',
								0,
								'product',
								0
							)";
                $this->ObjDatabase->setQuery($sql);
                $lastInsertProductID = $this->ObjDatabase->getInsertId();
                foreach ($listID as $list) {
                    $sql = " INSERT INTO " . DB_PREFIX . "campaign_cities						   
								(
									campaignID,
									cityID
								)
								VALUES
								(
									'" . $lastInsertProductID . "',
									'" . $list . "'
								)";
                    $this->ObjDatabase->setQuery($sql);
                }
            } else //if mail send using third party mail service
                {
                if ($campaignName == 'mail_chimp') {
                    if ($productTypeKey != '') {
                        $recipients = $this->getRecipientsCount($listID, $productTypeKey, $languageId);
                    }
                    $sql = " INSERT INTO  " . DB_PREFIX . "campaigns						   
								(
									thirdPartyCampaignID,
									title,
									createdDate,
									scheduledDate,
									content,
									openCount,
									clickCount,
									bounceCount,
									uniqueOpenedCount,
									uniqueClickCount,
									unsubscribedCount,
									forwardsCount,
									userCount,
									businessID,
									campaignType,
									purchaseCount
								)
								VALUES
								(
									'" . $campaignID . "',
									'" . $title . "',
									NOW(),
									'" . $campaignDate . "',
									'" . $content . "',
									0,
									0,
									0,
									0,
									0,
									0,
									0,
									'" . $recipients . "',
									0,
									'product',
									0
								)";
                    $this->ObjDatabase->setQuery($sql);
                    $lastInsertProductID = $this->ObjDatabase->getInsertId();
                    foreach ($listID as $list) {
                        $sql = " INSERT INTO  " . DB_PREFIX . "campaign_cities						   
									(
										campaignID,
										cityID
									)
									VALUES
									(
										'" . $lastInsertProductID . "',
										'" . $list . "'
									)";
                        $this->ObjDatabase->setQuery($sql);
                    }
                } else if ($campaignName == 'campaign_monitor') {
                    if ($productTypeKey != '') {
                        $recipients = $this->getRecipientsCount($listID, $productTypeKey, $languageId);
                    }
                    $sql = " INSERT INTO  " . DB_PREFIX . "campaigns						   
								(
									thirdPartyCampaignID,
									title,
									createdDate,
									scheduledDate,
									content,
									openCount,
									clickCount,
									bounceCount,
									uniqueOpenedCount,
									uniqueClickCount,
									unsubscribedCount,
									forwardsCount,
									userCount,
									businessID,
									campaignType,
									purchaseCount
								)
								VALUES
								(
									'" . $campaignID . "',
									'" . $title . "',
									NOW(),
									'" . $campaignDate . "',
									'" . $content . "',
									0,
									0,
									0,
									0,
									0,
									0,
									0,
									'" . $recipients . "',
									0,
									'product',
									0
								)";
                    $this->ObjDatabase->setQuery($sql);
                    $lastInsertProductID = $this->ObjDatabase->getInsertId();
                    foreach ($listID as $list) {
                        $sql = " INSERT INTO  " . DB_PREFIX . "campaign_cities						   
									(
										campaignID,
										cityID
									)
									VALUES
									(
										'" . $lastInsertProductID . "',
										'" . $list . "'
									)";
                        $this->ObjDatabase->setQuery($sql);
                    }
                } else if ($campaignName == 'constant_contact') {
                    $recipients = $this->getRecipientsCount($listID);
                    $sql        = " INSERT INTO  " . DB_PREFIX . "campaigns						   
								(
									thirdPartyCampaignID,
									title,
									createdDate,
									scheduledDate,
									content,
									openCount,
									clickCount,
									bounceCount,
									uniqueOpenedCount,
									uniqueClickCount,
									unsubscribedCount,
									forwardsCount,
									userCount,
									businessID,
									campaignType,
									purchaseCount
								)
								VALUES
								(
									'" . $campaignID . "',
									'" . $title . "',
									NOW(),
									'" . $campaignDate . "',
									'" . $content . "',
									0,
									0,
									0,
									0,
									0,
									0,
									0,
									'" . $recipients . "',
									0,
									'product',
									0
								)";
                    $this->ObjDatabase->setQuery($sql);
                    $lastInsertProductID = $this->ObjDatabase->getInsertId();
                    foreach ($listID as $list) {
                        $sql = " INSERT INTO  " . DB_PREFIX . "campaign_cities						   
									(
										campaignID,
										cityID
									)
									VALUES
									(
										'" . $lastInsertProductID . "',
										'" . $list . "'
									)";
                        $this->ObjDatabase->setQuery($sql);
                    }
                } else {
                    //other extended third parties
                }
            }
        }
        return true;
    }
    //Function to get the count of Recipients for a particular Campaign
    //@usge saveCampaign function
    //@parm array() List Ids of the the particular Campaign 
    public function getRecipientsCount($listID, $productTypeKey = '', $languageId = 0) {
        return 0;
    }
    //Function to get the next inserting Campaign ID 
    //@usage for setting tracking links while Generate product mial befor shedule the campagain
    public function getNextCampaignID() {
        $sql           = " SELECT 
						max(CampaignID) as max 
					FROM 
						" . DB_PREFIX . "campaigns";
        $resCampaignID = $this->ObjDatabase->readValues($sql);
        return $resCampaignID[0]['max'] + 1;
    }
    //Function update the counts of a particular Campaign
    //@usage Only in system Mail
    //@Options (open,click,unsubscribe)
    public function updateCount($type = 'open', $campaignID = 0, $user) {
        if ($campaignID > 0) {
            switch ($type) {
                case 'open':
                    $sql          = " SELECT 
									count(campaignRecipientsID) as openCount 
								FROM 
									" . DB_PREFIX . "campaign_recipients 
								WHERE 
									type='Open' 
									AND emailID = '" . $user . "' 
									AND campaignID='" . $campaignID . "' ";
                    $resOpenCount = $this->ObjDatabase->readValues($sql);
                    if ($resOpenCount[0]['openCount'] > 0) {
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										openCount=openCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                    } else {
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										uniqueOpenedCount=uniqueOpenedCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										openCount=openCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                        $sql                = " INSERT INTO " . DB_PREFIX . "campaign_recipients						   
									(
										campaignID,
										emailID,
										actionDate,
										type
									)
									VALUES
									(
										'" . $campaignID . "',
										'" . $user . "',
										NOW(),
										'Open'
									)";
                        $this->ObjDatabase->setQuery($sql);
                    }
                    break;
                case 'click':
                    $sql           = " SELECT 
									count(campaignRecipientsID) as clickCount 
								FROM 
									" . DB_PREFIX . "campaign_recipients 
								WHERE 
									type='Click' 
									AND emailID = '" . $user . "' 
									AND campaignID='" . $campaignID . "' ";
                    $resClickCount = $this->ObjDatabase->readValues($sql);
                    if ($resClickCount[0]['clickCount'] > 0) {
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										clickCount=clickCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                    } else {
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										uniqueClickCount=uniqueClickCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                        $sql                = " UPDATE 
										`" . DB_PREFIX . "campaigns` 
									SET 
										clickCount=clickCount+1 
									WHERE 
										campaignID='" . $campaignID . "' ";
                        $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                        $sql                = " INSERT INTO " . DB_PREFIX . "campaign_recipients						   
					 				(
					 					campaignID,
					 					emailID,
									 	actionDate,
										type
									)
					 				VALUES
					 				(
					 					'" . $campaignID . "',
					 					'" . $user . "',
					 					NOW(),
					 					'Click'
					 				)";
                        $this->ObjDatabase->setQuery($sql);
                    }
                    break;
                case 'unsubscribe':
                    $sql                = " UPDATE 
									`" . DB_PREFIX . "campaigns` 
								SET 
									unsubscribedCount=unsubscribedCount+1 
								WHERE 
									campaignID='" . $campaignID . "' ";
                    $resUpdateCampagain = $this->ObjDatabase->setQuery($sql);
                    break;
            }
            return true;
        } else
            return false;
    }
    //Email tracking reports end
    public function getCartExtraData($campaignID = 0, $notificationID = 0) {
        if ($campaignID > 0) {
            $sql    = " SELECT 
							extra 
						FROM 
							" . DB_PREFIX . "campaigns 
						WHERE 
							campaignID=" . $campaignID;
            $result = $this->ObjDatabase->readValue($sql);
        } else if ($notificationID > 0) {
            $sql    = " SELECT 
							extra 
						FROM 
							" . DB_PREFIX . "cart_notifications 
						WHERE 
							notificationID=" . $notificationID;
            $result = $this->ObjDatabase->readValue($sql);
        }
        return $result['extra'];
    }
    public function saveCampaignRecipient($campaignID, $user, $mode = 'send') {
        $sql = " INSERT INTO " . DB_PREFIX . "campaign_recipients						   
					(
						campaignID,
						emailID,
						actionDate,
						type
					)
					VALUES
					(
						'" . $campaignID . "',
						'" . $user . "',
						NOW(),
						'Send'
					)";
        $this->ObjDatabase->setQuery($sql);
    }
}