Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
        }
Пример #6
0
        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);
        }
Пример #7
0
        //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);
                        }
                    }
                }
            }
        }