public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000'), CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName), MEMBERTYPE.MemberTypeName, SAVINGSTYPE.savingsTypeName, SAVINGSTYPE.interestRate, SAVINGSTYPE.initialDeposit, SAVINGSTYPE.maintainingBalance, SAVINGSTYPE.balanceToEarn, case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end) end, CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)), CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)), DORMANCY.activationFee, FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000'), SAVINGSACCOUNT.dateOpened, SAVINGSACCOUNT.currentBalance FROM MEMBER, MEMBERTYPE, APPLICABLESAVINGS, SAVINGSTYPE, DORMANCY, SAVINGSACCOUNT WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBERTYPE.MemberTypeID = APPLICABLESAVINGS.MemberTypeID and APPLICABLESAVINGS.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = DORMANCY.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = SAVINGSACCOUNT.SavingsTypeID and SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMemberID.Text = "MEM - " + reader.GetString(0);
txtMemberName.Text = reader.GetString(1);
txtMemberType.Text = reader.GetString(2);
txtSavName.Text = reader.GetString(3);
txtInterest.Text = reader.GetDecimal(4).ToString();
txtInitial.Text = reader.GetDecimal(5).ToString();
txtMainBal.Text = reader.GetDecimal(6).ToString();
txtBalToEarn.Text = reader.GetDecimal(7).ToString();
txtMaxWithdraw.Text = reader.GetString(8);
txtDormancy.Text = reader.GetString(9);
txtDeductDetails.Text = reader.GetString(10);
txtActivationFee.Text = reader.GetDecimal(11).ToString();
txtSavAccount.Text = "SAV - " + reader.GetString(12);
txtDateStarted.Text = reader.GetDateTime(13).ToString();
txtCurrentBalance.Text = reader.GetDecimal(14).ToString();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], CASE SAVINGSTRANSACTION.transactionType when 1 then 'Deposit' when 2 then 'Withdraw' when 3 then 'Annual Increase' else 'Dormancy Deduction' end as [Transaction Type], SAVINGSTRANSACTION.transactionAmount as [Amount], SAVINGSTRANSACTION.transactionDate as [Date], SAVINGSTRANSACTION.transactionRepresentative as [Representative], SAVINGSTRANSACTION.currentBalance as [Balance] FROM SAVINGSACCOUNT, SAVINGSTRANSACTION WHERE SAVINGSACCOUNT.SavingsAccountID = SAVINGSTRANSACTION.SavingsAccountID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + "";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], MEMBERTYPE.MemberTypeName as [Member Type Name], TIMEDEPOSITRATES.TimeDepositRatesID as [ID], CONCAT(TIMEDEPOSITRATES.noOfDays, ' Days') as [No. Of Days], CONCAT(TIMEDEPOSITRATES.minAmount,' - ', TIMEDEPOSITRATES.maxAmount) as [Amount Range], CONCAT(TIMEDEPOSITRATES.interest, ' %') as [Interest], TIMEDEPOSITACCOUNT.TimeDepositAccountID as [Time Deposit Account ID], TIMEDEPOSITACCOUNT.currentBalance as [Amount] FROM TIMEDEPOSITRATES, TIMEDEPOSITACCOUNT, MEMBER, MEMBERTYPE WHERE TIMEDEPOSITRATES.TimeDepositRatesID = TIMEDEPOSITACCOUNT.TimeDepositRatesID and TIMEDEPOSITACCOUNT.MemberID = MEMBER.MemberID and MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBER.MemberID = " + SLS.Static.ID + "";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = SLS.Static.sqlParams;
DataSet ds = con.executeDataSet(sql,SLS.Static.parameters ,"Member");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Member";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, @DateNow ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@DateNow", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")));
DataSet ds = con.executeDataSet(sql, parameters, "Member");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Member";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT SAVINGSACCOUNT.SavingsAccountID, CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], SAVINGSACCOUNT.MemberID as [Member ID], CONCAT(MEMBER.fName,' ' , MEMBER.mName, ' ', MEMBER.lName) as [Member Name], SAVINGSACCOUNT.SavingsTypeID as [Savings Type ID], SAVINGSTYPE.savingsTypeName as [Savings Type Name], SAVINGSTYPE.interestRate as [Interest Rate], SAVINGSTYPE.initialDeposit as [Initial Deposit], SAVINGSTYPE.maintainingBalance as [Maintaining Balance], SAVINGSTYPE.balanceToEarn as [Balance To Earn], case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), (case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) end as [Maximum Withdrawal], CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)) as [Inactivity Period], CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) as [Deduction], DORMANCY.activationFee[Activation Fee], SAVINGSACCOUNT.dateOpened as [Date Started], SAVINGSACCOUNT.currentBalance as [Current Balance] FROM SAVINGSACCOUNT, MEMBER, SAVINGSTYPE, DORMANCY WHERE SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and DORMANCY.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and MEMBER.MemberID = " + SLS.Static.ID + " and SAVINGSACCOUNT.dateClosed IS NULL";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[2].Visible = false;
dataGridView1.Columns[3].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
public void loadDatabase()
{
if(SLS.Static.hasSearch == 0)
{
if(cobFilter.SelectedIndex == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilter.SelectedIndex == 1)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 1";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilter.SelectedIndex == 2)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 0";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}
else
{
}
}
private void cobNoOfDays_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT TimeDepositRatesID as [ID], CONCAT(noOfDays, ' Days') as [No. Of Days], CONCAT(minAmount,' - ', maxAmount) as [Amount Range], CONCAT(interest, ' %') as [Interest], [status] as [Status] FROM TIMEDEPOSITRATES WHERE CONCAT(noOfDays, ' Days') = '" + cobNoOfDays.SelectedItem + "'";
DataSet ds = con.executeDataSet(sql, "Time Deposit Rates");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Time Deposit Rates";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
catch
{
}
}
public void loadDatabase()
{
if (cobFilterCharge.SelectedIndex == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ChargeID as [ID], chargeName as [LoanCharge], [status] as [Status] FROM CHARGES";
DataSet ds = con.executeDataSet(sql, "Charge");
dataGridView3.DataSource = ds;
dataGridView3.DataMember = "Charge";
dataGridView3.Columns[0].Visible = false;
dataGridView3.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterCharge.SelectedIndex == 1)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ChargeID as [ID], chargeName as [LoanCharge], [status] as [Status] FROM CHARGES WHERE [status] = 1";
DataSet ds = con.executeDataSet(sql, "Charge");
dataGridView3.DataSource = ds;
dataGridView3.DataMember = "Charge";
dataGridView3.Columns[0].Visible = false;
dataGridView3.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterCharge.SelectedIndex == 2)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ChargeID as [ID], chargeName as [LoanCharge], [status] as [Status] FROM CHARGES WHERE [status] = 0";
DataSet ds = con.executeDataSet(sql, "Charge");
dataGridView3.DataSource = ds;
dataGridView3.DataMember = "Charge";
dataGridView3.Columns[0].Visible = false;
dataGridView3.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
if (cobFilterMode.SelectedIndex == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ModeID as [ID], modeName as [Mode], [status] as [Status], daysInterval FROM MODE";
DataSet ds = con.executeDataSet(sql, "Mode");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Mode";
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[3].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterMode.SelectedIndex == 1)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ModeID as [ID], modeName as [Mode], [status] as [Status], daysInterval FROM MODE WHERE [status] = 1";
DataSet ds = con.executeDataSet(sql, "Mode");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Mode";
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[3].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterMode.SelectedIndex == 2)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ModeID as [ID], modeName as [Mode], [status] as [Status], daysInterval MODE WHERE [status] = 0";
DataSet ds = con.executeDataSet(sql, "Mode");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Mode";
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[3].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
if (cobFilterTerm.SelectedIndex == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT TermID as [ID], noOfDays as [No. Of Days], [status] as [Status] FROM TERM";
DataSet ds = con.executeDataSet(sql, "Term");
dataGridView2.DataSource = ds;
dataGridView2.DataMember = "Term";
dataGridView2.Columns[0].Visible = false;
dataGridView2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterTerm.SelectedIndex == 1)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT TermID as [ID], noOfDays as [No. Of Days], [status] as [Status] FROM TERM WHERE [status] = 1";
DataSet ds = con.executeDataSet(sql, "Term");
dataGridView2.DataSource = ds;
dataGridView2.DataMember = "Term";
dataGridView2.Columns[0].Visible = false;
dataGridView2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilterTerm.SelectedIndex == 2)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT TermID as [ID], noOfDays as [No. Of Days], [status] as [Status] FROM TERM WHERE [status] = 0";
DataSet ds = con.executeDataSet(sql, "Term");
dataGridView2.DataSource = ds;
dataGridView2.DataMember = "Term";
dataGridView2.Columns[0].Visible = false;
dataGridView2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}