public async Task <SalesDashboardRes> GetSalesDashboardSummary([FromBody] SalesDashboardReq request) { var response = new SalesDashboardRes(); try { if (request != null) { response = await _MISRepository.GetSalesDashboardSummary(request); if (response == null) { response.ResponseStatus.Status = "Failure"; response.ResponseStatus.ErrorMessage = "An error."; } } else { response.ResponseStatus.Status = "Failure"; response.ResponseStatus.ErrorMessage = "Request details can not be blank."; } } catch (Exception ex) { response.ResponseStatus.Status = "Failure"; response.ResponseStatus.ErrorMessage = "An error occurs " + ex.Message.ToString(); } return(response); }
public async Task <BookingsDashboardRes> GetBookingsDashboardSummary(SalesDashboardReq request, string ticket) { BookingsDashboardRes salesDashboardRes = new BookingsDashboardRes(); salesDashboardRes = await serviceProxy.PostData(_configuration.GetValue <string>("ServiceMIS:GetBookingsDashboardSummary"), request, typeof(BookingsDashboardRes), ticket); return(salesDashboardRes); }
public BookingsDashboardRes GetBookingsDashboardSummary(SalesDashboardReq request) { BookingsDashboardRes response = new BookingsDashboardRes(); try { List <string> statusList = new List <string> { "C", "!", "J" }; #region filters //FilterDefinition<Bookings> filter = Builders<Bookings>.Filter.Empty; //if (!string.IsNullOrWhiteSpace(request.SalesOfficeID) && !string.IsNullOrWhiteSpace(request.SalesOffice)) //{ // var company = _MongoContext.mCompanies.AsQueryable().Where(a => a.Company_Id == request.SalesOfficeID).FirstOrDefault(); // var SalesUserList = company?.ContactDetails?.Select(a => a.MAIL).ToList(); // filter = filter & Builders<mQuote>.Filter.Where(x => SalesUserList.Contains(x.SalesPerson)); //} //if (!string.IsNullOrWhiteSpace(request.DestinationID) && !string.IsNullOrWhiteSpace(request.Destination)) //{ // filter = filter & Builders<mQuote>.Filter.Regex(x => x.AgentProductInfo.Destination, new BsonRegularExpression(new Regex(request.Destination))); //} //if (!string.IsNullOrWhiteSpace(request.AgentID) && !string.IsNullOrWhiteSpace(request.Agent)) //{ // filter = filter & Builders<mQuote>.Filter.Eq(x => x.AgentInfo.AgentName, request.Agent.Trim()); //} //if (!string.IsNullOrWhiteSpace(request.SalesPersonID) && !string.IsNullOrWhiteSpace(request.SalesPerson)) //{ // filter = filter & Builders<mQuote>.Filter.Eq(x => x.SalesPerson, request.SalesPersonID.Trim()); //} #endregion var resSales = _MongoContext.Bookings.AsQueryable().Where(a => !statusList.Contains(a.STATUS)).ToList().Select(a => new SalesDashboardData { QRFID = a.BookingNumber, AdultPax = a.BookingPax.Where(b => b.PERSTYPE == "ADULT").FirstOrDefault()?.PERSONS, //Budget = 1,//a.AgentProductInfo.BudgetAmount, SalesOfficer = a.StaffDetails?.Staff_SalesUser_Name, SalesOffice = a.AgentInfo?.Division_Name, //a.AgentProductInfo.Division, //check SalesOfficeID = a.AgentInfo?.Division_ID, //a.AgentProductInfo.DivisionID, //check //SalesValue = (Budget per person X currency conversion to EUR) X no of pax in "Twin" and "Double" rooms X no of departures SalesValue = 1, //CalculateSalesValue(a), //check NoOfDepartures = 1, CreateDate = a.AuditTrail.CREA_DT, StatusDate = a.AuditTrail.MODI_DT, Age = (DateTime.Now.Subtract(Convert.ToDateTime(a.AuditTrail.MODI_DT)).Days) // / 7 }).ToList(); var monthlist = CurrentFinancialYear(DateTime.Now, "yyyy-MM"); int year = Convert.ToInt16(monthlist?[0].Substring(0, 4)); DateTime FinStartDate = new DateTime(year, 4, 1); DateTime FinEndDate = new DateTime(year + 1, 3, 31); response.BookingsDashboardSummary = new BookingsDashboardSummary { FinancialYearMonths = CurrentFinancialYear(DateTime.Now), BookingVolumeGraph = resSales.Where(a => !string.IsNullOrEmpty(a.SalesOffice) && (a.CreateDate >= FinStartDate && a.CreateDate <= FinEndDate)) .GroupBy(a => new { a.CreateDate.Value.Year, a.CreateDate.Value.Month }) .Select(a => new PassengerForecastGraph { MonthYear = Convert.ToDateTime(a.FirstOrDefault().CreateDate).ToString("yyyy-MM"), PaxDetails = a.GroupBy(b => b.SalesOffice).Select(b => new SalesOfficeWiseDetailsGraph { SalesOffice = b.FirstOrDefault().SalesOffice, Quotes = b.Count(), TotalPax = Convert.ToInt32(b.Sum(c => c.AdultPax)), SalesValue = Convert.ToDouble(b.Sum(c => c.SalesValue)) }).OrderBy(b => b.SalesOffice).ToList() }).ToList(), BookingVolumeGrid = resSales.Where(a => !string.IsNullOrEmpty(a.SalesOffice) && (a.CreateDate >= FinStartDate && a.CreateDate <= FinEndDate)) .GroupBy(a => a.SalesOffice) .Select(a => new PassengerForecastGrid { SalesOffice = a.FirstOrDefault().SalesOffice, PaxDetails = a.GroupBy(b => new { b.CreateDate.Value.Month, b.CreateDate.Value.Year }).Select(b => new SalesOfficeWiseDetailsGrid { MonthYear = Convert.ToDateTime(b.FirstOrDefault().CreateDate).ToString("MMM yyyy"), Quotes = b.Count(), TotalPax = Convert.ToInt32(b.Sum(c => c.AdultPax)), SalesValue = Convert.ToDouble(b.Sum(c => c.SalesValue)) }).ToList() }).OrderBy(a => a.SalesOffice).ToList(), }; response.BookingsDashboardSummary.SalesOfficeList = response.BookingsDashboardSummary.BookingVolumeGrid.Select(a => a.SalesOffice).ToList(); monthlist.ForEach(b => { if (response.BookingsDashboardSummary.BookingVolumeGraph.Where(a => a.MonthYear == b).Count() < 1) { response.BookingsDashboardSummary.BookingVolumeGraph.Add(new PassengerForecastGraph { MonthYear = b, PaxDetails = new List <SalesOfficeWiseDetailsGraph>() }); } }); } catch (System.Exception ex) { Console.WriteLine(ex.Message); } return(response); }
public async Task <SalesDashboardRes> GetSalesDashboardSummary(SalesDashboardReq request) { SalesDashboardRes response = new SalesDashboardRes(); try { FilterDefinition <mQuote> filter = Builders <mQuote> .Filter.Empty; if (!string.IsNullOrWhiteSpace(request.SalesOfficeID) && !string.IsNullOrWhiteSpace(request.SalesOffice)) { var company = _MongoContext.mCompanies.AsQueryable().Where(a => a.Company_Id == request.SalesOfficeID).FirstOrDefault(); var SalesUserList = company?.ContactDetails?.Select(a => a.MAIL).ToList(); filter = filter & Builders <mQuote> .Filter.Where(x => SalesUserList.Contains(x.SalesPerson)); } if (!string.IsNullOrWhiteSpace(request.DestinationID) && !string.IsNullOrWhiteSpace(request.Destination)) { filter = filter & Builders <mQuote> .Filter.Regex(x => x.AgentProductInfo.Destination, new BsonRegularExpression(new Regex(request.Destination))); } if (!string.IsNullOrWhiteSpace(request.AgentID) && !string.IsNullOrWhiteSpace(request.Agent)) { filter = filter & Builders <mQuote> .Filter.Eq(x => x.AgentInfo.AgentName, request.Agent.Trim()); } if (!string.IsNullOrWhiteSpace(request.SalesPersonID) && !string.IsNullOrWhiteSpace(request.SalesPerson)) { filter = filter & Builders <mQuote> .Filter.Eq(x => x.SalesPerson, request.SalesPersonID.Trim()); } var resSales = _MongoContext.mQuote.Find(filter).ToList().Select(a => new SalesDashboardData { QRFID = a.QRFID, Destination = a.AgentProductInfo.Destination, Customer = a.AgentInfo.AgentName, //adultpax = no of departures X no of Adults AdultPax = (a.Departures.Where(b => b.IsDeleted == false).Count()) * (a.AgentPassengerInfo.Where(b => b.Type == "ADULT").Select(b => b.count).FirstOrDefault()), QRFStatus = a.CurrentPipeline, Budget = a.AgentProductInfo.BudgetAmount, SalesOfficer = a.SalesPersonUserName, SalesOffice = a.AgentProductInfo.Division, SalesOfficeID = a.AgentProductInfo.DivisionID, BusinessType = a.AgentProductInfo.Type, BaseCurrency = a.ExchangeRateSnapshot.REFCUR, //SalesValue = (Budget per person X currency conversion to EUR) X no of pax in "Twin" and "Double" rooms X no of departures SalesValue = CalculateSalesValue(a), CostValue = 0, NoOfDepartures = a.Departures.Where(b => b.IsDeleted == false).Count(), InvoiceValue = 0, CreateDate = a.Departures.Where(b => b.IsDeleted == false).OrderBy(b => b.Date).Select(b => b.Date).FirstOrDefault(), StatusDate = a.EditDate, Age = (DateTime.Now.Subtract(Convert.ToDateTime(a.EditDate)).Days) // / 7 }).ToList(); var SAP = resSales.Where(a => "Quote Pipeline,Amendment Pipeline".Contains(a.QRFStatus)).ToList(); var CAP = resSales.Where(a => "Costing Pipeline,Costing Approval Pipeline".Contains(a.QRFStatus)).ToList(); var AAP = resSales.Where(a => a.QRFStatus == "Agent Approval Pipeline").ToList(); var GAP = resSales.Where(a => a.QRFStatus == "Handover Pipeline").ToList(); var monthlist = CurrentFinancialYear(DateTime.Now, "yyyy-MM"); int year = Convert.ToInt16(monthlist?[0].Substring(0, 4)); DateTime FinStartDate = new DateTime(year, 4, 1); DateTime FinEndDate = new DateTime(year + 1, 3, 31); response.SalesDashboardSummary = new SalesDashboardSummary { #region Basic Fields SAPQuotes = SAP.Count, SAPPax = Convert.ToInt32(SAP.Sum(b => b.AdultPax)), SAPValue = Convert.ToDouble(SAP.Sum(b => b.SalesValue)), SAPAge1Week = SAP.Count <= 0 ? 0 : (decimal)((SAP.Where(b => b.Age >= 0 && b.Age <= 7).Count()) * 100) / SAP.Count, //% of Quotes Aging 1 Week SAPAge2Week = SAP.Count <= 0 ? 0 : (decimal)((SAP.Where(b => b.Age >= 8 && b.Age <= 14).Count()) * 100) / SAP.Count, //% of Quotes Aging 2 Week SAPAge2PlusWeek = SAP.Count <= 0 ? 0 : (decimal)((SAP.Where(b => b.Age > 14).Count()) * 100) / SAP.Count, //% of Quotes Aging 2+ Week CAPQuotes = CAP.Count, CAPPax = Convert.ToInt32(CAP.Sum(b => b.AdultPax)), CAPValue = Convert.ToDouble(CAP.Sum(b => b.SalesValue)), CAPAge1Week = CAP.Count <= 0 ? 0 : (decimal)((CAP.Where(b => b.Age >= 0 && b.Age <= 7).Count()) * 100) / CAP.Count, //% of Quotes Aging 1 Week CAPAge2Week = CAP.Count <= 0 ? 0 : (decimal)((CAP.Where(b => b.Age >= 8 && b.Age <= 14).Count()) * 100) / CAP.Count, //% of Quotes Aging 2 Week CAPAge2PlusWeek = CAP.Count <= 0 ? 0 : (decimal)((CAP.Where(b => b.Age > 14).Count()) * 100) / CAP.Count, //% of Quotes Aging 2+ Week AAPQuotes = AAP.Count, AAPPax = Convert.ToInt32(AAP.Sum(b => b.AdultPax)), AAPValue = Convert.ToDouble(AAP.Sum(b => b.SalesValue)), AAPAge1Week = AAP.Count <= 0 ? 0 : (decimal)((AAP.Where(b => b.Age >= 0 && b.Age <= 7).Count()) * 100) / AAP.Count, //% of Quotes Aging 1 Week AAPAge2Week = AAP.Count <= 0 ? 0 : (decimal)((AAP.Where(b => b.Age >= 8 && b.Age <= 14).Count()) * 100) / AAP.Count, //% of Quotes Aging 2 Week AAPAge2PlusWeek = AAP.Count <= 0 ? 0 : (decimal)((AAP.Where(b => b.Age > 14).Count()) * 100) / AAP.Count, //% of Quotes Aging 2+ Week GAPQuotes = GAP.Count, GAPPax = Convert.ToInt32(GAP.Sum(b => b.AdultPax)), GAPValue = Convert.ToDouble(GAP.Sum(b => b.SalesValue)), GAPAge1Week = GAP.Count <= 0 ? 0 : (decimal)((GAP.Where(b => b.Age >= 0 && b.Age <= 7).Count()) * 100) / GAP.Count, //% of Quotes Aging 1 Week GAPAge2Week = GAP.Count <= 0 ? 0 : (decimal)((GAP.Where(b => b.Age >= 8 && b.Age <= 14).Count()) * 100) / GAP.Count, //% of Quotes Aging 2 Week GAPAge2PlusWeek = GAP.Count <= 0 ? 0 : (decimal)((GAP.Where(b => b.Age > 14).Count()) * 100) / GAP.Count, //% of Quotes Aging 2+ Week BaseCurrency = resSales.Where(a => !string.IsNullOrEmpty(a.BaseCurrency)).Select(a => a.BaseCurrency).FirstOrDefault(), FinancialYearMonths = CurrentFinancialYear(DateTime.Now), #endregion PassengerForecastGraph = resSales.Where(a => !string.IsNullOrEmpty(a.SalesOffice) && (a.CreateDate >= FinStartDate && a.CreateDate <= FinEndDate)) .GroupBy(a => new { a.CreateDate.Value.Year, a.CreateDate.Value.Month }) .Select(a => new PassengerForecastGraph { MonthYear = Convert.ToDateTime(a.FirstOrDefault().CreateDate).ToString("yyyy-MM"), PaxDetails = a.GroupBy(b => b.SalesOffice).Select(b => new SalesOfficeWiseDetailsGraph { SalesOffice = b.FirstOrDefault().SalesOffice, Quotes = b.Count(), TotalPax = Convert.ToInt32(b.Sum(c => c.AdultPax)), SalesValue = Convert.ToDouble(b.Sum(c => c.SalesValue)) }).OrderBy(b => b.SalesOffice).ToList() }).ToList(), PassengerForecastGrid = resSales.Where(a => !string.IsNullOrEmpty(a.SalesOffice) && (a.CreateDate >= FinStartDate && a.CreateDate <= FinEndDate)) .GroupBy(a => a.SalesOffice) .Select(a => new PassengerForecastGrid { SalesOffice = a.FirstOrDefault().SalesOffice, PaxDetails = a.GroupBy(b => new { b.CreateDate.Value.Month, b.CreateDate.Value.Year }).Select(b => new SalesOfficeWiseDetailsGrid { MonthYear = Convert.ToDateTime(b.FirstOrDefault().CreateDate).ToString("MMM yyyy"), Quotes = b.Count(), TotalPax = Convert.ToInt32(b.Sum(c => c.AdultPax)), SalesValue = Convert.ToDouble(b.Sum(c => c.SalesValue)) }).ToList() }).OrderBy(a => a.SalesOffice).ToList(), }; //response.SalesDashboardSummary.SalesOfficeList = response.SalesDashboardSummary.PassengerForecastGrid.Select(a => a.SalesOffice.Replace(" ", "_")).ToList(); response.SalesDashboardSummary.SalesOfficeList = response.SalesDashboardSummary.PassengerForecastGrid.Select(a => a.SalesOffice).ToList(); monthlist.ForEach(b => { if (response.SalesDashboardSummary.PassengerForecastGraph.Where(a => a.MonthYear == b).Count() < 1) { response.SalesDashboardSummary.PassengerForecastGraph.Add(new PassengerForecastGraph { MonthYear = b, PaxDetails = new List <SalesOfficeWiseDetailsGraph>() }); } }); //foreach (var month in monthlist) //{ // if (response.SalesDashboardSummary.PassengerForecast1.Where(a => a.MonthYear == month).Count() < 1) // { // response.SalesDashboardSummary.PassengerForecast1.Add(new PassengerForecast1 { MonthYear = month, PaxDetails = new List<SalesOfficeWiseDetails1>() }); // } //} } catch (System.Exception ex) { Console.WriteLine(ex.Message); } return(response); }
public IActionResult _SalesDashboardResult(SalesDashboardFilters filters) { try { #region Bind Dashboard Data SalesDashboardReq request = new SalesDashboardReq() { SalesOffice = filters.SalesOffice, SalesOfficeID = filters.SalesOfficeID, SalesPerson = filters.SalesPerson, SalesPersonID = filters.SalesPersonID, Destination = filters.Destination, DestinationID = filters.DestinationID, Agent = filters.Agent, AgentID = filters.AgentID, }; var response = objMISProviders.GetSalesDashboardSummary(request, token).Result; var sales = response.SalesDashboardSummary.PassengerForecastGraph; StringBuilder json; SalesOfficeWiseDetailsGraph paxdetail; #region Quote Forecast json = new StringBuilder(); foreach (var pax in sales.OrderBy(a => a.MonthYear)) { if (json.Length > 0) { json.Append(","); } else { json.Append("["); } //change json.Append("{ \"month\" : \""); json.Append(pax.MonthYear); json.Append("\""); foreach (var SalesOffice in response.SalesDashboardSummary.SalesOfficeList) //pax.PaxDetails) { paxdetail = pax.PaxDetails.Where(a => a.SalesOffice == SalesOffice).FirstOrDefault(); json.Append(" , \""); json.Append(SalesOffice); if (paxdetail == null || paxdetail?.Quotes == 0) { json.Append("\" : \""); json.Append(0); json.Append("\""); } else { json.Append("\" : \""); json.Append(paxdetail?.Quotes); json.Append("\""); } } json.Append(" }"); } json.Append("]"); response.SalesDashboardSummary.QuoteChartJson = json.ToString(); #endregion #region Passenger Forecast json = new StringBuilder(); foreach (var pax in sales.OrderBy(a => a.MonthYear)) { if (json.Length > 0) { json.Append(","); } else { json.Append("["); } //change json.Append("{ \"month\" : \""); json.Append(pax.MonthYear); json.Append("\""); foreach (var SalesOffice in response.SalesDashboardSummary.SalesOfficeList) //pax.PaxDetails) { paxdetail = pax.PaxDetails.Where(a => a.SalesOffice == SalesOffice).FirstOrDefault(); json.Append(" , \""); json.Append(SalesOffice); if (paxdetail == null || paxdetail?.TotalPax == 0) { json.Append("\" : \""); json.Append(0); json.Append("\""); } else { json.Append("\" : \""); json.Append(paxdetail?.TotalPax); json.Append("\""); } } json.Append(" }"); } json.Append("]"); response.SalesDashboardSummary.PassengerChartJson = json.ToString(); #endregion #region Passenger Forecast json = new StringBuilder(); foreach (var pax in sales.OrderBy(a => a.MonthYear)) { if (json.Length > 0) { json.Append(","); } else { json.Append("["); } //change json.Append("{ \"month\" : \""); json.Append(pax.MonthYear); json.Append("\""); foreach (var SalesOffice in response.SalesDashboardSummary.SalesOfficeList) //pax.PaxDetails) { paxdetail = pax.PaxDetails.Where(a => a.SalesOffice == SalesOffice).FirstOrDefault(); json.Append(" , \""); json.Append(SalesOffice); if (paxdetail == null || paxdetail?.SalesValue == 0) { json.Append("\" : \""); json.Append(0); json.Append("\""); } else { json.Append("\" : \""); json.Append(paxdetail?.SalesValue); json.Append("\""); } } json.Append(" }"); } json.Append("]"); response.SalesDashboardSummary.RevenueChartJson = json.ToString(); #endregion return(View(response.SalesDashboardSummary)); #endregion } catch (Exception ex) { Console.WriteLine(ex.Message); return(View(new SalesDashboardSummary())); } }