//Aggregated Account Display Methods private void loadlstTransactions() { // Create an instance of a ListView column sorter and assign it // to the ListView control. lvwColumnSorter = new ListViewColumnSorter(); this.lstTransactions.ListViewItemSorter = lvwColumnSorter; lstTransactions.BeginUpdate(); lstTransactions.Clear(); buildlstTransactionsColumns(); if (!dbcTransactions.isEmpty()) { int recordCount = 0; foreach (DataRow row in dbcTransactions.getDT().Rows) { ListViewItem listItem = new ListViewItem(row["refTransTypeDesc"].ToString()); listItem.Name = recordCount + "!" + row["TransID"].ToString(); // listItem.SubItems.Add(row["refTransTypeSubDesc"].ToString()); // if (row["EmpID"].ToString() != "") //Harry 01062016 { var names = row["VendName"].ToString().TrimEnd().Split(' '); string lastName; if (names[names.Length - 1].IndexOf('(') != -1) { lastName = names[names.Length - 2]; } else { lastName = names[names.Length - 1]; } //string temp = lastName + " " + row["VendName"].ToString().Replace(lastName, ""); listItem.SubItems.Add(lastName + ", " + row["VendName"].ToString().Replace(lastName, "")); } else { listItem.SubItems.Add(row["VendName"].ToString()); } listItem.SubItems.Add(row["EmpUIN"].ToString()); listItem.SubItems.Add(row["PostingDate"].ToString()); // // listItem.SubItems.Add(row["TransactionDate"].ToString()); // listItem.SubItems.Add(row["theUnitID"].ToString()); // listItem.SubItems.Add(row["FundType"].ToString() + " " + row["DeptID"].ToString() + " " + row["FundProject"].ToString() + " " + row["FundClass"].ToString()); // listItem.SubItems.Add(row["GL"].ToString()); listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(UtilityParser.passEmptyNumber(row["TransAmount"].ToString())))); // listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(UtilityParser.passEmptyNumber(row["TransAmountRemainder"].ToString())))); // listItem.SubItems.Add(row["TransDesc"].ToString()); // listItem.SubItems.Add(row["StatusDesc"].ToString()); // listItem.SubItems.Add(row["TransBiReference"].ToString()); listItem.SubItems.Add(UtilityDates.convertFormatedDateToSortableString(row["PostingDate"].ToString())); // listItem.SubItems.Add(UtilityDates.convertFormatedDateToSortableString(row["TransactionDate"].ToString())); listItem.SubItems.Add(row["TransAmount"].ToString().PadLeft(12, '0')); listItem.SubItems.Add(row["TransAmountRemainder"].ToString().PadLeft(12, '0')); lstTransactions.Items.Add(listItem); recordCount++; } } lstTransactions.EndUpdate(); this.Cursor = Cursors.Default; }
// load cmboGLFilter private void loadCmboGLFilter() { ArrayList arrNew = new ArrayList(); int currentFY = UtilityDates.getCurrentFiscalYear(); arrNew.Add(new ListValues("", "YTD", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "01", "January", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "02", "February", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "03", "March", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "04", "April", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "05", "May", ListValues.FORWARD)); arrNew.Add(new ListValues((int.Parse(cmboFY.SelectedValue.ToString()) + 1).ToString() + "06", "June", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "07", "July", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "08", "August", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "09", "September", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "10", "October", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "11", "Novermber", ListValues.FORWARD)); arrNew.Add(new ListValues(cmboFY.SelectedValue.ToString() + "12", "December", ListValues.FORWARD)); cmboGLFilter.DataSource = arrNew; cmboGLFilter.DisplayMember = "Desc"; cmboGLFilter.ValueMember = "ID"; cmboGLFilter.SelectedIndex = 0; // selects Fiscal year to date isCmboGLFilterLoaded = true; }
private void cmdViewPayroll_Click(object sender, EventArgs e) { //Make sure that a date was selected if (!UtilityDates.isDate(txtTransDate.Text)) { MessageBox.Show("The transaction date does not appear to be a valid date"); return; } loadPendingPayroll(); }
private void loadLstPayroll() { try { // Create an instance of a ListView column sorter and assign it // to the ListView control. lvwColumnSorter_lstPayroll = new ListViewColumnSorter(); this.lstPayroll.ListViewItemSorter = lvwColumnSorter_lstPayroll; fillDBCPayroll(); lstPayroll.BeginUpdate(); lstPayroll.Clear(); buildLstPayrollColumns(); int recordCount = 0; foreach (DataRow row in dbcPayroll.getDT().Rows) { ListViewItem listItem = new ListViewItem(row["EmpNameFirst"].ToString() + " " + row["EmpNameLast"].ToString()); listItem.Name = recordCount + "!" + row["EmpAppointID"].ToString(); // index for record in dbConnector + empAppointID listItem.SubItems.Add(row["UnitID"].ToString()); listItem.SubItems.Add(row["EmpTypeDesc"].ToString()); listItem.SubItems.Add(row["EmpAppTypeDesc"].ToString()); listItem.SubItems.Add(row["DeptID"].ToString()); listItem.SubItems.Add(row["FundType"].ToString()); listItem.SubItems.Add(row["FundProjClass"].ToString()); listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(row["EmpAppointFTCompRate"].ToString()))); listItem.SubItems.Add(String.Format("{0:0.##}", row["EmpAppointTimeBase"].ToString())); listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(row["EmpAppointSalary"].ToString()))); listItem.SubItems.Add(row["JobCodeID"].ToString()); listItem.SubItems.Add(row["PayrollDateStart"].ToString()); listItem.SubItems.Add(row["PayrollDateEnd"].ToString()); listItem.SubItems.Add(row["EmpAppointPositionNum"].ToString()); listItem.SubItems.Add(row["GLNumber"].ToString()); listItem.SubItems.Add(row["EtracDateStart"].ToString()); listItem.SubItems.Add(row["EtracDateEnd"].ToString()); //SORT COLUMNS listItem.SubItems.Add(UtilityDates.convertFormatedDateToSortableString(row["PayrollDateStart"].ToString())); listItem.SubItems.Add(UtilityDates.convertFormatedDateToSortableString(row["PayrollDateEnd"].ToString())); listItem.SubItems.Add(row["EmpAppointFTCompRate"].ToString().PadLeft(12, '0')); listItem.SubItems.Add(row["EmpAppointSalary"].ToString().PadLeft(12, '0')); listItem.SubItems.Add(row["EmpNameLast"].ToString()); lstPayroll.Items.Add(listItem); recordCount++; // get count of records added to lstPayroll } lstPayroll.EndUpdate(); lblPendingPayrollCount.Text = recordCount.ToString(); } catch (Exception ex) { //MessageBox.Show(ex.ToString()); } }
private void cmdRunByFund_Click(object sender, EventArgs e) { if (UtilityDates.isDate(txtTransDate.Text)) { frmTransactionsPayrollByApptType frmPayByFund = new frmTransactionsPayrollByApptType(txtTransDate.Text, lstPayrollGroup.SelectedValue.ToString(), this); //frmPayByFund.MdiParent = frmSwitchboard.frmParent; frmPayByFund.Show(); } else { MessageBox.Show("You must first select a target month for payroll processing."); } }
private void cmdRunTransactions_Click(object sender, EventArgs e) { //Make sure that a date was selected if (!UtilityDates.isDate(txtTransDate.Text)) { MessageBox.Show("The transaction date does not appear to be a valid date"); return; } this.Cursor = Cursors.WaitCursor; addEmpToVendors(); checkBeforeRun(); //loadLstErrors(); this.Cursor = Cursors.Default; }
//Load cmboFY private void loadCmboFY() { ArrayList arrNew = new ArrayList(); // arrNew.Add(new ListValues("", "ALL FYs", ListValues.FORWARD)); for (int i = UtilityDates.getCurrentFiscalYear(); i >= 2008; i--) { arrNew.Add(new ListValues(i.ToString(), i.ToString(), ListValues.FORWARD)); } cmboFY.DataSource = arrNew; cmboFY.DisplayMember = "Desc"; cmboFY.ValueMember = "ID"; cmboFY.SelectedIndex = 0; // selects current fy which is always the second item on the list isCmboFYLoaded = true; }
//method to take row information and put it into a string sequence private String dataRowToString(DataRow row) { String strReturn = row["refTransTypeSubDesc"].ToString() + "\t" + row["StatusDesc"].ToString() + "\t" + row["UnitID"].ToString() + " " + row["DeptID"].ToString() + " " + row["FundType"].ToString() + "\t" + row["FundProject"].ToString() + "\t" + row["FundClass"].ToString() + "\t" + row["VendName"].ToString() + "\t" + row["EmpUIN"].ToString() + "\t" + UtilityParser.parseNullDate(DateTime.Parse(row["TransDatePosting"].ToString()).ToShortDateString()) + "\t" + UtilityDates.translateDateToPeriod(DateTime.Parse(row["TransDatePosting"].ToString())) + "\t" + UtilityParser.parseNullDate(DateTime.Parse(row["TransDateTransaction"].ToString()).ToShortDateString()) + "\t" + String.Format(nfi, "{0:c}", double.Parse(row["TransAmount"].ToString())) + "\t" + String.Format(nfi, "{0:c}", double.Parse(row["TransAmountRemainder"].ToString())) + "\t" + row["TransDesc"].ToString() + "\t" + row["GLNumber"].ToString() + "\n"; return(strReturn); }
private void buildDBConnector(bool isSearched) { this.Cursor = Cursors.WaitCursor; String whereSQL = ""; if (isSearched) { whereSQL += " WHERE Transactions.TransID in ("; for (int i = 0; i < arrTransIDs.Count; i++) { whereSQL += arrTransIDs[i] + ","; } whereSQL = whereSQL.TrimEnd(','); whereSQL += ")"; Console.WriteLine("ccc " + whereSQL); } else { if (lstTransTypes.SelectedValue != null) { if (whereSQL.Contains("WHERE")) { whereSQL += " AND "; } else { whereSQL += " WHERE "; } whereSQL += " Transactions.refTransTypeID = " + lstTransTypes.SelectedValue.ToString(); } if (lstStatus.SelectedValue != null) { if (whereSQL.Contains("WHERE")) { whereSQL += " AND "; } else { whereSQL += " WHERE "; } whereSQL += " Transactions.TransStatusID = " + lstStatus.SelectedValue.ToString(); } if (lstUnitID.SelectedValue != null) { if (whereSQL.Contains("WHERE")) { whereSQL += " AND "; } else { whereSQL += " WHERE "; } whereSQL += " Transactions_Funding.UnitID = '" + lstUnitID.SelectedValue.ToString() + "' "; } if (lstTransGL.SelectedValue != null) { if (whereSQL.Contains("WHERE")) { whereSQL += " AND "; } else { whereSQL += " WHERE "; } whereSQL += " Transactions.GLID = '" + lstTransGL.SelectedValue.ToString() + "' "; } } whereSQL += " AND Transactions.TransDatePosting >= CONVERT(DATETIME, '" + UtilityDates.getCurrentFiscalYear() + "-07-01 00:00:00', 102) AND Transactions.TransDatePosting < CONVERT(DATETIME, '" + (UtilityDates.getCurrentFiscalYear() + 1) + "-07-01 00:00:00', 102)"; dbcTransactions = new DBConnector(baseSQL + whereSQL + sortSQL, "Transactions"); Console.WriteLine(baseSQL + whereSQL + sortSQL); loadlstTransactions(); this.Cursor = Cursors.Default; }
//Methods //fill dbcPayroll private void fillDBCPayroll() { lblPendingPayrollCount.Text = " "; if (!UtilityDates.isDate(txtTransDate.Text)) { MessageBox.Show("The transaction date does not appear to be a valid date"); return; } String sql = @" SELECT Employees.EmpNameFirst, Employees.EmpNameLast, Employees.EmpID, Employees.UnitID, Employees.EmpUIN, refAppointmentType.EmpAppTypeDesc, Employees_Appointments.EmpAppointPrimaryFund, Employees_Appointments.EmpAppointSalary, Employees_Appointments.EmpAppointTimeBase, Employees_Appointments.EmpAppointFTCompRate, Employees_Appointments.JobCodeID, CONVERT(nvarchar(10), Employees_Appointments.EmpAppointDateBegin, 101) AS EtracDateStart, CONVERT(nvarchar(10), Employees_Appointments.EmpAppointDateEnd, 101) AS EtracDateEnd, CONVERT(nvarchar(10), Employees_Appointments.EmpAppointPayrollDateStart, 101) AS PayrollDateStart, CONVERT(nvarchar(10), Employees_Appointments.EmpAppointPayrollDateEnd,101) AS PayrollDateEnd, Employees_Appointments.EmpAppointEtrac, Employees_Appointments.EmpAppointPositionNum, Employees_Appointments.EmpAppointID, Employees_JobCodes.JobCodeGLID, Employees_Appointments.TransFundID, Employees.EmpTypeID, refEmployeeType.EmpTypeDesc, Transactions_Funding.DeptID, Transactions_Funding.FundType, Transactions_Funding.FundProject + Transactions_Funding.FundClass AS FundProjClass, Transactions_GLs.GLNumber FROM Employees_Appointments INNER JOIN Employees ON Employees_Appointments.EmpID = Employees.EmpID INNER JOIN Employees_JobCodes ON Employees_Appointments.JobCodeID = Employees_JobCodes.JobCodeID INNER JOIN refAppointmentType ON Employees_Appointments.EmpAppTypeID = refAppointmentType.EmpAppTypeID INNER JOIN refEmployeeType ON Employees.EmpTypeID = refEmployeeType.EmpTypeID INNER JOIN Transactions_Funding ON Employees_Appointments.TransFundID = Transactions_Funding.TransFundID INNER JOIN Transactions_GLs ON Employees_JobCodes.JobCodeGLID = Transactions_GLs.GLID WHERE (Employees_Appointments.EmpAppointPayrollDateStart <= CONVERT(DATETIME, '" + txtTransDate.Text + @"', 102)) AND (Employees_Appointments.EmpAppointPayrollDateEnd >= CONVERT(DATETIME, '" + txtTransDate.Text + @"', 102)) AND Employees_JobCodes.JobCodeHourly = 1 AND Employees.UnitID <> 'OTHER' AND Employees_Appointments.EmpAppTypeID <> 6 AND Employees_JobCodes.JobCodeGL <> '000000' AND EmpAppointSalary > 0 AND Employees.EmpTypeID = " + payrollGroup; //Console.WriteLine("\n[283]\n [{0}]", sql); if (lstUnitPayroll.SelectedItems.Count > 0) //Harry 20151029 // if (isUnitSelected()) { sql += " AND Employees.UnitID = '" + lstUnitPayroll.SelectedItems[0].Name + "'"; } if (lstFundPayroll.SelectedItems.Count > 0) //Harry 20151029 // if (isFundSelected()) { //If fund is provided from form, append to query to filter for specified unit sql += " AND Employees_Appointments.TransFundID = " + lstFundPayroll.SelectedItems[0].Name.ToString() + " "; } if (lstGLPayroll.SelectedItems.Count > 0) //Harry 20151029 // if (isGLSelected()) { //If fund is provided from form, append to query to filter for specified unit sql += " AND Transactions_GLs.GLID = " + lstGLPayroll.SelectedItems[0].Name.ToString() + " "; } //add order to sql sql += " ORDER BY Employees_JobCodes.JobCodeGL,Employees.EmpNameLast"; dbcPayroll = new DBConnector(sql, "Appointments"); }