<?php

/* Zend Framework
 * @category   Zend
 * @package    Zend_Controller_Action
 * @copyright  Copyright (c) 2008-2014 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 * @version    1.0
 * @author     Ranvir singh <ranvir@catpl.co.in>
 * Create Date 06-09-2016
 * Update Date 06-09-2016
 * ************************************************************* */

class Marketing_Model_Marketing extends Zend_Db_Table_Abstract {

    private $db = "";

    function __construct() {
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

    public function __destruct() {
        $this->db->closeConnection();
    }

    public function insertTable($table, $data) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->insert($data);
    }

    public function updateTable($table, $editData, $where) {
        $dbtable = new Zend_Db_Table("$table");
        return $dbtable->update($editData, $where);
    }

    public function checkExistingEmailId($agencySysId, $emailId, $id) {
        $id = base64_decode($id);
        $checkExistingEmailIdByIdData = $this->checkExistingEmailIdById($agencySysId, $emailId);
        $resultArr = array();
        $resultDataArr = array();
        //        echo "<pre>";print_r($checkExistingEmailIdByIdData);die('here');
        if (!empty($checkExistingEmailIdByIdData)) {
            $matchlist = 0;
            $matchContact = 0;

            foreach ($checkExistingEmailIdByIdData as $dataKey => $dataValue) {

                if ($dataValue['EmailId'] == $emailId && $dataValue['ListSysId'] == $id) {

                    $matchlist++;
                } else if ($dataValue['EmailId'] == $emailId && $dataValue['ListSysId'] != $id) {
                    $matchContact++;
                    $resultDataArr = ['CustSysId' => $dataValue['CustSysId']];
                } else {
                    $resultArr = array();
                }
            }
            if ($matchlist > 0) {
                $resultArr = ['status' => 'true'];
                return $resultArr;
            } else if ($matchContact > 0) {
                $resultArr = ['status' => 'false', 'data' => $resultDataArr];
                return $resultArr;
            } else {
                return $resultArr;
            }
        } else {
            return $resultArr;
        }
    }

