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); } }