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