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