protected void BtnGetCustomer_Click(object sender, EventArgs e)
{
Customer cusObj = new Customer();
cusObj = cusObj.GetCustomerById(TbCustId.Text);
if (cusObj != null)
{
PanelErrorResult.Visible = false;
PanelCust.Visible = true;
Lbl_custname.Text = cusObj.Name;
Lbl_Address.Text = cusObj.Address;
Lbl_HomePhone.Text = cusObj.HomePhone;
Lbl_Mobile.Text = cusObj.Mobile;
Lbl_err.Text = String.Empty;
HyplinkAdd.Visible = true;
TDMaster td = new TDMaster();
List <TDMaster> list = td.GetTDbyCustId(TbCustId.Text);
RefreshGridView(list);
Session["SScustId"] = TbCustId.Text;
Session["SScustName"] = cusObj.Name;
}
else
{
Lbl_err.Text = "Customer record not found!";
PanelErrorResult.Visible = true;
PanelCust.Visible = false;
Lbl_custname.Text = String.Empty;
Lbl_Address.Text = String.Empty;
Lbl_HomePhone.Text = String.Empty;
Lbl_Mobile.Text = String.Empty;
HyplinkAdd.Visible = false;
}
}
private static TDMaster Read(SqlDataReader rd)
{
string custId = rd["custId"].ToString();
string acc = rd["account"].ToString();
double principal = Convert.ToDouble(rd["principal"]);
int term = Convert.ToInt32(rd["term"]);
DateTime effFrom = Convert.ToDateTime(rd["effectFrom"]);
DateTime mDate = Convert.ToDateTime(rd["maturity"]);
double interest = Convert.ToDouble(rd["interestAmt"]);
double mAmt = Convert.ToDouble(rd["matureAmt"]);
double rate = Convert.ToDouble(rd["tdRate"]);
string mode = rd["renewMode"].ToString();
TDMaster td = new TDMaster
{
CustId = custId,
Account = acc,
Principal = principal,
Term = term,
EffectiveFrom = effFrom,
MaturityDate = mDate,
Interest = interest,
MaturedAmt = mAmt,
IntRate = rate,
RenewMode = mode
};
return(td);
}
public static List <TDMaster> SelectByCustId(string custId)
{
List <TDMaster> tdList = new List <TDMaster>();
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(connStr);
string sqlstmt = @"SELECT * From TDMaster where custId = @paraId and maturity >= GETDATE()";
SqlCommand cmd = new SqlCommand(sqlstmt, myConn);
cmd.Parameters.AddWithValue("@paraId", custId);
myConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TDMaster td = Read(dr);
tdList.Add(td);
}
myConn.Close();
return(tdList);
}
protected void BtnConfirm_Click(object sender, EventArgs e)
{
String account = TbNewAcno.Text.ToString();
String custId = LblCustId.Text.ToString();
double principal = Convert.ToDouble(TbPrincipal.Text.ToString());
int term = Convert.ToInt32(DdlTerm.SelectedItem.Text.ToString());
DateTime mDate = Convert.ToDateTime(LblMaturedDte.Text);
double interest = Convert.ToDouble(LblInterest.Text.ToString());
double mAmt = Convert.ToDouble(LblMaturedAmt.Text);
double rate = Convert.ToSingle(DdlTerm.SelectedValue);
string mode = DdlRenew.SelectedItem.Text;
// Instantiate an object from the TDMaster calss and call the AddTermDeposit() method
TDMaster td = new TDMaster(account, custId, principal, term, interest, mDate, mAmt, rate, mode);
int insCnt = td.AddTermDeposit();
if (insCnt == 1)
{
LblResult.Text = "Term Deposit Created!";
LblResult.ForeColor = Color.Green;
}
else
{
LblResult.Text = "Unable to insert term deposit. Please inform system administrator!";
LblResult.ForeColor = Color.Red;
}
BtnConfirm.Enabled = false;
}
public int Insert(TDMaster td)
{
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
string sqlStmt = "INSERT INTO TDMaster (account, custId, principal, term, " +
"effectFrom, maturity,interestAmt,matureAmt,tdRate,renewMode)" +
"VALUES (@paraAccount,@paraCustId,@paraPrincipal,@paraTerm," +
"GETDATE(),@paraMaturity,@paraInterest,@paraMatureAmt,@paraTdRate,@paraRenewMode)";
int result = 0; // Execute NonQuery return an integer value
SqlCommand sqlCmd = new SqlCommand(sqlStmt, myConn);
sqlCmd.Parameters.AddWithValue("@paraAccount", td.Account);
sqlCmd.Parameters.AddWithValue("@paraCustId", td.CustId);
sqlCmd.Parameters.AddWithValue("@paraPrincipal", td.Principal);
sqlCmd.Parameters.AddWithValue("@paraTerm", td.Term);
sqlCmd.Parameters.AddWithValue("@paraMaturity", td.MaturityDate);
sqlCmd.Parameters.AddWithValue("@paraInterest", td.Interest);
sqlCmd.Parameters.AddWithValue("@paraMatureAmt", td.MaturedAmt);
sqlCmd.Parameters.AddWithValue("@paraTdRate", td.IntRate);
sqlCmd.Parameters.AddWithValue("@paraRenewMode", td.RenewMode);
myConn.Open();
result = sqlCmd.ExecuteNonQuery();
myConn.Close();
return(result);
}
public static bool Insert(TDMaster td)
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
string sqlStmt = @"INSERT INTO TDMaster
(Account, CustId, Principal, Term, EffFrom, Maturity, InterestAmt, MatureAmt, TDRate, RenewMode)
VALUES
(@Account, @CustId, @Principal, @Term, @EffFrom, @Maturity, @InterestAmt, @MatureAmt, @TDRate, @RenewMode)";
SqlCommand cmd = new SqlCommand(sqlStmt, connection);
cmd.Parameters.AddWithValue("@Account", td.Account);
cmd.Parameters.AddWithValue("@CustId", td.CustId);
cmd.Parameters.AddWithValue("@Principal", td.Principal);
cmd.Parameters.AddWithValue("@Term", td.Term);
cmd.Parameters.AddWithValue("@EffFrom", td.EffFrom);
cmd.Parameters.AddWithValue("@Maturity", td.Maturity);
cmd.Parameters.AddWithValue("@InterestAmt", td.InterestAmt);
cmd.Parameters.AddWithValue("@MatureAmt", td.MatureAmt);
cmd.Parameters.AddWithValue("@TDRate", td.TDRate);
cmd.Parameters.AddWithValue("@RenewMode", td.RenewMode);
int count = cmd.ExecuteNonQuery();
return(count > 0);
}
finally
{
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
if (Session["SSTDAcNo"] != null)
{
// Assign session variables for customer id, name and account labels
LblCustId.Text = Session["SScustId"].ToString();
LblCustname.Text = Session["SScustName"].ToString();
LblAcno.Text = Session["SSTDAcNo"].ToString();
// Retrieve TDMaster records by account
TDMaster td = new TDMaster();
td = td.GetTDbyAccount(LblAcno.Text);
// Show TDMaster info on form
LblPrincipal.Text = td.Principal.ToString();
LblMaturedAmt.Text = td.MaturedAmt.ToString();
LblMaturedDte.Text = td.MaturityDate.ToString();
DdlRenew.SelectedItem.Text = td.RenewMode;
// Store the current renew mode to compare with the new choice
ViewState["currRenewMode"] = td.RenewMode;;
}
else
{
Response.Redirect("CustomerForm.aspx");
}
}
}
public static TDMaster GetComputedObject(double principal, int term, double rate)
{
TDMaster td = new TDMaster();
td.Principal = principal;
td.Term = term;
td.TDRate = rate;
Compute(td);
return(td);
}
public static bool Add(string account, string custId, double principal, int term, double rate, string renewMode)
{
TDMaster td = new TDMaster();
td.Account = account;
td.CustId = custId;
td.Principal = principal;
td.Term = term;
td.TDRate = rate;
td.RenewMode = renewMode;
Compute(td);
return(TDMasterDAL.Insert(td));
}
private static void Compute(TDMaster td)
{
double principal = td.Principal;
int term = td.Term;
double rate = td.TDRate;
DateTime effDate = DateTime.Today;
DateTime matureDate = effDate.AddMonths(term);
double matureAmt = CalculateMaturity(principal, term, rate);
double interestAmt = Math.Round(matureAmt - principal, 1);
td.EffFrom = effDate;
td.Maturity = matureDate;
td.MatureAmt = matureAmt;
td.InterestAmt = interestAmt;
}
protected void DdlTerm_SelectedIndexChanged(object sender, EventArgs e)
{
if (ValidateInput())
{
double principal = Convert.ToDouble(TbPrincipal.Text);
int term = Convert.ToInt32(DdlTerm.SelectedItem.Text);
double rate = Convert.ToDouble(DdlTerm.SelectedValue);
TDMaster td = TDMasterBLL.GetComputedObject(principal, term, rate);
LblIntRte.Text = DdlTerm.SelectedValue;
LblMaturedDte.Text = td.Maturity.ToShortDateString();
LblInterest.Text = td.InterestAmt.ToString();
LblMaturedAmt.Text = td.MatureAmt.ToString();
}
}
public static TDMaster SelectByAccount(string acc)
{
TDMaster td = null;
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(connStr);
string sqlstmt = @"SELECT * From TDMaster where account = @paraAcc and maturity >= GETDATE()";
SqlCommand cmd = new SqlCommand(sqlstmt, myConn);
cmd.Parameters.AddWithValue("@paraAcc", acc);
myConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
td = Read(dr);
}
myConn.Close();
return(td);
}
// There is no input validation before the update.
// What validations can you include here?
protected void BtnUpdate_Click(object sender, EventArgs e)
{
int updCnt;
string newRenewMode = "";
newRenewMode = DdlRenew.SelectedItem.Text;
// Update renewal mode in TDMaster table account
TDMaster td = new TDMaster();
updCnt = td.UpdTDbyAccount(LblAcno.Text, newRenewMode);
if (updCnt == 1)
{
LblResult.Text = "TD Renew Mode Updated!";
LblResult.ForeColor = Color.Green;
}
else
{
LblResult.Text = "Unable to change time deposit, please inform system administrator!";
LblResult.ForeColor = Color.Red;
}
BtnUpdate.Enabled = false;
}
protected void BtnUpdate_Click(object sender, EventArgs e)
{
// Retrieve the current renew mode from ViewState
string currRenewMode = (string)ViewState["currRenewMode"];
if (DdlRenew.SelectedIndex == 0)
{
LblResult.Text = "Please select a value";
LblResult.ForeColor = Color.Red;
}
else if (DdlRenew.SelectedItem.Text.Equals(currRenewMode))
{
LblResult.Text = "No change in the renewal mode!";
LblResult.ForeColor = Color.Red;
}
else
{
string newRenewMode = DdlRenew.SelectedItem.Text;
// Update renewal mode in TDMaster table account
TDMaster td = new TDMaster();
int updCnt = td.UpdTDbyAccount(LblAcno.Text, newRenewMode);
if (updCnt == 1)
{
LblResult.Text = "TD Renew Mode Updated!";
LblResult.ForeColor = Color.Green;
ViewState["currRenewMode"] = newRenewMode;
}
else
{
LblResult.Text = "Unable to change time deposit, please inform system administrator!";
LblResult.ForeColor = Color.Red;
}
}
}
