<?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: MenuModel.php, v 1.0 2011/06/14 20:52:11
//
class NewsletterModel extends Model
{
    public function saveNewsletter($languageID, $POST, $newsletterID = "")
    {
		$ObjImageModel	=	new	ImageModel();		
        $newsletterID = $POST['newsletterID'];
        $sentStatus   = $POST['sendingTime'] == 'Now' ? 'Yes' : 'No';
		$_POST['conditions'] = serialize($_POST['promotion_data']['conditions']);	
		$_POST['customerConditions'] = serialize($_POST['promotion_data']['bonuses']);	
 
        if( !empty($_POST['file'])){
            copy($_POST['newsletterAttachmenttemppath'],__CORE_PUBLIC_PATH."/uploads/newsletter/attachment/".$_POST['file']);
            $ObjImageModel->copy_file('public/uploads/newsletter/attachment/'.$_POST['file'],'/'.__UPLOADS_FOLDER.'newsletter/attachment/'.$_POST['file']);                       
            @unlink($_POST["newsletterAttachmenttemppath"]);
        }else if(isset($_POST['hidNewsletterAttachment']) && $_POST['hidNewsletterAttachment']!=''){
			$_POST['newsletterAttachment'] =$_POST['hidNewsletterAttachment'];
		} 
		if($_POST['templateType']!='product'){
			$_POST['productListingLimit'] =0;
		}
        if (!empty($_POST['newsletterGroup']))
        {
            $subscriberType = $_POST['newsletterGroup'];
            if ($subscriberType !='customgroup')
            {
                $_POST['otherSubcriberType'] = '';
            }
            if ($subscriberType !='savedgroup')
            {
                $_POST['customerRuleID'] = 0;
            }
        }
        $scheduleDate = ($POST['sendingTime'] == 'Sheduled') ? date("Y-m-d H:i:s", strtotime($POST['scheduleDate'])) : '';
		$resSubscribersCount	=count($_POST['emailList']);
        if ($newsletterID != '')
        {
            $newsLetterDetailsID = $POST['newsLetterDetailsID'];
              $sql                 = " UPDATE " . DB_PREFIX . "newsletters SET
									newsletterFromName  ='" . $this->escape($POST['newsletterFromName']) . "',
									newsletterFromEmail ='" . $this->escape($POST['newsletterFromEmail']) . "',
									newsletterReplyEmail ='" . $this->escape($POST['newsletterReplyEmail']) . "',
									newsletterGroup ='" . $subscriberType . "',
									newsletterSent ='" . $sentStatus . "',
									otherSubcriberType ='" . $this->escape($_POST['otherSubcriberType']) . "',
									conditions ='" . $this->escape($_POST['conditions']) . "',
									customerConditions ='" . $this->escape($_POST['customerConditions']) . "',
									newsletterAttachment ='" . $this->escape($_POST['newsletterAttachment']) . "',
									nlTemplateID='" . $POST['mailTemplates'] . "',
									templateType='" . $_POST['templateType'] . "',
									productListingLimit='" . $_POST['productListingLimit'] . "',
									totalRemainingCount='" . $_POST['totalRemainingCount'] . "',
									totalEmailCount='" . $_POST['totalEmailCount'] . "',
									customerRuleID='" . $_POST['customerRuleID'] . "',
									mailQueueStatus='" . $_POST['mailQueueStatus'] . "',
									mailScheduledTime=" . $_POST['mailScheduledTime'] . ",
									newsLetterStatus ='" . $_POST['sendingTime'] . "'
							  WHERE
								newsletterID  =" . $newsletterID;
            $resUpdateNewsletter = $this->ObjDatabase->setQuery($sql);
	/*	$sql	=	"DELETE	FROM	
						".DB_PREFIX."mail_queue_emails	
					WHERE	newsletterID ='".$newsletterID."'";
		$this->ObjDatabase->setQuery($sql);
 			if($resSubscribersCount>0)
			{
				foreach($_POST['emailList'] as $emailID)	
				{					
					if($emailID!='')	
					{
						$mailQueSql[]	=	"											
											(
												'".$emailID."',
												".$newsletterID."
											)";
					}
				}
				$this->ObjDatabase->setQuery("
										INSERT	INTO	
											".DB_PREFIX."mail_queue_emails
											(
												emailAddress,
												newsletterID
											)
											VALUES".implode(",",$mailQueSql));
			} */				
            if ($resUpdateNewsletter)
            {
                $sql              = " UPDATE " . DB_PREFIX . "newsletter_details SET
									newsLetterSubject		= '" . $this->escape($POST['newsLetterSubject'], FALSE) . "',
									campaignTitle		= '" . $this->escape($POST['campaignTitle'], FALSE) . "',
									newsLetterContentHtml   = '" . $this->escape($POST['newsLetterContentHtml'], FALSE) . "',                                    	 		languageID              = '" . $languageID['Current'] . "'
							  WHERE
								newsLetterDetailsID = " . $newsLetterDetailsID;
                $resUpdateDetails = $this->ObjDatabase->setQuery($sql);
                return $resUpdateDetails;
            }
            else
            {
                return $resUpdateNewsletter;
            }
        }
        else
        {
            $sql                 = "INSERT INTO " . DB_PREFIX . "newsletters
								 (
								  newsletterFromName,
								  nlTemplateID,
								  newsletterFromEmail,
								  newsletterReplyEmail,
								  newsletterSent,
								  newsletterCreateDate,
								  newsletterGroup,
								  conditions,
								  customerConditions,
								  newsletterAttachment,
								  otherSubcriberType,
								  newsLetterStatus,
								  templateType,
								  productListingLimit,
								  customerRuleID,
								  mailScheduledTime,
								  mailQueueStatus,
								  totalRemainingCount,
								  totalEmailCount
								 )
								 VALUES
								 (
								  '" . $this->escape($POST['newsletterFromName']) . "',
								  '" . $POST['mailTemplates'] . "',
								  '" . $this->escape($POST['newsletterFromEmail']) . "',
								  '" . $POST['newsletterReplyEmail'] . "',
								  '" . $sentStatus . "',								  
								  NOW(),
								 '" . $subscriberType . "',
								 '" . $this->escape($_POST['conditions']) . "',
								 '" . $this->escape($_POST['customerConditions']) . "',
								 '" . $this->escape($_POST['newsletterAttachment']) . "',
								 '" . $this->escape($_POST['otherSubcriberType']) . "',
								 '" . $_POST['sendingTime'] . "',
								 '" . $_POST['templateType'] . "',
								 '" . $_POST['productListingLimit'] . "',
								 '" . $_POST['customerRuleID'] . "',
								 " . $_POST['mailScheduledTime'] . ",
								 '" . $_POST['mailQueueStatus'] . "',
								 '" . $_POST['totalRemainingCount'] . "',
								 '" . $_POST['totalEmailCount'] . "'
								
								 )";
            $resInsertNewsletter = $this->ObjDatabase->setQuery($sql);
            if ($resInsertNewsletter)
            {
                $newsletterID     = $this->ObjDatabase->getInsertId();
/* 			if($resSubscribersCount>0)
			{
				foreach($_POST['emailList']  as $emailID)	
				{					
					if($emailID!='')	
					{
						$mailQueSql[]	=	"											
											(
												'".$emailID."',
												".$newsletterID."
											)";
					}
				}
				$this->ObjDatabase->setQuery("
										INSERT	INTO	
											".DB_PREFIX."mail_queue_emails
											(
												emailAddress,
												newsletterID
											)
											VALUES".implode(",",$mailQueSql));
			} */				
                $sql              = "INSERT INTO " . DB_PREFIX . "newsletter_details
							(newsLetterID,
							 languageID,
							 newsLetterSubject,
							 campaignTitle,
							 newsLetterContentHtml
							)
							VALUES
							(" . $newsletterID . ",
							 '" . $languageID['Current'] . "',
							  '" . $this->escape($POST['newsLetterSubject'], FALSE) . "',
							  '" . $this->escape($POST['campaignTitle'], FALSE) . "',
							  '" . $this->escape($POST['newsLetterContentHtml'], FALSE) . "')";
                $resInsertDetails = $this->ObjDatabase->setQuery($sql);
                if ($resInsertDetails)
                {
                    return $newsletterID;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return $resInsertNewsletter;
            }
        }
    }
    public function listNewsletters($languageID)
    {
        $sql    = "SELECT 
							 		  		newsltr.newsletterID,
									  		newsltrDet.newsLetterSubject,
							 		  		newsltr.newsletterSentDate
                             		  FROM 
							 		  		" . DB_PREFIX . "newsletters AS newsltr
                             		  JOIN
							 		  		" . DB_PREFIX . "newsletter_details AS newsltrDet
                             		  ON (newsltr.newsletterID = newsltrDet.newsletterID)
                             		  WHERE
							 		  		newsltrDet.languageID ='" . $languageID . "'
                             		  ORDER BY 
							 		  newsltr.newsletterID";
        $result = $this->ObjDatabase->readValues($sql);
        return $result;
    }
    public function getNewsletterDetails($languageID, $newsletterID)
    {
        $sql    = "SELECT 
	   									  *
									  FROM 
									  		" . DB_PREFIX . "newsletters AS newsltr
                                      LEFT JOIN
									  		" . DB_PREFIX . "newsletter_details AS newsltrDet
                                      ON (newsltr.newsletterID = newsltrDet.newsletterID)
                                      WHERE
									  newsltr.newsletterID='" . $newsletterID . "'
									  AND newsltrDet.languageID ='" . $languageID . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result;
    }
    public function getNewsletterTempLists($languageIDs, $limitFrom = 0, $limitTo = "", $nlTemaplateName = "", $nltStatus = "")
    {
        $currentLanguageID = $languageIDs;
        $sql               = "SELECT ";
        if ($limitTo)
        {
            $sql .= " SQL_CALC_FOUND_ROWS ";
        }
        $sql .= " * FROM
									  	  		" . DB_PREFIX . "newsletter_templates AS ns
										   LEFT JOIN  " . DB_PREFIX . "newsletter_template_details AS nsd
                                                 ON(ns.nlTemplateID = nsd.nlTemplateID)
										   WHERE ns.nltStatus!='Deleted' AND languageID='" . $currentLanguageID . "'";
        if ($nlTemaplateName != '')
        {
            $sql .= " AND nsd.nlTemplateName LIKE '%" . $this->escape($nlTemaplateName) . "%' ";
        }
        if ($nltStatus != '')
        {
            $sql .= " AND ns.nltStatus ='" . $this->escape($nltStatus) . "'";
        }
	
        if ($limitTo)
        {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        $resSelectNewsletterTempList = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        return $resSelectNewsletterTempList;
    }
    public function getNewsletterTempDetails($newsletterTempID, $languageIDs)
    {
        $sql    = "SELECT nwsltrTmp.*,nwsltrTmpDet.*
										   FROM
									  	      " . DB_PREFIX . "newsletter_templates AS nwsltrTmp
										   LEFT JOIN  " . DB_PREFIX . "newsletter_template_details AS nwsltrTmpDet
                                                 ON(nwsltrTmp.nlTemplateID = nwsltrTmpDet.nlTemplateID
												 AND nwsltrTmpDet.languageID='" . $languageIDs['Current'] . "')
										   WHERE 
										   	  nwsltrTmp.nlTemplateID='" . $newsletterTempID . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result;
    }
    public function getQueuedMailDetails($newsletterID)
    {
        $sql = "SELECT 
	   										   totalEmailCount,
										       totalRemainingCount
								   		   FROM " . DB_PREFIX . "mail_queue_contents
                                   		   WHERE 
											referringID=".$newsletterID."";
		return $this->ObjDatabase->readValue($sql);
   }
	public function saveNewsletterTemplate($languageID,$newsletterTempID=0)
     {
	 	   $newslettertempImage 	 = $_POST['nlTemplatePreview'];
				if(!empty($_POST['nlTemplatePreview_temp'])){
					$logoImageWidth  	 	 = 600; 
					$logoImageHeight 		 = 300;
					$ObjImageModel 			 = new ImageModel();					
					$ObjImageModel->copy_file($_POST["nlTemplatePreview_temp"],'/'.__UPLOADS_FOLDER.'newsletter/'.$_POST['nlTemplatePreview']);
					$ObjImageModel->resizeImage(__ROOT_PATH.'/'.$_POST["nlTemplatePreview_temp"],'', $logoImageWidth, $logoImageHeight);
					$ObjImageModel->copy_file($_POST["nlTemplatePreview_temp"],'/'.__UPLOADS_FOLDER.'newsletter/thumb/'.$_POST['nlTemplatePreview']);
					
					@unlink($_POST["nlTemplatePreview_temp"]);
				}			
			
			if(isset($_POST['nltdefault']) && ($_POST['nltdefault']=='Yes'))
			{
				$sql			  		    = 	" UPDATE ".DB_PREFIX."newsletter_templates SET		  											

                                          			nltDefault    ='No'
                                          	  WHERE nlTemplateID  !=" . $newsletterTempID . "";
            $this->ObjDatabase->setQuery($sql);
        }
	if($newsletterTempID>0){	
        $sql = " UPDATE " . DB_PREFIX . "newsletter_templates SET
		  											updatedDate   =now(),
													nlTemplatePreview ='" . $_POST['nlTemplatePreview'] . "',
													templateType='" . $_POST['templateType'] . "',
                                          	 		nltStatus     ='" . $this->escape($_POST['nltStatus']) . "'
                                          	  WHERE nlTemplateID  =" . $newsletterTempID . "";
        $this->ObjDatabase->setQuery($sql);
        $sqlCheckDetailsExists = "SELECT 
		   									     COUNT(*)
									  	   FROM " . DB_PREFIX . "newsletter_template_details
                                           WHERE nlTemplateID = '" . $newsletterTempID . "'
                                            	 AND languageID = '" . $languageID . "'";
        $sqlCheckDetailsExists = $this->ObjDatabase->readValue($sqlCheckDetailsExists);
        if ($sqlCheckDetailsExists[0] > 0)
        {
            $sql = " UPDATE " . DB_PREFIX . "newsletter_template_details SET
														nlTemplateName   ='" . $this->escape($_POST['nlTemplateName']) . "',
														nlTemplateContent='" . $this->escape($_POST['nlTemplateContent'], FALSE) . "'
												  WHERE nlTemplateID     =" . $newsletterTempID . " AND languageID=" . $languageID . "";
            $this->ObjDatabase->setQuery($sql);
        }
        else
        {
            $sqlInsertEmailTempDetails = "INSERT INTO " . DB_PREFIX . "newsletter_template_details(
                                             nlTemplateID,
                                             nlTemplateName,
                                             nlTemplateContent,
                                             languageID)
                                             VALUES(
                                             " . $newsletterTempID . ",
                                             '" . $this->escape($_POST['nlTemplateName']) . "',
                                             '" . $this->escape($_POST['nlTemplateContent'], FALSE) . "',
                                             '" . $languageID . "'
                                             )";
            $resInsertEmailTempDetails = $this->ObjDatabase->setQuery($sqlInsertEmailTempDetails);
        }
		}else{
             $sql = "INSERT INTO " . DB_PREFIX . "newsletter_templates(
                                             nlTemplatePreview,
                                             updatedDate,
                                             nltStatus,
                                             templateType)
                                             VALUES(
                                            '" . $this->escape($_POST['nlTemplatePreview']) . "',
                                             NOW(),
                                             '" . $this->escape($_POST['nltStatus']) . "',
                                             '" . $this->escape($_POST['templateType']) . "'
                                             )";
            $this->ObjDatabase->setQuery($sql);	
			$newsletterTempID	=	$this->ObjDatabase->getInsertId();
            $sqlInsertEmailTempDetails = "INSERT INTO " . DB_PREFIX . "newsletter_template_details(
                                             nlTemplateID,
                                             nlTemplateName,
                                             nlTemplateContent,
                                             languageID)
                                             VALUES(
                                             " . $newsletterTempID . ",
                                             '" . $this->escape($_POST['nlTemplateName']) . "',
                                             '" . $this->escape($_POST['nlTemplateContent'], FALSE) . "',
                                             '" . $languageID . "'
                                             )";
            $resInsertEmailTempDetails = $this->ObjDatabase->setQuery($sqlInsertEmailTempDetails);	
			
		}
    }
    function changeDefaultTemplate($nlTemplateID)
    {
        $sql = " UPDATE 
											" . DB_PREFIX . "newsletter_templates	
										  SET
											nltDefault    ='No'
										  WHERE
											nlTemplateID  !=" . $nlTemplateID . "";
        $this->ObjDatabase->setQuery($sql);
        $sql = " UPDATE 
											" . DB_PREFIX . "newsletter_templates	
										  SET
											nltDefault    ='Yes'
										  WHERE
											nlTemplateID  =" . $nlTemplateID . "";
        $this->ObjDatabase->setQuery($sql);
        return true;
    }
    //Function to Return List news letter subscribers
   public function listNewsletterSubscribers($conditions="",$arrayKey="conditions",$params = array()) {
        extract($params);
		//print_r($conditions);		
		$selectedItem	= (isset($selectedItem) && $selectedItem!='')?$selectedItem:'CONCAT(userEmail,"~*",userFirstName," ",userLastName) userEmail';
				
		$queryRes             = $this->generateQueryConditionString($conditions,'',$arrayKey); 
        $queryCondition       = ($queryRes['condition'] != '') ? $queryRes['condition'] . " ) " : '';
		 $queryCondition       =&$queryCondition;
		 //	echo "<pre>";
/* 	print_r($queryCondition);
print_r($queryRes);exit; */
        $queryConditionTables = $queryRes['tables'];
        $orderBy              = $queryRes['orderBy'];
	
        $sql = "
				SELECT 
					$selectedItem 
				FROM 
					" . DB_PREFIX . "user_profile  usrProf ";
					
		$sql .=	"	WHERE 
					1
					AND newsSubscribeStatus='Yes'
					";
        if ($queryCondition != '') {
            $sql .= "
					AND
					" . $queryCondition;
        }
		 $sql .= "
					GROUP BY userEmail
					" ;
               //  echo $sql;exit;
        return $this->ObjDatabase->getFields($sql);
    }
    //Function to Return Email ids of subscribed Users based on city , status , producttype, and language
    public function listEmailSubscribers($languageID)
    {
        $sql = "
				SELECT 
					subscEmailID 
				FROM 
					" . DB_PREFIX . "subscribers
				WHERE 
					subscStatus='Subscribed'
					AND languageID='" . $languageID . "'";
        return $this->ObjDatabase->getFields($sql);
    }
    public function listAffiliates($languageID)
    {
        $sql = "
				SELECT 
					affiliateEmail 
				FROM 
					" . DB_PREFIX . "affiliates 
				WHERE 
					affiliateStatus='Active'
					AND preferredLanguage='" . $languageID . "'";
        return $this->ObjDatabase->getFields($sql);
    }
    public function listBusiness($languageID)
    {
        $sql = "
						SELECT	
							businessEmail
						FROM 
							" . DB_PREFIX . "business	
						WHERE
							businessStatus='Active'
							AND businessDefaultLanguageID='" . $languageID . "'";
        return $this->ObjDatabase->getFields($sql);
    }
     public function changeTemplateStatus($nlTemplateID=0,$changeStatus="",$privilege="") {
       
	   if($privilege){
			 $sql = " UPDATE " . DB_PREFIX . "newsletter_templates SET
		  											
                                          	 		nltStatus     ='" . $this->escape($changeStatus) . "'
                                          	  WHERE nlTemplateID  =" . $nlTemplateID . "";
        $this->ObjDatabase->setQuery($sql);
		}
    } 			
 //Rewrite Condition Array
    public function rewriteConditions($params)
	{
        $ObjPromotions = new PromotionsModel();
        extract($params);
        $currentlanguage_id = $languageIDs['Current'];
        if ($type == "newsletterCustomerRule") {
            $dataSchema = $this->getCustomerRulesSchema($languageXml);
        }
        $params            = array(
            'conditionArray' => $conditionArray,
            'conditionOrigArray' => $conditionArray,
            'language_ids' => $languageIDs,
            'dataSchema' => $dataSchema,
            'currentlanguage_id' => $currentlanguage_id,
            'currentLoopKeyVar' => ''
        );
        //print_r($conditionArray);					
        $rewriteConditions = $ObjPromotions->getConditionData($params);
        //print_r($rewriteConditions);	
        return $rewriteConditions;
    }
  //Rewrite Condition Array
    public function rewriteBonus($params)
	{
        $ObjPromotions = new PromotionsModel();
        extract($params);
        $currentlanguage_id = $language_ids['Current'];
        if ($type == "newsletterCustomerRule") {
            $dataSchema = $this->getCustomerRulesSchema($languageXml,'bonuses');
        }
		$params				= array
							(
								'bonusArray'			=> $bonusArray,
								'bonusOrigArray'		=> $bonusArray,
								'dataSchema'			=> $dataSchema,
								'currentlanguage_id'	=> $currentlanguage_id,
								'currentLanguageID'	=> $currentlanguage_id,
								'language_ids'		    => $language_ids,
								'languageIDs'		    => $language_ids,
								'attrData'				=> array(),
								'currentLoopKeyVar' 	=> ''
							);	
					
		$rewriteBonus = $ObjPromotions->getBonusData($params);
        return $rewriteBonus;
    }	
    public function getCustomerRulesSchema($languageXml,$index='conditions') {
        //Conditions
        $schema[$index] = array(
            'lastpurchase' => array(
                'operators' => array(
                        'between->' . $languageXml["lblPromotionSchemaBetween"]
                    ),
                'type' => 'inputDate',
                'lang' => 'lblPromotionSchemaLastpurchaseday'
            ),
            'purchaseduration' => array(
                'operators' => array(
                        'on->' . $languageXml["lblPromotionSchemaOn"]
                    ),
                'type' => 'select',
                'options' => '7day->Last 7 Days~*lastMonth->Last Month'		,
                'lang' => 'lblPromotionSchemaPurchaseon'
            ),			
			'purchasevalue' => array(
                'operators' => array(
                    'eq->' . $languageXml["lblPromotionSchemaEq"],
                    'neq->' . $languageXml["lblPromotionSchemaNeq"],
                    'lte->' . $languageXml["lblPromotionSchemaLte"],
                    'gte->' . $languageXml["lblPromotionSchemaGte"],
                    'lt->' . $languageXml["lblPromotionSchemaLt"],
                    'gt->' . $languageXml["lblPromotionSchemaGt"]
                ),
                'type' => 'input',
                'lang' => 'lblPromotionSchemaPurchasevalue'
            ),			
            'totalquantity' => array(
                'operators' => array(
                    'eq->' . $languageXml["lblPromotionSchemaEq"],
                    'neq->' . $languageXml["lblPromotionSchemaNeq"],
                    'lte->' . $languageXml["lblPromotionSchemaLte"],
                    'gte->' . $languageXml["lblPromotionSchemaGte"],
                    'lt->' . $languageXml["lblPromotionSchemaLt"],
                    'gt->' . $languageXml["lblPromotionSchemaGt"]
                ),
                'type' => 'input',
                'lang' => 'lblPromotionSchemaTotalQuantity'
            ),			
            'totalorder' => array(
                'operators' => array(
                    'eq->' . $languageXml["lblPromotionSchemaEq"],
                    'neq->' . $languageXml["lblPromotionSchemaNeq"],
                    'lte->' . $languageXml["lblPromotionSchemaLte"],
                    'gte->' . $languageXml["lblPromotionSchemaGte"],
                    'lt->' . $languageXml["lblPromotionSchemaLt"],
                    'gt->' . $languageXml["lblPromotionSchemaGt"]
                ),
                'type' => 'input',
                'lang' => 'lblPromotionSchemaTotalOrder'
            ),		
            'usergroup' => array(
                'operators' => array(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
                'type' => 'select',
                'lang' => 'lblPromotionSchemaCustomergroup'
            ),
			'country' => array 
			(
				'operators' => array 
				(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
				'type' => 'select',
				'lang' => 'lblSchemaUserLastShippingCountry'
			),
			'state' => array
			(
				'operators' => array 
				(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
				'type' => 'select',
				'optionGroup' => 'Yes',
				'lang' => 'lblSchemaUserLastShippingState'
			),
			'billingcountry' => array 
			(
				'operators' => array 
				(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
				'type' => 'select',
				'lang' => 'lblSchemaUserLastBillingCountry'
			),
			'billingstate' => array
			(
				'operators' => array 
				(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
				'type' => 'select',
				'optionGroup' => 'Yes',
				'lang' => 'lblSchemaUserLastBillingState'
			),			
             'payment_method' => array(
                'operators' => array(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
				),
                'type' => 'select',
                'lang' => 'lblSchemaUserLastPaymentMethod'
            ),
            'shipping_method' => array(
                'operators' => array(
					'eq->'.$languageXml["lblPromotionSchemaEq"],
					'neq->'.$languageXml["lblPromotionSchemaNeq"]
                ),
                'type' => 'select',
                'lang' => 'lblSchemaUserLastShippingMethod'
            )
        );
        //print_r($schema);
        return $schema;
    }
    public function saveCustomerRule()
    {	
	$_POST['conditions'] = serialize($_POST['promotion_data']['conditions']);
	$customerRuleID = !empty($_POST['customerRuleID'])?$_POST['customerRuleID']:'' ;
	
	
       if ($customerRuleID > 0) {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "customer_rules 
				SET
					customerRuleName = '" . $this->escape($_POST['customerRuleName']) . "',
					conditions = '" . $this->escape($_POST['conditions']) . "',
					status = '" . $this->escape($_POST['status']) . "'";

            $sql .= "		
					
				WHERE
					customerRuleID = '" . $customerRuleID . "'
					";
            $this->ObjDatabase->setQuery($sql);
          
        } else {	
			$sql = "
							INSERT INTO " . DB_PREFIX . "customer_rules 
								(
								customerRuleName,
								status,
								conditions,
								createdDate
								)
							VALUES
								(
								
								'" . $this->escape($_POST['customerRuleName']) . "',
								'" . $this->escape($_POST['status']) . "',
								'" . $this->escape($_POST['conditions']) . "',
								NOW()
								)
							";
            $this->ObjDatabase->setQuery($sql);		 
		    $customerRuleID	= $this->ObjDatabase->getInsertId();
	   }
		return 	$customerRuleID ;		  
	}
    public function listCustomerRules($params = array()) {
        extract($params);
        $selectFields = '';
        if (isset($limitTo))
            $selectFields .= " SQL_CALC_FOUND_ROWS ";
        $sql = " 
				SELECT 
					" . $selectFields . "
					*
				FROM 
						" . DB_PREFIX . "customer_rules 
			
				WHERE
					1
				";

        if (isset($status) && $status != '') {
            $sql .= "
					AND
					status ='" . $status . "'
					";
        }
        if (isset($customerRuleName) && $customerRuleName != '') {
            $sql .= "
					AND
					customerRuleName like '%" . $this->escape($customerRuleName) . "%'
					";
        }
        if (isset($orderby) && isset($sortby)) {
            $sql .= "
					ORDER BY customerRuleID
						" . $orderby . "
				";
        }
        if (isset($limitTo) && $limitTo) {
            $sql .= " 
				LIMIT  
					" . $limitFrom . ",
					" . $limitTo . "
				";
        }
        return $this->ObjDatabase->readValues($sql);
    }
     public function changecustomerRuleStatus($params = array()) {
        extract($params);
        $sql = "
			UPDATE  
				" . DB_PREFIX . "customer_rules 
			SET 
				status = '" . $status . "' 
			WHERE
				customerRuleID IN ('" . implode("','", $customerRuleIDs) . "')
			";
        $this->ObjDatabase->setQuery($sql);
    } 	
    public function getCustomerRules($params = array()) {
        extract($params);
        $selectFields = '';
        $sql          = " 
			SELECT 
				" . $selectFields . "
				*
			FROM 
					" . DB_PREFIX . "customer_rules 
					WHERE
				customerRuleID = '" . $customerRuleID . "'
		";
        $sql .= " 
				LIMIT  
					1
				";
        return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
    }  
    //Recursive Function for generating Query Condition String
    private function generateQueryConditionString($conditions, $tables = '',$arrayKey="conditions") {
        $orderBy    = "";
		$query		= '';
		$key		= 1;
			//	echo "<pre>";
	//print_r($queryCondition);
	//print_r($conditions);exit;
        if (!empty($conditions)) {
            $braces = " ( ";
            if (isset($conditions[$arrayKey]) && !empty($conditions[$arrayKey])) {
                foreach ($conditions[$arrayKey] as  $condition) {
                    if ($conditions['set'] == 'any') {
                        $operator = " OR ";
                    } elseif ($conditions['set'] == 'all') {
                        $operator = " AND ";
                    }
                    $falseCondition = "";
                    if ($conditions['set_value'] == 0) {
                        $falseCondition = " ! ( ";
                    }
                    $query .= $braces;
                    if ($key != 1)
                        $query .= $operator;
                    $query .= $falseCondition;
                    $queryRes = $this->setQueryConditionString($condition, $tables,$arrayKey);
                    $query .= $queryRes['sql'];
                    $tables  = $queryRes['tables'];
                    $orderBy = $queryRes['orderBy'];
                    if ($conditions['set_value'] == 0) {
                        $query .= " ) ";
                    }
                    $braces = "";
					$key++;
                }
            }
        }

   return array(
            'condition' => $query,
            'tables' => $tables,
            'orderBy' => $orderBy
        );
    }
    //Set Query String based on conditions
    private function setQueryConditionString($condition, $tables,$arrayKey='conditions') {
		if($arrayKey=='conditions'){
			$arrayKey='condition';
		}else{
			$arrayKey='bonus';
		}
	
        $sql = '';
        if 	(	(isset($condition['valueTo']) && $condition['valueTo'] != '') ||
				(isset($condition['valueFrom']) && $condition['valueFrom'] != '') ||
				(isset($condition['valueNum']) && $condition['valueNum'] != '') ||
				(isset($condition['value']) && $condition['value'] != '') || 
				(isset($condition['value_select']) && $condition['value_select'] !='') || 
				(isset($condition['orderby']) && $condition['orderby'] != '')
			) 
		{
            if ($condition[$arrayKey] == 'lastpurchase') {
			
				$sql	= " usrProf.last_order_date >= '".$condition['valueFrom']."' AND usrProf.last_order_date <= '".$condition['valueTo']."'" ;	
                $tables['order_masters'] = 1;
            } elseif ($condition[$arrayKey] == 'purchasevalue') {
                $sql	= " usrProf.last_order_amount " . $this->operatorEvaluation($condition['operator'], $condition['value']);
                $tables['order_masters'] = 1;
            }
			elseif ($condition[$arrayKey] == 'totalquantity') {
				$sql	= " usrProf.total_order_qty " . $this->operatorEvaluation($condition['operator'], $condition['value']);	 
            }elseif ($condition[$arrayKey] == 'totalorder') {
				 $sql	= " usrProf.total_order " . $this->operatorEvaluation($condition['operator'], $condition['value']);	
            }elseif ($condition[$arrayKey] == 'lastvisit') {
               /*  $sql                 = " product.season_sell_start_date >= '".$condition['valueFrom']. "' AND product.season_sell_end_date <= '".$condition['valueTo']."'" ;  */
            }elseif ($condition[$arrayKey] == 'usergroup') {
                $sql	= " usrProf.userID>0 AND usrProf.userGroupID " . $this->operatorEvaluation($condition['operator'], $condition['value_select']);
            }elseif ($condition[$arrayKey] == 'country') {
				$trim ='"';
                $sql	= " usrProf.last_shipping_country " . $this->operatorEvaluation($condition['operator'], $condition['value_select']);
            }elseif ($condition[$arrayKey] == 'state') {
				$trim ='"';
                $sql	= " usrProf.last_shipping_state " . $this->operatorEvaluation($condition['operator'], $condition['value_select']);
            } elseif ($condition[$arrayKey] == 'billingcountry') {
				$trim ='"';
                $sql	= " usrProf.last_billing_country " . $this->operatorEvaluation($condition['operator'], $condition['value_select']);	
                
            } elseif ($condition[$arrayKey] == 'billingstate') {
				$trim ='"';
                $sql	= " usrProf.last_billing_state " . $this->operatorEvaluation($condition['operator'], $condition['value_select']);
            } elseif ($condition[$arrayKey] == 'payment_method') {
				$payment_string  ="(SELECT paySetGroupID FROM " . DB_PREFIX . "payment_settings_group WHERE paySetGroupKey='". $condition['value_select']."')";
				if ($condition['operator'] == 'neq') {
					$result = " != " . $payment_string . "";
				} elseif ($condition['operator'] == 'eq') {
					$result = " = " . $payment_string . "";
				}				
                $sql 	= " usrProf.last_payment_gateway " . $result;
            } elseif ($condition[$arrayKey] == 'shipping_method') {
				if($condition['operator']=='eq'){
                $sql            = " usrProf.last_shipping_method IN(SELECT settingsGroupID FROM " . DB_PREFIX . "shipping_methods WHERE settingsGroupID='". $condition['value_select']."')";
				}else{
					$sql            = " usrProf.last_shipping_method NOT IN(SELECT settingsGroupID FROM " . DB_PREFIX . "shipping_methods WHERE settingsGroupParent='". $condition['value_select']."')";
				}
            }
			elseif ($condition[$arrayKey] == 'purchaseduration') {
				
				if($condition['value_select']=='7day'){
					$fromDate	= date('Y-m-d', strtotime('-7 days'));
					$toDate		= date('Y-m-d');
				}else{
					//$fromDate	= date("Y-m-d", strtotime("first day of previous month"));
					//$toDate		= date("Y-m-d", strtotime("first day of this month"));
					$fromDate	= date('Y-m-d', strtotime('-30 days'));
					$toDate		= date('Y-m-d');					
				}
				$sql	= " usrProf.last_order_date > '".$fromDate."' AND usrProf.last_order_date <= '".$toDate."'" ;	
                $tables['order_masters'] = 1;
            }			
        }
        return array(
            'sql' => $sql,
            'tables' => $tables,
            'orderBy' => isset($orderBy)?$orderBy:''
        );
    }
    //Operator Evaluation
    private function operatorEvaluation($op, $value) {
        $result = false;
        if ($op == 'neq') {
            $result = " != '" . $value . "'";
        } elseif ($op == 'eq') {
            $result = " = '" . $value . "'";
        } elseif ($op == 'lte') {
            $result = " <= '" . $value . "'";
            ;
        } elseif ($op == 'lt') {
            $result = " < '" . $value . "'";
        } elseif ($op == 'gte') {
            $result = " >= '" . $value . "'";
        } elseif ($op == 'gt') {
            $result = " > '" . $value . "'";
        } elseif ($op == 'cont') {
            $result = " LIKE '%" . $value . "%' ";
        } elseif ($op == 'ncont') {
            $result = " NOT LIKE '%" . $value . "%' ";
        } elseif ($op == 'in') {
            $conditionValues = explode(',', $value);
            $conditionValue  = "'" . implode("','", $conditionValues) . "'";
            $result          = " IN (" . $conditionValue . ") ";
        } elseif ($op == 'nin') {
            $conditionValues = explode(',', $value);
            $conditionValue  = "'" . implode("','", $conditionValues) . "'";
            $result          = " NOT IN (" . $conditionValue . ") ";
        }
        return $result;
    }
 	//UPDATE NEWSLETTER CONTENT
    public function updateNewsletterContent()
    {
            $sql                 = " UPDATE " . DB_PREFIX . "newsletters SET
									
									newsLetterContentHtml		= '" . $this->escape($_POST['newsLetterContentHtml'], FALSE) . "'								
							  WHERE
								newsletterID  =" .$_POST['newsletterID'];
            $resUpdateNewsletter = $this->ObjDatabase->setQuery($sql);      
    } 
    public function listCustomProducts($params = array()) {
        extract($params);
        $queryRes             = $this->generateProductQueryConditionString($conditions);
		$userGroupID		  = isset($userGroupID)?$userGroupID:0;
        $queryCondition       = ($queryRes['condition'] != '') ? $queryRes['condition'] . " ) " : '';
        $queryConditionTables = $queryRes['tables'];
        $orderBy              = $queryRes['orderBy'];
        $isFeatured           = $queryRes['isFeatured'];
        $args                 = array(
            'queryConditionTables' => &$queryConditionTables,
            'queryCondition' 	=> &$queryCondition,
            'orderBy' 			=> &$orderBy,
			'userGroupID' 		=> $userGroupID,
            'itemCount' 		=> &$itemCount,
            'isFeatured' 		=> &$isFeatured
        );
		 $sql  = $this->setQueryString($args);
		
        return $this->ObjDatabase->getFields($sql);
    }
    //Set Query String
    private function setQueryString($params) {
        extract($params);
        $userGroupID = isset($userGroupID) ? (int) $userGroupID : 0;
        $today       = date("Y-m-d");
        $sql         = "
				SELECT
					product.productID
				FROM
					" . DB_PREFIX . "products product
				INNER JOIN
					" . DB_PREFIX . "product_options productOpt
						ON (
							product.productID = productOpt.productID
							AND 
							productOpt.productOptionStatus = 'Active'
							AND 
							productOpt.productOptionDefault = 'Yes'
						)
				LEFT JOIN
					" . DB_PREFIX . "variant_offer_price AS varOffPri
						ON	(
							varOffPri.productOptionID = productOpt.productOptionID
							AND
							varOffPri.userGroupID='" . (int) $userGroupID . "'
						)	
				LEFT JOIN
					" . DB_PREFIX . "variant_group_price AS varGroPri
						ON	(
							varGroPri.productOptionID = productOpt.productOptionID
							AND
							varGroPri.userGroupID='" . (int) $userGroupID . "'
						)		
					";
        if (isset($queryConditionTables['productToCat']) && $queryConditionTables['productToCat'] == 1) {
            $sql .= "			
				LEFT JOIN
					" . DB_PREFIX . "product_to_category productToCat
					ON product.productID = productToCat.productID";
        }
        if (isset($queryConditionTables['business']) && $queryConditionTables['business'] == 1) {
            $sql .= "			
				INNER JOIN
					" . DB_PREFIX . "business bus
					ON product.businessID = bus.businessID
					AND bus.businessStatus = 'Active'";
        }
        if ((isset($queryConditionTables['productAttrVal']) && $queryConditionTables['productAttrVal'] == 1) || (isset($queryConditionTables['productAttrValDet']) && $queryConditionTables['productAttrValDet'] == 1)) {
            $sql .= "			
				INNER JOIN
					" . DB_PREFIX . "product_attribute_values productAttrVal
					ON product.productID = productAttrVal.productID";
            if (isset($queryConditionTables['productAttrValDet']) && $queryConditionTables['productAttrValDet'] == 1) {
                $sql .= "					
				LEFT JOIN
					" . DB_PREFIX . "product_attribute_value_details productAttrValDet
					ON productAttrVal.attrValueID = productAttrValDet.attrValueID";
            }
            $sql .= "					
				INNER JOIN
					" . DB_PREFIX . "attributes attr
					ON attr.attributeID = productAttrVal.attributeID
					AND attr.attrStatus = 'A'";
        }
        $sql .= "			
				WHERE
					product.productStatus = 'Active' 
					AND 
					( 
						product.productStartTime <= '" . $today . "'
						AND 
						( 
							product.productEndTime = '0000-00-00' 
							OR 
							product.productEndTime >=  '" . $today . "'
						) 
					)";
        if ($queryCondition != '') {
            $sql .= "
					AND
					" . $queryCondition;
        }
        if ($isFeatured != '') {
            $sql .= $isFeatured;
        }
        if (isset($itemCount) && $itemCount > 0) {
            $sql .= "
			GROUP BY 
				product.productID
			";
        }
        if (isset($orderBy) && $orderBy != '') {
            $sql .= "
					ORDER BY
					" . $orderBy;
        }
        $sql .= "
			LIMIT 
				" . $itemCount;
        return $sql;
    }
    private function generateProductQueryConditionString($conditions, $tables = '') {
        $orderBy    = "";
        $isFeatured = "";
		$query		= '';
		$key		= 1;
        if (!empty($conditions)) {
            $braces = " ( ";
            if (isset($conditions['conditions']) && !empty($conditions['conditions'])) {
                foreach ($conditions['conditions'] as  $condition) {
                    if ($conditions['set'] == 'any') {
                        $operator = " OR ";
                    } elseif ($conditions['set'] == 'all') {
                        $operator = " AND ";
                    }
                    $falseCondition = "";
                    if ($conditions['set_value'] == 0) {
                        $falseCondition = " ! ( ";
                    }
                    $query .= $braces;
                    if ($key != 1)
                        $query .= $operator;
                    $query .= $falseCondition;
                    $queryRes = $this->setProductQueryConditionString($condition, $tables);
                    $query .= $queryRes['sql'];
                    $tables  = $queryRes['tables'];
                    $orderBy = $queryRes['orderBy'];
                    if ($conditions['set_value'] == 0) {
                        $query .= " ) ";
                    }
                    $braces = "";
					$key++;
                }
            }
        }
        if (isset($conditions['isFeatured']) && $conditions['isFeatured'] == 'Yes') {
            $isFeatured .= " 	
				AND
				featuredStatus ='Yes'";
        }
        if (isset($conditions['orderBy']) && $conditions['orderBy'] != '') {
            switch ($conditions['orderBy']) {
                case 'product_id':
                    $orderBy = " product.productID";
                    break;
                 case 'discount':
                    $orderBy = " 
						ROUND(
							(
								(
									(
									productOpt.productOptionRegularPrice-
										LEAST(
											COALESCE(
												varOffPri.offerPrice,
												productOpt.productOptionProductPrice
											),
											COALESCE(
												varGroPri.specialPrice,
												productOpt.productOptionProductPrice
											) 
										)
									)*100
								)/productOpt.productOptionRegularPrice
							),2
						) DESC
					";
                    break;
                case 'best_seller':
                    $orderBy = " purchasePopularity DESC";
                    break;
            }
        }
        return array(
            'condition' => $query,
            'tables' => $tables,
            'orderBy' => $orderBy,
            'isFeatured' => $isFeatured
        );
    }
     //Set Query String based on conditions
    private function setProductQueryConditionString($condition, $tables) {
        $sql = '';
        if 	(	(isset($condition['valueTo']) && $condition['valueTo'] != '') ||
				(isset($condition['valueFrom']) && $condition['valueFrom'] != '') ||
				(isset($condition['valueNum']) && $condition['valueNum'] != '') ||
				(isset($condition['value']) && $condition['value'] != '') || 
				(isset($condition['value_select']) && $condition['value_select'] !='') || 
				(isset($condition['orderby']) && $condition['orderby'] != '')
			) 
		{
            if ($condition['condition'] == 'price') {
                $sql                  = " productOpt.productOptionProductPrice " . $this->operatorEvaluation($condition['operator'], $condition['value']);
                $tables['productOpt'] = 1;
            } elseif ($condition['condition'] == 'categories') {
                $sql	= " productToCat.productCategoryID " . $this->operatorEvaluation($condition['operator'], $condition['value']);
                $tables['productToCat'] = 1;
            }
			elseif ($condition['condition'] == 'seasonPeriod') {
                $sql	= " product.season_start_date >= '".$condition['valueFrom']."' AND product.season_end_date <= '".$condition['valueTo']."'" ; 
            }elseif ($condition['condition'] == 'sellingPeriod') {
                $sql                 = " product.season_sell_start_date >= '".$condition['valueFrom']. "' AND product.season_sell_end_date <= '".$condition['valueTo']."'" ; 
            }elseif ($condition['condition'] == 'players') {
                $sql	= " product.players_id " . $this->operatorEvaluation($condition['operator'], $condition['value']);
            }elseif ($condition['condition'] == 'sports') {
                $sql	= " product.sports_id " . $this->operatorEvaluation($condition['operator'], $condition['value']);
            }elseif ($condition['condition'] == 'compts') {
                $sql	= " product.comp_id " . $this->operatorEvaluation($condition['operator'], $condition['value']);
            } elseif ($condition['condition'] == 'brands') {
                $sql 	= " product.brandID " . $this->operatorEvaluation($condition['operator'], $condition['value']);
            } elseif ($condition['condition'] == 'teams') {
                $sql 	= " product.team_id " . $this->operatorEvaluation($condition['operator'], $condition['value']);
            }  elseif ($condition['condition'] == 'products') {
                $sql            = " product.productID " . $this->operatorEvaluation($condition['operator'], $condition['value']);
                $tables['product'] = 1;
            } elseif ($condition['condition'] == 'business') {
                $sql	= " bus.businessID " . $this->operatorEvaluation($condition['operator'], $condition['value']);
                $tables['business'] = 1;
            } elseif ($condition['condition'] == 'attributes') {
                if (isset($condition['value_select']) && $condition['value_select'] > 0) {
                    $sql                   = " (attr.attributeID = '" . $condition['condition_element'] . "' AND  productAttrVal.attr_option_id " . $this->operatorEvaluation($condition['operator'], $condition['value_select']) . ")";
                    $tables['productAttrVal'] = 1;
                } elseif ($condition['value'] != '') {
                    $sql                      = " (attr.attributeID = '" . $condition['condition_element'] . "' AND  productAttrValDet.attrDetValue " . $this->operatorEvaluation($condition['operator'], $condition['value']) . ")";
                    $tables['productAttrValDet'] = 1;
                }
            }
        }
        return array(
            'sql' => $sql,
            'tables' => $tables,
            'orderBy' => isset($orderBy)?$orderBy:''
        );
    }   
}