<?php

/**
 * Class TblInsurance
 *
 * @name	Insurance Model
 * @author	Ranvir Singh
 * @created	07 Nov 2016
 * @updated	08 Nov 2016
 * @version 	1.0
 * @copyright 	Catabatic India Pvt Ltd
 * Handle Insurance Related functions
 *
 */
class Travel_Model_TblInsurance {

    protected $db;

    /*     * ************************************ */

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

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

    public function getInsurancePolicies_RV() {

        $sql = " SELECT count(t3.MasterPolicyRef) as TotalChildPolicy, t1.MasterPolicySysId, t1.LaunchDate, t1.ExpiryDate, t1.PolicyId, t1.GTXCommision, t1.IsActive,
            t2.Title as CompanyName, t2.Logo , t3.MasterPolicyRef 
from TB_Insurance_Policy_Master as t1 LEFT JOIN TB_Insurance_Company as t2 ON t1.InsuCompSysId = t2.InsuCompSysId
LEFT JOIN TB_Insurance_Policy_Childs as t3 ON  t1.MasterPolicySysId = t3.MasterPolicyRef

Where t1.IsMarkForDelete = '0' AND t1.IsActive = 1
GROUP BY t1.MasterPolicySysId, t1.MasterPolicySysId, t1.LaunchDate, t1.ExpiryDate, t1.PolicyId, t1.GTXCommision, t1.IsActive, t2.Title, t3.MasterPolicyRef ,t2.Logo 
        ";

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

        return $rowset;
    }

