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);
}
}
}
}
}
