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); }
public void Handle(string originalUrl, HttpConnectInformation connectInfo, ref bool handled) { if (originalUrl.Contains("/ImportTableData.aspx") == false) { return; } handled = true; if (connectInfo.Session["user"] == null || ((EJ.User)connectInfo.Session["user"]).Role.GetValueOrDefault().HasFlag(EJ.User_RoleEnum.数据库设计师) == false) { return; } try { int databaseid = Convert.ToInt32(connectInfo.Request.Query["dbid"]); System.IO.BinaryReader reader = new System.IO.BinaryReader(connectInfo.Request.Body); var tableNames = reader.ReadString().ToJsonObject <string[]>(); using (EJDB ejdb = new EJDB()) { var database = ejdb.Databases.FirstOrDefault(m => m.id == databaseid); var invokingDB = DBHelper.CreateInvokeDatabase(database); { //不要开事物,大数据太慢 try { if (connectInfo.Request.Query["clearDataFirst"] == "1") { foreach (var tableName in tableNames) { invokingDB.AllowIdentityInsert(tableName, true); invokingDB.ExecSqlString("delete from [" + tableName + "]"); } } Dictionary <string, string> pkNames = new Dictionary <string, string>(); for (int i = 0; i < tableNames.Length; i++) { var tableName = tableNames[i]; var db_table = ejdb.DBTable.FirstOrDefault(m => m.Name == tableName); if (db_table == null) { throw new Exception(string.Format("找不到{0}数据表定义", tableName)); } var pkcolumn = ejdb.DBColumn.FirstOrDefault(m => m.TableID == db_table.id && m.IsPKID == true); bool hasAutoColumn = ejdb.DBColumn.Count(m => m.TableID == db_table.id && m.IsAutoIncrement == true) > 0; pkNames[tableName] = pkcolumn.Name; if (pkcolumn == null) { throw (new Exception(string.Format("{0}-{1}没有设置主键", db_table.caption, db_table.Name))); } } while (true) { string _tablename = reader.ReadString(); if (_tablename == ":end") { break; } string pkname = null; if (pkNames.ContainsKey(_tablename)) { pkname = pkNames[_tablename]; } Dictionary <string, object> data = reader.ReadString().ToJsonObject <Dictionary <string, object> >(); CustomDataItem newDataItem; if (pkname != null && data.ContainsKey(pkname)) { if (Convert.ToInt32(invokingDB.ExecSqlString($"select count(*) from {invokingDB.FormatObjectName(_tablename)} where {invokingDB.FormatObjectName(pkname)}=@p0", data[pkname])) > 0) { newDataItem = new CustomDataItem(_tablename, pkname, data[pkname]); foreach (var item in data) { newDataItem.SetValue(item.Key, item.Value); } invokingDB.Update(newDataItem); continue; } } newDataItem = new CustomDataItem(_tablename, pkname, null); foreach (var item in data) { newDataItem.SetValue(item.Key, item.Value); } invokingDB.Insert(newDataItem); } } catch (Exception ex) { throw ex; } finally { foreach (var tableName in tableNames) { invokingDB.AllowIdentityInsert(tableName, false); } } } } connectInfo.Response.Write("ok\r\n"); } catch (Exception ex) { connectInfo.Response.Write($"{ex.Message}\r\n"); } }