//+------------------------------------------------------------------+
//|                                                   TestSQLite.mq5 |
//|                        Copyright 2019, MetaQuotes Software Corp. |
//|                                             https://www.mql5.com |
//+------------------------------------------------------------------+
#property copyright "Copyright 2019, MetaQuotes Software Corp."
#property link      "https://www.mql5.com"
#property version   "1.00"

//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+

  

datetime lastbar_timeopen;
//+------------------------------------------------------------------+
//| Expert initialization function                                   |
//+------------------------------------------------------------------+

int m_db; 
string m_filename;

struct Ticks_Info
{
   double val;
   int count;
   double percent;
};

int OnInit()
  {
//---
   m_filename=IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+"_stats.sqlite";
   m_db= DatabaseOpen(m_filename,DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE   |DATABASE_OPEN_MEMORY );
   if (m_db==INVALID_HANDLE){
      Print("DB: ", m_filename, " open failed with code ", GetLastError());
      return (INIT_FAILED);
   }
   
   //--- создадим таблицу TICKS
   if(!CreateTableTicks(m_db))
     {
      DatabaseClose(m_db);
      return (INIT_FAILED);
     }
//---
   return(INIT_SUCCEEDED);
  }
  
   
//+------------------------------------------------------------------+
//| Expert deinitialization function                                 |
//+------------------------------------------------------------------+
void OnDeinit(const int reason)
  {
     DatabaseTransactionCommit(m_db);
      string query= "select " 
	            "a.val, a.cnt, round(a.cnt*100.0/b.cnt) "
               "FROM "
               "( "
               "select "
	            " round((ASK-BID)*100000) val,  count(*) cnt "
               " from "
	            " TICKS "
               " GROUP BY 	round((ASK-BID)*100000) "
               " ) a, "
               " (select count(*)  cnt from ticks) b ";
      
      int request= DatabasePrepare(m_db,query); 
      if (request==INVALID_HANDLE)
      {
         Print("DB: ", m_filename, " request failed with code ", GetLastError());
      }
      
      
      Ticks_Info ticks_infos[], ticks_info;
      
      int i=0;
      ArrayResize(ticks_infos, 1000);
      Print("Spred profile");
      //--- выводим записи
      for(; DatabaseReadBind(request, ticks_info) ; i++)
      {
      
         ticks_infos[i].count=ticks_info.count;
         ticks_infos[i].val=ticks_info.val;
         ticks_infos[i].percent=ticks_info.percent;
     }
     ArrayResize(ticks_infos, i);
     ArrayPrint(ticks_infos);
     
     
   //--- удалим запрос
   DatabaseFinalize(request);
   DatabaseClose(m_db);
//---
      
   
  }
//+------------------------------------------------------------------+
//| Expert tick function                                             |
//+------------------------------------------------------------------+
void OnTick()
  {
    InsertTicks(m_db);
  }
//+------------------------------------------------------------------+

  
//+------------------------------------------------------------------+
//| Создает таблицу ticks                                            |
//+------------------------------------------------------------------+
bool CreateTableTicks(int database)
  {
//--- если таблица DEALS уже есть, удалим её
   if(!DeleteTable(database, "TICKS"))
     {
      return(false);
     }
//--- проверим наличие таблицы
   if(!DatabaseTableExists(database, "TICKS"))
      //--- создаем таблицу
      if(!DatabaseExecute(database, "CREATE TABLE TICKS("
                          "DATETIME      INT,"
                          "SYMBOL      CHAR(10),"
                          "ASK      REAL,"
                          "BID      REAL,"
                          "LAST      REAL,"
                          "VOLUME      INT,"
                          "TIME_MSC     INT,"
                          "FLAGS     INT,"
                          "VOLUME_REAL      REAL);"))
        {
         Print("DB: create the TICKS table  failed with code ", GetLastError());
         return(false);
        }
//--- таблица успешно создана
   return(true);
  }
//+------------------------------------------------------------------+
//| Удаляет из базы таблицу с указанным именем                       |
//+------------------------------------------------------------------+
bool DeleteTable(int database, string table_name)
  {
   if(!DatabaseExecute(database, "DROP TABLE IF EXISTS "+table_name))
     {
      Print("Failed to drop the DEALS table with code ", GetLastError());
      return(false);
     }
//--- таблица успешно удалена
   return(true);
  }
    
//+------------------------------------------------------------------+
//| Вносит Тики в таблицу базы данных                              |
//+------------------------------------------------------------------+
bool InsertTicks(int database)
{
   MqlTick last_tick;
   if(!SymbolInfoTick(Symbol(),last_tick))
         Print("SymbolInfoTick() failed, error = ",GetLastError());
   
   //--- внесем в таблицу каждую сделку через запрос
   string request_text=StringFormat("INSERT INTO TICKS (DATETIME,SYMBOL,ASK,BID,LAST,VOLUME,TIME_MSC,FLAGS,VOLUME_REAL) VALUES (%d, '%s', %G, %G, %G, %d, %d, %d, %G)",
                                       last_tick.time,Symbol() ,last_tick.ask, last_tick.bid, last_tick.last, last_tick.volume, last_tick.time_msc, last_tick.flags,last_tick.volume_real);

   if(!DatabaseExecute(database, request_text))
   {
      PrintFormat("%s: failed to insert string %s with code %d", __FUNCTION__, request_text, GetLastError());
   }
   return(true);
}