//Get Single Record
public static etblPropertyRoomRatePlanInventoryMap GetSingleRecordById(int roomid, int rateplanid, DateTime inventorydate)
{
etblPropertyRoomRatePlanInventoryMap eobj = new etblPropertyRoomRatePlanInventoryMap();
using (OneFineRateEntities db = new OneFineRateEntities())
{
var dbobj = (from t2 in db.tblPropertyRatePlanMaps
join t1 in db.tblPropertyRoomRatePlanInventoryMaps on t2.iRPId equals t1.iRPId into S1
from a in S1.DefaultIfEmpty()
join t3 in db.tblPropertyRoomMaps on a.iRoomId equals t3.iRoomId into S2
from b in S2.DefaultIfEmpty()
select new
{
a.dtInventoryDate,
a.iPropId,
a.iRoomId,
a.iRPId,
a.bCloseOut,
iMinLengthStay = a.iMinLengthStay == null ? t2.iMinLengthStay : a.iMinLengthStay,
iMaxLengthStay = a.iMaxLengthStay == null ? t2.iMaxLengthStay : a.iMaxLengthStay,
a.bCTA,
a.bCTD,
a.dSingleRate,
a.dDoubleRate,
a.dTripleRate,
a.dQuadrupleRate,
a.dQuintrupleRate,
a.dtActionDate,
iActionBy = t2.bLinkToExistingRatePlan == true ? 1 : 0,
t2.sRatePlan,
b.sRoomName
}).Where(u => u.dtInventoryDate == inventorydate && u.iRoomId == roomid && u.iRPId == rateplanid).AsQueryable().SingleOrDefault();
if (dbobj == null)
{
var NewdbObj = (from t2 in db.tblPropertyRatePlanMaps
join t3 in db.tblPropertyRoomMaps on t2.iPropId equals t3.iPropId into S2
from b in S2.DefaultIfEmpty()
select new
{
iActionBy = t2.bLinkToExistingRatePlan == true ? 1 : 0,
t2.sRatePlan,
b.sRoomName,
b.iRoomId,
t2.iRPId
}).Where(u => u.iRoomId == roomid && u.iRPId == rateplanid).AsQueryable().SingleOrDefault();
eobj = (etblPropertyRoomRatePlanInventoryMap)OneFineRateAppUtil.clsUtils.ConvertToObject(NewdbObj, eobj);
}
else
{
eobj = (etblPropertyRoomRatePlanInventoryMap)OneFineRateAppUtil.clsUtils.ConvertToObject(dbobj, eobj);
}
}
return(eobj);
}
//Get list of records
public static List <ePromoCodeDisp> getRecordForSearch(OneFineRateAppUtil.jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <ePromoCodeDisp> Promolst = new List <ePromoCodeDisp>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblPromo = (from a in db.tblPromoCodeMs
join c in db.tblUserMs on a.iActionBy equals c.iUserId into PC
from T in PC.DefaultIfEmpty()
join am in db.tblAmenityMs on a.iAmenityId equals am.iAmenityId into S2
from am1 in S2.DefaultIfEmpty()
join PPM in db.tblPropertyPromoMaps on a.iPromoCodeId equals PPM.iPromoCodeId into PPM1
from PPM2 in PPM1.DefaultIfEmpty()
select new
{
a.iPromoCodeId,
a.sPromoCode,
a.sPromoTitle,
a.sPromoDescription,
sAmenityName = am1.sAmenityName == null || am1.sAmenityName == "" ? (a.cPercentageValue == "1" ? a.dValue.ToString() + " %" : "Value: " + a.dValue.ToString()) : am1.sAmenityName,
a.dtBookingFrom,
a.dtBookingTo,
a.dtStayFrom,
a.dtStayTo,
a.dtActionDate,
cStatus = a.cStatus == "A" ? "Live" : "Disable",
ActionBy = T.sFirstName + " " + T.sLastName,
HotelCount = PPM1.Count()
}).Distinct().Where(u => u.sPromoCode.Contains(param.sSearch) || u.sPromoTitle.Contains(param.sSearch)).AsQueryable();
//get Total Count for show total records
TotalCount = objTblPromo.Count();
//for sorting
switch (param.iSortingCols)
{
case 0:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.sPromoCode) : objTblPromo.OrderByDescending(u => u.sPromoCode);
break;
case 3:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.dtBookingFrom) : objTblPromo.OrderByDescending(u => u.dtBookingFrom);
break;
case 4:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.dtBookingTo) : objTblPromo.OrderByDescending(u => u.dtBookingTo);
break;
case 5:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.dtStayFrom) : objTblPromo.OrderByDescending(u => u.dtStayFrom);
break;
case 6:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.dtStayTo) : objTblPromo.OrderByDescending(u => u.dtStayTo);
break;
case 7:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.cStatus) : objTblPromo.OrderByDescending(u => u.cStatus);
break;
case 8:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.ActionBy) : objTblPromo.OrderByDescending(u => u.ActionBy);
break;
case 9:
objTblPromo = param.sortDirection == "asc" ? objTblPromo.OrderBy(u => u.dtActionDate) : objTblPromo.OrderByDescending(u => u.dtActionDate);
break;
}
////implemented paging
var lstUser = objTblPromo.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
//convert bll entity object to db entity object
foreach (var item in lstUser)
{
Promolst.Add((ePromoCodeDisp)OneFineRateAppUtil.clsUtils.ConvertToObject(item, new ePromoCodeDisp()));
}
return(Promolst);
}
}
//Get list of records
public static List <etblLocalityList> getRecordForSearch(OneFineRateAppUtil.jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <etblLocalityList> locality = new List <etblLocalityList>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
//for searching
var objTblMacroArea = (from l in db.tblLocalityMs
join m in db.tblMacroAreaMs on l.iAreaId equals m.iAreaId
join c in db.tblCountryMs on l.iCountryId equals c.iCountryId
join s in db.tblStateMs on l.iStateId equals s.iStateId
join ct in db.tblCityMs on l.iCityId equals ct.iCityId
join u in db.tblUserMs on l.iActionBy equals u.iUserId into lt
from T in lt.DefaultIfEmpty()
join c1 in db.tblPropertyMs on l.iLocalityId equals c1.iLocalityId into S2
from a in S2.DefaultIfEmpty()
select new
{
l.iLocalityId,
l.iCountryId,
c.sCountry,
l.iStateId,
s.sState,
l.iCityId,
ct.sCity,
l.iAreaId,
m.sArea,
l.sLocality,
cStatus = l.cStatus == "A" ? "Live" : "Disable",
l.dtActionDate,
iActionBy = T.sFirstName + " " + T.sLastName,
iPropId = S2.Count()
}
).Distinct().Where(u => u.sCountry.Contains(param.sSearch) || u.sState.Contains(param.sSearch) ||
u.sCity.Contains(param.sSearch) || u.sArea.Contains(param.sSearch) || u.sLocality.Contains(param.sSearch)).AsQueryable();
//get Total Count for show total records
TotalCount = objTblMacroArea.Count();
//for sorting
switch (param.iSortingCols)
{
case 0:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sCountry) : objTblMacroArea.OrderByDescending(u => u.sCountry);
break;
case 1:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sState) : objTblMacroArea.OrderByDescending(u => u.sState);
break;
case 2:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sCity) : objTblMacroArea.OrderByDescending(u => u.sCity);
break;
case 3:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sArea) : objTblMacroArea.OrderByDescending(u => u.sArea);
break;
case 4:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sLocality) : objTblMacroArea.OrderByDescending(u => u.sLocality);
break;
case 5:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.cStatus) : objTblMacroArea.OrderByDescending(u => u.cStatus);
break;
}
////implemented paging
var data = objTblMacroArea.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
//convert bll entity object to db entity object
foreach (var item in data)
{
locality.Add((etblLocalityList)OneFineRateAppUtil.clsUtils.ConvertToObject(item, new etblLocalityList()));
}
return(locality);
}
}