<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :TblCRMCustomer.php
 * File Description :Manage to Customer Operation
 * Created By : Shobhit Srivastav 
 * Created Date: 03-Aug-2016
 */

class Travel_Model_CRM_AgencyCustomerTodo {
    protected $_dbObj;
    protected $_agencycustObj;

	public $_agencycusttodoObj;
	public $_crmcustomertodoObj;
	public $intLoggedinUserAgencySysId;
	public $intLoggedinUserId;
	public $UserRoleTitle;
	public $smtpUserName;
	public $smtpPassword;
	public $smtpPort;
	public $smtpHost;
	public $fromEmail;
	public $fromName;
	public $siteUrl;
	public $_crmcustomerObj;
	public $_agencyuserroleObj;


    public function __construct() {
		
		$this->_agencycusttodoObj = new Travel_Model_TblAgencyCustomerToDo();
		$this->_dbObj = Zend_Db_Table::getDefaultAdapter();
    }
	
	
	public function addAgencyCustomerTodo(array $data) {
		$this->_agencycusttodoObj->insert($data);
		$lastID = $this->_dbObj->lastInsertId();
		return $lastID;
	}
	
	public function UpdateAgencyCustomer(array $data, $where=NULL){
		$this->_agencycusttodoObj->update($data, $where);
	}
	public function UpdateCustomerTodo(array $data, $where=NULL){
		$this->_agencycusttodoObj->update($data, $where);
	}
	
