<?php

class Travel_Model_TblAgencyCustomerTrx {

    private $db = NULL;
    public $paymentMdl;
    private $baseUrl = '';
    private $_custtravelObj = '';
    private $_dbObj = '';
    public $ReportingToSysId = 0;

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

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

    public function addNewTransaction() {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        if (isset($_SESSION['VOUCHER_DETAIL']) && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['VoucherStatus'] == 1 && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['ResponseStatus'] == 1) {
            $bookingId = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['BookingId'];

            $fromD = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['CheckInDate'];
            $accoBookingId = @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'];
            $todate = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['CheckOutDate'];
            $currentDate = date("Y/m/d");
            $CurrencyType = 'INR';
            $price = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['HotelRoomsDetails'][0]['Price']['PublishedPrice'];
            $discount = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['HotelRoomsDetails'][0]['Price']['Discount'];
            $invoice = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['InvoiceNo'];
            $tax = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['HotelRoomsDetails'][0]['Price']['Tax'];
            $TrxSysId = 'HTL-0' . @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'];
            $insertBooking = array(
                'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId, 'CustomerSysId' => @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'],
                'TrxSysId' => $TrxSysId, 'MPSysId' => 0, 'InvoiceId' => $invoice, 'TrxOrdDocId' => '0', 'TrxDate' => $currentDate,
                'TrxType' => '1', 'TrxPayMediaType' => '1', 'CrPartyAccSysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
                'DrPartyAccSysId' => @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'], 'CrPartyName' => 'Agent', 'DrPartyName' => 'Customer',
                'CurrencyType' => $CurrencyType, 'Amount' => $price,
                'TotalTax' => '0', 'TotalDiscount' => $discount,
                'NetAmount' => $price, 'PaidAmount' => $price,
                'TBPoints' => 0, 'IsActive' => 1
            );

            try {
                $this->db->insert('TB_Agency_Customer_Trx', $insertBooking);
                $lastUserID = $this->db->lastInsertId('TB_Agency_Customer_Trx');
            } catch (Exception $e) {
                die('There has been an error. ' . $e->getMessage());
            }

            return 1;
        }
    }

    public function addNewTransactionTRX() {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        $fromD = date('Y-m-d H:i:s', strtotime($_SESSION['chekInDate']));
        $todate = date('Y-m-d H:i:s', strtotime($_SESSION['chekOutDate']));
        $price = $_SESSION['SUPLIERAMT'];
        $bookingId = ' ';
        $accoBookingId = @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'];
        $currentDate = date("Y/m/d");
        $CurrencyType = 'INR';


        $discount = '0';
        $invoice = '0';
        $tax = '0';
//$TrxSysId='HTL-0'.@$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'];
        $sql = "SELECT TOP 1 XRefTrxNo FROM TB_Agency_Customer_Trx where  CustomerSysId='" . @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'] . "' order by TrxSysId DESC";
        $rowset = $this->db->query($sql)->fetchAll();
// echo count($rowset);
        if (count($rowset) > 0) {
            foreach ($rowset as $TrxSysI) {
                $XRefTrxNo = $TrxSysI['XRefTrxNo'];
                $tmp = explode('-', $XRefTrxNo);
                $temp1 = $tmp['1'] + 1;
                $XRefTrxNo = 'HTL-0' . $temp1;
            }
        } else {
            $XRefTrxNo = 'HTL-01';
        }
        $insertBooking = array(
            'AgencySysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
            'CustomerSysId' => @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'],
            'XRefTrxNo' => $XRefTrxNo,
            'MPSysId' => '0', 'InvoiceId' => $invoice,
            'TrxOrdDocId' => '0', 'TrxDate' => $currentDate,
            'TrxType' => '1', 'TrxPayMediaType' => '1',
            'CrPartyAccSysId' => $sessionLogin_user->intLoggedinUserAgencySysId,
            'DrPartyAccSysId' => @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'],
            'CrPartyName' => 'Agent', 'DrPartyName' => 'Customer',
            'CurrencyType' => $CurrencyType, 'Amount' => $price,
            'TotalTax' => '0', 'TotalDiscount' => $discount,
            'NetAmount' => $price, 'PaidAmount' => $price,
            'TBPoints' => '0', 'IsActive' => '1'
        );


        try {
            $this->db->insert('TB_Agency_Customer_Trx', $insertBooking);
            $lastUserID = $this->db->lastInsertId('TB_Agency_Customer_Trx');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }

        return 5;
    }

    public function addTravelPlan() {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        if (isset($_SESSION['VOUCHER_DETAIL']) && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['VoucherStatus'] == 1 && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['ResponseStatus'] == 1) {
            $sqlMAX = "SELECT TOP 1 TPIntSysId FROM TB_Agency_Customer_TravelPlan_Accom order by TPIntSysId Desc";
            $maxId = $this->db->query($sqlMAX)->fetchAll();
            foreach ($maxId as $masIDS) {
                $maxUserID = $masIDS['TPIntSysId'];
            }

            $bookingId = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['BookingId'];
            $fromD = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['CheckInDate'];
            $accoBookingId = @$_SESSION['USER_DETAILS_HTL'][0]['CustomerSysId'];
//$todate=date("Y-m-d H:i:s");
            $todate = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['CheckOutDate'];
            $currentDate = date("Y-m-d H:i:s");
//$CurrencyType='INR';
//        $price='8.55';
//        $discount='0';
            $price = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['HotelRoomsDetails'][0]['Price']['PublishedPrice'];
            $discount = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['HotelRoomsDetails'][0]['Price']['Discount'];
//$invoice='tempinvoic';
            $invoice = @$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['InvoiceNo'];
            $insertTravelPlan_Accom = array(
                "AccoBookingId" => $accoBookingId, "XRefBookingId" => $bookingId, "AgencySysId" => $sessionLogin_user->intLoggedinUserAgencySysId,
                "NetCost" => $price, "UpdateDate" => $currentDate
            );
            try {
                $whereC = "TPIntSysId='" . $maxUserID . "'";
                $this->db->update('TB_Agency_Customer_TravelPlan_Accom', $insertTravelPlan_Accom, $whereC);
// $this->db->insert('TB_Agency_Customer_TravelPlan_Accom', $insertTravelPlan_Accom);
                $lastPlanID = $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Accom');
            } catch (Exception $e) {
                die('There has been an error. ' . $e->getMessage());
            }

            return 1;
        }
    }

    public function getAgentDetail() {
        $sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
        $sql = "SELECT * FROM TB_Agency WHERE AgencySysId='" . $sessionLogin_user->intLoggedinUserAgencySysId . "'";
        return $this->db->query($sql)->fetchAll();
    }

    public function updateAccomoWithBookingID($data, $TPSysId) {


        $where = array('TPIntSysId = ? ' => $TPSysId);
        $this->db->update('TB_Agency_Customer_TravelPlan_Accom', $data, $where);


//        if(isset($_SESSION['VOUCHER_DETAIL']) && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['VoucherStatus']==1 && $_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['ResponseStatus']==1){
//      
//        $sql="UPDATE TB_Agency_Customer_TravelPlan_Accom SET XRefBookingId='".$_SESSION['VOUCHER_DETAIL']['GetBookingDetailResult']['BookingId']."'"
//                . "WHERE SeqId = '".$_SESSION['SeqId']."' ";
//        $this->db->query($sql);
//        }

        return 1;
    }

    public function getBookedPackageDetail($agencySysID, $source, $filterData = null) { 
       //echo "<pre>";print_r($filterData);exit;
        $currentD = date("Y-m-d");
        $agency_customer = array('EmailId', 'Contacts', 'countrycode', 'FirstName', 'LastName', 'IsB2BAgent');
        $select = $this->_dbObj->select();
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"), array("tb2.*", "((select sum(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = '0' AND tb2.TPSysId = adT1.TPSysId AND (adT1.PlanType = 21 OR adT1.PlanType = 19))) as TotalTcs",
            new Zend_Db_Expr("(
        CASE
            WHEN EXISTS (
                SELECT 1 
                FROM TB_Agency_Customer_TravelPlan_AddonServices AS adT14 
                WHERE 
                    adT14.IsMarkForDelete = '0' 
                    AND tb2.TPSysId = adT14.TPSysId 
                    AND adT14.PlanType = 21
            ) THEN 'Included'
            ELSE 'Not Included'
        END
    ) AS TCSIncluded")
            ));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb5' => "TB_Agency"), "tb5.AgencySysId=tb2.AgencySysId", array('TrxCurrency'));
        $select->joinLeft(array('tb6' => "TB_IC_CurrencyConversion"), "tb6.TCurrencyType=tb5.TrxCurrency AND tb6.FCurrencyType = tb2.CurrencyType", array('Rate as ToBaseCurrencyExchangeRate'));
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));
        $select->joinLeft(array('tb44' => "TB_Agency_User"), "tb44.UserSysId=tb2.OpsAgentSysId", array('FirstName as OpsFirstName'));
        $select->joinLeft(array('tb7' => "TB_Agency"), "tb7.AgencySysId=tb3.AgencySysId", array('Title as CompanyName', 'NickId'));
        $select->joinLeft(array('tb8' => "TB_Agency_Customer_TravelPlan_Query"), "tb8.TPSysId=tb2.MasterTPSysId", array('PaymentStatus as QueryPaymentStatus', 'BookingStatus as QueryBookingStatus'));
        $select->joinLeft(array('tb9' => "TB_Agency_Customer_TravelPlan_Mapping"), "tb9.TPSysId=tb2.TPSysId", array('HotelVoucherStatus','FlightVoucherStatus','VisaVoucherStatus','SightseeingVoucherStatus','TransferVoucherStatus','InsuranceVoucherStatus','GuideVoucherStatus','MealVoucherStatus','OtherVoucherStatus'));
        if (!empty($filterData['category'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['category'] . '%');
        }
        if (isset($filterData['proposalId']) && !empty($filterData['proposalId'])) {
            $select->Where('tb2.TPSysId = ? ', $filterData['proposalId']);
        }
        
        if (isset($filterData['OpsAgentSysId']) && !empty($filterData['OpsAgentSysId'])) {
            $select->Where('tb2.OpsAgentSysId = ? ', $filterData['OpsAgentSysId']);
        }
        if (isset($filterData['TPSysIdArr']) && !empty($filterData['TPSysIdArr'])) {
            $select->Where('tb2.TPSysId IN (?)', $filterData['TPSysIdArr']);
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('tb2.DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['bookingdate'])) {
            $date = date('Y-m-d', strtotime($filterData['bookingdate']));
            $select->Where('SUBSTRING(convert(varchar, tb2.StartDate, 120),0,11) = ?', $date);
        }
        if (!empty($filterData['ValidTillcurrentDate']) && !empty($filterData['ValidTillfromDate'])) {
            $ValidTillcurrentDate = date('Y-m-d', strtotime($filterData['ValidTillcurrentDate']));
            $ValidTillfromDate = date('Y-m-d', strtotime($filterData['ValidTillfromDate']));
            $select->where("CONVERT(date,(tb2.ValidTill))  BETWEEN '$ValidTillfromDate' AND '$ValidTillcurrentDate'");
        }
        if($source == 'upcoming') {
            $select->where("CONVERT(date,(tb2.StartDate)) > ?", $currentD);
            $select->order("tb2.StartDate ASC");
        }
        if($source == 'intrip') {
            $select->where("CONVERT(date,(tb2.StartDate)) <= ?", $currentD);
            $select->where("CONVERT(date,(tb2.ValidTill)) >= ?", $currentD);
            $select->order("tb2.StartDate ASC");
        }
        if($source == 'completed') {
            $select->where("CONVERT(date,(tb2.ValidTill)) < ?",$currentD);
            $select->order("tb2.ValidTill ASC");
        }
        if (!empty($filterData['currentDate']) && !empty($filterData['fromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['currentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['fromDate']));
            $select->where("CONVERT(date,(tb2.StartDate))  BETWEEN '$fromDate' AND '$currentDate'");
        }
        if (!empty($filterData['updatecurrentDate']) && !empty($filterData['updatefromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['updatecurrentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['updatefromDate']));
            $select->where("(CONVERT(date,tb2.ApproveDate))  BETWEEN '$fromDate' AND '$currentDate'");
        }
        $ReportingToSysId = $filterData['UserSysId'] . (isset($this->ReportingToSysId) && !empty($this->ReportingToSysId) ? ',' . $this->ReportingToSysId : '');
        if (isset($filterData['PlanType']) && !empty($filterData['PlanType'])) {
            $select->where('tb2.PlanType IN(?)', $filterData['PlanType']);
        } else {
            $select->where('tb2.PlanType IN(?)', array(5));
        }
        $select->where('tb2.IsMarkForDel = 0');
        $select->where('tb2.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123));
        //$select->where("tb2.AgencySysId = $agencySysID OR tb2.AgencySysId IN (SELECT AgencySysId FROM TB_AGENCY AS AT1 WHERE AT1.MasterAgencySysId = $agencySysID AND AT1.AgencyType=2 AND AT1.IsActive = 1 )");
        $select->where("tb2.AgencySysId = $agencySysID"); 
        if ((!empty($filterData['agencyUserFilter']))) {
            $select->Where("tb2.AgentSysId = (?)", $filterData['agencyUserFilter']);
        } else if (!empty($filterData['UserSysId'])) {
            $select->Where("tb2.AgentSysId IN (?)", explode(',', $ReportingToSysId));
        }
        if (isset($filterData['CustomerName']) && !empty($filterData['CustomerName'])) {
            $select->Where('tb3.FirstName LIKE ?', '%' . $filterData['CustomerName'] . '%');
        }
        if (isset($filterData['CustomerEmail']) && !empty($filterData['CustomerEmail'])) {
            $select->Where('tb3.EmailId LIKE ?', '%' . $filterData['CustomerEmail'] . '%');
        }
        if (isset($filterData['CustomerPhone']) && !empty($filterData['CustomerPhone'])) {
            $select->Where('tb3.Contacts = ? ', $filterData['CustomerPhone']);
        }
        if (isset($filterData['source']) && !empty($filterData['source'])) {
            $select->Where('tb2.IsB2BProposal = ? ', $filterData['source']);
        }
        if(isset($filterData['agencyServicesName']) && !empty($filterData['agencyServicesName'])){
            $agencyServicesName = trim($filterData['agencyServicesName']);
            if($agencyServicesName != ''){ 
                $select->Where('tb9.'.$agencyServicesName.' is not null ');
                if(isset($filterData['voucherStatusFilter']) && $filterData['voucherStatusFilter'] != ''){
                    $select->Where('tb9.'.$agencyServicesName.' = ? ', (int)$filterData['voucherStatusFilter']);
                }
            }
            
            
        }
        
//        echo $select;die;
        if(isset($filterData['columnName']) && !empty($filterData['columnName'])){
            $columnName = $filterData['columnName'];
            $columnOrder = $filterData['columnOrder'];
            $select->order("$columnName $columnOrder");
        }else{
            $select->order("tb2.UpdateDate DESC");
        }
        return $select;
    }
    public function getBookedForexDetail($agencySysID, $filterData = null) { 
        $agency_customer = array('EmailId', 'Contacts', 'countrycode', 'FirstName', 'LastName', 'IsB2BAgent');
        $select = $this->_dbObj->select();
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"), array("tb2.*"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb5' => "TB_Agency"), "tb5.AgencySysId=tb2.AgencySysId", array('TrxCurrency'));
        $select->joinLeft(array('tb6' => "TB_IC_CurrencyConversion"), "tb6.TCurrencyType=tb5.TrxCurrency AND tb6.FCurrencyType = tb2.CurrencyType", array('Rate as ToBaseCurrencyExchangeRate'));
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));
        $select->joinLeft(array('tb7' => "TB_Agency"), "tb7.AgencySysId=tb3.AgencySysId", array('Title as CompanyName', 'NickId'));
        $select->joinLeft(array('tb8' => "TB_Agency_Customer_TravelPlan_Query"), "tb8.TPSysId=tb2.MasterTPSysId", array('PaymentStatus as QueryPaymentStatus', 'BookingStatus as QueryBookingStatus'));
        $select->joinLeft(array('tb9' => "TB_Agency_Customer_TravelPlan_Mapping"), "tb9.TPSysId=tb2.TPSysId", array('HotelVoucherStatus','FlightVoucherStatus','VisaVoucherStatus','SightseeingVoucherStatus','TransferVoucherStatus','InsuranceVoucherStatus','GuideVoucherStatus','MealVoucherStatus','OtherVoucherStatus'));

        if (!empty($filterData['category'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['category'] . '%');
        }
        if (isset($filterData['proposalId']) && !empty($filterData['proposalId'])) {
            $select->Where('tb2.TPSysId = ? ', $filterData['proposalId']);
        }
        if (isset($filterData['TPSysIdArr']) && !empty($filterData['TPSysIdArr'])) {
            $select->Where('tb2.TPSysId IN (?)', $filterData['TPSysIdArr']);
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('tb2.DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['bookingdate'])) {
            $date = date('Y-m-d', strtotime($filterData['bookingdate']));
            $select->Where('SUBSTRING(convert(varchar, tb2.StartDate, 120),0,11) = ?', $date);
        }
        if (!empty($filterData['ValidTillcurrentDate']) && !empty($filterData['ValidTillfromDate'])) {
            $ValidTillcurrentDate = date('Y-m-d', strtotime($filterData['ValidTillcurrentDate']));
            $ValidTillfromDate = date('Y-m-d', strtotime($filterData['ValidTillfromDate']));
            $select->where("CONVERT(date,(tb2.ValidTill))  BETWEEN '$ValidTillfromDate' AND '$ValidTillcurrentDate'");
        }
        if (!empty($filterData['currentDate']) && !empty($filterData['fromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['currentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['fromDate']));
            $select->where("CONVERT(date,(tb2.StartDate))  BETWEEN '$fromDate' AND '$currentDate'");
        }
        if (!empty($filterData['updatecurrentDate']) && !empty($filterData['updatefromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['updatecurrentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['updatefromDate']));
            $select->where("(CONVERT(date,tb2.ApproveDate))  BETWEEN '$fromDate' AND '$currentDate'");
        }
        $ReportingToSysId = $filterData['UserSysId'] . (isset($this->ReportingToSysId) && !empty($this->ReportingToSysId) ? ',' . $this->ReportingToSysId : '');
        if (isset($filterData['PlanType']) && !empty($filterData['PlanType'])) {
            $select->where('tb2.PlanType IN(?)', $filterData['PlanType']);
        } else {
            $select->where('tb2.PlanType IN(?)', array(7));
        }
        $select->where('tb2.IsMarkForDel = 0');
        $select->where('tb2.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123));
        $select->where("tb2.AgencySysId = $agencySysID OR tb2.AgencySysId IN (SELECT AgencySysId FROM TB_AGENCY AS AT1 WHERE AT1.MasterAgencySysId = $agencySysID AND AT1.AgencyType=2 AND AT1.IsActive = 1 )");
        if ((!empty($filterData['agencyUserFilter']))) {
            $select->Where("tb2.AgentSysId = (?)", $filterData['agencyUserFilter']);
        } else if (!empty($filterData['UserSysId'])) {
            $select->Where("tb2.AgentSysId IN (?)", explode(',', $ReportingToSysId));
        }
        if (isset($filterData['CustomerName']) && !empty($filterData['CustomerName'])) {
            $select->Where('tb3.FirstName LIKE ?', '%' . $filterData['CustomerName'] . '%');
        }
        if (isset($filterData['CustomerEmail']) && !empty($filterData['CustomerEmail'])) {
            $select->Where('tb3.EmailId LIKE ?', '%' . $filterData['CustomerEmail'] . '%');
        }
        if (isset($filterData['CustomerPhone']) && !empty($filterData['CustomerPhone'])) {
            $select->Where('tb3.Contacts = ? ', $filterData['CustomerPhone']);
        }
        if (isset($filterData['source']) && !empty($filterData['source'])) {
            $select->Where('tb2.IsB2BProposal = ? ', $filterData['source']);
        }
        if(isset($filterData['agencyServicesName']) && !empty($filterData['agencyServicesName'])){
            $agencyServicesName = trim($filterData['agencyServicesName']);
            if($agencyServicesName != ''){ 
                $select->Where('tb9.'.$agencyServicesName.' is not null ');
                if(isset($filterData['voucherStatusFilter']) && $filterData['voucherStatusFilter'] != ''){
                    $select->Where('tb9.'.$agencyServicesName.' = ? ', (int)$filterData['voucherStatusFilter']);
                }
            }
            
            
        }
        
//        echo $select;die;
        $select->order("tb2.UpdateDate DESC");
        return $select;
    }

    public function getAccountDetailByCust($agencySysID, $customerId) {
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"));
        $select->where('tb1.AgencySysId = ?', $agencySysID);
        $select->where('tb1.CustomerSysId =?', $customerId);
        $select->order("tb1.BalanceSysId DESC");
        return $select;
    }

    public function getBookedPackageDetailByCust($agencySysID, $customerId) {

//$agency_customer_trx = array('TrxSysId', 'TPSysId', 'CustomerSysId', 'PaidAmount', 'CurrencyType', 'TrxDate', 'TrxUpdatedOn', 'Amount', 'NetAmount', 'PaidAmount', 'CurrencyType', 'TrxFee', 'TrxOrdDocId AS PlanType');
        $agency_customer_travel_plan = array('UpdateDate', 'TPSysId', 'CustomerSysId', 'PlanType', 'PlanBookingId', 'Title', 'StartDate', 'RoomInfoJson', 'DestinationPlaces', 'MasterTPSysId', 'CurrencyType', 'StatusType', 'Price', 'NetPrice', 'CustomerSysId', 'SupplierSysId', 'Readymade', 'PackSpecType', 'VersionId', 'IsOnlySightseeingPkg');
        $agency_customer = array('AgencySysId', 'EmailId', 'Contacts', 'FirstName', 'LastName');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), $agency_customer_travel_plan);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb1.CustomerSysId", $agency_customer);
//$select->where('tb1.TrxStatus = ?', 1);
//$select->where('tb1.AgencySysId = ?', $agencySysID);
        $select->where('tb1.AgencySysId = ?', $agencySysID);
//$select->where('tb3.AgencySysId = ?', $agencySysID);
        $select->where('tb1.CustomerSysId =?', $customerId);
        $select->where('tb1.StatusType = 17 OR tb1.StatusType = 10 OR tb1.StatusType = 9 OR tb1.StatusType = 18 OR tb1.StatusType = 19 OR tb1.StatusType = 13 OR tb1.StatusType = 7 OR tb1.StatusType = 102 OR tb1.StatusType = 123');
// $select->where('tb1.NetAmount > ?', 0);
// $select->where('tb1.CustomerSysId =?', $customerId);
        $select->order("tb1.UpdateDate DESC");
//echo $select; exit;
        return $select;
    }

//Added BY Pardeep Panchal
    public function updateInvoiceNumber($data, $TrxSysId) {
        $where = array('TrxId = ? ' => $TrxSysId);
        $this->db->update('TB_Agency_Wallet_Trx', $data, $where);
    }

    public function insertInvoiceNumber($data) {
        $this->db->insert('TB_Agency_InvoiceNumbers', $data);
    }

    public function getTrasactionProposalByTPSysId($TpSysId, $TrxSysId = null) {
        $select = $this->db->select();
        $select->from("TB_Agency_Customer_Trx", array('TrxSysId', 'TrxDate', 'NetAmount', 'TrxStatus', 'CurrencyType', 'IsTCS', 'TcsFivePercentage', 'TcsTwentyPercentage', 'PancardNumber','ConversionRate'));
        $select->joinLeft("TB_Agency_Wallet_Trx", "TB_Agency_Wallet_Trx.TrxId = TB_Agency_Customer_Trx.TrxSysId", array('InvoiceId', 'AdminRemarks', 'GUID'));
        $select->joinLeft("TB_Master_Currency", "TB_Agency_Customer_Trx.CurrencyType = TB_Master_Currency.CurrencyType", array('Symbol'));
        $select->where("TPSysId =?", $TpSysId);
        if (!empty($TrxSysId)) {
            $select->where("TrxSysId =?", $TrxSysId);
        }
        $select->order("TrxSysId DESC");

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

    public function getTrxDetailByPlanId($TPSysId, $AgencySysId = NULL) {
        $agency_customer_trx = array('TrxSysId', 'TPSysId', 'CustomerSysId', 'TrxType', 'Amount', 'PaidAmount', 'NetAmount','Markup','AgencyMarkUp','GTXMarkup', 'CurrencyType', 'TrxDate', 'TrxUpdatedOn', 'TrxFee', 'Commission', 'XrefTrxNo', 'TrxPayMediaRefNo','CrPartyAccSysId','TrxPayMediaType', 'DrPartyName','IsTCS', 'TcsFivePercentage', 'TcsTwentyPercentage', 'PancardNumber','ConversionRate','IsPanVarified', 'TrxStatus', 'IsActive','PaymentModeType','XRefTrxNo');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trx"), $agency_customer_trx);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb2.TPSysId = tb1.TPSysId", array('StatusType','MasterTPSysId'));
        $select->joinLeft(array('tb3' => "TB_Agency_Wallet_Trx"), "tb3.TrxId = tb1.TrxSysId", array('TrxPayMediaRefNo as WalletTrxPayMediaRefNo','OffLinePaymentRefNo','OffLinePaymentBrank','OffLinePaymentBranch','TransFee','IsApproved')); 
        $AgencySysId ? $select->where('tb1.AgencySysId = ?', $AgencySysId) : '';
        $select->where('tb1.TPSysId = ?', $TPSysId);
        $select->where('tb1.TrxStatus = ?', 1);
        $select->where('tb1.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTrxDetailByCustomerIdInsteedOfInvoice($AgencySysId = NULL, $CustomerSysId = NULL) {
        $agency_customer_trx = array(new Zend_Db_Expr('SUM(Amount) as Amount'), new Zend_Db_Expr('SUM(NetAmount) as NetAmount'), new Zend_Db_Expr('SUM(PaidAmount) as PaidAmount'));
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trx"), $agency_customer_trx);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb2.TPSysId = tb1.TPSysId", array(''));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.CustomerSysId = ?', $CustomerSysId);
//$select->where('tb2.StatusType != 123');
//  $select->where('tb2.StatusType != 81');
        $select->where('tb1.TrxStatus = ?', 1);
        $select->where('tb1.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getTrxDetailByCustomerId($AgencySysId = NULL, $CustomerSysId = NULL) {
        $agency_customer_trx = array(new Zend_Db_Expr('SUM(Amount) as Amount'), new Zend_Db_Expr('SUM(NetAmount) as NetAmount'), new Zend_Db_Expr('SUM(PaidAmount) as PaidAmount'));
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trx"), $agency_customer_trx);
        $AgencySysId ? $select->where('tb1.AgencySysId = ?', $AgencySysId) : '';
        $CustomerSysId ? $select->where('tb1.CustomerSysId = ?', $CustomerSysId) : '';
        $select->where('tb1.TrxStatus = ?', 1);
        $select->where('tb1.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }

//function to get booked activity list created by Er Amit Kumar Dubey on 25 jan 2018 at 12:59 PM
    public function getBookedActivityDetail($agencySysID, $filterData = null) {

//$agency_customer_trx = array('TPSysId','CustomerSysId','PaidAmount','CurrencyType');
        $agency_customer_travel_plan = array('TPSysId', 'CustomerSysId', 'NetPrice', 'PlanBookingId', 'Title', 'StartDate', 'UpdateDate', 'RoomInfoJson', 'DestinationPlaces', 'MasterTPSysId', 'StatusType', 'Readymade', 'IsPriceShared', 'PkgSellValidTill');
        $agency_customer = array('AgencySysId', 'EmailId', 'Contacts', 'FirstName', 'LastName');
        $select = $this->_dbObj->select();

//$select->from(array('tb1' => "TB_Agency_Customer_Trx") ,$agency_customer_trx);
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));
//added filter condition
        if (!empty($filterData['category'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['category'] . '%');
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['proposalId']) && (int) $filterData['proposalId'] > 0) {
            $select->Where('tb2.TPSysId = ? ', (int) $filterData['proposalId']);
        }
        if (!empty($filterData['bookingdate'])) {
            $date = date('Y-m-d', strtotime($filterData['bookingdate']));
            $select->Where('SUBSTRING(convert(varchar, tb2.StartDate, 120),0,11) = ?', $date);
        }
//end of filter condition		
        $select->where('tb2.PlanType = ?', 9);
        $select->where('tb2.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123));
        $select->where('tb2.AgencySysId = ?', $agencySysID);
//$select->where('tb2.AgencySysId = ?', $agencySysID);
        $select->where('tb3.AgencySysId = ?', $agencySysID);
//$select->where('tb3.CustomerSysId IS NOT NULL');
//$select->where('tb2.CustomerSysId IS NOT NULL');
        $select->order("tb2.TPSysId DESC");
//$select->getPart(Zend_Db_Select::WHERE);
//echo $select;
//$result = $this->_dbObj->fetchAll($select);
//echo "<pre>";print_r($result);exit;
        return $select;
    }

//function to get booked visa list created by Er Amit Kumar Dubey on 25 jan 2018 at 05:59 PM
    public function getBookedVisaDetail($agencySysID, $filterData = null, $ReportingToSysIds = null) {
        $agency_customer_travel_plan = array('TPSysId', 'CustomerSysId', 'NetPrice', 'PlanBookingId', 'Title', 'StartDate', 'UpdateDate', 'RoomInfoJson', 'DestinationPlaces', 'MasterTPSysId', 'StatusType', 'Readymade', 'IsPriceShared', 'PkgSellValidTill');
        $agency_customer = array('AgencySysId', 'EmailId', 'Contacts', 'FirstName', 'LastName', 'IsB2BAgent');
        $select = $this->_dbObj->select();
//        echo"<pre>";print_r($filterData['StatusType']);
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));
        $select->joinLeft(array('tb5' => "TB_Agency_Customer_TravelPlan_Query"), "tb5.TPSysId=tb2.MasterTPSysId", array('PaymentStatus as QueryPaymentStatus', 'BookingStatus as QueryBookingStatus', 'UpdateDate as QueryUpdateDate'));
        $select->joinLeft(array('tb7' => "TB_Agency"), "tb7.AgencySysId=tb3.AgencySysId", array('Title as CompanyName', 'NickId'));
        $select->joinLeft(array('tb9' => "TB_Master_Currency"), "tb2.CurrencyType = tb9.CurrencyType", array('Symbol'));
        $select->joinLeft(array('tb10' => "TB_Agency_User"), "tb10.UserSysId=tb2.OpsAgentSysId", array('FirstName as OpsFirstName', 'LastName as OpsLastName'));
        if ((isset($filterData['paxName']) && !empty($filterData['paxName'])) || (isset($filterData['passportNumber']) && !empty($filterData['passportNumber']))) {
            $select->joinLeft(array('tb8' => "TB_Agency_Customer_TravelPlan_Pax"), "tb2.TPSysId = tb8.TPSysId ", array('TPPaxSysId'));
            $select->joinLeft(array('tb11' => "TB_Agency_Customer_Members"), "tb8.MemberSysId = tb11.MemberSysId ", array('MemberSysId'));
            if (!empty($filterData['paxName'])) {
                $paxName = preg_replace('/^(Mr|Ms|Mrs)\s+/i', '', trim($filterData['paxName']));
                $select->Where("((CONCAT(trim(tb11.FirstName), ' ', trim(tb11.LastName)) LIKE '%" . $paxName . "%') AND tb3.IsB2BAgent = 1) OR ((CONCAT(trim(tb3.FirstName), ' ', trim(tb3.LastName)) LIKE '%" . $paxName . "%') AND tb3.IsB2BAgent = 0) ");
            }
            if (!empty($filterData['passportNumber'])) {
                $select->Where("tb11.PassportNo LIKE '%" . $filterData['passportNumber'] . "%' OR tb3.PassportNo LIKE '%" . $filterData['passportNumber'] . "%' ");
            }
        }
        if (!empty($filterData['visaname'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['visaname'] . '%');
        }
        if (!empty($filterData['country_id'])) {
            $select->Where('tb2.Destinationplaces LIKE ?', '%' . $filterData['country'] . '%');
        }

        if (!empty($filterData['proposalId']) && (int) $filterData['proposalId'] > 0) {
            $select->Where('tb2.TPSysId = ?', (int) $filterData['proposalId']);
        }
        if ($ReportingToSysIds) {
            $select->where("tb2.AgentSysId  IN (" . $ReportingToSysIds . ") OR tb2.OpsAgentSysId IN (" . $ReportingToSysIds . ")");
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['bookingdate'])) {
            $bookingdate = explode('/',$filterData['bookingdate']);
            $date = $bookingdate[2].'-'.$bookingdate[1].'-'.$bookingdate[0];
            $select->Where('SUBSTRING(convert(varchar, tb2.CreateDate, 120),0,11) = ?', $date);
        }
        if (!empty($filterData['currentDate']) && !empty($filterData['fromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['currentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['fromDate']));
            $select->where("CONVERT(tb2.StartDate)  BETWEEN '$fromDate' AND '$currentDate'");
        }
        if (!empty($filterData['updatecurrentDate']) && !empty($filterData['updatefromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['updatecurrentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['updatefromDate']));
            $select->where("(CONVERT(date,tb2.UpdateDate))  BETWEEN '$fromDate' AND '$currentDate'");
        }
        if(isset($filterData['OpsAgentSysId']) && (int)$filterData['OpsAgentSysId'] > 0){
            $select->where('tb2.OpsAgentSysId = ?', (int)$filterData['OpsAgentSysId']);
        }
        if(isset($filterData['BookingStatus']) && (int)$filterData['BookingStatus'] > 0){
            $select->where('tb5.BookingStatus = ?', (int)$filterData['BookingStatus']);
        }
        $select->where('tb2.PlanType = ?', 6);
        $select->where('tb2.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123)); // removed ,10 // for booking cancelled
        $select->where('tb2.AgencySysId = ?', $agencySysID);
//$select->where('tb3.AgencySysId = ?', $agencySysID);
        if(isset($filterData['columnName']) && !empty($filterData['columnName'])){
            $columnName = $filterData['columnName'];
            $columnOrder = $filterData['columnOrder'];
            $select->order("$columnName $columnOrder");
        }else{
            $select->order("tb2.UpdateDate DESC");
        } 
//        echo $select;die;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getMiscellaneousListByCust($CustomerSysId) {
        $select = $this->db->select();
        $selectarray = array('MasterMiscSysId', 'ICSourceSysId', 'CONVERT(VARCHAR(24),InvoiceDate,103) as InvoiceDate', 'CONVERT(VARCHAR(24),PaymentDueDate,103) as PaymentDueDate', 'InvoiceNumber', 'ClientGST', 'ClientGSTNumber', 'Currency', 'TotalInvoiceValue', 'TotalCGST', 'TotalSGST', 'TotalIGST', 'OtherDiscount', 'TotalAdvance', 'TotalDueAmount', 'InvoiceValue', 'otherDiscountVal', 'CreateDate', 'InvoiceStatus');

        $customerarray = array('CustomerSysId', 'Contacts', 'EmailId', 'Address', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
        $Currencyarray = array('Symbol', 'Title', 'Icon');
        $select->from(array('tb1' => "TB_Agency_Miscellaneous"), $selectarray);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tb1.BilledTo = tb2.CustomerSysId", $customerarray);
        $select->joinLeft(array('tb3' => "TB_Master_Currency"), "tb1.Currency = tb3.CurrencyType", $Currencyarray);
        $select->where("tb1.BilledTo =?", $CustomerSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getMiscellaneousDetailByCustId($CustomerSysId) {
        $agency_customer_misc = array(new Zend_Db_Expr('SUM(InvoiceValue) as Amount'), new Zend_Db_Expr('SUM(TotalAdvance) as NetAmount'));
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Miscellaneous"), $agency_customer_misc);
        $select->where("tb1.BilledTo =?", $CustomerSysId);
        $select->where('tb1.IsMarkForDel = ?', 0);
        $select->where('tb1.TPSysId is NULL OR tb1.TPSysId = 0');
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function getBookedTransportDetail($agencySysID, $filterData = null, $ReportingToSysIds = null) {

//$agency_customer_trx = array('TPSysId','CustomerSysId','PaidAmount','CurrencyType');
        $agency_customer_travel_plan = array('TPSysId', 'CustomerSysId', 'NetPrice', 'PlanBookingId', 'Title', 'StartDate', 'UpdateDate', 'RoomInfoJson', 'DestinationPlaces', 'MasterTPSysId', 'StatusType', 'Readymade', 'IsPriceShared', 'PkgSellValidTill');
        $agency_customer = array('AgencySysId', 'EmailId', 'Contacts', 'FirstName', 'LastName', 'countrycode');
        $select = $this->_dbObj->select();

//$select->from(array('tb1' => "TB_Agency_Customer_Trx") ,$agency_customer_trx);
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));
        if (!empty($filterData['category'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['category'] . '%');
        }
        if ($ReportingToSysIds) {
            $select->where("tb2.AgentSysId  IN (" . $ReportingToSysIds . ")");
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['bookingdate'])) {
            $date = date('Y-m-d', strtotime($filterData['bookingdate']));
            $select->Where('SUBSTRING(convert(varchar, tb2.StartDate, 120),0,11) = ?', $date);
        }
        if (!empty($filterData['proposalId']) && (int) $filterData['proposalId'] > 0) {
            $select->Where('tb2.TPSysId = ?', (int) $filterData['proposalId']);
        }
        if (!empty($filterData['currentDate']) && !empty($filterData['fromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['currentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['fromDate']));
            $select->where("(tb2.StartDate)  BETWEEN '$fromDate' AND '$currentDate'");
        }
        $select->where('tb2.PlanType = ?', 11);
        $select->where('tb2.StatusType IN(?)', array(17, 9, 18, 19, 13)); // removed ,10 // for booking cancelled
        $select->where('tb2.AgencySysId = ?', $agencySysID);
        $select->where('tb3.AgencySysId = ?', $agencySysID);
        $select->order("tb2.TPSysId DESC");
//        echo $select;die;
//$result = $this->_dbObj->fetchAll($select);
//echo "<pre>";print_r($result);exit;
        return $select;
    }

    public function getMiscellaneousPackageDetail($agencySysID, $filterData = null) {

        $agency_customer = array('EmailId', 'Contacts', 'FirstName', 'LastName');
        $select = $this->_dbObj->select();

//$select->from(array('tb1' => "TB_Agency_Customer_Trx") ,$agency_customer_trx);
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", $agency_customer);
        $select->joinLeft(array('tb5' => "TB_Agency"), "tb5.AgencySysId=tb2.AgencySysId", array('TrxCurrency'));
        $select->joinLeft(array('tb6' => "TB_IC_CurrencyConversion"), "tb6.TCurrencyType=tb5.TrxCurrency AND tb6.FCurrencyType = tb2.CurrencyType", array('Rate as ToBaseCurrencyExchangeRate'));
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array('FirstName as AgentFirstName', 'Designation'));

        if (!empty($filterData['category'])) {
            $select->Where('tb2.Title LIKE ?', '%' . $filterData['category'] . '%');
        }
        if (!empty($filterData['hidden_selected_hotel_cityid'])) {
            $select->Where('DestinationPlacesSysId IN (?)', $filterData['hidden_selected_hotel_cityid']);
        }
        if (!empty($filterData['bookingdate'])) {
            $date = date('Y-m-d', strtotime($filterData['bookingdate']));
            $select->Where('SUBSTRING(convert(varchar, tb2.StartDate, 120),0,11) = ?', $date);
        }
        if (!empty($filterData['currentDate']) && !empty($filterData['fromDate'])) {
            $currentDate = date('Y-m-d', strtotime($filterData['currentDate']));
            $fromDate = date('Y-m-d', strtotime($filterData['fromDate']));
            $select->where("(tb2.StartDate)  BETWEEN '$fromDate' AND '$currentDate'");
// echo $select ; die;
        }

        $ReportingToSysId = $filterData['UserSysId'] . (isset($this->ReportingToSysId) && !empty($this->ReportingToSysId) ? ',' . $this->ReportingToSysId : '');

        $select->where('tb2.PlanType =?', 10);
        $select->where('tb2.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7));
        $select->where('tb2.AgencySysId = ?', $agencySysID);

        if (!empty($filterData['UserSysId'])) {
            $select->Where("tb2.AgentSysId IN (?)", explode(',', $ReportingToSysId));
        }

        $select->order("tb2.TPSysId DESC");
        return $select;
    }

    public function checkCustomerWallet($AgencySysId, $CustomerSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Wallet"), array('WalletSysId'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.CustomerSysId = ?', $CustomerSysId);
        $result = $this->db->fetchAll($select);
        $returnArray = False;
        if (empty($result)) {
            $select1 = $this->db->select();
            $select1->from(array('tb1' => "TB_Agency"), array('TrxCurrency'));
            $select1->where('tb1.AgencySysId = ?', $AgencySysId);
            $result1 = $this->db->fetchRow($select1);
            $insertArray = array(
                "CustomerSysId" => $CustomerSysId,
                "AgencySysId" => $AgencySysId,
                "Title" => "Customer_Wallet_$CustomerSysId",
                "CurrencyType" => isset($result1['TrxCurrency']) ? (int) $result1['TrxCurrency'] : 0,
                "BalanceAmount" => 0,
                "CreateDate" => date("Y-m-d H:i"),
                "UpdateDate" => date("Y-m-d H:i"),
                "IsMarkForDel" => false,
                "IsActive" => true
            );
            try {
                $this->db->insert('TB_Agency_Customer_Wallet', $insertArray);
                $returnArray = True;
            } catch (Zend_Exception $e) {
                $returnArray = False;
            }
        } else {
            $returnArray = True;
        }

        return $returnArray;
    }

    public function updateSecondEntryInBalanceSheet($TPSysId, $AgencySysId, $TrxSysId, $NetPrice) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('CurrencyType', 'SupplierSysId', 'MPSysId', 'WalletSysId', 'PlanType', 'CustomerSysId'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.TPSysId = ?', $TPSysId);
        $select->where('tb1.TrxType = ?', 0);
        $result11 = $this->db->fetchAll($select);
        if (!empty($result11)) {
            $result = $result11[0];
            $PlanType = isset($result['PlanType']) ? (int) $result['PlanType'] : 5;
            $CustomerSysId = isset($result['CustomerSysId']) ? (int) $result['CustomerSysId'] : 0;
            $remark = "Advance Against - Proposal No. -  " . $TPSysId;
            $otherDataArray['TrxSysId'] = $TrxSysId;
            $otherDataArray['TPSysId'] = $TPSysId;
            $otherDataArray['MPSysId'] = (int) $result['MPSysId'];
            $otherDataArray['SupplierSysId'] = (int) $result['SupplierSysId'];
            $otherDataArray['PlanType'] = $PlanType;
            $otherDataArray['Remarks'] = $remark;
            $otherDataArray['InvoiceId'] = '';
            $otherDataArray['RecieptNo'] = '';
            $otherDataArray['TrxType'] = 1;
            $otherDataArray['CurrencyType'] = (int) $result['CurrencyType'];
            $otherDataArray['Amount'] = 0;
            $otherDataArray['Markup'] = 0;
            $otherDataArray['Tax'] = 0;
            $otherDataArray['TCS'] = 0;
            $otherDataArray['Discount'] = 0;
            $otherDataArray['NetAmount'] = $NetPrice;
            $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
        }
    }

    public function updateFirstEntryInBalanceSheet($TPSysId, $AgencySysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('WalletSysId'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.TPSysId = ?', $TPSysId);
        $select->where('tb1.TrxType = ?', 0);
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            $select1 = $this->db->select();
            $select1->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), array('ShareCombinedFlightPricing', 'isShareWithFlight', 'Price', 'AgentServiceTaxAmount', 'XServiceTaxAmount', 'AgentsCustomServiceTaxAmount', 'CustomerSysId', 'IsB2BProposal', 'CurrencyType', 'NetPrice', 'CustomDiscount', 'ServiceTaxOnCustomDiscount', 'AgencyMarkUp', 'MarkUp', 'AgencyMarkUp', 'TotalCostGen', 'ServiceProiderFee', 'PlanType','SupplierSysId'));
            $select1->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_AddonServices"), "tb1.TPSysId = tb2.TPSysId AND tb2.PlanType = 21 AND tb2.IsActive= 1 and tb2.IsMarkForDelete = 0", array('Tax'));
            $select1->where('tb1.AgencySysId = ?', $AgencySysId);
            $select1->where('tb1.TPSysId = ?', $TPSysId);
            $result1 = $this->db->fetchRow($select1);
            if ($result1['PlanType'] == 5) {
                $result1['NetPrice'] = $result1['NetPrice'] + $result1['TotalCostGen'];
                $result1['Price'] = $result1['Price'] + $result1['TotalCostGen'] - $result1['ServiceProiderFee'];
                $result1['AgentServiceTaxAmount'] = $result1['AgentServiceTaxAmount'] + $result1['ServiceProiderFee'];
                $result1['TcsAmount'] = $result1['Tax'];
            }

            $CustomerSysId = isset($result1['CustomerSysId']) ? (int) $result1['CustomerSysId'] : 0;
            if ($CustomerSysId > 0) {
                $flightCost = $flightMarkup = $flightGST = 0;
                $encodedata = array();
                if ((int) $result1['isShareWithFlight'] == 1 && (int) $result1['ShareCombinedFlightPricing'] == 0) {
                    $select3 = $this->db->select();
                    $select3->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary"), array('TPIntSysId'));
                    $select3->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Air"), "tbl.TPIntSysId = tb2.TPIntSysId", array("encodedata"));
                    $select3->where('tbl.TPSysId = ?', $TPSysId);
                    $select3->where("tbl.IsActive = ?", 1);
                    $select3->where("tb2.IsActive = ?", 1);
                    $select3->where("tbl.IsMarkForDel = ?", 0);
                    $select3->where("tb2.IsMarkForDel = ?", 0);
                    $GetPackageFLightData = $this->db->fetchAll($select3);
                    if (!empty($GetPackageFLightData)) {
                        foreach ($GetPackageFLightData as $fKey => $fVal) {
                            $encodedata[] = json_decode($fVal['encodedata'], 1);
                        }
                    }
                    if (!empty($encodedata)) {
                        foreach ($encodedata as $flightVal) {
                            $flightCost += (float) $flightVal['totalcost_wom'];
                            $flightMarkup += (float) $flightVal['total_markup'];
                            $flightGST += (float) $flightVal['total_gst'];
                        }
                    }
                }
                $IsB2BProposal = isset($result1['IsB2BProposal']) ? (int) $result1['IsB2BProposal'] : 0;
                $MPSysId = 1;
                if ($IsB2BProposal == 1) {
                    $MPSysId = 2;
                }
                $PlanType = isset($result1['PlanType']) ? (int) $result1['PlanType'] : 5;
                $remark = '';
                switch ($PlanType) {
                    case 1:
                        $remark = "Flight Sale - Proposal No. -  " . $TPSysId;
                        break;
                    case 2:
                        $remark = "Hotel Sale - Proposal No. -  " . $TPSysId;
                        break;
                    case 5:
                        $remark = "Package Sale - Proposal No. -  " . $TPSysId;
                        break;
                    default:
                        $remark = "Miscellaneous Item Sale - Proposal No. -  " . $TPSysId;
                }
                $otherDataArray['TrxSysId'] = 0;
                $otherDataArray['TPSysId'] = $TPSysId;
                $otherDataArray['MPSysId'] = $MPSysId;
                $otherDataArray['SupplierSysId'] = (int) $result1['SupplierSysId'];
                $otherDataArray['PlanType'] = $PlanType;
                $otherDataArray['Remarks'] = $remark;
                $otherDataArray['InvoiceId'] = '';
                $otherDataArray['RecieptNo'] = '';
                $otherDataArray['TrxType'] = 0;
                $otherDataArray['CurrencyType'] = (int) $result1['CurrencyType'];
                $otherDataArray['Amount'] = (float) $result1['Price'] + (float) $flightCost;
                $otherDataArray['Markup'] = (float) $result1['MarkUp'] + (float) $result1['AgencyMarkUp'] + (float) $flightMarkup;
                $otherDataArray['Tax'] = (float) $result1['AgentServiceTaxAmount'] + (float) $result1['XServiceTaxAmount'] + (float) $result1['AgentsCustomServiceTaxAmount'] + (float) $flightGST;
                $otherDataArray['TCS'] = (float) $result1['TcsAmount'];
                $otherDataArray['Discount'] = (float) $result1['CustomDiscount'] + (float) $result1['ServiceTaxOnCustomDiscount'];
                $otherDataArray['NetAmount'] = (float) $result1['NetPrice'] + (float) $result1['TcsAmount'];
                $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
            }
        }
    }

    public function updateCreditNoteInBalanceSheet($InvoiceId, $AgencySysId, $CustomerSysId, $CurrencyType, $Amount, $Markup, $Tax, $Discount, $NetAmount) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('WalletSysId'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.InvoiceId = ?', $InvoiceId);
        $select->where('tb1.TrxType = ?', 1);
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            if ($CustomerSysId > 0) {
                $MPSysId = 1;
                $PlanType = 17;
                $remark = 'Credit Note - ' . $InvoiceId;
                $otherDataArray['TrxSysId'] = 0;
                $otherDataArray['TPSysId'] = 0;
                $otherDataArray['MPSysId'] = $MPSysId;
                $otherDataArray['SupplierSysId'] = 0;
                $otherDataArray['PlanType'] = $PlanType;
                $otherDataArray['Remarks'] = $remark;
                $otherDataArray['InvoiceId'] = $InvoiceId;
                $otherDataArray['RecieptNo'] = '';
                $otherDataArray['TrxType'] = 1;
                $otherDataArray['CurrencyType'] = (int) $CurrencyType;
                $otherDataArray['Amount'] = (float) $Amount;
                $otherDataArray['Markup'] = (float) $Markup;
                $otherDataArray['Tax'] = (float) $Tax;
                $otherDataArray['TCS'] = 0;
                $otherDataArray['Discount'] = (float) $Discount;
                $otherDataArray['NetAmount'] = (float) $NetAmount;
                $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
            }
        }
    }

    public function updateInvoiceInBalanceSheetWithCredit($InvoiceId, $AgencySysId, $CustomerSysId, $CurrencyType, $Amount, $Markup, $Tax, $Discount, $NetAmount, $InvoiceTPSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('WalletSysId'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.InvoiceId = ?', $InvoiceId);
        $select->where('tb1.TrxType = ?', 1);
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            if ($CustomerSysId > 0) {
                $MPSysId = 1;
                $PlanType = 18;
                $remark = 'Invoive No. - ' . $InvoiceId;
                if ($InvoiceTPSysId > 0) {

                    $select1 = $this->db->select();
                    $select1->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), array('CurrencyType', 'NetPrice', 'TotalCostGen', 'ServiceProiderFee'));
                    $select1->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_AddonServices"), "tb1.TPSysId = tb2.TPSysId AND tb2.PlanType = 21 AND tb2.IsActive= 1 and tb2.IsMarkForDelete = 0", array('Tax'));
                    $select1->where('tb1.AgencySysId = ?', $AgencySysId);
                    $select1->where('tb1.TPSysId = ?', $InvoiceTPSysId);
                    $result1 = $this->db->fetchAll($select1);
                    $remark = 'Invoice - Agaisnt PI (' . $InvoiceId . ') of Proposal No. - ' . $InvoiceTPSysId;
                }

                $otherDataArray['TrxSysId'] = 0;
                $otherDataArray['TPSysId'] = (int) $InvoiceTPSysId;
                $otherDataArray['MPSysId'] = $MPSysId;
                $otherDataArray['SupplierSysId'] = 0;
                $otherDataArray['PlanType'] = $PlanType;
                $otherDataArray['Remarks'] = $remark;
                $otherDataArray['InvoiceId'] = '';
                $otherDataArray['RecieptNo'] = '';
                $otherDataArray['TrxType'] = 1;
                $otherDataArray['CurrencyType'] = (int) $result1[0]['CurrencyType'];
                $otherDataArray['Amount'] = 0;
                $otherDataArray['Markup'] = 0;
                $otherDataArray['Tax'] = 0;
                $otherDataArray['TCS'] = 0;
                $otherDataArray['Discount'] = 0;
                $otherDataArray['NetAmount'] = $result1[0]['NetPrice'] + $result1[0]['TotalCostGen'] + $result1[0]['Tax'];
                $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                $otherDataArray['TrxType'] = 0;
                $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
            }
        }
    }

    public function updateInvoiceInBalanceSheet($InvoiceId, $AgencySysId, $CustomerSysId, $CurrencyType, $Amount, $Markup, $Tax, $Discount, $NetAmount, $InvoiceTPSysId) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('WalletSysId', 'NetAmount'));
        $select->where('tb1.AgencySysId = ?', $AgencySysId);
        $select->where('tb1.InvoiceId = ?', $InvoiceId);
        $select->where('tb1.TrxType = ?', 0);
        $select->order('BalanceSysId DESC');
        $result = $this->db->fetchAll($select);
        if (empty($result)) {
            $select1 = $this->db->select();
            $select1->from(array('tb1' => "TB_Agency_Customer_Balance_Sheet"), array('WalletSysId', 'NetAmount'));
            $select1->where('tb1.AgencySysId = ?', $AgencySysId);
            $select1->where('tb1.TPSysId = ?', $InvoiceTPSysId);
            $select1->where('tb1.TrxType = ?', 0);
            $select1->order('BalanceSysId DESC');
            $result1 = $this->db->fetchAll($select1);
            if (empty($result1)) {
                if ($CustomerSysId > 0) {
                    $MPSysId = 1;
                    $PlanType = 18;
                    $remark = 'Invoive No. - ' . $InvoiceId;
                    if ($InvoiceTPSysId > 0) {
                        $remark = 'Invoice - Agaisnt PI (' . $InvoiceId . ') of Proposal No. - ' . $InvoiceTPSysId;
                    }
                    $otherDataArray['TrxSysId'] = 0;
                    $otherDataArray['TPSysId'] = $InvoiceTPSysId;
                    $otherDataArray['MPSysId'] = $MPSysId;
                    $otherDataArray['SupplierSysId'] = 0;
                    $otherDataArray['PlanType'] = $PlanType;
                    $otherDataArray['Remarks'] = $remark;
                    $otherDataArray['InvoiceId'] = $InvoiceId;
                    $otherDataArray['RecieptNo'] = '';
                    $otherDataArray['TrxType'] = 0;
                    $otherDataArray['CurrencyType'] = (int) $CurrencyType;
                    $otherDataArray['Amount'] = (float) $Amount;
                    $otherDataArray['Markup'] = (float) $Markup;
                    $otherDataArray['Tax'] = (float) $Tax;
                    $otherDataArray['TCS'] = 0;
                    $otherDataArray['Discount'] = (float) $Discount;
                    $otherDataArray['NetAmount'] = (float) $NetAmount;
                    $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                }
            } else {
                if ($CustomerSysId > 0) {
                    $TrxType = 0;
                    $actualAmount = isset($result1[0]['NetAmount']) ? (float) $result1[0]['NetAmount'] : 0;
                    if ($NetAmount != $actualAmount) {
                        $reflectAmount = $actualAmount;
                        $TrxType = 1;
                        $MPSysId = 1;
                        $PlanType = 18;
                        $remark = 'Modify Invoive No. - ' . $InvoiceId;
                        $otherDataArray['TrxSysId'] = 0;
                        $otherDataArray['TPSysId'] = $InvoiceTPSysId;
                        $otherDataArray['MPSysId'] = $MPSysId;
                        $otherDataArray['SupplierSysId'] = 0;
                        $otherDataArray['PlanType'] = $PlanType;
                        $otherDataArray['Remarks'] = $remark;
                        $otherDataArray['InvoiceId'] = $InvoiceId;
                        $otherDataArray['RecieptNo'] = '';
                        $otherDataArray['TrxType'] = $TrxType;
                        $otherDataArray['CurrencyType'] = (int) $CurrencyType;
                        $otherDataArray['Amount'] = (float) $Amount;
                        $otherDataArray['Markup'] = (float) $Markup;
                        $otherDataArray['Tax'] = (float) $Tax;
                        $otherDataArray['TCS'] = 0;
                        $otherDataArray['Discount'] = (float) $Discount;
                        $otherDataArray['NetAmount'] = (float) $reflectAmount;
                        $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                        $otherDataArray['TrxType'] = 0;
                        $otherDataArray['NetAmount'] = (float) $NetAmount;
                        $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                    } else {
                        $BalanceSysId = isset($result1[0]['BalanceSysId']) ? (float) $result1[0]['BalanceSysId'] : 0;
                        $where = array('BalanceSysId =?' => $BalanceSysId);
                        $editData = array('InvoiceId' => $InvoiceId);
                        $this->paymentMdl->updateTable("TB_Agency_Customer_Balance_Sheet", $editData, $where);
                    }
                }
            }
        } else {
            if ($CustomerSysId > 0) {
                $TrxType = 0;
                $actualAmount = isset($result[0]['NetAmount']) ? (float) $result[0]['NetAmount'] : 0;
                if ($NetAmount != $actualAmount) {
//$reflectAmount = (float) $actualAmount - (float) $NetAmount;
                    $reflectAmount = $actualAmount;
                    $TrxType = 1;
                    $MPSysId = 1;
                    $PlanType = 18;
                    $remark = 'Modify Invoive No. - ' . $InvoiceId;
                    $otherDataArray['TrxSysId'] = 0;
                    $otherDataArray['TPSysId'] = 0;
                    $otherDataArray['MPSysId'] = $MPSysId;
                    $otherDataArray['SupplierSysId'] = 0;
                    $otherDataArray['PlanType'] = $PlanType;
                    $otherDataArray['Remarks'] = $remark;
                    $otherDataArray['InvoiceId'] = $InvoiceId;
                    $otherDataArray['RecieptNo'] = '';
                    $otherDataArray['TrxType'] = $TrxType;
                    $otherDataArray['CurrencyType'] = (int) $CurrencyType;
                    $otherDataArray['Amount'] = (float) $Amount;
                    $otherDataArray['Markup'] = (float) $Markup;
                    $otherDataArray['Tax'] = (float) $Tax;
                    $otherDataArray['TCS'] = 0;
                    $otherDataArray['Discount'] = (float) $Discount;
                    $otherDataArray['NetAmount'] = (float) $reflectAmount;
                    $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                    $otherDataArray['TrxType'] = 0;
                    $otherDataArray['NetAmount'] = (float) $NetAmount;
                    $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
                }
            }
        }
    }

    public function updatePaymentAgainstInvoiceInBalanceSheet($subscription_id, $AgencySysId, $TrxId, $FAmount) {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MiscellaneousPaymentLog"), array(''));
        $select->joinLeft(array('tb3' => "TB_Agency_Miscellaneous"), "tb3.MasterMiscSysId=tb1.MasterMiscSysId", array('InvoiceNumber', 'BilledTo', 'Currency'));
        $select->where('tb1.ICSourceSysId = ?', $AgencySysId);
        $select->where('tb1.MasterMiscPaySysId = ?', $subscription_id);
//echo $select; exit;
        $result = $this->db->fetchAll($select);
        if (!empty($result)) {
            $CustomerSysId = $result[0]['BilledTo'];
            $InvoiceId = $result[0]['InvoiceNumber'];
            $CurrencyType = $result[0]['Currency'];
//$CustomerSysId = $result[0]['BilledTo'];
            if ($CustomerSysId > 0) {
                $MPSysId = 1;
                $PlanType = 18;
                $remark = 'Advance Against - Invoive No. - ' . $InvoiceId;
                $otherDataArray['TrxSysId'] = 0;
                $otherDataArray['TPSysId'] = 0;
                $otherDataArray['MPSysId'] = $MPSysId;
                $otherDataArray['SupplierSysId'] = 0;
                $otherDataArray['PlanType'] = $PlanType;
                $otherDataArray['Remarks'] = $remark;
                $otherDataArray['InvoiceId'] = $InvoiceId;
                $otherDataArray['RecieptNo'] = '';
                $otherDataArray['TrxType'] = 1;
                $otherDataArray['CurrencyType'] = (int) $CurrencyType;
                $otherDataArray['Amount'] = 0;
                $otherDataArray['Markup'] = 0;
                $otherDataArray['Tax'] = 0;
                $otherDataArray['TCS'] = 0;
                $otherDataArray['Discount'] = 0;
                $otherDataArray['NetAmount'] = (float) $FAmount;
                $this->updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray);
            }
        }
    }

    public function updateBalanceSheet($AgencySysId, $CustomerSysId, $otherDataArray = array()) {
        $TrxSysId = isset($otherDataArray['TrxSysId']) ? (int) $otherDataArray['TrxSysId'] : 0;
        $TPSysId = isset($otherDataArray['TPSysId']) ? (int) $otherDataArray['TPSysId'] : 0;
        $MPSysId = isset($otherDataArray['MPSysId']) ? (int) $otherDataArray['MPSysId'] : 1;
        $SupplierSysId = isset($otherDataArray['SupplierSysId']) ? (int) $otherDataArray['SupplierSysId'] : 0;
        $PlanType = isset($otherDataArray['PlanType']) ? (int) $otherDataArray['PlanType'] : 5;
        $Remarks = isset($otherDataArray['Remarks']) ? trim($otherDataArray['Remarks']) : '';
        $InvoiceId = isset($otherDataArray['InvoiceId']) ? trim($otherDataArray['InvoiceId']) : '';
        $RecieptNo = isset($otherDataArray['RecieptNo']) ? trim($otherDataArray['RecieptNo']) : '';
        $TrxType = isset($otherDataArray['TrxType']) ? (int) $otherDataArray['TrxType'] : 0;
        $CurrencyType = isset($otherDataArray['CurrencyType']) ? (int) $otherDataArray['CurrencyType'] : 1;
        $Amount = isset($otherDataArray['Amount']) ? (float) $otherDataArray['Amount'] : 0;
        $Markup = isset($otherDataArray['Markup']) ? (float) $otherDataArray['Markup'] : 0;
        $Tax = isset($otherDataArray['Tax']) ? (float) $otherDataArray['Tax'] : 0;
        $TCS = isset($otherDataArray['TCS']) ? (float) $otherDataArray['TCS'] : 0;
        $Discount = isset($otherDataArray['Discount']) ? (float) $otherDataArray['Discount'] : 0;
        $NetAmount = isset($otherDataArray['NetAmount']) ? (float) $otherDataArray['NetAmount'] : 0;
        $where = array('AgencySysId =?' => $AgencySysId, 'CustomerSysId = ?' => $CustomerSysId);
        $checkCustomerWallet = $this->checkCustomerWallet($AgencySysId, $CustomerSysId);
        $returnVal = False;
        if ($checkCustomerWallet == True) {
            if ($TrxType == 0) {
                $editData = array('BalanceAmount' => new Zend_Db_Expr("BalanceAmount - $NetAmount"));
            } else if ($TrxType == 1) {
                $editData = array('BalanceAmount' => new Zend_Db_Expr("BalanceAmount + $NetAmount"));
            }
            try {
                $this->paymentMdl->updateTable("TB_Agency_Customer_Wallet", $editData, $where);
                $getwalletBalence = $this->paymentMdl->getDetailsByUniqueId("TB_Agency_Customer_Wallet", "CustomerSysId", $CustomerSysId);
                $insertArray = array(
                    "AgencySysId" => $AgencySysId,
                    "CustomerSysId" => $CustomerSysId,
                    "WalletSysId" => isset($getwalletBalence['WalletSysId']) ? (int) $getwalletBalence['WalletSysId'] : 0,
                    "TrxSysId" => $TrxSysId,
                    "TPSysId" => $TPSysId,
                    "MPSysId" => $MPSysId,
                    "SupplierSysId" => $SupplierSysId,
                    "PlanType" => $PlanType,
                    "Remarks" => $Remarks,
                    "InvoiceId" => $InvoiceId,
                    "RecieptNo" => $RecieptNo,
                    "TrxType" => $TrxType,
                    "TrxDate" => date("Y-m-d H:i:s"),
                    "CurrencyType" => $CurrencyType,
                    "Amount" => $Amount,
                    "Markup" => $Markup,
                    "Tax" => $Tax,
                    "TCS" => $TCS,
                    "Discount" => $Discount,
                    "NetAmount" => $NetAmount,
                    "BalanceAmount" => isset($getwalletBalence['BalanceAmount']) ? (float) $getwalletBalence['BalanceAmount'] : 0,
                    "IsActive" => 1,
                );
                $this->db->insert('TB_Agency_Customer_Balance_Sheet', $insertArray);
                $returnVal = True;
            } catch (Zend_Exception $e) {
                $e->getMessage();
                $returnVal = False;
            }
        }
        return $returnVal;
    }

    public function getTPSysIdFromInvoive($MasterMiscSysId, $AgencySysId) {
        $select1 = $this->db->select();
        $select1->from(array('tb1' => "TB_Agency_Miscellaneous"), array('TPSysId'));
        $select1->where('tb1.ICSourceSysId = ?', $AgencySysId);
        $select1->where('tb1.MasterMiscSysId = ?', $MasterMiscSysId);
        $result1 = $this->db->fetchAll($select1);
        return $result1;
    }
    public function updateTraxDataByTrxSysIds($TrxSysIds, $TPSysId) {  
        if($TPSysId > 0 && !empty($TrxSysIds)){ 
            $sqlPackage = "UPDATE TB_Agency_Customer_Trx SET TPSysId = $TPSysId WHERE TrxSysId IN ($TrxSysIds)"; 
            $this->db->query($sqlPackage);
            return 1;exit;
        }else{
            return 0;exit;
        }
        
    }
    public function getCustomerTrasactionProposalByTPSysId($TPSysId)
    {
        $select = $this->_dbObj->select();
        $select->from(array('tb2' => "TB_Agency_Customer_B2C_Wallet_History"));
        $select->joinLeft(array('tb3' => "TB_Master_Currency"), "tb3.CurrencyType=tb2.CurrencyType", array('Symbol')); 
        $select->where("tb2.TPSysId = ?", $TPSysId); 
        $select->where("tb2.IsMarkForDel = ?", 0);
//        $select->where("tb2.IsActive = ?", 1);
        $select->order("tb2.UpdateDate DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }
    public function getCustomerTrxDetailByPlanId($TPSysId, $AgencySysId = NULL) {
        $agency_customer_trx = array('*');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trx"), $agency_customer_trx);
        $AgencySysId ? $select->where('tb1.AgencySysId = ?', $AgencySysId) : '';
        $select->where('tb1.TPSysId = ?', $TPSysId);
        $select->where('tb1.TrxStatus = ?', 1);
        $select->where('tb1.IsActive = ?', 1);
        $result = $this->db->fetchAll($select);
        return $result;
    }
}