public OrderStats getOrderStatsById(int id)
{
SqlParameter[] prams = { database.MakeInParam("@id", SqlDbType.Int, 0, id), };
string sql = "SELECT tos.*, tt.name AS name1, tt.unit AS unit1 FROM tb_order_stats AS tos, tb_typeOfProcess AS tt " +
" WHERE tos.id = @id AND tos.type_of_process = tt.id ";
try
{
DataSet dataset = database.RunProcReturn(sql, prams, "tb_order_stats");
int count = 0;
if (null != dataset && (count = dataset.Tables[0].Rows.Count) > 0)
{
int i = 0;
OrderStats stats = parseOrderStats(dataset, i);
return(stats);
}
}
catch (Exception e)
{
Util.showError(e.Message);
}
return(null);
}
private Order getOrder()
{
Order order = constructOrder();
Console.WriteLine("dataGridViewItem.Rows.Count:" + dataGridViewItem.Rows.Count);
int i = 0;
foreach (DataGridViewRow row in dataGridViewItem.Rows)
{
if (row.IsNewRow)
{
continue;
}
i++;
OrderItem item = constructOrderItem(order, row);
order.OrderItems.AddLast(item);
}
Console.WriteLine("i:" + i);
Console.WriteLine("dataGridViewItemStats.Rows.Count: " + dataGridViewItemStats.Rows.Count);
foreach (DataGridViewRow row in dataGridViewItemStats.Rows)
{
if (row.IsNewRow)
{
continue;
}
OrderStats stats = constructOrderStats(order, row);
order.OrderStats.AddLast(stats);
}
return(order);
}
public void fillOrderStats(Order order)
{
SqlParameter[] prams = { database.MakeInParam("@order", SqlDbType.Int, 0, order.Id), };
string sql = "SELECT tos.*, tt.name AS name1, tt.unit AS unit1 FROM tb_order_stats AS tos, tb_typeOfProcess AS tt " +
" WHERE tos.[order] = @order AND tos.type_of_process = tt.id ";
try
{
DataSet dataset = database.RunProcReturn(sql, prams, "tb_order_stats");
int count = 0;
if (null != dataset && (count = dataset.Tables[0].Rows.Count) > 0)
{
int i = 0;
for (i = 0; i < count; i++)
{
OrderStats stats = parseOrderStats(dataset, i);
stats.Order = order;
order.OrderStats.AddLast(stats);
}
}
}
catch (Exception e)
{
Util.showError(e.Message);
}
}
public override async Task ExecuteAsync(CloudTable cloudTable = null)
{
// Store data for analytics about amount each type of events in each hour.
Console.WriteLine("...Generate some sample data...");
var createOrderStats1 = new OrderStats
{
PartitionKey = "CREATE_ORDER",
RowKey = DateTime.Now.Hour.ToString(),
Count = 10
};
var createOrderStats2 = new OrderStats
{
PartitionKey = "CREATE_ORDER",
RowKey = DateTime.Now.AddHours(1).ToString(),
Count = 10
};
var startWatchingOrderStats1 = new OrderStats
{
PartitionKey = "START_WATCHING",
RowKey = DateTime.Now.Hour.ToString(),
Count = 8
};
var startWatchingOrderStats2 = new OrderStats
{
PartitionKey = "START_WATCHING",
RowKey = DateTime.Now.AddHours(2).ToString(),
Count = 8
};
await cloudTable.ExecuteAsync(TableOperation.Insert(createOrderStats1));
await cloudTable.ExecuteAsync(TableOperation.Insert(createOrderStats2));
await cloudTable.ExecuteAsync(TableOperation.Insert(startWatchingOrderStats1));
await cloudTable.ExecuteAsync(TableOperation.Insert(startWatchingOrderStats2));
Console.WriteLine("...Fetch data for partition...");
TableQuery <OrderStats> query1 = new TableQuery <OrderStats>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "CREATE_ORDER"));
TableQuery <OrderStats> query2 = new TableQuery <OrderStats>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "START_WATCHING"));
var data1 = await cloudTable.ExecuteQuerySegmentedAsync(query1, null);
data1.Results.ForEach(os => Console.WriteLine($"Stats for \nEvent type: {os.PartitionKey}\n\tHour: {os.RowKey}\n\tCount: {os.Count}"));
var data2 = await cloudTable.ExecuteQuerySegmentedAsync(query2, null);
data2.Results.ForEach(os => Console.WriteLine($"Stats for \nEvent type: {os.PartitionKey}\n\tHour: {os.RowKey}\n\tCount: {os.Count}"));
}
private void fillOrderStatsRow(OrderStats stats, DataGridViewRow row)
{
row.Cells["ColumnStatsID"].Value = stats.Id;
row.Cells["ColumnProcessingName"].Tag = stats.TypeOfProcess;
row.Cells["ColumnProcessingName"].Value = stats.TypeOfProcess.Name;
row.Cells["ColumnThicknessStats"].Value = stats.ThicknessStats;
row.Cells["ColumnProcessingDiagram"].Tag = stats.Image;
row.Cells["ColumnProcessingDiagram"].Value = Util.getThumbnailImage(stats.Image);
row.Cells["ColumnDWG"].Tag = stats.Dwg;
row.Cells["ColumnUnitStats"].Value = stats.Unit;
row.Cells["ColumnNumberStats"].Value = stats.TotalNumber;
row.Cells["ColumnUnitPriceStats"].Value = stats.UnitPrice;
row.Cells["ColumnCostStats"].Value = stats.AmountOfMoney;
}
private OrderStats constructOrderStats(Order order, DataGridViewRow row)
{
OrderStats stats = new OrderStats();
stats.Id = Util.getIntValue(row.Cells["ColumnStatsID"].Value.ToString());
stats.Order = order;
stats.TypeOfProcess = (TypeOfProcess)row.Cells["ColumnProcessingName"].Tag;
stats.ThicknessStats = row.Cells["ColumnThicknessStats"].Value.ToString();
stats.Image = (Image)row.Cells["ColumnProcessingDiagram"].Tag;
stats.Dwg = (byte[])row.Cells["ColumnDWG"].Tag;
stats.Unit = row.Cells["ColumnUnitStats"].Value.ToString();
stats.TotalNumber = Util.getDecimalValue(row.Cells["ColumnNumberStats"].Value.ToString());
stats.UnitPrice = Util.getDecimalValue(row.Cells["ColumnUnitPriceStats"].Value.ToString());
stats.AmountOfMoney = Util.getDecimalValue(row.Cells["ColumnCostStats"].Value.ToString());
return(stats);
}
private OrderStats parseOrderStats(DataSet dataset, int index)
{
OrderStats stats = new OrderStats();
stats.Id = getIntValue(dataset, index, "id");
stats.TypeOfProcess = new TypeOfProcess(getIntValue(dataset, index, "type_of_process"),
getValue(dataset, index, "name1"), getValue(dataset, index, "unit1"));
stats.ThicknessStats = getValue(dataset, index, "thickness");
stats.Image = getImageValue(dataset, index, "image");
stats.Dwg = DBNull.Value == dataset.Tables[0].Rows[index]["dwg"] ? null : (byte[])dataset.Tables[0].Rows[index]["dwg"];
stats.Unit = getValue(dataset, index, "unit");
stats.TotalNumber = getDecimalValue(dataset, index, "total_number");
stats.UnitPrice = getDecimalValue(dataset, index, "unit_price");
stats.AmountOfMoney = getDecimalValue(dataset, index, "amount_of_money");
return(stats);
}
private SqlParameter[] constructParamsForOrderStats(OrderStats orderStats)
{
SqlParameter[] prams =
{
database.MakeInParam("@id", SqlDbType.Int, 0, orderStats.Id),
database.MakeInParam("@order", SqlDbType.Int, 0, orderStats.Order.Id),
database.MakeInParam("@type_of_process", SqlDbType.Int, 0, orderStats.TypeOfProcess.Id),
database.MakeInParam("@thickness", SqlDbType.VarChar, 20, orderStats.ThicknessStats),
database.MakeInParam("@image", SqlDbType.Image, 0, null == orderStats.Image ? DBNull.Value : (object)Util.imageToByteArray(orderStats.Image)),
database.MakeInParam("@dwg", SqlDbType.Image, 0, null == orderStats.Dwg ? DBNull.Value : (object)orderStats.Dwg),
database.MakeInParam("@unit", SqlDbType.NVarChar, 10, orderStats.Unit),
database.MakeInParam("@total_number", SqlDbType.Decimal, 0, orderStats.TotalNumber),
database.MakeInParam("@unit_price", SqlDbType.Money, 0, orderStats.UnitPrice),
database.MakeInParam("@amount_of_money", SqlDbType.Money, 0, orderStats.AmountOfMoney),
new SqlParameter("@message", SqlDbType.NVarChar, 50),
new SqlParameter("rval", SqlDbType.Int, 4)
};
prams[prams.Length - 2].Direction = ParameterDirection.Output;
prams[prams.Length - 1].Direction = ParameterDirection.ReturnValue;
return(prams);
}
public async Task <OrderStats> GetOrdersStats()
{
OrderStats orderStats = new OrderStats();
var orders = await unitOfWork.OrderRepository.GetOnlyOrders();
if (orders == null || orders.Count() == 0)
{
return(orderStats);
}
int len = 6;
var ordersByLastMonths = new List <OrdersByMonth>(len);
var today = DateTime.Now;
var firstIndex = today.AddMonths(-6);
string[] monthsTranslate = new string[12] {
"Styczeń", "Luty", "Marzec", "Kwiecień", "Maj", "Czerwiec", "Lipiec", "Sierpień",
"Wrzesień", "Pażdziernik", "Listopad", "Grudzień"
};
for (int i = 5; i >= 0; i--)
{
var _month = today.AddMonths(-i);
var counter = orders.Where(x => x.OrderCreationDate?.Month == _month.Month &&
x.OrderCreationDate?.Year == _month.Year).Count();
ordersByLastMonths.Add(new OrdersByMonth(monthsTranslate[_month.Month - 1] + " " + _month.Year, counter));
}
orderStats.OrdersByLastMonths = ordersByLastMonths;
orderStats.OrdersThisMonth = ordersByLastMonths[len - 1].AmountOfOrders;
orderStats.OrdersLastMonth = ordersByLastMonths[len - 2].AmountOfOrders;
int diff = orderStats.OrdersThisMonth - orderStats.OrdersLastMonth;
orderStats.LastMonthComparerMessage =
diff >= 0 ? $"+{diff}" : diff.ToString();
int?finishedOrderStatusId = null;
var orderStatuses = await unitOfWork.OrderStatusRepository.GetOrderStatusByNameAsync("Zakończone");
if (orderStatuses != null)
{
finishedOrderStatusId = orderStatuses.Id;
}
var finishedOrders = orders.Where(x => x.OrderStatusId.Equals(finishedOrderStatusId));
if (finishedOrders == null || finishedOrders.Count() == 0)
{
orderStats.TimelyDeliveriesRatio = 100;
}
else
{
int all = finishedOrders.Count();
int finishedOnTimeCounter = finishedOrders
.Where(x => x.OrderRealizationDate?.Date <= x.OrderExpectedDate?.Date).Count();
decimal ratio = (finishedOnTimeCounter / all) * 100;
orderStats.TimelyDeliveriesRatio = (int)ratio;
}
return(orderStats);
}
