protected override void WriteContent()
{
//建立寫入前置作業
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = false;
options.Row = writeRow;
DataRow[] rows = _table.Select();
//寫入內容
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
//寫入總計
string[] sumCols = new string[] { "數量", "標準工時", "實際工時(內+外)", "實際工資(內+外)" };
DataRow totalRow = _table.NewRow();
totalRow[0] = "總計";
foreach (string sumCol in sumCols)
{
object o;
o = _table.Compute("SUM([" + sumCol + "])", string.Empty);
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
_table.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
protected override void WriteContent()
{
DataTable linesTable = DataTableHelper.SelectDistinct(_table, "產線");
//建立寫入前置作業
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = true;
options.SummaryColumns.AddRange(new string[] { "退驗數量", "待驗數量", "數量", "標準工時", "單位人工成本", "總標準工時", "內部工時", "內部工資", "外包工資", "外包工時", "已生產數量", "尚需工時" });
//對每個產線
foreach (DataRow lineRow in linesTable.Rows)
{
DataRow[] rows = _table.Select("產線 = '" + lineRow["產線"] + "'", "工作單號,序號");
//寫入內容
options.Row = writeRow;
options.SummaryPrefix = lineRow["產線"] + "產線小計";
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
}
//寫入總計
DataTable tmpTable = _table.Clone();
DataRow totalRow = tmpTable.NewRow();
totalRow[0] = "總計";
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 + ")", "產線<>'F'");
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
tmpTable.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
protected override void WriteContent()
{
//�إg�J�e�m�@�~
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = false;
options.Row = writeRow;
DataRow[] rows = _table.Select();
//�g�J���e
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
//�g�J�`�p
string[] sumCols = new string[] { "�ƶq", "�зǤu��", "��ڤu��(��+�~)", "��ڤu��(��+�~)" };
DataRow totalRow = _table.NewRow();
totalRow[0] = "�`�p";
foreach (string sumCol in sumCols)
{
object o;
o = _table.Compute("SUM([" + sumCol + "])", string.Empty);
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
_table.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
protected override void WriteContent()
{
//this.SheetAdapter.PasteDataTable(_table, 3, 1);
int write = _columnHdrRow + 1;
//List<string> sortElements = new List<string>();
//if (!string.IsNullOrEmpty(_form.QCN))
// sortElements.Add("QCN");
//if (!string.IsNullOrEmpty(_form.PartNumber))
// sortElements.Add("�~��");
//if (!string.IsNullOrEmpty(_form.WorksheetFrom) || !string.IsNullOrEmpty(_form.WorksheetTo))
// sortElements.Add("�u�@�渹");
//sortElements.Add("������");
//DataRow[] rows = _table.Select(null, string.Join(",", sortElements.ToArray()));
DataRow[] rows;
int resultCol;
if (_form.InspeceMode == InspeceMode.ByPn)
{
rows = _groupTable.Select(null, _form.SortString);
foreach (DataRow row in rows)
write = this.SheetAdapters[0].PasteDataRow(row, write, 1);
}
else
{
rows = _table.Select(null, _form.SortString);
resultCol = this.SheetAdapters[0].ReportProfile.IndexOf("���窬�A") + 1;
foreach (DataRow row in rows)
{
write = this.SheetAdapters[0].PasteDataRow(row, write, 1);
string result = row["���窬�A"].ToString();
int color = 65280;
int fontColor = -16777216;
if (result == "NG")
{
color = 255;
fontColor = 16316664;
//fontColor = -1;
}
else if (result == "Concession")
{
color = 65535;
}
Range range = this.SheetAdapters[0].GetRange(write - 1, resultCol);
range.Interior.Color = color;
range.Font.Color = fontColor;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
}
//�έp���
if (_form.OutputStatistic)
{
DataView view = _table.DefaultView;
view.Sort = "���u ASC";
DataTable disLineTable = view.ToTable(true, "���u");
foreach (DataRow lineRow in disLineTable.Rows)
{
DataRow newRow = _statisticTable.NewRow();
string line = lineRow[0].ToString();
int count, num;
count = (int)_table.Compute("COUNT(���u)", "���u = '" + line + "' AND �̫��������=True AND ���窬�A <> 'NG'");
num = Convert.ToInt32(_table.Compute("SUM(�e�˦���)", "���u = '" + line + "' AND �̫��������=True"));
newRow["���u"] = line;
newRow["�J�w�`���"] = count;
newRow["�e���`����"] = num;
_statisticTable.Rows.Add(newRow);
}
write = _columnHdrRow + 1;
PasteDataRowsOptions option = new PasteDataRowsOptions();
option.IncludeSummary = true;
option.SummaryPrefix = "Total";
option.SummaryColumns.AddRange(new string[] { "�J�w�`���", "�e���`����" });
option.Row = write;
this.SheetAdapters[1].PasteDataRows(_statisticTable.Select(), option);
}
base.WriteContent();
}
protected override void WriteContent()
{
//this.SheetAdapter.PasteDataTable(_table, 3, 1);
int write = _columnHdrRow + 1;
//List<string> sortElements = new List<string>();
//if (!string.IsNullOrEmpty(_form.QCN))
// sortElements.Add("QCN");
//if (!string.IsNullOrEmpty(_form.PartNumber))
// sortElements.Add("品號");
//if (!string.IsNullOrEmpty(_form.WorksheetFrom) || !string.IsNullOrEmpty(_form.WorksheetTo))
// sortElements.Add("工作單號");
//sortElements.Add("檢驗日期");
//DataRow[] rows = _table.Select(null, string.Join(",", sortElements.ToArray()));
DataRow[] rows;
int resultCol;
if (_form.InspeceMode == InspeceMode.ByPn)
{
rows = _groupTable.Select(null, _form.SortString);
foreach (DataRow row in rows)
{
write = this.SheetAdapters[0].PasteDataRow(row, write, 1);
}
}
else
{
rows = _table.Select(null, _form.SortString);
resultCol = this.SheetAdapters[0].ReportProfile.IndexOf("檢驗狀態") + 1;
foreach (DataRow row in rows)
{
write = this.SheetAdapters[0].PasteDataRow(row, write, 1);
string result = row["檢驗狀態"].ToString();
int color = 65280;
int fontColor = -16777216;
if (result == "NG")
{
color = 255;
fontColor = 16316664;
//fontColor = -1;
}
else if (result == "Concession")
{
color = 65535;
}
Range range = this.SheetAdapters[0].GetRange(write - 1, resultCol);
range.Interior.Color = color;
range.Font.Color = fontColor;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
}
//統計資料
if (_form.OutputStatistic)
{
DataView view = _table.DefaultView;
view.Sort = "產線 ASC";
DataTable disLineTable = view.ToTable(true, "產線");
foreach (DataRow lineRow in disLineTable.Rows)
{
DataRow newRow = _statisticTable.NewRow();
string line = lineRow[0].ToString();
int count, num;
count = (int)_table.Compute("COUNT(產線)", "產線 = '" + line + "' AND 最後檢驗紀錄=True AND 檢驗狀態 <> 'NG'");
num = Convert.ToInt32(_table.Compute("SUM(送檢次數)", "產線 = '" + line + "' AND 最後檢驗紀錄=True"));
newRow["產線"] = line;
newRow["入庫總批數"] = count;
newRow["送檢總次數"] = num;
_statisticTable.Rows.Add(newRow);
}
write = _columnHdrRow + 1;
PasteDataRowsOptions option = new PasteDataRowsOptions();
option.IncludeSummary = true;
option.SummaryPrefix = "Total";
option.SummaryColumns.AddRange(new string[] { "入庫總批數", "送檢總次數" });
option.Row = write;
this.SheetAdapters[1].PasteDataRows(_statisticTable.Select(), option);
}
base.WriteContent();
}
protected override void WriteContent()
{
DataTable linesTable = DataTableHelper.SelectDistinct(_table, "���u");
//�إg�J�e�m�@�~
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = true;
/* 1.08.4
options.SummaryColumns.AddRange(new string[] { "�Ͳ��u��", "�~�]�u��" });*/
options.SummaryColumns.AddRange(new string[] { "�Ͳ��u��"});
foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
{
if(hourType != HourType.�@��u��)
options.SummaryColumns.Add(hourType.ToString());
}
options.ExcludeColumns.Add("�ɤJ");
string[] npCols = new string[NpDic.Count];
NpDic.Values.CopyTo(npCols, 0);
options.SummaryColumns.AddRange(npCols);
//��C�Ӳ��u
foreach (DataRow lineRow in linesTable.Rows)
{
DataRow[] rows = _table.Select("���u = '" + lineRow["���u"] + "'", "���u�W��");
//�g�J���e
options.Row = writeRow;
options.SummaryPrefix = lineRow["���u"] + "���u�p�p";
foreach (DataRow row in rows)
{
if (!row.IsNull("�ɤJ"))
row["���u"] = "��" + row["�ɤJ"] + "�ɤJ";
}
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
}
//�g�J�`�p
DataRow totalRow = _table.NewRow();
totalRow[0] = "�`�p";
foreach (string sumCol in options.SummaryColumns)
{
object o;
o = _table.Compute("SUM([" + sumCol + "])", string.Empty);
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
_table.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
//�]�w��ܮ榡
foreach (string npCol in npCols)
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf(npCol) + 1, "G/�q�ή榡;G/�q�ή榡;* \"-\"_-");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("�Ͳ��u��") + 1, "G/�q�ή榡;G/�q�ή榡;* \"-\"_-");
foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
{
if(hourType != HourType.�@��u��)
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf(hourType.ToString()) + 1, "G/�q�ή榡;G/�q�ή榡;* \"-\"_-");
}
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("�D�Ͳ�TTL") + 1, "G/�q�ή榡;G/�q�ή榡;* \"-\"_-");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("�Ͳ�%") + 1, "0.00%");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("�D�Ͳ�%") + 1, "0.00%");
//this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("�~�]�u��") + 1, "#,##0.00;#,##0.00;* \"-\"_-");
base.WriteContent();
}
void BeforePasteDataRowSummary(object sender, DataRow[] rows, PasteDataRowsOptions options, ref int writeRow, int col, object args)
{
decimal unusual = 0;
decimal package = 0;
if (rows.Length > 0)
{
DataTable table = rows[0].Table;
string line = rows[0]["���u"].ToString();
// ���o���`�Ͳ��u��
object result = table.Compute("SUM(�����u��)", "���u = '" + line + "' AND �u������=" + (int)HourType.���`�Ͳ��u��);
if (result != null && result != DBNull.Value)
unusual = (decimal)result;
// ���o�]�ˤu��
result = table.Compute("SUM(�����u��)", "���u = '" + line + "' AND �u������=" + (int)HourType.�]��);
if (result != null && result != DBNull.Value)
package = (decimal)result;
DataRow tmpRow = table.NewRow();
tmpRow["���"] = null;
tmpRow[0] = "���`�Ͳ��u��";
tmpRow["����`�u��"] = unusual;
writeRow = ((WorksheetAdapter)sender).PasteDataRow(tmpRow, writeRow, col);
tmpRow[0] = "�]��";
tmpRow["����`�u��"] = package;
writeRow = ((WorksheetAdapter)sender).PasteDataRow(tmpRow, writeRow, col);
}
}
protected override void WriteContent()
{
System.Data.DataTable linesTable = DataTableHelper.SelectDistinct(_table, "���u");
//�إg�J�e�m�@�~
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = false; //��ʱ���Summary Row
options.SummaryColumns.AddRange(new string[] { /*"�h��ƶq", */"�ƶq"/*, "�зǤu��", "���H�u����"*/, "�����u��", "�����u��", "�~�]�u��", "�~�]�u��", "�з��`�u��", "�з��`�u��", "����`�u��" /*"���зǤu��", "��ڤu��", "��ڤu��"*/ });
options.NoSummaryColumns.AddRange(new string[] { "�зǤu��", "���H�u����", "���зǤu��", "��ڤu��", "��ڤu��" });
PasteDataRowEventHandler beforeSummary = new PasteDataRowEventHandler(BeforePasteDataRowSummary);
//this.SheetAdapter.BeforePasteDataRowSummary += beforeSummary;
DataTableHelper dtHelper = new DataTableHelper();
DataTable nonNormalHourTable = dtHelper.SelectGroupByInto("ReportTable", _srcTable,
"���u, Sum(�����u��) �����u��, �u������",
null, "���u,��ڧ�����,�u�@�渹,�~��,�~�W,�ƶq,�зǤu��,���H�u����,���зǤu��,�u�~�s��,�u������");
//��C�Ӳ��u
foreach (DataRow lineRow in linesTable.Rows)
{
//NOTICE: ��Select����ק�ɤ]�����ק�BeforePasteDataRowSummary���e
DataRow[] rows = _table.Select("���u = '" + lineRow["���u"] + "'", "�u�@�渹,�Ǹ�");
foreach (DataRow row in rows)
{
string worksheet = (string)row["�u�@�渹"];
int serialNumber = (int)row["�Ǹ�"];
//��h���`+�]��
object abnormalResult = _srcTable.Compute("SUM(�����u��)", "�u�@�渹='" + worksheet + "' AND �u�~�s�� = " + serialNumber + " AND ���u = '" + lineRow["���u"] + "' AND (�u������=" + (int)HourType.���`�Ͳ��u�� + " OR �u������=" + (int)HourType.�]�� + ")");
if (abnormalResult != DBNull.Value)
row["����`�u��"] = (decimal)row["����`�u��"] - (decimal)abnormalResult;
}
//�g�J���e
options.IncludeSummary = false;
options.Row = writeRow;
options.SummaryPrefix = lineRow["���u"] + "���u�p�p";
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
//�g�J���`�Ͳ��u��,�]�ˤu��
decimal unusual = 0;
decimal package = 0;
// ���o���`�Ͳ��u��
object result = nonNormalHourTable.Compute("SUM(�����u��)", "���u = '" + lineRow["���u"] + "' AND �u������=" + (int)HourType.���`�Ͳ��u��);
if (result != null && result != DBNull.Value)
unusual = (decimal)result;
// ���o�]�ˤu��
result = nonNormalHourTable.Compute("SUM(�����u��)", "���u = '" + lineRow["���u"] + "' AND �u������=" + (int)HourType.�]��);
if (result != null && result != DBNull.Value)
package = (decimal)result;
DataRow tmpRow = _table.NewRow();
tmpRow["���"] = null;
tmpRow[0] = "���`�Ͳ��u��";
tmpRow["����`�u��"] = unusual;
writeRow = this.SheetAdapter.PasteDataRow(tmpRow, writeRow, options.Column);
tmpRow[0] = "�]��";
tmpRow["����`�u��"] = package;
writeRow = this.SheetAdapter.PasteDataRow(tmpRow, writeRow, options.Column);
//�g�J�p�p
options.Row = writeRow;
_subttlRows.Add(writeRow);
writeRow = this.SheetAdapter.PasteSummaryRow(rows, options);
}
//this.SheetAdapter.BeforePasteDataRowSummary -= beforeSummary;
decimal ttlUnusual = 0;
decimal ttlPackage = 0;
// ���o���`�Ͳ��u��
object tmpResult = nonNormalHourTable.Compute("SUM(�����u��)", "�u������=" + (int)HourType.���`�Ͳ��u��);
if (tmpResult != null && tmpResult != DBNull.Value)
ttlUnusual = (decimal)tmpResult;
// ���o�]�ˤu��
tmpResult = nonNormalHourTable.Compute("SUM(�����u��)", "�u������=" + (int)HourType.�]��);
if (tmpResult != null && tmpResult != DBNull.Value)
ttlPackage = (decimal)tmpResult;
//�g�J�`�p(���]�A���`,�]��)
DataTable tmpTable = _table.Clone();
tmpTable.Columns["����`�u��"].Expression = string.Empty; //����`�u�ɦۤv�p��
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;
}
////����`�u�� - (���`�Ͳ��u��+�]��) �e����L�F
//object ttlHourObj = _table.Compute("SUM(����`�u��)", string.Empty);
//decimal ttlHour = Convert.IsDBNull(ttlHourObj) ? 0 : (decimal)ttlHourObj;
////totalRow["����`�u��"] = ttlHour - (ttlUnusual + ttlPackage);
totalRow[0] = "�`�p";
_ttlRow = writeRow;
tmpTable.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
//tmpTable = _table.Clone();
////�g�J���`,�]���`�p
//tmpTable.Columns[tmpTable.Columns["�~�]�u��"].Ordinal].DataType = typeof(object);
//DataRow abnormalTotalRow = tmpTable.NewRow();
//abnormalTotalRow["���"] = null;
//abnormalTotalRow[tmpTable.Columns["�~�]�u��"].Ordinal] = "���`�Ͳ��u��";
//abnormalTotalRow["����`�u��"] = ttlUnusual;
//writeRow = this.SheetAdapter.PasteDataRow(abnormalTotalRow, writeRow, options.Column);
//abnormalTotalRow[tmpTable.Columns["�~�]�u��"].Ordinal] = "�]��";
//abnormalTotalRow["����`�u��"] = ttlPackage;
//writeRow = this.SheetAdapter.PasteDataRow(abnormalTotalRow, writeRow, options.Column);
////�g�J�`�p(+���`,�]��)
//totalRow = tmpTable.NewRow();
//options.SummaryColumns = new List<string>(new string[] { "����`�u��", "��`�u��" });
//options.NoSummaryColumns = new List<string>(new string[] { "�ƶq", "�����u��", "�����u��", "�~�]�u��", "�~�]�u��", "�зǤu��", "���H�u����", "���зǤu��", "��ڤu��", "��ڤu��", "�з��`�u��" });
//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["�~�]�u��"].Ordinal] = "Total";
//totalRow["���"] = null;
//tmpTable.Rows.Add(totalRow);
//_ttlRow = writeRow;
//writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
protected override void WriteContent()
{
DataTable linesTable = DataTableHelper.SelectDistinct(_table, "產線");
//建立寫入前置作業
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = true;
/* 1.08.4
* options.SummaryColumns.AddRange(new string[] { "生產工時", "外包工時" }); */
options.SummaryColumns.AddRange(new string[] { "生產工時" });
options.ExcludeColumns.Add("借入");
//string[] npCols = new string[NpDic.Count];
//NpDic.Values.CopyTo(npCols, 0);
List <string> npColList = new List <string>(NpDic.Values);
//npColList.Remove("np請假");
options.SummaryColumns.AddRange(npColList);
//對每個產線
foreach (DataRow lineRow in linesTable.Rows)
{
DataRow[] rows = _table.Select("產線 = '" + lineRow["產線"] + "'", "員工名稱");
//寫入內容
options.Row = writeRow;
options.SummaryPrefix = lineRow["產線"] + "產線小計";
foreach (DataRow row in rows)
{
if (!row.IsNull("借入"))
{
row["產線"] = "由" + row["借入"] + "借入";
}
}
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
}
//寫入總計
DataRow totalRow = _table.NewRow();
totalRow[0] = "總計";
foreach (string sumCol in options.SummaryColumns)
{
object o;
o = _table.Compute("SUM([" + sumCol + "])", string.Empty);
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
_table.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
//設定顯示格式
foreach (string npCol in NpDic.Values)
{
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf(npCol) + 1, "G/通用格式;G/通用格式;* \"-\"_-");
}
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("生產工時") + 1, "G/通用格式;G/通用格式;* \"-\"_-");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("非生產TTL") + 1, "G/通用格式;G/通用格式;* \"-\"_-");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("生產%") + 1, "0.00%");
this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("非生產%") + 1, "0.00%");
//this.SheetAdapter.SetFormat(4, _table.Columns.IndexOf("外包工時") + 1, "#,##0.00;#,##0.00;* \"-\"_-");
base.WriteContent();
}
protected override void WriteContent()
{
DataTable linesTable = DataTableHelper.SelectDistinct(_table, "產線");
//建立寫入前置作業
int writeRow = 4;
PasteDataRowsOptions options = new PasteDataRowsOptions();
options.IncludeSummary = true;
options.SummaryColumns.AddRange(new string[] {"退驗數量","待驗數量", "數量", "標準工時", "單位人工成本", "總標準工時", "內部工時", "內部工資", "外包工資", "外包工時", "已生產數量", "尚需工時" });
//對每個產線
foreach (DataRow lineRow in linesTable.Rows)
{
DataRow[] rows = _table.Select("產線 = '" + lineRow["產線"] + "'", "工作單號,序號");
//寫入內容
options.Row = writeRow;
options.SummaryPrefix = lineRow["產線"] + "產線小計";
writeRow = this.SheetAdapter.PasteDataRows(rows, options);
}
//寫入總計
DataTable tmpTable = _table.Clone();
DataRow totalRow = tmpTable.NewRow();
totalRow[0] = "總計";
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 + ")", "產線<>'F'");
totalRow[sumCol] = Convert.IsDBNull(o) ? 0 : (decimal)o;
}
tmpTable.Rows.Add(totalRow);
writeRow = this.SheetAdapter.PasteDataRow(totalRow, writeRow, 1);
base.WriteContent();
}
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();
}
void BeforePasteDataRowSummary(object sender, DataRow[] rows, PasteDataRowsOptions options, ref int writeRow, int col, object args)
{
decimal unusual = 0;
decimal package = 0;
if (rows.Length > 0)
{
DataTable table = rows[0].Table;
string line = rows[0]["產線"].ToString();
// 取得異常生產工時
object result = table.Compute("SUM(內部工時)", "產線 = '" + line + "' AND 工時類型=" + (int)HourType.異常生產工時);
if (result != null && result != DBNull.Value)
{
unusual = (decimal)result;
}
// 取得包裝工時
result = table.Compute("SUM(內部工時)", "產線 = '" + line + "' AND 工時類型=" + (int)HourType.包裝);
if (result != null && result != DBNull.Value)
{
package = (decimal)result;
}
DataRow tmpRow = table.NewRow();
tmpRow["單位"] = null;
tmpRow[0] = "異常生產工時";
tmpRow["實際總工時"] = unusual;
writeRow = ((WorksheetAdapter)sender).PasteDataRow(tmpRow, writeRow, col);
tmpRow[0] = "包裝";
tmpRow["實際總工時"] = package;
writeRow = ((WorksheetAdapter)sender).PasteDataRow(tmpRow, writeRow, col);
}
}
