<?php
/* MT4Analysis - Main database statistics.
   Copyright (C) 2010, Future Lab.

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, get a copy via the Internet at
   http://gnu.org/copyleft/gpl.html or write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston,
   MA 02111-1307 USA

   You can contact the author via email at rsmereka@future-lab.com

   Original version. Oct/2010, Rick Smereka */

// initialize session
session_start();

require 'lib/constants.php';
require 'lib/parse.php';
require 'lib/mt4analysis.php';
require 'lib/db.php';
require 'lib/datime.php';
require 'lib/sqlite_udf.php';

// no set value in drop-down constant
define('DD_NULL', '00');

// no set display in drop-down constant
define('DDD_NULL', '--Select--');

// prefix of config records in 'parms' table
define('CONFIG_PREFIX', 'stats');

// html null drop-down value option line, not selected
$dd_null_ns = "<OPTION VALUE='" . DD_NULL . "'>" . DDD_NULL . '</OPTION>';

// html null drop-down value option line, selected
$dd_null_sel = "<OPTION VALUE='" . DD_NULL . "' SELECTED>" . DDD_NULL; 
$dd_null_sel .= '</OPTION>';

// scope/timeframe definitions
define('MT4A_SCOPE_THIS_MONTH', 1);
define('MT4A_SCOPE_LAST_MONTH', 2);
define('MT4A_SCOPE_THIS_YEAR', 3);
define('MT4A_SCOPE_LAST_YEAR', 4);
define('MT4A_SCOPE_ALL', 5);
define('MT4A_SCOPE_CUSTOM', 6);
define('MT4A_SCOPE_MAX', 6);
$array_scope = array('0', 'This Month', 'Last Month', 'This Year',
                     'Last Year', 'All', 'Custom');

$database_driver = DATABASE_DRIVER;
$_SESSION['rpath_2_root'] = '';

setlocale(LC_MONETARY, 'en_US');

// internal functions
function set_dd_vars()
{
   // Populate drop-down session variables based on item changed.
   /* when any drop-down value is changed, the new value is passed as
      a 'GET' variable and the form is reloaded, all drop-down values
      are stored in session variables, any drop-down that has not been
      set has no corresponding session variable, 'GET' and 'SESSION'
      variable names are codes: 'dd' for dropdown 
      followed by the drop-down number, for example: 'dd1' is the
      database file name dropdown (the first one) and 'dd2' is the
      account name dropdown */

   for($i = 1; $i <= 5; $i++)
      {
      $idx = 'dd' . $i;

      // if 'GET' var is set, save it in a 'SESSION' var
      if (isset($_GET[$idx]) && strlen($_GET[$idx]) > 0)
         {
         $_SESSION[$idx] = $_GET[$idx];

         // populate session var logic
         switch($i)
            {
            case 1:
               // database changed
               $_SESSION['dname'] = '';
               $_SESSION['dd1'] = $_GET['dd1'];
               $_SESSION['dname'] = $_SESSION['dd1'];
               $_SESSION['dd2'] = $_SESSION['account'] = DD_NULL;
               $_SESSION['dd3'] = $_SESSION['scope'] = DD_NULL;
               break;

            case 2:
               // account changed
               $_SESSION['dd2'] = $_GET['dd2'];
               $_SESSION['account'] = $_SESSION['dd2'];
               $_SESSION['dd3'] = $_SESSION['scope'] = DD_NULL;
               break;

            case 3:
               // scope changed
               $_SESSION['dd3'] = $_GET['dd3'];
               $_SESSION['scope'] = $_SESSION['dd3'];
               break;

            case 4:
               // custom scope, start date changed
               $_SESSION['dd4'] = $_GET['dd4'];
               $_SESSION['sc_start'] = $_SESSION['dd4'];
               break;

            case 5:
               // custom scope, end date changed
               $_SESSION['dd5'] = $_GET['dd5'];
               $_SESSION['sc_end'] = $_SESSION['dd5'];
            }
         }
      }

   /* print_r($_GET);
   print_r($_SESSION); */
}

