<?php
/* mt4_sqlite_loader - MT4Analysis Project: A Program to parse a Metatrader 4
   (MT4) html input statement file and write the closed trade transactions to 
   an output SQLite database. Copyright (C) 2009-2010.

   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

   Program syntax:

      php mt4_sqlite_loader.php db account statement

   Where 'php' is the path and file name of the php CLI executable, 
   'mt4_sqlite_loader.php' is the file name of this program, 'db' is
   the path and file name of the SQLite database, 'account' 
   is the name of the account (must be a valid account in the 'account'
   table) and 'statement' is the path and file name of the MetaTrader 4
   statement file (normal or detailed).

   This is not an 'http' program. It is not intented to run from a 
   web server. This program is designed to be run from the command 
   line (CLI) and you must have Php installed with the ability to run 
   CLI programs (Google 'php cli' or start at: http://www.php-cli.com).
   This program writes to an SQLite 3 database. It uses the Php PDO 
   (Php Data Objects) interface (http://ca2.php.net/manual/en/book.pdo.php)
   You also must have the Php extension 'php_pdo' enabled as well as the 
   specific PDO databse driver extension enabled ('php_pdo_sqlite' in the 
   case of SQLite 3).

   This program depends on the Php library modules listed directy
   below. It is assumed that these library modules are stored in a
   directory called 'lib' which is a sub-directory right underneath
   the directory where this program is stored.
   Original version Apr/2009, Rick Smereka

   Fixed bug that ignored any closed trade that did not have any
   magic number or comment (trade in html started with 'td' instead of
   'td title'). Jun/2009, Rick Smereka

   Added detection of matching EA in 'ea' table and placement of the
   EA identification number in each closed trade. Open and close prices
   may appear in the Metatrader statement file with missing digits to the
   right of the decimal point. This situation is detected and the price
   is modified so it always has the correct number of decimal places.
   Jul/2009, Rick Smereka

   Removed detection of matching EA. Changed loading of date and time to
   new 'opendatime' and 'closedatime' fields. Aug/2009, Rick Smereka

   Provided for situation where broker is not using DST. Sep/2009,
   Rick Smereka

   Fixed bug where comment was only loading first word. Thanks to
   Jari Petays. Dec/2009, Rick Smereka

   Changed documentation. Added 'buy limit' and 'sell limit' transaction
   types. Jan/2010, Rick Smereka

   Modified 'adjust_datime' function to use year of trade to calculate
   broker start and stop DST. Feb/2010, Rick Smereka

   Added logic to store closed trade commission. Jul/2010, Rick Smereka

   Added storing last updated date/time to 'parms' table.
   Aug/2010, Rick Smereka

   Added define 'TIMEZONE' and call to 'date_default_timezone_set' to set 
   the timezone on the PC that this script is running on. 
   Sep/2010, Rick Smereka */

// name of this program
$apname = 'mt4_sqlite_loader';

// program version
define('VERSION', '1.09.01-2010.09.16');

/* this must be the proper timezone of the PC that this script is 
   running on, see the timezone list at
   http://php.net/manual/en/timezones.php */
define('TIMEZONE', 'America/Toronto');

// inserted library modules (assumed to be in 'lib' sub-directory)
require 'lib/mt4analysis.php';
require 'lib/constants.php';
require 'lib/parse.php';
require 'lib/html.php';
require 'lib/db.php';
require 'lib/math.php';

$version = VERSION;
$abort = ', program abort';
puts("$apname Version $version");
puts("By Rick Smereka, Copyright (C) 2009-2010 Future Lab");
puts("$apname comes with ABSOLUTELY NO WARRANTY");
puts('This is free software, and you are welcome to redistribute it');
puts("under certain conditions; see 'gpl.txt' for information.");

// get command line data
$argc = $_SERVER['argc'];
$argv = $_SERVER['argv'];

// process command line args
if ($argc < 2)
   {
   usage();
   exit;
   }

if ($argc < 4)
   {
   puts("$apname:improper number of parameters");
   usage();
   exit;
   }

$db = $argv[1];
$account = $argv[2];
$html_fname = $argv[3];
puts("$apname:statement file is $html_fname");
puts("$apname:account is $account");
puts("$apname:SQLite database file is $db");

// set database name global
$g_dbname = $db;

