public void GetCurrentHoldings(Accounts acc)
{
accounts = acc;
try
{
// always start with an empty list
this.Clear();
twpositions = null; // clear cache
// establish connection
App.OpenConnection();
string sql = "SELECT *, date(ActionDate) AS TodoDate FROM transgroup AS tg LEFT JOIN";
sql += " (SELECT transgroupid, SUM(amount) AS Cost, SUM(Fees) AS Fees, datetime(MIN(time)) AS startTime, datetime(MAX(time)) AS endTime from transactions GROUP BY transgroupid) AS t ON tg.id = t.transgroupid";
sql += " LEFT JOIN accounts AS a ON tg.Account = a.ID";
sql += " WHERE tg.Open = 1";
SQLiteCommand cmd = new SQLiteCommand(sql, App.ConnStr);
SQLiteDataReader readerGroup = cmd.ExecuteReader();
while (readerGroup.Read())
{
TransactionGroup grp = MapTransactionGroup(readerGroup);
// step thru open holdings
sql = "SELECT * FROM (SELECT symbol, transgroupid, type, datetime(expiredate) AS ExpireDate, strike, sum(quantity) AS total, sum(amount) as amount, datetime(Time) AS TransTime FROM transactions";
sql += " WHERE (transgroupid = @gr) GROUP BY symbol, type, expiredate, strike) WHERE (total <> 0)";
cmd = new SQLiteCommand(sql, App.ConnStr);
cmd.Parameters.AddWithValue("gr", grp.GroupID);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//decimal strike = reader["strike"].ToString();
decimal strike = 0m;
if (reader["Strike"] != DBNull.Value)
{
strike = Convert.ToDecimal(reader["Strike"]);
}
decimal quantity = 0.0m;
if (reader["Total"] != DBNull.Value)
{
quantity = Convert.ToDecimal(reader["Total"]);
}
decimal amount = 0.0m;
if (reader["Amount"] != DBNull.Value)
{
amount = Convert.ToDecimal(reader["Amount"]);
}
DateTime expDate = DateTime.MinValue;
if (reader["ExpireDate"] != DBNull.Value)
{
expDate = Convert.ToDateTime(reader["ExpireDate"].ToString());
}
DateTime transTime = DateTime.MinValue;
if (reader["TransTime"] != DBNull.Value)
{
transTime = Convert.ToDateTime(reader["TransTime"].ToString());
}
transTime = DateTime.SpecifyKind(transTime, DateTimeKind.Utc);
grp.Holdings.Add(reader["symbol"].ToString(), reader["type"].ToString(), expDate, strike, quantity, amount, transTime, 0, "", 0);
}
grp.EarliestExpiration = FindEarliestDate(grp.Holdings);
RetrieveCurrentData(grp);
this.Add(grp.GroupID, grp);
} // end of transaction group loop
}
catch (Exception ex)
{
Console.WriteLine("CurrentHoldings: " + ex.Message);
}
}
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");
}
}