示例#1
0
        private void abraWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            Data.HsaObjects.BatchParameters parameters = (Data.HsaObjects.BatchParameters)e.Argument;
            string company   = parameters.company;
            string checkDate = parameters.checkDate.ToShortDateString();

            DataSet   ds       = new DataSet();
            DataTable employee = new DataTable("Employee");

            string connString = @"Provider=vfpoledb;Data Source=\\cfs-hrms\abra\data;Collating Sequence=general;";

            OleDbConnection conn  = new OleDbConnection(connString);
            StringBuilder   query = new StringBuilder();

            query.Append("SELECT h.p_empno, h.p_lname, h.p_fname, h.p_mi, h.p_ssn, d.amount, d.erliab, t.u_abanum, t.u_acctnum, t.u_accttype ");
            //query.Append("TRIM(h.p_lname) + ', ' + TRIM(h.p_fname) + ' ' + TRIM(h.p_mi) AS FullName ");
            query.Append("FROM prdehist d INNER JOIN prckhist c ");
            query.Append("ON (d.company = c.company AND d.empno = c.empno AND d.chknumber = c.chknumber) ");
            query.Append("INNER JOIN hrpersnl h ON (d.company = h.p_company AND d.empno = h.p_empno) ");
            query.Append("INNER JOIN hrtkpers t ON (h.p_company = t.u_company AND h.p_empno = t.u_empno) ");
            query.Append("WHERE d.company = '" + company + "' AND ");
            query.Append("d.code IN ('HSAS','HSAF','HSAE','HSA','HSP1','HSAN') AND c.chkdate = {" + checkDate + "} ");
            query.Append("ORDER BY h.p_lname, h.p_fname, d.erliab;");
            //query.Append("AND (NOT (t.u_acctnum IS NULL) OR t.u_acctnum <> '');");



            OleDbCommand comm = new OleDbCommand(query.ToString(), conn);

            comm.CommandType = System.Data.CommandType.Text;



            conn.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter(comm);

            adapter.Fill(employee);

            ds.Tables.Add(employee);

            adapter.Dispose();

            conn.Close();
            conn.Dispose();

            // MessageBox.Show(employee.Rows.Count.ToString(), "Success", MessageBoxButton.OK, MessageBoxImage.Information);

            System.Diagnostics.Debug.WriteLine("Abra Query Completed.");

            Data.ACHFile newFile = new Cfs.Custom.Software.Data.ACHFile();

            newFile.achBatchCreated   = DateTime.Now;
            newFile.achBatchCreatedBy = Environment.UserName;
            newFile.achCheckDate      = DateTime.Parse(checkDate);
            newFile.achCompany        = company;
            newFile.achFileStatus     = 'N';
            newFile.achTotalAmount    = 0;

            context.ACHFiles.InsertOnSubmit(newFile);
            context.SubmitChanges();

            System.Diagnostics.Debug.WriteLine("Batch record created.");

            this._batchId = newFile.achBatchId;

            System.Diagnostics.Debug.WriteLine("Batch ID: " + this._batchId.ToString());
            System.Diagnostics.Debug.WriteLine("Employee Rows: " + employee.Rows.Count.ToString());

            foreach (DataRow row in employee.Rows)
            {
                Data.ACHDetail detail = new Cfs.Custom.Software.Data.ACHDetail();

                detail.achBatchId    = this._batchId;
                detail.employeeId    = row["p_empno"].ToString();
                detail.lastName      = row["p_lname"].ToString();
                detail.firstName     = row["p_fname"].ToString();
                detail.middleInitial = row["p_mi"].ToString();
                detail.erAmount      = decimal.Parse(row["erliab"].ToString());
                detail.emAmount      = decimal.Parse(row["amount"].ToString());
                detail.accountType   = row["u_accttype"] == null ? "32" : row["u_accttype"].ToString();
                detail.routingNumber = row["u_abanum"].ToString();
                detail.accountNumber = row["u_acctnum"].ToString();
                detail.includeInFile = true;


                context.ACHDetails.InsertOnSubmit(detail);
            }



            context.SubmitChanges();

            System.Diagnostics.Debug.WriteLine("Batch details saved.");

            var detailsQuery = from details in context.ACHDetails
                               where details.achBatchId == this._batchId
                               select details;

            //this.aCHDetailsBindingSource.DataSource = detailsQuery;

            e.Result = detailsQuery;

            System.Diagnostics.Debug.WriteLine("Worker Completed.");
        }
