public virtual async Task <IEnumerable <DRM> > GetDRMsAsync()
{
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.DRMs.ToListAsync());
}
}
public async Task <IEnumerable <SRV> > GetServiceTypesAsync()
{
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.SRVs.ToListAsync());
}
}
public virtual async Task <IEnumerable <CSL> > GetCSLAsync()
{
// locations
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.CSLs.ToListAsync());
}
}
public virtual async Task <IEnumerable <SA1> > GetSA1Async()
{
// bd consultants
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.SA1.ToListAsync());
}
}
public async Task <IEnumerable <LOC> > GetLOCAsync()
{
// locations for services/suppliers
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.LOCs.ToListAsync());
}
}
public async Task <Dictionary <string, string> > GetSuppliersChainsAsync()
{
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.CRMs
.Where(crm => crm.SUPPLIERMASTER == "Y")
.ToDictionaryAsync(crm => crm.CODE, crm => crm.NAME));
}
}
public virtual async Task <IEnumerable <BHD> > GetBHDsFromDateEnteredAsync(DateTime from)
{
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.BHDs
.Where(b => b.DATE_ENTERED >= from)
.ToListAsync());
}
}
public virtual async Task <Dictionary <int, int> > GetPaxNumbersAsync()
{
// pax number
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.BSDs
.Where(bsd => bsd.BSL_ID == 0)
.ToDictionaryAsync(bsd => bsd.BHD_ID, bsd => bsd.PAX));
}
}
public async Task <List <SuppliersAnalysisData> > GetServicesAndCostsForSAD(DateTime beginDt, DateTime endDt, List <string> BookingStatuses, List <string> excludedSL)
{
using (var tpContext = new ThirdpartyDBContext())
{
// join tables BSL-BHD -> BSD
var sadList = await tpContext.BSLs
.Where(bsl => bsl.DATE >= beginDt && bsl.DATE <= endDt)
.Where(bsl => BookingStatuses.Contains(bsl.BHD.STATUS))
.Where(bsl => !excludedSL.Contains(bsl.SL_STATUS))
.Join(tpContext.BSDs, bsl => bsl.BSL_ID, bsd => bsd.BSL_ID,
(bsl, bsd) => new SuppliersAnalysisData()
{
BSL_ID = bsl.BSL_ID,
OPT_ID = bsl.OPT_ID,
// from BSL and BHD tables
DATE = bsl.DATE,
BHD_ID = bsl.BHD_ID,
FULL_REFERENCE = bsl.BHD.FULL_REFERENCE,
BookingStatus = bsl.BHD.STATUS,
CURRENCY = bsl.BHD.CURRENCY,
BASE_DIV_RATE = (double)bsl.BHD.BASE_DIV_RATE,
BASE_MUT_RATE = (double)bsl.BHD.BASE_MUT_RATE,
SL_STATUS = bsl.SL_STATUS,
Service_Type_Code = bsl.SERVICE,
Client_CODE = bsl.BHD.AGENT,
Client_NAME = bsl.BHD.DRM.NAME,
LOCATION_CODE = bsl.LOCATION,
BDConsultant_CODE = bsl.BHD.SALE1,
// from BSD table
COST = (double)bsd.COST,
ROOMCOUNT = bsd.ROOMCOUNT,
SCU_FOC = bsd.SCU_FOC,
SCU_QTY = bsd.SCU_QTY,
// calculate £ cost
Base_Cost = (double)(bsd.COST * bsl.BHD.BASE_MUT_RATE / bsl.BHD.BASE_DIV_RATE),
})
.ToListAsync();
return(sadList);
}
}
public async Task <List <SuppliersAnalysisData> > GetSuppliersFromOptsAsync(IEnumerable <int> listOpt)
{
using (var tpContext = new ThirdpartyDBContext())
{
return(await tpContext.OPTs
.Where(o => listOpt.Contains(o.OPT_ID))
.Join(tpContext.CRMs, o => o.SUPPLIER, c => c.CODE,
(o, c) => new SuppliersAnalysisData
{
OPT_ID = o.OPT_ID,
SUPPLIER_CODE = o.SUPPLIER,
SUPPLIER_NAME = c.NAME,
CHAIN_CODE = c.SUPPLIERCHAIN
})
.ToListAsync());
}
}
private IEnumerable <BkgsFollowUp> ExtractAllFollowUp()
{
// extract all the bookings that fit the criterias
// attention there can be bookings with multiple service line being "follow up"
ThirdpartyDBContext te = new ThirdpartyDBContext();
//ReportingModel rm = new ReportingModel(te,"FollowUp");
// find the option_ID related to the "OPTION" service line
int Opt_ID = te.OPTs.Where(o => o.CODE.Trim() == "OPTION").Select(o => o.OPT_ID).FirstOrDefault();
// find all the service lines being "OPTION"
IEnumerable <BSL> BSLSelected = te.BSLs.Where(bs => bs.OPT_ID == Opt_ID);
// method using dictionaries
Dictionary <string, string> _SA1 = te.SA1.ToDictionary(s => s.CODE, s => s.DESCRIPTION);
Dictionary <string, string> _CSL = te.CSLs.ToDictionary(c => c.INITIALS, c => c.NAME);
// creation of a dictionary for BHDs
// filter by Date after the "01/02/2015" and booking status belonging to { "PE" , "Q" , "SO" }
DateTime BeginDate = new DateTime(2015, 2, 1);
List <string> bStatus = new List <string>()
{
"PE", "Q", "SO"
};
IEnumerable <BHD> _BHDfiltered = te.BHDs.Where(b => b.DATE_ENTERED >= BeginDate && (bStatus.Contains(b.STATUS.Trim())));
//IEnumerable<BHD> _BHDfiltered = te.BHDs.Where(b => b.DATE_ENTERED >= BeginDate);
Dictionary <int, BHD> _BHDdict = _BHDfiltered.ToDictionary(b => b.BHD_ID, b => b);
// for each BSL in BSLSelected create the obj BkgsFollowUp
Func <BSL, BkgsFollowUp> ProjectToBkgsFollowUp = _bsl =>
{
// incase of BHD is not in the dictionary
if (!_BHDdict.ContainsKey(_bsl.BHD_ID))
{
return(null);
}
BkgsFollowUp bk = new BkgsFollowUp();
bk.AgentCode = _BHDdict[_bsl.BHD_ID].AGENT;
bk.BDConsultant = _SA1[_BHDdict[_bsl.BHD_ID].SALE1];
bk.BookingName = _BHDdict[_bsl.BHD_ID].NAME;
bk.BookingRef = _BHDdict[_bsl.BHD_ID].FULL_REFERENCE;
bk.BookingStatus = _BHDdict[_bsl.BHD_ID].STATUS;
bk.BookingType = _BHDdict[_bsl.BHD_ID].SALE6;
bk.Booking_Date_Entered = _BHDdict[_bsl.BHD_ID].DATE_ENTERED;
bk.Consultant = _CSL[_BHDdict[_bsl.BHD_ID].CONSULTANT];
bk.Estimated_Turnover = _BHDdict[_bsl.BHD_ID].UDTEXT3;
bk.FollowUpdate = _bsl.DATE;
bk.TravelDate = _BHDdict[_bsl.BHD_ID].TRAVELDATE;
return(bk);
};
IEnumerable <BkgsFollowUp> _BksSelected = BSLSelected.Select(_bsl => ProjectToBkgsFollowUp(_bsl)).ToList();
// remove the null bkgs
_BksSelected = _BksSelected.Where(b => b != null).OrderBy(b => b.FollowUpdate);
return(_BksSelected);
}