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 defaultAll()
{
txtFN.Clear();
txtMN.Clear();
txtLN.Clear();
txtPosition.Clear();
er1.Visible = false;
er2.Visible = false;
er3.Visible = false;
er4.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT fName, mName, lName, position FROM LOANGRANTOR where lgID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtFN.Text = Convert.ToString(reader[0]);
txtMN.Text = Convert.ToString(reader[1]);
txtLN.Text = Convert.ToString(reader[2]);
txtPosition.Text = Convert.ToString(reader[3]);
}
}
}
public void defaultAll()
{
txtMemName.Text = "";
txtMinAge.Text = "";
txtMaxAge.Text = "";
txtFee.Text = "";
ckbSavings.Checked = false;
ckbTime.Checked = false;
ckbLoan.Checked = false;
txtShare.Text = "";
er1.Visible = true;
er2.Visible = true;
er3.Visible = true;
er4.Visible = true;
er5.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MemberTypeID, MemberTypeName, MinAgeRequired, MaxAgeRequired, Fee, SavingsApplicable, TimeApplicable, LoanApplicable, ShareRequired FROM MEMBERTYPE where MemberTypeID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberTypeID = Convert.ToInt32(reader[0]);
txtMemName.Text = Convert.ToString(reader[1]);
txtMinAge.Text = Convert.ToString(reader[2]);
txtMaxAge.Text = Convert.ToString(reader[3]);
txtFee.Text = Convert.ToString(reader[4]);
if (Convert.ToString(reader[5]) == "True")
{
ckbSavings.Checked = true;
}
else
{
ckbSavings.Checked = false;
}
if (Convert.ToString(reader[6]) == "True")
{
ckbTime.Checked = true;
}
else
{
ckbTime.Checked = false;
}
if (Convert.ToString(reader[7]) == "True")
{
ckbLoan.Checked = true;
txtShare.Enabled = true;
txtShare.Text = Convert.ToString(reader[8]);
}
else
{
ckbLoan.Checked = false;
txtShare.Enabled = false;
txtShare.Clear();
}
}
}
}
public void defaultAll()
{
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 + "";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMemberID.Text = "MEM - " + Convert.ToString(reader.GetInt32(0));
txtMemberName.Text = reader.GetString(1);
txtMemberType.Text = reader.GetString(2);
}
}
private void button2_Click(object sender, EventArgs e)
{
var date = DateTime.Now;
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO LOAN (MemberID, LoanTypeID, applyAmount, TermID, ModeID, dateApplied) VALUES (@MemID, @LTypeID, @ApplyAmt, @TermID, @ModeID, @dateApplied)";
Dictionary < String, Object > parameters = new Dictionary<string, object>();
parameters.Add("@MemId", Convert.ToInt32(textBox1.Text));
parameters.Add("@LTypeID", Convert.ToInt32(textBox6.Text));
parameters.Add("@ApplyAmt", textBox4.Text);
parameters.Add("@TermID", Convert.ToInt32(textBox7.Text));
parameters.Add("@ModeID", Convert.ToInt32(textBox8.Text));
parameters.Add("@dateApplied", date);
SqlDataReader reader = con.executeReader(sql, parameters);
MessageBox.Show("Loan Application Saved", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public Int32 checkValues()
{
//Required Fields Validation
Int32 isValid = 0;
SLS.Validate.Alpha ctrlString = new SLS.Validate.Alpha();
if (ctrlString.checkString(txtModeName.Text) == 1)
{
isValid = 1;
er1.Visible = true;
}
try
{
Convert.ToInt32(txtDaysInterval.Text);
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT daysInterval FROM MODE";
SqlDataReader reader = con.executeReader(sql);
while(reader.Read())
{
if (SLS.Static.ID == 0)
{
if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader.GetInt32(0)))
{
er2.Visible = true;
isValid = 1;
}
}
else
{
if(daysInterval != Convert.ToInt32(txtDaysInterval.Text))
{
if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader[0]))
{
er2.Visible = true;
isValid = 1;
}
}
}
}
}
catch (Exception)
{
isValid = 1;
er2.Visible = true;
}
return isValid;
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age] , MEMBERTYPE.MemberTypeID, MEMBERTYPE.MemberTypeName FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberID = Convert.ToInt32(reader[0]);
txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]);
txtMemberName.Text = Convert.ToString(reader[1]);
txtMemberType.Text = Convert.ToString(reader[4]);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT SAVINGSTYPE.SavingsTypeID, SAVINGSTYPE.savingsTypeName FROM SAVINGSTYPE, APPLICABLESAVINGS WHERE SAVINGSTYPE.SavingsTypeID = APPLICABLESAVINGS.SavingsTypeID AND APPLICABLESAVINGS.MemberTypeID = " + Convert.ToString(reader[3]) + " and SAVINGSTYPE.hasDormancy = 'true' and SAVINGSTYPE.[status] = 1";
reader = con.executeReader(sql);
cobSavingsType.Items.Clear();
int i = 0;
while (reader.Read())
{
string str = reader[1].ToString();
cobSavingsType.Items.Insert(i, "" + str);
i += 1;
}
cobSavingsType.Text = "";
cobSavingsType.Enabled = true;
er2.Visible = true;
SavingsID = 0;
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MAX(SavingsAccountID) FROM SAVINGSACCOUNT";
reader = con.executeReader(sql);
try
{
if (reader.HasRows)
{
reader.Read();
txtSavAccount.Text = "SAV - " + (reader.GetInt32(0) + 1).ToString("00000000");
}
}
catch(Exception)
{
txtSavAccount.Text = "SAV - 00000001";
}
txtDate.Text = DateTime.Now.ToLongDateString();
}
public void defaultAll()
{
txtNoDays.Clear();
er1.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT noOfDays FROM TERM where TermID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtNoDays.Text = Convert.ToString(reader[0]);
NoDays = Convert.ToInt32(reader[0]);
}
}
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberID = Convert.ToInt32(reader[0]);
txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]);
txtMemberName.Text = Convert.ToString(reader[1]);
txtMemberType.Text = Convert.ToString(reader[3]);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(TIMEDEPOSITRATES.noOfDays) FROM MEMBERTYPE, TIMEDEPOSITRATES WHERE MEMBERTYPE.TimeApplicable = 1 AND TIMEDEPOSITRATES.[status] = 1";
reader = con.executeReader(sql);
cobNoOfDays.Items.Clear();
int i = 0;
while(reader.Read())
{
string str = reader[0].ToString();
cobNoOfDays.Items.Insert(i, "" + str + " Days");
i += 1;
}
cobNoOfDays.Text = "";
cobNoOfDays.Enabled = true;
er1.Visible = true;
try
{
if (reader.HasRows)
{
reader.Read();
txtTimeAccount.Text = "TIME - " + (reader.GetInt32(0) + 1).ToString("00000000");
}
}
catch (Exception)
{
txtTimeAccount.Text = "TIME - 00000001";
}
txtNow.Text = Convert.ToString(DateTime.Now.ToLongDateString());
}
private int checkValues()
{
Int32 isValid = 0;
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT minAmount, maxAmount FROM TIMEDEPOSITRATES WHERE TimeDepositRatesID = @RateID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@RateID", Convert.ToInt32(RateID));
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
if (Convert.ToDecimal(txtAmount.Text) <= Convert.ToDecimal(reader[0]) || Convert.ToDecimal(txtAmount.Text) >= Convert.ToDecimal(reader[1]))
{
er1.Visible = true;
isValid = 1;
}
}
return isValid;
}
public void defaultAll()
{
txtModeName.Clear();
txtDaysInterval.Clear();
er1.Visible = false;
er2.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT modeName, daysInterval FROM MODE where ModeID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtModeName.Text = Convert.ToString(reader[0]);
txtDaysInterval.Text = Convert.ToString(reader[1]);
daysInterval = Convert.ToInt32(reader[1]);
}
}
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MAX(MemberID) FROM MEMBER";
SqlDataReader reader = con.executeReader(sql);
try
{
if (reader.HasRows)
{
reader.Read();
txtMemberId.Text = "MEM - " + (reader.GetInt32(0) + 1).ToString("00000000");
}
}
catch (Exception)
{
txtMemberId.Text = "MEM - 00000001";
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MemberTypeName FROM MEMBERTYPE WHERE DATEDIFF(YEAR, @birthDate, @DateNow )BETWEEN MinAgeRequired AND MaxAgeRequired";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@birthDate", Convert.ToDateTime(dtDateBirth.Value));
parameters.Add("@DateNow", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")));
reader = con.executeReader(sql, parameters);
Int32 x = 0;
cmbMemberType.Items.Clear();
while (reader.Read())
{
cmbMemberType.Items.Insert(x, "" + reader.GetString(0));
x++;
}
//Default Table Child
try
{
tableChild.Clear();
}
catch
{
}
finally
{
try
{
tableChild.Columns.Add(new DataColumn("Last Name", typeof(string)));
tableChild.Columns.Add(new DataColumn("First Name", typeof(string)));
tableChild.Columns.Add(new DataColumn("Middle Name", typeof(string)));
tableChild.Columns.Add(new DataColumn("Gender", typeof(string)));
tableChild.Columns.Add(new DataColumn("Birthdate", typeof(string)));
dataGridView1.DataSource = tableChild;
}
catch
{
}
}
//Educational Attainment
cmbEduc.Items.Clear();
for(int i=0;i<educString.Length;i++)
{
cmbEduc.Items.Insert(i, "" + educString[i]);
}
//Gender
cmbGender.Items.Clear();
for (int i = 0; i < genderString.Length; i++)
{
cmbGender.Items.Insert(i, "" + genderString[i]);
}
//Child Gender
cmbChildGender.Items.Clear();
for (int i = 0; i < childGenderString.Length; i++)
{
cmbChildGender.Items.Insert(i, "" + childGenderString[i]);
}
//Civil Status
cmbCivil.Items.Clear();
for (int i = 0; i < civilStatusString.Length; i++)
{
cmbCivil.Items.Insert(i, "" + civilStatusString[i]);
}
//PersonalInformation
txtLN.Clear();
txtFN.Clear();
txtMN.Clear();
dtDateBirth.Value = Convert.ToDateTime(DateTime.Now.ToLongDateString());
dtMembership.Value = Convert.ToDateTime(DateTime.Now.ToLongDateString());
dtSeminar.Value = Convert.ToDateTime(DateTime.Now.ToLongDateString());
txtGSISSSS.Clear();
txtInitialCapital.Clear();
txtMultiplier.Clear();
txtAge.Clear();
txtFee.Clear();
//ContactInformation
txtCPLN.Clear();
txtCPFN.Clear();
txtCPMN.Clear();
txtContact.Clear();
txtStreet.Clear();
txtMunicipality.Clear();
txtCity.Clear();
txtZip.Clear();
txtResidence.Clear();
txtTelNo.Clear();
checkBox2.Checked=false;
txtStreet2.Clear();
txtMunicipality2.Clear();
txtCity2.Clear();
txtZip2.Clear();
txtResidence2.Clear();
txtTelNo2.Clear();
//EmploymentInformation
rbGovernment.Checked = true;
txtEmployerName.Enabled = true;
txtEmployerAddress.Enabled = true;
txtEmployerTelNo.Enabled = true;
txtDateStarted.Enabled = true;
txtMonthlySalary.Enabled = true;
txtDepartment.Enabled = true;
rbGovernment.Enabled = true;
rbPrivate.Enabled = true;
rbAbroad.Enabled = true;
txtTypeOfBusiness.Enabled = false;
txtBusinessAdd.Enabled = false;
txtStartingCapital.Enabled = false;
txtPresentCapital.Enabled = false;
txtMonthlyNetIncome.Enabled = false;
txtTypeOfBusiness.Clear();
txtBusinessAdd.Clear();
txtStartingCapital.Clear();
txtPresentCapital.Clear();
txtMonthlyNetIncome.Clear();
txtEmployerName.Clear();
txtEmployerAddress.Clear();
txtEmployerTelNo.Clear();
txtDateStarted.Clear();
txtMonthlySalary.Clear();
txtDepartment.Clear();
rbFullTime.Checked = true;
//FamilyInformation
//Mother
checkBox1.Checked = false;
txtMNLN.Enabled = false;
txtMNMN.Enabled = false;
txtMNFN.Enabled = false;
dtMN.Enabled = false;
txtMNLN.Clear();
txtMNMN.Clear();
txtMNFN.Clear();
//Spouse
checkBox3.Checked = false;
txtSNLN.Enabled = false;
txtSNMN.Enabled = false;
txtSNFN.Enabled = false;
dtSN.Enabled = false;
txtSNLN.Clear();
txtSNMN.Clear();
txtSNFN.Clear();
//Children
checkBox4.Checked = false;
txtCNLN.Enabled = false;
txtCNMN.Enabled = false;
txtCNFN.Enabled = false;
dtChild.Enabled = false;
cmbChildGender.Enabled = false;
txtCNLN.Clear();
txtCNMN.Clear();
txtCNFN.Clear();
btnAddChild.Enabled=false;
btnDeleteChild.Enabled = false;
btnClearChild.Enabled = false;
//Father
checkBox5.Checked = false;
txtFNLN.Enabled = false;
txtFNMN.Enabled = false;
txtFNFN.Enabled = false;
dtFN.Enabled = false;
txtFNLN.Clear();
txtFNMN.Clear();
txtFNFN.Clear();
//Asterisk in Family
label101.Visible = false; label102.Visible = false;
label103.Visible = false; label104.Visible = false;
label105.Visible = false; label106.Visible = false;
label107.Visible = false; label108.Visible = false;
label109.Visible = false; label110.Visible = false;
label111.Visible = false; label112.Visible = false;
label113.Visible = false;
if (SLS.Static.ID != 0)
{
//Personal Information UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MemberID, fName, mName, lName, gender, birthDate, civilStatus, gsisNo, educAttainment, paidMembershipFee, applyDate, seminarDate, initialCapital, multiplier, MEMBERTYPE.MemberTypeName FROM MEMBER, MEMBERTYPE WHERE MemberID = " + SLS.Static.ID + " AND MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMemberId.Text = Convert.ToString(reader[0]);
txtFN.Text = Convert.ToString(reader[1]);
txtMN.Text = Convert.ToString(reader[2]);
txtLN.Text = Convert.ToString(reader[3]);
if (Convert.ToBoolean(reader[4]) == true)
{
cmbGender.SelectedIndex = 0;
}
else
{
cmbGender.SelectedIndex = 1;
}
dtDateBirth.Text = (Convert.ToDateTime(reader[5])).ToString("MM/dd/yyyy");
cmbCivil.SelectedIndex = Convert.ToInt32(reader[6]);
txtGSISSSS.Text = Convert.ToString(reader[7]);
cmbEduc.SelectedIndex = Convert.ToInt32(reader[8]);
txtFee.Text = Convert.ToString(reader[9]);
dtMembership.Text = (Convert.ToDateTime(reader[10])).ToString("MM/dd/yyyy");
dtSeminar.Text = (Convert.ToDateTime(reader[11])).ToString("MM/dd/yyyy");
txtInitialCapital.Text = Convert.ToString(reader[12]);
txtMultiplier.Text = Convert.ToString(reader[13]);
int index = cmbMemberType.Items.IndexOf(reader[14]);
cmbMemberType.SelectedIndex = index;
}
//Contact Person UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT contactLN, contactFN, contactMN, contactNo FROM CONTACTPERSON WHERE MemberID = " + SLS.Static.ID + "";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtCPLN.Text = Convert.ToString(reader[0]);
txtCPFN.Text = Convert.ToString(reader[1]);
txtCPMN.Text = Convert.ToString(reader[2]);
txtContact.Text = Convert.ToString(reader[3]);
}
//Contact Information UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT street, municipality, cityProvince, zipCode, residenceSince, telephoneNo FROM CONTACTINFORMATION WHERE MemberID = " + SLS.Static.ID + "";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtStreet.Text = Convert.ToString(reader[0]);
txtMunicipality.Text = Convert.ToString(reader[1]);
txtCity.Text = Convert.ToString(reader[2]);
txtZip.Text = Convert.ToString(reader[3]);
txtResidence.Text = Convert.ToString(reader[4]);
txtTelNo.Text = Convert.ToString(reader[5]);
}
//Employment Information UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT EmploymentInformationID, employmentNo FROM EMPLOYMENTINFORMATION WHERE MemberID = " + SLS.Static.ID + "";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
employID = Convert.ToInt32(reader[0]);
occup = Convert.ToInt32(reader[1]);
}
if (occup == 0 || occup == 1 || occup == 2)
{
if(occup==0)
{
rbFullTime.Checked = true;
}
else
if(occup==1)
{
rbPartTime.Checked = true;
}
else
{
rbContractual.Checked = true;
}
//Employed UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT employerName, employerAddress, employerType, employerTelNo, dateStarted, monthlySalary, department FROM EMPLOYED WHERE EmploymentInformationID = "+ employID.ToString() +"";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtEmployerName.Text = Convert.ToString(reader[0]);
txtEmployerAddress.Text = Convert.ToString(reader[1]);
emptype = Convert.ToInt32(reader[2]);
txtEmployerTelNo.Text = Convert.ToString(reader[3]);
txtDateStarted.Text = (Convert.ToDateTime(reader[4])).ToString("yyyy/MM/dd");
txtMonthlySalary.Text = Convert.ToString(reader[5]);
txtDepartment.Text = Convert.ToString(reader[6]);
if (emptype == 0)
{
rbGovernment.Checked = true;
}
else if (emptype == 1)
{
rbPrivate.Checked = true;
}
else
{
rbAbroad.Checked = true;
}
}
}
else if (occup == 3)
{
//Self-Employed UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT typeOfBusiness, startingCapital, monthlyNetIncome, businessAddress, presentCapital FROM SELFEMPLOYED WHERE EmploymentInformationID = "+ employID.ToString() +"";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtTypeOfBusiness.Text = Convert.ToString(reader[0]);
txtStartingCapital.Text = Convert.ToString(reader[1]);
txtMonthlyNetIncome.Text = Convert.ToString(reader[2]);
txtBusinessAdd.Text = Convert.ToString(reader[3]);
txtPresentCapital.Text = Convert.ToString(reader[4]);
}
}
else
{
rbRetired.Checked = true;
}
//Family Information UPDATE
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT relationship, fName, mName, lName, birthDate, gender FROM FAMILYBACKGROUND WHERE MemberID = " + SLS.Static.ID + "";
reader = con.executeReader(sql);
while (reader.Read())
{
if (Convert.ToString(reader[0]) == "Father")
{
checkBox5.Checked = true;
txtFNFN.Text = Convert.ToString(reader[1]);
txtFNMN.Text = Convert.ToString(reader[2]);
txtFNLN.Text = Convert.ToString(reader[3]);
dtFN.Text = (Convert.ToDateTime(reader[4])).ToString("MM/dd/yyyy");
}
if (Convert.ToString(reader[0]) == "Mother")
{
checkBox1.Checked = true;
txtMNFN.Text = Convert.ToString(reader[1]);
txtMNMN.Text = Convert.ToString(reader[2]);
txtMNLN.Text = Convert.ToString(reader[3]);
dtMN.Text = (Convert.ToDateTime(reader[4])).ToString("MM/dd/yyyy");
}
if (Convert.ToString(reader[0]) == "Spouse")
{
checkBox3.Checked = true;
txtSNFN.Text = Convert.ToString(reader[1]);
txtSNMN.Text = Convert.ToString(reader[2]);
txtSNLN.Text = Convert.ToString(reader[3]);
dtSN.Text = (Convert.ToDateTime(reader[4])).ToString("MM/dd/yyyy");
}
if (Convert.ToString(reader[0]) == "Son" || Convert.ToString(reader[0]) == "Daughter")
{
checkBox4.Checked = true;
txtCNFN.Text = Convert.ToString(reader[1]);
txtCNMN.Text = Convert.ToString(reader[2]);
txtCNLN.Text = Convert.ToString(reader[3]);
dtChild.Text = (Convert.ToDateTime(reader[4])).ToString("MM/dd/yyyy");
cmbChildGender.SelectedIndex = Convert.ToInt32(reader[5]);
addChildren();
ClearChild();
}
}
}
}
public void fillTable()
{
if (SLS.Static.ID == 0)
{
try
{
table.Columns.Add("ChargeID");
table.Columns.Add("Charges");
foreach (object modeChecked in cListMode.CheckedItems)
{
foreach (object termChecked in cListTerm.CheckedItems)
{
table.Columns.Add(new DataColumn("" + modeChecked.ToString() + " - " + termChecked.ToString(), typeof(Decimal)));
}
}
}
catch
{
}
try
{
DataRow Row = table.NewRow();
Row["ChargeID"] = 1;
Row["Charges"] = "Interest On Loan";
table.Rows.Add(Row);
foreach (object chargeChecked in cListCharges.CheckedItems)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ChargeID, chargeName FROM CHARGES WHERE chargeName = @chargeName";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@chargeName", chargeChecked.ToString());
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
Row = table.NewRow();
Row["ChargeID"] = reader[0].ToString();
Row["Charges"] = reader[1].ToString();
table.Rows.Add(Row);
}
}
dataGridView1.DataSource = table;
dataGridView1.Columns[0].Visible = false;
}
catch
{
}
}
else
{
}
}
private void btnStatus_Click(object sender, EventArgs e)
{
if (SLS.Static.ID != 0)
{
if (btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE DORMANCY SET [status] = 'false' WHERE DormancyID = @DormancyID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a dormancy is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE SAVINGSTYPE SET hasDormancy = 'false' WHERE SavingsTypeID = (SELECT SavingsTypeID FROM DORMANCY WHERE DormancyID = @DormancyID)";
parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
loadDatabase();
btnStatus.Text = "DELETE";
}
}
else
{
MessageBox.Show("Updating a dormancy is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT SAVINGSTYPE.hasDormancy FROM SAVINGSTYPE, DORMANCY WHERE SAVINGSTYPE.SavingsTypeID = DORMANCY.SavingsTypeID and DORMANCY.DormancyID = "+ SLS.Static.ID +" ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
if(Convert.ToString(reader.GetValue(0)) == "False")
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE DORMANCY SET [status] = 'true' WHERE DormancyID = @DormancyID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a dormancy is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE SAVINGSTYPE SET hasDormancy = 'true' WHERE SavingsTypeID = (SELECT SavingsTypeID FROM DORMANCY WHERE DormancyID = @DormancyID)";
parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
loadDatabase();
btnStatus.Text = "DELETE";
}
}
else
{
MessageBox.Show("Updating a dormancy is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("There is an active dormancy for the savings type. \n Try to disable the currently active dormancy to activate another.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE DORMANCY SET [status] = 'true' WHERE DormancyID = @DormancyID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a dormancy is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE SAVINGSTYPE SET hasDormancy = 'true' WHERE SavingsTypeID = (SELECT SavingsTypeID FROM DORMANCY WHERE DormancyID = @DormancyID)";
parameters = new Dictionary<string, object>();
parameters.Add("@DormancyID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if(result == 1)
{
loadDatabase();
btnStatus.Text = "DELETE";
}
}
else
{
MessageBox.Show("Updating a dormancy is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
else
{
MessageBox.Show("Please choose a record to be updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void defaultAll()
{
txtFN.Clear();
txtMN.Clear();
txtLN.Clear();
txtUser.Clear();
txtPass.Clear();
txtCPass.Clear();
txtAnswer.Clear();
cmbQuestion.Items.Clear();
for (int i = 0; i < questionString.Length; i++)
{
cmbQuestion.Items.Insert(i, "" + questionString[i]);
}
cmbAccount.Items.Clear();
for (int i = 0; i < accountString.Length; i++)
{
cmbAccount.Items.Insert(i, "" + accountString[i]);
}
er1.Visible = false;
er2.Visible = false;
er3.Visible = false;
er4.Visible = false;
er5.Visible = false;
er6.Visible = false;
er7.Visible = false;
er8.Visible = false;
er9.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT fName, mName, lName, username, [password], accountID, securityQuestion, securityAnswer FROM [USER] where UserID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtFN.Text = Convert.ToString(reader[0]);
txtMN.Text = Convert.ToString(reader[1]);
txtLN.Text = Convert.ToString(reader[2]);
txtUser.Text = Convert.ToString(reader[3]);
txtPass.Text = Convert.ToString(reader[4]);
cmbAccount.SelectedIndex = Convert.ToInt32(reader[5]);
cmbQuestion.SelectedIndex = Convert.ToInt32(cmbQuestion.Items.IndexOf(reader[6].ToString()));
//cmbQuestion.SelectedIndex = Convert.ToInt32(reader[6]);
txtAnswer.Text = Convert.ToString(reader[7]);
}
}
}
private void cobSavingsType_SelectedIndexChanged(object sender, EventArgs e)
{
SavingsName = cobSavingsType.Text.ToString();
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT SAVINGSTYPE.SavingsTypeID, SAVINGSTYPE.interestRate, SAVINGSTYPE.initialDeposit, SAVINGSTYPE.maintainingBalance, balanceToEarn, 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 as [Activation Fee], 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 FROM SAVINGSTYPE, DORMANCY WHERE SAVINGSTYPE.SavingsTypeID = DORMANCY.SavingsTypeID and SAVINGSTYPE.savingsTypeName = @savingsTypeName";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@savingsTypeName", SavingsName);
SqlDataReader reader = con.executeReader(sql, parameters);
while (reader.Read())
{
SavingsID = reader.GetInt32(0);
txtInterest.Text = reader.GetDecimal(1).ToString();
txtInitial.Text = reader.GetDecimal(2).ToString();
txtMainBal.Text = reader.GetDecimal(3).ToString();
txtBalToEarn.Text = reader.GetDecimal(4).ToString();
txtDormancy.Text = reader.GetValue(5).ToString();
txtDeductDetails.Text = reader.GetValue(6).ToString();
txtActivationFee.Text = reader.GetValue(7).ToString();
txtMaxWithdraw.Text = reader.GetValue(8).ToString();
}
}
private void cmbMemberType_SelectedIndexChanged(object sender, EventArgs e)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT Fee, ShareRequired FROM MEMBERTYPE WHERE MemberTypeName = @MemberTypeName";
Dictionary<String, Object> parameters = new Dictionary<string,object>();
parameters.Add("@MemberTypeName",cmbMemberType.SelectedItem.ToString());
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtFee.Text = Convert.ToString(reader[0]);
txtFee.Enabled = false;
if(Convert.ToDecimal(reader[1]) == 0)
{
txtInitialCapital.Text = Convert.ToString(0);
txtInitialCapital.Visible = false;
txtMultiplier.Visible = false;
label17.Visible = false;
label74.Visible = false;
}
else
{
txtInitialCapital.Text = Convert.ToString(reader[1]);
txtInitialCapital.Visible = true;
txtMultiplier.Visible =true;
label17.Visible = true;
label74.Visible = true;
}
}
}
public Int32 checkValues()
{
Int32 isValid = 0, check = 0;
try
{
Convert.ToDecimal(txtMin.Text);
if(Convert.ToDecimal(txtMin.Text) > 99)
{
er1.Visible = true;
isValid = 1;
check = 1;
}
}
catch
{
er1.Visible = true;
isValid = 1;
check = 1;
}
try
{
Convert.ToDecimal(txtMax.Text);
if (Convert.ToDecimal(txtMax.Text) > 100)
{
er2.Visible = true;
isValid = 1;
check = 1;
}
}
catch
{
er2.Visible = true;
isValid = 1;
check = 1;
}
if (check == 0 && Convert.ToDecimal(txtMin.Text) > Convert.ToDecimal(txtMax.Text))
{
er1.Visible = true;
er2.Visible = true;
isValid = 1;
check = 1;
}
if (check == 0 && SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT minElapsed, maxElapsed FROM TIMEDEPOSITPENALTY WHERE [status] = 1";
SqlDataReader reader = con.executeReader(sql);
if(reader.HasRows)
{
while (reader.Read() && isValid == 0)
{
if (min != Convert.ToDecimal(reader[0]) && max != Convert.ToDecimal(reader[1]))
{
for (Decimal i = Convert.ToDecimal(txtMin.Text); (i <= Convert.ToDecimal(txtMax.Text)) && isValid == 0; i = i + Convert.ToDecimal(0.01))
{
if (i >= Convert.ToDecimal(reader[0]) && i <= Convert.ToDecimal(reader[1]))
{
er1.Visible = true;
er2.Visible = true;
isValid = 1;
}
}
}
}
}
}
else if (check == 0 && SLS.Static.ID == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT minElapsed, maxElapsed FROM TIMEDEPOSITPENALTY WHERE [status] = 1";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
while (reader.Read() && isValid == 0)
{
for (Decimal i = Convert.ToDecimal(txtMin.Text); (i <= Convert.ToDecimal(txtMax.Text)) && isValid == 0; i = i + Convert.ToDecimal(0.01))
{
if (i >= Convert.ToDecimal(reader[0]) && i <= Convert.ToDecimal(reader[1]))
{
er1.Visible = true;
er2.Visible = true;
isValid = 1;
}
}
}
}
}
try
{
Convert.ToDecimal(txtReducedBy.Text);
if (Convert.ToDecimal(txtReducedBy.Text) > 100)
{
er3.Visible = true;
isValid = 1;
}
}
catch
{
er3.Visible = true;
isValid = 1;
}
return isValid;
}
public void defaultAll()
{
txtMin.Text = "";
er1.Visible = true;
txtMax.Text = "";
er2.Visible = true;
txtReducedBy.Text = "";
er3.Visible = true;
if(SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT minElapsed, maxElapsed, reducedBy FROM TIMEDEPOSITPENALTY WHERE TimeDepositPenaltyID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMin.Text = Convert.ToString(reader[0]);
min = Convert.ToDecimal(reader[0]);
er1.Visible = false;
txtMax.Text = Convert.ToString(reader[1]);
max = Convert.ToDecimal(reader[1]);
er2.Visible = false;
txtReducedBy.Text = Convert.ToString(reader[2]);
er3.Visible = false;
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (SLS.Static.ID == 0)
{
if (checkValues() == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO MEMBER (MemberTypeID, fName, mName, lName, gender, birthDate, civilStatus, gsisNo, educAttainment, paidMembershipFee, applyDate, seminarDate, initialCapital, multiplier, isActive) VALUES ((SELECT MemberTypeID FROM MEMBERTYPE WHERE MemberTypeName = @MemberTypeName), @fName, @mName, @lName, @gender, @birthDate, @civilStatus, @gsisNo, @educAttainment, @paidMembershipFee, @applyDate, @seminarDate, @isActive); SELECT SCOPE_IDENTIY()";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@MemberTypeName", cmbMemberType.SelectedItem.ToString());
parameters.Add("@fName", txtFN.Text);
parameters.Add("@mName", txtMN.Text);
parameters.Add("@lName", txtLN.Text);
parameters.Add("@gender", Convert.ToInt32(cmbGender.SelectedIndex));
parameters.Add("@birthDate", Convert.ToDateTime(dtDateBirth.Value));
parameters.Add("@civilStatus", Convert.ToInt32(cmbCivil.SelectedIndex));
parameters.Add("@gsisNo", txtGSISSSS.Text);
parameters.Add("@educAttainment", Convert.ToInt32(cmbEduc.SelectedIndex));
parameters.Add("@paidMembershipFee", Convert.ToDecimal(txtFee.Text));
parameters.Add("@applyDate", Convert.ToDateTime(dtMembership.Value));
parameters.Add("@seminarDate", Convert.ToDateTime(dtSeminar.Value));
parameters.Add("@isActive", true);
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
MemberID = reader.GetInt32(0);
MessageBox.Show("Adding a member is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "INSERT INTO CONTACTPERSON (contactFN, contactLN, contactMN, contactNo, MemberID) VALUES (@contactFN, @contactLN, @contactMN, @contactNo, @MemberID)";
parameters = new Dictionary<string, object>();
parameters.Add("@contactFN", txtCPFN.Text);
parameters.Add("@contactLN", txtCPLN.Text);
parameters.Add("@contactMN", txtCPMN.Text);
parameters.Add("@contactNo", txtContact.Text);
parameters.Add("@MemberID", MemberID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
sql = "INSERT INTO CONTACTINFORMATION (street, municipality, cityProvince, zipCode, residenceSince, telephoneNo, MemberID) VALUES (@street, @municipality, @cityProvince, @zipCode, @residenceSince, @telephoneNo, @MemberID)";
parameters = new Dictionary<string, object>();
parameters.Add("@street", txtStreet.Text);
parameters.Add("@municipality", txtMunicipality.Text);
parameters.Add("@cityProvince", txtCity.Text);
parameters.Add("@zipCode", txtZip.Text);
parameters.Add("@residenceSince", txtResidence.Text);
parameters.Add("@telephoneNo", txtTelNo.Text);
parameters.Add("@MemberID", MemberID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (checkBox2.Checked == true)
{
sql = "INSERT INTO CONTACTINFORMATION (street, municipality, cityProvince, zipCode, residenceSince, telephoneNo, MemberID) VALUES (@street, @municipality, @cityProvince, @zipCode, @residenceSince, @telephoneNo, @MemberID)";
parameters = new Dictionary<string, object>();
parameters.Add("@street", txtStreet2.Text);
parameters.Add("@municipality", txtMunicipality2.Text);
parameters.Add("@cityProvince", txtCity2.Text);
parameters.Add("@zipCode", txtZip2.Text);
parameters.Add("@residenceSince", txtResidence2.Text);
parameters.Add("@telephoneNo", txtTelNo2.Text);
parameters.Add("@MemberID", MemberID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
addToEmploymentInformation();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT ISNULL(MAX(EmploymentInformationID),1) FROM EMPLOYMENTINFORMATION";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
EmploymentInformationID = reader.GetInt32(0);
}
if (occup == 0 || occup == 1 || occup == 2)
{
addToEmployed();
}
else
if (occup == 3)
{
addToSelfEmployed();
}
if (checkBox5.Checked == true)
{
addFatherRelationship();
}
if (checkBox1.Checked == true)
{
addMotherRelationship();
}
if (checkBox3.Checked == true)
{
addSpouseRelationship();
}
if (tableChild.Rows.Count > 0)
{
for (int ind = 0; ind < tableChild.Rows.Count; ind++)
{
addChildrenRelationship(ind);
}
}
this.Close();
}
else
{
MessageBox.Show("Adding a member is not successful.", "Error", MessageBoxButtons.OK);
}
}
else
{
MessageBox.Show("Some required fields are missing/invalid!", "Error");
}
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE MEMBER SET fName = @fName, mName = @mName, lName = @lName, gender = @gender, birthDate = @birthDate, civilStatus = @civilStatus, gsisNo = @gsisNo, educAttainment = @educAttainment, paidMembershipFee = @paidMembershipFee, applyDate = @applyDate, seminarDate = @seminarDate, initialCapital = @initialCapital, multiplier = @multiplier, MemberTypeID = (SELECT MemberTypeID FROM MEMBERTYPE WHERE MemberTypeName = @MemberTypeName) WHERE MemberID = " + SLS.Static.ID + " ";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@fName", txtFN.Text);
parameters.Add("@mName", txtMN.Text);
parameters.Add("@lName", txtLN.Text);
parameters.Add("@birthDate", Convert.ToDateTime(dtDateBirth.Value));
parameters.Add("@gender", Convert.ToInt32(cmbGender.SelectedIndex));
parameters.Add("@MemberTypeName", cmbMemberType.SelectedItem.ToString());
parameters.Add("@civilStatus", Convert.ToInt32(cmbCivil.SelectedIndex));
parameters.Add("@gsisNo", txtGSISSSS.Text);
parameters.Add("@educAttainment", Convert.ToInt32(cmbEduc.SelectedIndex));
parameters.Add("@paidMembershipFee", Convert.ToDecimal(txtFee.Text));
parameters.Add("@applyDate", Convert.ToDateTime(dtMembership.Value));
parameters.Add("@seminarDate", Convert.ToDateTime(dtSeminar.Value));
parameters.Add("@initialCapital", Convert.ToDecimal(txtInitialCapital.Text));
parameters.Add("@multiplier", Convert.ToInt32(txtMultiplier.Text));
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a member is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Employment Information Delete
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT EmploymentInformationID FROM EMPLOYMENTINFORMATION WHERE MemberID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
int employID = Convert.ToInt32(reader[0]);
//Employed Delete
sql = "DELETE FROM EMPLOYED WHERE EmploymentInformationID = employID";
parameters = new Dictionary<string, object>();
parameters.Add("employID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
//Self-Employed Delete
sql = "DELETE FROM SELFEMPLOYED WHERE EmploymentInformationID = employID";
parameters = new Dictionary<string, object>();
parameters.Add("employID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
//Contact Person Delete
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "DELETE FROM CONTACTPERSON WHERE MemberID = @MemberID";
parameters = new Dictionary<string, object>();
parameters.Add("@MemberID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
//Contact Information Delete
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "DELETE FROM CONTACTINFORMATION WHERE MemberID = @MemberID";
parameters = new Dictionary<string, object>();
parameters.Add("@MemberID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
sql = "DELETE FROM EMPLOYMENTINFORMATION WHERE MemberID = @MemberID";
parameters = new Dictionary<string, object>();
parameters.Add("@MemberID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
//Family BackGround Delete
sql = "DELETE FROM FAMILYBACKGROUND WHERE MemberID = @MemberID";
parameters = new Dictionary<string, object>();
parameters.Add("@MemberID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
defaultAll();
this.Close();
}
else
{
MessageBox.Show("Updating a member is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnStatus_Click(object sender, EventArgs e)
{
if(btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE TIMEDEPOSITPENALTY SET [status] = @status WHERE TimeDepositPenaltyID = @TimeDepositPenaltyID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@TimeDepositPenaltyID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT minElapsed, maxElapsed FROM TIMEDEPOSITPENALTY WHERE TimeDepositPenaltyID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if(reader.HasRows)
{
reader.Read();
min = Convert.ToDecimal(reader[0]);
max = Convert.ToDecimal(reader[1]);
}
int isValid = 0;
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT minElapsed, maxElapsed FROM TIMEDEPOSITPENALTY WHERE [status] = 1";
reader = con.executeReader(sql);
if (reader.HasRows)
{
while (reader.Read() && isValid == 0)
{
for (Decimal i = min; (i <= max) && isValid == 0; i = i + Convert.ToDecimal(0.01))
{
if (i >= Convert.ToDecimal(reader[0]) && i <= Convert.ToDecimal(reader[1]))
{
isValid = 1;
}
}
}
}
if(isValid == 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE TIMEDEPOSITPENALTY SET [status] = @status WHERE TimeDepositPenaltyID = @TimeDepositPenaltyID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@TimeDepositPenaltyID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK);
}
}
else
{
MessageBox.Show("Another elapsed time is active.\nDisable first the active one.", "Error", MessageBoxButtons.OK);
}
}
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT savingsTypeName FROM SAVINGSTYPE where hasDormancy = 0";
SqlDataReader reader = con.executeReader(sql);
cobSavingsType.Items.Clear();
int i = 0;
while (reader.Read())
{
string str = reader[0].ToString();
cobSavingsType.Items.Insert(i, "" + str);
i += 1;
}
cobSavingsType.Text = "";
cobSavingsType.Enabled = true;
er1.Visible = true;
txtInactiveValue.Text = "";
er2.Visible = true;
cobInactiveTime.Items.Clear();
for (i = 0; i < timeString.Length; i++)
{
cobInactiveTime.Items.Insert(i, "" + timeString[i]);
}
cobInactiveTime.Text = "";
er3.Visible = true;
txtDeductAmount.Text = "";
er4.Visible = true;
cobDeductNature.Items.Clear();
for (i = 0; i < NatureString.Length; i++)
{
cobDeductNature.Items.Insert(i, "" + NatureString[i]);
}
cobDeductNature.Text = "";
er5.Visible = true;
cobDeductMode.Items.Clear();
for (i = 0; i < ModeString.Length; i++)
{
cobDeductMode.Items.Insert(i, "" + ModeString[i]);
}
cobDeductMode.Text = "";
er6.Visible = true;
txtActivateFee.Text = "";
er7.Visible = true;
if (SLS.Static.ID != 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DORMANCY.DormancyID, SAVINGSTYPE.savingsTypeName, DORMANCY.inactivityValue, DORMANCY.inactivityTime, DORMANCY.deductionAmount, DORMANCY.isPercentage, DORMANCY.deductionMode, DORMANCY.activationFee, DORMANCY.[status] FROM DORMANCY, SAVINGSTYPE WHERE DORMANCY.SavingsTypeID = SAVINGSTYPE.SavingsTypeID AND DORMANCY.DormancyID = " + SLS.Static.ID + " ";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
DormancyID = Convert.ToInt32(reader[0]);
cobSavingsType.Items.Insert(cobSavingsType.Items.Count, "" + reader[1].ToString());
int index = cobSavingsType.Items.IndexOf(reader[1]);
cobSavingsType.SelectedIndex = index;
cobSavingsType.Enabled = false;
er1.Visible = false;
txtInactiveValue.Text = Convert.ToString(reader[2]);
er2.Visible = false;
cobInactiveTime.SelectedIndex = Convert.ToInt32(reader[3]);
er3.Visible = false;
txtDeductAmount.Text = Convert.ToString(reader[4]);
er4.Visible = false;
cobDeductNature.SelectedIndex = Convert.ToInt32(reader[5]);
er5.Visible = false;
cobDeductMode.SelectedIndex = Convert.ToInt32(reader[6]);
er6.Visible = false;
txtActivateFee.Text = Convert.ToString(reader[7]);
er7.Visible = false;
}
}
}
public void defaultAll()
{
txtSavName.Text = "";
er1.Visible = true;
txtInterest.Text = "";
er2.Visible = true;
txtInitial.Text = "";
er3.Visible = true;
ckbMaxWithdraw.Checked = false;
txtMaxAmount.Text = "";
er4.Visible = false;
cobMaxMode.Items.Clear();
for (int x = 0; x < ModeString.Length; x++)
{
cobMaxMode.Items.Insert(x, "" + ModeString[x]);
}
er5.Visible = false;
txtMainBal.Text = "";
er6.Visible = false;
txtBalToEarn.Text = "";
er7.Visible = true;
ckbApplyAll.Checked = false;
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT memberTypeName FROM MEMBERTYPE where SavingsApplicable = 1 and [status] = 1 ORDER BY membertypename";
SqlDataReader reader = con.executeReader(sql);
cklbMemberType.Items.Clear();
int i = 0;
while (reader.Read())
{
string str = reader[0].ToString();
cklbMemberType.Items.Insert(i, "" + str);
i += 1;
}
if (SLS.Static.ID != 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT SavingsTypeID, SavingsTypeName, initialDeposit, maintainingBalance, balanceToEarn, interestRate, maxWithdrawAmount, maxWithdrawMode, hasDormancy, [status] FROM SAVINGSTYPE WHERE SavingsTypeID = " + SLS.Static.ID + " ";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtSavName.Text = Convert.ToString(reader[1]);
er1.Visible = false;
txtInitial.Text = Convert.ToString(reader[2]);
er3.Visible = false;
txtMainBal.Text = Convert.ToString(reader[3]);
er6.Visible = false;
txtBalToEarn.Text = Convert.ToString(reader[4]);
er7.Visible = false;
txtInterest.Text = Convert.ToString(reader[5]);
er2.Visible = false;
if (Convert.ToDecimal(reader[6]) != 0)
{
ckbMaxWithdraw.Checked = true;
txtMaxAmount.Enabled = true;
txtMaxAmount.Text = Convert.ToString(reader[6]);
er4.Visible = false;
cobMaxMode.Enabled = true;
er5.Visible = false;
if (Convert.ToInt32(reader[7]) == 1)
{
cobMaxMode.Text = "Per Day";
}
else if (Convert.ToInt32(reader[7]) == 2)
{
cobMaxMode.Text = "Per Week";
}
else if (Convert.ToInt32(reader[7]) == 3)
{
cobMaxMode.Text = "Per Month";
}
else if (Convert.ToInt32(reader[7]) == 4)
{
cobMaxMode.Text = "Per Year";
}
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MEMBERTYPE.memberTypeName FROM MEMBERTYPE, APPLICABLESAVINGS WHERE MEMBERTYPE.MemberTypeID = APPLICABLESAVINGS.MemberTypeID and APPLICABLESAVINGS.SavingsTypeID = @SavingsTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@SavingsTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cklbMemberType.Items.Count; i++)
{
cklbMemberType.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cklbMemberType.Items.IndexOf(reader.GetString(0));
cklbMemberType.SetItemChecked(index, true);
}
}
}
}
public void defaultAll()
{
txtLoanType.Clear();
ckbMemType.Checked = false;
cListMemType.Items.Clear();
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT memberTypeName FROM MEMBERTYPE where LoanApplicable = 1 and [status] = 1 ORDER BY membertypename";
SqlDataReader reader = con.executeReader(sql);
int i = 0;
while(reader.Read())
{
string str = reader[0].ToString();
cListMemType.Items.Insert(i, "" + str);
i += 1;
}
txtMinAmount.Clear();
txtMaxAmount.Clear();
txtNoOfComaker.Clear();
txtEntitlement.Clear();
txtEligibility.Clear();
ckbMode.Checked = false;
cListMode.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT modeName FROM MODE where [status] = 1 ORDER BY daysInterval";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
cListMode.Items.Insert(i, "" + Convert.ToString(reader.GetString(0)));
i += 1;
}
ckbTerm.Checked = false;
cListTerm.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT noOfDays FROM TERM where [status] = 1 ORDER BY noOfDays";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
cListTerm.Items.Insert(i, "" + Convert.ToString(reader.GetInt32(0)));
i += 1;
}
ckbCharges.Checked = false;
cListCharges.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT chargeName FROM CHARGES where [status] = 1 ORDER BY chargeName";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
if (reader.GetString(0) != "Interest On Loan")
{
cListCharges.Items.Insert(i, "" + Convert.ToString(reader.GetString(0)));
i += 1;
}
}
if(SLS.Static.ID != 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], noOfComaker as [No. of Co-maker], entitlement as [Entitlement], eligibility as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE LoanTypeID = " + SLS.Static.ID + " ";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtLoanType.Text = Convert.ToString(reader[1]);
txtMinAmount.Text = Convert.ToString(reader[2]);
txtMaxAmount.Text = Convert.ToString(reader[3]);
txtNoOfComaker.Text = Convert.ToString(reader[4]);
txtEntitlement.Text = Convert.ToString(reader[5]);
txtEligibility.Text = Convert.ToString(reader[6]);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MEMBERTYPE.memberTypeName FROM MEMBERTYPE, APPLICABLELOAN WHERE MEMBERTYPE.MemberTypeID = APPLICABLELOAN.MemberTypeID and APPLICABLELOAN.LoanTypeID = @LoanTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListMemType.Items.Count; i++)
{
cListMemType.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListMemType.Items.IndexOf(reader.GetString(0));
cListMemType.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(MODE.modeName) FROM MODE, LOANRATE, LOANTYPE WHERE MODE.ModeID = LOANRATE.ModeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListMode.Items.Count; i++)
{
cListMode.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListMode.Items.IndexOf(reader.GetString(0));
cListMode.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(TERM.noOfDays) FROM TERM, LOANRATE, LOANTYPE WHERE TERM.TermID = LOANRATE.TermID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListTerm.Items.Count; i++)
{
cListTerm.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListTerm.Items.IndexOf(Convert.ToString(reader.GetInt32(0)));
cListTerm.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(CHARGES.chargeName) FROM CHARGES, LOANRATE, LOANTYPE WHERE CHARGES.ChargeID = LOANRATE.ChargeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListCharges.Items.Count; i++)
{
cListCharges.SetItemChecked(i, false);
}
while (reader.Read())
{
if (reader.GetString(0) != "Interest On Loan")
{
int index = cListCharges.Items.IndexOf(reader.GetString(0));
cListCharges.SetItemChecked(index, true);
}
}
}
}
private void btnStatus_Click(object sender, EventArgs e)
{
if(SLS.Static.selected == 0)
{
if(btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE MODE SET [status] = @status WHERE ModeID = @ModeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@ModeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if(btnStatus.Text == "ACTIVATE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT daysInterval FROM MODE WHERE ModeID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
Int32 isValid = 0;
if(reader.HasRows && isValid == 0)
{
reader.Read();
if(daysInterval == reader.GetInt32(0))
{
isValid = 1;
}
}
if(isValid == 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE MODE SET [status] = @status WHERE ModeID = @ModeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@ModeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("There is an existing mode of payment.","Error");
}
}
}
else if (SLS.Static.selected == 1)
{
if (btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE TERM SET [status] = @status WHERE TermID = @TermID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@TermID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (btnStatus.Text == "ACTIVATE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT noOfDays FROM TERM WHERE TermID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
Int32 isValid = 0;
if (reader.HasRows && isValid == 0)
{
reader.Read();
if (noOfDays == reader.GetInt32(0))
{
isValid = 1;
}
}
if (isValid == 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE TERM SET [status] = @status WHERE TermID = @TermID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@TermID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("There is an existing mode of payment.", "Error");
}
}
}
else if (SLS.Static.selected == 2)
{
if (btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE CHARGE SET [status] = @status WHERE ChargeID = @ChargeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@ChargeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (btnStatus.Text == "ACTIVATE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE CHARGE SET [status] = @status WHERE ChargeID = @ChargeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@ChargeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if(SLS.Static.ID == 0)
{
if(checkValues() == 1)
{
MessageBox.Show("Some required fields are missing or invalid.", "Error");
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO LOANTYPE (loanTypeName, minAmount, maxAmount, noOfComaker, entitlement, eligibility, [status]) VALUES (@loanTypeName, @minAmount, @maxAmount, @noOfComaker, @entitlement, @eligibility, @status); SELECT CAST(scope_identity() AS int)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@loanTypeName", txtLoanType.Text);
parameters.Add("@minAmount", txtMinAmount.Text);
parameters.Add("@maxAmount", txtMaxAmount.Text);
parameters.Add("@noOfComaker", txtNoOfComaker.Text);
parameters.Add("@entitlement", txtEntitlement.Text);
parameters.Add("@eligibility", txtEligibility.Text);
parameters.Add("@status", true);
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
MessageBox.Show("Adding a loan type is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
foreach (object itemChecked in cListMemType.CheckedItems)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "INSERT INTO APPLICABLELOAN (LoanTypeID, MemberTypeID) VALUES (@LoanTypeID,(SELECT MemberTypeID FROM MEMBERTYPE WHERE memberTypeName = @memberTypeName))";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", reader[0]);
parameters.Add("@memberTypeName", itemChecked.ToString());
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
modeArr = new String[cListMode.Items.Count];
int sInd = 0;
for (int i = 0; i < cListMode.Items.Count; i++)
{
if (cListMode.GetItemCheckState(i) == CheckState.Checked)
{
modeArr[sInd] = Convert.ToString(cListMode.Items[i]);
sInd++;
}
}
termArr = new Int32[cListTerm.Items.Count];
sInd = 0;
for (int i = 0; i < cListTerm.Items.Count; i++)
{
if (cListTerm.GetItemCheckState(i) == CheckState.Checked)
{
termArr[sInd] = Convert.ToInt32(cListTerm.Items[i].ToString());
sInd++;
}
}
for (int j = 0; j <= dataGridView1.Rows.Count - 1; j++)
{
for (int k = 2; k <= dataGridView1.Columns.Count - 1; k++)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "INSERT INTO LOANRATE (ModeID, ChargeID, TermID, LoanTypeID, rate) VALUES ((SELECT ModeID FROM MODE WHERE modeName = @modeName AND [status] = 1), @ChargeID, (SELECT TermID FROM TERM WHERE noOfDays = @noOfDays AND [status] = 1), @LoanTypeID, @rate)";
parameters = new Dictionary<string, object>();
parameters.Add("@modeName", modeArr[(k - 2) / cListTerm.CheckedItems.Count]);
parameters.Add("@ChargeID", Convert.ToInt32(dataGridView1.Rows[j].Cells[0].Value.ToString()));
parameters.Add("@noOfDays", termArr[(k - 2) / cListMode.CheckedItems.Count]);
parameters.Add("@LoanTypeID", reader[0]);
parameters.Add("@rate", Convert.ToDecimal(dataGridView1.Rows[j].Cells[k].Value.ToString()));
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
}
this.Close();
}
else
{
MessageBox.Show("Adding a loan type is not successful.", "Error", MessageBoxButtons.OK);
}
}
}
}
private void txtAge_TextChanged(object sender, EventArgs e)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MemberTypeName FROM MEMBERTYPE WHERE DATEDIFF(YEAR, @birthDate , @DateNow )BETWEEN MinAgeRequired AND MaxAgeRequired";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@birthDate", Convert.ToDateTime(dtDateBirth.Value));
parameters.Add("@DateNow", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")));
SqlDataReader reader = con.executeReader(sql,parameters);
Int32 i=0;
cmbMemberType.Items.Clear();
while (reader.Read())
{
cmbMemberType.Items.Insert(i, "" + reader.GetString(0));
i++;
}
}
public Int32 checkValues()
{
Int32 isValid = 0;
try
{
try
{
if(cobTransType.SelectedIndex == 1)
{
if(Convert.ToDecimal(txtCurrentBalance.Text) - Convert.ToDecimal(txtAmount.Text) < Convert.ToDecimal(txtMainBal.Text))
{
er3.Visible = true;
isValid = 1;
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT SAVINGSTYPE.maxWithdrawMode, SAVINGSTYPE.maxWithdrawAmount FROM SAVINGSACCOUNT, SAVINGSTYPE WHERE SAVINGSACCOUNT.SavingsTypeID = SAVINGSTYPE.SavingsTypeID AND SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader1 = con.executeReader(sql);
if(reader1.HasRows)
{
reader1.Read();
if(reader1.GetInt32(0) == 1)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT ISNULL(SUM(transactionAmount),0) FROM SAVINGSTRANSACTION WHERE DATENAME(DAY,transactionDate) = DATENAME(DAY, " + Convert.ToString(DateTime.Now.ToLongDateString()) + " ) AND transactionType = 2 AND SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if(reader.HasRows)
{
reader.Read();
if(Convert.ToDecimal(reader.GetValue(0)) + Convert.ToDecimal(txtAmount.Text) > Convert.ToDecimal(reader1.GetValue(1)))
{
er3.Visible = true;
isValid = 1;
}
}
}
else if(reader1.GetInt32(0) == 2)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT ISNULL(SUM(transactionAmount),0) FROM SAVINGSTRANSACTION WHERE DATENAME(WEEK,transactionDate) = DATENAME(WEEK, " + DateTime.Now.ToLongDateString() + " ) AND transactionType = 2 AND SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if(reader.HasRows)
{
reader.Read();
if(Convert.ToDecimal(reader1.GetValue(0)) + Convert.ToDecimal(txtAmount.Text) > Convert.ToDecimal(reader1.GetValue(1)))
{
er3.Visible = true;
isValid = 1;
}
}
}
else if(Convert.ToInt32(reader1.GetValue(0)) == 3)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT ISNULL(SUM(transactionAmount),0) FROM SAVINGSTRANSACTION WHERE DATENAME(MONTH,transactionDate) = DATENAME(MONTH, @DateNow) AND transactionType = 2 AND SavingsAccountID = " + SLS.Static.ID + "";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@DateNow", Convert.ToDateTime(txtTransDate.Text));
SqlDataReader reader = con.executeReader(sql, parameters);
if(reader.HasRows)
{
reader.Read();
if(Convert.ToDecimal(reader.GetValue(0)) + Convert.ToDecimal(txtAmount.Text) > Convert.ToDecimal(reader1.GetValue(1)))
{
er3.Visible = true;
isValid = 1;
}
}
}
else if(reader1.GetInt32(0) == 4)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT ISNULL(SUM(transactionAmount),0) FROM SAVINGSTRANSACTION WHERE DATENAME(YEAR,transactionDate) = DATENAME(YEAR, " + DateTime.Now.ToLongDateString() + ") AND transactionType = 2 AND SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if(reader.HasRows)
{
reader.Read();
if(Convert.ToDecimal(reader1.GetValue(0)) + Convert.ToDecimal(txtAmount.Text) > Convert.ToDecimal(reader1.GetValue(1)))
{
er3.Visible = true;
isValid = 1;
}
else
{
MessageBox.Show("" + Convert.ToString(reader.GetValue(0)));
}
}
}
}
}
}
else if(cobTransType.SelectedIndex == 0)
{
try
{
Convert.ToDecimal(txtAmount.Text);
}
catch(Exception)
{
er3.Visible = true;
isValid = 1;
}
}
else
{
er3.Visible = true;
isValid = 1;
}
}
catch (Exception)
{
er3.Visible = true;
isValid = 1;
}
}
catch (Exception)
{
isValid = 1;
er1.Visible = true;
}
return isValid;
}
public void defaultAll()
{
txtNoOfDays.Text = "";
er1.Visible = true;
txtMinAmount.Text = "";
er2.Visible = true;
txtMaxAmount.Text = "";
er3.Visible = true;
txtInterest.Text = "";
er4.Visible = true;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT noOfDays, minAmount, maxAmount, interest as [Interest] FROM TIMEDEPOSITRATES WHERE TimeDepositRatesID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtNoOfDays.Text = Convert.ToString(reader[0]);
er1.Visible = false;
txtMinAmount.Text = Convert.ToString(reader[1]);
er2.Visible = false;
txtMaxAmount.Text = Convert.ToString(reader[2]);
er3.Visible = false;
txtInterest.Text = Convert.ToString(reader[3]);
er4.Visible = false;
}
}
}