billing.inc.php

Go to the documentation of this file.

<?php
// billing.inc.php - functions relating to billing
//
// SiT (Support Incident Tracker) - Support call tracking system
// Copyright (C) 2010-2011 The Support Incident Tracker Project
// Copyright (C) 2000-2009 Salford Software Ltd. and Contributors
//
// This software may be used and distributed according to the terms
// of the GNU General Public License, incorporated herein by reference.

// Prevent script from being run directly (ie. it must always be included
if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME']))
{
    exit;
}

define ("BILLING_APPROVED", 0);
define ("BILLING_AWAITINGAPPROVAL", 5);
define ("BILLING_RESERVED", 10);

function does_contact_have_billable_contract($contactid)
{
    global $now;
    $return = NO_BILLABLE_CONTRACT;

    $siteid = contact_siteid($contactid);
    $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
    $sql .= "WHERE m.servicelevel = sl.tag AND sl.timed = 'yes' AND m.site = {$siteid} ";
    $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";
    $result = mysql_query($sql);

    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        // We have some billable/timed contracts
        $return = SITE_HAS_BILLABLE_CONTRACT;

        // check if the contact is listed on one of these

        while ($obj = mysql_fetch_object($result))
        {
            $sqlcontact = "SELECT * FROM `{$GLOBALS['dbSupportContacts']}` ";
            $sqlcontact .= "WHERE maintenanceid = {$obj->id} AND contactid = {$contactid}";

            $resultcontact = mysql_query($sqlcontact);
            if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_ERROR);

            if (mysql_num_rows($resultcontact) > 0)
            {
                $return = CONTACT_HAS_BILLABLE_CONTRACT;
                break;
            }
        }
    }

    return $return;
}


function get_billable_contract_id($contactid)
{
    global $now;

    $return = -1;

    $siteid = contact_siteid($contactid);
    $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
    $sql .= "WHERE m.servicelevel = sl.tag AND sl.timed = 'yes' AND m.site = {$siteid} ";
    $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";

    $result = mysql_query($sql);

    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        $return = mysql_fetch_object($result)->id;
    }

    return $return;
}


function get_site_billable_contract_id($siteid)
{
    global $now;

    $return = -1;

    $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
    $sql .= "WHERE m.servicelevel = sl.tag AND sl.timed = 'yes' AND m.site = {$siteid} ";
    $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";

    $result = mysql_query($sql);

    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        $return = mysql_fetch_object($result)->id;
    }

    return $return;
}


function get_service_percentage($maintid)
{
    global $dbService;

    $sql = "SELECT * FROM `{$dbService}` ";
    $sql .= "WHERE contractid = {$maintid}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        $num = 0;
        while ($service = mysql_fetch_object($result))
        {
            $total += (float) $service->balance / (float) $service->creditamount;
            $num++;
        }
        $return = (float) $total / (float) $num;
    }
    else
    {
        $return = FALSE;
    }

    return $return;
}


function is_contract_timed($contractid)
{
    global $dbMaintenance, $dbServiceLevels;
    $timed = FALSE;
    $sql = "SELECT timed FROM `{$dbMaintenance}` AS m, `{$dbServiceLevels}` AS sl ";
    $sql .= "WHERE m.servicelevel = sl.tag AND m.id = {$contractid}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    list($timed) = mysql_fetch_row($result);
    if ($timed == 'yes')
    {
        return TRUE;
    }
    else
    {
        return FALSE;
    }
}


function update_last_billed_time($serviceid, $date)
{
    global $dbService;

    $rtnvalue = FALSE;

    if (!empty($serviceid) AND !empty($date))
    {
        $rtnvalue = TRUE;
        $sql .= "UPDATE `{$dbService}` SET lastbilled = '{$date}' WHERE serviceid = {$serviceid}";
        mysql_query($sql);
        if (mysql_error())
        {
            trigger_error(mysql_error(), E_USER_ERROR);
            $rtnvalue = FALSE;
        }

        if (mysql_affected_rows() < 1)
        {
            trigger_error("Approval failed", E_USER_ERROR);
            $rtnvalue = FALSE;
        }
    }

    return $rtnvalue;
}


function get_billable_multiplier($dayofweek, $hour, $billingmatrix = 'Default')
{
    $sql = "SELECT `{$dayofweek}` AS rate FROM {$GLOBALS['dbBillingMatrix']} WHERE hour = {$hour} AND tag = '{$billingmatrix}'";

    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    $rate = 1;

    if (mysql_num_rows($result) > 0)
    {
        $obj = mysql_fetch_object($result);
        $rate = $obj->rate;
    }

    return $rate;
}


function get_all_available_multipliers($matrixid='')
{
    $days = array('mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun', 'holiday');

    foreach ($days AS $d)
    {
        $sql = "SELECT DISTINCT({$d}) AS day FROM `{$GLOBALS['dbBillingMatrix']}` ";
        if (!empty($matrixid)) $sql .= " WHERE tag = '{$matrixid}'";
        $result = mysql_query($sql);
        if (mysql_error())
        {
            trigger_error(mysql_error(),E_USER_WARNING);
            return FALSE;
        }

        while ($obj = mysql_fetch_object($result))
        {
            $a[$obj->day] = $obj->day;
        }
    }

    ksort($a);

    return $a;
}


function get_unit_rate($contractid, $date='')
{
    $serviceid = get_serviceid($contractid, $date);

    if ($serviceid != -1)
    {
        $unitrate = get_service_unitrate($serviceid);
    }
    else
    {
        $unitrate = -1;
    }

    return $unitrate;
}


function get_service_unitrate($serviceid)
{
    $rtnvalue = FALSE;
    $sql = "SELECT unitrate FROM `{$GLOBALS['dbService']}` AS p WHERE serviceid = {$serviceid}";

    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }

    return $rtnvalue;
}


function get_service_incidentrate($serviceid)
{
    $rtnvalue = FALSE;
    $sql = "SELECT incidentrate FROM `{$GLOBALS['dbService']}` AS p WHERE serviceid = {$serviceid}";

    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }

    return $rtnvalue;
}


function get_serviceid($contractid, $date = '')
{
    global $now, $CONFIG;
    if (empty($date)) $date = $now;

    $sql = "SELECT serviceid FROM `{$GLOBALS['dbService']}` AS s ";
    $sql .= "WHERE contractid = {$contractid} AND UNIX_TIMESTAMP(startdate) <= {$date} ";
    $sql .= "AND UNIX_TIMESTAMP(enddate) > {$date} ";
    $sql .= "AND (balance > 0 OR (select count(1) FROM `{$GLOBALS['dbService']}` WHERE contractid = s.contractid AND balance > 0) = 0) ";

    if (!$CONFIG['billing_allow_incident_approval_against_overdrawn_service'])
    {
        $sql .= "AND balance > 0 ";
    }

    $sql .= "ORDER BY priority DESC, enddate ASC, balance DESC LIMIT 1";

    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    $serviceid = -1;

    if (mysql_num_rows($result) > 0)
    {
        list($serviceid) = mysql_fetch_row($result);
    }

    return $serviceid;
}


