<?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: CampaignModel.php, v 1.0 2011/06/14 20:52:11
//
class CampaignModel extends Model {
    //FUNCTION to save campaign & ad banner Details
    function saveCampaign() {
        $productID       = $this->escape($_POST['productID']);
        $bannerName      = $this->escape($_POST['bannerName']);
        $destinationURL  = $this->escape($_POST['destinationURL']);
        $bannerStartDate = date("Y-m-d", strtotime($this->escape($_POST['bannerStartDate'])));
        $bannerEndDate   = date("Y-m-d", strtotime($this->escape($_POST['bannerEndDate'])));
        $this->ObjDatabase->setQuery("
					INSERT INTO " . DB_PREFIX . "calendar_campaign
					(productID,
						bannerName,
						campaignStartDate,
						CampaignEndDate,
						campaignStatus,
						campaignModifiedDate,
						campaignCreatedDate,
						destinationUrl,
						languageID,
						bannerDesignID)
					VALUES
						(" . $productID . ",
						'" . $bannerName . "',
						'" . $bannerStartDate . "',
						'" . $bannerEndDate . "',
						'Active',
						CURRENT_TIMESTAMP,
						CURRENT_TIMESTAMP,
						'" . $destinationURL . "',
						" . (int) $_POST['languageID'] . ",
						" . (int) $_POST['design'] . ")");
        $campaignID = $this->ObjDatabase->getInsertId();
        $qryInsert  = array();
        foreach ($_POST['banners'] as $key => $bannerType) {
            $type = explode(",", $bannerType);
            $this->ObjDatabase->setQuery("
						INSERT INTO " . DB_PREFIX . "campaign_ads
							(adSize,
							campaignID,
							template,
							imagePath)
						VALUES
							('" . $type[1] . "',
							" . $campaignID . ",
							'" . $campaignID . "_" . $type[1] . "_" . $type[2] . "',
							'')");
            $_POST['banners'][$key] = array(
                'name' => $_POST['banners'][$key],
                'bannerid' => $this->ObjDatabase->getInsertId()
            );
        }
        return array(
            $campaignID,
            $_POST['banners']
        );
    }
    //END : saveCampaign
    //FUNCTION to GET the campaign details based on CampaignID
    function getCampaignDetails($campaignID, $status = "") {
        $campaignID = (int) $campaignID;
        $status     = $this->escape($status);
        $extQuery   = '';
        if ($status == '') {
            $extQuery .= " AND campaignStatus!='Deleted'";
            return $this->ObjDatabase->readValue("
						SELECT
							*
						FROM
								`" . DB_PREFIX . "calendar_campaign` calCam
							LEFT JOIN
								`" . DB_PREFIX . "products` product 
									ON (product.productID=calCam.productID)
							LEFT JOIN
								`" . DB_PREFIX . "product_details` productDet
									ON (product.productID=productDet.productID)
						WHERE
							campaignStatus!='Deleted'
							AND
							campaignID='" . $campaignID . "'" . $extQuery);
        }
    }
    //END : getCampaignDetails
    //FUNCTION to get the bannerDetails based on CampaignID
    function getCampaignSubDetails($campaignID) {
        $campaignID = (int) $campaignID;
        return $this->ObjDatabase->readValues("
			SELECT
				*
			FROM
				`" . DB_PREFIX . "campaign_ads`
			WHERE
				campaignID='" . $campaignID . "'");
    }
    function getCampaignBannerDetails($campaignID, $campaignAdID) {
        $campaignID   = (int) $campaignID;
        $campaignAdID = (int) $campaignAdID;
        return $this->ObjDatabase->readValue("
			SELECT
				*
			FROM
				`" . DB_PREFIX . "calendar_campaign` calCam
				INNER JOIN
				`" . DB_PREFIX . "campaign_ads` camAds
					ON (camAds.campaignID=calCam.campaignID)
			WHERE
				campaignStatus!='Deleted'
				AND
				calCam.campaignID='" . $campaignID . "'
				AND
				campaignAdID='" . $campaignAdID . "'");
    }
    //END :getCampaignSubDetails
    function checkDate($POST) {
        $varStartDate       = $this->escape($_POST['varStartDate']);
        $varEndDate         = $this->escape($_POST['varEndDate']);
        $_POST['productID'] = (int) $_POST['productID'];
        $sql                = "
					(SELECT
						timeZoneDiff
					FROM
						`" . DB_PREFIX . "timezones`
					WHERE
						timeZoneID=
						(SELECT
							productTimeZone
						FROM
							`" . DB_PREFIX . "products`
						WHERE
							productID='" . $_POST['productID'] . "'))";
        $res                = $this->ObjDatabase->readValue($sql);
        $sql                = "
					SELECT
						(productEndTime>='" . $varEndDate . "' &&
						productStartTime<='" . $varEndDate . "'
						&& 
						productEndTime>='" . $varStartDate . "' &&
						productStartTime<='" . $varStartDate . "') as checkdate,
						productEndTime
					FROM
						`" . DB_PREFIX . "products` as product
					WHERE
						productID='" . $_POST['productID'] . "'";
        $resDate            = $this->ObjDatabase->readValue($sql);
        return $resDate;
    }
    //FUNCTION to list all campaigns
    function listCampaign($languageIDs, $status = '') {
        $qryExt = '';
        $status = $this->escape($status);
        if ($status == '')
            $qryExt .= " AND campaignStatus!='Deleted'";
        else
            $qryExt .= " AND campaignStatus!='" . $status . "'";
        if ($_POST['banner'] != '')
            $qryExt .= " AND lower(bannerName) like lower('" . $this->escape($_POST['banner']) . "%') ";
        if ($_POST['key'] != '')
            $qryExt .= " AND productID in
							(
								SELECT
									product.productID
								FROM
									`" . DB_PREFIX . "products` product,
									" . DB_PREFIX . "product_details productDet
								WHERE
									product.productID=productDet.productID
									AND
									languageID='" . $languageIDs['Current'] . "'
									AND
									(
										lower(productTitle) like lower('%" . $this->escape($_POST['key']) . "%')
									)
							)";
        if ($_POST['languageID'] != '')
            $qryExt .= " AND languageID ='" . (int) $_POST['languageID'] . "'";
        $sql = "
				SELECT
					(
						SELECT
							count(*)
						FROM
							`" . DB_PREFIX . "campaign_ads`
						WHERE
							`campaignID`=calCam.`campaignID`
					)as banners,
					campaignID,
					calCam.productID,
					bannerName,
					DATE_FORMAT(campaignStartDate, '%Y-%m-%d') campaignStartDate,
					DATE_FORMAT(campaignEndDate,'%Y-%m-%d') campaignEndDate, 
					campaignStatus,
					campaignModifiedDate,
					campaignCreatedDate,
					destinationUrl,
					(
						select
							count(*)
						FROM
							`" . DB_PREFIX . "campaign_ads`
						WHERE
							campaignID=calCam.campaignID
					)
				FROM
					`" . DB_PREFIX . "calendar_campaign` as calCam ";
        /*City Based Privilages*/
        $sql .= " WHERE  campaignStatus!='Deleted' ";
        /*City Based Privilages*/
        $sql .= " AND
				((
				'" . $_POST['fromDate'] . "' BETWEEN `campaignStartDate` AND `campaignEndDate`
				OR
				'" . $this->escape($_POST['toDate']) . "' BETWEEN `campaignStartDate` AND `campaignEndDate`
				)
					OR
				(
				`campaignStartDate` BETWEEN '" . $_POST['fromDate'] . "' AND '" . $this->escape($_POST['toDate']) . "'
				OR
				`campaignEndDate` BETWEEN '" . $_POST['fromDate'] . "' AND '" . $this->escape($_POST['toDate']) . "'
				))
				" . $qryExt;
        return $this->ObjDatabase->readValues($sql);
    }
    //END : listCampaign
    //FUNCTION to change the status of Campaign as deleted
    function changeCampaignStatus() {
        $sql = "
			UPDATE
				`" . DB_PREFIX . "calendar_campaign`
			SET
				campaignStatus='" . $_POST['status'] . "',
				campaignModifiedDate=CURRENT_TIMESTAMP
			WHERE
				campaignID in (" . implode(',', $_POST['campaignIDs']) . ")
		";
        return $this->ObjDatabase->setQuery($sql);
    }
    //END : changeCampaignStatus
    //FUNCTION to update the banner Attributes when cliking the banner save option
    function updateBannerAttr($campaignID, $bannerid, $imagePath, $imgAttr) {
        return $this->ObjDatabase->setQuery("
					UPDATE `" . DB_PREFIX . "campaign_ads`
					SET
						imagePath='" . $imagePath . "',
						imageAttr='" . $imgAttr . "'
					WHERE
						campaignID='" . $campaignID . "'
						AND
						campaignAdID='" . $bannerid . "'");
    }
    //END : Function updateBannerAttr
    //FUNCTION to get the size of banner size
    function getBannerSize($bannerid) {
        return $this->ObjDatabase->readValue("
					SELECT
						adSize
					FROM
						`" . DB_PREFIX . "campaign_ads`
					WHERE
						campaignAdID='" . $bannerid . "'");
    }
    //END: Function getBannerSize 
    //FUNCTION to check campaign Exists or not
    function checkCampaignExists($campaignID) {
        return $this->ObjDatabase->readValue("
						SELECT EXISTS
						(
							SELECT
								*
							FROM
								`" . DB_PREFIX . "calendar_campaign`
							WHERE
								campaignID='" . $campaignID . "'
								AND
								campaignStatus='Active'
						)");
    }
    //END: Function checkCampaignExists
}