    public function getTotalRecords($table, $colsArr, $whereArr) {
        $select = $this->db->select();
        $select->from($table, $colsArr);

        foreach ($whereArr as $col => $val) {
            $select->where(" $col =?", $val);
        }

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

    public function getResultByID_RV($sid) {
        $sql = " SELECT count(t3.MasterPolicyRef) as TotalChildPolicy, t1.MasterPolicySysId, t1.LaunchDate, t1.ExpiryDate, t1.PolicyId, t1.GTXCommision, t1.IsActive, t1.Title,
            t1.MaxDuration, t1.MaxSumInsured, t1.GTXCommision, t1.ShortInfo, t1.DetailedInfo, t1.Inclusions, t1.Exclusions, t1.KeyFeatures, t1.TandC,
            t2.Title as CompanyName, t2.InsuCompSysId , t2.SupplierSysId , t1.CurrencyType, t3.MasterPolicyRef 
from TB_Insurance_Policy_Master as t1 LEFT JOIN TB_Insurance_Company as t2 ON t1.InsuCompSysId = t2.InsuCompSysId
LEFT JOIN TB_Insurance_Policy_Childs as t3 ON  t1.MasterPolicySysId = t3.MasterPolicyRef

Where t1.IsMarkForDelete = '0' AND t1.IsActive = 1 AND t1.MasterPolicySysId = $sid
GROUP BY t1.MasterPolicySysId, t1.MasterPolicySysId, t1.LaunchDate, t1.ExpiryDate, t1.PolicyId, t1.GTXCommision, t1.IsActive, t2.Title, t3.MasterPolicyRef, t1.CurrencyType, t1.Title,
t1.MaxDuration, t1.MaxSumInsured, t1.GTXCommision, t1.ShortInfo, t1.DetailedInfo, t1.Inclusions, t1.Exclusions, t1.KeyFeatures, t1.TandC,
t2.SupplierSysId , t2.InsuCompSysId 
        ";
        $result = $this->db->fetchRow($sql);
        return $result;
    }

    public function getInsurerListBySupplierId($sid) {
        $sql = " SELECT t1.InsuCompSysId, t1.Title, t1.SupplierSysId FROM TB_Insurance_Company t1 WHERE IsActive='1' AND IsMarkForDelete='0' AND t1.SupplierSysId = $sid ";
        $result = $this->db->fetchAll($sql);
        return $result;
    }

    public function getMasterPolicyListByInsurerId($sid) {
        $sql = " SELECT t1.MasterPolicySysId, t1.PolicyId  FROM TB_Insurance_Policy_Master t1 WHERE t1.InsuCompSysId = $sid ";
        $result = $this->db->fetchAll($sql);
        return $result;
    }

    public function alreadyExists($masterPolicySysId, $childPolicy) {
        $sql = " SELECT ChildSysId FROM TB_Insurance_Policy_Childs WHERE ChildPolicyId = '$childPolicy' AND MasterPolicyRef = '$masterPolicySysId' ";
        $result = $this->db->fetchRow($sql);

        if (!empty($result))
            return $result['ChildSysId'];
        else
            return false;
    }

    public function getListingChildPolicy($MasterPolicySysId) {
        $sql = " SELECT t1.ChildPolicyId, t1.IsIssued, t1.IssueDate, t2.PolicyId, t3.Title as CompanyName, t2.LaunchDate, t2.ExpiryDate
from TB_Insurance_Policy_Childs as t1 
LEFT JOIN TB_Insurance_Policy_Master as t2 ON t1.MasterPolicyRef = t2.MasterPolicySysId
LEFT JOIN TB_Insurance_Company as t3 on t3.InsuCompSysId = t2.InsuCompSysId

Where t1.IsMarkForDelete = '0' AND t1.IsActive = '1' AND t2.MasterPolicySysId = '$MasterPolicySysId'
";
        $result = $this->db->fetchAll($sql);
        return $result;
    }

    public function getInsuranceCompanyList() {
        $select = $this->db->select()
                ->from("TB_Insurance_Company")
                ->where("IsMarkForDelete =?", 0)
                ->where("IsActive =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTermAndConditionList($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Terms_Conditions")
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getAgencyInsuranceCompany($agencyId) {
        $select = $this->db->select()
                ->from("TB_Agency_InsuCompany")
                ->where("IsmarkForDel =?", 0)
                ->where("IsActive =?", 1)
                ->where("AgencySysId =?", $agencyId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getPaymentSettingList($agencySysId) {
        $select = "SELECT t3.title,t1.Id,t1.AgencySysId,t1.PaymentUrl,t1.PaymentGatewayMode,t1.PaymentMerchantId,t2.IsPaymentGateway,t1.PaymentSecretPwd,t1.PaymentOption,t1.ProdId,
            t1.PaymentUrl,t1.AccountNumber,t1.BankName,t1.BranchName,t1.IFSC,t1.RespHashKey,t1.ReqHashKey,t1.ReqAESKey,t1.ReqAESSaltKey,t1.ResAESKey,t1.ResAESSaltKey,t1.CreateDate,t1.UpdateDate
            from TB_Payment_Gateway_Setting as t1 
            LEFT JOIN TB_Agency as t2 ON t1.AgencySysId = t2.AgencySysId
            LEFT JOIN TB_Master_PaymentGatewayType as t3 ON t1.PaymentOption = t3.PaymentGatewayType
            Where t2.AgencySysId = '$agencySysId'";
        //        $select = $this->db->select()
        //                ->from("TB_Payment_Gateway_Setting")
        //                ->where("AgencySysId =?",$agencySysId);

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

    public function getEmailSettingList($agencySysId) {

        $sql = "SELECT IsEmailSetting,EmailKey FROM TB_Agency WHERE AgencySysId='$agencySysId'";
        $result = $this->db->fetchRow($sql);
        if (!empty($result))
            return $result;
        else
            return false;
    }

    public function getOtherSettingList($agencySysId) {

        $sql = "SELECT T2.IsWhatsApp, T1.IsEnabledB2BOtpLogin, T1.IsHotelInventory,T1.IsGTXPaymentGateway,T1.IsB2bSite,T1.IsDisplayHelloGTX,T1.IsTG,T1.IsGTXNetwork,T1.IsActiveSubscription,T1.IsSMSApi,T1.IsGTXNetworkWeb,T1.IsGTXLite "
                . " FROM TB_Agency AS T1 LEFT JOIN TB_Agency_Mapping AS T2 ON T1.AgencySysId = T2.AgencySysId"
                . " WHERE T1.AgencySysId='$agencySysId'";
       
        $result = $this->db->fetchRow($sql);
        if (!empty($result))
            return $result;
        else
            return false;
    }

    public function getGTXPaymentSettingList($agencySysId) {

        $sql = "SELECT IsGTXPaymentGateway FROM TB_Agency WHERE AgencySysId='$agencySysId'";
        $result = $this->db->fetchRow($sql);
        if (!empty($result))
            return $result;
        else
            return false;
    }
    
    public function getService($agencySysId) {
        $select = $this->db->select()
                ->from(("Tb_Agency_Services"))
                ->where("AgencySysId =?", $agencySysId)
                ->where("IsMarkForDelete =?", 0);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getIsB2CUrlSetting($agencySysId) {
        $select = $this->db->select()
                ->from(("TB_Agency"), array("IsB2CUrl", "B2CUrl"))
                ->where("AgencySysId =?", $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getIsB2CSite($agencySysId) {
        $select = $this->db->select()
                ->from(("TB_Agency"), array("IsSSL", "Url", "IsB2CSite", "IsSiteType"))
                ->where("AgencySysId =?", $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getIsB2BSite($agencySysId) {
        $select = $this->db->select()
                ->from(("TB_Agency"), array("IsB2bSSL", "IsB2bSite", "IsB2bSiteURL"))
                ->where("AgencySysId =?", $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getSecurityCheck($agencySysId) {
        $select = $this->db->select()
                ->from(("TB_Agency"), array("SecurityKey"))
                ->where("AgencySysId =?", $agencySysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function sslWebsite($agencySysId) {
        $select = $this->db->select()
                ->from(array('T1' => "TB_Agency"))
                ->joinLeft(array('T2' => "TB_Agency_Mapping"), "T1.AgencySysId = T2.AgencySysId")
                ->where("T1.AgencySysId =?", $agencySysId);
        $result = $this->db->fetchAll($select);
        // echo "<pre>"; print_r($result); die;
        return $result;
    }

    public function updateAgencyInsuranceCompany($agencyInsuData = array(), $agentInsuSysId = NULL) {
        $where = array('AgentInsuSysId =?' => $agentInsuSysId);
        return $this->db->update('TB_Agency_InsuCompany', $agencyInsuData, $where);
    }

    public function addAgencyInsuranceCompany($agencyInsuData = array()) {

        $agencyInsuDataForDb = array(
            'AgencySysId' => $agencyInsuData['AgencySysId'],
            'InsuCompSysId' => $agencyInsuData['InsuCompSysId'],
            'TrxCurrency' => $agencyInsuData['CurrencyId'],
            'Commision' => $agencyInsuData['Commision'],
            'AllowCredit' => $agencyInsuData['AllowCredit'],
            'CreditLimit' => $agencyInsuData['CreditLimit'],
            'Balance' => $agencyInsuData['Balance'],
            'IsActive' => 1,
            'IsmarkForDel' => 0,
        );

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

    public function alreadyExistsPaymentSetting($agencySysId) {
        $sql = " SELECT AgencySysId,CreateDate FROM TB_Payment_Gateway_Setting WHERE AgencySysId = '$agencySysId'";
        $result = $this->db->fetchRow($sql);

        if (!empty($result))
            return $result;
        else
            return false;
    }

    public function addPaymentSetting($agencyPaymentSettingArray = array()) {

        $agencyPaymentSettingArray = array(
            'AgencySysId' => $agencyPaymentSettingArray['AgencySysId'],
            'PaymentUrl' => $agencyPaymentSettingArray['PaymentUrl'],
            'PaymentMerchantId' => $agencyPaymentSettingArray['PaymentMerchantId'],
            'PaymentSecretPwd' => $agencyPaymentSettingArray['PaymentSecretPwd'],
            'AccountNumber' => $agencyPaymentSettingArray['AccountNumber'],
            'BankName' => $agencyPaymentSettingArray['BankName'],
            'BranchName' => $agencyPaymentSettingArray['BranchName'],
            'IFSC' => $agencyPaymentSettingArray['IFSC'],
            'RespHashKey' => $agencyPaymentSettingArray['RespHashKey'],
            'ReqHashKey' => $agencyPaymentSettingArray['ReqHashKey'],
            'ReqAESKey' => $agencyPaymentSettingArray['ReqAESKey'],
            'ReqAESSaltKey' => $agencyPaymentSettingArray['ReqAESSaltKey'],
            'ResAESKey' => $agencyPaymentSettingArray['ResAESKey'],
            'ResAESSaltKey' => $agencyPaymentSettingArray['ResAESSaltKey'],
            'UpdateDate' => $agencyPaymentSettingArray['CreateDate'],
            'CreateDate' => $agencyPaymentSettingArray['CreateDate'],
            'PaymentOption' => $agencyPaymentSettingArray['PaymentOption'],
            'ProdId' => $agencyPaymentSettingArray['ProdId'],
        );

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

    //    public function addEmailSetting($agencyPaymentSettingArray=array()) {
    ////        echo "<pre>";print_r($agencyPaymentSettingArray);die;
    //        $emailSetting = $agencyPaymentSettingArray['IsEmailSetting'];
    //        $emailKey = $agencyPaymentSettingArray['EmailKey'];
    //        $agencySysId = $agencyPaymentSettingArray['AgencySysId'];
    //        $sql = "INSERT INTO TB_Agency set IsEmailSetting = '$emailSetting' and EmailKey='$emailKey' WHERE AgencySysId='$agencySysId'";
    ////        echo $sql;die;
    //        $this->query($sql);
    //        return $this->db->lastInsertId('TB_Agency');
    //        
    //    }

    public function insertData($tablename, array $editData) {

        $dbtable = new Zend_Db_Table($tablename);
        try {
            $return = $dbtable->insert($editData);
        } catch (Zend_Exception $e) {
            $return = $e->getMessage();
        }
        // echo $return; exit;
        return $return; // return row effected or not
    }

    public function addEmailSetting($tablename, array $editData, array $where) {

        $dbtable = new Zend_Db_Table($tablename);
        try {
            $return = $dbtable->update($editData, $where);
        } catch (Zend_Exception $e) {
            $return = $e->getMessage();
        }
        // echo $return; exit;
        return $return; // return row effected or not
    }

    public function getAgencyMappingData($table, $colsArr, $whereArr) {
        $select = $this->db->select();
        $select->from($table, $colsArr);
        foreach ($whereArr as $col => $val) {
            $select->where(" $col =?", $val);
        }
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getMasterPaymentGatewayType() {
        $select = $this->db->select()
                ->from("TB_Master_paymentGatewayType")
                ->where("IsmarkForDel =?", 0)
                ->where("IsActive =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getMasterAPISource() {
        $select = $this->db->select()
                ->from("TB_Master_API_Source")
                ->where("IsmarkForDel =?", 0)
                ->where("IsActive =?", 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

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

    public function checkAgencyAPISource($AgencySysId, $APISourceSysId) {
        $select = $this->db->select()
                ->from("TB_Agency_API")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("APISourceSysId =?", $APISourceSysId)
                ->order("APISysId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function checkAgencyDistributionData($AgencySysId, $RefSysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Distribution")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("RefSysId =?", $RefSysId)
                ->order("DisSysId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

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

    public function checkAgencyDestinationExpert($AgencySysId, $DestinationSysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Destination_Expert")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("DestinationSysId =?", $DestinationSysId)
                ->order("DesExpSysId DESC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function getAgencyDestinationExpert($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Destination_Expert")
                ->where("AgencySysId =?", $AgencySysId)
                ->where("IsmarkForDel =?", 0)
                ->where("IsActive =?", 1)
                ->order("DesExpSysId ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function checkDataInAgencyMapping($AgencySysId) {
        $select = $this->db->select()
                ->from("TB_Agency_Mapping", array('AgencySysId'))
                ->where("AgencySysId =?", $AgencySysId);
        $result = $this->db->fetchRow($select);
        $AgencySysId = isset($result['AgencySysId']) ? $result['AgencySysId'] : null;
        return $AgencySysId;
    }

    public function updateTable($tablename, $editData, $where)
    {
        $update = $this->db->update("$tablename", $editData, $where);
        return $update;
    }

}