<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :TblCRMCustomer.php
 * File Description :Manage to Customer Operation
 * Created By : Shobhit Srivastav 
 * Created Date: 03-Aug-2016
 */

class Travel_Model_CRM_AgencyLeadAccount {

    protected $_dbObj;
    protected $_crmcustObj;
    protected $_crmcustMemberObj;
    public $ReportingToSysId;

    public function __construct() {

        $this->_crmcustObj = new Travel_Model_TblCrmAgencyLeadAccount();
        $this->_crmcustMemberObj = new Travel_Model_TblAgencyCustomerMember();
        $this->_dbObj = Zend_Db_Table::getDefaultAdapter();
    }

    public function addCustomer(array $data) {
        $this->_crmcustObj->insert($data);
        $lastID = $this->_dbObj->lastInsertId();
        return $lastID;
    }

    public function UpdateCustomer(array $data, $where = NULL) {
        $this->_crmcustObj->update($data, $where);
    }

    public function ChkMobileNum($mobilenum) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"));
        $select->where('tbl.PrimaryContactNumber = ?', $mobilenum);
        $select->where("IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        $custID = $result[0]['CRMCustSysId'] ? $result[0]['CRMCustSysId'] : 0;
        return $custID;
    }

    public function ChkEmail($email) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"));
        $select->where('tbl.EmailId = ?', $email);
        $select->where("IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        return @$result[0]['CRMCustSysId'];
        //return $result;
    }

