private void getPayrollPELRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, pey, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON " +
"tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND pey > 0";
parameters = new List <string>();
parameters.Add(employeeID);
PEYModel peyMod = new PEYModel();
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
peyMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
peyMod.LoanDate = dte.ToShortDateString();
peyMod.Loan = reader["pey"].ToString();
peyMod.Remarks = "AUTO-PAYSLIP";
lstPEY.Add(peyMod);
peyMod = new PEYModel();
}
conDB.closeConnection();
}
private void getPELRecord()
{
conDB = new ConnectionDB();
lstPEY = new List <PEYModel>();
PEYModel peyMod = new PEYModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, loandate, " +
"loans, interest FROM (tblemployees INNER JOIN tblloanspey ON " +
"tblemployees.ID = tblloanspey.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblloanspey.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
peyMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
peyMod.LoanDate = dte.ToShortDateString();
peyMod.Loan = reader["loans"].ToString();
peyMod.Interest = reader["interest"].ToString();
peyMod.Remarks = "MANUAL-ADMIN";
lstPEY.Add(peyMod);
peyMod = new PEYModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
PEYModel enkModd = dgvPEY.SelectedItem as PEYModel;
if (enkModd != null)
{
PEYDetails emlDet = new PEYDetails(enkModd.EmpID);
emlDet.ShowDialog();
}
}
private List <PEYModel> loadDatagridDetails()
{
conDB = new ConnectionDB();
List <PEYModel> lstPEY = new List <PEYModel>();
PEYModel peyMod = new PEYModel();
queryString = "SELECT tblloanspey.ID, empID, concat(firstname, ' ', lastname) as fullname, " +
"sum(loans) as loans, loandate, interest FROM (tblloanspey INNER JOIN tblemployees ON " +
" tblloanspey.empID = tblemployees.ID) " +
"WHERE tblloanspey.isDeleted = 0 AND tblemployees.isDeleted = 0 GROUP BY tblloanspey.empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
double isAddedbyAdmin = 0;
double current = 0;
while (reader.Read())
{
peyMod.ID = reader["ID"].ToString();
peyMod.EmpID = reader["empID"].ToString();
peyMod.FullName = reader["fullname"].ToString();
peyMod.Loan = reader["loans"].ToString();
peyMod.Interest = reader["interest"].ToString();
peyMod.TotalLoan = (Convert.ToDouble(peyMod.Loan) +
Convert.ToDouble(peyMod.Interest)).ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
peyMod.LoanDate = dte.ToShortDateString();
current = 0;
foreach (PEYModel p in lstPendingPEY)
{
if (p.EmpID.Equals(peyMod.EmpID))
{
double lo = Convert.ToDouble(peyMod.Loan);
double dblPending = Convert.ToDouble(p.Loan);
double dblInterests = Convert.ToDouble(peyMod.Interest);
peyMod.PendingBalance = (lo - dblPending).ToString();
peyMod.Loan = (lo - dblPending).ToString();
peyMod.TotalLoan = ((lo - dblPending) + dblInterests).ToString();
current += Convert.ToDouble(peyMod.TotalLoan);
}
}
isAddedbyAdmin += current;
lstPEY.Add(peyMod);
peyMod = new PEYModel();
}
lblTotalIS.Content = "Total: " + isAddedbyAdmin.ToString("N0");
conDB.closeConnection();
return(lstPEY);
}
private List <PEYModel> getPEYPending()
{
List <PEYModel> lstPeyPending = new List <PEYModel>();
PEYModel peys = new PEYModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(pey) as peypending FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
peys.EmpID = reader["empID"].ToString();
peys.Loan = reader["peypending"].ToString();
lstPeyPending.Add(peys);
peys = new PEYModel();
}
conDB.closeConnection();
return(lstPeyPending);
}
