| 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_TblHotel
{
protected $db = NULL;
public $intId = NULL;
public $intAccomSysId = NULL;
public $intLoggedinUserAgencySysId = '';
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 $baseUrl;
/* 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 $searchArr = 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 = 'DESC';
/* * ************************************ */
public function __construct()
{
$request = Zend_Controller_Front::getInstance()->getRequest();
$this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
$this->db = Zend_Db_Table::getDefaultAdapter();
}
public function __destruct()
{
$this->db->closeConnection();
}
public function getHotelAllInventoryList($intLoggedinUserAgencySysId)
{
$sql = " SELECT InvnItemSysId,TB_IC_Accomdation.AccomSysId, TB_IC_Accomdation.Title as HotelName, TB_IC_Accomdation.IsLocked, TB_IC_Accomdation.IsAgentSpec, TB_IC_Accomdation.ICSourceSysId
,XRefAccoSysId, XRefItemSysId, AgencySysId, TB_MP_Inventory_Accom.SupplierSysId, TB_MP_Inventory_Accom.Title,
TB_MP_Inventory_Accom.EconomyType, TB_Master_RoomType.Title as RoomType, TB_Master_Geo_City.Title as CityName
, CONVERT(VARCHAR(24),FromDate,103) as FromDate,CONVERT(VARCHAR(24),ToDate,103) as ToDate, DailyRoomLimit, PurchasedQty,
PublishQty, BlockQty, SoldQty, NetInHandQty, TB_IC_Supplier.SupplierName as SupplierName,
TB_MP_Inventory_Accom.IsActive, TB_MP_Inventory_Accom.IsMarkForDelete, TB_MP_Inventory_Accom.IsApproved, TB_MP_Inventory_Accom.IsSaleStart,TBMAM.Title as MealPlanName
FROM TB_MP_Inventory_Accom
LEFT JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
LEFT JOIN TB_Master_Geo_City ON TB_IC_Accomdation.CitySysId = TB_Master_Geo_City.CityId
LEFT JOIN TB_IC_Supplier ON TB_MP_Inventory_Accom.SupplierSysId = TB_IC_Supplier.SupplierSysId
LEFT JOIN TB_Master_RoomType ON TB_MP_Inventory_Accom.EconomyType = TB_Master_RoomType.RoomType
LEFT JOIN TB_Master_Accom_MealType TBMAM ON TBMAM.MealTypeId = TB_MP_Inventory_Accom.MealPlanType
WHERE 1=1 ";
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND AgencySysId = " . $intLoggedinUserAgencySysId;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
if (!empty($this->searchArr)) {
//echo "<pre>"; print_r($this->searchArr); die;
if ($this->searchArr['hotelId']) {
$sql .= " AND TB_MP_Inventory_Accom.XRefAccoSysId = " . $this->searchArr['hotelId'];
}
if ($this->searchArr['DestinationId']) {
$sql .= " AND TB_IC_Accomdation.CitySysId = '" . $this->searchArr['DestinationId'] . "' ";
}
if ($this->searchArr['rating']) {
$sql .= " AND TB_IC_Accomdation.Stars = '" . $this->searchArr['rating'] . "'";
}
if ($this->searchArr['supplierId']) {
$sql .= " AND TB_MP_Inventory_Accom.SupplierSysId = '" . $this->searchArr['supplierId'] . "'";
}
}
$sql .= " ORDER BY InvnItemSysId DESC ";
// echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getHotelList($intLoggedinUserAgencySysId)
{
$sql = "SELECT TB_IC_Accomdation.AccomSysId, TB_MP_Inventory_Accom.EconomyType AS RoomTypeId, TB_Master_RoomType.Title AS RoomTypeName, ICSourceSysId, 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, TB_IC_Accomdation.MaxPaxCount,
RoomCounts, TB_IC_Accomdation.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
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
LEFT OUTER JOIN TB_IC_Accomdation_Room ON TB_IC_Accomdation.AccomSysId=TB_IC_Accomdation_Room.AccomSysId
INNER JOIN TB_Master_RoomType ON TB_IC_Accomdation_Room.RoomType=TB_Master_RoomType.RoomType
LEFT OUTER JOIN TB_MP_Inventory_Accom ON TB_MP_Inventory_Accom.EconomyType=TB_IC_Accomdation_Room.RoomType
AND TB_MP_Inventory_Accom.XRefAccoSysId=TB_IC_Accomdation_Room.AccomSysId
WHERE 1 = 1 ";
if (!empty($this->intAccomSysId)) {
$sql .= " AND TB_IC_Accomdation.AccomSysId = " . $this->intAccomSysId;
}
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND (ICSourceSysId = '" . $intLoggedinUserAgencySysId . "' OR ICSourceSysId = '3' AND TB_IC_Accomdation.AccomSysId IN
(SELECT distinct(TB_MP_Inventory_Accom.XRefAccoSysId)
FROM TB_MP_Inventory_Accom WHERE 1=1 AND TB_MP_Inventory_Accom.AgencySysId = '" . $intLoggedinUserAgencySysId . "'
AND TB_IC_Accomdation.ICSourceSysId='3'
AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ))
AND TB_IC_Accomdation_Room.SourceSysId IN (2,$intLoggedinUserAgencySysId) AND TB_IC_Accomdation_Room.IsDelete='0'
";
}
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
if (!empty($this->searchArr)) {
//echo "<pre>"; print_r($this->searchArr); die;
if ($this->searchArr['hotelId']) {
$sql .= " AND TB_IC_Accomdation.AccomSysId = " . $this->searchArr['hotelId'];
}
if ($this->searchArr['DestinationId']) {
$sql .= " AND TB_IC_Accomdation.CitySysId = '" . $this->searchArr['DestinationId'] . "' ";
}
if ($this->searchArr['rating']) {
$sql .= " AND TB_IC_Accomdation.Stars = '" . $this->searchArr['rating'] . "'";
}
if ($this->searchArr['supplierId']) {
$sql .= " AND TB_MP_Inventory_Accom.SupplierSysId = '" . $this->searchArr['supplierId'] . "'";
}
}
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getAllHotelList($intLoggedinUserId)
{
$sql = "SELECT TB_IC_Accomdation.AccomSysId, ICSourceSysId, TB_IC_Accomdation.Title,TB_IC_Accomdation.CheckinTime,TB_IC_Accomdation.CheckOutTime,
TB_IC_Accomdation.Stars,TB_Master_EconomyType.Title As EconomyTypeTitle,
TB_Master_Geo_City.Title as CityName, TB_Master_Geo_Country.Title as CountryName,
TB_IC_Accomdation.IsApproved, TB_IC_Accomdation.IsActive
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_Master_EconomyType.EconomyType = TB_IC_Accomdation.EconomyType
WHERE 1 = 1 AND TB_IC_Accomdation.IsMarkForDel = 0 ";
if (!empty($intLoggedinUserId)) {
$sql .= " AND IsAgentSpec = 1 AND TB_IC_Accomdation.ICSourceSysId = " . $intLoggedinUserId;
}
if (!empty($this->intAccomSysId)) {
$sql .= " AND TB_IC_Accomdation.AccomSysId = " . $this->intAccomSysId;
}
if (!empty($this->searchArr)) {
// //echo "<pre>"; print_r($this->searchArr); die;
if ($this->searchArr['HotelName']) {
$sql .= " AND TB_IC_Accomdation.Title LIKE '%" . $this->searchArr['HotelName'] . "%' ";
}
if ($this->searchArr['DestinationId']) {
$sql .= " AND TB_IC_Accomdation.CitySysId = '" . $this->searchArr['DestinationId'] . "' ";
}
if ($this->searchArr['rating']) {
$sql .= " AND TB_IC_Accomdation.Stars IN( " . $this->searchArr['rating'] . ")";
}
if ($this->searchArr['IsActive'] != '') {
$sql .= " AND TB_IC_Accomdation.IsActive = " . $this->searchArr['IsActive'] . "";
}
}
$sql .= " ORDER BY AccomSysId DESC";
//echo $sql;//exit;
// return $sql;
return $this->db->query($sql)->fetchAll();
}
public function getCountryList($strCountryName)
{
$sql = "Select ContId, Title From TB_Master_Geo_Country where 1 = 1 AND IsActive='1' AND Code!='0' ";
if (!empty($strCountryName)) {
$sql .= " AND Title LIKE '$strCountryName%' ";
}
$sql .= " ORDER BY Title";
//echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = array('ContId' => $row['ContId'], 'value' => $row['Title'], 'label' => $row['Title']);
}
} else {
$response[] = array('ContId' => 0);
}
return $response;
}
public function getAutoSuggestList()
{
$sql = " SELECT top 50 AccomSysId, IsAgentSpec, ICSourceSysId, Title
FROM TB_IC_Accomdation
WHERE 1 = 1 ";
// $sql = " SELECT distinct(TB_IC_Accomdation.AccomSysId) AS AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title,
// TB_Master_Geo_City.Title as CityName, TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ContSysId
// 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
// WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND IsActive='1'";
// $sql .= " AND TB_IC_Accomdation.IsActive='1' AND TB_Master_Geo_City.IsActive='1' ";
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//AND TB_IC_Accomdation.IsApproved='1'
//echo $sql; exit;
return $this->db->query($sql)->fetchAll();
}
public function getAutoSuggestListCity()
{
$sql = " SELECT CityId, Title as CityName, ContSysId, IsActive
FROM TB_Master_Geo_City WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND IsActive='1' ";
$sql .= " ORDER BY Title ASC";
return $this->db->query($sql)->fetchAll();
}
public function getAutoSuggestListNew()
{
$sql = " SELECT distinct(TB_IC_Accomdation.AccomSysId) AS AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title,
TB_Master_Geo_City.Title as CityName, TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ContSysId, TB_MP_Inventory_Accom.XRefAccoSysId
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
JOIN TB_MP_Inventory_Accom ON TB_IC_Accomdation.AccomSysId = TB_MP_Inventory_Accom.XRefAccoSysId
WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_IC_Accomdation.IsActive='1' AND TB_Master_Geo_City.IsActive='1' ";
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//AND TB_IC_Accomdation.IsApproved='1'
// echo $sql; die;
return $this->db->query($sql)->fetchAll();
}
public function getAutoSuggestListByAgent()
{
$sql = " SELECT distinct(TB_IC_Accomdation.AccomSysId) AS AccomSysId, IsAgentSpec, ICSourceSysId, TB_IC_Accomdation.Title,
TB_Master_Geo_City.Title as CityName, TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ContSysId
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
WHERE 1 = 1 ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " AND TB_IC_Accomdation.IsAgentSpec='1' AND TB_IC_Accomdation.IsLocked='1' AND TB_IC_Accomdation.IsActive='1' AND TB_Master_Geo_City.IsActive='1' ";
$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 TOP 10 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_MP_Inventory_Accom.CostCurrency, 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;
}
if (!empty($this->intLoggedinUserAgencySysId)) {
$sql .= " AND AgencySysId = " . $this->intLoggedinUserAgencySysId;
}
$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, 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
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 ON TB_MP_Inventory_Accom.Currency = TB_Master_Currency.CurrencyType WHERE 1=1 ";
*/
$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,
NetTax, 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, TB_Master_Geo_City.Title AS CityName
FROM TB_MP_Inventory_Accom
LEFT JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
INNER JOIN TB_Master_Geo_City ON TB_IC_Accomdation.CitySysId = TB_Master_Geo_City.CityId
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.Title,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 getHotelRoomRateList($AccomSysId) {
$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.AccomSysId = " . $AccomSysId ;
// 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 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 getCustomizedMarkups($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->intMarkupType)) {
$sql .= " AND MarkUpType = " . $this->intMarkupType;
}
$sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
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;
}
//
//
//
// if(!empty($CustGrSysId)) {
// $sql .= " AND CustGrSysId = " . $CustGrSysId;
// }
//
// if(!empty($this->intMarkupType)) {
// $sql .= " AND MarkUpType = " . $this->intMarkupType;
// }
$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, $strcountryId, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '" . $keyword . "%' ";
$objHotel->strCondition .= " AND TB_Master_Geo_Country.ContId = '" . $strcountryId . "' ";
$objHotel->strCondition .= " AND ICSourceSysId IN ($intLoggedinUserAgencySysId,3)";
$rowset = $objHotel->getAutoSuggestList();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$internal = (($row['ICSourceSysId'] == $intLoggedinUserAgencySysId) && $row['IsAgentSpec'] == 1) ? ' (Internal)' : '';
$title = $row['Title'] . ', ' . $row['CityName'] . $internal;
//$response[] = array('AccomSysId' => $row['AccomSysId'], 'Title' => $row['Title']); ;
$response[] = array('value' => $row['AccomSysId'], 'label' => $title);
//$response[] = $row['Title'] ;
}
}
$response[] = array('value' => -1, 'label' => 'Add New');
return $response;
}
public function getAutoSuggestHotel($keyword, $strcountryId, $strcityId, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = $ckeckDuplicat = array();
$objHotel = new Travel_Model_TblHotel();
if ($keyword != "") {
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '%" . $keyword . "%' ";
}
$objHotel->strCondition .= " AND TB_IC_Accomdation.ContSysId = '" . $strcountryId . "' ";
$objHotel->strCondition .= " AND TB_IC_Accomdation.CitySysId = '" . $strcityId . "' ";
$objHotel->strCondition .= " AND ( ICSourceSysId = $intLoggedinUserAgencySysId OR ICSourceSysId = 3)";
$rowset = $objHotel->getAutoSuggestList();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if (!in_array($row['AccomSysId'], $ckeckDuplicat)) {
$ckeckDuplicat[] = $row['AccomSysId'];
$internal = (($row['ICSourceSysId'] == $intLoggedinUserAgencySysId) && $row['IsAgentSpec'] == 1) ? ' (Internal)' : '';
$title = $row['Title'] . ', ' . $internal . '-' . $row['AccomSysId'];
$titleSelect = $row['Title'];
$response[] = array('value' => $row['AccomSysId'], 'label' => $title, 'titleSelect' => $titleSelect);
}
}
}
$response[] = array('value' => -1, 'label' => 'Add New');
return $response;
}
public function getAutoSuggestHotelPackage($keyword, $strcityId, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = $ckeckDuplicat = array();
$objHotel = new Travel_Model_TblHotel();
if (strlen(trim($keyword)) > 2) {
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '%" . trim($keyword) . "%' ";
$objHotel->strCondition .= " AND TB_IC_Accomdation.CitySysId = '" . $strcityId . "' ";
$objHotel->strCondition .= !empty($intLoggedinUserAgencySysId) ? " AND (TB_IC_Accomdation.IsApproved = '1' OR ( TB_IC_Accomdation.ICSourceSysId = '$intLoggedinUserAgencySysId' AND TB_IC_Accomdation.IsAgentSpec = 1) ) " : " AND (TB_IC_Accomdation.IsApproved = '1') ";
$rowset = $objHotel->getAutoSuggestList();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if (!in_array($row['AccomSysId'], $ckeckDuplicat)) {
$internal = (($row['ICSourceSysId'] == $intLoggedinUserAgencySysId) && $row['IsAgentSpec'] == 1) ? ' (Internal)' : '';
$title = $row['Title'] . $internal;
$response[] = array('value' => $title, 'AccomSysId' => $row['AccomSysId'], 'label' => $title);
}
}
} else {
$defaultHotelArr = unserialize(DEFAULTHOTEL);
for ($d = 0; $d < count($defaultHotelArr); $d++) {
$response[] = $defaultHotelArr[$d];
}
}
} else {
$defaultHotelArr = unserialize(DEFAULTHOTEL);
for ($d = 0; $d < count($defaultHotelArr); $d++) {
$response[] = $defaultHotelArr[$d];
}
}
return $response;
}
public function getAutoSuggestNew($keyword, $strcountryId, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND TB_IC_Accomdation.Title like '%" . $keyword . "%' ";
if ($strcountryId) {
$objHotel->strCondition .= " AND TB_Master_Geo_Country.ContId = '" . $strcountryId . "' ";
}
$objHotel->strCondition .= " AND TB_MP_Inventory_Accom.AgencySysId IN ($intLoggedinUserAgencySysId,3)";
$rowset = $objHotel->getAutoSuggestListNew();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$internal = (($row['ICSourceSysId'] == $intLoggedinUserAgencySysId) && $row['IsAgentSpec'] == 1) ? ' (Internal)' : '';
$title = $row['Title'] . ', ' . $row['CityName'] . $internal;
//$response[] = array('AccomSysId' => $row['AccomSysId'], 'Title' => $row['Title']); ;
$response[] = array('value' => $row['AccomSysId'], 'label' => $title);
//$response[] = $row['Title'] ;
}
}
$response[] = array('value' => -1, 'label' => 'Add New');;
return $response;
}
public function getAutoSuggestCity($keyword, $strcountryId, $intLoggedinUserAgencySysId)
{
$keyword = trim($keyword);
$response = array();
$objHotel = new Travel_Model_TblHotel();
$objHotel->strCondition = " AND Title like '" . $keyword . "%' ";
$objHotel->strCondition .= " AND ContSysId = '" . $strcountryId . "' ";
//$objHotel->strCondition .= " AND ICSourceSysId IN ($intLoggedinUserAgencySysId,3)";
$rowset = $objHotel->getAutoSuggestListCity();
$response = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$title = $row['CityName'];
$response[] = array('value' => $row['CityId'], 'label' => $title);;
}
}
$response[] = array();;
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->getAutoSuggestListByAgent();
$response = array();
$inArray = array();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
if (!in_array($row['AccomSysId'], $inArray)) {
$inArray[] = $row['AccomSysId'];
$response[] = array('value' => $row['AccomSysId'], 'label' => $row['Title'] . ', ' . $row['CityName']);
}
}
}
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 = array('AccomSysId = ' . $AccomSysId);
$where[] = 'ItemSysId = ' . $ItemSysId;
$this->db->update('TB_IC_Accomdation_Room', $data, $where);
} catch (Exception $e) {
die($e->getMessage());
return false;
}
}
public function updateAccomPromotionsStatus($data, $AccomSysId, $AccomPromotionsSysId,$PromotionType = 0)
{
try {
$where = array('AcoomSysId = ' . $AccomSysId);
$where[] = '(AccomPromotionsSysId = ' . $AccomPromotionsSysId . ' OR PromotionsSysId = ' . $AccomPromotionsSysId . ')';
$this->db->update('Tb_Agency_Accom_Promotions', $data, $where);
if($PromotionType == 3){
$whereRoom = array('AccomSysId = ? ' => $AccomSysId,'PromotionsSysId = ? ' => $AccomPromotionsSysId);
$whereRoomRate = array('XRefAccoSysId = ? ' => $AccomSysId,'PromotionsSysId = ? ' => $AccomPromotionsSysId);
$this->db->update('Tb_MP_Accomdation_Room_Inventory', $data, $whereRoom);
$this->db->update('TB_MP_Inventory_Accom', $data, $whereRoomRate);
}
} 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 isEditICAccomodationRoomExists($AccomSysId, $RoomType, $ItemSysId)
{
$sql = " SELECT COUNT(*)AS TOT FROM TB_IC_Accomdation_Room WHERE AccomSysId = " . $AccomSysId . " AND RoomType = '" . $RoomType . "' AND ItemSysId != '" . $ItemSysId . "'";
$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 ";
$objHotel->orderBy = "";
$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 AminityId ASC ";
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 ASC";
$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();
}
// $objAmenity = new Travel_Model_TblAmenities();
// $objAmenity->orderByField = 'AminityId' ;
// $objAmenity->orderBy = 'ASC' ;
// $arrRoomAmenities = $objAmenity->getRoomAmenitiesList();
// $objHotel = new Travel_Model_TblHotel();
// $objHotel->orderByField = 'TB_Master_Accom_AccomAminity.AminityId' ;
// $objHotel->orderBy = 'ASC' ;
// $objHotel->getMasterAccomAminities();
$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)
{
echo $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 updateAccomodationGroupInfo($hotel_chain, $hotelId)
{
$currentDate = date('Y-m-d H:i:s');
$accGroupInsert = array(
'AccomGrSysId' => $hotel_chain,
'MemberSice' => $currentDate,
'UpdateDate' => $currentDate,
'CreateDate' => $currentDate,
'IsActive' => '1',
'IsMarkForDel' => '0'
);
try {
$where = array('AccomSysId =?' => $hotelId);
$this->db->update('TB_IC_Accomdation_Group_Map', $accGroupInsert, $where);
} catch (Exception $ex) {
die($ex->getMessage());
}
}
public function getHotelGroupMapByAccomSysId($hotelId)
{
$sql = " SELECT AccomGrSysId, AccomSysId "
. " FROM TB_IC_Accomdation_Group_Map WHERE AccomSysId='" . $hotelId . "' AND IsMarkForDel = '0' ";
return $this->db->query($sql)->fetch();
}
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 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, RackCommission, 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, RackCommission, 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();
// echo "<pre>"; print_r($arrRoomAmenities);
// die;
$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, Address, Rating, AccoAminitiesMask FROM TB_IC_Accomdation where AccomSysId = " . $AccomSysId;
$data = $this->db->query($sql)->fetch();
return $data;
}
public function getAccomodationHotelList($where)
{
// $sql = "SELECT TB_IC_Accomdation.*,TB_IC_Accomdation.Title as HotelName, COUNT(DISTINCT TB_MP_Inventory_Accom.XRefAccoSysId) ,TB_MP_Inventory_Accom.* FROM TB_IC_Accomdation "
// . " LEFT JOIN TB_MP_Inventory_Accom ON TB_MP_Inventory_Accom.XRefAccoSysId=TB_IC_Accomdation.AccomSysId"
// . " WHERE TB_MP_Inventory_Accom.XRefAccoSysId=TB_IC_Accomdation.AccomSysId AND TB_IC_Accomdation.IsActive = '1' ".$where;
//
$sql = "Select t1.*, t1.Title as HotelName , (select DOccupCost from TB_MP_Inventory_Accom where"
. " Title='Luxury Room at Affordable Price' AND t1.AccomSysId=XRefAccoSysId )"
. " as DOccupCost from TB_IC_Accomdation as t1 where t1.AccomSysId IN "
. "( SELECT XRefAccoSysId from TB_MP_Inventory_Accom " . $where;
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;
}
/**
* setSaleStartStop. To set IsSaleStart record.
*
* @param string $whereCondition
* @param string $tableName
* @param int $IsSaleStart
* @return int
*/
public function setSaleStartStop($whereCondition, $tableName, $IsSaleStart = 0)
{
try {
$isChanged = $this->db->update($tableName, array('IsSaleStart' => $IsSaleStart), $whereCondition);
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
return @$isChanged ? $isChanged : 0;
}
/**
* setApproveDisapprove. To set IsActive/IsDeactive record.
*
* @param string $whereCondition
* @param string $tableName
* @param int $isActive
* @return int
*/
public function setApproveDisapprove($whereCondition, $tableName, $updateArr)
{
try {
$isChanged = $this->db->update($tableName, $updateArr, $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 * From TB_IC_Accomdation where 1 = 1 " ;
$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 getSuppliersList($cityId, $intLoggedinUserId)
{
$sql = " SELECT distinct(TB_IC_Supplier.SupplierSysId), TB_IC_Supplier.SupplierName FROM TB_IC_Supplier "
. " RIGHT JOIN TB_MP_Inventory_Accom ON TB_MP_Inventory_Accom.SupplierSysId=TB_IC_Supplier.SupplierSysId WHERE 1 =1 AND TB_IC_Supplier.IsDelete='0' ";
if (!empty($cityId)) {
$sql .= " AND TB_IC_Supplier.CitySysId = " . $cityId;
}
if (!empty($intLoggedinUserId)) {
$sql .= " AND TB_IC_Supplier.AgentSysId = " . $intLoggedinUserId;
}
$sql .= " AND TB_IC_Supplier.Services LIKE '___1%' ";
$sql .= " ORDER BY TB_IC_Supplier.SupplierName ";
//echo $sql; exit;
return $this->db->query($sql)->fetchAll();
}
public function getHotelInventoryAgentList($intLoggedinUserAgencySysId)
{
$sql = " SELECT distinct(TB_MP_Inventory_Accom.XRefAccoSysId) AS hotelId, TB_IC_Accomdation.Title
FROM TB_MP_Inventory_Accom
INNER JOIN TB_IC_Accomdation ON TB_MP_Inventory_Accom.XRefAccoSysId = TB_IC_Accomdation.AccomSysId
WHERE 1=1 ";
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND TB_MP_Inventory_Accom.AgencySysId = " . $intLoggedinUserAgencySysId;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
$sql .= " ORDER BY Title ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getHotelInventoryListByCityId($cityId, $intLoggedinUserAgencySysId)
{
$sql = "SELECT tb2.AccomSysId as hotelId, tb2.Title
FROM TB_MP_Inventory_Accom AS tbl1
left JOIN TB_IC_Accomdation AS tb2 ON (tb2.AccomSysId=tbl1.XRefAccoSysId)
WHERE 1=1 AND tbl1.IsMarkForDelete = '0' ";
if (!empty($cityId)) {
$sql .= " AND tb2.CitySysId = '" . $cityId . "'";
}
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND tbl1.AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
}
$sql .= " GROUP BY tb2.AccomSysId, tb2.Title";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getHotelSearchListByCityId($cityId = '', $intLoggedinUserAgencySysId = NULL)
{
$sql = "SELECT tb2.AccomSysId as AccomSysId, tb2.Title, tb2.ICSourceSysId
FROM TB_IC_Accomdation AS tb2 WHERE 1=1 AND tb2.IsMarkForDel = '0' ";
if (!empty($cityId)) {
$sql .= " AND tb2.CitySysId = '" . $cityId . "'";
}
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND tb2.ICSourceSysId = '" . $intLoggedinUserAgencySysId . "' ";
}
//$sql .= " ORDER BY tb2.Title ASC";
return $this->db->query($sql)->fetchAll();
}
public function getTotalInventoryByHotelId($AccomSysId, $RoomTypeId)
{
$sql = " SELECT SUM(DailyRoomLimit) AS total_inventory,SUM(SoldQty) as sold_inventory, XRefAccoSysId FROM TB_MP_Inventory_Accom WHERE 1=1 ";
if (!empty($AccomSysId)) {
$sql .= " AND TB_MP_Inventory_Accom.XRefAccoSysId = " . $AccomSysId;
}
if (!empty($RoomTypeId)) {
$sql .= " AND TB_MP_Inventory_Accom.EconomyType = " . $RoomTypeId;
}
$sql .= " AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
$sql .= " GROUP BY XRefAccoSysId ";
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getTotalRooms($intLoggedinUserAgencySysId)
{
$sql = " Select SUM(QTY) AS total_rooms From TB_IC_Accomdation_Room
Where AccomSysId In(
SELECT distinct(XRefAccoSysId) AS XRefAccoSysId FROM TB_MP_Inventory_Accom
WHERE 1=1 AND TB_MP_Inventory_Accom.AgencySysId = '" . $intLoggedinUserAgencySysId . "'
AND TB_MP_Inventory_Accom.IsMarkForDelete = '0')
AND IsDelete='0' ";
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getTotalInventoryRooms($intLoggedinUserAgencySysId)
{
$sql = " SELECT SUM(DailyRoomLimit) AS total_inventory, SUM(SoldQty) AS total_sold_inventory, SUM(DOccupCost) as total_doccup_cost
FROM TB_MP_Inventory_Accom
WHERE 1=1 AND TB_MP_Inventory_Accom.AgencySysId = '" . $intLoggedinUserAgencySysId . "' AND TB_MP_Inventory_Accom.IsMarkForDelete = '0' ";
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getSoldTotalInventoryRooms($intLoggedinUserAgencySysId)
{
$sql = " SELECT SUM(SoldQty) AS total_sold_inventory, SUM(DOccupCost) as total_doccup_cost
FROM TB_MP_Inventory_Accom WHERE
XRefAccoSysId IN (SELECT XRefAccoSysId FROM TB_MP_Inventory_Accom
WHERE 1=1 AND TB_MP_Inventory_Accom.AgencySysId = '" . $intLoggedinUserAgencySysId . "' AND SoldQty!='0' AND TB_MP_Inventory_Accom.IsMarkForDelete = '0') ";
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getRoomMealPlanSysID($intAccomSysId, $intRoomTypeId)
{
$sql = " SELECT TB_Master_Accom_MealType.MealTypeId, TB_Master_Accom_MealType.Title AS mealPlan "
. "FROM TB_IC_Accomdation_Room INNER JOIN TB_Master_Accom_MealType ON TB_IC_Accomdation_Room.MealPlanType=TB_Master_Accom_MealType.MealTypeId"
. " WHERE TB_IC_Accomdation_Room.AccomSysId = " . $intAccomSysId . " AND TB_IC_Accomdation_Room.RoomType = " . $intRoomTypeId;
//echo $sql; die;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getHotelListDetailsByHotelId($intAccomSysId)
{
$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, CurrencyType, MinPublishedFare, MinCommissionEarned, IsDayLightApp
,GeoLat, GeoLong, Address, PinCode, TB_IC_Accomdation.ContSysId, CitySysId, TB_Master_Geo_City.Title as CityName,
TB_Master_Geo_Country.Title as CountryName, TB_IC_Accomdation.ZoneSysId, PrimaryContact, SecondaryContact, OtherContacts,
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, IsLocked, 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 ";
$sql .= " AND AccomSysId = '" . $intAccomSysId . "' ";
$sql .= " ORDER BY " . $this->orderByField . " " . $this->orderBy;
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getInventoryCityList($intLoggedinUserAgencySysId)
{
$sql = " SELECT tb3.CityId AS CityId, tb3.Title FROM TB_IC_Accomdation AS tb1 "
. "LEFT JOIN TB_Master_Geo_City AS tb3 ON tb1.CitySysId = tb3.CityId WHERE 1 = 1 ";
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND tb1.ICSourceSysId = " . $intLoggedinUserAgencySysId;
}
$sql .= " AND tb3.IsMarkForDel = '0' AND tb3.Title!='0' AND tb1.IsMarkForDel = '0' ";
$sql .= " ORDER BY tb3.Title";
//echo $sql; die;
$result = $this->db->query($sql)->fetchAll();
//echo "<pre>"; print_r($result); die;
return $result;
}
public function getHotelCityList($intLoggedinUserId)
{
$sql = " SELECT distinct(tb3.CityId) AS CityId, tb3.Title FROM TB_IC_Accomdation AS tb2 "
. "LEFT JOIN TB_Master_Geo_City AS tb3 ON tb2.CitySysId = tb3.CityId WHERE 1 = 1 ";
if (!empty($intLoggedinUserId)) {
$sql .= " AND tb2.ICSourceSysId = " . $intLoggedinUserId;
}
$sql .= " AND tb3.IsMarkForDel = '0' AND tb3.Title!='0' ";
$sql .= " ORDER BY tb3.Title";
//echo $sql; die;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getHotelInventoryTotalSoldCost($intLoggedinAgencyId)
{
$sql = " SELECT TMIA.InvnItemSysId, TMIA.DailyRoomLimit, TMIA.CostCurrency, TMIA.TrxCurrency, TMIA.DOccupCost
FROM TB_MP_Inventory_Accom AS TMIA
WHERE 1=1 AND TMIA.AgencySysId='" . $intLoggedinAgencyId . "' AND TMIA.IsMarkForDelete = '0' AND TMIA.SoldQty!='0' ";
$resData = $this->db->query($sql)->fetchAll();
$total_st_cost = 0;
foreach ($resData as $arrHotelInventory) {
//Cost Conversion
if ($arrHotelInventory['CostCurrency'] != $arrHotelInventory['TrxCurrency']) {
$objCurrency = new Travel_Model_TblCurrency();
$rate = $objCurrency->getConvertionRate($arrHotelInventory['CostCurrency'], $arrHotelInventory['TrxCurrency']);
//echo "<pre>";print_r($currencyDetails); die;
$TotalCost = $arrHotelInventory['DailyRoomLimit'] * ($arrHotelInventory['DOccupCost'] * $rate);
} else {
$TotalCost = $arrHotelInventory['DailyRoomLimit'] * $arrHotelInventory['DOccupCost'];
}
$total_st_cost = $total_st_cost + $TotalCost;
}
return $total_st_cost;
}
public function getHotelInventoryTotalCost($intLoggedinAgencyId)
{
$sql = " SELECT TMIA.InvnItemSysId, TMIA.DailyRoomLimit, TMIA.CostCurrency, TMIA.TrxCurrency, TMIA.DOccupCost
FROM TB_MP_Inventory_Accom AS TMIA
WHERE 1=1 AND TMIA.AgencySysId='" . $intLoggedinAgencyId . "' AND TMIA.IsMarkForDelete = '0' ";
//echo $sql; exit;
$resData = $this->db->query($sql)->fetchAll();
$total_st_cost = 0;
foreach ($resData as $arrHotelInventory) {
//Cost Conversion
if ($arrHotelInventory['CostCurrency'] != $arrHotelInventory['TrxCurrency']) {
$objCurrency = new Travel_Model_TblCurrency();
$rate = $objCurrency->getConvertionRate($arrHotelInventory['CostCurrency'], $arrHotelInventory['TrxCurrency']);
//echo "<pre>";print_r($currencyDetails); die;
$TotalCost = $arrHotelInventory['DailyRoomLimit'] * ($arrHotelInventory['DOccupCost'] * $rate);
} else {
$TotalCost = $arrHotelInventory['DailyRoomLimit'] * $arrHotelInventory['DOccupCost'];
}
$total_st_cost = $total_st_cost + $TotalCost;
}
return $total_st_cost;
}
public function getAgencyMarkupSettingDetails($intLoggedinUserAgencySysId, $maketPlaceId)
{
$agencyMarkup = array('AgencySysId', 'MPType', 'Currency', 'MarkUpType', 'IsSameForAll', 'MarkUp', 'DOccupMarkUp', 'SOccupMarkUp', 'ExtraBedMarkUp', 'ExtraWOBedMarkUp', 'Tax', 'IsActive');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Accom"), $agencyMarkup);
$select->where("tbl.AgencySysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.MPType = ?", $maketPlaceId);
$select->where("tbl.ItemSourceType = ?", 0);
//echo $select; die;
$result = $this->db->fetchRow($select);
return $result;
}
public function addAccomdationOtherImages($data)
{
$this->db->insert('TB_IC_Accomdation_Images', $data);
return $this->db->lastInsertId('TB_IC_Accomdation_Images');
}
public function getHotelOtherImaages($intAccomSysId)
{
$agencyMarkup = array('*');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Accomdation_Images"), $agencyMarkup);
$select->where("tbl.AccomSysId = ?", $intAccomSysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function deleteHotelOtherImaages($intAccomSysId)
{
$field = array('IsActive' => '0', 'IsMarkForDel' => '1');
$where = array('AccomSysId = ? ' => $intAccomSysId);
$this->db->update('TB_IC_Accomdation_Images', $field, $where);
}
public function getHotelListDetails()
{
// $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, PriceRange, 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 getAllHotelListAdmin()
{
$select = $this->db->select()->from(array("t1" => "TB_IC_Accomdation"), array("AccomSysId", "Title", "ICSourceSysId", "Stars", "Rating", "IsApproved", "IsActive", "CheckinTime", "CheckOutTime"));
$select->joinLeft(array('t2' => "TB_Master_Geo_City"), "t1.CitySysId = t2.CityId", array("Title as CityName"));
$select->joinLeft(array('t5' => "TB_Master_Geo_Country"), "t1.ContSysId = t5.ContId", array("Title as CountryName"));
$select->joinLeft(array('t6' => "TB_Master_EconomyType"), "t6.EconomyType = t1.EconomyType", array("Title as EconomyTypeTitle"));
$sql = "";
$sql .= " 1 = 1 ";
if (!empty($this->searchArr)) {
if ($this->searchArr['HotelName']) {
$sql .= " AND t1.Title LIKE '%" . $this->searchArr['HotelName'] . "%' ";
}
if ($this->searchArr['DestinationId']) {
$sql .= " AND t1.CitySysId = '" . $this->searchArr['DestinationId'] . "' ";
}
if ($this->searchArr['rating']) {
$sql .= " AND t1.Stars IN( " . $this->searchArr['rating'] . ")";
}
if ($this->searchArr['IsActive'] != '') {
$sql .= " AND t1.IsActive = " . $this->searchArr['IsActive'] . "";
}
}
if (!empty($sql)) {
$select->where($sql);
}
$select->where("t1.IsMarkForDel =?", 0);
$select->order("t1.AccomSysId DESC");
//echo $select;exit;
return $select;
}
public function getHotelDetailsById($intAccomSysId)
{
$agencyMarkup = array('Title');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Accomdation"), $agencyMarkup);
$select->where("tbl.AccomSysId = ?", $intAccomSysId);
$select->where("tbl.IsActive = ?", 1);
$result = $this->db->fetchAll($select);
return $result;
}
public function checkHotelInPackage($intAccomSysId)
{
$agencyMarkup = array('TPIntSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_TravelPlan_Itenary_Accom"), $agencyMarkup);
$select->where("tbl.AccoSysId = ?", $intAccomSysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function checkMaster($intAccomSysId)
{
$agencyMarkup = array('AccomSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Accomdation"), $agencyMarkup);
$select->where("tbl.AccomSysId = ?", $intAccomSysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function updateHotelInAccom($hotelId, $AgencySysId)
{
$update = array('IsActive' => 0, 'IsMarkForDel' => 1, 'IsApproved' => 0);
$updateI = array('IsActive' => 0, 'IsMarkForDelete' => 1);
$updateR = array('IsActive' => 0, 'IsDelete' => 1, 'IsApproved' => 0);
$checkMasterHotel = $this->checkMaster($hotelId);
if (empty($checkMasterHotel)) {
$this->db->update('TB_IC_Accomdation', $update, array('AccomSysId = ?' => $hotelId, 'ICSourceSysId = ?' => $AgencySysId));
$this->db->update('TB_MP_Inventory_Accom', $updateI, array('XRefAccoSysId = ?' => $hotelId));
$this->db->update('TB_IC_Accomdation_Room', $updateR, array('AccomSysId = ?' => $hotelId));
return 1;
} else {
return '2';
}
}
public function getHotelInventoryListByDestination($cityId, $intLoggedinUserAgencySysId)
{
$sql = "SELECT tb2.AccomSysId AS hotelId,tb2.Title,tb4.Title AS RoomType,tb5.Title AS City_title,tb6.Title AS Country_title,tb2.SupplierSysId,tb2.CurrencyType
FROM TB_IC_Accomdation AS tb2
JOIN TB_IC_Accomdation_Room AS tb3 ON tb2.AccomSysId = tb3.AccomSysId
JOIN TB_Master_RoomType AS tb4 ON tb4.RoomType = tb3.RoomType
JOIN TB_Master_Geo_City AS tb5 ON tb5.CityId = tb2.CitySysId
JOIN TB_Master_Geo_Country AS tb6 ON tb6.ContId = tb2.ContSysId";
$sql .= " AND tb2.CitySysId = '" . $cityId . "'";
$sql .= " AND tb2.ICSourceSysId = '" . $intLoggedinUserAgencySysId . "'";
$sql .= " AND tb2.IsMarkFordel = '0'";
$sql .= " ORDER BY tb2.AccomSysId DESC";
return $this->db->query($sql)->fetchAll();
}
public function getexistingHotelListByDestination($cityId, $intLoggedinUserAgencySysId)
{
$sql = "SELECT tb2.AccomSysId AS hotelId,tb2.Title,tb2.CitySysId,tb2.ContSysId,tb2.Stars,tb2.PrimaryContact,tb2.SecondaryContact,tb2.EmailID,tb2.SupplierSysId,tb4.Title AS RoomType,tb5.Title AS City_title
FROM TB_IC_Accomdation AS tb2
JOIN TB_IC_Accomdation_Room AS tb3 ON tb2.AccomSysId = tb3.AccomSysId
JOIN TB_Master_RoomType AS tb4 ON tb4.RoomType = tb3.RoomType
JOIN TB_Master_Geo_City AS tb5 ON tb5.CityId = tb2.CitySysId";
$sql .= " AND tb2.CitySysId = '" . $cityId . "'";
$sql .= " AND tb2.ICSourceSysId = '" . $intLoggedinUserAgencySysId . "'";
$sql .= " AND tb2.IsMarkFordel = '0'";
$sql .= " ORDER BY tb2.AccomSysId DESC";
return $this->db->query($sql)->fetchAll();
}
public function CheckExistingHotel($intAccomSysId,$intLoggedinUserAgencySysId){
$agencyMarkup = array('AccomSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Accomdation"), $agencyMarkup);
$select->where("tbl.AccomSysId = ?", $intAccomSysId);
$select->where("tbl.ICSourceSysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$result = $this->db->fetchRow($select);
return $result;
}
public function CheckExistingHotelBYName($intLoggedinUserAgencySysId,$hotel,$cityId){
$result = null;
if(!empty($hotel) && !empty($cityId)){
$agencyMarkup = array('AccomSysId','RateType');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Accomdation"), $agencyMarkup);
$select->where("tbl.Title = ?", trim($hotel));
$select->where("tbl.CitySysId = ?", $cityId);
$select->where("tbl.ICSourceSysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$result = $this->db->fetchRow($select);
}
return $result;
}
public function getMainSuppliers($intLoggedinUserAgencySysId)
{
$agencyMarkup = array('SupplierSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Supplier"), $agencyMarkup);
$select->where("tbl.XRefAgencySysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.SupplierName != ?",'');
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsDelete = ?", 0);
$result = $this->db->fetchRow($select);
return $result['SupplierSysId'];
}
public function CheckSuppliers($intLoggedinUserAgencySysId,$SupplierEmail)
{
$agencyMarkup = array('SupplierSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_IC_Supplier"), $agencyMarkup);
$select->where("tbl.XRefAgencySysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.EmailId = ?",$SupplierEmail);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsDelete = ?", 0);
$result = $this->db->fetchRow($select);
return $result['SupplierSysId'];
}
public function updateHotelData($hotel_id, $arrdata,$intLoggedinUserAgencySysId) {
$SupplierSysId = $arrdata['SupplierSysId'];
$CurrencyType = $arrdata['CurrencyType'];
$update = array('SupplierSysId' => $SupplierSysId,'CurrencyType'=>$CurrencyType);
try {
$this->db->update('TB_IC_Accomdation', $update, array('AccomSysId = ?' =>$hotel_id, 'ICSourceSysId = ?' => $intLoggedinUserAgencySysId));
return true;
} catch (Exception $e) {
return false;
}
}
public function getCountryIdByCityId($cityid){
$select = $this->db->select()
->from(array('t' => 'TB_Master_Geo_City'),array(''));
$select->joinLeft(array('t2' => "TB_Master_Geo_Country"), "t.ContSysId = t2.ContId", array('ContId'));
$select->where("t.CityId =?", $cityid);
$select->where("t.IsActive =?", 1);
$select->where("t.IsMarkForDel =?", 0);
$row = $this->db->fetchRow($select);
return $row['ContId'];
}
public function getcurrencyType($Currency){
$select = $this->db->select()
->from(array('t' => 'TB_Master_Currency'),array('CurrencyType'));
$select->where("t.Symbol =?", $Currency);
$select->where("t.IsActive =?", 1);
$select->where("t.IsDelete =?", 0);
$row = $this->db->fetchRow($select);
return $row['CurrencyType'];
}
public function getcurrencySymbol($Currency_type){
$select = $this->db->select()
->from(array('t' => 'TB_Master_Currency'),array('Symbol'));
$select->where("t.CurrencyType =?", $Currency_type);
$select->where("t.IsActive =?", 1);
$select->where("t.IsDelete =?", 0);
$row = $this->db->fetchRow($select);
return $row['Symbol'];
}
}