private void getPayrollGRLRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, grl, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND grl > 0";
parameters = new List <string>();
parameters.Add(employeeID);
GRLModel pelMod = new GRLModel();
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["grl"].ToString();
pelMod.Remarks = "AUTO-PAYSLIP";
lstGRL.Add(pelMod);
pelMod = new GRLModel();
}
conDB.closeConnection();
}
private void getGRLRecord()
{
conDB = new ConnectionDB();
lstGRL = new List <GRLModel>();
GRLModel grlMod = new GRLModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, loandate, " +
"loans FROM (tblemployees INNER JOIN tblloansgrl ON " +
"tblemployees.ID = tblloansgrl.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblloansgrl.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
grlMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
grlMod.LoanDate = dte.ToShortDateString();
grlMod.Loan = reader["loans"].ToString();
grlMod.Remarks = "MANUAL-ADMIN";
lstGRL.Add(grlMod);
grlMod = new GRLModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
GRLModel pelModd = dgvGRL.SelectedItem as GRLModel;
if (pelModd != null)
{
GRLDetails grlDet = new GRLDetails(pelModd.EmpID);
grlDet.ShowDialog();
}
}
private List <GRLModel> loadDatagridDetails()
{
conDB = new ConnectionDB();
List <GRLModel> lstGRL = new List <GRLModel>();
GRLModel grlMode = new GRLModel();
queryString = "SELECT tblloansgrl.ID, empID, concat(firstname, ' ', lastname) as fullname, " +
"sum(loans) as loans, loandate FROM (tblloansgrl INNER JOIN tblemployees ON " +
" tblloansgrl.empID = tblemployees.ID) " +
"WHERE tblloansgrl.isDeleted = 0 GROUP BY tblloansgrl.empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
double isAddedbyAdmin = 0;
double current = 0;
while (reader.Read())
{
grlMode.ID = reader["ID"].ToString();
grlMode.EmpID = reader["empID"].ToString();
grlMode.FullName = reader["fullname"].ToString();
grlMode.Loan = reader["loans"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
grlMode.LoanDate = dte.ToShortDateString();
current = 0;
foreach (GRLModel p in lstPendingGRL)
{
if (p.EmpID.Equals(grlMode.EmpID))
{
double lo = Convert.ToDouble(grlMode.Loan);
double dblPending = Convert.ToDouble(p.Loan);
grlMode.PendingBalance = (lo - dblPending).ToString();
grlMode.Loan = (lo - dblPending).ToString();
current += Convert.ToDouble(grlMode.Loan);
}
}
isAddedbyAdmin += current;
lstGRL.Add(grlMode);
grlMode = new GRLModel();
}
lblTotalIS.Content = "Total: " + isAddedbyAdmin.ToString("N0");
conDB.closeConnection();
return(lstGRL);
}
private List <GRLModel> getGRLPending()
{
List <GRLModel> lstGrlPending = new List <GRLModel>();
GRLModel grls = new GRLModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(grl) as grlpending FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
grls.EmpID = reader["empID"].ToString();
grls.Loan = reader["grlpending"].ToString();
lstGrlPending.Add(grls);
grls = new GRLModel();
}
conDB.closeConnection();
return(lstGrlPending);
}
