private void getPayrollSSSLoanRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, sssloan, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON " +
"tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND sssloan > 0";
parameters = new List <string>();
parameters.Add(employeeID);
SSSLoanModel sssLoan = new SSSLoanModel();
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
sssLoan.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
sssLoan.DateAdded = dte.ToShortDateString();
sssLoan.CurrentSSSLoan = reader["sssloan"].ToString();
sssLoan.Remarks = "AUTO-PAYSLIP";
lstSSSLoan.Add(sssLoan);
sssLoan = new SSSLoanModel();
}
conDB.closeConnection();
}
private void getSSSLoanRecord()
{
conDB = new ConnectionDB();
lstSSSLoan = new List <SSSLoanModel>();
SSSLoanModel sssLoan = new SSSLoanModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, dateadded, " +
"sssloan FROM (tblemployees INNER JOIN tblsssloan ON " +
"tblemployees.ID = tblsssloan.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblsssloan.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
sssLoan.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["dateadded"].ToString());
sssLoan.DateAdded = dte.ToShortDateString();
sssLoan.CurrentSSSLoan = reader["sssloan"].ToString();
sssLoan.Remarks = "MANUAL-ADMIN";
lstSSSLoan.Add(sssLoan);
sssLoan = new SSSLoanModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
SSSLoanModel sLo = dgvSSSLoan.SelectedItem as SSSLoanModel;
if (sLo != null)
{
SSSLoanDetails sloD = new SSSLoanDetails(sLo.empID);
sloD.ShowDialog();
}
}
private List <SSSLoanModel> getSSSToAdd()
{
List <SSSLoanModel> listSSS = new List <SSSLoanModel>();
SSSLoanModel sss = new SSSLoanModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(sssloan) as sssloan FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
sss.empID = reader["empID"].ToString();
sss.SSStoAdd = reader["sssloan"].ToString();
listSSS.Add(sss);
sss = new SSSLoanModel();
}
conDB.closeConnection();
return(listSSS);
}
private List <SSSLoanModel> getSSSRecord()
{
List <SSSLoanModel> lstSSS = new List <SSSLoanModel>();
SSSLoanModel sss = new SSSLoanModel();
conDB = new ConnectionDB();
queryString = "SELECT tblsssloan.empid, sum(sssloan) as existingsss, concat(lastname,' , ', firstname) as fullname, " +
"dateadded FROM (tblsssloan INNER JOIN tblemployees ON " +
"tblsssloan.empID = tblemployees.ID) WHERE tblsssloan.isDeleted = 0 GROUP BY tblsssloan.empid";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
double isAddedByAdmin = 0;
while (reader.Read())
{
sss.empID = reader["empID"].ToString();
sss.FullName = reader["fullname"].ToString();
double current = Convert.ToDouble(reader["existingsss"].ToString());
foreach (SSSLoanModel ism in lstSSSLoanToAdd)
{
if (sss.empID.Equals(ism.empID))
{
current = current - Convert.ToDouble(ism.SSStoAdd);
}
}
isAddedByAdmin += current;
DateTime dte = DateTime.Parse(reader["dateadded"].ToString());
sss.DateAdded = dte.ToShortDateString();
sss.CurrentSSSLoan = current.ToString();
lstSSS.Add(sss);
sss = new SSSLoanModel();
}
lblTotalIS.Content = "Total: " + isAddedByAdmin.ToString("N0");
conDB.closeConnection();
return(lstSSS);
}
