protected void buy(string sql)
{
SqlTransaction trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
protected void ExecuteDepositWithdraw_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
string Acctsql = "select bankAccountNumber, routingNumber from Account where accountNumber='" + getAccountNumber() + "' and bankAccountNumber IS NOT NULL";
DataTable acct = myHKeInvestData.getData(Acctsql);
if (acct == null || acct.Rows.Count == 0)
{
IncorrectAmount.Text = "Sorry you don't have a bank account or routing number on file";
}
else if (RadioButtonList1.SelectedValue == "Withdraw")
{
decimal withdrawal = decimal.Parse(Withdraw.Text);
string sql = "select balance from Account where accountNumber='" + getAccountNumber() + "'";
DataTable dtSecurityHolding = myHKeInvestData.getData(sql);
decimal balance = decimal.Parse(dtSecurityHolding.Rows[0]["balance"].ToString());
if (dtSecurityHolding == null || dtSecurityHolding.Rows.Count == 0)
{
IncorrectAmount.Text = "A sql error occurred or your balance doesn't exist";
}
//Withdraw money cannot be greater than what's in the account
else if (withdrawal > balance)
{
// money
IncorrectAmount.Text = "Cannot withdraw more money than presently in your account";
}
else
{
//Withdraw money
string depositSql = "update Account set balance=" + (balance - withdrawal) + " where accountNumber='" + getAccountNumber() + "'";
SqlTransaction trans = extData.beginTransaction();
extData.setData(depositSql, trans);
extData.commitTransaction(trans);
}
}
else
{
decimal deposit = decimal.Parse(Deposit.Text);
string sql = "select balance from Account where accountNumber='" + getAccountNumber() + "'";
DataTable dtSecurityHolding = myHKeInvestData.getData(sql);
decimal balance = decimal.Parse(dtSecurityHolding.Rows[0]["balance"].ToString());
if (dtSecurityHolding == null || dtSecurityHolding.Rows.Count == 0)
{
IncorrectAmount.Text = "A sql error occurred or your balance doesn't exist";
}
//Deposit money
string depositSql = "update Account set balance=" + (balance + deposit) + " where accountNumber='" + getAccountNumber() + "'";
SqlTransaction trans = extData.beginTransaction();
extData.setData(depositSql, trans);
extData.commitTransaction(trans);
}
}
}
protected void btnAdd_onClick(object sender, EventArgs e)
{
lblAddErrorMessage.Visible = false;
// Check if the input is valid
if (tbSecurityCode.Text.Trim().Equals("") || ddlFavoriteType.SelectedValue.Equals("0"))
{
lblAddErrorMessage.Text = "Invalid Input.";
lblAddErrorMessage.Visible = true;
return;
}
string code = tbSecurityCode.Text.Trim();
string type = ddlFavoriteType.SelectedValue.Trim();
DataTable dtTest = myExternalFunctions.getSecuritiesByCode(type, code);
// Check if such security exists
if (dtTest == null)
{
lblAddErrorMessage.Text = "No such security.";
lblAddErrorMessage.Visible = true;
return;
}
else
{
string sql;
// Check if it is already exists in the database
sql = "SELECT * FROM dbo.[FavoriteSecurities] f WHERE f.accountNumber='" + accountNumber + "' AND f.[type]='" + type + "' AND f.code ='" + code + "';";
DataTable d = myHKeInvestData.getData(sql);
if (d == null)
{
return; // sql error
}
if (d.Rows.Count != 0)
{
lblAddErrorMessage.Text = "Security already added.";
lblAddErrorMessage.Visible = true;
return;
}
// Now we are safe to add the record
// construct the sql for inserting record
sql = string.Format("INSERT INTO dbo.[FavoriteSecurities] VALUES ('{0}','{1}','{2}');", accountNumber, type, code);
var myTrans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
lblAddErrorMessage.Text = "Successfully added.";
lblAddErrorMessage.Visible = true;
}
}
private void UpdateAccountUserName(string accountNumber, string userName)
{
HKeInvestData myInvestData = new HKeInvestData();
string sql = "update [AccountTemp] set [userName]='" + userName + "' where [accountNumber]='" + accountNumber + "'";
SqlTransaction trans = myInvestData.beginTransaction();
myInvestData.setData(sql, trans);
myInvestData.commitTransaction(trans);
}
private void Update_OrderStatus(string referenceNumber, string status, decimal serviceFee)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string sql = string.Format("UPDATE [Order] SET orderStatus='{0}', serviceFee={1} WHERE orderReferenceNumber='{2}'", status, serviceFee, referenceNumber);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
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);
}
}
}
private void AddUserName(string userName, string accountNumber)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
SqlTransaction trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("update[Account] set[userName] = '" +
userName + "' " + "where [accountNumber]= '" + accountNumber + "'", trans);
myHKeInvestData.commitTransaction(trans);
}
protected void CreateAccount_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
string sql = "";
// 1. generate a unique account number
string lastName = LastNamePrimary.Text.Trim().ToUpper();
if (lastName.Length == 1)
{
lastName = lastName + lastName;
}
else
{
lastName = string.Concat(lastName[0], lastName[1]);
}
sql = string.Format("SELECT COUNT(*) FROM dbo.Account WHERE accountNumber like '{0}%'", lastName);
decimal newNumber = myHKeInvestData.getAggregateValue(sql) + 1;
string newAccountNumber = lastName + newNumber.ToString("00000000");
string accountType = AccountType.SelectedValue;
var myTrans = myHKeInvestData.beginTransaction();
sql = InsertAccount(newAccountNumber, accountType);
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
// 2. insert client information into client table
// 2.1 insert primary account holder's information
InsertPrimaryAccountHolder(newAccountNumber, true);
// 2.2 insert co-account holder's information (if any)
if (accountType != "individual")
{
InsertCoAccountHolder(newAccountNumber, false);
}
Response.Redirect("../Default.aspx");
}
private void Update_SecurityHolding(DataTable dtOrderDetails, string type, string accountNumber, decimal totalShares, string securityBase, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string sql = Get_UpdateSql(dtOrderDetails, type, accountNumber, totalShares, securityBase, buyOrSell);
if (sql == null)
{
return;
}
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
protected void CreateUser_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (verifyClient(sender, e))
{
}
var manager = Context.GetOwinContext().GetUserManager <ApplicationUserManager>();
var signInManager = Context.GetOwinContext().Get <ApplicationSignInManager>();
var user = new ApplicationUser()
{
UserName = UserName.Text, Email = Email.Text
};
IdentityResult result = manager.Create(user, Password.Text);
if (result.Succeeded)
{
// For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771
//string code = manager.GenerateEmailConfirmationToken(user.Id);
//string callbackUrl = IdentityHelper.GetUserConfirmationRedirectUrl(code, user.Id, Request);
//manager.SendEmail(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>.");
IdentityResult roleResult = manager.AddToRole(user.Id, "Client");
if (!roleResult.Succeeded)
{
ErrorMessage.Text = roleResult.Errors.FirstOrDefault();
}
SqlTransaction trans = myHKeInvestData.beginTransaction();
string sql = "UPDATE Account SET userName = '******' " +
"WHERE accountNumber = '" + AccountNumber.Text.Trim() + "'";
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
//sql = "SELECT userName FROM account WHERE accountNumber = '" + AccountNumber.Text.Trim() + "'";
//DataTable temp = myHKeInvestData.getData(sql);
signInManager.SignIn(user, isPersistent: false, rememberBrowser: false);
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
else
{
ErrorMessage.Text = result.Errors.FirstOrDefault();
}
}
}
protected void Register_Click(object sender, EventArgs e)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
SqlTransaction trans = myHKeInvestData.beginTransaction();
DateTime MyDateTime = new DateTime();
MyDateTime = DateTime.ParseExact(DateOfBirth.Text.Trim(), "MM/dd/yyyy", CultureInfo.InvariantCulture);
String DOB = MyDateTime.ToShortDateString();
myHKeInvestData.setData("insert into [Account] ([accountNumber], [accountType],[balance]) values ('" + AccountNumber.Text.Trim() + "', '" + ddlAccountType.SelectedValue.ToString().Trim() + "', '" + balance.Text.Trim() + "' )", trans);
myHKeInvestData.setData("insert into [Client] ([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]) values ('"
+ FirstName.Text.Trim() + "', '" + LastName.Text.Trim() + "', '" + DOB + "', '" + Email.Text.Trim() + "', '" + HKID.Text.Trim() + "', '" + AccountNumber.Text.Trim() + "', '" + Building.Text.Trim() + "', '" + Street.Text.Trim() + "', '" + District.Text.Trim() + "', '" + HomePhone.Text.Trim() + "', '" + HomeFax.Text.Trim() + "', '" + BusinessPhone.Text.Trim() + "', '" + MobilePhone.Text.Trim() + "', '" + CountryOfCitizenship.Text.Trim() + "', '" + CountryOfLegalResidence.Text.Trim() + "', '" + PassportCountryOfIssue.Text.Trim() + "', '" + ddlEmploymentStatus.SelectedValue.ToString().Trim() + "', '" + Occupation.Text.Trim() + "', '" + YearsWithEmployer.Text.Trim() + "', '" + EmployerName.Text.Trim() + "', '" + EmployerPhone.Text.Trim() + "', '" + NatureOfBusiness.Text.Trim() + "', '" + ddlEmployedByFinanceInst.SelectedValue.ToString().Trim() + "', '" + ddlMemberPublicTradedInst.SelectedValue.ToString().Trim() + "', '" + ddlPrimarySourceOfFunds.SelectedValue.ToString().Trim() + "', '" + ddlInvestmentObjective.SelectedValue.ToString().Trim() + "', '" + ddlInvestmentKnowledge.SelectedValue.ToString().Trim() + "', '" + ddlAnnualIncome.SelectedValue.ToString().Trim() + "', '" + ddlApproxLiquidNetWorth.SelectedValue.ToString().Trim() + "', '" + ddlSweep.SelectedValue.ToString().Trim() + "')", trans);
myHKeInvestData.commitTransaction(trans);
}
private decimal Update_AccountBalance(string accountNumber, decimal balance, decimal serviceFee, decimal totalPrice, string buyOrSell)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
decimal newBalance = balance - serviceFee;
if (buyOrSell == "buy")
{
newBalance = newBalance - totalPrice;
}
else if (buyOrSell == "sell")
{
newBalance = newBalance + totalPrice;
}
string sql = string.Format("UPDATE [Account] SET balance={0} WHERE accountNumber='{1}'", newBalance, accountNumber);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
return(newBalance);
}
protected void CreateUser_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
string sql = "SELECT a.userName FROM dbo.Client AS c, dbo.Account AS a WHERE c.accountNumber=a.accountNumber and RTRIM(c.firstName)='" + FirstName.Text.Trim() + "' and " +
"RTRIM(c.lastName)='" + LastName.Text.Trim() + "' and " +
"RTRIM(c.accountNumber)='" + AccountNumber.Text.Trim() + "' and " +
"RTRIM(c.HKIDPassportNumber)='" + HKID.Text.Trim() + "' and " +
"RTRIM(c.dateOfBirth)=CONVERT(date, '" + DateOfBirth.Text.Trim() + "', 103) and " +
"RTRIM(c.email)='" + Email.Text.Trim() + "' and " +
"c.isPrimary=(1)";
DataTable account = myHKeInvestData.getData(sql);
if (account.Rows.Count != 1)
{
ErrorMessage.Text = "user information doesn't match the account";
return;
}
if (!string.IsNullOrWhiteSpace(account.Rows[0].Field <string>("userName")))
{
ErrorMessage.Text = "account already exists";
return;
}
var manager = Context.GetOwinContext().GetUserManager <ApplicationUserManager>();
var signInManager = Context.GetOwinContext().Get <ApplicationSignInManager>();
var user = new ApplicationUser()
{
UserName = UserName.Text, Email = Email.Text
};
IdentityResult result = manager.Create(user, Password.Text);
if (result.Succeeded)
{
result = manager.AddToRole(user.Id, "Client");
if (result.Succeeded)
{
var myTrans = myHKeInvestData.beginTransaction();
sql = "UPDATE dbo.Account SET userName='******' WHERE accountNumber='" + AccountNumber.Text.Trim() + "'";
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
// For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771
string code = manager.GenerateEmailConfirmationToken(user.Id);
string callbackUrl = IdentityHelper.GetUserConfirmationRedirectUrl(code, user.Id, Request);
try
{
sendEmail(Email.Text.Trim(), callbackUrl);
signInManager.SignIn(user, isPersistent: false, rememberBrowser: false);
}
catch (Exception)
{
manager.Delete(user);
myTrans = myHKeInvestData.beginTransaction();
sql = "UPDATE dbo.Account SET userName='' WHERE accountNumber='" + AccountNumber.Text.Trim() + "'";
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
}
// manager.SendEmail(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>.");
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
else
{
ErrorMessage.Text = result.Errors.FirstOrDefault();
}
}
else
{
ErrorMessage.Text = result.Errors.FirstOrDefault();
}
}
protected void ExecuteOrderClick(object sender, EventArgs e)
{
if (Page.IsValid)
{
string varSecurityType = SecurityType.SelectedValue.ToString().Trim().ToLower();
string varTransactionType = TransactionType.SelectedValue.ToString().Trim().ToLower();
string accountNumber = getAccountNumber();
string name = "";
if (varSecurityType.Equals("stock"))
{
//declare all relevant variables for placing a stock order
//Sorry for bad naming convention
string varStockCode = StockCode.Text.ToString();
string varShares = StockSharesQuantity.Text.ToString();
string varOrderType = "";
string varExpiryDate = DaysUntilExpiration.SelectedValue;
string varAllOrNone = AllOrNone.Checked == true ? "Y" : "N";
string varStopPrice = StopPrice.Text.ToString();
string varLimitPrice = "";
name = extFunction.getSecuritiesByCode("stock", varStockCode).Rows[0]["name"].ToString().Trim();
//typeorder
if (OrderType.SelectedValue.Equals("Market Order"))
{
varOrderType = "market";
}
else if (OrderType.SelectedValue.Equals("Limit Order"))
{
varOrderType = "limit";
varLimitPrice = LimitPrice.Text;
}
else if (OrderType.SelectedValue.Equals("Stop Order"))
{
varOrderType = "stop";
}
else if (OrderType.SelectedValue.Equals("Stop Limit Order"))
{
varOrderType = "stop limit";
varLimitPrice = LimitPrice.Text;
}
//Check to see if the code exists
var validSecurity = extFunction.getSecuritiesByCode("stock", varStockCode);
if (validSecurity == null)
{
//Sell order was not succesfully submitted
InvalidStockCode.Text = "The code given does not exist";
}
else if (varTransactionType.Equals("buy"))
{
InvalidStockSharesQuantity.Text = stockSharesAmountIsValid(varShares, TransactionType.Text, varStockCode);
if (InvalidStockSharesQuantity.Text != "")
{
return;
}
//Limit price = high price here
string result = extFunction.submitStockBuyOrder(varStockCode, varShares, varOrderType, varExpiryDate, varAllOrNone, varLimitPrice, varStopPrice);
if (result != null)
{
//Figure out how to query with a value that should be zero
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [shares], [stockOrderType], [expiryDay], [allOrNone],";
if (varLimitPrice != "")
{
sql += "[limitPrice],";
}
if (varStopPrice != "")
{
sql += "[stopPrice],";
}
string timeNow = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt");
sql += "[accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType.Trim() + "', '" +
varSecurityType.Trim() + "', '" +
varStockCode + "', '" +
timeNow + "', cast('" +
varShares + "' as decimal(18,2)), '" +
varOrderType + "', '" +
varExpiryDate + "', '" +
varAllOrNone + "', '";
if (varLimitPrice != "")
{
sql += varLimitPrice + "', '";
}
if (varStopPrice != "")
{
sql += varStopPrice + "', '";
}
sql += accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans);
sendInvoice(result, varStockCode, varShares);
}
}
else if (TransactionType.SelectedValue.Equals("Sell"))
{
//Check sell price to see if stock is avlid
InvalidStockSharesQuantity.Text = stockSharesAmountIsValid(varShares, TransactionType.Text, varStockCode);
//Basically what the stock shares amount is validatesd as (recipe for bad code)
if (InvalidStockSharesQuantity.Text != "")
{
return;
}
// varLimitPrice = lowPrice
string result = extFunction.submitStockSellOrder(varStockCode, varShares, varOrderType, varExpiryDate, varAllOrNone, varLimitPrice, varStopPrice);
if (result != null)
{
//Code to write result into order history table
//Tested and properly replicates in the bonds
//Testing sql for Sell stock
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [shares], [stockOrderType], [expiryDay], [allOrNone],";
if (varLimitPrice != "")
{
sql += "[limitPrice],";
}
if (varStopPrice != "")
{
sql += "[stopPrice],";
}
sql += "[accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType.Trim() + "', '" +
varSecurityType.Trim() + "', '" +
varStockCode + "', '" +
DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', cast('" +
varShares + "' as decimal(18,2)), '" +
varOrderType + "', '" +
varExpiryDate + "', '" +
varAllOrNone + "', '";
if (varLimitPrice != "")
{
sql += varLimitPrice + "', '";
}
if (varStopPrice != "")
{
sql += varStopPrice + "', '";
}
sql += accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans); //Return URL
}
sendInvoice(result, varStockCode, varShares);
}
}
else
{
string varBondTrustCode = BondTrustCode.Text.ToString();
if (TransactionType.SelectedValue.Equals("Buy"))
{
string varBondTrustSharesAmount = BondTrustSharesQuantity.Text.ToString();
if (SecurityType.SelectedValue.Equals("Bond"))
{
var validSecurity = extFunction.getSecuritiesByCode("bond", varBondTrustCode);
if (validSecurity == null)
{
//Buy order was not succesfully submitted
InvalidBondTrustCode.Text = "The code given does not exist";
}
else
{
string result = extFunction.submitBondBuyOrder(varBondTrustCode, varBondTrustSharesAmount);
if (result != null)
{
//Yes. This is a redundant execution
name = extFunction.getSecuritiesByCode("bond", varBondTrustCode).Rows[0]["name"].ToString().Trim();
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [amount], [accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType + "', '" +
varSecurityType + "', '" +
varBondTrustCode + "', '" +
DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', '" +
varBondTrustSharesAmount + "', '" +
accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans);
sendInvoice(result, varBondTrustCode, varBondTrustSharesAmount);
}
}
}
else if (SecurityType.SelectedValue.Equals("Unit Trust"))
{
var validSecurity = extFunction.getSecuritiesByCode("unit trust", varBondTrustCode);
if (validSecurity == null)
{
//Buy order was not succesfully submitted
InvalidBondTrustCode.Text = "The code given does not exist";
}
else
{
string result = extFunction.submitUnitTrustBuyOrder(varBondTrustCode, varBondTrustSharesAmount);
if (result != null)
{
name = extFunction.getSecuritiesByCode("unit trust", varBondTrustCode).Rows[0]["name"].ToString().Trim();
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [amount], [accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType + "', '" +
varSecurityType + "', '" +
varBondTrustCode + "', '" +
DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', '" +
varBondTrustSharesAmount + "', '" +
accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans);
sendInvoice(result, varBondTrustCode, varBondTrustSharesAmount);
}
}
}
}
else if (TransactionType.SelectedValue.Equals("Sell"))
{
string varBondTrustShares = BondTrustSharesSelling.Text.ToString();
if (SecurityType.SelectedValue.Equals("Bond"))
{
var validSecurity = extFunction.getSecuritiesByCode("bond", varBondTrustCode);
string validSharesAmount = bondSharesAmountIsValid("bond", varBondTrustCode, varBondTrustShares, "sell");
if (validSecurity == null || validSharesAmount != "")
{
//Buy order was not succesfully submitted
InvalidBondTrustCode.Text = "The code given does not exist";
}
else
{
string result = extFunction.submitBondSellOrder(varBondTrustCode, varBondTrustShares);
if (result != null)
{
//Yes. This is a redundant execution
name = extFunction.getSecuritiesByCode("bond", varBondTrustCode).Rows[0]["name"].ToString().Trim();
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [shares], [accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType + "', '" +
varSecurityType + "', '" +
varBondTrustCode + "', '" +
DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', '" +
varBondTrustShares + "', '" +
accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans);
sendInvoice(result, varBondTrustCode, varBondTrustShares);
}
}
}
else if (SecurityType.SelectedValue.Equals("Unit Trust"))
{
var validSecurity = extFunction.getSecuritiesByCode("unit trust", varBondTrustCode);
string validSharesAmount = bondSharesAmountIsValid("unit trust", varBondTrustCode, varBondTrustShares, "sell");
if (validSecurity == null || validSharesAmount != "")
{
//Buy order was not succesfully submitted
InvalidBondTrustCode.Text = "The code given does not exist";
}
else
{
string result = extFunction.submitUnitTrustSellOrder(varBondTrustCode, varBondTrustShares);
if (result != null)
{
//Yes. This is a redundant execution
name = extFunction.getSecuritiesByCode("unit trust", varBondTrustCode).Rows[0]["name"].ToString().Trim();
string sql = "INSERT INTO OrderHistory ([referenceNumber], [buyOrSell], [securityType], [securityCode], [dateSubmitted], [shares], [accountNumber], [name]) VALUES ('" +
result + "', '" +
varTransactionType + "', '" +
varSecurityType + "', '" +
varBondTrustCode + "', '" +
DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + "', '" +
varBondTrustShares + "', '" +
accountNumber + "', '" +
name + "')";
SqlTransaction trans = extData.beginTransaction();
extData.setData(sql, trans);
extData.commitTransaction(trans);
sendInvoice(result, varBondTrustCode, varBondTrustShares);
}
}
}
}
}
}
}
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);*/
//}
}
}
protected void btnAlertClick_onClick(object sender, EventArgs e)
{
lblAlertErrorMessage.Visible = false;
decimal price = 0;
if (ddlAlertType.SelectedValue.Equals("0") || tbAlertPrice.Text.Trim().Equals("") || !decimal.TryParse(tbAlertPrice.Text.Trim(), out price))
{
return;
}
string choice = ddlAlertType.SelectedValue.Trim();
string accountNumber = (string)ViewState["accountNumber"];
string code = (string)ViewState["alertCode"];
string type = (string)ViewState["alertType"];
string isSameSide = "no";
string sql = "SELECT * FROM dbo.[Alert] a WHERE a.accountNumber='" + accountNumber + "' AND a.code='" + code + "' AND a.[type]='" + type + "';";
DataTable dt = myHKeInvestData.getData(sql);
if (dt == null)
{
return;
}
if (dt.Rows.Count >= 2)
{
lblAlertErrorMessage.Text = "No more alert allowed.";
lblAlertErrorMessage.Visible = true;
return;
}
else if (dt.Rows.Count == 1 && choice.Equals(Convert.ToString(dt.Rows[0]["highOrlow"]).Trim()))
{
lblAlertErrorMessage.Text = "No more alert allowed for choice " + choice + ".";
lblAlertErrorMessage.Visible = true;
return;
}
else
{
// Need to check the current price of the security
decimal marketPrice = myExternalFunctions.getSecuritiesPrice(type, code);
if (marketPrice == -1)
{
return; // invalid type and code
}
// Now comparing the limit price with the current price
if (choice.Equals("high"))
{
isSameSide = (marketPrice < price) ? "no" : "yes";
}
else
{
isSameSide = (marketPrice > price) ? "no" : "yes";
}
sql = string.Format("insert into dbo.[Alert] values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}');",
accountNumber,
code,
type,
choice,
price,
"no",
isSameSide
);
var myTrans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, myTrans);
myHKeInvestData.commitTransaction(myTrans);
}
lblAlertErrorMessage.Visible = false;
divAlertSettings.Visible = false;
}
protected void setAlertValue(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
//get user id
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"];
}
//************Now loginuserid stores the id**************
string choosencode = Snamecode.SelectedValue.Trim();
string choosentype = Stype.SelectedValue.Trim();
string high = "NULL";
string low = "NULL";
string inputhigh = high = highValue.Text.Trim();
string inputlow = lowValue.Text.Trim();
if (highValue.Text.Trim() != "")
{
high = highValue.Text.Trim();
}
if (lowValue.Text.Trim() != "")
{
low = lowValue.Text.Trim();
}
//verify if alert had been set
DataTable checkalert = myHKeInvestData.getData("SELECT * FROM Alert WHERE accountNumber = '" + loginuserid + "' AND type = '" + choosentype + "' AND code = '" + choosencode + "'");
if (checkalert.Rows.Count == 0)
{
//add new alert data if doesnt exist
SqlTransaction addalertdata = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("INSERT INTO Alert (accountNumber, type, code, highValue, lowValue) VALUES ('" + loginuserid + "', '" + choosentype + "', '" + choosencode + "', " + high + ", " + low + ")", addalertdata);
myHKeInvestData.commitTransaction(addalertdata);
}
else
{
//update alert info (cover old value)
SqlTransaction modifyalertdata = myHKeInvestData.beginTransaction();
if (inputhigh != "" && inputlow != "")
{
myHKeInvestData.setData("UPDATE Alert SET highValue = '" + high + "', lowValue = '" + low + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
else if (inputhigh == "" && inputlow != "")
{
myHKeInvestData.setData("UPDATE Alert SET lowValue = '" + low + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
else if (inputhigh != "" && inputlow == "")
{
myHKeInvestData.setData("UPDATE Alert SET highValue = '" + high + "' WHERE accountNumber = '" + loginuserid + "' AND Alert.type = '" + choosentype + "' AND Alert.code = '" + choosencode + "'", modifyalertdata);
myHKeInvestData.commitTransaction(modifyalertdata);
Label1.Text = "Your alert value had been updated.";
}
Label1.Visible = true;
}
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 CreateClient_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
try
{
string accountNumber = GenerateNextKey(LastName.Text.Trim());
decimal balance = Convert.ToDecimal(DepositAmount.Text.Trim());
string hkidPassNum = HKID.Text == "" ? PassportNumber.Text : HKID.Text;
string updateAccount = "INSERT INTO Account VALUES (" + "'" + accountNumber + "','" +
RadioButtonList1.SelectedValue + "','" +
balance + "','" +
PrimarySource.SelectedValue + "','" +
OtherInformation.Text + "','" +
InvestmentObjective.SelectedValue + "','" +
InvestmentKnowledge.SelectedValue + "','" +
AnnualIncome.SelectedValue + "','" +
LiquidWorth.SelectedValue + "','" +
FreeCreditSwee.SelectedValue + "','" +
null + "','" +
RoutingNumber.Text + "','" +
BankAccountNumber.Text + "')";
SqlTransaction trans1 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateAccount, trans1);
myHKeInvestData.commitTransaction(trans1);
string updateClient = "INSERT INTO Client VALUES ('" + accountNumber +
"','" + cbTitle.SelectedValue +
"','" + FirstName.Text + "'" + "," +
"'" + LastName.Text + "'" + "," +
"'" + FormatDateToSQL(DateOfBirth.Text) + "'" + "," +
"'" + Email.Text + "'" + "," +
"'" + Building.Text + "'" + "," +
"'" + Street.Text + "'" + "," +
"'" + District.Text + "'" + "," +
"'" + HomePhone.Text + "'" + "," +
"'" + HomeFax.Text + "'" + "," +
"'" + BusinessPhone.Text + "'" + "," +
"'" + MobilePhone.Text + "'" + "," +
"'" + CitizenshipCountry.Text + "'" + "," +
"'" + ResidenceCountry.Text + "'" + "," +
"'" + hkidPassNum + "'" + "," +
"'" + PassportCountry.Text + "'" + "," +
"'" + cbEmploymentStatus.Text + "'" + "," +
"'" + SpecificOccupation.Text + "'" + "," +
"'" + EmployYears.Text + "'" + "," +
"'" + EmployName.Text + "'" + "," +
"'" + EmployPhone.Text + "'" + "," +
"'" + BusinessNature.Text + "'" + "," +
"'" + IsEmployedFinancial.SelectedValue + "'" + "," +
"'" + IsInIPO.SelectedValue + "'" + "," +
"'" + FormatDateToSQL(SignedOn.Text) + "'," +
"'Y')";
SqlTransaction trans2 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateClient, trans2);
myHKeInvestData.commitTransaction(trans2);
if (!RadioButtonList1.SelectedValue.Equals("individual"))
{
string updateCOClient = "INSERT INTO Client VALUES ('" + accountNumber + "'," +
"'" + COcbTitle.SelectedValue + "'," +
"'" + COFirstName.Text + "'" + "," +
"'" + COLastName.Text + "'" + "," +
"'" + FormatDateToSQL(CODateOfBirth.Text) + "'" + "," +
"'" + COEmail.Text + "'" + "," +
"'" + COBuilding.Text + "'" + "," +
"'" + COStreet.Text + "'" + "," +
"'" + CODistrict.Text + "'" + "," +
"'" + COHomePhone.Text + "'" + "," +
"'" + COHomeFax.Text + "'" + "," +
"'" + COBusinessPhone.Text + "'" + "," +
"'" + COMobilePhone.Text + "'" + "," +
"'" + COCitizenshipCountry.Text + "'" + "," +
"'" + COResidenceCountry.Text + "'" + "," +
"'" + COHKID.Text + "'" + "," +
"'" + COPassportCountry.Text + "'" + "," +
"'" + COcbEmploymentStatus.Text + "'" + "," +
"'" + COSpecificOccupation.Text + "'" + "," +
"'" + COEmployYears.Text + "'" + "," +
"'" + COEmployName.Text + "'" + "," +
"'" + COEmployPhone.Text + "'" + "," +
"'" + COBusinessNature.Text + "'" + "," +
"'" + COIsEmployedFinancial.SelectedValue + "'" + "," +
"'" + COIsInIPO.SelectedValue + "'" + "," +
"'" + FormatDateToSQL(COSignedOn.Text) + "'," +
"'N')";
//Error is with this insertion
SqlTransaction trans3 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateCOClient, trans3);
myHKeInvestData.commitTransaction(trans3);
}
Console.WriteLine("Updated Successfully");
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
catch (Exception f)
{
Console.WriteLine("error thrown: " + f);
}
}
else
{
}
}
private void PeriodicTask()
{
do
{
/*
*
* FOR BUY & SELL PERIODIC TASK
*
*/
string status = "";
string refnum = "";
decimal fee = 0;
decimal cost = fee;
//get datatable where email has not yet sent out //another approach: condition: where status != completed --> still needa checkout
DataTable statustable = myHKeInvestData.getData("SELECT referenceNumber FROM TransactionRecord WHERE emailsent = 0");
foreach (DataRow rows in statustable.Rows)
{
//get referencenumber
refnum = rows["referenceNumber"].ToString();
//for each emailsent=0;
status = myExternalFunctions.getOrderStatus(refnum);
//if status is completed
if (String.Compare(status, "completed", true) == 0)
{
decimal accountNumber = myHKeInvestData.getAggregateValue("SELECT accountNumber FROM TransactionRecord WHERE referenceNumber = refnum");
string actnum = accountNumber.ToString();
string buyorsell = myData.getOneDatabyNO("buyOrSell", "TransactionRecord", actnum);
string date = DateTime.Now.ToString("yyyy-MM-dd");
decimal shares = myHKeInvestData.getAggregateValue("SELECT shares FROM TransactionRecord WHERE referenceNumber = refnum");
string strshares = shares.ToString();
string code = myData.getOneDatabyNO("securityCode", "TransactionRecord", actnum);
string username = myData.getOneDatabyNO("userName", "TransactionRecord", actnum);
decimal pricepshare = myHKeInvestData.getAggregateValue("SELECT executePrice FROM TransactionRecord WHERE referenceNumber = refnum");
string type = myData.getOneDatabyNO("securityType", "TransactionRecord", actnum);
string sname = myData.getOneDatabyNO("name", "TransactionRecord", actnum);
string sbase = myData.getOneDatabyNO("base", "TransactionRecord", actnum);
cost = shares * pricepshare;
string strcost = cost.ToString();
//get order transaction
DataTable ordertrans = myExternalFunctions.getOrderTransaction(refnum);
string exedate = "";
string exeshares = "";
string exeprice = "";
string transnum = "";
//string executeDate = "";
//string executeShares = "";
// DataTable temp = myHKeInvestData.getData("SELECT executeDate FROM ordertrans WHERE transactionNumber = '" + refnum + "'");
foreach (DataRow row in ordertrans.Rows)
{
transnum = row["transactionNumber"].ToString();
exedate = row["executeDate"].ToString();
exeshares = row["executeShares"].ToString();
exeprice = row["executePrice"].ToString();
}
//calcaulta transaction fee
fee = 0;
//modify account balance
SqlTransaction trans = myHKeInvestData.beginTransaction();
//set acct balance
myHKeInvestData.setData("UPDATE Account SET balance = (balance - '" + cost + "'-'" + fee + "' WHERE accountNumber = '" + accountNumber + "'", trans);
//set email sent =1
myHKeInvestData.setData("UPDATE TransactionRecord SET emailsent = 1 WHERE accountNumber ='" + accountNumber + "'", trans);
//update TransactionRecord to match the order status the rest of records
myHKeInvestData.setData("UPDATE TransactionRecord SET status = completed", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET transactionNumber ='" + transnum + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executeDate ='" + exedate + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executePrice ='" + exeprice + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.setData("UPDATE TransactionRecord SET executeShares ='" + exeshares + "' WHERE accountNumber ='" + accountNumber + "'", trans);
myHKeInvestData.commitTransaction(trans);
//update security holding
SqlTransaction addsecurity = myHKeInvestData.beginTransaction();
//check if same bond n code exist
DataTable check = myHKeInvestData.getData("SELECT accountNumber, type, code FROM SecurityHolding WHERE accountNumber = '" + actnum + "'");
foreach (DataRow row in check.Rows)
{
string checktype = row["type"].ToString();
if (String.Compare(checktype, type, true) == 0)
{
if (String.Compare(row["code"].ToString(), code, true) == 0)
{
if (String.Compare(buyorsell, "buy", true) == 0)
{
myHKeInvestData.setData("UPDATE SecurityHolding SET shares = shares + '" + shares + "' WHERE accountNumber = '" + actnum + "' AND type = '" + type + "' AND code = '" + code + "'", addsecurity);
}
else if (String.Compare(buyorsell, "sell", true) == 0)
{
myHKeInvestData.setData("UPDATE SecurityHolding SET shares = shares - '" + shares + "' WHERE accountNumber = '" + actnum + "' AND type = '" + type + "' AND code = '" + code + "'", addsecurity);
}
}
else
{
myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
}
}
else
{
myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
}
}
//myHKeInvestData.setData("INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "','" + type + "','" + sname + "','" + strshares + "','" + sbase + "')'", addsecurity);
myHKeInvestData.commitTransaction(addsecurity);
//myHKeInvestData.setData("UPDATE Account SET balance = (balance - cost) + value +"' WHERE accountNumber = '" + AccountNumber + "'", trans);
//gen invoice
// protected string generateInvoiceMsg(string user, string actnum, string orderrefnum, string buyorsell, string code,
//string sname, string stocktype, string date, string amt, string cost,
//string transnum, string dateExe, string numexe, string price)
string msg = generateInvoiceMsg(username, actnum, refnum, buyorsell, code, sname, type, date, strshares, strcost, refnum, exedate, exeshares, exeprice);
//send email
sendemail(username, msg);
//Update email flag
SqlTransaction emailflag = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE TransactionRecord SET emailsent = 1 WHERE accountNumber ='" + accountNumber + "'", emailflag);
myHKeInvestData.commitTransaction(emailflag);
}
//check if email sent
}
/*
*
* END OF PERIODIC TASK OF BUY & SELL
*
*/
// Place the method call for the periodic task here.
//if price in external table reach the value set in alert table, send email
//add a attribute "lastsent" to indicate if today had sent
//alert high, low save in table
//foreach compare wilth external
//HKeInvestData myHKeInvestData = new HKeInvestData();
//ExternalFunctions myExternalFunctions = new ExternalFunctions();
DataTable alerts = myHKeInvestData.getData("SELECT * FROM Alert");
foreach (DataRow row in alerts.Rows)
{
string id = "" + row["accountNumber"];
string type = "" + row["type"].ToString().Trim();
string code = "" + row["code"].ToString().Trim();
//string high = "" + row["high"];
decimal high = System.Convert.ToDecimal(row["highValue"]);
decimal low = System.Convert.ToDecimal(row["lowValue"]);
decimal current = myExternalFunctions.getSecuritiesPrice(type, code);
string date = "";
DataTable searchdate = myHKeInvestData.getData("SELECT lastsent FROM Alert WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'");
foreach (DataRow rows in searchdate.Rows)
{
date = date + rows["lastsent"];
}
if (date == DateTime.Now.ToString("yyyy-MM-dd"))
{
}
else
{
string email = "";
DataTable searchemail = myHKeInvestData.getData("SELECT email FROM Client WHERE accountNumber='" + id + "'");
foreach (DataRow rows in searchemail.Rows)
{
email = email + rows["email"];
}
string name = "";
DataTable security = myExternalFunctions.getSecuritiesByCode(type, code);
foreach (DataRow rows in security.Rows)
{
name = name + rows["name"];
}
if (high <= myExternalFunctions.getSecuritiesPrice(type, code))
{
SqlTransaction updatedate = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE alert SET lastsent='" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'", updatedate);
myHKeInvestData.commitTransaction(updatedate);
System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(email);
mail.From = new MailAddress("*****@*****.**", "HKeInvest", System.Text.Encoding.UTF8);
mail.Subject = "Alert Triggered!";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = "The high value alert for your " + type + " security, code: " + code + " name: " + name + " had been triggered. The current price of the security is " + current + ". The high alert value you set is " + high + ".";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true;
mail.Priority = MailPriority.High;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("comp3111_team120", "team120#");
client.Port = 587;
client.Host = "smtp.cse.ust.hk";
client.EnableSsl = true;
try
{
client.Send(mail);
//Page.RegisterStartupScript("UserMsg", "<script>alert('Successfully Send...');if(alert){ window.location='SendMail.aspx';}</script>");
}
catch (Exception ex)
{
Exception ex2 = ex;
string errorMessage = string.Empty;
while (ex2 != null)
{
errorMessage += ex2.ToString();
ex2 = ex2.InnerException;
}
//Page.RegisterStartupScript("UserMsg", "<script>alert('Sending Failed...');if(alert){ window.location='SendMail.aspx';}</script>");
}
}
else if (low >= myExternalFunctions.getSecuritiesPrice(type, code))
{
SqlTransaction updatedate = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("UPDATE alert SET lastsent='" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE accountNumber='" + id + "' AND code='" + code + "' AND type = '" + type + "'", updatedate);
myHKeInvestData.commitTransaction(updatedate);
System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(email);
mail.From = new MailAddress("*****@*****.**", "HKeInvest", System.Text.Encoding.UTF8);
mail.Subject = "Alert Triggered!";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = "The low value alert for your " + type + " security, code: " + code + " name: " + name + " had been triggered. The current price of the security is " + current + ". The low alert value you set is " + low + ".";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true;
mail.Priority = MailPriority.High;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("comp3111_team120", "team120#");
client.Port = 587;
client.Host = "smtp.cse.ust.hk";
client.EnableSsl = true;
try
{
client.Send(mail);
//Page.RegisterStartupScript("UserMsg", "<script>alert('Successfully Send...');if(alert){ window.location='SendMail.aspx';}</script>");
}
catch (Exception ex)
{
Exception ex2 = ex;
string errorMessage = string.Empty;
while (ex2 != null)
{
errorMessage += ex2.ToString();
ex2 = ex2.InnerException;
}
//Page.RegisterStartupScript("UserMsg", "<script>alert('Sending Failed...');if(alert){ window.location='SendMail.aspx';}</script>");
}
}
}
}
Thread.Sleep(10000);
} while (true);
}
private DataTable Sync_TransactionTable(DataTable dtTransaction, string orderReferenceNumber)
{
if (dtTransaction == null)
{
return(null);
}
// clone the table, and convert the column type
DataTable dtCloned = new DataTable();
var primaryKey = dtCloned.Columns.Add("transactionNumber", typeof(string));
dtCloned.Columns.Add("orderReferenceNumber", typeof(string));
dtCloned.Columns.Add("executeDate", typeof(DateTime));
dtCloned.Columns.Add("executeShares", typeof(decimal));
dtCloned.Columns.Add("executePrice", typeof(decimal));
dtCloned.PrimaryKey = new DataColumn[] { primaryKey };
foreach (DataRow transaction in dtTransaction.Rows)
{
DateTime executeDate = transaction.Field <DateTime>("executeDate");
string transactionNumber = transaction.Field <int>("transactionNumber").ToString("00000000");
string referenceNumber = transaction.Field <int>("referenceNumber").ToString("00000000");
decimal executeShares = transaction.Field <decimal>("executeShares");
decimal executePrice = transaction.Field <decimal>("executePrice");
DataRow newRow = dtCloned.NewRow();
newRow["transactionNumber"] = transactionNumber;
newRow["orderReferenceNumber"] = referenceNumber;
newRow["executeDate"] = executeDate;
newRow["executeShares"] = executeShares;
newRow["executePrice"] = executePrice;
dtCloned.Rows.Add(newRow);
}
HKeInvestData myHKeInvestData = new HKeInvestData();
DataTable dtLast = myHKeInvestData.getData("SELECT * FROM [Transaction] WHERE [orderReferenceNumber]='" + orderReferenceNumber.Trim() + "'");
dtLast.AcceptChanges();
dtLast.Merge(dtCloned);
DataTable dtChanges = dtLast.GetChanges(DataRowState.Added);
if (dtChanges == null)
{
return(null);
}
foreach (DataRow transaction in dtChanges.Rows)
{
DateTime executeDate = transaction.Field <DateTime>("executeDate");
string transactionNumber = transaction.Field <string>("transactionNumber");
string referenceNumber = transaction.Field <string>("orderReferenceNumber");
decimal executeShares = transaction.Field <decimal>("executeShares");
decimal executePrice = transaction.Field <decimal>("executePrice");
string date = executeDate.ToString("MM/dd/yyyy hh:mm:ss tt");
string sql = string.Format("INSERT INTO [Transaction] VALUES ('{0}', '{1}', '{2}', {3}, {4})",
transactionNumber,
referenceNumber,
date,
executeShares,
executePrice);
var trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(sql, trans);
myHKeInvestData.commitTransaction(trans);
}
return(dtChanges);
}
protected void CreateClient_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
try
{
//Get user information if on client
string accountNumber = AccountNumber.Text.Trim();
//TODO figure out some way to bind data
//Update account
string updateAccount = "UPDATE Account SET ";
//If statement to update the sql commands
if (PrimarySource.SelectedValue != "")
{
updateAccount += "sourceOfFunds='" + PrimarySource.SelectedValue + "',";
}
if (OtherInformation.Text.Trim() != "")
{
updateAccount += "otherSource='" + OtherInformation.Text.Trim() + "',";
}
//Code for investment objective
if (InvestmentObjective.SelectedValue != "")
{
updateAccount += "investmentObjective='" + InvestmentObjective.SelectedValue + "',";
}
if (InvestmentKnowledge.SelectedValue != "")
{
updateAccount += "investmentKnowledge='" + InvestmentKnowledge.SelectedValue + "',";
}
if (AnnualIncome.SelectedValue != "")
{
updateAccount += "annualIncome='" + AnnualIncome.SelectedValue + "',";
}
if (LiquidWorth.SelectedValue != "")
{
updateAccount += "approxLiquidNetWorth='" + LiquidWorth.SelectedValue + "',";
}
if (FreeCreditSwee.SelectedValue != "")
{
updateAccount += "sweepFreeCreditBalance='" + FreeCreditSwee + "',";
}
//Code to delete last comma
updateAccount = updateAccount.Remove(updateAccount.Length - 1);
updateAccount = " WHERE accountNumber = '" + accountNumber + "'";
if (updateAccount.Length > 56 && mainform.Visible)
{
SqlTransaction trans1 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateAccount, trans1);
myHKeInvestData.commitTransaction(trans1);
}
string updateClient = "UPDATE Client SET ";
if (cbTitle.SelectedValue != "")
{
updateClient += "title='" + cbTitle.SelectedValue + "',";
}
if (FirstName.Text.Trim() != "")
{
updateClient += "firstName='" + FirstName.Text.Trim() + "',";
}
if (LastName.Text.Trim() != "")
{
updateClient += "lastName='" + LastName.Text.Trim() + "',";
}
if (Email.Text != "")
{
updateClient += "email" + Email.Text + "',";
}
if (Building.Text != "")
{
updateClient += "building='" + Building.Text.Trim() + "',";
}
if (Street.Text != "")
{
updateClient += "street='" + Street.Text + "',";
}
if (District.Text != "")
{
updateClient += "district=" + District.Text + "',";
}
if (HomePhone.Text != "")
{
updateClient += "homePhone='" + HomePhone.Text + "',";
}
if (HomeFax.Text != "")
{
updateClient += "homeFax='" + HomeFax.Text + "',";
}
if (BusinessPhone.Text != "")
{
updateClient += "businessPhone='" + BusinessPhone.Text + "',";
}
if (MobilePhone.Text != "")
{
updateClient += "mobilePhone='" + MobilePhone.Text + "',";
}
//Execute sql to check if HKID, or passport has been updated
//Will fail if there is no regex from the
string sql = "SELECT * FROM Client WHERE accountNumber = '" + accountNumber + "' AND isPrimary = 'Y'";
DataTable temp = myHKeInvestData.getData(sql);
if (temp.Rows[0]["passportCountry"].ToString() != "")
{
if (HKID.Text != "")
{
updateClient += "HKIDPassportNumber='" + HKID.Text + "',";
}
if (PassportCountry.Text != "")
{
updateClient += "passportCountry='" + PassportCountry.Text + "',";
}
}
if (CitizenshipCountry.Text != "")
{
updateClient += "citizenship='" + CitizenshipCountry.Text + "',";
}
if (ResidenceCountry.Text != "")
{
updateClient += "legalResidence='" + ResidenceCountry.Text + "',";
}
if (cbEmploymentStatus.Text != "")
{
updateClient += "employeeStatus'" + cbEmploymentStatus.Text + "',";
}
if (SpecificOccupation.Text != "")
{
updateClient += "occupation='" + SpecificOccupation.Text + "',";
}
if (EmployYears.Text != "")
{
updateClient += "years='" + EmployYears.Text + "',";
}
if (EmployName.Text != "")
{
updateClient += "employerName='" + EmployName.Text + "',";
}
if (EmployPhone.Text != "")
{
updateClient += "employerPhone='" + EmployPhone.Text + "',";
}
if (BusinessNature.Text != "")
{
updateClient += "natureBusiness='" + BusinessNature + "','";
}
if (IsEmployedFinancial.SelectedValue != "")
{
updateClient += "isEmployedFinance'=" + IsEmployedFinancial.SelectedValue + "',";
}
if (IsInIPO.SelectedValue != "")
{
updateClient += "isPubliclyTraded'=" + IsInIPO.SelectedValue + "',";
}
updateClient = updateAccount.Remove(updateAccount.Length - 1);
updateClient = " WHERE accountNumber = '" + accountNumber + "' AND isPrimary = 'Y'";
//Checking to see if any fields in client are to be updated (Length 77)
if (updateClient.Length > 77 && mainform.Visible)
{
SqlTransaction trans2 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateClient, trans2);
myHKeInvestData.commitTransaction(trans2);
}
//Add some sort of auto postback for the account information that should be displayed
updateClient = "UPDATE Client SET ";
if (COcbTitle.SelectedValue != "")
{
updateClient += "title='" + COcbTitle.SelectedValue + "',";
}
if (COFirstName.Text.Trim() != "")
{
updateClient += "firstName='" + COFirstName.Text.Trim() + "',";
}
if (COLastName.Text.Trim() != "")
{
updateClient += "lastName='" + COLastName.Text.Trim() + "',";
}
if (COEmail.Text != "")
{
updateClient += "email" + COEmail.Text + "',";
}
if (COBuilding.Text != "")
{
updateClient += "building='" + COBuilding.Text.Trim() + "',";
}
if (COStreet.Text != "")
{
updateClient += "street='" + COStreet.Text + "',";
}
if (CODistrict.Text != "")
{
updateClient += "district=" + CODistrict.Text + "',";
}
if (COHomePhone.Text != "")
{
updateClient += "homePhone='" + COHomePhone.Text + "',";
}
if (COHomeFax.Text != "")
{
updateClient += "homeFax='" + COHomeFax.Text + "',";
}
if (COBusinessPhone.Text != "")
{
updateClient += "businessPhone='" + COBusinessPhone.Text + "',";
}
if (COMobilePhone.Text != "")
{
updateClient += "mobilePhone='" + COMobilePhone.Text + "',";
}
//Execute sql to check if HKID, or passport has been updated
//Will fail if there is no regex from the
sql = "SELECT * FROM Client WHERE accountNumber = '" + accountNumber + "' AND isPrimary='N'";
temp = myHKeInvestData.getData(sql);
//No error checking on temp
//Only allow for passport information updating
if (temp.Rows[0]["passportCountry"].ToString() != "")
{
if (COHKID.Text != "")
{
updateClient += "HKIDPassportNumber='" + COHKID.Text + "',";
}
if (COPassportCountry.Text != "")
{
updateClient += "passportCountry='" + COPassportCountry.Text + "',";
}
}
if (COCitizenshipCountry.Text != "")
{
updateClient += "citizenship='" + COCitizenshipCountry.Text + "',";
}
if (COResidenceCountry.Text != "")
{
updateClient += "legalResidence='" + COResidenceCountry.Text + "',";
}
if (COcbEmploymentStatus.Text != "")
{
updateClient += "employeeStatus'" + COcbEmploymentStatus.Text + "',";
}
if (COSpecificOccupation.Text != "")
{
updateClient += "occupation='" + COSpecificOccupation.Text + "',";
}
if (COEmployYears.Text != "")
{
updateClient += "years='" + COEmployYears.Text + "',";
}
if (COEmployName.Text != "")
{
updateClient += "employerName='" + COEmployName.Text + "',";
}
if (COEmployPhone.Text != "")
{
updateClient += "employerPhone='" + COEmployPhone.Text + "',";
}
if (COBusinessNature.Text != "")
{
updateClient += "natureBusiness='" + COBusinessNature + "','";
}
if (COIsEmployedFinancial.SelectedValue != "")
{
updateClient += "isEmployedFinance'=" + COIsEmployedFinancial.SelectedValue + "',";
}
if (COIsInIPO.SelectedValue != "")
{
updateClient += "isPubliclyTraded'=" + COIsInIPO.SelectedValue + "',";
}
//Removce final comma
updateClient = updateAccount.Remove(updateAccount.Length - 1);
updateClient = " WHERE accountNumber = '" + accountNumber + "' AND isPrimary = 'N'";
if (updateClient.Length > 77 && coAccount2.Visible)
{
SqlTransaction trans3 = myHKeInvestData.beginTransaction();
myHKeInvestData.setData(updateClient, trans3);
myHKeInvestData.commitTransaction(trans3);
}
Console.WriteLine("Updated Successfully");
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
catch (Exception f)
{
Console.WriteLine("error thrown: " + f);
}
}
else
{
}
}
protected void CreateUser_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
HKeInvestData myHKeInvestData = new HKeInvestData();
string idnum = HKID.Text.Trim();
string mail = Email.Text.Trim();
//check if HKIDPassportNumber is really stored in the database
DataTable curHKID = myHKeInvestData.getData("SELECT HKIDPassportNumber FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
if (curHKID.Rows.Count == 0)
{
ErrorMessage.Text = "The input data does not match the client data.";
return;
}
//check if input data matches the one in database
DataTable checkdata = myHKeInvestData.getData("SELECT email, accountNumber, lastName, firstName, dateOfBirth FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
DataTable checkdate = myHKeInvestData.getData("SELECT Convert(varchar(10),CONVERT(date,dateOfBirth,106),103) AS DOB FROM Client WHERE HKIDPassportNumber = '" + idnum + "'");
string checkemail = "";
string checkAccNum = "";
string checklastname = "";
string checkfirstname = "";
string checkDOB = "";
foreach (DataRow row in checkdata.Rows)
{
checkemail = checkemail + row["email"];
checkAccNum = checkAccNum + row["accountNumber"];
checklastname = checklastname + row["lastName"];
checkfirstname = checkfirstname + row["firstName"];
}
foreach (DataRow row in checkdate.Rows)
{
checkDOB = checkDOB + row["DOB"];
}
int emailcheck = checkemail.CompareTo(Email.Text.Trim());
Console.WriteLine(emailcheck);
int accNumCheck = checkAccNum.CompareTo(AccountNumber.Text.Trim());
Console.WriteLine(accNumCheck);
int lastNameCheck = checklastname.CompareTo(LastName.Text.Trim());
Console.WriteLine(lastNameCheck);
int firstNameCheck = checkfirstname.CompareTo(FirstName.Text.Trim());
Console.WriteLine(firstNameCheck);
int DOBcheck = checkDOB.CompareTo(DateOfBirth.Text.ToString());
Console.WriteLine(DOBcheck);
if (emailcheck == -1 || accNumCheck == -1 || lastNameCheck == -1 || firstNameCheck == -1 || DOBcheck == -1)
{
ErrorMessage.Text = "The input data does not match the client data.";
return;
}
var manager = Context.GetOwinContext().GetUserManager <ApplicationUserManager>();
var signInManager = Context.GetOwinContext().Get <ApplicationSignInManager>();
var user = new ApplicationUser()
{
UserName = UserName.Text, Email = Email.Text
};
IdentityResult result = manager.Create(user, Password.Text);
if (result.Succeeded)
{
//assign to role client
IdentityResult roleResult = manager.AddToRole(user.Id, "Client");
SqlTransaction trans = myHKeInvestData.beginTransaction();
myHKeInvestData.setData("update [Account] set [userName]='" + UserName.Text + "' WHERE [accountNumber] = '" + AccountNumber.Text + "'", trans);
myHKeInvestData.commitTransaction(trans);
if (!roleResult.Succeeded)
{
ErrorMessage.Text = roleResult.Errors.FirstOrDefault();
}
// For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771
//string code = manager.GenerateEmailConfirmationToken(user.Id);
//string callbackUrl = IdentityHelper.GetUserConfirmationRedirectUrl(code, user.Id, Request);
//manager.SendEmail(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>.");
signInManager.SignIn(user, isPersistent: false, rememberBrowser: false);
IdentityHelper.RedirectToReturnUrl(Request.QueryString["ReturnUrl"], Response);
}
else
{
ErrorMessage.Text = result.Errors.FirstOrDefault();
}
/*SqlTransaction trans = myHKeInvestData.beginTransaction();
* myHKeInvestData.setData("update [Account] set [userName]='" + UserName.Text + "' WHERE [accountNumber] = '" + AccountNumber.Text + "'", trans);
* myHKeInvestData.commitTransaction(trans);*/
}
}
