public List <DTO.SalePerformanceDTO> GetSalePerformance(string season, out Library.DTO.Notification notification) { notification = new Library.DTO.Notification { Type = Library.DTO.NotificationType.Success }; List <DTO.SalePerformanceDTO> data = new List <DTO.SalePerformanceDTO>(); try { using (MIEurofarPriceOverviewRptEntities context = CreateContext()) { data = converter.DB2DTO_SalePerformanceList(context.MIEurofarPriceOverviewRpt_function_GetSalePerformance(season, null).ToList()); } } catch (Exception ex) { notification.Type = Library.DTO.NotificationType.Error; notification.Message = Library.Helper.GetInnerException(ex).Message; } return(data); }
public DTO.HtmlReportData GetHtmlReportData(string Season, out Library.DTO.Notification notification) { DTO.HtmlReportData htmlReportData = new DTO.HtmlReportData(); notification = new Library.DTO.Notification() { Type = Library.DTO.NotificationType.Success }; try { using (MIEurofarPriceOverviewRptEntities context = CreateContext()) { var CustomerData = context.MIEurofarPriceOverviewRpt_MarginCustomer_HTML_View.Where(o => o.Season == Season).ToList(); var SupplierData = context.MIEurofarPriceOverviewRpt_Supplier_HTML_View.Where(o => o.Season == Season).ToList(); htmlReportData.CustomerData = converter.DB2DTO_CustomerData(CustomerData); htmlReportData.SupplierData = converter.DB2DTO_SupplierData(SupplierData); decimal ExRate = Convert.ToDecimal(supportFactory.GetSettingValue(Season, "ExRate-EUR-USD"), new System.Globalization.CultureInfo("en-US")); //decimal ExRate = 1.16m; // Calculation foreach (DTO.CustomerData dtoCustomer in htmlReportData.CustomerData) { //SaleAmountConvertToEUR if (ExRate != 0) { dtoCustomer.SaleAmountConvertToEUR = dtoCustomer.SaleAmountEUR + (dtoCustomer.SaleAmountUSD / ExRate); } //NetGrossMargin dtoCustomer.NetGrossMargin = dtoCustomer.FOBAmountUSD - dtoCustomer.FOBCostUSD - dtoCustomer.DamagesCost - dtoCustomer.CommissionAmountUSD - dtoCustomer.LCCostAmount - dtoCustomer.InterestAmount - dtoCustomer.InspectionCostUSD - dtoCustomer.SampleCostUSD - dtoCustomer.TransportationInUSD; //NetGrossMarginInPercentage if (dtoCustomer.FOBCostUSD != 0) { dtoCustomer.NetGrossMarginInPercentage = dtoCustomer.NetGrossMargin * 100 / dtoCustomer.FOBCostUSD; } } var dbSaleData = from p in htmlReportData.CustomerData group p by new { p.SaleNM } into g select new { SaleNM = g.Key.SaleNM, OrderQnt40HC = g.Sum(x => x.OrderQnt40HC), FOBCostUSD = g.Sum(x => x.FOBCostUSD), SaleAmountEUR = g.Sum(x => x.SaleAmountEUR), SaleAmountUSD = g.Sum(x => x.SaleAmountUSD), SaleAmountConvertToEUR = g.Sum(x => x.SaleAmountConvertToEUR), TotalTransportEUR = g.Sum(x => x.TotalTransportEUR), FOBAmountUSD = g.Sum(x => x.FOBAmountUSD), CommissionAmountUSD = g.Sum(x => x.CommissionAmountUSD), ShippedQnt40HC = g.Sum(x => x.ShippedQnt40HC), ToBeShippedQnt40HC = g.Sum(x => x.ToBeShippedQnt40HC), LCCostAmount = g.Sum(x => x.LCCostAmount), InterestAmount = g.Sum(x => x.InterestAmount), InspectionCostUSD = g.Sum(x => x.InspectionCostUSD), SampleCostUSD = g.Sum(x => x.SampleCostUSD), TransportationInUSD = g.Sum(x => x.TransportationInUSD) }; htmlReportData.SaleData = new List <DTO.SaleData>(); DTO.SaleData dtoSaleData = new DTO.SaleData(); foreach (var item in dbSaleData) { dtoSaleData = new DTO.SaleData(); dtoSaleData.SaleNM = item.SaleNM; dtoSaleData.OrderQnt40HC = item.OrderQnt40HC; dtoSaleData.FOBCostUSD = item.FOBCostUSD; dtoSaleData.SaleAmountEUR = item.SaleAmountEUR; dtoSaleData.SaleAmountUSD = item.SaleAmountUSD; dtoSaleData.SaleAmountConvertToEUR = item.SaleAmountConvertToEUR; dtoSaleData.TotalTransportEUR = item.TotalTransportEUR; dtoSaleData.FOBAmountUSD = item.FOBAmountUSD; dtoSaleData.LCCostAmount = item.LCCostAmount; dtoSaleData.InterestAmount = item.InterestAmount; dtoSaleData.InspectionCostUSD = item.InspectionCostUSD; dtoSaleData.TransportationInUSD = item.TransportationInUSD; dtoSaleData.SampleCostUSD = item.SampleCostUSD; if (item.FOBAmountUSD != 0 && item.FOBAmountUSD > 0) { dtoSaleData.CommissionPercentage = item.CommissionAmountUSD / item.FOBAmountUSD * 100; } dtoSaleData.CommissionAmountUSD = item.CommissionAmountUSD; dtoSaleData.DamagesCost = item.FOBCostUSD * 1.25m / 100; dtoSaleData.NetGrossMargin = item.FOBAmountUSD - item.FOBCostUSD - dtoSaleData.DamagesCost - item.CommissionAmountUSD - item.LCCostAmount - item.InterestAmount - item.InspectionCostUSD - item.SampleCostUSD - item.TransportationInUSD; if (item.FOBCostUSD != 0 && item.FOBCostUSD > 0) { dtoSaleData.NetGrossMarginInPercentage = dtoSaleData.NetGrossMargin * 100 / item.FOBCostUSD; } dtoSaleData.ShippedQnt40HC = item.ShippedQnt40HC; if (item.OrderQnt40HC != 0 && item.OrderQnt40HC > 0) { dtoSaleData.ShippedContInPercentage = item.ShippedQnt40HC / item.OrderQnt40HC * 100; dtoSaleData.ToBeShippedContInPercentage = item.ToBeShippedQnt40HC / item.OrderQnt40HC * 100; } dtoSaleData.ToBeShippedQnt40HC = item.ToBeShippedQnt40HC; htmlReportData.SaleData.Add(dtoSaleData); } } return(htmlReportData); } catch (Exception ex) { notification.Type = Library.DTO.NotificationType.Error; notification.Message = ex.Message; notification.DetailMessage.Add(ex.Message); if (ex.GetBaseException() != null) { notification.DetailMessage.Add(ex.GetBaseException().Message); } return(htmlReportData); } }