<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :Dashboard.php
 * File Description :Manage to Dashboard Operation
 * Created By : Pooja
 * Created Date: 28-Dec-2016
 */

class Travel_Model_Dashboard
{

    public $intAgencySysId = NULL;
    private $db = 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 getCustomersCount($agencysysID)
    {
        $select = $this->db->select();
        $arrAgencyCustomer = array('count(CustomerSysId) AS TotalCount');
        $select->from(array('tb1' => "TB_Agency_Customer"), $arrAgencyCustomer);
        $select->joinLeft(array('tb2' => "TB_Agency_CRM_LeadAccount"), "tb2.AgencyCRMLeadSysId = tb1.AgencyCRMLeadSysId", '');
        $select->joinLeft(array('tb3' => "TB_Master_LeadSource"), "tb3.LeadSourceSysId = tb2.LeadSourceSysId", '');
        $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb2.AssignUserSysId", '');
        $select->joinLeft(array('tb5' => "TB_Master_LeadStage"), "tb2.LeadStageSysId = tb5.LeadStageSysId", '');

        $select->where("tb1.IsActive = ?", 1);
        $select->where("tb2.IsActive = ?", 1);
        $select->where("tb1.IsMarkForDelete = ?", 0);
        $select->where("tb2.IsMarkForDelete = ?", 0);
        $select->where("tb2.AgencySysId = ?", $agencysysID);
        $result = $this->db->fetchAll($select);
        return (int) $result[0]['TotalCount'];
    }

    /*Query based on Travel_Model_AgencyLeads*/
    public function GetLeadList($agencySysID)
    {

        $whereCondition = " (tbl.AgencySysId = '$agencySysID') AND (tbl.StatusType IN (1, 2) ) ";
        $whereCondition .= " AND (tbl.PlanType IN (1, 2, 5) )";

        $travel = array('TPSysId', 'StatusType', 'RoomInfoJson', 'StartDate', 'PlanBookingId', 'AgencySysId', 'PlanType', 'CustomerSysId', 'CreateDate', 'Price', 'NetPrice', 'MinPax', 'DestinationPlacesSysId', 'DestinationPlaces');
        $travel_itenary = array('TPIntSysId as itenaryID');
        $travel_air = array('AdultPax', 'ChildPax', 'InfentPax', 'DestPlaceSysId', 'SourceAirportCode', 'DestAirportCode');
        $travel_accom = array('Title as hotelCityTitle', 'AdultPax as adultsinHotel', 'XRefCityId', 'RoomMemInfoJson', 'ChildPax as childsinHotel');
        $customer_details = array("(RTRIM(LTRIM(tb7.FirstName)) + ' ' + RTRIM(LTRIM(tb7.LastName))) as FullName", 'CRMCustSysId');
        $leadAgent_owner = array('FirstName as OwnerAgentFirstName', 'LastName as OwnerAgentLastName');
        $select = $this->db->select();
        $select->limit(5);
        $select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Query"), $travel);
//        $select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary"), "tbl.TPSysId = tb2.TPSysId", $travel_itenary);
//        $select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan_Air"), "tb2.TPIntSysId = tb3.TPIntSysId", $travel_air);
//        $select->joinLeft(array('tb4' => "TB_Agency_Customer_TravelPlan_Accom"), "tb2.TPIntSysId = tb4.TPIntSysId", $travel_accom);
        $select->joinLeft(array('tb6' => "TB_Agency_User"), "tbl.AgentSysId = tb6.UserSysId", $leadAgent_owner);
        //$select->joinLeft(array('tb7' => "TB_CRM_CustomerAccount"), "tbl.CustomerSysId = tb7.CRMCustSysId", $customer_details);
        $select->joinLeft(array('tb7' => "TB_Agency_Customer"), "tbl.CustomerSysId = tb7.CustomerSysId", $customer_details);

        $select->where($whereCondition);
        $select->order("tbl.TPSysId DESC");
        //		echo $select;
        $result = $this->db->fetchAll($select);
        return $result;
    }

    public function getResponseCount($agencysysID)
    {
        $select = $this->db->select();
        $select->from(array('tbl' => "TB_Agency_User_Inbox"), array('count(*) as Total'));
        $select->where("tbl.IsAgent = ?", 0);
        $select->where("tbl.AgencySysId = ?", $agencysysID);
        $result = $this->db->fetchOne($select);
        return $result;
    }


    public function getBookingCount($agencysysID, $TRXType, $agentsysId = NULL, $loginType = NULL)
    {
        $select = $this->db->select();
        $select->from(array('tb2' => "TB_Agency_Customer_TravelPlan"), array('count(tb2.TPSysId) as Total', 'PlanType'));
        $select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId=tb2.CustomerSysId", array());
       // $select->joinLeft(array('tb5' => "TB_Agency"), "tb5.AgencySysId=tb2.AgencySysId", array());
       // $select->joinLeft(array('tb6' => "TB_IC_CurrencyConversion"), "tb6.TCurrencyType=tb5.TrxCurrency AND tb6.FCurrencyType = tb2.CurrencyType", array());
       // $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId=tb2.TPSysId AND TRX.TrxStatus = 1", array());
       // $select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId=tb2.AgentSysId", array());
        $select->where('tb2.StatusType IN(?)', array( 17, 10, 9, 18, 19, 13, 7, 102, 123, 104));
        $select->where("tb2.AgencySysId = ?", $agencysysID);
        $select->where("tb2.PlanType != ?", 1);
        $select->where("tb2.IsMarkForDel = ?", 0);
        $select->group(array('tb2.PlanType'));
//          echo $select;
//        exit; 
        $result = $this->db->fetchAll($select);
        return $result;
    }
}