<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :CustomerTravelPlanFullfillment.php.php
 * File Description :Manage to Fullfillment Operation
 * Created By : Er Amit Kumar Dubey 
 * Created Date: 03-Feb-2018
 */

class Travel_Model_CRM_CustomerTravelPlanFullfillment {

    protected $_dbObj;
    protected $_crmcustObj;
    public $strCondition = "";

    public function __construct() {
        $this->_dbObj = Zend_Db_Table::getDefaultAdapter();
        $this->_HtmlPurifier = new Zend_Filter_HtmlPurifier();
    }

    public function copyCutomerToFullFillment($TPSysId) {
        if ($TPSysId > 0) {
            $objCustTravelPlan = new Travel_Model_CRM_CustomerTravelPlan();
            $recordItenary = $objCustTravelPlan->GetCustomerTravelPlanItenary($TPSysId);
            $recordTravelPlan = $objCustTravelPlan->GetCustomerTravelPlanByTPID($TPSysId);
            $chkRecordItenary = $this->GetCustomerTravelPlanItenaryFullfillment($TPSysId);
            $checkTravelPlanExist = $this->ChkFullfillmentRecordExist($TPSysId);
            $ChkFullfillmentCustomerTravelPlan = $this->ChkFullfillmentCustomerTravelPlan($TPSysId);
            //echo "<pre>";print_r($recordTravelPlan);echo "</pre>";die;
            if (count($checkTravelPlanExist) == 0) {
                $date = date('Y-m-d H:i:s');
                $userArray = array(
                    'TPSysId' => $TPSysId,
                    'SourceCost' => 0,
                    'IGST' => 0,
                    'SGST' => 0,
                    'CGST' => 0,
                    'IsGSTIncluded' => 0,
                    'GSTType' => 0,
                    'GSTSlab' => 0,
                    'Cost' => 0,
                    'TotalCost' => 0,
                    'CreatedDate' => $date,
                    'UpdatedDate' => $date
                );
                $fullfillmentObj = new Travel_Model_CRM_CustomerTravelPlanFullfillment();
                $Rcord = $fullfillmentObj->insertData('TB_Agency_Customer_TravelPlan_Other_Fullfillment', $userArray);
            }
            $userArray = array(
                    
                    'SourceCost' => 0,
                    'IGST' => 0,
                    'SGST' => 0,
                    'CGST' => 0,
                    'IsGSTIncluded' => 0,
                    'GSTType' => 0,
                    'GSTSlab' => 0,
                    'Cost' => 0,
                    'TotalCost' => 0,
                    
                );
            $objPackage = new Travel_Model_TblPackage();
            if(count($ChkFullfillmentCustomerTravelPlan) == 0){
                if ($recordTravelPlan) {
                    foreach ($recordTravelPlan as $TravelPlan) {
                        $objPackage->insertData('TB_Agency_Customer_TravelPlan_Fullfillment', $TravelPlan);
                    }
                }
            }
            
            $lastItenIds = array();
            $oldItenIds = array();
            if (count($chkRecordItenary) == 0) {
                foreach ($recordItenary as $itenary) {
                    $oldItenId = $itenary['TPIntSysId'];
                    $dataArray = $itenary;
                    unset($dataArray['TPIntSysId']);
                    $lastItenIds = $objPackage->insertData('TB_Agency_Customer_TravelPlan_Itenary_Fullfillment', $dataArray);

                    //insert accom
                    $chkRecordItenaryAccom = $this->GetTravelPlanCustomerAccomFullfillment($lastItenIds);
                    if (count($chkRecordItenaryAccom) == 0) {
                        $recordAccom = $objCustTravelPlan->GetTravelPlanCustomerItenaryAccomCopy($oldItenId);
                        if (count($recordAccom) > 0) {
                            $i = 0;
                            foreach ($recordAccom as $itenaryAccom) {
                                $dataAccomArray = $itenaryAccom;
                                $dataAccomArray['TPIntSysId'] = $lastItenIds;
                                unset($dataAccomArray['VersionId']);
                                $objPackage->insertData('TB_Agency_Customer_TravelPlan_Accom_Fullfillment', $dataAccomArray);
                                $i++;
                            }
                        }
                    }
                    //#end for accom
                    //insert activity
                    //$lastItenIdsArray=implode(",",$lastItenIds);
                    //echo $olditnids=implode(",",$oldItenIds);
                    $chkRecordItenaryActivity = $this->GetCustomerTravelPlanItenaryEventsFullfillment($lastItenIds);
                    if (count($chkRecordItenaryActivity) == 0) {
                        //$olditenaryIds=implode(",",$lastItenIdsArray);
                        $recordEvents = $objCustTravelPlan->GetCustomerTravelPlanItenaryEvents($oldItenId);
                        if (count($recordEvents) > 0) {
                            $i = 0;
                            foreach ($recordEvents as $eventsEvents) {
                                $dataEventsArray = $eventsEvents;
                                $dataEventsArray['TPIntSysId'] = $lastItenIds;
                                unset($dataEventsArray['VersionId']);
                                //echo "<pre>";print_r($dataEventsArray);
                                $objPackage->insertData('TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment', $dataEventsArray);
                                $i++;
                            }
                        }
                    }
                    //#end for activity
                    //insert sightseeing
                    $chkRecordItenarySS = $this->GetCustomerTravelPlanSightSeeingFullfillment($lastItenIds);
                    if (count($chkRecordItenarySS) == 0) {
                        $recordSS = $objCustTravelPlan->GetCustomerTravelPlanSightSeeing($oldItenId);
                        if (count($recordSS) > 0) {
                            $i = 0;
                            foreach ($recordSS as $eventsSS) {
                                $dataSSArray = $eventsSS;
                                $dataSSArray['TPIntSysId'] = $lastItenIds;
                                unset($dataSSArray['VersionId']);

                                //echo "<pre>";print_r($dataSSArray);
                                $objPackage->insertData('TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment', $dataSSArray);
                                $i++;
                            }
                        }
                    }
                    //#end for sightseeing
                    //insert places
                    $chkRecordItenaryPlaces = $this->GetAgencyCustomerTravelPlanItenaryPlacesFullfillment($lastItenIds);
                    //echo "<pre>";print_r($chkRecordItenaryPlaces);
                    //echo count($chkRecordItenaryPlaces);
                    if (count($chkRecordItenaryPlaces) == 0) {
                        $recordPlaces = $objCustTravelPlan->GetCustomerTravelPlanItenaryPaces($oldItenId);
                        if (count($recordPlaces) > 0) {
                            $i = 0;
                            foreach ($recordPlaces as $eventsPlaces) {
                                $dataPlacesArray = $eventsPlaces;
                                $dataPlacesArray['TPIntSysId'] = $lastItenIds;
                                unset($dataPlacesArray['VersionId']);
                                //echo "<pre>";print_r($dataSSArray);
                                $objPackage->insertData('TB_Agency_Customer_TravelPlan_Itenary_Places_Fullfillment', $dataPlacesArray);
                                $i++;
                            }
                        }
                    }
                    //#end for places
                    //insert transport
                    $chkRecordItenaryTransport = $this->GetAgencyCustomerTravelPlanItenaryTransportFullfillment($TPSysId);
                    //echo "<pre>";print_r($chkRecordItenaryTransport);
                    if (count($chkRecordItenaryTransport) == 0) {
                        $recordTransport = $objCustTravelPlan->GetAgencyCustomerTravelPlanItenaryTransport($TPSysId);
                        if (count($recordTransport) > 0) {
                            $i = 0;
                            foreach ($recordTransport as $eventsTransport) {
                                $dataTransportArray = $eventsTransport;
                                $dataTransportArray['TPIntSysId'] = $lastItenIds;
                                unset($dataTransportArray['FixTransSysId']);
                                //echo "<pre>";print_r($dataTransportArray);
                                $objPackage->insertData('TB_Agency_Customer_Trans_Fixed_Fullfillment', $dataTransportArray);
                                $i++;
                            }
                        }
                    }
                    //#end for transport
                    //insert otherservices
                    $chkRecordItenaryOTS = $this->GetCustomerTravelPlanOtherServicesFullfillment($TPSysId);
                    if (count($chkRecordItenaryOTS) == 0) {
                        $recordOTS = $objCustTravelPlan->GetCustomerTravelPlanOtherServices($TPSysId);
                        if (count($recordOTS) > 0) {
                            $i = 0;
                            foreach ($recordOTS as $eventsOTS) {
                                $dataOTSArray = $eventsOTS;
                                $dataOTSArray['TPIntSysId'] = $TPSysId;
                                unset($dataOTSArray['VersionId']);
                                //echo "<pre>";print_r($dataSSArray);
                                $objPackage->insertData('TB_Agency_Customer_TravelPlan_OtherServices_Fullfillment', $dataOTSArray);
                                $i++;
                            }
                        }
                    }
                    //#end for otherservices
                }
            }
        }
        return true;
    }

