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