コード例 #1
0
ファイル: UnitPriceReporter.cs プロジェクト: amon0424/SWLHMS
        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;
                }
            }
        }
コード例 #2
0
ファイル: UnitPriceReporter.cs プロジェクト: amon0424/SWLHMS
        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;
                }
            }
        }