    public function GetCustomerTravelPlanItenaryFullfillment($TPSysId) {
        $select = $this->_dbObj->select(array('TPSysId'));
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"));
        $select->where('tbl.TPSysId = ?', $TPSysId);
        $select->Where('tbl.IsMarkForDel = ?', '0');
        $select->Where('tbl.IsActive = ?', '1');
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }
    
    public function SequenceTravelPlanItenaryFullfillment($TPSysId) {
        $select = $this->_dbObj->select(array('Sequence'));
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"),array('Sequence'));
        $select->where('tbl.TPSysId = ?', $TPSysId);
        $select->Where('tbl.IsMarkForDel = ?', '0');
        $select->Where('tbl.IsActive = ?', '1');
        $select->order('tbl.TPIntSysId DESC');
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetTravelPlanCustomerAccomFullfillment($tplanitenayvalues, $vid = null) {
        $select = $this->_dbObj->select(array('TPIntSysId'));
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom_Fullfillment"));
        $select->Where('tbl.TPIntSysId IN (' . $tplanitenayvalues . ')');
        $vid ? $select->Where('tbl.VersionId = ?', $vid) : '';
        $select->Where('tbl.IsMarkForDel = ?', '0');
        $select->Where('tbl.IsActive = ?', '1');
        //echo $select;
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

    public function GetCustomerTravelPlanItenaryEventsFullfillment($tplanitenayvalues) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment"));
        $select->Where('tbl.TPIntSysId IN (' . $tplanitenayvalues . ')');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustomerTravelPlanSightSeeingFullfillment($tplanitenayvalues) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment"));
        $select->Where('tbl.InvnItemSysId IN (' . $tplanitenayvalues . ')');
        $select->Where('tbl.IsMarkForDelete = ?', '0');
        $select->Where('tbl.IsActive = ?', '1');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAgencyCustomerTravelPlanItenaryTransportFullfillment($tpsysID) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_Trans_Fixed_Fullfillment"));
        $select->where('tbl.TPSysId = ?', $tpsysID);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustomerTravelPlanOtherServicesFullfillment($tpsysID) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_OtherServices_Fullfillment"));
        $select->where('tbl.TPIntSysId = ?', $tpsysID);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetAgencyCustomerTravelPlanItenaryPlacesFullfillment($tplanitenayvalues) {
        $select = $this->_dbObj->select();
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Itenary_Places_Fullfillment"));
        $select->Where('tbl.TPIntSysId IN (' . $tplanitenayvalues . ')');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    //get hotel details from fullfillment
    public function GetCustomerItenaryAccomDetailsFullfill($TPIntSysId, $vid = NULL, $intAccomSysId = NULL) {
        $icaccomodation = array('PrimaryContact as HPrimaryContact', 'SecondaryContact as HSecondaryContact', 'Address as HAddress');
        $supplier_details = array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Accom_Fullfillment"));
        $select->joinLeft(array('tb2' => "TB_IC_Accomdation"), "tb1.AccoSysId = tb2.AccomSysId", $icaccomodation);
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb1.XrefCityId = tb3.CityId", array("Title as CityName"));
        $select->joinLeft(array('tb4' => "TB_Master_Accom_MealType"), "tb1.MealPlanType = tb4.MealTypeId", array('Title as RoomMealType'));
        $select->joinLeft(array('tb5' => "TB_IC_Supplier"), "tb1.SupplierSysId = tb5.SupplierSysId", $supplier_details);
        $select->joinLeft(array('tb6' => "TB_Master_Geo_City"), "tb1.PickUpCitySysId = tb6.CityId", array("Title as PickUpCityName"));
        $select->Where('tb1.TPIntSysId = ?', $TPIntSysId);
        $vid ? $select->Where('tb1.VersionId = ?', $vid) : '';
        $select->Where('tb1.IsMarkForDel = ?', '0');
        $select->Where('tb1.IsActive = ?', '1');
        $select->Where("tb1.AccoSysId = ?", $intAccomSysId);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustTravelPlanItenaryEventIds($tplanitenayvalues, $VersionId = NULL, $TPActivitySysId = NULL) {
        $columnArray = array('Title', 'FromDate', 'ActivityType', 'VehicleSupplierName', 'VehicleSupplierNumber', 'VehicleSupplierEmail', 'VehiclePickUpTime', 'VehicleDropTime', 'VehiclePickUpLocation', 'XRefVoucherNo', 'TotalCost', 'IsIncludedInTransport', 'VehicleName', 'VehicleContactPerson', 'VehicleContactNumber', 'BookingRemarks', 'IsActivityConfirmed', 'TotalAdult', 'TotalChild');
        $ActColumnArray = array("DurationInMin", "DurationStr", "PickUpLocation", "PrimaryContact");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment"), $columnArray);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb1.CityId = tb2.CityId", array("Title as CityName"));
        $select->joinLeft(array('tb3' => "TB_IC_Activity"), "tb3.ActivitySysId = tb1.InvnItemSysId", $ActColumnArray);
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb4.CityId = tb1.VehiclePickUpLocation", array("Title as PickUpLocationCityName"));
        $select->Where('tb1.TPIntSysId IN (' . $tplanitenayvalues . ')');
        if ($TPActivitySysId) {
            $select->Where('tb1.TPActivitySysId = ?', $TPActivitySysId);
        }
        if ($VersionId) {
            $select->Where('tb1.VersionId = ?', $VersionId);
        }
        $select->Where('tb1.IsMarkForDel = ?', '0');
        $select->Where('tb1.IsActive = ?', '1');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetCustTravelPlanItenarySSIds($tplanitenayvalues, $VersionId = NULL, $SSSysId = NULL) {
        $columnArray = array('Title', 'FromDate', 'SightSeeingType', 'VehicleSupplierName', 'VehicleSupplierNumber', 'VehicleSupplierEmail', 'VehiclePickUpTime', 'VehicleDropTime', 'VehiclePickUpLocation', 'XRefVoucherNo', 'TotalCost', 'IsIncludedInTransport', 'VehicleName', 'VehicleContactPerson', 'VehicleContactNumber', 'BookingRemarks', 'IsSightSeeingConfirmed', 'TotalAdult', 'TotalChild');
        $SSColumnArray = array("DurationInMin", "DurationStr", "PickUpLocation", "PrimaryContact");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment"), $columnArray);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb1.CityId = tb2.CityId", array("Title as CityName"));
        $select->joinLeft(array('tb4' => "TB_Master_Geo_City"), "tb1.VehiclePickUpLocation = tb4.CityId", array("Title as VehicleCityName"));
        $select->joinLeft(array('tb3' => "TB_IC_SightSeeing"), "tb3.SSSysId = tb1.SSSysId", $SSColumnArray);
        $select->joinLeft(array('tb5' => "TB_Master_Geo_City"), "tb5.CityId = tb1.VehiclePickUpLocation", array("Title as PickUpLocationCityName"));
        $select->Where('tb1.TPIntSysId IN (' . $tplanitenayvalues . ')');
        if ($SSSysId) {
            $select->Where('tb1.SSSysId = ?', $SSSysId);
        }
        if ($VersionId) {
            $select->Where('tb1.VersionId = ?', $VersionId);
        }
        $select->Where('tb1.IsMarkForDelete = ?', '0');
        $select->Where('tb1.IsActive = ?', '1');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function GetHotelDetailsWithTravelPlan($TPSysId, $AccomSysId) {
        $travel = array('TPSysId', 'NetPrice');
        $travel_itenary = array('TPIntSysId');
        $travel_accom = array('Title as hotelCityTitle', 'FromDate as hotelFromDate', 'TotalCost', 'ToDate as hotelToDate', 'CreateDate as hotelCreateDate', 'UpdateDate as hotelUpdateDate', 'AdultPax as adultsinHotel', 'TotalNights as hotelTotalNights', 'ChildPax as childsinHotel', 'InfantPax as infentinHotel', 'StarRating as hotelStarRating', 'ChildsAge as hotelChildsAge', 'XrefCityId as hotelXrefCityId', 'AccoSysId as hotelAccoSysId');
        $customer_details = array('CRMCustSysId', 'EmailId');
        $supplier_details = array('SupplierSysId', 'SupplierName', 'Salution as SupplierSalution', 'FirstName as SupplierFirstName', 'LastName as SupplierLastName', 'EmailId as SupplierEmailId');
        $accom_details = array('Title as ICHotelTitle', 'Rating as ICHotelRating', 'Stars as ICHotelStars');
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan"), $travel);
        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary_Fullfillment"), "tb1.TPSysId = tb2.TPSysId", $travel_itenary);
        $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan_Accom_Fullfillment"), "tb2.TPIntSysId = tb3.TPIntSysId", $travel_accom);
        $select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb4.CustomerSysId", $customer_details);
        $select->joinLeft(array('tb5' => "TB_IC_Supplier"), "tb3.SupplierSysId = tb5.SupplierSysId", $supplier_details);
        $select->joinLeft(array('tb6' => "TB_IC_Accomdation"), "tb3.AccoSysId = tb6.AccomSysId", $accom_details);
        $select->joinLeft(array('tb7' => "TB_Master_RoomType"), "tb3.RoomType = tb7.RoomType", array('Title as RoomTitle'));
        $select->joinLeft(array('tb8' => "TB_Master_Accom_MealType"), "tb3.MealPlanType = tb8.MealTypeId", array('Title as RoomMealType'));

        $select->where('tb1.TPSysId = ?', $TPSysId);
        $select->where('tb6.AccomSysId = ?', $AccomSysId);
        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb3.IsActive = ?", 1);
        $select->where("tb4.IsActive = ?", 1);
        //$select->where("tb5.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDel = ?", 0);
        $select->where("tb2.IsMarkForDel = ?", 0);
        $select->where("tb3.IsMarkForDel = ?", 0);
        $select->where("tb4.IsMarkForDelete = ?", 0);
        //$select->where("tb5.IsMarkForDelete = ?", 0);
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    //function to fetch activity details by activityid
    public function getCustPackageActivityListBySeqWithId($packageId, $seq, $ActivityId) {
        $sqlPackageActivity = "SELECT TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.*,TB_IC_Activity.DurationStr as duration,TB_Master_Geo_City.Title as CityName, TB_IC_Activity.*,TB_Master_ActivityGroupType.Title as Category,TB_IC_Supplier.SupplierName,TB_IC_Supplier.Salution as SupplierSalution,TB_IC_Supplier.FirstName as SupplierFirstName,TB_IC_Supplier.LastName as SupplierLastName					 
                        FROM TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment 
						LEFT JOIN TB_IC_Activity ON TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.TPActivitySysId=TB_IC_Activity.ActivitySysId
						LEFT JOIN TB_Master_Geo_City ON TB_IC_Activity.CitySysId = TB_Master_Geo_City.CityId
						LEFT JOIN TB_IC_Supplier ON TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.SupplierSysId = TB_IC_Supplier.SupplierSysId
						LEFT JOIN TB_Master_ActivityGroupType ON TB_IC_Activity.ActivityGrType=TB_Master_ActivityGroupType.ActivityGroupType
						WHERE TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.IsActive = '1' AND TB_IC_Activity.ActivitySysId = '" . $ActivityId . "' AND TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.IsMarkForDel = '0' AND TB_Agency_Customer_TravelPlan_Itenary_Events_Fullfillment.TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary_Fullfillment WHERE TPSysId = '" . @$packageId . "'  AND Sequence = " . $seq . " )";
        //AND TB_TravelPlan_Itenary_Events.IsActive = '1' AND TB_TravelPlan_Itenary_Events.IsMarkForDel = '0'
        $resultPackageActivity = $this->_dbObj->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    //function to fetch sightseeing details by sightseeing id
    public function getCustPackageSightseenListBySeqWithId($packageId, $seq, $SightSeeingSysId) {
        $sqlPackageActivity = "SELECT TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.*,TB_IC_SightSeeing.DurationStr as duration,TB_Master_Geo_City.Title as CityName, TB_IC_SightSeeing.*,TB_Master_SightSeeingGroupType.Title as Category,TB_IC_Supplier.SupplierName,TB_IC_Supplier.Salution as SupplierSalution,TB_IC_Supplier.FirstName as SupplierFirstName,TB_IC_Supplier.LastName as SupplierLastName
                        FROM TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment 
						LEFT JOIN TB_IC_SightSeeing ON TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.SSSysId=TB_IC_SightSeeing.SSSysId
						LEFT JOIN TB_Master_SightSeeingGroupType ON TB_IC_SightSeeing.SSGrType=TB_Master_SightSeeingGroupType.SSGroupType
						LEFT JOIN TB_Master_Geo_City ON TB_IC_SightSeeing.CitySysId = TB_Master_Geo_City.CityId 
						LEFT JOIN TB_IC_Supplier ON TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.SupplierSysId = TB_IC_Supplier.SupplierSysId
						WHERE TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.IsActive = '1' AND TB_IC_SightSeeing.SSSysId = '" . $SightSeeingSysId . "' AND TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.IsMarkForDelete = '0' AND TB_Agency_Customer_TravelPlan_SightSeeing_Fullfillment.TPIntSysId IN (SELECT TPIntSysId FROM TB_Agency_Customer_TravelPlan_Itenary_Fullfillment WHERE TPSysId = '" . @$packageId . "' AND Sequence = " . $seq . ")";

        //AND TB_TravelPlan_SightSeeing.IsActive = '1' AND TB_TravelPlan_SightSeeing.IsMarkForDelete = '0'
        $resultPackageActivity = $this->_dbObj->query($sqlPackageActivity)->fetchAll();
        return $resultPackageActivity;
    }

    public function GetCustTravelPlanItenaryTransDetails($tplanitenayvalues, $Sequence = NULL, $FixTransSysId = NULL) {
        $columnArray = array('FixTransSysId', 'VehicleName', 'TravelDateFrom', 'TravelDateTo', 'PickUpCity', 'PickUpLocation', 'PickUpRemarks', 'DropCity', 'DropLocation', 'DropRemarks', 'SupplierName', 'SupplierNumber', 'TotalCost', 'Remarks', 'EmailText', 'SendToEmailId');
        $ActColumnArray = array("DurationInMin", "DurationStr", "PickUpLocation", "PrimaryContact");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_Agency_Customer_Trans_Fixed_Fullfillment"), $columnArray);
        $select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb1.PickUpCity = tb2.CityId", array("Title as PickUpCityName"));
        $select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb1.DropCity = tb3.CityId", array("Title as DropCityName"));
        $select->joinLeft(array('tb4' => "TB_Agency_Transport_Route"), "tb4.RouteSysId = tb1.RouteSysId", array("RouteName"));

        // $select->joinLeft(array('tb3' => "TB_IC_Activity"), "tb3.ActivitySysId = tb1.InvnItemSysId", $ActColumnArray);
        $select->Where('tb1.TPIntSysId IN (' . $tplanitenayvalues . ')');
        if ($FixTransSysId) {
            $select->Where('tb1.FixTransSysId = ?', $FixTransSysId);
        }
        if ($Sequence) {
            $select->Where('tb1.Sequence = ?', $Sequence);
        }
        $select->Where('tb1.IsMarkForDel = ?', '0');
        $select->Where('tb1.IsActive = ?', '1');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    //get hotel details by accom id
    public function GetMasterHotelDetailsById($AccomSysIds) {
        $AccColumnArray = array("PrimaryContact", "Brief");
        $select = $this->_dbObj->select();
        $select->from(array('tb1' => "TB_IC_Accomdation"), $AccColumnArray);
        $select->Where('tb1.AccomSysId IN (' . $AccomSysIds . ')');
        //echo $select;
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

    public function ChkFullfillmentRecordExist($TPSysId, $IsSalesPrice=NULL,$TPIntSysId=NULL,$VersionId=NULL) {
        $select = $this->_dbObj->select(array('TPSysId', 'SourceCost', 'IGST', 'SGST', 'CGST', 'IsGSTIncluded', 'GSTType', 'GSTSlab', 'Cost', 'TotalCost', 'VehicleType', 'VehicleQty', 'VehicleName', 'PickUpDate', 'PickUpCitySysId', 'PickUpLocation', 'PickUpRemarks', 'DropCitySysId', 'DropLocation', 'DropDate', 'InstructionForDriver', 'SightSeeingIncluded', 'TransSupplierName', 'TransSupplierContact', 'TransSupportName', 'TransSupportContact', 'TransCondId'));
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Other_Fullfillment"));
        if(!empty($IsSalesPrice)){
            $select->where('tbl.IsSalesPrice = ?', $IsSalesPrice);
        }else{
            $select->where('tbl.IsSalesPrice = ?', 0);
        }
        if(!empty($TPIntSysId)){
            $select->where('tbl.TPIntSysId = ?', $TPIntSysId);
        }
        if(!empty($VersionId)){
            $select->where('tbl.VersionId = ?', $VersionId);
        }
        $select->where('tbl.TPSysId = ?', $TPSysId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }
    public function ChkFullfillmentRecordExistSales($TPSysId, $IsSalesPrice=NULL) {
        $select = $this->_dbObj->select(array('TPSysId', 'SourceCost', 'IGST', 'SGST', 'CGST', 'IsGSTIncluded', 'GSTType', 'GSTSlab', 'Cost', 'TotalCost', 'VehicleType', 'VehicleQty', 'VehicleName', 'PickUpDate', 'PickUpCitySysId', 'PickUpLocation', 'PickUpRemarks', 'DropCitySysId', 'DropLocation', 'DropDate', 'InstructionForDriver', 'SightSeeingIncluded', 'TransSupplierName', 'TransSupplierContact', 'TransSupportName', 'TransSupportContact', 'TransCondId'));
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Other_Fullfillment"));
        if(!empty($IsSalesPrice)){
            $select->where('tbl.IsSalesPrice = ?', $IsSalesPrice);
        }
        $select->where('tbl.TPIntSysId != ?', '');
        $select->where('tbl.TPSysId = ?', $TPSysId);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    public function ChkFullfillmentCustomerTravelPlan($TPSysId) {
        $select = $this->_dbObj->select('*');
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Fullfillment"));
        $select->where('tbl.TPSysId = ?', $TPSysId);
        $result = $this->_dbObj->fetchRow($select);
        return $result;
    }

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

    public function insertData($table, $data) {
        $this->_dbObj->insert($table, $data);
        return $this->_dbObj->lastInsertId();
    }
    
    public function GetMealPlan() {
        $select = $this->_dbObj->select(array('MealTypeId', 'Title','Icon'));
        $select->from(array('tb1' => "TB_Master_Accom_MealType"));
        $select->where("tb1.IsMarkForDel =?", 0);
        $select->where("tb1.IsActive =?", 1);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    
    public function getCustPackageList($id){
        $sqlFlexiPackage = "SELECT a.*,TBMC.Symbol FROM TB_Agency_Customer_TravelPlan_Fullfillment a "
                . "LEFT JOIN TB_Master_Currency TBMC ON TBMC.CurrencyType = a.CurrencyType WHERE TPSysId = '".@$id."' ";
    	$resultFlexiPackage = $this->_dbObj->query($sqlFlexiPackage)->fetch();
		        
        return $resultFlexiPackage;
    }
    
    public function GetTravelPlanDetailsofCustomer($tpID) {
        $select = $this->_dbObj->select();
//        $travelplanarray = array('TPSysId', 'IsCardChrgToCustomer', 'SupplierSysId', 'CurrencyType', 'AgencySysId', 'AgentSysId', 'CustomerSysId', 'RoomInfoJson', 'AdditionalReq', 'StartDate', 'ValidTill', 'Cities', 'Countries', 'Price', 'NetPrice', 'CreatorSysId');
        $travelplanarray = array('TPSysId', 'AgencySysId', 'AgentSysId');
        $customerarray = array('CustomerSysId', 'EmailId', 'Contacts', 'FirstName', 'Title as Salutation', 'LastName', 'IsB2BAgent');
        $select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Fullfillment"), $travelplanarray);
        $select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tb1.CustomerSysId = tb4.CustomerSysId", $customerarray);
        $select->where('tb1.TPSysId = ?', $tpID);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }
    public function GetVersionInfo($tpID) {
        $select = $this->_dbObj->select();
        $versionarray = array('TPSysId', 'VersionId');
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Fullfillment", $versionarray));
        $select->joinLeft(array('tb3' => "TB_Master_Currency"), "tbl.CurrencyType = tb3.CurrencyType", 'Symbol');
        $select->where('tbl.MasterTPSysId = ?', $tpID);
        $select->where("tbl.IsActive = ?", 1);
        $select->where("tbl.IsMarkForDel = ?", 0);
        $result = $this->_dbObj->fetchAll($select);
        return $result;
    }

}