Esempio n. 1
0
        // write transactions loaded from webservice into the database
        //
        private static void SaveTransactions(TWTransactions transactions)
        {
            try
            {
                SQLiteCommand cmd;

                if (transactions.Count() == 0)
                {
                    return;
                }

                string   propName = "LastDate-" + transactions[0].AccountRef;
                DateTime lastDate = Config.GetDateProp(propName);

                int maxLoadID = DBUtilities.GetMax("SELECT Max(LoadGroupID) FROM transactions");
                int newLoadID = maxLoadID + 1;

                //(new SQLiteCommand("DELETE FROM transactions WHERE 1=1", conn)).ExecuteNonQuery();

                string sql = "INSERT INTO transactions(Time, LoadGroupID, TransType, TransSubType, TransID, Symbol, 'Buy-Sell', 'Open-Close', Quantity, ExpireDate, Strike, Type, Price, Fees, Amount, Description, Account)"
                             + " Values(@tm,@lg,@tt,@tst,@tid,@sym,@buy,@op,@qu,@exp,@str,@ty,@pr,@fe,@am,@des,@acc)";

                SQLiteTransaction sqlTransaction = App.ConnStr.BeginTransaction();

                foreach (var record in transactions)
                {
                    if (record.Time.Trim(TimeSpan.TicksPerSecond) > lastDate)
                    {
                        cmd = new SQLiteCommand(sql, App.ConnStr);
                        cmd.Parameters.AddWithValue("tm", record.Time.Trim(TimeSpan.TicksPerSecond));
                        cmd.Parameters.AddWithValue("lg", newLoadID);  // load group id
                        cmd.Parameters.AddWithValue("tt", record.TransactionCode);
                        cmd.Parameters.AddWithValue("tst", record.TransactionSubcode);
                        cmd.Parameters.AddWithValue("tid", record.TransID);
                        cmd.Parameters.AddWithValue("sym", record.Symbol);
                        cmd.Parameters.AddWithValue("buy", record.BuySell);
                        cmd.Parameters.AddWithValue("op", record.OpenClose);
                        cmd.Parameters.AddWithValue("qu", record.Quantity);
                        cmd.Parameters.AddWithValue("exp", record.ExpireDate);
                        cmd.Parameters.AddWithValue("str", record.Strike);
                        cmd.Parameters.AddWithValue("ty", record.InsType);
                        cmd.Parameters.AddWithValue("pr", record.Price);
                        cmd.Parameters.AddWithValue("fe", record.Fees);
                        cmd.Parameters.AddWithValue("am", record.Amount);
                        cmd.Parameters.AddWithValue("des", record.Description);
                        cmd.Parameters.AddWithValue("acc", record.AccountRef);

                        cmd.ExecuteNonQuery();
                    }
                }

                sqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ERROR: Save Transaction: " + ex.Message);
            }

            return;
        }
Esempio n. 2
0
        //
        // Header function for setting up recursion
        // starts with data table of all possible transaction, account/symbol and a particular transaction time
        // chain is built from there
        //
        private static void ProcessTransactionChain(string account, string symbol, DataTable dt, string time)
        {
            Positions holdings = new Positions();
            SortedList <string, string> times = new SortedList <string, string>();

            // start recursion
            ProcessTransactionGroup(account, symbol, dt, time, holdings, times);
            Debug.WriteLine("First pass of chain completed.  Retrieving manually matched transactions...");


            // retrieve existing group id
            int groupID = holdings.GroupID();

            Debug.WriteLine("GroupID: " + groupID.ToString());

            // Collect remaining transactions that have been manually merged
            if (groupID > 0)
            {
                DataRow[] remainingRows = dt.Select("TransGroupID = " + groupID.ToString());
                while (remainingRows.Length > 0)
                {
                    string t = remainingRows[0]["TransTime"].ToString(); // maintain sqlite date format as a string
                    ProcessTransactionGroup(account, symbol, dt, t, holdings, times);

                    //refresh
                    remainingRows = dt.Select("TransGroupID = " + groupID.ToString());
                }
            }
            Debug.WriteLine("Chain completed for GroupID: " + groupID.ToString());

            try
            {
                if (groupID > 0)
                {
                    // update chain status
                    string        sql    = "UPDATE TransGroup SET Open = @op WHERE ID=@row";
                    SQLiteCommand cmdUpd = new SQLiteCommand(sql, App.ConnStr);
                    cmdUpd.Parameters.AddWithValue("op", !holdings.IsAllClosed());
                    cmdUpd.Parameters.AddWithValue("row", groupID);
                    cmdUpd.ExecuteNonQuery();
                }
                else
                {
                    string        sql = "INSERT INTO TransGroup(Account, Symbol, Open, Strategy, DefinedRisk, NeutralStrategy, CapitalRequired, Risk) Values(@ac,@sym,@op,@str,@dr,@ns,@cap,@rsk)";
                    SQLiteCommand cmd = new SQLiteCommand(sql, App.ConnStr);
                    cmd.Parameters.AddWithValue("ac", account);
                    cmd.Parameters.AddWithValue("sym", symbol);
                    cmd.Parameters.AddWithValue("op", !holdings.IsAllClosed());
                    string strat = GuessStrategy(holdings);
                    cmd.Parameters.AddWithValue("str", strat);
                    cmd.Parameters.AddWithValue("dr", DefaultDefinedRisk(strat));
                    cmd.Parameters.AddWithValue("ns", DefaultNeutralStrategy(strat));
                    decimal capital = DefaultCapital(account, strat, holdings);
                    cmd.Parameters.AddWithValue("rsk", DefaultRisk(strat, capital, holdings));
                    cmd.Parameters.AddWithValue("cap", FindTWMarginRequirement(account, holdings));
                    cmd.ExecuteNonQuery();

                    groupID = DBUtilities.GetMax("SELECT max(id) FROM TransGroup");
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ProcessTransactionChain (Update/insert TransGroup): " + ex.Message + "(groupID: " + groupID.ToString() + ")");
            }


            try
            {
                List <int> rows = holdings.GetRowNumbers();
                foreach (int r in rows)
                {
                    // update all of the rows in the chain
                    string        sql    = "UPDATE transactions SET TransGroupID = @id WHERE ID=@row";
                    SQLiteCommand cmdUpd = new SQLiteCommand(sql, App.ConnStr);
                    cmdUpd.Parameters.AddWithValue("id", groupID);
                    cmdUpd.Parameters.AddWithValue("row", r);
                    cmdUpd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ProcessTransactionChain (Updating transactions): " + ex.Message);
            }
        }