public async System.Threading.Tasks.Task <JsonResult> get_open_order()
{
List <Models.get_open_order> item_list_sku = new List <Models.get_open_order>();
List <Models.get_open_order> item_list = new List <Models.get_open_order>();
var ss = new ShipStationController();
var result = await ss.open_order();
for (int i = 0; i < result.orders.Length; i++)
{
var temp = new Models.get_open_order();
temp.orderID = result.orders[i].orderId;
temp.orderStatus = result.orders[i].orderStatus;
temp.orderDate = result.orders[i].modifyDate.ToString();
temp.item_num = result.orders[i].items.Length;
for (int j = 0; j < temp.item_num; j++)
{
var sku_temp = new Models.get_open_order();
sku_temp.sku = result.orders[i].items[j].sku;
sku_temp.count_qty = result.orders[i].items[j].quantity;
temp.sku += result.orders[i].items[j].sku + "<br/>";
temp.item_name += result.orders[i].items[j].name + "<br/>";
temp.qty += +result.orders[i].items[j].quantity + "<br/>";
item_list_sku.Add(sku_temp);
}
item_list.Add(temp);
}
//in stock product
var stock_breakdown = (from t in db.ts_stock where t.status == "Ready_for_Shipping" group t by t.sku into g select new { sku = g.Key, count_qtys = (from t in g select t.sku).Count() }).ToList();
//open orders
var online_breakdown = (from t in item_list_sku group t by t.sku into g select new { sku = g.Key, count_qty = (from t in g select t.count_qty).Sum() }).ToList();
var online_sku_only = (from t in item_list_sku select t.sku).Distinct();
//combine
var item_breakdown = (from t in online_sku_only join t2 in stock_breakdown on t equals t2.sku select new { sku = t, t2.count_qtys }).ToList();
var monthly_data = db.Database.SqlQuery <Models.Techsoup_Model>("select Count(sku) as numberofitem from ship_log where sku like '%HW-%' AND shipdate >= DATE_SUB(CURDATE(), INTERVAL 30 day) group by date_format(shipdate,'%Y-%m-%d %H')").ToList();
var number = (from t in monthly_data select t.numberofitem).ToList();
// var pallet_count = from l in db.pallet group l by l.pallet_name into g select new { pallet_name = g.Key, Count = (from l in g select l.pallet_name).Distinct().Count() };
// return Json(new {order = result.orders}, JsonRequestBehavior.AllowGet);
return(Json(new { month_graph = number, item_list = item_list, online_breakdown = online_breakdown, stock_breakdown = stock_breakdown, item_breakdown = item_breakdown }, JsonRequestBehavior.AllowGet));
}
//dropdown menu from in stock detail
public async System.Threading.Tasks.Task <JsonResult> get_sku_detail(string sku)
{
var production_sku = sku.Replace("HW-", "TSG_");
var ss = new ShipStationController();
var result = await ss.open_order();
List <Models.get_open_order> item_list = new List <Models.get_open_order>();
List <Models.get_open_order> item_list_sku = new List <Models.get_open_order>();
for (int i = 0; i < result.orders.Length; i++)
{
var temp = new Models.get_open_order();
temp.orderID = result.orders[i].orderId;
temp.orderStatus = result.orders[i].orderStatus;
temp.orderDate = result.orders[i].modifyDate.ToString();
temp.item_num = result.orders[i].items.Length;
for (int j = 0; j < temp.item_num; j++)
{
var sku_temp = new Models.get_open_order();
sku_temp.sku = result.orders[i].items[j].sku;
sku_temp.count_qty = result.orders[i].items[j].quantity;
temp.sku += result.orders[i].items[j].sku + "<br/>";
temp.item_name += result.orders[i].items[j].name + "<br/>";
temp.qty += result.orders[i].items[j].quantity + "<br/>";
item_list_sku.Add(sku_temp);
}
item_list.Add(temp);
}
//open orders
var online_breakdown = (from t in item_list_sku group t by t.sku into g select new { sku = g.Key, count_qty = (from t in g select t.count_qty).Sum() }).ToList();
var today = DateTime.Today.AddDays(1);
var month_ago = DateTime.Today.AddDays(-30);
var yesterday_sametime = DateTime.Now.AddHours(-24);
var now = DateTime.Now;
var pending = (from t in online_breakdown where t.sku == sku select t.count_qty);
var onhand = (from t in db.ts_stock where t.sku == sku && t.status == "Ready_for_Shipping" select t).Count();
var avg_month = (from a in db.ship_log where a.sku == sku && a.shipdate <today && a.shipdate> month_ago select a).ToList();
var pull_last_month = (from a in db.ts_stock where a.update_time != null && a.update_time <today && a.update_time> month_ago && a.sku == sku select a).Count();
var item_name = (from t in item_list where t.sku == sku select t.item_name).FirstOrDefault();
var production = (from t in db.production_log where t.channel == production_sku && t.time <now && t.time> yesterday_sametime select t).Count();
var production_detail = (from t in db.production_log where t.channel == production_sku && t.time <now && t.time> yesterday_sametime select t).ToList();
var monthly_data = db.Database.SqlQuery <Models.Techsoup_Model>("select Count(sku) as numberofitem from ship_log where sku='" + sku + "' AND shipdate >= DATE_SUB(CURDATE(), INTERVAL 30 day) group by date_format(shipdate,'%Y-%m-%d %H')").ToList();
var number = (from t in monthly_data select t.numberofitem).ToList();
return(Json(new { month_graph_sku = number, pulled = pull_last_month, production_detail = production_detail, production = production, name = item_name, pending_order = pending, onhand = onhand, avg_month = avg_month }, JsonRequestBehavior.AllowGet));
}
public async System.Threading.Tasks.Task <JsonResult> get_ts_stat()
{
var ss = new ShipStationController();
var weekly = db.Database.SqlQuery <ss_order.weekly_order>("select date_format(shipdate,'%Y-%m-%d') as date,Count(shipdate) as numberofitem from ship_log where sku like '%HW%' and shipdate >= DATE_SUB(CURDATE(), INTERVAL 7 day) group by date_format(shipdate,'%Y-%m-%d')"
).ToList();
var result = await ss.open_order();
var orders = result.total;
// var result = JsonConvert.DeserializeObject<Models.ShipStation_orderID.open_order.Rootobject>(open_order.ToString());
var today = DateTime.Today;
var tmr = DateTime.Today.AddDays(1);
var ship_today = (from t in db.ship_log where t.status == "Shipped" && t.sku.Contains("HW-") && t.shipdate == today select t).Count();
var add_today = (from t in db.ts_stock where t.time > DateTime.Today && t.time < tmr select t).Count();
var shipped = (from t in db.ship_log where t.status == "Shipped" && t.sku.Contains("HW-") select t).Count();
var instock = (from t in db.ts_stock where t.status == "Ready_for_Shipping" select t).Count();
var validated = (from t in db.process_run_time where t.process == "ShipStation" orderby t.time descending select t.time).FirstOrDefault();
validated = validated.AddMinutes(60);
return(Json(new { weekly = weekly, orders = orders, shipped = shipped, instock = instock, validated = validated, ship_today = ship_today, add_today = add_today }, JsonRequestBehavior.AllowGet));
}