public virtual ReportDataSet.UnfinishedWorksheetReportSourceDataTable GetData(System.DateTime 開始日期, System.DateTime 結束日期, bool 輸出全部) { this.Adapter.SelectCommand = this.CommandCollection[0]; this.Adapter.SelectCommand.Parameters[0].Value = ((System.DateTime)(開始日期)); this.Adapter.SelectCommand.Parameters[1].Value = ((System.DateTime)(結束日期)); this.Adapter.SelectCommand.Parameters[2].Value = ((bool)(輸出全部)); ReportDataSet.UnfinishedWorksheetReportSourceDataTable dataTable = new ReportDataSet.UnfinishedWorksheetReportSourceDataTable(true); this.Adapter.Fill(dataTable); return dataTable; }
protected override void BeforeExport() { string lineFilter = string.Empty; if (!string.IsNullOrEmpty(_form.Line)) { lineFilter = "產線 = '" + _form.Line + "'"; } UnfinishedWorksheetReportSourceTableAdapter adapter = new UnfinishedWorksheetReportSourceTableAdapter(); //取得基本報表資料 ReportDataSet.UnfinishedWorksheetReportSourceDataTable srcTable = adapter.GetData(this.StartDate, this.EndDate, this.StartDate == DateTime.MinValue && this.EndDate == DateTime.MaxValue); srcTable.Columns.Add("退驗數量", typeof(decimal)); srcTable.Columns.Add("待驗數量", typeof(decimal)); //變更公式計算 srcTable.Columns["完成%"].Expression = "IIF(數量 = 0,0,(已生產數量+待驗數量)/數量)"; //srcTable.Columns["生產效率"].Expression = "IIF((內部工時+外包工時) = 0,0,(已生產數量+待驗數量)*標準工時/(內部工時+外包工時))"; //取得日期範圍 int minYear, minMonth, maxYear, maxMonth; DateTime from, to; object o; o = srcTable.Compute("MIN(年份)", string.Empty); minYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o; o = srcTable.Compute("MIN(月份)", string.Empty); minMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o; o = srcTable.Compute("MAX(年份)", string.Empty); maxYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o; o = srcTable.Compute("MAX(月份)", string.Empty); maxMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o; from = new DateTime(minYear, minMonth, 1); to = new DateTime(maxYear, maxMonth, 1); //取得每月工作時數 Dictionary <DateTime, decimal> workHoursDic = new Dictionary <DateTime, decimal>(); for (DateTime date = new DateTime(from.Year, from.Month, 1); date <= to; date = date.AddMonths(1)) { decimal hours = Global.GetWorkingHours(date.Year, date.Month); workHoursDic.Add(date, hours); } //重新運算內部工資 DateTime curRowMonth = DateTime.MinValue; foreach (ReportDataSet.UnfinishedWorksheetReportSourceRow row in srcTable) { if (!Convert.IsDBNull(row["年份"])) { if (row.年份 != curRowMonth.Year || row.月份 != curRowMonth.Month) { curRowMonth = new DateTime(row.年份, row.月份, 1); } row.內部工資 = Math.Round(row.內部工資 / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero); } } //重新Group資料 DataTableHelper dtHelper = new DataTableHelper(); _table = dtHelper.SelectGroupByInto("ReportTable", srcTable, "產線,單據日期,預計完成日,工作單號,序號,品號,品名,退驗數量,待驗數量,數量,單位" + ",標準工時,單位人工成本, 總標準工時,Sum(內部工時) 內部工時, Sum(內部工資) 內部工資" + ",外包工時,外包工資,Sum(已生產數量) 已生產數量,完成%,生產效率,尚需工時,工品編號", lineFilter, "產線,單據日期,預計完成日,工作單號,品號,品名,數量,標準工時,單位人工成本,工品編號"); //設定欄位 _table.Columns["單位"].DefaultValue = "KPCS"; _table.Columns.Add("未完成數量", typeof(decimal), "數量-已生產數量-待驗數量").SetOrdinal(9); //取得LaborWage資料庫 LaborWageHelper lwHelper = new LaborWageHelper(); LaborWage工作單品號Table lwTable = null; //運算序號並填入外包工資與工時 string curWorksheetNumber = string.Empty; //int seriesNumber = 1; foreach (DataRow row in _table.Rows) { string wsNumber = row["工作單號"].ToString(); string pn = row["品號"].ToString(); int wpid = (int)row["工品編號"]; if (curWorksheetNumber != wsNumber) { curWorksheetNumber = wsNumber; //seriesNumber = 1; lwTable = lwHelper.GetData(curWorksheetNumber); } //取得退驗 row["退驗數量"] = DatabaseSet.GetNGAmount(wsNumber, wpid) / 1000.0f; row["待驗數量"] = DatabaseSet.GetInspectedAmount(wsNumber, wpid) / 1000.0f; //計算未完成數量 //row["未完成數量"] = (decimal)row["數量"] - (decimal)row["已生產數量"]; //DataRow[] lwRows = lwTable.Select(string.Format("品號 = '{0}'", row["品號"].ToString())); //DataRow[] lwRows = lwTable.Select(string.Format("工品編號 = {0}", row["工品編號"].ToString())); object result = lwTable.Compute("SUM(外包工資)", string.Format("工品編號 = {0}", row["工品編號"].ToString())); //if (lwRows.Length > 0) if (result != null && result != DBNull.Value) { //decimal laborWage = (decimal)lwRows[0]["外包工資"]; decimal laborWage = Convert.ToDecimal(result); //decimal number = (decimal)lwRows[0]["數量"]; row["外包工資"] = laborWage; row["外包工時"] = laborWage / Settings.HourlyPay; //row["已生產數量"] = (decimal)row["已生產數量"] + number; } if ((decimal)row["總標準工時"] != 0) { //row["生產效率"] = ((decimal)row["內部工時"] + (decimal)row["外包工時"]) / ((decimal)row["總標準工時"] * (decimal)row["完成%"]); //row["尚需工時"] = (decimal)row["總標準工時"] * (1 - (decimal)row["完成%"]); row["尚需工時"] = (decimal)row["總標準工時"] - (decimal)row["內部工時"] - (decimal)row["外包工時"]; } else { row["尚需工時"] = 0; } //row["序號"] = seriesNumber++; row["序號"] = wpid; } base.BeforeExport(); }