    public function GetCustomerDetailsByEmail($email) {
        $agenctcustomer = array('CountrySysId', 'StateOrZoneSysId', 'CitySysId', 'AgencySysId');
        $customer = array('CRMCustSysId', 'EmailId', 'Salutation', 'PrimaryContactNumber', 'FullName', 'FirstName', 'LastName', 'ActiveDate', 'IsApproved', 'IsActive', 'IsMarkForDelete');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"), $customer);
        //$select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.EmailId = tb2.EmailId" , $agenctcustomer);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CRMCustSysId = tb2.CRMCustSysId", $agenctcustomer);
        $select->where("tb2.EmailId = ?", $email);
        $select->where("tbl.IsActive = ?", 1);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function getCustomerList($agencysysID, $filterData = null) {
        $select = $this->_dbObj->select();
        //print_r($filterData);
        // $select->from("TB_CRM_CustomerAccount");
        $arrLeadSource = array('Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"));
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CRMCustSysId = tb2.CRMCustSysId");
        $select->joinLeft(array('tb3' => "TB_Master_LeadSource"), "tb3.LeadSourceSysId = tbl.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tbl.AssignUserSysId", $arrAgencyUser);
        $select->joinLeft(array('tb5' => "TB_Master_LeadStage"), "tbl.LeadStageSysId = tb5.LeadStageSysId", $arrLeadStage);

        if (!empty($filterData['phonenumber'])) {
            $select->where('tbl.PrimaryContactNumber LIKE ?', '%' . $filterData['phonenumber'] . '%');
        }
        if (!empty($filterData['customername'])) {
            $select->orWhere('lower(tbl.FirstName) LIKE ?', '%' . strtolower($filterData['customername']) . '%');
        }
        if (!empty($filterData['customername'])) {
            $select->orWhere('lower(tbl.LastName) LIKE ?', '%' . strtolower($filterData['customername']) . '%');
        }
        if (!empty($filterData['emailid'])) {
            $select->orWhere('tbl.EmailId LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        if (!empty($filterData['lead_source'])) {
            $select->orWhere('tbl.LeadSourceSysId = ?', $filterData['lead_source']);
        }

        $select->where("tbl.IsActive = ?", 1);
        $select->where("tb2.AgencySysId = ?", $agencysysID);
        $select->order("tbl.CRMCustSysId DESC");

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

    public function GetCustomerById($id) {
        $agencyUserColumn = array('UserSysId', 'EmailId as AgencyUserEmailId', 'FirstName as AgencyUserFirstName', 'LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_CRM_CustomerAccount"));
        $select->where("tb1.CRMCustSysId = ?", $id);
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb3.LeadStageSysId = tb1.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb1.AssignUserSysId = tb4.UserSysId", $agencyUserColumn);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    //query to insert remark data in db created by amit kumar dubey on 16 august 2016 at 1:09 PM
    public function addRemark(array $data) {
        $this->_crmcustObj->insert($data);
        $lastID = $this->_dbObj->lastInsertId();
        return $lastID;
    }

    public function ChkEmailMobileWithResponse($email = null, $mobile = null, $AgencySysId = null) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"));
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CRMCustSysId = tb2.CRMCustSysId");
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb2.CitySysId = tb3.CityId", array('Title as CityName'));
        $select->joinLeft(array('tb4' => "TB_Master_Geo_Country"), "tb2.CountrySysId = tb4.ContId", array('Title as CountryName'));
        if (isset($email) && !empty($email)) {
            $select->where('tbl.EmailId = ?', $email);
        }
        if (isset($mobile) && !empty($mobile)) {
            $select->where('tbl.PrimaryContactNumber = ?', $mobile);
        }
        $select->where("tb2.AgencySysId = ?", $AgencySysId);
        $select->where("tbl.IsActive = ?", 1);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
        //return $result;
    }

    public function GetCustomerWithDetails($id) {
        $agencyUserColumn = array('UserSysId', 'EmailId as AgencyUserEmailId', 'FirstName as AgencyUserFirstName', 'LastName as AgencyUserLastName');
        $agenctcustomer = array('CountrySysId', 'CustomerSysId', 'StateOrZoneSysId', 'CitySysId', 'AgencySysId', 'Relation', 'PassportNo', 'PassportExpiry', 'DOB', 'MarriageAnniversary');
        $customer = array('AgencyCRMLeadSysId', 'EmailId', 'Salutation', 'PrimaryContactNumber', 'FullName', 'FirstName', 'LastName', 'ActiveDate', 'IsApproved', 'IsActive', 'IsMarkForDelete', 'LeadSourceSysId');
        $arrLeadSource = array('tb4.Title as LeadSource');
        $arrLeadStage = array('tb5.Title as LeadStage');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_CRM_LeadAccount"), $customer);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.AgencyCRMLeadSysId = tb2.CRMCustSysId", $agenctcustomer);
        $select->joinLeft(array('tb3' => "TB_Agency_User"), "tbl.AssignUserSysId = tb3.UserSysId", $agencyUserColumn);
        $select->joinLeft(array('tb4' => "TB_Master_LeadSource"), "tb4.LeadSourceSysId = tbl.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb5' => "TB_Master_LeadStage"), "tb5.LeadStageSysId = tbl.LeadStageSysId", $arrLeadStage);
        $select->where("tbl.AgencyCRMLeadSysId = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetCustomerMembersById($agencySysID, $CRMCustSysId, $memberId = null) {
        $select = $this->_dbObj->select();
        $select->from('TB_Agency_Customer_Members');
        $select->where("TB_Agency_Customer_Members.CustomerSysId = ?", $CRMCustSysId);
        $select->where('TB_Agency_Customer_Members.AgencySysId = ?', $agencySysID);
        $memberId ? $select->where('TB_Agency_Customer_Members.MemberSysId = ?', $memberId) : '';
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAgencyMmbrById($id) {
        $agenctcustomer = array('CountrySysId', 'StateOrZoneSysId', 'Address', 'CitySysId', 'AgencySysId', 'CRMCustSysId', 'EmailId', 'Title', 'Contacts', 'FirstName', 'LastName', 'IsActive', 'IsMarkForDelete');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer"), $agenctcustomer);
        $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CRMCustSysId = tb2.CRMCustSysId", '');
        $select->where("tbl.CustomerSysId = ?", $id);
        //$select->where("tbl.IsActive = ?", 1);
        //echo $select;
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function UpdateCustomerMember(array $data, $where = NULL) {
        $this->_crmcustMemberObj->update($data, $where);
    }

    public function GetNameOfContStateCity($ContId, $StateId, $CityId) {
        $column = array('tb1.Title as countryName', 'tb2.Title as StateName', 'tb3.Title as CityName');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Master_Geo_Country"), $column);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_State"), "tb2.ContSysId = tb1.ContId", '');
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.ContSysId = tb1.ContId", '');
        $select->where("tb1.ContId = ?", $ContId);
        $select->where("tb2.StateId = ?", $StateId);
        $select->where("tb3.CityId = ?", $CityId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    //this function used for get agency details by id created by Amit kumar dubey on 20 sept 2016 at 12:19 PM
    public function GetAgencyDetailById($id) {
        //$thisagencyObj = new Travel_Model_TblAgency();
        $agency = array('AgencySysId', 'Title', 'Url', 'EmailIdForCustomer', 'Logo', 'AgencyPhoto', 'TrxCurrency', 'ServiceTaxNo', 'PanCardNo', 'AdharCardNo', 'UpdateDate', 'ContactInfo', 'CountrySysId', 'StateOrZoneSysId', 'CitySysId', 'Address', 'CIN');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency"), $agency);
        $select->where("tbl.AgencySysId = ?", $id);
        $select->where("tbl.IsActive = ?", 1);
        //echo $select;
        //$select->where("IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function getAllCustomerList($searchQuery) {
        $select = $this->_dbObj->select();
        $customerarray = array('CRMCustSysId', 'EmailId', 'FullName');
        $select->from(array('tbl' => "TB_CRM_CustomerAccount"), $customerarray);

        if (!empty($searchQuery)) {
            $select->orWhere('lower(tbl.FirstName) LIKE ?', '%' . strtolower($searchQuery) . '%');
        }
        if (!empty($searchQuery)) {
            $select->orWhere('lower(tbl.LastName) LIKE ?', '%' . strtolower($searchQuery) . '%');
        }
        if (!empty($searchQuery)) {
            $select->orWhere('tbl.EmailId LIKE ?', '%' . $searchQuery . '%');
        }
        $select->where("tbl.IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        $response = array();
        if (count($result) > 0) {
            foreach ($result as $row) {
                $response[] = array('CRMCustSysId' => $row['CRMCustSysId'], 'EmailId' => trim($row['FullName']) . "(" . trim($row['EmailId']) . ")", 'FullName' => trim($row['FullName']));
            }
        }
        return $response;
    }

    public function GetAllCustomerToDo($customerId) {
        $cuur_date = date('Y-m-d 00:00:00');
        $agencyUserColumn = array('UserSysId', 'EmailId as AgencyUserEmailId', 'FirstName as AgencyUserFirstName', 'LastName as AgencyUserLastName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
        $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CustSysId = tb2.CRMCustSysId");
        $select->joinLeft(array('tb3' => "TB_Agency_User"), "tbl.AssignToSysId = tb3.UserSysId", $agencyUserColumn);
        $select->where("tbl.CustSysId = ?", $customerId);
        $select->where("tbl.TPSysId = ?", 0);
        $select->where("tbl.EndDateTime > ?", $cuur_date);
        $select->order("tbl.ToDoSysId DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustomerToDoByID($customerId, $todoId) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
        $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CustSysId = tb2.CRMCustSysId");
        $select->where("tbl.CustSysId = ?", $customerId);
        $select->where("tbl.ToDoSysId = ?", $todoId);
        $select->order("tbl.ToDoSysId DESC");
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function getAllAgencyUserList($searchQuery, $agencyId) {
        $select = $this->_dbObj->select();
        $customerarray = array('UserSysId', 'EmailId', 'FirstName', 'LastName');
        $select->from(array('tbl' => "TB_Agency_User"), $customerarray);

        if (!empty($searchQuery)) {
            $select->orWhere('lower(tbl.FirstName) LIKE ?', '%' . strtolower($searchQuery) . '%');
        }
        if (!empty($searchQuery)) {
            $select->orWhere('lower(tbl.LastName) LIKE ?', '%' . strtolower($searchQuery) . '%');
        }
        if (!empty($searchQuery)) {
            $select->orWhere('tbl.EmailId LIKE ?', '%' . $searchQuery . '%');
        }
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.AgencySysId = ?", $agencyId);
        $result = $this->_dbObj->fetchAll($select);
        $response = array();
        if (count($result) > 0) {
            foreach ($result as $row) {
                $response[] = array('UserSysId' => $row['UserSysId'], 'EmailId' => trim($row['FirstName']) . " " . trim($row['FirstName']) . "(" . trim($row['EmailId']) . ")", 'FullName' => trim($row['FirstName']) . " " . trim($row['LastName']));
            }
        }
        return $response;
    }

    // Added By Pardeep Panchal


    public function getLeadSource() {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Master_LeadSource"));
        $select->where("IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function checkAndCreateLeadSource($strLeadSource) {   // for Master LeadSouce Creation
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Master_LeadSource"));
        $select->where("Title = ?", $strLeadSource);
        $select->where("IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        if (count($result) == 0) {
            $data = array(
                'Title' => $strLeadSource,
                'IsActive' => '1',
                'IsMarkForDel' => '0'
            );
            $this->_dbObj->insert("TB_Master_LeadSource", $data);
            $intLeadSouceId = $this->_dbObj->lastInsertId("TB_Master_LeadSource");
        }
        return $intLeadSouceId;
    }

    public function GetGtxCustomer($filterData = NULL) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb1.AssignUserSysId = ?", $filterData['UserSysId']);
        } else {
            $select->where("tb1.AssignUserSysId = ?", 0);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition


        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsApproved = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.ActiveDate DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function UpdateGtxCrmCustomer(array $data, $where = NULL) {
        $this->_crmcustObj->update($data, $where);
    }

    public function GetAgencyCustomerById($id) {
        $select = $this->_dbObj->select();
        $select->from('TB_Agency_Customer');
        $select->where("TB_Agency_Customer.CustomerSysId = ?", $id);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function getLeadSourceByAgency($AgencySysId = null) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Master_LeadSource"));
        if (isset($AgencySysId) && !empty($AgencySysId)) {
            $select->where("AgencySysId = ?", $AgencySysId);
        }
        $select->where("IsActive = ?", 1);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function ChkAgencyUser($custID) {
        $select = $this->_dbObj->select();
        $select->from("TB_Agency_Customer");
        $select->where('TB_Agency_Customer.CRMCustSysId = ?', $custID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCRMCustDetails($custID) {
        $select = $this->_dbObj->select();
        $select->from("TB_CRM_CustomerAccount");
        $select->where('TB_CRM_CustomerAccount.CRMCustSysId = ?', $custID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAllCustomerToDoByLeadId($customerId, $TPSysId) {
        $cuur_date = date('Y-m-d 00:00:00');
        $agencyUserColumn = array('UserSysId', 'EmailId as AgencyUserEmailId', 'FirstName as AgencyUserFirstName', 'LastName as AgencyUserLastName');
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
        $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CustSysId = tb2.CRMCustSysId");
        $select->joinLeft(array('tb3' => "TB_Agency_User"), "tbl.AssignToSysId = tb3.UserSysId", $agencyUserColumn);
        $select->where("tbl.CustSysId = ?", $customerId);
        $select->where("tbl.TPSysId = ?", $TPSysId);
        //$select->where("tbl.EndDateTime > ?", $cuur_date);
        $select->order("tbl.ToDoSysId DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    //this function used for display deleted/acrhieved customer
    public function GetArchieveGtxCustomer($filterData = null) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadSource);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition
        //$select->where("tb1.IsActive = ?", 1);
        //$select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 1);
        $select->order("tb1.UpdatedDate DESC");
        //$result = $this->_dbObj->fetchAll($select);
        return $select;
    }

    public function addAgencyLeadSource($agencyLeadSourceData = array(), $usersysid = 0) {
        $agencyLeadSourceDataForDB = array(
            'AgencySysId' => $agencyLeadSourceData['AgencySysId'],
            'Title' => $agencyLeadSourceData['Title'],
            'IsSetDefaultUserId' => isset($agencyLeadSourceData['IsSetDefaultUserId']) ? (int) $agencyLeadSourceData['IsSetDefaultUserId'] : 0,
            'URL' => '',
            'IsMarkForDel' => 0,
            'IsActive' => 1
        );
        try {
            $this->_dbObj->insert('TB_Master_LeadSource', $agencyLeadSourceDataForDB);
            $id = $this->_dbObj->lastInsertId('TB_Master_LeadSource');
            if ($usersysid > 0) {
                $agencyLeadSourceDataMapping = array(
                    'UserSysId' => $usersysid,
                    'LeadSourceSysId' => $id,
                    'IsMarkForDel' => 0,
                    'IsAssignLead' => 0
                );
                $this->_dbObj->insert('TB_Agency_User_LeadSource_Mapping', $agencyLeadSourceDataMapping);
            }
            return $id;
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function GetAgencyLeadCustomerById($id) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->where("tb1.AgencyCRMLeadSysId = ?", $id);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetAgencyCustomerByLeadIdB2B($id) {
        $select = $this->_dbObj->select();
        $select->from('TB_Agency_Customer');
        $select->where("TB_Agency_Customer.CustomerSysId = ?", $id);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetAgencyCustomerByLeadId($id) {
        $select = $this->_dbObj->select();
        $select->from('TB_Agency_Customer');
        $select->where("TB_Agency_Customer.AgencyCRMLeadSysId = ?", $id);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetGtxAssignedCustomer($filterData = NULL) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb1.AssignUserSysId = ?", $filterData['UserSysId']);
        } else {
            $select->where("tb1.AssignUserSysId > ?", 0);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition



        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.ActiveDate DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetGtxUnAssignedCustomer($filterData = NULL) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb1.AssignUserSysId = ?", $filterData['UserSysId']);
        } else {
            $select->where("tb1.AssignUserSysId = ?", 0);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition



        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.ActiveDate DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAgencyLeadAssignedToAgentArray($filterData = NULL) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb1.AssignUserSysId = ?", $filterData['UserSysId']);
        } else {
            $select->where("tb1.AssignUserSysId > ?", 0);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition



        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.ActiveDate DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAgencyLeadUnAssignedToAgentArray($filterData = NULL) {
        $select = $this->_dbObj->select();
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrLeadStage = array('tb3.Title as LeadStage');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb3' => "TB_Master_LeadStage"), "tb1.LeadStageSysId = tb3.LeadStageSysId", $arrLeadStage);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);

        //added filter condition
        if (!empty($filterData['lead_stage'])) {
            $select->Where('tb1.LeadStageSysId = ?', $filterData['lead_stage']);
        }
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("tb1.AgencySysId = ?", $filterData['AgencySysId']);
        }
        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb1.AssignUserSysId = ?", $filterData['UserSysId']);
        } else {
            $select->where("tb1.AssignUserSysId > ?", 0);
        }
        if (!empty($filterData['AgentSourceIds'])) {
            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
        }
        //end of filter condition



        $select->where("tb1.IsActive = ?", 1);
        //$select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.ActiveDate DESC");
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

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

    public function GetAgencyLeadAssignedToAgent($AgentSysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->where("tb1.AssignUserSysId = ?", $AgentSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetGtxCustomerApproved($filterData = NULL) {
        $checkALLData = isset($filterData['checkALLData']) ? $filterData['checkALLData'] : null;
        $agencySysId = $filterData['MainAgencySysId'];
        $displayAllLeadArray = array(2645, 33872, 24843);
        $select = $this->_dbObj->select();
        $arrLeadStage = array('TPStatus as LeadStage');
        $arrAgencyUser = array('tb4.FirstName as AgencyUserFirstName', 'tb4.LastName as AgencyUserLastName', 'tb4.EmailId as AgencyUserEmailId');
        $arrLeadSource = array('tb2.Title as LeadSource');
        $arrMediumSource = array('tb8.CampaignName as LeadMedium');
        $arCampaignSource = array('tb9.CampaignName as LeadCampaign');
        $b2bagencyColumn = array('Title as CompanyName', 'AgencyType AS IsB2BAgent');
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", $arrLeadSource);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", $arrAgencyUser);
        $select->joinLeft(array('tb7' => "TB_Master_TravelPlan_Status"), "tb1.LeadStageSysId = tb7.TPStatusSysId", $arrLeadStage);
        $select->joinLeft(array('tb8' => "TB_Agency_Medium_Master"), "tb8.MediumSysId = tb1.MediumSysId", $arrMediumSource);
        $select->joinLeft(array('tb9' => "TB_Agency_Campaign_Master"), "tb9.CampSysId = tb1.CampaignSysId", $arCampaignSource);
        $select->joinLeft(array('sub_agency' => "TB_Agency"), "sub_agency.AgencySysId = tb1.AgencySysId AND sub_agency.MasterAgencySysId = $agencySysId", $b2bagencyColumn);
        $ReportingToSysId = $filterData['UserSysId'] . (isset($this->ReportingToSysId) && !empty($this->ReportingToSysId) ? ',' . $this->ReportingToSysId : '');
        if (!empty($filterData['lead_source'])) {
            $select->Where('tb1.LeadSourceSysId = ?', $filterData['lead_source']);
        }
        if ((!empty($filterData['customeremail'])) || (!empty($filterData['customername'])) || (!empty($filterData['customermobile']))) {
            
        } else {
            if ($checkALLData != 'FullLead') {
                if (!empty($filterData['lead_stage'])) {
                    $select->Where('tb1.LeadStageSysId IN (?)', $filterData['lead_stage']);
                }
            }
        }
        if ($checkALLData != 'FullLead') {
            if (!empty($filterData['lead_stage_check'])) {
                $select->Where('tb1.LeadStageSysId IN (?)', $filterData['lead_stage_check']);
            }
        }
        if (!empty($filterData['customermobile'])) {
            $select->Where('tb1.PrimaryContactNumber = ?', $filterData['customermobile']);
        }
        if (!empty($filterData['customeremail'])) {
            $select->Where('tb1.EmailId = ?', $filterData['customeremail']);
        }
        if (!empty($filterData['customername'])) {
            $select->Where('lower(tb1.FirstName) LIKE ' . "'%" . strtolower($filterData['customername']) . "%' OR lower(tb1.LastName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.FullName) like '%" . strtolower($filterData['customername']) . "%' OR lower(tb1.PrimaryContactNumber) like '%" . strtolower($filterData['customername']) . "%'  ");
        }
        if (!empty($filterData['CityName'])) {
            $select->Where('lower(tb1.FromDestination) LIKE ' . "'%" . strtolower($filterData['CityName']) . "%' OR lower(tb1.Destination) like '%" . strtolower($filterData['CityName']) . "%'");
        }
        if (isset($filterData['lead_type']) && $filterData['lead_type'] != NULL) {
            $leadtype = (int) $filterData['lead_type'];
            if ($leadtype == 0) {
                $select->Where(("sub_agency.AgencyType = $leadtype OR AgencyType is null"));
            } else {
                $select->Where("sub_agency.AgencyType = $leadtype");
            }
        }
        if (!empty($filterData['UserSysId'])) {
            if (!empty($filterData['agencyUserFilter'])) {
                $select->Where("tb1.AssignUserSysId IN (?)", $filterData['agencyUserFilter']);
            } else {
                $select->Where("tb1.AssignUserSysId IN (?)", explode(',', $ReportingToSysId));
            }
        }
        if (isset($filterData['assigUnassignFilter']) && $filterData['assigUnassignFilter'] == 2) {
            $select->Where("tb1.AssignUserSysId = (?)", 0);
        }
        if (!empty($filterData['dateFrom']) && !empty($filterData['dateTo'] && $filterData['dateTo'] != '__-__-____' && $filterData['dateFrom'] != '__-__-____')) {
            $dateFromEx = explode('-', $filterData['dateFrom']);
            $dateToEx = explode('-', $filterData['dateTo']);
            $dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
            $dateTos = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
            $select->Where('CONVERT(date,tb1.CreatedDate) >= ?', $dateFrom);
            $select->Where('CONVERT(date,tb1.CreatedDate) <= ?', $dateTos);
        } else {
            if (in_array($filterData['MainAgencySysId'], $displayAllLeadArray)) {
                $date = strtotime(date("Y-m-d", strtotime("-24 month")));
                $befor30days = date('Y-m-d', $date);
                $select->Where('CONVERT(date,tb1.CreatedDate) >= ?', $befor30days);
                $select->Where('CONVERT(date,tb1.CreatedDate) <= ?', date("Y-m-d"));
            } else {
                $date = strtotime(date("Y-m-d", strtotime("-12 month")));
                $befor30days = date('Y-m-d', $date);
                $select->Where('CONVERT(date,tb1.CreatedDate) >= ?', $befor30days);
                $select->Where('CONVERT(date,tb1.CreatedDate) <= ?', date("Y-m-d"));
            }
        }
        $select->Where("(tb1.AgencySysId = $agencySysId OR sub_agency.AgencySysId IS NOT NULL)");
        if ($checkALLData != 'FullLead') {
            $select->where("tb1.IsActive = ?", 1);
            $select->where("tb1.IsMarkForDelete = ?", 0);
        }
        if (isset($filterData['limit']) && (int) $filterData['limit'] > 0) {
            $select->limit($filterData['limit']);
        }
        if (isset($filterData['order']) && $filterData['order'] != '') {
            $orderArray = explode("_", $filterData['order']);
            if (isset($orderArray[1]) && $orderArray[1] == 1) {
                $select->order("tb1.ActiveDate DESC");
            } else {
                $select->order("tb1.ActiveDate ASC");
            }
        } else {
            $select->order("tb1.ActiveDate DESC");
        }
        //echo $select;
        //exit;
        return $select;
    }

    public function getLeadAccount($AgencySysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Master_LeadSource"));
        $select->where("IsActive = ?", 1);
        $select->where("AgencySysId = ?", $AgencySysId);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function updateAgencyLeadAccount($agencyLeadAccountDataDb = array(), $AgencySysId = NULL) {
        $where = array('AgencySysId =?' => $AgencySysId);
        return $this->_dbObj->update('TB_Master_LeadSource', $agencyLeadAccountDataDb, $where);
    }
    public function GetGtxCustomerApprovedCount($filterData = NULL) {
        
       $agencySysId =  $filterData['AgencySysId'];
        
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('count(tb1.AgencyCRMLeadSysId) as countRow'));
        $select->joinLeft(array('tb2' => "TB_Master_LeadSource"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId", array());
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId",  array());
        $select->joinLeft(array('tb7' => "TB_Master_TravelPlan_Status"), "tb1.LeadStageSysId = tb7.TPStatusSysId",  array());
        $select->joinLeft(array('tb8' => "TB_Agency_Medium_Master"), "tb8.MediumSysId = tb1.MediumSysId",  array());
        $select->joinLeft(array('tb9' => "TB_Agency_Campaign_Master"), "tb9.CampSysId = tb1.CampaignSysId",  array());
        $select->joinLeft(array('sub_agency' => "TB_Agency"), "sub_agency.AgencySysId = tb1.AgencySysId AND sub_agency.MasterAgencySysId = $agencySysId",  array());
        
        
        if (!empty($filterData['AgencySysId'])) {
            $select->Where("(tb1.AgencySysId = $agencySysId OR sub_agency.AgencySysId IS NOT NULL)");
        }
        if (!empty($filterData['ReportingToSysId'])) {
            $select->Where("tb1.AssignUserSysId IN (?)", $filterData['ReportingToSysId']);
        }/* else{
          $select->where("tb1.AssignUserSysId = ?", 0);
          } */
//        if (!empty($filterData['AgentSourceIds'])) {
//            $select->Where("tb1.LeadSourceSysId IN(" . $filterData['AgentSourceIds'] . ")");
//        }
        //end of filter condition
        $select->Where('tb1.LeadStageSysId IN (?)', array(0,45, 46, 47, 48, 49, 51, 52, 53, 159, 161, 162, 163));

        $date = strtotime(date("Y-m-d", strtotime("-1 year")));
        $befor30days = date('Y-m-d', $date);
        $select->Where('CONVERT(date,tb1.CreatedDate) >= ?', $befor30days);
        $select->Where('CONVERT(date,tb1.CreatedDate) <= ?', date("Y-m-d", strtotime("+0 day")));

        //$select->where("tb3.AgencyCRMLeadSysId IS NULL");
        $select->where("tb1.IsActive = ?", 1);
        //$select->where("tb1.IsApproved = ?", 0);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        /*  echo $select;
          exit; */
        $result = $this->_dbObj->fetchRow($select);
        return $result['countRow'];
    }

    public function getAgencyCRMLeadAccount($id) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('AgencyCRMLeadSysId', 'PlanType', 'Noofdays As DaysCountFrom', 'Remarks AS leadRemark', 'startDate as StartDate', 'startDate as TStartDate', 'RoomInfoJson', 'FromDestination', 'ToFromDestinationSysId', 'Destination AS DestinationPlaces', 'LeadSourceSysId', 'ToDestinationSysId', 'SocialSysId', 'Purpose as PurposeLead', 'ExpectedClosureDate as ExpectedClosureDateLead', 'Preference as PreferenceLead', 'Inclusions as InclusionsLead', 'budget as budgetLead', 'NoOfAdult as NoOfAdultLead', 'NoOfChild as NoOfChildLead', 'TripType as TripTypeLead', 'EndDate as EndDateLead'));
        //                $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"),array('PlanType','Noofdays As DaysCountFrom','Remarks AS leadRemark','startDate as StartDate','RoomInfoJson','Destination AS DestinationPlaces'));
        $select->where("tb1.AgencyCRMLeadSysId = ?", $id);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function getUserDetailAgencyCRMLeadAccount($id) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array());
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.AssignUserSysId", array('FirstName', 'LastName'));
        $select->where("tb1.AgencyCRMLeadSysId = ?", $id);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function updateAgencyLeadData($agencyLeadAccountDataDb = array(), $where = 0) {
        // print_r($agencyLeadAccountDataDb);
        // print_r($where); 
        return $this->_dbObj->update('TB_Agency_CRM_LeadAccount', $agencyLeadAccountDataDb, $where);
    }

    public function checkLeadFromSoiceId($id) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('AgencyCRMLeadSysId'));
        $select->where("tb1.SocialSysId = ?", $id);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getMaxFaceBookId($AgencySysId, $FormSysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('MAX(SocialSysId) AS SocialSysId'));
        $select->where("tb1.SocialFormSysId = ?", $FormSysId);
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function checkMultiPleLeadFromSoiceId($ids, $FormSysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('SocialSysId'));
        $select->where("tb1.SocialSysId IN (?)", $ids);
        $select->where("tb1.SocialFormSysId = ?", $FormSysId);
        //echo $select; exit;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getFaceBookUserId($formId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Marketing_Facebook_Forms"), array());
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb2.UserSysId = tb1.AssignUserSysId AND tb2.AgencySysId = tb1.AgencySysId", array('FirstName', 'LastName', 'EmailId'));
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb2.AgencySysId = tb3.AgencySysId", array('IsAutoProposalOnFBLead', 'AgencySysId', 'DisplayName', 'EmailKey', 'Logo'));
        $select->where("tb1.FormID = ?", $formId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function updateCustomerVisaDetail($customerVisaData = array()) {

        try {
            $this->_dbObj->insert('TB_Agency_Customer_Visa', $customerVisaData);
            return $this->_dbObj->lastInsertId('TB_Agency_Customer_Visa');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function deleteCustomerVisaDetail($updateData = array(), $where = array()) {

        return $this->_dbObj->update('TB_Agency_Customer_Visa', $updateData, $where);
    }

    public function getCustomerVisaDetail($AgencySysId, $CustomerSysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Visa"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tb1.VisaCountry = tb2.ContId", array('Title as CountryName'));
        $select->where("tb1.CustomerSysId = ?", $CustomerSysId);
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function updateCustomerMemberVisaDetail($customerVisaData = array()) {

        try {
            $this->_dbObj->insert('TB_Agency_Customer_Member_Visa', $customerVisaData);
            return $this->_dbObj->lastInsertId('TB_Agency_Customer_Member_Visa');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function deleteCustomerMemberVisaDetail($updateData = array(), $where = array()) {

        return $this->_dbObj->update('TB_Agency_Customer_Member_Visa', $updateData, $where);
    }

    public function getCustomerMemberVisaDetail($AgencySysId, $CustomerSysId, $CustomerMemberSysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Member_Visa"));
        $select->joinLeft(array('tb2' => "TB_Master_Geo_Country"), "tb1.VisaCountry = tb2.ContId", array('Title as CountryName'));
        $select->where("tb1.CustomerSysId = ?", $CustomerSysId);
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.CustomerMemberSysId = ?", $CustomerMemberSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getAllB2bAgency($AgencySysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency"), array("AgencySysId"));
        $select->where("tb1.MasterAgencySysId = ?", $AgencySysId);
        $select->orWhere("tb1.AgencySysId = ?", $AgencySysId);
        $select->Where("tb1.IsActive = ?", 1);
        $select->Where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getFacebookRoundRobinArray($users, $companies) {
        $appts = array();
        foreach ($users as $user) {
            foreach ($companies as $company) {
                $appts[] = array($user, $company);
            }
        }
        return $appts;
    }

    public function updateFaceBookData($table, $data, $where) {
        $this->_dbObj->update($table, $data, $where);
    }

    public function GetAgencySysIdFromAgencyDestinationExpert($DestinationSysId) {
        $arrLeadSource = array("AgencySysId", "DestinationSysId");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Destination_Expert_Search_String"), $arrLeadSource);
        $select->where("tb1.DestinationSysId = ?", $DestinationSysId);
        $resultArray = $this->_dbObj->fetchAll($select);
        return $resultArray;
    }

    public function getFacebookUserIdFromRoundRobin($strLeadSource) {
        $arrLeadSource = array("UserSysId", "IsAssignLead", "LeadSourceSysId");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Marketing_Facebook_User_LeadSource_Mapping"), $arrLeadSource);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.UserSysId = tb2.UserSysId", array());
        $select->where("tb1.LeadSourceSysId = ?", $strLeadSource);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb2.IsMarkForDel = ?", 0);
        $select->order("tb1.IsAssignLead DESC");
        $resultArray = $this->_dbObj->fetchAll($select);
        $LeadSourceSysId = isset($resultArray[0]['LeadSourceSysId']) ? $resultArray[0]['LeadSourceSysId'] : 0;
        $alluser = $userArray = array();
        if (!empty($resultArray)) {
            foreach ($resultArray as $resultVal) {
                $alluser[] = $resultVal['UserSysId'];
                if ($resultVal['IsAssignLead'] == 0) {
                    $userArray[] = $resultVal['UserSysId'];
                }
            }
        }
        $userSysId = 0;
        if (count($alluser) > 1) {
            $appts = $this->getFacebookRoundRobinArray($userArray, array($LeadSourceSysId));
            $where = array('LeadSourceSysId = ? ' => $LeadSourceSysId);
            $updateData = array('IsAssignLead' => 0);
            $this->updateFaceBookData('TB_Agency_Marketing_Facebook_User_LeadSource_Mapping', $updateData, $where);
            foreach ($appts as $valSet) {
                $LeadSourceSysId = $valSet[1];
                $userSysId = $valSet[0];
                $where = array('LeadSourceSysId = ? ' => $LeadSourceSysId, 'UserSysId = ? ' => $userSysId);
                $updateData = array('IsAssignLead' => 1);
                $this->updateFaceBookData('TB_Agency_Marketing_Facebook_User_LeadSource_Mapping', $updateData, $where);
                break;
            }
        } else {
            $userSysId = $userArray[0];
        }
        return $userSysId;
    }

    public function getAgentIdFromLeadId($LeadId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('AssignUserSysId','B2BType','B2BAgencySysId'));
        $select->where("tb1.AgencyCRMLeadSysId = ?", $LeadId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function checkLeadCRMCUstomerByEmail($EmailId, $AgencySysId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array('AgencyCRMLeadSysId'));
        $select->where("tb1.AgencySysId = ?", $AgencySysId);
        $select->where("tb1.EmailId = ?", $EmailId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }
}