function average_span($dbh, $wquery = '')
{
   /* Find the (estimated) average time span in a group of trades.
      The database handle is expected in 'dbh' and 'wquery' is assumed
      to be the 'WHERE' clause of an SQL query. Function returns the
      average trade time as a sentence. */
   $mname = 'average_span';
   $query = 'SELECT opendatime AS open, closedatime AS close FROM ';
   $query .= 'closedtrade';
   $cnt = 0;

   if ($wquery != '')
      $query .= ' WHERE ' . $wquery;

   try
      {
      if (!$stmt = $dbh->prepare($query))
         die("$mname:PDO prepare failed,SQL syntax error?");

      if ($stmt->execute())
         {
         while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
            $cnt++;

            // convert date/time stamp to Unix time stamp
            $uopen = strtotime($row['open']);
            $uclose = strtotime($row['close']);

            // time span in number of seconds added to total
            $span += $uclose - $uopen;
            }
         }
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $dbf);
      die();
      }

   // average span in seconds
   if ($cnt > 0)
      {
      $aspan = round($span / $cnt, 0);
      return(tsentence(tspan_estimate($aspan)));
      }

   return('N/A');
}

function do_table($afilter = '')
{
   /* Dump the stats into a HTML table. 'afilter' may contain
      an additional SQL filter clause. */
   global $dbh, $filter, $filter_date;

   // build complete filter
   if ($afilter != '')
      {
      $f1 = $filter . ' AND ' . $afilter;

      if ($filter_date == '')
         $f2 = $afilter;
      else
         $f2 = $filter_date . ' AND ' . $afilter;
      }
   else
      {
      $f1 = $filter;
      $f2 = $filter_date;
      }

   // total trades
   tbl2('Trades', 'L', true);
   $query = 'SELECT count() as c FROM closedtrade WHERE ' . $f1;
   $trades = db_get_one($dbh, $query, 'c');
   tbl2(number_format($trades, 0), 'R');

   // if no trades, no use proceeding
   if ($trades == 0)
      return;

   // count of wins
   tbl2('Won');
   $query = 'SELECT count() as c FROM closedtrade WHERE ' . $f1;
   $query .= ' AND pl + rollover - commission > 0';
   $win = db_get_one($dbh, $query, 'c');
   tbl2(number_format($win, 0), 'R');

   // count of break even
   tbl2('Break Even');
   $query = 'SELECT count() as c FROM closedtrade WHERE ' . $f1;
   $query .= ' AND pl + rollover - commission = 0';
   $tmp = db_get_one($dbh, $query, 'c');
   tbl2(number_format($tmp, 0), 'R');

   // count of losses
   tbl2('Lost');
   $query = 'SELECT count() as c FROM closedtrade WHERE ' . $f1;
   $query .= ' AND pl + rollover - commission < 0';
   $tmp = db_get_one($dbh, $query, 'c');
   tbl2(number_format($tmp, 0), 'R');

   // win percent
   tbl2('Won %');

   if ($trades > 0)
      $tmp = number_format($win / $trades * 100, 2);
   else
      $tmp = 0;

   tbl2($tmp, 'R');

   // PL
   tbl2('P/L');
   $query = 'SELECT total(pl + rollover - commission) as t FROM ';
   $query .= 'closedtrade WHERE ' . $f1;
   $tmp = db_get_one($dbh, $query, 't');
   tbl2('$' . number_format($tmp, 2), 'R');

   // average PL
   tbl2('Avg. P/L');
   $query = 'SELECT avg(pl + rollover - commission) as p FROM ';
   $query .= 'closedtrade WHERE ' . $f1;
   $tmp = db_get_one($dbh, $query, 'p');
   tbl2('$' . number_format($tmp, 2), 'R');

   // total pips
   tbl2('Pips');
   $query = 'SELECT total(pips(ticket)) as p FROM closedtrade ';
   $query .= 'WHERE ' . $f1;
   $tmp = db_get_one($dbh, $query, 'p');
   $dec = ($_SESSION['fiveDecimals']) ? 1 : 0;
   tbl2(number_format($tmp, $dec), 'R');

   // average pips
   tbl2('Avg. Pips');
   $query = 'SELECT avg(pips(ticket)) as p FROM closedtrade ';
   $query .= 'WHERE ' . $f1;
   $tmp = db_get_one($dbh, $query, 'p');
   tbl2(number_format($tmp, $dec), 'R');

   // average trade length
   tbl2('Avg. Time');
   $query = $f1;
   $tmp = average_span($dbh, $query);
   tbl2($tmp);

   // drawdown
   if ($afilter == '')
      {
      // dd (money)
      // only at account level
      // high balance
      $query = 'SELECT max(balance(ticket)) as b FROM closedtrade ';
      $query .= 'WHERE ' . $f1;
      $highbal = db_get_one($dbh, $query, 'b');
      /* tbl2('hb');
      tbl2(number_format($highbal, 2), 'R'); */

      // low balance
      $query = 'SELECT min(balance(ticket)) as b FROM closedtrade ';
      $query .= 'WHERE ' . $f1;
      $lowbal = db_get_one($dbh, $query, 'b');
      /* tbl2('lb');
      tbl2(number_format($lowbal, 2), 'R'); */
      $tmp = $highbal - $lowbal;
      tbl2('DD');
      tbl2('$' . number_format($tmp, 2), 'R');

      // dd (percent)
      $tmp = round($highbal / $lowbal * 10, 2);
      tbl2('DD %');
      tbl2(number_format($tmp, 2), 'R');
      }

   tbl2('<H2>Long</H2>');
   tbl2('');
   tbl2('');
   tbl2('');

   // longs
   tbl2('Trades');
   $query = 'SELECT count() as c FROM closedtrade WHERE ';
   $query .= ' transactiontype = ' . MT4_TRANS_TYPE_BUY;

   if ($f2 != '')
      $query .= ' AND ' . $f2;

   $trades = db_get_one($dbh, $query, 'c');
   tbl2(number_format($trades, 0), 'R');

   if ($trades > 0)
      {
      // long wins
      tbl2('Won');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_BUY;
      $query .= ' AND pl + rollover - commission > 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $win = db_get_one($dbh, $query, 'c');
      tbl2(number_format($win, 0), 'R');

      // long break even
      tbl2('Break Even');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_BUY;
      $query .= ' AND pl + rollover - commission = 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'c');
      tbl2(number_format($tmp, 0), 'R');

      // long losses
      tbl2('Lost');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_BUY;
      $query .= ' AND pl + rollover - commission < 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'c');
      tbl2(number_format($tmp, 0), 'R');

      // long win percent
      tbl2('Won %');

      if ($trades > 0)
         $tmp = number_format($win / $trades * 100, 2);
      else
         $tmp = 0;

      tbl2($tmp, 'R');

      // long PL
      tbl2('P/L');
      $query = 'SELECT total(pl + rollover - commission) as t FROM ';
      $query .= 'closedtrade WHERE transactiontype = ';
      $query .= MT4_TRANS_TYPE_BUY;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 't');
      tbl2('$' . number_format($tmp, 2), 'R');

      // long average PL
      tbl2('Avg. P/L');
      $query = 'SELECT avg(pl + rollover - commission) as p FROM ';
      $query .= 'closedtrade WHERE transactiontype = ';
      $query .= MT4_TRANS_TYPE_BUY;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2('$' . number_format($tmp, 2), 'R');

      // long pips
      tbl2('Pips');
      $query = 'SELECT total(pips(ticket)) as p FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_BUY;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2(number_format($tmp, $dec), 'R'); 

      // long average pips
      tbl2('Avg. Pips');
      $query = 'SELECT avg(pips(ticket)) as p FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_BUY;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2(number_format($tmp, $dec), 'R');

      // long average trade length
      tbl2('Avg. Time');
      $query = 'transactiontype = ' . MT4_TRANS_TYPE_BUY;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = average_span($dbh, $query);
      tbl2($tmp);
      }
   else
      {
      tbl2('');
      tbl2('');
      }

   tbl2('<H2>Short</H2>');
   tbl2('');
   tbl2('');
   tbl2('');

   // shorts
   tbl2('Trades');
   $query = 'SELECT count() as c FROM closedtrade WHERE ';
   $query .= ' transactiontype = ' . MT4_TRANS_TYPE_SELL;

   if ($f2 != '')
      $query .= ' AND ' . $f2;

   $trades = db_get_one($dbh, $query, 'c');
   tbl2(number_format($trades, 0), 'R');

   if ($trades > 0)
      {
      // short wins
      tbl2('Won');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL;
      $query .= ' AND pl + rollover - commission > 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $win = db_get_one($dbh, $query, 'c');
      tbl2(number_format($win, 0), 'R');

      // short break even
      tbl2('Break Even');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL;
      $query .= ' AND pl + rollover - commission = 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'c');
      tbl2(number_format($tmp, 0), 'R');
 
      // short losses
      tbl2('Lost');
      $query = 'SELECT count() as c FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL;
      $query .= ' AND pl + rollover - commission < 0';

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'c');
      tbl2(number_format($tmp, 0), 'R');

      // short win percent
      tbl2('Won %');

      if ($trades > 0)
         $tmp = number_format($win / $trades * 100, 2);
      else
         $tmp = 0;

      tbl2($tmp, 'R');

      // short PL
      tbl2('P/L');
      $query = 'SELECT total(pl + rollover - commission) as t FROM ';
      $query .= 'closedtrade WHERE transactiontype = ';
      $query .= MT4_TRANS_TYPE_SELL;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 't');
      tbl2('$' . number_format($tmp, 2), 'R');

      // short average PL
      tbl2('Avg. P/L');
      $query = 'SELECT avg(pl + rollover - commission) as p FROM ';
      $query .= 'closedtrade WHERE transactiontype = ';
      $query .= MT4_TRANS_TYPE_SELL;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2('$' . number_format($tmp, 2), 'R');

      // short pips
      tbl2('Pips');
      $query = 'SELECT total(pips(ticket)) as p FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2(number_format($tmp, $dec), 'R');

      // short average pips
      tbl2('Avg. Pips');
      $query = 'SELECT avg(pips(ticket)) as p FROM closedtrade WHERE ';
      $query .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = db_get_one($dbh, $query, 'p');
      tbl2(number_format($tmp, $dec), 'R');

      // short average trade length
      tbl2('Avg. Time');
      $query = 'transactiontype = ' . MT4_TRANS_TYPE_SELL;

      if ($f2 != '')
         $query .= ' AND ' . $f2;

      $tmp = average_span($dbh, $query);
      tbl2($tmp);
      }
}