function get_contract_balance($contractid, $includenonapproved = FALSE, $showonlycurrentlyvalid = TRUE, $includereserved = TRUE)
{
    global $dbService, $now;
    $balance = 0.00;

    $sql = "SELECT SUM(balance) FROM `{$dbService}` ";
    $sql .= "WHERE contractid = {$contractid} ";
    if ($showonlycurrentlyvalid)
    {
        $sql .= "AND UNIX_TIMESTAMP(startdate) <= {$now} ";
        $sql .= "AND UNIX_TIMESTAMP(enddate) >= {$now}  ";
    }
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
    list($balance) = mysql_fetch_row($result);

    if ($includenonapproved)
    {
        // Need to get sum of non approved incidents for this contract and deduct
        $balance += contract_transaction_total($contractid, BILLING_AWAITINGAPPROVAL);
    }

    if ($includereserved)
    {
        $balance += contract_transaction_total($contractid, BILLING_RESERVED);
    }

    return $balance;
}


function get_overdraft($contractid)
{
    $rtnvalue = FALSE;
    $sql = "SELECT DISTINCT sl.tag FROM `{$GLOBALS['dbServiceLevels']}` AS sl, `{$GLOBALS['dbMaintenance']}` AS m ";
    $sql .= "WHERE m.servicelevel = sl.tag AND m.id = {$contractid}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting servicelevel details. ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) == 1)
    {
        list($tag) = mysql_fetch_row($result);
        $sql = "SELECT DISTINCT limit FROM `{$GLOBALS['dbBillingPeriods']}` ";
        $sql .= "WHERE AND tag = '{$tag}'";
        $result = mysql_query($sql);
        if (mysql_error()) trigger_error("Error getting servicelevel details. ".mysql_error(), E_USER_WARNING);
        if (mysql_num_rows($result) == 1)
        {
            list($rtnvalue) = mysql_fetch_row($result);
        }
    }

    return $rtnvalue;
}


function reserve_monies($serviceid, $linktype, $linkref, $amount, $description)
{
    global $now, $sit;
    $rtnvalue = FALSE;
    $balance = get_service_balance($serviceid, TRUE, TRUE);
    // TODO take into account overdraft limit

    $amount *= -1;

    if ($balance != FALSE)
    {
        $sql = "INSERT INTO `{$GLOBALS['dbTransactions']}` (serviceid, amount, description, userid, dateupdated, transactionstatus) ";
        $sql .= "VALUES ('{$serviceid}', '{$amount}', '{$description}', '{$_SESSION['userid']}', '".date('Y-m-d H:i:s', $now)."', '".BILLING_RESERVED."')";
        $result = mysql_query($sql);
        if (mysql_error())
        {
            trigger_error("Error inserting transaction. ".mysql_error(), E_USER_WARNING);
            $rtnvalue = FALSE;
        }

        $rtnvalue = mysql_insert_id();

        if ($rtnvalue != FALSE)
        {

            $sql = "INSERT INTO `{$GLOBALS['dbLinks']}` VALUES ({$linktype}, {$rtnvalue}, {$linkref}, 'left', '{$_SESSION['userid']}')";
            mysql_query($sql);
            if (mysql_error())
            {
                trigger_error(mysql_error(),E_USER_ERROR);
                $rtnvalue = FALSE;
            }
            if (mysql_affected_rows() < 1)
            {
                trigger_error("Link reservation failed",E_USER_ERROR);
                $rtnvalue = FALSE;
            }
        }
    }

    return $rtnvalue;
}


function transition_reserved_monites($transactionid, $amount, $description='')
{
    $rtnvalue = TRUE;
    $sql = "UPDATE `{$GLOBALS['dbTransactions']}` SET amount = {$amount}, transactionstatus = ".BILLING_AWAITINGAPPROVAL." ";
    if (!empty($description))
    {
        $sql .= ", description = '{$description}' ";
    }
    $sql .= "WHERE transactionid = {$transactionid} AND transactionstatus = ".BILLING_RESERVED;
    mysql_query($sql);

    if (mysql_error())
    {
        trigger_error(mysql_error(), E_USER_ERROR);
        $rtnvalue = FALSE;
    }
    if (mysql_affected_rows() < 1)
    {
        trigger_error("Transition reserved monies failed {$sql}",E_USER_ERROR);
        $rtnvalue = FALSE;
    }

    return $rtnvalue;
}


function unreserve_monies($transactionid, $linktype)
{
    $rtnvalue = FALSE;
    $sql = "DELETE FROM `{$GLOBALS['dbTransactions']}` WHERE transactionid = {$transactionid} AND transactionstatus = ".BILLING_RESERVED;
    mysql_query($sql);

    if (mysql_error()) trigger_error("Error unreserving monies ".mysql_error(), E_USER_ERROR);
    if (mysql_affected_rows() == 1) $rtnvalue = TRUE;

    if ($rtnvalue != FALSE)
    {
        $sql = "DELETE FROM `{$GLOBALS['dbLinks']}` WHERE linktype =  {$linktype} AND origcolref = {$transactionid}";
        mysql_query($sql);
        if (mysql_error())
        {
            trigger_error(mysql_error(),E_USER_ERROR);
            $rtnvalue = FALSE;
        }
        if (mysql_affected_rows() < 1)
        {
            trigger_error("Link deletion failed",E_USER_ERROR);
            $rtnvalue = FALSE;
        }
    }

    return $rtnvalue;
}


function update_reservation($transactionid, $amount, $description='')
{
    return update_transaction($transactionid, $amount, $description, BILLING_RESERVED);
}


function update_transaction($transactionid, $amount, $description='', $status)
{
    if ($status == BILLING_APPROVED)
    {
        trigger_error("You cant change a approved transaction", E_USER_ERROR);
        exit;
    }

    $rtnvalue = FALSE;
    // Note we dont need to check its awaiting reservation as we check this when doing the update
    if (is_numeric($transactionid))
    {
        $sql = "UPDATE `{$GLOBALS['dbTransactions']}` SET amount = '{$amount}' ";
        if (!empty($description))
        {
            $sql .= ", description = '{$description}' ";
        }
        $sql .= "WHERE transactionid = {$transactionid} AND transactionstatus = {$status}";
        mysql_query($sql);
        if (mysql_error())
        {
            trigger_error(mysql_error(),E_USER_ERROR);
            $rtnvalue = FALSE;
        }
        if (mysql_affected_rows() > 0)
        {
            $rtnvalue = TRUE;
        }
    }

    return $rtnvalue;
}


