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 "";
}
}