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