public static void storePending(string stock_id, int portfolio_id, int ticket, double volume, double price, string order_date, string rec_date, string rec, string type, string info)
{
info = "Pending " + info;
try
{
Database db = new Database();
//if ticket =-1 aka position is NOT successfully opened then just set stock_ticket=-1 in StockRecommend table
if (ticket < 0)
{
db.executeNonQuery(string.Format("update StockRecommend set stock_ticket = {0} where portfolio_id={1} and stock_id='{2}' and date =Convert(datetime,'{3}',103) and type ='{4}'",
ticket, portfolio_id, stock_id, rec_date, type));
return;
}
//else
//1. change rec in Stockinportfolio to pending
//2. create new record in pendingDetails
//3. change stock recommend status to pending
else
{
//1. change rec in Stockinportfolio to pending
db.executeNonQuery(string.Format("update StockInPortfolio set rec = 'PENDING' , stock_ticket={0} where portfolio_id={1} and stock_id='{2}' and type like '%{3}%' and rec like '%OPEN%';", ticket, portfolio_id, stock_id, type));
//2. create new record in pendingDetails
db.executeNonQuery(string.Format("insert into PendingDetails(portfolio_id,stock_id,type,volume,price,ticket) values ('{0}','{1}','{2}',{3},{4},{5});",
portfolio_id, stock_id, type, volume, price, ticket));
//3. change stock recommend status to pending
db.executeNonQuery(string.Format("update StockRecommend set stock_ticket='{0}', rec ='{1}' where portfolio_id={2} and stock_id = '{3}' and date = Convert(datetime,'{4}',103);",
info, rec, portfolio_id, stock_id, rec_date));
return;
}
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void cleanupPending()
{
try
{
Database db = new Database();
db.executeNonQuery("delete from pendingDetails where portfolio_id=" + account_id);
db.executeNonQuery("delete from Stockinportfolio where rec like '%PENDING%' and portfolio_id=" + account_id);
}
catch (Exception e)
{
return;
}
}
public static void cleanupPending()
{
try
{
Database db = new Database();
db.executeNonQuery("delete from pendingDetails where portfolio_id=" + account_id);
db.executeNonQuery("delete from Stockinportfolio where rec like '%PENDING%' and portfolio_id=" + account_id);
}
catch (Exception e)
{
return;
}
}
public static void updatePending(int ticket, string order_date, double price)
{
try
{
Database db = new Database();
//extract all information from the ticket and pass to updateOpenLocal
//we can assume that
DataTable dt = db.query(string.Format("select stock_id,portfolio_id,volume,date,type from Stockinportfolio where stock_ticket ={0} and portfolio_id={1}", ticket, account_id));
string stock_id = dt.Rows[0]["stock_id"].ToString();
int portfolio_id = Int32.Parse(dt.Rows[0]["portfolio_id"].ToString());
double volume = Int32.Parse(dt.Rows[0]["volume"].ToString());
string rec_date = dt.Rows[0]["date"].ToString();
string type = dt.Rows[0]["type"].ToString();
//change status from PENDING to '' by setting rec to OPEN and then call updateOpenLocal
db.executeNonQuery(string.Format("update Stockinportfolio set rec ='OPEN', stock_ticket=null where stock_ticket ={0} and portfolio_id={1}", ticket, account_id));
updateOpenLocal(stock_id, portfolio_id, ticket, volume, price, order_date, rec_date, "OPEN", type, "");
}
catch (System.Exception ex)
{
log(ex.ToString());
}
//updateOpen(stock_id, portfolio_id, ticket, volume, price, order_date, rec_date, "OPEN", type);
//updateOpen(string stock_id, int portfolio_id, int ticket, int volume, double price, string order_date, string rec_date, string rec, string type)
//delete the pending order by ticket
//change pending order in stockinportfolio into open
return;
}
public static void updateClose(int ticket, string date, double volume, double price)
{
Database db = new Database();
string sql = "";
DataTable temp;
try
{
//extract information from PositionDetails
temp = db.query(String.Format("select portfolio_id,stock_id,type from PositionDetails where ticket ={0} and portfolio_id={1}", ticket, account_id));
temp.Rows[0]["portfolio_id"].ToString();
string portfolio_id = temp.Rows[0]["portfolio_id"].ToString();
string stock_id = temp.Rows[0]["stock_id"].ToString();
string status = temp.Rows[0]["type"].ToString();
//delete the record in PositionsDetails
sql = string.Format("delete from PositionDetails where ticket={0} and portfolio_id={1}", ticket, account_id);
db.executeNonQuery(sql);
//update volume and price and volume in Stockinportfolio to reflect the change
//if the last record is reached, no update is needed because the record in Stockinportfolio will be deleted eventually
string nvolume = db.query("select SUM(volume) as volume from PositionDetails where portfolio_id=" + account_id).Rows[0]["volume"].ToString();
if (nvolume.Length > 0)
{
int new_volume = Int32.Parse(nvolume);
double new_price = Double.Parse(db.query("select SUM(volume*price)/SUM(volume) as avgPrice from PositionDetails where portfolio_id=" + account_id).Rows[0]["avgPrice"].ToString());
//update new volume and price
sql = string.Format("update Stockinportfolio set volume={3} , avgPrice = {4} where portfolio_id = {0} AND stock_id = '{1}' AND type = '{2}' and rec ='' ;", portfolio_id, stock_id
, status, new_volume, new_price);
db.executeNonQuery(sql);
}
//insert in StockRecord
sql = String.Format("insert into stockrecord(portfolio_id,stock_id,stock_ticket,date,volume,price,status) values({0},'{1}',{2},'{3}',{4},{5},'{6}');",
portfolio_id, stock_id, ticket, date, -volume, price, status);
//log(sql.Replace(";","\n") + "\n");
db.executeNonQuery(sql);
}
catch (System.Exception e)
{
log(e.ToString() + "\n" + sql);
}
}
public static void updateCash(double cash, double capital)
{
try
{
Database db = new Database();
string sql = string.Format("update Portfolio set cash={0},capital={1} where portfolio_id={2};", cash, capital, account_id);
db.executeNonQuery(sql);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void cleanClose()
{
try
{
Database db = new Database();
string sql = String.Format("delete from StockInPortfolio where portfolio_id={0} and (rec ='' or rec like'%CLOSE%') and (select COUNT(P.ticket) from PositionDetails as P where StockInPortfolio.stock_id=P.stock_id and StockInPortfolio.portfolio_id=p.portfolio_id and StockInPortfolio.type=p.type) = 0", account_id);
db.executeNonQuery(sql);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void cleanClose()
{
try
{
Database db = new Database();
string sql = String.Format("delete from StockInPortfolio where portfolio_id={0} and (rec ='' or rec like'%CLOSE%') and (select COUNT(P.ticket) from PositionDetails as P where StockInPortfolio.stock_id=P.stock_id and StockInPortfolio.portfolio_id=p.portfolio_id and StockInPortfolio.type=p.type) = 0",account_id);
db.executeNonQuery(sql);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void updatePrice(string symbol, string date_time, double open, double close, double high, double low, int volume)
{
try
{
Database db = new Database();
db.executeNonQuery(string.Format("insert into Stock(stock_id,date,openPrice, closedPrice,maxPrice,minPrice,matchedVolume ) values('{0}','{1}',{2},{3},{4},{5},{6});", symbol, date_time,
open, close, high, low, volume), false);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void updateOpen(string stock_id, int portfolio_id, int ticket, double volume, double price, string order_date, string rec_date, string rec, string type, string error)
{
//set up connection
Database db = new Database();
//legacy code
SqlConnection myConnection = new SqlConnection(Database.getCString());
try
{
myConnection.Open();
}
catch (System.Exception e)
{
Console.WriteLine(e.ToString());
}
string sql = "";
try
{
//if ticket =-1 aka position is NOT successfully opened then just set stock_ticket=-1 in StockRecommend table
if (ticket < 0)
{
sql = string.Format("update StockRecommend set stock_ticket ='{0}' where portfolio_id={1} and stock_id='{2}' and date =Convert(datetime,'{3}',103) and type ='{4}'",
error, portfolio_id, stock_id, rec_date, type);
db.executeNonQuery(sql);
}
//else
//1. insert order into Stock record
//2. Create new record in PositionDetails
//2. make new record in stockStockInPortfolio with real price date and volume
//if the currency exist then stack up the volume, and update the new price
// 3. update the stock ticket in Stockrecommended
else
{
//1. insert order into stockRecord
sql = string.Format("insert into StockRecord(portfolio_id,stock_id,stock_ticket,date,volume,price,status) values({0},'{1}',{2},'{3}',{4},{5},'{6}');",
portfolio_id, stock_id, ticket, order_date, volume, price, type);
db.executeNonQuery(sql);
//2. Create new record in PositionDetails
sql = string.Format("insert into PositionDetails(portfolio_id,stock_id,type,volume,price,ticket) values ('{0}','{1}','{2}',{3},{4},{5});",
portfolio_id, stock_id, type, volume, price, ticket);
db.executeNonQuery(sql);
//3.create a new record in stockStockInPortfolio with real price date and volume
//check if the current position exists in the portfolio
SqlDataReader myreader = null;
//date is excluded because there cannot exist two records of the same type in Stockinportfolio
string query = string.Format("select volume,avgprice from StockInPortfolio where portfolio_id={0} and stock_id='{1}' and type ='{2}' and rec ='';", portfolio_id, stock_id, type);
SqlCommand mycommand = new SqlCommand(query, myConnection);
myreader = mycommand.ExecuteReader();
//if exist stack up the volume and update the price
if (myreader.Read())
{
int old_volume = int.Parse(myreader["volume"].ToString());
double old_price = Double.Parse(myreader["avgprice"].ToString());
//calculate new price and volume
double new_volume = old_volume + volume;
double new_price = (price * volume + old_price * old_volume) / new_volume;
sql = string.Format("update StockInPortfolio set volume={0}, avgprice={1} where portfolio_id={2} and stock_id='{3}' and type like '%{4}%' and rec ='';", new_volume, new_price, portfolio_id, stock_id, type);
db.executeNonQuery(sql);
}
//else, create new record
else
{
sql = string.Format("insert into StockInPortfolio(stock_id,portfolio_id,type,volume,date,avgPrice,rec) values('{0}',{1},'{2}',{3},'{4}',{5},'');",
stock_id, portfolio_id, type, volume, order_date, price);
db.executeNonQuery(sql);
}
myreader.Close();
//4. update the stock ticket in Stockrecommended
sql = string.Format("update StockRecommend set stock_ticket='{0}, '+stock_ticket, rec ='{1}' where portfolio_id={2} and stock_id like '{3}' and date = Convert(datetime,'{4}',103) ;",
ticket, rec, portfolio_id, stock_id, rec_date);
db.executeNonQuery(sql);
}
db.executeNonQuery(string.Format("delete from Stockinportfolio where portfolio_id={0} and stock_id='{1}' and type like '%{2}%' and rec like '%OPEN%'", portfolio_id, stock_id, type));
myConnection.Close();
}
catch (System.Exception e)
{
log(e.ToString() + "\n" + sql + "\n");
}
}
public static void updatePrice(string symbol, string date_time, double open, double close, double high, double low, int volume)
{
try
{
Database db = new Database();
db.executeNonQuery(string.Format("insert into Stock(stock_id,date,openPrice, closedPrice,maxPrice,minPrice,matchedVolume ) values('{0}','{1}',{2},{3},{4},{5},{6});", symbol, date_time,
open, close, high, low, volume), false);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void updatePending(int ticket, string order_date, double price)
{
try
{
Database db = new Database();
//extract all information from the ticket and pass to updateOpenLocal
//we can assume that
DataTable dt = db.query(string.Format("select stock_id,portfolio_id,volume,date,type from Stockinportfolio where stock_ticket ={0} and portfolio_id={1}", ticket,account_id));
string stock_id = dt.Rows[0]["stock_id"].ToString();
int portfolio_id = Int32.Parse(dt.Rows[0]["portfolio_id"].ToString());
double volume = Int32.Parse(dt.Rows[0]["volume"].ToString());
string rec_date = dt.Rows[0]["date"].ToString();
string type = dt.Rows[0]["type"].ToString();
//change status from PENDING to '' by setting rec to OPEN and then call updateOpenLocal
db.executeNonQuery(string.Format("update Stockinportfolio set rec ='OPEN', stock_ticket=null where stock_ticket ={0} and portfolio_id={1}", ticket, account_id));
updateOpenLocal(stock_id, portfolio_id, ticket, volume, price, order_date, rec_date, "OPEN", type, "");
}
catch (System.Exception ex)
{
log(ex.ToString());
}
//updateOpen(stock_id, portfolio_id, ticket, volume, price, order_date, rec_date, "OPEN", type);
//updateOpen(string stock_id, int portfolio_id, int ticket, int volume, double price, string order_date, string rec_date, string rec, string type)
//delete the pending order by ticket
//change pending order in stockinportfolio into open
return;
}
public static void updateOpenLocal(string stock_id, int portfolio_id, int ticket, double volume, double price, string order_date, string rec_date, string rec, string type, string error)
{
//set up connection
Database db = new Database();
//legacy code
SqlConnection myConnection = new SqlConnection(Database.getCString());
try
{
myConnection.Open();
}
catch (System.Exception e)
{
Console.WriteLine(e.ToString());
}
string sql = "";
try
{
//if ticket =-1 aka position is NOT successfully opened then just set stock_ticket=-1 in StockRecommend table
if (ticket < 0)
{
sql = string.Format("update StockRecommend set stock_ticket ='{0}' where portfolio_id={1} and stock_id='{2}' and date = Convert(datetime,'{3}',103) and type ='{4}'",
error, portfolio_id, stock_id, rec_date, type);
db.executeNonQuery(sql);
}
//else
//1. insert order into Stock record
//2. Create new record in PositionDetails
//2. make new record in stockStockInPortfolio with real price date and volume
//if the currency exist then stack up the volume, and update the new price
// 3. update the stock ticket in Stockrecommended
else
{
//1. insert order into stockRecord
sql = string.Format("insert into StockRecord(portfolio_id,stock_id,stock_ticket,date,volume,price,status) values({0},'{1}',{2},'{3}',{4},{5},'{6}');",
portfolio_id, stock_id, ticket, order_date, volume, price, type);
db.executeNonQuery(sql);
//2. Create new record in PositionDetails
sql = string.Format("insert into PositionDetails(portfolio_id,stock_id,type,volume,price,ticket) values ('{0}','{1}','{2}',{3},{4},{5});",
portfolio_id, stock_id, type, volume, price, ticket);
db.executeNonQuery(sql);
//3.create a new record in stockStockInPortfolio with real price date and volume
//check if the current position exists in the portfolio
SqlDataReader myreader = null;
//date is excluded because there cannot exist two records of the same type in Stockinportfolio
string query = string.Format("select volume,avgprice from StockInPortfolio where portfolio_id={0} and stock_id='{1}' and type ='{2}' and rec ='';", portfolio_id, stock_id, type);
SqlCommand mycommand = new SqlCommand(query, myConnection);
myreader = mycommand.ExecuteReader();
//if exist stack up the volume and update the price
if (myreader.Read())
{
int old_volume = int.Parse(myreader["volume"].ToString());
double old_price = Double.Parse(myreader["avgprice"].ToString());
//calculate new price and volume
double new_volume = old_volume + volume;
double new_price = (price * volume + old_price * old_volume) / new_volume;
sql = string.Format("update StockInPortfolio set volume={0}, avgprice={1} where portfolio_id={2} and stock_id='{3}' and type like '%{4}%' and rec ='';", new_volume, new_price, portfolio_id, stock_id, type);
db.executeNonQuery(sql);
}
//else, create new record
else
{
sql = string.Format("insert into StockInPortfolio(stock_id,portfolio_id,type,volume,date,avgPrice,rec) values('{0}',{1},'{2}',{3},'{4}',{5},'');",
stock_id, portfolio_id, type, volume, order_date, price);
db.executeNonQuery(sql);
}
myreader.Close();
//4. update the stock ticket in Stockrecommended
sql = string.Format("update StockRecommend set stock_ticket='{0}, '+stock_ticket , rec ='{1}' where portfolio_id={2} and stock_id like '{3}' and date = Convert(datetime,'{4}',103) ;",
ticket, rec, portfolio_id, stock_id, rec_date);
db.executeNonQuery(sql);
}
db.executeNonQuery(string.Format("delete from Stockinportfolio where portfolio_id={0} and stock_id='{1}' and type like '%{2}%' and rec like '%OPEN%'", portfolio_id, stock_id, type));
myConnection.Close();
}
catch (System.Exception e)
{
log(e.ToString() + "\n" + sql + "\n");
}
}
public static void updateClose(int ticket, string date, double volume, double price)
{
Database db = new Database();
string sql = "";
DataTable temp;
try
{
//extract information from PositionDetails
temp = db.query(String.Format("select portfolio_id,stock_id,type from PositionDetails where ticket ={0} and portfolio_id={1}", ticket,account_id));
temp.Rows[0]["portfolio_id"].ToString();
string portfolio_id = temp.Rows[0]["portfolio_id"].ToString();
string stock_id = temp.Rows[0]["stock_id"].ToString();
string status = temp.Rows[0]["type"].ToString();
//delete the record in PositionsDetails
sql = string.Format("delete from PositionDetails where ticket={0} and portfolio_id={1}", ticket, account_id);
db.executeNonQuery(sql);
//update volume and price and volume in Stockinportfolio to reflect the change
//if the last record is reached, no update is needed because the record in Stockinportfolio will be deleted eventually
string nvolume = db.query("select SUM(volume) as volume from PositionDetails where portfolio_id="+account_id).Rows[0]["volume"].ToString();
if (nvolume.Length > 0)
{
int new_volume = Int32.Parse(nvolume);
double new_price = Double.Parse(db.query("select SUM(volume*price)/SUM(volume) as avgPrice from PositionDetails where portfolio_id=" + account_id).Rows[0]["avgPrice"].ToString());
//update new volume and price
sql = string.Format("update Stockinportfolio set volume={3} , avgPrice = {4} where portfolio_id = {0} AND stock_id = '{1}' AND type = '{2}' and rec ='' ;", portfolio_id, stock_id
, status, new_volume, new_price);
db.executeNonQuery(sql);
}
//insert in StockRecord
sql = String.Format("insert into stockrecord(portfolio_id,stock_id,stock_ticket,date,volume,price,status) values({0},'{1}',{2},'{3}',{4},{5},'{6}');",
portfolio_id, stock_id, ticket, date, -volume, price, status);
//log(sql.Replace(";","\n") + "\n");
db.executeNonQuery(sql);
}
catch (System.Exception e)
{
log(e.ToString() + "\n" + sql);
}
}
public static void updateCash( double cash, double capital)
{
try
{
Database db = new Database();
string sql = string.Format("update Portfolio set cash={0},capital={1} where portfolio_id={2};", cash, capital, account_id);
db.executeNonQuery(sql);
}
catch (Exception e)
{
log(e.ToString());
}
}
public static void storePending(string stock_id, int portfolio_id, int ticket, double volume, double price, string order_date, string rec_date, string rec, string type, string info)
{
info = "Pending " + info;
try
{
Database db = new Database();
//if ticket =-1 aka position is NOT successfully opened then just set stock_ticket=-1 in StockRecommend table
if (ticket < 0)
{
db.executeNonQuery(string.Format("update StockRecommend set stock_ticket = {0} where portfolio_id={1} and stock_id='{2}' and date =Convert(datetime,'{3}',103) and type ='{4}'",
ticket, portfolio_id, stock_id, rec_date, type));
return;
}
//else
//1. change rec in Stockinportfolio to pending
//2. create new record in pendingDetails
//3. change stock recommend status to pending
else
{
//1. change rec in Stockinportfolio to pending
db.executeNonQuery(string.Format("update StockInPortfolio set rec = 'PENDING' , stock_ticket={0} where portfolio_id={1} and stock_id='{2}' and type like '%{3}%' and rec like '%OPEN%';", ticket, portfolio_id, stock_id, type));
//2. create new record in pendingDetails
db.executeNonQuery(string.Format("insert into PendingDetails(portfolio_id,stock_id,type,volume,price,ticket) values ('{0}','{1}','{2}',{3},{4},{5});",
portfolio_id, stock_id, type, volume, price, ticket));
//3. change stock recommend status to pending
db.executeNonQuery(string.Format("update StockRecommend set stock_ticket='{0}', rec ='{1}' where portfolio_id={2} and stock_id = '{3}' and date = Convert(datetime,'{4}',103);",
info, rec, portfolio_id, stock_id, rec_date));
return;
}
}
catch (Exception e)
{
log(e.ToString());
}
}