function close_billable_incident($incidentid)
{
    global $now, $sit;
    $rtnvalue = TRUE;
    $sql = "SELECT i.maintenanceid FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbServiceLevels']}` AS sl ";
    $sql .= "WHERE i.servicelevel = sl.tag AND i.priority = sl.priority AND i.id = {$incidentid} AND sl.timed = 'yes'";
    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error("Error identifying if incident was timed ".mysql_error(), E_USER_WARNING);
        $rtnvalue = FALSE;
    }

    if (mysql_num_rows($result) > 0)
    {
        // Was logged against a timed contract
        list($contractid) = mysql_fetch_row($result);
        $duration = 0;
        $sql = "SELECT SUM(duration) FROM `{$GLOBALS['dbUpdates']}` WHERE incidentid = {$incidentid}";
        $result = mysql_query($sql);
        if (mysql_error())
        {
            trigger_error("Error getting duration for billable incident. ".mysql_error(), E_USER_WARNING);
            $rtnvalue = FALSE;
        }
        list($duration) = mysql_fetch_row($result);
        if ($duration > 0)
        {
            // There where activities on this update so add to the transactions table

            $bills = get_incident_billable_breakdown_array($incidentid);

            $multipliers = get_all_available_multipliers();

            $totalunits = 0;
            $totalbillableunits = 0;
            $totalrefunds = 0;

            foreach ($bills AS $bill)
            {
                foreach ($multipliers AS $m)
                {
                    $a[$m] += $bill[$m]['count'];
                }
            }

            foreach ($multipliers AS $m)
            {
                $s .= sprintf($GLOBALS['strXUnitsAtX'], $a[$m], $m);
                $totalunits += $a[$m];
                $totalbillableunits += ($m * $a[$m]);
            }

            $unitrate = get_unit_rate(incident_maintid($incidentid));

            $totalrefunds = $bills['refunds'];
            // $numberofunits += $bills['refunds'];

            $cost = (($totalbillableunits + $totalrefunds)  * $unitrate) * -1;

            $desc = trim("{$numberofunits} {$strUnits} @ {$CONFIG['currency_symbol']}{$unitrate} for incident {$incidentid}. {$s}"); //FIXME i18n

            // $rtn = update_contract_balance(incident_maintid($incidentid), $desc, $cost);

            // Add transaction
            $serviceid = get_serviceid($contractid);
            if ($serviceid < 1) trigger_error("Invalid service ID",E_USER_ERROR);
            $date = date('Y-m-d H:i:s', $now);

            $sql = "INSERT INTO `{$GLOBALS['dbTransactions']}` (serviceid, totalunits, totalbillableunits, totalrefunds, amount, description, userid, dateupdated, transactionstatus) ";
            $sql .= "VALUES ('{$serviceid}', '{$totalunits}',  '{$totalbillableunits}', '{$totalrefunds}', '{$cost}', '{$desc}', '{$_SESSION['userid']}', '{$date}', '".BILLING_AWAITINGAPPROVAL."')";

            $result = mysql_query($sql);
            if (mysql_error())
            {
                trigger_error("Error inserting transaction. ".mysql_error(), E_USER_WARNING);
                $rtnvalue = FALSE;
            }

            $transactionid = mysql_insert_id();

            if ($transactionid != FALSE)
            {

                $sql = "INSERT INTO `{$GLOBALS['dbLinks']}` VALUES (6, {$transactionid}, {$incidentid}, 'left', {$_SESSION['userid']})";
                mysql_query($sql);
                if (mysql_error())
                {
                    trigger_error(mysql_error(),E_USER_ERROR);
                    $rtnvalue = FALSE;
                }
                if (mysql_affected_rows() < 1)
                {
                    trigger_error("Link transaction on closure failed",E_USER_ERROR);
                    $rtnvalue = FALSE;
                }
            }

        }
    }

    return $rtnvalue;
}


function approve_incident_transaction($transactionid)
{
    global $dbLinks, $sit, $CONFIG, $strUnits;

    $rtnvalue = TRUE;

    // Check transaction exists, and is awaiting approval and is an incident
    $sql = "SELECT l.linkcolref, t.serviceid FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
    $sql .= "WHERE t.transactionid = l.origcolref AND t.transactionstatus = ".BILLING_AWAITINGAPPROVAL." AND l.linktype = 6 AND t.transactionid = {$transactionid}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error identify incident transaction. ".mysql_error(), E_USER_WARNING);
    if (mysql_num_rows($result) > 0)
    {
        list($incidentid, $serviceid) = mysql_fetch_row($result);

        $bills = get_incident_billable_breakdown_array($incidentid);

        $multipliers = get_all_available_multipliers();

        $totalunits = 0;
        $totalbillableunits = 0;
        $totalrefunds = 0;

        foreach ($bills AS $bill)
        {
            foreach ($multipliers AS $m)
            {
                $a[$m] += $bill[$m]['count'];
            }
        }

        foreach ($multipliers AS $m)
        {
            $s .= sprintf($GLOBALS['strXUnitsAtX'], $a[$m], $m);
            $totalbillableunits += ($m * $a[$m]);
            $totalunits += $a[$m];
        }

        $unitrate = get_unit_rate(incident_maintid($incidentid));

        $totalrefunds += $bills['refunds'];

        $cost = (($totalbillableunits += $totalrefunds) * $unitrate) * -1;

        $desc = trim("Incident {$incidentid}: {$totalbillableunits}: {$strUnits} @ {$CONFIG['currency_symbol']}{$unitrate}. {$s}"); //FIXME i18n

        $rtn = update_contract_balance(incident_maintid($incidentid), $desc, $cost, $serviceid, $transactionid, $totalunits, $totalbillableunits, $totalrefunds);

        if ($rtn == FALSE)
        {
            $rtnvalue = FALSE;
        }
    }
    else
    {
        $rtnvalue = FALSE;
    }

    return $rtnvalue;
}


function update_contract_balance($contractid, $description, $amount, $serviceid='', $transactionid='', $totalunits=0, $totalbillableunits=0, $totalrefunds=0)
{
    global $now, $dbService, $dbTransactions;
    $rtnvalue = TRUE;

    if (empty($totalunits)) $totalunits = -1;
    if (empty($totalbillableunits)) $totalbillableunits = -1;
    if (empty($totalrefunds)) $totalrefunds = 0;

    if ($serviceid == '')
    {
        // Find the correct service record to update
        $serviceid = get_serviceid($contractid);
        if ($serviceid < 1) trigger_error("Invalid service ID",E_USER_ERROR);
    }

    if (trim($amount) == '') $amount = 0;
    $date = date('Y-m-d H:i:s', $now);

    // Update the balance
    $sql = "UPDATE `{$dbService}` SET balance = (balance + {$amount}) WHERE serviceid = '{$serviceid}' LIMIT 1";
    mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_ERROR);
        $rtnvalue = FALSE;
    }

    if (mysql_affected_rows() < 1 AND $amount != 0)
    {
        trigger_error("Contract balance update failed",E_USER_ERROR);
        $rtnvalue = FALSE;
    }

    if ($rtnvalue != FALSE)
    {
        // Log the transaction
        if (empty($transactionid))
        {
            $sql = "INSERT INTO `{$dbTransactions}` (serviceid, totalunits, totalbillableunits, totalrefunds, amount, description, userid, dateupdated, transactionstatus) ";
            $sql .= "VALUES ('{$serviceid}', '{$totalunits}', '{$totalbillableunits}', '{$totalrefunds}', '{$amount}', '{$description}', '{$_SESSION['userid']}', '{$date}', '".BILLING_APPROVED."')";
            $result = mysql_query($sql);

            $rtnvalue = mysql_insert_id();
        }
        else
        {
            $sql = "UPDATE `{$dbTransactions}` SET serviceid = {$serviceid}, totalunits = {$totalunits}, totalbillableunits = {$totalbillableunits}, totalrefunds = {$totalrefunds} ";
            $sql .= ", amount = {$amount}, userid = {$_SESSION['userid']} , dateupdated = '{$date}', transactionstatus = '".BILLING_APPROVED."' ";
            if (!empty($description))
            {
                $sql .= ", description = '{$description}' ";
            }
            $sql .= "WHERE transactionid = {$transactionid}";
            $result = mysql_query($sql);
            $rtnvalue = $transactionid;
        }

        if (mysql_error())
        {
            trigger_error(mysql_error(),E_USER_ERROR);
            $rtnvalue = FALSE;
        }
        if (mysql_affected_rows() < 1)
        {
            trigger_error("Transaction insert failed",E_USER_ERROR);
            $rtnvalue = FALSE;
        }
    }

    return $rtnvalue;
}


