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;
}
}
}
private void button4_Click(object sender, EventArgs e)
{
metroProgressSpinner1.Visible = true;
Cursor.Current = Cursors.WaitCursor;
filltable();
int row_num = 1;
metroProgressSpinner1.Style = MetroFramework.MetroColorStyle.Yellow;
string qry = "select BrCode,gl_l_name as 'General Ledger Name',sl_l_name as 'Sub Ledger Name','opening_balance'= " +
"CASE WHEN op_dr_bal > 0 THEN op_dr_bal " +
"WHEN op_cr_bal> 0 THEN op_cr_bal*-1 " +
"ELSE 0 " +
"END," +
"DR_BAL as 'Debit',CR_BAL as 'Credit',(op_dr_bal + DR_BAL - op_cr_bal - CR_BAL) as 'Closing Balance',spid " +
"from TblRPTLEDGER ORDER BY brcode,gl_l_name";
SQL.DataTable dtTrial = new SQL.DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(qry, clsConnection.Conn))
{
da.Fill(dtTrial);
}
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
try
{
SQL.DataTable dtbranch =
dtTrial.DefaultView.ToTable(true, "BrCode");
foreach (SQL.DataRow x in dtbranch.Rows)
{
metroProgressSpinner1.Maximum = dtbranch.Rows.Count;
oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
oSheet.Name = x[0].ToString().Replace(" ", "").
Replace(" ", "").Replace("/", "").
Replace("\\", "").Replace("*", "");
string[] colNames = new string[dtTrial.Columns.Count];
int col = 0;
foreach (SQL.DataColumn dc in dtTrial.Columns)
{
colNames[col++] = dc.ColumnName;
}
char lastColumn = (char)(65 + dtTrial.Columns.Count - 1);
oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.Columns.AutoFit();
SQL.DataRow[] dr = dtTrial.Select(string.Format("BrCode='{0}'", x[0].ToString()));
string[,] rowData = new string[dr.Count <SQL.DataRow>(), dtTrial.Columns.Count];
int rowCnt = 0;
int redRows = 2;
foreach (SQL.DataRow row in dr)
{
for (col = 0; col < dtTrial.Columns.Count; col++)
{
rowData[rowCnt, col] = row[col].ToString();
}
if (double.Parse(row["Opening_Balance"].ToString()) == 0)
{
Range range = oSheet.get_Range("A" + redRows.ToString(), "H" + redRows.ToString());
range.Cells.Interior.Color = System.Drawing.Color.LightYellow;
}
redRows++;
rowCnt++;
}
Range range1 = oSheet.get_Range("A" + rowCnt.ToString(), "H" + rowCnt.ToString());
range1.Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;
metroProgressSpinner1.Value = row_num;
row_num = row_num + 1;
}
//Cursor gets default - wait ends
Cursor.Current = Cursors.Default;
metroProgressSpinner1.Visible = false;
//Filesave pops up
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel File|*.xlsx";
saveFileDialog1.Title = "Save an Excel File";
saveFileDialog1.ShowDialog();
// If the file name is not an empty string open it for saving.
if (saveFileDialog1.FileName != "")
{
object misValue = System.Reflection.Missing.Value;
oWB.SaveAs(saveFileDialog1.FileName, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
oWB.Close(saveFileDialog1.FileName);
oXL.Quit();
MessageBox.Show("Excel file created");
}
}
catch (Exception ex)
{
MessageBox.Show("Exception Occured while creating Excel file " + ex.ToString());
}
finally
{
Marshal.ReleaseComObject(oWB);
}
}
