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