private void getPayrollPELRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, pel, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON " +
"tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND pel > 0";
parameters = new List <string>();
parameters.Add(employeeID);
PELModel pelMod = new PELModel();
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
pelMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
pelMod.LoanDate = dte.ToShortDateString();
pelMod.Loan = reader["pel"].ToString();
pelMod.Remarks = "AUTO-PAYSLIP";
lstPEL.Add(pelMod);
pelMod = new PELModel();
}
conDB.closeConnection();
}
private void getPELRecord()
{
conDB = new ConnectionDB();
lstPEL = new List <PELModel>();
PELModel pelMod = new PELModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, loandate, " +
"loans, interest FROM (tblemployees INNER JOIN tblloanspel ON " +
"tblemployees.ID = tblloanspel.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblloanspel.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
pelMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
pelMod.LoanDate = dte.ToShortDateString();
pelMod.Loan = reader["loans"].ToString();
pelMod.Interest = reader["interest"].ToString();
pelMod.Remarks = "MANUAL-ADMIN";
lstPEL.Add(pelMod);
pelMod = new PELModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
PELModel pelMod = dgvPEL.SelectedItem as PELModel;
if (pelMod != null)
{
PELDetails pelDet = new PELDetails(pelMod.EmpID);
pelDet.ShowDialog();
}
}
private List <PELModel> loadDatagridDetails()
{
conDB = new ConnectionDB();
List <PELModel> lstPEL = new List <PELModel>();
PELModel pelMod = new PELModel();
double isAddedByAdmin = 0;
queryString = "SELECT tblloanspel.ID, empID, concat(firstname, ' ', lastname) as fullname, " +
"sum(loans) as loans, loandate, sum(interest) as interest FROM (tblloanspel INNER JOIN tblemployees ON " +
" tblloanspel.empID = tblemployees.ID) " +
"WHERE tblloanspel.isDeleted = 0 GROUP BY tblloanspel.empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
pelMod.ID = reader["ID"].ToString();
pelMod.EmpID = reader["empID"].ToString();
pelMod.FullName = reader["fullname"].ToString();
pelMod.Loan = (string.IsNullOrEmpty(reader["loans"].ToString())) ?
"0" : reader["loans"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
pelMod.Interest = (!string.IsNullOrEmpty(reader["interest"].ToString())) ?
reader["interest"].ToString() : "0";
pelMod.TotalLoan = (Convert.ToDouble(pelMod.Loan) +
Convert.ToDouble(pelMod.Interest)).ToString();
pelMod.LoanDate = dte.ToShortDateString();
double current = 0;
foreach (PELModel p in lstPendingPEL)
{
if (p.EmpID.Equals(pelMod.EmpID))
{
double lo = Convert.ToDouble(pelMod.Loan);
double dblPending = Convert.ToDouble(p.Loan);
double dblInterests = Convert.ToDouble(pelMod.Interest);
pelMod.PendingBalance = (lo - dblPending).ToString();
pelMod.Loan = (lo - dblPending).ToString();
pelMod.TotalLoan = ((lo - dblPending) + dblInterests).ToString();
current += Convert.ToDouble(pelMod.TotalLoan);
}
}
isAddedByAdmin = isAddedByAdmin + current;
lstPEL.Add(pelMod);
pelMod = new PELModel();
}
lblTotalIS.Content = "Total: " + isAddedByAdmin.ToString("N0");
conDB.closeConnection();
return(lstPEL);
}
private List <PELModel> getPELPending()
{
List <PELModel> lstPelPending = new List <PELModel>();
PELModel pels = new PELModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(pel) as pelpending FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
pels.EmpID = reader["empID"].ToString();
pels.Loan = reader["pelpending"].ToString();
lstPelPending.Add(pels);
pels = new PELModel();
}
conDB.closeConnection();
return(lstPelPending);
}