function maintid_from_transaction($transactionid)
{
    $rtnvalue = -1;
    $sql = "SELECT i.maintenanceid FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbIncidents']}` AS i WHERE ";
    $sql .= "l.origcolref = {$transactionid} AND l.linkcolref = i.id AND l.linktype = 6";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting maintid for transaction. ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }

    return $rtnvalue;
}


function contract_transaction_total($contractid, $status)
{
    $rtnvalue = FALSE;

    $sql = "SELECT SUM(t.amount) FROM `{$GLOBALS['dbTransactions']}` AS t, `{$GLOBALS['dbService']}` AS s ";
    $sql .= "WHERE s.serviceid = t.serviceid AND s.contractid = {$contractid} AND t.transactionstatus = '{$status}'";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting total for type {$status}. ".mysql_error(), E_USER_WARNING);
    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }

    return $rtnvalue;
}


function service_transaction_total($serviceid, $status)
{
    $rtnvalue = FALSE;
    $sql = "SELECT SUM(amount) FROM `{$GLOBALS['dbTransactions']}` ";
    $sql .= "WHERE serviceid = {$serviceid} AND transactionstatus = '{$status}'";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting total for type {$status}. ".mysql_error(), E_USER_WARNING);
    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }
    return $rtnvalue;
}


function get_service_balance($serviceid, $includeawaitingapproval = TRUE, $includereserved = TRUE)
{
    global $dbService;

    $balance = FALSE;

    $sql = "SELECT balance FROM `{$dbService}` WHERE serviceid = {$serviceid}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
    if (mysql_num_rows($result) == 1)
    {
        list($balance) = mysql_fetch_row($result);
        if ($includeawaitingapproval)
        {
            $balance += service_transaction_total($serviceid, BILLING_AWAITINGAPPROVAL);
        }

        if ($includereserved)
        {
            $balance += service_transaction_total($serviceid, BILLING_RESERVED);
        }
    }
    return $balance;
}


function is_billable_incident_approved($incidentid)
{
    $sql = "SELECT DISTINCT origcolref, linkcolref ";
    $sql .= "FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
    $sql .= "WHERE l.linktype = 6 ";
    $sql .= "AND l.origcolref = t.transactionid ";
    $sql .= "AND linkcolref = {$incidentid} ";
    $sql .= "AND direction = 'left' ";
    $sql .= "AND t.transactionstatus = '".BILLING_APPROVED."'";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0) return TRUE;
    else return FALSE;
}


function get_incident_transactionid($incidentid)
{
    $rtnvalue = FALSE;
    $sql = "SELECT origcolref ";
    $sql .= "FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
    $sql .= "WHERE l.linktype = 6 ";
    $sql .= "AND l.origcolref = t.transactionid ";
    $sql .= "AND linkcolref = {$incidentid} ";
    $sql .= "AND direction = 'left' ";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        list($rtnvalue) = mysql_fetch_row($result);
    }

    return $rtnvalue;
}


function contract_service_table($contractid, $billing)
{
    global $CONFIG, $dbService, $now;

    $sql = "SELECT * FROM `{$dbService}` WHERE contractid = {$contractid} ORDER BY enddate DESC";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
    if (mysql_num_rows($result) > 0)
    {
        $shade = 'shade1';
        $html = "\n<table class='maintable' id='contractservicetable'>";
        $html .= "<tr>";
        if ($billing) $html .= "<th></th>";
        $html .= "<th>{$GLOBALS['strStartDate']}</th><th>{$GLOBALS['strEndDate']}</th>";
        if ($billing)
        {
            $html .= "<th>{$GLOBALS['strAvailableBalance']}</th>";
        }
        $html .= "<th>{$GLOBALS['strActions']}</th>";
        $html .= "</tr>\n";
        while ($service = mysql_fetch_object($result))
        {
            $service->startdate = mysql2date($service->startdate . '09:00');
            $service->enddate = mysql2date($service->enddate . '17:00');
            $service->lastbilled = mysql2date($service->lastbilled);

            $expired = false;
            $future = false;
            if ($service->enddate < $now) $expired = true;
            if ($service->startdate > $now) $future = true;

            if ($future)
            {
                $shade = 'notice';
            }
            elseif ($expired)
            {
                $shade = 'expired';
            }
            $html .= "<tr class='{$shade}'>";

            if ($billing)
            {
                $balance = get_service_balance($service->serviceid);
                $awaitingapproval = service_transaction_total($service->serviceid, BILLING_AWAITINGAPPROVAL) * -1;
                $reserved = service_transaction_total($service->serviceid, BILLING_RESERVED) * -1;

                $span = "<strong>{$GLOBALS['strServiceID']}:</strong> {$service->serviceid}<br />";
                if (!empty($service->title))
                {
                    $span .= "<strong>{$GLOBALS['strTitle']}</strong>: {$service->title}<br />";
                }

                if (!empty($service->notes))
                {
                    $span .= "<strong>{$GLOBALS['strNotes']}</strong>: {$service->notes}<br />";
                }

                if (!empty($service->cust_ref))
                {
                    $span .= "<strong>{$GLOBALS['strCustomerReference']}</strong>: {$service->cust_ref}";
                    if ($service->cust_ref_date != "1970-01-01")
                    {
                        $span .= " - <strong>{$GLOBALS['strCustomerReferenceDate']}</strong>: {$service->cust_ref_date}";
                    }
                    $span .= "<br />";
                }

                if ($service->creditamount != 0)
                {
                    $span .= "<strong>{$GLOBALS['strCreditAmount']}</strong>: {$CONFIG['currency_symbol']}".number_format($service->creditamount, 2)."<br />";
                }

                if ($service->unitrate != 0)
                {
                    $span .= "<strong>{$GLOBALS['strUnitRate']}</strong>: {$CONFIG['currency_symbol']}{$service->unitrate}<br />";
                }

                $span .= "<strong>{$GLOBALS['strBillingMatrix']}</string>: {$service->billingmatrix}<br />";

                if ($balance != $service->balance)
                {
                    $span .= "<strong>{$GLOBALS['strBalance']}</strong>: {$CONFIG['currency_symbol']}".number_format($service->balance, 2)."<br />";
                    if ($awaitingapproval != FALSE)
                    {
                        $span .= "<strong>{$GLOBALS['strAwaitingApproval']}</strong>: {$CONFIG['currency_symbol']}".number_format($awaitingapproval, 2)."<br />";
                    }

                    if ($reserved != FALSE)
                    {
                        $span .= "<strong>{$GLOBALS['strReserved']}</strong>: {$CONFIG['currency_symbol']}".number_format($reserved, 2)."<br />";
                    }

                    $span .= "<strong>{$GLOBALS['strAvailableBalance']}</strong>: ";
                    if (!$expired)
                    {
                        $span .= "{$CONFIG['currency_symbol']}".number_format($balance, 2);
                    }
                    else
                    {
                        $span .= $GLOBALS['strExpired'];
                    }
                    $span .= "<br />";
                }

                if ($service->lastbilled > 0)
                {
                    $span .= "<strong>{$GLOBALS['strLastBilled']}</strong>: ".ldate($CONFIG['dateformat_date'], $service->lastbilled)."<br />";
                }

                if ($service->foc == 'yes')
                {
                    $span .= "<strong>{$GLOBALS['strFreeOfCharge']}</strong>";
                }

                $html .= "<td><a name='billingicon' class='info'>".icon('billing', 16);
                if (!empty($span))
                {
                        $html .= "<span>{$span}</span>";
                }
                $html .= "</a></td>";
                $html .= "<td><a href='transactions.php?serviceid={$service->serviceid}' class='info'>".ldate($CONFIG['dateformat_date'], $service->startdate);
                if (!empty($span))
                {
                        $html .= "<span>{$span}</span>";
                }
                $html .= "</a></td>";
            }
            else
            {
                $html .= "<td>".ldate($CONFIG['dateformat_date'],$service->startdate);
                $html .= "</td>";
            }
            $html .= "<td>";
            $html .= ldate($CONFIG['dateformat_date'], $service->enddate)."</td>";

            if ($billing)
            {
                $html .= "<td>{$CONFIG['currency_symbol']}";
                if (!$expired) $html .= number_format($balance, 2);
                else $html .= "0";
                $html .= "</td>";
            }

            $html .= "<td>";
            $operations[$GLOBALS['strEditService']] = array('url' => "contract_edit_service.php?mode=editservice&amp;serviceid={$service->serviceid}&amp;contractid={$contractid}", 'perm' => PERM_SERVICE_EDIT);
            if ($billing)
            {
                $operations[$GLOBALS['strEditBalance']] = array('url' => "contract_edit_service.php?mode=showform&amp;sourceservice={$service->serviceid}&amp;contractid={$contractid}", 'perm' => PERM_SERVICE_BALANCE_EDIT);
                $operations[$GLOBALS['strViewTransactions']] = array('url' => "transactions.php?serviceid={$service->serviceid}", 'perm' => PERM_BILLING_TRANSACTION_VIEW);
            }
            $html .= html_action_links($operations);
            $html .= "</td></tr>\n";

            if ($shade == 'shade1') $shade = 'shade2';
            else $shade = 'shade1';
        }
        $html .= "</table>\n";
    }
    return $html;
}


