624 lines
64 KiB
MQL5
624 lines
64 KiB
MQL5
//+------------------------------------------------------------------+
|
|
//| SQLiteFunc.mqh |
|
|
//| Copyright 2024, MetaQuotes Ltd. |
|
|
//| https://www.mql5.com |
|
|
//+------------------------------------------------------------------+
|
|
#property copyright "Copyright 2024, MetaQuotes Ltd."
|
|
#property link "https://www.mql5.com"
|
|
//+------------------------------------------------------------------+
|
|
//| Структура для хранения сделки |
|
|
//+------------------------------------------------------------------+
|
|
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
|
|
};
|
|
//+------------------------------------------------------------------+
|
|
//| Структура для хранения трейда: |
|
|
//| порядок членов соответствует позиции в терминале |
|
|
//+------------------------------------------------------------------+
|
|
struct STrade
|
|
{
|
|
long account; // номер счёта
|
|
datetime time_in; // время входа
|
|
ulong ticket; // ID позиции
|
|
char type; // покупка или продажа
|
|
double volume; // объем
|
|
string symbol; // символ
|
|
double price_in; // цена входа
|
|
datetime time_out; // время выхода
|
|
double price_out; // цена выхода
|
|
double commission; // комиссия за вход и выход
|
|
double swap; // своп
|
|
double profit; // прибыль или убыток
|
|
};
|
|
//+------------------------------------------------------------------+
|
|
//| Структура для хранения статистики по символу |
|
|
//+------------------------------------------------------------------+
|
|
struct SSymbolStats
|
|
{
|
|
string name; // имя символа
|
|
int trades; // количество трейдов по символу
|
|
double gross_profit; // общая прибыль по символу
|
|
double gross_loss; // общий убыток по символу
|
|
double total_commission; // сумма комиссий по символу
|
|
double total_swap; // сумма свопов по символу
|
|
double total_profit; // общая прибыль без учета свопов и комиссий
|
|
double net_profit; // чистая прибыль с учетом свопов и комиссий
|
|
int win_trades; // количество прибыльных трейдов
|
|
int loss_trades; // количество убыточных трейдов
|
|
long long_trades; // покупки
|
|
long short_trades; // продажи
|
|
double expected_payoff; // матожидание трейда без учета свопов и комиссии
|
|
double win_percent; // процент выигрышных трейдов
|
|
double loss_percent; // процент проигрышных трейдов
|
|
double average_profit; // средняя прибыль
|
|
double average_loss; // средний убыток
|
|
double profit_factor; // профит-фактор
|
|
};
|
|
//+------------------------------------------------------------------+
|
|
//| Структура для хранения статистики по Magic Number |
|
|
//+------------------------------------------------------------------+
|
|
struct SMagicStats
|
|
{
|
|
long magic; // Magic Number советника
|
|
int trades; // количество трейдов по символу
|
|
double gross_profit; // общая прибыль по символу
|
|
double gross_loss; // общий убыток по символу
|
|
double total_commission; // сумма комиссий по символу
|
|
double total_swap; // сумма свопов по символу
|
|
double total_profit; // общая прибыль без учета свопов и комиссий
|
|
double net_profit; // чистая прибыль с учетом свопов и комиссий
|
|
int win_trades; // количество прибыльных трейдов
|
|
int loss_trades; // количество убыточных трейдов
|
|
long long_trades; // покупки
|
|
long short_trades; // продажи
|
|
double expected_payoff; // матожидание трейда без учета свопов и комиссии
|
|
double win_percent; // процент выигрышных трейдов
|
|
double loss_percent; // процент проигрышных трейдов
|
|
double average_profit; // средняя прибыль
|
|
double average_loss; // средний убыток
|
|
double profit_factor; // профит-фактор
|
|
};
|
|
//+------------------------------------------------------------------+
|
|
//| Структура для хранения статистики по счёту |
|
|
//+------------------------------------------------------------------+
|
|
struct SAccountStats
|
|
{
|
|
long account; // номер счёта
|
|
int trades; // количество трейдов по символу
|
|
double gross_profit; // общая прибыль по символу
|
|
double gross_loss; // общий убыток по символу
|
|
double total_commission; // сумма комиссий по символу
|
|
double total_swap; // сумма свопов по символу
|
|
double total_profit; // общая прибыль без учета свопов и комиссий
|
|
double net_profit; // чистая прибыль с учетом свопов и комиссий
|
|
int win_trades; // количество прибыльных трейдов
|
|
int loss_trades; // количество убыточных трейдов
|
|
long long_trades; // покупки
|
|
long short_trades; // продажи
|
|
double expected_payoff; // матожидание трейда без учета свопов и комиссии
|
|
double win_percent; // процент выигрышных трейдов
|
|
double loss_percent; // процент проигрышных трейдов
|
|
double average_profit; // средняя прибыль
|
|
double average_loss; // средний убыток
|
|
double profit_factor; // профит-фактор
|
|
};
|
|
//+------------------------------------------------------------------+
|
|
//| Запрашивает историю сделок за указанный период |
|
|
//+------------------------------------------------------------------+
|
|
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;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Удаляет из базы таблицу с указанным именем |
|
|
//+------------------------------------------------------------------+
|
|
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);
|
|
}
|
|
//--- таблица успешно удалена
|
|
return(true);
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Создает таблицу DEALS |
|
|
//+------------------------------------------------------------------+
|
|
bool CreateTableDeals(int database)
|
|
{
|
|
//--- если таблица DEALS уже есть, удалим её
|
|
if(!DeleteTable(database, "DEALS"))
|
|
return(false);
|
|
|
|
//--- проверим наличие таблицы
|
|
ResetLastError();
|
|
if(!DatabaseTableExists(database, "DEALS"))
|
|
//--- создаем таблицу
|
|
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);
|
|
}
|
|
//--- таблица успешно создана
|
|
return(true);
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Создает таблицу TRADES |
|
|
//+------------------------------------------------------------------+
|
|
bool CreateTableTrades(int database)
|
|
{
|
|
//--- если таблица TRADES уже есть, удалим её
|
|
if(!DeleteTable(database, "TRADES"))
|
|
return(false);
|
|
//--- проверим наличие таблицы
|
|
ResetLastError();
|
|
if(!DatabaseTableExists(database, "TRADES"))
|
|
//--- создаем таблицу
|
|
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);
|
|
}
|
|
//--- таблица успешно создана
|
|
return(true);
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Вносит сделки в таблицу базы данных |
|
|
//+------------------------------------------------------------------+
|
|
bool InsertDeals(int database)
|
|
{
|
|
//--- вспомогательные переменные
|
|
long account_login=AccountInfoInteger(ACCOUNT_LOGIN); // номер счёта
|
|
ulong deal_ticket; // тикет сделки
|
|
long order_ticket; // тикет ордера,по которому была совершена сделка
|
|
long position_ticket; // ID позиции, к которой относится сделка
|
|
datetime time; // время совершения сделки
|
|
long type ; // тип сделки
|
|
long entry ; // направление сделки
|
|
string symbol; // по какому символу была сделка
|
|
double volume; // объем операции
|
|
double price; // цена
|
|
double profit; // финансовый результат
|
|
double swap; // своп
|
|
double commission; // комиссия
|
|
long magic; // Magic number (ID советника)
|
|
long reason; // причина или источник проведения сделки
|
|
//--- пройдем по всем сделкам и внесем их в базу данных
|
|
bool failed=false;
|
|
int deals=HistoryDealsTotal();
|
|
//--- заблокируем базу данных перед выполнением транзакций
|
|
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);
|
|
//--- внесем в таблицу каждую сделку через запрос
|
|
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;
|
|
}
|
|
}
|
|
//--- проверим на наличие ошибок при выполнении транзакций
|
|
if(failed)
|
|
{
|
|
//--- откатим все транзакции и разблокируем базу данных
|
|
DatabaseTransactionRollback(database);
|
|
PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError());
|
|
return(false);
|
|
}
|
|
//--- все транзакции прошли успешно - зафиксируем изменения и разблокируем базу данных
|
|
DatabaseTransactionCommit(database);
|
|
return(true);
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Заполняет таблицу TRADES на основе таблицы DEALS |
|
|
//+------------------------------------------------------------------+
|
|
bool FillTRADEStableBasedOnDEALStable(int database)
|
|
{
|
|
if(!DatabaseTableExists(database, "DEALS"))
|
|
{
|
|
PrintFormat("%s: Error. DEALS table is missing in the database", __FUNCTION__);
|
|
return false;
|
|
}
|
|
//--- заполним таблицу 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;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Заполняет из БД список всех трейдов |
|
|
//+------------------------------------------------------------------+
|
|
bool FillsListTradesFromDB(int database, string db_name, STrade &array[])
|
|
{
|
|
STrade trade;
|
|
ResetLastError();
|
|
//--- Запросим из БД список трейдов, отсортированный по убыванию времени входа в рынок
|
|
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;
|
|
}
|
|
//--- Прочитаем в массив структур данные созданной таблицы трейдов
|
|
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;
|
|
}
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Заполняет из БД список всех символов |
|
|
//+------------------------------------------------------------------+
|
|
bool FillsListSymbolsFromDB(int database, string db_name, string &array[])
|
|
{
|
|
//--- Проверим наличие созданной таблицы трейдов в базе данных
|
|
ResetLastError();
|
|
if(!DatabaseTableExists(database, "TRADES"))
|
|
{
|
|
//--- Если таблица ещё не создана - сообщим о том, как её создать
|
|
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;
|
|
}
|
|
|
|
//--- запросим из БД список всех символов, на которых осуществлялась торговля. Список отсортирован по алфавиту
|
|
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;
|
|
}
|
|
|
|
//--- Прочитаем в массив данные созданной таблицы символов
|
|
for(int i=0; DatabaseRead(request); i++)
|
|
{
|
|
ArrayResize(array, i+1);
|
|
DatabaseColumnText(request, 0, array[i]);
|
|
}
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Заполняет из БД список всех магиков |
|
|
//+------------------------------------------------------------------+
|
|
bool FillsListMagicsFromDB(int database, string db_name, long &array[])
|
|
{
|
|
//--- Проверим наличие созданной таблицы трейдов в базе данных
|
|
ResetLastError();
|
|
if(!DatabaseTableExists(database, "DEALS"))
|
|
{
|
|
//--- Если таблица ещё не создана - сообщим о том, как её создать
|
|
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;
|
|
}
|
|
|
|
//--- запросим из БД список всех магиков, на которых осуществлялась торговля. Список отсортирован по возрастанию
|
|
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;
|
|
}
|
|
|
|
//--- Прочитаем в массив данные созданной таблицы магиков
|
|
for(int i=0; DatabaseRead(request); i++)
|
|
{
|
|
ArrayResize(array, i+1);
|
|
DatabaseColumnLong(request, 0, array[i]);
|
|
}
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+-------------------------------------------------------------------+
|
|
//|Получает из БД и сохраняет в массив статистику торговли по символам|
|
|
//+-------------------------------------------------------------------+
|
|
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;
|
|
}
|
|
|
|
//--- получаем записи из результатов запроса
|
|
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;
|
|
}
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//|Получает из БД и сохраняет в массив статистику торговли по магикам|
|
|
//+------------------------------------------------------------------+
|
|
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;
|
|
}
|
|
|
|
//--- получаем записи из результатов запроса
|
|
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;
|
|
}
|
|
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+------------------------------------------------------------------+
|
|
//| Получает из БД и сохраняет в массив статистику торговли по счёту |
|
|
//+------------------------------------------------------------------+
|
|
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;
|
|
}
|
|
|
|
//--- получаем записи из результатов запроса
|
|
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;
|
|
}
|
|
//--- удалим запрос после использования
|
|
DatabaseFinalize(request);
|
|
return true;
|
|
}
|
|
//+------------------------------------------------------------------+
|