// 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;
}
public static TWTransactions Transactions(string accountNumber, DateTime?start, DateTime?end)
{
SetHeaders(Token);
string url = "https://api.tastyworks.com/accounts/" + accountNumber + "/transactions?";
if (start != null)
{
url += "start-date=" + String.Format("{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'}", start) + "&";
}
if (end != null)
{
url += "end-date=" + String.Format("{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'}", end);
}
string reply = Web.DownloadString(url);
//Debug.WriteLine(reply);
JObject package = JObject.Parse(reply);
TWTransactions returnList = new TWTransactions();
Int32 pages = Convert.ToInt32(package["pagination"]["total-pages"]);
Int32 pageOffset = 0;
List <JToken> list = package["data"]["items"].Children().ToList();
do
{
foreach (JToken item in list)
{
//Debug.WriteLine(item.ToString());
TWTransaction inst = new TWTransaction();
inst.TransID = Convert.ToInt32(item["id"]);
inst.Time = Convert.ToDateTime(item["executed-at"]).ToUniversalTime();
inst.TransactionCode = item["transaction-type"].ToString();
inst.TransactionSubcode = item["transaction-sub-type"].ToString();
if (item["action"] != null)
{
inst.Action = item["action"].ToString();
}
inst.Description = item["description"].ToString();
inst.AccountRef = item["account-number"].ToString();
inst.Price = Convert.ToDecimal(item["price"]);
inst.Fees = Convert.ToDecimal(item["commission"]) + Convert.ToDecimal(item["clearing-fees"]) + Convert.ToDecimal(item["regulatory-fees"]);
inst.Amount = Convert.ToDecimal(item["value"]) * ((item["value-effect"].ToString() == "Debit") ? -1 : 1);
if ((inst.TransactionCode == "Trade") || (inst.TransactionCode == "Receive Deliver"))
{
inst.Symbol = item["underlying-symbol"].ToString();
inst.Quantity = Convert.ToDecimal(item["quantity"]);
SymbolDecoder symbol = new SymbolDecoder(item["symbol"].ToString(), item["instrument-type"].ToString());
inst.InsType = symbol.Type;
inst.ExpireDate = symbol.Expiration;
inst.Strike = symbol.Strike;
}
CompleteInstance(inst);
returnList.Add(inst);
}
if (pages > 1)
{
SetHeaders(Token);
reply = Web.DownloadString(url + "&page-offset=" + ++pageOffset);
package = JObject.Parse(reply);
list = package["data"]["items"].Children().ToList();
}
pages--;
} while (pages > 0);
return((returnList.Count > 0) ? returnList : null);
}
public static void Load(Accounts accounts)
{
try
{
// establish db connection
App.OpenConnection();
if (TastyWorks.InitiateSession(Config.GetEncryptedProp("Username"), Config.GetEncryptedProp("Password")))
{
// cache the current positions for details required to establish default risk and capreq
twpositions = new Dictionary <string, TWPositions>();
twMarginReq = new Dictionary <string, TWMargins>();
foreach (Account a in accounts)
{
if (a.Active)
{
// retrieve Tastyworks positions for given account
TWPositions pos = TastyWorks.Positions(a.ID);
twpositions.Add(a.ID, pos);
TWMargins mar = TastyWorks.MarginData(a.ID);
twMarginReq.Add(a.ID, mar);
}
}
// proceed with transactions from all accounts
foreach (Account a in accounts)
{
if (a.Active)
{
Debug.WriteLine(a.ID);
// retrieve Tastyworks transactions for the past month
TWTransactions transactions = TastyWorks.Transactions(a.ID, DateTime.Today.AddDays(-30), null);
SaveTransactions(transactions); // transfer transaction array to database
// save latest transaction for next upload
SQLiteCommand cmd = new SQLiteCommand("SELECT max(time) FROM transactions WHERE Account = @ac", App.ConnStr);
cmd.Parameters.AddWithValue("ac", a.ID);
SQLiteDataReader rdr = cmd.ExecuteReader();
string propName = "LastDate-" + a.ID;
if (rdr.Read())
{
Config.SetProp(propName, rdr[0].ToString());
}
}
}
UpdateNewTransactions(); // matches unassociated asignments and exercises
}
else
{
MessageBox.Show("Login to TastyWorks failed", "Error");
}
}
catch (Exception ex)
{
Console.WriteLine("ERROR DataLoader: " + ex.Message);
MessageBox.Show(ex.Message, "Sync Error");
}
}
