public static List <eBlackListedDomainM> GetBlackListDomains(jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <eBlackListedDomainM> macroarea = new List <eBlackListedDomainM>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var blacklistedDomains = (from m in db.tblBlackListedDomains
join u in db.tblUserMs on m.iActionBy equals u.iUserId into S1
from user in S1.DefaultIfEmpty()
select new eBlackListedDomainM
{
sDomainName = m.sDomainName,
cStatus = m.cStatus,
dtActionDate = m.dtActionDate,
iActionBy = m.iActionBy,
sActionBy = user.sFirstName + " " + user.sLastName
}
)
.Distinct()
.Where(u => u.sDomainName.Contains(param.sSearch) ||
u.sActionBy.Contains(param.sSearch))
.AsQueryable();
TotalCount = blacklistedDomains.Count();
switch (param.iSortingCols)
{
case 0:
blacklistedDomains = param.sortDirection == "asc" ? blacklistedDomains.OrderBy(u => u.sDomainName)
: blacklistedDomains.OrderByDescending(u => u.sDomainName);
break;
case 1:
blacklistedDomains = param.sortDirection == "asc" ? blacklistedDomains.OrderBy(u => u.sActionBy)
: blacklistedDomains.OrderByDescending(u => u.sActionBy);
break;
case 2:
blacklistedDomains = param.sortDirection == "asc" ? blacklistedDomains.OrderBy(u => u.cStatus)
: blacklistedDomains.OrderByDescending(u => u.cStatus);
break;
default:
blacklistedDomains = blacklistedDomains.OrderBy(u => u.sDomainName);
break;
}
var data = blacklistedDomains.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
return(data);
}
}
//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);
}
public static List <etblRoomRateRangeM> GetRateRanges(jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <etblRoomRateRangeM> macroarea = new List <etblRoomRateRangeM>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblMacroArea =
(from m in db.tblRoomRateRangeMs
join u in db.tblUserMs on m.iActionBy equals u.iUserId into S1
from user in S1.DefaultIfEmpty()
select new etblRoomRateRangeM
{
iRoomRateRangeId = m.iRangeId,
dAmountFrom = m.dAmountFrom.Value,
dAmountTo = m.dAmountTo.Value,
cStatus = m.cStatus,
iActionBy = m.iActionBy,
sActionByName = user.sFirstName + " " + user.sLastName,
dtActionDate = m.dtActionDate
}).Where(x => x.sActionByName.Contains(param.sSearch) ||
x.dAmountFrom.ToString().Contains(param.sSearch) ||
x.dAmountTo.ToString().Contains(param.sSearch)).AsQueryable();
TotalCount = objTblMacroArea.Count();
switch (param.iSortingCols)
{
case 0:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.iRoomRateRangeId) : objTblMacroArea.OrderByDescending(u => u.iRoomRateRangeId);
break;
case 1:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.dAmountFrom) : objTblMacroArea.OrderByDescending(u => u.dAmountFrom);
break;
case 2:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.dAmountTo) : objTblMacroArea.OrderByDescending(u => u.dAmountTo);
break;
case 4:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.dtActionDate) : objTblMacroArea.OrderByDescending(u => u.dtActionDate);
break;
default:
objTblMacroArea = objTblMacroArea.OrderBy(u => u.sActionByName);
break;
}
var data = objTblMacroArea.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
return(data);
}
}
public static List <eChannelManager> getRecordForSearch(OneFineRateAppUtil.jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <eChannelManager> ChannelManager = new List <eChannelManager>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblUser = (from PCM in db.tblPropertyChannelMgrMaps
join U in db.tblUserMs on PCM.iActionBy equals U.iUserId into S1
from Users in S1.DefaultIfEmpty()
join P in db.tblPropertyMs on PCM.iPropId equals P.iPropId into Prop
from Property in Prop.DefaultIfEmpty()
join CM in db.tblChannelMgrMs on PCM.iChannelMgr equals CM.iChannelMgr into ChMgr
from channelmanager in ChMgr.DefaultIfEmpty()
join C in db.tblCityMs on Property.iCityId equals C.iCityId into Cit
from City in Cit.DefaultIfEmpty()
select new
{
PCM.iPropId,
sHotelName = Property.sHotelName + " , " + City.sCity,
channelmanager.sChannelMgrName,
PCM.dtActionDate,
sActionBy = Users.sFirstName + " " + Users.sLastName
}).Distinct().Where(u => u.sHotelName.Contains(param.sSearch) || u.sChannelMgrName.Contains(param.sSearch)).AsQueryable();
//get Total Count for show total records
TotalCount = objTblUser.Count();
switch (param.iSortingCols)
{
case 0:
objTblUser = param.sortDirection == "asc" ? objTblUser.OrderBy(u => u.sHotelName) : objTblUser.OrderByDescending(u => u.sHotelName);
break;
case 1:
objTblUser = param.sortDirection == "asc" ? objTblUser.OrderBy(u => u.sChannelMgrName) : objTblUser.OrderByDescending(u => u.sChannelMgrName);
break;
}
////implemented paging
//List<tblUserM> lstUser = objTblUser.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
var lstUser = objTblUser.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
//convert bll entity object to db entity object
ChannelManager.AddRange(lstUser.Select(usr => (eChannelManager)OneFineRateAppUtil.clsUtils.ConvertToObject(usr, new eChannelManager())));
return(ChannelManager);
}
}
public static List <eExchange> getRecordForSearch(OneFineRateAppUtil.jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <eExchange> Exchange = new List <eExchange>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblUser = (from ER in db.tblExchangeRatesMs
join U in db.tblUserMs on ER.iActionBy equals U.iUserId into S1
from Users in S1.DefaultIfEmpty()
join C1 in db.tblCurrencyMs on ER.sCurrencyCodeFrom equals C1.sCurrencyCode into Curr1
from Currency1 in Curr1.DefaultIfEmpty()
join C2 in db.tblCurrencyMs on ER.sCurrencyCodeTo equals C2.sCurrencyCode into Curr2
from Currency2 in Curr2.DefaultIfEmpty()
select new
{
sCurrencyCodeFrom = ER.sCurrencyCodeFrom + " - " + Currency1.sCurrency,
sCurrencyCodeTo = ER.sCurrencyCodeTo + " - " + Currency2.sCurrency,
ER.dRate,
ER.dtActionDate,
sActionBy = Users.sFirstName + " " + Users.sLastName
}).Distinct().Where(u => u.sCurrencyCodeFrom.Contains(param.sSearch) || u.sCurrencyCodeTo.Contains(param.sSearch)).AsQueryable();
//get Total Count for show total records
TotalCount = objTblUser.Count();
switch (param.iSortingCols)
{
case 0:
objTblUser = param.sortDirection == "asc" ? objTblUser.OrderBy(u => u.sCurrencyCodeFrom) : objTblUser.OrderByDescending(u => u.sCurrencyCodeFrom);
break;
case 1:
objTblUser = param.sortDirection == "asc" ? objTblUser.OrderBy(u => u.sCurrencyCodeTo) : objTblUser.OrderByDescending(u => u.sCurrencyCodeTo);
break;
}
////implemented paging
//List<tblUserM> lstUser = objTblUser.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
var lstUser = objTblUser.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
//convert bll entity object to db entity object
Exchange.AddRange(lstUser.Select(usr => (eExchange)OneFineRateAppUtil.clsUtils.ConvertToObject(usr, new eExchange())));
return(Exchange);
}
}
public static List <eCorporateCompanyM> GetCorporateCompanies(jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <eCorporateCompanyM> macroarea = new List <eCorporateCompanyM>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblMacroArea = (from m in db.tblCorporateCompanyMs
join c in db.tblCorporateDomainMaps on m.iCompanyId equals c.iCompanyId into ccm
join u in db.tblUserMs on m.iActionBy equals u.iUserId into S1
from user in S1.DefaultIfEmpty()
select new eCorporateCompanyM
{
iCompanyId = m.iCompanyId,
sCompanyName = m.sCompanyName,
sDomainNameList = ccm.Select(y => y.sDomainName),
sDomainNames = ccm.Select(y => y.sDomainName).FirstOrDefault(),
sEmailAddress = m.sEmailAddress,
sMobileNumber = m.sMobileNumber,
sPrimaryContact = m.sPrimaryContact,
sTelephoneNumber = m.sTelephoneNumber,
cStatus = m.cStatus,
dtActionDate = m.dtActionDate,
iActionBy = m.iActionBy,
sActionBy = user.sFirstName + " " + user.sLastName,
GstnEntityType = m.sEntityType,
GstnEntityName = m.sRegisteredEntityName,
GstnNumber = m.sGstinNumber
}
)
.Where(u => u.sCompanyName.Contains(param.sSearch) ||
u.sDomainNameList.Contains(param.sSearch) ||
u.sEmailAddress.Contains(param.sSearch) ||
u.sMobileNumber.Contains(param.sSearch) ||
u.sPrimaryContact.Contains(param.sSearch) ||
u.sActionBy.Contains(param.sSearch)).AsQueryable();
TotalCount = objTblMacroArea.Count();
switch (param.iSortingCols)
{
case 0:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sCompanyName) : objTblMacroArea.OrderByDescending(u => u.sCompanyName);
break;
case 1:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sPrimaryContact) : objTblMacroArea.OrderByDescending(u => u.sPrimaryContact);
break;
case 2:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sTelephoneNumber) : objTblMacroArea.OrderByDescending(u => u.sTelephoneNumber);
break;
case 3:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sMobileNumber) : objTblMacroArea.OrderByDescending(u => u.sMobileNumber);
break;
case 4:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sEmailAddress) : objTblMacroArea.OrderByDescending(u => u.sEmailAddress);
break;
case 5:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sDomainNames) : objTblMacroArea.OrderByDescending(u => u.sDomainNames);
break;
case 6:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.cStatus) : objTblMacroArea.OrderByDescending(u => u.cStatus);
break;
default:
objTblMacroArea = objTblMacroArea.OrderBy(u => u.sCompanyName);
break;
}
var data = objTblMacroArea.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
foreach (var item in data)
{
item.sDomainNames = string.Join("<br/>", item.sDomainNameList);
}
return(data);
}
}
public static List <eServiceChargeM> GetServiceCharges(jQueryDataTableParamModel param, out int TotalCount)
{
using (OneFineRateEntities db = new OneFineRateEntities())
{
List <eServiceChargeM> macroarea = new List <eServiceChargeM>();
param.sSearch = param.sSearch == null ? "" : param.sSearch;
var objTblMacroArea =
(from m in db.tblServiceChargeMs
join u in db.tblUserMs on m.iActionBy equals u.iUserId into S1
from user in S1.DefaultIfEmpty()
select new eServiceChargeM
{
ServiceChargeId = m.iServiceChargeId,
dtFrom = m.dtFrom,
dtTo = m.dtTo,
Status = m.cStatus,
ServiceCharge = m.dServiceCharge,
GstValueType = m.cGstValueType,
GstValue = m.dGstValue,
ActionById = m.iActionBy,
ActionByName = user.sFirstName + " " + user.sLastName,
ActionDate = m.dtActionDate
}).Where(u => u.GstValue.ToString().Contains(param.sSearch) ||
u.GstValueType.Contains(param.sSearch) ||
u.ActionByName.Contains(param.sSearch) ||
u.ServiceCharge.ToString().Contains(param.sSearch)).AsQueryable();
TotalCount = objTblMacroArea.Count();
switch (param.iSortingCols)
{
case 0:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.ServiceChargeId) : objTblMacroArea.OrderByDescending(u => u.ServiceChargeId);
break;
case 1:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.ServiceCharge) : objTblMacroArea.OrderByDescending(u => u.ServiceCharge);
break;
case 2:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.GstValueType) : objTblMacroArea.OrderByDescending(u => u.GstValueType);
break;
case 3:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.GstValue) : objTblMacroArea.OrderByDescending(u => u.GstValue);
break;
case 4:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.From) : objTblMacroArea.OrderByDescending(u => u.From);
break;
case 5:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.To) : objTblMacroArea.OrderByDescending(u => u.To);
break;
case 6:
objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.ActionDate) : objTblMacroArea.OrderByDescending(u => u.ActionDate);
break;
default:
objTblMacroArea = objTblMacroArea.OrderBy(u => u.ActionByName);
break;
}
var data = objTblMacroArea.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();
foreach (var item in data)
{
item.From = item.dtFrom.Value.ToString("dd/MM/yyyy");
item.To = item.dtTo.Value.ToString("dd/MM/yyyy");
}
return(data);
}
}
