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();
}
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 WriteContent()
{
System.Data.DataTable linesTable = DataTableHelper.SelectDistinct(_table, "產線");
//建立寫入前置作業
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = false; //手動控制Summary Row
options.SummaryColumns.AddRange(new string[] { /*"退驗數量", */ "數量" /*, "標準工時", "單位人工成本"*/, "內部工時", "內部工資", "外包工資", "外包工時", "標準總工資", "標準總工時", "實際總工時" /*"單位標準工資", "實際工時", "實際工資"*/ });
options.NoSummaryColumns.AddRange(new string[] { "標準工時", "單位人工成本", "單位標準工資", "實際工時", "實際工資" });
PasteDataRowEventHandler beforeSummary = new PasteDataRowEventHandler(BeforePasteDataRowSummary);
//this.SheetAdapter.BeforePasteDataRowSummary += beforeSummary;
DataTableHelper dtHelper = new DataTableHelper();
DataTable nonNormalHourTable = dtHelper.SelectGroupByInto("ReportTable", _srcTable,
"產線, Sum(內部工時) 內部工時, 工時類型",
null, "產線,實際完成日,工作單號,品號,品名,數量,標準工時,單位人工成本,單位標準工資,工品編號,工時類型");
//對每個產線
foreach (DataRow lineRow in linesTable.Rows)
{
//NOTICE: 當Select條件修改時也必須修改BeforePasteDataRowSummary內容
DataRow[] rows = _table.Select("產線 = '" + lineRow["產線"] + "'", "工作單號,序號");
foreach (DataRow row in rows)
{
string worksheet = (string)row["工作單號"];
int serialNumber = (int)row["序號"];
//減去異常+包裝
object abnormalResult = _srcTable.Compute("SUM(內部工時)", "工作單號='" + worksheet + "' AND 工品編號 = " + serialNumber + " AND 產線 = '" + lineRow["產線"] + "' AND (工時類型=" + (int)HourType.異常生產工時 + " OR 工時類型=" + (int)HourType.包裝 + ")");
if (abnormalResult != DBNull.Value)
{
row["實際總工時"] = (decimal)row["實際總工時"] - (decimal)abnormalResult;
}
}
//寫入內容
options.IncludeSummary = false;
options.Row = writeRow;
options.SummaryPrefix = lineRow["產線"] + "產線小計";
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
//寫入異常生產工時,包裝工時
decimal unusual = 0;
decimal package = 0;
// 取得異常生產工時
object result = nonNormalHourTable.Compute("SUM(內部工時)", "產線 = '" + lineRow["產線"] + "' AND 工時類型=" + (int)HourType.異常生產工時);
if (result != null && result != DBNull.Value)
{
unusual = (decimal)result;
}
// 取得包裝工時
result = nonNormalHourTable.Compute("SUM(內部工時)", "產線 = '" + lineRow["產線"] + "' AND 工時類型=" + (int)HourType.包裝);
if (result != null && result != DBNull.Value)
{
package = (decimal)result;
}
DataRow tmpRow = _table.NewRow();
tmpRow["單位"] = null;
tmpRow[0] = "異常生產工時";
tmpRow["實際總工時"] = unusual;
writeRow = this.SheetAdapter.PasteDataRow(tmpRow, writeRow, options.Column);
tmpRow[0] = "包裝";
tmpRow["實際總工時"] = package;
writeRow = this.SheetAdapter.PasteDataRow(tmpRow, writeRow, options.Column);
//寫入小計
options.Row = writeRow;
_subttlRows.Add(writeRow);
writeRow = this.SheetAdapter.PasteSummaryRow(rows, options);
}
//this.SheetAdapter.BeforePasteDataRowSummary -= beforeSummary;
decimal ttlUnusual = 0;
decimal ttlPackage = 0;
// 取得異常生產工時
object tmpResult = nonNormalHourTable.Compute("SUM(內部工時)", "工時類型=" + (int)HourType.異常生產工時);
if (tmpResult != null && tmpResult != DBNull.Value)
{
ttlUnusual = (decimal)tmpResult;
}
// 取得包裝工時
tmpResult = nonNormalHourTable.Compute("SUM(內部工時)", "工時類型=" + (int)HourType.包裝);
if (tmpResult != null && tmpResult != DBNull.Value)
{
ttlPackage = (decimal)tmpResult;
}
//寫入總計(不包括異常,包裝)
DataTable tmpTable = _table.Clone();
tmpTable.Columns["實際總工時"].Expression = string.Empty; //實際總工時自己計算
DataRow totalRow = tmpTable.NewRow();
foreach (string sumCol in options.SummaryColumns)
{
if (!string.IsNullOrEmpty(tmpTable.Columns[sumCol].Expression))
{
tmpTable.Columns[sumCol].Expression = string.Empty;
}
object o;
o = _table.Compute("SUM(" + sumCol + ")", string.Empty);
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
foreach (string noSumCol in options.NoSummaryColumns)
{
tmpTable.Columns[noSumCol].Expression = string.Empty;
totalRow[noSumCol] = DBNull.Value;
}
////實際總工時 - (異常生產工時+包裝) 前面減過了
//object ttlHourObj = _table.Compute("SUM(實際總工時)", string.Empty);
//decimal ttlHour = Convert.IsDBNull(ttlHourObj) ? 0 : (decimal)ttlHourObj;
////totalRow["實際總工時"] = ttlHour - (ttlUnusual + ttlPackage);
totalRow[0] = "總計";
_ttlRow = writeRow;
tmpTable.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
//tmpTable = _table.Clone();
////寫入異常,包裝總計
//tmpTable.Columns[tmpTable.Columns["外包工資"].Ordinal].DataType = typeof(object);
//DataRow abnormalTotalRow = tmpTable.NewRow();
//abnormalTotalRow["單位"] = null;
//abnormalTotalRow[tmpTable.Columns["外包工資"].Ordinal] = "異常生產工時";
//abnormalTotalRow["實際總工時"] = ttlUnusual;
//writeRow = this.SheetAdapter.PasteDataRow(abnormalTotalRow, writeRow, options.Column);
//abnormalTotalRow[tmpTable.Columns["外包工資"].Ordinal] = "包裝";
//abnormalTotalRow["實際總工時"] = ttlPackage;
//writeRow = this.SheetAdapter.PasteDataRow(abnormalTotalRow, writeRow, options.Column);
////寫入總計(+異常,包裝)
//totalRow = tmpTable.NewRow();
//options.SummaryColumns = new List<string>(new string[] { "實際總工時", "標準總工時" });
//options.NoSummaryColumns = new List<string>(new string[] { "數量", "內部工時", "內部工資", "外包工資", "外包工時", "標準工時", "單位人工成本", "單位標準工資", "實際工時", "實際工資", "標準總工資" });
//foreach (string sumCol in options.SummaryColumns)
//{
// if (!string.IsNullOrEmpty(tmpTable.Columns[sumCol].Expression))
// tmpTable.Columns[sumCol].Expression = string.Empty;
// object o;
// o = _table.Compute("SUM(" + sumCol + ")", string.Empty);
// totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
//}
//foreach (string noSumCol in options.NoSummaryColumns)
//{
// tmpTable.Columns[noSumCol].Expression = string.Empty;
// totalRow[noSumCol] = DBNull.Value;
//}
//totalRow[tmpTable.Columns["外包工資"].Ordinal] = "Total";
//totalRow["單位"] = null;
//tmpTable.Rows.Add(totalRow);
//_ttlRow = writeRow;
//writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