function get_incident_billing_details($incidentid)
{
    /*
    $array[owner][] = array(owner, starttime, duration)
    */
    $sql = "SELECT * FROM `{$GLOBALS['dbUpdates']}` WHERE incidentid = {$incidentid} AND duration IS NOT NULL";
    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    if (mysql_num_rows($result) > 0)
    {
        while($obj = mysql_fetch_object($result))
        {
            if ($obj->duration > 0)
            {
                $temparray['owner'] = $obj->userid;
                $temparray['starttime'] = ($obj->timestamp-($obj->duration*60));
                $temparray['duration'] = $obj->duration;
                $billing[$obj->userid][] = $temparray;
            }
            else
            {
                if (empty($billing['refunds'])) $billing['refunds'] = 0;
                $billing['refunds'] += $obj->duration;
            }
        }
    }

    return $billing;
}


function group_billing_periods(&$count, $countType, $activity, $period)
{
    $duration = $activity['duration'] * 60;
    $startTime = $activity['starttime'];

    if (!empty($count[$countType]))
    {
        while ($duration > 0)
        {
            $saved = "false";
            foreach ($count[$countType] AS $ind)
            {
                /*
                echo "<pre>";
                print_r($ind);
                echo "</pre>";
                */
                //echo "IN:{$ind}:START:{$act['starttime']}:ENG:{$engineerPeriod}<br />";

                if($ind <= $activity['starttime'] AND $ind <= ($activity['starttime'] + $period))
                {
                    //echo "IND:{$ind}:START:{$act['starttime']}<br />";
                    // already have something which starts in this period just need to check it fits in the period
                    if($ind + $period > $activity['starttime'] + $duration)
                    {
                        $remainderInPeriod = ($ind + $period) - $activity['starttime'];
                        $duration -= $remainderInPeriod;

                        $saved = "true";
                    }
                }
            }
            //echo "Saved: {$saved}<br />";
            // This section runs when there are no engineer or customer billing period totals yet (first iteration)
            if ($saved == "false" AND $activity['duration'] > 0)
            {
                //echo "BB:".$activity['starttime'].":SAVED:{$saved}:DUR:{$activity['duration']}<br />";
                // need to add a new block
                $count[$countType][$startTime] = $startTime;

                $startTime += $period;

                $duration -= $period;
            }
        }
    }
    else
    {
        $count[$countType][$activity['starttime']] = $activity['starttime'];
        $localDur = $duration - $period;

        while ($localDur > 0)
        {
            $startTime += $period;
            $count[$countType][$startTime] = $startTime;
            $localDur -= $period; // was just -
        }
    }
}


function make_incident_billing_array($incidentid, $totals = TRUE)
{

    $billing = get_incident_billing_details($incidentid);

    // echo "<pre>";
    // print_r($billing);
    // echo "</pre><hr />";

    $sql = "SELECT servicelevel, priority FROM `{$GLOBALS['dbIncidents']}` WHERE id = {$incidentid}";
    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    $incident = mysql_fetch_object($result);
    $servicelevel_tag = $incident->servicelevel;
    $priority = $incident->priority;

    if (!empty($billing))
    {
        $billingSQL = "SELECT * FROM `{$GLOBALS['dbBillingPeriods']}` WHERE tag='{$servicelevel_tag}' AND priority='{$priority}'";

        /*
        echo "<pre>";
        print_r($billing);
        echo "</pre>";

        echo "<pre>";
        print_r(make_billing_array($incidentid));
        echo "</pre>";
        */

        //echo $billingSQL;

        $billingresult = mysql_query($billingSQL);
        // echo $billingSQL;
        if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
        $billingObj = mysql_fetch_object($billingresult);

        unset($billingresult);

        $engineerPeriod = $billingObj->engineerperiod * 60;  //to seconds
        $customerPeriod = $billingObj->customerperiod * 60;

        if (empty($engineerPeriod) OR $engineerPeriod == 0) $engineerPeriod = 3600;
        if (empty($customerPeriod) OR $customerPeriod == 0) $customerPeriod = 3600;

        /*
        echo "<pre>";
        print_r($billing);
        echo "</pre>";
        */

        $count = array();


        // Loop over each activity that happened during the duration of the incident
        // Grouped by Engineer - and then calculate totals
        foreach ($billing AS $engineer)
        {
            /*
                [eng][starttime]
            */

            if (is_array($engineer))
            {
                $owner = "";
                $duration = 0;

                unset($count);
                $count = array();

                $count['engineer'];
                $count['customer'];

                foreach ($engineer AS $activity)
                {
                    $owner = user_realname($activity['owner']);
                    $duration += $activity['duration'];

                    /*
                    echo "<pre>";
                    print_r($count);
                    echo "</pre>";
                    */

                    group_billing_periods($count, 'engineer', $activity, $engineerPeriod);

                    // Optimisation no need to compute again if we already have the details
                    if ($engineerPeriod != $customerPeriod)
                    {
                        group_billing_periods($count, 'customer', $activity, $customerPeriod);
                    }
                    else
                    {
                        $count['customer'] = $count['engineer'];
                    }
                }

                $tduration += $duration;
                $totalengineerperiods += sizeof($count['engineer']);
                $totalcustomerperiods += sizeof($count['customer']);
                /*
                echo "<pre>";
                print_r($count);
                echo "</pre>";
                */

                $billing_a[$activity['owner']]['owner'] = $owner;
                $billing_a[$activity['owner']]['duration'] = $duration;
                $billing_a[$activity['owner']]['engineerperiods'] = $count['engineer'];
                $billing_a[$activity['owner']]['customerperiods'] = $count['customer'];
            }

            if ($totals == TRUE)
            {
                if (empty($totalengineerperiods)) $totalengineerperiods = 0;
                if (empty($totalcustomerperiods)) $totalcustomerperiods = 0;
                if (empty($tduration)) $tduration = 0;

                $billing_a[-1]['totalduration'] = $tduration;
                $billing_a[-1]['totalengineerperiods'] = $totalengineerperiods;
                $billing_a[-1]['totalcustomerperiods'] = $totalcustomerperiods;
                $billing_a[-1]['customerperiod'] = $customerPeriod;
                $billing_a[-1]['engineerperiod'] = $engineerPeriod;
            }

            if (!empty($billing['refunds'])) $billing_a[-1]['refunds'] = $billing['refunds']/$customerPeriod; // return refunds as a number of units
            else $billing_a[-1]['refunds'] = 0;

        }

    }

    //echo "<pre>";
    //print_r($billing_a);
    //echo "</pre>";

    return $billing_a;
}


