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; }