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(); }
public virtual ReportDataSet.FinishedWorksheetReportSourceDataTable GetData(global::System.Nullable<global::System.DateTime> 開始日期, global::System.Nullable<global::System.DateTime> 結束日期) { this.Adapter.SelectCommand = this.CommandCollection[0]; if ((開始日期.HasValue == true)) { this.Adapter.SelectCommand.Parameters[0].Value = ((System.DateTime)(開始日期.Value)); } else { this.Adapter.SelectCommand.Parameters[0].Value = global::System.DBNull.Value; } if ((結束日期.HasValue == true)) { this.Adapter.SelectCommand.Parameters[1].Value = ((System.DateTime)(結束日期.Value)); } else { this.Adapter.SelectCommand.Parameters[1].Value = global::System.DBNull.Value; } ReportDataSet.FinishedWorksheetReportSourceDataTable dataTable = new ReportDataSet.FinishedWorksheetReportSourceDataTable(true); this.Adapter.Fill(dataTable); return dataTable; }
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(); }