function billable_units_site($siteid, $startdate=0, $enddate=0)
{
    $sql = "SELECT i.id FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbContacts']}` AS c ";
    $sql .= "WHERE c.id = i.contact AND c.siteid = {$siteid} ";
    if ($startdate != 0)
    {
        $sql .= "AND closed >= {$startdate} ";
    }

    if ($enddate != 0)
    {
        $sql .= "AND closed <= {$enddate} ";
    }

    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error(mysql_error(),E_USER_WARNING);
        return FALSE;
    }

    $units = 0;

    if (mysql_num_rows($result) > 0)
    {
        while ($obj = mysql_fetch_object($result))
        {
            $a = make_incident_billing_array($obj->id);
            $units += $a[-1]['totalcustomerperiods'];
        }
    }

    return $units;
}


function get_incident_billable_breakdown_array($incidentid)
{
    $billable = make_incident_billing_array($incidentid, FALSE);

    $billingmatrix = '';

    $serviceid = get_serviceid(incident_maintid($incidentid));
    $sql = "SELECT billingmatrix FROM `{$GLOBALS['dbService']}` WHERE serviceid = {$serviceid}";
    $result = mysql_query($sql);
    if (mysql_error())
    {
        trigger_error("Unable to get billing matrix for service {$serviceid} ".mysql_error(),E_USER_WARNING);
    }
    list($billingmatrix) = mysql_fetch_row($result);

    //echo "<pre>";
    //print_r($billable);
    //echo "</pre>";

    if (!empty($billable))
    {
        foreach ($billable AS $engineer)
        {
            if (is_array($engineer) AND empty($engineer['refunds']))
            {
                $engineerName = $engineer['owner'];
                foreach ($engineer['customerperiods'] AS $period)
                {
                    // $period is the start time
                    $day = date('D', $period);
                    $hour = date('H', $period);

                    $dayNumber = date('d', $period);
                    $month = date('n', $period);
                    $year = date('Y', $period);
                    // echo "DAY {$day} HOUR {$hour}";

                    $dayofweek = strtolower($day);

                    if (is_day_bank_holiday($dayNumber, $month, $year))
                    {
                        $dayofweek = "holiday";
                    }

                    $multiplier = get_billable_multiplier($dayofweek, $hour, $billingmatrix);

                    $billing[$engineerName]['owner'] = $engineerName;
                    $billing[$engineerName][$multiplier]['multiplier'] = $multiplier;
                    if (empty($billing[$engineerName][$multiplier]['count']))
                    {
                        $billing[$engineerName][$multiplier]['count'] = 0;
                    }

                    $billing[$engineerName][$multiplier]['count']++;
                }
            }
        }

        if (!empty($billable[-1]['refunds'])) $billing['refunds'] = $billable[-1]['refunds'];

    }

    return $billing;
}


function contract_unit_balance($contractid, $includenonapproved = FALSE, $includereserved = TRUE, $showonlycurrentlyvalid = TRUE)
{
    global $now, $dbService;

    $unitbalance = 0;

    $sql = "SELECT * FROM `{$dbService}` WHERE contractid = {$contractid} ";

    if ($showonlycurrentlyvalid)
    {
        $sql .= "AND UNIX_TIMESTAMP(startdate) <= {$now} ";
        $sql .= "AND UNIX_TIMESTAMP(enddate) >= {$now}  ";
    }
    $sql .= "ORDER BY enddate DESC";

    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        while ($service = mysql_fetch_object($result))
        {
            $multiplier = get_billable_multiplier(strtolower(date('D', $now)), date('G', $now));
            $unitamount = $service->unitrate * $multiplier;
            if ($unitamount > 0 AND $service->balance != 0) $unitbalance += round($service->balance / $unitamount);
        }

        if ($includenonapproved)
        {
            $awaiting = contract_transaction_total($contractid, BILLING_AWAITINGAPPROVAL);
            if ($awaiting != 0) $unitbalance += round($awaiting / $unitamount);
        }

        if ($includereserved)
        {
            $reserved = contract_transaction_total($contractid, BILLING_RESERVED);
            if ($reserved != 0) $unitbalance += round($reserved / $unitamount);
        }
    }

    return $unitbalance;
}


function contract_balance($contractid, $includenonapproved = FALSE, $includereserved = TRUE, $showonlycurrentlyvalid = TRUE)
{
    global $now, $dbService;

    $unitbalance = 0;

    $sql = "SELECT * FROM `{$dbService}` WHERE contractid = {$contractid} ";

    if ($showonlycurrentlyvalid)
    {
        $sql .= "AND UNIX_TIMESTAMP(startdate) <= {$now} ";
        $sql .= "AND UNIX_TIMESTAMP(enddate) >= {$now}  ";
    }
    $sql .= "ORDER BY enddate DESC";

    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        while ($service = mysql_fetch_object($result))
        {
            $balance += round($service->balance);
        }
    }

    if ($includenonapproved)
    {
        $awaiting = contract_transaction_total($contractid, BILLING_AWAITINGAPPROVAL);
        if ($awaiting != 0) $balance += round($awaiting);
    }

    if ($includereserved)
    {
        $reserved = contract_transaction_total($contractid, BILLING_RESERVED);
        if ($reserved != 0) $balance += round($reserved);
    }

    return $balance;
}


