public List <CustomerBalances> GetCustomerBalances(string customerNo, DateTime endDate, List <CustomerBalances> custBalances)
{
custBalances = custBalances ?? new List <CustomerBalances>();
custBalances.Clear();
try
{
string sqlstatement = " CustomerBalances '" + ValueConverters.format_sql_string(customerNo) + "','" + ValueConverters.FormatSqlDate(endDate, true) + "'";
DbDataReader reader = DbConnector.GetSqlReader(sqlstatement);
double balance, interest, insurance, penalty, appraisal;
while (reader.Read())
{
balance = ValueConverters.StringtoDouble(reader["Balance"].ToString());
interest = ValueConverters.StringtoDouble(reader["IntBalance"].ToString());
insurance = ValueConverters.StringtoDouble(reader["InsBalance"].ToString());
penalty = ValueConverters.StringtoDouble(reader["PenBalance"].ToString());
appraisal = ValueConverters.StringtoDouble(reader["AppBalance"].ToString());
custBalances.Add(new CustomerBalances
{
CustomerNo = ValueConverters.ConvertNullToEmptyString(reader["CustomerNo"].ToString()),
AccountNo = ValueConverters.ConvertNullToEmptyString(reader["AccountNo"].ToString()),
CustomerName = ValueConverters.ConvertNullToEmptyString(reader["CustomerName"].ToString()),
Category = ValueConverters.ConvertNullToEmptyString(reader["Category"].ToString()),
Ttype = ValueConverters.ConvertNullToEmptyString(reader["ttype"].ToString()),
ProductName = ValueConverters.ConvertNullToEmptyString(reader["ProductName"].ToString()),
Balance = balance,
IntBalance = interest,
InsBalance = insurance,
PenBalance = penalty,
AppBalance = appraisal,
TotalBalance = ValueConverters.Round05(balance + interest + insurance + penalty + appraisal),
OtherCharges = insurance + penalty + appraisal
});
}
reader.Close();
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
}
return(custBalances);
}
public double GetGlBalance(string accountNo)
{
double glBalance = 0.00;
try
{
string query = "Exec GetGlBalance '" + ValueConverters.format_sql_string(accountNo) + "'";
DbDataReader reader = DbConnector.GetSqlReader(query);
while (reader.Read())
{
glBalance = ValueConverters.StringtoDouble(reader["balance"].ToString());
}
reader.Close();
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
}
return(glBalance);
}
public List <CustomerDetailsView> GetCustomerDetails(string customerNo)
{
List <CustomerDetailsView> custRecord = null;
if (ValueConverters.IsStringEmpty(customerNo))
{
return(custRecord);
}
customerNo = ValueConverters.PADLeft(Int32.Parse(customerNoMask), customerNo.Trim(), '0');
try
{
string sqlCommand = "SELECT SigningInstructions, CustomerNo,coalesce(CustomerIdNo,'') as CustomerIdNo,coalesce(CustomerName,'') as CustomerName,Locked,AccountRemarks,AccountComments,MemberType,Branch," +
" AccountRemarks,AccountComments,DateClosed,coalesce(MobileNo,'') as MobileNo,EmpNumber as EmploymentNo,DateOfBirth,JoiningDate,Coalesce(EmployerCode,'') as EmployerCode," +
" coalesce((select name from BranchSettings where branchcode=tbl_customer.Branch),'') as BranchName, " +
" coalesce((select MembershipName from tbl_MembershipTypes where MembershipCode=tbl_customer.MemberType),'') as MemberTypeName, " +
" coalesce((select DPTNAME from tbl_Departments where DptCode=tbl_customer.EmployerCode),'') as EmployerName " +
" from tbl_customer WHERE CUSTOMERNO ='" + ValueConverters.format_sql_string(customerNo) + "'";
DbDataReader reader = DbConnector.GetSqlReader(sqlCommand);
custRecord = Functions.DataReaderMapToList <CustomerDetailsView>(reader);
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
}
if (custRecord == null || custRecord.Count < 1)
{
custRecord = new List <CustomerDetailsView>
{
new CustomerDetailsView()
};
custRecord[0].CustomerName = "Customer Record Not Found ";
}
return(custRecord);
}
public List <CustomerLoanStatementViewModel> GetSingleLoanStatement(List <CustomerLoanStatementViewModel> loanStatement,
string customerno, string loanCode, DateTime?startDate = null, DateTime?endDate = null,
string combinedRepayments = "")
{
if (loanStatement == null)
{
loanStatement = new List <CustomerLoanStatementViewModel>();
}
if (startDate == null)
{
startDate = new DateTime(1900, 01, 01);
}
if (endDate == null || endDate.Value.Year == 1900)
{
endDate = DateTime.Now;
}
string sqlStatement = " Exec getcustomerloans_Single '" + ValueConverters.format_sql_string(customerno) + "','" +
ValueConverters.format_sql_string(loanCode) + "','" + ValueConverters.FormatSqlDate(startDate) + "','" + ValueConverters.FormatSqlDate(endDate, true) + "'";
try
{
DbDataReader statement_Reader = DbConnector.GetSqlReader(sqlStatement);
loanStatement = this.mainDb.Database.SqlQuery <CustomerLoanStatementViewModel>(sqlStatement).ToList();
int count = 0;
decimal balance = 0, intbalance = 0, appBalance = 0, penBalance = 0, insBalance = 0;
foreach (var item in loanStatement)
{
if (combinedRepayments == "1")
{
item.Debit = item.Debit + item.IntDr + item.PenDr + item.InsDr + item.AppDb;
item.Credit = item.Credit + item.IntCr + item.PenCr + item.InsCr + item.AppCr;
item.IntDr = item.PenDr = item.InsDr = item.AppDb = item.IntCr = item.PenCr = item.InsCr = item.AppCr = 0;
}
balance = balance + item.Debit - item.Credit;
intbalance = intbalance + item.IntDr - item.IntCr;
appBalance = appBalance + item.AppDb - item.AppCr;
penBalance = penBalance + item.PenDr - item.PenCr;
insBalance = insBalance + item.InsDr - item.InsCr;
loanStatement[count].OtherDr = item.AppDb + item.PenDr + item.InsDr;
loanStatement[count].OtherCr = item.AppCr + item.PenCr + item.InsCr;
loanStatement[count].OtherBal = penBalance + appBalance + insBalance;
loanStatement[count].Balance = balance;
loanStatement[count].InsBal = insBalance;
loanStatement[count].IntBal = intbalance;
loanStatement[count].AppBal = appBalance;
loanStatement[count].PenBal = penBalance;
loanStatement[count].TotBalance = (decimal)ValueConverters.Round05((double)(penBalance + appBalance + intbalance + insBalance + balance));
count++;
}
}
catch (Exception ex)
{
//log here
}
return(loanStatement);
}