private void deleteButton_Click(object sender, EventArgs e) { if (this.stubDataView.SelectedRows.Count > 0) { Data.CheckStub selectedStub = (Data.CheckStub) this.stubDataView.CurrentRow.DataBoundItem; selectedStub.isVoided = true; selectedStub.voidedStamp = DateTime.Now; this.ObjectContext.SubmitChanges(); this.stubDataView.DataSource = this.ObjectContext.CheckStubs.Where(s => s.isUploaded == false && s.isVoided == false); } else { MessageBox.Show("No file selected."); } }
private void uploadButton_Click(object sender, EventArgs e) { if (this.stubDataView.SelectedRows.Count > 0) { Data.CheckStub selectedStub = (Data.CheckStub) this.stubDataView.CurrentRow.DataBoundItem; //MessageBox.Show(selectedStub.stubBatchId.ToString()); var headers = this.ObjectContext.CheckStubHeaders.Where(h => h.checkStubBatchId == selectedStub.stubBatchId); var detail = this.ObjectContext.CheckStubDetails.Where(d => d.checkStubBatchId == selectedStub.stubBatchId); var summary = this.ObjectContext.CheckStubYtds.Where(s => s.checkStubBatchId == selectedStub.stubBatchId); //MessageBox.Show(headers.Count.ToString()); //MessageBox.Show(detail.Count.ToString()); //MessageBox.Show(summary.Count.ToString()); Data.PayStubObjects.ExportFileDetail fileDetail = new Data.PayStubObjects.ExportFileDetail(); fileDetail.createdBy = Environment.UserName; fileDetail.createdStamp = DateTime.Now; fileDetail.uploadedIp = Environment.MachineName; fileDetail.checkDate = selectedStub.checkDate; fileDetail.checkHeader = headers.ToList(); fileDetail.checkDetail = detail.ToList(); fileDetail.checkSummary = summary.ToList(); JavaScriptSerializer js = new JavaScriptSerializer(); string fileString = js.Serialize(fileDetail); string encryptedString = Security.Encryption.EncryptData(fileString, "cfsToSgfChkStbs!4565"); Sgf.Web.Services.CheckStubImportSoapClient client = new Sgf.Web.Services.CheckStubImportSoapClient(); try { client.ImportCheckStubs(encryptedString); selectedStub.isUploaded = true; selectedStub.uploadedStamp = DateTime.Now; // this.ObjectContext.FalkCheckStubs.Attach(selectedStub, true); this.ObjectContext.SubmitChanges(); this.stubDataView.DataSource = this.ObjectContext.CheckStubs.Where(s => s.isUploaded == false && s.isVoided == false); MessageBox.Show("Process completed successfully."); this.Close(); } catch (Exception ex) { string message = ex.Message; if (ex.InnerException != null) { message += Environment.NewLine + ex.InnerException.Message; } MessageBox.Show(message); } finally { client.Close(); } } else { MessageBox.Show("No file selected."); } }
private void button1_Click(object sender, EventArgs e) { Guid guid = Guid.NewGuid(); string codeKey = guid.ToString(); codeKey = codeKey.Substring(0, 8); //List<string> parameters = new List<string>(); //parameters.Add(codeKey); //parameters.Add(this.payDate.Text); //this.abraWorker.RunWorkerAsync(parameters); string checkDate = DateTime.Parse(this.payDate.Text).ToShortDateString(); DateTime startDate = new DateTime((int)this.payrollYear.SelectedItem, 1, 1); string company = this.companyList.SelectedItem.ToString(); string connString = @"Provider=vfpoledb;Data Source=\\cfs-hrms\abra\data;Collating Sequence=general;"; OleDbConnection conn = new OleDbConnection(connString); long stubId = 0; try { Data.CheckStub stub = new Data.CheckStub(); stub.stubBatchCreatedBy = Environment.UserName; stub.stubBatchCreated = DateTime.Now; stub.checkDate = DateTime.Parse(checkDate); stub.companyId = company; stub.isUploaded = false; stub.isVoided = false; this.ObjectContext.CheckStubs.InsertOnSubmit(stub); this.ObjectContext.SubmitChanges(); stubId = stub.stubBatchId; StringBuilder query = new StringBuilder(); query.Append("SELECT c.empno, c.chkdate, c.chknumber, c.chkstatus, c.totalgross, c.amount, c.payperiod, "); query.Append("h.p_lname, h.p_fname, h.p_mi, h.p_hstreet1, h.p_hstreet2, h.p_hcity, h.p_hstate, "); query.Append("h.p_hzip, h.p_salhour, h.p_salary, h.p_unitrate "); query.Append("FROM prckhist c INNER JOIN hrpersnl h ON c.company = h.p_company AND c.empno = h.p_empno "); query.Append("WHERE c.company = '" + company + "' AND c.chkdate = {" + checkDate + "};"); OleDbCommand comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; conn.Open(); OleDbDataReader reader = comm.ExecuteReader(); while (reader.Read()) { Data.CheckStubHeader item = new Data.CheckStubHeader(); item.abraId = int.Parse(reader["empno"].ToString()); item.checkDate = DateTime.Parse(reader["chkdate"].ToString()); item.checkNumber = reader["chknumber"].ToString(); item.checkStatus = char.Parse(reader["chkstatus"].ToString().Trim()); item.grossPay = decimal.Parse(reader["totalgross"].ToString()); item.netPay = decimal.Parse(reader["amount"].ToString()); DateTime payPeriodEndDate = DateTime.Parse(reader["payperiod"].ToString()); DateTime payPeriodStartDate; if (company == "SGF") { if (payPeriodEndDate.Day > 15) { payPeriodStartDate = new DateTime(payPeriodEndDate.Year, payPeriodEndDate.Month, 16); } else { payPeriodStartDate = new DateTime(payPeriodEndDate.Year, payPeriodEndDate.Month, 1); } } else { payPeriodStartDate = payPeriodEndDate.AddDays(-13); } item.payPeriodStart = payPeriodStartDate; item.payPeriodEnd = payPeriodEndDate; item.payToOrder = reader["p_fname"].ToString().Trim() + " " + reader["p_mi"].ToString().Trim() + " " + reader["p_lname"].ToString().Trim(); item.payToAddress = reader["p_hstreet1"].ToString().Trim(); item.payToAddress += Environment.NewLine; if (reader["p_hstreet2"].ToString().Trim() != string.Empty) { item.payToAddress += reader["p_hstreet2"].ToString().Trim(); item.payToAddress += Environment.NewLine; } item.payToAddress += reader["p_hcity"].ToString().Trim() + ", " + reader["p_hstate"].ToString().Trim() + " " + reader["p_hzip"].ToString().Trim(); string empType = reader["p_salhour"].ToString().Trim(); if (empType == "S") { item.salaryRate = decimal.Parse(reader["p_salary"].ToString()); } else { item.salaryRate = decimal.Parse(reader["p_unitrate"].ToString()); } item.checkStubBatchId = stubId; this.ObjectContext.CheckStubHeaders.InsertOnSubmit(item); } reader.Close(); reader.Dispose(); query = new StringBuilder(); query.Append("SELECT p.empno, p.accttype, p.bankacct, p.amount, c.chknumber, p.chkdate "); query.Append("FROM prhdirct p INNER JOIN prckhist c ON p.company = c.company AND "); query.Append("p.empno = c.empno AND p.chkdate = c.chkdate "); query.Append("WHERE p.company = '" + company + "' AND p.chkdate = {" + checkDate + "};"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader ddReader = comm.ExecuteReader(); while (ddReader.Read()) { Data.CheckStubDetail item = new Data.CheckStubDetail(); item.abraId = int.Parse(ddReader["empno"].ToString()); item.checkNumber = ddReader["chknumber"].ToString(); item.checkDate = DateTime.Parse(ddReader["chkdate"].ToString()); item.checkSectionId = (int)Data.PayStubObjects.CheckStubSection.DirectDeposits; item.itemCode = ddReader["accttype"].ToString(); string accountNumber = ddReader["bankacct"].ToString().Trim(); item.itemDescription = accountNumber.Substring(accountNumber.Length - 4).PadLeft(10, 'X'); item.itemAmount = double.Parse(ddReader["amount"].ToString()); item.itemRate = 0; item.itemQuantity = 0; item.checkStubBatchId = stubId; this.ObjectContext.CheckStubDetails.InsertOnSubmit(item); } ddReader.Close(); ddReader.Dispose(); query = new StringBuilder(); query.Append("SELECT j.empno, j.earncode, p.stubdesc, j.payrate, j.hours, j.amount, j.chknumber, j.chkdate FROM prjobcst j "); query.Append("INNER JOIN prcodes p ON j.earncode = p.code "); query.Append("WHERE j.company = '" + company + "' AND p.company = '" + company + "' AND chkdate = {" + checkDate + "};"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader eeReader = comm.ExecuteReader(); while (eeReader.Read()) { Data.CheckStubDetail item = new Data.CheckStubDetail(); item.abraId = int.Parse(eeReader["empno"].ToString()); item.checkNumber = eeReader["chknumber"].ToString(); item.checkDate = DateTime.Parse(eeReader["chkdate"].ToString()); item.checkSectionId = (int)Data.PayStubObjects.CheckStubSection.Earnings; item.itemCode = eeReader["earncode"].ToString(); item.itemDescription = eeReader["stubdesc"].ToString(); item.itemRate = double.Parse(eeReader["payrate"].ToString()); item.itemQuantity = double.Parse(eeReader["hours"].ToString()); item.itemAmount = double.Parse(eeReader["amount"].ToString()); item.checkStubBatchId = stubId; this.ObjectContext.CheckStubDetails.InsertOnSubmit(item); } eeReader.Close(); eeReader.Dispose(); query = new StringBuilder(); query.Append("SELECT d.empno, d.code, t.taxdesc, d.amount, d.chknumber, c.chkdate "); query.Append("FROM prdehist d INNER JOIN prckhist c ON d.chknumber = c.chknumber AND d.empno = c.empno AND d.company = c.company "); query.Append("INNER JOIN prtaxcds t ON d.code = t.taxcode WHERE d.company = '" + company + "' AND c.chkdate = {" + checkDate + "} "); query.Append("AND d.detailType = 'T' AND d.amount <> 0 AND t.company = '" + company + "';"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader taxReader = comm.ExecuteReader(); while (taxReader.Read()) { Data.CheckStubDetail item = new Data.CheckStubDetail(); item.abraId = int.Parse(taxReader["empno"].ToString()); item.checkNumber = taxReader["chknumber"].ToString(); item.checkDate = DateTime.Parse(taxReader["chkdate"].ToString()); item.checkSectionId = (int)Data.PayStubObjects.CheckStubSection.Taxes; item.itemCode = taxReader["code"].ToString(); item.itemDescription = taxReader["taxdesc"].ToString(); item.itemAmount = double.Parse(taxReader["amount"].ToString()); item.itemRate = 0; item.itemQuantity = 0; item.checkStubBatchId = stubId; this.ObjectContext.CheckStubDetails.InsertOnSubmit(item); } taxReader.Close(); taxReader.Dispose(); query = new StringBuilder(); query.Append("SELECT d.empno, d.code, p.stubdesc, d.amount, d.chknumber, c.chkdate "); query.Append("FROM prdehist d INNER JOIN prckhist c ON d.chknumber = c.chknumber AND d.empno = c.empno AND d.company = c.company "); query.Append("INNER JOIN prcodes p ON d.code = p.code WHERE p.company = '" + company + "' AND c.company = '" + company + "' AND c.chkdate = {" + checkDate + "} "); query.Append("AND d.detailType = 'D' AND d.amount <> 0;"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader dedReader = comm.ExecuteReader(); while (dedReader.Read()) { Data.CheckStubDetail item = new Data.CheckStubDetail(); item.abraId = int.Parse(dedReader["empno"].ToString()); item.checkNumber = dedReader["chknumber"].ToString(); item.checkDate = DateTime.Parse(dedReader["chkdate"].ToString()); item.checkSectionId = (int)Data.PayStubObjects.CheckStubSection.Deductions; item.itemCode = dedReader["code"].ToString(); item.itemDescription = dedReader["stubdesc"].ToString(); item.itemAmount = double.Parse(dedReader["amount"].ToString()); item.itemRate = 0; item.itemQuantity = 0; item.checkStubBatchId = stubId; this.ObjectContext.CheckStubDetails.InsertOnSubmit(item); } dedReader.Close(); dedReader.Dispose(); query = new StringBuilder(); query.Append("SELECT p.empno, p.detailtype, p.code, SUM(p.amount) AS ytdamount "); query.Append("FROM prdehist p INNER JOIN prckhist c "); query.Append("ON p.chknumber = c.chknumber AND p.empno = c.empno AND p.company = c.company "); query.Append("WHERE p.company = '" + company + "' AND c.company = '" + company + "' AND c.chkdate BETWEEN { " + startDate.ToShortDateString() + "} "); query.Append("AND {" + checkDate + "} "); query.Append("GROUP BY p.empno, p.detailtype, p.code "); query.Append("HAVING SUM(p.amount) <> 0;"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader ytdReader = comm.ExecuteReader(); while (ytdReader.Read()) { Data.CheckStubYtd item = new Data.CheckStubYtd(); item.checkNumber = string.Empty; item.checkDate = DateTime.Parse(checkDate); //item.checkSectionId = (int)Data.PayStubObjects.CheckStubSection.YtdDetail; item.abraId = int.Parse(ytdReader["empno"].ToString()); item.itemCode = ytdReader["code"].ToString(); item.codeType = ytdReader["detailtype"].ToString(); item.ytdAmount = double.Parse(ytdReader["ytdamount"].ToString()); item.checkStubBatchId = stubId; this.ObjectContext.CheckStubYtds.InsertOnSubmit(item); } ytdReader.Close(); ytdReader.Dispose(); if (company == "CFS") { query = new StringBuilder(); query.Append("SELECT s.t_empno, s.t_id, s.t_carry, s.t_close, s.t_taken "); query.Append("FROM hatsum s INNER JOIN prckhist c "); query.Append("ON s.t_empno = c.empno AND s.t_company = c.company "); query.Append("WHERE s.t_noaccrue = 0 AND s.t_company = '" + company + "' AND c.company = '" + company + "' AND c.chkdate = { " + checkDate + "};"); comm = new OleDbCommand(query.ToString(), conn); comm.CommandType = System.Data.CommandType.Text; OleDbDataReader ptoReader = comm.ExecuteReader(); while (ptoReader.Read()) { Data.CheckStubPtoBalance balance = new Data.CheckStubPtoBalance(); balance.checkStubBatchId = stubId; balance.abraId = int.Parse(ptoReader["t_empno"].ToString()); balance.ptoCode = ptoReader["t_id"].ToString(); balance.ptoCarried = double.Parse(ptoReader["t_carry"].ToString()); balance.ptoClose = double.Parse(ptoReader["t_close"].ToString()); balance.ptoTaken = double.Parse(ptoReader["t_taken"].ToString()); this.ObjectContext.CheckStubPtoBalances.InsertOnSubmit(balance); } ptoReader.Close(); ptoReader.Dispose(); } comm.Dispose(); this.ObjectContext.SubmitChanges(); //Data.PayStubObjects.ExportFileDetail file = new Data.PayStubObjects.ExportFileDetail(); MessageBox.Show("Data export complete."); } catch (Exception ex) { MessageBox.Show(ex.Message); if (ex.InnerException != null) { MessageBox.Show(ex.InnerException.Message); } } finally { conn.Close(); conn.Dispose(); this.stubDataView.Refresh(); } }