artmedia70/Article-16233-MQL5-SQLite-Trade-Statistics-Panel
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
Article-16233-MQL5-SQLite-T.../SQLiteFunc.mqh

625 lines
64 KiB
MQL5
Raw Permalink Normal View History

new files added
2026-03-23 15:27:40 +07:00
<EFBFBD><EFBFBD>//+------------------------------------------------------------------+
//| SQLiteFunc.mqh |
//| Copyright 2024, MetaQuotes Ltd. |
//| https://www.mql5.com |
//+------------------------------------------------------------------+
#property copyright "Copyright 2024, MetaQuotes Ltd."
#property link "https://www.mql5.com"
//+------------------------------------------------------------------+
//| !B@C:BC@0 4;O E@0=5=8O A45;:8 |
//+------------------------------------------------------------------+
struct SDeal
{
long account; // ACCOUNT
ulong ticket; // DEAL_TICKET
long order_ticket; // DEAL_ORDER
long position_ticket; // DEAL_POSITION_ID
datetime time; // DEAL_TIME
char type; // DEAL_TYPE
char entry; // DEAL_ENTRY
string symbol; // DEAL_SYMBOL
double volume; // DEAL_VOLUME
double price; // DEAL_PRICE
double profit; // DEAL_PROFIT
double swap; // DEAL_SWAP
double commission; // DEAL_COMMISSION
long magic; // DEAL_MAGIC
char reason; // DEAL_REASON
};
//+------------------------------------------------------------------+
//| !B@C:BC@0 4;O E@0=5=8O B@5940: |
//| ?>@O4>: G;5=>2 A>>B25BAB2C5B ?>78F88 2 B5@<8=0;5 |
//+------------------------------------------------------------------+
struct STrade
{
long account; // =><5@ AGQB0
datetime time_in; // 2@5<O 2E>40
ulong ticket; // ID ?>78F88
char type; // ?>:C?:0 8;8 ?@>4060
double volume; // >1J5<
string symbol; // A8<2>;
double price_in; // F5=0 2E>40
datetime time_out; // 2@5<O 2KE>40
double price_out; // F5=0 2KE>40
double commission; // :><8AA8O 70 2E>4 8 2KE>4
double swap; // A2>?
double profit; // ?@81K;L 8;8 C1KB>:
};
//+------------------------------------------------------------------+
//| !B@C:BC@0 4;O E@0=5=8O AB0B8AB8:8 ?> A8<2>;C |
//+------------------------------------------------------------------+
struct SSymbolStats
{
string name; // 8<O A8<2>;0
int trades; // :>;8G5AB2> B@594>2 ?> A8<2>;C
double gross_profit; // >1I0O ?@81K;L ?> A8<2>;C
double gross_loss; // >1I89 C1KB>: ?> A8<2>;C
double total_commission; // AC<<0 :><8AA89 ?> A8<2>;C
double total_swap; // AC<<0 A2>?>2 ?> A8<2>;C
double total_profit; // >1I0O ?@81K;L 157 CG5B0 A2>?>2 8 :><8AA89
double net_profit; // G8AB0O ?@81K;L A CG5B>< A2>?>2 8 :><8AA89
int win_trades; // :>;8G5AB2> ?@81K;L=KE B@594>2
int loss_trades; // :>;8G5AB2> C1KB>G=KE B@594>2
long long_trades; // ?>:C?:8
long short_trades; // ?@>4068
double expected_payoff; // <0B>6840=85 B@5940 157 CG5B0 A2>?>2 8 :><8AA88
double win_percent; // ?@>F5=B 2K83@KH=KE B@594>2
double loss_percent; // ?@>F5=B ?@>83@KH=KE B@594>2
double average_profit; // A@54=OO ?@81K;L
double average_loss; // A@54=89 C1KB>:
double profit_factor; // ?@>D8B-D0:B>@
};
//+------------------------------------------------------------------+
//| !B@C:BC@0 4;O E@0=5=8O AB0B8AB8:8 ?> Magic Number |
//+------------------------------------------------------------------+
struct SMagicStats
{
long magic; // Magic Number A>25B=8:0
int trades; // :>;8G5AB2> B@594>2 ?> A8<2>;C
double gross_profit; // >1I0O ?@81K;L ?> A8<2>;C
double gross_loss; // >1I89 C1KB>: ?> A8<2>;C
double total_commission; // AC<<0 :><8AA89 ?> A8<2>;C
double total_swap; // AC<<0 A2>?>2 ?> A8<2>;C
double total_profit; // >1I0O ?@81K;L 157 CG5B0 A2>?>2 8 :><8AA89
double net_profit; // G8AB0O ?@81K;L A CG5B>< A2>?>2 8 :><8AA89
int win_trades; // :>;8G5AB2> ?@81K;L=KE B@594>2
int loss_trades; // :>;8G5AB2> C1KB>G=KE B@594>2
long long_trades; // ?>:C?:8
long short_trades; // ?@>4068
double expected_payoff; // <0B>6840=85 B@5940 157 CG5B0 A2>?>2 8 :><8AA88
double win_percent; // ?@>F5=B 2K83@KH=KE B@594>2
double loss_percent; // ?@>F5=B ?@>83@KH=KE B@594>2
double average_profit; // A@54=OO ?@81K;L
double average_loss; // A@54=89 C1KB>:
double profit_factor; // ?@>D8B-D0:B>@
};
//+------------------------------------------------------------------+
//| !B@C:BC@0 4;O E@0=5=8O AB0B8AB8:8 ?> AGQBC |
//+------------------------------------------------------------------+
struct SAccountStats
{
long account; // =><5@ AGQB0
int trades; // :>;8G5AB2> B@594>2 ?> A8<2>;C
double gross_profit; // >1I0O ?@81K;L ?> A8<2>;C
double gross_loss; // >1I89 C1KB>: ?> A8<2>;C
double total_commission; // AC<<0 :><8AA89 ?> A8<2>;C
double total_swap; // AC<<0 A2>?>2 ?> A8<2>;C
double total_profit; // >1I0O ?@81K;L 157 CG5B0 A2>?>2 8 :><8AA89
double net_profit; // G8AB0O ?@81K;L A CG5B>< A2>?>2 8 :><8AA89
int win_trades; // :>;8G5AB2> ?@81K;L=KE B@594>2
int loss_trades; // :>;8G5AB2> C1KB>G=KE B@594>2
long long_trades; // ?>:C?:8
long short_trades; // ?@>4068
double expected_payoff; // <0B>6840=85 B@5940 157 CG5B0 A2>?>2 8 :><8AA88
double win_percent; // ?@>F5=B 2K83@KH=KE B@594>2
double loss_percent; // ?@>F5=B ?@>83@KH=KE B@594>2
double average_profit; // A@54=OO ?@81K;L
double average_loss; // A@54=89 C1KB>:
double profit_factor; // ?@>D8B-D0:B>@
};
//+------------------------------------------------------------------+
//| 0?@0H8205B 8AB>@8N A45;>: 70 C:070==K9 ?5@8>4 |
//+------------------------------------------------------------------+
bool GetHistoryDeals(const datetime from_date, const datetime to_date)
{
ResetLastError();
if(HistorySelect(from_date, to_date))
return true;
Print("HistorySelect() failed. Error ", GetLastError());
return false;
}
//+------------------------------------------------------------------+
//| #40;O5B 87 107K B01;8FC A C:070==K< 8<5=5< |
//+------------------------------------------------------------------+
bool DeleteTable(int database, string table_name)
{
ResetLastError();
if(!DatabaseExecute(database, "DROP TABLE IF EXISTS "+table_name))
{
Print("Failed to drop the DEALS table with code ", GetLastError());
return(false);
}
//--- B01;8F0 CA?5H=> C40;5=0
return(true);
}
//+------------------------------------------------------------------+
//| !>7405B B01;8FC DEALS |
//+------------------------------------------------------------------+
bool CreateTableDeals(int database)
{
//--- 5A;8 B01;8F0 DEALS C65 5ABL, C40;8< 5Q
if(!DeleteTable(database, "DEALS"))
return(false);
//--- ?@>25@8< =0;8G85 B01;8FK
ResetLastError();
if(!DatabaseTableExists(database, "DEALS"))
//--- A>7405< B01;8FC
if(!DatabaseExecute(database, "CREATE TABLE DEALS("
"ID INT KEY NOT NULL,"
"ACCOUNT INT NOT NULL,"
"ORDER_ID INT NOT NULL,"
"POSITION_ID INT NOT NULL,"
"TIME INT NOT NULL,"
"TYPE INT NOT NULL,"
"ENTRY INT NOT NULL,"
"SYMBOL CHAR(10),"
"VOLUME REAL,"
"PRICE REAL,"
"PROFIT REAL,"
"SWAP REAL,"
"COMMISSION REAL,"
"MAGIC INT,"
"REASON INT );"))
{
Print("DB: create the DEALS table failed with code ", GetLastError());
return(false);
}
//--- B01;8F0 CA?5H=> A>740=0
return(true);
}
//+------------------------------------------------------------------+
//| !>7405B B01;8FC TRADES |
//+------------------------------------------------------------------+
bool CreateTableTrades(int database)
{
//--- 5A;8 B01;8F0 TRADES C65 5ABL, C40;8< 5Q
if(!DeleteTable(database, "TRADES"))
return(false);
//--- ?@>25@8< =0;8G85 B01;8FK
ResetLastError();
if(!DatabaseTableExists(database, "TRADES"))
//--- A>7405< B01;8FC
if(!DatabaseExecute(database, "CREATE TABLE TRADES("
"ACCOUNT INT NOT NULL,"
"TIME_IN INT NOT NULL,"
"TICKET INT NOT NULL,"
"TYPE INT NOT NULL,"
"VOLUME REAL,"
"SYMBOL CHAR(10),"
"PRICE_IN REAL,"
"TIME_OUT INT NOT NULL,"
"PRICE_OUT REAL,"
"COMMISSION REAL,"
"SWAP REAL,"
"PROFIT REAL);"))
{
Print("DB: create the TRADES table failed with code ", GetLastError());
return(false);
}
//--- B01;8F0 CA?5H=> A>740=0
return(true);
}
//+------------------------------------------------------------------+
//| =>A8B A45;:8 2 B01;8FC 107K 40==KE |
//+------------------------------------------------------------------+
bool InsertDeals(int database)
{
//--- 2A?><>30B5;L=K5 ?5@5<5==K5
long account_login=AccountInfoInteger(ACCOUNT_LOGIN); // =><5@ AGQB0
ulong deal_ticket; // B8:5B A45;:8
long order_ticket; // B8:5B >@45@0,?> :>B>@><C 1K;0 A>25@H5=0 A45;:0
long position_ticket; // ID ?>78F88, : :>B>@>9 >B=>A8BAO A45;:0
datetime time; // 2@5<O A>25@H5=8O A45;:8
long type ; // B8? A45;:8
long entry ; // =0?@02;5=85 A45;:8
string symbol; // ?> :0:><C A8<2>;C 1K;0 A45;:0
double volume; // >1J5< >?5@0F88
double price; // F5=0
double profit; // D8=0=A>2K9 @57C;LB0B
double swap; // A2>?
double commission; // :><8AA8O
long magic; // Magic number (ID A>25B=8:0)
long reason; // ?@8G8=0 8;8 8AB>G=8: ?@>2545=8O A45;:8
//--- ?@>945< ?> 2A5< A45;:0< 8 2=5A5< 8E 2 107C 40==KE
bool failed=false;
int deals=HistoryDealsTotal();
//--- 701;>:8@C5< 107C 40==KE ?5@54 2K?>;=5=85< B@0=70:F89
DatabaseTransactionBegin(database);
ResetLastError();
for(int i=0; i<deals; i++)
{
deal_ticket= HistoryDealGetTicket(i);
order_ticket= HistoryDealGetInteger(deal_ticket, DEAL_ORDER);
position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID);
time= (datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME);
type= HistoryDealGetInteger(deal_ticket, DEAL_TYPE);
entry= HistoryDealGetInteger(deal_ticket, DEAL_ENTRY);
symbol= HistoryDealGetString(deal_ticket, DEAL_SYMBOL);
volume= HistoryDealGetDouble(deal_ticket, DEAL_VOLUME);
price= HistoryDealGetDouble(deal_ticket, DEAL_PRICE);
profit= HistoryDealGetDouble(deal_ticket, DEAL_PROFIT);
swap= HistoryDealGetDouble(deal_ticket, DEAL_SWAP);
commission= HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION);
magic= HistoryDealGetInteger(deal_ticket, DEAL_MAGIC);
reason= HistoryDealGetInteger(deal_ticket, DEAL_REASON);
//--- 2=5A5< 2 B01;8FC :064CN A45;:C G5@57 70?@>A
string request_text=StringFormat("INSERT INTO DEALS (ID,ACCOUNT,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)"
"VALUES (%I64d, %I64d, %I64d, %I64d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %I64d, %d)",
deal_ticket, account_login, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason);
if(!DatabaseExecute(database, request_text))
{
PrintFormat("%s: failed to insert deal #%d with code %d", __FUNCTION__, deal_ticket, GetLastError());
PrintFormat("i=%d: deal #%d %s", i, deal_ticket, symbol);
failed=true;
break;
}
}
//--- ?@>25@8< =0 =0;8G85 >H81>: ?@8 2K?>;=5=88 B@0=70:F89
if(failed)
{
//--- >B:0B8< 2A5 B@0=70:F88 8 @071;>:8@C5< 107C 40==KE
DatabaseTransactionRollback(database);
PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError());
return(false);
}
//--- 2A5 B@0=70:F88 ?@>H;8 CA?5H=> - 70D8:A8@C5< 87<5=5=8O 8 @071;>:8@C5< 107C 40==KE
DatabaseTransactionCommit(database);
return(true);
}
//+------------------------------------------------------------------+
//| 0?>;=O5B B01;8FC TRADES =0 >A=>25 B01;8FK DEALS |
//+------------------------------------------------------------------+
bool FillTRADEStableBasedOnDEALStable(int database)
{
if(!DatabaseTableExists(database, "DEALS"))
{
PrintFormat("%s: Error. DEALS table is missing in the database", __FUNCTION__);
return false;
}
//--- 70?>;=8< B01;8FC TRADES
if(!DatabaseExecute(database, "INSERT INTO TRADES(TIME_IN,ACCOUNT,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) "
"SELECT "
" d1.time as time_in,"
" d1.account as account,"
" d1.position_id as ticket,"
" d1.type as type,"
" d1.volume as volume,"
" d1.symbol as symbol,"
" d1.price as price_in,"
" d2.time as time_out,"
" d2.price as price_out,"
" d1.commission+d2.commission as commission,"
" d2.swap as swap,"
" d2.profit as profit "
"FROM DEALS d1 "
"INNER JOIN DEALS d2 ON d1.position_id=d2.position_id "
"WHERE d1.entry=0 AND d2.entry=1"))
{
Print("DB: fillng the TRADES table failed with code ", GetLastError());
return false;
}
return true;
}
//+------------------------------------------------------------------+
//| 0?>;=O5B 87  A?8A>: 2A5E B@594>2 |
//+------------------------------------------------------------------+
bool FillsListTradesFromDB(int database, string db_name, STrade &array[])
{
STrade trade;
ResetLastError();
//--- 0?@>A8< 87  A?8A>: B@594>2, >BA>@B8@>20==K9 ?> C1K20=8N 2@5<5=8 2E>40 2 @K=>:
int request=DatabasePrepare(database, "SELECT * FROM TRADES ORDER BY time_in DESC");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- @>G8B05< 2 <0AA82 AB@C:BC@ 40==K5 A>740==>9 B01;8FK B@594>2
for(int i=0; DatabaseReadBind(request, trade); i++)
{
ArrayResize(array, i+1);
array[i].account=trade.account;
array[i].time_in=trade.time_in;
array[i].ticket=trade.ticket;
array[i].type=trade.type;
array[i].volume=trade.volume;
array[i].symbol=trade.symbol;
array[i].price_in=trade.price_in;
array[i].time_out=trade.time_out;
array[i].price_out=trade.price_out;
array[i].commission=trade.commission;
array[i].swap=trade.swap;
array[i].profit=trade.profit;
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+------------------------------------------------------------------+
//| 0?>;=O5B 87  A?8A>: 2A5E A8<2>;>2 |
//+------------------------------------------------------------------+
bool FillsListSymbolsFromDB(int database, string db_name, string &array[])
{
//--- @>25@8< =0;8G85 A>740==>9 B01;8FK B@594>2 2 1075 40==KE
ResetLastError();
if(!DatabaseTableExists(database, "TRADES"))
{
//--- A;8 B01;8F0 5IQ =5 A>740=0 - A>>1I8< > B><, :0: 5Q A>740BL
if(GetLastError()==5126)
Alert("First you need to get the trade history.\nClick the \"Get trade history\" button.");
else
Print("DatabaseTableExists() failed. Error ",GetLastError());
return false;
}
//--- 70?@>A8< 87  A?8A>: 2A5E A8<2>;>2, =0 :>B>@KE >ACI5AB2;O;0AL B>@3>2;O. !?8A>: >BA>@B8@>20= ?> 0;D028BC
int request=DatabasePrepare(database, "SELECT DISTINCT symbol FROM TRADES ORDER BY symbol ASC");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- @>G8B05< 2 <0AA82 40==K5 A>740==>9 B01;8FK A8<2>;>2
for(int i=0; DatabaseRead(request); i++)
{
ArrayResize(array, i+1);
DatabaseColumnText(request, 0, array[i]);
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+------------------------------------------------------------------+
//| 0?>;=O5B 87  A?8A>: 2A5E <038:>2 |
//+------------------------------------------------------------------+
bool FillsListMagicsFromDB(int database, string db_name, long &array[])
{
//--- @>25@8< =0;8G85 A>740==>9 B01;8FK B@594>2 2 1075 40==KE
ResetLastError();
if(!DatabaseTableExists(database, "DEALS"))
{
//--- A;8 B01;8F0 5IQ =5 A>740=0 - A>>1I8< > B><, :0: 5Q A>740BL
if(GetLastError()==5126)
Alert("First you need to get the trade history.\nClick the \"Get trade history\" button.");
else
Print("DatabaseTableExists() failed. Error ",GetLastError());
return false;
}
//--- 70?@>A8< 87  A?8A>: 2A5E <038:>2, =0 :>B>@KE >ACI5AB2;O;0AL B>@3>2;O. !?8A>: >BA>@B8@>20= ?> 2>7@0AB0=8N
int request=DatabasePrepare(database, "SELECT DISTINCT magic FROM DEALS ORDER BY magic ASC");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- @>G8B05< 2 <0AA82 40==K5 A>740==>9 B01;8FK <038:>2
for(int i=0; DatabaseRead(request); i++)
{
ArrayResize(array, i+1);
DatabaseColumnLong(request, 0, array[i]);
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+-------------------------------------------------------------------+
//|>;CG05B 87  8 A>E@0=O5B 2 <0AA82 AB0B8AB8:C B>@3>2;8 ?> A8<2>;0<|
//+-------------------------------------------------------------------+
bool GetTradingStatsBySymbols(int database, string db_name, SSymbolStats &array[])
{
int request=DatabasePrepare(database, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, "
" r.long_trades as long_trades,"
" r.short_trades as short_trades "
"FROM "
" ("
" SELECT SYMBOL,"
" sum(case when entry =1 then 1 else 0 end) as trades,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(swap) as total_swap,"
" sum(commission) as total_commission,"
" sum(profit) as total_profit,"
" sum(profit+swap+commission) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades, "
" sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, "
" sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades "
" FROM DEALS "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY SYMBOL ORDER BY net_profit DESC"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- ?>;CG05< 70?8A8 87 @57C;LB0B>2 70?@>A0
SSymbolStats symbol_stats;
for(int i=0; DatabaseReadBind(request, symbol_stats) ; i++)
{
ArrayResize(array, i+1);
array[i].name=symbol_stats.name;
array[i].trades=symbol_stats.trades;
array[i].long_trades=symbol_stats.long_trades;
array[i].short_trades=symbol_stats.short_trades;
array[i].gross_profit=symbol_stats.gross_profit;
array[i].gross_loss=symbol_stats.gross_loss;
array[i].total_commission=symbol_stats.total_commission;
array[i].total_swap=symbol_stats.total_swap;
array[i].total_profit=symbol_stats.total_profit;
array[i].net_profit=symbol_stats.net_profit;
array[i].win_trades=symbol_stats.win_trades;
array[i].loss_trades=symbol_stats.loss_trades;
array[i].expected_payoff=symbol_stats.expected_payoff;
array[i].win_percent=symbol_stats.win_percent;
array[i].loss_percent=symbol_stats.loss_percent;
array[i].average_profit=symbol_stats.average_profit;
array[i].average_loss=symbol_stats.average_loss;
array[i].profit_factor=symbol_stats.profit_factor;
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+------------------------------------------------------------------+
//|>;CG05B 87  8 A>E@0=O5B 2 <0AA82 AB0B8AB8:C B>@3>2;8 ?> <038:0<|
//+------------------------------------------------------------------+
bool GetTradingStatsByMagics(int database, string db_name, SMagicStats &array[])
{
int request=DatabasePrepare(database, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, "
" r.long_trades as long_trades,"
" r.short_trades as short_trades "
"FROM "
" ("
" SELECT MAGIC,"
" sum(case when entry =1 then 1 else 0 end) as trades,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(swap) as total_swap,"
" sum(commission) as total_commission,"
" sum(profit) as total_profit,"
" sum(profit+swap+commission) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades, "
" sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, "
" sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades "
" FROM DEALS "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY MAGIC ORDER BY net_profit DESC"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- ?>;CG05< 70?8A8 87 @57C;LB0B>2 70?@>A0
SMagicStats magic_stats;
for(int i=0; DatabaseReadBind(request, magic_stats) ; i++)
{
ArrayResize(array, i+1);
array[i].magic=magic_stats.magic;
array[i].trades=magic_stats.trades;
array[i].long_trades=magic_stats.long_trades;
array[i].short_trades=magic_stats.short_trades;
array[i].gross_profit=magic_stats.gross_profit;
array[i].gross_loss=magic_stats.gross_loss;
array[i].total_commission=magic_stats.total_commission;
array[i].total_swap=magic_stats.total_swap;
array[i].total_profit=magic_stats.total_profit;
array[i].net_profit=magic_stats.net_profit;
array[i].win_trades=magic_stats.win_trades;
array[i].loss_trades=magic_stats.loss_trades;
array[i].expected_payoff=magic_stats.expected_payoff;
array[i].win_percent=magic_stats.win_percent;
array[i].loss_percent=magic_stats.loss_percent;
array[i].average_profit=magic_stats.average_profit;
array[i].average_loss=magic_stats.average_loss;
array[i].profit_factor=magic_stats.profit_factor;
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+------------------------------------------------------------------+
//| >;CG05B 87  8 A>E@0=O5B 2 <0AA82 AB0B8AB8:C B>@3>2;8 ?> AGQBC |
//+------------------------------------------------------------------+
bool GetTradingStatsByAccount(int database, string db_name, SAccountStats &array[])
{
int request=DatabasePrepare(database, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor, "
" r.long_trades as long_trades,"
" r.short_trades as short_trades "
"FROM "
" ("
" SELECT ACCOUNT,"
" sum(case when entry =1 then 1 else 0 end) as trades,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(swap) as total_swap,"
" sum(commission) as total_commission,"
" sum(profit) as total_profit,"
" sum(profit+swap+commission) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades, "
" sum(case when type = 0 AND entry = 0 then 1 else 0 end) as long_trades, "
" sum(case when type = 1 AND entry = 0 then 1 else 0 end) as short_trades "
" FROM DEALS "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY ACCOUNT ORDER BY net_profit DESC"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", db_name, " request failed with code ", GetLastError());
DatabaseClose(database);
return false;
}
//--- ?>;CG05< 70?8A8 87 @57C;LB0B>2 70?@>A0
SAccountStats account_stats;
for(int i=0; DatabaseReadBind(request, account_stats) ; i++)
{
ArrayResize(array, i+1);
array[i].account=account_stats.account;
array[i].trades=account_stats.trades;
array[i].long_trades=account_stats.long_trades;
array[i].short_trades=account_stats.short_trades;
array[i].gross_profit=account_stats.gross_profit;
array[i].gross_loss=account_stats.gross_loss;
array[i].total_commission=account_stats.total_commission;
array[i].total_swap=account_stats.total_swap;
array[i].total_profit=account_stats.total_profit;
array[i].net_profit=account_stats.net_profit;
array[i].win_trades=account_stats.win_trades;
array[i].loss_trades=account_stats.loss_trades;
array[i].expected_payoff=account_stats.expected_payoff;
array[i].win_percent=account_stats.win_percent;
array[i].loss_percent=account_stats.loss_percent;
array[i].average_profit=account_stats.average_profit;
array[i].average_loss=account_stats.average_loss;
array[i].profit_factor=account_stats.profit_factor;
}
//--- C40;8< 70?@>A ?>A;5 8A?>;L7>20=8O
DatabaseFinalize(request);
return true;
}
//+------------------------------------------------------------------+
Reference in a new issue Copy permalink