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