function get_config_startup()
{
   /* Obtain the saved screen startup parameters from the 'parms'
      table (if present) and build a parameter string. */
   global $database_driver;
   $mname = 'get_config_startup';

   // if database name not set, exit
   if (!isset($_SESSION['dname']) || !strlen($_SESSION['dname']) ||
       $_SESSION['dname'] == DD_NULL)
      return('');

   $prefix = CONFIG_PREFIX . '-';
   $len = strlen($prefix);
   $db = mt4a_dbname();
   $query = 'SELECT name, value from parms WHERE ';
   $query .= "substr(name,1,$len) = '$prefix' ORDER BY name";
   $parms = '';
   $cnt = 0;
 
   try
      {
      $dbh = new PDO("$database_driver:$db");

      if (!$stmt = $dbh->prepare($query))
         die("$mname:PDO prepare error, SQL syntax error?");

      if ($stmt->execute())
         {
         while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
            $cnt++;
            word($row['name'], $dd, 2, '-');
            $parm = $dd . '=' . $row['value'];

            if ($cnt > 1)
               $parms .= '&';

            $parms .= $parm;
            }
         }
      else
         die("$mname:PDO execute fail");
      }
   catch(PDOException $e)
      {
      db_error($mname . ':' . $e->getMessage(), $db);
      die();
      }

   if ($cnt == 0)
      return('');

   return($parms);
}

