<?php
/* SQLite UDF module.
   Copyright (C) 2009-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. Jul/2009, Rick Smereka

   Change calculation of 'account_balance' to include commission.
   Removed function 'roi' and 'tradetime'. Aug/2010, Rick Smereka */

function ea_id($ticket)
{
   /* Using the closed trade ticket, locate the matching ea in the
      'ea' table. Returns ea id upon success, zero otherwise.*/
   global $dbh, $dbf;
   $mname = 'udf:ea_id';
   $query = 'SELECT id, magic as magicSQL FROM ea';

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

      if ($stmt->execute())
         {
         while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
            // for each ea, see if there is matching magic
            $query = 'SELECT magic FROM closedtrade WHERE ticket = ';
            $query .= $ticket . ' AND ' . $row['magicSQL'];
            $tmp = db_get_one($dbh, $query, 'magic');

            // if there is a match, return id
            if ($tmp != '')
               return($row['id']);
            }
         }
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $dbf);
      die();
      }

   return(0);
}

function ea_desc($ticket)
{
   /* Using the closed trade ticket, locate the matching ea in the
      'ea' table. Returns ea description upon success, '**Unknown'
      otherwise.*/
   global $dbh, $dbf;
   $mname = 'udf:ea_desc';
   $na = '**Unknown';
   $query = 'SELECT description, magic as magicSQL FROM ea';

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

      if ($stmt->execute())
         {
         while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
            // for each ea, see if there is matching magic
            $query = 'SELECT magic FROM closedtrade WHERE ticket = ';
            $query .= $ticket . ' AND ' . $row['magicSQL'];
            $tmp = db_get_one($dbh, $query, 'magic');
            // puts("<P>query=$query,tmp=$tmp</P>");

            // if there is a match, return id
            if ($tmp != '')
               return($row['description']);
            }
         }
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $dbf);
      die();
      }

   return($na);
}

function pips($ticket)
{
   /* Determine the number of pips won/lost in the closed trade by ticket.
      Returns the number of pips won/lost upon success, zero
      otherwise. */
   global $dbh, $dbf;
   $mname = 'udf:pips';

   if (!iscli())
      {
      if (isset($_SESSION['fiveDecimals']) && 
          strlen($_SESSION['fiveDecimals']))
         $fivedecimals = $_SESSION['fiveDecimals'];
      else
         die("$mname:five decimal indicator not set(http session)");
      }
   else
      {
      global $g_fivedecimals;

      if (isset($g_fivedecimals) && strlen($g_fivedecimals))
         $fivedecimals = $g_fivedecimals;
      else
         die("$mname:five decimal indicator not set(cli global)");
      }

   if ($fivedecimals == '')
      die("$mname:five decimal indicator not set");

   $query = 'SELECT transactiontype, openprice, closeprice FROM closedtrade ';
   $query .= 'WHERE ticket = ' . $ticket;

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

      if ($stmt->execute())
         $row = $stmt->fetch(PDO::FETCH_ASSOC);
      else
         return(0);
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $dbf);
      die();
      }

   if ($row['transactiontype'] != MT4_TRANS_TYPE_BUY &&
       $row['transactiontype'] != MT4_TRANS_TYPE_SELL)
      // calculate only buys and sells
      return(0);

   $openprice = $row['openprice'];
   $closeprice = $row['closeprice'];
   // puts("$mname:before:open=$openprice,close=$closeprice<BR />");

   // make certain that the precision of the open and close price is the same
   if (!adjust_price_precision($ticket, $openprice, $closeprice))
      return(0);

   // puts("$mname:after:open=$openprice,close=$closeprice<BR />");

   if ($row['transactiontype'] == MT4_TRANS_TYPE_BUY)
      $p = $closeprice - $openprice;
   else
      $p = $openprice - $closeprice;

   $digits = price_precision($ticket);
   $multipler = intval('1' . lpad('', '0', $digits - 1));
   $rounder = ($fivedecimals) ? 1 : 0;
   // puts("<P>$mname:p=$p,digits=$digits,multipler=$multipler,rounder=$rounder</P>");
   return(round($p * $multipler, $rounder));
}

function account_balance($ticket)
{
   /* Determine the account balance as at and including the current
      trade. An accurate account balance depends on the proper
      balance records being present in the database. This balance
      includes profit/loss as well as rollover and commission. The method I
      am using is very brute force and time consuming. I am totaling
      all records lower than the current ticket. This relies on the
      fact that ticket numbers always increase. */
   global $dbh, $dbf;
   $mname = 'udf:account_balance';
   $query = 'SELECT total(pl + rollover - commission) as balance FROM '; 
   $query .= "closedtrade WHERE ticket <= $ticket";
   // puts("$mname:query=$query");

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

      if ($stmt->execute())
         $row = $stmt->fetch(PDO::FETCH_ASSOC);
      else
         die("$mname:PDO exec failed");
      }
   catch(PDOException $e)
      {
      db_error($e->getMessage(), $dbf);
      die();
      }

   return(round($row['balance'], 2));
}
?>
