| Server IP : 103.234.187.230 / Your IP : 216.73.216.216 Web Server : Apache System : Linux lserver42043-ind.megavelocity.net 3.10.0-1160.108.1.el7.x86_64 #1 SMP Thu Jan 25 16:17:31 UTC 2024 x86_64 User : apache ( 48) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/html/b2bzend/application/modules/dashboard/models/ |
Upload File : |
<?php
/* Zend Framework
* @category Zend
* @package Zend_Controller_Action
* @copyright Copyright (c) 2008-2014 Zend Technologies USA Inc. (http://www.zend.com)
* @license http://framework.zend.com/license/new-bsd New BSD License
* @version 1.0
* @author Ranvir singh <ranvir@catpl.co.in>
* Create Date 06-09-2016
* Update Date 06-09-2016
* ************************************************************* */
class Dashboard_Model_DashboardProcedure extends Zend_Db_Table_Abstract {
public $db;
public function __construct() {
$this->db = Zend_Db_Table::getDefaultAdapter();
}
public function __destruct() {
$this->db->closeConnection();
}
public function customerOpsQuery($AgencySysId, $pageNumber, $setItem, $lead_stage, $getData, $columnNameOrder, $orderDetails, $getRoleDetail, $AgentSysId, $ReportingToSysId) {
$this->lead_quality = '';
$this->lead_type = '';
$this->lead_stage = 0;
$this->customername = '';
$this->customermobile = '';
$this->customeremail = '';
$this->Destination = '';
$this->agencyUserFilter = 0;
$this->customerQueryId = 0;
$this->dateFrom = '';
$this->dateTo = '';
$this->agencyName = '';
$this->TravelFromDate = '';
$this->TravelToDate = '';
$this->lead_source = 0;
$this->supplierSysId = 0;
$this->plan_type = 0;
if (!empty($getData)) {
$this->lead_quality = isset($getData['lead_quality']) && $getData['lead_quality'] != "" ? $getData['lead_quality'] : '';
$this->lead_type = isset($getData['lead_type']) && $getData['lead_type'] != "" ? $getData['lead_type'] : '';
//$this->lead_stage = isset($getData['lead_stage']) && $getData['lead_stage'] != "" ? $getData['lead_stage'] : 0;
$this->lead_source = isset($getData['lead_source']) && $getData['lead_source'] != "" ? (int) $getData['lead_source'] : 0;
$this->customername = isset($getData['customername']) && $getData['customername'] != "" ? $getData['customername'] : '';
$this->customermobile = isset($getData['customermobile']) && $getData['customermobile'] != "" ? $getData['customermobile'] : '';
$this->customeremail = isset($getData['customeremail']) && $getData['customeremail'] != "" ? $getData['customeremail'] : '';
$this->Destination = isset($getData['CityName']) && $getData['CityName'] != "" ? $getData['CityName'] : '';
$this->agencyUserFilter = isset($getData['agencyUserFilter']) && $getData['agencyUserFilter'] != "" ? $getData['agencyUserFilter'] : 0;
$this->agencyOpsUserFilter = isset($getData['agencyOpsUserFilter']) && $getData['agencyOpsUserFilter'] != "" ? $getData['agencyOpsUserFilter'] : 0;
$this->plan_type = isset($getData['plan_type']) && $getData['plan_type'] != "" ? $getData['plan_type'] : 0;
$customerQueryId = isset($getData['customerQueryId']) ? trim($getData['customerQueryId']) : '';
if ($customerQueryId != "") {
$explodeQuery = explode("/", $customerQueryId);
if (count($explodeQuery) == 3) {
$this->customerQueryId = $explodeQuery[2];
} else if (count($explodeQuery) == 1) {
$this->customerQueryId = $explodeQuery[0];
} else {
$this->customerQueryId = intval($customerQueryId);
}
}
if (!empty($getData['dateFrom']) && !empty($getData['dateTo'] && $getData['dateTo'] != '__-__-____' && $getData['dateFrom'] != '__-__-____')) {
$dateFromEx = explode('-', $getData['dateFrom']);
$dateToEx = explode('-', $getData['dateTo']);
$dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
$dateTo = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
$dateTos = date('Y-m-d', strtotime($dateTo . ' +1 days'));
$this->dateFrom = $dateFrom;
$this->dateTo = $dateTos;
}
if (!empty($getData['TravelFromDate']) && !empty($getData['TravelToDate'] && $getData['TravelToDate'] != '__-__-____' && $getData['TravelFromDate'] != '__-__-____')) {
$dateFromEx = explode('-', $getData['TravelFromDate']);
$dateToEx = explode('-', $getData['TravelToDate']);
$dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
$dateTo = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
$dateTos = date('Y-m-d', strtotime($dateTo . ' +1 days'));
$this->TravelFromDate = $dateFrom;
$this->TravelToDate = $dateTos;
}
}
$this->lead_stage = 1;
if (isset($lead_stage) && $lead_stage == "completed") {
$this->lead_stage = 2;
}
// echo $this->lead_stage;die;
$this->agencyName = isset($getData['agencyName']) && $getData['agencyName'] != "" ? $getData['agencyName'] : '';
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$proQuery = "GET_All_OPS_QUERY '',$AgencySysId,$pageNumber,$setItem,'','$this->lead_type','$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate','$this->plan_type','$this->agencyOpsUserFilter'";
$statement = $this->db->query($proQuery);
} else {
//$AgentSysId = "$AgentSysId,$ReportingToSysId";
$AgentSysId = '';
$proQuery = "GET_All_OPS_QUERY '$AgentSysId',$AgencySysId,$pageNumber,$setItem,'','$this->lead_type','$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate','$this->plan_type','$this->agencyOpsUserFilter'";
$statement = $this->db->query($proQuery);
}
$resultSet1 = $statement->fetchAll();
$returnArray = array();
$returnArray['Query']['Result1'] = $resultSet1;
return $returnArray;
}
public function customerQuery($AgencySysId, $pageNumber, $setItem, $lead_stage, $getData, $columnNameOrder, $orderDetails, $getRoleDetail, $AgentSysId, $ReportingToSysId) {
$this->lead_quality = '';
$this->lead_type = 0;
$this->lead_stage = 0;
$this->customername = '';
$this->customermobile = '';
$this->customeremail = '';
$this->Destination = '';
$this->payment_stage = 0;
$this->booking_stage = 0;
$this->agencyUserFilter = 0;
$this->customerQueryId = 0;
$this->dateFrom = '';
$this->dateTo = '';
$this->agencyName = '';
$this->TravelFromDate = '';
$this->TravelToDate = '';
$this->lead_source = 0;
$this->supplierSysId = 0;
$this->plan_type = 0;
$this->IsOperationalLead = 0;
$this->customerLeadId = 0;
$this->MinPax = 0;
if (!empty($getData)) {
$this->lead_quality = isset($getData['lead_quality']) && $getData['lead_quality'] != "" ? $getData['lead_quality'] : '';
$this->lead_type = isset($getData['lead_type']) && $getData['lead_type'] != "" ? $getData['lead_type'] : 0;
$this->lead_stage = isset($getData['lead_stage']) && $getData['lead_stage'] != "" ? $getData['lead_stage'] : 0;
$this->lead_source = isset($getData['lead_source']) && $getData['lead_source'] != "" ? (int) $getData['lead_source'] : 0;
$this->customername = isset($getData['customername']) && $getData['customername'] != "" ? $getData['customername'] : '';
$this->customermobile = isset($getData['customermobile']) && $getData['customermobile'] != "" ? $getData['customermobile'] : '';
$this->customeremail = isset($getData['customeremail']) && $getData['customeremail'] != "" ? $getData['customeremail'] : '';
//$this->Destination = isset($getData['Destination']) && $getData['Destination'] != "" ? $getData['Destination'] : '';
$this->Destination = isset($getData['CityName']) && $getData['CityName'] != "" ? $getData['CityName'] : '';
$this->agencyUserFilter = isset($getData['agencyUserFilter']) && $getData['agencyUserFilter'] != "" ? $getData['agencyUserFilter'] : 0;
$this->IsOperationalLead = isset($getData['IsOperationalLead']) && $getData['IsOperationalLead'] != "" ? $getData['IsOperationalLead'] : 0;
$this->plan_type = isset($getData['plan_type']) && $getData['plan_type'] != "" ? $getData['plan_type'] : 0;
$this->payment_stage = isset($getData['payment_stage']) && $getData['payment_stage'] != "" ? $getData['payment_stage'] : 0;
$this->booking_stage = isset($getData['booking_stage']) && $getData['booking_stage'] != "" ? $getData['booking_stage'] : 0;
$this->customerLeadId = isset($getData['customerLeadId']) ? (int) ($getData['customerLeadId']) : 0;
$this->MinPax = isset($getData['MinPax']) ? (int) ($getData['MinPax']) : 0;
$customerQueryId = isset($getData['customerQueryId']) ? trim($getData['customerQueryId']) : '';
if ($customerQueryId != "") {
$explodeQuery = explode("/", $customerQueryId);
if (count($explodeQuery) == 3) {
$this->customerQueryId = $explodeQuery[2];
} else if (count($explodeQuery) == 1) {
$this->customerQueryId = $explodeQuery[0];
} else {
$this->customerQueryId = intval($customerQueryId);
}
}
if (!empty($getData['dateFrom']) && !empty($getData['dateTo'] && $getData['dateTo'] != '__-__-____' && $getData['dateFrom'] != '__-__-____')) {
$dateFromEx = explode('-', $getData['dateFrom']);
$dateToEx = explode('-', $getData['dateTo']);
$dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
$dateTo = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
$dateTos = date('Y-m-d', strtotime($dateTo . ' +1 days'));
$this->dateFrom = $dateFrom;
$this->dateTo = $dateTos;
}
if (!empty($getData['TravelFromDate']) && !empty($getData['TravelToDate'] && $getData['TravelToDate'] != '__-__-____' && $getData['TravelFromDate'] != '__-__-____')) {
$dateFromEx = explode('-', $getData['TravelFromDate']);
$dateToEx = explode('-', $getData['TravelToDate']);
$dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
$dateTo = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
$dateTos = date('Y-m-d', strtotime($dateTo . ' +1 days'));
$this->TravelFromDate = $dateFrom;
$this->TravelToDate = $dateTo;
}
}
// echo "<pre>";print_r($this->Destination);die;
$this->agencyName = isset($getData['agencyName']) && $getData['agencyName'] != "" ? $getData['agencyName'] : '';
$finalResultSet = array();
if ($lead_stage == 'overall') {
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$proQuery = "GET_Customer_Query_overall '',$AgencySysId,$pageNumber,$setItem,'',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate','$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage','$this->MinPax','$this->customerLeadId'";
$statement = $this->db->query($proQuery);
} else {
$AgentSysId = "$AgentSysId,$ReportingToSysId";
$statement = $this->db->query("GET_Customer_Query_overall '$AgentSysId',$AgencySysId,$pageNumber,$setItem,'',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate','$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage','$this->MinPax','$this->customerLeadId'");
}
} else if ($lead_stage == 'UnAssigned') {
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$statement = $this->db->query("GET_Customer_Query_unassigned '',$AgencySysId,$pageNumber,$setItem,'',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate','$this->plan_type','$this->IsOperationalLead'");
} else {
$statement = $this->db->query("GET_Customer_Query_unassigned '$AgentSysId',$AgencySysId,$pageNumber,$setItem,'',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead'");
}
} else {
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
if ($AgencySysId == 21) {
//"GET_Customer_Query_Optimized '',$AgencySysId,$pageNumber,$setItem,'$lead_stage',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage'";
$statement = $this->db->query("GET_Customer_Query '',$AgencySysId,$pageNumber,$setItem,'$lead_stage',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage','$this->MinPax','$this->customerLeadId' ");
} else {
//$statement = $this->db->query("GET_Customer_Query '',$AgencySysId,$pageNumber,$setItem,'$lead_stage',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage'");
$statement = $this->db->query("GET_Customer_Query '',$AgencySysId,$pageNumber,$setItem,'$lead_stage',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage','$this->MinPax','$this->customerLeadId'");
}
} else {
$AgentSysId = "$AgentSysId,$ReportingToSysId";
$statement = $this->db->query("GET_Customer_Query '$AgentSysId',$AgencySysId,$pageNumber,$setItem,'$lead_stage',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->dateFrom','$this->dateTo','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->TravelFromDate','$this->TravelToDate', '$this->plan_type','$this->IsOperationalLead','$this->payment_stage','$this->booking_stage','$this->MinPax','$this->customerLeadId'");
}
}
// echo "<pre>";print_r($statement);die;
//echo $lead_stage;die;
$resultSet1 = $statement->fetchAll();
// $finalResultSet['Result1'] = array();
// $tPSysIdArray = array();
// $r = 0;
// foreach ($resultSet1 as $row) {
// $tPSysIdArray[$r] = $row['TPSysId'];
// $finalResultSet['Result1'][] = $row;
// $r++;
// }
// $finalResultSetProposal = array();
// if (!empty($tPSysIdArray)) {
// $rowMasterTpSysId = implode(',', $tPSysIdArray);
// if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
// $statementProposal = $this->db->query("GET_CUSTOMER_QUERY_PROPOSAL '',$AgencySysId,'$rowMasterTpSysId'");
// } else {
// $AgentSysId = "$AgentSysId,$ReportingToSysId";
// $statementProposal = $this->db->query("GET_CUSTOMER_QUERY_PROPOSAL '$AgentSysId',$AgencySysId,'$rowMasterTpSysId'");
// }
// $resultSetProposal = $statementProposal->fetchAll();
// $finalResultSetProposal['Result1'] = array();
// foreach ($resultSetProposal as $rowProposal) {
// $finalResultSetProposal['Result1'][] = $rowProposal;
// $r++;
// }
// }
$returnArray = array();
$returnArray['Query']['Result1'] = $resultSet1;
// $returnArray['Proposal'] = $finalResultSetProposal;
//echo "<pre>";print_r($returnArray);exit;
return $returnArray;
}
public function getb2bQuery($AgencySysId, $B2BAgencySysId, $pageNumber, $setItem, $lead_stage, $getData, $columnNameOrder, $orderDetails, $getRoleDetail, $AgentSysId, $ReportingToSysId, $IsProposal) {
$this->lead_quality = '';
$this->lead_type = 0;
$this->lead_stage = 0;
$this->lead_state = 0;
$this->customername = '';
$this->customermobile = '';
$this->customeremail = '';
$this->Destination = '';
$this->agencyUserFilter = 0;
$this->customerQueryId = 0;
$this->FromTravelDate = '';
$this->ToTravelDate = '';
$this->FromCreateDate = '';
$this->ToCreateDate = '';
$this->agencyName = '';
$this->lead_source = 0;
$this->supplierSysId = 0;
$this->agent_remark = '';
$this->QueryId = 0;
if (!empty($getData)) {
$this->lead_quality = isset($getData['lead_quality']) && $getData['lead_quality'] != "" ? $getData['lead_quality'] : '';
$this->lead_type = isset($getData['lead_type']) && $getData['lead_type'] != "" ? $getData['lead_type'] : 0;
$this->lead_source = isset($getData['lead_source']) && $getData['lead_source'] != "" ? (int) $getData['lead_source'] : 0;
$this->customername = isset($getData['customername']) && $getData['customername'] != "" ? $getData['customername'] : '';
$this->customermobile = isset($getData['customermobile']) && $getData['customermobile'] != "" ? $getData['customermobile'] : '';
$this->customeremail = isset($getData['customeremail']) && $getData['customeremail'] != "" ? $getData['customeremail'] : '';
$this->Destination = isset($getData['Destination']) && $getData['Destination'] != "" ? $getData['Destination'] : '';
$this->agent_remark = isset($getData['Remark']) && $getData['Remark'] != "" ? $getData['Remark'] : '';
$this->QueryId = isset($getData['QueryId']) && !empty($getData['QueryId']) ? (int) $getData['QueryId'] : 0;
$this->agencyUserFilter = isset($getData['agencyUserFilter']) && $getData['agencyUserFilter'] != "" ? $getData['agencyUserFilter'] : 0;
if (!empty($getData['FromTravelDate']) && !empty($getData['ToTravelDate'])) {
$this->FromTravelDate = $getData['FromTravelDate'];
$this->ToTravelDate = $getData['ToTravelDate'];
}
if (!empty($getData['FromCreateDate']) && !empty($getData['ToCreateDate'])) {
$this->FromCreateDate = $getData['FromCreateDate'];
$this->ToCreateDate = $getData['ToCreateDate'];
}
}
$this->agencyName = isset($getData['agencyName']) && $getData['agencyName'] != "" ? $getData['agencyName'] : '';
$finalResultSet = array();
if ($IsProposal == 1) {
if (isset($getData['IsDiscard']) && $getData['IsDiscard'] == 1) {
$this->lead_state = "4,70,104,120";
} else if (isset($getData['IsConfirmed']) && $getData['IsConfirmed'] == 1) {
$this->lead_state = "17, 10, 9, 18, 19, 13, 7, 102, 123";
} else {
$this->lead_state = "4,70,104,120";
}
if ($lead_stage == 'overall') {
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$proQuery = "GET_B2B_PROPOSAL_OVERALL '',$AgencySysId,$B2BAgencySysId,$pageNumber,$setItem,'$this->lead_state',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->FromCreateDate','$this->ToCreateDate','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->agent_remark',$this->QueryId,'$this->FromTravelDate','$this->ToTravelDate'";
$statement = $this->db->query($proQuery);
} else {
$AgentSysId = "$AgentSysId,$ReportingToSysId";
$statement = $this->db->query("GET_B2B_PROPOSAL_OVERALL '$AgentSysId',$AgencySysId,$B2BAgencySysId,$pageNumber,$setItem,'$this->lead_state',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->FromCreateDate','$this->ToCreateDate','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->agent_remark',$this->QueryId,'$this->FromTravelDate','$this->ToTravelDate'");
}
}
} else {
if (isset($getData['IsDiscard']) && $getData['IsDiscard'] == 1) {
$this->lead_state = "25, 28, 30, 31, 32, 33";
} else if (isset($getData['IsConfirmed']) && $getData['IsConfirmed'] == 1) {
$this->lead_state = "29, 21, 22, 23, 24, 38, 37, 34, 35, 122, 143";
} else {
$this->lead_state = "1, 2, 58, 36, 20,3,86,26,83,84,62,69,29, 21, 22, 23, 24, 38, 37, 34, 35, 122,125";
}
if ($lead_stage == 'overall') {
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$proQuery = "GET_B2B_Query_overall '',$AgencySysId,$B2BAgencySysId,$pageNumber,$setItem,'$this->lead_state',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->FromCreateDate','$this->ToCreateDate','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->agent_remark',$this->QueryId,'$this->FromTravelDate','$this->ToTravelDate'";
$statement = $this->db->query($proQuery);
} else {
$AgentSysId = "$AgentSysId,$ReportingToSysId";
$statement = $this->db->query("GET_B2B_Query_overall '$AgentSysId',$AgencySysId,$B2BAgencySysId,$pageNumber,$setItem,'$this->lead_state',$this->lead_type,'$this->lead_quality',$this->lead_stage,'$this->customername','$this->customermobile','$this->customeremail','$this->Destination','$this->agencyUserFilter',$this->customerQueryId,'$this->FromCreateDate','$this->ToCreateDate','$this->agencyName','$columnNameOrder','$orderDetails',$this->lead_source,'$this->agent_remark',$this->QueryId,'$this->FromTravelDate','$this->ToTravelDate'");
}
}
}
// echo "<pre>";print_r($statement);die;
$resultSet1 = $statement->fetchAll();
$returnArray = array();
$returnArray['Query']['Result1'] = $resultSet1;
return $returnArray;
}
public function customerLead($AgencySysId, $pageNumber, $setItem, $getData, $AgentSysId, $ReportingToSysId, $getRoleDetail, $orderBy, $columnNameOrder) {
$this->lead_stage = 0;
$this->AgentSourceIds = 0;
$this->lead_type = 0;
$this->customername = '';
$this->customermobile = '';
$this->customeremail = '';
$this->agencyUserFilter = 0;
$this->dateFrom = '';
$this->dateTo = '';
$this->assigUnassignFilter = 0;
// echo "<pre>";print_r($getData);die;
if (!empty($getData)) {
$this->lead_stage = isset($getData['lead_stage']) && $getData['lead_stage'] != "" ? $getData['lead_stage'] : 0;
$this->AgentSourceIds = isset($getData['AgentSourceIds']) && $getData['AgentSourceIds'] != "" ? $getData['AgentSourceIds'] : 0;
$this->lead_type = isset($getData['lead_type']) && $getData['lead_type'] != "" ? $getData['lead_type'] : 0;
$this->customername = isset($getData['customername']) && $getData['customername'] != "" ? $getData['customername'] : '';
$this->customermobile = isset($getData['customermobile']) && $getData['customermobile'] != "" ? $getData['customermobile'] : '';
$this->customeremail = isset($getData['customeremail']) && $getData['customeremail'] != "" ? $getData['customeremail'] : '';
$this->agencyUserFilter = isset($getData['agencyUserFilter']) && $getData['agencyUserFilter'] != "" ? $getData['agencyUserFilter'] : 0;
$this->assigUnassignFilter = (isset($getData['assigUnassignFilter']) && $getData['assigUnassignFilter'] == 2) ? $getData['assigUnassignFilter'] : 0;
if (!empty($getData['dateFrom']) && !empty($getData['dateTo'] && $getData['dateTo'] != '__-__-____' && $getData['dateFrom'] != '__-__-____')) {
$dateFromEx = explode('-', $getData['dateFrom']);
$dateToEx = explode('-', $getData['dateTo']);
$dateFrom = $dateFromEx[2] . '-' . $dateFromEx[1] . '-' . $dateFromEx[0];
$dateTo = $dateToEx[2] . '-' . $dateToEx[1] . '-' . $dateToEx[0];
$dateTos = date('Y-m-d', strtotime($dateTo . ' +1 days'));
$this->dateFrom = $dateFrom;
$this->dateTo = $dateTos;
}
}
if (($getRoleDetail['IsAdmin'] == True) || $getRoleDetail['IsSuperAdmin'] == True) {
$statement = $this->db->query("GET_CUSTOMER_LEAD '',$AgencySysId,$pageNumber,$setItem,'$this->lead_stage',$this->lead_type,'$this->customername','$this->customermobile','$this->customeremail','$this->agencyUserFilter','$this->dateFrom','$this->dateTo',$this->assigUnassignFilter,'$orderBy','$columnNameOrder'");
} else {
// $managerList = array()
$passAgentSysId = "$AgentSysId,$ReportingToSysId";
$statement = $this->db->query("GET_CUSTOMER_LEAD '$passAgentSysId',$AgencySysId,$pageNumber,$setItem,'$this->lead_stage',$this->lead_type,'$this->customername','$this->customermobile','$this->customeremail','$this->agencyUserFilter','$this->dateFrom','$this->dateTo',$this->AgentSourceIds',$this->assigUnassignFilter,'$orderBy','$columnNameOrder'");
}
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
// echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getLeadData($AgencySysId, $FilterType, $UserType, $AgentSysId) {
switch ($FilterType) {
case 'current-month': {
$currentDate = date("Y-m-d");
$currentMonth = date("m");
$currentYear = date("Y");
$fromDate = "$currentYear-$currentMonth-01";
break;
}
case 'current-week': {
$currentDate = date("Y-m-d");
if (date('D') != 'Mon') {
$fromDate = date('Y-m-d', strtotime('last Monday'));
} else {
$fromDate = date('Y-m-d');
}
break;
}
case 'today': {
$currentDate = date("Y-m-d");
$fromDate = date("Y-m-d");
break;
}
default: {
$currentDate = date("Y-m-d");
$fromDate = date("Y-m-d");
break;
}
}
if ($UserType == TRUE) {
$AgentSysId = '';
}
$proQuery = "GET_NEWDASHBOARD_LEADDATA $AgencySysId,'$currentDate','$AgentSysId','$fromDate'";
$statement = $this->db->query($proQuery);
$resultSetProposal = $statement->fetchAll();
$NewDataArray = array();
foreach ($resultSetProposal as $finalData) {
$NewDataArray[$finalData['LeadSourceSysId']]['Title'] = $finalData['Title'];
$NewDataArray[$finalData['LeadSourceSysId']]['TotalLead'] += $finalData['TotalLead'];
if ($finalData['IsMarkForDelete'] == '1') {
$NewDataArray[$finalData['LeadSourceSysId']]['TotalJunkLead'] += $finalData['TotalLead'];
} else {
if ($finalData['LeadStageSysId'] == 50) {
$NewDataArray[$finalData['LeadSourceSysId']]['QueryConverted'] += $finalData['TotalLead'];
} else if (($finalData['LeadStageSysId'] == 53 || $finalData['LeadStageSysId'] == 48) || $finalData['LeadStageSysId'] == 52) {
$NewDataArray[$finalData['LeadSourceSysId']]['TotalInProcess'] += $finalData['TotalLead'];
} else {
$NewDataArray[$finalData['LeadSourceSysId']]['TotalJunkLead'] += $finalData['TotalLead'];
}
}
}
return $NewDataArray;
}
public function getQueryData($AgencySysId, $FilterType, $UserType, $AgentSysId) {
switch ($FilterType) {
case 'current-month': {
$currentDate = date("Y-m-d");
$currentMonth = date("m");
$currentYear = date("Y");
$fromDate = "$currentYear-$currentMonth-01";
break;
}
case 'last-month': {
$fromDate = date('Y-m-d', strtotime('first day of last month'));
$currentDate = date('Y-m-d', strtotime('last day of last month'));
break;
}
default: {
$currentDate = date("Y-m-d");
$currentMonth = date("m");
$currentYear = date("Y");
$fromDate = "$currentYear-$currentMonth-01";
break;
}
}
if ($UserType == TRUE) {
$AgentSysId = '';
}
$proQuery = "GET_NEWDASHBOARD_QUERYDATA $AgencySysId,'$currentDate','$AgentSysId','$fromDate'";
$statement = $this->db->query($proQuery);
$resultSetProposal = $statement->fetchAll();
// echo "<pre>";print_r($resultSetProposal);exit;
$planTypeArray = unserialize(GTXPLAN_TYPE);
$NewDataArray = array();
$wonStatus = array(29, 21, 22, 23, 24, 38, 37, 34, 35, 122, 143);
$followUpStageArray = array(1, 2, 58, 36, 20, 86, 26, 83, 84, 62, 69, 125);
$failed = array(25, 28, 30, 31, 32, 33, 78, 63, 76, 77, 127);
foreach ($resultSetProposal as $finalData) {
$NewDataArray[$finalData['PlanType']]['Title'] = $planTypeArray[$finalData['PlanType']];
$NewDataArray[$finalData['PlanType']]['TotalQuery'] += $finalData['TotalLead'];
if ($finalData['IsMarkForDel'] == '1') {
$NewDataArray[$finalData['PlanType']]['LostQuery'] += $finalData['TotalLead'];
} else {
if (in_array($finalData['StatusType'], $failed)) {
$NewDataArray[$finalData['PlanType']]['Lost'] += $finalData['TotalLead'];
} else if (in_array($finalData['StatusType'], $wonStatus)) {
$NewDataArray[$finalData['PlanType']]['WonQuery'] += $finalData['TotalLead'];
} else if (in_array($finalData['StatusType'], $followUpStageArray)) {
$NewDataArray[$finalData['PlanType']]['WIP'] += $finalData['TotalLead'];
if ($finalData['LeadQuality'] == 1) {
$NewDataArray[$finalData['PlanType']]['HotQuery'] += $finalData['TotalLead'];
}
}
}
}
return $NewDataArray;
}
public function GetAgencySubscriptionReport($month, $year) {
$proQuery = "GetAgencySubscriptionReport '$month', '$year'";
$statement = $this->db->query($proQuery);
$resultSetProposal = $statement->fetchAll();
return $resultSetProposal;
}
public function getAllAgentNotCompletedToDoForDashbord($limit = NULL, $AgencyUserSysID = NULL, $AgencySysId = NULL, $CustomerSysId = NULL, $IsMarkForDel = NULL, $Order = NULL){
$cuur_date = date('Y-m-d 00:00:00');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_ToDo"), array('StartDateTime', 'EndDateTime', 'ToDoSysId', 'CustSysId', 'Subject'));
$b2bagencyColumn=array('Title as CompanyName');
$select->joinLeft(array('tb2' => "TB_Agency_Customer"), "tbl.CustSysId = tb2.CustomerSysId", array('IsB2BAgent','EmailId','FirstName', 'LastName', 'countrycode', 'Contacts'));
$select->joinLeft(array('tb3' => "TB_Agency"), "tb3.AgencySysId = tb2.AgencySysId",$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->db->fetchAll($select);
return $result;
}
}