// main line
$title = 'Database Statistics';

// if no $_GET or $_POST vars present, load saved config (if present)
if (count($_GET) == 0 && count($_POST) == 0)
   if (($parms = get_config_startup()) != '')
      header("Location: http://{$_SERVER['HTTP_HOST']}{$_SERVER['PHP_SELF']}?$parms");
?>
<HTML>
<HEAD>
<?php puts("<TITLE>Mt4Analysis - $title</TITLE>"); ?>
<SCRIPT language=Javascript>
function treload(opt, fname, idx)
{
   var val = opt.value;
   self.location = fname + '?' + idx + '=' + urlencode(val);
}
function reload(opt, fname, idx)
{
   var val = opt[opt.selectedIndex].value;
   self.location = fname + '?' + idx + '=' + urlencode(val);
}
function urlencode(inputString)
{
   var encodedInputString=escape(inputString);
   encodedInputString = encodedInputString.replace("+", "%2B");
   encodedInputString = encodedInputString.replace("/", "%2F");        
   return encodedInputString;
}
</SCRIPT>
<SCRIPT TYPE="text/javascript" src="js/datetimepicker_css.js"></SCRIPT>
</HEAD>
<BODY>
<A NAME="top"</A>
<?php
/* some Php versions still have 'magic quotes' turned on and since
   I cannot turn this off at runtime, the following code removes
   the quotes from the I/O arrays, taken from
   http://php.net/manual/en/security.magicquotes.disabling.php */
