<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Adriano
 * Date: 12/11/12
 * Time: 17:11
 * To change this template use File | Settings | File Templates.
 */

if(!defined('ROOTPATH'))
    define('ROOTPATH', dirname(__FILE__).'/../..');

require_once ROOTPATH.'/api/controller.php';
use prototype\api\Config as Config;


class Grid{


    private $post;
    private $get;
    private $offset;
    private $response;

    public function Grid( $post, $get ){
        $this->post = $post;
        $this->get = $get;

        $this->offset = ($this->get['rows'] * $this->get['page']) - $this->get['rows'];
    }

    public function Execute(){

        $start = '';
        $end = '';

        $order = array(
            'GITEC' => 's.gitec',
            'SR' => 's.sr',
            'PV' => 's.pv',
            'owner' => 's.owner',
            'id' => 's.id_object',
            'cause' => 's.cause',
            'dateOccurrence' => 's.date_occurrence',
            'status' => 's.id_status',
            'category' => 's.id_category',
            'budget' => 'b.control_number'
        );

        $condition = array();

        //Dados do chamado
        $sql = 'select s.id_object as "id", s.gitec as "GITEC", s.pv as  "PV",'
            .'s.sr as "SR", s.timestamp as "timestamp", s.owner as "owner",'
            .'s.cause as "cause", s.occurrence as "occurrence", s.id_category as "category",'
            .'s.id_status as "status", s.post_date as "postDate", s.insurer_date as "insurerDate",'
            .'s.is_valid as "isValid", s.insurer_protocol as "insurerProtocol", bu.id_archive  as "budgetId", '

            //verify as budget
            .'b.control_number as "budget", '

            //verify as budget upload
            .'count( bu.id_archive ) as "budgetUpload", '

            //verify as payment
            .'p.id_payment as "payment" '

            //Join entre object e budget
            .'FROM sinister_object as "s"  LEFT JOIN sinister_budget as "b" ON (b.id_object = s.id_object) '

            //Join entre object e payment
            .'LEFT JOIN sinister_payment as "p" ON (p.id_object = s.id_object) '

            //Join entre object e budget upload
            .'LEFT JOIN sinister_archive as "bu" ON (bu.id_object = s.id_object AND bu.type_archive = 4) ';

        //Chamados apartir de uma data
        if (isset($this->get['start']) && $this->get['start'] != '') {
            $condition[] = (count( $condition ) > 0 ? ' AND' : '') . ' s.date_occurrence >= \''. pg_escape_string( $this->get['start'] ) .'\'';
        }

        //Chamados at� de uma data
        if (isset($this->get['end']) && $this->get['end'] != '') {
            $condition[] = (count( $condition ) > 0 ? ' AND' : '') . ' s.date_occurrence <= \''. pg_escape_string( $this->get['end'] ) .'\'';
        }


        //Chamados de um usu�rio em especifico
        if (isset($this->get['owner']) && $this->get['owner'] != '') {
            $condition[] = (count( $condition ) > 0 ? ' AND' : '') . ' s.owner = \''. pg_escape_string( $this->get['owner'] ) .'\'';
        }

        ////Chamados de uma GITEC
        //if (isset($this->get['GITEC']) && $this->get['GITEC'] != '') {
        //    $condition[] = (count( $condition ) > 0 ? ' AND' : '') .' s.gitec = \''. pg_escape_string( $this->get['GITEC'] ) .'\'';
        //}

        ////Chamados de uma FUNC_GITEC
        //if (isset($this->get['FUNC_GITEC']) && $this->get['FUNC_GITEC'] != '') {
        //    $condition[] = (count( $condition ) > 0 ? ' AND' : '') .' s.gitec = \''. pg_escape_string( $this->get['FUNC_GITEC'] ) .'\'';
        //}

        //Chamados de uma SR
        //if (isset($this->get['SR']) && $this->get['SR'] != '') {
        //    $condition[] = (count( $condition ) > 0 ? ' AND' : '') .' s.sr = \''. pg_escape_string( $this->get['SR'] ) .'\'';
        //}
        //
        ////Chamados de uma FUNC_SR
        //if (isset($this->get['FUNC_SR']) && $this->get['FUNC_SR'] != '') {
        //    $condition[] = (count( $condition ) > 0 ? ' AND' : '') .' s.sr = \''. pg_escape_string( $this->get['FUNC_SR'] ) .'\'';
        //}
        //
        ////Chamados de uma PV
        //if (isset($this->get['PV']) && $this->get['PV'] != '') {
        //    $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' s.pv = \''. pg_escape_string( $this->get['PV'] ) .'\'';
        //}
        //
        ////Chamados de uma FUNC_PV
        //if (isset($this->get['FUNC_PV']) && $this->get['FUNC_PV'] != '') {
        //    $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' s.pv = \''. pg_escape_string( $this->get['FUNC_PV'] ) .'\'';
        //}

        //Chamados em Status
        if (isset($this->get['status']) && $this->get['status'] != '') {
            $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' s.id_status = \''. pg_escape_string( $this->get['status'] ) .'\'';
        }

        //Chamados por id
        if (isset($this->get['id']) && $this->get['id'] != '') {
            $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' s.id_object = \''. pg_escape_string( $this->get['id'] ) .'\'';
        }

        //Chamados por categoria
        if (isset($this->get['category']) && $this->get['category'] != '') {
            $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' s.id_category = \''. pg_escape_string( $this->get['category'] ) .'\'';
        }

        //Filtro por numero de or�amento
        if (isset($this->get['controlNumber']) && $this->get['controlNumber'] != '') {
            $condition[] =  (count( $condition ) > 0 ? ' AND' : '') .' b.control_number LIKE \''."%". pg_escape_string( $this->get['controlNumber'] ) ."%".'\'';
        }

        //Not in Status
        if (isset($this->get['!status']) && $this->get['!status'] != '') {
            $condition[] = (count( $condition ) > 0 ? ' AND' : '') . ' s.id_status NOT IN ( '. implode(', ', $this->get['!status'] ) .')';
        }


        if( count( $condition ) ){

            //Custom where
            $where = 'where ( ';

            foreach($condition as $v){
                if($v != '')
                    $where .= $v;
            }

            $where .= ') ';
        }else{

            $where = '';

        }

        $sqlCount = 'SELECT count( * ) from sinister_object as "s" LEFT JOIN sinister_budget as "b" ON ( s.id_object = b.id_object ) ';

        $count = Controller::service('PostgreSQL')->execResultSql( $sqlCount.$where );

        $count = is_array( $count ) ? $count[0]['count'] : $count;

        if(isset( $order[ $this->get['sidx'] ])){
            $where .= ' GROUP BY s.id_object, b.control_number, p.id_payment, bu.id_archive ORDER BY '.$order[ $this->get['sidx'] ]. ' '. $this->get['sord'];
        }else{
            $where .= ' GROUP BY s.id_object, b.control_number, p.id_payment, bu.id_archive ORDER BY s.timestamp DESC';

        }

        $where .= ' limit '.$this->get['rows'].' offset '.$this->offset;

        $data = Controller::service('PostgreSQL')->execResultSql( $sql.$where );

        if(empty($data) || count($data) == 0){
            echo json_encode( array() );
            return false;
        }

        $i = 0;
        $this->response['rows'] = array();

        $userIndex = array();

        /*
         *  Normalize data
         * */
        foreach($data as &$v){

            if( !isset($userIndex[ $v['owner'] ]) ){

                $user = Controller::read(array( 'concept' => 'user', 'id' => (int)$v['owner'] ));
                if( isset( $user[0] ) ) $user = $user[0];

                $userIndex[ $v['owner'] ] = $user;

            }

            $v['name'] = $userIndex[ $v['owner'] ]['name'];
            $v['dateOccurrence'] =  date("d/m/Y",  ($v['timestamp'] / 1000) );


            $this->response['rows'][$i] = $v;
            ++$i;
        }

        $this->response['page'] = $this->get['page'];
        $this->response['total'] = ceil($count / $this->get['rows']);
        $this->response['records'] = $count;

        echo json_encode( $this->toUtf8( $this->response ) );
        return false;
    }

    public function toUtf8($data)
    {
        if(!is_array($data))
            return mb_convert_encoding( $data , 'UTF-8' , 'UTF-8 , ISO-8859-1' );

        $return = array();

        foreach ($data as $i => $v)
            $return[$this->toUtf8($i)] = $this->toUtf8($v);

        return $return;
    }


}

$grid = new Grid($_POST, $_GET );
$grid->Execute();