function transactions_report($serviceid, $startdate, $enddate, $sites, $display, $sitebreakdown=TRUE, $showfoc=TRUE, $focaszero=FALSE, $includeawaitingapproval = TRUE, $includereserved = TRUE)
{
    global $CONFIG;

    $csv_currency = html_entity_decode($CONFIG['currency_symbol'], ENT_NOQUOTES, "ISO-8859-15"); // Note using -15 as -1 doesnt support euro

    $sql = "SELECT DISTINCT t.*, m.site, p.foc, p.cust_ref, p.cust_ref_date, p.title, p.notes ";
    $sql .= "FROM `{$GLOBALS['dbTransactions']}` AS t, `{$GLOBALS['dbService']}` AS p, ";
    $sql .= "`{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl, `{$GLOBALS['dbSites']}` AS s ";
    $sql .= "WHERE t.serviceid = p.serviceid AND p.contractid = m.id "; // AND t.date <= '{$enddateorig}' ";
    $sql .= "AND m.servicelevel = sl.tag AND sl.timed = 'yes' AND m.site = s.id ";
    if ($serviceid > 0) $sql .= "AND t.serviceid = {$serviceid} ";
    if (!empty($startdate)) $sql .= "AND t.dateupdated >= '{$startdate}' ";
    if (!empty($enddate)) $sql .= "AND t.dateupdated <= '{$enddate}' ";
    $orsql[] = "t.transactionstatus = ".BILLING_APPROVED;
    if ($includeawaitingapproval) $orsql[] = "t.transactionstatus = ".BILLING_AWAITINGAPPROVAL;
    if ($includereserved) $orsql[] = "t.transactionstatus = ".BILLING_RESERVED;
    $o = implode(" OR ", $orsql);
    $sql .= "AND ($o) ";

    if (!$showfoc) $sql .= "AND p.foc = 'no' ";

    if (!empty($sites))
    {
        $sitestr = '';

        foreach ($sites AS $s)
        {
            $s = clean_int($s);
            if (empty($sitestr)) $sitestr .= "m.site = {$s} ";
            else $sitestr .= "OR m.site = {$s} ";
        }

        $sql .= "AND {$sitestr} ";
    }

    if (!empty($site)) $sql .= "AND m.site = {$site} ";

    $sql .= "ORDER BY t.dateupdated, s.name ";

    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0)
    {
        $shade = 'shade1';

        $total = 0;
        $totalcredit = 0;
        $totaldebit = 0;

        $details = '';

        while ($transaction = mysql_fetch_object($result))
        {
            if ($display == 'html')
            {
                if ($serviceid > 0 AND empty($details))
                {
                    if (!empty($transaction->cust_ref))
                    {
                        $details .= "<tr>";
                        $details .= "<th>{$GLOBALS['strCustomerReference']}</th><td>{$transaction->cust_ref}</td>";
                        if ($transaction->cust_ref_date != "1970-01-01")
                        {
                            $details .= "<th>{$GLOBALS['strCustomerReferenceDate']}</th><td>{$transaction->cust_ref_date}</td>";
                        }
                        $details .= "</tr>";
                    }

                    if (!empty($transaction->title))
                    {
                        $details .= "<tr><th>{$GLOBALS['strTitle']}</th><td>{$transaction->title}</td></tr>";
                    }

                    if (!empty($transaction->notes))
                    {
                        $details .= "<tr><th>{$GLOBALS['strNotes']}</th><td>{$transaction->notes}</td></tr>";
                    }
                }

                $str = "<tr class='$shade'>";
                $str .= "<td>" . date($CONFIG['dateformat_datetime'], mysql2date($transaction->dateupdated)) . "</td>";
                $str .= "<td>{$transaction->transactionid}</td>";
                $str .= "<td>{$transaction->serviceid}</td>";
                $str .= "<td>".site_name($transaction->site)."</td>";
                $str .= "<td>{$transaction->description}</td>";
                $str .= "<td>";
                switch ($transaction->transactionstatus)
                {
                    case BILLING_APPROVED: $str .= $GLOBALS['strApproved'];
                        break;
                    case BILLING_AWAITINGAPPROVAL: $str .= $GLOBALS['strAwaitingApproval'];
                        break;
                    case BILLING_RESERVED: $str .= $GLOBALS['strReserved'];
                        break;
                }
                $str .= "</td>";
            }
            elseif ($display == 'csv')
            {
                if ($serviceid > 0 AND empty($details))
                {
                    if (!empty($transaction->cust_ref))
                    {
                        $details .= "\"{$GLOBALS['strCustomerReference']}\",\"{$transaction->cust_ref}\",";
                        if ($transaction->cust_ref_date != "1970-01-01")
                        {
                            $details .= "\"{$GLOBALS['strCustomerReferenceDate']}\",\"{$transaction->cust_ref_date}\",";
                        }
                        $details .= "\n";
                    }

                    if (!empty($transaction->title))
                    {
                        $details .= "\"{$GLOBALS['strTitle']}\",\"{$transaction->title}\"\n";
                    }

                    if (!empty($transaction->notes))
                    {
                        $details .= "\"{$GLOBALS['strNotes']}\",\"{$transaction->notes}\"\n";
                    }
                }

                $str = "\"" . date($CONFIG['dateformat_datetime'], mysql2date($transaction->dateupdated)) . "\",";
                $str .= "\"{$transaction->transactionid}\",";
                $str .= "\"{$transaction->serviceid}\",\"";
                $str .= site_name($transaction->site)."\",";
                $str .= "\"".html_entity_decode($transaction->description)."\",";
                $str .= "\"";
                switch ($transaction->transactionstatus)
                {
                    case BILLING_APPROVED:
                        $str .= $GLOBALS['strApproved'];
                        break;
                    case BILLING_AWAITINGAPPROVAL:
                        $str .= $GLOBALS['strAwaitingApproval'];
                        break;
                    case BILLING_RESERVED:
                        $str .= $GLOBALS['strReserved'];
                        break;
                }
                $str .= "\",";
            }

            if ($focaszero AND $transaction->foc == 'yes')
            {
                $transaction->amount = 0;
            }

            $total += $transaction->amount;
            if ($transaction->amount < 0)
            {
                $totaldebit += $transaction->amount;
                if ($display == 'html')
                {
                    $str .= "<td></td><td>{$CONFIG['currency_symbol']}".number_format($transaction->amount, 2)."</td>";
                }
                elseif ($display == 'csv')
                {
                    $str .= ",\"{$csv_currency}".number_format($transaction->amount, 2)."\",";
                }
            }
            else
            {
                $totalcredit += $transaction->amount;
                if ($display == 'html')
                {
                    $str .= "<td>{$CONFIG['currency_symbol']}".number_format($transaction->amount, 2)."</td><td></td>";
                }
                elseif ($display == 'csv')
                {
                    $str .= "\"{$csv_currency}".number_format($transaction->amount, 2)."\",,";
                }
            }

            if ($display == 'html') $str .= "</tr>";
            elseif ($display == 'csv') $str .= "\n";

            if ($sitebreakdown == TRUE)
            {
                $table[$transaction->site]['site'] = site_name($transaction->site);
                $table[$transaction->site]['str'] .= $str;
                if ($transaction->amount < 0)
                {
                    $table[$transaction->site]['debit'] += $transaction->amount;
                }
                else
                {
                    $table[$transaction->site]['credit'] += $transaction->amount;
                }
            }
            else
            {
                $table .= $str;
            }
            if ($shade == 'shade1') $shade = 'shade2';
            else $shade = 'shade1';
        }

        if ($sitebreakdown == TRUE)
        {
            foreach ($table AS $e)
            {
                if ($display == 'html')
                {
                    $text .= "<h3>{$e['site']}</h3>";
                    $text .= "<table align='center'  width='60%'>";
                    //echo "<tr><th colspan='7'>{$e['site']}</th></tr>";
                    $text .= "<tr><th>{$GLOBALS['strDate']}</th><th>{$GLOBALS['strID']}</th><th>{$GLOBALS['strServiceID']}</th>";
                    $text .= "<th>{$GLOBALS['strSite']}</th><th>{$GLOBALS['strDescription']}</th><th>{$GLOBALS['strCredit']}</th><th>{$GLOBALS['strDebit']}</th></tr>";
                    $text .= $e['str'];
                    $text .= "<tr><td colspan='5' align='right'>{$GLOBALS['strTotal']}</td>";
                    $text .= "<td>{$CONFIG['currency_symbol']}".number_format($e['credit'], 2)."</td>";
                    $text .= "<td>{$CONFIG['currency_symbol']}".number_format($e['debit'], 2)."</td></tr>";
                    $text .= "</table>";
                }
                elseif ($display == 'csv')
                {
                    $text .= "\"{$e['site']}\"\n\n";
                    $text .= "\"{$GLOBALS['strDate']}\",\"{$GLOBALS['strID']}\",\"{$GLOBALS['strServiceID']}\",";
                    $text .= "\"{$GLOBALS['strSite']}\",\"{$GLOBALS['strDescription']}\",\"{$GLOBALS['strCredit']}\",\"{$GLOBALS['strDebit']}\"\n";
                    $text .= $e['str'];
                    $text .= ",,,,{$GLOBALS['strTotal']},";
                    $text .= "\"{$csv_currency}".number_format($e['credit'], 2)."\",\"";
                    $text .="{$csv_currency}".number_format($e['debit'], 2)."\"\n";
                }
            }
        }
        else
        {
            if ($display == 'html')
            {
                if (!empty($details))
                {
                    // Dont need to worry about this in the above section as sitebreakdown and serviceid are multually exclusive
                    $text .= "<div><table class='maintable'>{$details}</table></div>";
                }

                $text .= "<table class='maintable'>";
                $text .= "<tr><th>{$GLOBALS['strDate']}</th><th>{$GLOBALS['strID']}</th><th>{$GLOBALS['strServiceID']}</th>";
                $text .= "<th>{$GLOBALS['strSite']}</th>";
                $text .= "<th>{$GLOBALS['strDescription']}</th><th>{$GLOBALS['strStatus']}</th><th>{$GLOBALS['strCredit']}</th><th>{$GLOBALS['strDebit']}</th></tr>";
                $text .= $table;
                $text .= "<tfoot><tr><td colspan='6' align='right'>{$GLOBALS['strTOTALS']}</td>";
                $text .= "<td>{$CONFIG['currency_symbol']}".number_format($totalcredit, 2)."</td>";
                $text .= "<td>{$CONFIG['currency_symbol']}".number_format($totaldebit, 2)."</td></tr></tfoot>";
                $text .= "</table>";
            }
            elseif ($display == 'csv')
            {
                if (!empty($details))
                {
                    $text .= $details;
                }
                $text .= "\"{$GLOBALS['strDate']}\",\"{$GLOBALS['strID']}\",\"{$GLOBALS['strServiceID']}\",";
                $text .= "\"{$GLOBALS['strSite']}\",";
                $text .= "\"{$GLOBALS['strDescription']}\",\"{$GLOBALS['strStatus']}\",\"{$GLOBALS['strCredit']}\",\"{$GLOBALS['strDebit']}\"\n";
                $text .= $table;
                $text .= ",,,,{$GLOBALS['strTOTALS']},";
                $text .= "\"{$csv_currency}".number_format($totalcredit, 2)."\",\"";
                $text .= "{$csv_currency}".number_format($totaldebit, 2)."\"\n";
            }
        }
    }
    else
    {
        if ($display == 'html')
        {
            $text = "<p align='center'>{$GLOBALS['strNoTransactionsMatchYourSearch']}</p>";
        }
        elseif ($display == 'csv')
        {
            $text = $GLOBALS['strNoTransactionsMatchYourSearch']."\n";
        }
    }

    return $text;
}


