コード例 #1
0
        protected override void BeforeExport()
        {
            LoadNP();

            LineLaborHourReportSourceTableAdapter adapter = new LineLaborHourReportSourceTableAdapter();

            //���o�򥻳�����
            ReportDataSet.LineLaborHourReportSourceDataTable srcTable = adapter.GetData(_startDate, _endDate);

            //�إ߳���_table
            CreateReportTable();

            ////���o���w����d�򤺪��u�@�渹
            //OleDbCommand cmd = new OleDbCommand();
            //cmd.CommandText = "SELECT DISTINCT(�渹) FROM �u�@�� WHERE ��ڤ�� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND ��ڤ�� < #" + _endDate.ToString("yyyy/MM/dd") + "#";
            //cmd.Connection = adapter.Connection;

            //DataTable wsNumTable = new DataTable();
            //wsNumTable.Columns.Add(new DataColumn("�渹", typeof(string)));

            //OleDbDataAdapter wsNumAdapter = new OleDbDataAdapter();
            //wsNumAdapter.SelectCommand = cmd;
            //wsNumAdapter.Fill(wsNumTable);

            //List<string> wsNumList = new List<string>();
            //foreach (DataRow row in wsNumTable.Rows)
            //    wsNumList.Add(row["�渹"].ToString());

            //Ū���D�Ͳ����

            //�إߤ���table(���u,���u)
            DataTable groupTable = DataTableHelper.SelectDistinct(srcTable, "���u", "�ɤJ���u", "���u�m�W");

            /* 1.08.4
            //���oLaborWage���
            LaborWageHelper lwHelper = new LaborWageHelper();
            //LaborWage���u�u��Table lwTable = lwHelper.GetDataGroupByLabor(wsNumList);
            LaborWage���u�u��Table lwTable = lwHelper.GetDataGroupByDate(_startDate, _endDate);

            //Union lw table
            foreach (DataRow r in lwTable.Rows)
            {
                string name = r["���u�m�W"].ToString();

                DataRow[] rs = groupTable.Select("�ɤJ���u IS NULL AND ���u�m�W = '" + name + "'");
                if (rs.Length == 0)
                {
                    DataRow[] laborRow = DatabaseSet.���uTable.Select("�m�W ='" + name + "'");
                    if (laborRow.Length != 0)
                    {
                        DataRow nr = groupTable.NewRow();
                        nr["���u"] = laborRow[0]["���u"];
                        nr["���u�m�W"] = name;
                        groupTable.Rows.Add(nr);
                    }
                }
            }
            */

            //Re order
            groupTable.DefaultView.Sort = "���u, �ɤJ���u, ���u�m�W";
            groupTable = groupTable.DefaultView.ToTable();

            //��C�Ӥ��նi��ƾڶ�J
            foreach (DataRow groupRow in groupTable.Rows)
            {
                string selFilter = "���u='" + groupRow["���u"] + "' AND ���u�m�W = '" + groupRow["���u�m�W"] + "'";

                DataRow newRow = _table.NewRow();

                if (groupRow.IsNull("�ɤJ���u"))
                {
                    newRow["���u"] = groupRow["���u"];
                    selFilter += " AND �ɤJ���u IS NULL ";
                }
                else
                {
                    newRow["���u"] = groupRow["�ɤJ���u"];
                    newRow["�ɤJ"] = groupRow["���u"];
                    selFilter += " AND �ɤJ���u = '" + groupRow["�ɤJ���u"] + "'";
                }

                DataRow[] rows = srcTable.Select(selFilter);

                newRow["���u�W��"] = groupRow["���u�m�W"].ToString();

                decimal npHour = 0;
                Dictionary<HourType, decimal> pHours = new Dictionary<HourType, decimal>();
                foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
                {
                    pHours.Add(hourType, 0);
                }

                //���ݩ�Ӥ��ժ�Row�i���J
                foreach (DataRow srcRow in rows)
                {
                    int np = (int)srcRow["�D�Ͳ��s��"];
                    decimal hour = (decimal)srcRow["�u��"];

                    //���Ͳ��u��
                    if (np == -1)
                    {
                        HourType hourType = (HourType)srcRow["�u������"];

                        pHours[hourType] += hour;
                    }
                    else
                    {
                        //�D�Ͳ��u��
                        if (NpDic.ContainsKey(np) )
                        {
                            if(NpDic[np] != "np�а�")
                                npHour += hour;

                            newRow[NpDic[np]] = (decimal)newRow[NpDic[np]] + hour;
                        }
                    }
                }
                decimal ttlHour = npHour;
                foreach (decimal val in pHours.Values)
                    ttlHour += val;

                newRow["�Ͳ��u��"] = pHours[HourType.�@��u��];
                foreach (HourType hourType in Enum.GetValues(typeof(HourType)))
                {
                    if (hourType != HourType.�@��u��)
                        newRow[hourType.ToString()] = pHours[hourType];
                }
                /* 1.08.4
                DataRow[] lwRows = lwTable.Select("���u�m�W = '" + groupRow["���u�m�W"].ToString() + "'");
                if (lwRows.Length > 0)
                    newRow["�~�]�u��"] = (decimal)lwRows[0]["�u��"];
                */

                _table.Rows.Add(newRow);
            }

            base.BeforeExport();
        }
