void InitReportTable()
{
ReportDataSetTableAdapters.UnitPriceReportTableAdapter adapter = new Mong.ReportDataSetTableAdapters.UnitPriceReportTableAdapter();
//���o������
_table = adapter.GetData(_startDate, _endDate);
//���o�u�@�渹,�~��,��ڤu���
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT �u�@��.�渹 AS �渹, ���~�~��.�~��, Year(�u��.���) AS �~��, Month(�u��.���) AS ���, " +
"IIF(SUM(�u��.�u��) IS NULL , 0, SUM(�u��.�u��)) AS ��ڤu��," +
"IIF(SUM(�u��.�u��) IS NULL , 0, SUM(���u.�~�� * �u��.�u��)) AS ��ڤu�� " +
"FROM ((((�u�@�� INNER JOIN �u�@��~�� ON �u�@��~��.�渹 = �u�@��.�渹) " +
"INNER JOIN ���~�~�� ON �u�@��~��.�~�� = ���~�~��.�~��) " +
"LEFT JOIN �u�� ON �u��.�u�@�渹 = �u�@��~��.�渹 AND �u�@��~��.�s�� = �u��.�u�~�s��) " +
"LEFT JOIN ���u ON �u��.���u�s�� = ���u.�s��) " +
"WHERE �u�@��.��ڧ����� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND �u�@��.��ڧ����� < #" + _endDate.ToString("yyyy/MM/dd") + "# " +
"GROUP BY �u�@��.�渹, ���~�~��.�~��, Year(�u��.���), Month(�u��.���)" +
"ORDER BY �u�@��.�渹, ���~�~��.�~��, Year(�u��.���), Month(�u��.���)";
cmd.Connection = adapter.Connection;
System.Data.DataTable baseTable = new System.Data.DataTable();
baseTable.Columns.Add(new DataColumn("�渹", typeof(string)));
baseTable.Columns.Add(new DataColumn("�~��", typeof(string)));
baseTable.Columns.Add(new DataColumn("��ڤu��", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("��ڤu��", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("�~��", typeof(int)));
baseTable.Columns.Add(new DataColumn("���", typeof(int)));
System.Data.OleDb.OleDbDataAdapter baseAdapter = new System.Data.OleDb.OleDbDataAdapter();
baseAdapter.SelectCommand = cmd;
baseAdapter.Fill(baseTable);
//���o����d��
int minYear, minMonth, maxYear, maxMonth;
DateTime minDate, maxDate;
object o;
o = baseTable.Compute("MIN(�~��)", string.Empty);
minYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MIN(���)", string.Empty);
minMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
o = baseTable.Compute("MAX(�~��)", string.Empty);
maxYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MAX(���)", string.Empty);
maxMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
minDate = new DateTime(minYear, minMonth, 1);
maxDate = new DateTime(maxYear, maxMonth, 1);
//���o�C��u�@�ɼ�
Dictionary<DateTime, decimal> workHoursDic = new Dictionary<DateTime, decimal>();
for (DateTime date = new DateTime(minDate.Year, minDate.Month, 1); date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
workHoursDic.Add(date, hours);
}
//���s�p��baseTable����ڤu��
DateTime curRowMonth = DateTime.MinValue;
foreach (DataRow row in baseTable.Rows)
{
if (!Convert.IsDBNull(row["�~��"]))
{
int year = (int)row["�~��"];
int month = (int)row["���"];
if (year != curRowMonth.Year || month != curRowMonth.Month)
curRowMonth = new DateTime(year, month, 1);
row["��ڤu��"] = Math.Round(((decimal)row["��ڤu��"]) / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero);
}
}
//���sGroup
DataTableHelper dtHelper = new DataTableHelper();
System.Data.DataTable groupTable = dtHelper.SelectGroupByInto("GroupTable", baseTable, "�~��,SUM(��ڤu��) ��ڤu��, SUM(��ڤu��) ��ڤu��", null, "�~��");
//���o���w����d���u�@�渹
cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT DISTINCT(�渹) FROM �u�@�� WHERE ��ڧ����� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND ��ڧ����� < #" + _endDate.ToString("yyyy/MM/dd") + "#";
cmd.Connection = adapter.Connection;
System.Data.DataTable wsNumTable = new System.Data.DataTable();
wsNumTable.Columns.Add(new DataColumn("�渹", typeof(string)));
System.Data.OleDb.OleDbDataAdapter wsNumAdapter = new System.Data.OleDb.OleDbDataAdapter();
wsNumAdapter.SelectCommand = cmd;
wsNumAdapter.Fill(wsNumTable);
List<string> wsNumList = new List<string>();
foreach (DataRow row in wsNumTable.Rows)
wsNumList.Add(row["�渹"].ToString());
//���oLaborWage��Ʈw
LaborWageHelper lwHelper = new LaborWageHelper();
LaborWage�u�@��~��Table lwTable = lwHelper.GetDataGroupByPartNumber(wsNumList);
//��J�u��P�u��
foreach (ReportDataSet.UnitPriceReportRow row in _table)
{
DataRow[] partRows = groupTable.Select(string.Format("�~�� = '{0}'", row.�~��));
if (partRows.Length > 0)
{
row._��ڤu��_��_�~_ = (decimal)partRows[0]["��ڤu��"];
row._��ڤu��_��_�~_ = (decimal)partRows[0]["��ڤu��"];
}
//��JLaborWage���
DataRow[] lwRows = lwTable.Select(string.Format("�~�� = '{0}'", row.�~��));
if (lwRows.Length > 0)
{
decimal wage = (decimal)lwRows[0]["�~�]�u��"];
row._��ڤu��_��_�~_ += wage;
row._��ڤu��_��_�~_ += wage / Settings.HourlyPay;
}
}
}
void InitReportTable()
{
ReportDataSetTableAdapters.UnitPriceReportTableAdapter adapter = new Mong.ReportDataSetTableAdapters.UnitPriceReportTableAdapter();
//取得基本報表資料
_table = adapter.GetData(_startDate, _endDate);
//取得工作單號,品號,實際工資表
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT 工作單.單號 AS 單號, 產品品號.品號, Year(工時.日期) AS 年份, Month(工時.日期) AS 月份, " +
"IIF(SUM(工時.工時) IS NULL , 0, SUM(工時.工時)) AS 實際工時," +
"IIF(SUM(工時.工時) IS NULL , 0, SUM(員工.薪水 * 工時.工時)) AS 實際工資 " +
"FROM ((((工作單 INNER JOIN 工作單品號 ON 工作單品號.單號 = 工作單.單號) " +
"INNER JOIN 產品品號 ON 工作單品號.品號 = 產品品號.品號) " +
"LEFT JOIN 工時 ON 工時.工作單號 = 工作單品號.單號 AND 工作單品號.編號 = 工時.工品編號) " +
"LEFT JOIN 員工 ON 工時.員工編號 = 員工.編號) " +
"WHERE 工作單.實際完成日 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 工作單.實際完成日 < #" + _endDate.ToString("yyyy/MM/dd") + "# " +
"GROUP BY 工作單.單號, 產品品號.品號, Year(工時.日期), Month(工時.日期)" +
"ORDER BY 工作單.單號, 產品品號.品號, Year(工時.日期), Month(工時.日期)";
cmd.Connection = adapter.Connection;
System.Data.DataTable baseTable = new System.Data.DataTable();
baseTable.Columns.Add(new DataColumn("單號", typeof(string)));
baseTable.Columns.Add(new DataColumn("品號", typeof(string)));
baseTable.Columns.Add(new DataColumn("實際工時", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("實際工資", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("年份", typeof(int)));
baseTable.Columns.Add(new DataColumn("月份", typeof(int)));
System.Data.OleDb.OleDbDataAdapter baseAdapter = new System.Data.OleDb.OleDbDataAdapter();
baseAdapter.SelectCommand = cmd;
baseAdapter.Fill(baseTable);
//取得日期範圍
int minYear, minMonth, maxYear, maxMonth;
DateTime minDate, maxDate;
object o;
o = baseTable.Compute("MIN(年份)", string.Empty);
minYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MIN(月份)", string.Empty);
minMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
o = baseTable.Compute("MAX(年份)", string.Empty);
maxYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MAX(月份)", string.Empty);
maxMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
minDate = new DateTime(minYear, minMonth, 1);
maxDate = new DateTime(maxYear, maxMonth, 1);
//取得每月工作時數
Dictionary <DateTime, decimal> workHoursDic = new Dictionary <DateTime, decimal>();
for (DateTime date = new DateTime(minDate.Year, minDate.Month, 1); date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
workHoursDic.Add(date, hours);
}
//重新計算baseTable的實際工資
DateTime curRowMonth = DateTime.MinValue;
foreach (DataRow row in baseTable.Rows)
{
if (!Convert.IsDBNull(row["年份"]))
{
int year = (int)row["年份"];
int month = (int)row["月份"];
if (year != curRowMonth.Year || month != curRowMonth.Month)
{
curRowMonth = new DateTime(year, month, 1);
}
row["實際工資"] = Math.Round(((decimal)row["實際工資"]) / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero);
}
}
//重新Group
DataTableHelper dtHelper = new DataTableHelper();
System.Data.DataTable groupTable = dtHelper.SelectGroupByInto("GroupTable", baseTable, "品號,SUM(實際工時) 實際工時, SUM(實際工資) 實際工資", null, "品號");
//取得指定日期範圍內的工作單號
cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT DISTINCT(單號) FROM 工作單 WHERE 實際完成日 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 實際完成日 < #" + _endDate.ToString("yyyy/MM/dd") + "#";
cmd.Connection = adapter.Connection;
System.Data.DataTable wsNumTable = new System.Data.DataTable();
wsNumTable.Columns.Add(new DataColumn("單號", typeof(string)));
System.Data.OleDb.OleDbDataAdapter wsNumAdapter = new System.Data.OleDb.OleDbDataAdapter();
wsNumAdapter.SelectCommand = cmd;
wsNumAdapter.Fill(wsNumTable);
List <string> wsNumList = new List <string>();
foreach (DataRow row in wsNumTable.Rows)
{
wsNumList.Add(row["單號"].ToString());
}
//取得LaborWage資料庫
LaborWageHelper lwHelper = new LaborWageHelper();
LaborWage工作單品號Table lwTable = lwHelper.GetDataGroupByPartNumber(wsNumList);
//填入工資與工時
foreach (ReportDataSet.UnitPriceReportRow row in _table)
{
DataRow[] partRows = groupTable.Select(string.Format("品號 = '{0}'", row.品號));
if (partRows.Length > 0)
{
row._實際工時_內_外_ = (decimal)partRows[0]["實際工時"];
row._實際工資_內_外_ = (decimal)partRows[0]["實際工資"];
}
//填入LaborWage資料
DataRow[] lwRows = lwTable.Select(string.Format("品號 = '{0}'", row.品號));
if (lwRows.Length > 0)
{
decimal wage = (decimal)lwRows[0]["外包工資"];
row._實際工資_內_外_ += wage;
row._實際工時_內_外_ += wage / Settings.HourlyPay;
}
}
}
protected override void BeforeExport()
{
_subttlRows = new List<int>();
string lineFilter = string.Empty;
if(!string.IsNullOrEmpty(_form.Line))
lineFilter = "���u = '" + _form.Line + "'";
FinishedWorksheetReportSourceTableAdapter adapter = new FinishedWorksheetReportSourceTableAdapter();
//���o������
_srcTable = adapter.GetData(_startDate, _endDate);
_srcTable.Columns.Add("����`�u��auto", typeof(decimal), "(�����u�� + �~�]�u��)");
//_srcTable.Columns.Add("�h��ƶq", typeof(decimal));
//���o�C��u�@�ɼ�
Dictionary<DateTime, decimal> workHoursDic = new Dictionary<DateTime, decimal>();
DateTime minDate = _startDate;
DateTime maxDate = _endDate;
object tmpObj = _srcTable.Compute("MIN(���)",string.Empty);
if (tmpObj != DBNull.Value)
minDate = (DateTime)tmpObj;
tmpObj = _srcTable.Compute("MAX(���)", string.Empty);
if (tmpObj != DBNull.Value)
{
maxDate = (DateTime)tmpObj;
maxDate = new DateTime(maxDate.Year, maxDate.Month, DateTime.DaysInMonth(maxDate.Year, maxDate.Month));
}
for (DateTime date = minDate; date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
DateTime key = new DateTime(date.Year, date.Month, 1);
workHoursDic.Add(key, hours);
}
//���s�B���ڤu��
DateTime curRowMonth = DateTime.MinValue;
foreach (ReportDataSet.FinishedWorksheetReportSourceRow row in _srcTable)
{
if (!row.IsNull("�~��"))
{
if (row.�~�� != curRowMonth.Year || row.��� != curRowMonth.Month)
curRowMonth = new DateTime(row.�~��, row.���, 1);
row.�����u�� = Math.Round(row.�����u�� / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero);
}
}
//���sGroup���
DataTableHelper dtHelper = new DataTableHelper();
_table = dtHelper.SelectGroupByInto("ReportTable", _srcTable,
"���u,��ڧ�����,�u�@�渹,�Ǹ�,�~��,�~�W,�ƶq,���," +
"Sum(�����u��) �����u��, Sum(�����u��) �����u��,�~�]�u��,�~�]�u��,�зǤu��,���H�u����,����`�u��,����`�u��auto,����`�u��," +
"�з��`�u��,�з��`�u��,�Ͳ��IJv,���зǤu��,��ڤu��,��ڤu��,�u�~�s��",
lineFilter, "���u,��ڧ�����,�u�@�渹,�~��,�~�W,�ƶq,�зǤu��,���H�u����,���зǤu��,�u�~�s��");
//�]�w���
_table.Columns["���"].DefaultValue = "KPCS";
//���oLaborWage��Ʈw
LaborWageHelper lwHelper = new LaborWageHelper();
LaborWage�u�@��~��Table lwTable = null;
//�B��Ǹ��ö�J�~�]�u��P�u��
string curWorksheetNumber = string.Empty;
//int seriesNumber = 1;
foreach (DataRow row in _table.Rows)
{
string wsNumber = row["�u�@�渹"].ToString();
string pn = row["�~��"].ToString();
int wpid = (int)row["�u�~�s��"];
if (curWorksheetNumber != wsNumber)
{
curWorksheetNumber = wsNumber;
lwTable = lwHelper.GetData(curWorksheetNumber);
//seriesNumber = 1;
}
//���o�h��
//row["�h��ƶq"] = DatabaseSet.GetNGAmount(wsNumber, wpid) / 1000.0f;
//DataRow[] lwRows = lwTable.Select("�u�~�s�� = " + row["�u�~�s��"]);
object result = lwTable.Compute("SUM(�~�]�u��)", string.Format("�u�~�s�� = {0}", row["�u�~�s��"].ToString()));
//if (lwRows.Length > 0)
if (result != null && result != DBNull.Value)
{
//decimal laborWage = (decimal)lwRows[0]["�~�]�u��"];
decimal laborWage = Convert.ToDecimal(result);
row["�~�]�u��"] = laborWage;
row["�~�]�u��"] = laborWage / Settings.HourlyPay;
}
//row["�Ǹ�"] = seriesNumber++;
row["�Ǹ�"] = wpid;
row["����`�u��"] = row["����`�u��auto"];
}
base.BeforeExport();
}
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();
}
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();
}
protected override void BeforeExport()
{
_subttlRows = new List <int>();
string lineFilter = string.Empty;
if (!string.IsNullOrEmpty(_form.Line))
{
lineFilter = "產線 = '" + _form.Line + "'";
}
FinishedWorksheetReportSourceTableAdapter adapter = new FinishedWorksheetReportSourceTableAdapter();
//取得基本報表資料
_srcTable = adapter.GetData(_startDate, _endDate);
_srcTable.Columns.Add("實際總工時auto", typeof(decimal), "(內部工時 + 外包工時)");
//_srcTable.Columns.Add("退驗數量", typeof(decimal));
//取得每月工作時數
Dictionary <DateTime, decimal> workHoursDic = new Dictionary <DateTime, decimal>();
DateTime minDate = _startDate;
DateTime maxDate = _endDate;
object tmpObj = _srcTable.Compute("MIN(日期)", string.Empty);
if (tmpObj != DBNull.Value)
{
minDate = (DateTime)tmpObj;
}
tmpObj = _srcTable.Compute("MAX(日期)", string.Empty);
if (tmpObj != DBNull.Value)
{
maxDate = (DateTime)tmpObj;
maxDate = new DateTime(maxDate.Year, maxDate.Month, DateTime.DaysInMonth(maxDate.Year, maxDate.Month));
}
for (DateTime date = minDate; date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
DateTime key = new DateTime(date.Year, date.Month, 1);
workHoursDic.Add(key, hours);
}
//重新運算實際工資
DateTime curRowMonth = DateTime.MinValue;
foreach (ReportDataSet.FinishedWorksheetReportSourceRow row in _srcTable)
{
if (!row.IsNull("年份"))
{
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(內部工資) 內部工資,外包工時,外包工資,標準工時,單位人工成本,實際總工時,實際總工時auto,實際總工資," +
"標準總工時,標準總工資,生產效率,單位標準工資,實際工時,實際工資,工品編號",
lineFilter, "產線,實際完成日,工作單號,品號,品名,數量,標準工時,單位人工成本,單位標準工資,工品編號");
//設定欄位
_table.Columns["單位"].DefaultValue = "KPCS";
//取得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;
lwTable = lwHelper.GetData(curWorksheetNumber);
//seriesNumber = 1;
}
//取得退驗
//row["退驗數量"] = DatabaseSet.GetNGAmount(wsNumber, wpid) / 1000.0f;
//DataRow[] lwRows = lwTable.Select("工品編號 = " + row["工品編號"]);
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);
row["外包工資"] = laborWage;
row["外包工時"] = laborWage / Settings.HourlyPay;
}
//row["序號"] = seriesNumber++;
row["序號"] = wpid;
row["實際總工時"] = row["實際總工時auto"];
}
base.BeforeExport();
}