public bool LogMobileTrail(MobileOperatorTrail trail)
{
bool insertResult = false;
try
{
string insertMobileUser = "******" +
" VALUES('" + trail.ReferenceNo.Format_Sql_String() + "','" +
trail.Ledger.Format_Sql_String() + "','" +
trail.CustomerNo.Format_Sql_String() + "','" +
trail.AccountNo.Format_Sql_String() + "','" +
ValueConverters.FormatSqlDate(trail.TransactionDate) + "','" +
trail.Description.Format_Sql_String() + "','" +
ValueConverters.ConvertNullToDecimal(trail.Amount) + "','" +
trail.DeviceInfo.Format_Sql_String() + "','" +
trail.LoginCode.Format_Sql_String() + "','" +
Guid.NewGuid().ToString() + "'); ";
int result = db.Database.ExecuteSqlCommand(insertMobileUser);
if (result >= 1)
{
insertResult = true;
}
}
catch (Exception ex)
{
Utility.WriteErrorLog(ex.Message, ref ex);
}
return(insertResult);
}
public bool RegisterMobileUser(MobileUsers user)
{
bool insertResult = false;
try
{
string insertMobileUser = "******" +
" VALUES('" + user.CustomerName.Format_Sql_String() + "','" +
user.MobileNo.Format_Sql_String() + "','" +
user.Pin + "','" +
user.tbl_CustomerId + "','" +
user.CustomerNo.Format_Sql_String() + "','" +
user.Email.Format_Sql_String() + "','" +
user.IdNo.Format_Sql_String() + "','" +
ValueConverters.FormatSqlDate(DateTime.Now) + "','" +
ValueConverters.ConvertNullToBool(user.Enabled) + "'); ";
int result = mainDb.Database.ExecuteSqlCommand(insertMobileUser);
if (result >= 1)
{
insertResult = true;
}
}
catch (Exception ex)
{
//log ex
}
return(insertResult);
}
public string TellerDayClosed(string tellerGlAccountNo)
{
string result = string.Empty;
if (!CustomValidation.ValidateGlAccount_(tellerGlAccountNo))
{
result = "You Do Not Have a Valid Teller Account ";
_validationDictionary.AddError("GLDEBIT", result);
_validationDictionary.AddError("PaidTo", result);
}
else
{
try
{
string query = "SELECT top 1 TransactionDate from tbl_Teller_Closing where TellerAccount='" +
tellerGlAccountNo + "' and TransactionDate>='" + ValueConverters.FormatSqlDate(DateTime.Now) + "'";
DbDataReader reader = DbConnector.GetSqlReader(query);
while (reader.Read())
{
string closedDate = reader["TransactionDate"].ToString();
if (ValueConverters.IsStringEmpty(closedDate) == false)
{
result = "Closing Already Done for This Teller ";
_validationDictionary.AddError("GLDEBIT", result);
_validationDictionary.AddError("PaidTo", result);
_validationDictionary.AddError("TransactionAmount", result);
_validationDictionary.AddError("CustomerNo", result);
}
}
reader.Close();
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
result = "Could not Check Account Status";
_validationDictionary.AddError("GLDEBIT", result);
_validationDictionary.AddError("PaidTo", result);
}
}
return(result);
}
public List <CustomerInvestmentStatementViewModel> GetSingleInvestmentStatement(List <CustomerInvestmentStatementViewModel> investmentStatement,
string customerno, string investmentCode, DateTime?startDate = null, DateTime?endDate = null)
{
if (investmentStatement == null)
{
investmentStatement = new List <CustomerInvestmentStatementViewModel>();
}
if (startDate == null)
{
startDate = new DateTime(1900, 01, 01);
}
if (endDate == null || endDate.Value.Year == 1900)
{
endDate = DateTime.Now;
}
string sqlStatement = " Exec getcustomerInvestments_single '" + customerno.Format_Sql_String() + "','" +
investmentCode.Format_Sql_String() + "','" + ValueConverters.FormatSqlDate(startDate) + "','" + ValueConverters.FormatSqlDate(endDate, true) + "'";
try
{
DbDataReader statement_Reader = DbConnector.GetSqlReader(sqlStatement);
investmentStatement = this.mainDb.Database.SqlQuery <CustomerInvestmentStatementViewModel>(sqlStatement).ToList();
int count = 0;
decimal balance = 0;
foreach (var item in investmentStatement)
{
balance = balance + item.Credit - item.Debit;
investmentStatement[count].Balance = balance;
investmentStatement[count].ProductName = getProductName(item.ProductCode);
count++;
}
}
catch (Exception ex)
{
// Utility.WriteErrorLog(ref ex);
}
return(investmentStatement);
}
public List <CustomerSavings> GetCustomerSavings(String accountNo)
{
List <CustomerSavings> customerSavings = new List <CustomerSavings>();
try
{
DateTime startDate = new DateTime(1990, 1, 1);
DateTime endDate = DateTime.Now;
String query = "Exec getcustomersavings '" + accountNo + "' ,'" +
ValueConverters.FormatSqlDate(startDate) + "' ,'" +
ValueConverters.FormatSqlDate(endDate) + "' ";
DbDataReader reader = DbConnector.GetSqlReader(query);
customerSavings = this.mainDb.Database.SqlQuery <CustomerSavings>(query).ToList();
}
catch (Exception e)
{
//log here
}
return(customerSavings);
}
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 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);
}