// open input html statement file
$handle = fopen($html_fname, 'r');

if ($handle == false)
   die("$apname:unable to open input statement file" . $abort);

// set timezone
if (!date_default_timezone_set(TIMEZONE))
   die('invalid timezone');

/* hunt for the tag that is the opening trigger
   (I use this as the beginning mark) */
$trig = 'TR ALIGN=RIGHT';

if (!html_scan_until($handle, $trig, HTML_NEXT_TYPE_TAG, $buf))
   die('unable to find first marker, is this an MT4 statement?' . $abort);

$done = false;
$rec_count = 1;
$trades_added = 0;
$database_driver = DATABASE_DRIVER;
$query = 'SELECT id, broker FROM account WHERE description = ';
$query .= "'" . $account . "'";

// open database and get account info
try
   {
   $dbh = new PDO("$database_driver:$db");

   if (!$stmt = $dbh->prepare($query))
      {
      $mes = "$apname:PDO prepare failed(get account info), ";
      $mes .= 'SQL syntax error?' . $abort;
      die($mes);
      }

   if ($stmt->execute())
      $row = $stmt->fetch(PDO::FETCH_ASSOC);
   else
      die("$apame:PDO execute failed(get account info)" . $abort);
   }
catch(PDOException $e)
   {
   print "Error!:" . $e->getMessage() . $abort . "\n";
   die();
   }

if (!isset($row['broker']) || $row['broker'] <= 0)
   die("$apname:'$account':no such account" . $abort);

$broker = $row['broker'];
$accountid = $row['id'];
$query = 'SELECT description, currencyPairSet, fiveDecimals, normTimeOffset, ';
$query .= 'normTimeStartDate, normTimeStarTime, DSTimeOffset, ';
$query .= 'DSTimeStartDate, DSTimeStarTime FROM ';
$query .= "broker WHERE id = '$broker'";

// get broker info
try
   {
   if (!$stmt = $dbh->prepare($query))
      {
      $mes = "$apname:PDO prepare failed(get account info), ";
      $mes .= 'SQL syntax error?' . $abort;
      die($mes);
      }

   if ($stmt->execute())
      $row = $stmt->fetch(PDO::FETCH_ASSOC);
   else
      die("$apame:PDO execute failed(get account info)" . $abort);
   }
catch(PDOException $e)
   {
   db_error($e->getMessage(), $db);
   die();
   }

puts("$apname:broker is {$row['description']}");

if ($row['fiveDecimals'])
   puts("$apname:this is a 5 decimal broker");
else
   puts("$apname:this is not a 5 decimal broker");

// set broker 5 decimal flag as a global
$g_fivedecimals = $row['fiveDecimals'];

// determine whether broker uses DST and set global broker using DST flag
if ($row['DSTimeOffset'] == -1)
   {
   $b_DST = false;
   puts("$apname:broker is not configured to use DST");
   }
else
   {
   $b_DST = true;
   puts("$apname:broker is configured to use DST");
   }

// get currency pair set name
$currencypairset = $row['currencyPairSet'];
$query = 'SELECT description from currencyPairSet WHERE id = ';
$query .= "'" . $currencypairset . "'";
$cpairsetdesc = db_get_one($dbh, $query, 'description');
puts("$apname:currency pair set is $cpairsetdesc");
$b_nto = $row['normTimeOffset'];
$b_ntd = $row['normTimeStartDate'];
$b_ntt = $row['normTimeStarTime'];
$b_dso = $row['DSTimeOffset'];
$b_dsd = $row['DSTimeStartDate'];
$b_dst = $row['DSTimeStarTime'];

// main loop
while(!$done)
   {
   if (!load_db_rec($handle, $dbh))
      $done = true;
   else
      $rec_count++;
   }

fclose($handle);
update_datime($dbh);
puts("$apname:done, $rec_count statement entries processed");
puts("$apname:$trades_added entries added to the database");
exit;