	public function GetAllAgentToDo(){
		 $cuur_date = date('Y-m-d 00:00:00');
		 $select = $this->_dbObj->select();
	     $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
	     $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
	     //$select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CustSysId = tb2.CRMCustSysId");
		 $select->where("tbl.EndDateTime > ?", $cuur_date);
		 $select->where("tbl.IsActive = ?", 1);
		 $select->where("tbl.IsMarkForDel = ?", 0);
		 $select->where("tb2.IsActive = ?", 1);
		 $select->where("tb2.IsMarkForDelete = ?", 0);
		 $select->order("tbl.ToDoSysId DESC");
	     $result = $this->_dbObj->fetchAll($select);
	     return $result; 
	}
	public function GetAllAgentNotCompletedToDo($limit = NULL, $AgencyUserSysID = NULL, $AgencySysId = NULL, $CustomerSysId = NULL, $IsMarkForDel = NULL, $Order = NULL){
            $cuur_date = date('Y-m-d 00:00:00');
            $select = $this->_dbObj->select();
            $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
            $b2bagencyColumn=array('AgencyName as CompanyName');
            
            $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
            $select->joinLeft(array('tb3' => "TB_Agency_User"), "tb3.UserSysId = tb2.CreatedByUserSysId",$b2bagencyColumn);
            $select->where("tbl.EndDateTime > ?", $cuur_date);
            $select->where("tbl.IsCompleted = ?", 0);
            $select->where("tbl.IsActive = ?", 1);
            $select->where("(tb2.IsMarkForDelete = 0 AND tb2.IsActive = 1) OR (tbl.CustSysId = 0) ");
            $AgencyUserSysID?$select->where('tbl.AssignToSysId = ?', $AgencyUserSysID):'';
            if($CustomerSysId){
                $select->where('tb2.CustomerSysId = ?', $CustomerSysId);
            }
            if($IsMarkForDel == 1){
                $select->where("tbl.IsMarkForDel = ?", 0);
            }
            $select->where("(tb2.AgencySysId = $AgencySysId OR tb2.AgencySysId IN (SELECT AgencySysId FROM TB_Agency Where MasterAgencySysId = $AgencySysId)) OR (tbl.CustSysId = 0 AND tbl.AgencySysId = $AgencySysId)");
            if($Order){
                $select->order($Order);
            }
            if($limit){
                $select->limit($limit);
            }
            $result = $this->_dbObj->fetchAll($select);
            return $result; 
	}
	public function GetAllCustomerNotCompletedToDo($limit = NULL, $AgencyUserSysID = NULL, $AgencySysId = NULL, $CustomerSysId = NULL, $IsMarkForDel = NULL, $Order = NULL){
            $cuur_date = date('Y-m-d 00:00:00');
            $select = $this->_dbObj->select();
            $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
            $b2bagencyColumn=array('AgencyName as CompanyName');
            
            $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
            $select->joinLeft(array('tb3' => "TB_Agency_User"), "tb3.UserSysId = tb2.CreatedByUserSysId",$b2bagencyColumn);
            $select->where("tbl.EndDateTime > ?", $cuur_date);
            $select->where("tbl.IsCompleted = ?", 0);
            $select->where("tbl.IsActive = ?", 1);
            $select->where("(tb2.IsMarkForDelete = 0 AND tb2.IsActive = 1) OR (tbl.CustSysId = 0) ");
            $AgencyUserSysID?$select->where('tbl.AssignToSysId = ?', $AgencyUserSysID):'';
            $select->where("tbl.TPSysId = ?", 0);
            if($CustomerSysId){
                $select->where('tb2.CustomerSysId = ?', $CustomerSysId);
            }
            if($IsMarkForDel == 1){
                $select->where("tbl.IsMarkForDel = ?", 0);
            }
            $select->where("(tb2.AgencySysId = $AgencySysId OR tb2.AgencySysId IN (SELECT AgencySysId FROM TB_Agency Where MasterAgencySysId = $AgencySysId)) OR (tbl.CustSysId = 0 AND tbl.AgencySysId = $AgencySysId)");
            if($Order){
                $select->order($Order);
            }
            if($limit){
                $select->limit($limit);
            }
            $result = $this->_dbObj->fetchAll($select);
            return $result; 
	}
	//this function is used in view all todo list cretaed by Er amit kumar dubey on 13 january 2017 at 12:58 PM
	public function GetViewAllAgentNotCompletedToDo($AgencyUserSysID = NULL, $AgencySysId = NULL, $filterData = NULL, $limit = NULL){
		 $cuur_date = date('Y-m-d 00:00:00');
		 $cuur_dateEnd = date('Y-m-d 23:59:59.999');
		 $select = $this->_dbObj->select();
	     $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
	     $select->limit($limit);
	     $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
	    // $select->joinLeft(array('tb3' => "TB_Agency_CRM_LeadAccount"), "tb2.AgencyCRMLeadSysId = tb3.AgencyCRMLeadSysId");
		// $select->where("tbl.EndDateTime > ?", $cuur_date);
		if(!empty($filterData['todofromdate'])){
			$date1=explode("/",$filterData['todofromdate']);
			$date2=explode("/",$filterData['todotodate']);
			$filterData['todofromdate']=$date1[2]."-".$date1[1]."-".$date1[0];
			$filterData['todotodate']=$date2[2]."-".$date2[1]."-".$date2[0];
			$select->where('tbl.EndDateTime >= ?', $filterData['todofromdate']);
			$select->where('tbl.EndDateTime <= ?', $filterData['todotodate']." 23:59:59.999");
		} else {
		    $select->where('tbl.EndDateTime >= ?', $cuur_date);
		    $select->where('tbl.EndDateTime <= ?', $cuur_dateEnd);
		}
  		$AgencyUserSysID?$select->where('tbl.AssignToSysId = ?', $AgencyUserSysID):'';
  		$select->where('tb2.AgencySysId = ?', $AgencySysId);
		$select->where("tbl.IsCompleted = ?", 0);
		$select->where("tbl.IsActive = ?", 1);
		$select->where("tbl.IsMarkForDel = ?", 0);
		$select->where("tb2.IsActive = ?", 1);
		$select->where("tb2.IsMarkForDelete = ?", 0);
		$select->order("tbl.StartDateTime DESC");
		$result = $this->_dbObj->fetchAll($select);
	    return $result; 
	}
	//this function is used in header notification todo list cretaed by Er amit kumar dubey on 13 january 2017 at 05:56 PM
	public function GetViewAllAgentToDoNotification($AgencyUserSysID = NULL, $AgencySysId = NULL, $filterData = NULL, $limit = NULL){
		 $assignedToUserColumn = array('FirstName as AssignedToFirstName','LastName as AssignedToLastName');
		 $customerToColumn = array('FirstName','LastName');
		 $cuur_date = date('Y-m-d 00:00:00');
		 $cuur_dateEnd = date('Y-m-d 23:59:59.999');
		 $select = $this->_dbObj->select();
	     $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
	     $select->limit($limit);
	     $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId",$customerToColumn);
	     $select->joinLeft(array('tb3' => "TB_Agency_user"), "tbl.AssignToSysId = tb3.UserSysId",$assignedToUserColumn);
		// $select->where("tbl.EndDateTime > ?", $cuur_date);
		    $select->where('tbl.EndDateTime >= ?', $cuur_date);
		    $select->where('tbl.EndDateTime <= ?', $cuur_dateEnd);
		/*if(!empty($filterData['todotodate'])){
		 $select->where('tbl.EndDateTime >= ?', $filterData['todofromdate']);
		}*/
  		$AgencyUserSysID?$select->where('tbl.AssignToSysId = ?', $AgencyUserSysID):'';
  		$select->where('tb2.AgencySysId = ?', $AgencySysId);
		$select->where("tbl.IsCompleted = ?", 0);
		$select->where("tbl.IsActive = ?", 1);
		$select->where("tbl.IsMarkForDel = ?", 0);
		$select->where("tb2.IsActive = ?", 1);
		$select->where("tb2.IsMarkForDelete = ?", 0);
		$select->order("tbl.StartDateTime DESC");
		$result = $this->_dbObj->fetchAll($select);
	     return $result; 
	}
	//this function is used in view all todo list cretaed by Er amit kumar dubey on 05 july 2017 at 06:30 PM
	public function GetViewAllAgentToDo($AgencyUserSysID = NULL, $AgencySysId = NULL, $filterData = NULL, $limit = NULL, $Order = NULL){
		 $createdByUserColumn = array('FirstName as CreatedByFirstName','LastName as CreatedByLastName');
		 $assignedToUserColumn = array('FirstName as AssignedToFirstName','LastName as AssignedToLastName');
		 $b2bagencyColumn = array('AgencyName as CompanyName');
		 
		 $cuur_date = date('Y-m-d 00:00:00');
		 $cuur_dateEnd = date('Y-m-d 23:59:59.999');
		 $select = $this->_dbObj->select();
	     $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
	     $select->limit($limit);
	     $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
		 $select->joinLeft(array('tb3' => "TB_Agency_User"), "tbl.CreatedBySysId = tb3.UserSysId",$createdByUserColumn);
		 $select->joinLeft(array('tb4' => "TB_Agency_User"), "tbl.AssignToSysId = tb4.UserSysId",$assignedToUserColumn);
		 $select->joinLeft(array('tb5' => "TB_Agency_User"), "tb5.UserSysId = tb2.CreatedByUserSysId",$b2bagencyColumn);
	    // $select->joinLeft(array('tb3' => "TB_Agency_CRM_LeadAccount"), "tb2.AgencyCRMLeadSysId = tb3.AgencyCRMLeadSysId");
		// $select->where("tbl.EndDateTime > ?", $cuur_date);
		if(!empty($filterData['todofromdate'])){
			$date1=explode("/",$filterData['todofromdate']);
			$date2=explode("/",$filterData['todotodate']);
			$filterData['todofromdate']=$date1[2]."-".$date1[1]."-".$date1[0];
			$filterData['todotodate']=$date2[2]."-".$date2[1]."-".$date2[0];
			$select->where('tbl.StartDateTime >= ?', $filterData['todofromdate']);
			$select->where('tbl.StartDateTime <= ?', $filterData['todotodate']." 23:59:59.999");
			//$select->where('tbl.EndDateTime >= ?', $filterData['todofromdate']);
			//$select->where('tbl.EndDateTime <= ?', $filterData['todotodate']." 23:59:59.999");
		} 
                if(!empty($filterData['createdatefromdate'])){
			$date1=explode("/",$filterData['createdatefromdate']);
			$date2=explode("/",$filterData['createdatetodate']);
			$filterData['createdatefromdate']=$date1[2]."-".$date1[1]."-".$date1[0];
			$filterData['createdatetodate']=$date2[2]."-".$date2[1]."-".$date2[0];
			$select->where('tbl.Createdate >= ?', $filterData['createdatefromdate']);
			$select->where('tbl.Createdate <= ?', $filterData['createdatetodate']." 23:59:59.999");
			//$select->where('tbl.EndDateTime >= ?', $filterData['todofromdate']);
			//$select->where('tbl.EndDateTime <= ?', $filterData['todotodate']." 23:59:59.999");
		} 
		if(!empty($filterData['agencyUserAssignedToFilter'])){
			$select->where('tbl.AssignToSysId = ?', $filterData['agencyUserAssignedToFilter']);
		} 
                if(!empty($filterData['customerName'])){
			$select->where("CONCAT(tb2.FirstName,' ',tb2.LastName) like (?)", '%'.$filterData['customerName'].'%');
		} 
		if(!empty($filterData['agencyUserCreatedByFilter'])){
			$select->where('tbl.CreatedBySysId = ?', $filterData['agencyUserCreatedByFilter']);
		} 
		if(!empty($filterData['statgeType'])){
			$select->where('tbl.type = ?', $filterData['statgeType']);	
		}
		if(!empty($filterData['agencyUserTaskFilter'])){
			if($filterData['agencyUserTaskFilter']==1){
				$select->where('tbl.IsCompleted = ?', 1);
			}
			if($filterData['agencyUserTaskFilter']==2){
				$time = date('H:i');
				$select->where('tbl.IsCompleted = ?', 0);
				$select->where('tbl.StartDateTime > ?', $filterData['todofromdate']." ".$time);
			}
			if($filterData['agencyUserTaskFilter']==3){
				$time = date('H:i');
				$select->where('tbl.IsCompleted = ?', 0);
				$select->where('tbl.StartDateTime >= ?', $filterData['todofromdate']." ".$time);
				$select->where('tbl.StartDateTime <= ?', $filterData['todotodate']." ".$time);
			}
		} 
  		//$AgencyUserSysID?$select->where('tbl.AssignToSysId IN ('.$AgencyUserSysID.') or tbl.CreatedBySysId IN ('.$AgencyUserSysID.')'):'';
                $AgencyUserSysID?$select->where('tbl.AssignToSysId IN ('.$AgencyUserSysID.') '):'';
                $select->where("(tb2.AgencySysId = $AgencySysId OR tb2.AgencySysId IN (SELECT AgencySysId FROM TB_Agency Where MasterAgencySysId = $AgencySysId)) OR (tbl.CustSysId = 0 AND tbl.AgencySysId = $AgencySysId)"); 
		$select->where("(tb2.IsMarkForDelete = 0 AND tb2.IsActive = 1) OR (tbl.CustSysId = 0) ");
                $select->where("tbl.IsActive = ?", 1);
		$select->where("tbl.IsMarkForDel = ?", 0);
//		$select->where("tb2.IsActive = ?", 1);
//		$select->where("tb2.IsMarkForDelete = ?", 0);
                if($Order){
                    $select->order($Order);
                }else{
                    $select->order("tbl.StartDateTime DESC");
                }
		
                //echo $select; exit;
		$result = $this->_dbObj->fetchAll($select);
	    return $result; 
	}
	
