private List <PendingBalanceModel> loadUnpaidDR()
{
conDB = new ConnectionDB();
List <PendingBalanceModel> lstPending = new List <PendingBalanceModel>();
PendingBalanceModel pending = new PendingBalanceModel();
string queryString = "SELECT tblclients.ID, firstname, lastname, drNo, total FROM " +
"(dbpackage.tblorderhistory INNER JOIN dbpackage.tblclients ON " +
"dbpackage.tblorderhistory.clientID = dbpackage.tblclients.ID) " +
"WHERE (dbpackage.tblorderhistory.isDeleted = 0) AND (dbpackage.tblclients.isDeleted = 0) AND isPaid = 0 AND dbpackage.tblorderhistory.isCancelled = 0";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
pending.ID = reader["ID"].ToString();
pending.Firstname = reader["firstname"].ToString();
pending.Lastname = reader["lastname"].ToString();
pending.DRNo = reader["drNo"].ToString();
double z = Convert.ToDouble(reader["total"].ToString());
pending.Total = z.ToString("N0");
lstPending.Add(pending);
pending = new PendingBalanceModel();
}
conDB.closeConnection();
return(lstPending);
}
private List <PendingBalanceModel> loadIncompletePaidDR()
{
conDB = new ConnectionDB();
List <PendingBalanceModel> lstPending = new List <PendingBalanceModel>();
PendingBalanceModel pending = new PendingBalanceModel();
string queryString = "SELECT dbpackage.tblclients.ID, firstname, lastname, orderID, " +
"total - sum(amountpaid) as pending, total, isPaid FROM ((dbpackage.tblpaymenthistory " +
"INNER JOIN dbpackage.tblorderhistory ON dbpackage.tblpaymenthistory.orderID = dbpackage.tblorderhistory.ID) " +
"INNER JOIN dbpackage.tblclients ON dbpackage.tblpaymenthistory.clientID = dbpackage.tblclients.ID) " +
"WHERE dbpackage.tblorderhistory.isDeleted = 0 and dbpackage.tblpaymenthistory.isDeleted = 0 " +
"and isPaid = 0 group by orderID";
MySqlDataReader reader = conDB.getSelectConnection(queryString, null);
while (reader.Read())
{
pending.ID = reader["ID"].ToString();
pending.Firstname = reader["firstname"].ToString();
pending.Lastname = reader["lastname"].ToString();
//pending.DRNo = reader["drNo"].ToString();
pending.Total = reader["total"].ToString();
pending.UnpaidAmount = reader["pending"].ToString();
lstPending.Add(pending);
pending = new PendingBalanceModel();
}
conDB.closeConnection();
return(lstPending);
}
private List <PendingBalanceModel> loadPendingBalances()
{
List <PendingBalanceModel> unpaidDR = loadUnpaidDR();
List <PendingBalanceModel> incDR = loadIncompletePaidDR();
List <PendingBalanceModel> finalUnpaidDR = new List <PendingBalanceModel>();
PendingBalanceModel finalPBM = new PendingBalanceModel();
foreach (PendingBalanceModel pbm in unpaidDR)
{
finalPBM.ID = pbm.ID;
finalPBM.Firstname = pbm.Firstname;
finalPBM.Lastname = pbm.Lastname;
finalPBM.FullName = finalPBM.Firstname + " " + finalPBM.Lastname;
finalPBM.Total = pbm.Total;
finalPBM.DRNo = pbm.DRNo;
finalPBM.UnpaidAmount = pbm.Total;
foreach (PendingBalanceModel incpbm in incDR)
{
if (pbm.ID.Equals(incpbm.ID))
{
double x = Convert.ToDouble(pbm.Total);
double y = Convert.ToDouble(incpbm.UnpaidAmount);
double sum = x - y;
if (sum == 0)
{
finalPBM.UnpaidAmount = Convert.ToDouble(pbm.Total).ToString("N0");
}
else
{
finalPBM.UnpaidAmount = (y).ToString("N0");
}
}
//else
//{
// finalPBM.UnpaidAmount = pbm.Total;
//}
}
finalUnpaidDR.Add(finalPBM);
finalPBM = new PendingBalanceModel();
}
return(finalUnpaidDR);
}