function load_db_rec($handle, $dbh)
{
   /* Parse one closed trade data from the html file and write
      to the sqlite database if the trade does not already exist.
      Returns 'true' normally or 'false' upon eof of input html
      file. */
   global $rec_count, $abort, $currencypairset, $broker, $accountid;
   global $trades_added;
   $mname = 'load_db_rec';

   // s/b 'td title' or 'td' tag for next trade or 'td colspan' for end
   if (!scan_check($handle, $title))
      return(false);

   // puts("$mname:first tag='$title',rec_count=$rec_count");

   // 1 - ticket
   if (!html_next($handle, $ticket, $typ))
      return(false);

   // if trade is already in database, advance and return
   if (exist($dbh, 'closedtrade', 'ticket', $ticket))
      {
      if (!html_scan_until($handle, 'TR ', HTML_NEXT_TYPE_TAG, $buf))
         return(false);
      
      return(true);
      }

   // 2 - open date/time
   if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $o_datime))
      die("$mname:unexpected eof(rec=$rec_count,field 2)" . $abort);

   // modify MT date/time stamp
   $o_datime = conv_datime($dbh, $o_datime);

   // 3 - transaction type
   if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $transaction))
      die("$mname:unexpected eof(rec=$rec_count,field 3)" . $abort);

   // verify transaction type with database and get type ID
   $query = 'SELECT id FROM transactiontype WHERE ';
   $query .= "description LIKE '$transaction'";
   $transactiontype = db_get_one($dbh, $query, 'id');
   // echo "$mname:transaction='$transaction',transactiontype='$transactiontype'\n";

   // process transactions
   switch($transactiontype)
      {
      case MT4_TRANS_TYPE_CREDIT:
         // i am not processing credits
         puts("$mname:credit $ticket not processed");
         break;

      case MT4_TRANS_TYPE_BALANCE:
         // fourth field is a comment
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $comment))
            die("$mname:unexpected eof(rec=$rec_count,field 4");

         // fifth field is the amount
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $amount))
            die("$mname:unexpected eof(rec=$rec_count,field 5" . $abort);

         // remove spaces in amount
         $amount = conv_amount($amount);

         // write database record
         $mes = 'INSERT INTO closedtrade (ticket, opendatime, ';
         $mes .= 'transactiontype, pl, comment, broker, account) VALUES(';
         $mes .= $ticket . ',' . sql_addquotes($o_datime, true);
         $mes .= $transactiontype . ', ' . $amount . ',';
         $mes .= sql_addquotes($comment, true);
         $mes .= $broker . ', ' . $accountid . ')';
         // puts("$mname:mes($rec_count)=$mes");

         try
            {
            if (!$stmt = $dbh->prepare($mes))
               {
               $mes = "$mname:PDO prepare failed(save,$rec_count), ";
               $mes .= 'SQL syntax error?' . $abort;
               die($mes);
               }

            if (!$stmt->execute())
               die("$mname:PDO execute failed(save,$rec_count)" . $abort);
            }
         catch(PDOException $e)
            {
            print 'Error!:' . $e->getMessage() . "\n";
            die();
            }

         /* puts("com=$mes");   
         $mes = "ticket=$ticket,o_datime=$o_datime,o_date=$o_date,";
         $mes .= "o_time=$o_time,";
         $mes .= "transaction=$transaction,comment=$comment,amount=$amount";
         echo $mes . "\n"; */
         puts("$mname:balance $ticket added");
         $trades_added++;
         break;

      case MT4_TRANS_TYPE_BUY_STOP:
         // i am only processing market orders
         puts("$mname:buy stop $ticket not market order, not added");
         break;

      case MT4_TRANS_TYPE_SELL_STOP:
         // i am only processing market orders
         puts("$mname:sell stop $ticket not market order, not added");
         break;

      case MT4_TRANS_TYPE_BUY_LIMIT:
         // i am only processing market orders
         puts("$mname:buy limit $ticket not market order, not added");
         break;

      case MT4_TRANS_TYPE_SELL_LIMIT:
         // i am only processing market orders
         puts("$mname:sell limit $ticket not market order, not added");
         break;

      case MT4_TRANS_TYPE_BUY:
      case MT4_TRANS_TYPE_SELL:
         // get EA magic/reference number and any comment from 'td' tag
         if (words($title, '=') > 1)
            {
            if (!word($title, $title2, 2, '='))
               die("$mname:unable to get word 2($title)" . $abort);

            $title = alltrim(str_replace('"', '', $title2));
            $nwords = words($title);

            if ($nwords > 0)
               {
               if (!word($title, $magic, 1))
                  die("$mname:unable to get magic($title,$rec_count)" . $abort);

               // remove leading '#' from magic
               $magic = str_replace('#', '', $magic);
               }
            else
               $magic = '';

            if ($nwords > 1)
               {
               $len = strlen($title);

               // get index of second word
               if (($indx = indxword($title, 2)) == -1)
                  die("$mname:error get word 2 index($title,$rec_count)" . $abort);

               $comment = substr($title, $indx, $len - $indx);
               }
            else
               $comment = '';

            // puts("magic=$magic,comment=$comment,title=$title");
            }
         else
            // closed trade with no magic or comment is ok
            $magic = $comment = '';

         // 4 - lot size
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $lot))
            die("$mname:unexpected eof(rec=$rec_count,field 4" . $abort);

         // 5 - currency pair
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $pair))
            die("$mname:unexpected eof(rec=$rec_count,field 5" . $abort);

         // lookup pair's id number
         $query = 'SELECT id FROM currencypair WHERE ';
         $query .= "description LIKE '$pair' AND setID = '$currencypairset'";
         $pairid = db_get_one($dbh, $query, 'id');
         // echo "$mname:pair='$pair',pairid='$pairid'\n";

         if ($pairid == '')
            // must be known pair in 'currencypair' table
            die("$mname:unknown currency pair($pair,$rec_count)" . $abort);

         // 6 - open price
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $o_price))
            die("$mname:unexpected eof(rec=$rec_count,field 6" . $abort);

         // 7 - stop loss
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $sl))
            die("$mname:unexpected eof(rec=$rec_count,field 7" . $abort);

         // 8 - take profit
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $tp))
            die("$mname:unexpected eof(rec=$rec_count,field 8" . $abort);

         // 9 - close date/time
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $c_datime))
            die("$mname:unexpected eof(rec=$rec_count,field 9)" . $abort);

         $c_datime = conv_datime($dbh, $c_datime);

         // 10 - close price
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $c_price))
            die("$mname:unexpected eof(rec=$rec_count,field 10" . $abort);

         // 11 - commission
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $commission))
            die("$mname:unexpected eof(rec=$rec_count,field 11" . $abort);

         // 12 - taxes (ignore)
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $tmp))
            die("$mname:unexpected eof(rec=$rec_count,field 12" . $abort);

         // 13 - rollover/swap
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $rollover))
            die("$mname:unexpected eof(rec=$rec_count,field 13" . $abort);

         // 14 - profit/loss
         if (!html_scan_until($handle, '', HTML_NEXT_TYPE_TEXT, $pl))
            die("$mname:unexpected eof(rec=$rec_count,field 14" . $abort);

         $pl = conv_amount($pl);

         if (!adjust_price_precision($ticket, $o_price, $c_price))
            die("$mname:error adjusting prices(rec=$rec_count" . $abort);

         /* $mes = "ticket=$ticket,o_datime=$o_datime,o_date=$o_date,";
         $mes .= "o_time=$o_time,lot=$lot,pair=$pair,pairid=$pairid,";
         $mes .= "open price=$o_price,sl=$sl,tp=$tp,c_datime=$c_datime,";
         $mes .= "c_date=$c_date,c_time=$c_time,c_price=$c_price,";
         $mes .= "commission=$commission,";
         $mes .= "swap=$rollover,pl=$pl,magic=$magic,comment=$comment";
         echo $mes . "\n"; */

         // write database record
         $mes = 'INSERT INTO closedtrade (ticket, opendatime, ';
         $mes .= 'transactiontype, lot, currencyPairSet, currencyPair, ';
         $mes .= 'openprice, sl, tp, closedatime, closeprice,';
         $mes .= 'rollover, pl, comment, magic, broker, account, ';
         $mes .= 'commission) VALUES(';
         $mes .= $ticket . ', ' . sql_addquotes($o_datime, true);
         $mes .= $transactiontype . ', ' . $lot . ', ' . $currencypairset;
         $mes .= ', ' . $pairid . ', ';
         $mes .= $o_price . ', ' . $sl . ', ' . $tp . ', ';
         $mes .= sql_addquotes($c_datime, true);
         $mes .= $c_price . ', ' . $rollover . ', ' . $pl . ', ';
         $mes .= sql_addquotes($comment, true);
         $mes .= sql_addquotes($magic, true);
         $mes .= $broker . ', ' . $accountid . ', ' . $commission . ')';
         // puts("$mname:mes($rec_count)=$mes");

         try
            {
            if (!$stmt = $dbh->prepare($mes))
               {
               $mes = "$mname:PDO prepare failed(save,$rec_count), ";
               $mes .= 'SQL syntax error?' . $abort;
               die($mes);
               }

            if (!$stmt->execute())
               die("$mname:PDO execute failed(save,$rec_count)" . $abort);
            }
         catch(PDOException $e)
            {
            print 'Error!:' . $e->getMessage() . $abort . "\n";
            die();
            }

         puts("$mname:trade $ticket added");
         $trades_added++;
         break;

      default:
         die("$mname:unknown transaction type(tt=$transactiontype,rec=$rec_count)"
             . $abort);
      }
   
   // scan for start of next row
   if (!html_scan_until($handle, 'TR ', HTML_NEXT_TYPE_TAG, $buf))
      return(false);

   return(true);
}

