<?php

require_once('BaseModel.php');

class Travel_Model_TblMiscellaneous extends BaseModel
{

    protected $db = NULL;
    public $intId = NULL;
    public $strCondition = NULL;
    public $searchArr = NULL;
    public $searchActivitiesArr = NULL;
    public $baseUrl;

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

    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 addItems($ICSourceSysId)
    {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        if ($request->isPost()) {
            $post = $request->getPost();
            $CreateDate = date('Y-m-d H:i:s');
            $mis_id = isset($post['mis_id']) && !empty($post['mis_id']) ? $post['mis_id'] : '';
            //echo '<pre>';print_r($post);die;
            try {
                $insert = array(
                    'ICSourceSysId' => $ICSourceSysId,
                    'IsAgentSpec' => !empty($ICSourceSysId && $ICSourceSysId > 0) ? $ICSourceSysId : '0',
                    'MiscItemName' => isset($post['itemname']) && !empty($post['itemname']) ? $post['itemname'] : 'NA',
                    'MiscCategory' => isset($post['category']) && !empty($post['category']) ? $post['category'] : '0',
                    'MiscReverceCharge' => isset($post['reverce_charge']) && !empty($post['reverce_charge']) ? $post['reverce_charge'] : '0',
                    'MiscSupplier' => isset($post['hidden_selected_supplier_sys_id']) && !empty($post['hidden_selected_supplier_sys_id']) ? $post['hidden_selected_supplier_sys_id'] : '0',
                    'MiscSupplierName' => isset($post['supplier_name']) && !empty($post['supplier_name']) ? $post['supplier_name'] : '0',
                    'MiscSacHacCode' => isset($post['sac_hac_code']) && !empty($post['sac_hac_code']) ? $post['sac_hac_code'] : 'NA',
                    'MiscCost' => isset($post['cost']) && !empty($post['cost']) ? $post['cost'] : '0',
                    'GSTPercentage' => isset($post['GSTPercentage']) && !empty($post['GSTPercentage']) ? $post['GSTPercentage'] : '0',
                    'MiscGST' => isset($post['gst']) && !empty($post['gst']) ? $post['gst'] : '0',
                    'MiscTotalCostGst' => isset($post['total_costgst']) && !empty($post['total_costgst']) ? $post['total_costgst'] : '0',
                    'MiscReverceGst' => isset($post['reverse_gst']) && !empty($post['reverse_gst']) ? $post['reverse_gst'] : '0',
                    'MiscTotalCost' => isset($post['total_cost']) && !empty($post['total_cost']) ? $post['total_cost'] : '0',
                    'CreateDate' => $CreateDate,
                    'UpdateDate' => $CreateDate,
                    'ApproveDate' => $CreateDate,
                    'IsActive' => 1,
                    'IsApproved' => !empty($ICSourceSysId && $ICSourceSysId > 0) ? 0 : 1,
                    'IsMarkForDel' => 0
                );
                if (!empty($mis_id) && $mis_id > 0) {
                    $where = array('InvnMiscSysId = ? ' => $mis_id);
                    $this->db->update('TB_MP_Inventory_Miscellaneous', $insert, $where);
                    $lastInsertId = $mis_id;
                } else {
                    $this->db->insert('TB_MP_Inventory_Miscellaneous', $insert);
                    $lastInsertId = $this->db->lastInsertId('TB_MP_Inventory_Miscellaneous');
                }
                $markup_id = isset($post['markup_id']) && !empty($post['markup_id']) ? $post['markup_id'] : '';
                $MarkUpType = isset($post['markup_type']) && !empty($post['markup_type']) ? $post['markup_type'] : '';
                $markup_value = isset($post['markup_value']) && !empty($post['markup_value']) ? $post['markup_value'] : '';
                $MrkSysId = isset($post['MrkSysId']) && !empty($post['MrkSysId']) ? $post['MrkSysId'] : '';
                if ($markup_id) {
                    foreach ($markup_id as $k => $val) {
                        $insert_M = array(
                            'ICSourceSysId' => $ICSourceSysId,
                            'IsAgentSpec' => !empty($ICSourceSysId && $ICSourceSysId > 0) ? $ICSourceSysId : '0',
                            'InvnMiscSysId' => $lastInsertId,
                            'MPType' => $markup_id[$k],
                            'MarkUpType' => $MarkUpType[$k],
                            'MarkUp' => $markup_value[$k],
                            'CreateDate' => $CreateDate,
                            'UpdateDate' => $CreateDate,
                            'ApproveDate' => $CreateDate,
                            'IsActive' => 1,
                            'IsApproved' => !empty($ICSourceSysId && $ICSourceSysId > 0) ? 0 : 1,
                            'IsMarkForDel' => 0
                        );
                        if (!empty($MrkSysId) && array_key_exists($k, $MrkSysId)) {
                            $where = array('MrkSysId = ? ' => $MrkSysId[$k]);
                            $this->db->update('TB_MP_StdMarkup_Miscellaneous', $insert_M, $where);
                        } else {
                            $this->db->insert('TB_MP_StdMarkup_Miscellaneous', $insert_M);
                        }


                        //echo '<pre>';
                        //print_r($insert_M);                              
                    }
                }

                return true;
            } catch (Exception $e) {
                die('There has been an error. ' . $e->getMessage());
            }
            return 0;
            //echo '<pre>';print_r($post);
        }
    }

