<?php

include_once "Controller.php";

class AjaxController extends Controller {

    /**
     * @param $ui
     * @param array $_L
     * @param array $config
     * @param $_pd
     */
    public function __construct($ui, $_L, $config, $_pd) {
        parent::__construct($ui, $_L, $config, $_pd);
    }

    /**
     * @name patient
     */
    public function Associated ($tipo="Associated"){
         $d = ORM::for_table('crm_accounts')
                ->table_alias('c')
                ->select('c.*')
                ->join('crm_accounts_groups', array('c.id', '=', 'ag.crm_accounts_id'), 'ag')
                ->join('crm_groups', array('g.id', '=', 'ag.crm_group_id'), 'g')
                ->where('g.gname', $tipo)
                ->where('g.default', 1);
                $term =_post('term');
                
        $d->where_any_is(array(array("c.account" => '%' . $term . '%')
                ), 'like');
  
                 
          $crms= $d->find_many();

        $data = array();
        foreach ($crms as $c) {
            $data[] = array(
                'id' => $c->id,
                'name'=>   $c->account,
                'text'=>$c->account,
                'cpf_cnpj' => $c->cpf_cnpj,
                'account' => $c->account,
                'company' => $c->company,
                'kinf_of_person' => $c->kind_of_person,
                'foreign_doc_identification' => $c->foreign_doc_identification,
            );
        }

        echo json_encode($data);
    }
    
    
    public function accounts() {
        $contacts = ORM::for_table('crm_accounts')
                        ->table_alias('c')
                        ->select('c.*')
                        ->left_outer_join('crm_accounts_groups', array('c.id', '=', 'g.crm_accounts_id'), 'g')
//            ->where('g.crm_group_id', 5)
                        ->order_by_asc('account')->find_many();

        foreach ($contacts as $c) {
            $data[] = array(
                'id' => $c->id,
                'name' => $c->account,
            );
        }
        echo json_encode($data);
    }

    public function species() {
        $term = _post('term');
        $specie = ORM::for_table('module_fish_species')
                ->table_alias('s');
        $specie->where_any_is(array(array("s.popular_name" => '%' . $term . '%')
            , array("s.scientific_name" => '%' . $term . '%')
                ), 'like');
//         $specie  ->select(array('s.popular_name', 's.id','s.scientific_name'))
        $especies = $specie->find_many();
        $data = array();
        foreach ($especies as $p) {
            $data[] = array(
                'id' => $p->id,
                'text' => $p->popular_name . " - " . $p->scientific_name
            );
        }
        echo json_encode($data);
    }

    public function tankAssociated() {
        $term = _post('term');
        $notIN = _post('notin');
        if (isset($this->routes[3])) {
            $crmn_accounts_id = $this->routes[3];
        }else{
             $crmn_accounts_id='';
        }
        if(!empty(_post('crm_accounts_id'))){
            $crmn_accounts_id=_post('crm_accounts_id');
        }
        

        $tank = ORM::for_table('module_fish_tank')
                ->table_alias('s');
        $tank->where_any_is(array(array("s.title_tank" => '%' . $term . '%')
                ), 'like');
        //         $specie  ->select(array('s.popular_name', 's.id','s.scientific_name'))
        $tank->where_raw('(s.crm_accounts_id=' . intval($crmn_accounts_id) . " or s.crm_accounts_id IS NULL)");
        $tank->where_null('deleted_at');
        //adiciona a regra dos not in, para nao replicar a busca para o select
        $tank->where_not_in('s.id', explode(',',$notIN));
        $tankS = $tank->find_many();
        $data = array();
        foreach ($tankS as $p) {
            $data[] = array(
                'id' => $p->id,
                'text' => $p->title_tank
            );
        }
        echo json_encode($data);
    }
    
        public function tankLots() {
        $term = _post('term');
        $notIN = _post('notin');
        if (isset($this->routes[3])) {
            $lots = $this->routes[3];
        }else{
             $lots='';
        }
        $data = array();
        if(!empty($lots)){
            $tank = ORM::for_table('module_fish_tank')
                    ->table_alias('s');
            $tank->where_any_is(array(array("s.title_tank" => '%' . $term . '%')
                    ), 'like');
            $tank->where_raw('s.id IN (select sub.module_fish_tank_id from module_fish_lots_distribution sub where sub.module_fish_lots_id=' . intval($lots) . ")");
            $tank->where_null('s.deleted_at');
            //adiciona a regra dos not in, para nao replicar a busca para o select
            if(!empty($notIN)){
                $tank->where_not_in('s.id', explode(',',$notIN));
            }
            $tankS = $tank->find_many();

            foreach ($tankS as $p) {
                $data[] = array(
                    'id' => $p->id,
                    'text' => $p->title_tank
                );
            }
        }
        echo json_encode($data);
    }

    
    //listagem de program Associated
    public function program() {
        $term = _post('term');
        $program = ORM::for_table('module_fish_food_programs')
                ->table_alias('s');
        $program->where_any_is(array(array("s.name" => '%' . $term . '%')
                ), 'like');
        $program->where_null('deleted_at');
        $programS = $program->find_many();
        $data = array();
        foreach ($programS as $p) {
            $data[] = array(
                'id' => $p->id,
                'text' => $p->name
            );
        }
        echo json_encode($data);
    }
        //listagem de program Associated
    public function programAssociated() {
        $term = _post('term');
        if (isset($this->routes[3])) {
            $crmn_accounts_id = $this->routes[3];
        }else{
             $crmn_accounts_id='';
        }
//        if(!empty(_post('crm_accounts_id'))){
//            $crmn_accounts_id=_post('crm_accounts_id');
//        }
        
        $program = ORM::for_table('module_fish_food_programs')
                ->table_alias('s');
        $program->where_any_is(array(array("s.name" => '%' . $term . '%')
                ), 'like');
        if(!empty($crmn_accounts_id)){
            $program->where("crm_accounts_id",$crmn_accounts_id);
        }
        $program->where_null('deleted_at');
        $programS = $program->find_many();
        $data = array();
        foreach ($programS as $p) {
            $data[] = array(
                'id' => $p->id,
                'text' => $p->name
            );
        }
        echo json_encode($data);
    }

