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