示例#2
0
        private void btnImportUltiProData_Click(object sender, EventArgs e)
        {
            if (this.company.SelectedIndex == -1)
            {
                MessageBox.Show("Please select Company.", "Company", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }


            string   company   = this.company.SelectedItem.ToString();
            DateTime checkDate = DateTime.Parse(this.achCheckDateDateTimePicker.Text);

            if (this.openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string filePath = this.openFileDialog1.FileName;
                ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
                ExcelPackage package = new ExcelPackage(new FileInfo(filePath));

                //ExcelWorksheet sheet = package.Workbook.Worksheets[1];
                var            sheetList = package.Workbook.Worksheets.ToList();
                ExcelWorksheet sheet     = sheetList.First();

                Data.ACHFile newFile = new Cfs.Custom.Software.Data.ACHFile();

                newFile.achBatchCreated   = DateTime.Now;
                newFile.achBatchCreatedBy = Environment.UserName;
                newFile.achCheckDate      = checkDate;
                newFile.achCompany        = company;
                newFile.achFileStatus     = 'N';
                newFile.achTotalAmount    = 0;

                context.ACHFiles.InsertOnSubmit(newFile);
                context.SubmitChanges();

                System.Diagnostics.Debug.WriteLine("Batch record created.");

                this._batchId = newFile.achBatchId;


                for (int row = 2; row <= sheet.Dimension.End.Row; row++)
                {
                    string accountType = "32";

                    string accountTypeString = sheet.Cells[row, 14].Value.ToString().Trim();
                    switch (accountTypeString)
                    {
                    case "Checking":
                        accountType = "22";
                        break;
                    }


                    decimal erAmount = 0;
                    decimal emAmount = 0;

                    string deductionCode = sheet.Cells[row, 8].Value.ToString().Trim();

                    switch (deductionCode)
                    {
                    case "HSAFM":
                    case "HSAS":
                        erAmount = decimal.Parse(sheet.Cells[row, 9].Value.ToString());
                        break;

                    default:
                        emAmount = decimal.Parse(sheet.Cells[row, 9].Value.ToString());
                        break;
                    }


                    Data.ACHDetail detail = new Cfs.Custom.Software.Data.ACHDetail();

                    detail.achBatchId    = this._batchId;
                    detail.employeeId    = sheet.Cells[row, 1].Value.ToString();
                    detail.lastName      = sheet.Cells[row, 2].Value.ToString();
                    detail.firstName     = sheet.Cells[row, 3].Value.ToString();
                    detail.middleInitial = sheet.Cells[row, 4].Value == null ? string.Empty : sheet.Cells[row, 4].Value.ToString();
                    detail.erAmount      = erAmount;
                    detail.emAmount      = emAmount;
                    detail.accountType   = accountType;
                    detail.routingNumber = sheet.Cells[row, 11].Value == null ? string.Empty : sheet.Cells[row, 11].Value.ToString();
                    detail.accountNumber = sheet.Cells[row, 12].Value == null ? string.Empty : sheet.Cells[row, 12].Value.ToString();
                    detail.includeInFile = true;


                    context.ACHDetails.InsertOnSubmit(detail);
                }

                context.SubmitChanges();

                // CLOSE EXCEL SPREADSHEET
                package.Dispose();



                var achDetail = from details in context.ACHDetails
                                where details.achBatchId == this._batchId
                                select details;

                this.aCHDetailsBindingSource.DataSource = achDetail;



                this.updateBalances();
            }



            //string company = this.company.SelectedItem.ToString();
            //DateTime checkDate = DateTime.Parse(this.achCheckDateDateTimePicker.Text);

            ////ThreadStart starter = delegate { getHsaDataFromAbra(company, checkDate); };
            ////new Thread(starter).Start();

            ////this.getHsaDataFromAbra(company, checkDate);

            //Data.HsaObjects.BatchParameters parameters = new Data.HsaObjects.BatchParameters();
            //parameters.company = company;
            //parameters.checkDate = checkDate;
        }
示例#3
0
        private void getHsaDataFromAbra(string company, string checkDate)
        {
            DataSet   ds       = new DataSet();
            DataTable employee = new DataTable("Employee");

            string connString = @"Provider=vfpoledb;Data Source=\\cfs-hrms\abra\data;Collating Sequence=general;";

            OleDbConnection conn  = new OleDbConnection(connString);
            StringBuilder   query = new StringBuilder();

            query.Append("SELECT h.p_empno, h.p_lname, h.p_fname, h.p_mi, h.p_ssn, d.amount, d.erliab, t.u_abanum, t.u_acctnum, t.u_accttype ");
            //query.Append("TRIM(h.p_lname) + ', ' + TRIM(h.p_fname) + ' ' + TRIM(h.p_mi) AS FullName ");
            query.Append("FROM prdehist d INNER JOIN prckhist c ");
            query.Append("ON (d.company = c.company AND d.empno = c.empno AND d.chknumber = c.chknumber) ");
            query.Append("INNER JOIN hrpersnl h ON (d.company = h.p_company AND d.empno = h.p_empno) ");
            query.Append("INNER JOIN hrtkpers t ON (h.p_company = t.u_company AND h.p_empno = t.u_empno) ");
            query.Append("WHERE d.company = '" + company + "' AND ");
            query.Append("d.code IN ('HSAS','HSAF','HSAE','HSA','HSP1','HSAN') AND c.chkdate = {" + checkDate + "} ");
            query.Append("ORDER BY h.p_lname, h.p_fname, d.erliab;");
            //query.Append("AND (NOT (t.u_acctnum IS NULL) OR t.u_acctnum <> '');");



            OleDbCommand comm = new OleDbCommand(query.ToString(), conn);

            comm.CommandType = System.Data.CommandType.Text;


            try
            {
                conn.Open();

                OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
                adapter.Fill(employee);

                ds.Tables.Add(employee);

                adapter.Dispose();

                conn.Close();
                conn.Dispose();

                // MessageBox.Show(employee.Rows.Count.ToString(), "Success", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


            Data.ACHFile newFile = new Cfs.Custom.Software.Data.ACHFile();

            newFile.achBatchCreated   = DateTime.Now;
            newFile.achBatchCreatedBy = Environment.UserName;
            newFile.achCheckDate      = DateTime.Parse(checkDate);
            newFile.achCompany        = company;
            newFile.achFileStatus     = 'N';
            newFile.achTotalAmount    = 0;

            context.ACHFiles.InsertOnSubmit(newFile);
            context.SubmitChanges();

            this._batchId = newFile.achBatchId;
            decimal totalErAmount = 0;
            decimal totalEmAmount = 0;

            foreach (DataRow row in employee.Rows)
            {
                Data.ACHDetail detail = new Cfs.Custom.Software.Data.ACHDetail();

                detail.achBatchId    = this._batchId;
                detail.employeeId    = row["p_empno"].ToString();
                detail.lastName      = row["p_lname"].ToString();
                detail.firstName     = row["p_fname"].ToString();
                detail.middleInitial = row["p_mi"].ToString();
                detail.erAmount      = decimal.Parse(row["erliab"].ToString());
                detail.emAmount      = decimal.Parse(row["amount"].ToString());
                detail.accountType   = row["u_accttype"] == null ? "32" : row["u_accttype"].ToString();
                detail.routingNumber = row["u_abanum"].ToString();
                detail.accountNumber = row["u_acctnum"].ToString();
                detail.includeInFile = true;

                totalErAmount += decimal.Parse(row["erliab"].ToString());
                totalEmAmount += decimal.Parse(row["amount"].ToString());

                context.ACHDetails.InsertOnSubmit(detail);
            }

            this.totalErAmount_label.Text = totalErAmount.ToString("$#.00");
            this.totalEmAmount_label.Text = totalEmAmount.ToString("$#.00");

            context.SubmitChanges();

            var detailsQuery = from details in context.ACHDetails
                               where details.achBatchId == this._batchId
                               select details;

            this.aCHDetailsBindingSource.DataSource = detailsQuery;
        }