public int insertloanCharge(String chargeName, double amount, String amountStatus, ArrayList loanTypeNo, int Status)
{
int id = 0;
int resultInner = 0;
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "EXEC insertCharges @Status, @ChargeName, @Amount, @AmountStatus, @isArchived";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@Status", Status);
parameters.Add("@ChargeName", chargeName);
parameters.Add("@Amount", amount);
parameters.Add("@AmountStatus", amountStatus);
parameters.Add("@isArchived", 0);
int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
String sqlSelect = "Select ChargeId from CHARGES where isArchived=0 and ChargeName= " + "'" + chargeName + "'";
id = Convert.ToInt32(dal.executeScalar(sqlSelect));
if (result != 0)
{
foreach (int i in loanTypeNo)
{
String sqlInner = "INSERT INTO LOAN_TYPE_CHARGES VALUES (@LoanTypeId, @ChargeId)";
Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
parametersInner.Add("@LoanTypeId", i);
parametersInner.Add("@ChargeId", id);
resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
}
}
return resultInner;
}
public int checkIfLoanIsCollateralized(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select isCollateral from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
int isCollateral = Convert.ToInt32(dal.executeScalar(sql));
return isCollateral;
}
public int checkIfLoanHasComakers(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select CoMaker from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
int comaker = Convert.ToInt32(dal.executeScalar(sql));
return comaker;
}
public String checkSpouseName(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select SpouseName from MEMBER WHERE AccountNo = " + "'" + accountNo + "'";
String spouse = Convert.ToString(dal.executeScalar(sql));
return spouse;
}
public int insertFee(String feeDesc, Double feeAmount, ArrayList memberTypeNo, int Status)
{
int resultInner = 0;
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "EXEC insertFee @feeDesc, @Amount, @Status;";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@feeDesc", feeDesc);
parameters.Add("@Amount", feeAmount);
parameters.Add("@Status", Status);
int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
Object rs = dal.executeScalar("SELECT @@IDENTITY"); //problem area
int id = int.Parse(rs.ToString());
if (id != 0)
{
foreach (int i in memberTypeNo)
{
String sqlInner = "INSERT INTO MEMBER_TYPE_FEE (MemberTypeNo, FeeId) VALUES (@memberTypeNo, @feeId)";
Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
parametersInner.Add("@memberTypeNo", i);
parametersInner.Add("@feeId", id);
resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
}
}
return resultInner;
}
public DataSet selectActiveMember()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select Member.AccountNo as 'Account No.', concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) as 'Account Name', Member_Type.Description AS 'Member Type' from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
String sqlct = "Select COUNT(Member.AccountNo) from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo and AccountNo not in (Select AccountNo from Termination)";
DataSet ds = dal.executeDataSet(sql);
checkEmpty = Convert.ToInt32(dal.executeScalar(sqlct));
return ds;
}
public int InsertMinCapitalContrib()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sqlUpdate = "Update MINIMUM_CAPITAL_CONTRIBUTION set isArchived = 1 where MemberTypeNo = "+"'"+this.comboAccountType+"'";
dal.executeScalar(sqlUpdate);
String sql = "EXEC SpInsert_MinimumCapitalContribution @MinimumContribution,@Duration,@Status,@MemberTypeNo";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@MinimumContribution", this.txtContribution);
parameters.Add("@Duration", this.comboContribution);
parameters.Add("@Status", this.Status);
parameters.Add("@MemberTypeNo", this.comboAccountType);
int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
return result;
}
public void insertInterestRate()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String updateSql = "Update LOAN_INTEREST_RATE set isArchived = 1 where LoanTypeId = " + "'" + this.LoanTypeId + "'";
dal.executeScalar(updateSql);
String sql = "EXEC insertLoanInterestRate @LoanTypeId, @InterestRateStatus, @InterestRateValue, @Per, @ActivationDate, @Status";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeId", this.LoanTypeId);
parameters.Add("@InterestRateStatus", this.InterestRateStatus);
parameters.Add("@InterestRateValue", this.InterestRate);
parameters.Add("@Per", this.Per);
parameters.Add("@ActivationDate", this.DateFrom);
parameters.Add("@Status", this.Status);
dal.executeNonQuery(sql, parameters);
}
public void insertLoanPayment(String paymentType, String accountNo, int applicationId, double amount, String duedate, Boolean hasInterest)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sqlSelect = "Select AmortizationId from loan_amortization where LoanApplicationId = " + "'" + applicationId + "' and AmortizationDueDate ="+"'"+duedate+"'";
int amortizationId = Convert.ToInt32(dal.executeScalar(sqlSelect));
String sql = "EXEC insertAmortizationPayment @PaymentType, @AmountPaid, @hasInterest, @AccountNo, @AmortizationId, @LoanApplicationId, @isFullyPaid";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@PaymentType", paymentType);
parameters.Add("@AmountPaid", amount);
parameters.Add("@hasInterest", hasInterest);
parameters.Add("@AccountNo", accountNo);
parameters.Add("@AmortizationId", amortizationId);
parameters.Add("@LoanApplicationId", applicationId);
parameters.Add("@isFullyPaid", 0);
dal.executeNonQuery(sql, parameters);
String sql2 = "Update LOAN_AMORTIZATION set isPaid = 1 where AmortizationId =" +"'"+amortizationId+"'";
dal.executeScalar(sql2);
String sql3 = "Select count(AmortizationId) from LOAN_AMORTIZATION WHERE isPaid = 1 and LoanApplicationId= "+"'"+applicationId+"'";
int countPaid = Convert.ToInt32(dal.executeScalar(sql3));
String sql4 = "Select count(AmortizationId) from LOAN_AMORTIZATION WHERE LoanApplicationId= " + "'" + applicationId + "'";
int countAmortization = Convert.ToInt32(dal.executeScalar(sql4));
if (countPaid == countAmortization)
{
String sql5 = "Update PAYMENT set isFullyPaid = 1 where LoanApplicationId =" + "'" + applicationId + "'";
dal.executeScalar(sql5);
String sql6 = "Update LOAN_INFORMATION set isCleared = 1 where LoanApplicationId =" + "'" + applicationId + "'";
dal.executeScalar(sql6);
}
}
public double selectRemainingBalance(int loanApplicationId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select sum(Amount) from loan_amortization where isPaid = 0 and LoanApplicationId = " + "'" + loanApplicationId + "'";
double amount = Convert.ToDouble(dal.executeScalar(sql));
return amount;
}
public int selectPaymentDatesWithInterestRates(int loanApplicationId, String firstDate, String secondDate)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select count(ORNo) from PAYMENT where hasInterest = 1 and LoanApplicationId = "+"'"+loanApplicationId+"'"+" and PaymentDate between "+"'"+firstDate+"'"+ "and " + "'" + secondDate + "'";
int i = Convert.ToInt32(dal.executeScalar(sql));
return i;
}
public String selectMaturityDate(int loanApplicationId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select MaturityDate from LOAN_INFORMATION where LoanApplicationId = " + "'" + loanApplicationId + "'";
String mdate = Convert.ToString(dal.executeScalar(sql));
return mdate;
}
public String selectInterestPerLoanType(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select concat(InterestRateStatus,' ',InterestRateValue,' ',Per) from LOAN_INTEREST_RATE where (CURRENT_TIMESTAMP >= ActivationDate) and Status = 1 and isArchived = 0 and LoanTypeId =" + "'" + loanTypeId + "'";
String interest = Convert.ToString(dal.executeScalar(sql));
return interest;
}
public double selectGrantedLoanAmount(int loanApplicationId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select ApprovedAmount from LOAN_INFORMATION where loanapplicationid = " + "'" + loanApplicationId + "'";
double amount = Convert.ToDouble(dal.executeScalar(sql));
return amount;
}
public int selectPreviousEligibility(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select isPreviousUnpaid from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
int elig = Convert.ToInt32(dal.executeScalar(sql));
return elig;
}
public String selectCurrentShareCapital(String accountNo)
{
try
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select SUM(ShareCapital) from MEMBER_SHARECAPITAL where AccountNo = " + "'" + accountNo + "'";
String currentShareCapital = (Convert.ToDouble(dal.executeScalar(sql))).ToString();
return currentShareCapital;
}
catch (Exception) { return "0"; }
}
public int insertLoanApplication(String AccountNo, int LoanTypeId, String Purpose, String PaymentDurationStatus, int PaymentDurationValue, String Terms, double LoanAmount, double ApprovedAmount, String DateFiled, String DateApproved, String DateMaturity, double NetLoan)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "EXEC insertLoanInformation @AccountNo,@LoanTypeId,@Purpose,@PaymentDurationStatus,@PaymentDurationValue,@Terms,@LoanAmount,@ApprovedAmount,@DateFiled,@DateApproved,@MaturityDate,@NetLoanProceeds";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@AccountNo", AccountNo);
parameters.Add("@LoanTypeId", LoanTypeId);
parameters.Add("@Purpose", Purpose);
parameters.Add("@PaymentDurationStatus", PaymentDurationStatus);
parameters.Add("@PaymentDurationValue", PaymentDurationValue);
parameters.Add("@Terms", Terms);
parameters.Add("@LoanAmount", LoanAmount);
parameters.Add("@ApprovedAmount", ApprovedAmount);
parameters.Add("@DateFiled", DateFiled);
parameters.Add("@DateApproved", DateApproved);
parameters.Add("@MaturityDate", DateMaturity);
parameters.Add("@NetLoanProceeds", NetLoan);
dal.executeNonQuery(sql, parameters);
String sql2 = "select max(LoanApplicationId) from Loan_Information";
int loanApplicationId = Convert.ToInt32(dal.executeScalar(sql2));
return loanApplicationId;
}
public String selectInterestRate(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select concat(InterestRateValue,' ',InterestRateStatus) as 'Interest Rate' from LOAN_INTEREST_RATE where status=1 and isArchived = 0 and loanTypeId =" + "'" + loanTypeId + "'";
String interest = Convert.ToString(dal.executeScalar(sql));
return interest;
}
public int selectLoanApprovedCount(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select count(LoanApplicationId)+1 from LOAN_INFORMATION where AccountNo = " + "'" + accountNo + "'";
int loanCount = Convert.ToInt32(dal.executeScalar(sql));
return loanCount;
}
public String selectTotalSavings(String accountNo)
{
try
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select sum(SAVINGS_ACCOUNT.Balance) from SAVINGS_ACCOUNT, MEMBER_SAVINGS_ACCOUNT WHERE SAVINGS_ACCOUNT.SavingsAccountNo = MEMBER_SAVINGS_ACCOUNT.SavingsAccountNo AND MEMBER_SAVINGS_ACCOUNT.MemberAccountNo = " + "'" + accountNo + "'";
String totalSavings = (Convert.ToDouble(dal.executeScalar(sql))).ToString();
return totalSavings;
}
catch (Exception) { return "0"; }
}
public double selectTimeDepositBalance(int certificateNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select DepositAmount from TIME_DEPOSIT where CertificateNo = " + "'" + certificateNo + "'";
double balance = Convert.ToDouble(dal.executeScalar(sql));
return balance;
}
public String selectMaximumLoanableAmount(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select concat(MaxLoanableAmt,' ',MaxLoanableAmtStatus,' ',Deduction) from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
String mla = Convert.ToString(dal.executeScalar(sql));
return mla;
}
public Boolean selectSavingsStatus(int memberTypeNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select hasSavings from MEMBER_TYPE WHERE MemberTypeNo =" + "'" + memberTypeNo + "'";
Boolean hasSavings = Convert.ToBoolean(dal.executeScalar(sql));
return hasSavings;
}
public DataSet selectActiveMembershipUnpaid()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select Member.AccountNo as 'Account No.', concat(Member.FirstName,' ',Member.LastName) as 'Name', MEMBER_TYPE.Description as 'Member Type' from Member, Member_Type where Member.Status = 1 and Member.MemberTypeNo=Member_Type.MemberTypeNo AND (AccountNo in (select accountNo from Fee, Member_Type, Member_Type_Fee,Member where Fee.FeeId=Member_Type_Fee.FeeId and Member_Type.MemberTypeNo=MEMBER_TYPE_FEE.MemberTypeNo and Member_type.MemberTypeNo = MEMBER.MemberTypeNo and Fee.isArchived=0 and Member_Type.MemberTypeNo= MEMBER.MemberTypeNo and Fee.dateCreated < Member.DateCreated group by AccountNo having count(fee.description) > 0)) and (AccountNo not in (Select AccountNo from Payment) or AccountNo in (Select AccountNo from Payment where isFullyPaid=0 and PaymentType='Membership')) and Member.AccountNo not in (Select AccountNo from Termination)";
String sqlct = "Select COUNT(Member.AccountNo) from Member, Member_Type where Member.MemberTypeNo=Member_Type.MemberTypeNo AND (AccountNo in (select accountNo from Fee, Member_Type, Member_Type_Fee,Member where Member.Status = 1 and Fee.FeeId=Member_Type_Fee.FeeId and Member_Type.MemberTypeNo=MEMBER_TYPE_FEE.MemberTypeNo and Member_type.MemberTypeNo = MEMBER.MemberTypeNo and Fee.isArchived=0 and Member_Type.MemberTypeNo= MEMBER.MemberTypeNo and Fee.dateCreated < Member.DateCreated group by AccountNo having count(fee.description) > 0)) and (AccountNo not in (Select AccountNo from Payment) or AccountNo in (Select AccountNo from Payment where isFullyPaid=0 and PaymentType='Membership')) and Member.AccountNo not in (Select AccountNo from Termination)";
DataSet ds = dal.executeDataSet(sql);
checkEmpty = Convert.ToInt32(dal.executeScalar(sqlct));
return ds;
}
public int activateStatus(String accountNo)
{
int result = 0;
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MemberStatusNo FROM MEMBER_STATUS WHERE isArchived = 0 AND status=1 AND DelinquencyValue=0";
try
{
int StatusNo = int.Parse(dal.executeScalar(sql).ToString());
String sqle = "Update Member SET MemberStatusNo = @statusNo WHERE AccountNo=@AccountNo";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@statusNo", StatusNo);
parameters.Add("@AccountNo", this.AccountNo);
result = Convert.ToInt32(dal.executeNonQuery(sqle, parameters));
}
catch (Exception) { }
return result;
}
public DataSet selectActiveMemberWithLoan()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select distinct Member.AccountNo as 'Account No.', concat (Member.FirstName,' ',member.LastName) as 'Name', MEMBER_TYPE.Description from MEMBER, MEMBER_TYPE, LOAN_INFORMATION WHERE MEMBER.AccountNo = LOAN_INFORMATION.AccountNo and MEMBER.MemberTypeNo = MEMBER_TYPE.MemberTypeNo AND LOAN_INFORMATION.isCleared = 0;";
String sqlct = "Select count(Member.AccountNo) from MEMBER, MEMBER_TYPE, LOAN_INFORMATION WHERE MEMBER.AccountNo = LOAN_INFORMATION.AccountNo and MEMBER.MemberTypeNo = MEMBER_TYPE.MemberTypeNo AND LOAN_INFORMATION.isCleared = 0;";
DataSet ds = dal.executeDataSet(sql);
checkEmpty = Convert.ToInt32(dal.executeScalar(sqlct));
return ds;
}
public int selectUnclearedLoan(String accountNo)
{
try
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select LoanApplicationId from LOAN_INFORMATION where isCleared = 0 and AccountNo = " + "'" + accountNo + "'";
int lappId = Convert.ToInt32(dal.executeScalar(sql));
return lappId;
}
catch (Exception) { return 0; }
}
public String selectPaymentDuration(int loanTypeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select concat(MinimumPaymentDuration,' ',MinPDStatus,' to ',MaximumPaymentDuration,' ',MaxPDStatus) from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
String pd = Convert.ToString(dal.executeScalar(sql));
return pd;
}
public int insertPenalties(String penaltyName, int gp, double amount, String amountStatus, String deduction, String durationStatus, int durationValue, ArrayList loanTypeNo, int Status)
{
int id = 0;
int resultInner = 0;
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "EXEC insertPenalty @PenaltyName, @GracePeriod, @Amount, @AmountStatus, @Deduction, @DurationStatus, @DurationValue, @Status, @isArchived";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@PenaltyName", penaltyName);
parameters.Add("@GracePeriod", gp);
parameters.Add("@Amount", amount);
parameters.Add("@AmountStatus", amountStatus);
parameters.Add("@Deduction", deduction);
parameters.Add("@DurationStatus", durationStatus);
parameters.Add("@DurationValue", durationValue);
parameters.Add("@Status", Status);
parameters.Add("@isArchived", 0);
int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
String sqlSelect = "Select PenaltyId from PENALTY where isArchived=0 and PenaltyName= " + "'" + penaltyName + "'";
id = Convert.ToInt32(dal.executeScalar(sqlSelect));
if (result != 0)
{
foreach (int i in loanTypeNo)
{
String sqlInner = "INSERT INTO LOAN_TYPE_PENALTY VALUES (@LoanTypeId, @PenaltyId)";
Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
parametersInner.Add("@LoanTypeId", i);
parametersInner.Add("@PenaltyId", id);
resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
}
}
return resultInner;
}
public String selectUnclearedLoanType(int loanApplicationId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select concat(LOAN_INFORMATION.LoanTypeId,' ',LOAN_TYPE.LoanTypeName) from LOAN_INFORMATION, LOAN_TYPE where LOAN_INFORMATION.LoanTypeId = LOAN_TYPE.LoanTypeId and LOAN_INFORMATION.LoanApplicationId = " + "'" + loanApplicationId + "'";
String loanType = Convert.ToString(dal.executeScalar(sql));
return loanType;
}
