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); }
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 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(); } } }
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);*/ } }