private static TDatatables GetHCDataBu(string selYear, string bu) { int nYear = 0; int.TryParse(selYear, out nYear); var res = new TDatatables(); res.kinds = GetHCRows(); using (var context = new WarehouseLaborEffEntities()) { var qry = (from c in context.v_tbl_hcdata where 0 == string.Compare(c.Warehouse, bu, true) orderby c.Date select c ).AsEnumerable(); var items = from c in qry where c.Date.Year == nYear select new { Date = DateTimeHelper.GetLocalDateStr(c.Date).Substring(0, 7), /*yyyy-MM*/ c.System_Clerk, c.Receiving, c.Shipping, c.RTV_Scrap, c.Inventory_Control, c.Overall, c.Forklift_Driver, c.Total }; res.data = items.ToList(); } return(res); }
internal static TDatatables GetMonthData(int selYear, string selKind) { var res = new TDatatables(); if (string.IsNullOrEmpty(selKind)) { return(res); } var sql = string.Format(@"select cast(`Date` as char(10)) as `Date` ,`Warehouse` ,{0} from V_Tbl_MonthData where year(`Date`)={1} order by `Date`,Warehouse " , selKind, selYear ); MySqlParameter[] parameter = null; var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter); if (DataTableHelper.IsEmptyDataSet(ds)) { return(res); } var dt = DataTableHelper.GetDataTable0(ds); var qry = dt.AsEnumerable().Select(x => new { Date = x["Date"].ToString().Substring(0, 7), /*yyyy-MM*/ Warehouse = x["Warehouse"].ToString(), Item = x[selKind].ToString() }); //var lstRows = GetKinds(); var kinds = qry.Select(x => x.Warehouse) .Distinct() .OrderBy(x => x) .ToList(); var cols = qry.Select(x => x.Date) .Distinct() .OrderBy(x => x) .Select(y => new TColEntry(y, y)).ToList(); var qry2 = from x in qry group x by x.Warehouse into g select new { name = g.Key, items = g.Select(x => x.Item).ToList() }; res.data = qry2.ToList(); res.columns = cols; res.kinds = kinds; return(res); }
private static TDatatables GetHCRateBu(DateTime date, string bu) { var res = new TDatatables(); res.kinds = GetHCRateKinds(); using (var context = new WarehouseLaborEffEntities()) { var qry = (from c in context.v_tbl_hcdata_rate where (0 == string.Compare(c.Warehouse, bu, true) && c.Date == date ) select c ).AsEnumerable().Select(c => new { //Date = DateTimeHelper.GetLocalDateStr(c.Date).Substring(0, 7),/*yyyy-MM*/ c.System_Clerk, c.Receiving, c.Shipping, c.RTV_Scrap, c.Inventory_Control, c.Overall }); var dat = qry.Single(); //var vals = new List<int>(); //vals.Add(Int2Still(dat.System_Clerk)); //vals.Add(Int2Still(dat.Receiving)); //vals.Add(Int2Still(dat.Shipping)); //vals.Add(Int2Still(dat.RTV_Scrap)); //vals.Add(Int2Still(dat.Inventory_Control)); //vals.Add(Int2Still(dat.Overall)); res.data = dat; } return(res); }
public static TDatatables GetWeekData(string bu, string startDate, string endDate) { var res = new TDatatables(); var sql = string.Format(@"select `Date` ,`HC_FCST` ,`HC_Actual` ,`HC_Support` ,`HC_Utilization` as `HC_Utilization(%)` ,`Case_ID_in` ,`Case_ID_out` ,`Pallet_In` ,`Pallet_Out` ,`Jobs_Rec` ,`Jobs_Issue` ,`Reel_ID_Rec` from V_Tbl_WeekData where Warehouse= @Warehouse and ( @StartDate<=`Date` and `Date`<= @EndDate) order by `Date` " ); var parameter = new MySqlParameter[] { new MySqlParameter("@Warehouse", bu), new MySqlParameter("@StartDate", startDate), new MySqlParameter("@EndDate", endDate) }; var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter); if (DataTableHelper.IsEmptyDataSet(ds)) { return(res); } var dt = DataTableHelper.GetDataTable0(ds); var qry = dt.AsEnumerable(); //var lstRows = GetKinds(); var cols = qry.Select(x => DateTimeHelper.GetLocalDateStrNull(x["Date"])) .Distinct() .OrderBy(x => x) .ToList(); var dtNew = DataTableHelper.DataTableRotateNoLeft(dt); dtNew.Columns[0].ColumnName = "Week"; int nColNew = dtNew.Columns.Count; for (var i = 0; i < nColNew - 1; i++) { dtNew.Columns[i + 1].ColumnName = cols[i]; } var arrCols = new List <TColEntry>(); arrCols.Add(new TColEntry("Week", "Week")); arrCols.AddRange((from x in cols select new TColEntry(x, x) ).ToList()); res.columns = arrCols; var sData = JsonHelper.DataTableToJsonArr(dtNew); res.data = sData; res.kinds = GetKinds(); return(res); }