    public function InvMiscellaneousDetails($InvnVisaAllSysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('InvnMiscSysId', 'ICSourceSysId', 'MiscItemName', 'MiscCategory', 'MiscReverceCharge', 'GSTPercentage', 'MiscSupplier', 'MiscSacHacCode', 'MiscCost', 'MiscGST', 'MiscTotalCostGst', 'MiscReverceGst', 'MiscTotalCost', 'IsActive', 'IsApproved');
        $supplierarray = array('SupplierName');
        $select->from(array('tb1' => "TB_MP_Inventory_Miscellaneous"), $selectarray);
        $select->joinLeft(array('tb3' => "TB_IC_Supplier"), "tb1.MiscSupplier = tb3.SupplierSysId", $supplierarray);
        $select->where("tb1.InvnMiscSysId =?", $InvnVisaAllSysId);
        $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function InvMiscellaneousList($ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('InvnMiscSysId', 'ICSourceSysId', 'MiscItemName', 'MiscCategory', 'MiscReverceCharge', 'GSTPercentage', 'MiscSupplier', 'MiscSacHacCode', 'MiscCost', 'MiscGST', 'MiscTotalCostGst', 'MiscReverceGst', 'MiscTotalCost', 'IsActive', 'IsApproved');

        $supplierarray = array('SupplierName');
        $select->from(array('tb1' => "TB_MP_Inventory_Miscellaneous"), $selectarray);
        $select->joinLeft(array('tb3' => "TB_IC_Supplier"), "tb1.MiscSupplier = tb3.SupplierSysId", $supplierarray);
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('InvnMiscSysId DESC'));
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function AgencyMiscellaneousList($ICSourceSysId, $fromDate = null, $currentDate = null, $post = array(), $pageType = null)
    {
        $totalSubQuery = "SELECT SUM(Total) FROM TB_Agency_Miscellaneous_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $markupSubQuery = "SELECT SUM(TotalMarkup) FROM TB_Agency_Miscellaneous_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $discountSubQuery = "SELECT SUM(TotalDiscount) FROM TB_Agency_Miscellaneous_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $select = $this->db->select();
        $selectarray = array('TPSysId', 'MasterTPSysId', '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', 'TcsType', 'TcsAmount', 'client_state', 'allowFlexiPayment', "($markupSubQuery) AS TotalMarkUP", "($discountSubQuery) AS TotalDiscount", "($totalSubQuery) AS TotalVal");
        $customerarray = array('CustomerSysId', 'IsB2BAgent', 'Contacts', 'PinCode', 'CountrySysId', 'CountryCode', 'CitySysId', 'PanCard', 'Remarks', 'EmailId', 'Address', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
        $Currencyarray = array('Symbol', 'Title', 'Icon');
        $b2bagencyColumn = array('UserSysId', 'GSTIN', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId', 'FirstName as AgFirstName', 'LastName as AgLastName');
        $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->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb1.AgentSysId AND tb1.ICSourceSysId = tb6.AgencySysId", $b2bagencyColumn);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_City"), "tb7.CityId = tb6.CitySysId", array('Title as AgCityName'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb8.CityId = tb2.CitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb9' => "TB_Agency"), "tb9.AgencySysId = tb2.AgencySysId", array('Title as CompanyName', 'MasterAgencySysId'));
        $select->joinLeft(array('tb10' => "TB_Master_Geo_Country"), "tb10.ContId = tb2.CountrySysId", array('Title as CountryName'));
        $select->joinLeft(array('tb11' => "TB_Master_Geo_State"), "tb11.StateId = tb1.client_state", array('Title as stateName'));
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        if (!empty($fromDate) && !empty($currentDate)) {
            $start_date_formatted = date('Y-m-d', strtotime($fromDate)) . ' 00:00:00';
            $end_date_formatted = date('Y-m-d', strtotime($currentDate)) . ' 00:00:00';
            $select->where("InvoiceDate >= ?", $start_date_formatted);
            $select->where("InvoiceDate <= ?", $end_date_formatted);
        }
        if (isset($post['customername']) && !empty(trim($post['customername']))) {
            $select->where("tb2.FirstName =?", trim($post['customername']));
        }
        if (isset($post['customermobile']) && !empty(trim($post['customermobile']))) {
            $select->where("tb2.Contacts =?", trim($post['customermobile']));
        }
        if (isset($post['customeremail']) && !empty(trim($post['customeremail']))) {
            $select->where("tb2.EmailId =?", trim($post['customeremail']));
        }
        if (isset($post['InvoiceStatus']) && !empty(trim($post['InvoiceStatus']))) {
            $select->where("tb1.InvoiceStatus =?", trim($post['InvoiceStatus']));
        }
        if (isset($post['InvoiceNumber']) && !empty(trim($post['InvoiceNumber']))) {
            $select->where("tb1.InvoiceNumber =?", trim($post['InvoiceNumber']));
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        if ($pageType == 'Invoice') {
            return $this->db->fetchAll($select);
        } else {
            //echo $select; exit;
            return $select;
        }
    }

    public function AgencyMiscellaneousRowDetails($MasterMiscSysId)
    {
        $select = $this->db->select();
        $selectarray = array('Description', 'ItemsSACCode');
        $select->from(array('tb1' => "TB_Agency_Miscellaneous_Details"), $selectarray);
        $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        return $this->db->fetchAll($select);
    }

    public function AgencyMiscellaneousRow($MasterMiscSysId = null, $ICSourceSysId = null, $TPSysId = null, $MasterTPSysId = null)
    {
        $select = $this->db->select();
        $selectarray = array('allowFlexiPayment', 'SalesByUserSysId', 'isDisplayMarkup', 'MasterMiscSysId', 'BilledTo', 'ICSourceSysId', 'AgentSysId', 'CONVERT(VARCHAR(24),InvoiceDate,103) as InvoiceDate', 'CONVERT(VARCHAR(24),PaymentDueDate,103) as PaymentDueDate', 'CONVERT(VARCHAR(20),PaymentDueDate,120) as PaymentDueDateDay', 'InvoiceNumber', 'ClientGST', 'ClientGSTNumber', 'Currency', 'TotalInvoiceValue', 'TotalCGST', 'TotalSGST', 'TotalIGST', 'OtherDiscount', 'TotalAdvance', 'TotalDueAmount', 'InvoiceValue', 'otherDiscountVal', 'CreateDate', 'notes', 'termsandcondition', 'SumdiscountItem', 'InvoiceStatus', 'client_state', 'ProposalID', 'TcsType', 'TcsAmount', 'TPSysId');
        $customerarray = array('CustomerSysId', 'countrycode', 'Contacts', 'EmailId', 'CitySysId', 'PinCode', 'Address', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
        $Currencyarray = array('Symbol', 'Title', 'Icon');
        $Statearray = array('Title as stateName');
        $b2bagencyColumn = array('AgencyName as CompanyName', 'UserSysId', 'Signature', 'IsB2BAgent', 'GSTIN', 'PanNumber', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId');
        $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->joinLeft(array('tb4' => "TB_Master_Geo_State"), "tb1.client_state = tb4.StateId", $Statearray);
        $select->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb2.CreatedByUserSysId", $b2bagencyColumn);

        $select->joinLeft(array('tb7' => "TB_Master_Geo_City"), "tb7.CityId = tb6.CitySysId", array('Title as AgCityName'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb8.CityId = tb2.CitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb9' => "TB_Master_Geo_Country"), "tb9.ContId = tb8.ContSysId", array('Title as CountryName'));
        $select->joinLeft(array('tb10' => "TB_Agency_Customer_TravelPlan"), "tb1.TPSysId = tb10.TPSysId", array('PlanType'));
        
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        if (!empty($TPSysId) && $TPSysId > 0) {
            $select->where("tb1.TPSysId =?", $TPSysId);
        }
        if (!empty($MasterTPSysId) && $MasterTPSysId > 0) {
            $select->where("tb1.MasterTPSysId =?", $MasterTPSysId);
        }
        if (!empty($MasterMiscSysId) && $MasterMiscSysId > 0) {
            $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        }
        //        $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function AgencyMiscellaneousPayentRow($MasterMiscPaySysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MiscellaneousPaymentLog"));
        $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        $select->where("tb1.MasterMiscPaySysId =?", $MasterMiscPaySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function AgencyMiscellaneousPayentRowPub($MasterMiscPaySysId)
    {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_MiscellaneousPaymentLog"));
        $select->where("tb1.MasterMiscPaySysId =?", $MasterMiscPaySysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function AgencyMisDetailList($MasterMiscSysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('MasterMiscDetSysId', 'MasterMiscSysId', 'ICSourceSysId', 'MisxItems_name', 'Description', 'ItemQty', 'ItemRates', 'Total', 'DiscountType', 'Discount', 'TotalDiscount', 'TotalAmount', 'MarkupType', 'Markup', 'TotalMarkup', 'SubTotal', 'ServiceFeeTotal', 'GSTPercentage', 'TotalGST', 'SGST', 'CGST', 'IGST', 'ItemsSACCode', 'TcsType', 'TcsAmount');

        $select->from(array('tb1' => "TB_Agency_Miscellaneous_Details"), $selectarray);
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function AgencyMisGetLastId($ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('InvoiceNumber');
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->from(array('tb1' => "TB_Agency_Miscellaneous"), $selectarray);
        $select->order(array('MasterMiscSysId DESC'));
        $result = $this->db->fetchRow($select);
        return $result['InvoiceNumber'];
    }

    public function getItemsSearch($MiscItemName, $ICSourceSysId, $market_place)
    {
        $select = $this->db->select();
        $selectarray = array('InvnMiscSysId', 'ICSourceSysId', 'MiscItemName', 'MiscCategory', 'MiscReverceCharge', 'GSTPercentage', 'MiscCost', 'MiscGST', 'MiscTotalCostGst', 'MiscReverceGst', 'MiscTotalCost', 'IsActive', 'IsApproved');

        $select->from(array('tb1' => "TB_MP_Inventory_Miscellaneous"), $selectarray);
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->where('MiscItemName LIKE ?', '%' . $MiscItemName . '%');
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('MiscItemName ASC'));
        $result = $this->db->fetchAll($select);
        if (count($result) > 0) {
            foreach ($result as $row) {
                $dataName = $row['MiscItemName'];
                $MarkupMiscList = $this->getItemMarkup($row['InvnMiscSysId'], $market_place, $ICSourceSysId);
                $response[] = array('InvnMiscSysId' => $row['InvnMiscSysId'], 'MarkUpType' => $MarkupMiscList['MarkUpType'], 'MarkUp' => $MarkupMiscList['MarkUp'] + '0', 'MiscCost' => $row['MiscCost'], 'GSTPercentage' => $row['GSTPercentage'], 'value' => $dataName, 'label' => $dataName);
            }
        } else {
            $response[] = array('InvnMiscSysId' => 0, 'MarkUpType' => 0, 'MarkUp' => 0, 'MiscCost' => 0, 'GSTPercentage' => 0, 'value' => 'Add New Item', 'label' => 'Add New Item');
        }
        return $response;
    }

    public function MarkupMiscList($InvnVisaSysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('MrkSysId', 'InvnMiscSysId', 'MPType', 'MarkUpType', 'MarkUp', 'ICSourceSysId');
        $select->from(array('tb1' => "TB_MP_StdMarkup_Miscellaneous"), $selectarray);
        $select->where("tb1.InvnMiscSysId =?", $InvnVisaSysId);
        $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getItemMarkup($item_id, $MPType, $ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('MrkSysId', 'InvnMiscSysId', 'MPType', 'MarkUpType', 'MarkUp', 'ICSourceSysId');
        $select->from(array('tb1' => "TB_MP_StdMarkup_Miscellaneous"), $selectarray);
        $select->where("tb1.InvnMiscSysId =?", $item_id);
        $select->where("tb1.MPType =?", $MPType);
        $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function CancelInvoice($DeleteId)
    {
        try {
            $this->db->beginTransaction();
            $insert = array(
                'InvoiceStatus' => 3,
            );
            $where = array('MasterMiscSysId =?' => $DeleteId);
            $this->db->update('TB_Agency_Miscellaneous', $insert, $where);
            $this->db->update('TB_Agency_Miscellaneous_Details', $insert, $where);
            $this->db->commit();
            return true;
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }

    public function AgencyPerformaInvoiceList($ICSourceSysId, $fromDate = null, $currentDate = null, $post = array(), $pageType = null)
    {
        $totalSubQuery = "SELECT SUM(Total) FROM TB_Agency_Perfoma_Invoice_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $markupSubQuery = "SELECT SUM(TotalMarkup) FROM TB_Agency_Perfoma_Invoice_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $discountSubQuery = "SELECT SUM(TotalDiscount) FROM TB_Agency_Perfoma_Invoice_Details AS TAMD WHERE TAMD.MasterMiscSysId = tb1.MasterMiscSysId AND TAMD.IsActive=1 AND TAMD.IsMarkForDel=0";
        $select = $this->db->select();
        $selectarray = array('TPSysId', 'MasterTPSysId', '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', 'client_state', "($markupSubQuery) AS TotalMarkUP", "($discountSubQuery) AS TotalDiscount", "($totalSubQuery) AS TotalVal");
        $customerarray = array('CustomerSysId', 'Contacts', 'CountryCode', 'CitySysId', 'EmailId', 'Address', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
        $Currencyarray = array('Symbol', 'Title', 'Icon');
        $b2bagencyColumn = array('AgencyName as CompanyName', 'UserSysId', 'IsB2BAgent', 'GSTIN', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId', 'FirstName as AgFirstName', 'LastName as AgLastName');
        $select->from(array('tb1' => "TB_Agency_Performa_Invoice"), $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->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb1.AgentSysId", $b2bagencyColumn);
        $select->joinLeft(array('tb7' => "TB_Master_Geo_City"), "tb7.CityId = tb6.CitySysId", array('Title as AgCityName'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb8.CityId = tb2.CitySysId", array('Title as CityName'));
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        if (!empty($fromDate) && !empty($currentDate)) {
            $start_date_formatted = date('Y-m-d', strtotime($fromDate)) . ' 00:00:00';
            $end_date_formatted = date('Y-m-d', strtotime($currentDate)) . ' 00:00:00';
            $select->where("InvoiceDate >= ?", $start_date_formatted);
            $select->where("InvoiceDate <= ?", $end_date_formatted);
        }
        if (isset($post['customername']) && !empty(trim($post['customername']))) {
            $select->where("tb2.FirstName =?", trim($post['customername']));
        }
        if (isset($post['customermobile']) && !empty(trim($post['customermobile']))) {
            $select->where("tb2.Contacts =?", trim($post['customermobile']));
        }
        if (isset($post['customeremail']) && !empty(trim($post['customeremail']))) {
            $select->where("tb2.EmailId =?", trim($post['customeremail']));
        }
        if (isset($post['InvoiceStatus']) && !empty(trim($post['InvoiceStatus']))) {
            $select->where("tb1.InvoiceStatus =?", trim($post['InvoiceStatus']));
        }
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        if ($pageType == 'Invoice') {
            return $this->db->fetchAll($select);
        } else {
            //echo $select; exit;
            return $select;
        }
    }

    public function AgencyPerformaInvoiceRow($MasterMiscSysId = null, $ICSourceSysId = null, $TPSysId = null, $MasterTPSysId = null)
    {
        $select = $this->db->select();
        $selectarray = array('SalesByUserSysId', 'isDisplayMarkup', 'MasterMiscSysId', 'BilledTo', 'ICSourceSysId', 'AgentSysId', 'CONVERT(VARCHAR(24),InvoiceDate,103) as InvoiceDate', 'CONVERT(VARCHAR(24),PaymentDueDate,103) as PaymentDueDate', 'CONVERT(VARCHAR(20),PaymentDueDate,120) as PaymentDueDateDay', 'InvoiceNumber', 'ClientGST', 'ClientGSTNumber', 'Currency', 'TotalInvoiceValue', 'TotalCGST', 'TotalSGST', 'TotalIGST', 'OtherDiscount', 'TotalAdvance', 'TotalDueAmount', 'InvoiceValue', 'otherDiscountVal', 'CreateDate', 'notes', 'termsandcondition', 'SumdiscountItem', 'InvoiceStatus', 'client_state', 'ProposalID', 'TcsType', 'TcsAmount', 'TPSysId');
        $customerarray = array('CustomerSysId', 'countrycode', 'Contacts', 'EmailId', 'CitySysId', 'PinCode', 'Address', 'FirstName', "(RTRIM(LTRIM(tb2.FirstName)) + ' ' + RTRIM(LTRIM(tb2.LastName))) as FullName");
        $Currencyarray = array('Symbol', 'Title', 'Icon');
        $Statearray = array('Title as stateName');
        $b2bagencyColumn = array('AgencyName as CompanyName', 'UserSysId', 'IsB2BAgent', 'GSTIN', 'Address as AgAddress', 'PinCode as AgPinCode', 'StateOrZoneSysId as AgStateOrZoneSysId', 'ContactNo1 as AgContact', 'EmailId as AgEmailId', 'CitySysId as AgCitySysId');
        $select->from(array('tb1' => "TB_Agency_Performa_Invoice"), $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->joinLeft(array('tb4' => "TB_Master_Geo_State"), "tb1.client_state = tb4.StateId", $Statearray);
        $select->joinLeft(array('tb6' => "TB_Agency_User"), "tb6.UserSysId = tb2.CreatedByUserSysId", $b2bagencyColumn);

        $select->joinLeft(array('tb7' => "TB_Master_Geo_City"), "tb7.CityId = tb6.CitySysId", array('Title as AgCityName'));
        $select->joinLeft(array('tb8' => "TB_Master_Geo_City"), "tb8.CityId = tb2.CitySysId", array('Title as CityName'));
        $select->joinLeft(array('tb9' => "TB_Master_Geo_Country"), "tb9.ContId = tb8.ContSysId", array('Title as CountryName'));

        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        if (!empty($TPSysId) && $TPSysId > 0) {
            $select->where("tb1.TPSysId =?", $TPSysId);
        }
        if (!empty($MasterTPSysId) && $MasterTPSysId > 0) {
            $select->where("tb1.MasterTPSysId =?", $MasterTPSysId);
        }
        if (!empty($MasterMiscSysId) && $MasterMiscSysId > 0) {
            $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        }
        //        $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchRow($select);
        return $result;
    }

    public function AgencyPerformaInvoiceDetailList($MasterMiscSysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('MasterMiscSysId', 'ICSourceSysId', 'MisxItems_name', 'Description', 'ItemQty', 'ItemRates', 'Total', 'DiscountType', 'Discount', 'TotalDiscount', 'TotalAmount', 'MarkupType', 'Markup', 'TotalMarkup', 'SubTotal', 'ServiceFeeTotal', 'GSTPercentage', 'TotalGST', 'SGST', 'CGST', 'IGST', 'ItemsSACCode', 'TcsType', 'TcsAmount');

        $select->from(array('tb1' => "TB_Agency_Perfoma_Invoice_Details"), $selectarray);
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->where("tb1.MasterMiscSysId =?", $MasterMiscSysId);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        // echo $select; exit;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function AgencyPerformaInvoiceGetLastId($ICSourceSysId)
    {
        $select = $this->db->select();
        $selectarray = array('InvoiceNumber');
        if (!empty($ICSourceSysId) && $ICSourceSysId > 0) {
            $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        }
        $select->from(array('tb1' => "TB_Agency_Performa_Invoice"), $selectarray);
        $select->order(array('MasterMiscSysId DESC'));
        $result = $this->db->fetchRow($select);
        return $result['InvoiceNumber'];
    }

    public function getmiscDetailById($miscId, $agentSysId)
    {
        $select = $this->db->select();
        $select->from(array('tb1' => "Tb_Ic_Misc_Items"), array("Title", "Description", "SupplierSysId", "CurrencySysId", "IsTaxable", "TaxSysId", "Cost", "MarkupType", "PriceType", "B2BMarkup", "B2CMarkup", "SacCodeSysId"));
        $select->joinLeft(array('tb2' => "TB_IC_Supplier"), "tb1.SupplierSysId = tb2.SupplierSysId", array('SupplierName'));
        $select->joinLeft(array('tb3' => "TB_Master_Currency"), "tb3.CurrencyType = tb1.CurrencySysId", array('Symbol'));
        $select->where("tb1.MiscItemSysId = ?", $miscId);
        $select->where("tb1.AgentSysId = ?", $agentSysId);
        //        $select->order("tb3.MiscItemSysId DESC");
        $result = $this->db->fetchAll($select);
        return $result;
    }
    public function getProposalInvoiceData($TPSysId, $ICSourceSysId)
    {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_Agency_Miscellaneous"));
        $select->where("tb1.ICSourceSysId =?", $ICSourceSysId);
        $select->where("tb1.TPSysId =?", $TPSysId);
        $select->where("tb1.IsActive =?", 1);
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->order(array('CreateDate DESC'));
        $result = $this->db->fetchRow($select);
        return $result;
    }
}