public void setOneData(string toChangeVar, string Table, string value, string loginName) { string accountNumber = ""; DataTable temp = myHKeInvestData.getData("SELECT accountNumber FROM Account WHERE userName = '******'"); foreach (DataRow row in temp.Rows) { accountNumber = row["accountNumber"].ToString(); } SqlTransaction trans = myHKeInvestData.beginTransaction(); myHKeInvestData.setData("UPDATE " + Table + " SET " + toChangeVar + " = '" + value + "' WHERE accountNumber = '" + accountNumber + "'", trans); myHKeInvestData.commitTransaction(trans); }
public void createStockBuyOrder(string referenceNumber, string shares, string orderType, string expiryDay, string allOrNone, string highPrice, string stopPrice) { HKeInvestData myHKeInvestData = new HKeInvestData(); var trans = myHKeInvestData.beginTransaction(); string sql = string.Format("INSERT INTO [StockOrderBuy] VALUES ({0}, '{1}', {2}, '{3}',", shares, orderType, int.Parse(expiryDay), allOrNone); if (orderType == "limit") { sql += highPrice + ", NULL, '" + referenceNumber + "')"; } else if (orderType == "stop") { sql += "NULL, " + stopPrice + ", '" + referenceNumber + "')"; } else if (orderType == "stop limit") { sql += highPrice + "," + stopPrice + ", '" + referenceNumber + "')"; } else { sql += "NULL, NULL, '" + referenceNumber + "')"; } myHKeInvestData.setData(sql, trans); myHKeInvestData.commitTransaction(trans); }
public void changeData(string toChange, string value, string AccountNumber) { SqlTransaction trans = myHKeInvestData.beginTransaction(); if (String.Compare(toChange, "balance") == 0) { myHKeInvestData.setData("UPDATE Account SET " + toChange + " = '" + value + "' WHERE accountNumber = '" + AccountNumber + "'", trans); } else { myHKeInvestData.setData("UPDATE Client SET " + toChange + " = '" + value + "' WHERE accountNumber = '" + AccountNumber + "'", trans); } myHKeInvestData.commitTransaction(trans); //DataBinder.Eval(toChange, value); }
public void createUnitTrustBuyOrder(string referenceNumber, string amount) { HKeInvestData myHKeInvestData = new HKeInvestData(); var trans = myHKeInvestData.beginTransaction(); string sql = string.Format("INSERT INTO [UnitTrustOrderBuy] VALUES ({0}, '{1}')", decimal.Parse(amount), referenceNumber); myHKeInvestData.setData(sql, trans); myHKeInvestData.commitTransaction(trans); }
public void createBondSellOrder(string referenceNumber, string shares) { HKeInvestData myHKeInvestData = new HKeInvestData(); var trans = myHKeInvestData.beginTransaction(); string sql = string.Format("INSERT INTO [BondOrderSell] VALUES ({0}, '{1}')", decimal.Parse(shares), referenceNumber); myHKeInvestData.setData(sql, trans); myHKeInvestData.commitTransaction(trans); }
public void createNewOrder(string accountNumber, string referenceNumber, string name, string code) { string status = "pending"; decimal serviceFee = 0; string date = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt"); HKeInvestData myHKeInvestData = new HKeInvestData(); var trans = myHKeInvestData.beginTransaction(); string sql = string.Format("INSERT INTO [Order] VALUES ('{0}', '{1}', '{2}', {3}, '{4}', '{5}', '{6}')", referenceNumber, name, status, serviceFee, code, date, accountNumber); myHKeInvestData.setData(sql, trans); myHKeInvestData.commitTransaction(trans); }
//UNTESTED //Also updates balance in function //To be called through threading public void updateLocalOrderStatus() { string sql = "SELECT referenceNumber, accountNumber, buyOrSell, name, securityType, securityCode, stockOrderType, dateSubmitted, feesPaid FROM OrderHistory WHERE status = 'pending' OR status = 'partial'"; //string accountNumber = getAccountNumber(); DataTable orderNums = extData.getData(sql); if (orderNums != null && orderNums.Rows.Count > 0) { foreach (DataRow row in orderNums.Rows) { string referenceNumber = row["referenceNumber"].ToString().Trim(); string orderStatus = extFunction.getOrderStatus(referenceNumber).Trim(); string accountNumber = row["accountNumber"].ToString().Trim(); string varSecurityType = row["securityType"].ToString().Trim(); string varSecurityCode = row["securityCode"].ToString().Trim(); if (orderStatus != "pending" && orderStatus != "partial") { //Including calculate fees function decimal fees = calculateFees(referenceNumber, accountNumber); SqlTransaction trans = extData.beginTransaction(); sql = "UPDATE OrderHistory SET status ='" + orderStatus + "', feesPaid = '" + fees.ToString().Trim() + "' WHERE referenceNumber = '" + referenceNumber + "'"; extData.setData(sql, trans); extData.commitTransaction(trans); //Now that the order has been completed fees can be calculated and applied to the balance in account and the feespaid in orderhistory string buyOrSell = row["buyOrSell"].ToString().Trim(); sql = "SELECT balance FROM Account WHERE accountNumber = '" + accountNumber + "'"; decimal balance = Decimal.Parse(extData.getData(sql).Rows[0]["balance"].ToString()); //Get transaction information from external table decimal costOfOrder = 0m; decimal totalSharesExecuted = 0m; DataTable transactions = extFunction.getOrderTransaction(referenceNumber); if (transactions != null && transactions.Rows != null) { foreach (DataRow tRow in transactions.Rows) { //Implement some sort of currency conversion here costOfOrder += decimal.Parse(tRow["executeShares"].ToString()) * decimal.Parse(tRow["executePrice"].ToString()); totalSharesExecuted += decimal.Parse(tRow["executeShares"].ToString()); } } if (buyOrSell.Equals("buy")) { balance -= fees; balance -= costOfOrder; } else { balance -= fees; balance += costOfOrder; } trans = extData.beginTransaction(); sql = "UPDATE Account SET balance = '" + balance + "' WHERE accountNumber = '" + accountNumber + "'"; extData.setData(sql, trans); extData.commitTransaction(trans); //Now that the order has been completed. Send email //orderReference, accountNumber, buyorsell, securitycode, securityname, (stock order type), dateSubmited, totalnumSharesBoughtt, executedDollarAmount, feeCharged, (FOREACH transaction) transactionNumber, date executed, quantity of shares, price per share string address = "";//get from sql statement sql = "SELECT email FROM Client WHERE accountNumber = '" + accountNumber + "' AND isPrimary = 'Y'"; DataTable tempForAddress = extData.getData(sql); if (tempForAddress != null && tempForAddress.Rows.Count > 0) { address = tempForAddress.Rows[0]["email"].ToString().Trim(); } string subject = "Order Number: " + referenceNumber + " has been " + orderStatus; string body = "Order Reference Number: " + referenceNumber + "\n" + "Account Number: " + accountNumber + "\n" + "Buy or Sell Order: " + buyOrSell + "\n" + "Security Code: " + varSecurityCode + "\n" + "Security Name: " + row["name"].ToString() + "\n"; if (varSecurityType.Equals("stock")) { body += "Stock Order Type: " + row["stockOrderType"].ToString() + "\n"; } body += "Date Submitted: " + row["dateSubmitted"].ToString() + "\n" + "Total Shares Executed: " + totalSharesExecuted.ToString() + "\n" + "Total Executed Dollar Amount: " + costOfOrder + "\n"; if (transactions != null && transactions.Rows != null) { foreach (DataRow tRow in transactions.Rows) { body += "Transaction number: " + tRow["transactionNumber"].ToString() + "\n" + "Execution Date: " + tRow["executeDate"].ToString() + "\n" + "Quantity of Shares: " + tRow["executeShares"].ToString() + "\n" + "Price per share: " + tRow["executePrice"].ToString() + "\n"; } } body += "\n Sincerely yours, the HKeInvestment Team. Contact our hotline if there is any issues with your invoice."; // Check that the email is not missing a sender's address, a subject, or a body if (body != "" && address != "") { sendInvoiceEmail(address, subject, body); } else { // TODO: Handle this } //Get base of transaction //Should probably store in the local database as well string currencyBase = ""; DataTable currencyBaseTable = extFunction.getSecuritiesByCode(varSecurityType, varSecurityCode); if (currencyBaseTable != null && currencyBaseTable.Rows.Count > 0) { //All stocks lack a currency base if (varSecurityType.Equals("stock")) { currencyBase = "HKD"; } else { currencyBase = currencyBaseTable.Rows[0]["base"].ToString().Trim(); } } /////////////////////////UPDATE SECURITY HOLDING DETAILS/////////////////////////////////////// sql = "SELECT * FROM SecurityHolding WHERE accountNumber = '" + accountNumber + "' AND type = '" + varSecurityType + "' AND code = '" + varSecurityCode + "'"; DataTable curSecurityHolding = extData.getData(sql); //Checking the values of the secuityholding if (curSecurityHolding == null || curSecurityHolding.Rows.Count == 0) { //if (buyOrSell.Equals("buy")) //{ trans = extData.beginTransaction(); sql = "INSERT INTO SecurityHolding (accountNumber, type, code, name, shares, base) VALUES ('" + accountNumber + "', '" + varSecurityType + "', '" + varSecurityCode + "', '" + row["name"].ToString().Trim() + "', '" + totalSharesExecuted.ToString().Trim() + "', '" + currencyBase + "')"; extData.setData(sql, trans); extData.commitTransaction(trans); //} } else { //current number of shares held decimal curShares = decimal.Parse(curSecurityHolding.Rows[0]["shares"].ToString()); trans = extData.beginTransaction(); sql = ""; if (buyOrSell.Equals("buy")) { curShares += totalSharesExecuted; } else { curShares -= totalSharesExecuted; } if (curShares <= 0m) { sql = "DELETE FROM SecurityHolding WHERE accountNumber = '" + accountNumber + "' AND type = '" + varSecurityType + "' AND code = '" + varSecurityCode + "'"; } else { sql = "UPDATE SecurityHolding SET shares ='" + curShares + "' WHERE accountNumber = '" + accountNumber + "' AND type = '" + varSecurityType + "' AND code = '" + varSecurityCode + "'"; } extData.setData(sql, trans); extData.commitTransaction(trans); } } } } }