function service_dropdown_contract($contractid, $name, $selected=0)
{
    global $now, $CONFIG;
    $sql = "SELECT * FROM `{$GLOBALS['dbService']}` WHERE contractid = {$contractid} ";
    $sql .= "AND UNIX_TIMESTAMP(startdate) <= {$now} AND UNIX_TIMESTAMP(enddate) >= {$now}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting services. ".mysql_error(), E_USER_WARNING);

    $html = FALSE;

    if (mysql_num_rows($result) > 0)
    {
        $html = "<select name='{$name}' id={$name}>\n";
        $html .= "<option value='0' ";
        if ($selected == 0) $html .= " selected='selected' ";
        $html .= "></option>";
        while ($obj = mysql_fetch_object($result))
        {
            $html .= "<option value='{$obj->serviceid}' ";
            if ($selected == $obj->serviceid) $html .= " selected='selected' ";
            $html .= ">{$CONFIG['currency_symbol']}".get_service_balance($obj->serviceid, TRUE, TRUE);
            $html .= " ({$obj->startdate} - {$obj->enddate})</option>";
        }
        $html .= "</select>\n";
    }

    return $html;
}


function service_dropdown_site($siteid, $name, $selected=0)
{
    global $now, $CONFIG;
    $sql = "SELECT s.* FROM `{$GLOBALS['dbService']}` AS s, `{$GLOBALS['dbMaintenance']}` AS m ";
    $sql .= "WHERE s.contractid = m.id AND  m.site = {$siteid} ";
    $sql .= "AND UNIX_TIMESTAMP(s.startdate) <= {$now} AND UNIX_TIMESTAMP(s.enddate) >= {$now}";
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting services. ".mysql_error(), E_USER_WARNING);

    $html = FALSE;

    if (mysql_num_rows($result) > 0)
    {
        $html = "<select name='{$name}' id={$name}>\n";
        $html .= "<option value='0' ";
        if ($selected == 0) $html .= " selected='selected' ";
        $html .= "></option>";
        while ($obj = mysql_fetch_object($result))
        {
            $html .= "<option value='{$obj->serviceid}' ";
            if ($selected == $obj->serviceid) $html .= " selected='selected' ";
            $html .= ">{$CONFIG['currency_symbol']}".get_service_balance($obj->serviceid, TRUE, TRUE);
            $html .= " ({$obj->startdate} - {$obj->enddate})</option>";
        }
        $html .= "</select>\n";
    }
    else
    {
        $html = "No services currently valid";
    }

    return $html;
}


function is_transaction_approved($transactionid)
{
    $sql = "SELECT transactionid FROM `{$GLOBALS['dbTransactions']}` WHERE transactionid = {$transactionid} AND transactionstaus = ".BILLING_APPROVED;
    $result = mysql_query($sql);
    if (mysql_error()) trigger_error("Error getting services. ".mysql_error(), E_USER_WARNING);

    if (mysql_num_rows($result) > 0) return TRUE;
    else return FALSE;
}

?>