     //listagem de program Associated
    public function ration() {
        $term = _post('term');

        $d = ORM::for_table('sys_items')
            ->table_alias('i')
            ->select('i.*')
            ->left_outer_join('sys_items_category', array('is.id', '=', 'i.category_id'), 'is')
            ->where("is.name", 'Ração')
            ->order_by_asc('i.description');
        
           $d->where_any_is(array(array("i.description" => '%' . $term . '%'),
               array("i.id" => '%' . $term . '%')
                ), 'like');
        $rations=$d->find_many();
        $data = array();
        foreach ($rations as $p) {
            $data[] = array(
                'id' => $p->id,
                'text' =>"(".$p->id.") " .$p->description
            );
        }
        echo json_encode($data);
    }
       //listagem de program Associated
    public function lotsAssociated() {
        $term = _post('term');
        if (isset($this->routes[3])) {
            $crmn_accounts_id = $this->routes[3];
        }else{
             $crmn_accounts_id='';
        }
        
        $d = ORM::for_table('module_fish_lots')
            ->table_alias('l')
            ->select('l.*')
    //            ->select('t.title_tank')
            ->left_outer_join('module_fish_lots_distribution', array('ld.module_fish_lots_id', '=', 'l.id'), 'ld')
//            ->left_outer_join('module_fish_tank', array('t.id', '=', 'ld.module_fish_tank_id'), 't')
            ->order_by_asc('l.title_lote');
        
           $d->where_any_is(array(
               array("l.title_lote" => '%' . $term . '%')
                ), 'like');
           
        if(!empty($crmn_accounts_id)){
            $d->where('crm_accounts_id',$crmn_accounts_id);
        } 
        $d->where_raw(" ld.deleted_at IS NULL  AND l.deleted_at IS NULL ");

              
        $lots=$d->find_many();
        $data = array();
        if(count($lots)>0){
            if (!isset($this->routes[4])) {
            $data[] = array(
                    'id' => '0',
                    'text' =>$this->_L['Select']
                );
            }
            foreach ($lots as $p) {
                $data[$p->id] = array(
                    'id' => $p->id,
                    'text' =>$p->title_lote
                );
            }
        }
        echo json_encode($data);
    }
   
    public function lotsTankAssociated() {
        $term = _post('term');
        if (isset($this->routes[3])) {
            $crmn_accounts_id = $this->routes[3];
        }else{
             $crmn_accounts_id='';
        }
        
    $d = ORM::for_table('module_fish_lots')
            ->table_alias('l')
            ->select('l.*')
            ->select('t.title_tank')
            ->select('lp.module_fish_program_id')
                ->select_expr('ld.id as lote_id')
            ->join('module_fish_lots_distribution', array('ld.module_fish_lots_id', '=', 'l.id'), 'ld')
            ->join('module_fish_tank', array('t.id', '=', 'ld.module_fish_tank_id'), 't')
            ->join('module_fish_lots_distribution_program', array('lp.module_fish_lots_distribution_id', '=', 'ld.id'), 'lp')
            ->order_by_expr('concat(l.title_lote,t.title_tank) ASC');
            $d->where_raw(" ld.deleted_at IS NULL  AND l.deleted_at IS NULL ");

           $d->where_any_is(array(
               array("l.title_lote" => '%' . $term . '%'),
                array("t.title_tank" => '%' . $term . '%')
                ), 'like');
           
        if(!empty($crmn_accounts_id)){
            $d->where('l.crm_accounts_id',$crmn_accounts_id);
        } 
           
        $lots=$d->find_many();
        $data = array();
        foreach ($lots as $p) {
            $data[] = array(
                'id' => $p->lote_id,
                'text' =>$p->title_lote ." - ".$p->title_tank 
            );
        }
        echo json_encode($data);
    }
        
}