| 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_TblBuyHotel
{
protected $db = NULL;
public $intId = NULL;
public $intAccomSysId = NULL;
public $intLoggedinUserAgencySysId = NULL;
public $InvnItemSysId = NULL;
public $MPType = NULL;
public $CustGrSysId = NULL;
public $strName = NULL;
public $intCountry = NULL;
public $intState = NULL;
public $intCity = NULL;
public $strAddress1 = NULL;
public $strAddress2 = NULL;
public $intRating = NULL;
public $location = NULL;
public $description = NULL;
public $postalCode = NULL;
public $services = NULL;
public $strHotelImage = NULL;
public $intTotalPaxCount = NULL;
public $strNationality = NULL;
public $intTotalRoomCount = NULL;
public $intTotalNightCount = NULL;
public $strRoomPaxInfo = '';
/* Standard, Deluxe and Luxury */
public $strCategory = NULL;
/* From database standard, deluxe and luxury */
public $intCategoryId = NULL;
public $isBackend = 'Y';
/* Common to all models */
public $strCondition = NULL;
public $deleted = 'N';
public $status = '1';
public $strSelectedView = '';
public $intQueryOffset = 0;
public $intListPerPage = 10;
public $isVerified = 'Y';
public $intMarkupType = NULL;
public $orderByField = 'TB_IC_Accomdation.AccomSysId';
public $orderBy = '';
public $onRequest = '';
public $intLimitPerPage = 5;
public $intPageNumber = 1;
public $strCheckInDate = NULL;
public $strCheckOutDate = NULL;
public $strCheckInDateTime = NULL;
public $strCheckOutDateTime = NULL;
public $strResultsSource = "";
/* * ************************************ */
public function __construct()
{
$request = Zend_Controller_Front::getInstance()->getRequest();
$this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
$this->db = Zend_Db_Table::getDefaultAdapter();
$sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
$this->intLoggedinUserAgencySysId = $sessionLogin_user->intLoggedinUserAgencySysId;
}
public function __destruct()
{
$this->db->closeConnection();
}
public function getAgencyMarkup($AgencySysId = NULL)
{
$sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
$sql = " SELECT SOccupMarkUp,DOccupMarkUp,ExtraBedMarkUp,ExtraWOBedMarkUp,MarkUpType,"
. "Currency FROM TB_MP_StdMarkup_Accom WHERE MPType = '1' AND ItemSourceType='0' ";
if (!empty($AgencySysId)) {
$sql .= " AND AgencySysId =" . $AgencySysId;;
}
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getSuplierData($supplierSysId)
{
$sql = "Select ST.Title [SType],S.SupplierSysId [SupplierSysId],S.SupplierName [SupplierName],
S.Address [Address],S.EmailId [Email],C.Title [City],C.Country [Country],S.ContactNo,S.ContactPerson,S.Designation,
SO.AboutUs
From TB_IC_Supplier S Inner Join TB_Master_SupplierType ST
ON S.SupplierTypeSysId = ST.SupplierTypeSysId
Inner Join TB_Master_Geo_City [C]
ON S.CitySysId = C.CityId
Left Outer Join TB_Supplier_OtherDetails SO
ON S.SupplierSysId = SO.SupplierSysId
Where S.SupplierSysId='" . $supplierSysId . "'";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getHotelList()
{
// $sql = " Select * From TB_IC_Accomdation where 1 = 1 " ;
$sql = " SELECT AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title, AliasAndMeta, ShortName, PlaceSysId_XRef
,TB_IC_Accomdation.Icon, ThumnailImg, DetailImg, Stars, Rating, Popularity, AwardRecognizations, PropertyType,
TB_IC_Accomdation.EconomyType AS EconomyTypeId,
TB_Master_EconomyType.Title as EconomyType
,AccoAminitiesMask, MaxPaxCount, RoomCounts, Brief, CheckinTime, CheckOutTime, IsDayLightApp
,GeoLat, GeoLong, Address, PinCode, CitySysId, TB_Master_Geo_City.Title as CityName,
TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ZoneSysId,
TB_IC_Accomdation.ZoneType, TB_IC_Accomdation.StateSysId, TB_IC_Accomdation.ContSysId
,TB_IC_Accomdation.UpdateDate, TB_IC_Accomdation.ApproveDate, TB_IC_Accomdation.ApproveBy,
TB_IC_Accomdation.CreateDate, TB_IC_Accomdation.IsApproved, TB_IC_Accomdation.IsActive, TB_IC_Accomdation.IsMarkForDel
FROM TB_IC_Accomdation LEFT JOIN TB_Master_Geo_City ON TB_IC_Accomdation.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_Master_Geo_Country ON TB_IC_Accomdation.ContSysId = TB_Master_Geo_Country.ContId
LEFT JOIN TB_Master_EconomyType ON TB_IC_Accomdation.EconomyType = TB_Master_EconomyType.EconomyType
WHERE 1 = 1 ";
if (!empty($this->intAccomSysId)) {
$sql .= " AND AccomSysId = " . $this->intAccomSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getAutoSuggestList()
{
// $sql = " Select * From TB_IC_Accomdation where 1 = 1 " ;
$sql = " SELECT TOP 10 AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title, AliasAndMeta, ShortName, PlaceSysId_XRef
,TB_IC_Accomdation.Icon, ThumnailImg, DetailImg, Stars, Rating, Popularity, AwardRecognizations, PropertyType,
TB_IC_Accomdation.EconomyType AS EconomyTypeId,
TB_Master_EconomyType.Title as EconomyType
,AccoAminitiesMask, MaxPaxCount, RoomCounts, Brief, CheckinTime, CheckOutTime, IsDayLightApp
,GeoLat, GeoLong, Address, PinCode, CitySysId, TB_Master_Geo_City.Title as CityName,
TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ZoneSysId,
TB_IC_Accomdation.ZoneType, TB_IC_Accomdation.StateSysId, TB_IC_Accomdation.ContSysId
,TB_IC_Accomdation.UpdateDate, TB_IC_Accomdation.ApproveDate, TB_IC_Accomdation.ApproveBy,
TB_IC_Accomdation.CreateDate, TB_IC_Accomdation.IsApproved, TB_IC_Accomdation.IsActive, TB_IC_Accomdation.IsMarkForDel
FROM TB_IC_Accomdation LEFT JOIN TB_Master_Geo_City ON TB_IC_Accomdation.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_Master_Geo_Country ON TB_IC_Accomdation.ContSysId = TB_Master_Geo_Country.ContId
LEFT JOIN TB_Master_EconomyType ON TB_IC_Accomdation.EconomyType = TB_Master_EconomyType.EconomyType
WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql; die;
return $this->db->query($sql)->fetchAll();
}
public function changeStatus($AccomSysId, $Status)
{
$update = array('IsActive' => $Status);
return $this->db->update('TB_IC_Accomdation', $update, array('AccomSysId = ?' => $AccomSysId));
}
public function getAvailableRoomTypes($intAccomSysId)
{
$sql = " SELECT DISTINCT TB_IC_Accomdation_Room.RoomType as RoomTypeId,
TB_Master_RoomType.Title as RoomType
FROM TB_IC_Accomdation_Room LEFT JOIN TB_Master_RoomType
ON TB_IC_Accomdation_Room.RoomType = TB_Master_RoomType.RoomType
WHERE AccomSysId = " . $intAccomSysId;
return $this->db->query($sql)->fetchAll();
}
public function addHotelInventory($data)
{
try {
$this->db->insert('TB_MP_Inventory_Accom', $data);
return $this->db->lastInsertId('TB_MP_Inventory_Accom');
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
return 0;
}
public function getHotelInventoryList()
{
/*
$sql = " SELECT InvnItemSysId, TB_IC_Accomdation.Title as HotelName, InventoryType
,XRefAccoSysId, XRefItemSysId, AgencySysId, SupplierSysId, TB_MP_Inventory_Accom.Title, TB_MP_Inventory_Accom.EconomyType,
TB_Master_EconomyType.Title as EconomyTypeTitle
,MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate, DailyRoomLimit, PurchasedQty,
PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType
,SoldQty, ReturnQty, NetInHandQty, Currency, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_Master_SupplierType.Title as SupplierType,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved, TB_Master_Currency.Title as CurrencyTitle,
TB_Master_Currency.Symbol as CurrencySymbol
FROM TB_MP_Inventory_Accom
INNER JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_Master_SupplierType ON TB_MP_Inventory_Accom.SupplierSysId = TB_Master_SupplierType.SupplierTypeSysId
LEFT JOIN TB_Master_Currency ON TB_MP_Inventory_Accom.Currency = TB_Master_Currency.CurrencyType
LEFT JOIN TB_Master_EconomyType ON TB_MP_Inventory_Accom.EconomyType = TB_Master_EconomyType.EconomyType
WHERE 1=1 ";
*/
$sql = " SELECT InvnItemSysId, TB_IC_Accomdation.Title as HotelName, InventoryType
,XRefAccoSysId, XRefItemSysId, AgencySysId, TB_MP_Inventory_Accom.SupplierSysId, TB_MP_Inventory_Accom.Title,
TB_MP_Inventory_Accom.EconomyType,TB_IC_Accomdation.Stars as StarRating, TB_IC_Accomdation.Address as Address,
TB_Master_Currency.Symbol as CurrencySymbol, TB_Master_RoomType.Title as RoomType
,MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate, DailyRoomLimit, PurchasedQty,
PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType, RateType, Tax
,SoldQty, ReturnQty, NetInHandQty, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_IC_Supplier.SupplierName as SupplierName,
NetDOccupCost, NetSOccupCost, NetExtraBedCost, NetExtraWOBedCost, NetTax, RackCommission,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved
FROM TB_MP_Inventory_Accom
INNER JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_IC_Supplier ON TB_MP_Inventory_Accom.SupplierSysId = TB_IC_Supplier.SupplierSysId
LEFT JOIN TB_Master_Currency ON TB_MP_Inventory_Accom.CostCurrency = TB_Master_Currency.CurrencyType
LEFT JOIN TB_Master_RoomType ON TB_MP_Inventory_Accom.EconomyType = TB_Master_RoomType.RoomType
WHERE 1=1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
if (!empty($this->intAccomSysId)) {
$sql .= " AND XRefAccoSysId = " . $this->intAccomSysId;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
$sql .= " ORDER BY InvnItemSysId DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getMPInventoryAccomList()
{
$sql = " SELECT InvnItemSysId, TB_IC_Accomdation.Title as HotelName, InventoryType
,XRefAccoSysId, XRefItemSysId, AgencySysId, SupplierSysId, TB_MP_Inventory_Accom.Title, TB_MP_Inventory_Accom.EconomyType
,MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate,
DailyRoomLimit, PurchasedQty, PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType
,SoldQty, ReturnQty, NetInHandQty, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_MP_Inventory_Accom.Tax,
RackCommission,NetDOccupCost,NetSOccupCost,NetExtraBedCost,NetExtraWOBedCost,
CostCurrencyTable.Symbol as CurrencySymbol, TrxCurrencyTable.Symbol as TrxCurrencySymbol,
TB_Master_SupplierType.Title as SupplierType,TB_MP_Inventory_Accom.CostCurrency,
TB_MP_Inventory_Accom.TrxCurrency,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg, RateType
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved
FROM TB_MP_Inventory_Accom
LEFT JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_Master_SupplierType ON TB_MP_Inventory_Accom.SupplierSysId = TB_Master_SupplierType.SupplierTypeSysId
LEFT JOIN TB_Master_Currency AS CostCurrencyTable ON TB_MP_Inventory_Accom.CostCurrency = CostCurrencyTable.CurrencyType
LEFT JOIN TB_Master_Currency AS TrxCurrencyTable ON TB_MP_Inventory_Accom.TrxCurrency = TrxCurrencyTable.CurrencyType
WHERE 1=1 ";
if (!empty($this->intAccomSysId)) {
$sql .= " AND TB_MP_Inventory_Accom.XRefAccoSysId = " . $this->intAccomSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
$sql .= " ORDER BY InvnItemSysId DESC ";
return $this->db->query($sql)->fetchAll();
}
public function getMPInventoryRoomList()
{
$sql = " SELECT InvnItemSysId
,XRefAccoSysId, XRefItemSysId, TB_MP_Inventory_Accom.Title, TB_MP_Inventory_Accom.EconomyType,
TB_Master_RoomType.Title as RoomTypeText, TB_IC_Accomdation_Room.RoomType as RoomTypeId,
TB_IC_Accomdation_Room.MaxPaxCount,TB_IC_Accomdation_Room.MaxAllowChild
,TB_IC_Accomdation_Room.PaxCount, TB_IC_Accomdation_Room.AllowChild,TB_IC_Accomdation_Room.OtherDetails
,TB_MP_Inventory_Accom.MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate,
DailyRoomLimit, PurchasedQty, PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType
,SoldQty, ReturnQty, NetInHandQty, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_MP_Inventory_Accom.Tax,
RackCommission,NetDOccupCost,NetSOccupCost,NetExtraBedCost,NetExtraWOBedCost,
CostCurrencyTable.Symbol as CurrencySymbol, TrxCurrencyTable.Symbol as TrxCurrencySymbol,
TB_Master_SupplierType.Title as SupplierType,TB_MP_Inventory_Accom.CostCurrency,
TB_MP_Inventory_Accom.TrxCurrency,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg, RateType
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved
FROM TB_MP_Inventory_Accom
LEFT JOIN TB_IC_Accomdation_Room ON TB_MP_Inventory_Accom.XRefItemSysId = TB_IC_Accomdation_Room.ItemSysId
LEFT JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_RoomType ON TB_IC_Accomdation_Room.RoomType = TB_Master_RoomType.RoomType
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_Master_SupplierType ON TB_MP_Inventory_Accom.SupplierSysId = TB_Master_SupplierType.SupplierTypeSysId
LEFT JOIN TB_Master_Currency AS CostCurrencyTable ON TB_MP_Inventory_Accom.CostCurrency = CostCurrencyTable.CurrencyType
LEFT JOIN TB_Master_Currency AS TrxCurrencyTable ON TB_MP_Inventory_Accom.TrxCurrency = TrxCurrencyTable.CurrencyType
WHERE 1=1 ";
if (!empty($this->intAccomSysId)) {
$sql .= " AND TB_MP_Inventory_Accom.XRefAccoSysId = " . $this->intAccomSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
$sql .= " ORDER BY InvnItemSysId DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getMPInventoryHotelRoomList()
{
$sql = " SELECT TB_IC_Accomdation_Room.AccomSysId,TB_IC_Accomdation_Room.ItemSysId,TB_IC_Accomdation_Room.MealPlanType,
TB_Master_Accom_MealType.Title as MealType, TB_Master_RoomType.Title as RoomTypeText, TB_IC_Accomdation_Room.RoomType as RoomTypeId,
TB_IC_Accomdation_Room.MaxPaxCount,TB_IC_Accomdation_Room.MaxAllowChild, TB_IC_Accomdation_Room.Qty
,TB_IC_Accomdation_Room.PaxCount, TB_IC_Accomdation_Room.AllowChild,TB_IC_Accomdation_Room.OtherDetails
FROM TB_IC_Accomdation_Room
LEFT JOIN TB_IC_Accomdation ON TB_IC_Accomdation_Room.AccomSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_RoomType ON TB_IC_Accomdation_Room.RoomType = TB_Master_RoomType.RoomType
LEFT JOIN TB_Master_Accom_MealType ON TB_IC_Accomdation_Room.MealPlanType = TB_Master_Accom_MealType.MealTypeId
WHERE 1=1 ";
if (!empty($this->intAccomSysId)) {
$sql .= " AND TB_IC_Accomdation_Room.AccomSysId = " . $this->intAccomSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_IC_Accomdation_Room.IsDelete = '0' ";
$sql .= " ORDER BY TB_IC_Accomdation_Room.AccomSysId DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getMPInventoryAccom($XRefAccoSysId)
{
$sql = " SELECT InvnItemSysId, TB_IC_Accomdation.Title as HotelName, InventoryType, TB_MP_Inventory_Accom.AccomSysId, AgencySysId, SupplierSysId, TB_MP_Inventory_Accom.Title, TB_MP_Inventory_Accom.EconomyType
,MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate, DailyRoomLimit, PurchasedQty, PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType
,SoldQty, ReturnQty, NetInHandQty, Currency, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_Master_SupplierType.Title as SupplierType,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved, TB_Master_Currency.Title as CurrencyTitle,
TB_Master_Currency.Symbol as CurrencySymbol
FROM TB_MP_Inventory_Accom
INNER JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.AccomSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_Master_SupplierType ON TB_MP_Inventory_Accom.SupplierSysId = TB_Master_SupplierType.SupplierTypeSysId
LEFT JOIN TB_Master_Currency ON TB_MP_Inventory_Accom.Currency = TB_Master_Currency.CurrencyType
WHERE TB_MP_Inventory_Accom.XRefAccoSysId = " . $XRefAccoSysId;
// if(!empty($intRecordId)) {
// $sql .= " AND TB_MP_Inventory_Accom.InvnItemSysId = " . $intRecordId ;
// }
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY InvnItemSysId DESC ";
//echo $sql;
return $this->db->query($sql)->fetchAll();
}
public function addMPInventoryAccom($insertData)
{
$this->db->insert('TB_MP_Inventory_Accom', $insertData);
return $this->db->lastInsertId('TB_MP_Inventory_Accom');
}
public function getICAccomdationRoom($XRefAccoSysId)
{
$sql = " SELECT AccomSysId, ItemSysId, SourceSysId, Title, RoomType, EconomyType, Qty, BedType, PaxCount
,IsPetAllow, MaxPetAllow, AllowChild, AllowInfant, MaxPaxCount, MaxAllowChild, MaxAllowInfant
,MealPlanType, AminitiesMask, InclustionOptionsMask, OtherDetails, UpdatedDate, CreateDate
,ApproveDate, ApproveBy, IsDelete, IsActive, IsApproved FROM TB_IC_Accomdation_Room
WHERE AccomSysId = " . $XRefAccoSysId;
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
if (!empty($this->ItemSysId)) {
$sql .= " AND TB_IC_Accomdation_Room.ItemSysId = " . $this->ItemSysId;
}
return $this->db->query($sql)->fetchAll();
}
public function getMarkups($InvnItemSysId, $MPType = '', $CustGrSysId = '')
{
$sql = " SELECT InvnItemSysId, TB_MP_Inventory_Accom_Alloc.MPType AS MPType_id, TB_Master_MPType.Title as MPType,
CustGrSysId, ItemType, AgencySysId, AllocateQty, IsVirtualInven
,SoldQty, ReturnQty, NetQty, BlockQty, MarkUpType, MarkUp, Currency, DOccupPrice, SOccupPrice
,ExtraBedPrice, ExtraWOBedPrice, PerPaxPrice, OtherPrice, Discount, DiscountType, DiscountAmount,
DiscountMsg, NetPrice, IsPublish, PublishDate, TB_MP_Inventory_Accom_Alloc.Details, RateRuleSysId, TCDocSysId, ValidTill,
ApproveDate, CreateDate, UpdateDate, IsApproved, IsMarkForDel, IsActive
FROM TB_MP_Inventory_Accom_Alloc INNER JOIN TB_Master_MPType
ON TB_MP_Inventory_Accom_Alloc.MPType = TB_Master_MPType.MPType
WHERE 1 = 1 ";
if (!empty($MPType)) {
$sql .= " AND TB_MP_Inventory_Accom_Alloc.MPType = " . $MPType;
}
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
if (!empty($CustGrSysId)) {
$sql .= " AND CustGrSysId = " . $CustGrSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
//$sql .= " AND TB_MP_Inventory_Accom_Alloc.IsMarkForDel != '1' " ;
if (!empty($this->intMarkupType)) {
$sql .= " AND MarkUpType = " . $this->intMarkupType;
}
$sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
return $this->db->query($sql)->fetchAll();
}
public function getMPInventoryAccomAlloc()
{
$sql = " SELECT InvnItemSysId, TB_MP_Inventory_Accom_Alloc.MPType AS MPType_id, TB_Master_MPType.Title as MPType,
CustGrSysId, ItemType, AgencySysId, AllocateQty, IsVirtualInven,TB_MP_Inventory_Accom_Alloc.Tax
,SoldQty, ReturnQty, NetQty, BlockQty, MarkUpType, MarkUp, Currency, DOccupPrice, SOccupPrice
,ExtraBedPrice, ExtraWOBedPrice, PerPaxPrice, OtherPrice, Discount, DiscountType, DiscountAmount,
DiscountMsg, NetPrice, IsPublish, PublishDate, TB_MP_Inventory_Accom_Alloc.Details, RateRuleSysId, TCDocSysId, ValidTill,
ApproveDate, CreateDate, UpdateDate, IsApproved, IsMarkForDel, IsActive
FROM TB_MP_Inventory_Accom_Alloc INNER JOIN TB_Master_MPType
ON TB_MP_Inventory_Accom_Alloc.MPType = TB_Master_MPType.MPType
WHERE 1 = 1 ";
if (!empty($this->InvnItemSysId)) {
$sql .= " AND TB_MP_Inventory_Accom_Alloc.InvnItemSysId = " . $this->InvnItemSysId;
}
if (!empty($this->MPType)) {
$sql .= " AND TB_MP_Inventory_Accom_Alloc.MPType = " . $this->MPType;
}
if (!empty($this->CustGrSysId)) {
$sql .= " AND CustGrSysId = " . $this->CustGrSysId;
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_MP_Inventory_Accom_Alloc.IsMarkForDel != '1' ";
$sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
return $this->db->query($sql)->fetchAll();
}
public function addMarkupSettings($data)
{
try {
$this->db->insert('TB_MP_Inventory_Accom_Alloc', $data);
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
return true;
}
public function updateMarkupSettings($InvnItemSysId, $MPType, $CustGrSysId, $data)
{
$where = array(
'InvnItemSysId = ? ' => $InvnItemSysId,
'MPType = ? ' => $MPType,
'CustGrSysId = ? ' => $CustGrSysId
);
$this->db->update('TB_MP_Inventory_Accom_Alloc', $data, $where);
}
public function deleteMarkupSetting($InvnItemSysId, $MPType, $CustGrSysId)
{
$field = array('IsMarkForDel' => '1');
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'MPType = ? ' => $MPType, 'CustGrSysId = ? ' => $CustGrSysId);
$this->db->update('TB_MP_Inventory_Accom_Alloc', $field, $where);
}
public function deleteBulkHotelInventory($intRecordId)
{
try {
$this->db->beginTransaction();
$where = array('InvnItemSysId = ? ' => $intRecordId);
//$this->db->delete('TB_MP_Inventory_Accom_Alloc', $where);
$where = array('InvnItemSysId = ? ' => $intRecordId);
//$this->db->delete('TB_MP_Inventory_Accom', $where);
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
throw new Exception($e->getMessage());
}
}
public function deleteMPInventoryAccom($InvnItemSysId)
{
try {
$this->db->beginTransaction();
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
//$this->db->delete('TB_MP_Inventory_Accom_Alloc', $where);
$this->db->update('TB_MP_Inventory_Accom_Alloc', array('IsMarkForDel' => '1'), $where);
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
//$this->db->delete('TB_MP_Inventory_Accom', $where);
$this->db->update('TB_MP_Inventory_Accom', array('IsMarkForDelete' => '1'), $where);
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
throw new Exception($e->getMessage());
}
}
public function getAutoSuggest($keyword, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '" . $keyword . "%' ";
$objHotel->strCondition .= " AND ICSourceSysId IN ($intLoggedinUserAgencySysId,3)";
$rowset = $objHotel->getAutoSuggestList();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
//$response[] = array('AccomSysId' => $row['AccomSysId'], 'Title' => $row['Title']); ;
$response[] = array('value' => $row['AccomSysId'], 'label' => $row['Title'] . ', ' . $row['CityName']);
//$response[] = $row['Title'] ;
}
}
$response[] = array('value' => -1, 'label' => 'Add New');;
return $response;
}
public function getAutoSuggestByAgent($keyword, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '" . $keyword . "%' ";
$objHotel->strCondition .= " AND ICSourceSysId IN ($intLoggedinUserAgencySysId)";
$rowset = $objHotel->getAutoSuggestList();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
//$response[] = array('AccomSysId' => $row['AccomSysId'], 'Title' => $row['Title']); ;
$response[] = array('value' => $row['AccomSysId'], 'label' => $row['Title'] . ', ' . $row['CityName']);
//$response[] = $row['Title'] ;
}
}
//$response[] = array('value' => -1 , 'label' => 'Add New'); ;
return $response;
}
public function getHotelRoomIdsList($intAccomSysId)
{
$sql = "select RoomType from TB_IC_Accomdation_Room WHERE 1 = 1 ";
if (!empty($intAccomSysId)) {
$sql .= " AND AccomSysId = " . $intAccomSysId;
}
//echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
//$response[] = array('AccomSysId' => $row['AccomSysId'], 'Title' => $row['Title']); ;
$response[] = $row['RoomType'];
//$response[] = $row['Title'] ;
}
}
//$response[] = array('value' => -1 , 'label' => 'Add New'); ;
return $response;
}
public function getNextRoomAccomodationId($intAccomSysId)
{
$sql = " SELECT isnull(max(ItemSysId),0)+1 as ItemSysId
from TB_IC_Accomdation_Room where AccomSysId = " . $intAccomSysId;
$data = $this->db->query($sql)->fetch();
return $data['ItemSysId'];
}
public function addIndividualHotelRoomsInventory($data)
{
try {
// echo "<pre>";
// print_r($data);
// exit;
$this->db->insert('TB_IC_Accomdation_Room', $data);
return true;
} catch (Exception $e) {
die($e->getMessage());
return false;
}
}
public function addICAccomdationRoom($data)
{
try {
$this->db->insert('TB_IC_Accomdation_Room', $data);
return $this->db->lastInsertId('TB_IC_Accomdation_Room');
} catch (Exception $e) {
die($e->getMessage());
return false;
}
}
public function updateICAccomdationRoom($data, $AccomSysId, $ItemSysId)
{
try {
$where[] = 'AccomSysId = ' . $AccomSysId;
$where[] = 'ItemSysId = ' . $ItemSysId;
// echo "<pre>"; print_r($data);
// echo "<pre>"; print_r($where);
// exit;
$this->db->update('TB_IC_Accomdation_Room', $data, $where);
} catch (Exception $e) {
die($e->getMessage());
return false;
}
}
public function isICAccomodationRoomExists($AccomSysId, $RoomType)
{
$sql = " SELECT COUNT(*)AS TOT FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $AccomSysId . " AND RoomType = " . $RoomType;
$result = $this->db->query($sql)->fetch();
$total = $result['TOT'];
return $total > 0 ? true : false;
}
public function addICAccomodationRoom($data)
{
try {
// echo "<pre>";
// print_r($data);
// exit;
$this->db->insert('TB_IC_Accomdation_Room', $data);
return true;
} catch (Exception $e) {
die($e->getMessage());
return false;
}
}
public function getHotelRoomsInventory($intAccomSysId)
{
$sql = " SELECT AccomSysId, ItemSysId, TB_IC_Accomdation_Room.Title, TB_Master_RoomType.RoomType as RoomTypeId,
TB_Master_RoomType.Title as RoomType, EconomyType, Qty, BedType, PaxCount, IsPetAllow,
TB_IC_Accomdation_Room.MealPlanType as MealTypeId, TB_Master_Accom_MealType.Title as MealType
,MaxPetAllow, AllowChild, AllowInfant, MaxPaxCount, MaxAllowChild, MaxAllowInfant, MealPlanType
,AminitiesMask, InclustionOptionsMask, OtherDetails, UpdatedDate, TB_Master_RoomType.CreateDate,
IsDelete, TB_Master_RoomType.IsActive
FROM TB_IC_Accomdation_Room
LEFT JOIN TB_Master_RoomType ON TB_IC_Accomdation_Room.RoomType = TB_Master_RoomType.RoomType
LEFT JOIN TB_Master_Accom_MealType ON TB_IC_Accomdation_Room.MealPlanType = TB_Master_Accom_MealType.MealTypeId
WHERE 1 = 1 AND AccomSysId = " . $intAccomSysId;
$sql .= " ORDER BY TB_IC_Accomdation_Room.CreateDate DESC ";
return $this->db->query($sql)->fetchAll();
}
public function getHotelRoomAmenities($intAccomSysId, $ItemSysId, $format)
{
$sql = " SELECT AminitiesMask FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $intAccomSysId . " "
. " AND ItemSysId = " . $ItemSysId;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
$AminitiesMask = $result[0]['AminitiesMask'];
$commaSepMask = '';
if (strlen($AminitiesMask) > 0) {
for ($i = 1; $i < strlen($AminitiesMask); $i++) {
$char = substr($AminitiesMask, $i, 1);
$commaSepMask .= $char . ",";
}
}
$AminitiesMask = Zend_Controller_Action_HelperBroker::getStaticHelper('String')->cleanCommaSepString($commaSepMask);
if (strtolower($format) == 'comma')
return $AminitiesMask;
if (!empty($AminitiesMask)) {
$objAmenities = new Travel_Model_TblAmenities();
$objAmenities->strCondition = " AND AminityId IN ( " . $AminitiesMask . " ) ";
$arrAmenities = $objAmenities->getRoomAmenitiesList();
$response = array();
if (count($arrAmenities) > 0) {
foreach ($arrAmenities as $aminity) {
$response[] = array('AminityId' => $aminity['AminityId'], 'Title' => $aminity['Title']);
}
}
return $response;
}
}
return strtolower($format) == 'comma' ? '' : array();
}
public function createAccoAmenitiesMask($amenitiesIds)
{
$mask = '1';
$objHotel = new Travel_Model_TblHotel();
$objHotel->orderByField = " AminityId ";
$arrMasterAmenities = $objHotel->getMasterAccomAminities();
//echo "<pre>"; print_r($arrMasterAmenities);
$arrAmenities = explode(",", $amenitiesIds);
if (count($arrMasterAmenities) > 0) {
foreach ($arrMasterAmenities as $amenity) {
$char = in_array($amenity['AminityId'], $arrAmenities) ? '1' : '0';
//echo $char;
$mask .= $char;
}
}
return $mask;
}
public function addAccomodationRoomAmenities($AccomSysId, $amenities)
{
$objHotel = new Travel_Model_TblHotel();
$amenities_mask = $objHotel->createAccoAmenitiesMask($amenities);
$update = array('AccoAminitiesMask' => $amenities_mask);
$this->db->update('TB_IC_Accomdation', $update, 'AccomSysId = ' . $AccomSysId);
}
public function getRoomAmenitiesList()
{
$sql = " SELECT AminityId, Title, Icon, UpdateDate, CreateDate, IsMarkForDel, IsActive
FROM TB_Master_Accom_RoomAminity WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
return $this->db->query($sql)->fetchAll();
}
public function createRoomAmenitiesMask($amenitiesIds)
{
$mask = '1';
$arrAmenities = explode(",", $amenitiesIds);
$objHotel = new Travel_Model_TblHotel();
$objHotel->orderByField = 'AminityId';
$objHotel->orderBy = 'asc';
$arrMasterAmenities = $objHotel->getRoomAmenitiesList();
if (count($arrMasterAmenities) > 0) {
foreach ($arrMasterAmenities as $amenity) {
$char = in_array($amenity['AminityId'], $arrAmenities) ? '1' : '0';
$mask .= $char;
}
}
return $mask;
}
public function getAmenities($AccomSysId, $format)
{
$sql = " SELECT AccoAminitiesMask FROM TB_IC_Accomdation WHERE AccomSysId = " . $AccomSysId;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
// $objAmenities = new Travel_Model_TblAmenities();
// return $objAmenities->getAmenitiesFromMask($result[0]['AccoAminitiesMask'], $format);
return $this->getAccoAmenitiesFromMask($result[0]['AccoAminitiesMask'], $format);
}
return ($format == 'comma') ? '' : array();
}
public function getMasking($AccomSysId)
{
$sql = " SELECT AccoAminitiesMask FROM TB_IC_Accomdation WHERE AccomSysId = " . $AccomSysId;
$result = $this->db->query($sql)->fetch();
$varfromDb = $result['AccoAminitiesMask'];
$sql_master_ame = " SELECT AminityId, CatMask, Title, Icon, IsApprove, ApproveDate
,UpdateDate, CreateDate, IsMarkForDel, IsActive
FROM TB_Master_Accom_AccomAminity WHERE 1 = 1 and IsMarkForDel='0' order by AminityId DESC";
$arrMasterAmenities = $this->db->query($sql_master_ame)->fetchAll();
//$arrMasterAmenities = $objHotel->getMasterAccomAminities();
//echo "<pre>"; print_r($resultMask); die;
$varfromDbArr = str_split(trim($varfromDb));
//echo "<pre>"; print_r($varfromDbArr); die;
$resultMask = array();
$counter = 1;
foreach ($arrMasterAmenities as $value) {
if (@$varfromDbArr[$counter] == 1) {
$resultMask[$value['AminityId']] = $value['AminityId'];
}
$counter++;
}
//echo "<pre>"; print_r($resultMask); die;
return $resultMask;
}
public function getAccoAmenitiesFromMask($mask, $format)
{
if (empty($mask)) {
return ($format == 'comma') ? '' : array();
}
$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;
$amenityIds = '';
for ($i = 1; $i <= strlen($mask); $i++) {
$ch = substr($mask, $i, 1);
if ($ch == '1')
$amenityIds .= ($i) . ",";
}
if (strTolower($format) == 'comma')
return $amenityIds;
if (!empty($amenityIds)) {
$amenityIds = Zend_Controller_Action_HelperBroker::getStaticHelper('String')->cleanCommaSepString($amenityIds);
// $objAmenity = new Travel_Model_TblAmenities();
// $objAmenity->strCondition = " AND TB_Master_Accom_RoomAminity.AminityId IN ( ".$amenityIds." ) ";
// return $objAmenity->getRoomAmenitiesList();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND TB_Master_Accom_AccomAminity.AminityId IN ( " . $amenityIds . " ) ";
return $objHotel->getMasterAccomAminities();
}
return array();
}
public function getRoomAmenitiesFromMask($mask, $format)
{
if (empty($mask)) {
return ($format == 'comma') ? '' : array();
}
$objAmenity = new Travel_Model_TblAmenities();
$objAmenity->orderByField = 'AminityId';
$objAmenity->orderBy = 'ASC';
$arrRoomAmenities = $objAmenity->getRoomAmenitiesList();
$amenityIds = '';
for ($i = 1; $i <= strlen($mask); $i++) {
$ch = substr($mask, $i, 1);
if ($ch == '1')
$amenityIds .= ($i) . ",";
}
if (strTolower($format) == 'comma')
return $amenityIds;
if (!empty($amenityIds)) {
$amenityIds = Zend_Controller_Action_HelperBroker::getStaticHelper('String')->cleanCommaSepString($amenityIds);
$objAmenity = new Travel_Model_TblAmenities();
$objAmenity->strCondition = " AND TB_Master_Accom_RoomAminity.AminityId IN ( " . $amenityIds . " ) ";
return $objAmenity->getRoomAmenitiesList();
}
return array();
}
public function getRoomAmenities($AccomSysId, $ItemSysId, $format)
{
$sql = " SELECT AminitiesMask FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $AccomSysId . " "
. " AND ItemSysId = " . $ItemSysId;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
$objHotel = new Travel_Model_TblHotel();
return $objHotel->getAmenitiesFromMask($result[0]['AminitiesMask'], $format);
}
return array();
}
public function getAccomodationGroups()
{
$sql = " SELECT AccomGrSysId, Title, Details, Icon, Meta, ThumnailImg, DetailImg, AgeOfGroup, GrDOB, UpdateDate
,CreateDate, IsActive, IsMarkForDel FROM TB_IC_Accomdation_Group WHERE 1 = 1 ";
return $this->db->query($sql)->fetchAll();
}
public function getAccomodationEconomyType()
{
$sql = " SELECT EconomyType, Title, Icon, UpdatedDate, CreateDate, IsDelete, IsActive
FROM TB_Master_EconomyType WHERE 1 = 1 ";
return $this->db->query($sql)->fetchAll();
}
public function addAccomodation($insert, $hotel_chain)
{
try {
$this->db->beginTransaction();
$AccomGrSysId = $hotel_chain;
unset($insert['AccomGrSysId']);
$this->db->insert('TB_IC_Accomdation', $insert);
$intLastInsertId = $this->db->lastInsertId('TB_IC_Accomdation');
if (empty($intLastInsertId))
$this->db->rollBack();
$currentDate = date('Y-m-d H:i:s');
$accGroupInsert = array(
'AccomGrSysId' => $AccomGrSysId,
'AccomSysId' => $intLastInsertId,
'MemberSice' => $currentDate,
'UpdateDate' => $currentDate,
'CreateDate' => $currentDate,
'IsActive' => '1',
'IsMarkForDel' => '0'
);
$this->db->insert('TB_IC_Accomdation_Group_Map', $accGroupInsert);
$this->db->commit();
return $intLastInsertId;
} catch (Exception $ex) {
$this->db->rollBack();
die($ex->getMessage());
}
}
public function updateAccomodationInfo($insert, $hotelId)
{
try {
$where = array('AccomSysId =?' => $hotelId);
$this->db->update('TB_IC_Accomdation', $insert, $where);
} catch (Exception $ex) {
die($ex->getMessage());
}
}
public function getAccomodationRoomTypes($AccomSysId)
{
$sql = " SELECT DISTINCT TB_IC_Accomdation_Room.RoomType as RoomTypeId, TB_Master_RoomType.Title as RoomType FROM TB_IC_Accomdation_Room "
. " LEFT JOIN TB_Master_RoomType ON TB_IC_Accomdation_Room.RoomType = TB_Master_RoomType.RoomType"
. " WHERE TB_IC_Accomdation_Room.AccomSysId = " . $AccomSysId . " AND TB_Master_RoomType.IsMarkForDel = '0' "
. " AND TB_Master_RoomType.IsActive = '1' ";
// echo $sql;
return $this->db->query($sql)->fetchAll();
}
public function getMasterRoomType()
{
$sql = " SELECT RoomType as RoomTypeId , RoomGrMask, Title as RoomType, Icon, UpdateDate, CreateDate, IsMarkForDel, IsActive "
. " FROM TB_Master_RoomType WHERE IsMarkForDel = '0' ";
return $this->db->query($sql)->fetchAll();
}
// public function deleteMpInventoryAccom($InvnItemSysId) {
// $where = array('InvnItemSysId = ? ' => $InvnItemSysId);
// return $this->db->delete('TB_MP_Inventory_Accom', $where);
// }
public function addTermsConditions($data)
{
$this->db->insert('TB_IC_Accomdation_Artifect', $data);
return $this->db->lastInsertId('TB_IC_Accomdation_Artifect');
}
public function updateTermsConditions($AccomSysId, $data)
{
// $Writeup = $this->db->quoteInto($data['Writeup']);
$Writeup = $data['Writeup'];
$update = " UPDATE TB_IC_Accomdation_Artifect SET Writeup = '" . $Writeup . "' WHERE AccomSysId = " . $AccomSysId . " "
. " AND ArtifectType = " . ACCOM_ARTIFECT_TYPE_ID;
return $this->db->query($update);
}
public function isTermsNConditionsExists($AccomSysId)
{
$sql = " SELECT COUNT(*) as tot FROM TB_IC_Accomdation_Artifect WHERE AccomSysId = " . $AccomSysId . " AND "
. " ArtifectType = " . ACCOM_ARTIFECT_TYPE_ID;
$result = $this->db->query($sql)->fetch();
if ($result['tot'] > 0) {
return true;
}
return false;
}
public function getICAccomodationItemSysId($intAccomSysId, $RoomTypeId)
{
$sql = " SELECT TOP 1 ItemSysId FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $intAccomSysId . " "
. " AND RoomType = " . $RoomTypeId;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
return $result[0]['ItemSysId'];
}
return 0;
}
public function getMasterAccomAminities()
{
$sql = " SELECT AminityId, CatMask, Title, Icon, IsApprove, ApproveDate
,UpdateDate, CreateDate, IsMarkForDel, IsActive
FROM TB_Master_Accom_AccomAminity WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
if (!empty($this->orderByField)) {
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
} else {
$sql .= " ORDER BY Title ";
}
// echo $sql ;exit;
return $this->db->query($sql)->fetchAll();
}
public function isStandardMarkupExists($AgencySysId, $CustGrSysId)
{
$sql = " SELECT COUNT(*) AS tot FROM TB_MP_StdMarkup_Accom "
. " WHERE AgencySysId = " . $AgencySysId . " AND CustGrSysId = " . $CustGrSysId . " AND IsMarkForDel = '0' AND ItemSourceType='0' ";
$response = $this->db->query($sql)->fetch();
if ($response['tot'] > 0) {
return true;
}
return false;
}
public function isCustomizedMarkupExists($InvnItemSysId)
{
$sql = " SELECT COUNT(*) AS tot FROM TB_MP_Inventory_Accom_Alloc "
. " WHERE InvnItemSysId = " . $InvnItemSysId . " AND IsMarkForDel = '0' ";
$response = $this->db->query($sql)->fetch();
if ($response['tot'] > 0) {
return true;
}
return false;
}
public function getAccomoWherID($accomSysId)
{
$sql = "SELECT * FROM TB_IC_Accomdation_Artifect WHERE AccomSysId='" . $accomSysId . "'";
return $this->db->query($sql)->fetchAll();
}
public function getAccomodationbriefWherID($accomSysId)
{
$sql = "SELECT Brief as Details FROM TB_IC_Accomdation WHERE AccomSysId='" . $accomSysId . "'";
return $this->db->query($sql)->fetchAll();
}
public function getAlreadyAddedMarketPlaces($AgencySysId, $InvnItemSysId)
{
$sql = " SELECT MPType FROM TB_MP_Inventory_Accom_Alloc "
. " WHERE AgencySysId = " . $AgencySysId . " AND InvnItemSysId = " . $InvnItemSysId . " AND IsMarkForDel = '0' ";
$result = $this->db->query($sql)->fetchAll();
$response = array();
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = $row['MPType'];
}
}
return $response;
}
public function cloneMPInventoryAccom($InvnItemSysId)
{
$currentDate = date('Y-m-d H:i:s');
$sql = " INSERT INTO TB_MP_Inventory_Accom ( InventoryType, XRefAccoSysId, XRefItemSysId
,FromDate, ToDate, AgencySysId, SupplierSysId, Title, EconomyType, MealPlanType
,IxFixedDateInven, DailyRoomLimit, PurchasedQty, PublishQty, BlockQty
,SoldQty, ReturnQty, NetInHandQty, CostCurrency, TrxCurrency,IsPerPaxCostAllowed
,RateType,PerPaxCost,DOccupCost,SOccupCost,ExtraBedCost,ExtraWOBedCost
,NetDOccupCost, NetSOccupCost, NetExtraBedCost, NetExtraWOBedCost
,Tax, NetTax
,OtherCost
,SaleAmount
,DefaultPrice
,Brief
,IfAnyOffer
,OfferMsg
,OfferImage
,CreatorSysId
,CreateDate
,ApproveDate
,UpdateDate
,IsActive
,IsMarkForDelete
,IsApproved)
SELECT InventoryType, XRefAccoSysId, XRefItemSysId
,FromDate, ToDate, AgencySysId, SupplierSysId, Title, EconomyType, MealPlanType
,IxFixedDateInven, DailyRoomLimit, PurchasedQty, PublishQty, BlockQty
,SoldQty, ReturnQty, NetInHandQty, CostCurrency, TrxCurrency,IsPerPaxCostAllowed
,RateType,PerPaxCost,DOccupCost,SOccupCost,ExtraBedCost,ExtraWOBedCost
,NetDOccupCost, NetSOccupCost, NetExtraBedCost, NetExtraWOBedCost
,Tax,NetTax
,OtherCost
,SaleAmount
,DefaultPrice
,Brief
,IfAnyOffer
,OfferMsg
,OfferImage
,CreatorSysId
,'" . $currentDate . "'
,ApproveDate
,'" . $currentDate . "'
,'0'
,'0'
,'0' FROM TB_MP_Inventory_Accom WHERE InvnItemSysId = " . $InvnItemSysId;
$this->db->query($sql);
return $this->db->lastInsertId('TB_MP_Inventory_Accom');
}
public function updateMPInventoryAccom($InvnItemSysId, $data)
{
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
return $this->db->update('TB_MP_Inventory_Accom', $data, $where);
}
public function getAmenitiesFromMask($mask, $format)
{
if (empty($mask))
return array();
$objAmenity = new Travel_Model_TblAmenities();
$objAmenity->orderByField = 'AminityId';
$objAmenity->orderBy = 'ASC';
$arrRoomAmenities = $objAmenity->getRoomAmenitiesList();
$amenityIds = '';
for ($i = 1; $i <= strlen($mask); $i++) {
$ch = substr($mask, $i, 1);
if ($ch == '1')
$amenityIds .= ($i) . ",";
}
if (strTolower($format) == 'comma')
return $amenityIds;
if (!empty($amenityIds)) {
$amenityIds = Zend_Controller_Action_HelperBroker::getStaticHelper('String')->cleanCommaSepString($amenityIds);
$objAmenity = new Travel_Model_TblAmenities();
$objAmenity->strCondition = " AND TB_Master_Accom_RoomAminity.AminityId IN ( " . $amenityIds . " ) ";
return $objAmenity->getRoomAmenitiesList();
}
return array();
}
public function getTermsNConditions($AccomSysId)
{
if (!empty($AccomSysId)) {
$sql = " SELECT Writeup FROM TB_IC_Accomdation_Artifect WHERE AccomSysId = " . $AccomSysId . " "
. " AND ArtifectType = " . ACCOM_ARTIFECT_TYPE_ID;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
return $result[0]['Writeup'];
}
}
return '';
}
public function getBuyHotelAutoSuggest($keyword)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblCity();
$rowset = $objHotel->getCityListCondition($keyword);
//$hotel=$objHotel->getCityHotelList($id);
$respon = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
// print_r($row['CityId']);
$response[] = array('TBBCityId' => $row['TBBCityId'], 'CityId' => $row['CityId'], 'label' => $row['Title']);
}
}
//print_r($response); exit;
return $response;
}
public function getHotelAminityAutoSuggest($keyword = NULL)
{
$keyword = trim($keyword);
$response = array();
$sql = " SELECT AminityId, Title FROM TB_Master_Accom_AccomAminity WHERE 1 = 1 AND IsActive='1' "; //AND Title like '" .$keyword."%'" ;
$rowset = $this->db->query($sql)->fetchAll();
// $respon = array();
//
// if(count($rowset) > 0 ){
// foreach($rowset as $row) {
// // print_r($row['CityId']);
//
// $response[] =array('AminityId'=>$row['AminityId'],'label'=>$row['Title']);
//
// }
//
//
// }
// echo '<pre>';
// print_r($rowset); exit;
return $rowset;
}
public function getAccomodation($AccomSysId)
{
$sql = " SELECT Title as HotelName, Address, Rating,Stars, AccoAminitiesMask FROM TB_IC_Accomdation where AccomSysId = " . $AccomSysId;
$data = $this->db->query($sql)->fetch();
return $data;
}
public function getAccomodationHotelList($where)
{
$sql = "select CASE WHEN MIN(IAC.DOccupPrice) IS NULL THEN MIN(IA.DOccupCost) ELSE MIN(IAC.DOccupPrice) END
[DOccupCost],IA.XRefAccoSysId,IA.Title, A.Title AS HotelName,IA.InvnItemSysId, IA.SuppliersysId,IA.InventoryType
,A.ShortName,A.Location,A.DetailImg,A.Stars,A.Rating,A.Address,A.Popularity,A.AwardRecognizations,A.Brief,
A.AccomSysId,A.AccoAminitiesMask, A.RoomCounts,A.CheckinTime,A.CheckOutTime,MA.DOccupMarkUp,
MA.MarkUpType,IA.CostCurrency
from TB_IC_Accomdation A Left outer join TB_MP_Inventory_Accom IA on
IA.XRefAccoSysId = A.AccomSysId
LEFT Outer Join TB_MP_Inventory_Accom_Alloc IAC
on IA.InvnItemSysId = IAC.InvnItemSysId
left outer join TB_MP_StdMarkup_Accom MA on
IAC.AgencySysId=MA.AgencySysId
AND IAC.MPType=MA.MPType" . $where;
echo $sql;
exit;
return $this->db->query($sql)->fetchAll();
}
public function addStdMarkupAccom($data = array())
{
try {
$select = $this->db->select()
->from("TB_MP_StdMarkup_Accom", array('count(*) AS totalRecords'))
->where("ItemSourceType =?", 0)
->where("AgencySysId =?", $data['AgencySysId'])
->where("MpType =?", $data['MPType']);
$resultData = $this->db->fetchOne($select);
// echo $resultData; die;
if ($resultData == 1) {
$where['AgencySysId = ?'] = $data['AgencySysId'];
$where['MpType = ?'] = $data['MPType'];
return $this->db->update('TB_MP_StdMarkup_Accom', $data, $where);
} else {
$this->db->insert('TB_MP_StdMarkup_Accom', $data);
return $this->db->lastInsertId('TB_MP_StdMarkup_Accom');
}
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
/**
* SetActiveDeactive. To set IsActive/IsDeactive record.
*
* @param string $whereCondition
* @param string $tableName
* @param int $isActive
* @return int
*/
public function setActiveDeactive($whereCondition, $tableName, $isActive = 0)
{
try {
$isChanged = $this->db->update($tableName, array('IsActive' => $isActive), $whereCondition);
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
return @$isChanged ? $isChanged : 0;
}
public function getRoomIdByRoomType($AccomSysId, $roomType)
{
$sql = " SELECT ItemSysId FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $AccomSysId . " AND RoomType = " . $roomType;
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
return $result[0]['ItemSysId'];
}
return 0;
}
public function getPackageHotelList($packageId)
{
$sql = " SELECT InvnItemSysId, TB_IC_Accomdation.Title as HotelName, InventoryType
,XRefAccoSysId, XRefItemSysId, TB_MP_Inventory_Accom.SupplierSysId, TB_MP_Inventory_Accom.Title,
TB_MP_Inventory_Accom.EconomyType,TB_IC_Accomdation.Stars as StarRating, TB_IC_Accomdation.Address as Address,
TB_Master_Currency.Symbol as CurrencySymbol, TB_Master_RoomType.Title as RoomType
,MealPlanType, IxFixedDateInven, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate, DailyRoomLimit, PurchasedQty,
PublishQty, BlockQty, TB_Master_Accom_MealType.Title as MealType, RateType, Tax
,SoldQty, ReturnQty, NetInHandQty, IsPerPaxCostAllowed, PerPaxCost, DOccupCost, SOccupCost, TB_IC_Supplier.SupplierName as SupplierName,
NetDOccupCost, NetSOccupCost, NetExtraBedCost, NetExtraWOBedCost, NetTax, RackCommission,
ExtraBedCost, ExtraWOBedCost, OtherCost, SaleAmount, DefaultPrice, TB_MP_Inventory_Accom.Brief, IfAnyOffer, OfferMsg
, OfferImage, CreatorSysId, TB_MP_Inventory_Accom.CreateDate, TB_MP_Inventory_Accom.ApproveDate, TB_MP_Inventory_Accom.UpdateDate,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved,
TB_TravelPlan_Itenary_Accom.seqId
FROM TB_MP_Inventory_Accom
INNER JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Accom_MealType ON TB_MP_Inventory_Accom.MealPlanType = TB_Master_Accom_MealType.MealTypeId
LEFT JOIN TB_IC_Supplier ON TB_MP_Inventory_Accom.SupplierSysId = TB_IC_Supplier.SupplierSysId
LEFT JOIN TB_Master_Currency ON TB_MP_Inventory_Accom.CostCurrency = TB_Master_Currency.CurrencyType
LEFT JOIN TB_Master_RoomType ON TB_MP_Inventory_Accom.EconomyType = TB_Master_RoomType.RoomType
LEFT JOIN TB_TravelPlan_Itenary_Accom ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_TravelPlan_Itenary_Accom.AccoSysId
WHERE TB_MP_Inventory_Accom.IsMarkForDelete = '0' AND TB_MP_Inventory_Accom.IsActive = '0' AND TB_TravelPlan_Itenary_Accom.TPIntSysId = '" . @$packageId . "' ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getRoomItemSysID($AccomSysId, $intRoomTypeId)
{
$sql = " SELECT ItemSysId,AccomSysId FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $AccomSysId . " AND RoomType = " . $intRoomTypeId;
$result = $this->db->query($sql)->fetch();
$ItemSysId = $result['ItemSysId'];
return $ItemSysId;
}
public function gethtlInventryInduData($accomSysId, $invnItemSysId)
{
$sql = "select * from TB_MP_Inventory_Accom where InvnItemSysId='" . $invnItemSysId . "'"
. "And XRefAccoSysId='" . $accomSysId . "'";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getHtlInventyData($accomSysId, $invnItemSysId)
{
$sql = "Select t1.*, t2.*,t1.Title as HotelName, t3.SupplierName From TB_IC_Accomdation as t1 LEFT JOIN TB_MP_Inventory_Accom AS t2 "
. " on t1.AccomSysId = t2.XRefAccoSysId LEFT JOIN TB_IC_Supplier AS t3 "
. "on t2.SupplierSysId=t3.SupplierSysId where t1.AccomSysId='" . $accomSysId . "'"
. " AND t2.InvnItemSysId='" . $invnItemSysId . "'";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getBuyHotelCityAutoSuggest($keyword)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblCity();
$rowset = $objHotel->getCityListWithCountryDetail($keyword);
$respon = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = array('TBBCityId' => $row['TBBCityId'], 'CityId' => $row['CityId'], 'CityName' => stripslashes($row['cityTitle']), 'label' => $row['cityTitle'] . '(' . $row['countryTitle'] . ')', 'ContId' => $row['ContId'], 'countryCode' => trim($row['countryCode']), 'countryTitle' => trim($row['countryTitle']));
}
}
return $response;
}
public function getAutosuggestForPackage($keyword)
{
$keyword = trim($keyword);
$response = array();
$sql = "SELECT * FROM TB_MASTER_VIEW_PKGSEARCH_STRING WHERE Title like '%" . $keyword . "%'";
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
foreach ($result as $row) {
if ($row['TypeTitle'] == 'state' || $row['TypeTitle'] == 'city') {
$response[] = array('CityId' => $row['Id'], 'CityName' => stripslashes($row['Title']), 'Title' => $row['Title'] . '(' . trim($row['CountryName']) . ')', 'label' => $row['Title'] . '(' . $row['CountryName'] . ')', 'CountryId' => $row['CountryId']);
} else {
$response[] = array('CityId' => $row['Id'], 'CityName' => stripslashes($row['Title']), 'Title' => $row['Title'], 'label' => $row['Title'], 'CountryId' => $row['CountryId']);
}
}
}
return $response;
}
public function getAutosuggestForPackageForMobile($keyword)
{
$keyword = trim($keyword);
$response = array();
$where = '';
if ($keyword != '') {
$where = "WHERE Title like '%" . $keyword . "%'";
}
$sql = "SELECT * FROM TB_MASTER_VIEW_PKGSEARCH_STRING $where";
$response['data'][] = array('CityId' => '', 'CityName' => '', 'Title' => '', 'label' => 'Select', 'CountryId' => '');
$result = $this->db->query($sql)->fetchAll();
if (count($result) > 0) {
foreach ($result as $row) {
if ($row['TypeTitle'] == 'state' || $row['TypeTitle'] == 'city') {
$response['data'][] = array('CityId' => $row['Id'], 'CityName' => stripslashes($row['Title']), 'Title' => $row['Title'] . '(' . $row['CountryName'] . ')', 'label' => $row['Title'] . '(' . $row['CountryName'] . ')', 'CountryId' => $row['CountryId']);
} else {
$response['data'][] = array('CityId' => $row['Id'], 'CityName' => stripslashes($row['Title']), 'Title' => $row['Title'], 'label' => $row['Title']);
}
}
}
return $response;
}
public function gethotellistDb($con)
{
$sql = "select A.UpdateDate,A.CurrencyType,A.MinPublishedFare ,A.ResultIndex,A.TokenId,A.TraceId,A.Title as HotelName,A.ShortName,A.Location,A.DetailImg,A.Stars,A.Rating,A.Address,
A.Popularity,A.AwardRecognizations,A.Brief,A.AccomSysId,A.AccoAminitiesMask,A.RoomCounts,A.CheckinTime,
A.CheckOutTime, R.AwardURL FROM TB_IC_Accomdation A LEFT JOIN TB_IC_Accomdation_Awards R ON A.AccomSysId=R.AccomSysId WHERE " . $con;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function gethotellistDbupdated($con)
{
$updatesession = new Zend_Session_Namespace('updatesession');
$sql = "select P.Title,A.UpdateDate,A.CurrencyType,A.MinPublishedFare ,A.ResultIndex,A.TokenId,A.TraceId,A.Title as HotelName,
A.ShortName,A.Location,A.DetailImg,A.Stars,A.Rating,A.Address,
A.Popularity,A.AwardRecognizations,A.Brief,A.AccomSysId,A.AccoAminitiesMask,A.RoomCounts,A.CheckinTime,
A.CheckOutTime, R.AwardURL FROM TB_IC_Accomdation A LEFT JOIN TB_IC_Accomdation_Awards R ON A.AccomSysId=R.AccomSysId
LEFT JOIN TB_Master_PropertyType P ON P.PropType=A.PropertyType
WHERE A.UpdateDate>='" . $updatesession->updatesessitime . "' AND " . $con;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function gethotelResultIndex($hotlCode)
{
$sql = "select ResultIndex FROM TB_IC_Accomdation WHERE ShortName='" . $hotlCode . "'";
$result = $this->db->query($sql)->fetchAll();
foreach ($result as $htlCd) {
$htlCd = $htlCd['ResultIndex'];
}
return $htlCd;
}
public function gethotelprice($accomsysid)
{
$select = $this->db->select();
$select = $this->db->select();
$selectedfields = array('SOccupCost', 'DOccupCost', 'ExtraBedCost', 'ExtraWOBedCost');
$supplierdetails = array('SupplierName');
$select->from(array('tbl' => "TB_MP_Inventory_Accom"), $selectedfields);
$select->joinInner(array('tb2' => "TB_IC_Supplier"), 'tbl.SupplierSysId = tb2.SupplierSysId', $supplierdetails);
$select->where("tbl.XRefAccoSysId = ?", $accomsysid);
$result = $this->db->fetchAll($select);
return $result;
}
public function GetSelectedHotel($tpintsysID)
{
$select = $this->db->select();
$selectedfields = array('TPIntSysId', 'AccoSysId');
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Accom"), $selectedfields);
$select->where("tbl.TPIntSysId = ?", $tpintsysID);
$select->where("tbl.IsMarkForDel = ?", 0);
//echo $select;
$result = $this->db->fetchAll($select);
return $result;
}
public function GetSelectedHotelBYO($tpintsysID, $catID = '')
{
$select = $this->db->select();
$selectedfields = array('TPIntSysId', 'AccoSysId', 'InvnItemSysId', 'MealPlanType', 'RoomType', 'SupplierSysId');
$select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"), $selectedfields);
$select->where("tbl.TPIntSysId = ?", $tpintsysID);
$select->where("tbl.BYOPackType = ?", $catID);
$select->where("tbl.IsMarkForDel = ?", 0);
//echo $select;
$result = $this->db->fetchAll($select);
return $result;
}
/* Added By Pardeep Panchal Starts */
public function getHotelSearchResultList()
{
//echo "jhgfdsgdsgfjdsgfjds";exit;
if (!empty($this->strResultsSource) && $this->strResultsSource === "BOTH") {
if ($this->strSelectedView == 'Count') {
$arrAccomdation = array('COUNT(AccomSysId) AS total', 'min(MinPublishedFare) as minPrice,max(MinPublishedFare) as maxPrice', new Zend_Db_Expr('1 as countType'));
$arrAccomdationInventory = array();
$arrAccomdationInven = array('COUNT(AccomSysId) AS total', 'min(MinPublishedFare) as minPrice,max(MinPublishedFare) as maxPrice', new Zend_Db_Expr('2 as countType'));
} else {
$arrAccomdation = array(
'UpdateDate',
'CurrencyType',
'ResultIndex',
'TokenId',
'TraceId',
'Title as HotelName',
'ShortName',
'Location',
'DetailImg',
'Stars',
'Rating',
'Address',
'AwardURL',
'Popularity',
'AwardRecognizations',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'CheckinTime',
//'CheckOutTime','EconomyType',new Zend_Db_Expr('CAST(t1.MinPublishedFare AS Varchar(500)) as MinPublishedFare'),'MinCommissionEarned','IsAgentSpec as XRefAccoSysId','ICSourceSysId',new Zend_Db_Expr('0 as InvnItemSysId'),new Zend_Db_Expr('1 as SupplierSysId'),
'CheckOutTime',
'EconomyType',
't1.MinPublishedFare',
'MinCommissionEarned',
'IsAgentSpec as XRefAccoSysId',
'ICSourceSysId',
new Zend_Db_Expr('0 as InvnItemSysId'),
new Zend_Db_Expr('1 as SupplierSysId'),
new Zend_Db_Expr('0 as NetDOccupCost'),
new Zend_Db_Expr('0 as NetSOccupCost'),
new Zend_Db_Expr("'' as EconomyTypeTitle"),
new Zend_Db_Expr("'' as MealPlanTypeTitle")
);
$arrAccomdationInven = array(
'UpdateDate',
'CurrencyType',
new Zend_Db_Expr("'' as ResultIndex"),
new Zend_Db_Expr("'' as TokenId"),
new Zend_Db_Expr("'' as TraceId"),
'Title as HotelName',
'ShortName',
new Zend_Db_Expr('0 as Location'),
'DetailImg',
'Stars',
'Rating',
'Address',
'AwardURL',
'Popularity',
'AwardRecognizations',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'CheckinTime',
'CheckOutTime',
'EconomyType'
);
if ($this->strRoomPaxInfo > 0) {
$strRoomPaxInfo = $this->strRoomPaxInfo;
}
//$arrAccomdationInventory = array(new Zend_Db_Expr('dbo.CalHotelInvPrice(t3.InvnItemSysId,t3.AgencySysId,1,1) as MinPublishedFare'),new Zend_Db_Expr('0 as MinCommissionEarned'),'XRefAccoSysId','InventoryType as ICSourceSysId','InvnItemSysId','SupplierSysId','NetDOccupCost','NetSOccupCost');
//dbo.CalcHotelInvPrice(49,3,1,'1-EB,2-N,1-WOB')
$arrAccomdationInventory = array(new Zend_Db_Expr('0 as MinPublishedFare'), new Zend_Db_Expr('0 as MinCommissionEarned'), 'XRefAccoSysId', 'InventoryType as ICSourceSysId', 'InvnItemSysId', 'SupplierSysId', 'NetDOccupCost', 'NetSOccupCost');
}
// For Api Hotels DB Results...
$select1 = $this->db->select();
$select1->from(array('t1' => 'TB_IC_Accomdation_API'), $arrAccomdation);
$select1->where("t1.Title != ''");
$select1->where("t1.IsActive = ?", 1);
$select1->where("t1.IsAgentSpec = ?", 0);
$select1->where("t1.IsExpired = ?", 0);
$select1->where("t1.IsMarkForDel = ?", 0);
if (!empty($this->strCheckInDateTime) && !empty($this->strCheckOutDateTime)) {
$select1->where("t1.CheckinTime = ?", $this->strCheckInDateTime);
$select1->where("t1.CheckOutTime = ?", $this->strCheckOutDateTime);
}
if ($this->intTotalPaxCount > 0) {
$select1->where("t1.MaxPaxCount = ?", $this->intTotalPaxCount);
}
if ($this->intTotalRoomCount > 0) {
$select1->where("t1.RoomCounts = ?", $this->intTotalRoomCount);
}
if ($this->intTotalNightCount > 0) {
$select1->where("t1.NightCount = ?", $this->intTotalNightCount);
}
if (!empty($this->strNationality)) {
$select1->where("t1.Nationality = ?", $this->strNationality);
}
if (!empty($this->strCondition)) {
$select1->where($this->strCondition);
}
//echo $select1; exit;
// For Inventory Hotels DB Results...
$select2 = $this->db->select();
$select2->from(array('t1' => 'TB_IC_Accomdation'), $arrAccomdationInven);
$select->joinInner(array('t2' => 'Tb_MP_Accomdation_Room_Inventory'), 't2.AccomSysId = t1.AccomSysId ', array('FromDate', 'RoomSysId'));
$select->joinInner(array('t3' => 'TB_MP_Inventory_Accom'), 't3.RoomInventorySysId = t2.id', $arrAccomdationInventory);
if ($this->strSelectedView != 'Count') {
$select2->joinLeft(array('t4' => 'TB_Master_RoomType'), 't4.RoomType = t3.EconomyType ', array("Title as EconomyTypeTitle"));
$select2->joinLeft(array('t5' => 'TB_Master_Accom_MealType'), 't5.MealTypeId = t3.MealPlanType', array("Title as MealPlanTypeTitle"));
}
$select2->where("t1.Title != ''");
//$select2->where("t1.IsActive = ?",1);
$select2->where("t2.IsSaleStart = ?", 1);
$select2->where("t2.IsMarkForDelete = ?", 0);
$select2->where("t2.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
if (!empty($this->strCondition)) {
$select2->where($this->strCondition);
}
if (!empty($this->strCheckInDate) && !empty($this->strCheckOutDate)) {
$select2->where(" t3.FromDate = '" . trim($this->strCheckInDate) . "'"); // Added By Pardeep Panchal After Disscussion with GG...
//$select2->where(" t3.ToDate <= '".$this->strCheckOutDate."'");
}
if ($this->strSelectedView != 'Count') {
$select = $this->db->select()
->union(array($select1, $select2), Zend_Db_Select::SQL_UNION_ALL);
} else {
$select = $this->db->select()
->union(array($select1, $select2));
//$select = $this->db->select($select1);
}
// echo $select;exit;
if ($this->strSelectedView != 'Count') {
if (!empty($this->onRequest)) {
$select->order('InvnItemSysId DESC');
} else {
if (!empty($this->orderBy)) {
$select->order('MinPublishedFare ' . $this->orderBy);
} else {
$select->order(array('MinPublishedFare ASC'));
}
}
} else {
$select->order(array('countType ASC'));
}
// echo $select."===P"; exit;
if ($this->strSelectedView == 'Count') {
$result = $this->db->fetchAll($select);
$arrResult = array();
//echo "<pre>";print_r($result);exit;
if (count($result) > 0) {
$arrResult['minPrice'] = round($result[0]['minPrice']);
$arrResult['maxPrice'] = round($result[0]['maxPrice']);
foreach ($result as $res) {
//if($result[0]['countType'] == 1 && $result[0]['total'] == 0){ // Condition For getting Total Count of results Inventory/API
//$arrResult['total'] = 0;
//}else{
@$arrResult['total'] += $res['total'];
//}
}
}
return $arrResult;
} else {
$result = $this->db->fetchAll($select);
return $result;
}
} else if (!empty($this->strResultsSource) && $this->strResultsSource === "API") { // Only API Results...
return $this->getHotelSearchResultListApiOnly($this->strCondition);
} else if (!empty($this->strResultsSource) && $this->strResultsSource === "INV") { // Only Inventory Results..
return $this->getHotelSearchResultListInventoryOnly($this->strCondition);
}
}
public function getHotelSearchResultListApiOnly($strCondition)
{
//echo $strCondition;exit;
if ($this->strSelectedView == 'Count') {
$arrAccomdation = array('COUNT(AccomSysId) AS total', 'min(MinPublishedFare) as minPrice,max(MinPublishedFare) as maxPrice', new Zend_Db_Expr('1 as countType'));
} else {
$arrAccomdation = array(
'UpdateDate',
'CurrencyType',
'ResultIndex',
'TokenId',
'TraceId',
'Title as HotelName',
'ShortName',
'Location',
'DetailImg',
'Stars',
'Rating',
'Address',
'AwardURL',
'Popularity',
'AwardRecognizations',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'CheckinTime',
'CheckOutTime',
'MinPublishedFare',
'MinCommissionEarned',
'IsAgentSpec as XRefAccoSysId',
'ICSourceSysId',
new Zend_Db_Expr('0 as InvnItemSysId'),
new Zend_Db_Expr('1 as SupplierSysId'),
new Zend_Db_Expr('0 as NetDOccupCost'),
new Zend_Db_Expr('0 as NetSOccupCost')
);
}
// For Api Hotels DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrAccomdation);
$select->where("t1.Title != ''");
$select->where("t1.IsActive = ?", 1);
$select->where("t1.IsAgentSpec = ?", 0);
$select->where("t1.IsExpired = ?", 0);
$select->where("t1.IsMarkForDel = ?", 0);
if ($this->intTotalPaxCount > 0) {
$select->where("t1.MaxPaxCount = ?", $this->intTotalPaxCount);
}
if ($this->intTotalRoomCount > 0) {
$select->where("t1.RoomCounts = ?", $this->intTotalRoomCount);
}
if ($this->intTotalNightCount > 0) {
$select->where("t1.NightCount = ?", $this->intTotalNightCount);
}
if (!empty($this->strNationality)) {
$select->where("t1.Nationality = ?", $this->strNationality);
}
if (!empty($strCondition)) {
$select->where($strCondition);
}
//echo $select;exit;
if ($this->strSelectedView != 'Count') {
if (!empty($this->onRequest)) {
$select->order('InvnItemSysId DESC');
} else {
if (!empty($this->orderBy)) {
$select->order('MinPublishedFare ' . $this->orderBy);
} else {
$select->order(array('t1.MinPublishedFare ASC'));
}
}
} else {
$select->order(array('countType ASC'));
}
if ($this->strSelectedView == 'Count') {
$result = $this->db->fetchAll($select);
$arrResult = array();
$arrResult['minPrice'] = 0;
$arrResult['maxPrice'] = 0;
$arrResult['total'] = 0;
if (count($result) > 0) {
foreach ($result as $res) {
$arrResult['minPrice'] += round($res['minPrice']);
$arrResult['maxPrice'] += round($res['maxPrice']);
if ($result[0]['countType'] == 1 && $result[0]['total'] == 0) {
$arrResult['total'] = 0;
} else {
$arrResult['total'] += $res['total'];
}
}
}
return $arrResult;
} else {
$result = $this->db->fetchAll($select);
return $result;
}
}
public function getHotelSearchResultListInventoryOnly($strCondition)
{
if ($this->strSelectedView == 'Count') {
$arrAccomdationInventory = array();
$arrAccomdationInven = array('COUNT(AccomSysId) AS total', 'min(MinPublishedFare) as minPrice,max(MinPublishedFare) as maxPrice', new Zend_Db_Expr('2 as countType'));
} else {
$arrAccomdationInven = array(
'UpdateDate',
'CurrencyType',
new Zend_Db_Expr("'' as ResultIndex"),
new Zend_Db_Expr("'' as TokenId"),
new Zend_Db_Expr("'' as TraceId"),
'Title as HotelName',
'ShortName',
new Zend_Db_Expr("'' as Location"),
'DetailImg',
'Stars',
'Rating',
'Address',
'AwardURL',
'Popularity',
'AwardRecognizations',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'CheckinTime',
'CheckOutTime',
'EconomyType'
);
if ($this->intTotalPaxCount > 0) {
$intTotalPaxCount = $this->intTotalPaxCount;
}
$strRoomPaxInfo = "1-N";
$arrAccomdationInventory = array(new Zend_Db_Expr('0 as MinCommissionEarned'), 'XRefAccoSysId', 'InvnItemSysId', 'SupplierSysId', 'NetDOccupCost', 'NetSOccupCost', 'NetExtraBedCost', 'NetExtraWOBedCost');
//$arrAccomdationInventoryAccomAlloc = array('MarkUp');
}
// For Inventory Hotels DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation'), $arrAccomdationInven);
$select->joinInner(array('t2' => 'Tb_MP_Accomdation_Room_Inventory'), 't2.AccomSysId = t1.AccomSysId ', array('FromDate', 'RoomSysId'));
$select->joinInner(array('t3' => 'TB_MP_Inventory_Accom'), 't3.RoomInventorySysId = t2.id', $arrAccomdationInventory);
if ($this->strSelectedView != 'Count') {
$select->joinLeft(array('t4' => 'TB_Master_RoomType'), 't4.RoomType = t2.RoomSysId ', array("Title as EconomyTypeTitle", "RoomType as RoomTypeId"));
$select->joinLeft(array('t5' => 'TB_Master_Accom_MealType'), 't5.MealTypeId = t3.MealPlanType', array("Title as MealPlanTypeTitle", "MealTypeId"));
$select->joinLeft(array('t6' => 'TB_IC_Supplier'), 't3.SupplierSysId = t6.SupplierSysId', array("SupplierName"));
$select->joinLeft(array('t7' => 'TB_Master_Currency'), 't7.CurrencyType = t3.TrxCurrency', array('symbol'));
}
$select->where("t1.Title != ''");
$select->where("t1.IsActive = ?", 1);
$select->where("t2.IsActive = ?", 1);
$select->where("t2.IsSaleStart = ?", 1);
$select->where("t2.IsMarkForDelete = ?", 0);
$select->where("t2.IsMarkForDelete = ?", 0);
$select->where("t2.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
if (!empty($strCondition)) {
$select->where($strCondition);
}
if (!empty(trim($this->strCheckInDate)) && !empty(trim($this->strCheckOutDate))) {
$select->where("(t2.FromDate >= '" . trim($this->strCheckInDate) . "' AND t2.FromDate < '" . trim($this->strCheckOutDate) . "' AND t1.HotelRateType = 1) OR (('" . trim($this->strCheckInDate) . "' BETWEEN t2.FromDate AND t2.ToDate OR '" . trim($this->strCheckOutDate) . "' BETWEEN t2.FromDate AND t2.ToDate OR t2.FromDate BETWEEN '" . trim($this->strCheckInDate) . "' AND '" . trim($this->strCheckOutDate) . "' OR t2.ToDate BETWEEN '" . trim($this->strCheckInDate) . "' AND '" . trim($this->strCheckOutDate) . "') AND t1.HotelRateType = 2)");
}
//echo $select;
if ($this->strSelectedView != 'Count') {
if (!empty($this->onRequest)) {
$select->order('InvnItemSysId DESC');
} else {
if (!empty($this->orderBy)) {
$select->order('MinPublishedFare ' . $this->orderBy);
} else {
$select->order(array('t1.MinPublishedFare ASC'));
}
}
} else {
$select->order(array('countType ASC'));
}
if ($this->strSelectedView == 'Count') {
$result = $this->db->fetchAll($select);
$arrResult = array();
$arrResult['minPrice'] = 0;
$arrResult['maxPrice'] = 0;
$arrResult['total'] = 0;
if (count($result) > 0) {
foreach ($result as $res) {
$arrResult['minPrice'] += round($res['minPrice']);
$arrResult['maxPrice'] += round($res['maxPrice']);
if ($result[0]['countType'] == 1 && $result[0]['total'] == 0) {
$arrResult['total'] = 0;
} else {
$arrResult['total'] += $res['total'];
}
}
}
return $arrResult;
} else {
// echo $select; exit;
$result = $this->db->fetchAll($select);
// echo "<pre>";print_r($result);exit('sss');
$hotelArray = array();
$i = 0;
foreach ($result as $key => $val) {
$keyVal = trim($val['AccomSysId'] . trim($val['SupplierSysId']));
$hotelArray[$keyVal]["AccoAminitiesMask"] = $val['AccoAminitiesMask'];
$hotelArray[$keyVal]["SupplierName"] = $val['SupplierName'];
$hotelArray[$keyVal]["DetailImg"] = trim($val['DetailImg']);
$hotelArray[$keyVal]["AccomSysId"] = $val['AccomSysId'];
$hotelArray[$keyVal]["FromDate"] = $val['FromDate'];
$hotelArray[$keyVal]["InvnItemSysId"] = $val['InvnItemSysId'];
$hotelArray[$keyVal]["HotelName"] = $val['HotelName'];
$hotelArray[$keyVal]["XRefAccoSysId"] = $val['XRefAccoSysId'];
$hotelArray[$keyVal]["AwardURL"] = $val['AwardURL'];
$hotelArray[$keyVal]["Location"] = $val['Location'];
$hotelArray[$keyVal]["SupplierSysId"] = $val['SupplierSysId'];
$hotelArray[$keyVal]["Stars"] = $val['Stars'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['MealTypeId'] = $val['MealTypeId'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['MealPlanTypeTitle'] = $val['MealPlanTypeTitle'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['RoomTypeId'] = $val['RoomTypeId'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['EconomyTypeTitle'] = $val['EconomyTypeTitle'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['InvnItemSysId'] = $val['InvnItemSysId'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['NetDOccupCost'] = $val['NetDOccupCost'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['price'][$i] = $val['NetDOccupCost'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['EconomyTypeTitle'] = $val['EconomyTypeTitle'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['MealPlanTypeTitle'] = $val['MealPlanTypeTitle'];
$hotelArray[$keyVal]["EconomyType"][$val['RoomTypeId']]["MealPlanTypeTitle"][$val['MealTypeId']]['currency'] = $val['symbol'];
$i++;
}
// echo "<pre>";print_r($hotelArray);exit;
return $hotelArray;
}
}
public function getTempHotelSearchResultList()
{ // For showing Results in background only....
$arrAccomdation = array(
'UpdateDate',
'CurrencyType',
'ResultIndex',
'TokenId',
'TraceId',
'Title as HotelName',
'ShortName',
'Location',
'DetailImg',
'Stars',
'Rating',
'Address',
'AwardURL',
'Popularity',
'AwardRecognizations',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'CheckinTime',
'CheckOutTime',
'MinPublishedFare',
'MinCommissionEarned'
);
// For Api Hotels DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrAccomdation);
$select->where("t1.Title != ''");
$select->where("t1.IsActive = ?", 1);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
$select->limitPage(1, 5);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAccomdationImages($intGTXCityId)
{
$arrAccomdationImages = array('ImageURL as Details', 'HotelCode');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_TBQ_SImages'), $arrAccomdationImages);
$select->where("t1.CitySysId = ? ", $intGTXCityId);
$select->where('t1.IsActive = ? ', 1);
//$select->where('t1.ImageURL != ? ',"http://www.travelboutiqueonline.com/Images/HotelNA.jpg");
$result = $this->db->fetchAll($select);
//echo $select; exit;
return $result;
}
public function getAccomdationImagesByAccomSysId($intAccomSysId)
{
$arrAccomdationImages = array('ImageURL as Details', 'HotelCode');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_TBQ_SImages'), $arrAccomdationImages);
$select->where("t1.AccomSysId = ? ", $intAccomSysId);
$select->where('t1.IsActive = ? ', 1);
$result = $this->db->fetchAll($select);
//echo $select; exit;
return $result;
}
public function getHotelMappedAmenities($hotelcode)
{
$sql = " SELECT TOP 1 AccoAminitiesMask FROM TB_IC_Accomdation_API WHERE ShortName ='" . $hotelcode . "' ORDER BY AccomSysId DESC";
$htmAminityPop = '';
$result = $this->db->query($sql)->fetchAll();
foreach ($result as $val) {
$hotelAminity = $val['AccoAminitiesMask'];
}
//$hotelAminity=substr($hotelAminity,3);
$hotelAminity = substr($hotelAminity, 1);
//echo $hotelAminity; exit;
$MstAminitysql = " SELECT AminityId, Title FROM TB_Master_Accom_AccomAminity Where IsActive = 1 "; //AND Title like '" .$keyword."%'" ;
$rowsetMst = $this->db->query($MstAminitysql)->fetchAll();
foreach ($rowsetMst as $val) {
$aminity[] = $val['Title'];
}
//echo print_r($aminity);
if ($hotelAminity != '') {
$htlaminiti = str_split($hotelAminity);
//unset($htlaminiti[0]);
$aminitiesArray = array_values($htlaminiti);
//print_r($aminitiesArray);
$count = min(count($aminity), count($aminitiesArray));
$amitnity = array_combine($aminity, $aminitiesArray);
$s = 0;
foreach ($amitnity as $key => $value) {
if ($value == 1) {
$htmAminityPop[$s] = $key;
$s++;
}
}
//print_r($resutl);
return $htmAminityPop;
}
}
public function insertApiHotels($data)
{
$this->db->insert('TB_IC_Accomdation_API', $data);
$intLastInsertedId = $this->db->lastInsertId('TB_IC_Accomdation_API');
return $intLastInsertedId;
}
public function insertApiRoomType($data)
{
$this->db->insert('TB_Master_RoomType', $data);
$intLastInsertedId = $this->db->lastInsertId('TB_Master_RoomType');
return $intLastInsertedId;
}
public function deleteApiHotelsImages($intAccomSysId)
{
$where = array("AccomSysId = ?" => $intAccomSysId);
$this->db->delete('TB_IC_Accomdation_TBQ_SImages', $where);
}
public function countApiHotelsImages($strHotelCode)
{
$arrSelect = array('AccomSysId');
$select = $this->db->select();
$select->from('TB_IC_Accomdation_TBQ_SImages', $arrSelect);
$select->where("HotelCode = ?", $strHotelCode);
$result = $this->db->fetchAll($select);
return count($result);
}
public function insertApiHotelsImages($imgData)
{
$this->db->insert('TB_IC_Accomdation_TBQ_SImages', $imgData);
$intLastInsertedId = $this->db->lastInsertId('TB_IC_Accomdation_TBQ_SImages');
return $intLastInsertedId;
}
public function TB_IC_Accomdation_Images($imgData)
{
$this->db->insert('TB_IC_Accomdation_Images', $imgData);
$intLastInsertedId = $this->db->lastInsertId('TB_IC_Accomdation_Images');
return $intLastInsertedId;
}
public function checkIfApiHotelExists($hotelCode, $hotelSource, $intAPICityId)
{
$arrHotel = array('AccomSysId');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation'), $arrHotel);
$select->where("LTRIM(RTRIM(t1.ShortName)) = ? ", $hotelCode);
$select->where("t1.ICSourceSysId = ? ", $hotelSource);
$select->where("t1.APICityId = ? ", $intAPICityId);
$select->limit(1);
$result = $this->db->fetchRow($select);
//print_r($result);exit;
if (!empty($result['AccomSysId'])) {
return 1;
} else {
return 0;
}
//return $result['AccomSysId'];
}
public function updateApiHotelsInfo($data, $where)
{ // Update if hotels already exists in Db while API Search...
$this->db->update('TB_IC_Accomdation', $data, $where);
}
public function updateTraceIdExpired($data, $where)
{
$this->db->update('TB_IC_Accomdation_API', $data, $where);
}
public function updateApiHotelAmenities($data, $where)
{
$this->db->update('TB_IC_Accomdation_API', $data, $where);
}
public function getHotelInfoByShortCode($strHotelCode, $strTraceId)
{
$arrHotel = array('AccomSysId');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrHotel);
$select->where("LTRIM(RTRIM(t1.ShortName)) = ? ", $strHotelCode);
$select->where("t1.TraceId = ? ", $strTraceId);
$select->order("t1.AccomSysId DESC");
$result = $this->db->fetchRow($select);
//print_r($result);exit;
return $result['AccomSysId'];
}
public function getHotelInfoById($intAccomSysId)
{
$arrHotel = array(
'AccomSysId',
'Title',
'DetailImg',
'Brief',
'MinPublishedFare',
'GeoLat',
'GeoLong',
'Address',
'Stars',
'TokenId',
'MinCommissionEarned',
'CurrencyType'
);
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrHotel);
$select->where("t1.AccomSysId = ? ", $intAccomSysId);
$result = $this->db->fetchRow($select);
//echo $select; exit;
//print_r($result);exit;
return $result;
}
public function getINVHotelInfoById($intAccomSysId, $InvnItemSysId)
{
$arrHotel = array(
'AccomSysId',
'Title',
'DetailImg',
'Brief',
'MinPublishedFare',
'GeoLat',
'GeoLong',
'Address',
'Stars',
'MinCommissionEarned',
'CurrencyType'
);
$arrAccomdationInventory = array('XRefAccoSysId', 'InventoryType as ICSourceSysId', 'InvnItemSysId', 'SupplierSysId', 'NetDOccupCost', 'NetSOccupCost', 'NetExtraBedCost', 'NetExtraWOBedCost', 'ToDate', 'FromDate');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation'), $arrHotel);
$select->joinInner(array('t3' => 'TB_MP_Inventory_Accom'), 't3.XRefAccoSysId = t1.AccomSysId ', $arrAccomdationInventory);
$select->joinLeft(array('t4' => 'TB_Master_RoomType'), 't4.RoomType = t3.EconomyType ', array("Title as EconomyTypeTitle", "RoomType as RoomTypeId"));
$select->joinLeft(array('t5' => 'TB_Master_Accom_MealType'), 't5.MealTypeId = t3.MealPlanType', array("Title as MealPlanTypeTitle", "MealTypeId"));
$select->where("t1.AccomSysId = ? ", $intAccomSysId);
$select->where("t3.InvnItemSysId = ? ", $InvnItemSysId);
$result = $this->db->fetchRow($select);
//echo $select; exit;
//print_r($result);exit;
return $result;
}
public function getHotelShortNameByGTXCityId($intGTXCityId)
{
$arrHotel = array('ShortName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrHotel);
$select->where("t1.CitySysId = ? ", $intGTXCityId);
$result = $this->db->fetchAll($select);
//echo $select; exit;
// echo "<pre>";
// print_r($result);exit;
if (count($result) > 0) {
$arrInfo = array();
for ($i = 0; $i < count($result); $i++) {
$arrInfo[] = trim($result[$i]['ShortName']);
}
}
return $arrInfo;
}
public function GetAgencyMmbrById($id)
{
$agenctcustomer = array('CountrySysId', 'StateOrZoneSysId', 'Address', 'CitySysId', 'AgencySysId', 'CustomerSysId', 'EmailId', 'Title', 'Contacts', 'FirstName', 'LastName', 'IsActive', 'IsMarkForDelete');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), $agenctcustomer);
// $select->joinLeft(array('tb2' => "TB_CRM_CustomerAccount"), "tbl.CRMCustSysId = tb2.CRMCustSysId" , '');
$select->where("tbl.CustomerSysId = ?", $id);
//$select->where("tbl.IsActive = ?", 1);
//echo $select;
$result = $this->db->fetchRow($select);
return $result;
}
public function GetAgencyCustomerDetailsForBooking($id, $TPSysId)
{
$agenyCustomerTravelPlan_Pax = array('CustomerSysId', 'MemberSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Pax"), $agenyCustomerTravelPlan_Pax);
$select->where("tbl.TPSysId = ?", $TPSysId);
$select->order("tbl.TPSysId DESC");
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchRow($select);
if (is_array($result) && (count($result) > 0)) {
$intCustomerSysId = trim($result['CustomerSysId']);
$intMemberSysId = trim($result['MemberSysId']);
if (!empty($intMemberSysId)) {
$agenyCustomerMember = array('MemberSysId', 'Title', 'Relation', 'FirstName', 'LastName', 'Contacts', 'countrycode');
$agenyCustomer = array('EmailId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_Members"), $agenyCustomerMember);
$select->joinInner(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tbl.CustomerSysId", $agenyCustomer);
$select->where("tbl.MemberSysId = ?", $intMemberSysId);
$select->limit(1);
$result = $this->db->fetchRow($select);
} else {
$agenyCustomer = array('Title', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'countrycode');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
$select->where("tbl.CustomerSysId = ?", $intCustomerSysId);
$select->limit(1);
$result = $this->db->fetchRow($select);
}
} else {
$agenyCustomer = array('Title', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'countrycode');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
$select->where("tbl.CustomerSysId = ?", $id);
$select->limit(1);
$result = $this->db->fetchRow($select);
}
return $result;
}
public function updateBookingDetails($data, $TPSysId)
{
//echo "<pre>"; print_r($data); exit;
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_Trx', $data, $where);
}
public function updateBookingStatus($data, $TPSysId)
{
//echo "<pre>"; print_r($data); exit;
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
}
public function updateBookingStatusTravelPlan($data, $TPSysId, $MasterTPSysId = NULL)
{
//echo "<pre>"; print_r($data); exit;
$where = array('TPSysId = ? ' => $TPSysId);
//echo "<pre>"; print_r($where); exit;
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
// update main query status created by Er Amit Kumar Dubey on 11 may 2017 at 01:26 PM
$customHelper = Zend_Controller_Action_HelperBroker::getStaticHelper("Custom");
$statusArray = $customHelper->changeQueryStatusByProposal();
$querystatus = $statusArray[$data['StatusType']];
if (isset($querystatus) && !empty($querystatus) && !empty($MasterTPSysId)) {
$whereMaster = array('TPSysId = ? ' => $MasterTPSysId);
$updatetravelplanquery = array(
'StatusType' => $querystatus,
'UpdateDate' => date('Y-m-d H:i:s')
);
$this->db->update('TB_Agency_Customer_TravelPlan_Query', $updatetravelplanquery, $whereMaster);
}
}
public function updateCancellationResponse($data, $TPSysId)
{
$where = array('TPIntSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan_Accom', $data, $where);
return 1;
}
public function getBookingDetails($TPSysId)
{
$arrTrx = array('APIBookingRes');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_Trx"), $arrTrx);
$select->where("tbl.TPSysId = ?", $TPSysId);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getGTXMarkups($intAccomType)
{
$arrAllocMarkups = array('Currency', 'AccomType', 'MarkUpType', 'MarkUp');
$select = $this->db->select();
$select->from(array('tbl' => "TB_System_StdMarkup_Accom_Alloc"), $arrAllocMarkups);
$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
$select->where("tbl.AccomType = ?", $intAccomType);
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
if (count($result) > 0) {
return $result;
} else {
$arrAccomMarkups = array('Currency', 'AccomType', 'MarkUpType', 'MarkUp');
$select = $this->db->select();
$select->from(array('tbl' => "TB_System_StdMarkup_Accom"), $arrAllocMarkups);
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.AccomType = ?", $intAccomType);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
}
return $result;
}
public function getAgencyMarkups($intAccomType = 1, $AgencySysId = NULL)
{
$arrAllocMarkups = array('Currency', 'AccomType', 'MarkUpType', 'MarkUp', 'Tax');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Accom"), $arrAllocMarkups);
if (!empty($AgencySysId)) {
$select->where("tbl.AgencySysId = ?", $AgencySysId);
} else {
$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("tbl.AccomType = ?", $intAccomType);
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getApiServiceTax($intSource = NULL)
{
$arrTaxCategory = array('');
$arrTaxRate = array('Percentage');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_TaxCategory"), $arrTaxCategory);
$select->joinInner(array('tb2' => "TB_Master_TaxRate"), "tb2.TaxCatSysId = tbl.TaxCatSysId", $arrTaxRate);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDelete = ?", 0);
$select->where("tb2.IsActive = ?", 1);
$select->where("tb2.IsMarkForDelete = ?", 0);
if (!empty($intSource)) {
$select->where("tb2.TaxCatSysId = ?", $intSource);
}
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function viewHotelBookingVocher($custID, $TPSysId)
{
$sql = "SELECT A.Title as HOTELNAME,A.Stars, A.Address AS HOTELADD,A.PrimaryContact AS HOTELCONT, ACTI.TPIntSysId,AC.FirstName,AC.LastName,AC.EmailId,
AC.Contacts,ACTP.TPSysId,ACX.XRefTrxNo,AG.Title,AG.PrimaryContactName,ACX.TrxDate,ACX.TrxType,ACX.PaidAmount,ACTI.TPSysId,
ACTA.AccoSysId,ACTA.FromDate AS CheckInTime,ACTA.ToDate AS CheckOutTime,ACTA.XRefCityId,
GC.Title AS CITYNAME,ACTA.RoomType,ACX.XRefTrxNo,ACX.TrxSysId,ACTA.RoomNumber,ACTA.TotalPax,ACTA.TotalNights,ACTA.AccoBookingId,ACTA.TotalCost,ACTA.Discounts,
ACTA.Taxes,ACTA.AdultPax,ACTA.ChildPax,ACTA.TotalNights,ACTA.SpecialRequest,ACX.APIBookingRes,ACTA.XRefBookingId
FROM TB_Agency_Customer_TravelPlan_Accom ACTA
LEFT OUTER JOIN TB_IC_Accomdation_API A ON A.AccomSysId=ACTA.AccoSysId
LEFT OUTER JOIN TB_Agency_Customer_TravelPlan_Itenary ACTI ON ACTI.TPIntSysId=ACTA.TPIntSysId
LEFT OUTER JOIN TB_Agency_Customer_TravelPlan ACTP ON ACTI.TPSysId=ACTP.TPSysId
LEFT OUTER JOIN TB_Agency_Customer_Trx ACX ON ACTP.TPSysId= ACX.TPSysId
LEFT OUTER JOIN TB_Agency_Customer AC ON AC.CustomerSysId=ACX.CustomerSysId
LEFT OUTER JOIN TB_Agency AG ON AG.AgencySysId=AC.AgencySysId
LEFT OUTER JOIN TB_Master_Geo_City GC ON GC.TBBCityId=ACTA.XRefCityId
WHERE AG.AgencySysId='" . $this->intLoggedinUserAgencySysId . "' AND
GC.TBBCityId=ACTA.XRefCityId AND ACX.TrxType='H' AND ACX.CustomerSysId='" . $custID . "' AND ACTP.TPSysId='" . $TPSysId . "' ";
$res = $this->db->query($sql)->fetchAll();
$_SESSION['voucer_view'] = $res;
return $res;
}
public function getHotelBookingList($ReportingToSysIds = null, $AccomType = 'API', $StatusType = '')
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'IsB2BProposal', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'NetPrice', 'PackSpecType', 'EconomyMask', 'SourcePlaces', 'IsHotelOnlyPackage', 'StartDate', 'RoomInfoJson');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAccom = array(
'AccoSysId',
'XRefBookingId',
'APIBookingRes',
'Taxes',
'NetCost',
'MarkUp',
'AgencyMarkUp',
'GTXMarkup',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'AgentServiceTaxAmount',
'CheckInTime',
'CheckOutTime',
'FromDate',
'ToDate',
'RoomTypeStr',
'RoomMemInfoJson',
'Title',
'CurrencyType',
'SourceSysId as ICSourceSysId',
'RoomIndex as IsNewMigration',
'XrefBookingId',
'UpdateDate',
'IsFromAPI'
);
$arrICAccomdationAPI = array('Title AS hotelName', 'Address', 'Stars', 'Rating');
$arrICAccomdationINV = array('Title AS hotelNameInv', 'Address as AddressInv', 'Stars as StarInv', 'Rating as RatingInv');
$arrCustomerTrx = array('TrxDate AS bookingDate', 'PaidAmount');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrGeoCity = array('CityId', 'Title AS cityTitle');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Accom"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAccom);
$select->joinLeft(array('ICA' => "TB_IC_Accomdation"), "ICA.AccomSysId = TPA.AccoSysId AND TPA.IsFromAPI = '0'", $arrICAccomdationINV);
$select->joinLeft(array('ICAA' => "TB_IC_Accomdation_API"), "ICAA.AccomSysId = TPA.AccoSysId AND TPA.IsFromAPI = '1'", $arrICAccomdationAPI);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinLeft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName as AgencyName']);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = TPA.XRefCityId", $arrGeoCity);
$select->joinLeft(array('TAC' => "TB_Agency_Customer"), "TAC.CustomerSysId = TP.CustomerSysId", array('Title as CustomerSalutation', 'FirstName as CustomerFirstName', 'LastName as CustomerLastName', 'EmailId as CustomerEmailId', 'Contacts as CustomerContacts'));
$select->joinLeft(array('TACTA' => "TB_Agency"), "TAC.AgencySysId=TACTA.AgencySysId", array('Title as CompanyName', 'NickId'));
$select->joinLeft(array('TACTAIL' => "TB_Agency_Customer_TravelPlan_Accom_Inventory_Log"), "TACTAIL.TPSysId = TP.TPSysId", array('InventoryStatus', 'AccomHistoryLog', 'RefNo', 'AccomHistoryLog', 'AdultPax', 'ChildPax', 'InfantPax'));
$select->joinLeft(array('TMRT' => 'TB_Master_RoomType'), 'TMRT.RoomType = TACTAIL.RoomType ', array("Title as EconomyTypeTitle", "RoomType as RoomTypeId"));
$select->joinLeft(array('TMAMT' => 'TB_Master_Accom_MealType'), 'TMAMT.MealTypeId = TACTAIL.MealPlanType', array("Title as MealPlanTypeTitle", "MealTypeId"));
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TP.PlanType = ?", 2);
if ($StatusType == 'Request') {
$select->where('TP.StatusType IN(?)', array(126));
} else {
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123, 104));
$select->where("TRX.TrxType = ?", 'H');
$select->where("TRX.MPSysId = ?", 1);
}
if (!empty($this->strCondition)) {
// echo "<pre>"; print_r($this->strCondition); die("test model");
$select->where($this->strCondition);
// echo $select; die;
}
if (!empty($this->strCondition1['currentDate']) && !empty($this->strCondition1['fromDate'])) {
$currentDate = date('Y-m-d', strtotime($this->strCondition1['currentDate']));
$fromDate = date('Y-m-d', strtotime($this->strCondition1['fromDate']));
$select->where("CONVERT(date,TP.UpdateDate) >= '$fromDate' AND CONVERT(date,TP.UpdateDate) <= '$currentDate'");
}
if ($StatusType == 'Request') {
$select->order('TP.TPSysId DESC');
} else {
$select->order('TRX.TrxDate DESC');
}
// echo $select; exit;
$result = $this->db->fetchAll($select);
// echo "<pre>"s;print_r($result);die;
return $result;
// return $select;
}
public function viewHotelBookingDetails($intTPSysId)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'SupplierSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAccom = array(
'TPIntSysId',
'XRefBookingId',
'APIBookingRes',
'APICancellationRes',
'Taxes',
'NetCost',
'MarkUp',
'AgencyMarkUp',
'GTXMarkup',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'AgentServiceTaxAmount',
'CheckInTime',
'CheckOutTime',
'TotalNights',
'FromDate',
'ToDate',
'RoomTypeStr',
'RoomMemInfoJson'
);
$arrICAccomdationAPI = array('Title AS hotelName', 'Address', 'Stars', 'Rating');
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('Title AS agentName', 'PrimaryContactName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Accom"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAccom);
$select->joinLeft(array('ICA' => "TB_IC_Accomdation_API"), "ICA.AccomSysId = TPA.AccoSysId", $arrICAccomdationAPI);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("TP.PlanType = ?", 2);
$select->where('TP.TPSysId = ?', $intTPSysId);
$select->limit(1);
$result = $this->db->fetchAll($select);
return $result;
}
public function viewHotelBookingVoucher($intTPSysId)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAccom = array(
'XRefBookingId',
'APIBookingRes',
'Taxes',
'NetCost',
'MarkUp',
'AgencyMarkUp',
'GTXMarkup',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'AgentServiceTaxAmount',
'CheckInTime',
'CheckOutTime',
'FromDate',
'ToDate',
'RoomTypeStr',
'RoomMemInfoJson',
'TotalNights'
);
$arrICAccomdationAPI = array('Title AS hotelName', 'Address as hotelAddress', 'Stars', 'Rating', 'PrimaryContact');
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'ServiceTaxNo', 'PanCardNo');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrAgencyBank = array('BankName', 'AccountName', 'AccountNo', 'IFSCCode', 'Branch', 'Address');
$arrGC = array('Title AS CityTitle', 'Country');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Accom"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAccom);
$select->joinLeft(array('ICA' => "TB_IC_Accomdation_API"), "ICA.AccomSysId = TPA.AccoSysId", $arrICAccomdationAPI);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId", array("InvoiceId"));
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('AGB' => "TB_Agency_BankDetails"), "AGB.AgencySysId = AG.AgencySysId", $arrAgencyBank);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
$select->where("TP.PlanType = ?", 2);
$select->where('TP.TPSysId = ?', $intTPSysId);
$select->limit(1);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyMarkupsByAgencyId($intAccomType = 1, $AgencySysId)
{
$arrAllocMarkups = array('Currency', 'AccomType', 'MarkUpType', 'MarkUp', 'Tax');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Accom"), $arrAllocMarkups);
$select->where("tbl.AgencySysId = ?", $AgencySysId);
$select->where("tbl.AccomType = ?", $intAccomType);
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function checkIfHotelAlreadyExists($ShortName, $TraceId)
{ // for Api Hotel Only....
$arrHotel = array('AccomSysId');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation_API'), $arrHotel);
$select->where("t1.ShortName = ? ", $ShortName);
$select->where("t1.TraceId = ? ", $TraceId);
$result = $this->db->fetchRow($select);
if ($result) {
return $result['AccomSysId'];
} else {
return false;
}
}
public function checkIfRoomTypeExists($ApiRoomTypeId, $RoomType)
{ // for Api Hotel Only....
$arrHotel = array('RoomType');
$select = $this->db->select();
$select->from(array('t1' => 'TB_Master_RoomType'), $arrHotel);
$select->where("t1.Title = ? ", $RoomType);
$select->where("t1.ApiRoomTypeId = ? ", $ApiRoomTypeId);
$result = $this->db->fetchRow($select);
return $result['RoomType'];
}
public function getInventoryHotels()
{
$arrAccomdationInven = array(
'UpdateDate',
'CurrencyType',
'Title as HotelName',
'ShortName',
'DetailImg',
'Stars',
'Rating',
'Address',
'Brief',
'AccomSysId',
'AccoAminitiesMask',
'RoomCounts',
'EconomyType'
);
if ($this->intTotalPaxCount > 0) {
$intTotalPaxCount = $this->intTotalPaxCount;
}
$arrAccomdationInventory = array('XRefAccoSysId', 'InventoryType as ICSourceSysId', 'InvnItemSysId', 'SupplierSysId', 'NetDOccupCost', 'NetSOccupCost', 'NetExtraBedCost', 'NetExtraWOBedCost', 'ToDate', 'FromDate');
$selectarrayInv = array('id', 'PublishQty as DailyRoomLimit', 'RoomSysId as EconomyType', 'CONVERT(VARCHAR(24),FromDate,111) as FromDate');
// For Inventory Hotels DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Accomdation'), $arrAccomdationInven);
$select->joinInner(array('t2' => 'Tb_MP_Accomdation_Room_Inventory'), 't2.AccomSysId = t1.AccomSysId ', $selectarrayInv);
$select->joinInner(array('t3' => 'TB_MP_Inventory_Accom'), 't3.RoomInventorySysId = t2.id', $arrAccomdationInventory);
$select->joinLeft(array('t4' => 'TB_Master_RoomType'), 't4.RoomType = t3.EconomyType ', array("Title as EconomyTypeTitle", "RoomType as RoomTypeId"));
$select->joinLeft(array('t5' => 'TB_Master_Accom_MealType'), 't5.MealTypeId = t3.MealPlanType', array("Title as MealPlanTypeTitle", "MealTypeId"));
$select->joinLeft(array('t6' => 'TB_IC_Supplier'), 't3.SupplierSysId = t6.SupplierSysId', array("SupplierName"));
$select->joinLeft(array('t7' => 'TB_Master_Currency'), 't7.CurrencyType = t3.CostCurrency', array('symbol'));
$select->where("t1.Title != ''");
$select->where("t1.IsActive = ?", 1);
$select->where("t2.IsSaleStart = ?", 1);
$select->where("t2.IsMarkForDelete = ?", 0);
$select->where("t2.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
if (!empty($this->strCheckInDate) && !empty($this->strCheckOutDate)) {
$select->where(" t3.FromDate >= '" . $this->strCheckInDate . " 00:00:000'");
$select->where(" t3.FromDate < '" . $this->strCheckOutDate . " 00:00:000'");
}
$select->order(array('t1.MinPublishedFare ASC'));
$result = $this->db->fetchAll($select);
return $result;
}
public function getAllMPType()
{
$arrMealType = array('Title as MPType', 'MealTypeId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_Accom_MealType"), $arrMealType);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$result = $this->db->fetchAll($select);
return $result;
}
public function getMPTypeByMealTypeId($MealTypeId)
{
$arrMealType = array('Title as MPType', 'MealTypeId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_Accom_MealType"), $arrMealType);
$select->where("tbl.MealTypeId = ?", $MealTypeId);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$result = $this->db->fetchRow($select);
return $result;
}
public function updateB2CBookingDetails($data, $VersionId)
{
$where = array('VersionId = ? ' => $VersionId);
$this->db->update('TB_Agency_Customer_TravelPlan_Accom', $data, $where);
}
public function updateAccomRoomDetail($data, $VersionId)
{
$where = array('VersionId = ? ' => $VersionId);
$this->db->update('TB_Agency_Customer_TravelPlan_Accom_Room', $data, $where);
}
public function updateAccomRoomByTPSysId($data, $TPSysId)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan_Accom_Room', $data, $where);
}
/* Added By Pardeep Panchal Ends */
public function getOnlyHotelPackageBookingList($ReportingToSysIds = null)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'NetPrice', 'PackSpecType', 'EconomyMask', 'IsHotelOnlyPackage', 'StartDate', 'RoomInfoJson');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAccom = array(
'XRefBookingId',
'APIBookingRes',
'Taxes',
'NetCost',
'MarkUp',
'AgencyMarkUp',
'GTXMarkup',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'AgentServiceTaxAmount',
'CheckInTime',
'CheckOutTime',
'FromDate',
'ToDate',
'RoomTypeStr',
'RoomMemInfoJson',
'Title',
'CurrencyType',
'SourceSysId as ICSourceSysId',
'RoomIndex as IsNewMigration',
'XrefBookingId'
);
$arrICAccomdationAPI = array('Title AS hotelName', 'Address', 'Stars', 'Rating');
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrGeoCity = array('CityId', 'Title AS cityTitle');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Accom"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAccom);
$select->joinLeft(array('ICA' => "TB_IC_Accomdation"), "ICA.AccomSysId = TPA.AccoSysId", $arrICAccomdationAPI);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinLeft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName as AgencyName']);
$select->joinLeft(array('TAC' => "TB_Agency_Customer"), "TAC.CustomerSysId = TP.CustomerSysId", array('Title as CustomerSalutation', 'FirstName as CustomerFirstName', 'LastName as CustomerLastName', 'EmailId as CustomerEmailId', 'Contacts as CustomerContacts'));
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = TPA.XRefCityId", $arrGeoCity);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TP.PlanType = ?", 2);
$select->where("TP.IsHotelOnlyPackage = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 13, 7, 102, 123, 104));
if (!empty($this->strCondition1['currentDate']) && !empty($this->strCondition1['fromDate'])) {
$currentDate = date('Y-m-d', strtotime($this->strCondition1['currentDate']));
$fromDate = date('Y-m-d', strtotime($this->strCondition1['fromDate']));
$select->where("CONVERT(date,TP.UpdateDate) >= '$fromDate' AND CONVERT(date,TP.UpdateDate) <= '$currentDate'");
}
// $select->where("TRX.TrxType = ?", 'H');
// $select->where("TRX.MPSysId = ?", 1);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
$select->order('TRX.TrxDate DESC');
$result = $this->db->fetchAll($select);
return $result;
//echo "<pre>";print_r($result);die;
// return $select;
}
public function updateHotelLockInventoryStatusManual($AccomHistoryLog, $statusU)
{
$status = (int) $statusU;
$checkInventoryData = $this->checkHotelHoldSoldInventory($AccomHistoryLog);
if (!empty($checkInventoryData)) {
$FromDate = $checkInventoryData['FromDate']->format('Y-m-d');
$totalInventory = 1;
$FinalFixedInventoryWhere = array('AccomSysId =?' => $checkInventoryData['AccoSysId'], 'FromDate =? ' => $FromDate, 'RoomSysId =? ' => $checkInventoryData['RoomType'], 'AgencySysId =?' => $checkInventoryData['AgencySysId']);
$whereCustomerTrx = array('AccomHistoryLog =?' => $AccomHistoryLog);
$editDataInventory = array("InventoryStatus" => $status);
if ($status == 0) {
if ($checkInventoryData['InventoryStatus'] == 1) {
$updateFinalInventory = array("SoldQty" => new Zend_Db_Expr('SoldQty - ' . $totalInventory), "BlockQty" => new Zend_Db_Expr('BlockQty + ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
} else if ($checkInventoryData['InventoryStatus'] == 2) {
$updateFinalInventory = array("NetInHandQty" => new Zend_Db_Expr('NetInHandQty - ' . $totalInventory), "BlockQty" => new Zend_Db_Expr('BlockQty + ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
}
} else if ($status == 1) {
if ($checkInventoryData['InventoryStatus'] == 0) {
$updateFinalInventory = array("SoldQty" => new Zend_Db_Expr('SoldQty + ' . $totalInventory), "BlockQty" => new Zend_Db_Expr('BlockQty - ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
} else if ($checkInventoryData['InventoryStatus'] == 2) {
$updateFinalInventory = array("SoldQty" => new Zend_Db_Expr('SoldQty + ' . $totalInventory), "NetInHandQty" => new Zend_Db_Expr('NetInHandQty - ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
}
} else if ($status == 2) {
if ($checkInventoryData['InventoryStatus'] == 0) {
$updateFinalInventory = array("NetInHandQty" => new Zend_Db_Expr('NetInHandQty + ' . $totalInventory), "BlockQty" => new Zend_Db_Expr('BlockQty - ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
} else if ($checkInventoryData['InventoryStatus'] == 1) {
$updateFinalInventory = array("NetInHandQty" => new Zend_Db_Expr('NetInHandQty + ' . $totalInventory), "SoldQty" => new Zend_Db_Expr('SoldQty - ' . $totalInventory));
$this->db->update("Tb_MP_Accomdation_Room_Inventory", $updateFinalInventory, $FinalFixedInventoryWhere);
}
}
$this->db->update("TB_Agency_Customer_TravelPlan_Accom_Inventory_Log", $editDataInventory, $whereCustomerTrx);
$status = array('status' => 'success');
} else {
$status = array('status' => 'fail');
}
return $status;
}
public function checkHotelHoldSoldInventory($AccomHistoryLog)
{
$select = $this->db->select();
$select->from(array("t1" => "TB_Agency_Customer_TravelPlan_Accom_Inventory_Log"), array('InventoryStatus', 'FromDate', 'AccoSysId', 'RoomType', 'AgencySysId'));
$select->where('t1.AccomHistoryLog = ?', $AccomHistoryLog);
$select->where('t1.IsActive = ?', 1);
$select->where('t1.IsMarkForDel = ?', 0);
$result = $this->db->fetchRow($select);
return $result;
}
}