public static byte[] GetMonthData_Down(string selKind)
{
byte[] bys = null;
var sCol = selKind;
if (0 == string.Compare("HC_Utilization", selKind, true))
{
sCol = "(HC_Utilization / 100.0) as HC_Utilization";
}
var sql = string.Format(@"select
cast(Date as char(10)) as Date,
Warehouse,
{0}
from V_Tbl_MonthData
order by Date,Warehouse
"
, sCol
);
MySqlParameter[] parameter = null;
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
public static byte[] GetWeekData_Down(string bu, string startWeek, string endWeek)
{
byte[] bys = null;
var sql = string.Format(@"select
cast(Date as char(10)) as Date
,Warehouse
,HC_FCST
,HC_Actual
,HC_Support
, (HC_Utilization /100.0) 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", startWeek),
new MySqlParameter("@EndDate", endWeek)
};
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
public static byte[] GetHCData_Down(string bus, string selYear)
{
byte[] bys = null;
MySqlParameter[] parameter = null;
string sql = string.Empty;
if (string.IsNullOrEmpty(bus) || 0 == string.Compare("all", bus, true))
{
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where year(Date)={0}
order by Date
"
, selYear
);
}
else
{
var whList = bus.Split(new char[] { ',' });
var sb = new StringBuilder();
foreach (var bu in whList)
{
sb.AppendFormat("'{0}',", bu);
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where Warehouse in ({0})
and year(Date)={1}
order by Date
",
sb.ToString(), selYear
);
}
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}