<?php

class Travel_Model_TblAgency {

    public $intAgencySysId = NULL;
    public $intId = NULL;
    public $intMPType = NULL;
    public $intCustGrSysId = NULL;
    public $intMarkupType = NULL;
    private $db = NULL;
    public $MasterAgencySysId;

    public function __construct() {
        //$request = Zend_Controller_Front::getInstance()->getRequest();
        //$this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

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

    public function getAgencyUserList($intLoggedinUserId) {
        $sql = "Select TB_Agency.AgencySysId, TB_Agency.TrxCurrency, TB_Master_Currency.Symbol, TB_Agency.MarkUpOnGTXNetworkPackage From TB_Agency JOIN TB_Master_Currency ON TB_Agency.TrxCurrency=TB_Master_Currency.CurrencyType where 1 = 1 ";

        if (!empty($intLoggedinUserId)) {
            $sql .= " AND TB_Agency.AgencySysId ='" . $intLoggedinUserId . "' ";
        }
        //echo $sql; die;

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

    public function deleteStandardMarkupSetting($AgencySysId, $MPType, $CustGrSysId) {
        $where = array('AgencySysId = ? ' => $AgencySysId, 'MPType = ? ' => $MPType, 'CustGrSysId = ? ' => $CustGrSysId, 'ItemSourceType = ? ' => 0);
        $this->db->delete('TB_MP_StdMarkup_Accom', $where);
    }

    public function getStandardMarkupAccomodation() {

        $sql = " SELECT AgencySysId, TB_MP_StdMarkup_Accom.MPType AS MPType_id,  TB_Master_MPType.Title AS MPType, CustGrSysId, Currency, MarkUpType, IsSameForAll, MarkUp, DOccupMarkUp,
                 SOccupMarkUp, ExtraBedMarkUp, ExtraWOBedMarkUp, PerPaxMarkUp, TB_MP_StdMarkup_Accom.Tax, ValidTill, ApproveDate, CreateDate
                 , UpdateDate, IsApproved, IsMarkForDel, IsActive 
                    FROM TB_MP_StdMarkup_Accom INNER JOIN TB_Master_MPType  
                        ON TB_MP_StdMarkup_Accom.MPType = TB_Master_MPType.MPType 
                    WHERE 1 = 1 AND ItemSourceType ='0' ";

        /*  $sql = " SELECT AgencySysId, TB_MP_Inventory_Accom_Alloc.MPType AS MPType_id, MPType, CustGrSysId, Currency, MarkUpType, IsSameForAll, MarkUp, DOccupMarkUp,
          SOccupMarkUp, ExtraBedMarkUp, ExtraWOBedMarkUp, PerPaxMarkUp, ValidTill, ApproveDate, CreateDate
          , UpdateDate, IsApproved, IsMarkForDel, IsActive
          FROM TB_MP_StdMarkup_Accom INNER JOIN TB_MP_Inventory_Accom_Alloc
          WHERE 1 = 1 ";
         */

        if (!empty($this->intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $this->intAgencySysId;
        }

        if (!empty($this->intMPType)) {
            $sql .= " AND TB_MP_StdMarkup_Accom.MPType = " . $this->intMPType;
        }

        if (!empty($this->CustGrSysId)) {
            $sql .= " AND CustGrSysId = " . $this->CustGrSysId;
        }

        //        if(!empty($this->intMarkupType)) {
        //            $sql .= " AND MarkUpType = " . $this->intMarkupType;
        //        }
        //echo $sql;exit;

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

    public function getAgentTxnCurrency($AgencySysId) {
        $sql = " SELECT TrxCurrency FROM TB_Agency WHERE AgencySysId = " . $AgencySysId;
        $result = $this->db->query($sql)->fetchAll();
        if (!empty($result)) {
            return $result[0]['TrxCurrency'];
        }
        return 0;
    }

    public function getMPStdMarkupAccom() {

        $sql = " SELECT AgencySysId, TB_MP_StdMarkup_Accom.MPType AS MPType_id,  TB_Master_MPType.Title AS MPType, CustGrSysId, Currency, MarkUpType, IsSameForAll, MarkUp, DOccupMarkUp,
                 SOccupMarkUp, ExtraBedMarkUp, ExtraWOBedMarkUp, PerPaxMarkUp, ValidTill, ApproveDate, CreateDate
                 , UpdateDate, IsApproved, IsMarkForDel, IsActive 
                    FROM TB_MP_StdMarkup_Accom INNER JOIN TB_Master_MPType  
                        ON TB_MP_StdMarkup_Accom.MPType = TB_Master_MPType.MPType 
                    WHERE 1 = 1 AND ItemSourceType='0' ";

        if (!empty($this->intAgencySysId)) {
            $sql .= " AND AgencySysId = " . $this->intAgencySysId;
        }

        if (!empty($this->intMPType)) {
            $sql .= " AND TB_MP_StdMarkup_Accom.MPType = " . $this->intMPType;
        }

        if (!empty($this->CustGrSysId)) {
            $sql .= " AND CustGrSysId = " . $this->CustGrSysId;
        }

        if (!empty($this->intMarkupType)) {
            $sql .= " AND MarkUpType = " . $this->intMarkupType;
        }

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

    public function isAgencyEmailExists($email) {
        //        $select = $this->db->select()
        //                ->from("TB_Agency", array('count(*) AS totalRecords'))
        //                ->where("PrimaryEmail =?", $email);
        $select = "Select count(*) from (
select PrimaryEmail as username from TB_Agency where PrimaryEmail = '$email' AND MasterAgencySysId = '0'
UNION
select EmailId as username from TB_Agency_User where EmailId = '$email' AND IsB2BAgent = '0'
) result";
        $count = $this->db->fetchOne($select);
        if ($count > 0) {
            return true;
        }
        return false;
    }

    public function getDataByEmail($email) {
        $select = $this->db->select()
                ->from("TB_Agency")
                ->where("PrimaryEmail =?", $email)
                ->where("IsActive =?", 1)
                ->where("IsMarkForDelete =?", 0)
                ->where("MasterAgencySysId = 0 OR AgencyType = 2");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    /**
     * registerAgency() method is used to add agency(add registration)
     * @param array
     * @return true 
     */
    public function updateAgencyDataNew($AgencySysId, $agencyData = array()) {

        if ($AgencySysId) {
            $agencyDataForDb = array(
                'MasterAgencySysId' => isset($agencyData['MasterAgencySysId']) ? $agencyData['MasterAgencySysId'] : 0,
                'AgencyType' => isset($agencyData['AgencyType']) ? $agencyData['AgencyType'] : 1,
                'Title' => $agencyData['agencyName'] ? $agencyData['agencyName'] : 0,
                'DisplayName' => $agencyData['DisplayName'] ? $agencyData['DisplayName'] : $agencyData['agencyName'],
                'NickId' => $agencyData['NickId'] ? $agencyData['NickId'] : 0,
                'AgencyShortDetail' => $agencyData['shortDetail'] ? $agencyData['shortDetail'] : 0,
                'ServiceTaxNo' => isset($agencyData['ServiceTaxNo']) ? $agencyData['ServiceTaxNo'] : '',
                'PanCardNo' => isset($agencyData['PanCardNo']) ? $agencyData['PanCardNo'] : '',
                'AdharCardNo' => isset($agencyData['AdharCardNo']) ? $agencyData['AdharCardNo'] : '',
                'PrimaryEmail' => $agencyData['emailId'] ? $agencyData['emailId'] : 0,
                'PrimaryContactName' => $agencyData['contactName'] ? $agencyData['contactName'] : 0,
                'ContactInfo' => $agencyData['mobileNo'] ? $agencyData['mobileNo'] : 0,
                'countrycode' => $agencyData['countrycode'] ? $agencyData['countrycode'] : 0,
                'Address' => isset($agencyData['address']) ? trim($agencyData['address']) : '',
                'CitySysId' => $agencyData['selectCity'] ? $agencyData['selectCity'] : 0,
                'StateOrZoneSysId' => $agencyData['selectState'] ? $agencyData['selectState'] : 0,
                'CountrySysId' => $agencyData['selectCountry'] ? $agencyData['selectCountry'] : 0,
                'CreateDate' => $agencyData['CreateDate'] ? $agencyData['CreateDate'] : 0,
                'IsVarified' => (isset($agencyData['IsVarified']) && !empty((int) $agencyData['IsVarified'])) ? (int) $agencyData['IsVarified'] : 0,
                'IsMobileVerified' => (isset($agencyData['IsMobileVerified']) && !empty((int) $agencyData['IsMobileVerified'])) ? (int) $agencyData['IsMobileVerified'] : 0,
                'IsActive' => isset($agencyData['IsActive']) ? $agencyData['IsActive'] : 0,
                'AgencyMarketPlaceSysId' => isset($agencyData['AgencyMarketPlaceSysId']) ? $agencyData['AgencyMarketPlaceSysId'] : 0,
                'IsMarkForDelete' => 0,
                'IsApproved' => isset($agencyData['IsApproved']) ? $agencyData['IsApproved'] : 1,
                'IsActiveSubscription' => isset($agencyData['IsActiveSubscription']) ? $agencyData['IsActiveSubscription'] : 0,
                //   'TrxCurrency' => isset($agencyData['CurrencySysId']) ? $agencyData['CurrencySysId'] : 0,
                'Url' => isset($agencyData['Url']) ? $agencyData['Url'] : '',
                'IsB2CSite' => isset($agencyData['IsB2CSite']) ? $agencyData['IsB2CSite'] : 0,
                'IsB2bSiteURL' => isset($agencyData['IsB2bSiteURL']) ? $agencyData['IsB2bSiteURL'] : '',
                'IsB2bSite' => isset($agencyData['IsB2bSite']) ? $agencyData['IsB2bSite'] : 0,
                'IsSSL' => isset($agencyData['IsSSL']) ? $agencyData['IsSSL'] : 0,
                'IsB2bSSL' => isset($agencyData['IsB2bSSL']) ? $agencyData['IsB2bSSL'] : '',
                'BusinessType' => isset($agencyData['BusinessType']) ? $agencyData['BusinessType'] : 0,
                'FirmType' => isset($agencyData['FirmType']) ? $agencyData['FirmType'] : 0,
                'RMUserSysId' => isset($agencyData['RMUserSysId']) ? $agencyData['RMUserSysId'] : 0,
                'IsPaid' => isset($agencyData['IsPaid']) ? $agencyData['IsPaid'] : 0,
                'DistributorId' => isset($agencyData['DistributorId']) ? $agencyData['DistributorId'] : null,
                'DistributorCommission' => isset($agencyData['DistributorCommission']) ? $agencyData['DistributorCommission'] : null,
            );
            $where = array('AgencySysId =?' => $AgencySysId);
            $this->db->update('TB_Agency', $agencyDataForDb, $where);
        }
    }

    public function registerAgency($agencyData = array(), $IsB2BAgency = NULL) {
        $value = 0;
        $agencyDataForDb = array(
            'MasterAgencySysId' => isset($agencyData['MasterAgencySysId']) ? $agencyData['MasterAgencySysId'] : 0,
            'AgencyType' => isset($agencyData['AgencyType']) ? $agencyData['AgencyType'] : 1,
            'Title' => $agencyData['agencyName'] ? $agencyData['agencyName'] : 0,
            'DisplayName' => $agencyData['DisplayName'] ? $agencyData['DisplayName'] : $agencyData['agencyName'],
            'BusinessTypeMask' => 0,
            'TradeMask' => 0,
            'NickId' => $agencyData['NickId'] ? $agencyData['NickId'] : 0,
            'Logo' => '',
            'AgencyPhoto' => 0,
            'AgencyShortDetail' => $agencyData['shortDetail'] ? $agencyData['shortDetail'] : 0,
            'Url' => '',
            'ServiceTaxNo' => isset($agencyData['ServiceTaxNo']) ? $agencyData['ServiceTaxNo'] : '',
            'PanCardNo' => isset($agencyData['PanCardNo']) ? $agencyData['PanCardNo'] : '',
            'AdharCardNo' => isset($agencyData['AdharCardNo']) ? $agencyData['AdharCardNo'] : '',
            'CIN' => '',
            'ContactUsNo' => 0,
            'EmailIdForCustomer' => '',
            'PrimaryUserSysId' => 0,
            'PrimaryEmail' => $agencyData['emailId'] ? $agencyData['emailId'] : 0,
            'PrimaryContactName' => $agencyData['contactName'] ? $agencyData['contactName'] : 0,
            'ContactInfo' => $agencyData['mobileNo'] ? $agencyData['mobileNo'] : 0,
            'countrycode' => $agencyData['countrycode'] ? $agencyData['countrycode'] : 0,
            'Address' => isset($agencyData['address']) ? trim($agencyData['address']) : '',
            'CitySysId' => $agencyData['selectCity'] ? $agencyData['selectCity'] : 0,
            'StateOrZoneSysId' => $agencyData['selectState'] ? $agencyData['selectState'] : 0,
            'CountrySysId' => $agencyData['selectCountry'] ? $agencyData['selectCountry'] : 0,
            'CreateDate' => $agencyData['CreateDate'] ? $agencyData['CreateDate'] : 0,
            'UpdateDate' => 0,
            'ActivationDate' => 0,
            'ApprovalDate' => 0,
            'IsVarified' => (isset($agencyData['IsVarified']) && !empty((int) $agencyData['IsVarified'])) ? (int) $agencyData['IsVarified'] : 0,
            'IsMobileVerified' => (isset($agencyData['IsMobileVerified']) && !empty((int) $agencyData['IsMobileVerified'])) ? (int) $agencyData['IsMobileVerified'] : 0,
            'IsActive' => isset($agencyData['IsActive']) ? $agencyData['IsActive'] : 0,
            'AgencyMarketPlaceSysId' => isset($agencyData['AgencyMarketPlaceSysId']) ? $agencyData['AgencyMarketPlaceSysId'] : 0,
            'IsMarkForDelete' => 0,
            'IsApproved' => isset($agencyData['IsApproved']) ? $agencyData['IsApproved'] : 1,
            'IsActiveSubscription' => isset($agencyData['IsActiveSubscription']) ? $agencyData['IsActiveSubscription'] : 0,
            'TrxCurrency' => isset($agencyData['CurrencySysId']) ? $agencyData['CurrencySysId'] : 0,
            'Url' => isset($agencyData['Url']) ? $agencyData['Url'] : '',
            'IsB2CSite' => isset($agencyData['IsB2CSite']) ? $agencyData['IsB2CSite'] : 0,
            'IsB2bSiteURL' => isset($agencyData['IsB2bSiteURL']) ? $agencyData['IsB2bSiteURL'] : '',
            'IsB2bSite' => isset($agencyData['IsB2bSite']) ? $agencyData['IsB2bSite'] : 0,
            'IsSSL' => isset($agencyData['IsSSL']) ? $agencyData['IsSSL'] : 0,
            'IsB2bSSL' => isset($agencyData['IsB2bSSL']) ? $agencyData['IsB2bSSL'] : '',
            'BusinessType' => isset($agencyData['BusinessType']) ? $agencyData['BusinessType'] : 0,
            'FirmType' => isset($agencyData['FirmType']) ? $agencyData['FirmType'] : 0,
            'RMUserSysId' => isset($agencyData['RMUserSysId']) ? $agencyData['RMUserSysId'] : 0,
            'IsPaid' => isset($agencyData['IsPaid']) ? $agencyData['IsPaid'] : 0,
            'DistributorId' => isset($agencyData['DistributorId']) ? $agencyData['DistributorId'] : null,
            'DistributorCommission' => isset($agencyData['DistributorCommission']) ? $agencyData['DistributorCommission'] : 0,        
            );
        try {
            $this->db->insert('TB_Agency', $agencyDataForDb);
            $value = $this->db->lastInsertId('TB_Agency');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        if (isset($agencyData['AgencyType']) && $agencyData['AgencyType'] == 2) {
            $agencyMappingDataForDb = [
                'AgencySysId' => (int) $value,
                'IsSSL' => isset($agencyData['IsSSL']) ? $agencyData['IsSSL'] : 0,
                'URL' => isset($agencyData['Url']) ? $agencyData['Url'] : 0,
                'IsSiteType' => isset($agencyData['IsSiteType']) ? $agencyData['IsSiteType'] : 0,
                'IsMaster' => 1,
                'IsActive' => 1,
                'IsMarkForDel' => 0,
            ];
            $this->db->insert('TB_Agency_WebSite_Mapping', $agencyMappingDataForDb);
        }


        if ($IsB2BAgency != '') {
            return $value;
        } else {
            return 1;
        }
    }

    /**
     * addUserRole() method is used to add agency's user role
     * @param array
     * @return roleid 
     */
    public function addUserRole($userRoleData = array()) {

        $userRoleDataForDb = array(
            'AgencySysId' => $userRoleData['AgencySysId'],
            'Band' => 0,
            'RoleTitle' => $userRoleData['RoleTitle'] ? $userRoleData['RoleTitle'] : 0,
            'RoleDesc' => $userRoleData['RoleTitle'] ? $userRoleData['RoleTitle'] : 0,
            'ModuleMask' => 0,
            'IsAdmin' => $userRoleData['IsAdmin'] ? $userRoleData['IsAdmin'] : 0,
            'IsAbleToManageMember' => $userRoleData['IsAbleToManageMember'] ? $userRoleData['IsAbleToManageMember'] : 0,
            'IsSuperAdmin' => 0,
            'IsAbleToManageTask' => 0,
            'IsMarkForDel' => 0,
            'IsActive' => 1,
        );

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

    /**
     * addAgencyUser() method is used to add agency's user data
     * @param array
     * @return userid 
     */
    public function addAgencyUser($agencyUserData = array(), $agencyUserRole = NULL) {
        $agencyUserDataForDb = array(
            'AgencySysId' => $agencyUserData['AgencySysId'],
            'IsfromSM' => 0,
            'SMTypeId' => 0,
            'SMId' => 0,
            'UserId' => 0,
            'EmpId' => 0,
            'JoinDate' => $agencyUserData['CreateDate'] ? $agencyUserData['CreateDate'] : 0,
            'ExitDate' => 0,
            'UserName' => 0,
            'FirstName' => $agencyUserData['FirstName'] ? $agencyUserData['FirstName'] : '',
            'LastName' => $agencyUserData['LastName'] ? $agencyUserData['LastName'] : '',
            'UserRole' => $agencyUserData['userRole'] ? $agencyUserData['userRole'] : '',
            'UserAccessLevelType' => 0,
            'Password' => $agencyUserData['Password'] ? $agencyUserData['Password'] : '',
            'RandStr' => $agencyUserData['RandStr'] ? $agencyUserData['RandStr'] : '0',
            'Designation' => $agencyUserData['Designation'] ? $agencyUserData['Designation'] : '',
            'PasswordExpiryDate' => 0,
            'IsPswExpire' => 0,
            'EmailId' => $agencyUserData['EmailId'] ? $agencyUserData['EmailId'] : '',
            'LastLoginTime' => 0,
            'IsLogInNow' => 0,
            'CreatedByUserSysId' => 0,
            'RandomCode' => 0,
            'Gender' => 0,
            'UserNickName' => 0,
            'UserPicPath' => 0,
            'ContactNo1' => $agencyUserData['ContNo'] ? $agencyUserData['ContNo'] : '',
            'ContactNo2' => $agencyUserData['mobileNo'] ? $agencyUserData['mobileNo'] : '',
            'countrycode' => $agencyUserData['countrycode'] ? $agencyUserData['countrycode'] : '',
            'UserDetails' => 0,
            'MacIp' => 0,
            'UpdateDate' => 0,
            'RegisterDate' => $agencyUserData['CreateDate'] ? $agencyUserData['CreateDate'] : 0,
            'IsMarkForDel' => 0,
            'IsActive' => 1,
            'IsApproved' => 1
        );

        try {
            $this->db->insert('TB_Agency_User', $agencyUserDataForDb);
            $userId = $this->db->lastInsertId('TB_Agency_User');
            $agencyUserRoleArr = unserialize(AGENCY_USER_ROLE);
            if ($agencyUserRole == $agencyUserRoleArr[0]) {

                $primaryUserDataForAgency = array(
                    'EmailIdForCustomer' => $agencyUserData['EmailIdForCustomer'] ? $agencyUserData['EmailIdForCustomer'] : 0,
                    'PrimaryUserSysId' => $userId,
                    'PrimaryEmail' => $agencyUserData['EmailId'] ? $agencyUserData['EmailId'] : '',
                    'PrimaryContactName' => $agencyUserData['FirstName'] . ' ' . $agencyUserData['LastName'],
                    'PrimaryContactNo' => $agencyUserData['ContNo'] ? $agencyUserData['ContNo'] : '',
                    'PrimaryMobileNo' => $agencyUserData['mobileNo'] ? $agencyUserData['mobileNo'] : '',
                    'countrycode' => $agencyUserData['countrycode'] ? $agencyUserData['countrycode'] : '',
                    'UpdateDate' => $agencyUserData['CreateDate'] ? $agencyUserData['CreateDate'] : 0
                );

                $this->updateAgencyData($primaryUserDataForAgency, $agencyUserData['AgencySysId']);
            }
            return $userId;
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    /**
     * updateAgencyData() method is used to update agency's data
     * @param array
     * @return true 
     */
    public function updateAgencyData($agencyData = array(), $AgencySysId = NULL) {
        if ($AgencySysId) {
            $where = array('AgencySysId =?' => $AgencySysId);
            $this->db->update('TB_Agency', $agencyData, $where);
        }
    }

    public function updateLeadAgencyData($agencyData = array(), $AgencySysId = NULL) {
        $where = array('AgencyCRMLeadSysId =?' => $AgencySysId);
        $this->db->update('TB_Agency_CRM_LeadAccount', $agencyData, $where);
    }

    //
    //        public function getDataByAgencyId($AgencyId,$role) {
    //        $select = $this->db->select()
    //                ->from("TB_Agency_UserRoles")
    //                ->where("AgencySysId =?", $role)
    //                ->where("AgencySysId =?", $AgencyId);
    //        $result = $this->db->fetchRow($select);
    //        return $result;
    //    }

    public function addAgencyFile($agencyFileData = array()) {

        $agencyFileDataForDB = array(
            'AgencySysId' => $agencyFileData['AgencySysId'] ? $agencyFileData['AgencySysId'] : 0,
            'ParentDocSysId' => $agencyFileData['ParentDocSysId'] ? $agencyFileData['ParentDocSysId'] : 0,
            'Seq' => 0,
            'DocType' => @$agencyFileData['DocType'] ? @$agencyFileData['DocType'] : 0,
            'Title' => $agencyFileData['Title'] ? $agencyFileData['Title'] : 0,
            'SubTitle' => $agencyFileData['originalFileName'] ? $agencyFileData['originalFileName'] : 0,
            'FilesPath' => $agencyFileData['fileName'] ? $agencyFileData['fileName'] : 0,
            'Link' => 0,
            'Tags' => 0,
            'Writeup' => 0,
            'CreatorSysId' => 0,
            'CreateDate' => $agencyFileData['CreateDate'] ? $agencyFileData['CreateDate'] : '',
            'ModifyDate' => isset($agencyFileData['ModifyDate']) ? $agencyFileData['ModifyDate'] : '',
            'ApproveDate' => 0,
            'IsApproved' => 0,
            'IsToShow' => 0,
            'IsActive' => 0,
            'IsMarkForDel' => 0
        );

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

    public function updateAgencyFile($agencyData, $where) {
        if (!empty($agencyData) && !empty($where)) {
            $this->db->update('TB_Agency_Documents', $agencyData, $where);
        }
    }

    public function addAgencyAboutData($agencyAboutData = array()) {

        $agencyAboutDataForDB = array(
            'AgencySysId' => $agencyAboutData['AgencySysId'],
            'AboutUs' => $agencyAboutData['AboutUs'] ? $agencyAboutData['AboutUs'] : 0,
            'FacebookURL' => $agencyAboutData['FacebookURL'] ? $agencyAboutData['FacebookURL'] : '',
            'GoogleURL' => $agencyAboutData['GoogleURL'] ? $agencyAboutData['GoogleURL'] : '',
            'YouTubeURL' => $agencyAboutData['YouTubeURL'] ? $agencyAboutData['YouTubeURL'] : '',
            'TwitterURL' => $agencyAboutData['TwitterURL'] ? $agencyAboutData['TwitterURL'] : '',
            'InstagramURL' => $agencyAboutData['InstagramURL'] ? $agencyAboutData['InstagramURL'] : '',
            'PintrestURL' => $agencyAboutData['PintrestURL'] ? $agencyAboutData['PintrestURL'] : '',
            'FacebookFeedbackPostURL' => $agencyAboutData['FacebookFeedbackPostURL'] ? $agencyAboutData['FacebookFeedbackPostURL'] : '',
            'GoogleFeedbackPostURL' => $agencyAboutData['GoogleFeedbackPostURL'] ? $agencyAboutData['GoogleFeedbackPostURL'] : '',
            'CreateDate' => $agencyAboutData['CreateDate'] ? $agencyAboutData['CreateDate'] : 0,
            'UpdateDate' => 0,
            'IsActive' => 0,
            'IsMarkForDelete' => 0,
            'IsApproved' => 0
        );

        try {
            $select = $this->db->select()
                    ->from("TB_Agency_OtherDetails", array('count(*) AS totalRecords'))
                    ->where("AgencySysId =?", $agencyAboutData['AgencySysId']);
            $resultData = $this->db->fetchOne($select);
            //echo $resultData; die;
            if ($resultData == 1) {
                $where = array('AgencySysId =?' => $agencyAboutData['AgencySysId']);
                return $this->db->update('TB_Agency_OtherDetails', $agencyAboutDataForDB, $where);
            } else {
                $this->db->insert('TB_Agency_OtherDetails', $agencyAboutDataForDB);
                return $this->db->lastInsertId('TB_Agency_OtherDetails');
            }
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function addAgencyBankData($agencyBankData = array()) {

        $agencyBankDataForDB = array(
            'AgencySysId' => $agencyBankData['AgencySysId'],
            'EncryptionSalt' => 0,
            'BankName' => $agencyBankData['BankName'] ? $agencyBankData['BankName'] : 0,
            'AccountName' => $agencyBankData['accountHolderName'] ? $agencyBankData['accountHolderName'] : 0,
            'AccountNo' => $agencyBankData['accountNumber'] ? $agencyBankData['accountNumber'] : 0,
            'IFSCCode' => $agencyBankData['ifscCode'] ? $agencyBankData['ifscCode'] : 0,
            'AccountType' => $agencyBankData['accountType'] ? $agencyBankData['accountType'] : 0,
            'Branch' => $agencyBankData['branchName'] ? $agencyBankData['branchName'] : 0,
            'OtherInfo' => 0,
            'ContactInfo' => 0,
            'Address' => 0,
            'CitySysId' => $agencyBankData['selectCity3'] ? $agencyBankData['selectCity3'] : 0,
            'StateOrZoneSysId' => 0,
            'CountrySysId' => $agencyBankData['selectCountry3'] ? $agencyBankData['selectCountry3'] : 0,
            'CreateDate' => $agencyBankData['CreateDate'] ? $agencyBankData['CreateDate'] : 0,
            'CancelledChequeFile' => $agencyBankData['CancelledChequeFile'] ? $agencyBankData['CancelledChequeFile'] : 0,
            'UpdateDate' => 0,
            'ActivationDate' => 0,
            'ApprovalDate' => 0,
            'IsVarified' => 0,
            'IsActive' => 0,
            'IsMarkForDelete' => 0,
            'IsApproved' => 0
        );

        try {
            $select = $this->db->select()
                    ->from("TB_Agency_BankDetails", array('count(*) AS totalRecords'))
                    ->where("AgencySysId =?", $agencyBankData['AgencySysId']);
            $resultData = $this->db->fetchOne($select);
            //echo $resultData; die;
            if ($resultData == 1) {
                $where = array('AgencySysId =?' => $agencyBankData['AgencySysId']);
                return $this->db->update('TB_Agency_BankDetails', $agencyBankDataForDB, $where);
            } else {
                $this->db->insert('TB_Agency_BankDetails', $agencyBankDataForDB);
                return $this->db->lastInsertId('TB_Agency_BankDetails');
            }
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function addAgencyAddressData($agencyAddressData = array()) {
        $agencyAddressDataForDB = array(
            'AgencySysId' => $agencyAddressData['AgencySysId'] ? $agencyAddressData['AgencySysId'] : 0,
            'AddressType' => $agencyAddressData['AddressType'] ? $agencyAddressData['AddressType'] : 0,
            'ContactInfo' => 0,
            'CopyOfAddressProofFile' => $agencyAddressData['copyAddressProofFile'] ? $agencyAddressData['copyAddressProofFile'] : '',
            'Address' => $agencyAddressData['address'] ? $agencyAddressData['address'] : '',
            'CitySysId' => $agencyAddressData['selectCity'] ? $agencyAddressData['selectCity'] : '',
            'StateOrZoneSysId' => 0,
            'CountrySysId' => $agencyAddressData['selectCountry'] ? $agencyAddressData['selectCountry'] : '',
            'CreateDate' => $agencyAddressData['CreateDate'] ? $agencyAddressData['CreateDate'] : '',
            'Pincode' => $agencyAddressData['pinCode'] ? $agencyAddressData['pinCode'] : '',
            'UpdateDate' => $agencyAddressData['UpdateDate'] ? $agencyAddressData['UpdateDate'] : '',
            'ActivationDate' => 0,
            'ApprovalDate' => 0,
            'IsVarified' => isset($agencyAddressData['IsVarified']) ? $agencyAddressData['IsVarified'] : 0,
            'IsActive' => isset($agencyAddressData['IsActive']) ? $agencyAddressData['IsActive'] : 0,
            'IsMarkForDelete' => 0,
            'IsApproved' => isset($agencyAddressData['IsApproved']) ? $agencyAddressData['IsApproved'] : 0
        );

        try {
            $select = $this->db->select()
                    ->from("TB_Agency_Addresses", array('count(*) AS totalRecords'))
                    ->where("AddressType =?", $agencyAddressData['AddressType'])
                    ->where("AgencySysId =?", $agencyAddressData['AgencySysId']);
            $resultData = $this->db->fetchOne($select);
            //echo $resultData; die;
            if ($resultData == 1) {
                $where['AddressType = ?'] = $agencyAddressData['AddressType'];
                $where['AgencySysId = ?'] = $agencyAddressData['AgencySysId'];
                //$where = array('AgencySysId =?' => $agencyAddressData['AgencySysId']);
                return $this->db->update('TB_Agency_Addresses', $agencyAddressDataForDB, $where);
            } else {
                $this->db->insert('TB_Agency_Addresses', $agencyAddressDataForDB);
                return $this->db->lastInsertId('TB_Agency_Addresses');
            }
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function addAgencyUserDocument($agencyUserDocData = array()) {


        $agencyUserDocDataForDB = array(
            'UserSysId' => $agencyUserDocData['UserSysId'],
            'ParentDocSysId' => 0,
            'Seq' => 0,
            'DocType' => $agencyUserDocData['docType'] ? $agencyUserDocData['docType'] : 0,
            'Title' => $agencyUserDocData['Title'] ? $agencyUserDocData['Title'] : 0,
            'SubTitle' => $agencyUserDocData['Title'] ? $agencyUserDocData['Title'] : 0,
            'Info' => 0,
            'Link' => 0,
            'FilesWithTitle' => $agencyUserDocData['fileName'] ? $agencyUserDocData['fileName'] : 0,
            'Tags' => 0,
            'CreatorSysId' => 0,
            'IsToShow' => 0,
            'CreateDate' => $agencyUserDocData['CreateDate'] ? $agencyUserDocData['CreateDate'] : 0,
            'ModifyDate' => 0,
            'ApproveDate' => 0,
            'IsApproved' => 0,
            'IsActive' => 0,
            'IsMarkForDel' => 0
        );

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

    public function getUserDataByAgency($agencyId) {
        $select = $this->db->select()
                ->from(array("TBA" => "TB_Agency"))
                ->joinLeft(array('TBAU' => 'TB_Agency_User'), 'TBAU.AgencySysId=TBA.AgencySysId AND TBAU.UserSysId=TBA.PrimaryUserSysId', array("FirstName", "LastName", 'TBAU.SecondaryEmail', 'TBAU.ContactNo2', 'UserSysId'))
                ->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('ReportingToSysId'))
                ->joinleft(array("TBMC" => "TB_Master_Currency"), 'TBMC.CurrencyType=TBA.TrxCurrency', array('Symbol'))
                ->where("TBA.AgencySysId =?", $agencyId)
                ->where("TBA.IsMarkForDelete =?", 0)
                ->where("TBA.IsActive =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getUserDataByAgencyNew($agencyId) {
        $select = $this->db->select()
                ->from(array("TBA" => "TB_Agency"))
                ->joinLeft(array('TBAU' => 'TB_Agency_User'), 'TBAU.AgencySysId=TBA.AgencySysId AND TBAU.UserSysId=TBA.PrimaryUserSysId', array("FirstName", "LastName", 'TBAU.SecondaryEmail', 'TBAU.ContactNo2', 'UserSysId'))
                //            ->joinLeft(array('TBADU' => 'TB_Agency_DeptUserMap'), 'TBADU.UserSysId=TBAU.UserSysId', array('ReportingToSysId'))
                ->where("TBA.AgencySysId =?", $agencyId)
                ->where("TBA.IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getWhatsappAllowDataByAgency($agencyId) {
        $select = $this->db->select()
                ->from(array("TBA" => "TB_Agency"))
                ->joinLeft(array('TBM' => 'TB_Agency_Mapping'), 'TBA.AgencySysId=TBM.AgencySysId', array('IsWhatsApp', 'WhatsappUser'))
                ->where("TBA.AgencySysId =?", $agencyId)
                ->where("TBM.IsWhatsApp =?", 1)
                ->where("TBA.IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getExtraMarkupDetail($intLoggedinUserAgencySysId, $CurrencyType) {
        $sql = " SELECT * FROM TB_Agency_CurrencyExcPadding WHERE "
                . " AgencySysId = " . $intLoggedinUserAgencySysId . " AND CurrencyType = " . $CurrencyType;
        return $this->db->query($sql)->fetchAll();
    }

    public function validateAgent($UserSysId, $AgencySysId) {
        $sql = " SELECT TBA.IsFranchise,TBM.IsEnabledB2CWallet,TBAU.IsArchivedPer,TBM.IsWhatsApp,TBM.IsCallCenter,TBM.IsSanKash,TBM.IsHotelOnlyRates,TBM.TotalOnlyRatesHotels,TBM.TotalInventoryHotels,TBA.IsHotelInventory,TBA.TaxLabelId,TBA.IsAutoProposalOnFBLead,TBAU.IsAllowFlightBooking,TBA.IsCreditLimitActivate,TBA.IsCheckWallet,TBAU.IsMaskingOnEmailAndMobile,TBAU.IsFullMaskingEnabled, TBAU.ExtensionNo,TBA.AgencyTimeZone,TBA.AgencydateFormat,TBA.IsSiteType,TBA.IsGTXNetwork, TBA.IsTG,TBA.IsGTXNetworkWeb,TBAU.UserSysId, TBA.AgencySysId,SenderEmailSysId,IsSameEmailId, IsfromSM, SMTypeId, SMId, UserId, EmpId, TBAU.JoinDate, TBAU.ExitDate, UserName
                ,FirstName, LastName, UserRole, IsInsurance, IsDemo, UserAccessLevelType, PasswordExpiryDate, IsPswExpire
                ,EmailId, OtherEmail, SecondaryEmail, LastLoginTime, IsLogInNow, CreatedByUserSysId,ContactNo1, TAD.IsOps
                , TBAU.RandomCode, Gender, UserNickName, TBAU.IsMarkForDel, TBAU.IsActive, TBAU.IsApproved,Logo ,TBA.IsActive as AgencyIsActive,TBA.IsApproved as AgencyIsApproved,TBA.IsB2CSite, TBA.SecurityKey,TBAU.ShowAllWhatsAppData
                  FROM TB_Agency_User TBAU
                  LEFT JOIN TB_Agency TBA ON TBA.AgencySysId = TBAU.AgencySysId
                  LEFT JOIN TB_Agency_Mapping TBM ON TBA.AgencySysId = TBM.AgencySysId
                  LEFT JOIN TB_Agency_DeptUserMap TADUM ON TADUM.UserSysId = TBAU.UserSysId
                  LEFT JOIN TB_Agency_Dept TAD ON TAD.DeptSysId = TADUM.DeptSysId 
                  WHERE ( TBAU.UserSysId ='" . $UserSysId . "' AND TBAU.AgencySysId ='" . $AgencySysId . "' ) AND TBAU.IsMarkForDel = '0'";
        //echo $sql; exit;
        return $this->db->query($sql)->fetchAll();
    }

    public function getTrxCurrency($intAgencySysId) {
        $sql = " SELECT TrxCurrency FROM TB_Agency WHERE AgencySysId = " . $intAgencySysId;
        $result = $this->db->query($sql)->fetchAll();

        if (!empty($result)) {
            return $result[0]['TrxCurrency'];
        }

        return 0;
    }

    public function getAgencyGroup($intAgencySysId) {
        $sql = " SELECT GrSysId FROM TB_Agency_User_Group WHERE AgencySysId = " . $intAgencySysId;
        $result = $this->db->query($sql)->fetchAll();

        if (!empty($result)) {
            return $result[0]['GrSysId'];
        }

        return 0;
    }

    /**
     * Delete record
     * @param type $whereCondition
     * @param type $tableName
     * @return int
     */
    public function deleteRecord($whereCondition, $tableName) {
        try {
            $isDeleted = $this->db->update($tableName, array('IsActive' => 0), $whereCondition);
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return @$isDeleted ? $isDeleted : 0;
    }

    public function getPersonalDetailsByAgency($agencyId, $roleTitle = NULL) {
        //        $roleTitle = unserialize(AGENCY_USER_ROLE);
        $where = " TBA.AgencySysId = " . $agencyId;
        if ($roleTitle) {
            $where .= " AND TBAUR.RoleTitle = '" . $roleTitle . "'";
        }
        $selectPersonalInfo = $this->db->select()
                ->from(array("TBA" => "TB_Agency"), array('TBA.AgencySysId', 'TBA.EmailIdForCustomer'))
                ->joinLeft(array('TBAU' => 'TB_Agency_User'), 'TBAU.AgencySysId=TBA.AgencySysId', array('TBAU.countrycode', 'TBAU.UserSysId', 'TBAU.FirstName', 'TBAU.LastName', 'TBAU.ContactNo1', 'TBAU.ContactNo2', 'TBAU.EmailId', 'TBAU.UserRole', 'TBAU.Designation'))
                ->joinLeft(array('TBAUR' => 'TB_Agency_UserRoles'), 'TBAU.UserRole=TBAUR.RoleSysId', array('TBAUR.RoleTitle'))
                ->joinLeft(array('TBAUD' => 'TB_Agency_User_Documents'), 'TBAU.UserSysId=TBAUD.UserSysId', array('TBAUD.DocSysId', 'TBAUD.DocType', 'TBAUD.FilesWithTitle'))
                ->where($where);
        //                ->where("TBAUR.RoleTitle IN (?)", $roleTitle);
        //                ->where("TBAUR.RoleTitle =", $roleTitle);
        $resultPersonalInfo = $this->db->fetchRow($selectPersonalInfo);
        return $resultPersonalInfo;
    }

    public function getBankDetailsByAgency($agencyId) {
        $where = " TBABD.AgencySysId = " . $agencyId;
        $where .= " AND TBABD.IsMarkForDelete = 0";
        $selectCompBAnkDet = $this->db->select()

                // ->from(array("TBA" => "TB_Agency"), array('TBA.AgencySysId', 'TBA.AgencyType', 'TBA.EmailIdForCustomer', 'TBA.Title as companyName', 'TBA.FirmType', 'TBA.ServiceTaxNo', 'TBA.BusinessTypeMask', 'TBA.DisplayName', 'TBA.Url', 'TBA.Logo', 'TBA.PanCardNo', 'TBA.CIN'))
                ->from(array('TBABD' => 'TB_Agency_BankDetails'), array('TBABD.AccountName', 'TBABD.AccountNo', 'TBABD.IFSCCode', 'TBABD.AccountType', 'TBABD.BankName', 'TBABD.Branch', 'TBABD.CitySysId as BankCitySysId', 'TBABD.CountrySysId as BankCountrySysId', 'TBABD.CancelledChequeFile', 'TBABD.BankInfoSysId'))
                ->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBABD.CountrySysId', array('TBMGCO.Title as CountryName'))
                ->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBABD.CitySysId', array('TBMGC.Title as CityName'))
                ->where($where);
        $resultBankDetails = $this->db->fetchRow($selectCompBAnkDet);
        return $resultBankDetails;
    }

    public function getAllBankDetailsByAgency($agencyId) {
        $selectCompBAnkDet = $this->db->select()
                // ->from(array("TBA" => "TB_Agency"), array('TBA.AgencySysId', 'TBA.AgencyType', 'TBA.EmailIdForCustomer', 'TBA.Title as companyName', 'TBA.FirmType', 'TBA.ServiceTaxNo', 'TBA.BusinessTypeMask', 'TBA.DisplayName', 'TBA.Url', 'TBA.Logo', 'TBA.PanCardNo', 'TBA.CIN'))
                ->from(array('TBABD' => 'TB_Agency_BankDetails'), array('TBABD.AccountName', 'TBABD.AccountNo', 'TBABD.IFSCCode', 'TBABD.AccountType', 'TBABD.BankName', 'TBABD.Branch', 'TBABD.CitySysId as BankCitySysId', 'TBABD.CountrySysId as BankCountrySysId', 'TBABD.CancelledChequeFile', 'TBABD.BankInfoSysId', 'TBABD.IsMarkForDelete', 'TBABD.IsActive', 'TBABD.IsApproved'))
                ->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBABD.CountrySysId', array('TBMGCO.Title as CountryName'))
                ->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBABD.CitySysId', array('TBMGC.Title as CityName'))
                ->where("TBABD.IsMarkForDelete =?", 0)
                ->order("BankInfoSysId DESC")
                ->where("TBABD.AgencySysId =?", $agencyId);

        $resultBankDetails = $this->db->fetchAll($selectCompBAnkDet);
        // echo "<pre>";print_r($resultBankDetails); die("");
        return $resultBankDetails;
    }

    public function getAddressByAgency($agencyId, $addressType = NULL, $OnlyAddress = 0) {
        $where = " TBAA.AgencySysId = " . $agencyId;
        if ($addressType) {
            $where .= " AND TBAA.AddressType = '" . $addressType . "'";
        }
        $select = $this->db->select();
        $select->from(array('TBAA' => 'TB_Agency_Addresses'), array('TBAA.AddressType', 'TBAA.AddressSeq', 'TBAA.Address as Address', 'TBAA.Pincode', 'TBAA.CopyOfAddressProofFile', 'TBAA.CitySysId', 'TBAA.CountrySysId'));
        if ($OnlyAddress == 0) {
            $select->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAA.CountrySysId', array('TBMGCO.Title as CountryName'));
            $select->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBAA.CitySysId', array('TBMGC.Title as CityName', 'TBMGC.StateSysId'));
            $select->joinLeft(array('TMGS' => 'TB_Master_Geo_State'), 'TMGS.StateId=TBMGC.StateSysId', array('TMGS.Title as StateName', 'TMGS.GSTNo'));
            $select->joinLeft(array('TBAOD' => 'TB_Agency_OtherDetails'), 'TBAA.AgencySysId=TBAOD.AgencySysId', array('FacebookURL', 'GoogleURL', 'YouTubeURL', 'TwitterURL', 'InstagramURL', 'PintrestURL'));
        }
        $select->where($where);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyFiles($agencyId, $docType = NULL) {
        $where = " AgencySysId = " . $agencyId;
        if ($docType) {
            $where .= " AND DocType = '" . $docType . "'";
        }
        $select = $this->db->select()
                ->from('TB_Agency_Documents')
                ->where($where);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function addbank($bankdataadd) {
        return $this->db->insert('TB_Agency_BankDetails', $bankdataadd);
    }

    public function updateAgencyBankDetails($agencyBankData, $BankInfoSysId) {
        $where = array('BankInfoSysId =?' => $BankInfoSysId);
        return $this->db->update('TB_Agency_BankDetails', $agencyBankData, $where);
    }

    public function updateAgencyAdressDetails($agencyAddressData, $AddressSeq) {
        $where = array('AddressSeq =?' => $AddressSeq);
        return $this->db->update('TB_Agency_Addresses', $agencyAddressData, $where);
    }

    public function getAgencyOtherDetails($agencyId) {
        $where = " AgencySysId = " . $agencyId;

        $select = $this->db->select()
                ->from('TB_Agency_OtherDetails')
                ->where($where);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyDocDataByParentId($agencyId, $ParentDocSysId = NULL) {
        $where = " AgencySysId = " . $agencyId;
        if ($ParentDocSysId) {
            $where .= " AND ParentDocSysId = '" . $ParentDocSysId . "'";
        }

        $select = $this->db->select()
                ->from('TB_Agency_Documents')
                ->where($where);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAllAddressByAgency($agencyId, $addressType = NULL) {
        $where = " TBAA.AgencySysId = " . $agencyId;
        if ($addressType) {
            $where .= " AND TBAA.AddressType = '" . $addressType . "'";
        }
        $select = $this->db->select()
                //                ->from(array("TBA" => "TB_Agency"), array('TBA.EmailIdForCustomer'))
                ->from(array('TBAA' => 'TB_Agency_Addresses'), array('TBAA.AddressType', 'TBAA.AddressSeq', 'TBAA.Address as Address', 'TBAA.Pincode', 'TBAA.CopyOfAddressProofFile', 'TBAA.CitySysId', 'TBAA.CountrySysId'))
                ->joinLeft(array('TBMGCO' => 'TB_Master_Geo_Country'), 'TBMGCO.ContId=TBAA.CountrySysId', array('TBMGCO.Title as CountryName'))
                ->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBAA.CitySysId', array('TBMGC.Title as CityName'))
                ->where($where);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    /**
     * SetActiveDeactive. To set IsActive/IsDeactive record.
     * 
     * @param  string $whereCondition
     * @param  string $tableName
     * @param  int    $isActive
     * @return int
     */
    public function setActiveDeactive($whereCondition, $tableName, $value) {
        try {
            $isChanged = $this->db->update($tableName, array('IsActive' => $value), $whereCondition);
            if (@$isChanged) {
                $this->db->query("UPDATE TB_Agency_User SET IsActive=$value WHERE $whereCondition");
            }
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }

        return @$isChanged ? $isChanged : 0;
    }

    public function getAgencyDataById($AgencySysId) {
        $select = $this->db->select()
                ->from(array("TBA" => "TB_Agency"))
                ->joinleft(array("TBAU" => "TB_Agency_User"), 'TBAU.AgencySysId=TBA.AgencySysId', array('UserSysId', 'countrycode2', 'ContactNo2', 'SecondaryEmail', "EmailId", "FirstName", "LastName", "ContactNo1", "UserSysId", "AgencySysId", "countrycode", 'Address as UserAddress', 'GSTIN', 'OtherEmail', 'SignatureType', 'Signature'))
                ->joinleft(array("TBMC" => "TB_Master_Currency"), 'TBMC.CurrencyType=TBA.TrxCurrency', array('Symbol'))
                ->joinLeft(array('TBAM' => "TB_Agency_Mapping"), "TBA.AgencySysId = TBAM.AgencySysId", array("B2BGreetingMessage", "IsB2CGreetingMessage", "IsB2BGreetingMessage", "IsSanKash", "IsAllowFlexiPayment", "StaffUser", "B2BUser", "B2CUser", "IsAuthentication", "IsEnabledFlexiPay", "IsB2BRewardsPoints", "IsHotelOnlyRates", "TotalOnlyRatesHotels", "TotalInventoryHotels", "Salutation", "Title as showTitle", "ProposalExpiryDays"))
                ->where("TBA.AgencySysId =?", $AgencySysId)
                ->order("TBAU.UserSysId");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyDataByIdForResendOPT($AgencySysId) {
        $select = $this->db->select()
                ->from(array("TBA" => "TB_Agency"), array("Title", "CountrySysId", "CitySysId", "StateOrZoneSysId", "PanCardNo", "AgencyShortDetail"))
                ->joinleft(array("TBAU" => "TB_Agency_User"), 'TBAU.AgencySysId=TBA.AgencySysId', array("EmailId", "FirstName", "LastName", "ContactNo1", "UserSysId", "AgencySysId", "countrycode"))
                ->where("TBA.AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function editAgencyDetails($agencyDataForDb = array(), $AgencySysId = NULL) {
        $where = array('AgencySysId =?' => $AgencySysId);
        $this->db->update('TB_Agency', $agencyDataForDb, $where);
    }

    public function addAgencyWallet($agencyWalletData = array()) {
        $agencyWalletDataForDB = array(
            'AgencySysId' => $agencyWalletData['AgencySysId'],
            'Title' => $agencyWalletData['Title'],
            'CurrencyType' => $agencyWalletData['CurrencyType'],
            'BalanceAmount' => 0,
            'CreateDate' => $agencyWalletData['CreateDate'],
            'UpdateDate' => $agencyWalletData['UpdateDate'],
            'IsMarkForDel' => 0,
            'IsActive' => 1,
        );
        try {
            $this->db->insert('TB_Agency_Wallet', $agencyWalletDataForDB);
            return $this->db->lastInsertId('TB_Agency_Wallet');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function getAgencyUserDataById($AgentSysId) {
        $select = $this->db->select();
        $select->from(array('TBAU' => "TB_Agency_User"));
        $select->where("TBAU.UserSysId =?", $AgentSysId);
        $select->joinLeft(array('TBMGCO' => "TB_Master_Geo_Country"), "TBMGCO.ContId = TBAU.CountrySysId", array('Title as CountryTitle'));
        $select->joinLeft(array('TBMGC' => "TB_Master_Geo_City"), "TBMGC.CityId = TBAU.CitySysId", array('Title as CityTitle'));
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getB2BProposalCustomerDetail($AgencySysId = null, $CustomerSysId = null) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Customer"), array("CustomerSysId", "IsActive"))
                ->joinLeft(array('t2' => "TB_Agency_User"), "t1.CreatedByUserSysId = t2.UserSysId", array("AgencyCode", "EmailId", "FirstName", "LastName", "AgencySysId", "UserSysId", "AgencyName", "ContactNo1", 'Signature'))
                ->joinLeft(array('t3' => "TB_Agency"), "t2.AgencySysId = t3.AgencySysId", array('Title', 'DisplayName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryContactName'))
                ->where("t1.AgencySysId =?", $AgencySysId)
                ->where("t1.IsB2BAgent =?", 1)
                ->where("t1.IsActive =?", 1)
                ->where("t1.IsMarkForDelete =?", '0')
                ->where("t2.IsB2BAgent =?", 1)
                ->where("t2.IsActive =?", 1)
                ->where("t2.IsMarkForDel =?", '0')
                ->where("t3.IsActive =?", 1)
                ->where("t3.IsMarkForDelete =?", '0')
                ->where("t1.CustomerSysId =?", $CustomerSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyWalletDetails($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Wallet")
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyWalletBalance($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Wallet")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("IsMarkForDel =?", 0)
                ->where("IsActive =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updateAgencyWallet($agencyWalletDataDb = array(), $WalletSysId = NULL) {
        $where = array('WalletSysId =?' => $WalletSysId);
        return $this->db->update('TB_Agency_Wallet', $agencyWalletDataDb, $where);
    }

    public function getAgencyTnCDetails($AgencySysId) {
        $select = $this->db->select()
                ->from(
                        array("t1" => "TB_Agency_Terms_Conditions"),
                        array(
                            "TncId",
                            "AgencySysId",
                            "Inclusions",
                            "Exclusions",
                            "CancellationPolicy",
                            "TravelBasics",
                            "BookingTerms",
                            "WhyUseUs",
                            "TermsAndConditions",
                            "CreateDate",
                            "UpdateDate",
                            "IsMarkForDel",
                            "IsActive",
                            "Payment",
                            "CancellationPolicyInternational",
                            "TermsAndConditionsInternational",
                            "InclusionsInternational",
                            "InclusionsDomestic",
                            "ExclusionDomestic",
                            "ExclusionInternational",
                            "PaymentDetails",
                            "bookingTermVisa",
                            "travelBasicsVisa",
                            "termConditionsVisa",
                            "cancellationPolicyVisa",
                            "REFUNDPOLICY",
                            "IsInclusions",
                            "IsExclusions",
                            "IsCancellationPolicy",
                            "IsTravelBasics",
                            "IsBookingTerms",
                            "IsWhyUseUs",
                            "IsTermsAndConditions",
                            "IsPayment",
                            "IsCancellationPolicyInternational",
                            "IsTermsAndConditionsInternational",
                            "IsInclusionsInternational",
                            "IsInclusionsDomestic",
                            "IsExclusionDomestic",
                            "IsExclusionInternational",
                            "IsPaymentDetails",
                            "IsbookingTermVisa",
                            "IstravelBasicsVisa",
                            "IstermConditionsVisa",
                            "IscancellationPolicyVisa",
                            "IsREFUNDPOLICY",
                            "IstermConditionsTransfer",
                            "TermConditionsTransfer",
                            "IsTermConditionsInvoice",
                            "TermConditionsInvoice",
                            "IsInclusionTransfer",
                            "IsExclusionTransfer",
                            "IsBookingTermTransfer",
                            "InclusionTransfer",
                            "ExclusionTransfer",
                            "BookingTermTransfer",
                            "IsTCFlightTicket",
                            "TCFlightTicket",
                            "IsTCSightseeingProposal",
                            "TCSightseeingProposal",
                            "IsTCSightseeingVoucher",
                            "TCSightseeingVoucher",
                            "IsTCHotelProposal",
                            "TCHotelProposal",
                            "IsTCHotelVoucher",
                            "TCHotelVoucher",
                            "IsTCForex",
                            "TCForex"
                        )
                )
                ->where("t1.AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

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

    public function insertData($table, $data) {
        $this->db->insert($table, $data);
        return $this->db->lastInsertId();
    }

    /* Added By Pardeep Panchal */

    public function getAgencyInvoiceNumberSeries($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Template", array('From', 'To'))
                ->where("AgencySysId =?", $AgencySysId)
                ->where("IsActive =?", 1)
                ->order("AgencyTemplateSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }
    public function getAgencyWisePrefix($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Mapping", array('InvoicePrefix'))
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyLastGeneratedInvoiceNumber_BKP($AgencySysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Trx"), array(''))
                ->joinLeft(array("t2" => "TB_Agency_Wallet_Trx"), "t2.TrxId = t1.TrxSysId", array('InvoiceId'))
                ->where("t2.AgencySysId = ?", $AgencySysId)
                ->where("t2.InvoiceId != ''")
                ->where("t2.InvoiceId != '0'")
                ->order("t2.TrxId DESC");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyLastGeneratedInvoiceNumber($AgencySysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_InvoiceNumbers"), array('InvoiceNumber'))
                ->where("t1.AgencySysId = ?", $AgencySysId)
                ->where("t1.InvoiceNumber != ''")
                ->order("t1.InvoiceSysId DESC");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyLastGeneratedCreditNoteNumber($AgencySysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Credit_Note_Number"), array('InvoiceNumber'))
                ->where("t1.AgencySysId = ?", $AgencySysId)
                ->where("t1.InvoiceNumber != ''")
                ->order("t1.InvoiceSysId DESC");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkIfFinanacialYearChanged($AgencySysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_InvoiceNumbers"), array('IsFinancialYearChanged'))
                ->where("t1.AgencySysId = ?", $AgencySysId)
                ->order("t1.InvoiceSysId DESC");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkService($AgencySysId, $PlanType) {
        $select = $this->db->select()
                ->from(array("t1" => "Tb_Agency_Services"))
                ->where("t1.AgencySysId = ?", $AgencySysId)
                ->where("t1.plan_type = ?", $PlanType)
                ->where("t1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyEmailTemplates($AgencySysId) {
        $arrTempDetails = array('Id', 'TemplateName', 'Subject', 'CreateDate', 'IsMarkForDel', 'IsActive', 'MessageBody');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Email_Template"), $arrTempDetails);
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.IsMarkForDel = ?', 0);
        $select->where('tb1.IsActive = ?', 1);
        $select->order('tb1.TemplateName');
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        // echo "<pre>";print_r($result);exit;
        return $result;
    }

    public function getAgencyEmailTemplateByTemplateId($id) {
        $arrTempDetails = array('Id', 'TemplateName', 'Subject', 'MessageBody', 'CreateDate', 'IsMarkForDel', 'IsActive');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Email_Template"), $arrTempDetails);
        $select->where('tb1.Id = ?', $id);
        $select->where('tb1.IsMarkForDel = ?', 0);
        $select->where('tb1.IsActive = ?', 1);
        $select->order('tb1.TemplateName');
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getEmailTemplateList($isActive = NULL) {
        $strCondition = " 1 = 1";
        if ($isActive) {
            $strCondition .= " AND IsActive = " . $isActive;
        }
        $strCondition .= " AND IsMarkForDel != '1' ";
        $select = $this->db->select();
        $select->from("TB_Agency_Email_Template");
        $select->where($strCondition);
        if (!empty($this->intAgencySysId)) {
            $select->where("AgencySysId =?", $this->intAgencySysId);
        }
        if (!empty($this->intId)) {
            $select->where("Id =?", $this->intId);
        }
        $select->order("TemplateName Asc");
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function isEmailTemplateExists($TemplateName, $intId = NULL, $AgencySysId = NULL) {

        $strCondition = " TemplateName = '" . $TemplateName . "'";
        if (!empty($intId)) {
            $strCondition .= " AND Id != '" . $intId . "'";
        }
        if (!empty($AgencySysId)) {
            $strCondition .= " AND AgencySysId = '" . $AgencySysId . "'";
        }
        $select = $this->db->select()
                ->from("TB_Agency_Email_Template", array('count(*) AS totalRecords'))
                ->where($strCondition);

        $result = $this->db->fetchOne($select);
        if ($result > 0) {
            return true;
        }
        return false;
    }

    public function updateEmailTemplate($intId, $data = array()) {
        $strWhereCondition = array('Id =?' => $intId);
        $this->db->update('TB_Agency_Email_Template', $data, $strWhereCondition);
    }

    public function addEmailTemplate($data = array()) {
        $this->db->insert('TB_Agency_Email_Template', $data);
        return $this->db->lastInsertId();
    }

    public function deleteEmailTemplate($intId, $data) {
        $strWhereCondition = array('Id =?' => $intId);
        $this->db->update('TB_Agency_Email_Template', $data, $strWhereCondition);
    }

    public function emailStatistics() {

        $select = $this->db->select();
        $select->from("TB_Agency_Sent_Sms_Email_Audit");
        if (!empty($this->intAgencySysId)) {
            $select->where("AgencySysId =?", $this->intAgencySysId);
        }
        $select->order("CreateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function emailStatisticsByDate() {

        $select = $this->db->select();
        $select->from("TB_Agency_Sent_Sms_Email_Audit");
        if (!empty($this->intAgencySysId)) {
            $select->where("AgencySysId =?", $this->intAgencySysId);
        }
        $select->where("CreateDate < ?", date('Y-m-d'));
        $select->where("IsPaymentDone =?", 0);
        $select->order("Source Asc");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function isB2BAgencyEmailExists($email, $AgencySysId, $AgencyType = 1) {
        $select = $this->db->select();
        $select->from("TB_Agency");
        $select->where("PrimaryEmail =?", $email);
        $select->where("MasterAgencySysId =?", $AgencySysId);
        $select->where("AgencyType =?", $AgencyType);
        $select->where("IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getFranchise($AgencySysId, $filterData = null, $agencyType = 1, $type = 'normal') {
        $agencyColumn = array('AgencySysId', 'IsCheckB2BWallet', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail', 'CreateDate', 'IsActive', 'IsApproved', 'countrycode', 'BusinessType', 'FirmType', 'IsPaid');
        $agentColumn = array('UserSysId', 'AgencyCode', 'FirstName as UserFirstName', 'LastName as UserLastName', 'IsB2BAgent');
        $reportingArray = array('FirstName as RFirstName', 'LastName as RLastName');
        //    $CreditColumn = array('CreditAmount', 'BalanceAmount', 'CreditDate', 'CreditSysId', 'IsActive as isStart', 'IsOneTime');
        //    $CreditHistoryColumn = array('sum(Amount) as TotalBusiness');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId AND tb1.PrimaryUserSysId = tb2.UserSysId", $agentColumn);

        $select->joinLeft(array('tb5' => 'TB_Agency_User'), 'tb1.RMUserSysId=tb5.UserSysId', $reportingArray);
        // $select->joinLeft(array('tb4' => 'TB_Agency_Credit'), 'tb1.AgencySysId=tb4.AgencySysId', $CreditColumn);
        // $select->joinLeft(array('tb6' => 'TB_Agency_Credit_History'), 'tb4.CreditSysId=tb6.CreditSysId AND tb6.IsCredit = 2 AND tb6.TrxType = 0 AND tb6.PlanType != 7', $CreditHistoryColumn);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb2.CountrySysId = tb7.ContId", array('Title as CountryTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb2.CitySysId = tb8.CityId", array('Title as CityTitle'));
        $select->joinLeft(array('tb9' => "TB_Agency_Franchise"), "tb1.AgencyMarketPlaceSysId = tb9.MarketPlaceSysId", array('Title as MarkupTitle'));
        $select->joinLeft(array('tb10' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb10.AgencySysId", array('IsAutoFTEnabled'));
        $select->joinLeft(array('tb11' => "TB_Agency_Reward_Points"), "tb1.AgencySysId = tb11.AgencySysId", array('IsActive AS rewardPoints'));
        //        $select->joinLeft(array('tb12' => "TB_Agency_BankDetails"), "tb1.AgencySysId = tb12.AgencySysId", array('BankName', 'AccountName', 'AccountNo', 'IFSCCode', 'AccountType', 'Branch', 'CitySysId', 'CountrySysId'));
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        if ($agencyType == 3) {
            $select->where("tb2.IsB2BAgent =?", 3);
        } else if ($agencyType == 2) {
            $select->where("tb2.IsB2BAgent =?", 2);
        } else {
            $select->where("tb2.IsB2BAgent =?", 1);
        }
        $select->where("tb1.IsMarkForDelete =?", 0);
        //$select->where("tb2.UserRole = 0 OR tb2.UserRole IS NULL");

        if (!empty($filterData['agencymobile'])) {
            $select->Where('tb1.ContactInfo LIKE ?', '%' . $filterData['agencymobile'] . '%');
        }
        if (!empty($filterData['agencyname'])) {
            $select->Where('lower(tb1.Title) LIKE \'%' . strtolower($filterData['agencyname']) . '%\' OR lower(tb1.PrimaryContactName) LIKE \'%' . strtolower($filterData['agencyname']) . '%\'');
        }
        if (!empty($filterData['emailid'])) {
            $select->Where('tb1.PrimaryEmail LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        if (!empty($filterData['agencycode'])) {
            $select->Where('tb2.AgencyCode = ?', $filterData['agencycode']);
        }
        if (isset($filterData['IsApproved'])) {
            $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        }
        if (isset($filterData['IsActive']) && $filterData['IsActive'] != '') {
            $select->Where('tb1.IsActive = ?', $filterData['IsActive']);
        }
        //        if (isset($filterData['IsCreditLimit']) && (int) $filterData['IsCreditLimit'] == 1) {
        //            $select->Where('tb4.CreditAmount >0 ');
        //        }
        if (isset($filterData['cityName']) && $filterData['cityName'] != '') {
            $select->Where("tb8.Title like '%" . trim($filterData['cityName']) . "%' ");
        }
        if (isset($filterData['countryName']) && $filterData['countryName'] != '') {
            $select->Where("tb7.Title like '%" . trim($filterData['countryName']) . "%' ");
        }
        if (isset($filterData['ReportingToFilter']) && (int) $filterData['ReportingToFilter'] > 0) {
            $select->Where('tb1.RMUserSysId = ?', (int) $filterData['ReportingToFilter']);
        }
        $select->order("tb1.AgencySysId DESC");
        $select->group(array('tb11.IsActive', 'tb1.IsPaid', 'tb1.FirmType', 'tb1.CreateDate', 'tb1.AgencySysId', 'tb1.Title', 'tb1.PrimaryContactName', 'tb1.ContactInfo', 'tb1.PrimaryEmail', 'tb1.IsActive', 'tb2.AgencyCode', 'tb2.UserSysId', 'tb5.FirstName', 'tb5.LastName', 'tb1.IsApproved', 'tb7.Title', 'tb8.Title', 'tb2.FirstName', 'tb2.LastName', 'tb2.IsB2BAgent', 'tb9.Title', 'tb10.IsAutoFTEnabled', 'tb1.countrycode', 'tb1.BusinessType', 'tb1.IsCheckB2BWallet'));
        //echo $select; exit;
        //        
        //        echo "<pre>"; print_r($result); die("model");
        if ($type == 'export') {
            return $this->db->fetchAll($select);
        } else {
            return $select;
        }
    }

    public function getB2BAgency($AgencySysId, $filterData = null, $agencyType = 1, $type = 'normal') {
        $agencyColumn = array('RMUserSysId AS ReportingToSysId', 'AgencySysId', 'IsCheckB2BWallet', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail', 'CreateDate', 'IsActive', 'IsApproved', 'countrycode', 'BusinessType', 'FirmType', 'IsPaid');
        $agentColumn = array('UserSysId', 'AgencyCode', 'FirstName as UserFirstName', 'LastName as UserLastName', 'IsB2BAgent');
        //$agentRoleColumn = array('');
        $reportingArray = array('FirstName as RFirstName', 'LastName as RLastName');
        $CreditColumn = array('CreditAmount', 'BalanceAmount', 'CreditDate', 'CreditSysId', 'IsActive as isStart', 'IsOneTime');
        $CreditHistoryColumn = array('sum(Amount) as TotalBusiness');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId AND tb1.PrimaryUserSysId = tb2.UserSysId", $agentColumn);
        // $select->joinLeft(array('tb3' => 'TB_Agency_DeptUserMap'), 'tb3.UserSysId=tb2.UserSysId', $agentRoleColumn);
        $select->joinLeft(array('tb5' => 'TB_Agency_User'), 'tb1.RMUserSysId=tb5.UserSysId', $reportingArray);
        $select->joinLeft(array('tb4' => 'TB_Agency_Credit'), 'tb1.AgencySysId=tb4.AgencySysId', $CreditColumn);
        $select->joinLeft(array('tb6' => 'TB_Agency_Credit_History'), 'tb4.CreditSysId=tb6.CreditSysId AND tb6.IsCredit = 2 AND tb6.TrxType = 0 AND tb6.PlanType != 7', $CreditHistoryColumn);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb2.CountrySysId = tb7.ContId", array('Title as CountryTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb2.CitySysId = tb8.CityId", array('Title as CityTitle'));
        $select->joinLeft(array('tb9' => "TB_Agency_MarketPlace"), "tb1.AgencyMarketPlaceSysId = tb9.MarketPlaceSysId", array('Title as MarkupTitle'));
        $select->joinLeft(array('tb10' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb10.AgencySysId", array('IsAutoFTEnabled'));
        $select->joinLeft(array('tb11' => "TB_Agency_Reward_Points"), "tb1.AgencySysId = tb11.AgencySysId", array('IsActive AS rewardPoints'));
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        if ($agencyType == 3) {
            $select->where("tb2.IsB2BAgent =?", 3);
        } else if ($agencyType == 2) {
            $select->where("tb2.IsB2BAgent =?", 2);
        } else {
            $select->where("tb2.IsB2BAgent =?", 1);
        }
        $select->where("tb1.IsMarkForDelete =?", 0);
        //$select->where("tb2.UserRole = 0 OR tb2.UserRole IS NULL");

        if (!empty($filterData['agencymobile'])) {
            $select->Where('tb1.ContactInfo LIKE ?', '%' . $filterData['agencymobile'] . '%');
        }
        if (!empty($filterData['agencyname'])) {
            $select->Where('lower(tb1.Title) LIKE \'%' . strtolower($filterData['agencyname']) . '%\' OR lower(tb1.PrimaryContactName) LIKE \'%' . strtolower($filterData['agencyname']) . '%\'');
        }
        if (!empty($filterData['emailid'])) {
            $select->Where('tb1.PrimaryEmail LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        if (!empty($filterData['agencycode'])) {
            $select->Where('tb2.AgencyCode = ?', $filterData['agencycode']);
        }
        if (isset($filterData['IsApproved'])) {
            $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        }
        if (isset($filterData['IsActive']) && $filterData['IsActive'] != '') {
            $select->Where('tb1.IsActive = ?', $filterData['IsActive']);
        }
        if (isset($filterData['IsCreditLimit']) && (int) $filterData['IsCreditLimit'] == 1) {
            $select->Where('tb4.CreditAmount >0 ');
        }
        if (!empty($filterData['ToDate']) && !empty($filterData['FromDate'])) {
            $ToDate = date('Y-m-d', strtotime($filterData['ToDate']));
            $FromDate = date('Y-m-d', strtotime($filterData['FromDate']));
            $select->where("CONVERT(date,(tb4.CreditDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }
        if (isset($filterData['cityName']) && $filterData['cityName'] != '') {
            $select->Where("tb8.Title like '%" . trim($filterData['cityName']) . "%' ");
        }
        if (isset($filterData['countryName']) && $filterData['countryName'] != '') {
            $select->Where("tb7.Title like '%" . trim($filterData['countryName']) . "%' ");
        }

        if (isset($filterData['ReportingToFilter']) && (int) $filterData['ReportingToFilter'] > 0) {
            $select->Where('tb1.RMUserSysId = ?', (int) $filterData['ReportingToFilter']);
        }
        $select->order("tb1.AgencySysId DESC");
        $select->group(array('tb11.IsActive', 'tb1.IsPaid', 'tb1.FirmType', 'tb4.IsOneTime', 'tb1.CreateDate', 'tb1.AgencySysId', 'tb1.Title', 'tb1.PrimaryContactName', 'tb1.ContactInfo', 'tb1.PrimaryEmail', 'tb1.IsActive', 'tb2.AgencyCode', 'tb2.UserSysId', 'tb1.RMUserSysId', 'tb4.CreditAmount', 'tb4.BalanceAmount', 'tb5.FirstName', 'tb5.LastName', 'tb1.IsApproved', 'tb7.Title', 'tb8.Title', 'tb4.CreditDate', 'tb4.CreditSysId', 'tb4.IsActive', 'tb2.FirstName', 'tb2.LastName', 'tb2.IsB2BAgent', 'tb9.Title', 'tb10.IsAutoFTEnabled', 'tb1.countrycode', 'tb1.BusinessType', 'tb1.IsCheckB2BWallet'));
        //        echo $select;
        //        exit;
        //        
        //        echo "<pre>"; print_r($result); die("model");
        if ($type == 'export') {
            return $this->db->fetchAll($select);
        } else {
            return $select;
        }
    }

    public function getFranchisee($AgencySysId, $filterData = null, $agencyType = 1, $type = 'normal') {
        $agencyColumn = array('RMUserSysId AS ReportingToSysId', 'AgencySysId', 'IsCheckB2BWallet', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail', 'CreateDate', 'IsActive', 'IsApproved', 'countrycode', 'BusinessType', 'FirmType', 'IsPaid');
        $agentColumn = array('UserSysId', 'AgencyCode', 'FirstName as UserFirstName', 'LastName as UserLastName', 'IsB2BAgent');

        $reportingArray = array('FirstName as RFirstName', 'LastName as RLastName');
        $FranchiseColumn = array('sum(Amount) as TotalBusiness', 'sum(AdjustedCommission) as FinalCommission', 'SUM(CASE WHEN tb4.Paid = 1 THEN tb4.AdjustedCommission ELSE 0 END) AS PaidCommission', 'MAX(tb4.CreateDate) as CreateDateC');
        //$CreditHistoryColumn = array('sum(Amount) as TotalBusiness');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId AND tb1.PrimaryUserSysId = tb2.UserSysId", $agentColumn);
        $select->joinLeft(array('tb5' => 'TB_Agency_User'), 'tb1.RMUserSysId=tb5.UserSysId', $reportingArray);
        $select->joinLeft(array('tb4' => 'Tb_Agency_Commission'), 'tb1.AgencySysId=tb4.FranchiseSysId', $FranchiseColumn);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan"), "tb4.TPSysId = tb3.TPSysId", array());
        $select->where("tb3.StatusType !=?", 15);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb2.CountrySysId = tb7.ContId", array('Title as CountryTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb2.CitySysId = tb8.CityId", array('Title as CityTitle'));
        $select->joinLeft(array('tb9' => "TB_Agency_MarketPlace"), "tb1.AgencyMarketPlaceSysId = tb9.MarketPlaceSysId", array('Title as MarkupTitle'));
        $select->joinLeft(array('tb10' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb10.AgencySysId", array('IsAutoFTEnabled'));
        $select->joinLeft(array('tb11' => "TB_Agency_Reward_Points"), "tb1.AgencySysId = tb11.AgencySysId", array('IsActive AS rewardPoints'));
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("tb2.IsB2BAgent =?", 3);
        $select->where("tb1.IsMarkForDelete =?", 0);
        //$select->where("tb2.UserRole = 0 OR tb2.UserRole IS NULL");

        if (!empty($filterData['agencymobile'])) {
            $select->Where('tb1.ContactInfo LIKE ?', '%' . $filterData['agencymobile'] . '%');
        }
        if (!empty($filterData['agencyname'])) {
            $select->Where('lower(tb1.Title) LIKE \'%' . strtolower(trim($filterData['agencyname'])) . '%\' OR lower(tb1.PrimaryContactName) LIKE \'%' . strtolower(trim($filterData['agencyname'])) . '%\'');
        }
        if (!empty($filterData['emailid'])) {
            $select->Where('tb1.PrimaryEmail LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        //        if (!empty($filterData['agencycode'])) {
        //            $select->Where('tb2.AgencyCode = ?', $filterData['agencycode']);
        //        }
        if (isset($filterData['IsApproved'])) {
            $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        }

        if (!empty($filterData['ToCreateDate']) && !empty($filterData['FromCreateDate'])) {
            $ToDate = date('Y-m-d', strtotime($filterData['ToCreateDate']));
            $FromDate = date('Y-m-d', strtotime($filterData['FromCreateDate']));
            $select->where("CONVERT(date,(tb4.CreateDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }
        if (isset($filterData['IsActive']) && $filterData['IsActive'] != '') {
            $select->Where('tb1.IsActive = ?', $filterData['IsActive']);
        }

        //        if (isset($filterData['cityName']) && $filterData['cityName'] != '') {
        //            $select->Where("tb8.Title like '%" . trim($filterData['cityName']) . "%' ");
        //        }
        //        if (isset($filterData['countryName']) && $filterData['countryName'] != '') {
        //            $select->Where("tb7.Title like '%" . trim($filterData['countryName']) . "%' ");
        //        }
        if (isset($filterData['ReportingToFilter']) && (int) $filterData['ReportingToFilter'] > 0) {
            $select->Where('tb1.RMUserSysId = ?', (int) $filterData['ReportingToFilter']);
        }
        $select->order("tb1.AgencySysId DESC");
        $select->group(array('tb11.IsActive', 'tb1.IsPaid', 'tb1.FirmType', 'tb1.CreateDate', 'tb1.AgencySysId', 'tb1.Title', 'tb1.PrimaryContactName', 'tb1.ContactInfo', 'tb1.PrimaryEmail', 'tb1.IsActive', 'tb2.AgencyCode', 'tb2.UserSysId', 'tb1.RMUserSysId', 'tb5.FirstName', 'tb5.LastName', 'tb1.IsApproved', 'tb7.Title', 'tb8.Title', 'tb2.FirstName', 'tb2.LastName', 'tb2.IsB2BAgent', 'tb9.Title', 'tb10.IsAutoFTEnabled', 'tb1.countrycode', 'tb1.BusinessType', 'tb1.IsCheckB2BWallet'));
        //        echo $select; exit;
        //        
        //        echo "<pre>"; print_r($result); die("model");
        if ($type == 'export') {
            return $this->db->fetchAll($select);
        } else {
            return $select;
        }
    }

    public function getBNPLB2BAgencyTotal($AgencySysId, $filterData = null, $agencyType = 1, $type = "normal") {
        $agencyColumn = array();
        $CreditColumn = array('sum(tb4.CreditAmount) as CreditAmount', 'sum(tb4.BalanceAmount) as BalanceAmount');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb4' => 'TB_Agency_BNPL_Credit'), 'tb1.AgencySysId=tb4.AgencySysId', $CreditColumn);
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("tb1.FirmType =?", 5);
        $select->where("tb1.IsPaid =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        return $this->db->fetchAll($select);
    }

    public function getTotalCreditLimit($AgencySysId) {
        $agencyColumn = array();
        $CreditColumn = array('sum(tb4.CreditAmount) as CreditAmount', 'sum(tb4.BalanceAmount) as BalanceAmount');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb4' => 'TB_Agency_Credit'), 'tb1.AgencySysId=tb4.AgencySysId', $CreditColumn);
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->Where('tb1.IsApproved = ?', 1);
        return $this->db->fetchAll($select);
    }

    public function getBNPLB2BAgency($AgencySysId, $filterData = null, $agencyType = 1, $type = "normal") {
        $agencyColumn = array('FirmType', 'IsPaid', 'AgencySysId', 'IsCheckB2BWallet', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail', 'CreateDate', 'IsActive', 'IsApproved', 'countrycode', 'BusinessType');
        $agentColumn = array('UserSysId', 'AgencyCode', 'FirstName as UserFirstName', 'LastName as UserLastName', 'IsB2BAgent');
        $agentRoleColumn = array('ReportingToSysId');
        $reportingArray = array('FirstName as RFirstName', 'LastName as RLastName');
        $CreditColumn = array('CreditAmount', 'BalanceAmount', 'CreditDate', 'CreditSysId', 'IsActive as isStart', 'IsOneTime');
        $CreditHistoryColumn = array('sum(Amount) as TotalBusiness');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId AND tb1.PrimaryUserSysId = tb2.UserSysId", $agentColumn);
        $select->joinLeft(array('tb3' => 'TB_Agency_DeptUserMap'), 'tb3.UserSysId=tb2.UserSysId', $agentRoleColumn);
        $select->joinLeft(array('tb5' => 'TB_Agency_User'), 'tb3.ReportingToSysId=tb5.UserSysId', $reportingArray);
        $select->joinLeft(array('tb4' => 'TB_Agency_BNPL_Credit'), 'tb1.AgencySysId=tb4.AgencySysId', $CreditColumn);
        $select->joinLeft(array('tb6' => 'TB_Agency_BNPL_Credit_History'), 'tb4.CreditSysId=tb6.CreditSysId AND tb6.IsCredit = 2 AND tb6.TrxType = 0 AND tb6.IsActive=1 AND tb6.PlanType != 7', $CreditHistoryColumn);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb1.CountrySysId = tb7.ContId", array('Title as CountryTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb1.CitySysId = tb8.CityId", array('Title as CityTitle'));
        $select->joinLeft(array('tb9' => "TB_Agency_MarketPlace"), "tb1.AgencyMarketPlaceSysId = tb9.MarketPlaceSysId", array('Title as MarkupTitle'));
        $select->joinLeft(array('tb10' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb10.AgencySysId", array('IsAutoFTEnabled'));
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("tb1.FirmType =?", 5);
        $select->where("tb1.IsPaid =?", 1);
        if ($agencyType == 2) {
            $select->where("tb2.IsB2BAgent =?", 2);
        } else {
            $select->where("tb2.IsB2BAgent =?", 1);
        }
        $select->where("tb1.IsMarkForDelete =?", 0);
        //$select->where("tb2.UserRole = 0 OR tb2.UserRole IS NULL");

        if (!empty($filterData['agencymobile'])) {
            $select->Where('tb1.ContactInfo LIKE ?', '%' . $filterData['agencymobile'] . '%');
        }
        if (!empty($filterData['agencyname'])) {
            $select->Where('lower(tb1.Title) LIKE \'%' . strtolower($filterData['agencyname']) . '%\' OR lower(tb1.PrimaryContactName) LIKE \'%' . strtolower($filterData['agencyname']) . '%\'');
        }
        if (!empty($filterData['emailid'])) {
            $select->Where('tb1.PrimaryEmail LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        if (!empty($filterData['agencycode'])) {
            $select->Where('tb2.AgencyCode = ?', $filterData['agencycode']);
        }
        if (isset($filterData['IsApproved'])) {
            $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        }
        if (isset($filterData['IsActive']) && $filterData['IsActive'] != '') {
            $select->Where('tb1.IsActive = ?', $filterData['IsActive']);
        }
        if (isset($filterData['IsCreditLimit']) && (int) $filterData['IsCreditLimit'] == 1) {
            $select->Where('tb4.CreditAmount >0 ');
        }
        $select->order("tb1.AgencySysId DESC");
        $select->group(array('tb1.FirmType', 'tb1.IsPaid', 'tb4.IsOneTime', 'tb1.CreateDate', 'tb1.AgencySysId', 'tb1.Title', 'tb1.PrimaryContactName', 'tb1.ContactInfo', 'tb1.PrimaryEmail', 'tb1.IsActive', 'tb2.AgencyCode', 'tb2.UserSysId', 'tb3.ReportingToSysId', 'tb4.CreditAmount', 'tb4.BalanceAmount', 'tb5.FirstName', 'tb5.LastName', 'tb1.IsApproved', 'tb7.Title', 'tb8.Title', 'tb4.CreditDate', 'tb4.CreditSysId', 'tb4.IsActive', 'tb2.FirstName', 'tb2.LastName', 'tb2.IsB2BAgent', 'tb9.Title', 'tb10.IsAutoFTEnabled', 'tb1.countrycode', 'tb1.BusinessType', 'tb1.IsCheckB2BWallet'));
        if ($type == 'export') {
            //echo $select; exit;
            return $this->db->fetchAll($select);
        } else {
            return $select;
        }
    }

    public function getB2BAgencyUser($AgencySysId) {
        $agencyColumn = array('AgencySysId', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail');
        $agentColumn = array('UserSysId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId", $agentColumn);
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyMarketPlaceList($type = 1, $isActive = NULL, $AgencySysId = NULL, $MarketPlaceSysId = NULL) {
        $agencColumn = array('Title as AgencyName');
        $agencymarketPlaceColumn = array('IsActive', 'MarketPlaceSysId', 'IsDefault', 'IsDefaultB2C', 'IsDefaultLoggedinB2C', 'Title', 'AgencySysId', 'MarkUpValue', 'MarkUpType', 'PlanType', 'MasterId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MarketPlace"), $agencymarketPlaceColumn);
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", $agencColumn);
        $select->where("tb1.IsMarkForDelete =?", 0);
        if (!empty($MarketPlaceSysId)) {
            $select->where("tb1.MarketPlaceSysId =?", $MarketPlaceSysId);
        }
        if (!empty($AgencySysId)) {
            $select->where("tb1.AgencySysId =?", $AgencySysId);
        }
        if (!empty($isActive)) {
            $select->where("tb1.isActive =?", $isActive);
        }
        $select->where("tb1.MasterId =?", 0);
        $select->where("tb1.Type =?", $type);
        $select->order("tb1.MarketPlaceSysId DESC");
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyFranchiseList($type = 1, $isActive = NULL, $AgencySysId = NULL, $MarketPlaceSysId = NULL) {
        $agencColumn = array('Title as AgencyName');
        $agencymarketPlaceColumn = array('IsActive', 'MarketPlaceSysId', 'IsDefault', 'Title', 'AgencySysId', 'PlanType', 'MasterId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Franchise"), $agencymarketPlaceColumn);
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb1.AgencySysId = tb2.AgencySysId", $agencColumn);
        $select->where("tb1.IsMarkForDelete =?", 0);
        if (!empty($MarketPlaceSysId)) {
            $select->where("tb1.MarketPlaceSysId =?", $MarketPlaceSysId);
        }
        if (!empty($AgencySysId)) {
            $select->where("tb1.AgencySysId =?", $AgencySysId);
        }
        if (!empty($isActive)) {
            $select->where("tb1.isActive =?", $isActive);
        }
        $select->where("tb1.MasterId =?", 0);
        $select->order("tb1.MarketPlaceSysId DESC");
        //echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function agencyEditFranchiseList($rowid) {
        // $agencymarketPlaceColumn = array('IsActive', 'MarketPlaceSysId', 'Title', 'AgencySysId', 'MarkUpValue', 'MarkUpType', 'PlanType', 'MasterId', 'ItemSourceType', 'Commission', 'TDS', 'IsFlexiPayment', 'MinDepositAmount', 'ProcessingFee', 'FlexiBookingActiveBefore', 'MaximumFlexiCreditLimit', 'IsMarkupDiscount', 'AdultFlexiCostFD', 'ChildFlexiCostFD', 'InfantFlexiCostFD', 'AdultFlexiCostFI', 'ChildFlexiCostFI', 'InfantFlexiCostFI', 'FlexiBookingPayCancelBefore', 'FlexiBookingActiveTo', 'RewardsPoints');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Franchise"));
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.MarketPlaceSysId =?", $rowid);
        $select->orWhere("tb1.MasterId =?", $rowid);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function agencyEditMarketplace($rowid) {
        $agencymarketPlaceColumn = array('IsActive', 'MarketPlaceSysId', 'Title', 'AgencySysId', 'MarkUpValue', 'MarkUpType', 'PlanType', 'MasterId', 'ItemSourceType', 'Commission', 'TDS', 'IsFlexiPayment', 'MinDepositAmount', 'ProcessingFee', 'FlexiBookingActiveBefore', 'MaximumFlexiCreditLimit', 'IsMarkupDiscount', 'AdultFlexiCostFD', 'ChildFlexiCostFD', 'InfantFlexiCostFD', 'AdultFlexiCostFI', 'ChildFlexiCostFI', 'InfantFlexiCostFI', 'FlexiBookingPayCancelBefore', 'FlexiBookingActiveTo', 'RewardsPoints');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MarketPlace"), $agencymarketPlaceColumn);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.MarketPlaceSysId =?", $rowid);
        $select->orWhere("tb1.MasterId =?", $rowid);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addAgencyMarkUp($data = array()) {
        $this->db->insert('TB_Agency_MarketPlace', $data);
        return $this->db->lastInsertId();
    }

    public function addAgencyFranchise($data = array()) {
        $this->db->insert('TB_Agency_Franchise', $data);
        return $this->db->lastInsertId();
    }

    public function updateAgencyMarkUp($agencyMarketPlaceData, $where) {
        $this->db->update('TB_Agency_MarketPlace', $agencyMarketPlaceData, $where);
    }

    public function updateAgencyFranchise($agencyMarketPlaceData, $where) {
        $this->db->update('TB_Agency_Franchise', $agencyMarketPlaceData, $where);
    }

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

    /* public function agencyMarketPlaceList($AgencySysId) {
      $marketPlaceColumn=array('MarketPlaceSysId','MarkUpValue');
      $select = $this->db->select();
      $select->from(array('tb1' => "TB_Agency_MarketPlace") ,$marketPlaceColumn);
      $select->where("tb1.AgencySysId =?", $AgencySysId);
      $select->where("tb1.IsActive =?", 1);
      $select->where("tb1.IsMarkForDelete =?", 0);
      $result = $this->db->fetchAll($select);
      return $result;
      }
     */

    public function checkB2BAgencyByEmail($email, $MasterAgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency");
        $select->where("PrimaryEmail =?", $email);
        $select->where("MasterAgencySysId =?", $MasterAgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    /* Added By Pardeep Panchal Ends */

    public function isB2BAgencyExists($email = NULL, $AgencySysId = NULL, $MasterAgencySysId = NULL) {
        $select = $this->db->select();
        $select->from("TB_Agency");
        $email ? $select->where("PrimaryEmail =?", $email) : '';
        $select->where("MasterAgencySysId =?", $MasterAgencySysId);
        $select->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function isB2CCustomerExists($CustomerSysId = NULL, $AgencySysId = Null) {
        $select = $this->db->select();
        $select->from("TB_Agency_Customer");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("CustomerSysId =?", $CustomerSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyDeptUserMapById($UserSysId) {
        $Column = array('DeptSysId', 'UserSysId');
        $select = $this->db->select()
                ->from(array("TBD" => "TB_Agency_DeptUserMap"), $Column)
                ->where("TBD.ReportingToSysId =?", $UserSysId)
                ->where("TBD.IsActive =?", 1)
                ->where("TBD.IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        $UserSysIdArr = array();
        if ($result) {
            foreach ($result as $value) {
                $UserSysIdArr[] = $value['UserSysId'];
            }
        }
        return !empty($UserSysIdArr) ? implode(',', $UserSysIdArr) : '';
    }

    public function isB2BAgencyNameExists($name, $AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency");
        $select->where("Title =?", $name);
        $select->where("MasterAgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getEmailMessageIds($TPSysId = NULL) {
        $select = $this->db->select();
        $select->from("TB_Agency_Sent_Sms_Email", array('RefSysId', 'SysId'));
        $select->where("FirstCron =?", 0);
        $select->where("TPSysId =?", $TPSysId);
        $select->where("TypeSysId =?", 1);
        $select->order("SysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadSource($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_LeadSource"), array('Title', 'IsActive', 'LeadSourceSysId', 'IsSetDefaultUserId', 'IsMappedPlanType', 'IsEnabledCheckBox'));
        $select->joinLeft(array('tb2' => "TB_Agency_User_LeadSource_Mapping"), "tb2.LeadSourceSysId = tb1.LeadSourceSysId AND tb2.IsMarkForDel = 0", array("UserSysId", "PlanType"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order("tb1.LeadSourceSysId DESC");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        $returnArray = [];
        foreach ($result as $resultSet) {
            $returnArray[$resultSet['LeadSourceSysId']]['Title'] = $resultSet['Title'];
            $returnArray[$resultSet['LeadSourceSysId']]['IsMappedPlanType'] = $resultSet['IsMappedPlanType'];
            $returnArray[$resultSet['LeadSourceSysId']]['IsActive'] = $resultSet['IsActive'];
            $returnArray[$resultSet['LeadSourceSysId']]['LeadSourceSysId'] = $resultSet['LeadSourceSysId'];
            $returnArray[$resultSet['LeadSourceSysId']]['IsEnabledCheckBox'] = $resultSet['IsEnabledCheckBox'];
            $returnArray[$resultSet['LeadSourceSysId']]['IsSetDefaultUserId'] = $resultSet['IsSetDefaultUserId'];
            if ($resultSet['IsMappedPlanType'] == 1) {
                $returnArray[$resultSet['LeadSourceSysId']]['UsersysId']['PlanType'][$resultSet['PlanType']][] = $resultSet['UserSysId'];
            } else {
                $returnArray[$resultSet['LeadSourceSysId']]['UsersysId'][] = $resultSet['UserSysId'];
            }
        }
        //echo "<pre>";print_r($returnArray);exit;
        return $returnArray;
    }

    public function getSenderEmailID($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Sender_EmailID");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsMarkForDel =?", 0);
        $select->where("IsCCMail =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getDefaultCC($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Sender_EmailID");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsMarkForDel =?", 0);
        $select->where("IsCCMail =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getActiveSenderEmailID($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Sender_EmailID");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsMarkForDel =?", 0);
        $select->where("IsActive =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSenderEmailIDBySenderSysId($AgencySysId, $SenderSysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Sender_EmailID");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("SenderSysId =?", $SenderSysId);
        $select->where("IsMarkForDel =?", 0);
        $select->where("IsActive =?", 1);
        //echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getDocumentList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Visa_Document"), array("IsMandatory"));
        $select->joinLeft(array('tb2' => "TB_Master_VisaDocument"), "tb1.DocSysId = tb2.DocSysId", array("DocSysId", "Title", "Description", "FieldType"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb2.IsMarkForDel != ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    //    public function getDocumentList() {
    //        $select = $this->db->select();
    //        $select->from("TB_Master_VisaDocument");
    //        $select->where("IsMarkForDel =?", 0);
    //        $select->order("DocSysId DESC");
    //        $result = $this->db->fetchAll($select);
    //        return $result;
    //    }

    public function getDocumentListEdit($id) {
        $select = $this->db->select();
        $select->from("TB_Master_VisaDocument");
        $select->where("DocSysId = ?", $id);
        return $this->db->fetchRow($select);
    }

    public function getDocumentListEditbyagencyId($id, $AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Visa_Document");
        $select->where("DocSysId = ?", $id);
        $select->where("AgencySysId = ?", $AgencySysId);

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

    public function getUserSysId($agencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_User", array("UserSysId"));
        $select->where("AgencySysId = $agencySysId");
        $select->order("UserSysId");
        $select->limit(1);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getGreetingMessage($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array("GreetingMessage"));
        $select->joinLeft(array('tb2' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb2.AgencySysId", array("B2BGreetingMessage", "IsB2CGreetingMessage", "IsB2BGreetingMessage"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkDuplicateLeadFromDayOne($agencySysId, $PrimaryContactNumber) {
//        $currentDate = date("Y-m-d");
//        $select = $this->db->select();
//        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array("AgencyCRMLeadSysId"));
//        $select->where("tb1.PrimaryContactNumber = ?", $PrimaryContactNumber);
//        $select->where("CONVERT(date,tb1.CreatedDate) = ?", $currentDate);
//        $select->where("tb1.AgencySysId = ?", $agencySysId);
//        $select->where("tb1.IsAuto = ?", 1);
//        $result = $this->db->fetchRow($select);
//        return $result;
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_CRM_LeadAccount"), array("AgencyCRMLeadSysId"));
        $select->where("tb1.PrimaryContactNumber = ?", $PrimaryContactNumber);
        $select->where("tb1.CreatedDate >= DATEADD(DAY, -6, CONVERT(date, GETDATE()))");
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsAuto = ?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkDuplicateQueryFromDayOne($agencySysId, $customerSysId) {
        // $currentDate = date("Y-m-d");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Query"), array("TPSysId", "AgentSysId"));
        $select->where("tb1.CustomerSysId = ?", $customerSysId);
        $select->where("tb1.CreatedDate >= DATEADD(DAY, -6, CONVERT(date, GETDATE()))");
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsAuto = ?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyMappingData($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Mapping"), array("*"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getActiveStaff($agencySysId, $IsB2BAgent = 0) {
        $select = $this->db->select();
        $select->from("TB_Agency_User", array('EmailId', 'UserSysId', 'LastName', 'FirstName'));
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsActive = ?", 1);
        $select->where("IsMarkForDel = ?", 0);
        $select->where("IsB2BAgent = ?", $IsB2BAgent);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function setDefaultCampaign($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Campaign_Master")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("CampaignName =?", 'Default');
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            $agencyCampaignData = array(
                'AgencySysId' => $AgencySysId,
                'CampaignName' => 'Default',
                'IsDefault' => 1,
                'IsActive' => 1,
                'IsMarkForDel' => 0
            );
            $this->db->insert('TB_Agency_Campaign_Master', $agencyCampaignData);
        }
    }

    public function setMarketingWallet($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Marketing_Wallet")
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            $agencyCampaignData = array(
                'AgencySysId' => $AgencySysId,
                'PerEmailPrice' => '.06',
                'TotalEmail' => '100',
                'CreateDate' => date("Y-m-d"),
                'IsMarkForDel' => 0,
                'UpdateDate' => date("Y-m-d"),
                'IsActive' => 1
            );
            $this->db->insert('TB_Agency_Marketing_Wallet', $agencyCampaignData);
        }
    }

    public function getDefaultCampaignByAgencyId($AgencySysId, $DefaultCampaign = 'Default') {
        $select = $this->db->select()
                ->from("TB_Agency_Campaign_Master")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("CampaignName =?", $DefaultCampaign);
        $result = $this->db->fetchRow($select);
        $defaultCampaignSysId = isset($result['CampSysId']) ? (int) $result['CampSysId'] : 0;
        return $defaultCampaignSysId;
    }

    public function getDefaultMediumByAgencyId($AgencySysId, $DefaultCampaign = 'Default') {
        $select = $this->db->select()
                ->from("TB_Agency_Medium_Master")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("CampaignName =?", $DefaultCampaign);
        $result = $this->db->fetchRow($select);
        $defaultCampaignSysId = isset($result['MediumSysId']) ? (int) $result['MediumSysId'] : 0;
        return $defaultCampaignSysId;
    }

    public function getTcsSettingsByAgencyId($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Tcs_Tax_Settings")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("IsActive =?", 1);
        $result = $this->db->fetchRow($select);
        $defaultTcs = isset($result['IsActive']) ? (int) $result['IsActive'] : 0;
        return $defaultTcs;
    }

    public function checkDefaultCampainSix($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_GTX_Lead_Page")
                ->where("LeadPageType = 6")
                ->where("AgencySysId =?", $AgencySysId);
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getActiveAgencySysId() {
        $select = $this->db->select()
                ->from("TB_Agency")
                ->where("IsActive = 1")
                ->where("MasterAgencySysId = '0'")
                ->where("IsMarkForDelete = '0'")
                ->where("IsApproved = 1");
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addDefaultCampainSix($AgencySysId) {
        $checkData = $this->checkDefaultCampainSix($AgencySysId);
        if (empty($checkData)) {
            $agencyData = $this->getAgencyDataById($AgencySysId);
            $getAddressAgency = $this->getAddressByAgency($AgencySysId);
            // echo "<pre>";print_r($agencyData);exit;
            $insertData = array(
                'AgencySysId' => $AgencySysId,
                'AgentSysId' => isset($agencyData['userId']) ? $agencyData['userId'] : 0,
                'AgencyName' => isset($agencyData['Title']) ? $agencyData['Title'] : '',
                'TemplatePageName' => "General Query Page",
                'BannerImageName' => '',
                'BannerTextMain' => '',
                'BannerTextSub' => '',
                'EnquiryFormHeading' => '',
                'EnquiryFormDescription' => '',
                'CountryCode' => isset($agencyData['DefaultCountryCode']) ? $agencyData['DefaultCountryCode'] : '',
                'ContactNumber' => isset($agencyData['PrimaryContactNo']) ? $agencyData['PrimaryContactNo'] : '',
                'EmailId' => isset($agencyData['PrimaryEmail']) ? $agencyData['PrimaryEmail'] : '',
                'Address' => isset($getAddressAgency['Address']) ? $getAddressAgency['Address'] : '',
                'PackageHeadingMain' => '',
                'PackageHeadingDescription' => '',
                'LeadPackageId' => '',
                'LeadPackageImageName' => '',
                'LeadPackageName' => '',
                'LeadPackagePrice' => '',
                'LeadPackageDay' => '',
                'LeadPackageHotels' => '',
                'LeadPackageSightseeings' => '',
                'LeadPackageInclusions' => '',
                'LeadPackagePax' => '',
                'LeadPackageTncInclusions' => '',
                'LeadAboutUs' => isset($agencyData['DefaultCountryCode']) ? $agencyData['DefaultCountryCode'] : '',
                'LeadSocialDescription' => '',
                'LeadSocialUrl' => '',
                'IsDisplay' => 0,
                'IsActive' => 1,
                'IsMarkForDel' => 0,
                'LeadTestimonialName' => '',
                'LeadTestimonialStar' => '',
                'LeadTestimonialDate' => '',
                'LeadTestimonialTitle' => '',
                'LeadTestimonialDescription' => '',
                'LeadFooterMainHeading' => '',
                'LeadFooterSection2' => '',
                'LeadFooterSection3' => '',
                'LeadPageType' => 6,
                'LeadPartnersImageName' => '',
                'LeadPackageOldPrice' => '',
                'LeadPackageQualityType' => '',
                'aboutUsIconName' => '',
                'companyExperienceIconName' => '',
                'LeadCompanyExperienceDesc' => '',
                'LeadMetaTitle' => '',
                'LeadMetaKeyword' => '',
                'LeadMetaDescription' => '',
                'LeadMetaHeaderScript' => '',
                'LeadMetaFooterScript' => '',
                'LeadPackageDetailUrl' => '',
                'LeadPackageCountry' => '',
                'IsFixedDeparturePackage' => '',
                'FixedDeparturePackageDate' => '',
                'LeadSourceSysId' => 0,
            );
            return $this->db->insert("TB_Agency_GTX_Lead_Page", $insertData);
        }
    }

    public function getAgencyDataFromMarketing($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency", array('Title', 'Logo', 'EmailKey'))
                ->where("IsActive = 1")
                ->where("MasterAgencySysId = '0'")
                ->where("IsMarkForDelete = '0'")
                ->where("AgencySysId = $AgencySysId")
                ->where("IsApproved = 1");
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappMessageTemplateWise($agencySysId, $templateType = null, $IsDefault = null) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_WhatsAppEmail_Template", array('*')));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        if ($templateType) {
            $select->where("tb1.TemplateType = ?", 1);
            if ($IsDefault) {
                $select->where("tb1.IsDefault = ?", 1);
            } else {
                $select->where('tb1.IsDefault = 0 OR tb1.IsDefault is NULL');
            }
        } else {
            $select->where('tb1.TemplateType = 0 OR tb1.TemplateType is NULL');
        }
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.Id DESC");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappMessage($agencySysId, $id = '') {
        $select = $this->db->select();
        $select->from("TB_Agency_WhatsAppEmail_Template", array('*'));
        $select->where("AgencySysId = ?", $agencySysId);
        if ($id != '') {
            $select->where("Id = ?", $id);
        }
        $select->where("IsMarkForDel = ?", 0);
        $select->order("Id DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappCarouselMessage($agencySysId, $id = '', $OnlyMasterId = 0) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_WhatsAppEmail_Template", array('*')));
        $select->joinleft(array('tb2' => "TB_Agency_Facebook_Template_Header"), "tb1.Id = tb2.TemplateSysId", array("TemplateHeaderSysId", "HeaderType as VarHeaderType", "HeaderValue", "MappingFromFB", "HeaderValueReference", "HeaderValueText"));
        $select->where("AgencySysId = ?", $agencySysId);
        if (!empty($id)) {
            if ($OnlyMasterId == 1) {
                $select->where("MasterId = ?", $id);
            } else {
                $select->where("Id = $id OR MasterId = $id");
            }
        }
        $select->where("IsMarkForDel = ?", 0);
        $select->order("Id ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

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

        $select->from(array('tb1' => "TB_Agency_WhatsAppEmail_Template", array('*')));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsDefault = ?", 0);
        $select->where("TemplateType = 1 OR TemplateType = 2");
        $select->where("MasterId is NULL");
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.Id DESC");

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

    public function getOnlyWhatsappAPITemplateWithVariable($agencySysId, $status = null) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_WhatsAppEmail_Template"), array('*'));
        $select->joinLeft(array('tb2' => "TB_Agency_Facebook_Template_Header"), "tb1.Id = tb2.TemplateSysid", array('TemplateHeaderSysId', 'HeaderType as VariableHeaderType', 'HeaderValue', 'MappingFromFB', 'HeaderValueReference', 'HeaderValueText'));
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsDefault = ?", 0);
        $select->where("TemplateType = 1 OR TemplateType = 2");
        $select->where("MasterId is NULL");
        $select->where("IsMarkForDel = ?", 0);
        if ($status) {
            $select->where("TemplateStatus = ?", 'APPROVED');
        }
        $select->order("Id DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappMessageVariable($id) {
        $select = $this->db->select();
        $select->from("TB_Agency_Facebook_Template_Header", array('*'));
        $select->where("TemplateSysId = ?", $id);
        $select->order("TemplateHeaderSysId ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappMessageByName($agencySysId, $TemplateName) {
        $select = $this->db->select();
        $select->from("TB_Agency_WhatsAppEmail_Template", array('Id', 'TemplateName'));
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("TemplateName = ?", $TemplateName);
        $select->where("IsMarkForDel = ?", 0);
        $select->order("Id DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getSupportContact($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Support_Contact");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsMarkForDelete =?", 0);
        //        $select->order("UpdateDate ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkSupportContact($AgencySysId, $PlanType, $MarketType = 0, $ItemSourceType = 0, $IsMarkForDelete = 0) {
        $select = $this->db->select();
        $select->from("TB_Agency_Support_Contact");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("PlanType =?", $PlanType);
        if ($MarketType) {
            $select->where("MarketType =?", $MarketType);
        }
        if ($ItemSourceType) {
            $select->where("ItemSourceType =?", $ItemSourceType);
        }

        if ($IsMarkForDelete == 1) {
            $select->where("IsMarkForDelete =?", 0);
        }

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

    public function getAgencyData($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency", array("DisplayName", "Logo", "AgencySysId", "PrimaryEmail", "PrimaryMobileNo", "countrycode2", "TaxLabelId", "SecurityKey", "RMUserSysId", "AgencyType"));
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsActive =?", 1);
        $select->where("IsMarkForDelete =?", 0);
        $select->where("IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAgencyCreditLimitOtp($CreditSysId, $HistoryCreditSysId = NULL) {
        $CreditArray = array('AgencySysId', 'CreditSysId');
        $CreditHistoryArray = array('OTPCode', 'CodeValidFrom', 'CodeValidTo', 'TrxType', 'IsCredit', 'IsOneTime');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
        $select->where("tb1.CreditSysId =?", $CreditSysId);
        if ($HistoryCreditSysId) {
            $select->where("tb2.HistoryCreditSysId =?", $HistoryCreditSysId);
        }
        $select->where("tb2.IsActive =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.HistoryCreditSysId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAgencyBNPLCreditLimitOtp($CreditSysId, $HistoryCreditSysId = NULL) {
        $CreditArray = array('AgencySysId', 'CreditSysId');
        $CreditHistoryArray = array('OTPCode', 'CodeValidFrom', 'CodeValidTo', 'TrxType', 'IsCredit', 'IsOneTime');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_BNPL_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_BNPL_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
        $select->where("tb1.CreditSysId =?", $CreditSysId);
        if ($HistoryCreditSysId) {
            $select->where("tb2.HistoryCreditSysId =?", $HistoryCreditSysId);
        }
        $select->where("tb2.IsActive =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.HistoryCreditSysId DESC");
        // echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkB2CCustomerCreditLimitOtp($WalletSysId, $HistoryWalletSysId = NULL) {
        $CreditArray = array('AgencySysId', 'WalletSysId');
        $CreditHistoryArray = array('OTPCode', 'CodeValidFrom', 'CodeValidTo', 'TrxType', 'IsCredit', 'IsOneTime');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_B2C_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_B2C_Wallet_History"), "tb1.WalletSysId = tb2.WalletSysId", $CreditHistoryArray);
        $select->where("tb1.WalletSysId =?", $WalletSysId);
        if ($HistoryWalletSysId) {
            $select->where("tb2.HistoryWalletSysId =?", $HistoryWalletSysId);
        }
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyCreditLimitDataByAgencyIdwithids($AgencySysId, $isCredit = 0, $Isfetched = 0, $IsActive = 0) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remark', 'TrxType', 'Amount', 'CreditAmount as historyCreditAmount', 'BalanceAmount as historyBalanceAmount', 'IsCredit', 'HistoryCreditSysId', 'RefrenceNo', 'PlanType', 'PaymentMode', 'HistoryCreditSysId', 'IsActive');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb1.AgencySysId = tb3.AgencySysId", array('Title as Agencyname'));
        //        $select->joinLeft(array('tb4' => "TB_Agency_Customer_Trx"), "tb2.HistoryCreditSysId = tb4.TPSysId", array(''));
        //        $select->joinLeft(array('tb5' => "TB_Agency_Wallet_Trx"), "tb4.TrxSysId = tb5.TrxId", array('ApprovedBy'));
        //        $select->joinLeft(array('tb6' => "TB_Agency_User"), "tb5.ApprovedBy = tb6.UserSysId", array('FirstName','LastName'));
        if ($AgencySysId != '') {
            $select->where("tb1.AgencySysId IN (?)", $AgencySysId);
        }
        if ($isCredit == 3) {
            $select->where("tb2.isCredit =?", 2);
            $select->where("tb2.PaymentMode != ?", 'Wallet Refund');
            $select->where("tb2.PlanType =?", 7);
            $select->where("tb2.TrxType =?", 1);
        } else {
            if ($Isfetched == 1) {
                $select->where("tb2.Isfetched =?", 0);
            }
            if ($isCredit == 2) {
                $select->where("tb2.isCredit =?", 2);
                $select->where("tb2.IsActive =?", '0');
                $select->where("tb2.PlanType =?", 7);
                $select->where("tb2.TrxType =?", 1);
            } else {
                $select->where("tb2.IsActive =?", 1);
            }
        }


        if (isset($this->filterData['agencyname']) && !empty($this->filterData['agencyname'])) {
            $select->Where('tb3.Title LIKE ?', '%' . $this->filterData['agencyname'] . '%');
        }
        if (isset($this->filterData['amount']) && !empty($this->filterData['amount'])) {
            $select->Where('tb2.Amount =?', $this->filterData['amount']);
        }
        if (isset($this->filterData['refno']) && !empty($this->filterData['refno'])) {
            $select->Where('tb2.RefrenceNo =?', $this->filterData['refno']);
        }
        if (isset($this->filterData['type']) && !empty($this->filterData['type'])) {
            $select->Where('tb2.TrxType =?', $this->filterData['type']);
        }
        if (isset($this->filterData['IsActive']) && $this->filterData['IsActive'] != '') {
            $select->Where('tb2.IsActive =?', $this->filterData['IsActive']);
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.UpdateDate DESC");
        //$select->order("tb2.UpdateDate DESC");
        //        echo $select;die;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyWalletHistoryAgencyId($AgencySysId) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('*');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Wallet_History"), "tb1.WalletSysId = tb2.WalletSysId", $CreditHistoryArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb2.AgencySysId =?", $AgencySysId);
        $select->order("tb2.UpdateDate DESC");
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyBNPLCreditLimitDataByAgencyId($AgencySysId, $isCredit = 0, $Isfetched = 0) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remark', 'TrxType', 'Amount', 'CreditAmount as historyCreditAmount', 'BalanceAmount as historyBalanceAmount', 'IsCredit', 'HistoryCreditSysId', 'RefrenceNo', 'PlanType', 'PaymentMode', 'HistoryCreditSysId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_BNPL_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_BNPL_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb1.AgencySysId = tb3.AgencySysId", array('Title as Agencyname'));
        if ($AgencySysId != '') {
            $select->where("tb1.AgencySysId =?", $AgencySysId);
        }
        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
            $select->Where('CONVERT(date,tb2.UpdateDate) >= ?', $this->filterArray['FromDate']);
            $select->Where('CONVERT(date,tb2.UpdateDate) <= ?', $this->filterArray['ToDate']);
        }
        if ($Isfetched == 1) {
            $select->where("tb2.Isfetched =?", 0);
        }
        if ($isCredit == 2) {
            $select->where("tb2.isCredit =?", 2);
            $select->where("tb2.IsActive =?", '0');
            $select->where("tb2.PlanType =?", 7);
            $select->where("tb2.TrxType =?", 1);
        } else {
            $select->where("tb2.IsActive =?", 1);
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.UpdateDate DESC");

        //$select->order("tb2.UpdateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyCreditLimitDataByAgencyIdNew($AgencySysId, $PageSize, $PageNumber, $FromDate, $ToDate) {
        $querySet = "GetAgencyCreditHistory $AgencySysId,'$FromDate','$ToDate',$PageNumber,$PageSize";
        $statement = $this->db->query($querySet);
        return $resultSet1 = $statement->fetchAll();
    }

    public function getFranchiseeCommissionProvisionalReport($AgencySysId, $paid = null) {
        $CreditArray = array(
            'CommSysId',
            'Paid',
            'TPSysId',
            'Amount',
            'AdjustedCommission AS FinalCommission',
            '(select SUM(NetAmount) from TB_Agency_Customer_Trx as tb3 where tb1.TPSysId = tb3.TPSysId AND tb3.IsActive = 1 AND tb3.TrxStatus = 1) AS NetAmount',
            '(select SUM(Tax) from TB_Agency_Customer_TravelPlan_AddonServices as tb4 where tb1.TPSysId = tb4.TPSysId AND tb4.IsActive = 1 AND (tb4.IsTotalCostInPackage is NULL)) AS Tax'
                //  '(select SUM(TotalCost) from TB_Agency_Customer_TravelPlan_AddonServices as tb5 where tb1.TPSysId = tb5.TPSysId AND (tb5.PlanType = 22)) AS ServiceFee',
        );
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Commission"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb1.TPSysId = tb2.TPSysId", array("ApproveDate", "RoomInfoJson", "StartDate", "Title", "ValidTill"));
        $select->joinLeft(array('tb3' => "TB_Master_TravelPlan_Status"), "tb2.StatusType = tb3.TPStatusSysId", array("TPStatus AS PaymenyStatus"));
        // $select->joinLeft(array('tb4' => "TB_Master_TravelPlan_Status"), "tb2.BookingStatus = tb4.TPStatusSysId", array("TPStatus AS BookingStatus"));
        $select->where("tb2.StatusType !=?", 15);
        $select->where("tb1.FranchiseSysId =?", $AgencySysId);
        if ($paid) {
            $select->where("tb1.Paid =?", $paid);
        }
        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
            $ToDate = date('Y-m-d', strtotime($this->filterArray['ToDate']));
            $FromDate = date('Y-m-d', strtotime($this->filterArray['FromDate']));
            $select->where("CONVERT(date,(tb1.CreateDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }
        $select->order("CommSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getFranchiseeCommissionProvisionalReportForB2b($AgencySysId, $pageNumber, $setItem, $paid = 0) {
        $querySet = "GET_Franchisee_Commission_Data $AgencySysId,$pageNumber,$setItem,$paid";
        $statement = $this->db->query($querySet);
        $resultSetProposal = $statement->fetchAll();
        $finalResultSet['Result1'] = array();
        foreach ($resultSetProposal as $row) {
            $finalResultSet['Result1'][] = $row;
        }
        $statement->nextRowSet();
        $resultSet2 = $statement->fetchAll();
        $finalResultSet['Result2'] = array();
        foreach ($resultSet2 as $row) {
            $finalResultSet['Result2'] = $row;
        }


        return $finalResultSet;
    }

    public function getFranchiseePaymentReportIDWise($CommInvSysId) {
        $CreditArray = array(
            'CommInvSysId',
            'Commission',
            'FinalCommission',
            'Remark',
            'Status',
            'CGST',
            'SGST',
            'IGST',
            'TDS',
            'InvoiceNumber',
            'InvoiceDate',
            '(SELECT tb1.CommSysId, tb1.Paid, tb1.TPSysId, tb1.Amount, tb1.AdjustedCommission AS FinalCommission, (select SUM(NetAmount) from TB_Agency_Customer_Trx as tb3 where tb1.TPSysId = tb3.TPSysId AND tb3.IsActive = 1 AND tb3.TrxStatus = 1) AS NetAmount, (select SUM(Tax) from TB_Agency_Customer_TravelPlan_AddonServices as tb4 where tb1.TPSysId = tb4.TPSysId AND tb4.IsActive = 1) AS Tax, tb2.ApproveDate, "tb2"."RoomInfoJson", tb2.StartDate, tb2.Title, tb2.ValidTill, tb3.TPStatus AS PaymenyStatus FROM TB_Agency_Commission AS tb1 LEFT JOIN TB_Agency_Customer_TravelPlan AS tb2 ON tb1.TPSysId = tb2.TPSysId LEFT JOIN TB_Master_TravelPlan_Status AS tb3 ON tb2.StatusType = tb3.TPStatusSysId WHERE t4.CommInvSysId =  tb1.CommInvSysId FOR JSON PATH ) AS CommissionArr'
        );
        $select = $this->db->select();
        $select->from(array('t4' => "Tb_Agency_Commission_Invoice"), $CreditArray);
        $select->where("t4.Status = '0' OR t4.Status is Null");
        $select->where("t4.CommInvSysId =?", $CommInvSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getFranchiseePaymentReport($AgencySysId, $paid = 0) {
        $CreditArray = array(
            'CommInvSysId',
            'FinalCommission',
            'Remark',
            'Status',
            'CGST',
            'SGST',
            'IGST',
            'TDS',
            'InvoiceNumber',
            'InvoiceDate',
        );
        $select = $this->db->select();
        $select->from(array('t4' => "Tb_Agency_Commission_Invoice"), $CreditArray);
        if ($paid == 1) {
            $select->where("t4.Status = 1");
        } else {
            $select->where("t4.Status = '0' OR t4.Status is Null");
        }
        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
            $ToDate = date('Y-m-d', strtotime($this->filterArray['ToDate']));
            $FromDate = date('Y-m-d', strtotime($this->filterArray['FromDate']));
            $select->where("CONVERT(date,(t4.InvoiceDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }
        $select->where("t4.FranchiseSysId =?", $AgencySysId);
        //        echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyCreditLimitDataByAgencyId($AgencySysId, $isCredit = 0, $Isfetched = 0) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remark', 'TrxType', 'Amount', 'CreditAmount as historyCreditAmount', 'BalanceAmount as historyBalanceAmount', 'IsCredit', 'HistoryCreditSysId', 'RefrenceNo', 'PlanType', 'PaymentMode', 'HistoryCreditSysId');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb1.AgencySysId = tb3.AgencySysId", array('Title as Agencyname'));
        if ($AgencySysId != '') {
            $select->where("tb1.AgencySysId =?", $AgencySysId);
        }
        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
            $select->Where('CONVERT(date,tb2.UpdateDate) >= ?', $this->filterArray['FromDate']);
            $select->Where('CONVERT(date,tb2.UpdateDate) <= ?', $this->filterArray['ToDate']);
        }
        if ($Isfetched == 1) {
            $select->where("tb2.Isfetched =?", 0);
        }
        if ($isCredit == 2) {
            $select->where("tb2.isCredit =?", 2);
            $select->where("tb2.IsActive =?", '0');
            $select->where("tb2.PlanType =?", 7);
            $select->where("tb2.TrxType =?", 1);
        } else {
            $select->where("tb2.IsActive =?", 1);
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.UpdateDate DESC");

        //$select->order("tb2.UpdateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    //    public function getAgencyBnplCreditLimitDataByAgencyId($AgencySysId, $isCredit = 0, $Isfetched = 0) {
    //        $CreditArray = array('*');
    //        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remark', 'TrxType', 'Amount', 'CreditAmount as historyCreditAmount', 'BalanceAmount as historyBalanceAmount', 'IsCredit', 'HistoryCreditSysId', 'RefrenceNo', 'PlanType', 'PaymentMode', 'HistoryCreditSysId');
    //        $select = $this->db->select();
    //        $select->from(array('tb1' => "TB_Agency_BNPL_Credit"), $CreditArray);
    //        $select->joinLeft(array('tb2' => "TB_Agency_BNPL_Credit_History"), "tb1.CreditSysId = tb2.CreditSysId", $CreditHistoryArray);
    //        $select->joinLeft(array('tb3' => "TB_Agency"), "tb1.AgencySysId = tb3.AgencySysId", array('Title as Agencyname'));
    //        if ($AgencySysId != '') {
    //            $select->where("tb1.AgencySysId =?", $AgencySysId);
    //        }
    //        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
    //            $select->Where('CONVERT(date,tb2.UpdateDate) >= ?', $this->filterArray['FromDate']);
    //            $select->Where('CONVERT(date,tb2.UpdateDate) <= ?', $this->filterArray['ToDate']);
    //        }
    //        if ($Isfetched == 1) {
    //            $select->where("tb2.Isfetched =?", 0);
    //        }
    //        if ($isCredit == 2) {
    //            $select->where("tb2.isCredit =?", 2);
    //            $select->where("tb2.IsActive =?", '0');
    //            $select->where("tb2.PlanType =?", 7);
    //            $select->where("tb2.TrxType =?", 1);
    //        } else {
    //            $select->where("tb2.IsActive =?", 1);
    //        }
    //        $select->where("tb1.IsMarkForDel =?", 0);
    //        $select->where("tb2.IsMarkForDel =?", 0);
    //        $select->order("tb2.UpdateDate DESC");
    //
    //        //$select->order("tb2.UpdateDate DESC");
    //        $result = $this->db->fetchAll($select);
    //        return $result;
    //    }

    public function getAgencyCreditHistoryDataById($CreditSysId, $HistoryCreditSysId) {
        $CreditHistoryArray = array('*');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array("Title", "DisplayName"));
        $select->joinLeft(array('tb2' => "TB_Agency_Credit_History"), "tb1.AgencySysId = tb2.AgencySysId", $CreditHistoryArray);
        $select->where("tb2.HistoryCreditSysId =?", $HistoryCreditSysId);
        $select->where("tb2.CreditSysId =?", $CreditSysId);
        $select->where("tb2.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkB2CWallet($AgencySysId, $CreditSysId, $customerSysId) {
        $CreditArray = array("CreditAmount", "CreditDays", "BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_B2C_Wallet"), $CreditArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.WalletSysId =?", $CreditSysId);
        $select->where("tb1.CustomerSysId =?", $customerSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkCreditBalance($AgencySysId, $CreditSysId) {
        $CreditArray = array("CreditAmount", "CreditDays", "BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getB2BAgencySysId($SecurityKey) {
        $CreditArray = array("AgencySysId", "MasterAgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Credit"), "tb1.AgencySysId = tb2.AgencySysId", array("CreditSysId"));
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getBNPLAgencySysId($SecurityKey) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_BNPL_Credit"), "tb1.AgencySysId = tb2.AgencySysId", array("CreditSysId"));
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCreditBalance($CreditSysId, $AgencySysId) {
        $CreditArray = array("BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        $getBalance = isset($result['BalanceAmount']) ? $result['BalanceAmount'] : 0;
        return $getBalance;
    }

    public function getBNPLCreditBalance($CreditSysId, $AgencySysId) {
        $CreditArray = array("BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_BNPL_Credit"), $CreditArray);
        $select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        $getBalance = isset($result['BalanceAmount']) ? $result['BalanceAmount'] : 0;
        return $getBalance;
    }

    public function getCreditBalanceFromB2CWallet($WalletSysId, $AgencySysId) {
        $CreditArray = array("BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_B2C_Wallet"), $CreditArray);
        $select->where("tb1.WalletSysId =?", $WalletSysId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        $getBalance = isset($result['BalanceAmount']) ? $result['BalanceAmount'] : 0;
        return $getBalance;
    }

    public function checkOfflineData($CreditSysId, $HistoryCreditSysId, $AgencySysId) {
        $CreditHistoryArray = array('*');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), array());
        $select->joinLeft(array('tb2' => "TB_Agency_Credit_History"), "tb1.AgencySysId = tb2.AgencySysId", $CreditHistoryArray);
        $select->where("tb2.HistoryCreditSysId =?", $HistoryCreditSysId);
        $select->where("tb2.CreditSysId =?", $CreditSysId);
        $select->where("tb2.AgencySysId =?", $AgencySysId);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->where("tb2.IsActive =?", 0);
        $select->where("tb2.IsCredit =?", 2);
        //$select->where("tb2.TrxType =?", 1);
        $select->where("tb2.PlanType =?", 7);
        $select->where("tb1.IsActive =?", 1);
        // echo $select;exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getB2bAgencyMarkup($markupSysId, $agencySysId, $default, $PlanType = 0, $ItemSourceType = 0) {
        $CreditHistoryArray = array("FlexiBookingPayCancelBefore", "AdultFlexiCostFD", "ChildFlexiCostFD", "InfantFlexiCostFD", "AdultFlexiCostFI", "ChildFlexiCostFI", "InfantFlexiCostFI", "IsFlexiPayment", "MinDepositAmount", "ProcessingFee", "FlexiBookingActiveBefore", "MaximumFlexiCreditLimit", "ProcessingFeeType", "IsMarkupDiscount", "MarketPlaceSysId", "MasterId", "IsDefault", "Title", "MarkUpType", "MarkUpValue", "PlanType", "ItemSourceType", "Commission", "TDS", "FlexiBookingActiveTo", "RewardsPoints");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MarketPlace"), $CreditHistoryArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Addresses"), "tb2.AgencySysId = tb1.AgencySysId", array("CountrySysId"));
        $select->joinLeft(array('tb3' => "TB_Agency"), "tb3.AgencySysId = tb1.AgencySysId", array("TrxCurrency as Currency"));
        $select->joinLeft(array('tb4' => "TB_Master_Currency"), "tb4.CurrencyType = tb3.TrxCurrency", array("Symbol"));
        $select->where("tb1.AgencySysId =?", $agencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        if ($PlanType > 0) {
            $select->where("tb1.PlanType =?", $PlanType);
        }
        if ($ItemSourceType > 0) {
            $select->where("tb1.ItemSourceType =?", $ItemSourceType);
        }
        if ($default == 2) {
            $select->where("tb1.IsDefaultB2C =?", 1);
        } else if ($default == 1) {
            $select->where("tb1.IsDefault =?", 1);
        } else {
            $whereCondition = " tb1.MasterId = $markupSysId OR tb1.MarketPlaceSysId = $markupSysId";
            $select->where($whereCondition);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getMarkupForB2CAgency($B2CAgencySysIdKey, $PlanType, $IsB2CLoggedIn) {
        $CreditHistoryArray = array("IsFlexiPayment", "MinDepositAmount", "ProcessingFee", "FlexiBookingActiveBefore", "MaximumFlexiCreditLimit", "ProcessingFeeType", "IsMarkupDiscount", "MarketPlaceSysId", "MasterId", "IsDefault", "Title", 'IsMarkupDiscount', "MarkUpType", "MarkUpValue", "PlanType", "ItemSourceType", "Commission", "TDS");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array("TrxCurrency as Currency"));
        //$select->joinLeft(array('tb2' => "TB_Agency_Addresses"), "tb2.AgencySysId = tb1.AgencySysId", array("CountrySysId"));
        $select->joinLeft(array('tb3' => "TB_Agency_MarketPlace"), "tb3.AgencySysId = tb1.AgencySysId", $CreditHistoryArray);
        $select->joinLeft(array('tb4' => "TB_Master_Currency"), "tb4.CurrencyType = tb1.TrxCurrency", array("LTRIM(RTRIM(Symbol)) AS Symbol"));
        $select->where("tb1.SecurityKey =?", $B2CAgencySysIdKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb3.IsActive =?", 1);
        $select->where("tb3.IsMarkForDelete =?", 0);
        $select->where("tb1.IsMarkForDelete =?", 0);
        if ($PlanType > 0) {
            $select->where("tb3.PlanType =?", $PlanType);
        }
        if ($IsB2CLoggedIn == 1) {
            $select->where("tb3.IsDefaultLoggedinB2C =?", $IsB2CLoggedIn);
        } else {
            $select->where("tb3.IsDefaultB2C =?", 1);
        }

        //        if ($ItemSourceType > 0) {
        //            $select->where("tb1.ItemSourceType =?", $ItemSourceType);
        //        }
        //        if ($default == 1) {
        //            $select->where("tb1.IsDefault =?", 1);
        //        } else {
        //            $whereCondition = " tb1.MasterId = $markupSysId OR tb1.MarketPlaceSysId = $markupSysId";
        //            $select->where($whereCondition);
        //        }
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getMarkupForB2bAgency($B2BAgencySysIdKey, $B2CAgencySysIdKey, $PlanType = 0, $ItemSourceType = 0) {
        $CreditArray = array("AgencyMarketPlaceSysId", "MasterAgencySysId", "FirmType", "IsPaid");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->where("tb1.SecurityKey =?", $B2BAgencySysIdKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        $returnSetArray = array();
        if (!empty($result)) {
            $AgencyMarketPlaceSysId = isset($result[0]['AgencyMarketPlaceSysId']) ? (int) $result[0]['AgencyMarketPlaceSysId'] : 0;
            $FirmType = isset($result[0]['FirmType']) ? (int) $result[0]['FirmType'] : 0;
            $IsPaid = isset($result[0]['IsPaid']) ? (int) $result[0]['IsPaid'] : 0;
            $MasterAgencySysId = isset($result[0]['MasterAgencySysId']) ? (int) $result[0]['MasterAgencySysId'] : 0;
            if ($AgencyMarketPlaceSysId != 0) {
                $default = False;
                $returnArray = $this->getB2bAgencyMarkup($AgencyMarketPlaceSysId, $MasterAgencySysId, $default, $PlanType, $ItemSourceType);
            } else {
                $default = True;
                $returnArray = $this->getB2bAgencyMarkup($AgencyMarketPlaceSysId, $MasterAgencySysId, $default, $PlanType, $ItemSourceType);
            }
            if (!empty($returnArray)) {
                $r = 0;
                foreach ($returnArray as $returnSetVal) {
                    $returnSetArray[$r] = $returnSetVal;
                    if (($FirmType == 5 && $IsPaid == 1) && ($returnSetArray[$r]['IsFlexiPayment'] == 1)) {
                        $returnSetArray[$r]['IsFlexiPayment'] = 1;
                    } else {
                        $returnSetArray[$r]['IsFlexiPayment'] = 0;
                    }
                    $r++;
                }
            }
        }
        return $returnSetArray;
    }

    public function getBankDetail($B2BAgencySysIdKey, $B2CAgencySysIdKey) {
        $CreditArray = array();
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_BankDetails"), "tb1.AgencySysId = tb2.AgencySysId");
        $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"));
        $select->where("tb1.SecurityKey =?", $B2CAgencySysIdKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSupportContactForB2b($B2BAgencySysIdKey, $B2CAgencySysIdKey) {
        $CreditArray = array();
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Support_Contact"), "tb1.AgencySysId = tb2.AgencySysId", array("PlanType", "MarketType", "ItemSourceType", "IsBookingEmail"));
        $select->joinLeft(array('tb3' => "TB_Agency_User"), "tb2.UserSysId = tb3.UserSysId", array("FirstName", "LastName", "Gender", "EmailId", "ContactNo1", "ContactNo2", "countrycode", "countrycode2"));
        $select->where("tb1.SecurityKey =?", $B2CAgencySysIdKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb2.IsMarkForDelete =?", 0);
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.IsApproved =?", 1);
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkAgencyId($AgencySysId, $SecurityKey) {
        $CreditArray = array("AgencySysId", "TrxCurrency");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAgency($SecurityKey) {
        $CreditArray = array("AgencySysId", "IVKEY", "Title", "IsCheckWallet", "TrxCurrency", "MasterAgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkFireBaseToken($AgencySysId, $UserSysId) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_User_Fire_Base_Token"), $CreditArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.UserSysId =?", $UserSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAndGetFireBaseToken($SecurityKey, $UserSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array());
        $select->joinLeft(array('tb3' => "Tb_Agency_User_Fire_Base_Token"), "tb1.AgencySysId = tb3.AgencySysId AND tb3.UserSysId = $UserSysId", array("Title"));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgentSysIdFromTpSysId($TpSysId) {
        $result = 0;
        if ((int) $TpSysId > 0) {
            $CreditArray = array("AgentSysId");
            $select = $this->db->select();
            $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), $CreditArray);
            $select->where("tb1.TPSysId =?", $TpSysId);
            $resultArray = $this->db->fetchRow($select);
            $result = isset($resultArray['AgentSysId']) ? (int) $resultArray['AgentSysId'] : 0;
        }
        return $result;
    }

    public function getCallCenterAPIKey($agencySysId) {
        $CreditArray = array("Type", "AccessToken");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Call_Center_Setting"), $CreditArray);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb1.AgencySysId =?", $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAndGetWhatsAppDetailByAgency($SecurityKey) {
        $CreditArray = array("AgencySysId", "IVKEY");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb3' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb3.AgencySysId", array());
        $select->joinLeft(array('tb2' => "TB_Agency_WhatsApp_Setting"), "tb1.AgencySysId = tb2.AgencySysId", array("AccessToken", "APIURL"));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.SecurityKey =?", $SecurityKey);
        $select->where("tb1.IsApproved =?", 1);
        $select->where("tb3.IsWhatsApp =?", 1);
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDel =?", 0);
        //echo $select;
        //exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAgencyFromNickId($NickId) {
        $CreditArray = array("AgencySysId", "PrimaryEmail", "PrimaryUserSysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.NickId =?", $NickId);
        // echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAndUpdateB2BWallet($AgencySysId, $userSysId, $BalanceAmount) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        $currentDate = date("Y-m-d H:i");
        if (is_array($result) && (isset($result['AgencySysId']) && $result['AgencySysId'] == $AgencySysId)) {
            $updatedArray = array(
                'BalanceAmount' => (float) $BalanceAmount,
            );
            $where = array(
                "AgencySysId=(?)" => $AgencySysId,
                "UserSysId = (?)" => $userSysId,
            );
            $this->updateData('TB_Agency_Credit', $updatedArray, $where);
        } else {
            $creditArray = array(
                'AgencySysId' => $AgencySysId,
                'CurrencyType' => 1,
                'UserSysId' => $userSysId,
                'BalanceAmount' => (float) $BalanceAmount,
                'CreditDays' => 0,
                'CreditDate' => $currentDate,
                'CreateDate' => $currentDate,
                'UpdateDate' => $currentDate,
                'IsMarkForDel' => 0,
                'IsActive' => 1,
                'CreditAmount' => 0
            );
            $CreditSysId = $this->insertData("TB_Agency_Credit", $creditArray);
        }
    }

    public function checkAgencyWallet($AgencySysId, $userSysId, $TrxCurrency = 1) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        $currentDate = date("Y-m-d H:i");
        if (is_array($result) && (isset($result['AgencySysId']) && $result['AgencySysId'] == $AgencySysId)) {
            
        } else {
            $creditArray = array(
                'AgencySysId' => $AgencySysId,
                'CurrencyType' => $TrxCurrency,
                'UserSysId' => $userSysId,
                'BalanceAmount' => 0,
                'CreditDays' => 0,
                'CreditDate' => $currentDate,
                'CreateDate' => $currentDate,
                'UpdateDate' => $currentDate,
                'IsMarkForDel' => 0,
                'IsActive' => 1,
                'CreditAmount' => 0
            );

            $CreditSysId = $this->insertData("TB_Agency_Credit", $creditArray);
            $creditHistoryArray = array(
                "AgencySysId" => $AgencySysId,
                "CreditSysId" => $CreditSysId,
                "CurrencyType" => $TrxCurrency,
                "CreditAmount" => 0,
                "BalanceAmount" => 0,
                "CreditDays" => 0,
                "CreditDate" => $currentDate,
                "UserSysId" => $userSysId,
                "Remark" => "Welcome",
                "OTPCode" => '',
                "IsCredit" => 1,
                "PlanType" => 7,
                "CodeValidFrom" => $currentDate,
                "CodeValidTo" => $currentDate,
                "CreateDate" => $currentDate,
                "UpdateDate" => $currentDate,
                "PaymentMode" => "Start Account",
                "IsMarkForDel" => 0,
                "IsActive" => 1,
                "TrxType" => 1,
                "Amount" => 0,
                "TrxId" => ''
            );
            try {
                $this->insertData("TB_Agency_Credit_History", $creditHistoryArray);
            } catch (Zend_Exception $e) {
                echo $e->getMessage();
                exit;
            }
        }
    }

    public function addDefaultTax($AgencySysId) {
        $dafultTaxArray = array(3 => "No Tax", 4 => "Tax Extra w/o text", 5 => "Tax Extra with text");
        $select = $this->db->select()
                ->from("TB_Master_Agency_Tax_Settings")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("IsMarkForDel =?", 0)
                ->where("IsActive =?", 1)
                ->where("IsDefault =?", 1)
                ->where("PlanType =?", 5)
                ->where("IsDefaultId IN (?)", array(3, 4, 5));
        $result = $this->db->fetchAll($select);
        $checkdata = array();
        if (!empty($result)) {
            foreach ($result as $resultVal) {
                if (!in_array($resultVal['IsDefaultId'], $checkdata)) {
                    $checkdata[] = $resultVal['IsDefaultId'];
                }
            }
            if (!in_array(3, $checkdata)) {
                $saveData = array(
                    "AgencySysId" => $AgencySysId,
                    "TaxName" => "No Tax",
                    "TaxPercentage" => 0,
                    "TaxType" => 3,
                    "DefaultSelect" => 0,
                    "DefaultDisplay" => 0,
                    "IfCaasApplicable" => 0,
                    "CaasApplicableTaxPercentage" => 0,
                    "CaasApplicableTaxType" => 0,
                    "IsActive" => 1,
                    "IsMarkForDel" => 0,
                    "IsOrder" => 3,
                    "IsDefault" => 1,
                    "IsDefaultId" => 3,
                    "PlanType" => 5,
                    "Tax" => 1
                );
                $this->db->insert('TB_Master_Agency_Tax_Settings', $saveData);
            } else if (!in_array(4, $checkdata)) {
                $saveData = array(
                    "AgencySysId" => $AgencySysId,
                    "TaxName" => "Tax Extra w/o text",
                    "TaxPercentage" => 0,
                    "TaxType" => 3,
                    "DefaultSelect" => 0,
                    "DefaultDisplay" => 0,
                    "IfCaasApplicable" => 0,
                    "CaasApplicableTaxPercentage" => 0,
                    "CaasApplicableTaxType" => 0,
                    "IsActive" => 1,
                    "IsMarkForDel" => 0,
                    "IsOrder" => 4,
                    "IsDefault" => 1,
                    "IsDefaultId" => 4,
                    "PlanType" => 5,
                    "Tax" => 1
                );
                $this->db->insert('TB_Master_Agency_Tax_Settings', $saveData);
            } else if (!in_array(5, $checkdata)) {
                $saveData = array(
                    "AgencySysId" => $AgencySysId,
                    "TaxName" => "Tax Extra with text",
                    "TaxPercentage" => 0,
                    "TaxType" => 3,
                    "DefaultSelect" => 0,
                    "DefaultDisplay" => 0,
                    "IfCaasApplicable" => 0,
                    "CaasApplicableTaxPercentage" => 0,
                    "CaasApplicableTaxType" => 0,
                    "IsActive" => 1,
                    "IsMarkForDel" => 0,
                    "IsOrder" => 5,
                    "IsDefault" => 1,
                    "IsDefaultId" => 5,
                    "PlanType" => 5,
                    "Tax" => 1
                );
                $this->db->insert('TB_Master_Agency_Tax_Settings', $saveData);
            }
        } else {
            foreach ($dafultTaxArray as $key => $val) {
                $saveData = array(
                    "AgencySysId" => $AgencySysId,
                    "TaxName" => $val,
                    "TaxPercentage" => 0,
                    "TaxType" => 3,
                    "DefaultSelect" => 0,
                    "DefaultDisplay" => 0,
                    "IfCaasApplicable" => 0,
                    "CaasApplicableTaxPercentage" => 0,
                    "CaasApplicableTaxType" => 0,
                    "IsActive" => 1,
                    "IsMarkForDel" => 0,
                    "IsOrder" => $key,
                    "IsDefault" => 1,
                    "IsDefaultId" => $key,
                    "PlanType" => 5,
                    "Tax" => 1
                );
                $this->db->insert('TB_Master_Agency_Tax_Settings', $saveData);
            }
        }
        return true;
    }

    public function getWalletBalanceForAgency($AgencySysId) {
        $CreditArray = array("Title", "CurrencyType", "BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Master_Currency"), "tb1.CurrencyType = tb2.CurrencyType", array("Symbol"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCreditBNPLBalanceForAgency($AgencySysId) {
        $CreditArray = array("CreditAmount", "CreditDays", "BalanceAmount", "CreditSysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_BNPL_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Master_Currency"), "tb1.CurrencyType = tb2.CurrencyType", array("Symbol"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        //$select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        //echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCreditBalanceForAgency($AgencySysId) {
        $CreditArray = array("CreditAmount", "CreditDays", "BalanceAmount", "CreditSysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Credit"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Master_Currency"), "tb1.CurrencyType = tb2.CurrencyType", array("Symbol"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        //$select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        //echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCommissionForFranchisee($AgencySysId) {
        $FranchiseColumn = array('sum(Amount) as TotalBusiness', 'sum(AdjustedCommission) as FinalCommission', 'SUM(CASE WHEN tb1.Paid = 1 THEN tb1.AdjustedCommission ELSE 0 END) AS PaidCommission');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Commission"), $FranchiseColumn);
        $select->joinLeft(array('tb2' => "TB_Master_Currency"), "tb1.CurrencyType = tb2.CurrencyType", array("Symbol"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan"), "tb1.TPSysId = tb3.TPSysId", array());
        $select->where("tb3.StatusType !=?", 15);
        $select->where("tb1.FranchiseSysId =?", $AgencySysId);
        //$select->where("tb1.CreditSysId =?", $CreditSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->group("Symbol");
        // echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getB2BAgencyId($B2BAgencySysIdKey, $B2CAgencySysIdKey) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency"), "tb1.MasterAgencySysId = tb2.AgencySysId", array());
        $select->where("tb1.SecurityKey =?", $B2BAgencySysIdKey);
        $select->where("tb2.SecurityKey =?", $B2CAgencySysIdKey);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb2.IsMarkForDelete =?", 0);
        $select->where("tb2.IsActive =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getTaxTypeLable($TaxType = 1) {
        $CreditArray = array("Title");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_TaxType"), $CreditArray);
        $select->where("tb1.TaxType =?", $TaxType);
        $result = $this->db->fetchRow($select);
        $returnTaxTable = isset($result['Title']) && $result['Title'] != '' ? trim($result['Title']) : 'GST';
        return $returnTaxTable;
    }

    public function getB2bAgencyIds($masterAgencyId) {
        $CreditArray = array("AgencySysId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $CreditArray);
        $select->where("tb1.MasterAgencySysId =?", $masterAgencyId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);

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

    public function getFinanceUserDetail($agencyId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency"), array(""))
                ->joinLeft(array('t2' => "TB_Agency_User"), "t1.AgencySysId = t2.AgencySysId", array("isFinance", "ContactNo1", "EmailId"))
                ->where("t2.AgencySysId=?", $agencyId)
                ->where("t2.isFinance=?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkLeadSource($LeadSourceSysId, $UserSysId, $PlanType = 0) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_User_LeadSource_Mapping"), array("MappingSysId"))
                ->where("LeadSourceSysId = ?", $LeadSourceSysId)
                ->where("UserSysId = ?", $UserSysId);
        if ($PlanType > 0) {
            $select->where("PlanType = ?", $PlanType);
        }
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function addmanulaLeadSource() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Marketing_Facebook_Forms"), array("FormID", "AssignUserSysId"));
        $select->where("tb1.AssignUserSysId != ?", 0);
        //$select->where("tb1.IsActive = ?", 1);
        $resultArray = $this->db->fetchAll($select);

        foreach ($resultArray as $valu) {
            $LeadSourceSysId = $valu['FormID'];
            $UserSysId = $valu['AssignUserSysId'];
            $checkLeadSource = $this->checkLeadSource($LeadSourceSysId, $UserSysId);
            if (empty($checkLeadSource)) {
                $insertData = array('IsMarkForDel' => 0, 'LeadSourceSysId' => $LeadSourceSysId, 'UserSysId' => $UserSysId);
                $this->insertData('TB_Agency_Marketing_Facebook_User_LeadSource_Mapping', $insertData);
            }
        }
    }

    public function getLeadSourceDestination($agencyId, $destinationSysId = '') {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Master_Destination_LeadSource"), array("*"))
                ->joinLeft(array('t2' => "TB_Agency_Destination_LeadSource_Mapping"), "t1.LeadSourceSysId = t2.LeadSourceSysId", array("UserSysId"))
                ->where("t1.AgencySysId=?", $agencyId)
                ->where("t1.IsMarkForDel=?", 0)
                ->where("t2.IsMarkForDel=?", 0);
        if ($destinationSysId != '') {
            $select->where("t1.DestinationSysId=?", $destinationSysId);
            $result = $this->db->fetchRow($select);
            return $result;
        }
        return $select;
    }

    public function checkLeadDestinationUserMAp($LeadSourceSysId, $UserSysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Destination_LeadSource_Mapping"), array("MappingSysId"))
                ->where("LeadSourceSysId = ?", $LeadSourceSysId)
                ->where("UserSysId = ?", $UserSysId);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadSourcebyID($LeadSourceSysId) {
        $select = $this->db->select();
        $select->from("TB_Master_LeadSource", array('*'));
        $select->where("LeadSourceSysId = ?", $LeadSourceSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getOneTimeCreditLimitData() {
        $currentDate = date('Y-m-d');
        $updatedArray = array(
            'IsActive' => 0
        );
        $where = array(
            "CreateDate=(?)" => $currentDate,
            "IsOneTime = 1",
            "IsActive = 1",
            "IsMarkForDel = 0"
        );
        try {
            $this->updateData('TB_Agency_Credit', $updatedArray, $where);
        } catch (Zend_Exception $e) {
            echo $e->getMessage();
            exit;
        }
    }

    public function checkCreditLimitData() {
        $objPackage = new Travel_Model_TblPackage();
        $currentData = date("Y-m-d");
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_Credit"), array("*"))
                //->where("t1.AgencySysId=?", 49196)
                ->where("t1.CreditAmount > ?", 0)
                ->where("t1.IsMarkForDel=?", 0)
                ->where("t1.IsActive=?", 1)
                ->where("t1.CreditDate=?", $currentData);
        //echo $select; exit;
        $result = $this->db->fetchAll($select);

        if (!empty($result)) {
            foreach ($result as $value) {
                $CreditDate = date("Y-m-d");
                $CreditSysId = $value['CreditSysId'];
                $AgencySysId = $value['AgencySysId'];
                if ($value['IsOneTime'] == 1) {
                    if ($CreditSysId) {
                        $updateData = [
                            'IsOneTime' => 0,
                            'BalanceAmount' => new Zend_Db_Expr("BalanceAmount - CreditAmount"),
                            'CreditAmount' => new Zend_Db_Expr("CreditAmount - CreditAmount"),
                            'CreditDays' => 0,
                            'UpdateDate' => date("Y-m-d H:i:s")
                        ];
                        $whereQuery1 = array('CreditSysId = ? ' => $CreditSysId, 'AgencySysId = ? ' => $AgencySysId);
                        $objPackage->updateData('TB_Agency_Credit', $updateData, $whereQuery1);
                        $BalanceAmount = $this->getCreditBalance($CreditSysId, $AgencySysId);
                        $saveHistoryData = [
                            'PlanType' => 7,
                            'AgencySysId' => $AgencySysId,
                            'CreditSysId' => $CreditSysId,
                            'CurrencyType' => $value['CurrencyType'],
                            'CreditAmount' => $value['CreditAmount'],
                            'BalanceAmount' => $BalanceAmount,
                            'CreditDays' => $value['CreditDays'],
                            'CreditDate' => $CreditDate,
                            'UserSysId' => $value['UserSysId'],
                            'IsOneTime' => $value['IsOneTime'],
                            'Remark' => 'updated by automated schedule',
                            'IsCredit' => 1,
                            'OTPCode' => 1,
                            'CodeValidFrom' => date('Y-m-d H:i'),
                            'CodeValidTo' => date('Y-m-d H:i', (strtotime(date('Y-m-d H:i')) + 600)),
                            'CreateDate' => date('Y-m-d H:i'),
                            'UpdateDate' => date('Y-m-d H:i'),
                            'IsMarkForDel' => 0,
                            'IsActive' => 1,
                            'RefrenceNo' => time(),
                            'PaymentMode' => "Reduce Credit",
                            'TrxType' => 0,
                            'Amount' => $value['CreditAmount'],
                        ];
                        $objPackage->insertData('TB_Agency_Credit_History', $saveHistoryData);
                    }
                } else {
                    if ($CreditSysId) {
                        $updateData = [
                            'BalanceAmount' => new Zend_Db_Expr("BalanceAmount - CreditAmount"),
                        ];
                        $whereQuery1 = array('CreditSysId = ? ' => $CreditSysId, 'AgencySysId = ? ' => $AgencySysId);
                        $objPackage->updateData('TB_Agency_Credit', $updateData, $whereQuery1);
                        $BalanceAmount = $this->getCreditBalance($CreditSysId, $AgencySysId);
                        $saveHistoryData = [
                            'PlanType' => 7,
                            'AgencySysId' => $AgencySysId,
                            'CreditSysId' => $CreditSysId,
                            'CurrencyType' => $value['CurrencyType'],
                            'CreditAmount' => $value['CreditAmount'],
                            'BalanceAmount' => $BalanceAmount,
                            'CreditDays' => $value['CreditDays'],
                            'CreditDate' => $CreditDate,
                            'UserSysId' => $value['UserSysId'],
                            'IsOneTime' => $value['IsOneTime'],
                            'Remark' => '',
                            'IsCredit' => 1,
                            'OTPCode' => 1,
                            'CodeValidFrom' => date('Y-m-d H:i'),
                            'CodeValidTo' => date('Y-m-d H:i', (strtotime(date('Y-m-d H:i')) + 600)),
                            'CreateDate' => date('Y-m-d H:i'),
                            'UpdateDate' => date('Y-m-d H:i'),
                            'IsMarkForDel' => 0,
                            'IsActive' => 1,
                            'RefrenceNo' => time(),
                            'PaymentMode' => "Reduce Credit",
                            'TrxType' => 0,
                            'Amount' => $value['CreditAmount'],
                        ];
                        $objPackage->insertData('TB_Agency_Credit_History', $saveHistoryData);
                        $CreditDate = date('Y-m-d', strtotime(date('Y-m-d') . " +" . $value['CreditDays'] . " days"));
                        $updateDataNext = [
                            'BalanceAmount' => new Zend_Db_Expr("BalanceAmount + CreditAmount"),
                            'CreditDate' => $CreditDate,
                            'UpdateDate' => date("Y-m-d H:i")
                        ];
                        $whereQueryNext = array('CreditSysId = ? ' => $CreditSysId, 'AgencySysId = ? ' => $AgencySysId);
                        $objPackage->updateData('TB_Agency_Credit', $updateDataNext, $whereQueryNext);
                        $BalanceAmountNext = $this->getCreditBalance($CreditSysId, $AgencySysId);
                        $saveHistoryDataNext = [
                            'PlanType' => 7,
                            'AgencySysId' => $AgencySysId,
                            'CreditSysId' => $CreditSysId,
                            'CurrencyType' => $value['CurrencyType'],
                            'CreditAmount' => $value['CreditAmount'],
                            'BalanceAmount' => $BalanceAmountNext,
                            'CreditDays' => $value['CreditDays'],
                            'CreditDate' => $CreditDate,
                            'UserSysId' => $value['UserSysId'],
                            'IsOneTime' => $value['IsOneTime'],
                            'Remark' => 'auto update',
                            'IsCredit' => 1,
                            'OTPCode' => 1,
                            'CodeValidFrom' => date('Y-m-d H:i'),
                            'CodeValidTo' => date('Y-m-d H:i', (strtotime(date('Y-m-d H:i')) + 600)),
                            'CreateDate' => date('Y-m-d H:i'),
                            'UpdateDate' => date('Y-m-d H:i:s'),
                            'IsMarkForDel' => 0,
                            'IsActive' => 1,
                            'RefrenceNo' => time(),
                            'PaymentMode' => "Add Credit",
                            'TrxType' => 1,
                            'Amount' => $value['CreditAmount'],
                        ];
                        $objPackage->insertData('TB_Agency_Credit_History', $saveHistoryDataNext);
                    }
                }
            }
        }
    }

    public function getTcsSettingDetail($AgencySysId, $IsActive = 1) {
        $select = $this->db->select();
        $select->from("TB_Agency_Tcs_Tax_Settings");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsActive =?", $IsActive);
        $select->where("IsMarkForDel = 0");
        $select->order("TcsTaxSysId DESC");

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

    public function checkGetAndUpdateTCSSetting($AgencySysId, $data = array()) {
        $objPackage = new Travel_Model_TblPackage();
        $select = $this->db->select();
        $select->from("TB_Agency_Tcs_Tax_Settings");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->order("TcsTaxSysId DESC");
        $result = $this->db->fetchRow($select);
        if (!empty($data)) {
            $IsActive = isset($data['IsApplyTCS']) ? (int) $data['IsApplyTCS'] : 0;
            $IsPanCard = (isset($data['IsPanCard']) && $IsActive == 1) ? (int) $data['IsPanCard'] : 0;
            $IsNotPanCard = (isset($data['IsNotPanCard']) && $IsActive == 1) ? (int) $data['IsNotPanCard'] : 0;
            $CurrencyType = isset($data['MyBaseCurrency']) ? (int) $data['MyBaseCurrency'] : 1;
            if (!empty($result)) {
                $updateData = [
                    'IsPanCard' => $IsPanCard,
                    'IsNotPanCard' => $IsNotPanCard,
                    'UpdateDate' => date('Y-m-d'),
                    'CurrencyType' => $CurrencyType,
                    'IsActive' => $IsActive,
                ];
                $whereUpdate = array('AgencySysId = ? ' => $AgencySysId);
                $objPackage->updateData('TB_Agency_Tcs_Tax_Settings', $updateData, $whereUpdate);
            } else {
                $insertData = [
                    'AgencySysId' => $AgencySysId,
                    'IsPanCard' => $IsPanCard,
                    'IsNotPanCard' => $IsNotPanCard,
                    'CreateDate' => date('Y-m-d'),
                    'UpdateDate' => date('Y-m-d'),
                    'CurrencyType' => $CurrencyType,
                    'IsActive' => $IsActive,
                    'IsMarkForDel' => 0,
                ];
                $objPackage->insertData('TB_Agency_Tcs_Tax_Settings', $insertData);
            }
        }
        return $result;
    }

    public function getAgencyLogo($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('Logo'))
                ->where("tb1.AgencySysId = $AgencySysId")
                ->where("tb1.IsActive =?", 1)
                ->where("tb1.IsMarkForDelete =?", 0)
                ->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getb2bAgencyIdsWithMaster($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('AgencySysId'))
                ->where("(tb1.AgencySysId = $AgencySysId OR tb1.MasterAgencySysId = $AgencySysId)")
                ->where("tb1.IsActive =?", 1)
                ->where("tb1.IsMarkForDelete =?", 0)
                ->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchAll($select);
        $agencyArray = array();
        foreach ($result as $valArray) {
            $agencyArray[] = $valArray['AgencySysId'];
        }
        return $agencyArray;
    }

    public function getAgencySysIdWithAgencySupplier($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('AgencySysId'))
                ->where("tb1.MasterAgencySysId = $AgencySysId")
                ->where("tb1.AgencyType = 2")
                ->where("tb1.IsActive =?", 1)
                ->where("tb1.IsMarkForDelete =?", 0)
                ->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchAll($select);
        $agencyIdArray[] = $AgencySysId;
        if (!empty($result)) {
            foreach ($result as $agencyIds) {
                $agencyIdArray[] = $agencyIds['AgencySysId'];
            }
        }
        return $agencyIdArray;
    }

    public function getAgencySysIdFromSecurityKey($B2CSecurityKey, $B2BSecurityKey) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('AgencySysId', 'SecurityKey'))
                ->where("tb1.SecurityKey = '$B2CSecurityKey' OR tb1.SecurityKey = '$B2BSecurityKey'");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkGetAndUpdateAgencySeriesFareMarkup($AgencySysId, $data = array()) {
        $objPackage = new Travel_Model_TblPackage();
        $select = $this->db->select();
        $select->from("TB_Agency_Series_Fare_Markup");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->order("SFSysId DESC");
        $result = $this->db->fetchRow($select);

        if (!empty($data)) {
            $SFMarkUpType = isset($data['SFMarkUpType']) ? (int) $data['SFMarkUpType'] : 1;
            $SFB2CMarkUp = isset($data['SFB2CMarkUp']) ? (float) $data['SFB2CMarkUp'] : 0;
            $SFB2BMarkUp = isset($data['SFB2BMarkUp']) ? (float) $data['SFB2BMarkUp'] : 0;
            $SFInfantMarkUp = isset($data['SFInfantMarkUp']) ? (float) $data['SFInfantMarkUp'] : 0;
            $insertData = [
                'AgencySysId' => $AgencySysId,
                'MarkUpType' => $SFMarkUpType,
                'B2CMarkUp' => $SFB2CMarkUp,
                'B2BMarkUp' => $SFB2BMarkUp,
                'InfantMarkUp' => $SFInfantMarkUp,
                'CreateDate' => date('Y-m-d'),
                'UpdateDate' => date('Y-m-d'),
                'IsApproved' => 1,
                'IsActive' => 1,
                'IsMarkForDel' => 0,
            ];
            if (!empty($result)) {
                unset($insertData['AgencySysId']);
                unset($insertData['CreateDate']);
                $whereUpdate = array('AgencySysId = ? ' => $AgencySysId);
                $objPackage->updateData('TB_Agency_Series_Fare_Markup', $insertData, $whereUpdate);
            } else {
                $objPackage->insertData('TB_Agency_Series_Fare_Markup', $insertData);
            }
        }
        return $result;
    }

    public function getAgencySeriesFareMarkup($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Series_Fare_Markup"), array('*'))
                ->where("tb1.AgencySysId = $AgencySysId")
                ->where("tb1.IsActive =?", 1)
                ->where("tb1.IsMarkForDel =?", 0)
                ->where("tb1.IsApproved =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getActivateApiAgencyWise($AgencySysId, $IsSereiesFare = null) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_API"), array(""))
                ->joinLeft(array('t2' => "TB_Master_API_Source"), "t1.APISourceSysId = t2.APISourceSysId", array("Title", "APISourceSysId As GTXId"))
                ->where("t1.AgencySysId=?", $AgencySysId)
                ->where("t1.IsActive=?", 1)
                ->where("t2.PlanType=?", 1)
                ->where("t1.IsMarkForDel=?", 0)
                ->where("t2.IsMarkForDel=?", 0);
        if ($IsSereiesFare) {
            $select->where("t2.IsSeriesFare=?", 1);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getFlightMarkupAirlineWise($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Flight_Markup_Airline_Wise"))
                ->joinLeft(array('tb2' => "TB_Master_Airline"), "tb1.AirlineSysId = tb2.AirlineSysId", array("Title", 'AirlineSysId'))
                ->where("tb1.AgencySysId = '$AgencySysId'")
                ->where("tb1.IsMarkForDelete = 0")
                ->where("tb1.IsActive = 1");
        //echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getFlightAirlineName($Airlineid) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_Airline"), array('Title', 'AirlineSysId'))
                ->where("tb1.AirlineSysId = '$Airlineid'")
                ->where("tb1.IsMarkForDelete = 0")
                ->where("tb1.IsActive = 1");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getFlightMarkupSectorWisebyID($FlightMarkupSectorWise) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Flight_Markup_Sector_Wise"))
                ->joinLeft(array('tb2_from' => "TB_Master_Airport"), "tb1.FromAirportSysId = tb2_from.ISO", array("FromAirportCode" => "AirportCode", 'FromISO' => 'ISO', 'FromAirportTitle' => 'AirportTitle'))
                ->joinLeft(array('tb2_to' => "TB_Master_Airport"), "tb1.ToAirportSysId = tb2_to.ISO", array("ToAirportCode" => "AirportCode", 'ToISO' => 'ISO', 'ToAirportTitle' => 'AirportTitle'))
                ->joinLeft(array('tb3' => "TB_Master_Airline"), "tb3.AirlineSysId = tb1.AirlineSysId", array('AirlineName' => 'Title'))
                ->where("tb1.FlightMarkupSectorWise = ?", $FlightMarkupSectorWise)
                ->where("tb1.IsMarkForDelete = ?", 0)
                ->where("tb1.IsActive = ?", 1)
                ->where("tb2_from.IsMarkForDel = ?", 0)
                ->where("tb2_from.IsActive = ?", 1)
                ->where("tb2_to.IsMarkForDel = ?", 0)
                ->where("tb2_to.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getFlightMarkupSectorWise($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Flight_Markup_Sector_Wise"))
                ->joinLeft(array('tb2_from' => "TB_Master_Airport"), "tb1.FromAirportSysId = tb2_from.ISO", array("FromAirportCode" => "AirportCode", 'FromISO' => 'ISO', 'FromAirportTitle' => 'AirportTitle'))
                ->joinLeft(array('tb2_to' => "TB_Master_Airport"), "tb1.ToAirportSysId = tb2_to.ISO", array("ToAirportCode" => "AirportCode", 'ToISO' => 'ISO', 'ToAirportTitle' => 'AirportTitle'))
                ->joinLeft(array('tb3' => "TB_Master_Airline"), "tb3.AirlineSysId = tb1.AirlineSysId", array('AirlineName' => 'Title'))
                ->where("tb1.AgencySysId = ?", $AgencySysId)
                ->where("tb1.IsMarkForDelete = ?", 0)
                ->where("tb1.IsActive = ?", 1)
                ->where("tb2_from.IsMarkForDel = ?", 0)
                ->where("tb2_from.IsActive = ?", 1)
                ->where("tb2_to.IsMarkForDel = ?", 0)
                ->where("tb2_to.IsActive = ?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencySysIdWithPrimaryUserIdFromSecurityKey($SecurityKey) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('AgencySysId', 'PrimaryUserSysId'))
                ->where("tb1.SecurityKey = '$SecurityKey'")
                ->where("tb1.IsApproved = 1")
                ->where("tb1.IsActive = 1");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyBookingFromField($AgencySysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Booking_From_Field");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("IsActive =?", 1);
        $select->where("IsMarkForDel =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkAgencyBookingFromField($AgencySysId = NULL, $PlanType = 5, $IsLeadPax = NULL, $FieldName = NULL, $Type = NULL) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Booking_From_Field");
        $select->where("AgencySysId =?", $AgencySysId);
        $select->where("PlanType =?", $PlanType);
        $select->where("IsLeadPax =?", $IsLeadPax);
        $select->where("FieldName =?", $FieldName);
        $select->where("Type =?", $Type);
        $select->where("IsActive =?", 1);
        $select->where("IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyLastGeneratedPerformaInvoiceNumber($AgencySysId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_PerformaInvoiceNumbers"), array('InvoiceNumber'))
                ->where("t1.AgencySysId = ?", $AgencySysId)
                ->where("t1.InvoiceNumber != ''")
                ->order("t1.InvoiceSysId DESC");
        //echo $select;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCreditLimitDataByCustomerId($CustomerSysId) {
        $CreditArray = array('BalanceAmount', 'CreditAmount', 'WalletSysId', 'IsOneTime', 'CreditDays');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_B2C_Wallet"), $CreditArray);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb3.CustomerSysId", array('CustomerSysId', 'Title', 'FirstName', 'LastName'));
        $select->where("tb1.CustomerSysId =?", $CustomerSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        //$select->where("tb2.IsMarkForDel =?", 0);
        //$select->order("tb2.UpdateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAllPdfComponentData($AgencySysId, $IsDisplay = 0) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Pdf_Component"), array('*'));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        if ($IsDisplay > 0) {
            $select->where("tb1.IsDisplay =?", $IsDisplay);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkPdfComponentData($AgencySysId, $id) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Pdf_Component"), array('CompSysId'));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.ComponentId  =?", $id);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkFlightApidata($ApiSourceSysId, $AirlineSysId, $FlightMarkupAirlineWise = 0, $AgencySysId = null) {
        $select = $this->db->select();
        $select->from(array("t1" => "Tb_Agency_Flight_Markup_Airline_Wise"));
        $select->where("t1.ApiSourceSysId = ?", $ApiSourceSysId);
        $select->where("t1.AirlineSysId = ?", $AirlineSysId);
        if ($AgencySysId) {
            $select->where("t1.AgencySysId = ?", $AgencySysId);
        }
        if ($FlightMarkupAirlineWise > 0) {
            $select->where("t1.FlightMarkupAirlineWise != ?", $FlightMarkupAirlineWise);
        }
        //echo $select; exit;
        $result = $this->db->fetchRow($select);
        return $result ? 1 : 0;
    }

    public function checkFlightSeriesFaredata($AgencySysId, $ApiSourceSysId, $FromAirportSysId, $ToAirportSysId, $flightairline_id, $FlightMarkupSectorWise = 0) {
        $select = $this->db->select();
        $select->from(array("t1" => "Tb_Agency_Flight_Markup_Sector_Wise"));
        $select->where("t1.ApiSourceSysId = ?", $ApiSourceSysId);
        $select->where("t1.FromAirportSysId = ?", $FromAirportSysId);
        $select->where("t1.ToAirportSysId = ?", $ToAirportSysId);
        $select->where("t1.AirlineSysId = ?", $flightairline_id);
        $select->where("t1.AgencySysId = ?", $AgencySysId);
        if ($FlightMarkupSectorWise > 0) {
            $select->where("t1.FlightMarkupSectorWise != ?", $FlightMarkupSectorWise);
        }
        $result = $this->db->fetchRow($select);
        return $result ? 1 : 0;
    }

    public function getFareTypeMeppingData($AgencySysId, $apisouceSysId = null) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_Fare_Type"), array('FareTypeSysId as old_FareTypeSysId', 'Title', 'ApiSourceSysId', 'ColorCode'));
        $select->joinLeft(array('tb2' => "Tb_Agency_Fare_Type_Mapping"), "tb1.FareTypeSysId = tb2.FareTypeSysId AND tb2.AgencySysId = $AgencySysId", array('Title AS NewTitle', 'ColorCode as NewColorCode', 'B2BRemark', 'B2CRemark', 'FareTypeMappingSysId', 'AllowFlexiPayment'));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        if ($apisouceSysId) {
            $select->where("tb1.ApiSourceSysId =?", $apisouceSysId);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getDoMoreData($DoMoreSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Do_More"));
        $select->where("tb1.DoMoreSysId =?", $DoMoreSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getTipsForYouDataByID($TipsForYouSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Tips_For_You"));
        $select->where("tb1.TipsForYouSysId =?", $TipsForYouSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getDoMoreList() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Do_More"));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTipsForYouList() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Tips_For_You"));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getOffersList($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Offer"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getDoMoreListhome() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Do_More"));
        $select->where("tb1.DisplayOnHome =?", 1);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTipsForYouListhome() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Master_Tips_For_You"));
        $select->where("tb1.DisplayOnHome =?", 1);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getOffersListhome($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Offer"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.DisplayOnHome =?", 1);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getcustmenberdocument($CustomerSysId, $AgencySysId, $memberCustSysId = null) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Customer_Members_Document"));
        $select->where("tb1.CustomerSysId =?", $CustomerSysId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        if (empty($memberCustSysId)) {
            $select->where("tb1.MemberSysId IS NULL");
        } else {
            $select->where("tb1.MemberSysId =?", $memberCustSysId);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getmenberdocumentById($MembersDocumentSysId, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Customer_Members_Document"));
        $select->where("tb1.MembersDocumentSysId =?", $MembersDocumentSysId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getdocumentByIds($MembersDocumentSysIds, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Customer_Members_Document"));
        $select->where('tb1.MembersDocumentSysId IN (?)', $MembersDocumentSysIds);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updateMemberDocument($table, $data, $where) {
        try {
            $this->db->update($table, $data, $where);
            return true;
        } catch (Exception $e) {
            error_log('Error in memberDocumentData: ' . $e->getMessage());
            return false;
        }
    }

    public function getMemberById($MembersId, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Members"), array('EmailId', 'Title', 'FirstName', 'LastName'));
        $select->where("tb1.MemberSysId =?", $MembersId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCustomerById($CustomerId, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer"), array('EmailId', 'Title', 'FirstName', 'LastName'));
        $select->where("tb1.CustomerSysId =?", $CustomerId);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getTotalFlightBookingPaxWise($agencySysId, $month, $year) {
        $statement = $this->db->query("GET_AGENCY_WISE_FLIGHT_BOOKING_TOATAL_PAX_WISE '$agencySysId','$year','$month'");
        $resultSet1 = $statement->fetchAll();
        $finalResultSet['Result1'] = array();
        foreach ($resultSet1 as $row) {
            $finalResultSet['Result1'][] = $row;
        }
        return $finalResultSet;
    }

    public function getAgencySysIdFromSecurityKeyInFaceBook($SecurityKey, $IsWhatsApp = 0) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array('AgencySysId'));
        $select->joinLeft(array('tb2' => "Tb_Agency_Mapping"), "tb1.AgencySysId = tb2.AgencySysId", array('WhatsAppNumberId'))
                ->where("tb1.SecurityKey = '$SecurityKey'");
        if ($IsWhatsApp == 1) {
            $select->where("tb2.IsWhatsApp = 1");
        }
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getNetworkAgencyWise($AgencySysId, $destination = null) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_TravelPlan_Mapping_Network_Web"), array());
        $select->joinLeft(array('tb2' => "TB_TBB2C_Packages_Master"), "tb1.TPSysId = tb2.GTXPkgId AND tb2.AgencySysId = $AgencySysId");
        $select->where("tb1.ToAgencySysId =?", $AgencySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb1.IsWeb =?", 1);
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->where("tb2.AgencySysId =?", $AgencySysId);
        if ($destination) {
            $select->where("tb2.PackageSearchString LIKE '%$destination%'");
        }
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getB2cCustomerCreditLimitDataByAgencyId($AgencySysId, $isCredit = 0, $Isfetched = 0) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remark', 'TrxType', 'Amount', 'CreditAmount as historyCreditAmount', 'BalanceAmount as historyBalanceAmount', 'IsCredit', 'HistoryWalletSysId', 'ReferenceNo', 'PlanType', 'PaymentMode');

        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_B2C_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_B2C_Wallet_History"), "tb1.WalletSysId = tb2.WalletSysId", $CreditHistoryArray);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb3.CustomerSysId", array('FirstName', 'LastName', 'Title'));
        $select->where("tb1.AgencySysId =? ", $AgencySysId);

        if ($Isfetched == 1) {
            $select->where("tb2.Isfetched =?", 0);
        }
        if ($isCredit == 2) {
            $select->where("tb2.isCredit =?", 2);
            $select->where("tb2.IsActive =?", '0');
            $select->where("tb2.PlanType =?", 7);
            $select->where("tb2.TrxType =?", 1);
        } else {
            $select->where("tb2.IsActive =?", 1);
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.UpdateDate DESC");
        //$select->order("tb2.UpdateDate DESC");
        //        echo $select;die;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCustomerCreditHistoryDataById($WalletSysId, $HistoryWalletSysId, $AgencySysId = 0) {
        $CreditHistoryArray = array('*');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer"), array("Title", "FirstName", "LastName"));
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_B2C_Wallet_History"), "tb1.CustomerSysId = tb2.CustomerSysId", $CreditHistoryArray);
        $select->where("tb2.HistoryWalletSysId =?", $HistoryWalletSysId);
        $select->where("tb2.WalletSysId =?", $WalletSysId);
        if ($AgencySysId > 0) {
            $select->where("tb2.AgencySysId =?", $AgencySysId);
        }
        $select->where("tb2.IsMarkForDel =?", 0);
        //        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getGtxNetworkAgencyWise($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), array("IsGTXNetworkWeb"));
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $select->where("tb1.IsApproved =?", 1);
        $select->where("tb1.IsActive =?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkNetworkPackage($AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_TravelPlan_Mapping_Network_Web"), array("TPSysId"));
        $select->joinLeft(array("T3" => "Tb_Agency_Mapping_GTX_Network"), "T3.FromAgencySysId = tb1.AgencySysId AND T3.ToAgencySysId = tb1.ToAgencySysId", array(""));
        $select->where("tb1.ToAgencySysId =?", $AgencySysId);
        $select->where("tb1.IsWeb =?", 1);
        $select->where("T3.IsDeactivated !=?", 1);
        $select->where("tb1.IsActive =?", 1);
        $select->order("tb1.TPSysId DESC");
        $result = $this->db->fetchAll($select);
        $dataArray = array();
        foreach ($result as $resultData) {
            $dataArray[$resultData['TPSysId']] = 1;
        }
        return $dataArray;
    }

    public function deleteTemplateVariable($TemplateSysId) {
        if ($TemplateSysId > 0) {
            $whereTemp = array('TemplateSysId = ? ' => $TemplateSysId);
            $this->db->delete('TB_Agency_Facebook_Template_Header', $whereTemp);
        }
    }

    public function deleteMultipleTemplateVariable($TemplateSysId) {
        if (!empty($TemplateSysId)) {
            $whereTemp = array('TemplateSysId IN (?) ' => $TemplateSysId);
            $this->db->delete('TB_Agency_Facebook_Template_Header', $whereTemp);
        }
    }

    public function deleteCarouselTemplate($TemplateSysId) {
        if ($TemplateSysId > 0) {
            $whereTemp = array('MasterId = ? ' => $TemplateSysId);
            $this->db->delete('TB_Agency_WhatsAppEmail_Template', $whereTemp);
        }
    }

    public function deleteInteractiveTemplateVariable($TemplateSysId) {
        if ($TemplateSysId > 0) {
            $whereTemp = array('TemplateSysId = ? ' => $TemplateSysId);
            $this->db->delete('TB_Agency_Whatsapp_Template_Header', $whereTemp);
        }
    }

    public function getDocumentVisaCountry($docId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_VisaDocument_Country"));
        $select->where("tb1.DocSysId =?", $docId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function deleteDocumentVisaCountry($docId) {
        if ($docId > 0) {
            $whereTemp = array('DocSysId = ? ' => $docId);
            $this->db->delete('TB_Master_VisaDocument_Country', $whereTemp);
        }
    }

    public function getDocumentLabel($agencySysId, $lable, $DocSysId = 0) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Visa_Document"), array("IsMandatory"));
        $select->joinLeft(array('tb2' => "TB_Master_VisaDocument"), "tb1.DocSysId = tb2.DocSysId", array("DocSysId", "Title", "Description", "Label"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb2.Label = ?", $lable);
        if ($DocSysId > 0) {
            $select->where("tb2.DocSysId != ?", $DocSysId);
        }
        $select->where("tb2.IsMarkForDel != ?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getDocumentListByCoveredCountry($agencySysId, $CoverCountryId) {

        $select1 = $this->db->select();
        $select1->from(array('tb1' => "Tb_Agency_Visa_Document"), array("IsMandatory"));
        $select1->joinLeft(array('tb2' => "TB_Master_VisaDocument"), "tb1.DocSysId = tb2.DocSysId", array("DocSysId", "Title", "Description", "FieldType"));
        $select1->joinLeft(array('tb3' => "TB_Master_VisaDocument_Country"), "tb2.DocSysId = tb3.DocSysId", array("CountrySysId"));
        $select1->where("tb1.AgencySysId = ?", $agencySysId);
        $select1->where("tb2.IsMarkForDel != ?", 1);
        if (!empty($CoverCountryId)) {
            $select1->where("tb3.CountrySysId IN (?)", $CoverCountryId);
        }
        $result1 = $this->db->fetchAll($select1);

        $select2 = $this->db->select();
        $select2->from(array('tb1' => "Tb_Agency_Visa_Document"), array("IsMandatory"));
        $select2->joinLeft(array('tb2' => "TB_Master_VisaDocument"), "tb1.DocSysId = tb2.DocSysId", array("DocSysId", "Title", "Description", "FieldType"));
        $select2->joinLeft(array('tb3' => "TB_Master_VisaDocument_Country"), "tb2.DocSysId = tb3.DocSysId", array("CountrySysId"));
        $select2->where("tb1.AgencySysId = ?", $agencySysId);
        $select2->where("tb2.IsMarkForDel != ?", 1);
        if (!empty($CoverCountryId)) {
            $select2->where("tb3.CountrySysId is NULL OR tb3.CountrySysId = ''");
        }
        $result2 = $this->db->fetchAll($select2);
        $result = array_merge($result1, $result2);
        return $result;
    }

    public function getAgencyFinanceUserDetail($agencyId) {
        $select = $this->db->select()
                ->from(array("t1" => "TB_Agency_User"), array("UserSysId", "isFinance", "countrycode", "ContactNo1", "EmailId", "Signature", "SignatureType"))
                ->where("t1.AgencySysId=?", $agencyId)
                ->where("t1.isFinance=?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getChatBotFlow($agencySysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Flow");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsActive = ?", 1);
        $select->where("IsMarkForDelete = ?", 0);
        $select->order("FlowSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappTagsById($agencySysId, $TagsSysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Tags");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("TagsSysId = ?", $TagsSysId);
        $select->where("IsMarkForDelete = ?", 0);
        $select->where("IsActive = ?", 1);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getWhatsappTagsList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Tags"));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.TagsSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getKeyWordActionList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Keyword_Action"));
        $select->joinLeft(array('tb2' => "Tb_Agency_Whatsapp_Tags"), "tb1.TagsSysId = tb2.TagsSysId", array("Title as keyword"));
        $select->joinleft(array('tb3' => "TB_Agency_User"), "tb1.AgentSysId = tb3.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->where("tb2.AgencySysId = ?", $agencySysId);
        $select->where("tb2.IsMarkForDelete = ?", 0);
        $select->order("tb1.KeyActionSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getKeywordActionById($KeywordActionSysId, $agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Keyword_Action"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->where("tb1.KeyActionSysId = ?", $KeywordActionSysId);
        $select->orwhere("tb1.MasterKeyActionSysId =?", $KeywordActionSysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updateKeywordActionStatus($data, $KeyActionSysId, $agencySysId) {
        try {
            $where = 'KeyActionSysId = ' . $KeyActionSysId . ' AND AgencySysId = ' . $agencySysId . ' OR MasterKeyActionSysId = ' . $KeyActionSysId;
            $this->db->update('Tb_Agency_Whatsapp_Keyword_Action', $data, $where);
            return true;
        } catch (Exception $e) {
            die($e->getMessage());
            return false;
        }
    }

    public function checkExistKeyword($keyword, $agencySysId, $TagsSysId = null) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Tags");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("Title = ?", $keyword);
        if (!empty($TagsSysId)) {
            $select->where("TagsSysId !=?", $TagsSysId);
        }
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkKeywordAction($keyword, $agencySysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Keyword_Action");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("TagsSysId = ?", $keyword);
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getInteractiveTemplate($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Interactive_Template"));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.InteractiveTemplateSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getInteractiveTemplateById($InteractiveTemplateSysId, $agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Interactive_Template"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.InteractiveTemplateSysId = ?", $InteractiveTemplateSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        //        echo $select;die;
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getInteractiveTemplateListAction($InteractiveTemplateSysId, $agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Interactive_List_Action"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.InteractiveTemplateSysId = ?", $InteractiveTemplateSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappInteractiveTemplate($agencySysId, $templateType = 0) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Interactive_Template"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        if ($templateType > 0) {
            $select->where("tb1.TemplateType = ?", $templateType);
        }

        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFlow($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow"));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.FlowSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFlowById($agencySysId, $FlowSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.FlowSysId = ?", $FlowSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFlowActionList($agencySysId, $FlowSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow_Action"));
        $select->joinLeft(array('tb2' => "Tb_Agency_Whatsapp_Interactive_Template"), "tb1.TemplateId = tb2.InteractiveTemplateSysId");
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.FlowSysId = ?", $FlowSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function updateFlowAction($actionid, $data, $where) {
        $this->db->update('Tb_Agency_Whatsapp_Flow_Action', $data, $where);
    }

    public function getInteractiveTemplateListActionWithFlowAction($InteractiveTemplateSysId, $agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Interactive_List_Action"));
        $select->joinLeft(array('tb2' => "Tb_Agency_Whatsapp_Flow_Action"), "tb1.id = tb2.SourceId", array("id as FlowActionId", "TargetId"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.InteractiveTemplateSysId = ?", $InteractiveTemplateSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappStickerlist($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Stickers"));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.id DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappStickerByID($agencySysId, $id) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Stickers");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("id = ?", $id);
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getForUpdateWhatsappFlowActionList($agencySysId, $FlowSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow_Action"));
        $select->joinLeft(array('tb2' => "Tb_Agency_Whatsapp_Interactive_Template"), "tb1.SourceId = tb2.InteractiveTemplateSysId", array("TemplateName", "TemplateType as TTemplateType"));
        $select->joinLeft(array('tb3' => "Tb_Agency_Whatsapp_Interactive_List_Action"), "tb1.SourceId = tb3.id", array("ActionName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.FlowSysId = ?", $FlowSysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->where("tb1.IsDisplay = ?", 1);
        $select->order("tb1.id ASC");
        //        $select->order("tb1.Seq ASC"); 
        //        echo $select;die;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFlowForm($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow_Form"));
        $select->joinleft(array('tb2' => "TB_Agency_User"), "tb1.AgentSysId = tb2.UserSysId", array("FirstName", "LastName"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->order("tb1.id DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFormFlowSuggest($condition, $agencySysId) {
        $condition = trim($condition);
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Flow_Form");
        $select->where($condition);
        $select->where("IsApproved = ?", 1);
        $select->where("IsJsonStatus = ?", 1);
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsMarkForDelete = ?", 0);
        $select->order("id DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getWhatsappFlowFormBYFlowId($Formid, $agencySysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Flow_Form");
        $select->where("FormId = ?", $Formid);
        $select->where("IsApproved = ?", 1);
        $select->where("IsJsonStatus = ?", 1);
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getWhatsappFlowFormId($id, $agencySysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Whatsapp_Flow_Form");
        $select->where("id = ?", $id);
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getWhatsappFlowActionTargetNull($agencySysId, $FlowSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Agency_Whatsapp_Flow_Action"), array('TemplateId'));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.FlowSysId = ?", $FlowSysId);
        $select->where("tb1.TargetId is Null");
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkCarouselWhatsappMessage($agencySysId, $id) {
        $select = $this->db->select();
        $select->from("TB_Agency_WhatsAppEmail_Template", array('Id'));
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("MasterId = ?", $id);
        $select->where("IsMarkForDel = ?", 0);
        $select->order("Id ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getDailyExpenseByID($agencySysId, $id) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Expenses");
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("ExpSysId = ?", $id);
        $select->where("IsMarkForDelete = ?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getDailyExpenseList($agencySysId, array $searchArr) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Expenses");
        if (!empty($searchArr)) {
            if (!empty($searchArr['FromDate']) && !empty($searchArr['ToDate'])) {
                $FromDate = $searchArr['FromDate'];
                $ToDate = $searchArr['ToDate'];
                $select->where("CreateDate BETWEEN '$FromDate' AND '$ToDate'");
            }
            if (!empty($searchArr['PaymentType'])) {
                $select->where("PaymentType = ?", $searchArr['PaymentType']);
            }
            if (!empty($searchArr['Status'])) {
                $select->where("Status = ?", $searchArr['Status']);
            }
        }
        $select->where("AgencySysId = ?", $agencySysId);
        $select->where("IsMarkForDelete = ?", 0);
        $select->order("ExpSysId ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getDistributorFranchise($AgencySysId) {
        $select = $this->db->select();
        $select->from("TB_Agency", array('AgencySysId', 'Title'));
        $select->where("AgencyType = ?", 3);
        $select->where("FirmType = ?", 6);
        $select->where("MasterAgencySysId = ?", $AgencySysId);
        $select->where("IsMarkForDelete = ?", 0);
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkUserPermissions($AgentSysId) {
        $select = $this->db->select();
        $select->from("Tb_Agency_User_Permissions", array('PermissionSysId'));
        $select->where("AgentSysId = ?", $AgentSysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

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

        $select->from(array('tb1' => "TB_Agency_WhatsAppEmail_Template", array('*')));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsDefault = ?", 0);
        $select->where("TemplateType = 1");
        $select->where("MasterId is NULL");
        $select->where("tb1.TemplateStatus = ?", 'APPROVED');
        $select->where("tb1.IsBotTemplate = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->order("tb1.Id DESC");

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

    public function getLeadMediumList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Medium_Master"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadMediumbyID($MediumSysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Medium_Master", array('*'));
        $select->where("MediumSysId = ?", $MediumSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getLeadCampaignList($agencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Campaign_Master"));
        $select->where("tb1.AgencySysId = ?", $agencySysId);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getLeadCampaignbyID($CampSysId) {
        $select = $this->db->select();
        $select->from("TB_Agency_Campaign_Master", array('*'));
        $select->where("CampSysId = ?", $CampSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkCommssionVal($commissionId, $agencySysId, $Type = 3) {
        $select = $this->db->select();
        $select->from("Tb_Agency_Commission", array('CommSysId', 'AdjustedCommission'));
        $select->where("CommSysId = ?", $commissionId);
        $select->where("FranchiseSysId = ?", $agencySysId);
        $select->where("IsActive = ?", 1);
        $select->where("IsMarkForDelete = ?", 0);
        $select->where("Paid = ?", $Type);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAllPaymentMode() {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_PaymentMediaType"), array('PaymentMediaType', 'Title'));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel  =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getBusInventory($TPSysId, $Fromdate) {
        $CreditArray = array("BusSeatsId", "TPSysId", "SeatType", "SeatNo", "Status", "InventoryDate", "SeatId");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_TravelPlan_Bus_Seats"), $CreditArray);
        $select->joinleft(array('tb2' => "Tb_Bus_Seats"), "tb1.SeatId = tb2.BusSeatsId", array("SeatNo", "SeatType"));
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.TPSysId =?", $TPSysId);
        $select->where("tb1.InventoryDate =?", $Fromdate);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyBankDetails($AgencySysId) {
        $CreditArray = array("BankInfoSysId", "AccountNo", "AccountName", "BankName", "IFSCCode", "Branch");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_BankDetails"), $CreditArray);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDelete =?", 0);
        $select->where("tb1.AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getSupplierBankDetails($AgencySysId, $SupplierSysId) {
        $CreditArray = array("BankInfoSysId", "AccountNo", "AccountName", "BankName", "IFSCCode", "Branch");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_IC_Supplier"), array());
        $select->joinleft(array('tb2' => "TB_Supplier_BankDetails"), "tb1.SupplierSysId = tb2.SupplierSysId", $CreditArray);
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDelete =?", 0);
        $select->where("tb1.XRefAgencySysId =?", $AgencySysId);
        $select->where("tb1.SupplierSysId =?", $SupplierSysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getPaymentMediaType() {
        $CreditArray = array("PaymentMediaType", "TiTle AS Name");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Master_PaymentMediaType"), $CreditArray);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencySuppliers($agencySysId, $SupplierSysId = null) {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        if ($request->isPost()) {
            $post = $request->getPost();
        }
        $whereCondition = " XRefAgencySysId = '$agencySysId'";
        if ($SupplierSysId) {
            $whereCondition .= " AND T1.SupplierSysId = $SupplierSysId ";
        }
        if (isset($post['supplierName']) && !empty($post['supplierName'])) {
            $supplierName = $post['supplierName'];
            $whereCondition .= " AND ( SupplierName = '" . $supplierName . "' OR CompanyName = '" . $supplierName . "' OR  SupplierName LIKE '" . $supplierName . "%' OR CompanyName LIKE '" . $supplierName . "%' ) ";
        }
        if (isset($post['supplierservices']) && !empty($post['supplierservices'])) {
            $supplierservices = $post['supplierservices'];
            $whereCondition .= " AND ( ',' + ServicesType + ',' like '%,' + '$supplierservices' + ',%' )";
        }
        if (isset($post['hidden_city_id']) && !empty($post['hidden_city_id'])) {
            $hidden_city_id = $post['hidden_city_id'];
            $whereCondition .= " AND ( ',' + ExpertiseDestinationsId + ',' like '%,' + '$hidden_city_id' + ',%' )";
        }
        if (isset($post['hidden_location_id']) && !empty($post['hidden_location_id'])) {
            $hidden_location_id = $post['hidden_location_id'];
            $whereCondition .= " AND ( ',' + otherlocationcity + ',' like '%,' + '$hidden_location_id' + ',%' OR CitySysId = '$hidden_location_id' )";
        }
        if (isset($post['contactName']) && !empty($post['contactName'])) {
            $contactName = $post['contactName'];
            $whereCondition .= " AND ( ContactPerson = '$contactName' OR ContactPerson LIKE '$contactName%' )";
        }
        if (isset($post['phoneNo']) && !empty($post['phoneNo'])) {
            $phoneNo = $post['phoneNo'];
            $whereCondition .= " AND ( ContactNo = '$phoneNo' OR ContactNo LIKE '$phoneNo%' )";
        }
        if (isset($post['emailid']) && !empty($post['emailid'])) {
            $emailId = $post['emailid'];
            $whereCondition .= " AND (EmailId LIKE '$emailId%' )";
        }
        $select = $this->db->select()
                ->from(array("T1" => "TB_IC_Supplier"), array('*'))
                ->joinLeft(array('T2' => 'TB_Supplier_Wallet'), 'T2.SupplierSysId=T1.SupplierSysId', array('BalanceAmount'))
                ->joinLeft(array('T3' => 'TB_Master_Currency'), 'T3.CurrencyType=T2.CurrencyType', array('Symbol'))
                ->where($whereCondition)
                ->where("T1.IsActive=?", 1)
                ->order("T1.SupplierSysId DESC");
        return $select;
    }

    public function getSupplierDetails($AgencySysId, $SupplierSysId) {
        $select = $this->db->select()
                ->from(array("T1" => "TB_IC_Supplier"), array('*'))
                ->joinLeft(array('T2' => 'TB_Supplier_Wallet'), 'T2.SupplierSysId=T1.SupplierSysId', array('BalanceAmount'))
                ->joinLeft(array('T3' => 'TB_Master_Currency'), 'T3.CurrencyType=T2.CurrencyType', array('Symbol'))
                ->where("T1.XRefAgencySysId=?", $AgencySysId)
                ->where("T1.SupplierSysId=?", $SupplierSysId)
                ->where("T1.IsActive=?", 1)
                ->order("T1.SupplierSysId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getSupplierWalletData($SupplierSysId) {
        $CreditArray = array('*');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Supplier_Wallet"), $CreditArray);
        $select->joinLeft(array('T3' => 'TB_Master_Currency'), 'T3.CurrencyType=tb1.CurrencyType', array('Symbol'));
        $select->where("tb1.SupplierSysId =?", $SupplierSysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkSupplierCreditLimitOtp($WalletId, $TransactionId = NULL) {
        $CreditArray = array('SupplierSysId', 'WalletId', 'BalanceAmount');
        $CreditHistoryArray = array('OTPCode', 'CodeValidFrom', 'CodeValidTo', 'TransactionType');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Supplier_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Supplier_Wallet_History"), "tb1.WalletId = tb2.WalletId", $CreditHistoryArray);
        $select->where("tb1.WalletId =?", $WalletId);
        if ($TransactionId) {
            $select->where("tb2.TransactionId =?", $TransactionId);
        }
//        $select->where("tb2.IsActive =?", 0);
//        $select->where("tb2.IsMarkForDel =?", 0);
        $select->order("tb2.TransactionId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getCreditBalanceSupplier($WalletId, $SupplierSysId) {
        $CreditArray = array("BalanceAmount");
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Supplier_Wallet"), $CreditArray);
        $select->where("tb1.WalletId =?", $WalletId);
        $select->where("tb1.SupplierSysId =?", $SupplierSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        $getBalance = isset($result['BalanceAmount']) ? $result['BalanceAmount'] : 0;
        return $getBalance;
    }

    public function checkWalletHistoryFromSupplier($SupplierSysId) {
        $CreditArray = array('SUM(TotalAmount) AS TotalAmount');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Supplier_Wallet_History"), $CreditArray);
        $select->where("tb1.SupplierId =?", $SupplierSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyCreditLimitDataBySupplierId($SupplierSysId) {
        $CreditArray = array('*');
        $CreditHistoryArray = array('UpdateDate as historyUpdateDate', 'Remarks', 'TransactionType', 'Balance as historyBalanceAmount', 'TransactionReference', 'PaymentMode', 'TransactionId', 'TotalAmount', 'TPSysId', 'ReferenceType');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Supplier_Wallet"), $CreditArray);
        $select->joinLeft(array('tb2' => "TB_Supplier_Wallet_History"), "tb1.WalletId = tb2.WalletId", $CreditHistoryArray);
        if ($SupplierSysId != '') {
            $select->where("tb1.SupplierSysId =?", $SupplierSysId);
        }
        if (isset($this->filterArray['FromDate']) && !empty($this->filterArray['FromDate']) && isset($this->filterArray['ToDate']) && !empty($this->filterArray['ToDate'])) {
            $select->Where('CONVERT(date,tb2.UpdateDate) >= ?', $this->filterArray['FromDate']);
            $select->Where('CONVERT(date,tb2.UpdateDate) <= ?', $this->filterArray['ToDate']);
        }
        $select->where("tb2.IsActive =?", 1);
        $select->where("tb2.IsMarkForDel =?", 0);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order("tb2.UpdateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getCurrencyRateWithMarkup($AgencySysId, $FromCurrency, $ToCurrency) {
        if ($FromCurrency === $ToCurrency) {
            return 1.0;
        }
        $select = $this->db->select()
                ->from(['tb1' => 'TB_Master_Currency'], ['CurrencyType', 'Symbol'])
                ->where('tb1.Symbol IN (?)', [$FromCurrency, $ToCurrency]);
        $currencies = $this->db->fetchAll($select);
        $currencyMap = [];
        foreach ($currencies as $row) {
            $currencyMap[trim($row['Symbol'])] = $row['CurrencyType'];
        }
        $FCurrencyType = isset($currencyMap[$FromCurrency]) ? $currencyMap[$FromCurrency] : null;
        $TCurrencyType = isset($currencyMap[$ToCurrency]) ? $currencyMap[$ToCurrency] : null;
        if ($FCurrencyType === null || $TCurrencyType === null) {
            return 1.0;
        }
        $rateRow = $this->db->fetchRow(
                $this->db->select()
                        ->from(['tb1' => 'TB_IC_CurrencyConversion'], ['Rate'])
                        ->where('tb1.FCurrencyType = ?', $FCurrencyType)
                        ->where('tb1.TCurrencyType = ?', $TCurrencyType)
        );

        $rate = isset($rateRow['Rate']) ? (float) $rateRow['Rate'] : 1;
        $agencyRow = $this->db->fetchRow(
                $this->db->select()
                        ->from(['tb1' => 'TB_Agency'], ['ExchangeRateMaarkup'])
                        ->where('tb1.AgencySysId = ?', $AgencySysId)
        );

        $markup = isset($agencyRow['ExchangeRateMaarkup']) ? (float) $agencyRow['ExchangeRateMaarkup'] : 0.0;
        return $rate + ($rate * $markup / 100);
    }

    public function getFranchiseeDistributorPaymentReport($FranchiseSysId, $paid = 0, $distributorPaid = 0) {
        // Step 1: Get all Distributor / Agency IDs for the given Franchise
        $CreditArray = ["AgencySysId", "Title", "FirmType"];
        $select = $this->db->select()
                ->from(['tb1' => "TB_Agency"], $CreditArray)
                ->where("tb1.IsActive = ?", 1)
                ->where("(tb1.distributorId = $FranchiseSysId OR tb1.AgencySysId = $FranchiseSysId)")
                ->where("tb1.IsMarkForDelete = ?", 0);

        $agencyData = $this->db->fetchAll($select);
        $agencyTitle = [];
        foreach ($agencyData as $agency) {
            $id = $agency['AgencySysId'];
            $agencyTitle[$id] = $agency['Title'];
        }
        $AgencySysIds = array_column($agencyData, 'AgencySysId');

        // Step 2: Fetch Commission Data
        $CreditArray22 = [
            'CommInvSysId',
            'FranchiseSysId',
            'FinalCommission',
            'Remark',
            'Status',
            'CGST',
            'SGST',
            'IGST',
            'TDS',
            'InvoiceNumber',
            'InvoiceDate',
        ];
        $select = $this->db->select()
                ->from(['t4' => "Tb_Agency_Commission_Invoice"], $CreditArray22);

        if ($paid == 1) {
            $select->where("t4.Status = ?", 1);
        } else {
            $select->where("t4.Status = 0 OR t4.Status IS NULL");
        }
        if ($distributorPaid == 1) {
            $select->where("t4.DistributorStatus = ?", 1);
        } else {
            $select->where("t4.DistributorStatus = 0 OR t4.DistributorStatus IS NULL");
        }

        if (!empty($this->filterArray['FromDate']) && !empty($this->filterArray['ToDate'])) {
            $FromDate = date('Y-m-d', strtotime($this->filterArray['FromDate']));
            $ToDate = date('Y-m-d', strtotime($this->filterArray['ToDate']));
            $select->where("CONVERT(date,(t4.InvoiceDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }

        $select->where("t4.FranchiseSysId IN (?)", $AgencySysIds);
//                ->group("t4.FranchiseSysId");
//echo $select; die('data');
        $commissionData = $this->db->fetchAll($select);

        $response = [];
     
        foreach ($commissionData as $row) {
            $response[] = [
                'Title' => $agencyTitle[$row['FranchiseSysId']],
                'CommInvSysId' => $row['CommInvSysId'],
                'FranchiseSysId' => $row['FranchiseSysId'],
                'FinalCommission' => $row['FinalCommission'],
                'Remark' => $row['Remark'],
                'Status' => $row['Status'],
                'CGST' => $row['CGST'],
                'SGST' => $row['SGST'],
                'IGST' => $row['IGST'],
                'TDS' => $row['TDS'],
                'InvoiceNumber' => $row['InvoiceNumber'],
                'InvoiceDate' => $row['InvoiceDate']
            ];
        }
        return $response;
    }

    public function getCommissionForDistributorFranchisee($AgencySysId) {
        // Step 1: Fetch agency data based on given ID
        $CreditArray = ["AgencySysId", "Title", "FirmType"];

        $select = $this->db->select()
                ->from(['tb1' => "TB_Agency"], $CreditArray)
                ->where("tb1.IsActive = ?", 1)
                ->where("(tb1.distributorId = $AgencySysId OR tb1.AgencySysId = $AgencySysId)")
                ->where("tb1.IsMarkForDelete = ?", 0);

        $agencyData = $this->db->fetchAll($select);

        // If no agencies found, return early
        if (empty($agencyData)) {
            return [
                'AgencySysId' => $AgencySysId,
                'TotalBusiness' => 0,
                'FinalCommission' => 0,
                'PaidCommission' => 0
            ];
        }

        $AgencySysIds = array_column($agencyData, 'AgencySysId');

        // Step 2: Fetch commission data
        $FranchiseColumn = [
            'tb1.FranchiseSysId',
            'SUM(tb1.IGST) AS TotalIGST',
            'SUM(tb1.CGST) AS TotalCGST',
            'SUM(tb1.SGST) AS TotalSGST',
            'SUM(tb1.Tds) AS TotalTds',
            'SUM(CASE WHEN tb1.Status = 1 THEN tb1.FinalCommission ELSE 0 END) AS TotalBusiness',
            'SUM(CASE WHEN tb1.DistributorStatus = 1 THEN tb1.FinalCommission ELSE 0 END) AS PaidCommission',
            'SUM(CASE WHEN tb1.DistributorStatus = 1 THEN tb1.IGST ELSE 0 END) AS PaidIGST',
            'SUM(CASE WHEN tb1.DistributorStatus = 1 THEN tb1.CGST ELSE 0 END) AS PaidCGST',
            'SUM(CASE WHEN tb1.DistributorStatus = 1 THEN tb1.SGST ELSE 0 END) AS PaidSGST',
            'SUM(CASE WHEN tb1.DistributorStatus = 1 THEN tb1.Tds ELSE 0 END) AS PaidTds',
        ];

        $select = $this->db->select()
                ->from(['tb1' => "Tb_Agency_Commission_Invoice"], $FranchiseColumn)
//        ->joinLeft(['tb2' => "TB_Master_Currency"], "tb1.CurrencyType = tb2.CurrencyType", ["tb2.Symbol"])
//                ->joinLeft(['tb3' => "TB_Agency_Customer_TravelPlan"], "tb1.TPSysId = tb3.TPSysId", [])
//                ->where("tb3.StatusType != ?", 15)
                ->where("tb1.FranchiseSysId IN (?)", $AgencySysIds)
                ->where("tb1.IsActive = ?", 1)
                ->where("tb1.Status = ?", 1)
                ->where("tb1.IsMarkForDelete = ?", 0)
                ->group("tb1.FranchiseSysId");

        $commissionData = $this->db->fetchAll($select);

        // Step 3: Map commission data to FranchiseSysId
        $commissionMap = [];
        foreach ($commissionData as $row) {
            $commissionMap[$row['FranchiseSysId']] = $row;
        }

        // Step 4: Aggregate commission values
        $TotalBusiness = $FinalCommission = $PaidCommission = $TotalIGST = $TotalCGST = $TotalSGST = $TotalTds = $PaidIGST = $PaidCGST = $PaidSGST = $PaidTds = 0.0;

        foreach ($agencyData as $agency) {
            $id = $agency['AgencySysId'];
            $TotalBusiness += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['TotalBusiness'] : 0;
            $FinalCommission += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['FinalCommission'] : 0;
            $PaidCommission += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['PaidCommission'] : 0;
            $TotalIGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['TotalIGST'] : 0;
            $TotalCGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['TotalCGST'] : 0;
            $TotalSGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['TotalSGST'] : 0;
            $TotalTds += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['TotalTds'] : 0;
            $PaidIGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['PaidIGST'] : 0;
            $PaidCGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['PaidCGST'] : 0;
            $PaidSGST += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['PaidSGST'] : 0;
            $PaidTds += isset($commissionMap[$id]) ? (float) $commissionMap[$id]['PaidTds'] : 0;
        }

        // Step 5: Final Response
        return [
            'AgencySysId' => $AgencySysId,
            'TotalBusiness' => $TotalBusiness,
            'FinalCommission' => $FinalCommission,
            'PaidCommission' => $PaidCommission,
            'TotalIGST' => $TotalIGST,
            'TotalCGST' => $TotalCGST,
            'TotalSGST' => $TotalSGST,
            'TotalTds' => $TotalTds,
            'PaidIGST' => $PaidIGST,
            'PaidCGST' => $PaidCGST,
            'PaidSGST' => $PaidSGST,
            'PaidTds' => $PaidTds,
        ];
    }
    public function getFranchiseDistributor($AgencySysId, $filterData = null, $agencyType = 3, $type = 'normal') {
        $agencyColumn = array('RMUserSysId AS ReportingToSysId', 'AgencySysId', 'IsCheckB2BWallet', 'Title', 'PrimaryContactName', 'ContactInfo', 'PrimaryEmail', 'CreateDate', 'IsActive', 'IsApproved', 'countrycode', 'BusinessType', 'FirmType', 'IsPaid');
        $agentColumn = array('UserSysId', 'AgencyCode', 'FirstName as UserFirstName', 'LastName as UserLastName', 'IsB2BAgent');

        $reportingArray = array('FirstName as RFirstName', 'LastName as RLastName');
        $FranchiseColumn = array('sum(Amount) as TotalBusiness', 'sum(AdjustedCommission) as FinalCommission', 'SUM(CASE WHEN tb4.Paid = 1 THEN tb4.AdjustedCommission ELSE 0 END) AS PaidCommission', 'MAX(tb4.CreateDate) as CreateDateC');
        //$CreditHistoryColumn = array('sum(Amount) as TotalBusiness');
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency"), $agencyColumn);
        $select->joinLeft(array('tb2' => "TB_Agency_User"), "tb1.AgencySysId = tb2.AgencySysId AND tb1.PrimaryUserSysId = tb2.UserSysId", $agentColumn);
        $select->joinLeft(array('tb5' => 'TB_Agency_User'), 'tb1.RMUserSysId=tb5.UserSysId', $reportingArray);
        $select->joinLeft(array('tb4' => 'Tb_Agency_Commission'), 'tb1.AgencySysId=tb4.FranchiseSysId', $FranchiseColumn);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan"), "tb4.TPSysId = tb3.TPSysId", array());
        $select->where("tb3.StatusType !=?", 15);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_Country"), "tb2.CountrySysId = tb7.ContId", array('Title as CountryTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb2.CitySysId = tb8.CityId", array('Title as CityTitle'));
        $select->joinLeft(array('tb9' => "TB_Agency_MarketPlace"), "tb1.AgencyMarketPlaceSysId = tb9.MarketPlaceSysId", array('Title as MarkupTitle'));
        $select->joinLeft(array('tb10' => "TB_Agency_Mapping"), "tb1.AgencySysId = tb10.AgencySysId", array('IsAutoFTEnabled'));
        $select->joinLeft(array('tb11' => "TB_Agency_Reward_Points"), "tb1.AgencySysId = tb11.AgencySysId", array('IsActive AS rewardPoints'));
        $select->where("tb1.MasterAgencySysId =?", $AgencySysId);
        $select->where("(tb1.FirmType = 6 OR tb1.distributorId != 0)");
        $select->where("tb2.IsB2BAgent =?", 3);
        $select->where("tb1.IsMarkForDelete =?", 0);
        //$select->where("tb2.UserRole = 0 OR tb2.UserRole IS NULL");

        if (!empty($filterData['agencymobile'])) {
            $select->Where('tb1.ContactInfo LIKE ?', '%' . $filterData['agencymobile'] . '%');
        }
        if (!empty($filterData['agencyname'])) {
            $select->Where('lower(tb1.Title) LIKE \'%' . strtolower(trim($filterData['agencyname'])) . '%\' OR lower(tb1.PrimaryContactName) LIKE \'%' . strtolower(trim($filterData['agencyname'])) . '%\'');
        }
        if (!empty($filterData['emailid'])) {
            $select->Where('tb1.PrimaryEmail LIKE ?', '%' . $filterData['emailid'] . '%');
        }
        //        if (!empty($filterData['agencycode'])) {
        //            $select->Where('tb2.AgencyCode = ?', $filterData['agencycode']);
        //        }
        if (isset($filterData['IsApproved'])) {
            $select->Where('tb1.IsApproved = ?', $filterData['IsApproved']);
        }

        if (!empty($filterData['ToCreateDate']) && !empty($filterData['FromCreateDate'])) {
            $ToDate = date('Y-m-d', strtotime($filterData['ToCreateDate']));
            $FromDate = date('Y-m-d', strtotime($filterData['FromCreateDate']));
            $select->where("CONVERT(date,(tb4.CreateDate)) BETWEEN '$FromDate' AND '$ToDate'");
        }
        if (isset($filterData['IsActive']) && $filterData['IsActive'] != '') {
            $select->Where('tb1.IsActive = ?', $filterData['IsActive']);
        }

        if (isset($filterData['ReportingToFilter']) && (int) $filterData['ReportingToFilter'] > 0) {
            $select->Where('tb1.RMUserSysId = ?', (int) $filterData['ReportingToFilter']);
        }
        $select->order("tb1.AgencySysId DESC");
        $select->group(array('tb11.IsActive', 'tb1.IsPaid', 'tb1.FirmType', 'tb1.CreateDate', 'tb1.AgencySysId', 'tb1.Title', 'tb1.PrimaryContactName', 'tb1.ContactInfo', 'tb1.PrimaryEmail', 'tb1.IsActive', 'tb2.AgencyCode', 'tb2.UserSysId', 'tb1.RMUserSysId', 'tb5.FirstName', 'tb5.LastName', 'tb1.IsApproved', 'tb7.Title', 'tb8.Title', 'tb2.FirstName', 'tb2.LastName', 'tb2.IsB2BAgent', 'tb9.Title', 'tb10.IsAutoFTEnabled', 'tb1.countrycode', 'tb1.BusinessType', 'tb1.IsCheckB2BWallet'));
        //        echo $select; exit;

        if ($type == 'export') {
            return $this->db->fetchAll($select);
        } else {
            return $select;
        }
    }
}