if (get_magic_quotes_gpc())
   {
   function stripslashes_deep($value)
      {
         $value = is_array($value) ?  array_map('stripslashes_deep', $value) :
                                      stripslashes($value);
         return $value;
      }

   $_POST = array_map('stripslashes_deep', $_POST);
   $_GET = array_map('stripslashes_deep', $_GET);
   $_COOKIE = array_map('stripslashes_deep', $_COOKIE);
   $_REQUEST = array_map('stripslashes_deep', $_REQUEST);
   }

page_header($title);
set_dd_vars();
puts("<TABLE BORDER='2'>");
puts("<FORM NAME='com' ACTION='{$_SERVER['PHP_SELF']}' METHOD='POST'>");
puts('<TR>');
puts('<TH>Database</TH>');
$mes = "<TD><SELECT NAME='dd1' onchange=\"reload(this.form.dd1.options, ";
$mes .= "'{$_SERVER['PHP_SELF']}', 'dd1')\">";
puts($mes);
$sel = (isset($_SESSION['dd1'])) ? $_SESSION['dd1'] : DD_NULL;
$mes = ($sel == DD_NULL) ? $dd_null_sel : $dd_null_ns;
puts($mes);

// get a list of every file in the trade path
$spath = (IN_PRODUCTION) ? $_SERVER['DOCUMENT_ROOT_OLD'] : 
                           $_SERVER['DOCUMENT_ROOT'];
$dpath = (IN_PRODUCTION) ? TRADE_PATH_PRODUCTION : TRADE_PATH_LOCAL;
$db_path = $spath . $dpath;
// p("spath=$spath,dpath=$dpath,db_path=$db_path");
$db_files = scandir($db_path);
$cnt = 1;

// populate dropdown (skip current and parent dot references)
foreach ($db_files as $db_file)
   {
   if ($db_file == '.' || $db_file == '..')
      continue;

   $mes = "<OPTION VALUE='$db_file'";

   if ($db_file == $sel)
      $mes .= ' SELECTED';

   $mes .= ">$db_file</OPTION>";
   puts($mes);
   $cnt++;
   }
?>
</SELECT>
</TD>
</TR>
<TR>
<TH>Account</TH>
<?php
$mes = "<TD><SELECT NAME='dd2' onchange=\"reload(this.form.dd2.options, ";
$mes .= "'{$_SERVER['PHP_SELF']}', 'dd2')\">";
puts($mes);
$sel = (isset($_SESSION['dd2'])) ? $_SESSION['dd2'] : DD_NULL;
$mes = ($sel == DD_NULL) ? $dd_null_sel : $dd_null_ns;
puts($mes);

// only populate account dropdown if there is a valid database
if (isset($_SESSION['dd1']) && $_SESSION['dd1'] != DD_NULL)
   {
   // build database path and name
   $db = mt4a_dbname();
   $query = 'SELECT id, description FROM account';
 
   try
      {
      $dbh = new PDO("$database_driver:$db");

      if (!$stmt = $dbh->prepare($query))
         {
         puts('</SELECT></TD></TR>');
         $mes = '<P><CENTER>Unable to open database, is this an SQLite';
         $mes .= 'database?</CENTER></P>';
         puts($mes);
         die();
         }

      if ($stmt->execute())
         {
         while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
            $mes = "<OPTION VALUE='{$row['id']}'";

            if ($row['id'] == $sel)
               $mes .= ' SELECTED';

            $mes .= ">{$row['description']}</OPTION>";
            puts($mes);
            }
         }
      else
         die('Unable to locate dd2 data');
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $db);
      die();
      }
   }
?>
</SELECT>
</TD>
</TR>
<TR>
<TH>Time Frame</TH>
<?php
$mes = "<TD><SELECT NAME='dd3' onchange=\"reload(this.form.dd3.options, ";
$mes .= "'{$_SERVER['PHP_SELF']}', 'dd3')\">";
puts($mes);
$sel = (isset($_SESSION['dd3'])) ? $_SESSION['dd3'] : DD_NULL;
$mes = ($sel == DD_NULL) ? $dd_null_sel : $dd_null_ns;
puts($mes);

// only populate scope dropdown if there is a valid account
if (isset($_SESSION['dd2']) && $_SESSION['dd2'] != DD_NULL)
   {

   for($i = 1; $i <= MT4A_SCOPE_MAX; $i++)
      {
      $mes = "<OPTION VALUE='$i'";

      if ($i == $sel)
         $mes .= ' SELECTED';

      $mes .= ">{$array_scope[$i]}</OPTION>";
      puts($mes);
      }
   }
