<?php

namespace App\Http\Controllers\Api\Danoral;

use App\Danoral\Danoral\sysOLD\PreFacturacionView;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Danoral\Entidades\EntidadView;
use App\Http\Resources\DanoralCliente as DanoralClienteResource;
use App\Http\Resources\DanoralFacturasJSys;
use App\Http\Resources\EntidadEdif;
use App\Danoral\Entidades\Entidad;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\JoinClause;

class DanoralController extends Controller
{
    public static function getClientes(){
        $entidades =  EntidadView::where([
            ['datose->cod->value', '!=', ''],
            ['typeE', 2],
            ['enableE', true],
        ])
        ->whereNotNull('datose->cod->value')
        ->orderBy(DB::raw("FORMAT('%10s', \"datose\"->'cod'->>'value')::bytea"), 'asc')
        ->get();

        return response()->json(DanoralClienteResource::collection($entidades), 200);
    }

    public static function getFacturas(Request $request){

        $ano        = $request->ano;
        $mes        = $request->mes;
        $isDanoralE = isset($request->isDanoralE)?$request->isDanoralE:0;
        $isPre      = isset($request->isPre)?$request->isPre:0;

        $conexion_danoral = (new Entidad)->getCN_jsys($ano, 1);


        if($isPre) {
            $e_edif = DB::connection($conexion_danoral)
            ->table('entidades')
            ->leftJoin('entidadesedif', 'entidades.id_Entidad', '=', 'entidadesedif.id_Entidad')
            ->where([
                ['id_TipoEntidad', 2],
                ['cod_Entidad', '<>', ''],
                ['activo_Entidad', 1],
                ['padreEdif_EE', 0],
                //['id_EntidadFamilia', ($isDanoralE?'=':'<>'), 401],
            ])
            ->orderBy('cod_Entidad', 'asc')
            ->get();

            /*

            $facturas = DB::connection($conexion_danoral)
                ->table('entidades AS e')
                ->select([
                    'e.id_Entidad',
                    'e.cod_Entidad',
                    'e.id_EntidadFamilia',
                    'e.nombre_Entidad',
                    'c.ano_Cond',
                    'c.mes_Cond',
                    'c.porcIVA',
                    DB::raw('SUM(IF(dt.tipoDT = 26, dt.montoDT, 0))/((c.porcIVA + 100)/100) AS gtosAdmin'),
                    DB::raw('SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))/((c.porcIVA + 100)/100) AS gtosCobr'),             
                    DB::raw('(SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) / ((c.porcIVA + 100)/100) AS base'),             
                    DB::raw('((SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) * (c.porcIVA/100)) / ((c.porcIVA+100)/100) AS IVA'),             
                    DB::raw('(SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) as montoTotal'),             
                    DB::raw('COUNT(dt.montoDT) as countMontoDT')
                ])
                ->join('entidadesedif AS ee', 'ee.id_Entidad', '=', 'e.id_Entidad')
                ->join('condominios AS c', 'c.id_Entidad', '=', 'e.id_Entidad')
                ->join('itemsdt AS dt', function(JoinClause  $join)
                {
                    $join->on('dt.anoDT', '=', 'c.ano_Cond');
                    $join->on('dt.mesDT', '=', 'c.mes_Cond');
                })
                ->where([
                    ['c.ano_Cond', $ano],
                    ['c.mes_Cond', $mes],
                    ['dt.ctaDT', 20444],
                    ['e.id_TipoEntidad', 2],
                    ['e.activo_Entidad', true],                    
                ])
                ->get();
                */

            $facturasArr = DB::connection($conexion_danoral)
            ->select("
                SELECT
                    e.id_Entidad,
                    e.cod_Entidad,
                    e.id_EntidadFamilia,
                    e.nombre_Entidad,
                    c.ano_Cond,
                    c.mes_Cond,
                    c.porcIVA,
                    SUM(IF(dt.tipoDT = 26, dt.montoDT, 0))/((c.porcIVA + 100)/100) AS gtosAdmin,
                    SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))/((c.porcIVA + 100)/100) AS gtosCobr,
                    (SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) / ((c.porcIVA + 100)/100) AS base,
                    ((SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) * (c.porcIVA/100)) / ((c.porcIVA+100)/100) AS IVA,
                    (SUM(IF(dt.tipoDT = 26, dt.montoDT, 0)) + SUM(IF(dt.tipoDT = 27, dt.montoDT, 0))) as montoTotal,
                    COUNT(dt.montoDT) as countMontoDT
                FROM itemsdt dt, entidades e, entidadesedif ee, condominios c 
                WHERE dt.ctaDT = 20444
                    AND ano_Cond = $ano
                    AND mes_Cond = $mes
                    AND e.id_Entidad = ee.id_Entidad 
                    AND c.ano_Cond = dt.anoDT 
                    AND c.mes_Cond = dt.mesDT 
                    AND c.id_Entidad = e.id_Entidad 
                    AND e.id_TipoEntidad = 2
                    AND e.activo_Entidad = true
                    AND ee.padreEdif_EE = false
                    AND (dt.id_Entidad IN (
                    SELECT eSub.id_Entidad
                        FROM entidades AS eSub
                            LEFT JOIN entidades AS eSub1 ON (eSub.id_EntidadPadre = eSub1.id_Entidad)
                            LEFT JOIN entidades AS eSub2 ON (eSub1.id_EntidadPadre = eSub2.id_Entidad)
                            LEFT JOIN entidades AS eSub3 ON (eSub2.id_EntidadPadre = eSub3.id_Entidad)
                            LEFT JOIN entidades AS eSub4 ON (eSub3.id_EntidadPadre = eSub4.id_Entidad)
                        WHERE true
                            AND (eSub.id_EntidadPadre = e.id_Entidad 
                            OR   eSub1.id_EntidadPadre = e.id_Entidad
                            OR   eSub2.id_EntidadPadre = e.id_Entidad
                            OR   eSub3.id_EntidadPadre = e.id_Entidad
                            OR   eSub4.id_EntidadPadre = e.id_Entidad))
                            OR (dt.id_Entidad = e.id_Entidad AND ee.hono_noComun_EE = false )
                    )
                GROUP BY e.id_EntidadFamilia, e.id_Entidad, c.ano_Cond, c.mes_Cond, e.cod_Entidad, e.nombre_Entidad, c.porcIVA 
                ORDER BY e.id_EntidadFamilia, e.cod_Entidad
            ");


            /*
            $facturas = PreFacturacionView::where([
                ['ano_Cond', $ano],
                ['mes_Cond', $mes],
                //['id_EntidadFamilia', ($isDanoralE?'=':'<>'), 401],
            ])
            ->orderBy('cod_Entidad', 'asc')
            ->get();
            */

            $facturas = collect($facturasArr);
            $facturasErr = collect();
            $totalGtosAdmin = 0;
            $totalGtosCobranza = 0;
            $totalBase = 0;
            $totalIVA = 0;
            $totalMonto = 0;
            foreach($e_edif as $e) {
                $findF = $facturas->firstWhere('id_Entidad', $e->id_Entidad);

                if($findF) {
                    $totalGtosAdmin     += $findF->gtosAdmin;
                    $totalGtosCobranza  += $findF->gtosCobr;
                    $totalBase          += $findF->base;
                    $totalIVA           += $findF->IVA;
                    $totalMonto         += $findF->montoTotal;
                    if($findF->gtosAdmin >= -1 && $findF->gtosAdmin <= 1) {
                        $facturas->forget($findF);
                        $findF->errNro = 1;
                        $facturas->prepend($findF);
                    }
                }else{
                    $fErr = new PreFacturacionView;
                    $fErr->id_Entidad       = $e->id_Entidad;
                    $fErr->cod_Entidad      = $e->cod_Entidad;
                    $fErr->nombre_Entidad   = $e->nombre_Entidad;
                    $fErr->ano_Cond         = 0;
                    $fErr->mes_Cond         = 0;
                    $fErr->gtosAdmin        = 0;
                    $fErr->gtosCobr         = 0;
                    $fErr->base             = 0;
                    $fErr->porcIVA          = 0;
                    $fErr->IVA              = 0;
                    $fErr->montoTotal       = 0;
                    $fErr->errNro           = 2;

                    $facturasErr->push($fErr);
                }

                
            }
            //$resF = $facturasErr->merge($facturas);

            return response()->json([
                'facturas'          => DanoralFacturasJSys::collection($facturas),
                'facturasErr'       => DanoralFacturasJSys::collection($facturasErr),
                'totalGtosAdmin'    => number_format($totalGtosAdmin, 2, ',', '.'),
                'totalGtosCobranza' => number_format($totalGtosCobranza, 2, ',', '.'),
                'totalBase'         => number_format($totalBase, 2, ',', '.'),
                'totalIVA'          => number_format($totalIVA, 2, ',', '.'),
                'totalMonto'        => number_format($totalMonto, 2, ',', '.'),
                'countF'            => number_format(count($facturas), 0, ',', '.'),
            ],200);

        }

        
    }
}
