public int generateSavingsPassbook()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT TOP 1 SavingsPassbookNo FROM MEMBER_SAVINGS_PASSBOOK ORDER BY SavingsPassbookNo DESC";
SqlDataReader read = dal.executeReader(sql);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i + 1;
}
public int generateCertificateNo()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT TOP 1 CertificateNo FROM TIME_DEPOSIT ORDER BY CertificateNo DESC";
SqlDataReader read = dal.executeReader(sql);
int i = 0;
while (read.Read())
{
String maxAcctNo = read[0].ToString().Split('-')[1];
i = Convert.ToInt32(maxAcctNo);
}
return i + 1;
}
public int generateAccountNo()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT TOP 1 SavingsAccountNo FROM SAVINGS_ACCOUNT ORDER BY SavingsAccountNo DESC";
SqlDataReader read = dal.executeReader(sql);
int i = 0;
while (read.Read())
{
String maxAcctNo = read[0].ToString().Split('-')[1];
i = Convert.ToInt32(maxAcctNo);
}
return i + 1;
}
public int selectActivePassbook(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT SavingsPassbookNo FROM MEMBER_SAVINGS_PASSBOOK WHERE SavingsAccountNo = @accountNo AND Status = 1";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@accountNo", this.selectSavingsAccount(accountNo));
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = int.Parse(read[0].ToString());
}
return i;
}
public int checkBalanceRange(double MinBal, double MaxBal, int savings, int interestid)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM INTEREST_RATE WHERE AccountTypeId = @savings AND InterestId != @interestid AND Status = 1 AND MaximumRange != 0 AND ((@MinBal BETWEEN MinimumRange AND MaximumRange) OR (@MaxBal BETWEEN MinimumRange AND MaximumRange) OR (MinimumRange >= @MinBal AND MaximumRange <= @MaxBal))";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@savings", savings);
parameters.Add("@interestid", interestid);
parameters.Add("@MinBal", MinBal);
parameters.Add("@MaxBal", MaxBal);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public double selectMaximumWithdrawal(int AccountType)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MaximumWithdrawal FROM MAXIMUM_WITHDRAWAL WHERE AccountTypeId = @TypeId";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@TypeId", AccountType);
SqlDataReader read = dal.executeReader(sql, parameters);
double d = 0;
while (read.Read())
{
d = double.Parse(read[0].ToString());
}
return d;
}
public double selectMaintainingBalance(int AccountType)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MaintainingBalance FROM MAINTAINING_BALANCE WHERE SavingsAccountTypeId = @TypeId";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@TypeId", AccountType);
SqlDataReader read = dal.executeReader(sql, parameters);
double d = 0;
while (read.Read())
{
d = double.Parse(read[0].ToString());
}
return d;
}
public SqlDataReader selectMember(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT * FROM MEMBER INNER JOIN MEMBER_TYPE ON MEMBER.MemberTypeNo = MEMBER_TYPE.MemberTypeNo WHERE AccountNo = @acctNo";
Dictionary<String, Object> parameters = new Dictionary<String, Object>();
parameters.Add("@acctNo", accountNo);
SqlDataReader ds = dal.executeReader(sql, parameters);
return ds;
}
public int getLoanPassbook(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT TOP 1 LoanPassbookNo FROM MEMBER_LOAN_PASSBOOK WHERE AccountNo = @acctNo ORDER BY LoanPassbookNo DESC";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@acctNo", accountNo);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public int checkBracketed(int savings, double MinBal, int earlyWithdrawalId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM EARLY_WITHDRAWAL WHERE EarlyWithdrawalId != @earlyWithdrawalId AND AccountTypeId = @savings AND Status = 1 AND (MaximumRange >= @MinBal OR MinimumRange >= @MinBal)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@earlyWithdrawalId", earlyWithdrawalId);
parameters.Add("@savings", savings);
parameters.Add("@MinBal", MinBal);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public int getInsertId()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT TOP 1 AccountTypeId FROM SAVINGS_ACCOUNT_TYPE ORDER BY AccountTypeId DESC";
SqlDataReader read = dal.executeReader(sql);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i + 1;
}
public double selectInitialDeposit(int TypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT InitialDeposit FROM SAVINGS_ACCOUNT_TYPE WHERE Status = 1 AND isArchived = 0 AND isTimeDeposit = 1 AND AccountTypeId = @TypeId";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@TypeId", TypeId);
SqlDataReader read = dal.executeReader(sql, parameters);
double d = 0;
while (read.Read())
{
d = double.Parse(read[0].ToString());
}
return d;
}
public SqlDataReader selectClassNames(String newName, String oldName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT dbo.RemoveSpecialChars(Description), dbo.RemoveSpecialChars(@newname), DelinquencyValue AS 'Delinquency', DelinquencyUnit AS 'Unit', DelinquencyBasis AS 'Basis' FROM Member_Status WHERE isArchived = 0 and Description != @oldName";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@newname", newName);
parameters.Add("@oldName", oldName);
SqlDataReader ds = dal.executeReader(sql,parameters);
return ds;
}
public int checkName(String Name)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM SAVINGS_ACCOUNT_TYPE WHERE dbo.RemoveSpecialChars(SavingsTypeName) = dbo.RemoveSpecialChars(@Name) AND isArchived = 0";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@Name", Name);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public int checkInterest(int savings, double interest, int interestid)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM INTEREST_RATE WHERE AccountTypeId = @savings AND InterestRate = @interest AND InterestId != @interestid";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@savings", savings);
parameters.Add("@interest", interest);
parameters.Add("@interestid", interestid);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public int checkPenalty(int savings, double penalty, String penaltyRate, int id)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM EARLY_WITHDRAWAL WHERE AccountTypeId = @savings AND PenaltyValue = @penalty AND PenaltyStatus = @penaltyRate AND EarlyWithdrawalId != @id";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@savings", savings);
parameters.Add("@penalty", penalty);
parameters.Add("@penaltyRate", penaltyRate);
parameters.Add("@id", id);
SqlDataReader read = dal.executeReader(sql, parameters);
int i = 0;
while (read.Read())
{
i = (int)read[0];
}
return i;
}
public SqlDataReader selectFeeNames(String name)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT COUNT(*) FROM Fee WHERE isArchived = 0 AND dbo.RemoveSpecialChars(Description) = dbo.RemoveSpecialChars(@name)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@name", name);
SqlDataReader ds = dal.executeReader(sql, parameters);
return ds;
}
public SqlDataReader selectMemberTypeNames(String newName, String oldName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT Description FROM Member_Type WHERE isArchived = 0 AND NOT(Description = @old) AND dbo.RemoveSpecialChars(Description) = dbo.RemoveSpecialChars(@new)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@old", oldName);
parameters.Add("@new", newName);
SqlDataReader ds = dal.executeReader(sql,parameters);
return ds;
}