?>
</SELECT>
</TD>
</TR>
<?php
// entry for custom time frame
if ($_SESSION['dd3'] == MT4A_SCOPE_CUSTOM)
   {
   puts('<TR>');
   puts('<TH>Start date/time</TH>');
   $mes = "<TD><INPUT TYPE='Text' id='dd4' maxlength='19' size='19' ";
   $mes .= "onchange=\"treload(this.form.dd4, ";
   $mes .= "'{$_SERVER['PHP_SELF']}', 'dd4')\">";
   $mes .= "<A HREF=\"javascript:NewCssCal('dd4','yyyymmdd','arrow',";
   $mes .= 'true,24,false)"><IMG SRC="images/cal.gif" width="16" ';
   $mes .= 'height="16" alt="Pick a date and time"></A>';
   puts($mes);
   puts('</TD>');
   puts('</TR>');
   }
?>
</FORM>
</TABLE>
<?php
// report only if all drop-downs are filled in
if (isset($_SESSION['dd1']) && $_SESSION['dd1'] != DD_NULL &&
    isset($_SESSION['dd2']) && $_SESSION['dd2'] != DD_NULL &&
    isset($_SESSION['dd3']) && $_SESSION['dd3'] != DD_NULL)

   {
?>
<P><H1>Time Frame</H1></P>
<TABLE BORDER='2'>
<?php
// database is already open
try
   {
   // hook in SQLite UDF's
   // ea id
   if (!$dbh->sqliteCreateFunction('eaid', 'ea_id', 1))
      die('sqliteCreateFunction failed(eaid)');

   // ea description
   if (!$dbh->sqliteCreateFunction('eadesc', 'ea_desc', 1))
      die('sqliteCreateFunction failed(eadesc)');

   // pips
   if (!$dbh->sqliteCreateFunction('pips', 'pips', 1))
      die('sqliteCreateFunction failed(pips)');

   // balance
   if (!$dbh->sqliteCreateFunction('balance', 'account_balance', 1))
      die('sqliteCreateFunction failed(balance)');
   }
catch(PDOException $e)
   {
   db_error($e->getMessage(), $db);
   die();
   }

// set session vars needed by udf's
$query = 'SELECT broker FROM account WHERE id = ' . $_SESSION['account'];
$broker = db_get_one($dbh, $query, 'broker');

if ($broker == '')
   die('unable to locate broker id');

$query = 'SELECT fiveDecimals FROM broker WHERE id = ' . $broker;
$tmp = db_get_one($dbh, $query, 'fiveDecimals');

if ($tmp == '')
   die('unable to locate is 5 decimals(broker)');
else
   $_SESSION['fiveDecimals'] = $tmp;

// static part of database filter
$filter = '(transactiontype = ' . MT4_TRANS_TYPE_BUY . ' OR ';
$filter .= 'transactiontype = ' . MT4_TRANS_TYPE_SELL . ') AND ';
$filter .= "account = {$_SESSION['account']}";

// start date (start of first open trade)
$query = 'SELECT min(ticket) AS t FROM closedtrade WHERE ' . $filter;

if (($ticket = db_get_one($dbh, $query, 't')) == '')
   {
   $mes = '</TABLE><P><H1><CENTER>No Trading History';
   $mes .= '</CENTER></H1></P>';
   puts($mes);
   exit;
   }

$query = "SELECT datetime(opendatime, 'localtime') as open FROM ";
$query .= 'closedtrade WHERE ticket = ' . $ticket;
   
if (($date_acc_start = db_get_one($dbh, $query, 'open')) == '')
   die("unable to get trade open date/time from ticket $ticket");

// stop date (last update)
$query = "SELECT datetime(value, 'localtime') as t FROM parms ";
$query .= "WHERE name = '" . DB_UPDATE_DATIME . "'";

if (($date_acc_stop = db_get_one($dbh, $query, 't')) == '')
   die("unable to get last update date/time from 'parms'");

// add scope to filter
$array_date_today = date_parse(date('Y-m-d 00:00:00'));
$filter_date_const = "datetime(closedatime, 'localtime')";

switch($_SESSION['scope'])
   {
   case MT4A_SCOPE_THIS_MONTH:
      $date_start = "{$array_date_today['year']}-";
      $date_start .= lpad($array_date_today['month'], '0', 2);
      $date_start .= '-01 00:00:00';
      $date_end = $date_acc_stop;
      $filter_date = "$filter_date_const >= '$date_start'";
      break;   

   case MT4A_SCOPE_LAST_MONTH:
      $array_date_today['month']--;

      if ($array_date_today['month'] < 1)
         $array_date_today['year']--;

      $date_start = "{$array_date_today['year']}-";
      $date_start .= lpad($array_date_today['month'], '0', 2);
      $date_start .= '-01 00:00:00';
      $dm = days_in_month($array_date_today['year'],
                          $array_date_today['month']);
      $date_end = "{$array_date_today['year']}-";
      $date_end .= lpad($array_date_today['month'], '0', 2);
      $date_end .= "-$dm 23:59:59";
      $filter_date = "$filter_date_const >= '$date_start' AND ";
      $filter_date .= "$filter_date_const <= '$date_end'";
      break;

   case MT4A_SCOPE_THIS_YEAR:
      $date_start = "{$array_date_today['year']}-01-01 00:00:00";
      $date_end = $date_acc_stop;
      $filter_date = "$filter_date_const >= '$date_start'";
      break;

   case MT4A_SCOPE_LAST_YEAR:
      $array_date_today['year']--;
      $date_start = "{$array_date_today['year']}-01-01 00:00:00";
      $date_end = "{$array_date_today['year']}-12-31 23:59:59";
      $filter_date = "$filter_date_const >= '$date_start' AND ";
      $filter_date .= "$filter_date_const <= '$date_end'";
      break;

   case MT4A_SCOPE_ALL:
      $date_start = $date_acc_start;
      $date_end = $date_acc_stop;
      $filter_date = '';
      break;

   case MT4A_SCOPE_CUSTOM:
      $date_start = $date_acc_start;
      $date_end = $date_acc_stop;
      $filter_date = '';
      break;
   }

if ($filter_date != '')
   $filter .= " AND $filter_date";

// br("main query=$filter");
// start date
tbl2('Start', 'L', true);
tbl2($date_start);

// end date
tbl2('End');
tbl2($date_end);

// running time is from open of first trade to last database update 
$tmp3 = tsentence(tspan($date_start, $date_end));
tbl2('Span');
tbl2($tmp3);
tbl2('');
tbl2('');
?>
</TR>
</TABLE>
<P><H1>Account</H1></P>
<TABLE BORDER='2'>
<?php do_table(); ?>
</TABLE>
<P><H1>Expert Advisors</H1></P>
<?php
// get each ea and output
$query = 'SELECT description, magic FROM ea ORDER BY description';

try
   {
   if (!$stmt = $dbh->prepare($query))
      die("$mname:PDO prepare failed(ea list),SQL syntax error?");

   if ($stmt->execute())
      {
      while($row = $stmt->fetch(PDO::FETCH_ASSOC))
         {
         // puts("magic={$row['magic']}<BR />");
         puts("<P><H2>{$row['description']}</H2></P>");
         puts("<TABLE BORDER='2'>");
         do_table($row['magic']);
         puts('</TABLE><BR />');
         }
      }
   }
catch(PDOException $e)
   {
   db_error($e->getMessage(), $dbf);
   die();
   }

// get each currency pair and output
puts('<P><H1>Currency Pairs</H1></P>');
$query = 'SELECT DISTINCT closedtrade.currencypair AS cp, ';
$query .= 'currencypair.description as name FROM closedtrade, ';
$query .= 'currencypair WHERE cp = currencypair.id AND ' . $filter;
$query .= ' ORDER BY name';

try
   {
   if (!$stmt = $dbh->prepare($query))
      die("$mname:PDO prepare failed(pair list),SQL syntax error?");

   if ($stmt->execute())
      {
      while($row = $stmt->fetch(PDO::FETCH_ASSOC))
         {
         puts("<P><H2>{$row['name']}</H2></P>");
         puts("<TABLE BORDER='2'>");
         $cp_filter = "currencypair = {$row['cp']}";
         do_table($cp_filter);
         puts('</TABLE><BR />');
         }
      }
   }
catch(PDOException $e)
   {
   db_error($e->getMessage(), $dbf);
   die();
   }
}

// <link href="/fl/mt4analysis/css/rfnet.css" rel="stylesheet" type="text/css">
require 'misc/footer.php';
?>
</BODY>
</HTML>


