<?php

namespace App\Http\Controllers;

use App\Models\ManoObraTerceros;
use App\Models\InformacionManoObra;
use App\Models\InformacionFinanciera;
use App\Models\DistribucionIfmCostosCuentas;
use App\Models\DistribucionIfmCostosBases;
use App\Models\DistribucionValorDirecto;
use App\Models\AvanceModulos;
use Illuminate\Http\Request;

class ManoObraTercerosController extends Controller
{
    public function consolidarManoObraTerceros(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('mot_ano');
        $mes = $request->input('mot_mes');

        \DB::transaction(function() use($contrato, $institucion, $ano, $mes) {
            // Borrar informacion procesada
            $ifm = \DB::select("select ifm_pk_id
                                from costos_principal.informacion_financiera
                                join costos_principal.cuentas_contables on (cuc_pk_id = cuc_fk_id)
                                where informacion_financiera.con_fk_id = ".$contrato." and
                                      informacion_financiera.ins_fk_id = '".$institucion."' and
                                      ifm_ano = ".$ano." and 
                                      ifm_mes = ".$mes." and
                                      ifm_nit_tercero in (
                                          select mot_nit_tercero
                                          from costos_principal.mano_obra_terceros
                                          where con_fk_id = ".$contrato." and
                                                ins_fk_id = '".$institucion."' and
                                                mot_ano = ".$ano." and 
                                                mot_mes = ".$mes."
                                      ) and
                                      elm_fk_id = 3");

            if (count($ifm) > 0) {
                $movimientos = "";

                foreach ($ifm as $registro) {
                    $movimientos .= $registro->ifm_pk_id.",";
                }

                $movimientos[strlen($movimientos) - 1] = " ";

                \DB::select('update costos_principal.informacion_financiera
                            set ifm_asignado = false
                            where ifm_pk_id in ('.$movimientos.')');

                \DB::select('delete from costos_principal.distribucion_ifm_costos_cuentas
                            where ifm_fk_id in ('.$movimientos.')');

                \DB::select('delete from costos_principal.distribucion_ifm_costos_bases
                            where ifm_fk_id in ('.$movimientos.')');
            }

            ManoObraTerceros::on('costos_principal')
                            ->where('con_fk_id', $contrato)
                            ->where('ins_fk_id', $institucion)
                            ->where('mot_ano', $ano)
                            ->where('mot_mes', $mes)
                            ->delete();

            \DB::select("delete from costos_principal.informacion_bases_distribucion
                         where bdi_fk_id in (
                                   select bdi_pk_id
                                   from costos_principal.bases_distribucion
                                   where con_fk_id = ".$contrato." and 
                                         ins_fk_id = '".$institucion."' and
                                         bdi_ano = ".$ano." and
                                         bdi_mano_obra = true
                               ) and
                               con_fk_id = ".$contrato." and 
                               ins_fk_id = '".$institucion."' and
                               ibd_ano = ".$ano." and 
                               ibd_mes = ".$mes);

            \DB::select("delete from costos_principal.informacion_bases_distribucion2
                        where bdi_fk_id in (
                                  select bdi_pk_id
                                  from costos_principal.bases_distribucion
                                  where con_fk_id = ".$contrato." and 
                                        ins_fk_id = '".$institucion."' and
                                        bdi_ano = ".$ano." and
                                        bdi_mano_obra = true
                              ) and 
                              con_fk_id = ".$contrato." and 
                              ins_fk_id = '".$institucion."' and
                              ibd2_ano = ".$ano." and 
                              ibd2_mes = ".$mes);


            // Consolidar la nueva informacion
            \DB::select("insert into costos_principal.mano_obra_terceros (con_fk_id, ins_fk_id, mot_ano, mot_mes, mot_nit_tercero,
                                                                          mot_razon_social, mot_costo)
                        select ".$contrato.",
                               '".$institucion."',
                               ".$ano.",
                               ".$mes.",
                               ifm_nit_tercero,
                               ifm_razon_social,
                               sum(ifm_valor)
                        from costos_principal.informacion_financiera as ifm
                        join costos_principal.cuentas_contables as cuc on (cuc.cuc_pk_id = ifm.cuc_fk_id)
                        join costos_principal.elementos as elm on (elm.elm_pk_id = cuc.elm_fk_id)
                        where ifm.con_fk_id = ".$contrato." and
                              ifm.ins_fk_id = '".$institucion."' and
                              ifm_ano = ".$ano." and
                              ifm_mes = ".$mes." and
                              ifm_mano_obra = true
                        group by 1, 2, 3, 4, 5, 6");
        });

        AvanceModulos::on('costos_principal')
                     ->where('con_fk_id', $contrato)
                     ->where('ins_fk_id', $institucion)
                     ->where('avm_ano', $ano)
                     ->where('avm_mes', $mes)
        ->update(
            [
                "avm_info_mano_obra" => false
            ]
        );

        return array("response" => 0);
    }

    public function getManoObraTerceros(Request $request) {
        return ManoObraTerceros::on('costos_principal')
                               ->where('con_fk_id', $request->input('con_fk_id'))
                               ->where('ins_fk_id', $request->input('ins_fk_id'))
                               ->where('mot_ano', $request->input('mot_ano'))
                               ->where('mot_mes', $request->input('mot_mes'))
                               ->get()->toArray();
    }

    public function devolverMovimiento(Request $request) {
        \DB::transaction(function() use($request) {
            $con_fk_id = $request->input('con_fk_id');
            $ins_fk_id = $request->input('ins_fk_id');
            $mot_ano = $request->input('mot_ano');
            $mot_mes = $request->input('mot_mes');
            $mot_pk_id = $request->input('mot_pk_id');

            $tercero = ManoObraTerceros::on('costos_principal')
                                       ->where('con_fk_id', $con_fk_id)
                                       ->where('ins_fk_id', $ins_fk_id)
                                       ->where('mot_ano', $mot_ano)
                                       ->where('mot_mes', $mot_mes)
                                       ->where('mot_pk_id', $mot_pk_id);

            // Incluir en informacion financiera y borrar asignaciones
            $movimientos = InformacionFinanciera::on('costos_principal')
                                                ->join('cuentas_contables', 'cuc_pk_id', 'cuc_fk_id')
                                                ->where('informacion_financiera.con_fk_id', $con_fk_id)
                                                ->where('informacion_financiera.ins_fk_id', $ins_fk_id)
                                                ->where('ifm_ano', $mot_ano)
                                                ->where('ifm_mes', $mot_mes)
                                                ->where('elm_fk_id', 3)
                                                ->where('ifm_nit_tercero', $tercero->get()->toArray()[0]["mot_nit_tercero"])
                                                ->where('ifm_razon_social', $tercero->get()->toArray()[0]["mot_razon_social"]);
            $movimientos->update(
                [
                    "ifm_mano_obra" => false,
                    "ifm_preasignado" => false,
                    "ifm_asignado" => false
                ]
            );

            $movimientos = InformacionFinanciera::on('costos_principal')
                                                ->join('cuentas_contables', 'cuc_pk_id', 'cuc_fk_id')
                                                ->where('informacion_financiera.con_fk_id', $con_fk_id)
                                                ->where('informacion_financiera.ins_fk_id', $ins_fk_id)
                                                ->where('ifm_ano', $mot_ano)
                                                ->where('ifm_mes', $mot_mes)
                                                ->where('elm_fk_id', 3)
                                                ->where('ifm_nit_tercero', $tercero->get()->toArray()[0]["mot_nit_tercero"])
                                                ->where('ifm_razon_social', $tercero->get()->toArray()[0]["mot_razon_social"])
                                                ->get()->toArray();

            // Borrar las posibles asignaciones
            foreach($movimientos as $ifm) {
                DistribucionIfmCostosCuentas::on('costos_principal')
                                            ->where('con_fk_id', $con_fk_id)
                                            ->where('ins_fk_id', $ins_fk_id)
                                            ->where('dcu_ano', $mot_ano)
                                            ->where('dcu_mes', $mot_mes)
                                            ->where('ifm_fk_id', $ifm['ifm_pk_id'])
                                            ->delete();
            }

            foreach($movimientos as $ifm) {
                DistribucionIfmCostosBases::on('costos_principal')
                                          ->where('con_fk_id', $con_fk_id)
                                          ->where('ins_fk_id', $ins_fk_id)
                                          ->where('dib_ano', $mot_ano)
                                          ->where('dib_mes', $mot_mes)
                                          ->where('ifm_fk_id', $ifm['ifm_pk_id'])
                                          ->delete();
            }

            foreach($movimientos as $ifm) {
                DistribucionValorDirecto::on('costos_principal')
                                        ->where('con_fk_id', $con_fk_id)
                                        ->where('ins_fk_id', $ins_fk_id)
                                        ->where('dvd_ano', $mot_ano)
                                        ->where('dvd_mes', $mot_mes)
                                        ->where('ifm_fk_id', $ifm['ifm_pk_id'])
                                        ->delete();
            }

            // Quitar el tercero del consolidado de terceros
            $tercero->delete();

            // Quitar el avance del modulo de informacion de informacion financiera
            AvanceModulos::on('costos_principal')
                        ->where('con_fk_id', $con_fk_id)
                        ->where('ins_fk_id', $ins_fk_id)
                        ->where('avm_ano', $mot_ano)
                        ->where('avm_mes', $mot_mes)
            ->update(
                [
                    "avm_inf_financiera" => false
                ]
            );

            $this->verificarAvance($con_fk_id, $ins_fk_id, $mot_ano, $mot_mes);
        });
    }

    public function verificarAvance($contrato, $institucion, $ano, $mes) {
        // Verificar si existe registro del avance
        $conteo = AvanceModulos::on('costos_principal')
                               ->where('con_fk_id', $contrato)
                               ->where('ins_fk_id', $institucion)
                               ->where('avm_ano', $ano)
                               ->where('avm_mes', $mes)
                               ->count();

        // Verificar el avance del modulo
        $imo = InformacionManoObra::on('costos_principal')
                                  ->where('con_fk_id', $contrato)
                                  ->where('ins_fk_id', $institucion)
                                  ->where('imo_ano', $ano)
                                  ->where('imo_mes', $mes)
                                  ->whereNotNull('imo_valor')
                                  ->distinct('mot_fk_id')
                                  ->count('mot_fk_id');

        $mot = ManoObraTerceros::on('costos_principal')
                               ->where('con_fk_id', $contrato)
                               ->where('ins_fk_id', $institucion)
                               ->where('mot_ano', $ano)
                               ->where('mot_mes', $mes)
                               ->count();

        if ($conteo > 0) {
            if ($imo > 0 && $imo == $mot) {
                AvanceModulos::on('costos_principal')
                             ->where('con_fk_id', $contrato)
                             ->where('ins_fk_id', $institucion)
                             ->where('avm_ano', $ano)
                             ->where('avm_mes', $mes)
                ->update(
                    [
                        "avm_info_mano_obra" => true
                    ]
                );
            } else {
                AvanceModulos::on('costos_principal')
                             ->where('con_fk_id', $contrato)
                             ->where('ins_fk_id', $institucion)
                             ->where('avm_ano', $ano)
                             ->where('avm_mes', $mes)
                ->update(
                    [
                        "avm_info_mano_obra" => false
                    ]
                ); 
            }
        } else {
            if ($imo > 0 && $imo == $mot) {
                AvanceModulos::on('costos_principal')
                ->create(
                    [
                        "con_fk_id" => $contrato,
                        "ins_fk_id" => $institucion,
                        "avm_ano" => $ano,
                        "avm_mes" => $mes,
                        "avm_info_mano_obra" => true
                    ]
                );
            } else {
                AvanceModulos::on('costos_principal')
                ->create(
                    [
                        "con_fk_id" => $contrato,
                        "ins_fk_id" => $institucion,
                        "avm_ano" => $ano,
                        "avm_mes" => $mes,
                        "avm_info_mano_obra" => false
                    ]
                );
            }
        }          
    }
}
