//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");
}
