| 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/models/ |
Upload File : |
<?php
class Travel_Model_PackageSearch
{
public $intId = NULL;
private $db = NULL;
public $baseUrl = '';
public $imageServerUrl = '';
public $searcharray;
public function __construct()
{
$this->db = Zend_Db_Table::getDefaultAdapter();
$request = Zend_Controller_Front::getInstance()->getRequest();
$this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
$registry = Zend_Registry::getInstance();
$this->imageServerUrl = @$registry->imageServerURL;
}
public function __destruct()
{
$this->db->closeConnection();
}
public function getPackageList($fields, $intLoggedinUserAgencySysId)
{
$sqlPackage = " SELECT TPSysId,$fields FROM TB_TravelPlan WHERE AgencySysId = '" . @$intLoggedinUserAgencySysId . "' AND IsMarkForDel = '0' AND IsActive = '1'";
$resultPackage = $this->db->query($sqlPackage)->fetchAll();
return $resultPackage;
}
public function getPackageThemeList()
{
$sqlPackageTheme = "SELECT * FROM TB_Master_PackType WHERE IsActive = '1' AND IsMarkForDel = '0' ORDER BY PackType ASC ";
$resultPackageTheme = $this->db->query($sqlPackageTheme)->fetchAll();
return $resultPackageTheme;
}
public function getPackageInclusionList()
{
$sqlPackageIncl = "SELECT * FROM TB_Master_PackInclusion WHERE IsActive = '1' AND IsMarkForDel = '0' ORDER BY InclId ASC ";
$resultPackageIncl = $this->db->query($sqlPackageIncl)->fetchAll();
return $resultPackageIncl;
}
public function getPackageSearchData($getData, $filterData = null, $sortingData = null)
{
$whereCondition = " 1 = 1 AND TBTP.IsCustEdit = '0' AND TBTP.IsActive = 1 AND TBTP.IsMarkForDel = 0 AND TBTP.IsAprooved = 1 And TBTPMWR.PricePerPerson Is NOT NULL AND TBTPMWR.PricePerPerson != 0 AND TBTPMWR.IsActive = 1 AND TBTPMWR.IsMarkForDel = 0 ";
$whereCondition .= " AND TBTP.MasterRefId = 0"; //It is used for package categories (TO display only master package)
$whereCondition .= " AND TBTP.Readymade = 1 AND TBTPMWR.MarketPlaceId = " . $getData['MarketPlaceId'] . ""; // only for readymade packages
if (@$_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId']) {
$whereCondition .= " AND TBTP.AgencySysId = " . $_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId'];
}
if (@$getData['search_specific_date'] && $getData['search_specific_date'] != '__/__/____') {
$date = $getData['search_specific_date'];
} else {
$date = date('d/m/Y');
}
$currentDate = date("Y-m-d");
$tripdate = date("Y-m-d", strtotime(str_replace('/', '-', @$date)));
$whereCondition .= " AND '$tripdate' BETWEEN CAST(TBTPMWR.FromDate AS DATE) AND CAST(TBTPMWR.ToDate AS DATE) ";
$whereCondition .= " AND '$currentDate' <= CAST(TBTP.PkgSellValidTill AS DATE) "; //package validity expicre check
if (@$getData['search_going_to']) {
if (is_array($getData['search_going_to'])) {
$goingTo = explode('__', $getData['search_going_to'][0]);
} else {
$goingTo = explode('__', $getData['search_going_to']);
}
$seachMultiData = $getData['search_going_to_city'];
if (trim($seachMultiData)) {
$expodeDataSet = explode(',', str_replace(",,", "", $seachMultiData));
if (!empty($expodeDataSet)) {
$whereCondition .= " AND ( ";
for ($i = 0; $i < count($expodeDataSet); $i++) {
$whereCondition .= " TBTP.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
if (count($expodeDataSet) > $i + 1) {
$whereCondition .= " OR ";
}
}
$whereCondition .= " ) ";
}
}
}
if (@$getData['search_price_range'] && $getData['search_price_range'] != '0__0') {
$priceRange = explode('__', $getData['search_price_range']);
$whereCondition .= " AND cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRange[0] AND $priceRange[1] ";
}
if (@$getData['travelers']) {
$whereCondition .= " AND TBTP.MinPax <= CASE WHEN (TBTP.MinPax != '') THEN $getData[travelers] ELSE 0 END ";
}
$tpsysidArr = array();
if (@$getData['search_inclusions']) {
}
if (isset($getData['search_theme'])) {
}
$searchArrayIdsFound = new Zend_Session_Namespace('searchArrayIdsFound');
$sessionSearchIds = $searchArrayIdsFound->searchIds;
if (isset($filterData) && isset($sessionSearchIds)) {
if (isset($sortingData['filterPriceRange'])) {
$priceRangeCondn = ' ';
$Inc = 0;
foreach ($sortingData['filterPriceRange'] as $key => $priceRange) {
$priceRangeArr = explode('__', $priceRange);
$sign = ($Inc > 0) ? 'OR' : '';
if ($priceRangeArr[0] == '50000') {
$priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) >= $priceRangeArr[0] ";
} else {
$priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRangeArr[0] AND $priceRangeArr[1] ";
}
$Inc++;
}
$whereCondition .= 'AND ( ' . $priceRangeCondn . ')';
}
if (isset($sortingData['filterDuration'])) {
$filterDurationidsArr = '';
$Inc = 0;
foreach ($sortingData['filterDuration'] as $key => $filterDuration) {
$filterDurationArr = explode('__', $filterDuration);
$sign = ($Inc > 0) ? 'OR' : '';
if (!isset($filterDurationArr[1]) && empty($filterDurationArr[1])) {
$filterDurationidsArr .= " $sign TBTPI.Sequence >= $filterDurationArr[0]";
} else {
$filterDurationidsArr .= " $sign (select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
group by TPSysId) BETWEEN $filterDurationArr[0] AND $filterDurationArr[1] ";
}
$Inc++;
}
$whereCondition .= 'AND ( ' . $filterDurationidsArr . ')';
}
if (isset($sortingData['FilterPackageType'])) {
$ids = implode(', ', $sortingData['FilterPackageType']);
$whereCondition .= 'AND ( TBTP.TourType IN ( ' . $ids . '))';
}
if (isset($sortingData['FilterInclusion'])) {
$maskStr = Zend_Controller_Action_HelperBroker::getStaticHelper('PackageSearch')->getMaskingStringforsearch($sortingData['FilterInclusion']);
$whereCondition .= "AND TBTP.InclMask LIKE '$maskStr%' ";
}
} else {
if (count($tpsysidArr) > 0) {
$tpsysidArr = array_unique(array_values($tpsysidArr));
$ids = implode(', ', $tpsysidArr);
$whereCondition .= 'AND ( TBTP.TPSysId IN ( ' . $ids . '))';
}
}
if (isset($sortingData['sorting'])) {
if ($sortingData['sorting'] == 'price') {
$order = "cast(TBTPMWR.PricePerPerson as float) $sortingData[sortOrder] ";
} else {
$order = " days $sortingData[sortOrder] ";
}
} else {
$order = " cast(TBTPMWR.PricePerPerson as float) ASC , days DESC";
}
if (@$getData['IsB2BAgent'] == 1) {
$whereCondition .= " AND TBTPMWR.MarketPlaceId = 2";
}
$whereCondition .= " AND TBTP.IsFixedDeparturePackage = '0'";
$orderby = " ORDER BY TBTPI.Sequence ASC";
$orderby = " ORDER BY TBTPI.TPIntSysId ASC";
//end sorting conditions
return $this->packagesearchQueryNew($whereCondition, $orderby);
}
public function getPackageSearchDataFixed($getData, $filterData = null, $sortingData = null)
{
$whereCondition = " 1 = 1 AND TBTP.IsCustEdit = '0' AND TBTP.IsActive = 1 AND TBTP.IsMarkForDel = 0 AND TBTP.IsAprooved = 1 And TBTPMWR.PricePerPerson Is NOT NULL AND TBTPMWR.PricePerPerson != 0 AND TBTPMWR.IsActive = 1 AND TBTPMWR.IsMarkForDel = 0 ";
$whereCondition .= " AND TBTP.MasterRefId = 0"; //It is used for package categories (TO display only master package)
$whereCondition .= " AND TBTP.Readymade = 1 AND TBTPMWR.MarketPlaceId = " . $getData['MarketPlaceId'] . ""; // only for readymade packages
if (@$_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId']) {
$whereCondition .= " AND TBTP.AgencySysId = " . $_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId'];
}
if (@$getData['search_specific_date'] && $getData['search_specific_date'] != '__/__/____') {
$date = $getData['search_specific_date'];
} else {
$date = date('d/m/Y');
}
$currentDate = date("Y-m-d");
$tripdate = date("Y-m-d", strtotime(str_replace('/', '-', @$date)));
$whereCondition .= " AND '$currentDate' <= CAST(TBTP.PkgSellValidTill AS DATE) "; //package validity expicre check
if (@$getData['search_going_to']) {
if (is_array($getData['search_going_to'])) {
$goingTo = explode('__', $getData['search_going_to'][0]);
} else {
$goingTo = explode('__', $getData['search_going_to']);
}
$seachMultiData = $getData['search_going_to_city'];
if (trim($seachMultiData)) {
$expodeDataSet = explode(',', str_replace(",,", "", $seachMultiData));
if (!empty($expodeDataSet)) {
$whereCondition .= " AND ( ";
for ($i = 0; $i < count($expodeDataSet); $i++) {
$whereCondition .= " TBTP.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
if (count($expodeDataSet) > $i + 1) {
$whereCondition .= " OR ";
}
}
$whereCondition .= " ) ";
}
}
}
if (@$getData['search_price_range'] && $getData['search_price_range'] != '0__0') {
$priceRange = explode('__', $getData['search_price_range']);
$whereCondition .= " AND cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRange[0] AND $priceRange[1] ";
}
if (@$getData['travelers']) {
$whereCondition .= " AND TBTP.MinPax <= CASE WHEN (TBTP.MinPax != '') THEN $getData[travelers] ELSE 0 END ";
}
$tpsysidArr = array();
if (@$getData['search_inclusions']) {
}
$searchArrayIdsFound = new Zend_Session_Namespace('searchArrayIdsFound');
$sessionSearchIds = $searchArrayIdsFound->searchIds;
if (isset($filterData) && isset($sessionSearchIds)) {
if (isset($sortingData['filterPriceRange'])) {
$priceRangeCondn = ' ';
$Inc = 0;
foreach ($sortingData['filterPriceRange'] as $key => $priceRange) {
$priceRangeArr = explode('__', $priceRange);
$sign = ($Inc > 0) ? 'OR' : '';
if ($priceRangeArr[0] == '50000') {
$priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) >= $priceRangeArr[0] ";
} else {
$priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRangeArr[0] AND $priceRangeArr[1] ";
}
$Inc++;
}
$whereCondition .= 'AND ( ' . $priceRangeCondn . ')';
}
if (isset($sortingData['filterDuration'])) {
$filterDurationidsArr = '';
$Inc = 0;
foreach ($sortingData['filterDuration'] as $key => $filterDuration) {
$filterDurationArr = explode('__', $filterDuration);
$sign = ($Inc > 0) ? 'OR' : '';
if (!isset($filterDurationArr[1]) && empty($filterDurationArr[1])) {
$filterDurationidsArr .= " $sign TBTPI.Sequence >= $filterDurationArr[0]";
} else {
$filterDurationidsArr .= " $sign (select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
group by TPSysId) BETWEEN $filterDurationArr[0] AND $filterDurationArr[1] ";
}
$Inc++;
}
$whereCondition .= 'AND ( ' . $filterDurationidsArr . ')';
}
if (isset($sortingData['FilterPackageType'])) {
$ids = implode(', ', $sortingData['FilterPackageType']);
$whereCondition .= 'AND ( TBTP.TourType IN ( ' . $ids . '))';
}
if (isset($sortingData['FilterInclusion'])) {
$maskStr = Zend_Controller_Action_HelperBroker::getStaticHelper('PackageSearch')->getMaskingStringforsearch($sortingData['FilterInclusion']);
$whereCondition .= "AND TBTP.InclMask LIKE '$maskStr%' ";
}
} else {
if (count($tpsysidArr) > 0) {
$tpsysidArr = array_unique(array_values($tpsysidArr));
$ids = implode(', ', $tpsysidArr);
$whereCondition .= 'AND ( TBTP.TPSysId IN ( ' . $ids . '))';
}
}
if (isset($sortingData['sorting'])) {
if ($sortingData['sorting'] == 'price') {
$order = "cast(TBTPMWR.PricePerPerson as float) $sortingData[sortOrder] ";
} else {
$order = " days $sortingData[sortOrder] ";
}
} else {
$order = " cast(TBTPMWR.PricePerPerson as float) ASC , days DESC";
}
if (@$getData['IsB2BAgent'] == 1) {
$whereCondition .= " AND TBTPMWR.MarketPlaceId = 2";
}
$whereCondition .= " AND TBTP.IsFixedDeparturePackage = 1";
$orderby = " ORDER BY TBTPI.Sequence ASC";
$orderby = " ORDER BY TBTPI.TPIntSysId ASC";
return $this->packagesearchQueryNew($whereCondition, $orderby);
}
public function packagesearchQuery($whereCondition, $Order = NULL)
{
$select = "SELECT TBTP.PackSpecType, TBTPMWR.CurrencyType,TBTP.IsFixedDeparturePackage,TBTP.TPSysId, TBTP.Title, TBTP.InclMask, TBTP.MasterRefId, TBTP.EconomyMask, TBTPMWR.TPRateSheetSysId,TBTPMWR.PricePerPerson as CostPPDO, TBTP.SupplierSysId,SupplierName,TBICS.Rating as SupplierRating,
(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
group by TPSysId) as days FROM TB_TravelPlan AS TBTP
LEFT JOIN TB_IC_Supplier AS TBICS ON TBTP.SupplierSysId=TBICS.SupplierSysId
LEFT JOIN TB_TravelPlan_Itenary AS TBTPI ON TBTP.TPSysId=TBTPI.TPSysId
LEFT JOIN TB_TravelPlan_MarketWise_Rate AS TBTPMWR ON TBTP.TPSysId=TBTPMWR.TPIntSysId and pax=2 WHERE $whereCondition
GROUP BY TBTP.PackSpecType,TBTPMWR.CurrencyType,TBTP.IsFixedDeparturePackage,TBTP.TPSysId,TBTPMWR.TPRateSheetSysId,TBTP.Title,TBTP.InclMask,TBTP.EconomyMask,TBTP.MasterRefId,TBTP.SupplierSysId,SupplierName,TBICS.Rating,TBTPMWR.PricePerPerson $Order";
$result = $this->db->query($select)->fetchAll();
// $result = $this->db->fetchAll($select);
return $result;
}
public function packagesearchQueryNew($whereCondition, $Order = NULL)
{
$select = $this->db->select()->from(array("TBTP" => "TB_TravelPlan"), array("(SELECT count(VersionId) from TB_TravelPlan_Itenary_Accom AS T6666 WHERE TBTPI.TPIntSysId = T6666.TPIntSysId AND T6666.IsActive = 1 AND T6666.IsMarkForDel = 0) AS HotelCount","PackSpecType", "IsFixedDeparturePackage", "TPSysId", "Title", "InclMask", "MasterRefId", "EconomyMask", "SupplierSysId", "(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId group by TPSysId) as days","SourcePlaces","DestinationPlaces"))
->joinLeft(array('TBTPI' => "TB_TravelPlan_Itenary"), "TBTP.TPSysId = TBTPI.TPSysId", array("TPIntSysId", "Sequence", "Title as DayTitle"))
->joinLeft(array('TBTPIP' => "TB_TravelPlan_Itenary_Places"), "TBTPIP.TPIntSysId = TBTPI.TPIntSysId", array(""))
->joinLeft(array('TBTPIA' => "TB_TravelPlan_Itenary_Accom"), "TBTPIA.TPIntSysId = TBTPI.TPIntSysId AND TBTPIA.IsSelectedOnPkg = 1 AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0", array("FromDate"))
->joinLeft(array('TBMGC' => "TB_Master_Geo_City"), "TBMGC.CityId = TBTPIP.PlaceSysId", array("CityId", "Title AS CityName"))
->joinLeft(array('TBICS' => "TB_IC_Supplier"), "TBTP.SupplierSysId = TBICS.SupplierSysId", array("SupplierName", "Rating as SupplierRating"))
->joinLeft(array('TBTPMWR' => "TB_TravelPlan_MarketWise_Rate"), "TBTP.TPSysId=TBTPMWR.TPIntSysId and pax=2", array("CurrencyType", "PricePerPerson as CostPPDO", "TPRateSheetSysId"))
->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBICA.AccomSysId=TBTPIA.AccoSysId", array("Title as HotelName", "AccomSysId", "Stars"))
->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBMRT.RoomType=TBTPIA.RoomType", array("Title as EconomyTypeTitle"))
->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBMAMT.MealTypeId=TBTPIA.MealPlanType", array("Title as MealPlanTypeTitle"))
->joinLeft(array('TTTC' => "TB_TravelPlan_Terms_Conditions"), "TTTC.TPIntSysId=TBTP.TPSysId", array("Inclusions", "Exclusions", "CancellationPolicy"))
->where($whereCondition)
. $Order;
//echo $select; exit;
// $select = "SELECT TBTP.PackSpecType, TBTPMWR.CurrencyType,TBTP.IsFixedDeparturePackage,TBTP.TPSysId, TBTP.Title, TBTP.InclMask, TBTP.MasterRefId, TBTP.EconomyMask, TBTPMWR.TPRateSheetSysId,TBTPMWR.PricePerPerson as CostPPDO, TBTP.SupplierSysId,SupplierName,TBICS.Rating as SupplierRating,
//(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
// group by TPSysId) as days FROM TB_TravelPlan AS TBTP
// LEFT JOIN TB_IC_Supplier AS TBICS ON TBTP.SupplierSysId=TBICS.SupplierSysId
// LEFT JOIN TB_TravelPlan_Itenary AS TBTPI ON TBTP.TPSysId=TBTPI.TPSysId
// LEFT JOIN TB_TravelPlan_MarketWise_Rate AS TBTPMWR ON TBTP.TPSysId=TBTPMWR.TPIntSysId and pax=2 WHERE $whereCondition
// --GROUP BY TBTP.PackSpecType,TBTPMWR.CurrencyType,TBTP.IsFixedDeparturePackage,TBTP.TPSysId,TBTPMWR.TPRateSheetSysId,TBTP.Title,TBTP.InclMask,TBTP.EconomyMask,TBTP.MasterRefId,TBTP.SupplierSysId,SupplierName,TBICS.Rating,TBTPMWR.PricePerPerson $Order";
//echo $select;
$result = $this->db->query($select)->fetchAll();
// $result = $this->db->fetchAll($select);
return $result;
}
public function getPackageIdsByDuration($filterDurationArr, $condition)
{
if (count($filterDurationArr) == 1) {
$havingCondn = " = $filterDurationArr[0] ";
} else {
$havingCondn = " BETWEEN $filterDurationArr[0] AND $filterDurationArr[1] ";
}
if (isset($condition)) {
$where = " AND TBTP.TPSysId IN ($condition) ";
}
$sql = "SELECT TBTP.TPSysId, max(Sequence) as seqcount from TB_TravelPlan TBTP
LEFT JOIN TB_TravelPlan_Itenary AS TBTPI ON TBTP.TPSysId=TBTPI.TPSysId
$where
Group By TBTP.TPSysId
Having max(Sequence) $havingCondn";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getInclusionsIdForSearch_6oct2016($inclusionsData)
{
$getPackageInclusionList = $this->getPackageInclusionList();
$inclusionMasking = $this->getMaskingValueFromSearch($inclusionsData, $getPackageInclusionList, 'InclId');
$inclusionDB = $this->getPackageList('InclMask', $_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId']); // get inclusion list
$inclusionDBListing = $this->getMaskingValueFromListing($inclusionDB, $getPackageInclusionList, 'InclId', 'InclMask');
$inclusionIds = array();
foreach ($inclusionDBListing as $key => $sub_arr) {
// echo '<pre>';
// print_r($inclusionDBListing);
// print_r($sub_arr);die;
if (array_intersect($inclusionMasking, $sub_arr)) {
$inclusionIds[] = $key; // IDS in which inclusion match
}
}
// $inclusionIds = implode(',', array_unique($inclusionIds));
// if ($inclusionIds) {
// $whereCondition = " AND TBTP.TPSysId IN ($inclusionIds)";
// }
return $inclusionIds;
}
public function getMaskingValueFromSearch_6oct2016($maskingArr, $masterListingArr, $primaryKey)
{
if (count($masterListingArr) > 0) {
foreach ($masterListingArr as $maskVal) {
if (in_array($maskVal[$primaryKey], $maskingArr)) {
$mask[] = $maskVal['Title'];
}
}
}
return $mask;
}
public function getMaskingValueFromListing_6oct2016($inclusionDB, $masterListingArr, $primaryKey, $maskingVal)
{
foreach ($inclusionDB as $inclusions) {
$maskingArr = $this->getMasking($inclusions[$maskingVal], $masterListingArr, $primaryKey);
// $mask[$inclusions['TPSysId']] = $maskingArr;
foreach ($masterListingArr as $maskVal) {
if (in_array($maskVal[$primaryKey], $maskingArr)) {
$mask[$inclusions['TPSysId']][] = $maskVal['Title'];
}
}
}
// return array_unique($mask);
return $mask;
}
public function getMasking($varfromDb, $masterListingArr, $primaryKey)
{
$varfromDbArr = str_split(trim($varfromDb));
$resultMask = array();
$counter = 1;
foreach ($masterListingArr as $value) {
if (@$varfromDbArr[$counter] == 1) {
$resultMask[$value[$primaryKey]] = $value[$primaryKey];
}
$counter++;
}
return $resultMask;
}
public function getPackageTncList($packageId)
{
$sqlPackageHotel = "SELECT Inclusions,Exclusions,CancellationPolicy FROM TB_TravelPlan_Terms_Conditions WHERE TPIntSysId = '" . @$packageId . "' AND IsActive = '1' AND IsMarkForDel = '0' ";
$result = $this->db->query($sqlPackageHotel)->fetchAll();
return $result;
}
public function getPackageCities($packageId)
{
$sql = "SELECT TBTPI.*,TBMGC.CityId,
TBMGC.Title AS CityName FROM TB_TravelPlan_Itenary AS TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Places AS TBTPIP ON TBTPIP.TPIntSysId=TBTPI.TPIntSysId
LEFT JOIN TB_Master_Geo_City AS TBMGC ON TBMGC.CityId=TBTPIP.PlaceSysId
WHERE ( TBTPI.IsActive = '1' AND TBTPI.IsMarkForDel = '0' AND TBTPI.TPSysId = '" . @$packageId . "' ) order by TBTPI.Sequence ASC,TBTPI.TotalMinDays ASC";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getPackageHotelReadymade($packageId, $cityId, $startSequence, $endSequence, $TotalMiDays = 0)
{
$select = "select TBTPIA.FromDate,TBTPIA.RoomType,TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.Brief,TBICA.DetailImg,TBICA.AccomSysId,TBICA.Stars, TBICA.Rating, TBICA.AccoAminitiesMask,TBMRT.Title as EconomyTypeTitle,TBICA.IsAgentSpec,TBICA.ICSourceSysId,TBMAMT.Title as MealPlanTypeTitle
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
LEFT JOIN TB_Master_RoomType AS TBMRT ON TBMRT.RoomType=TBTPIA.RoomType
LEFT JOIN TB_Master_Accom_MealType AS TBMAMT ON TBMAMT.MealTypeId=TBTPIA.MealPlanType
where TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBTPIA.XRefCityId = '" . $cityId . "' AND TBTPI.TotalMinDays = '" . $TotalMiDays . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 "; //changed TBTPIA.XRefCityId from TBICA.CitySysId by mangal(10-09-2020) for showing any hotel on agent view page
// where TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 ";
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function getPackageHotel($packageId, $cityId, $startSequence, $endSequence, $byoPackType = NULL)
{
$select = "select TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.AccomSysId,TBICA.Stars,TBMRT.Title as EconomyTypeTitle,TBMAMT.Title as MealPlanTypeTitle, TBICA.Rating, TBICA.AccoAminitiesMask,TBICA.IsAgentSpec,TBICA.ICSourceSysId,TBTPIA.RoomType
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_Master_RoomType AS TBMRT ON TBMRT.RoomType=TBTPIA.RoomType
LEFT JOIN TB_Master_Accom_MealType AS TBMAMT ON TBMAMT.MealTypeId=TBTPIA.MealPlanType
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
where TBTPI.TPSysId = '" . $packageId . "' AND TBTPIA.BYOPackType = '" . $byoPackType . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBTPIA.XRefCityId = '" . $cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBMAMT.IsActive = 1 AND TBMAMT.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 "; //changed TBTPIA.XRefCityId from TBICA.CitySysId by mangal(10-09-2020) for showing any hotel on agent view page
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function getPackageHotelList($packageId, $cityId)
{
$select = "select TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.AccomSysId,TBICA.Stars, TBICA.Rating, TBICA.AccoAminitiesMask
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
where TBTPI.TPSysId = '" . @$packageId . "' AND TBICA.CitySysId = '" . @$cityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 ";
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function getPackageHotelList_20sept2016($packageId, $economyType = 1)
{
$whereCondition = " TBTPIA.IsActive = '1' AND TBTPIA.IsMarkForDel = '0' ";
// if ($economyType == 1) {
$whereCondition .= " AND TBTP.TPSysId = '" . @$packageId . "' ";
// } else {
// $whereCondition .= " AND TBTP.MasterRefId = '" . @$packageId . "' AND TBTP.EconomyMask = $economyType";
// }
$select = $this->db->select()
->distinct('DISTINCT AccoSysId')
->from(array("TBTP" => "TB_TravelPlan"), array('TBTP.TPSysId'))
->joinLeft(array('TBTPI' => 'TB_TravelPlan_Itenary'), 'TBTP.TPSysId=TBTPI.TPSysId', array('TBTPI.TPIntSysId'))
// ->joinLeft(array('TBTPMWR' => 'TB_TravelPlan_MarketWise_Rate'), 'TBTPI.TPIntSysId=TBTPMWR.TPIntSysId', array('TBTPMWR.CostPPDO'))
->joinLeft(array("TBTPIA" => "TB_TravelPlan_Itenary_Accom"), 'TBTPI.TPIntSysId=TBTPIA.TPIntSysId', array('AccoSysId', 'Title'))
->joinLeft(array("TBTPIP" => "TB_TravelPlan_Itenary_Places"), 'TBTPIP.TPIntSysId=TBTPIA.TPIntSysId', array('TPIntSysId'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBTPIP.PlaceSysId', array('TBMGC.CityId as CityId', 'TBMGC.Title as CityName'))
->joinLeft(array('TBICA' => 'TB_IC_Accomdation'), 'TBICA.AccomSysId=TBTPIA.AccoSysId', array('Stars', 'Rating', 'AccoAminitiesMask'))
->where($whereCondition)
->order("CityName ASC");
// echo $select.'</br></br>';
$result = $this->db->fetchAll($select);
return $result;
}
public function getSelectedPackageHotelListByPackageId($packageId)
{
$select = $this->db->select()
->distinct('DISTINCT AccoSysId')
->from(array("TBTP" => "TB_TravelPlan"), array('TPSysId','PackSpecType'))
->joinLeft(array('TBTPI' => 'TB_TravelPlan_Itenary'), 'TBTP.TPSysId=TBTPI.TPSysId', array('Title as DayTitle','Sequence'))
->joinLeft(array("TBTPIA" => "TB_TravelPlan_Itenary_Accom"), 'TBTPI.TPIntSysId=TBTPIA.TPIntSysId', array('FromDate', 'RoomType','MealPlanType'))
->joinLeft(array("TBTPIP" => "TB_TravelPlan_Itenary_Places"), 'TBTPIP.TPIntSysId=TBTPIA.TPIntSysId', array('TPIntSysId'))
->joinLeft(array('TBMGC' => 'TB_Master_Geo_City'), 'TBMGC.CityId=TBTPIP.PlaceSysId', array('CityId', 'Title as CityName'))
->joinLeft(array('TBICA' => 'TB_IC_Accomdation'), 'TBICA.AccomSysId=TBTPIA.AccoSysId', array('Title as HotelName','AccomSysId','Stars'))
->joinLeft(array('TBMRT' => 'TB_Master_RoomType'), 'TBMRT.RoomType = TBTPIA.RoomType', array("Title as EconomyTypeTitle"))
->joinLeft(array('TBMAMT' => 'TB_Master_Accom_MealType'), 'TBMAMT.MealTypeId = TBTPIA.MealPlanType', array("Title as MealPlanTypeTitle"))
->where("TBTP.TPSysId =?", $packageId)
->where("TBTP.IsActive =?", 1)
->where("TBTP.IsMarkForDel =?", 0)
->where("TBTPIA.IsSelectedOnPkg =?", 1)
->where("TBTPIA.IsActive =?", 1)
->where("TBTPIA.IsMarkForDel =?", 0)
->order("TBTPI.Sequence ASC" ) ;
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlexiPackageItenList($id)
{
$sqlFlexiPackageIten = "SELECT a.Sequence FROM TB_TravelPlan_Itenary as a WHERE a.TPSysId = '" . @$id . "' and a.IsMarkForDel = '0' ORDER BY a.Sequence ASC";
$resultFlexiPackageIten = $this->db->query($sqlFlexiPackageIten)->fetchAll();
return $resultFlexiPackageIten;
}
public function getFlexiPackageActivityList($packageId)
{
$whereCondition = "TBTP.TPSysId = '" . @$packageId . "' AND TBTPIE.IsActive = '1' AND TBTPIE.IsMarkForDel = '0' AND TBTPIE.Type = 'Included'";
$select = $this->db->select()
->from(array("TBTP" => "TB_TravelPlan"), array('PackSpecType'))
->joinLeft(array('TBTPI' => 'TB_TravelPlan_Itenary'), 'TBTP.TPSysId=TBTPI.TPSysId', array('Sequence'))
->joinLeft(array("TBTPIE" => "TB_TravelPlan_Itenary_Events"), 'TBTPI.TPIntSysId=TBTPIE.TPIntSysId', array('TPActivitySysId', 'Title'))
->where($whereCondition)
->order("Sequence ASC");
$result = $this->db->fetchAll($select);
// echo $select;
return $result;
}
public function getFlexiPackageSightseenList($packageId)
{
$whereCondition = " TBTP.TPSysId = '" . @$packageId . "' AND TBTPS.IsActive = '1' AND TBTPS.IsMarkForDelete = '0' AND TBTPS.Type IN ('Included','Free') ";
$select = $this->db->select()
->from(array("TBTP" => "TB_TravelPlan"), array('TBTP.TPSysId', 'PackSpecType'))
->joinLeft(array('TBTPI' => 'TB_TravelPlan_Itenary'), 'TBTP.TPSysId=TBTPI.TPSysId', array('TBTPI.Sequence'))
->joinLeft(array("TBTPS" => "TB_TravelPlan_SightSeeing"), 'TBTPI.TPIntSysId=TBTPS.InvnItemSysId', array('SeqId', 'Title', 'CityId', 'SSSysId'))
->where($whereCondition)
->order("TBTPI.Sequence ASC");
// echo $select;
$result = $this->db->fetchAll($select);
return $result;
}
public function getCityList()
{
$sql = "SELECT CityId,Title,Country FROM TB_Master_Geo_City WHERE isActive = 1 AND isActive = 1 AND IsMarkForDel = '0' ORDER BY TITLE ASC ";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getPackMPRate($packid, $travelers, $tripdate)
{
/* Baljeet Sir's Function */
$sqlPackage = "SELECT * FROM TB_TravelPlan_MarketWise_Rate WHERE TPIntSysId = '" . @$packid . "' AND IsMarkForDel = '0' AND IsActive = '1' AND (FromDate>='" . @$tripdate . "' OR ToDate>= '" . @$tripdate . "')";
$resultPackage = $this->db->query($sqlPackage)->fetchAll();
return $resultPackage;
}
public function getChildPackageList($id)
{
//pooja IsMarkForDel uncomment
$whereCondition = " TBTP.IsActive = '1' AND TBTP.IsMarkForDel = '0' AND (TBTP.TPSysId = '" . @$id . "' OR TBTP.MasterRefId = '" . @$id . "') ";
// $whereCondition = " (TBTP.TPSysId = '" . @$id . "' OR TBTP.MasterRefId = '" . @$id . "') ";
$select = $this->db->select()
->from(array("TBTP" => "TB_TravelPlan"), array('TPSysId', 'EconomyMask'))
->joinLeft(array("TBTPMR" => "TB_TravelPlan_MarketWise_Rate"), 'TBTP.TPSysId=TBTPMR.TPIntSysId AND TBTPMR.IsActive = 1 AND TBTPMR.IsMarkForDel = 0', array('PricePerPerson as CostPPDO'))
->where($whereCondition);
$select->group(array("TBTP.TPSysId", "TBTP.EconomyMask", "TBTPMR.PricePerPerson"));
// ->group("TBTP.TPSysId, TBTP.EconomyMask, TBTPMR.PricePerPerson");
//echo $select;
$result = $this->db->fetchAll($select);
return $result;
}
public function getPackageHotelCount($packageId)
{
$whereCondition = " TBTPIA.IsActive = '1' AND TBTPIA.IsMarkForDel = '0' ";
$whereCondition .= " AND TBTP.TPSysId = '" . @$packageId . "' ";
$sql = "SELECT count(TBTP.TPSysId) as hotelCount FROM TB_TravelPlan AS TBTP LEFT JOIN TB_TravelPlan_Itenary AS TBTPI ON TBTP.TPSysId=TBTPI.TPSysId LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId WHERE $whereCondition";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getHotelsListbyCityId($packageId, $CityId, $startSequence, $endSequence)
{
$select = "select TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.AccomSysId,TBICA.Stars, TBICA.Rating, TBICA.AccoAminitiesMask
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
where TBTPI.TPSysId = '" . @$packageId . "' AND TBICA.CitySysId = '" . @$CityId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence ORDER BY TBTPI.Sequence";
$result = $this->db->query($select)->fetchAll();
return $result;
}
// function for fetch food preference . created by Er amit kumar dubey on 12 june 2017 at 12:26 PM
public function getFoodPreferenceList()
{
$sqlFoodPreference = "SELECT * FROM TB_Master_Cuisines WHERE IsActive = '1' AND IsMarkForDel = '0' ORDER BY Title ASC ";
$resultFoodPreference = $this->db->query($sqlFoodPreference)->fetchAll();
return $resultFoodPreference;
}
//this function created by Er Amit Kumar Dubey on 11 august 2017 at 11:59 PM for get result for dynamic package search
public function getDynamicPackageSearchData($getData, $filterData = null, $sortingData = null)
{
$whereCondition = " 1 = 1 AND TBTP.IsActive = 1 AND TBTP.IsMarkForDel = 0 AND TBTP.IsAprooved = 1 ";
$whereCondition .= " AND TBTP.MasterRefId = '0'"; //It is used for package categories (TO display only master package)
$whereCondition .= " AND TBTP.Readymade = 2 ";
//. "AND TBTPMWR.MarketPlaceId = " . $getData['MarketPlaceId'] . ""; // only for readymade packages
if (@$_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId']) {
$whereCondition .= " AND TBTP.AgencySysId = " . $_SESSION['sessionLogin_user']['intLoggedinUserAgencySysId'];
}
if (@$getData['search_specific_date'] && $getData['search_specific_date'] != '__/__/____') {
$date = $getData['search_specific_date'];
} else {
$date = date('d/m/Y');
}
$tripdate = date("Y-m-d", strtotime(str_replace('/', '-', @$date)));
// $whereCondition .= " AND '$tripdate' BETWEEN CAST(TBTPMWR.FromDate AS DATE) AND CAST(TBTPMWR.ToDate AS DATE) ";
$whereCondition .= " AND '$tripdate' <= CAST(TBTP.PkgSellValidTill AS DATE) "; //package validity expicre check
if (@$getData['search_going_to']) {
if (is_array($getData['search_going_to'])) {
$goingTo = explode('__', $getData['search_going_to'][0]);
} else {
$goingTo = explode('__', $getData['search_going_to']);
}
$seachMultiData = $getData['search_going_to_city'];
if (trim($seachMultiData)) {
$expodeDataSet = explode(',', str_replace(",,", "", $seachMultiData));
if (!empty($expodeDataSet)) {
$whereCondition .= " AND ( ";
for ($i = 0; $i < count($expodeDataSet); $i++) {
$whereCondition .= " TBTP.SearchString LIKE '%" . trim($expodeDataSet[$i]) . "%' ";
if (count($expodeDataSet) > $i + 1) {
$whereCondition .= " OR ";
}
}
$whereCondition .= " ) ";
}
}
}
// if (@$getData['search_price_range'] && $getData['search_price_range'] != '0__0') {
// $priceRange = explode('__', $getData['search_price_range']);
// $whereCondition .= " AND cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRange[0] AND $priceRange[1] ";
// }
if (@$getData['travelers']) {
$whereCondition .= " AND TBTP.MinPax <= CASE WHEN (TBTP.MinPax != '') THEN $getData[travelers] ELSE 0 END ";
}
$tpsysidArr = array();
if (@$getData['search_inclusions']) {
}
if (isset($getData['search_theme'])) {
}
$searchArrayIdsFound = new Zend_Session_Namespace('searchArrayIdsFound');
$sessionSearchIds = $searchArrayIdsFound->searchIds;
if (isset($filterData) && isset($sessionSearchIds)) {
// if (isset($sortingData['filterPriceRange'])) {
// $priceRangeCondn = ' ';
// $Inc = 0;
// foreach ($sortingData['filterPriceRange'] as $key => $priceRange) {
// $priceRangeArr = explode('__', $priceRange);
// $sign = ($Inc > 0) ? 'OR' : '';
// if ($priceRangeArr[0] == '50000') {
// $priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) >= $priceRangeArr[0] ";
// } else {
// $priceRangeCondn .= " $sign cast(TBTPMWR.CostPPDO as float) BETWEEN $priceRangeArr[0] AND $priceRangeArr[1] ";
// }
// $Inc++;
// }
// $whereCondition .= 'AND ( ' . $priceRangeCondn . ')';
// }
if (isset($sortingData['filterDuration'])) {
$filterDurationidsArr = '';
$Inc = 0;
foreach ($sortingData['filterDuration'] as $key => $filterDuration) {
$filterDurationArr = explode('__', $filterDuration);
$sign = ($Inc > 0) ? 'OR' : '';
if (!isset($filterDurationArr[1]) && empty($filterDurationArr[1])) {
$filterDurationidsArr .= " $sign (select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
group by TPSysId) >= $filterDurationArr[0]";
} else {
$filterDurationidsArr .= " $sign (select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
group by TPSysId) BETWEEN $filterDurationArr[0] AND $filterDurationArr[1] ";
}
$Inc++;
}
$whereCondition .= 'AND ( ' . $filterDurationidsArr . ')';
}
if (isset($sortingData['FilterPackageType'])) {
$ids = implode(', ', $sortingData['FilterPackageType']);
$whereCondition .= 'AND ( TBTP.TourType IN ( ' . $ids . '))';
}
if (isset($sortingData['FilterInclusion'])) {
$maskStr = Zend_Controller_Action_HelperBroker::getStaticHelper('PackageSearch')->getMaskingStringforsearch($sortingData['FilterInclusion']);
$whereCondition .= "AND TBTP.InclMask LIKE '$maskStr%' ";
}
} else {
if (count($tpsysidArr) > 0) {
$tpsysidArr = array_unique(array_values($tpsysidArr));
$ids = implode(', ', $tpsysidArr);
$whereCondition .= 'AND ( TBTP.TPSysId IN ( ' . $ids . '))';
}
}
$order = " days $sortingData[sortOrder] ";
if (@$getData['IsB2BAgent'] == 1) {
$whereCondition .= " AND TBTPMWR.MarketPlaceId = 2";
} else {
$whereCondition .= " AND TBTPMWR.MarketPlaceId = 1";
}
$whereCondition .= " AND TBTPMWR.IsMarkForDel = 0 AND TBTPMWR.IsActive = 1 AND TBTPIA.IsSelectedOnPkg = 1 AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsActive = 1 AND TBTPI.IsActive = 1 AND TBTPI.IsMarkForDel = 0 AND TBTPIP.IsActive = 1 AND TBTPIP.IsMarkForDel = 0";
$orderby = " ORDER BY Sequence ";
return $this->dynamicPackagesearchQuery($whereCondition, $orderby);
}
//function used for dynamic search query
public function dynamicPackagesearchQuery($whereCondition, $Order = NULL)
{
$select = $this->db->select()->from(array("TBTP" => "TB_TravelPlan"), array("PackSpecType", "IsFixedDeparturePackage", "TPSysId", "Title", "InclMask", "MasterRefId", "EconomyMask", "SupplierSysId", "(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId group by TPSysId) as days","SourcePlaces","DestinationPlaces"))
->joinLeft(array('TBTPI' => "TB_TravelPlan_Itenary"), "TBTP.TPSysId = TBTPI.TPSysId", array("Sequence", "Title as DayTitle"))
->joinLeft(array('TBTPIP' => "TB_TravelPlan_Itenary_Places"), "TBTPIP.TPIntSysId = TBTPI.TPIntSysId", array(""))
->joinLeft(array('TBTPIA' => "TB_TravelPlan_Itenary_Accom"), "TBTPIA.TPIntSysId = TBTPI.TPIntSysId", array("FromDate", "BYOPackType","BYOPackType"))
->joinLeft(array('TBMGC' => "TB_Master_Geo_City"), "TBMGC.CityId = TBTPIP.PlaceSysId", array("CityId", "Title AS CityName"))
->joinLeft(array('TBICS' => "TB_IC_Supplier"), "TBTP.SupplierSysId = TBICS.SupplierSysId", array("SupplierName", "Rating as SupplierRating"))
->joinLeft(array('TBTPMWR' => "TB_TravelPlan_MarketWise_Rate"), "TBTP.TPSysId=TBTPMWR.TPIntSysId", array("CurrencyType", "PricePerPerson as CostPPDO", "TPRateSheetSysId"))
->joinLeft(array('TBICA' => "TB_IC_Accomdation"), "TBICA.AccomSysId=TBTPIA.AccoSysId", array("Title as HotelName", "AccomSysId", "Stars"))
->joinLeft(array('TBMRT' => "TB_Master_RoomType"), "TBMRT.RoomType=TBTPIA.RoomType", array("Title as EconomyTypeTitle"))
->joinLeft(array('TBMAMT' => "TB_Master_Accom_MealType"), "TBMAMT.MealTypeId=TBTPIA.MealPlanType", array("Title as MealPlanTypeTitle"))
->joinLeft(array('TTTC' => "TB_TravelPlan_Terms_Conditions"), "TTTC.TPIntSysId=TBTP.TPSysId", array("Inclusions", "Exclusions", "CancellationPolicy"))
->where($whereCondition)
. $Order;
//echo $select; exit;
// $select = "SELECT TBTP.PackSpecType,TBTPMWR.CurrencyType,TBTP.TPSysId, TBTP.Title,TBTP.BYOPackTypeMask, TBTP.InclMask, TBTP.MasterRefId,TBTP.MinPax,TBTP.GroupSize, TBTP.EconomyMask,
// TBTPMWR.PricePerPerson as CostPPDO,TBTPMWR.TPRateSheetSysId,TBTP.TourType,TBTPMWR.PackCategoryId,TBTP.SupplierSysId,SupplierName,TBICS.Rating as SupplierRating,
//(select max(sequence) from TB_TravelPlan_Itenary TPTI where TPTI.TPSysId = TBTP.TPSysId
// group by TPSysId) as days FROM TB_TravelPlan AS TBTP
// LEFT JOIN TB_IC_Supplier AS TBICS ON TBTP.SupplierSysId=TBICS.SupplierSysId
// LEFT JOIN TB_TravelPlan_Itenary AS TBTPI ON TBTP.TPSysId=TBTPI.TPSysId
// LEFT JOIN TB_TravelPlan_MarketWise_Rate AS TBTPMWR ON TBTP.TPSysId=TBTPMWR.TPIntSysId WHERE $whereCondition
// GROUP BY TBTP.PackSpecType,TBTPMWR.CurrencyType,TBTP.TPSysId, TBTP.Title,TBTPMWR.TPRateSheetSysId,TBTP.TourType,TBTPMWR.PackCategoryId,TBTP.InclMask,TBTP.MinPax,TBTP.GroupSize,TBTP.BYOPackTypeMask,TBTP.EconomyMask,TBTP.MasterRefId,TBTP.SupplierSysId,SupplierName,TBICS.Rating,TBTPMWR.PricePerPerson $Order";
// // echo $select;
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function getDynamicPackageHotelByCatId($packageId, $cityId, $startSequence, $endSequence, $catId)
{
$select = "select TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.AccomSysId,TBICA.Stars,TBMRT.Title as EconomyTypeTitle,TBMAMT.Title as MealPlanTypeTitle, TBICA.Rating, TBICA.AccoAminitiesMask
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_Master_RoomType AS TBMRT ON TBMRT.RoomType=TBTPIA.RoomType
LEFT JOIN TB_Master_Accom_MealType AS TBMAMT ON TBMAMT.MealTypeId=TBTPIA.MealPlanType
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
where TBTPI.TPSysId = '" . $packageId . "' AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence AND TBICA.CitySysId = '" . $cityId . "' AND TBTPIA.BYOPackType = '" . $catId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPIA.IsSelectedOnPkg = 1 ";
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function getDynamicHotelsListbyCityId($packageId, $CityId, $startSequence, $endSequence, $catId)
{
$select = "select TBICA.CitySysId ,Cities as CityName,TBTPI.TPSysId,TBTPI.Sequence, TBICA.Title,TBICA.AccomSysId,TBICA.Stars, TBICA.Rating, TBICA.AccoAminitiesMask
FROM TB_TravelPlan_Itenary TBTPI
LEFT JOIN TB_TravelPlan_Itenary_Accom AS TBTPIA ON TBTPI.TPIntSysId=TBTPIA.TPIntSysId
LEFT JOIN TB_IC_Accomdation AS TBICA ON TBICA.AccomSysId=TBTPIA.AccoSysId
where TBTPI.TPSysId = '" . @$packageId . "' AND TBICA.CitySysId = '" . @$CityId . "' AND TBTPIA.BYOPackType = '" . @$catId . "' AND TBTPIA.IsActive = 1 AND TBTPIA.IsMarkForDel = 0 AND TBTPI.Sequence BETWEEN $startSequence AND $endSequence ORDER BY TBTPI.Sequence";
$result = $this->db->query($select)->fetchAll();
return $result;
}
public function quickPkgListing($AgencySysId)
{
$select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan"), array("StartDate", "TPSysId", "RoomInfoJson", "Title", "DestinationPlaces", "Cities", "CreateDate", "EconomyMask", "NoofNight", "PKqueryType", "TPId"))
->joinLeft(array('t2' => "TB_Agency_Customer"), "t1.CustomerSysId = t2.CustomerSysId", array("FirstName", "LastName"))
->joinLeft(array('t3' => "TB_Agency_User"), "t1.AgentSysId = t3.UserSysId", array("FirstName as AFirstName", "LastName as ALastName"))
->where("t1.AgencySysId =?", $AgencySysId)->where("t1.Readymade =?", 1)->where("t1.PackSpecType =?", 4)->where("t1.TourType =?", 1)->where("t1.EconomyMask IN (6,7,8)")->order("t1.CreateDate DESC");
if (!empty($this->srtSearchData)) {
$select->where($this->srtSearchData);
}
if (!empty($this->srtSearchDataDes)) {
$select->where($this->srtSearchDataDes);
}
//echo $select;die;
//$result = $this->db->fetchAll($select);
return $select;
}
public function getNumberOfdays($TpSysId)
{
$select = $this->db->select()->from(array("t1" => "TB_Agency_Customer_TravelPlan_Itenary"), array("SEQUENCE"));
$select->where("t1.TPSysId =?", $TpSysId)->where("t1.IsActive =?", 1)->where("t1.IsMarkForDel =?", '0');
$select->order("SEQUENCE DESC");
$result = $this->db->fetchRow($select);
return $result;
}
public function daywaiseQuickPkgListing($AgencySysId)
{
//echo "<pre>";print_r($this->searcharray);die();
$select = $this->db->select()->from(array("t1" => "TB_Agency_BYO_Customer_TravelPlan"), array("BYOTPSysId", "AgencySysId", "AgentSysId", "StartDate", "FromDestinationId", "FromDestination", "ToDestinationId", "ToDestination", "CreateDate", "UpdateDate", "IsActive", "IsMarkForDel", "EndDestinationId", "EndDestination"))
->joinLeft(array('t2' => "TB_Agency_Customer_TravelPlan_Query"), "t1.QueryId = t2.TPSysId", array("StatusType", "TPSysId", "NoofNight", 'DaysCountFrom'))
->joinLeft(array('t3' => "TB_Agency_User"), "t1.AgentSysId = t3.UserSysId", array("FirstName as AFirstName", "LastName as ALastName"))
->joinLeft(array('t4' => "TB_Agency_Customer"), "t4.CustomerSysId = t2.CustomerSysId", array("FirstName", "LastName", "Contacts", "EmailId","countrycode"))
->where("t1.AgencySysId =?", $AgencySysId)
->where("t1.IsActive =?", 1)
->where("t1.IsVoucher =?", 0)
->where("t1.IsMarkForDel =?", 0);
if(isset($this->searcharray['destination_id']) && !empty($this->searcharray['destination_id'])){
$select->where("t1.ToDestinationId =?", $this->searcharray['destination_id']);
}
if(isset($this->searcharray['creation_date']) && !empty($this->searcharray['creation_date'])){
$select->where("CAST(t1.CreateDate as Date) =?", $this->searcharray['creation_date']);
}
if(isset($this->searcharray['travel_date']) && !empty($this->searcharray['travel_date'])){
$select->where("CAST(t1.StartDate as Date) =?", $this->searcharray['travel_date']);
}
if(isset($this->searcharray['cust_name']) && !empty($this->searcharray['cust_name'])){
$select->where('t4.FirstName LIKE \'%' . trim($this->searcharray['cust_name']) . '%\' OR t4.LastName LIKE \'%' . trim($this->searcharray['cust_name']) . '%\'');
}
$select->order("t1.CreateDate DESC");
return $select;
}
}