public async Task <IEnumerable <VoidLogDto> > getVoidLog([FromBody] SearchCondition scon) { List <VoidLogDto> list = new List <VoidLogDto>(); //0. connect to DB await setConnectionString(scon.ShopId); var _auditDataService = new ShopDataService <Audit>(); var _stockDataService = new ShopDataService <Stock>(); var _staffDataService = new ShopDataService <Staff>(); //1. fetch data from DB IEnumerable <Audit> audits = await _auditDataService.Query(a => a.audit_date >= scon.DateFrom && a.audit_date <= scon.DateTo && a.tran_type == "AV"); //2. create view model foreach (var record in audits) { VoidLogDto vm = new VoidLogDto(); Stock target_stock = await _stockDataService.GetSingle(s => s.stock_id == record.stock_id); vm.item_name = target_stock.description; vm.quantity = record.movement; Staff target_staff = await _staffDataService.GetSingle(s => s.staff_id == record.source_id); vm.staff_name = target_staff.surname + " " + target_staff.given_names; list.Add(vm); } //3. return view model return(list); }
private async Task <SummaryDetailDto> GetSummaryHelper(int shopId, DateTime DateFrom, DateTime DateTo) { //0. connect to shop db var _docketDataService = new ShopDataService <Docket>(); var _returnDataService = new ShopDataService <Return>(); var _returnsLineDataService = new ShopDataService <ReturnsLine>(); var _docketLineDataService = new ShopDataService <DocketLine>(); var _paymentDataService = new ShopDataService <Payments>(); var _stockDataService = new ShopDataService <Stock>(); //1. create dto SummaryDetailDto dto = new SummaryDetailDto(); //2. fetch values from DB IEnumerable <Docket> dockets = await _docketDataService.Query(d => d.docket_date >= DateFrom && d.docket_date <= DateTo); IEnumerable <int> docket_ids = dockets.Select(d => d.docket_id).ToList(); IEnumerable <Return> returns = await _returnDataService.Query(d => d.returns_date >= DateFrom && d.returns_date <= DateTo); IEnumerable <int> returns_ids = returns.Select(r => r.returns_id).ToList(); IEnumerable <ReturnsLine> returnslines = await _returnsLineDataService.Query(rl => returns_ids.Contains(rl.returns_id)); IEnumerable <DocketLine> docketLines = await _docketLineDataService.Query(dl => docket_ids.Contains(dl.docket_id)); #region - calculate Avg_Item_Per_Sale /** calculate Avg_Item_Per_Sale */ List <double> sum_sales_by_quantity = new List <double>(); //mean: save the total quantity for every sales foreach (var temp_id in docket_ids) { double sum_dl = 0; foreach (var dl in docketLines) { if (temp_id == dl.docket_id) { sum_dl += dl.quantity; } } sum_sales_by_quantity.Add(sum_dl); } //will be called in value mapping of dto.Avg_Item_Per_Sale #endregion #region - get payment data IEnumerable <Payments> payments = await _paymentDataService.Query(p => p.docket_date >= DateFrom && p.docket_date <= DateTo); List <PaymentDetail> paymentDetails = new List <PaymentDetail>(); foreach (var item in payments.GroupBy(p => p.paymenttype)) { PaymentDetail pd = new PaymentDetail(); pd.paymenttype = item.Select(i => i.paymenttype).First(); pd.amount = item.Sum(i => i.amount); paymentDetails.Add(pd); } #endregion #region - create hourly summary double[] arr_hour_sale = new double[24]; for (int i = 1; i < 25; i++) { arr_hour_sale[i - 1] = (double)dockets.Where(d => d.docket_date.Hour > i - 1 && d.docket_date.Hour <= i).Sum(d => d.total_inc); } #endregion #region -custom data group var custom1_db = await _stockDataService.GetSingle(s => s.custom1 != null); var custom2_db = await _stockDataService.GetSingle(s => s.custom2 != null); CustomDataItem custom1 = new CustomDataItem { Name = custom1_db.custom1, Quantity = 0, Amount = 0 }; CustomDataItem custom2 = new CustomDataItem { Name = custom2_db.custom2, Quantity = 0, Amount = 0 }; CustomDataItem others = new CustomDataItem { Name = "others", Quantity = 0, Amount = 0 }; CustomDataItem extra = new CustomDataItem { Name = "extra", Quantity = 0, Amount = 0 }; foreach (var dl in docketLines) { string cat1 = _stockDataService.GetSingleEnity(s => s.stock_id == dl.stock_id).cat1; if (cat1 != "TASTE" && cat1 != "EXTRA") { if (dl.size_level == 1) { custom1.Quantity = custom1.Quantity + dl.quantity; custom1.Amount = Math.Round((custom1.Amount + (double)dl.sell_inc * dl.quantity) * 100) / 100; } else if (dl.size_level == 2) { custom2.Quantity = custom2.Quantity + dl.quantity; custom2.Amount = Math.Round((custom2.Amount + (double)dl.sell_inc * dl.quantity) * 100) / 100; } else { others.Quantity = others.Quantity + dl.quantity; others.Amount = Math.Round((others.Amount + (double)dl.sell_inc * dl.quantity) * 100) / 100; } } else { extra.Quantity = extra.Quantity + dl.quantity; extra.Amount = Math.Round((extra.Amount + (double)dl.sell_inc * dl.quantity) * 100) / 100; } } List <CustomDataItem> customer_data_sum = new List <CustomDataItem>(); customer_data_sum.Add(custom1); customer_data_sum.Add(custom2); customer_data_sum.Add(others); customer_data_sum.Add(extra); #endregion dto.Total_Sales = (double)dockets.Sum(d => d.total_inc); dto.Number_Of_Transactions = dockets.Count(); dto.Total_Refund = (double)returns.Sum(r => r.total_inc); dto.Total_Discount = (double)dockets.Sum(d => d.discount); if (dto.Number_Of_Transactions == 0) { dto.Avg_Sales_Value = 0; } else { dto.Avg_Sales_Value = Math.Round(dto.Total_Sales / dto.Number_Of_Transactions, 2); } dto.Avg_Item_Per_Sale = (sum_sales_by_quantity.Count() > 0)?Math.Round(sum_sales_by_quantity.Average(), 2):0; dto.Hourly_Sales = arr_hour_sale; dto.PaymentSum = paymentDetails; dto.CustomDataSum = customer_data_sum; //return dto return(dto); }