<?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_AgencyUserInbox
{
	protected $_dbObj;
	protected $_agencyuserMsgObj;

	public function __construct()
	{
		$this->_agencyuserMsgObj = new Travel_Model_TblAgencyUserInbox();
		$this->_dbObj = Zend_Db_Table::getDefaultAdapter();
	}
	//query to insert remark data in db created by amit kumar dubey on 16 august 2016 at 1:09 PM
	public function addNotification(array $data)
	{
		$this->_agencyuserMsgObj->insert($data);
		$lastID = $this->_dbObj->lastInsertId();
		return $lastID;
	}
	public function updateNotification(array $data, $where = NULL)
	{
		$this->_agencyuserMsgObj->update($data, $where);
	}
	public function GetAgentMsgNotification($intLoggedinUserId, $AgencySysId = NULL)
	{
		$notificationColumn = array('tb1.RefSysId', 'max(tb1.CreatedDate) as CreatedDate', 'max(tb1.MsgSysId) as MsgSysId', 'max(tb1.SenderSysId) as SenderSysId');
		$travelPlanColumn = array('max(tb2.MasterTPSysId) as MasterTPSysId');
		$customerColumn = array('max(FirstName) as FirstName');
		$select = $this->_dbObj->select();
		$select->from(array('tb1' => "TB_Agency_User_Inbox"), $notificationColumn);
		$select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb1.RefSysId = tb2.TPSysId", $travelPlanColumn);
		$select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb2.CustomerSysId = tb3.CustomerSysId", $customerColumn);
		$select->where("tb1.ModuleType = ?", 1);
		$select->where("tb1.MsgType = ?", 1);
		$select->where("tb2.isPlanViewed = ?", 0);
		if ($AgencySysId != '') {
			$select->where("tb2.AgencySysId = ?", $AgencySysId);
			$select->where("tb1.AgencySysId = ?", $AgencySysId);
		} else {
			$select->where("tb2.AgentSysId = ?", $intLoggedinUserId);
			$select->where("tb1.UserSysId = ?", $intLoggedinUserId);
		}
		$select->order("CreatedDate DESC");
		$select->group("tb1.RefSysId");

		$result = $this->_dbObj->fetchAll($select);
		return $result;
	}
	public function CheckCustomerMsgCount($customerId, $leadId, $AgentSysId = NULL)
	{
		$select = $this->_dbObj->select();
		$messagearray = array('MsgSysId', 'IsAgent', 'Message', 'CreatedDate', 'UpdateDate');
		$tb2array = array('FirstName', 'LastName');
		$select->from(array('tb1' => "TB_Agency_User_Inbox"), $messagearray);
		//$select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tb2.CRMCustSysId = tb1.SenderSysId" , $tb2array);
		$select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tb1.SenderSysId", $tb2array);
		$select->where('tb1.SenderSysId = ?', $customerId);
		$select->where('tb1.UserSysId!=0');
		$select->where('tb1.RefSysId = ?', $leadId);
		$select->where("tb1.IsAgent =?", 0);
		$select->where("tb2.IsActive =?", 1);
		$select->where("tb2.IsMarkForDelete =?", 0);
		$select->where("tb1.IsMarkForDel =?", 0);
		$select->where("tb1.IsActive =?", 1);
		$select->order("tb1.UpdateDate DESC");
		// echo $select;
		$result = $this->_dbObj->fetchRow($select);
		if (is_array($result) && count($result) == 0) {
			$select = $this->_dbObj->select();
			$messagearray = array('MsgSysId', 'IsAgent', 'Message', 'CreatedDate', 'UpdateDate');
			$tb2array = array('FirstName', 'LastName');
			$select->from(array('tb1' => "TB_Agency_User_Inbox"), $messagearray);
			//$select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tb2.CRMCustSysId = tb1.SenderSysId" , $tb2array);
			$select->joinLeft(array('tb2' => "TB_Agency_User"), "tb2.UserSysId = tb1.SenderSysId", $tb2array);
			$AgentSysId ? $select->where('tb1.SenderSysId = ?', $AgentSysId) : '';
			$select->where('tb1.RefSysId = ?', $leadId);
			$select->where("tb1.IsAgent =?", 1);
			$select->where("tb2.IsActive =?", 1);
			$select->where("tb2.IsMarkForDel =?", 0);
			$select->where("tb1.IsMarkForDel =?", 0);
			$select->where("tb1.IsActive =?", 1);
			$select->order("tb1.UpdateDate DESC");
			//echo $select;
			$result = $this->_dbObj->fetchRow($select);
		}
		return $result;
	}
	public function GetCustChatHistDetWithLead($leadId, $CRMCustSysId, $order = 'DESC')
	{
		$select = $this->_dbObj->select();
		$messagearray = array('MsgSysId', 'IsAgent', 'Message', 'CreatedDate', 'UpdateDate');
		$tb2array = array('FirstName', 'LastName');
		$tb5array = array('FirstName as AgentFirstName', 'LastName as AgentLastName');
		$select->from(array('tb1' => "TB_Agency_User_Inbox"), $messagearray);
		/*	   $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tb2.CRMCustSysId = tb1.SenderSysId" , $tb2array);
	   $select->joinLeft(array('tb3' => "TB_CRM_CustomerAccount"), "tb3.CRMCustSysId = tb1.UserSysId" , '');
*/
		$select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tb1.SenderSysId", $tb2array);
		$select->joinLeft(array('tb3' => "TB_Agency_Customer"), "tb3.CustomerSysId = tb1.UserSysId", '');
		$select->joinLeft(array('tb4' => "TB_Agency_User"), "tb4.UserSysId = tb1.UserSysId", '');
		$select->joinLeft(array('tb5' => "TB_Agency_User"), "tb5.UserSysId = tb1.SenderSysId", $tb5array);
		$select->where('tb1.RefSysId = ?', $leadId);
		$select->where('tb1.UserSysId= ' . $CRMCustSysId . ' or tb1.SenderSysId= ' . $CRMCustSysId . '');
		$select->order("tb1.MsgSysId " . $order . "");
		// $select->where("tb2.IsActive =?",1);
		//$select->where("tb2.IsMarkForDelete =?",0);
		$select->where("tb1.IsMarkForDel =?", 0);
		$select->where("tb1.IsActive =?", 1);
		// echo $select;
		$result = $this->_dbObj->fetchAll($select);
		return $result;
	}
	//fetch all notification when user assigned created by Er Amit Kumar Dubey on 4 april 2017 at 12:25 PM
	public function GetAssignUserMsgNotification($intLoggedinUserId, $AgencySysId)
	{
		$notificationColumn = array('RefSysId', 'CreatedDate', 'MsgSysId', 'UserSysId', 'SenderSysId', 'MsgType');
		$select = $this->_dbObj->select();
		$select->from(array('tb1' => "TB_Agency_User_Inbox"), $notificationColumn);
		$select->where("tb1.ModuleType = ?", 1);
		$select->where("tb1.MsgType = ?", 3);
		$select->where("tb1.Accepted = ?", 0);
		$select->where("tb1.IsMarkForDel = ?", 0);
		$select->where("tb1.IsActive = ?", 1);
		$select->where("tb1.UserSysId = ?", $intLoggedinUserId);
		$select->where("tb1.AgencySysId = ?", $AgencySysId);
		$select->order("tb1.UpdateDate DESC");
		$result = $this->_dbObj->fetchAll($select);
		return $result;
	}
}