private void getPayrollElecBillRecord()
{
conDB = new ConnectionDB();
queryString = "SELECT empID, electricbill, concat(lastname,', ', firstname) as fullname, tblpayroll.startdate" +
" FROM (tblpayroll INNER JOIN tblemployees ON " +
"tblpayroll.empID = tblemployees.ID)" +
" WHERE tblpayroll.isDeleted = 0 AND empID = ? AND electricbill > 0";
parameters = new List <string>();
parameters.Add(employeeID);
ElectricBillModel elecBill = new ElectricBillModel();
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
elecBill.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["startdate"].ToString());
elecBill.DateAdded = dte.ToShortDateString();
elecBill.CurrentElecBill = reader["electricbill"].ToString();
elecBill.Remarks = "AUTO-PAYSLIP";
lstElecBill.Add(elecBill);
elecBill = new ElectricBillModel();
}
conDB.closeConnection();
}
private void getElecBillRecord()
{
conDB = new ConnectionDB();
lstElecBill = new List <ElectricBillModel>();
ElectricBillModel elecBill = new ElectricBillModel();
queryString = "SELECT tblemployees.ID, concat(lastname,', ', firstname) as fullname, dateadded, " +
"elecbill FROM (tblemployees INNER JOIN tblelecbill ON " +
"tblemployees.ID = tblelecbill.empID) " +
"WHERE tblemployees.isDeleted = 0 AND tblelecbill.isDeleted = 0 " +
"AND tblemployees.ID = ?";
parameters = new List <string>();
parameters.Add(employeeID);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
elecBill.FullName = reader["fullname"].ToString();
DateTime dte = DateTime.Parse(reader["dateadded"].ToString());
elecBill.DateAdded = dte.ToShortDateString();
elecBill.CurrentElecBill = reader["elecbill"].ToString();
elecBill.Remarks = "MANUAL-ADMIN";
lstElecBill.Add(elecBill);
elecBill = new ElectricBillModel();
}
conDB.closeConnection();
}
private void btnDetails_Click(object sender, RoutedEventArgs e)
{
ElectricBillModel eMod = dgvElecBill.SelectedItem as ElectricBillModel;
if (eMod != null)
{
ElecBillDetails eDet = new ElecBillDetails(eMod.empID);
eDet.ShowDialog();
}
}
private List <ElectricBillModel> getElecBillToAdd()
{
List <ElectricBillModel> lstElecBill = new List <ElectricBillModel>();
ElectricBillModel eBill = new ElectricBillModel();
conDB = new ConnectionDB();
queryString = "SELECT empID, sum(electricbill) as electricbill FROM tblpayroll WHERE tblpayroll.isDeleted = 0 GROUP BY empID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
eBill.empID = reader["empID"].ToString();
eBill.ElecBillToAdd = reader["electricbill"].ToString();
lstElecBill.Add(eBill);
eBill = new ElectricBillModel();
}
conDB.closeConnection();
return(lstElecBill);
}
private List <ElectricBillModel> getElecBillRecord()
{
List <ElectricBillModel> lstElecBill = new List <ElectricBillModel>();
ElectricBillModel elecBill = new ElectricBillModel();
conDB = new ConnectionDB();
queryString = "SELECT tblelecbill.empid, sum(elecbill) as electricbill, concat(lastname,' , ', firstname) as fullname, " +
"dateadded FROM (tblelecbill INNER JOIN tblemployees ON " +
"tblelecbill.empID = tblemployees.ID) WHERE tblelecbill.isDeleted = 0 GROUP BY tblelecbill.empid";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
double isAddedByAdmin = 0;
while (reader.Read())
{
elecBill.empID = reader["empID"].ToString();
elecBill.FullName = reader["fullname"].ToString();
double current = Convert.ToDouble(reader["electricbill"].ToString());
foreach (ElectricBillModel ism in lstElecBillToAdd)
{
if (elecBill.empID.Equals(ism.empID))
{
current = current - Convert.ToDouble(ism.ElecBillToAdd);
}
}
isAddedByAdmin += current;
DateTime dte = DateTime.Parse(reader["dateadded"].ToString());
elecBill.DateAdded = dte.ToShortDateString();
elecBill.CurrentElecBill = current.ToString();
lstElecBill.Add(elecBill);
elecBill = new ElectricBillModel();
}
lblTotalIS.Content = "Total: " + isAddedByAdmin.ToString("N0");
conDB.closeConnection();
return(lstElecBill);
}