function scan_check($handle, &$title)
{
   /* Scan the input for either the 'td title' tag or the
      'td colspan' tag. The 'td title' tag signifies the
      beginning of the next closed trade and the 'td colspan'
      tag indicates the end of the closed trade section.
      Note that the beginning of the next trade may start
      just with 'td' if there is no magic number and no
      comment. Returns 'false' upon eof or end of closed trades,
      'true' otherwise. Upon success, 'title' will be 
      loaded with the matching tag. */
   $trig_next = 'TD TITLE';
   $trig_end = 'TD COLSPAN';
   $title = '';
   $ret = html_next($handle, $buf, $typ);

   while($ret)
      {
      if ($typ == HTML_NEXT_TYPE_TAG)
         {
         // first test, exact match to 'td' (case insensitive)
         if (!strcasecmp($buf, 'TD'))
            {
            $title = 'TD';
            return(true);
            }

         if (stristr($buf, $trig_end) != false)
            {
            $title = $buf;
            return(false);
            }

         if (stristr($buf, $trig_next) != false)
            {
            $title = $buf;
            return(true);
            }
         }

      $ret = html_next($handle, $buf, $typ);
      }

   return($ret);
}

function conv_amount($amount)
{
   // Convert an amount by removing any spaces.
   return(str_replace(' ', '', $amount));
}

