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