<?php

    /*
     * Script:    DataTables server-side script for PHP and MySQL
     * Copyright: 2010 - Allan Jardine
     * License:   GPL v2 or BSD (3-point)
     */

    // BETA - Modified Chris Wright 06/12

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */

    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */
    $aColumns = array('hit.timestamp', 'hit.id', 'config.Name', 'hit.meter_id','levels.LevelName','pos.sm_pos','hit.hit_value'  );

    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "hit.id";

    /* DB table to use */
    $sTable = "hit
       INNER JOIN config
               ON hit.id = config.id
       INNER JOIN levels
               ON hit.meter_id = levels.id
       INNER JOIN POS
               ON pos.id = hit.id
       INNER JOIN controllers
               ON pos.controller_id = controllers.id";

    /* Database connection information */
    $gaSql['user']       = "";
    $gaSql['password']   = "";
    $gaSql['db']         = "";
    $gaSql['server']     = "localhost";


    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */

    function fatal_error ( $sErrorMessage = '' )
    {
        /* CW 06/2012
         * Return HTTP 500 to the client with an error message in the body
         */
        header( $_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error' );
        die( $sErrorMessage );
    }

    function parse_mysql_field ($sField)
    {
        /* CW 06/2012
         * Takes as string definition of a MySQL database field and return an array describing
         * the column name, table name (if present) and alias name (if present)
         */
        $sRegex =
             // Delimiter
            '/'
            // Leading white space
          . '^\s*'
            // Table name
          . '(?:(?P<table>(?:`[^`]+`|[\w$]+))\.)?'
            // Column name
          . '(?P<column>(?:`[^`]+`|[\w$]+))'
            // Column alias
          . '(?:\s+as\s+(?P<alias>(?:(?P<aliasquotetype>[`\'"]?)[\w$]+\4|[\w$]+)))?'
            // Delimiter/no case
          . '/i';
        preg_match($sRegex, $sField, $aMatches);

        $aResult = array();

        if ( $aMatches['table'] != '')
        {
            $aResult['table'] = trim( $aMatches['table'], '`' );
        }
        $aResult['column'] = trim( $aMatches['column'], '`' );
        if ( !empty( $aMatches['alias'] ) )
        {
            if ( !empty( $aMatches['aliasquotetype'] ) )
            {
                $aResult['alias'] = trim( $aMatches['alias'], $aMatches['aliasquotetype'] );
            }
            else
            {
                $aResult['alias'] = $aMatches['alias'];
            }
        }

        return $aResult;

    }

    if ( ! function_exists( 'json_encode' ) )
    {
        function json_encode ($mData)
        {
            /* CW 06/2012
             * Replacement for native PHP json_enocde() when unavailable
             */
            $bIsObject = FALSE;
            switch (TRUE) {
                case $mData === NULL: return 'null';
                case is_bool($mData): return $mData ? 'true' : 'false';
                case is_int($mData) || is_float($mData): return $mData;
                case is_string($mData): return '"' . addcslashes($mData, '"\\') . '"';
                case is_object($mData):
                    $bIsObject = TRUE;
                    $mData = get_object_vars($mData);
                case is_array($mData):
                    $i = 0;
                    foreach ($mData as $mKey => $mValue)
                    {
                        if ($mKey !== $i++)
                        {
                            $bIsObject = TRUE;
                            break;
                        }
                    }
                    $iLength = count($mData);
                    if ($bIsObject)
                    {
                        foreach ($mData as $sKey => $mValue)
                        {
                            $mData[$sKey] = json_encode((string) $sKey) . ':' . json_encode($mValue);
                        }
                        return '{' . implode ( ',', $mData ) . '}';
                    }
                    else
                    {
                        foreach ($mData as $iKey => $mValue)
                        {
                               $mData[$iKey] = json_encode($mValue);
                        }
                        return '[' . implode ( ',', $mData ) . ']';
                    }
            }
        }
    }

    /*
     * MySQL connection
     */
    if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
    {
        /* CW 06/2012
         * http://www.phpfreaks.com/blog/or-die-must-die
         * Send a meaningful HTTP response code on the event of failure - especially since
         * the client is expecting a JSON response on success
         */
        fatal_error( 'Could not open connection to server' );
    }

    if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
    {
        /* CW 06/2012
         * Giving the database name out is unnecessary information and may provide
         * the haxxors with that missing piece of information that let's them bring
         * the whole site down
         */
        fatal_error( 'Could not select database ' );
    }

    /* CW 06/2012
     * Number of columns cached to prevent loop condition on count()
     */
    $iNumCols = count($aColumns);

    /* CW 06/2012
     * Create list of columns for use outside field list
     * This allows the use of identifier aliases in $aColumns, and quotes identifiers
     * to avoid collisions with MySQL reserved words
     */
    $aClauseColumns = array();
    for ( $i = 0 ; $i < $iNumCols ; $i++ )
    {
        /* Parse column string */
        $aField = parse_mysql_field( $aColumns[$i] );

        /* Create properly quoted strings from parsed values */
        $sClauseCol = $sFieldCol = $sTableName = $sColumnName = $sColumnAlias = '';
        if ( !empty( $aField['table'] ) )
        {
            $sTableName = '`' . trim( $aField['table'], '`' ) . '`.';
            $sClauseCol .= $sTableName;
            $sFieldCol .= $sTableName;
        }
        $sColumnName = '`' . trim( $aField['column'], '`' ) . '`';
        $sClauseCol .= $sColumnName;
        $sFieldCol .= $sColumnName;
        if ( !empty( $aField['alias'] ) )
        {
            $sFieldCol .= ' AS `' . $aMatches['alias'] . '`';
        }
        $aClauseColumns[$i] = $sClauseCol;
        $aColumns[$i] = $sFieldCol;
    }

    /*
     * Paging
     */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        /* CW 06/2012
         * @mysql_real_escape_string() is for STRINGS!!
         * LIMIT parameters are integers - casting to int is quicker than
         * ...escape_string() and will not produce syntactically invalid queries
         */
        $sLimit = "LIMIT ".((int) $_GET['iDisplayStart'] ).", ".((int) $_GET['iDisplayLength'] );
    }


    /*
     * Ordering
     */
    /* CW 06/2012
     * Using an array removes the nasty string initialisation that may end up
     * being reversed. Also removes the need to substr_replace()
     */
    $sOrder = "";
    $aOrder = array();
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        /* CW 06/2012
         * The intval() call is completely unnecessary, type coercion will sort it
         * out for us. However if we want to force the type, we should do it *before*
         * the loop since the result will be the same every time.
         */
        $iSortingCols = (int) $_GET['iSortingCols'];
        for ( $i = 0 ; $i < $iSortingCols ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                /* CW 06/2012
                 * @mysql_real_escape_string() is for STRINGS!!
                 * MySQL keywords cannot be escaped in a meaningful way. Use string
                 * comparisons instead.
                 */
                $sSortDirection = (strtoupper( trim( $_GET['sSortDir_'.$i] ) ) == 'DESC') ? 'DESC' : 'ASC';
                $aOrder[] = $aClauseColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".$sSortDirection;
            }
        }

        if ( count( $aOrder ) > 0)
        {
            $sOrder = "ORDER BY " . implode(', ', $aOrder);
        }
    }


    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    /* CW 06/2012
     * Array use for same reasons as in Ordering: section
     */
    $sWhere = "";
    $aWhereOr = array(); //
    if ( isset( $_GET['sSearch'] ) && $_GET['sSearch'] !== '' )
    {
        for ( $i = 0 ; $i < $iNumCols ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $aWhereOr[] = $aClauseColumns[$i]." LIKE '%".@mysql_real_escape_string( $_GET['sSearch'] )."%'";
            }
        }
        if ( count($aWhereOr) > 0 )
        {
            $sWhere .= "WHERE (" . implode(' OR ', $aWhereOr) . ')';
        }
    }

    /* Individual column filtering */
    /* CW 06/2012
     * Array use for same reasons as in Ordering: section
     */
    $aWhereAnd = array();
    for ( $i = 0 ; $i < $iNumCols ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && isset( $_GET['sSearch_'.$i] ) && $_GET['sSearch_'.$i] !== '' )
        {
            $aWhereAnd[] = $aClauseColumns[$i]." LIKE '%".@mysql_real_escape_string($_GET['sSearch_'.$i])."%'";
        }
    }
    if ( count($aWhereAnd) > 0 )
    {
        if ($sWhere == "")
        {
            $sWhere .= "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= implode(' AND ', $aWhereAnd);
    }


    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS " . implode( ", ", $aColumns ) . "
        FROM $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] )
        /* CW 06/2012
         * Showing mysql_error() to the outside world is a BIG no-no
         */
        or fatal_error( 'MySQL Error: ' . mysql_errno() );

    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] )
        or fatal_error( 'MySQL Error: ' . mysql_errno() );
    $iFilteredTotal = mysql_result( $rResultFilterTotal, 0, 0 );
    mysql_free_result( $rResultFilterTotal );

    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] )
        or fatal_error( 'MySQL Error: ' . mysql_errno() );
    $iTotal = mysql_result( $rResultTotal, 0, 0 );
    mysql_free_result( $rResultTotal );


    /*
     * Output
     */
    $aOutput = array
    (
        "sEcho" => (int) $_GET['sEcho'],
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    if ( mysql_num_rows( $rResult ) > 0 )
    {

        /* CW 06/2012
         * Find the position of the 'version' column if one was defined
         */
        $aCols = array_keys( mysql_fetch_assoc( $rResult ) );
        mysql_data_seek($rResult, 0);
        $iVersionCol = array_search( 'version', $aCols );

        /* CW 06/2012
         * Using _fetch_row() gives us the array in the format we want with no
         * messing about. This will be faster and more memory efficient
         */
        while ( $aRow = mysql_fetch_row( $rResult ) )
        {
            if ( $iVersionCol !== FALSE && $aRow[$iVersionCol] == '0')
            {
                /* CW 06/2012
                 * Only modify the data if 'version' is defined
                 */
                $aRow[$iVersionCol] = '-';
            }
            $aOutput['aaData'][] = $aRow;
        }
    }

    echo json_encode( $aOutput );