<?php

namespace App\Danoral\Transacciones;

use App\Danoral\ModelBase;

class TransDTView extends ModelBase
{
    protected $tableName = 'transdt_view';
    protected $primaryKey = 'idDT';
    protected $keyType = 'bigint';
    public $incrementing = false;
    public $timestamps = false;

    function __construct($anoSchema = null) {
        if(is_null($anoSchema)){
            $this->table = parent::$SCHEMA .'.'. $this->tableName;
        }else{
            parent::getSchema_ano($anoSchema);
        }
    }

    public static function getTransDTView($input) {

        if(!isset($input['idE'])){
            return null;
        }
        $idE = $input['idE'];


        //inicializacion where
        $where = [ ['enableT', true] ];
        //ctaDT
        if(!empty($input['ctaDT'])){ $where[] = ['ctaDT', $input['ctaDT']]; }
        //refT
        if(!empty($input['refT'])){ $where[] = ['refT', $input['refT']]; }
        //dateD - dateH
        if(!empty($input['fechaDesde'])){
            $dateD = date("Y-m-d",strtotime($input['fechaDesde']));
            
            if(!empty($input['fechaHasta'])){
                $dateH = date("Y-m-d",strtotime($input['fechaHasta']));
                $where[] = ['dateT', '>=', $dateD];
                $where[] = ['dateT', '<=', $dateH];
            }else{
                $where[] = ['dateT', $dateD];
            }
        }
        //typeT
        $types = 'true';
        if(!empty($input['types'])){
            $types = '"typeT" IN('.implode(",",$input['types']).')';
        }

        $transDTv = TransDTView::where($where)
            ->where(function($query) use ($idE)  {
                $query->orWhere('idESuper', $idE)
                    ->orWhere('idE', $idE);
            })
            ->whereNotNull('idT')
            //->whereIn('typeT', $input['types'])
            ->whereRaw($types)
            ->orderBy('dateT', 'asc')
            ->orderBy('ctaDT', 'asc')
            ->orderBy('codE', 'asc')
            ->orderBy('anoDT', 'asc')
            ->orderBy('mesDT', 'asc')
            ->orderBy('typeDT', 'asc')
            ->get();

        return $transDTv;
    }

    public function getPeriodo(){
        if($this->mesDT && $this->anoDT){
            return ($this->mesDT<10?'0'.$this->mesDT: $this->mesDT) . '/' . $this->anoDT;
        }else{
            return '';
        }
    }



}

/*
SELECT dt."idT",
    t."ctaT",
    t."dateT",
    t."typeT",
    tt."nameTypeT",
    t."refT",
    t."descT",
    t."enableT",
    t."idU",
    (es."datose" -> 'cod'::text) ->> 'value'::text AS "codESuper",
    (e."datose" -> 'cod'::text) ->> 'value'::text AS "codE",
    es."idE" AS "idESuper",
    dt."idE",
    dt."dhDT",
    dt."idDT",
    dt."ctaDT",
    dt."typeDT",
    dt."anoDT",
    dt."mesDT",
    dt."montoDT",
    dt."idMoneda",
    dtd."descripcionDT",
    count(dtc."idDT_base") AS "countTcBase"
   FROM admin.transdt dt
     LEFT JOIN admin.trans t ON dt."idT" = t."idT"
     LEFT JOIN admin.transdt_des dtd ON dt."idDT" = dtd."idDT"
     LEFT JOIN admin.transdt_cambio dtc ON dt."idDT" = dtc."idDT_quote"
     LEFT JOIN admin.entidades e ON dt."idE" = e."idE"
     LEFT JOIN admin.entidades es ON e."idESuper" = es."idE"
     LEFT JOIN config.trans_types tt ON t."typeT" = tt."typeT"
  GROUP BY dt."idT", t."ctaT", 
t."dateT", 
t."typeT",
tt."nameTypeT", t."refT", t."descT", t."enableT", t."idU", 
((es."datose" -> 'cod'::text) ->> 'value'::text),
((e."datose" -> 'cod'::text) ->> 'value'::text),
es."idE", 
dt."idE", 
dt."dhDT", dt."idDT", dt."ctaDT", dt."typeDT", dt."anoDT", dt."mesDT", dt."montoDT", dt."idMoneda", dtd."descripcionDT"
  ORDER BY t."dateT";

  */
