public static string getAutoClose() { try { string tickets = ""; string stock_id = ""; string portfolio_id = ""; string type = ""; //get all opening positions in Stockinportfolio Database db = new Database(); DataTable table = db.query("select stock_id,portfolio_id,type from StockInPortfolio where rec = '' and portfolio_id=" + account_id); if (table.Rows.Count == 0) { return(""); } //query the ticket of individual order in each positions DataTable ticket_table; foreach (DataRow row in table.Rows) { //make the select ticket query stock_id = row["stock_id"].ToString(); portfolio_id = row["portfolio_id"].ToString(); type = row["type"].ToString(); ticket_table = db.query(String.Format("select ticket from PositionDetails where stock_id='{0}' and portfolio_id={1} and type = '{2}' ", stock_id, portfolio_id, type)); //prepare the tickets string if (ticket_table.Rows.Count == 0) { log("In getAutoClose, one record in Stockinportfolio doesn't have the corresponding children in PositionsDetails " + stock_id + " " + portfolio_id + " " + type); return(""); } foreach (DataRow ticket_row in ticket_table.Rows) { tickets += ticket_row["ticket"].ToString() + ";"; } } if (tickets.Length == 0) { return(""); } return(tickets.Substring(0, tickets.Length - 1)); } catch (Exception e) { log(e.ToString()); return(""); } }
public static string getPendingDetails() { Database db = new Database(); try { DataTable table = db.query("select ticket,stock_id,portfolio_id,type from pendingDetails where portfolio_id=" + account_id); string orders = ""; foreach (DataRow row in table.Rows) { orders += row["ticket"] + ";"; } if (orders.Length == 0) { return(""); } else { return(orders.Substring(0, orders.Length - 1)); } } catch (Exception e) { Database.log_sql_error(e); return(""); } }
public static string getClose() { String sql = ""; try { Database db = new Database(); sql = string.Format("select ticket,P.stock_id,S.portfolio_id,S.type from StockInPortfolio as S join PositionDetails as P on S.stock_id = P.stock_id and S.portfolio_id=P.portfolio_id and S.type = P.type where S.rec like '%CLOSE%' and S.portfolio_id=" + account_id); DataTable dt = db.query(sql); //get string of closed positions string order = ""; foreach (DataRow row in dt.Rows) { order += row["ticket"].ToString() + ";"; } if (order.Length == 0) { return(""); } else { return(order.Substring(0, order.Length - 1)); } } 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 string getAutoClose() { try { string tickets = ""; string stock_id = ""; string portfolio_id = ""; string type = ""; //get all opening positions in Stockinportfolio Database db = new Database(); DataTable table = db.query("select stock_id,portfolio_id,type from StockInPortfolio where rec = '' and portfolio_id=" + account_id); if (table.Rows.Count == 0) return ""; //query the ticket of individual order in each positions DataTable ticket_table; foreach (DataRow row in table.Rows) { //make the select ticket query stock_id = row["stock_id"].ToString(); portfolio_id = row["portfolio_id"].ToString(); type = row["type"].ToString(); ticket_table = db.query(String.Format("select ticket from PositionDetails where stock_id='{0}' and portfolio_id={1} and type = '{2}' ", stock_id, portfolio_id, type)); //prepare the tickets string if (ticket_table.Rows.Count == 0) { log("In getAutoClose, one record in Stockinportfolio doesn't have the corresponding children in PositionsDetails " + stock_id + " " + portfolio_id + " " + type); return ""; } foreach (DataRow ticket_row in ticket_table.Rows) { tickets += ticket_row["ticket"].ToString() + ";"; } } if (tickets.Length == 0) return ""; return tickets.Substring(0, tickets.Length - 1); } catch (Exception e) { log(e.ToString()); 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 string getOpen() { String sql = ""; try { Database db = new Database(); sql = "select stock_id,portfolio_id,type,volume,avgPrice,rec,date from stockinportfolio where rec like 'OPEN%' and portfolio_id=" + account_id; DataTable dt = db.query(sql); string order = ""; foreach (DataRow Row in dt.Rows) { order += Row["stock_id"].ToString() + ","; order += Row["portfolio_id"].ToString() + ","; order += Row["type"].ToString() + ","; order += Row["volume"].ToString() + ","; order += Row["avgPrice"].ToString() + ","; order += Row["rec"].ToString() + ","; order += Row["date"].ToString(); order += ";"; } if (order.Length == 0) { return(""); } else { return(order.Substring(0, order.Length - 1)); } } catch (Exception e) { Database.log_sql_error(e, sql); 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 string getPendingDetails() { Database db = new Database(); try { DataTable table = db.query("select ticket,stock_id,portfolio_id,type from pendingDetails where portfolio_id=" + account_id); string orders = ""; foreach (DataRow row in table.Rows) { orders += row["ticket"] + ";"; } if (orders.Length == 0) return ""; else return orders.Substring(0, orders.Length - 1); } catch (Exception e) { Database.log_sql_error(e); return ""; } }
public static string getOpen() { String sql = ""; try { Database db = new Database(); sql = "select stock_id,portfolio_id,type,volume,avgPrice,rec,date from stockinportfolio where rec like 'OPEN%' and portfolio_id=" + account_id; DataTable dt = db.query(sql); string order = ""; foreach (DataRow Row in dt.Rows) { order += Row["stock_id"].ToString() + ","; order += Row["portfolio_id"].ToString() + ","; order += Row["type"].ToString() + ","; order += Row["volume"].ToString() + ","; order += Row["avgPrice"].ToString() + ","; order += Row["rec"].ToString() + ","; order += Row["date"].ToString(); order += ";"; } if (order.Length == 0) return ""; else return order.Substring(0, order.Length - 1); } catch (Exception e) { Database.log_sql_error(e, sql); return ""; } }
public static string getClose() { String sql = ""; try { Database db = new Database(); sql = string.Format("select ticket,P.stock_id,S.portfolio_id,S.type from StockInPortfolio as S join PositionDetails as P on S.stock_id = P.stock_id and S.portfolio_id=P.portfolio_id and S.type = P.type where S.rec like '%CLOSE%' and S.portfolio_id="+account_id); DataTable dt = db.query(sql); //get string of closed positions string order = ""; foreach (DataRow row in dt.Rows) { order += row["ticket"].ToString() + ";"; } if (order.Length == 0) return ""; else return order.Substring(0, order.Length - 1); } catch (Exception e) { return ""; } }