/*
 * MySQLiDataSource class of KoolGrid
 * Add this class to the MySQLiDataSource.php file and use this class as your data source.
 * Method: AddSortObject() will allow you to apply a final sort on a nested query.
 */
class MySQLiDataSource_MariaDB extends MySQLiDataSource
{    
    /**
     * In MariaDB you can not do sort in sub queries therefore you must sort outside
     * @var string
     */
    public $FinalSort = array();
    
    /**
     * Add a sort to the final query.
     * @param string $fieldName
     * @param strng $fieldOrder Use "ASC" or "DESC"     * 
     */
    public function AddSortObject($fieldName='',$fieldOrder='')
    {        
        // @param type $sortOrder Use 0 = ASC , <0 for DESC 
        $sortOrder = ($fieldOrder == "ASC" ? 0 : 2 );
        $DataSourceSort = new DataSourceSort($fieldName, $fieldOrder, $sortOrder);
        $this->FinalSort[] = $DataSourceSort ; 
    }
    
    /**
     * Check if a field sort is in the sort array already since KoolGrid columns
     * can be sorted.
     * @param DataSourceSort $DataSortObjNew
     * @return boolean
     */
    protected function IsInSortArray( &$DataSortObjNew )
    {
        //$DataSortObj = new DataSourceSort($fieldName, $fieldOrder, $sortOrder);
        foreach( $this->Sorts as $DataSortObj )
        {   // add sort objects
            if( $DataSortObj->Field == $DataSortObjNew->Field )
            {   // no duplicate fields please
                return true;
            }
        }
        return false ;
    }
    
    /**
     * MariaDB compatible
     * @param type $_start
     * @param type $_count
     * @return type
     */
    function GetData($_start=0,$_count=9999999)
    {
        //Return associate array of data
        $_tpl_select_command =  "SELECT * FROM ({SelectCommand}) AS _TMP {where} {orderby} {groupby} {limit} ";
        if( count( $this->FinalSort ) > 0 )
        {
            foreach( $this->FinalSort as $arrSort )
            {   
                // add sort objects
                if( !$this->IsInSortArray( $arrSort ) )
                {
                    $this->Sorts[] = $arrSort ;
                }
            }
        }        
           
        //Filters
        $_where = "";
        $_filters = $this->Filters;
        for( $i=0; $i < sizeof($_filters); $i++)
        {   $_where .= " and " . $this->GetFilterExpression($_filters[$i]); }
        
        if( $_where != "" )
        {   $_where = "WHERE ".substr($_where,5); }
         
        //Order
        $_orderby = "";
        $_orders = $this->Sorts;
        for($i=0;$i<sizeof($_orders);$i++)
        {   $_orderby.=", ".$_orders[$i]->Field." ".$_orders[$i]->Order; }
        
        if ($_orderby!="")
        {   $_orderby = "ORDER BY ".substr($_orderby,2); }
            
        //Group
        $_groupby = "";
        $_groups = $this->Groups;
        for($i=0;$i<sizeof($_groups);$i++)
        {
                $_groupby.=", ".$_groups[$i]->Field;
        }
        if ($_groupby!="")
        {
                $_groupby = "GROUP BY ".substr($_groupby,2);
        }
        
        //Limit
        $_limit = "LIMIT ".$_start." , ".$_count; 		
        $_select_command = str_replace("{SelectCommand}",$this->SelectCommand,$_tpl_select_command);
        $_select_command = str_replace("{where}",$_where,$_select_command);
        $_select_command = str_replace("{orderby}",$_orderby,$_select_command);
        $_select_command = str_replace("{groupby}",$_groupby,$_select_command);
        $_select_command = str_replace("{limit}",$_limit,$_select_command);
        $_result = mysqli_query($this->_Link, str_replace(';', ' ', $_select_command) ); // $_select_command
        $_rows = array();
        if( $_result ) {
            while ($_row = mysqli_fetch_assoc($_result)) 
            {
                foreach ($_row as $_column => & $_value)
                    $_value = $this->getMappedValue($_value, $_column);
                            array_push($_rows,$_row);
            }
        }
        return $_rows;
    }
        
}

   /**
     * Function to setup all queries.
     */
    protected function SetupQueries()
    {   
        $this->MySQLiDataSource->SelectCommand = $this->GetSelectStatement();
        $this->MySQLiDataSource->AddSortObject('responseType', 'DESC');
        $this->MySQLiDataSource->AddSortObject('tstamp', 'ASC');
    }