    public function checkExistingEmailIdById($agencySysId, $emailId) {
        $selectedvalues = array('CustSysId', 'AgencySysId', 'EmailId');
        $selectedvaluesMap = array('CustSysId as mapCustSysId', 'ListSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), $selectedvalues);
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId", $selectedvaluesMap);
        $select->where('tbl.EmailId = ?', $emailId);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingListData($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_List"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        // $select->where('tbl.IsActive = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->order('tbl.UpdateDate DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingListDataForCampaign($agencySysId) {

        $sql = "SELECT tbl.ListSysId,tbl.Title,tbl.AgencySysId,tbl.FromEmailId,tbl.FromEmailName FROM TB_Agency_Marketing_List AS tbl 
RIGHT JOIN TB_Agency_Marketing_Mapping_From_List_To_Contacts as tb2 on tbl.ListSysId = tb2.ListSysId AND tb2.IsActive = 1
WHERE (tbl.AgencySysId = $agencySysId) AND (tbl.IsActive = 1) AND (tbl.IsMarkForDelete = 0) 
Group by  tbl.ListSysId,tbl.Title,tbl.AgencySysId,tbl.FromEmailId,tbl.FromEmailName
ORDER BY tbl.ListSysId DESC";

        $result = $this->db->query($sql)->fetchAll();

        return $result;
    }

    public function GetAllMarkrtingListDataForCampaignForList($agencySysId) {

        $sql = "SELECT tbl.IsDefaultList,tbl.IsActive,tbl.ListSysId,tbl.Title,tbl.AgencySysId,tbl.FromEmailId,tbl.FromEmailName FROM TB_Agency_Marketing_List AS tbl 
RIGHT JOIN TB_Agency_Marketing_Mapping_From_List_To_Contacts as tb2 on tbl.ListSysId = tb2.ListSysId AND tb2.IsActive = 1
WHERE (tbl.AgencySysId = $agencySysId AND tbl.IsMarkForDelete = 0)
Group by  tbl.ListSysId,tbl.Title,tbl.AgencySysId,tbl.FromEmailId,tbl.FromEmailName,tbl.IsActive,tbl.IsDefaultList
ORDER BY tbl.ListSysId DESC";

        $result = $this->db->query($sql)->fetchAll();

        return $result;
    }

    public function GetAllMarkrtingListDataOnlyActive($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_List"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsActive = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->order('tbl.UpdateDate DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingContactList($agencySysId) {
        $selectedvaluesMap = array('CustSysId as mapCustSysId', 'ListSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), array());
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId AND tb2.AgencySysId = $agencySysId ", $selectedvaluesMap);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingContact($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        if (isset($this->searchContact) && !empty($this->searchContact)) {
            if (isset($this->searchContact['searchContactEmail']) && !empty($this->searchContact['searchContactEmail'])) {
                $Email = $this->searchContact['searchContactEmail'];
                $select->where('tbl.EmailId LIKE ?', "%$Email%");
            }
        }
        if (isset($this->searchContact) && !empty($this->searchContact)) {
            if (isset($this->searchContact['searchContactPhone']) && !empty($this->searchContact['searchContactPhone'])) {
                $MobileNo = $this->searchContact['searchContactPhone'];
                $select->where('tbl.MobileNo = ?', "$MobileNo");
            }
        }
        return $select;
    }

    public function GetAllMarkrtingContactListCount($agencySysId) {
        $selectedvaluesMap = array('count(tb2.CustSysId) as mapCustSysId', 'ListSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), array());
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId AND tb2.AgencySysId = $agencySysId ", $selectedvaluesMap);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->group('tb2.ListSysId');
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingContactListDataById($agencySysId, $listid) {
        $selectedvaluesMap = array('CustSysId as mapCustSysId', 'ListSysId', 'Issubscribe');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId", $selectedvaluesMap);
        //$select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tbl.EmailId = tb3.EmailId AND tbl.MobileNo = tb3.Contacts AND  tb3.AgencySysId = $agencySysId AND tb3.IsMarkForDelete = '0'  AND tb3.IsActive = '1'", array('DOB', 'MarriageAnniversary', 'CustomerSysId'));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tbl.EmailId = tb3.EmailId AND  tb3.AgencySysId = $agencySysId AND tb3.IsMarkForDelete = '0'  AND tb3.IsActive = '1'", array('DOB', 'MarriageAnniversary', 'CustomerSysId'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tb2.ListSysId = ?', $listid);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        if (isset($this->searchContact) && !empty($this->searchContact)) {
            if (isset($this->searchContact['searchContactEmail']) && !empty($this->searchContact['searchContactEmail'])) {
                $Email = $this->searchContact['searchContactEmail'];
                $select->where('tbl.EmailId LIKE ?', "%$Email%");
            }
        }
        if (isset($this->searchContact) && !empty($this->searchContact)) {
            if (isset($this->searchContact['searchContactPhone']) && !empty($this->searchContact['searchContactPhone'])) {
                $MobileNo = $this->searchContact['searchContactPhone'];
                $select->where('tbl.MobileNo LIKE ?', "%$MobileNo%");
            }
        }

        // $select->order('tb2.MapSysId DESC');
        // echo $select; exit;
        // $result = $this->db->fetchAll($select);
        //echo "<pre>";print_r($result);die;
        return $select;
    }

    public function GetAllSubscribedContactListDataById($agencySysId, $listid) {
        $selectedvaluesMap = array('CustSysId as mapCustSysId', 'ListSysId', 'Issubscribe');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId", $selectedvaluesMap);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tb2.ListSysId = ?', $listid);
        $select->where('tb2.Issubscribe = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllSubscribedContactListDataForWhatsApp($agencySysId, $listid) {
        $selectedvaluesMap = array('');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), array('FirstName', 'ContCode', 'MobileNo'));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId", $selectedvaluesMap);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tb2.ListSysId = ?', $listid);
        $select->where("tbl.ContCode != '' ");
        $select->where('tb2.Issubscribe = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllSubscribedContactListDataByIdCount($agencySysId, $listid) {
        $selectedvaluesMap = array('CustSysId as mapCustSysId', 'ListSysId', 'Issubscribe');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), array('count(tbl.CustSysId) as TotalCount'));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), "tb2.CustSysId = tbl.CustSysId", array(''));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tb2.ListSysId = ?', $listid);
        $select->where('tb2.Issubscribe = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return (int) $result[0]['TotalCount'];
    }

    public function GetAllMarkrtingListDataByLeadId($agencySysId, $listId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_List"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.ListSysId = ?', $listId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function GetCountAllContactById($agencySysId, $listId) {
        $sql = "SELECT count(tb1.CustSysId) as rows from TB_Agency_Marketing_Mapping_From_List_To_Contacts as tb1 INNER JOIN TB_Agency_Marketing_Contacts as tb2 ON tb1.CustSysId = tb2.CustSysId AND tb2.IsMarkForDelete = '0' where tb1.ListSysId = $listId AND tb1.AgencySysId = $agencySysId ";
        return $this->db->query($sql)->fetchAll();
    }

