//Load lstPayrollMonths
public void loadLstPayrollMonths()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadPayrollTransactionsByMonth(InfoLoader.PAYROLL_SALARY);
lstPayrollMonths.BeginUpdate();
lstPayrollMonths.Clear();
lstPayrollMonths.Columns.Add("MONTH", 150, HorizontalAlignment.Left);
lstPayrollMonths.Columns.Add("# TRANS", 70, HorizontalAlignment.Left);
lstPayrollMonths.Columns.Add("MM", 0, HorizontalAlignment.Left);
lstPayrollMonths.Columns.Add("YYYY", 0, HorizontalAlignment.Left);
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 3)
{
ListViewItem listItem = new ListViewItem(UtilityDates.months[int.Parse(cols[0])] + " " + cols[1]);
listItem.Name = cols[0]; // index for Month
listItem.SubItems.Add(cols[2]);
listItem.SubItems.Add(cols[0]);
listItem.SubItems.Add(cols[1]);
lstPayrollMonths.Items.Add(listItem);
}
}
lstPayrollMonths.EndUpdate();
}
//load Transaction GLs - lstTransGL
private void loadLstTransGL()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadAllGLs().Split('\n');
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 3)
{
arrNew.Add(new ListValues(cols[0], cols[1] + " " + cols[2], ListValues.FORWARD));
}
}
if (arrNew.Count > 0)
{
lstTransGL.DataSource = arrNew;
lstTransGL.DisplayMember = "Desc";
lstTransGL.ValueMember = "ID";
lstTransGL.ClearSelected();
}
else
{
lstTransGL.DataSource = null;
}
}
private void loadLstUnits()
{
// Create an instance of a ListView column sorter and assign it
// to the ListView control.
lvwColumnSorter = new ListViewColumnSorter();
this.lstUnits.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadUnits(true).Split('\n'); //do not include "OTHER"
selectedUnitID = "";
lstUnits.BeginUpdate();
lstUnits.Clear();
lstUnits.Columns.Add("Units", 75, HorizontalAlignment.Left);
lstUnits.Columns.Add("_SortUnits", 0, HorizontalAlignment.Left);
//Add "All Units" option
ListViewItem listItem = new ListViewItem("ALL UNITS");
listItem.Name = "";
lstUnits.Items.Add(listItem);
//Add results from Units query
for (int i = 0; i < tmpArray.Length; i++)
{
if (tmpArray[i].Trim().Length > 0)
{
listItem = new ListViewItem(tmpArray[i]);
listItem.Name = tmpArray[i];
lstUnits.Items.Add(listItem);
}
}
lstUnits.EndUpdate();
}
//Load lstTransSubTypes
private void loadLstTransSubTypes()
{
// Create an instance of a ListView column sorter and assign it
// to the ListView control.
lvwColumnSorter = new ListViewColumnSorter();
this.lstTransSubTypes.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadTransSubTypes(selectedTransType, false).Split('\n'); //false = non-utility trans types
selectedTransSubType = "";
lstTransSubTypes.BeginUpdate();
lstTransSubTypes.Clear();
lstTransSubTypes.Columns.Add("Trans Sub Type", 160, HorizontalAlignment.Left);
lstTransSubTypes.Columns.Add("_SortTransSubType", 0, HorizontalAlignment.Left);
//Add results from TransTypes query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 2)
{
ListViewItem listItem = new ListViewItem(cols[1]);
listItem.Name = cols[0];
lstTransSubTypes.Items.Add(listItem);
}
}
lstTransSubTypes.EndUpdate();
}
private void loadLstTransStatus()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadStatus(InfoLoader.TABLE_TRANSACTIONS).Split('\n'); //
selectedStatusID = "";
lstTransStatus.BeginUpdate();
lstTransStatus.Clear();
lstTransStatus.Columns.Add("Payee", 117, HorizontalAlignment.Left);
//Add "ANY STATUSES" option
ListViewItem listItem = new ListViewItem("ANY STATUS");
listItem.Name = "";
lstTransStatus.Items.Add(listItem);
//Add results from Payee query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 2)
{
listItem = new ListViewItem(cols[1]);
listItem.Name = cols[0];
lstTransStatus.Items.Add(listItem);
}
}
lstTransStatus.EndUpdate();
}
private void lstPayroll_DoubleClick(object sender, EventArgs e)
{
ListViewItem item = ((ListView)sender).SelectedItems[0];
String[] wholeName = item.Name.ToString().Split('!');
frmEmployee frmEmp = new frmEmployee(InfoLoader.getEmpIDfromAppointID(wholeName[1]));
frmEmp.MdiParent = frmSwitchboard.frmParent;
frmEmp.Show();
}
private void loadLstTransPayees()
{
lvwColumnSorter = new ListViewColumnSorter();
this.lstTransPayees.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadPayeesByTransType(selectedTransType).Split('\n'); //
selectedTransPayeeID = "";
lstTransPayees.BeginUpdate();
lstTransPayees.Clear();
lstTransPayees.Columns.Add("Payee", 120, HorizontalAlignment.Left);
lstTransPayees.Columns.Add("_SortPayees", 0, HorizontalAlignment.Left);
//Add "All PAYEES" option
ListViewItem listItem = new ListViewItem(" ALL PAYEES");
listItem.Name = "";
lstTransPayees.Items.Add(listItem);
//Add results from Payee query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 3) //01062016 sort by LastName Harry.
{
var names = cols[1].TrimEnd().Split(' ');
if (cols[2] != "")
{
string lastName;
if (names[names.Length - 1].IndexOf('(') != -1)
{
lastName = names[names.Length - 2];
}
else
{
lastName = names[names.Length - 1];
}
cols[1] = lastName + ", " + cols[1].Replace(lastName, "");
//cols[1] = names[1] + ", " + names[0];
}
listItem = new ListViewItem(cols[1]);
listItem.Name = cols[0];
lstTransPayees.Items.Add(listItem);
}
}
lstTransPayees.EndUpdate();
}
//Load lstGLPayroll
private void loadLstGLPayroll(bool isFilterByUnit)
{
ArrayList arrNew = new ArrayList();
String[] tmpArray;
String unit = "";//Should remain empty if no unit is specified
if (isFilterByUnit)
{
unit = lstUnitPayroll.SelectedItems[0].Name;
}
String fund = "";
//try
if (lstFundPayroll.SelectedItems.Count > 0) //Harry 20151029
{
fund = lstFundPayroll.SelectedItems[0].Name;
}
//catch (Exception ex)
//{
// //No fund was selected for filtering
//}
tmpArray = InfoLoader.getPayrollByGL(txtTransDate.Text, unit, fund, payrollGroup == PG_FACULTY).Split('\n');
lstGLPayroll.BeginUpdate();
lstGLPayroll.Clear();
lstGLPayroll.Columns.Add("Count", 50, HorizontalAlignment.Left);
lstGLPayroll.Columns.Add("GL", 200, HorizontalAlignment.Left);
lstGLPayroll.Columns.Add("Payroll", 106, HorizontalAlignment.Left);
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 5)
{
ListViewItem listItem = new ListViewItem(cols[3]);
listItem.Name = cols[4]; // GLID for record in dbConnector
listItem.SubItems.Add(cols[0] + " " + cols[1]);
listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(cols[2])));
lstGLPayroll.Items.Add(listItem);
}
}
lstGLPayroll.EndUpdate();
}
//Load Unit listbox lstUnitID
private void loadLstUnit()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadUnits(true).Split('\n');
for (int i = 0; i < tmpArray.Length; i++)
{
if (tmpArray[i].ToString().Trim().Length > 0)
{
arrNew.Add(new ListValues(tmpArray[i], ListValues.FORWARD));
}
}
lstUnitID.DataSource = arrNew;
lstUnitID.DisplayMember = "Desc";
lstUnitID.ValueMember = "ID";
lstUnitID.ClearSelected();
}
//Load refStatus cmboTransStatusID
public void loadStatus()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadStatus(InfoLoader.TABLE_TRANSACTIONS).Split('\n');
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 2)
{
arrNew.Add(new ListValues(cols[0], cols[1], ListValues.FORWARD));
}
}
lstStatus.DataSource = arrNew;
lstStatus.DisplayMember = "Desc";
lstStatus.ValueMember = "ID";
lstStatus.ClearSelected();
}
//Load main transaction type listbox lstTransTypes
private void loadLstTransTypes()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadTransTypes(false).Split('\n'); //false = non-utility trans types
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 2)
{
arrNew.Add(new ListValues(cols[0], cols[1], ListValues.FORWARD));
arrNew.Sort();
}
}
if (arrNew.Count > 0)
{
lstTransTypes.DataSource = arrNew;
lstTransTypes.DisplayMember = "Desc";
lstTransTypes.ValueMember = "ID";
}
lstTransTypes.ClearSelected();
}
private void loadLstTransFunding()
{
// Create an instance of a ListView column sorter and assign it
// to the ListView control.
lvwColumnSorter = new ListViewColumnSorter();
this.lstTransFunding.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadTransFunding(getLstUnitsSelection()).Split('\n');
selectedTransFundID = "";
lstTransFunding.BeginUpdate();
lstTransFunding.Clear();
lstTransFunding.Columns.Add("Trans Funding", 205, HorizontalAlignment.Left);
lstTransFunding.Columns.Add("_SortTransFund", 0, HorizontalAlignment.Left);
//Add "All Funds" option
ListViewItem listItem = new ListViewItem("ALL FUNDS");
listItem.Name = "";
lstTransFunding.Items.Add(listItem);
//Add results from Funds query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 7)
{
listItem = new ListViewItem(makeFundingString(cols));
listItem.Name = cols[0];
lstTransFunding.Items.Add(listItem);
}
}
lstTransFunding.EndUpdate();
}
//Load lstFundPayroll
private void loadLstFundPayroll(bool isFilterByUnit)
{
String unit = "";
if (isFilterByUnit)
{
unit = lstUnitPayroll.SelectedItems[0].Name;
}
arrLstFunds = InfoLoader.getPayrollByFund(txtTransDate.Text, unit, payrollGroup == PG_FACULTY).Split('\n');
lstFundPayroll.BeginUpdate();
lstFundPayroll.Clear();
lstFundPayroll.Columns.Add("Count", 50, HorizontalAlignment.Left);
lstFundPayroll.Columns.Add("Dept ID", 70, HorizontalAlignment.Left);
lstFundPayroll.Columns.Add("Fund Type", 80, HorizontalAlignment.Left);
lstFundPayroll.Columns.Add("Project", 70, HorizontalAlignment.Left);
lstFundPayroll.Columns.Add("Class", 65, HorizontalAlignment.Left);
lstFundPayroll.Columns.Add("Payroll", 80, HorizontalAlignment.Left);
for (int i = 0; i < arrLstFunds.Length; i++)
{
String[] cols = arrLstFunds[i].Split('\t');
if (cols.Length == 7)
{
ListViewItem listItem = new ListViewItem(cols[6]);
listItem.Name = cols[0]; // index for record in dbConnector
listItem.SubItems.Add(cols[2]);
listItem.SubItems.Add(cols[1]);
listItem.SubItems.Add(cols[3]);
listItem.SubItems.Add(cols[4]);
listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(cols[5])));
lstFundPayroll.Items.Add(listItem);
}
}
lstFundPayroll.EndUpdate();
}
private void loadLstGLs()
{
// Create an instance of a ListView column sorter and assign it
// to the ListView control.
lvwColumnSorter = new ListViewColumnSorter();
this.lstGLs.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadAllGLs().Split('\n'); //
selectedGLID = "";
lstGLs.BeginUpdate();
lstGLs.Clear();
lstGLs.Columns.Add("GLs", 195, HorizontalAlignment.Left);
lstGLs.Columns.Add("_SortGLs", 0, HorizontalAlignment.Left);
//Add "All GLs" option
ListViewItem listItem = new ListViewItem("ALL GLs");
listItem.Name = "";
lstGLs.Items.Add(listItem);
//Add results from GL query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 3)
{
listItem = new ListViewItem(cols[1] + " " + cols[2]);
listItem.Name = cols[0];
lstGLs.Items.Add(listItem);
}
}
lstGLs.EndUpdate();
}
private void loadLstTrans2FundType()
{
// Create an instance of a ListView column sorter and assign it
// to the ListView control.
lvwColumnSorter = new ListViewColumnSorter();
this.lstFund2Type.ListViewItemSorter = lvwColumnSorter;
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.loadFundTypes(2, "2010").Split('\n');
lstFund2Type.BeginUpdate();
lstFund2Type.Clear();
lstFund2Type.Columns.Add("Trans Funding", 200, HorizontalAlignment.Left);
lstFund2Type.Columns.Add("_SortFund2type", 0, HorizontalAlignment.Left);
//Add "All Funds" option
ListViewItem listItem = new ListViewItem("ALL");
listItem.Name = "";
lstFund2Type.Items.Add(listItem);
//Add results from Funds query
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols[0].Length > 0)
{
listItem = new ListViewItem(cols[0]);
listItem.Name = cols[0];
lstFund2Type.Items.Add(listItem);
}
}
lstFund2Type.EndUpdate();
//lstFund2Type.Items[0].Selected = true;
}
//Load lstUnitPayroll
private void loadLstUnitPayroll()
{
ArrayList arrNew = new ArrayList();
String[] tmpArray = InfoLoader.getPayrollByUnit(txtTransDate.Text, payrollGroup == PG_FACULTY).Split('\n');
lstUnitPayroll.BeginUpdate();
lstUnitPayroll.Clear();
lstUnitPayroll.Columns.Add("UNIT", 70, HorizontalAlignment.Left);
lstUnitPayroll.Columns.Add("PAYROLL", 106, HorizontalAlignment.Left);
for (int i = 0; i < tmpArray.Length; i++)
{
String[] cols = tmpArray[i].Split('\t');
if (cols.Length == 2)
{
ListViewItem listItem = new ListViewItem(cols[0]);
listItem.Name = cols[0]; // index for record in dbConnector
listItem.SubItems.Add(String.Format(nfi, "{0:c}", double.Parse(cols[1])));
lstUnitPayroll.Items.Add(listItem);
}
}
lstUnitPayroll.EndUpdate();
}
//collect all employees with active appointments spanning the supplied transaction date
private void collectAppointments()
{
//Check for null payroll dates
String[] errors = InfoLoader.getAppointmentsWithNullPayrollDates().Split('\n');
if (int.Parse(errors[0]) != 0)
{
String errMsg = "The following appointments are missing payroll dates and prevent running payroll:\n\n";
for (int i = 1; i < errors.Length; i++)
{
String[] cols = errors[i].Split('\t');
if (cols.Length == 10)
{
errMsg += "Position Number " + cols[9] + " for employee " + cols[0] + " ( " + cols[7] + "-" + cols[8] + " Job Code: " + cols[2] + ")\n\n";
}
}
MessageBox.Show(errMsg);
cmdRunTransactions.Enabled = true;
return;
}
//Check for funding problems
errors = InfoLoader.getAppointmentsWithNullTransFundID().Split('\n');
if (int.Parse(errors[0]) != 0)
{
String errMsg = "The following appointments are missing a valid funding source and prevent running payroll:\n\n";
for (int i = 1; i < errors.Length; i++)
{
String[] cols = errors[i].Split('\t');
if (cols.Length == 10)
{
errMsg += "Position Number " + cols[9] + " for employee " + cols[0] + " ( " + cols[7] + "-" + cols[8] + " Job Code: " + cols[2] + ")\n\n";
}
}
MessageBox.Show(errMsg);
cmdRunTransactions.Enabled = true;
return;
}
//Clear filtering selections
clearAllSelections();
fillDBCPayroll();
int recordsCreated = 0;
//Loop through appointments to create transactions
SqlCommandBuilder commBuilder = new SqlCommandBuilder(dbcTransactions.getDA());
//Make sure that all TransSubTypes match
int transTypeSubID;
//int a = 0;
foreach (DataRow row in dbcPayroll.getDT().Rows)
{
//Console.WriteLine(a++);
transTypeSubID = InfoLoader.getTransSubTypeByGL(row["JobCodeGLID"].ToString());
if (transTypeSubID == 0)
{
MessageBox.Show("There was an error processing this payroll request. The following GL is not associated with a Transaction Sub Type:\n" +
InfoLoader.getGLFromID(row["JobCodeGLID"].ToString()));
cmdRunTransactions.Enabled = true;
return;
}
}
//------------------------------------------------------
//Console.WriteLine("dbcPayroll.getDT().Rows: [{0}]", dbcPayroll.getDT().Rows.Count.ToString());
String sql = "SELECT * FROM ActivityLog";
DBConnector log = new DBConnector(sql, "ActivityLog");
//int b = 0;
DateTime transDate = DateTime.Parse(txtTransDate.Text);
String nowS = DateTime.Now.ToShortDateString();
DateTime now = DateTime.Now;
//[Bao] NextTransRelatedID !!!!!!!!!!!!!!!!!!!!
int nextTransRElatedID = InfoLoader.getNextTransRelatedID();
String userName = UserProfile.getUserName();
int tableID = InfoLoader.TABLE_TRANSACTIONS;
//int actRowID = InfoLoader.getLatestTransID();
int empID = UserProfile.getUserID();
int actTypeID = Activities.ACT_T_NEW;
String actDesc = InfoLoader.getTransType(5) + " TRANSACTION CREATED";
//------------------------------------------------------
foreach (DataRow row in dbcPayroll.getDT().Rows)
{
//Console.WriteLine(b++);
transTypeSubID = InfoLoader.getTransSubTypeByGL(row["JobCodeGLID"].ToString());
// Should be INCREASE HERE !!!!!!!
nextTransRElatedID = nextTransRElatedID + 1;
try
{
//CREATE NEW RECORD
DataRow newRow = dbcTransactions.getDT().NewRow();
int getVendID = int.Parse(InfoLoader.getVendIDFromEmpID(int.Parse(row["EmpID"].ToString())));
newRow["TransStatusID"] = 11;
newRow["refTransTypeID"] = 5;
newRow["refTransTypeSubID"] = transTypeSubID;
newRow["TransFundID"] = int.Parse(row["TransFundID"].ToString());
newRow["TransPayeeID"] = getVendID;
newRow["TransVendID"] = getVendID;
newRow["UnitID"] = row["UnitID"].ToString();
newRow["TransDatePosting"] = transDate;
newRow["TransDateTransaction"] = transDate;
newRow["TransAmount"] = double.Parse(row["EmpAppointSalary"].ToString());
newRow["TransAmountRemainder"] = 0;
newRow["GLID"] = row["JobCodeGLID"].ToString();
newRow["TransDesc"] = "PAYROLL";
newRow["TransRelatedID"] = nextTransRElatedID;
newRow["TransCreatedUser"] = userName;
newRow["TransCreatedDate"] = nowS;
dbcTransactions.getDT().Rows.Add(newRow);
//newRow["TransRelatedID"] = nextTransRElatedID;
//Add Creation Notice to Activity Log
//Activities.addActivity(InfoLoader.TABLE_TRANSACTIONS,
// InfoLoader.getLatestTransID(),
// UserProfile.getUserID(),
// Activities.ACT_T_NEW,
// InfoLoader.getTransType(5) + " TRANSACTION CREATED");
//Activities.addActivityTrans(tableID, actRowID++, empID, actTypeID, actDesc, log, now);
Activities.addActivityTrans(tableID, nextTransRElatedID, empID, actTypeID, actDesc, log, now);
}
catch (Exception ex) // copy transaction errors to payroll error table
{
MessageBox.Show("ERROR: " + ex.ToString());
}
recordsCreated++;
}
dbcTransactions.getDA().Update(dbcTransactions.getDS(), "Transactions");
SqlCommandBuilder commBuilder2 = new SqlCommandBuilder(log.getDA());
//Update database with new information from data table
log.getDA().Update(log.getDS().Tables[0]);
MessageBox.Show(recordsCreated + " payroll transactions were created for " + lstPayrollGroup.Text);
loadPage();
cmdRunTransactions.Enabled = true;
}
//Search transactions for specified criteria
private void searchTransactions()
{
buildDBConnector(false);
arrTransIDs = new ArrayList();
String[] data;
//Determine type of user search request
int switchCase = int.Parse(cmboTransSearch.SelectedValue.ToString());
switch (switchCase)
{
case 0:
data = InfoLoader.transSearchByDesc(txtTransSearch.Text).Split('\n');
break;
case 1:
data = InfoLoader.transSearchTransDetails(txtTransSearch.Text).Split('\n');
break;
case 2:
data = InfoLoader.transSearchPayee(txtTransSearch.Text).Split('\n');
break;
case -1:
return;
default:
return;
}
for (int i = 0; i < data.Length; i++)
{
if (data[i].Length > 0)
{
arrTransIDs.Add(data[i].ToString());
}
}
if (arrTransIDs.Count > 0)
{
buildDBConnector(true);
}
else
{
lstTransactions.Clear();
buildlstTransactionsColumns();
}
/*
* //get detail element info
* String[] elementData = InfoLoader.getTransDetElement(cmboTransSearch.SelectedValue.ToString()).Split('\t');
* int itemIdex = int.Parse(elementData[2]) + 1;
*
* //find any relevant detail types by identifying transsubtypeid
* String[] detailData = InfoLoader.loadDetailsBySubType(elementData[1]).Split('\n');
*
* //look for requested data in results
*
*/
}
