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