protected void Snamecode_SelectedIndexChanged(object sender, EventArgs e)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string loginuser = Context.User.Identity.GetUserName();
DataTable idsearch = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'");
string loginuserid = "";
foreach (DataRow row in idsearch.Rows)
{
loginuserid = loginuserid + row["accountNumber"];
}
string choosencode = Snamecode.SelectedValue.Trim();
string choosentype = Stype.SelectedValue.Trim();
string curhighv = "";
string curlowv = "";
DataTable curalert = myHKeInvestData.getData("SELECT * FROM Alert WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'");
if (curalert.Rows.Count == 0)
{
}
else
{
foreach (DataRow row in curalert.Rows)
{
curhighv = curhighv + row["highValue"];
curlowv = curlowv + row["lowValue"];
}
curhigh.Text = curhighv;
curlow.Text = curlowv;
}
}
protected void Stype_SelectedIndexChanged(object sender, EventArgs e)
{
if (Stype.SelectedValue != "")
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//get user id and security data
string loginuser = Context.User.Identity.GetUserName();
DataTable idsearch = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'");
string loginuserid = "";
foreach (DataRow row in idsearch.Rows)
{
loginuserid = loginuserid + row["accountNumber"];
}
//get data to be input in dropdown list
Snamecode.Items.Clear();
Snamecode.Items.Add(new ListItem("Name (Code)", ""));
DataTable heldsecurity = myHKeInvestData.getData("SELECT code, name FROM SecurityHolding WHERE SecurityHolding.accountNumber = '" + loginuserid + "' AND SecurityHolding.type = '" + Stype.SelectedValue + "'");
foreach (DataRow row in heldsecurity.Rows)
{
//Snamecode.Items.Add(New ListItem(row["name"].ToString().Trim() + " (code: " + row["code"].ToString().Trim() + ")", row["code"].ToString().Trim()));
//Snamecode.Items.Add(row["name"].ToString().Trim()+" (code: "+row["code"].ToString().Trim()+")");
Snamecode.Items.Add(new ListItem(row["name"].ToString().Trim() + " (code: " + row["code"].ToString().Trim() + ")", row["code"].ToString().Trim()));
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string userName = Context.User.Identity.GetUserName();
string sql = "SELECT accountNumber FROM dbo.Account WHERE userName='******'";
DataTable dtAccountNumber = myHKeInvestData.getData(sql);
if (dtAccountNumber.Rows.Count == 1)
{
ViewState["accountNumber"] = dtAccountNumber.Rows[0].Field <string>("accountNumber").Trim();
}
else
{
ViewState["accountNumber"] = "";
}
ViewState.Add("SortExpression", "name");
ViewState.Add("SortDirection", "ASC");
ViewState["SortExpression"] = "name";
ViewState["SortDirection"] = "ASC";
}
if (!hasLoadAccountNumber)
{
hasLoadAccountNumber = true;
accountNumber = (string)ViewState["accountNumber"];
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Get the available currencies to populate the DropDownList.
//ddlCurrency.Items.Clear();
string userName = Context.User.Identity.GetUserName();
string AccountSql = "select [accountNumber] from [AccountTemp] where [userName]='" + userName + "'";
DataTable dTaccountNumberOfClient = myHKeInvestData.getData(AccountSql);
if (dTaccountNumberOfClient == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
// If no result is returned by the SQL statement, then display a message.
if (dTaccountNumberOfClient.Rows.Count == 0)
{
lblResultMessage.Text = "Error.";
lblResultMessage.Visible = true;
lblClientName.Visible = false;
gvSecurityHolding.Visible = false;
return;
}
// Show the client name(s) on the web page.
int j = 1;
foreach (DataRow row in dTaccountNumberOfClient.Rows)
{
if (j > 1)
{
lblResultMessage.Text = "Error.";
lblResultMessage.Visible = true;
lblClientName.Visible = false;
gvSecurityHolding.Visible = false;
return;
}
else
{
accountNumber = row["accountNumber"].ToString();
txtAccountNumber.Text = accountNumber;
}
j = j + 1;
}
DataTable dtCurrency = myExternalFunctions.getCurrencyData();
if (updated == false)
{
foreach (DataRow row in dtCurrency.Rows)
{
ddlCurrency.Items.Add(row["currency"].ToString().Trim());
}
updated = true;
}
}
public string getAccountNumber()
{
string sql = "SELECT accountNumber FROM Account WHERE userName = '******'";
DataTable temp = myHKeInvestData.getData(sql);
//ERROR: no error catching for not having the account number
return(temp.Rows[0]["accountNumber"].ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
gvTransactionDetails.Visible = false;
divTransacionDetails.Visible = false;
if (!IsPostBack)
{
// First load the account number into the viewstate
string userName = Context.User.Identity.GetUserName();
string sql = "SELECT accountNumber FROM dbo.Account WHERE userName='******'";
DataTable dtAccountNumber = myHKeInvestData.getData(sql);
if (dtAccountNumber.Rows.Count == 1)
{
accountNumber = dtAccountNumber.Rows[0].Field <string>("accountNumber");
ViewState["accountNumber"] = accountNumber;
}
else
{
accountNumber = "";
}
// Initializing the calendars
DateTime today = DateTime.Today;
FromDate.SelectedDate = today;
ToDate.SelectedDate = today;
ViewState["fromDate"] = today;
ViewState["toDate"] = today;
// Initializing grid views
initGVOrderStatus();
setGVOrderStatus();
initGVHistory();
setGVHistory();
// Initializing view states for variables
ViewState["bosChoice"] = null;
ViewState["typeChoice"] = null;
ViewState["codeChoice"] = null;
ViewState["statusChoice"] = null;
ViewState["dtOrderHistory"] = dtOrderHistory;
}
// Loading the variables for convenience
accountNumber = (string)ViewState["accountNumber"];
bosChoice = (string)ViewState["bosChoice"];
typeChoice = (string)ViewState["typeChoice"];
codeChoice = (string)ViewState["codeChoice"];
statusChoice = (string)ViewState["statusChoice"];
dtOrderHistory = (DataTable)ViewState["dtOrderHistory"];
}
private decimal Get_Assets(string accountNumber, decimal balance)
{
HKeInvestCode myHKeInvestCode = new HKeInvestCode();
HKeInvestData myHKeInvestData = new HKeInvestData();
ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable dtCurrency = myExternalFunctions.getCurrencyData();
DataTable dt = myHKeInvestData.getData("SELECT type, code, shares, base FROM [SecurityHolding] WHERE accountNumber='" + accountNumber + "'");
decimal ret = balance;
foreach (DataRow row in dt.Rows)
{
string securityCode = row["code"].ToString();
string securityType = row["type"].ToString();
string securityBase = row["base"].ToString();
decimal shares = Convert.ToDecimal(row["shares"]);
decimal price = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
decimal value = Math.Round(shares * price - (decimal).005, 2);
DataRow[] baseRateRow = dtCurrency.Select("currency = '" + securityBase + "'");
DataRow[] toRateRow = dtCurrency.Select("currency = 'HKD'");
if (baseRateRow.Length == 1 && toRateRow.Length == 1)
{
value = myHKeInvestCode.convertCurrency(securityBase, baseRateRow[0]["rate"].ToString(), "HKD", toRateRow[0]["rate"].ToString(), value);
}
ret += value;
}
return(ret);
}
private void Send_Notification(string accountNumber, string type, string code, string highOrLow, decimal currPrice)
{
// fetch the primary account holder email
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtClient = myHKeInvestData.getData(string.Format("SELECT lastName, email FROM [Client] WHERE accountNumber='{0}' AND isPrimary=(1)", accountNumber));
if (dtClient.Rows.Count != 1)
{
return;
}
string destination = dtClient.Rows[0].Field <string>("email");
string lastName = dtClient.Rows[0].Field <string>("lastName");
// fetch the name of the security
DataTable dtSecurity = myHKeInvestData.getData(string.Format("SELECT name FROM [SecurityHolding] WHERE accountNumber='{0}' AND type='{1}' AND code='{2}'", accountNumber, type, code));
if (dtSecurity.Rows.Count != 1)
{
return;
}
string securityName = dtSecurity.Rows[0].Field <string>("name");
#region construct notification message
string text = string.Format(@"Hi, {0}<br/>One of your alert(s) has been triggered:<br/>{1} {2} {3}, {4} price reached<br/>the price that triggered the alert: {5}<br/>",
lastName, type, code, securityName, highOrLow, currPrice);
string html = text;
#endregion
#region emailAccount
string username = "******";
#endregion
// Create an instance of MailMessage named mail.
MailMessage mail = new MailMessage();
// Set the sender (From), receiver (To), subject and message body fields of the mail message.
mail.From = new MailAddress(username, "Team104 Newbee");
mail.To.Add(destination);
mail.Subject = "[HKeInvest] Alert Notification";
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(text, null, MediaTypeNames.Text.Plain));
mail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(html, null, MediaTypeNames.Text.Html));
// Create an instance of SmtpClient named emailServer and set the mail server to use as "smtp.cse.ust.hk".
SmtpClient emailServer = new SmtpClient("smtp.cse.ust.hk");
// Send the message.
emailServer.Send(mail);
return;
}
protected void Confirm_Click(object sender, EventArgs e)
{
if (ddlSecurityType.SelectedValue != "0")
{
string accountNumber = myHKeInvestCode.getUserAccountNumber(Session, Context.User.Identity.GetUserName());
string securityType = ddlSecurityType.SelectedValue;
ViewState["type"] = ddlSecurityType.SelectedValue;
string sql = "SELECT type, code, name, shares, base, 0.00 as price, 0.00 as value FROM [SecurityHolding] WHERE accountNumber='" + accountNumber + "' and type='" + securityType + "'";
DataTable dtSecurityHolding = myHKeInvestData.getData(sql);
if (dtSecurityHolding == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
int dtRow = 0;
foreach (DataRow row in dtSecurityHolding.Rows)
{
string securityCode = row["code"].ToString();
decimal shares = Convert.ToDecimal(row["shares"]);
decimal price = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
decimal value = Math.Round(shares * price - (decimal).005, 2);
dtSecurityHolding.Rows[dtRow]["price"] = price;
dtSecurityHolding.Rows[dtRow]["value"] = value;
dtRow = dtRow + 1;
}
ErrorMessage.Text = "";
SecurityInformation.Visible = true;
gvSecurityInformation.DataSource = dtSecurityHolding;
gvSecurityInformation.DataBind();
StockSell.Visible = false;
NormalSell.Visible = false;
}
else
{
ErrorMessage.Text = "please select a security type";
SecurityInformation.Visible = false;
NormalSell.Visible = false;
StockSell.Visible = false;
return;
}
}
private string Get_UpdateSql(DataTable dtOrderDetails, string type, string accountNumber, decimal totalShares, string securityBase, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string code = dtOrderDetails.Rows[0].Field <string>("securityCode");
// find if the security is in the account
string sql = string.Format("SELECT shares FROM [SecurityHolding] WHERE type='{0}' and code='{1}' and accountNumber='{2}'", type, code, accountNumber);
DataTable dt = myHKeInvestData.getData(sql);
if (dt.Rows.Count == 0)
{
if (buyOrSell == "sell")
{
return(null);
}
// new security, return insert statement
return(string.Format("INSERT INTO [SecurityHolding] VALUES ('{0}', '{1}', '{2}', '{3}', {4}, '{5}')",
accountNumber,
type,
code,
dtOrderDetails.Rows[0].Field <string>("securityName"),
totalShares,
securityBase));
}
else
{
decimal newShares;
// already hold this security generate update
if (buyOrSell == "buy")
{
newShares = totalShares + dt.Rows[0].Field <decimal>("shares");
return(string.Format("UPDATE [SecurityHolding] SET shares={0} WHERE accountNumber='{1}' and type='{2}' and code='{3}'", newShares, accountNumber, type, code));
}
else if (buyOrSell == "sell")
{
newShares = dt.Rows[0].Field <decimal>("shares") - totalShares;
if (newShares == 0)
{
// sold out delete the record;
return(string.Format("DELETE FROM [SecurityHolding] WHERE accountNumber='{0}' and type='{1}' and code='{2}'", accountNumber, type, code));
}
else if (newShares > 0)
{
return(string.Format("UPDATE [SecurityHolding] SET shares={0} WHERE accountNumber='{1}' and type='{2}' and code='{3}'", newShares, accountNumber, type, code));
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
public bool accountExists(string accountNumber)
{
if (accountNumber == null)
{
return(false);
}
string sql = "SELECT * FROM dbo.[Account] a WHERE a.accountNumber='" + accountNumber + "';";
DataTable dt = myHKeInvestData.getData(sql);
if (dt == null)
{
return(false);
}
if (dt.Rows.Count != 1)
{
return(false);
}
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
// Get the available currencies to populate the DropDownList.
/*DataTable dtCurrency = myExternalFunctions.getCurrencyData();
* foreach (DataRow row in dtCurrency.Rows)
* {
* ddlCurrency.Items.Add(row["currency"].ToString().Trim());
* }*/
string loggedinuser = Context.User.Identity.GetUserName();
/*lbltest.Text = loggedinuser;
* lbltest.Visible = true;*/
string sql = "SELECT accountNumber FROM Account WHERE userName = '******'";
string loggedinuserid = "";
//execute sql command in database
DataTable loginuserid = myHKeInvestData.getData(sql);
foreach (DataRow row in loginuserid.Rows)
{
loggedinuserid = loggedinuserid + row["accountNumber"];
}
lblAccountNumber.Text = loggedinuserid;
lblAccountNumber.Visible = true;
//DataTable dtCurrency = (DataTable)Session["toCurrency"];
if (!IsPostBack)
{
myHKeInvestCode.addSessionVariable(Session);
DataTable dtCurrency = (DataTable)Session["CurrencyData"];
foreach (DataRow row in dtCurrency.Rows)
{
ddlCurrency.Items.Add(row["currency"].ToString().Trim());
}
}
}
private string amountIsValid(string securityType, string code, string amount)
{
decimal number;
if (!decimal.TryParse(amount, out number) || number <= 0)
{
return("Invalid or missing dollar amount of " + securityType + " to buy.\nValue is '" + amount + "'.");
}
//SQL query to get quantity of bonds or unit trusts owned
//Querying over the security holding's details
string accountNumber = getAccountNumber();
string sql = "SELECT shares FROM SecurityHolding WHERE accountNumber = '" +
accountNumber + "' AND type = '" +
securityType + "' AND code = '" +
code + "'";
DataTable qty = extData.getData(sql);
//Bonds or Unit trusts only
if (qty.Rows == null || qty.Rows.Count == 0)
{
}
else
{
int availSecurities = Convert.ToInt32(qty.Rows[0]["shares"]);
if (availSecurities > number)
{
return("Not enough securites to sell");
}
}
return("");
}
//Error checking on display of program
protected void AccountNumberChanged(object sender, EventArgs e)
{
string accountNumber = getAccountNumber();
string sqlAccount = "SELECT * FROM Account WHERE accountNumber = '" + accountNumber + "'";
string sqlClient = "SELECT * FROM Client WHERE accountNumber = '" + accountNumber + "'";
DataTable dtClient = myHKeInvestData.getData(sqlClient);
if (dtClient == null || dtClient.Rows.Count == 0)
{
//Error invalid sql
}
else if (dtClient.Rows.Count == 1)
{
}
else if (dtClient.Rows.Count == 2)
{
coAccount2.Visible = true;
coAccount3.Visible = true;
coAccount4.Visible = true;
}
}
//Error checking on display of program
protected void AccountNumberChanged(object sender, EventArgs e)
{
string accountNumber = AccountNumber.Text.Trim();
string sqlAccount = "SELECT * FROM Account WHERE accountNumber = '" + accountNumber + "'";
string sqlClient = "SELECT * FROM Client WHERE accountNumber = '" + accountNumber + "'";
DataTable dtClient = myHKeInvestData.getData(sqlClient);
if (dtClient == null || dtClient.Rows.Count == 0)
{
//Error invalid sql
}
else if (dtClient.Rows.Count == 1)
{
mainform.Visible = true;
passport.Visible = dtClient.Rows[0]["passportCountry"].ToString().Trim() != "" ? true : false;
}
else if (dtClient.Rows.Count == 2)
{
mainform.Visible = true;
coAccount2.Visible = true;
coAccount3.Visible = true;
coAccount4.Visible = true;
if (dtClient.Rows[0]["isPrimary"].ToString().Trim() == "Y")
{
passport.Visible = dtClient.Rows[0]["passportCountry"].ToString().Trim() != "" ? true : false;
copassport.Visible = dtClient.Rows[1]["passportCountry"].ToString().Trim() != "" ? true : false;
}
else
{
passport.Visible = dtClient.Rows[1]["passportCountry"].ToString().Trim() != "" ? true : false;
copassport.Visible = dtClient.Rows[0]["passportCountry"].ToString().Trim() != "" ? true : false;
}
}
}
protected void cvUserName_ServerValidate(object source, ServerValidateEventArgs args)
{
string username = UserName.Text.Trim();
string sql2 = "SELECT userName FROM Account WHERE userName = '******'";
HKeInvestData myHKeInvestData2 = new HKeInvestData();
DataTable dtUser = myHKeInvestData2.getData(sql2);
foreach (DataRow row in dtUser.Rows)
{
if (row != null)
{
args.IsValid = false;
cvUserName.ErrorMessage = "The user name has been used.";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string userName = Context.User.Identity.GetUserName();
string accountNumber = myHKeInvestCode.getUserAccountNumber(Session, userName);
if (!string.IsNullOrWhiteSpace(accountNumber))
{
lblAccountNumber.Text += accountNumber;
DataTable dtAccountInformation = myHKeInvestData.getData("SELECT balance FROM [Account] WHERE [accountNumber]='" + accountNumber + "'");
decimal balance = (decimal)dtAccountInformation.Rows[0]["balance"];
lblAccountBalance.Text += balance.ToString();
ViewState["accountBalance"] = balance;
}
}
}
private bool CheckClientRecord(string firstName, string lastName, string dateOfBirth, string email, string HKID, string accountNumber)
{
string isPrimary = "yes";
string sql = "select [firstName], [lastName], [dateOfBirth], [email], [HKIDPassportNumber] from [ClientTemp] where [accountNumber]='" + accountNumber + "' and [firstName]='" + firstName + "' and [lastName]='" + lastName + "' and [dateOfBirth]=CONVERT(date, '" + DateOfBirth.Text + "', 103) and [email]='" + email + "' and [HKIDPassportNumber]='" + HKID + "' and [isPrimary]='" + isPrimary + "'";
HKeInvestData myInvestData = new HKeInvestData();
DataTable dtClient = myInvestData.getData(sql);
if (dtClient == null || dtClient.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
private string GenerateNextKey(string lastname)
{
string lastletters = "";
if (lastname.Length == 1)
{
lastletters = lastname.Substring(0, 1) + lastname.Substring(0, 1);
}
else
{
lastletters = lastname.Substring(0, 2);
}
lastletters = lastletters.ToUpper();
string sql = "SELECT MAX(accountNumber) as max FROM Account WHERE AccountNumber LIKE " + "'" + lastletters + "%'";
DataTable dtClient = myHKeInvestData.getData(sql);
string accountNumber = "";
if (dtClient == null || dtClient.Rows.Count == 0)
{
accountNumber = lastletters + "00000001";
}
else
{
string prevAccountNumber = dtClient.Rows[0]["max"].ToString();
if (prevAccountNumber.Length != 10)
{
accountNumber = lastletters + "00000001";
}
else
{
int nextNumber = Int32.Parse(prevAccountNumber.Substring(2, 8));
nextNumber++;
string tempNumber = nextNumber.ToString();
accountNumber = lastletters;
for (int i = 8; i > tempNumber.Length; i--)
{
accountNumber += "0";
}
accountNumber += tempNumber;
}
}
return(accountNumber);
}
private DataTable getOrderDetails(string referenceNumber, out string type, out string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [UnitTrustOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "unit trust";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [UnitTrustOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "unit trust";
buyOrSell = "sell";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [StockOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "stock";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [StockOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "stock";
buyOrSell = "sell";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [BondOrderBuy] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "bond";
buyOrSell = "buy";
return(dt);
}
dt = myHKeInvestData.getData("SELECT * FROM [Order] o, [BondOrderSell] u WHERE o.orderReferenceNumber=u.orderReferenceNumber and o.orderReferenceNumber='" + referenceNumber + "'");
if (dt.Rows.Count == 1)
{
type = "bond";
buyOrSell = "sell";
return(dt);
}
type = "";
buyOrSell = "";
return(null);
}
private void Check_AlertStatus()
{
HKeInvestData myHKeInvestData = new HKeInvestData();
ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable dtAlert = myHKeInvestData.getData("SELECT * FROM [Alert]");
foreach (DataRow alert in dtAlert.Rows)
{
string accountNumber = alert.Field <string>("accountNumber");
string code = alert.Field <string>("code");
string type = alert.Field <string>("type");
string highOrLow = alert.Field <string>("highOrLow");
decimal value = alert.Field <decimal>("value");
string isSameSide = alert.Field <string>("isSameSide");
decimal currPrice = myExternalFunctions.getSecuritiesPrice(type, code);
if (((highOrLow == "high" && currPrice >= value) || (highOrLow == "low" && currPrice <= value)) && isSameSide == "no")
{
// send notification to the client and cancel the alert.
string sql = string.Format("DELETE FROM [Alert] WHERE accountNumber='{0}' AND code='{1}' AND type='{2}' AND highOrLow='{3}'",
accountNumber,
code,
type,
highOrLow);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
Send_Notification(accountNumber, type, code, highOrLow, currPrice);
}
else if (isSameSide == "yes" && ((highOrLow == "high" && currPrice < value) || (highOrLow == "low" && currPrice > value)))
{
string sql = string.Format("UPDATE [Alert] SET isSameSide='no' WHERE accountNumber='{0}' AND code='{1}' AND type='{2}' AND highOrLow='{3}'",
accountNumber,
code,
type,
highOrLow);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string loggedinuser = Context.User.Identity.GetUserName();
/*lbltest.Text = loggedinuser;
* lbltest.Visible = true;*/
string sql = "SELECT accountNumber FROM Account WHERE userName = '******'";
string loggedinuserid = "";
//execute sql command in database
DataTable loginuserid = myHKeInvestData.getData(sql);
foreach (DataRow row in loginuserid.Rows)
{
loggedinuserid = loggedinuserid + row["accountNumber"];
}
lblAccountNumber.Text = loggedinuserid;
lblAccountNumber.Visible = true;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string userName = Context.User.Identity.GetUserName();
string sql = "SELECT accountNumber FROM dbo.Account WHERE userName='******'";
DataTable dtAccountNumber = myHKeInvestData.getData(sql);
if (dtAccountNumber.Rows.Count == 1)
{
ViewState["accountNumber"] = dtAccountNumber.Rows[0].Field <string>("accountNumber");
}
else
{
accountNumber = "";
}
gvTrackGroup.Visible = false;
gvTrackIndividual.Visible = false;
}
accountNumber = (string)ViewState["accountNumber"];
}
private bool verifyClient(object sender, EventArgs e)
{
string sql = "";
string accountNumber = AccountNumber.Text.Trim();
string firstName = FirstName.Text.Trim();
string lastName = LastName.Text.Trim();
string DOB = formatDateToSQL(DateOfBirth.Text.Trim());
string ID = HKID.Text.Trim();
string email = Email.Text.Trim();
sql = "SELECT * FROM Client " +
"WHERE accountNumber = '" + accountNumber + "' " +
"AND firstName = '" + firstName + "' " +
"AND lastName = '" + lastName + "' " +
"AND dateOfBirth = '" + DOB + "' " +
"AND HKIDPassportNumber = '" + ID + "' " +
"AND email = '" + email + "' ";
DataTable dtClient = myHKeInvestData.getData(sql);
if (dtClient == null)
{
ErrorMessage.Text = "SQL error occurred. Beware of SQL injection.";
return(false);
} // If the DataSet is null, a SQL error occurred.
// If no result is returned by the SQL statement, then display a message.
if (dtClient.Rows.Count == 0)
{
ErrorMessage.Text = "Information provided is inaccurate or your in person application has not been completed.";
return(false);
}
else
{
return(true);
}
}
// Creating sql string for finding latest executed orders
protected string makeSQL(string type, string bOrs)
{
string accountNumber = (string)ViewState["accountNumber"];
string action = bOrs.ToLower();
string sql = "SELECT t.executeDate,o.orderReferenceNumber,o.dateOfSubmission FROM dbo.[Order] o,dbo.[" + type + "Order" + bOrs + "] " + action + ",dbo.[Transaction] t WHERE o.accountNumber='" + accountNumber + "' AND o.orderStatus = 'completed' AND o.orderReferenceNumber=" + action + ".orderReferenceNumber AND " + action + ".orderReferenceNumber=t.orderReferenceNumber AND NOT EXISTS (SELECT * FROM dbo.[Order] o1,dbo.[" + type + "Order" + bOrs + "] " + action + "1,dbo.[Transaction] t1 WHERE o1.accountNumber='" + accountNumber + "' AND o1.orderReferenceNumber=" + action + "1.orderReferenceNumber AND " + action + "1.orderReferenceNumber=t1.orderReferenceNumber AND t1.executeDate>t.executeDate);";
string targetORN = "";
string targetDate = "";
DataTable dt = myHKeInvestData.getData(sql);
if (dt == null)
{
return(null);
}
if (dt.Rows.Count != 0)
{
foreach (DataRow row in dt.Rows)
{
targetORN = Convert.ToString(row["orderReferenceNumber"]).Trim();
targetDate = Convert.ToString(row["executeDate"]).Trim();
}
}
sql = "SELECT '" + targetDate + "' as executeDate,'" + toLowerType(type) + "' as targetType, o.securityCode, t.executePrice,t.executeShares,o.dateOfSubmission FROM dbo.[Transaction] t, dbo.[Order] o WHERE o.orderReferenceNumber='" + targetORN + "' AND o.orderReferenceNumber=t.orderReferenceNumber;";
return(sql);
}
protected void cuvSecurityCodeInput_ServerValidate(object source, ServerValidateEventArgs args)
{
rfvAlertType.Validate();
rfvSecurityTypeInput.Validate();
rfvSecurityCodeInput.Validate();
rfvAlertValue.Validate();
revSecurityCodeInput.Validate();
revAlertValue.Validate();
if (rfvAlertType.IsValid && rfvSecurityTypeInput.IsValid && rfvSecurityCodeInput.IsValid && rfvAlertValue.IsValid &&
revSecurityCodeInput.IsValid && revAlertValue.IsValid)
{
string clientUserName = Context.User.Identity.GetUserName();
string securityType = rblSecurityTypeInput.SelectedValue.ToString();
string securityCode = tbxSecurityCodeInput.Text.ToString();
float alertValue = float.Parse(tbxAlertValue.Text.ToString());
string sql = "";
// Select "*" because we are just making sure the client has the security
sql = "SELECT * "
+ "FROM Account AS a FULL JOIN SecurityHolding AS sh ON a.accountNumber=sh.accountNumber "
+ "WHERE userName='******' AND code='" + securityCode + "'";
// Create a DataTable to hold our query to the local database
DataTable clientSecurity = myHKeInvestData.getData(sql);
if (clientSecurity == null || clientSecurity.Rows.Count == 0)
{
cuvSecurityCodeInput.ErrorMessage = "Client has no security of this type with this code.";
args.IsValid = false;
}
else
{
DataTable desiredSecurity = myExternalFunctions.getSecuritiesByCode(securityType, securityCode);
if (desiredSecurity == null)
{
cuvSecurityCodeInput.ErrorMessage = "This security does not exist.";
args.IsValid = false;
}
else
{
float price;
if (securityType == "stock")
{
price = float.Parse(desiredSecurity.Rows[0]["close"].ToString());
}
else
{
price = float.Parse(desiredSecurity.Rows[0]["price"].ToString());
}
if (rblAlertType.SelectedValue == "High")
{
if (price > alertValue)
{
cuvSecurityCodeInput.ErrorMessage = "The price is already higher than the alert value.";
args.IsValid = false;
}
}
else // Alert type should only either be "High" or "Low"
{
if (price < alertValue)
{
cuvSecurityCodeInput.ErrorMessage = "The price is already lower than the alert value.";
args.IsValid = false;
}
}
}
}
}
}
private Boolean isValidClient()
{
DataTable clientDt = theData.getData("Select firstName, lastName, dateOfBirth, email, HKIDPassportNumber, accountNumber, building, street, district, homePhone, homeFax, businessPhone, mobilePhone, countryOfCitizenship, countryOfLegalResidence, passportCountryOfIssue, employmentStatus, occupation, yearsWithEmployer, employerName, employerPhone, natureOfBusiness, employedByFinanceInst, memberPublicTradedInst, primarySourceOfFunds, investmentObjective, investmentKnowledge, annualIncome, approxLiquidNetWorth, sweep from ClientTemp where accountNumber='" + AccountNumber.Text.Trim() + "';");
String colName = "";
String colValue = "";
Boolean checkFailed = false;
String MyString = DateOfBirth.Text.Trim(); // get value from text field
DateTime MyDateTime = new DateTime();
//MyDateTime = DateTime.Parse(MyString);
MessageBox.Show(MyString);
//String newDateOfBirth = MyDateTime.ToString("MM/dd/yyyy"); //add MyString_new
MyDateTime = DateTime.ParseExact(DateOfBirth.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
String theBdate = MyDateTime.ToShortDateString();
foreach (DataColumn col in clientDt.Columns)
{
colName = col.ColumnName.Trim();
colValue = clientDt.Rows[0][col].ToString().Trim();
switch (colName)
{
case ("firstName"):
if (!colValue.Equals(FirstName.Text.Trim()))
{
checkFailed = true; MessageBox.Show("firstName failed");
}
break;
case ("lastName"):
if (!colValue.Equals(LastName.Text.Trim()))
{
checkFailed = true; MessageBox.Show("lastName failed");
}
break;
case ("dateOfBirth"):
MyDateTime = DateTime.Parse(colValue);
colValue = MyDateTime.ToShortDateString();
if (!colValue.Equals(theBdate.Trim()))
{
checkFailed = true;
MessageBox.Show("dateOfBirth failed..." + colValue + "...." + theBdate);
}
break;
case ("email"):
if (!colValue.Equals(Email.Text.Trim()))
{
checkFailed = true; MessageBox.Show("email failed");
}
break;
case ("HKIDPassportNumber"):
if (!colValue.Equals(HKID.Text.Trim()))
{
checkFailed = true; MessageBox.Show("HKIDPass failed");
}
break;
case ("accountNumber"):
if (!colValue.Equals(AccountNumber.Text.Trim()))
{
checkFailed = true; MessageBox.Show("accountNum failed");
}
break;
case ("building"):
break;
case ("street"):
if (!colValue.Equals(Street.Text.Trim()))
{
checkFailed = true; MessageBox.Show("street failed");
}
break;
case ("district"):
if (!colValue.Equals(District.Text.Trim()))
{
checkFailed = true; MessageBox.Show("districtText failed");
}
break;
case ("homePhone"):
if (!colValue.Equals(HomePhone.Text.Trim()))
{
checkFailed = true; MessageBox.Show("homePhone failed");
}
break;
case ("homeFax"):
if (!colValue.Equals(HomeFax.Text.Trim()))
{
checkFailed = true; MessageBox.Show("homeFax failed");
}
break;
case ("businessPhone"):
if (!colValue.Equals(BusinessPhone.Text.Trim()))
{
checkFailed = true; MessageBox.Show("businessPhone failed");
}
break;
case ("mobilePhone"):
if (!colValue.Equals(MobilePhone.Text.Trim()))
{
checkFailed = true; MessageBox.Show("mobilePhone failed");
}
break;
case ("countryOfCitizenship"):
if (!colValue.Equals(CountryOfCitizenship.Text.Trim()))
{
checkFailed = true;
MessageBox.Show("countryOfCitizen failed" + colValue + "..." + CountryOfCitizenship.Text.ToString());
MessageBox.Show(colValue.Equals(CountryOfCitizenship.Text.ToString().Trim()).ToString());
}
break;
case ("countryOfLegalResidence"):
if (!colValue.Equals(CountryOfLegalResidence.Text.Trim()))
{
checkFailed = true; MessageBox.Show("countryLegalRes failed");
}
break;
case ("passportCountryOfIssue"):
if (!colValue.Equals(PassportCountryOfIssue.Text.Trim()))
{
checkFailed = true; MessageBox.Show("passCountryOfIssue failed");
}
break;
case ("employmentStatus"):
if (!colValue.Equals(ddlEmploymentStatus.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("employmentStatus failed");
}
break;
case ("occupation"):
if (!colValue.Equals(Occupation.Text.Trim()))
{
checkFailed = true; MessageBox.Show("occupation failed");
}
break;
case ("yearsWithEmployer"):
if (!colValue.Equals(YearsWithEmployer.Text.Trim()))
{
checkFailed = true; MessageBox.Show("yearsWithEmployer failed");
}
break;
case ("employerName"):
if (!colValue.Equals(EmployerName.Text.Trim()))
{
checkFailed = true; MessageBox.Show("employerName failed");
}
break;
case ("employerPhone"):
if (!colValue.Equals(EmployerPhone.Text.Trim()))
{
checkFailed = true; MessageBox.Show("employerPhone failed");
}
break;
case ("natureOfBusiness"):
if (!colValue.Equals(NatureOfBusiness.Text.Trim()))
{
checkFailed = true; MessageBox.Show("natureOfBusiness failed");
}
break;
case ("employedByFinanceInst"):
if (!colValue.Equals(ddlEmployedByFinanceInst.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("emploeyedByFinanceInst failed");
}
break;
case ("memberPublicTradedInst"):
if (!colValue.Equals(ddlMemberPublicTradedInst.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("memberPublic failed");
}
break;
case ("primarySourceOfFunds"):
if (!colValue.Equals(ddlPrimarySourceOfFunds.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("primarySourceOfFunds failed..." + colValue + "..." + ddlPrimarySourceOfFunds.ToString().Trim());
}
break;
case ("investmentObjective"):
if (!colValue.Equals(ddlInvestmentObjective.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("investmentObjective failed");
}
break;
case ("annualIncome"):
if (!colValue.Equals(ddlAnnualIncome.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("annualIncome failed");
}
break;
case ("approxLiquidNetWorth"):
if (!colValue.Equals(ddlApproxLiquidNetWorth.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("approxLiquidNetWorth failed");
}
break;
case ("sweep"):
if (!colValue.Equals(ddlSweep.SelectedValue.ToString().Trim()))
{
checkFailed = true; MessageBox.Show("sweep failed");
}
break;
default:
MessageBox.Show("Something weird happened");
break;
}
if (checkFailed == true)
{
return(false);
}
}
return(true);
}
protected void ddlSecurityType_SelectedIndexChanged(object sender, EventArgs e)
{
// Reset visbility of controls and initialize values.
lblResultMessage.Visible = false;
ddlCurrency.Visible = false;
gvSecurityHolding.Visible = false;
ddlCurrency.SelectedIndex = 0;
string sql = "";
//NameValueCollection nvc = Request.Form;
// *******************************************************************
// TODO: Set the account number and security type from the web page. *
// *******************************************************************
string accountNumber = txtAccountNumber.Text.Trim(); // Set the account number from a web form control!
string securityType = ddlSecurityType.SelectedValue; // Set the securityType from a web form control!
//System.Console.WriteLine(accountNumber + " " + securityType);
// Check if an account number has been specified.
if (accountNumber == "")
{
lblResultMessage.Text = "Please specify an account number.";
lblResultMessage.Visible = true;
ddlSecurityType.SelectedIndex = 0;
return;
}
// No action when the first item in the DropDownList is selected.
if (securityType == "0")
{
return;
}
// *****************************************************************************************
// TODO: Construct the SQL statement to retrieve the first and last name of the client(s). *
// *****************************************************************************************
//Sql statement requires matching stock type
//Also need to watch for sql injection problems
sql = "SELECT firstName, lastName FROM Client, SecurityHolding " +
"WHERE Client.accountNumber = '" + accountNumber + "' " +
"AND Client.accountNumber = SecurityHolding.accountNumber " +
"AND SecurityHolding.type = '" + securityType + "'"; // Complete the SQL statement.
DataTable dtClient = myHKeInvestData.getData(sql);
if (dtClient == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
// If no result is returned by the SQL statement, then display a message.
if (dtClient.Rows.Count == 0)
{
lblResultMessage.Text = "No such account number.";
lblResultMessage.Visible = true;
lblClientName.Visible = false;
gvSecurityHolding.Visible = false;
return;
}
// Show the client name(s) on the web page.
string clientName = "Client(s): ";
int i = 1;
foreach (DataRow row in dtClient.Rows)
{
clientName = clientName + row["lastName"] + ", " + row["firstName"];
if (dtClient.Rows.Count != i)
{
clientName = clientName + "and ";
}
i = i + 1;
}
lblClientName.Text = clientName;
lblClientName.Visible = true;
// *****************************************************************************************************************************
// TODO: Construct the SQL select statement to get the code, name, shares and base of the security holdings of a specific type *
// in an account. The select statement should also return three additonal columns -- price, value and convertedValue -- *
// whose values are not actually in the database, but are set to the constant 0.00 by the select statement. (HINT: see *
// http://stackoverflow.com/questions/2504163/include-in-select-a-column-that-isnt-actually-in-the-database.) *
// *****************************************************************************************************************************
sql = "SELECT code, name, shares, base, " +
"cast('0.00' AS numeric(12,2)) AS price, " +
"cast('0.00' AS numeric(12,2)) AS value, " +
"cast('0.00' AS numeric(12,2)) AS convertedValue " +
"FROM SecurityHolding " +
"WHERE accountNumber = '" + accountNumber + "'" +
" AND type = '" + securityType + "'"; // Complete the SQL statement.
DataTable dtSecurityHolding = myHKeInvestData.getData(sql);
if (dtSecurityHolding == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
// If no result is returned, then display a message that the account does not hold this type of security.
if (dtSecurityHolding.Rows.Count == 0)
{
lblResultMessage.Text = "No " + securityType + "s held in this account.";
lblResultMessage.Visible = true;
gvSecurityHolding.Visible = false;
return;
}
// For each security in the result, get its current price from an external system, calculate the total value
// of the security and change the current price and total value columns of the security in the result.
int dtRow = 0;
foreach (DataRow row in dtSecurityHolding.Rows)
{
string securityCode = row["code"].ToString();
decimal shares = Convert.ToDecimal(row["shares"]);
decimal price = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
decimal value = Math.Round(shares * price - (decimal).005, 2);
dtSecurityHolding.Rows[dtRow]["price"] = price;
dtSecurityHolding.Rows[dtRow]["value"] = value;
dtRow = dtRow + 1;
}
// Set the initial sort expression and sort direction for sorting the GridView in ViewState.
ViewState["SortExpression"] = "name";
ViewState["SortDirection"] = "ASC";
// Bind the GridView to the DataTable.
gvSecurityHolding.DataSource = dtSecurityHolding;
gvSecurityHolding.DataBind();
// Set the visibility of controls and GridView data.
gvSecurityHolding.Visible = true;
ddlCurrency.Visible = true;
gvSecurityHolding.Columns[myHKeInvestCode.getColumnIndexByName(gvSecurityHolding, "convertedValue")].Visible = false;
}
protected void ddlSecurityType_SelectedIndexChanged(object sender, EventArgs e)
{
// Reset visbility of controls and initialize values.
//BUG FIXED HERE: There was a rea line under 'lblResultMessage', which means no such variable specified: typo in the aspx page!
lblResultMessage.Visible = false;
ddlCurrency.Visible = false;
gvSecurityHolding.Visible = false;
ddlCurrency.SelectedIndex = 0;
string sql = "";
// *******************************************************************
// TODO: Set the account number and security type from the web page. *
// *******************************************************************
string accountNumber = txtAccountNumber.Text.Trim(); // Set the account number from a web form control!
string securityType = ddlSecurityType.SelectedValue.Trim(); // Set the securityType from a web form control!
// Check if an account number has been specified.
if (accountNumber == "")
{
lblResultMessage.Text = "Please specify an account number.";
lblResultMessage.Visible = true;
ddlSecurityType.SelectedIndex = 0;
return;
}
// No action when the first item in the DropDownList is selected.
if (securityType == "0")
{
return;
}
// *****************************************************************************************
// TODO: Construct the SQL statement to retrieve the first and last name of the client(s). *
// *****************************************************************************************
sql = "select [firstName], [lastName] from [Client] where [accountNumber]='" + accountNumber + "'"; // Complete the SQL statement.
//Explanation for this task:
//1. An account can be held by many clients.
//2. An account can invest in multiple ways.
//3. An account can invest in stocks from multiple companies.
//4. No relationship between client and investment(can not specify which investment is done by which of clients holding one account."
DataTable dtClient = myHKeInvestData.getData(sql);
if (dtClient == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
// If no result is returned by the SQL statement, then display a message.
if (dtClient.Rows.Count == 0)
{
lblResultMessage.Text = "No such account number.";
lblResultMessage.Visible = true;
lblClientName.Visible = false;
gvSecurityHolding.Visible = false;
return;
}
// Show the client name(s) on the web page.
string clientName = "Client(s): ";
int i = 1;
foreach (DataRow row in dtClient.Rows)
{
clientName = clientName + row["lastName"] + ", " + row["firstName"];
if (dtClient.Rows.Count != i)
{
clientName = clientName + "and ";
}
i = i + 1;
}
lblClientName.Text = clientName;
lblClientName.Visible = true;
// *****************************************************************************************************************************
// TODO: Construct the SQL select statement to get the code, name, shares and base of the security holdings of a specific type *
// in an account. The select statement should also return three additonal columns -- price, value and convertedValue -- *
// whose values are not actually in the database, but are set to the constant 0.00 by the select statement. (HINT: see *
// http://stackoverflow.com/questions/2504163/include-in-select-a-column-that-isnt-actually-in-the-database.) *
// *****************************************************************************************************************************
sql = "select [code], [name], [shares], [base], '0.00' AS [price], '0.00' AS [value], '0.00' AS [convertedValue] from [SecurityHolding] where [SecurityHolding].[accountNumber]='" + accountNumber + "' and [type]='" + securityType + "'"; // Complete the SQL statement.
DataTable dtSecurityHolding = myHKeInvestData.getData(sql);
if (dtSecurityHolding == null)
{
return;
} // If the DataSet is null, a SQL error occurred.
// If no result is returned, then display a message that the account does not hold this type of security.
if (dtSecurityHolding.Rows.Count == 0)
{
lblResultMessage.Text = "No " + securityType + "s held in this account.";
lblResultMessage.Visible = true;
gvSecurityHolding.Visible = false;
return;
}
// For each security in the result, get its current price from an external system, calculate the total value
// of the security and change the current price and total value columns of the security in the result.
int dtRow = 0;
foreach (DataRow row in dtSecurityHolding.Rows)
{
string securityCode = row["code"].ToString();
decimal shares = Convert.ToDecimal(row["shares"]);
decimal price = myExternalFunctions.getSecuritiesPrice(securityType, securityCode);
decimal value = Math.Round(shares * price - (decimal).005, 2);
dtSecurityHolding.Rows[dtRow]["price"] = price;
dtSecurityHolding.Rows[dtRow]["value"] = value;
dtRow = dtRow + 1;
}
// Set the initial sort expression and sort direction for sorting the GridView in ViewState.
ViewState["SortExpression"] = "name";
ViewState["SortDirection"] = "ASC";
// Bind the GridView to the DataTable.
gvSecurityHolding.DataSource = dtSecurityHolding;
gvSecurityHolding.DataBind();
// Set the visibility of controls and GridView data.
gvSecurityHolding.Visible = true;
ddlCurrency.Visible = true;
gvSecurityHolding.Columns[myHKeInvestCode.getColumnIndexByName(gvSecurityHolding, "convertedValue")].Visible = false;
}
protected void CreateAccount(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//AddAccountRecord
string generateAccNum = "";
//SELECT accountNumber FROM Account WHERE accountNumber LIKE 'AA%'
string accEng = "";
if (LastName.Text.Length == 1)
{
accEng = accEng + LastName.Text.ToUpper() + LastName.Text.ToUpper();
}
else if (LastName.Text.Length > 1)
{
accEng = accEng + LastName.Text.Substring(0, 2).ToUpper();
}
int acDigit = 1;
//string accDigit = "";
string precedingzeros = "";
int precedzeros = 8 - acDigit.ToString().Length;
DataTable samelastname = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE accountNumber LIKE '" + accEng + "%' ORDER BY accountNumber");
if (samelastname.Rows.Count != 0)
{
foreach (DataRow row in samelastname.Rows)
{
//for each accNum with same last name, compare the 8 digit and returns the one havn't used
int ifDigitEq = 0;
string accindatabase = "" + row["accountNumber"];
string compareAccDigit = accindatabase.Substring(2, 8);
string precedzero = "";
for (int i = 0; i < precedzeros; i++)
{
precedzero = precedzero + "0";
}
string comAccDigit = precedingzeros + acDigit.ToString();
ifDigitEq = compareAccDigit.CompareTo(comAccDigit);
Console.WriteLine(compareAccDigit);
if (ifDigitEq != 1 || ifDigitEq != -1)
{
acDigit = acDigit + 1;
}
}
}
for (int i = 0; i < precedzeros; i++)
{
precedingzeros = precedingzeros + "0";
}
generateAccNum = accEng + precedingzeros + acDigit.ToString();
//inserting data into table Account
SqlTransaction tranAcc = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Account (accountNumber, accountType, balance, sweepFreeCredit) VALUES ('" + generateAccNum + "', '" + ddlAccType.SelectedValue + "', " + deposit.Text + ", '" + ddlsweep.SelectedValue + "')", tranAcc);
myHKeInvestData.commitTransaction(tranAcc);
//inserting data into table Client
SqlTransaction tranCli = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Client (accountNumber, title, lastName, firstName, dateOfBirth, email, building, street, district, homePhone, homeFax, businessPhone, mobile, citizenship, legalResidence, HKIDPassportNumber, passportCountryOfIssue) VALUES ('" + generateAccNum + "', '" + ddlTitle.SelectedValue + "', '" + LastName.Text + "', '" + FirstName.Text + "', '" + DateOfBirth.Text + "', '" + Email.Text + "', '" + Building.Text + "', '" + Street.Text + "', '" + District.Text + "', " + HomePhone.Text + ", " + HomeFax.Text + ", " + BusinessPhone.Text + ", " + MobilePhone.Text + ", '" + Citizenship.Text + "', '" + Residence.Text + "', '" + HKID.Text + "', '" + PassportCountry.Text + "')", tranCli);
myHKeInvestData.commitTransaction(tranCli);
//inserting data into table Employment
if (ddlEmployed.SelectedValue != "employed")
{
SqlTransaction tranEmpl = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Employment (accountNumber, status) VALUES ('" + generateAccNum + "', '" + ddlEmployed.SelectedValue + "')", tranEmpl);
myHKeInvestData.commitTransaction(tranEmpl);
}
else
{
SqlTransaction tranEmpl = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Employment (accountNumber, status, specificOccupation, yearsWithEmployer, employerName, employerPhone, businessNature) VALUES ('" + generateAccNum + "', '" + ddlEmployed.SelectedValue + "', '" + specificOccupation.Text + "', " + yearEmploy.Text + ", '" + employerName.Text + "', " + employerPhone.Text + ", '" + busiNature.Text + "')", tranEmpl);
myHKeInvestData.commitTransaction(tranEmpl);
}
//inserting data into table Investment
SqlTransaction tranInv = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO RegulatoryDisclosures (accountNumber, employedByFinancialInstitution, publiclyTradedCompany, primarySourceOfFunds, otherSource) VALUES ('" + generateAccNum + "', '" + ddlemployedByFinancialInstitution.SelectedValue + "', '" + ddlDirector.SelectedValue + "', '" + ddlPrimarySource.SelectedValue + "', '" + otherPrimarySource.Text + "')", tranInv);
myHKeInvestData.commitTransaction(tranInv);
//inserting data into table Regulatory Disclosures
SqlTransaction tranReg = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Investment (accountNumber, objective, knowledge, experience, annualIncome, liquidNetWorth) VALUES ('" + generateAccNum + "', '" + ddlInvestmentObjective.SelectedValue + "', '" + ddlInvestmentKnowledge.SelectedValue + "', '" + ddlInvestmentExperience.SelectedValue + "', '" + ddlAnnualIncome.SelectedValue + "', '" + ddlNetWorth.SelectedValue + "')", tranReg);
myHKeInvestData.commitTransaction(tranReg);
//inserting data into table Security Holdings
/*SqlTransaction tranSec = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("", tranSec);
* myHKeInvestData.commitTransaction(tranSec);*/
//INSERT INTO Account (accountNumber, accountType, balance) VALUES ('HI00000001', 'individual', 1000)
//generate a new account number for new added client
/*string generateAccNum= "";
* SqlTransaction trans = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("update [Account] set [accountNumber]='" + generateAccNum + "' WHERE [HKIDPassportNumber] = '" + HKID.Text + "'", trans);
* myHKeInvestData.commitTransaction(trans);*/
//}
}
}
