<?php

/**
 * The public-facing functionality of the plugin.
 *
 * @link       https://orbicular.media
 * @since      1.0.0
 *
 * @package    Cemetery_Sexton
 * @subpackage Cemetery_Sexton/public
 */

/**
 * The public-facing functionality of the plugin.
 *
 * Defines the plugin name, version, and two examples hooks for how to
 * enqueue the public-facing stylesheet and JavaScript.
 *
 * @package    Cemetery_Sexton
 * @subpackage Cemetery_Sexton/public
 * @author     Bryan Meeks <bryan@orbicular.media>
 */
class Cemetery_Sexton_Results {
    //Build pagination parameters
    public function results_pagination( $getParams ) {
        //build blank array;        
        $pagi = array('orderby', 'sorttype', 'pageno', 'recpage');
        $pagi = array_fill_keys($pagi, '');
        //Build array
        foreach($getParams as $key => $value){
            if($key == 'orderby' || $key == 'sorttype' || $key == 'pageno' || $key == 'recpage'){
                $pagi[$key] = $value;
            }
        }
        //Set defaults if not specified by user
        if(empty($pagi['pageno'])){
            $pagi['pageno'] = 1;
        }
        if(empty($pagi['recpage'])) {
            $pagi['recpage'] = 25;
        }
        if(empty($pagi['orderby'])) {
            $pagi['orderby'] = 'name';
        }
        if(empty($pagi['sorttype'])) {
            $pagi['sorttype'] = 'ASC';
        }
        //Make new sort type
        if($pagi['sorttype'] == 'ASC'){
            $pagi['newsorttype'] = 'DESC';
        }else {
            $pagi['newsorttype'] = 'ASC';
        }
        
        //echo "results_pagination ";
        //print_r($pagi);
        //echo "<br>";
        return $pagi;
    }
    //Get query parameters and build string for sql statement
    public function results_buildQueryParameters( $getParams ) {
        //must have empty array incase no parameters are passed
        $para = array();
        $str = array();
        //Build array from url get parameteres
        foreach($getParams as $key => $value){
            if ($key != 'option' && $key != 'controller' && $key != 'task' && $key != 'view' &&
                $key != 'layout' && $key != 'Itemid' && $key != 'pageno' && $key != 'recpage' &&
                $key != 'sorttype' && $key != 'newsorttype' && $key != 'orderby' && 
                $key != 'id' && $key != 'pagename' && $key != 'similar') {
                    if ($value != Null){
                        $para[$key] = $value;
                    }
                }
        }
        //Get similar?
        $similar = !empty($getParams['similar']) ? $getParams['similar'] : '';
        //Build query parameters
        foreach($para as $key => $value)
        {
            //Similar or exact condition
            if($similar == 'yes') {
                $str[] = $key." LIKE '%".$value."%'";
            }else {
                $str[] = $key." LIKE '".$value."'";
            }
            //Simple search
            //$str[] = $key.' = "'.$value.'"';
        }
        $finalStr = implode(' AND ', $str);
        //echo "results_buildQueryParameters ".$finalStr."<br>";
        return $finalStr;
    }
    //Build query for SQL
    public function results_getQuery( $getParams ) {
        $querystring = $this->results_buildQueryParameters( $getParams );
        $table = 'dbo.Inter';
        $columns = 'InterID, LastName, FirstName, MidName, Suffix, DateBirth, DateDeath';
        //Get pagination parameters
        $pagi = $this->results_pagination( $getParams );
        //print_r($pagi);
        //Order by depends on sort type and order by
        $sorttype = $pagi['sorttype'];
        $orderby = $pagi['orderby'];
        
        //Change orderby into sql string with sorttype
        //Create orderby string for inner sql statement
        if($orderby == 'name'){
            $orderbyin = 'LastName '.$sorttype.', FirstName '.$sorttype.', MidName '.$sorttype;
        }else{
            $orderbyin = $_GET['orderby'].' '.$sorttype;
        }
        
        //Create both orderby string for outer sql statement
        if ($sorttype == 'ASC') {
            $orderbyout = str_replace('ASC','DESC',$orderbyin);
        }else  {
            $orderbyout = str_replace('DESC','ASC',$orderbyin);
        }
        
        //Get Pagination variables
        $rstotal = $this->results_getTotal( $getParams );
        $currentpage = $pagi['pageno'];
        $recordsbypage = $pagi['recpage'];
        
        //Pagination start and stop limits
        //This fixes last page of results, displaying only the remaining rows
        $rows = ($recordsbypage * ($currentpage-1)) + $recordsbypage;
        if ($rows > $rstotal){
            $sqllimit = $rstotal;
            $recpage = ($rstotal - ($rows - $recordsbypage));
        }else{
            $sqllimit = $rows;
            $recpage = $recordsbypage;
        }
        
        //This complex sql statement is design for speed
        //This only queries the database for the desired amount of rows, increasing speed
        $query = 'SELECT * FROM ( '
            . 'SELECT TOP '.$recpage.' '.$columns.' FROM ( '
                . 'SELECT TOP '.$sqllimit.' '.$columns.' FROM '.$table.' WHERE '.$querystring.' ORDER BY '.$orderbyin.' )'
                    . ' AS newtb1 ORDER BY '.$orderbyout.' )'
                        . ' AS newtb2 ORDER BY '.$orderbyin ;
        //echo "results_getQuery ".$query."<br>";
        return $query;
    }
    
    //Get total number of results from query
    //This is done separately to get all of the records not just those within the limit
    public function results_getTotal( $getParams ) {
        $querystring = $this->results_buildQueryParameters( $getParams );
        $table = 'dbo.Inter';
        //SQL statement to count rows from table
        $totalstring = 'SELECT * FROM '.$table.' WHERE '.$querystring;
        //Connect to database
        $db = New Cemetery_Sexton_SQLconnect();
        $conn = $db->cemetery_sexton_connect();
        //Execute SQL statement
        $query = sqlsrv_query($conn,$totalstring,array(), array( "Scrollable" => 'static' ));
        //Get the number of rows from the query
        $total = sqlsrv_num_rows($query);
        //echo "results_getTotal ".$total."<-<br>";
        return $total;
    }
    
    public function results_getPageParams( $getParams ) {
        //must have empty array incase no parameters are passed
        $para = array();
        //Build array from url get parameteres
        //$get =  $_GET;
        foreach($getParams as $key => $value){
            if ($key != 'option' && $key != 'controller' && $key != 'task' && $key != 'view' &&
                $key != 'layout' && $key != 'Itemid' && $key != 'pageno' && $key != 'recpage' &&
                $key != 'sorttype' && $key != 'newsorttype' && $key != 'pagename' && $key != 'orderby') {
                    $para[$key] = $value;
                }
        }
        $params = http_build_query($para);
        //echo "results_getPageParams ".$para."<br>";
        return $params;
    }
    
    //Get the records from the database
    public function results_getRecords( $getParams ) {
        //Connect to database
        $sql = $this->results_getQuery( $getParams );
        $db = New Cemetery_Sexton_SQLconnect();
        $conn = $db->cemetery_sexton_connect();
        //Execute SQL statement
        $query = sqlsrv_query($conn,$sql);
        //check query
        if( $query === false)
        {
            echo "Error in query preparation/execution.\n";
            die( print_r( sqlsrv_errors(), true));
        }      
        //Build results multidimensional array
        $i=0;
        while($rs = sqlsrv_fetch_array($query,SQLSRV_FETCH_ASSOC)){
            $interRow[$i]=$rs;
            $i++;
        }
        return $interRow;
    }

}