| 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/report/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 Report_Model_Report extends Zend_Db_Table_Abstract {
function __construct() {
$this->db = Zend_Db_Table::getDefaultAdapter();
}
public function __destruct() {
$this->db->closeConnection();
}
public function getdebtorsReport($AgencySysId, $startPage = 1, $endPage = 25, $getData = array()) {
$this->customername = '';
$this->customermobile = '';
$this->customeremail = '';
if (!empty($getData)) {
$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'] : '';
}
$statement = $this->db->query("GET_Debtors_Report $AgencySysId,$startPage,$endPage,'$this->customername','$this->customeremail','$this->customermobile'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
return $finalResultSet;
}
public function getAllProposalDetailForsuccess($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_QUERY_SUCCESS_REPORT_COUNT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
return $finalResultSet;
}
public function getAllProposalDetailForCampainSuccess($AgencySysId, $leadIds) {
if (!empty($leadIds)) {
$leadIds1 = implode(', ', $leadIds);
// echo "GET_QUERY_SUCCESS_REPORT_COUNT $AgencySysId,'$leadIds1'"; exit;
$statement = $this->db->query("GET_QUERY_SUCCESS_REPORT_COUNT_FOR_SUCCESS $AgencySysId,'$leadIds1'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
return $finalResultSet;
}
}
public function getAllData($AgencySysId, $currentDate) {
$statement = $this->db->query("GET_LEAD_QUERY_STATICS_DATA $AgencySysId,'$currentDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet3 = $statement->fetchAll();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
return $finalResultSet;
}
public function getAllEmpReoprtData($AgencySysId, $currentDate, $ReportingToSysId = NULL) {
$statement = $this->db->query("GET_LEAD_QUERY_REPORT_DATA $AgencySysId,'$currentDate','$ReportingToSysId'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
$statement->nextRowSet(); // Advance to the fourth result set
$resultSet4 = $statement->fetchAll();
foreach ($resultSet4 as $row) {
$finalResultSet['Result4'][] = $row;
}
$statement->nextRowSet(); // Advance to the fourth result set
$resultSet5 = $statement->fetchAll();
foreach ($resultSet5 as $row) {
$finalResultSet['Result5'][] = $row;
}
return $finalResultSet;
}
public function getLeadSourceWiseReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_LEAD_SOURCE_WISE_REPORT_COUNT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
return $finalResultSet;
}
public function getDCReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_DCR_REPORT_DATA $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet();
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet();
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result3'] = array();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
$statement->nextRowSet();
$resultSet4 = $statement->fetchAll();
$finalResultSet['Result4'] = array();
foreach ($resultSet4 as $row) {
$finalResultSet['Result4'][] = $row;
}
$statement->nextRowSet();
$resultSet5 = $statement->fetchAll();
$finalResultSet['Result5'] = array();
foreach ($resultSet5 as $row) {
$finalResultSet['Result5'][] = $row;
}
$statement->nextRowSet();
$resultSet6 = $statement->fetchAll();
$finalResultSet['Result6'] = array();
foreach ($resultSet6 as $row) {
$finalResultSet['Result6'][] = $row;
}
$statement->nextRowSet();
$resultSet7 = $statement->fetchAll();
$finalResultSet['Result7'] = array();
foreach ($resultSet7 as $row) {
$finalResultSet['Result7'][] = $row;
}
return $finalResultSet;
}
public function getLeadStatusReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_LEAD_STATUS_REPORT_COUNT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result3'] = array();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
//echo "<pre>"; print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getCampaignSucceessReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$queryProcedure = "GET_CAMPAIGN_SUCCESS_REPORT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'";
$statement = $this->db->query($queryProcedure);
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
return $finalResultSet;
}
public function getTcsProvisionalReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_TCS_PROVISIONAL_REPORT_DATA $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
// echo "<pre>";print_r($finalResultSet);
return $finalResultSet;
}
public function getGSTCollectionReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_GST_COLLECTION_REPORT_DATA $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
// echo "<pre>";print_r($finalResultSet);
return $finalResultSet;
}
public function getTcsCollectionReport($pageNumber, $AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_TCS_COLLECTION_REPORT_DATA $pageNumber, $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
//echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getQueryStatusReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_QUERY_STATUS_REPORT_COUNT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result3'] = array();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet4 = $statement->fetchAll();
$finalResultSet['Result4'] = array();
foreach ($resultSet4 as $row) {
$finalResultSet['Result4'][] = $row;
}
// echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getCheckoutReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_QUERY_Checkout_REPORT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'][0] = array();
foreach ($resultSet1 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][0]['PlanType'] = 5;
$finalResultSet['Result1'][0]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][0]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result1'][1] = array();
foreach ($resultSet2 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][1]['PlanType'] = 1;
$finalResultSet['Result1'][1]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][1]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result1'][2] = array();
foreach ($resultSet3 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][2]['PlanType'] = 6;
$finalResultSet['Result1'][2]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][2]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet4 = $statement->fetchAll();
$finalResultSet['Result1'][3] = array();
foreach ($resultSet4 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][3]['PlanType'] = 2;
$finalResultSet['Result1'][3]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][3]);
}
}
//echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getArrivalReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_QUERY_ARRIVAL_REPORT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'][0] = array();
foreach ($resultSet1 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][0]['PlanType'] = 5;
$finalResultSet['Result1'][0]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][0]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result1'][1] = array();
foreach ($resultSet2 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][1]['PlanType'] = 1;
$finalResultSet['Result1'][1]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][1]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result1'][2] = array();
foreach ($resultSet3 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][2]['PlanType'] = 6;
$finalResultSet['Result1'][2]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][2]);
}
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet4 = $statement->fetchAll();
$finalResultSet['Result1'][3] = array();
foreach ($resultSet4 as $row) {
if ($row['TotalQuery'] > 0) {
$finalResultSet['Result1'][3]['PlanType'] = 2;
$finalResultSet['Result1'][3]['TotalQuery'] = $row['TotalQuery'];
} else {
unset($finalResultSet['Result1'][3]);
}
}
//echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getQueryStatusValueWiseReport($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_QUERY_STATUS_VALUE_WISE $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result3'] = array();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
// echo "<pre>";print_r($finalResultSet);exit;
return $finalResultSet;
}
public function getQueryStatusReportForDestination($AgencySysId, $currentDate, $ReportingToSysId = NULL, $fromDate) {
$statement = $this->db->query("GET_DESTINATION_WISE_REPORT_COUNT $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet();
// Advance to the second result set
return $finalResultSet;
}
public function getAllCustomerData($AgencySysId, $queryMonthParam, $birthdayMonthParam, $marriageAMonthParam, $getPost) {
$this->queryMonthParam = '';
$this->birthdayMonthParam = '';
$this->marriageAMonthParam = '';
$this->TravelledwithUs = '';
$this->TravelledwithUsNO = '';
$this->TravellerType = '';
$this->PassportExpiresinMonth = '';
$this->DestinationPlacesSysId = '';
$this->ageGroupParamFrom = '';
$this->ageGroupParamTo = '';
if ($queryMonthParam != '') {
$this->queryMonthParam = $queryMonthParam;
}
if ($birthdayMonthParam != '') {
$this->birthdayMonthParam = $birthdayMonthParam;
}
if ($marriageAMonthParam != '') {
$this->marriageAMonthParam = $marriageAMonthParam;
}
if ($getPost != '') {
if ($getPost['TravelledYes'] == 1) {
$this->TravelledwithUs = "17";
}
if ($getPost['TravelledNo'] == 1) {
$this->TravelledwithUsNO = "17";
}
if (!empty($getPost['TravellerType'])) {
$travellerTypeArra = $getPost['TravellerType'];
$Farr = array_diff($travellerTypeArra, array('0'));
if (!empty($Farr)) {
$this->TravellerType = implode(',', $Farr);
}
}
if ($getPost['AnniversaryBirth'] == 1 || $getPost['AnniversaryMarriage'] == 1) {
if ($getPost['AnniversaryBirth'] == 1) {
$getAnniversaryDate = $getPost['AnniversaryDate'];
$FarrB = array_diff($getAnniversaryDate, array('0'));
if (!empty($FarrB)) {
$this->birthdayMonthParam = implode(',', $FarrB);
}
}
if ($getPost['AnniversaryMarriage'] == 1) {
$getAnniversaryDate = $getPost['AnniversaryDate'];
$FarrAB = array_diff($getAnniversaryDate, array('0'));
if (!empty($FarrAB)) {
$this->marriageAMonthParam = implode(',', $FarrAB);
}
}
}
if (!empty($getPost['QueryDate'])) {
$travellerTypeArra = $getPost['QueryDate'];
$FarrQ = array_diff($travellerTypeArra, array('0'));
if (!empty($FarrQ)) {
$this->queryMonthParam = implode(',', $FarrQ);
}
}
if (!empty($getPost['QueryDate'])) {
$travellerTypeArra = $getPost['QueryDate'];
$FarrQ = array_diff($travellerTypeArra, array('0'));
if (!empty($FarrQ)) {
$this->queryMonthParam = implode(',', $FarrQ);
}
}
if (!empty($getPost['AgeGroup'])) {
$AgeGroupArray = $getPost['AgeGroup'];
$FAgeGroup = array_diff($AgeGroupArray, array('0'));
if (!empty($FAgeGroup)) {
$AgeGroupFrom = array();
$AgeGroupTo = array();
foreach ($FAgeGroup as $valuesSet) {
$arrAgeGroup = explode("-", $valuesSet);
$AgeGroupFrom1 = trim($arrAgeGroup[0]);
$timeFrom = strtotime("-" . $AgeGroupFrom1 . ' years');
$AgeGroupFrom[] = date('Y', $timeFrom);
$AgeGroupTo1 = trim($arrAgeGroup[1]);
$timeTo = strtotime("-" . $AgeGroupTo1 . ' years');
$AgeGroupTo[] = date('Y', $timeTo);
}
$this->ageGroupParamFrom = $AgeGroupFrom[0];
$this->ageGroupParamTo = $AgeGroupTo[0];
}
}
if (!empty($getPost['PassportExpiresinMonth'])) {
$travellerTypeArra = $getPost['PassportExpiresinMonth'];
$FarrPE = array_diff($travellerTypeArra, array('0'));
if (!empty($FarrPE)) {
$this->PassportExpiresinMonth = implode(',', $FarrPE);
}
}
if ($getPost['otherlocationcity'] != "") {
$otherlocationcity = $getPost['otherlocationcity'];
$this->DestinationPlacesSysId = $otherlocationcity;
}
}
$statement = $this->db->query("GET_CUSTOMER_SEGMENTATION_DATA $AgencySysId,'$this->queryMonthParam','$this->birthdayMonthParam','$this->marriageAMonthParam','$this->TravelledwithUs','$this->TravelledwithUsNO','$this->TravellerType','$this->PassportExpiresinMonth','$this->DestinationPlacesSysId','$this->ageGroupParamFrom','$this->ageGroupParamTo'");
$finalResultSet = array();
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
return $finalResultSet;
}
function sanitize_data_Email($input_data) {
$searchArr = array("#", "<", ">", "\'");
$input_data = str_replace($searchArr, "", $input_data);
return htmlentities(stripslashes($input_data), ENT_QUOTES);
}
public function getCustomerDetailByCustSysId($CustSysId, $AgencySysId) {
$customerDetails = array('CustomerSysId', 'EmailId', 'FirstName', 'LastName');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Customer"), $customerDetails);
$select->where('tb1.CustomerSysId = ?', $CustSysId);
$select->where('tb1.AgencySysId = ?', $AgencySysId);
$result = $this->db->fetchRow($select);
return $result;
}
// public function copyDataFormCustomerToListData($AgencySysId, $queryMonthParam, $birthdayMonthParam, $marriageAMonthParam, $getPost,$ListSysId) {
// $this->queryMonthParam = '';
// $this->birthdayMonthParam = '';
// $this->marriageAMonthParam = '';
// $this->TravelledwithUs = '';
// $this->TravelledwithUsNO = '';
// $this->TravellerType = '';
// $this->PassportExpiresinMonth = '';
// $this->DestinationPlacesSysId = '';
// $this->ageGroupParamFrom = '';
// $this->ageGroupParamTo = '';
// if ($queryMonthParam != '') {
// $this->queryMonthParam = $queryMonthParam;
// }
// if ($birthdayMonthParam != '') {
// $this->birthdayMonthParam = $birthdayMonthParam;
// }
// if ($marriageAMonthParam != '') {
// $this->marriageAMonthParam = $marriageAMonthParam;
// }
// echo "<pre>";print_r($getPost);exit;
// if ($getPost != '') {
// if ($getPost['TravelledYes'] == 1) {
// $this->TravelledwithUs = "17";
// }
// if ($getPost['TravelledNo'] == 1) {
// $this->TravelledwithUsNO = "17";
// }
// if (!empty($getPost['TravellerType'])) {
// $travellerTypeArra = $getPost['TravellerType'];
// $Farr = array_diff($travellerTypeArra, array('0'));
// if (!empty($Farr)) {
// $this->TravellerType = implode(',', $Farr);
// }
// }
// if($getPost['AnniversaryBirth'] == 1 || $getPost['AnniversaryMarriage'] == 1 ){
// if($getPost['AnniversaryBirth'] == 1){
// $getAnniversaryDate = $getPost['AnniversaryDate'];
// $FarrB = array_diff($getAnniversaryDate, array('0'));
// if (!empty($FarrB)) {
// $this->birthdayMonthParam = implode(',', $FarrB);
// }
// }
// if($getPost['AnniversaryMarriage'] == 1){
// $getAnniversaryDate = $getPost['AnniversaryDate'];
// $FarrAB = array_diff($getAnniversaryDate, array('0'));
// if (!empty($FarrAB)) {
// $this->marriageAMonthParam = implode(',', $FarrAB);
// }
// }
// }
// if (!empty($getPost['QueryDate'])) {
// $travellerTypeArra = $getPost['QueryDate'];
// $FarrQ = array_diff($travellerTypeArra, array('0'));
// if (!empty($FarrQ)) {
// $this->queryMonthParam = implode(',', $FarrQ);
// }
// }
// if (!empty($getPost['QueryDate'])) {
// $travellerTypeArra = $getPost['QueryDate'];
// $FarrQ = array_diff($travellerTypeArra, array('0'));
// if (!empty($FarrQ)) {
// $this->queryMonthParam = implode(',', $FarrQ);
// }
// }
// if (!empty($getPost['AgeGroup'])) {
// $AgeGroupArray = $getPost['AgeGroup'];
// $FAgeGroup = array_diff($AgeGroupArray, array('0'));
// if (!empty($FAgeGroup)) {
// $AgeGroupFrom = array();
// $AgeGroupTo = array();
// foreach($FAgeGroup as $valuesSet){
// $arrAgeGroup = explode("-", $valuesSet);
// $AgeGroupFrom1 = trim($arrAgeGroup[0]);
// $timeFrom = strtotime("-" . $AgeGroupFrom1 . ' years');
// $AgeGroupFrom[] = date('Y', $timeFrom);
// $AgeGroupTo1 = trim($arrAgeGroup[1]);
// $timeTo = strtotime("-" . $AgeGroupTo1 . ' years');
// $AgeGroupTo[] = date('Y', $timeTo);
// }
// $this->ageGroupParamFrom = $AgeGroupFrom[0];
// $this->ageGroupParamTo = $AgeGroupTo[0];
// }
// }
// if (!empty($getPost['PassportExpiresinMonth'])) {
// $travellerTypeArra = $getPost['PassportExpiresinMonth'];
// $FarrPE = array_diff($travellerTypeArra, array('0'));
// if (!empty($FarrPE)) {
// $this->PassportExpiresinMonth = implode(',', $FarrPE);
// }
// }
// if ($getPost['otherlocationcity'] != "") {
// $otherlocationcity = $getPost['otherlocationcity'];
// $this->DestinationPlacesSysId = $otherlocationcity;
//
// }
// }
//
// $statement = $this->db->query("GET_CUSTOMER_SEGMENTATION_DATA $AgencySysId,'$this->queryMonthParam','$this->birthdayMonthParam','$this->marriageAMonthParam','$this->TravelledwithUs','$this->TravelledwithUsNO','$this->TravellerType','$this->PassportExpiresinMonth','$this->DestinationPlacesSysId','$this->ageGroupParamFrom','$this->ageGroupParamTo',$ListSysId");
//
// }
public function copyDataFormCustomerToListData($AgencySysId, $queryMonthParam, $birthdayMonthParam, $marriageAMonthParam, $getPost, $ListSysId) {
$this->queryMonthParam = '';
$this->birthdayMonthParam = '';
$this->marriageAMonthParam = '';
$this->TravelledwithUs = '';
$this->TravelledwithUsNO = '';
$this->TravellerType = '';
$this->PassportExpiresinMonth = '';
$this->DestinationPlacesSysId = '';
$this->ageGroupParamFrom = '';
$this->ageGroupParamTo = '';
$this->CustomerTag = '';
if ($queryMonthParam != '') {
$this->queryMonthParam = $queryMonthParam;
}
if ($birthdayMonthParam != '') {
$this->birthdayMonthParam = $birthdayMonthParam;
}
if ($marriageAMonthParam != '') {
$this->marriageAMonthParam = $marriageAMonthParam;
}
$checkFilter = false;
if ($getPost['otherlocationcity'] != "") {
$checkFilter = true;
}
if ($getPost['TravellerTypeDomestic'] != 0) {
$checkFilter = true;
}
if ($getPost['TravellerTypeInternational'] != 0) {
$checkFilter = true;
}
if ($getPost['CustomerTags'] != '') {
$checkFilter = true;
}
if ($getPost['packageTheme'] != '') {
$checkFilter = true;
}
$momthArray = array(
1 => 'Jan',
2 => 'Feb',
3 => 'Mar',
4 => 'Apr',
5 => 'May',
6 => 'Jun',
7 => 'Jul',
8 => 'Aug',
9 => 'Sep',
10 => 'Oct',
11 => 'Nov',
12 => 'Dec'
);
if (!empty($getPost['AgeGroup'])) {
$AgeGroupArray = $getPost['AgeGroup'];
$FAgeGroup = array_diff($AgeGroupArray, array('0'));
if (!empty($FAgeGroup)) {
$checkFilter = true;
}
}
$getAnniversaryDate = $getPost['AnniversaryDate'];
$FarrB = array_diff($getAnniversaryDate, array('0'));
$travellerTypeArra = $getPost['QueryDate'];
$FarrQ = array_diff($travellerTypeArra, array('0'));
$copyFilter = false;
$ListArray = array();
if (!empty($FarrB) || !empty($FarrQ)) {
if (!empty($FarrB)) {
if ($getPost['AnniversaryBirth'] == 1) {
foreach ($FarrB as $fvalue) {
$getMonth = $momthArray[$fvalue];
$ListArray[] = "Customer_Birth_Month_$getMonth";
}
}
if ($getPost['AnniversaryMarriage'] == 1) {
foreach ($FarrB as $fvalue) {
$getMonth = $momthArray[$fvalue];
$ListArray[] = "Customer_Anniversary_Month_$getMonth";
}
}
$copyFilter = true;
}
if (!empty($FarrQ)) {
foreach ($FarrQ as $fvalue) {
$getMonth = $momthArray[$fvalue];
$ListArray[] = "Customer_Query_Month_$getMonth";
}
$copyFilter = true;
}
}
if ($copyFilter == TRUE) {
if (!empty($ListArray)) {
$this->ListTitle = implode(',', $ListArray);
$querySetNew = "COPYDATEFROMDEFAULTLISTTONEWLIST $AgencySysId,'$this->ListTitle',$ListSysId";
$statement = $this->db->query($querySetNew);
}
}
if ($checkFilter == true) {
if ($getPost['TravellerTypeDomestic'] != 0) {
$checkFilter = true;
$this->TravellerType = 2;
}
if ($getPost['CustomerTags'] != '') {
$checkFilter = true;
$this->CustomerTag = explode(',', $getPost['CustomerTags']);
}
if ($getPost['TravellerTypeInternational'] != 0) {
$checkFilter = true;
$this->TravellerType = 3;
}
if ($getPost['TravellerTypeDomestic'] == 1 && $getPost['TravellerTypeInternational'] == 1) {
$this->TravellerType = '';
}
if ($getPost['otherlocationcity'] != "") {
$otherlocationcity = $getPost['otherlocationcity'];
$this->DestinationPlacesSysId = $otherlocationcity;
}
if (!empty($getPost['AgeGroup'])) {
$AgeGroupArray = $getPost['AgeGroup'];
$FAgeGroup = array_diff($AgeGroupArray, array('0'));
if (!empty($FAgeGroup)) {
$AgeGroupFrom = array();
$AgeGroupTo = array();
foreach ($FAgeGroup as $valuesSet) {
$arrAgeGroup = explode("-", $valuesSet);
$AgeGroupFrom1 = trim($arrAgeGroup[0]);
$timeFrom = strtotime("-" . $AgeGroupFrom1 . ' years');
$AgeGroupFrom[] = date('Y', $timeFrom);
$AgeGroupTo1 = trim($arrAgeGroup[1]);
$timeTo = strtotime("-" . $AgeGroupTo1 . ' years');
$AgeGroupTo[] = date('Y', $timeTo);
}
$this->ageGroupParamFrom = $AgeGroupFrom[0];
$this->ageGroupParamTo = $AgeGroupTo[0];
}
}
$leadSourceArray = array();
foreach ($getPost['packageTheme'] as $packageTheme) {
if ($packageTheme > 0) {
$leadSourceArray[] = $packageTheme;
}
}
if (isset($getPost['LeadType']) && ($getPost['LeadType'] == 1)) {
if (isset($getPost['CustomerTypeB2B']) && ($getPost['CustomerTypeB2B'] == 1)) {
$querySet = " SELECT 1 AS CustomerSysId,TBC.FirstName,TBC.LastName,TBC.EmailId,TBC.PrimaryContactNumber AS Contacts from TB_Agency AS T1
Left Join TB_Agency_CRM_LeadAccount AS TBC ON T1.AgencySysId = TBC.AgencySysId
where T1.MasterAgencySysId = $AgencySysId AND TBC.IsActive = 1 ";
} else {
$querySet = " SELECT 1 AS CustomerSysId,TBC.FirstName,TBC.LastName,TBC.EmailId,TBC.PrimaryContactNumber AS Contacts from TB_Agency_CRM_LeadAccount AS TBC
where TBC.AgencySysId = $AgencySysId AND TBC.IsActive = 1 ";
}
if (!empty($leadSourceArray)) {
$querySet .= " AND TBC.LeadSourceSysId IN (" . implode(',', $leadSourceArray) . ")";
}
$querySet .= " group by TBC.EmailId,TBC.FirstName,TBC.LastName,TBC.PrimaryContactNumber ";
} else if (isset($getPost['CustomerTypeB2B']) && ($getPost['CustomerTypeB2B'] == 1)) {
$querySet = " SELECT TBC.CustomerSysId,TBC.FirstName,TBC.LastName,TBC.EmailId,TBC.Contacts from TB_Agency AS T1
Left Join TB_Agency_Customer AS TBC ON T1.AgencySysId = TBC.AgencySysId
where T1.MasterAgencySysId = $AgencySysId AND TBC.IsActive = 1 ";
if (!empty($leadSourceArray)) {
$querySet .= " AND TBC.LeadSourceSysId IN (" . implode(',', $leadSourceArray) . ")";
}
} else {
if ($this->TravellerType != '' || $this->DestinationPlacesSysId != '' || !empty($leadSourceArray) || !empty($this->CustomerTag)) {
$querySet = " SELECT TBC.CustomerSysId,TBC.FirstName,TBC.LastName,TBC.EmailId,TBC.Contacts FROM TB_Agency_Customer TBC
LEFT JOIN TB_Agency_Customer_TravelPlan_Query TBTP ON TBTP.CustomerSysId = TBC.CustomerSysId
AND TBTP.AgencySysId = $AgencySysId
AND TBTP.IsB2BProposal=0
AND TBTP.VersionId = 0
AND TBTP.CustomerSysId != 0
WHERE TBC.ISACTIVE = 1
AND TBC.ISMARKFORDELETE =0
AND TBC.AgencySysId = $AgencySysId
AND TBC.IsB2BAgent = 0 ";
if ($this->TravellerType != '') {
$querySet .= " AND TBTP.PackRangeType != $this->TravellerType";
}
if ($this->DestinationPlacesSysId != '') {
$querySet .= " AND TBTP.DestinationPlacesSysId IN (" . $this->DestinationPlacesSysId . ")";
}
if (!empty($leadSourceArray)) {
$querySet .= " AND TBC.LeadSourceSysId IN (" . implode(',', $leadSourceArray) . ")";
}
if (!empty($this->CustomerTag)) {
$u = 0;
foreach ($this->CustomerTag as $value) {
if ($u == 0) {
$querySet .= " AND (";
} else {
$querySet .= " OR ";
}
$querySet .= " TBC.SpecialRequest like ''%$value%''";
$u++;
}
$querySet .= ")";
}
$querySet .= " group by TBC.CustomerSysId,TBC.EmailId,TBC.FirstName,TBC.LastName,TBC.Contacts,TBC.UpdateDate
order by TBC.UpdateDate";
}
}
$querySP = "GET_CUSTOMER_BUILD_YOUR_SEGMENTATION_DATA $AgencySysId,$ListSysId,'$querySet'";
if($querySet != '') {
$statement = $this->db->query($querySP);
}
}
if ($checkFilter == FALSE && $copyFilter == FALSE) {
$retrun = FALSE;
} else {
$retrun = TRUE;
}
}
public function checkRecodeandInsertCustList($AgencySysId, $EmailId) {
$customerDetails = array('CustSysId');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Marketing_Contacts"), $customerDetails);
$select->where('tb1.AgencySysId = ?', $AgencySysId);
$select->where('tb1.EmailId = ?', $EmailId);
$select->where('tb1.IsMarkForDelete = ?', 0);
$result = $this->db->fetchRow($select);
return $result;
}
public function getNewsletterSubscribersListId($AgencySysId) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Marketing_List"), array('ListSysId'));
$select->where('tb1.AgencySysId = ?', $AgencySysId);
$select->where('tb1.Title = ?', 'Newsletter_Subscribers');
$select->where('tb1.IsMarkForDelete = ?', 0);
$select->where('tb1.IsDefaultList = ?', 1);
$result = $this->db->fetchRow($select);
return $result;
}
public function checkRecodeandInsertCustMapList($AgencySysId, $CustSysId, $ListSysId) {
$customerDetails = array('CustSysId');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Marketing_Mapping_From_List_To_Contacts"), $customerDetails);
$select->where('tb1.AgencySysId = ?', $AgencySysId);
$select->where('tb1.CustSysId = ?', $CustSysId);
$select->where('tb1.ListSysId = ?', $ListSysId);
$select->where('tb1.IsActive = ?', 1);
$select->where('tb1.IsMarkForDelete = ?', 0);
$result = $this->db->fetchRow($select);
return $result;
}
public function insertCustMapList() {
}
public function uploadDataFromCSV($agencySysId, $ListSysId, $file) {
$fileFULL = 'D:\/' . $file;
$errorMessage = '';
$query = "DECLARE @ErrorMessageOutput NVARCHAR(MAX);
EXEC [BULK_INSERT_FROM_CSV_NEW]
$agencySysId,
$ListSysId,
'$fileFULL',
@ErrorMessageOutput OUTPUT;
SELECT @ErrorMessageOutput AS ErrorMessage;";
//echo $query; exit;
try {
$resultSet = $this->db->query($query);
$result = $resultSet->fetch(); // Fetch the first row
if (!empty($result['ErrorMessage'])) {
echo "Error occurred: " . $result['ErrorMessage']; exit;
} else {
echo "Bulk insert completed successfully.";
}
} catch (Zend_Exception $e) {
echo "Database error: " . $e->getMessage();
die;
}
}
public function checkUpload() {
echo $query = "BULK_INSERT_FROM_CSV_NEW 2597,7200,'D:/Html/users/userweb500/Html/SendEmailCSV/upload/sample_contact.csv'";
try {
$this->db->query($query);
} catch (Zend_Exception $e) {
echo $e->getMessage();
}
die;
}
public function mergeExistingList($agencySysId, $ListSysId, $mergeList) {
$iploadList = implode(',', $mergeList);
$query = "MERGE_EXISTING_LIST $agencySysId,$ListSysId,'$iploadList'";
try {
$result = $this->db->query($query);
} catch (Zend_Exception $e) {
$result = $e->getMessage();
}
return $result;
}
public function getListSysId($AgencySysId, $listTitle) {
$customerDetails = array('ListSysId');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Marketing_List"), $customerDetails);
$select->where('tb1.AgencySysId = ?', $AgencySysId);
$select->where('tb1.Title = ?', $listTitle);
$select->where('tb1.IsActive = ?', 1);
$select->where('tb1.IsMarkForDelete = ?', '0');
$result = $this->db->fetchRow($select);
// echo $select;exit;
return $result;
}
public function getB2bReport($AgencySysId, $currentDate, $AgentSysId, $fromDate) {
$returnArray = array();
$customerDetails = array('Title', 'AgencySysId');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency"), $customerDetails);
$select->where('tb1.MasterAgencySysId = ?', $AgencySysId);
$select->where('tb1.AgencyType != ?', 2);
$select->order('tb1.AgencySysId DESC');
$result = $this->db->fetchAll($select);
$valData = array();
$valDataTitle = array();
foreach ($result as $valRequest) {
if (!in_array($valRequest['AgencySysId'], $valData)) {
$valData[] = $valRequest['AgencySysId'];
$valDataTitle[$valRequest['AgencySysId']] = $valRequest['Title'];
}
}
$finalDataArray = array();
if (!empty($valData)) {
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Query"), array("B2BAgencySysId"));
$select->where('tb1.B2BAgencySysId IN (?)', $valData);
$select->where('tb1.MasterTPSysId = (?)', '0');
$select->where('tb1.CustomerSysId != (?)', '0');
$select->where('convert(date,CreateDate) >= (?)', $fromDate);
$select->where('convert(date,CreateDate) <= (?)', $currentDate);
$select->group('tb1.B2BAgencySysId');
$result = $this->db->fetchAll($select);
foreach ($result as $valRequest) {
if (!in_array($valRequest['B2BAgencySysId'], $finalDataArray)) {
$finalDataArray[] = $valRequest['B2BAgencySysId'];
}
}
$finalResultSet = array();
$ReportingToSysId = '';
if (!empty($finalDataArray)) {
$ReportingToSysId = implode(',', $finalDataArray);
$statement = $this->db->query("B2B_REPORT_DATA $AgencySysId,'$currentDate','$ReportingToSysId','$fromDate'");
$resultSet1 = $statement->fetchAll();
$finalResultSet['Result1'] = array();
foreach ($resultSet1 as $row) {
$finalResultSet['Result1'][] = $row;
}
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll();
$finalResultSet['Result2'] = array();
foreach ($resultSet2 as $row) {
$finalResultSet['Result2'][] = $row;
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll();
$finalResultSet['Result3'] = array();
foreach ($resultSet3 as $row) {
$finalResultSet['Result3'][] = $row;
}
$statement->nextRowSet(); // Advance to the third result set
$resultSet4 = $statement->fetchAll();
$finalResultSet['Result4'] = array();
foreach ($resultSet4 as $row) {
$finalResultSet['Result4'][] = $row;
}
}
}
//echo "<pre>";print_r($finalResultSet);
return $finalResultSet;
}
public function getAgenctWiseRevenueReport($AgencySysId, $filterData, $type = '') {
// $sql = "SELECT TBA.Title,TBA.AgencySysId,
// (select count(*) as TotalPax from TB_Agency_Customer_TravelPlan_Pax as TBACTP where TBACTP.TPSysId IN (SELECT TPSysId
// FROM TB_Agency_Customer_TravelPlan as TBACT WHERE TBACT.CustomerSysId = TBAC.CustomerSysId AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123) ) ) as TotalPax
//
// ,(SELECT SUM(NetPrice) FROM TB_Agency_Customer_TravelPlan as TBACT WHERE TBACT.CustomerSysId = TBAC.CustomerSysId AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123)) as TotalPrice
// FROM TB_Agency as TBA "
// . "Left Join TB_Agency_Customer as TBAC ON TBA.AgencySysId=TBAC.AgencySysId "
// . "WHERE TBA.MasterAgencySysId = $AgencySysId AND TBA.AgencyType != '2' ORDER BY TBA.AgencySysId DESC ";
//
//echo "<pre>";print_r($this->searchData);die;
$condition = '';
if (!empty($filterData['updatecurrentDate']) && !empty($filterData['updatefromDate'])) {
$currentDate = date('Y-m-d', strtotime($filterData['updatecurrentDate']));
$fromDate = date('Y-m-d', strtotime($filterData['updatefromDate']));
$condition .= " AND (CONVERT(date,TBACT.UpdateDate)) BETWEEN '$fromDate' AND '$currentDate' ";
}
$select = $this->db->select();
$versionarray = array('Title', 'AgencySysId',);
$select->from(array('TBA' => "TB_Agency"), $versionarray);
$select->joinLeft(array('TBAC' => "TB_Agency_Customer"), "TBA.AgencySysId = TBAC.AgencySysId", array('((SELECT count(*) as TotalPax from TB_Agency_Customer_TravelPlan_Pax as TBACTP where TBACTP.TPSysId IN (SELECT TPSysId FROM TB_Agency_Customer_TravelPlan as TBACT WHERE TBACT.CustomerSysId = TBAC.CustomerSysId AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123) ' . $condition . ' ) )) as TotalPax'
, '((select sum(TBACT.NetPrice) FROM TB_Agency_Customer_TravelPlan TBACT where TBAC.CustomerSysId = TBACT.CustomerSysId AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123) ' . $condition . ' )) as TotalPrice'));
$select->where('TBA.MasterAgencySysId = ?', $AgencySysId);
$select->where("TBA.AgencyType != ?", 2);
if (!empty($this->searchData['agencyname']) && !empty(trim($this->searchData['agencyname']))) {
$select->Where("TBA.Title like '%" . $this->searchData['agencyname'] . "%'");
}
if (!empty($this->searchData['agencyid']) && !empty(trim($this->searchData['agencyid']))) {
$select->where("TBA.AgencySysId = ?", (int) $this->searchData['agencyid']);
}
$select->order("TBA.AgencySysId DESC");
//echo $select;die;
if ($type == 'export') {
$result = $this->db->fetchAll($select);
return $result;
} else {
return $select;
}
}
public function getAgenctWiseRevenueReportSubquery($AgencySysId, $filterData, $type = '') { // error_reporting(E_ALL);
$condition = '';
if (!empty($filterData['updatecurrentDate']) && !empty($filterData['updatefromDate'])) {
$currentDate = date('Y-m-d', strtotime($filterData['updatecurrentDate']));
$fromDate = date('Y-m-d', strtotime($filterData['updatefromDate']));
$condition .= " (CONVERT(date,TBACT.UpdateDate)) BETWEEN '$fromDate' AND '$currentDate' ";
}
// $sql = " SELECT SubQuery.Title, SubQuery.AgencySysId, SubQuery.TotalPax, SubQuery.TotalPrice
// FROM (
// SELECT TBA.Title, TBA.AgencySysId,
// ((SELECT COUNT(*)
// FROM TB_Agency_Customer_TravelPlan_Pax AS TBACTP
// WHERE TBACTP.TPSysId IN (
// SELECT TPSysId
// FROM TB_Agency_Customer_TravelPlan AS TBACT
// WHERE TBACT.CustomerSysId = TBAC.CustomerSysId
// AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123)
// $condition
// )
// )) AS TotalPax,
// ((SELECT SUM(TBACT.NetPrice)
// FROM TB_Agency_Customer_TravelPlan AS TBACT
// WHERE TBAC.CustomerSysId = TBACT.CustomerSysId
// AND TBACT.StatusType IN (17, 10, 9, 18, 19, 13, 7, 102, 123)
// $condition
// )) AS TotalPrice
// FROM TB_Agency AS TBA
// LEFT JOIN TB_Agency_Customer AS TBAC ON TBA.AgencySysId = TBAC.AgencySysId
// WHERE (TBA.MasterAgencySysId = $AgencySysId) AND (TBA.AgencyType != 2) $conditionM
// ) AS SubQuery
// WHERE SubQuery.TotalPrice > 0
// ORDER BY SubQuery.AgencySysId DESC";
// echo $select;die;
// Base select query for the inner query
$innerSelect = $this->db->select()
->from(array('TBA' => 'TB_Agency'), array(
'Title' => 'TBA.Title',
'AgencySysId' => 'TBA.AgencySysId'
))
->joinLeft(array('TBAC' => 'TB_Agency_Customer'), 'TBA.AgencySysId = TBAC.AgencySysId', array())
->where('TBA.MasterAgencySysId = ?', $AgencySysId)
->where('TBA.AgencyType != ?', 2);
$totalPaxSelectInner = $this->db->select()
->from('TB_Agency_Customer_TravelPlan AS TBACT', array('TPSysId'))
->where('TBACT.CustomerSysId = TBAC.CustomerSysId')
->where('TBACT.StatusType IN (?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123));
if ($condition != '') {
$totalPaxSelectInner->where($condition);
}
// Subquery for TotalPax calculation
$totalPaxSelect = $this->db->select()
->from('TB_Agency_Customer_TravelPlan_Pax AS TBACTP', array(new Zend_Db_Expr('COUNT(*)')))
->where('TBACTP.TPSysId IN (?)', $totalPaxSelectInner
// Custom condition if any
);
// Subquery for TotalPrice calculation
$totalPriceSelect = $this->db->select()
->from('TB_Agency_Customer_TravelPlan AS TBACT', array(new Zend_Db_Expr('SUM(TBACT.NetPrice)')))
->where('TBACT.CustomerSysId = TBAC.CustomerSysId')
->where('TBACT.StatusType IN (?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123));
if ($condition != '') {
$totalPriceSelect->where($condition); // Custom condition if any
}
// Add the subqueries as columns to the inner query
$innerSelect->columns(array(
'TotalPax' => new Zend_Db_Expr('(' . $totalPaxSelect . ')'),
'TotalPrice' => new Zend_Db_Expr('(' . $totalPriceSelect . ')')
));
// Wrap the inner query in a new select for final filtering and ordering
$outerSelect = $this->db->select()
->from(array('SubQuery' => $innerSelect), array(
'Title',
'AgencySysId',
'TotalPax',
'TotalPrice'
));
if (isset($this->searchData['agencyname']) && !empty(trim($this->searchData['agencyname']))) {
$outerSelect->Where("SubQuery.Title like '%" . $this->searchData['agencyname'] . "%'");
}
if (isset($this->searchData['agencyid']) && !empty(trim($this->searchData['agencyid']))) {
$outerSelect->where("SubQuery.AgencySysId = ?", (int) $this->searchData['agencyid']);
}
if (isset($this->searchData['showAllAgency']) && $this->searchData['showAllAgency'] == 1) {
} else {
$outerSelect->where('SubQuery.TotalPrice > ?', 0);
}
$outerSelect->order('SubQuery.AgencySysId DESC');
// echo $outerSelect;die;
if ($type == 'export') {
$result = $this->db->fetchAll($outerSelect);
return $result;
} else {
return $outerSelect;
}
}
}