public DataSet selectAllMemberTypes()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MemberTypeNo, Description FROM MEMBER_TYPE WHERE isArchived=0 and hasLoan=1";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet SelectAllCapitalContribution()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MinCapitalContributionId AS 'Minimum Capital Contribution ID', l.MemberTypeNo AS 'Member Type Id', Description AS 'Member Type', MinimumContribution AS 'Share Capital', Duration, l.Status, l.isArchived FROM MINIMUM_CAPITAL_CONTRIBUTION l INNER JOIN MEMBER_TYPE m ON l.MemberTypeNo = m.MemberTypeNo";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectAccountTypes()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT AccountTypeId, SavingsTypeName FROM SAVINGS_ACCOUNT_TYPE WHERE AccountTypeId NOT IN(SELECT AccountTypeId FROM MAXIMUM_WITHDRAWAL) AND isMaximumWithdrawal = 1 AND isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectMembers()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select m.AccountNo as 'Account No.', concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) as 'Name', SUM(h.ShareCapital) AS 'Balance' from Member m inner join MEMBER_TYPE t on m.MemberTypeNo = t.MemberTypeNo full join MEMBER_SHARECAPITAL h on m.AccountNo = h.accountNo where m.Status = 1 and t.hasShareCapital = 1 group by m.AccountNo, m.FirstName, m.LastName, m.MiddleName";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectMaximumWithdrawalAll()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT m.AccountTypeId AS 'Account Type Id', SavingsTypeName AS 'Account Type', MaximumWithdrawal AS 'Maximum Withdrawal', m.Status, isArchived, m.DateCreated AS 'Date Created', m.DateModified AS 'Last Modified' FROM MAXIMUM_WITHDRAWAL m INNER JOIN SAVINGS_ACCOUNT_TYPE s ON m.AccountTypeId = s.AccountTypeId";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectMember()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', Description AS 'Member Type' FROM MEMBER m INNER JOIN MEMBER_TYPE mt ON m.MemberTypeNo = mt.MemberTypeNo WHERE m.Status=1";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public void setData()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS 'Name', CONCAT(AddressNo, ' ', AddressStreet, ', ', AddressBarangay, ', ', AddressCity, ', ', AddressProvince, ', ', AddressZipCode) AS 'Address', BirthDate, TelephoneNo, Gender FROM MEMBER WHERE AccountNo = @AccountNo";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@AccountNo", this.AccountNo);
DataSet ds = dal.executeDataSet(sql, parameters);
}
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 DataSet searchMemberByName(String Name)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "Select m.AccountNo as 'Account No.', concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) as 'Name', SUM(h.ShareCapital) AS 'Balance' from Member m inner join MEMBER_TYPE t on m.MemberTypeNo = t.MemberTypeNo full join MEMBER_SHARECAPITAL h on m.AccountNo = h.accountNo where m.Status = 1 and hasShareCapital = 1 and concat(m.FirstName,' ',m.MiddleName,' ',m.LastName) like(@MemberName) group by m.AccountNo, m.FirstName, m.LastName, m.MiddleName";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
Name = "%" + Name + "%";
parameters.Add("@MemberName", Name);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet searchMemberName(String MemberName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', Description AS 'Member Type' FROM MEMBER m INNER JOIN MEMBER_TYPE mt ON m.MemberTypeNo = mt.MemberTypeNo WHERE m.Status=1 and LastName LIKE(@searchName) OR FirstName LIKE(@searchName) OR MiddleName LIKE(@searchName)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
MemberName = "%" + MemberName + "%";
parameters.Add("@searchName", MemberName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet searchTimeDepositCertificateNo(String searchName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT m.AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', CertificateNo AS 'Certificate No', DepositAmount AS 'Current Balance', isHold FROM MEMBER m INNER JOIN TIME_DEPOSIT td ON m.AccountNo = td.AccountNo WHERE td.Status = 1 AND CertificateNo LIKE(@searchName)";
searchName = "%" + searchName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@searchName", searchName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet searchMemberByName(String Name)
{
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) and concat(Member.FirstName,' ',Member.MiddleName,' ',Member.LastName) like(@MemberName)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
Name = "%" + Name + "%";
parameters.Add("@MemberName", Name);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet searchMember(String searchName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT DISTINCT sa.SavingsAccountNo AS 'Account Holder', SavingsTypeName AS 'Savings Type', sa.AccountTypeId, Balance FROM SAVINGS_ACCOUNT sa INNER JOIN MEMBER_SAVINGS_ACCOUNT msa ON sa.SavingsAccountNo = msa.SavingsAccountNo INNER JOIN SAVINGS_ACCOUNT_TYPE sat ON sa.AccountTypeId = sat.AccountTypeId WHERE sa.SavingsAccountNo LIKE(@searchName)";
searchName = "%" + searchName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@searchName", searchName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectFees()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT FeeId AS 'Fee No', Description, Amount, Status, isArchived FROM fee WHERE isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectMemberTypes()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MemberTypeNo AS 'Type No', Description AS 'Type Name' FROM Member_Type WHERE isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet searchFees(String searchName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT FeeId AS 'Fee No', Description, Amount, Status,isArchived FROM fee WHERE isArchived = 0 AND Description LIKE(@searchName)";
searchName = "%" + searchName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@searchName", searchName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectActiveMemberTypes(int FeeId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MEMBER_TYPE_FEE.MemberTypeNo, Description FROM MEMBER_TYPE inner JOIN MEMBER_TYPE_FEE on MEMBER_TYPE.MemberTypeNo = MEMBER_TYPE_Fee.MemberTypeNo WHERE FeeId= @feeId";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@feeId", FeeId);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectRequirements(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MEMBER_TYPE_REQUIREMENT.RequirementNo, Description FROM MEMBERSHIP_REQUIREMENT INNER JOIN MEMBER_TYPE_REQUIREMENT on MEMBERSHIP_REQUIREMENT.RequirementNo = MEMBER_TYPE_REQUIREMENT.RequirementNo WHERE MemberTypeNo = (SELECT MemberTypeNo FROM MEMBER WHERE AccountNo = @accountNo) AND isArchived = 0 AND Status = 1 AND dateCreated < (SELECT dateCreated FROM MEMBER WHERE AccountNo = @accountNo)";
Dictionary<String, Object> parameters = new Dictionary<String, Object>();
parameters.Add("@accountNo", accountNo);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet SelectMemberType()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT MEMBER_TYPE.MemberTypeNo, Description from MEMBER_TYPE LEFT JOIN MINIMUM_CAPITAL_CONTRIBUTION ON MEMBER_TYPE.MemberTypeNo= MINIMUM_CAPITAL_CONTRIBUTION.MemberTypeNo where MEMBER_TYPE.MemberTypeNo NOT IN(SELECT MemberTypeNo from MINIMUM_CAPITAL_CONTRIBUTION) AND MEMBER_TYPE.isArchived = 0 and MEMBER_TYPE.hasLoan =1";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectAccountHolder(String accountNo)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT m.AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name' FROM MEMBER_SAVINGS_ACCOUNT msa INNER JOIN MEMBER m ON msa.MemberAccountNo = m.AccountNo WHERE msa.SavingsAccountNo LIKE(@AccountNo)";
accountNo = "%" + accountNo + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@accountNo", accountNo);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectMember()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT DISTINCT sa.SavingsAccountNo AS 'Account No', SavingsTypeName AS 'Savings Type', sa.AccountTypeId, Balance FROM SAVINGS_ACCOUNT sa INNER JOIN MEMBER_SAVINGS_ACCOUNT msa ON sa.SavingsAccountNo = msa.SavingsAccountNo INNER JOIN SAVINGS_ACCOUNT_TYPE sat ON sa.AccountTypeId = sat.AccountTypeId";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectLoanTypes()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT LoanTypeId AS 'Type No', LoanTypeName AS 'Type Name' FROM LOAN_TYPE WHERE isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
//retrieve
public DataSet searchPenalties(String penaltyName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT PenaltyId AS 'Penalty Id', PenaltyName AS 'Penalty Name', GracePeriod as 'Grace Period', concat(Amount,' ',AmountStatus) as 'Penalty', Deduction, concat(DurationValue,' ',DurationStatus) as 'Duration', Status, isArchived FROM PENALTY WHERE isArchived = 0 AND PenaltyName LIKE(@penaltyName)";
penaltyName = "%" + penaltyName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@penaltyName", penaltyName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet searchloanCharge(String chargeName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT ChargeId AS 'Charge Id', ChargeName AS 'Charge Name', concat(Amount,' ',AmountStatus) as 'Charge', Status, isArchived FROM CHARGES WHERE isArchived = 0 AND ChargeName LIKE(@chargeName)";
chargeName = "%" + chargeName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@chargeName", chargeName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectloanCharge()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT ChargeId AS 'Charge Id', ChargeName AS 'Charge Name', concat(Amount,' ',AmountStatus) as 'Charge', Status, isArchived FROM CHARGES WHERE isArchived=0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet searchInterestRatesAll(String searchName)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT i.InterestId AS 'Interest Id', i.AccountTypeId AS 'Account Type Id', SavingsTypeName AS 'Account Type', CONCAT(InterestRate, '% ', Per) AS 'Interest Rate', CONCAT(MinimumRange, '-', MaximumRange) AS 'Balance Range', i.Status, isArchived, i.DateCreated AS 'Date Created', i.DateModified AS 'Last Modified' FROM INTEREST_RATE i INNER JOIN SAVINGS_ACCOUNT_TYPE s ON i.AccountTypeId = s.AccountTypeId WHERE SavingsTypeName LIKE(@searchName)";
searchName = "%" + searchName + "%";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@searchName", searchName);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectActiveLoanTypes(int penaltyId)
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT LOAN_TYPE_PENALTY.LoanTypeId, LoanTypeName FROM LOAN_TYPE inner JOIN LOAN_TYPE_PENALTY on LOAN_TYPE.LoanTypeId = LOAN_TYPE_PENALTY.LoanTypeId WHERE PenaltyId = @penaltyId";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@penaltyId", penaltyId);
DataSet ds = dal.executeDataSet(sql, parameters);
return ds;
}
public DataSet selectAccountTypes()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT AccountTypeId, SavingsTypeName FROM SAVINGS_ACCOUNT_TYPE WHERE isInterestRate = 1 AND isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
//retrieve
public DataSet selectPenalties()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT PenaltyId AS 'Penalty Id', PenaltyName AS 'Penalty Name', GracePeriod as 'Grace Period', concat(Amount,' ',AmountStatus) as 'Penalty', Deduction, concat(DurationValue,' ',DurationStatus) as 'Duration', Status as 'Active', isArchived FROM PENALTY WHERE isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
public DataSet selectInterestRates()
{
DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
String sql = "SELECT i.InterestId AS 'Interest Id', i.AccountTypeId AS 'Account Type Id', SavingsTypeName AS 'Account Type', CONCAT(InterestRate, '% ', Per) AS 'Interest Rate', CONCAT(MinimumRange, '-', MaximumRange) AS 'Balance Range', i.Status, isArchived, i.DateCreated AS 'Date Created', i.DateModified AS 'Last Modified' FROM INTEREST_RATE i INNER JOIN SAVINGS_ACCOUNT_TYPE s ON i.AccountTypeId = s.AccountTypeId WHERE isArchived = 0";
DataSet ds = dal.executeDataSet(sql);
return ds;
}
