protected override void BeforeExport()
{
LoadNP();
LineLaborHourReportSourceTableAdapter adapter = new LineLaborHourReportSourceTableAdapter();
//取得基本報表資料
ReportDataSet.LineLaborHourReportSourceDataTable srcTable = adapter.GetData(_startDate, _endDate);
//建立報表_table
CreateReportTable();
//取得指定日期範圍內的工作單號
//OleDbCommand cmd = new OleDbCommand();
//cmd.CommandText = "SELECT DISTINCT(單號) FROM 工作單 WHERE 單據日期 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 單據日期 < #" + _endDate.ToString("yyyy/MM/dd") + "#";
//cmd.Connection = adapter.Connection;
//DataTable wsNumTable = new DataTable();
//wsNumTable.Columns.Add(new DataColumn("單號", typeof(string)));
//OleDbDataAdapter wsNumAdapter = new OleDbDataAdapter();
//wsNumAdapter.SelectCommand = cmd;
//wsNumAdapter.Fill(wsNumTable);
//List<string> wsNumList = new List<string>();
//foreach (DataRow row in wsNumTable.Rows)
// wsNumList.Add(row["單號"].ToString());
//建立分組table(產線,員工)
DataTable groupTable = DataTableHelper.SelectDistinct(srcTable, "產線", "借入產線", "員工姓名", "日期");
/* 1.08.4
* //取得LaborWage資料
* LaborWageHelper lwHelper = new LaborWageHelper();
* LaborWage員工工時Table lwTable = lwHelper.GetDataByDate(_startDate, _endDate);
* DataColumn col = new DataColumn("已處理", typeof(bool));
* col.DefaultValue = false;
* lwTable.Columns.Add(col);
*
* //Union lw table
* foreach (DataRow r in lwTable.Rows)
* {
* DateTime date = (DateTime)r["日期"];
* string name = r["員工姓名"].ToString();
*
* DataRow[] rs = groupTable.Select("借入產線 IS NULL AND 員工姓名 = '" + name + "' AND 日期 = #" + date.ToString("yyyy/MM/dd") + "#");
* if (rs.Length == 0)
* {
*
* DataRow[] laborRow = DatabaseSet.員工Table.Select("姓名 ='" + name + "'");
* if (laborRow.Length != 0)
* {
* DataRow nr = groupTable.NewRow();
* nr["產線"] = laborRow[0]["產線"];
* nr["員工姓名"] = name;
* nr["日期"] = date;
* groupTable.Rows.Add(nr);
* }
* }
* }
*/
//Re order
groupTable.DefaultView.Sort = "產線, 借入產線, 員工姓名, 日期";
groupTable = groupTable.DefaultView.ToTable();
//對每個分組進行數據填入
foreach (DataRow groupRow in groupTable.Rows)
{
string selFilter = "產線='" + groupRow["產線"] + "' AND " +
"員工姓名 = '" + groupRow["員工姓名"] + "' AND " +
"日期 = #" + ((DateTime)groupRow["日期"]).ToString("yyyy/MM/dd") + "#";
DataRow newRow = _table.NewRow();
if (groupRow.IsNull("借入產線"))
{
newRow["產線"] = groupRow["產線"];
selFilter += " AND 借入產線 IS NULL ";
}
else
{
newRow["產線"] = groupRow["借入產線"];
newRow["借入"] = groupRow["產線"];
selFilter += " AND 借入產線 = '" + groupRow["借入產線"] + "'";
}
decimal pHour = 0, npHour = 0;
DataRow[] rows = srcTable.Select(selFilter);
//對屬於該分組的Row進行填入
foreach (DataRow srcRow in rows)
{
int np = (int)srcRow["非生產編號"];
decimal hour = (decimal)srcRow["工時"];
//為生產工時
if (np == -1)
{
pHour += hour;
}
else
{
npHour += hour;
newRow[NpDic[np]] = (decimal)newRow[NpDic[np]] + hour;
}
}
decimal ttlHour = pHour + npHour;
newRow["日期"] = groupRow["日期"];
newRow["生產工時"] = pHour;
newRow["員工名稱"] = groupRow["員工姓名"];
// Process labor wage data
DateTime date = ((DateTime)newRow["日期"]);
//// Check whether each data which date lower than current processed are processed
//DataRow[] unprocessedRows = lwTable.Select("員工姓名 = '" + groupRow["員工姓名"].ToString() + "' AND " +
// "日期 < #" + date.ToString("yyyy/MM/dd") + "# AND " +
// "已處理 = FALSE");
//// Process those have not be processed
//if (unprocessedRows.Length > 0)
//{
// foreach (DataRow row in unprocessedRows)
// {
// DataRow r = _table.NewRow();
// foreach (DataColumn c in _table.Columns)
// r[c] = newRow[c];
// r["外包工時"] = row["工時"];
// }
//}
//// *****
/* 1.08.4
* DataRow[] lwRows = lwTable.Select(
* "員工姓名 = '" + groupRow["員工姓名"].ToString() + "' AND " +
* "日期 = #" + date.ToString("yyyy/MM/dd") + "#");
*
* if (lwRows.Length > 0)
* {
* newRow["外包工時"] = lwRows[0]["工時"];
* lwRows[0]["已處理"] = true;
* }
*/
// *****
_table.Rows.Add(newRow);
}
base.BeforeExport();
}
public virtual ReportDataSet.LineLaborHourReportSourceDataTable GetData(System.DateTime 日期, System.DateTime 日期1) {
this.Adapter.SelectCommand = this.CommandCollection[0];
this.Adapter.SelectCommand.Parameters[0].Value = ((System.DateTime)(日期));
this.Adapter.SelectCommand.Parameters[1].Value = ((System.DateTime)(日期1));
ReportDataSet.LineLaborHourReportSourceDataTable dataTable = new ReportDataSet.LineLaborHourReportSourceDataTable();
this.Adapter.Fill(dataTable);
return dataTable;
}
protected override void BeforeExport()
{
LoadNP();
LineLaborHourReportSourceTableAdapter adapter = new LineLaborHourReportSourceTableAdapter();
//取得基本報表資料
ReportDataSet.LineLaborHourReportSourceDataTable srcTable = adapter.GetData(_startDate, _endDate);
//建立報表_table
CreateReportTable();
////取得指定日期範圍內的工作單號
//OleDbCommand cmd = new OleDbCommand();
//cmd.CommandText = "SELECT DISTINCT(單號) FROM 工作單 WHERE 單據日期 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 單據日期 < #" + _endDate.ToString("yyyy/MM/dd") + "#";
//cmd.Connection = adapter.Connection;
//DataTable wsNumTable = new DataTable();
//wsNumTable.Columns.Add(new DataColumn("單號", typeof(string)));
//OleDbDataAdapter wsNumAdapter = new OleDbDataAdapter();
//wsNumAdapter.SelectCommand = cmd;
//wsNumAdapter.Fill(wsNumTable);
//List<string> wsNumList = new List<string>();
//foreach (DataRow row in wsNumTable.Rows)
// wsNumList.Add(row["單號"].ToString());
//讀取非生產資料
//建立分組table(產線,員工)
DataTable groupTable = DataTableHelper.SelectDistinct(srcTable, "產線", "借入產線", "員工姓名");
/* 1.08.4
* //取得LaborWage資料
* LaborWageHelper lwHelper = new LaborWageHelper();
* //LaborWage員工工時Table lwTable = lwHelper.GetDataGroupByLabor(wsNumList);
* LaborWage員工工時Table lwTable = lwHelper.GetDataGroupByDate(_startDate, _endDate);
*
* //Union lw table
* foreach (DataRow r in lwTable.Rows)
* {
* string name = r["員工姓名"].ToString();
*
* DataRow[] rs = groupTable.Select("借入產線 IS NULL AND 員工姓名 = '" + name + "'");
* if (rs.Length == 0)
* {
* DataRow[] laborRow = DatabaseSet.員工Table.Select("姓名 ='" + name + "'");
* if (laborRow.Length != 0)
* {
* DataRow nr = groupTable.NewRow();
* nr["產線"] = laborRow[0]["產線"];
* nr["員工姓名"] = name;
* groupTable.Rows.Add(nr);
* }
* }
* }
*/
//Re order
groupTable.DefaultView.Sort = "產線, 借入產線, 員工姓名";
groupTable = groupTable.DefaultView.ToTable();
//對每個分組進行數據填入
foreach (DataRow groupRow in groupTable.Rows)
{
string selFilter = "產線='" + groupRow["產線"] + "' AND 員工姓名 = '" + groupRow["員工姓名"] + "'";
DataRow newRow = _table.NewRow();
if (groupRow.IsNull("借入產線"))
{
newRow["產線"] = groupRow["產線"];
selFilter += " AND 借入產線 IS NULL ";
}
else
{
newRow["產線"] = groupRow["借入產線"];
newRow["借入"] = groupRow["產線"];
selFilter += " AND 借入產線 = '" + groupRow["借入產線"] + "'";
}
DataRow[] rows = srcTable.Select(selFilter);
newRow["員工名稱"] = groupRow["員工姓名"].ToString();
decimal npHour = 0;
Dictionary <HourType, decimal> pHours = new Dictionary <HourType, decimal>();
foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
{
pHours.Add(hourType, 0);
}
//對屬於該分組的Row進行填入
foreach (DataRow srcRow in rows)
{
int np = (int)srcRow["非生產編號"];
decimal hour = (decimal)srcRow["工時"];
//為生產工時
if (np == -1)
{
HourType hourType = (HourType)srcRow["工時類型"];
pHours[hourType] += hour;
}
else
{
//非生產工時
if (NpDic.ContainsKey(np))
{
if (NpDic[np] != "np請假")
{
npHour += hour;
}
newRow[NpDic[np]] = (decimal)newRow[NpDic[np]] + hour;
}
}
}
decimal ttlHour = npHour;
foreach (decimal val in pHours.Values)
{
ttlHour += val;
}
newRow["生產工時"] = pHours[HourType.一般工時];
foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
{
if (hourType != HourType.一般工時)
{
newRow[hourType.ToString()] = pHours[hourType];
}
}
/* 1.08.4
* DataRow[] lwRows = lwTable.Select("員工姓名 = '" + groupRow["員工姓名"].ToString() + "'");
* if (lwRows.Length > 0)
* newRow["外包工時"] = (decimal)lwRows[0]["工時"];
*/
_table.Rows.Add(newRow);
}
base.BeforeExport();
}
