private void getPayrollPELRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, eml, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND eml > 0";
parameters = new List <string>();
parameters.Add(employeeID);
EMLModel emlMod = new EMLModel();
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
emlMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
emlMod.LoanDate = dte.ToShortDateString();
emlMod.Loan = reader["eml"].ToString();
emlMod.Remarks = "AUTO-PAYSLIP";
lstEML.Add(emlMod);
emlMod = new EMLModel();
}
conDB.closeConnection();
}
private void getEMLRecord()
{
conDB = new ConnectionDB();
lstEML = new List <EMLModel>();
EMLModel emlMod = new EMLModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, loandate, " +
"loans FROM (tblemployees INNER JOIN tblloanseml ON " +
"tblemployees.ID = tblloanseml.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblloanseml.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
emlMod.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
emlMod.LoanDate = dte.ToShortDateString();
emlMod.Loan = reader["loans"].ToString();
emlMod.Remarks = "MANUAL-ADMIN";
lstEML.Add(emlMod);
emlMod = new EMLModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
EMLModel enkModd = dgvEML.SelectedItem as EMLModel;
if (enkModd != null)
{
EMLDetails emlDet = new EMLDetails(enkModd.EmpID);
emlDet.ShowDialog();
}
}
private List <EMLModel> loadDatagridDetails()
{
conDB = new ConnectionDB();
List <EMLModel> lstEML = new List <EMLModel>();
EMLModel emlMode = new EMLModel();
queryString = "SELECT tblloanseml.ID, empID, concat(firstname, ' ', lastname) as fullname, " +
"sum(loans) as loans, loandate, sum(interest) as interest FROM (tblloanseml INNER JOIN tblemployees ON " +
" tblloanseml.empID = tblemployees.ID) " +
"WHERE tblloanseml.isDeleted = 0 GROUP BY tblloanseml.empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
double isAddedbyAdmin = 0;
double current = 0;
while (reader.Read())
{
emlMode.ID = reader["ID"].ToString();
emlMode.EmpID = reader["empID"].ToString();
emlMode.FullName = reader["fullname"].ToString();
emlMode.Loan = (string.IsNullOrEmpty(reader["loans"].ToString())) ?
"0" : reader["loans"].ToString();
DateTime dte = DateTime.Parse(reader["loandate"].ToString());
emlMode.Interest = (!string.IsNullOrEmpty(reader["interest"].ToString())) ?
reader["interest"].ToString() : "0";
emlMode.TotalLoan = (Convert.ToDouble(emlMode.Loan) +
Convert.ToDouble(emlMode.Interest)).ToString();
emlMode.LoanDate = dte.ToShortDateString();
current = 0;
foreach (EMLModel p in lstPendingEML)
{
if (p.EmpID.Equals(emlMode.EmpID))
{
double lo = Convert.ToDouble(emlMode.Loan);
double dblPending = Convert.ToDouble(p.Loan);
double dblInterests = Convert.ToDouble(emlMode.Interest);
emlMode.PendingBalance = (lo - dblPending).ToString();
emlMode.Loan = (lo - dblPending).ToString();
emlMode.TotalLoan = ((lo - dblPending) + dblInterests).ToString();
current += Convert.ToDouble(emlMode.TotalLoan);
}
}
isAddedbyAdmin += current;
lstEML.Add(emlMode);
emlMode = new EMLModel();
}
lblTotalIS.Content = "Total: " + isAddedbyAdmin.ToString("N0");
conDB.closeConnection();
return(lstEML);
}
private List <EMLModel> getEMLPending()
{
List <EMLModel> lstEmlPending = new List <EMLModel>();
EMLModel emls = new EMLModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(eml) as emlpending FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
emls.EmpID = reader["empID"].ToString();
emls.Loan = reader["emlpending"].ToString();
lstEmlPending.Add(emls);
emls = new EMLModel();
}
conDB.closeConnection();
return(lstEmlPending);
}