コード例 #2
0
        protected override void BeforeExport()
        {
            LoadNP();

            LineLaborHourReportSourceTableAdapter adapter = new LineLaborHourReportSourceTableAdapter();

            //���o�򥻳�����
            ReportDataSet.LineLaborHourReportSourceDataTable srcTable = adapter.GetData(_startDate, _endDate);

            //�إ߳���_table
            CreateReportTable();

            //���o���w����d�򤺪��u�@�渹
            //OleDbCommand cmd = new OleDbCommand();
            //cmd.CommandText = "SELECT DISTINCT(�渹) FROM �u�@�� WHERE ��ڤ�� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND ��ڤ�� < #" + _endDate.ToString("yyyy/MM/dd") + "#";
            //cmd.Connection = adapter.Connection;

            //DataTable wsNumTable = new DataTable();
            //wsNumTable.Columns.Add(new DataColumn("�渹", typeof(string)));

            //OleDbDataAdapter wsNumAdapter = new OleDbDataAdapter();
            //wsNumAdapter.SelectCommand = cmd;
            //wsNumAdapter.Fill(wsNumTable);

            //List<string> wsNumList = new List<string>();
            //foreach (DataRow row in wsNumTable.Rows)
            //    wsNumList.Add(row["�渹"].ToString());

            //�إߤ���table(���u,���u)
            DataTable groupTable = DataTableHelper.SelectDistinct(srcTable, "���u", "�ɤJ���u", "���u�m�W", "���");

            /* 1.08.4
            //���oLaborWage���
            LaborWageHelper lwHelper = new LaborWageHelper();
            LaborWage���u�u��Table lwTable = lwHelper.GetDataByDate(_startDate, _endDate);
            DataColumn col = new DataColumn("�w�B�z", typeof(bool));
            col.DefaultValue = false;
            lwTable.Columns.Add(col);

            //Union lw table
            foreach (DataRow r in lwTable.Rows)
            {
                DateTime date = (DateTime)r["���"];
                string name = r["���u�m�W"].ToString();

                DataRow[] rs = groupTable.Select("�ɤJ���u IS NULL AND ���u�m�W = '" + name + "' AND ��� = #" + date.ToString("yyyy/MM/dd") + "#");
                if (rs.Length == 0)
                {

                    DataRow[] laborRow = DatabaseSet.���uTable.Select("�m�W ='" + name + "'");
                    if (laborRow.Length != 0)
                    {
                        DataRow nr = groupTable.NewRow();
                        nr["���u"] = laborRow[0]["���u"];
                        nr["���u�m�W"] = name;
                        nr["���"] = date;
                        groupTable.Rows.Add(nr);
                    }
                }
            }
            */

            //Re order
            groupTable.DefaultView.Sort = "���u, �ɤJ���u, ���u�m�W, ���";
            groupTable = groupTable.DefaultView.ToTable();

            //��C�Ӥ��նi��ƾڶ�J
            foreach (DataRow groupRow in groupTable.Rows)
            {
                string selFilter = "���u='" + groupRow["���u"] + "' AND " +
                                   "���u�m�W = '" + groupRow["���u�m�W"] + "' AND " +
                                   "��� = #" + ((DateTime)groupRow["���"]).ToString("yyyy/MM/dd") + "#";

                DataRow newRow = _table.NewRow();

                if (groupRow.IsNull("�ɤJ���u"))
                {
                    newRow["���u"] = groupRow["���u"];
                    selFilter += " AND �ɤJ���u IS NULL ";
                }
                else
                {
                    newRow["���u"] = groupRow["�ɤJ���u"];
                    newRow["�ɤJ"] = groupRow["���u"];
                    selFilter += " AND �ɤJ���u = '" + groupRow["�ɤJ���u"] + "'";
                }

                decimal pHour = 0, npHour = 0;
                DataRow[] rows = srcTable.Select(selFilter);

                //���ݩ�Ӥ��ժ�Row�i���J
                foreach (DataRow srcRow in rows)
                {
                    int np = (int)srcRow["�D�Ͳ��s��"];
                    decimal hour = (decimal)srcRow["�u��"];

                    //���Ͳ��u��
                    if (np == -1)
                    {
                        pHour += hour;
                    }
                    else
                    {
                        npHour += hour;
                        newRow[NpDic[np]] = (decimal)newRow[NpDic[np]] + hour;
                    }
                }
                decimal ttlHour = pHour + npHour;

                newRow["���"] = groupRow["���"];
                newRow["�Ͳ��u��"] = pHour;
                newRow["���u�W��"] = groupRow["���u�m�W"];

                // Process labor wage data
                DateTime date = ((DateTime)newRow["���"]);

                //// Check whether each data which date lower than current processed are processed
                //DataRow[] unprocessedRows = lwTable.Select("���u�m�W = '" + groupRow["���u�m�W"].ToString() + "' AND " +
                //    "��� < #" + date.ToString("yyyy/MM/dd") + "# AND " +
                //    "�w�B�z = FALSE");
                //// Process those have not be processed
                //if (unprocessedRows.Length > 0)
                //{
                //    foreach (DataRow row in unprocessedRows)
                //    {
                //        DataRow r = _table.NewRow();
                //        foreach (DataColumn c in _table.Columns)
                //            r[c] = newRow[c];
                //        r["�~�]�u��"] = row["�u��"];
                //    }
                //}
                //// *****

                /* 1.08.4
                DataRow[] lwRows = lwTable.Select(
                    "���u�m�W = '" + groupRow["���u�m�W"].ToString() + "' AND " +
                    "��� = #" + date.ToString("yyyy/MM/dd") + "#");

                if (lwRows.Length > 0)
                {
                    newRow["�~�]�u��"] = lwRows[0]["�u��"];
                    lwRows[0]["�w�B�z"] = true;
                }
                */

                // *****
                _table.Rows.Add(newRow);
            }

            base.BeforeExport();
        }