    public function GetAllMarkrtingWhatsAppCampaignData($agencySysId, $campaignSysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_WhatsApp_Campaign"), array('Title', 'Recipients', 'IsActive'));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_WhatsApp_Campaign_User"), "tbl.CampaignSysId = tb2.CampaignSysId", array('WhatsAppStatus', "count(WhatsAppStatus) as TotalCount"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campaignSysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->group(array('tbl.Title', 'tbl.Recipients', 'tb2.WhatsAppStatus', 'tbl.IsActive'));
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingCampaignData($agencySysId, $campaignSysId = NULL) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Template"), "tbl.TemplateSysId = tb2.TemplateSysId", array('TemplateName', 'TemplateCategoryId', 'TemplateSubject', 'IsMarkForDelete as TemplateIsMarkForDelete'));
        $select->joinLeft(array('tb3' => "TB_Agency_Marketing_List"), "tbl.ListSysId = tb3.ListSysId", array('Title as listTitle'));

        $select->where('tbl.AgencySysId = ?', $agencySysId);
        if ($campaignSysId != NULL) {
            $select->where('tbl.CampaignSysId = ?', $campaignSysId);
        }
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.CreatedDate DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingCampaignDataCheckLaunch($agencySysId, $campId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);
        $select->where('tbl.CampaignlaunchStatus = ?', 1);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.CreatedDate DESC');

        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAllTemplatreDataById($agencySysId, $TemplateSysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Template"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.TemplateSysId = ?', $TemplateSysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getListIdByTitleName($agencySysId, $Title) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_List"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.Title LIKE ?', '%' . trim($Title) . '%');
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAddress($agencySysId) {
        $column = array("Address", "Pincode", "CitySysId", "CountrySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Addresses"), $column);
        $select->where('tb1.AgencySysId = ?', $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getTotalEmail($agencySysId) {
        $column = array("TotalEmail", "PerEmailPrice");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Marketing_Wallet"), $column);
        $select->where('tb1.AgencySysId = ?', $agencySysId);
        $select->where('tb1.IsActive = ?', 1);
        $select->where('tb1.IsMarkForDel = ?', 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getTotalEmailUpdate($agencySysId, $totalEmail) {
        $editData = array('TotalEmail' => new Zend_Db_Expr("TotalEmail - $totalEmail"));
        $where = array('AgencySysId =?' => $agencySysId);
        $this->updateTable("TB_Agency_Marketing_Wallet", $editData, $where);
    }

    public function getAllTemplateData($agencySysId) {
        $select = $this->db->select();


        $select->from(array('tbl' => "TB_Agency_Marketing_Template"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.UpdateDate DESC');
        if (isset($this->searchArr) && !empty($this->searchArr)) {
            if (isset($this->searchArr['searchTemplateName']) && !empty($this->searchArr['searchTemplateName'])) {
                $TemplateName = $this->searchArr['searchTemplateName'];
                $select->where('tbl.TemplateName LIKE ?', "%$TemplateName%");
            }
        }
        //$result = $this->db->fetchAll($select);

        return $select;
    }

    public function getTemplateDataByTempId($agencySysId, $tempId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Template"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.TemplateSysId = ?', $tempId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchRow($select);
        // $result = $this->db->fetchAll($select);
        // echo "<pre>"; print_r($result); die("modelm");
        return $result;
    }

    public function getEmailTrackStatusFromCampUserData($agencySysId, $campId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign_User"), ['EmailTackCode']);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);

        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getEmailTrackStatusFromCampData($agencySysId, $campId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), ['EmailTackCode']);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getEmailTrackCampUserDataForUpdateCamp($agencySysId, $campId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign_User"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);

        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsAppUserInCampaingn($agencySysId, $campId, $statusSysId = NULL) {
        $selectedvaluesMap = array('FirstName', 'LastName', 'EmailId', 'CustSysId');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_WhatsApp_Campaign_User"), array('CustomerSysId', 'WhatsAppTackCode', 'WhatsAppStatus'));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Contacts"), "tb2.CustSysId = tbl.CustSysId", $selectedvaluesMap);
        if (!empty($statusSysId)) {
            $select->where('tbl.WhatsAppStatus = ?', $statusSysId);
        }
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);
        $select->where("tbl.IsMarkForDelete = '0'");
        return $select;
    }

    public function getUserInCampaingn($agencySysId, $campId, $statusSysId = NULL) {
        $selectedvaluesMap = array('EmailTackStatus', 'EmailStatus');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Campaign_User"), "tb2.CustomerSysId = tbl.CustSysId", $selectedvaluesMap);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tb2.CampaignSysId = ?', $campId);
        $select->where("tbl.IsMarkForDelete = '0'");
        if ($statusSysId) {
            if ($statusSysId == 'UmVjaXBpZW50cw==') {
                
            } else if ($statusSysId == 'UGVuZGluZw==') {
                $select->where("tb2.EmailStatus = ''");
            } else if ($statusSysId == 'RGVsaXZlcmVk') {
                $select->where("tb2.EmailStatus IN('Delivered','Opened','Clicked','Unsubscribed')");
            } else if ($statusSysId == 'T3BlbmVk') {
                $select->where("tb2.EmailStatus IN('Opened')");
            } else {
                $select->where('tb2.EmailStatus = ?', base64_decode($statusSysId));
            }
        }
        //echo $select; exit;
        //  $result = $this->db->fetchAll($select);
        return $select;
    }

    public function GetAllCampaignDataByCampaignId($agencySysId, $campId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), array('CampaignSysId', 'ListSysId', 'TemplateSysId', 'FromEmailId', 'FromEmailName', 'Title'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignSysId = ?', $campId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function updateCampaignUserWiseDataModel($emailId, $CampaignSysId, $agencySysId, $Delivered) {
        if (is_array($emailId)) {
            $finalEmail = array();
            $g = 0;
            foreach ($emailId as $valueEmail) {
                if (!is_object($valueEmail)) {
                    $finalEmail[$g] = $valueEmail;
                    $g++;
                }
            }
            $sql = "UPDATE TB_Agency_Marketing_Campaign_User SET EmailStatus = '$Delivered' WHERE CampaignSysId = $CampaignSysId AND AgencySysId = $agencySysId AND CustomerSysId IN (SELECT CustSysId FROM TB_Agency_Marketing_Contacts WHERE IsMarkForDelete = '0' AND IsActive = 1 AND AgencySysId = $agencySysId AND EmailId IN('" . implode("','", $finalEmail) . "'))";
            $sql = str_replace("''", "'", $sql);
            try {
                $this->db->query($sql);
            } catch (Zend_Exception $e) {
                $e->getMessage();
                //    $sql;
            }
        }
    }

    public function GetAllWhatsAppCampaignDataDefault($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_WhatsApp_Campaign"));
        $select->joinLeft(array('tb2' => "TB_Agency_WhatsAppEmail_Template"), "tbl.TemplateSysId = tb2.Id", array('TemplateName'));
        $select->joinLeft(array('tb3' => "TB_Agency_Marketing_List"), "tbl.ListSysId = tb3.ListSysId", array('Title as listTitle'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        if (isset($this->filterCampaign) && $this->filterCampaign != '') {
            $select->where('tbl.Title LIKE ?', "$this->filterCampaign%");
        }
        $select->where('tbl.IsMarkForDelete = ?', 0);
        // $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.CampaignSysId DESC');
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function GetAllMarkrtingCampaignDataDefault($isDefaultCampaign, $agencySysId = NULL) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Template"), "tbl.TemplateSysId = tb2.TemplateSysId", array('TemplateName', 'TemplateCategoryId', 'TemplateSubject', 'IsMarkForDelete as TemplateIsMarkForDelete'));
        $select->joinLeft(array('tb3' => "TB_Agency_Marketing_List"), "tbl.ListSysId = tb3.ListSysId", array('Title as listTitle'));
        $select->where('tbl.isDefaultCampaign = ?', $isDefaultCampaign);
        if ($agencySysId != NULL) {
            $select->where('tbl.AgencySysId = ?', $agencySysId);
        }

        if (isset($this->filterCampaign) && $this->filterCampaign != '') {
            $select->where('tbl.Title LIKE ?', "$this->filterCampaign%");
        }
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTemplateDataByTempIdDefault($tempId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Template"));
        $select->where('tbl.TemplateSysId = ?', $tempId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchRow($select);

        return $result;
    }

    public function getCampaignSysIdForAutoFollowUp($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), array('CampaignSysId'));
        $select->where('tbl.AgencySysId  = ?', $agencySysId);
        $select->where('tbl.CampaignlaunchStatus  = ?', 1);
        $select->where('tbl.IsDefaultCampaign   = ?', 1);
        $select->where('tbl.Title LIKE ?', "Auto%");
        $select->where('convert(date,tbl.CampaignRunDate) = ?', date('Y-m-d'));
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCampaignSysIdForAutoFollowUpAfter5Days($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), array('CampaignSysId'));
        $select->where('tbl.AgencySysId  = ?', $agencySysId);
        $select->where('tbl.CampaignlaunchStatus  = ?', 1);
        $select->where('tbl.IsDefaultCampaign   = ?', 1);
        $select->where('tbl.Title LIKE ?', "After5Days%");
        $select->where('convert(date,tbl.CampaignRunDate) = ?', date('Y-m-d'));
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getBirthCampaign($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), array('CampaignSysId'));
        $select->where('tbl.AgencySysId  = ?', $agencySysId);
        $select->where('tbl.CampaignlaunchStatus  = ?', 1);
        $select->where('tbl.IsDefaultCampaign   = ?', 1);
        $select->where('tbl.Title LIKE ?', "BIRTHDAY%");
        $select->where('convert(date,tbl.CampaignRunDate) = ?', date('Y-m-d'));
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAnniversaryCampaign($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"), array('CampaignSysId'));
        $select->where('tbl.AgencySysId  = ?', $agencySysId);
        $select->where('tbl.CampaignlaunchStatus  = ?', 1);
        $select->where('tbl.IsDefaultCampaign   = ?', 1);
        $select->where('tbl.Title LIKE ?', "ANNIVERSARY%");
        $select->where('convert(date,tbl.CampaignRunDate) = ?', date('Y-m-d'));
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function GetAllMarkrtingCampaignDataReport($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Campaign"));
        $select->joinLeft(array('tb2' => "TB_Agency_Marketing_Template"), "tbl.TemplateSysId = tb2.TemplateSysId", array('TemplateName', 'TemplateCategoryId', 'TemplateSubject', 'IsMarkForDelete as TemplateIsMarkForDelete'));
        $select->joinLeft(array('tb3' => "TB_Agency_Marketing_List"), "tbl.ListSysId = tb3.ListSysId", array('Title as listTitle'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CampaignlaunchStatus = ?', 1);
        $select->where('tbl.IsActive = ?', 1);
        $select->order('tbl.CampaignSysId DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function copyDataFromListToCampaign($AgencySysId, $ListSysId, $CampaignSysId) {
        $statement = $this->db->query("COPY_USER_MAPPING_DATA_FROM_LIST_TO_CAMPAIGN $AgencySysId,$ListSysId,$CampaignSysId");
    }

    public function createCSVFILE($AgencySysId, $CampaignSysId, $UserSysId, $campSysId) {
        //echo "CREATE_CSV_FOR_MARKETING $AgencySysId,$CampaignSysId,$UserSysId,$campSysId"; exit;
        $statement = $this->db->query("CREATE_CSV_FOR_MARKETING $AgencySysId,$CampaignSysId,$UserSysId,$campSysId");
    }

    public function getAllGTXLeadPageData($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_GTX_Lead_Page"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->order('tbl.GtxLeadSysId DESC');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAllGTXLeadPageDataIsDisplay($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_GTX_Lead_Page"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.isDisplay = ?', 1);
        $select->order('tbl.GtxLeadSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAllGTXLeadPageDataIsDisplayById($agencySysId, $GtxLeadSysId) {
        $arrLeadSource = array('Title as LeadSource');
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_GTX_Lead_Page"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tbl.LeadSourceSysId", $arrLeadSource);
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.GtxLeadSysId = ?', $GtxLeadSysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->order('tbl.GtxLeadSysId DESC');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCountAllGTXLeadIsDisplay($agencySysId) {
        $sql = "SELECT count(tb1.GtxLeadSysId) as rows from TB_Agency_GTX_Lead_Page as tb1 where tb1.AgencySysId = $agencySysId AND isDisplay = 1 ";

        return $this->db->query($sql)->fetchAll();
    }

    public function GetListContactListDataByCustSysId($agencySysId, $CustSysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_Marketing_Contacts"), array('*'));
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.EmailId = tb2.EmailId AND tbl.MobileNo = tb2.Contacts AND  tb2.AgencySysId = $agencySysId AND tb2.IsMarkForDelete = '0'  AND tb2.IsActive = '1'", array('DOB', 'MarriageAnniversary', 'CustomerSysId'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.CustSysId = ?', $CustSysId);
        $select->where('tbl.IsMarkForDelete = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSourceAgencyId($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Master_LeadSource"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $select->where('tbl.IsActive = ?', 1);
        $select->where('tbl.Title != ?', '');
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function createCSVFILEForScheduler($AgencySysId, $CampaignSysId, $UserSysId, $campSysId, $ScheduleDate, $templateSysId, $persanalized, $fromEmailId, $fromEmailName, $TemplateSubject, $getAllContactListData,$type) {

        if($type==2){
            $departuredateNew = DateTime::createFromFormat('d/m/Y H:i A', $ScheduleDate);
            $departuredateNew1 = $departuredateNew->format('Y-m-d-H');
        } else{
            $departuredateNew = DateTime::createFromFormat('d/m/Y H:i', $ScheduleDate);
            $departuredateNew1 = $departuredateNew->format('Y-m-d-H');
        }
        // $departuredateNew2 = $departuredateNew->format('H');
        $addline = array($ScheduleDate, $CampaignSysId, $campSysId, $AgencySysId, $templateSysId, $persanalized, $fromEmailId, $fromEmailName, $TemplateSubject, $getAllContactListData,$type);
        $fileName = $departuredateNew1 . '_scheduler.csv';
        $file = fopen($_SERVER['DOCUMENT_ROOT'] . "/public/upload/marketing/csv/" . $fileName, "a+");
        fputcsv($file, $addline);
        fclose($file);
    }

    public function getLeadFunnelList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Lead_Generation_Funnel"), array('*'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        return $select;
    }

    public function getLeadFunnelByid($agencySysId, $id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Lead_Generation_Funnel"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.Id = ?', $id);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getLeadFunnelDestination_CoveredCity($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Lead_Generation_Funnel_Destination_Covered"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.DestinationId", array('Title', 'Country'));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadFunnelDestination_CoveredState($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Lead_Generation_Funnel_Destination_Covered"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_State"), "tb2.StateId = tbl.DestinationId", array('Title'));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadFunnelDestination_Covered($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Lead_Generation_Funnel_Destination_Covered"));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSalesFunnelList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel"), array('*'));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.IsMarkForDel = ?', 0);
        return $select;
    }

    public function getSalesFunnelByid($agencySysId, $id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel"));
        $select->where('tbl.AgencySysId = ?', $agencySysId);
        $select->where('tbl.Id = ?', $id);
        $select->where('tbl.IsMarkForDel = ?', 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getSalesFunnelLead_Stage($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel_Lead_Stage"), array('LeadStageId'));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSalesFunnelDestination_CoveredCity($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel_Destination_Covered"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.DestinationId", array('Title', 'Country'));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSalesFunnelDestination_Covered($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel_Destination_Covered"));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSalesFunnelDestination_CoveredAirport($id) {
        $select = $this->db->select();
        $select->from(array('tbl' => "Tb_Agency_Marketing_Sales_Funnel_Destination_Covered"));
        $select->joinLeft(array('tb2' => "TB_Master_Airport"), "tbl.DestinationId = tb2.ISO", array('AirportCode', 'AirportTitle', 'CityId as AirportCityId', 'CountryCode', 'ISO'));
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb2.CityId = tb3.CityId", array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode', 'ContSysId'));
        $select->where('tbl.SalesFunnelSysId = ?', $id);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deleteSalesFunnelLead_Stage($id) {
        if ($id > 0) {
            $whereTemp = array('SalesFunnelSysId = ? ' => $id);
            $this->db->delete('Tb_Agency_Marketing_Sales_Funnel_Lead_Stage', $whereTemp);
        }
    }

    public function deleteSalesFunnelDestination_Covered($id) {
        if ($id > 0) {
            $whereTemp = array('SalesFunnelSysId = ? ' => $id);
            $this->db->delete('Tb_Agency_Marketing_Sales_Funnel_Destination_Covered', $whereTemp);
        }
    }

    public function deleteLeadFunnelDestination_Covered($id) {
        if ($id > 0) {
            $whereTemp = array('SalesFunnelSysId = ? ' => $id);
            $this->db->delete('Tb_Agency_Marketing_Lead_Generation_Funnel_Destination_Covered', $whereTemp);
        }
    }

}