	//this function is used in cron for send email to all agent/agency for pending todo till current date cretaed by Er amit kumar dubey on 27 dec 2017 at 03:03 PM
	public function GetAllPendingTodoEmail($AgencyUserSysID){
		 $createdByUserColumn = array('FirstName as CreatedByFirstName','LastName as CreatedByLastName');
		 $assignedToUserColumn = array('FirstName as AssignedToFirstName','LastName as AssignedToLastName');
		 $b2bagencyColumn = array('AgencyName as CompanyName');
		 
		 
		 $cuur_date = date('Y-m-d 00:00:00',strtotime('yesterday'));
		 $cuur_dateEnd = date('Y-m-d 23:59:59.999');
		 $select = $this->_dbObj->select();
	     $select->from(array('tbl' => "TB_Agency_Customer_ToDo"));
	     $select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId");
		 $select->joinLeft(array('tb3' => "TB_Agency_User"), "tbl.CreatedBySysId = tb3.UserSysId",$createdByUserColumn);
		 $select->joinLeft(array('tb4' => "TB_Agency_User"), "tbl.AssignToSysId = tb4.UserSysId",$assignedToUserColumn);
		 $select->joinLeft(array('tb5' => "TB_Agency_User"), "tb5.UserSysId = tb2.CreatedByUserSysId",$b2bagencyColumn);
	    $AgencyUserSysID?$select->where('tbl.AssignToSysId = '.$AgencyUserSysID.' or tbl.CreatedBySysId = '.$AgencyUserSysID.''):'';
		$select->where('tbl.StartDateTime >= ?', $cuur_date);
		$select->where('tbl.StartDateTime <= ?', $cuur_dateEnd);
		$select->where("tbl.IsActive = ?", 1);
		$select->where("tbl.IsMarkForDel = ?", 0);
		$select->where("tb2.IsActive = ?", 1);
		$select->where("tb2.IsMarkForDelete = ?", 0);
		$select->order("tbl.StartDateTime DESC");
		$result = $this->_dbObj->fetchAll($select);
	    return $result; 
	}
	
	
	
}