function conv_datime($dbh, $datime)
{
   /* Convert an MT4 date/time stamp into an ISO-8601 timestamp
      (minus the 'T' between the date and time) and adjust the
      date/time stamp to GMT. Returns the modified date/time stamp. */
   global $rec_count;
   $mname = 'conv_datime';

   // must be space(s) separating the date and time
   if (words($datime) < 2)
      die("$mname:unknown date/time stamp($datime,$rec_count)");

   // replace periods in date with dashes
   $datime = str_replace('.', '-', $datime);

   // add zero seconds to time portion
   $datime .= ':00';

   // adjust to GMT
   $ret = adjust_datime($dbh, $datime);
   return($ret);
}

function adjust_datime($dbh, $ts)
{
   /* Adjust date/time to GMT depending on whether daylight saving time is 
      in effect. The database handle is expected in 'dbh' and 'ts' must 
      contain a valid ISO-8601 date. Returns the adjusted ISO-8601 date. 
      Note that the ISO-8601 dates do not have the 'T' between the date and 
      time as required by ISO-8601 (SQLite does not require it). */
   global $b_nto, $b_ntd, $b_ntt, $b_dso, $b_dsd, $b_dst, $b_DST, $abort;
   $mname = 'adjust_datime';
   
   // generate a Unix timestamp from 'ts'
   if (($nts = strtotime($ts)) == false)
      die("$mname:error creating Unix timestamp($ts)");
      
   // if the broker is not configured to use DST, use normal offset
   if (!$b_DST)
      $offset = $b_nto;
   else
      {
      $b_ntdmm = substr($b_ntd, 0, 2);
      $b_ntddd = substr($b_ntd, 2, 2);
      $b_ntthh = substr($b_ntt, 0, 2);
      $b_nttmm = substr($b_ntt, 2, 2);
      $b_dsdmm = substr($b_dsd, 0, 2);
      $b_dsddd = substr($b_dsd, 2, 2);
      $b_dsthh = substr($b_dst, 0, 2);
      $b_dstmm = substr($b_dst, 2, 2);

      // compute Unix timestamps from broker data except use year from trade
      $yyyy = date('Y', $nts);
      $tmp = $yyyy . '-' . $b_ntdmm . '-' . $b_ntddd . ' ';
      $tmp .= $b_ntthh . ':' . $b_nttmm . ':' . '00';
      // puts("$mname:tmp bu_nt=$tmp");

      if (($bu_nt = strtotime($tmp)) == false)
         die("$mname:error creating bu_nt timestamp");

      $tmp = $yyyy . '-' . $b_dsdmm . '-' . $b_dsddd . ' ';
      $tmp .= $b_dsthh . ':' . $b_dstmm . ':' . '00';
      // puts("$mname:tmp bu_ds=$tmp");

      if (($bu_ds = strtotime($tmp)) == false)
         die("$mname:error creating bu_ds timestamp");

      /* puts("b_nto=$b_nto,b_ntd=$b_ntd,b_ntdmm=$b_ntdmm,b_ntddd=$b_ntddd");
      puts("b_ntt=$b_ntt,b_ntthh=$b_ntthh,b_nttmm=$b_nttmm,b_dso=$b_dso");
      puts("b_dsd=$b_dsd,b_dsdmm=$b_dsdmm,b_dsddd=$b_dsddd,b_dst=$b_dst");
      puts("b_dsthh=$b_dsthh,b_dstmm=$b_dstmm,bu_nt=$bu_nt,bu_ds=$bu_ds"); */

      // is the input date/time stamp part of the brokers' DS time?
      if ($nts >= $bu_ds && $nts < $bu_nt)
         {
         // puts("$mname:$ts:is DS time");
         $offset = $b_dso;
         }
      else
         {
         // puts("$mname:$ts:is not DS time");
         $offset = $b_nto;
         }
      }
      
   // if broker current time is GMT, do nothing
   if ($offset == 0)
      return($ts);

   // change the sign on the broker offset
   $offset = change_int_sign($offset);

   // use SQLite to generate a new ISO-8601 date/time stamp adjusted to GMT
   $tmp = ($offset > 1 || $offset < 1) ? 'hours' : 'hour';
   $tmp1 = "'";
   $tmp1 .= ($offset > 0) ? '+' : '';
   $tmp1 .= $offset . ' ' . $tmp . "'";
   $query = "SELECT datetime($nts, 'unixepoch', $tmp1) as datime";
   // puts("$mname:datetime query:$query");
   
   try
      {
      if (!$stmt = $dbh->prepare($query))
         {
         $mes = "$mname:PDO prepare failed(datetime), ";
         $mes .= 'SQL syntax error?' . $abort;
         die($mes);
         }

      if ($stmt->execute())
	      $row = $stmt->fetch(PDO::FETCH_ASSOC);
      else
         die("$mname:PDO execute failed(datetime)" . $abort);
      }
   catch(PDOException $e)
      {
      print "Error!:" . $e->getMessage() . $abort . "\n";
      die();
      }
   
   // puts("$mname:adjusted timestamp is {$row['datime']}");
   return($row['datime']);
}

function update_datime($dbh)
{
   /* Write the date and time of this update to the 'parms' table.
      time stamp is written as UTC */
   global $abort;
   $datime = date('Y-m-d H:i:s');
   $datime = "datetime('$datime', 'utc')";

   // insert or update depending on whether record exists.
   if (exist($dbh, 'parms', 'name', DB_UPDATE_DATIME))
      {
      $com = "UPDATE parms SET value = $datime WHERE name = '";
      $com .= DB_UPDATE_DATIME . "'";
      }
   else
      {
      $com = 'INSERT INTO parms VALUES (';
      $com .= "'" . DB_UPDATE_DATIME . "', $datime)";
      }

   try
      {
      if (!$stmt = $dbh->prepare($com))
         die("$mname:PDO prepare failed, SQL syntax error?" . $abort);
        
      if (!$stmt->execute())
         die("$mname:PDO execute failed" . $abort);
      }
   catch(PDOException $e)
      {
      print "Error!:" . $e->getMessage() . $abort . "\n";
      die();
      }
}

function usage()
{
   // Display program usage.
   global $apname;

   puts("usage: php $apname.php db account_name mt4_statement_file");
}
?>

