<?php

class Cobranzas_model extends Databases { //OPERACIONES

    private $db = DB_NAME_HEAD;
    protected $token = false;

    public function obtenerCobranzas($nroOperacion = FALSE, $nroCliente = FALSE) {
        $where = '';
        if ($nroOperacion) {
            $where .= "ID_OPERACION=$nroOperacion";
        }
        if ($nroCliente) {
            if ($where) {
                $where .= " AND ";
            }
            $where .= "ID_CLIENTE=$nroCliente";
        }
        if (!$where) {
            return FALSE;
        }
        
        $sql = "SELECT o.EMP, o.TIP, o.TCO, o.SUC, o.NCO, o.id_operacion, o.id_cliente FROM " . $this->db . ".dbo.a_operaciones o "
                . "join " . $this->db . ".dbo.ACOBYPAG_CUOTAS a on o.emp=a.emp and o.tip=a.tip and o.TCO=a.TCO and o.SUC=a.suc and o.nco=a.nco where $where "
                . "GROUP BY o.EMP, o.TIP, o.TCO, o.SUC, o.NCO, o.id_operacion, o.id_cliente";
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            foreach ($data  as $k => $dt) {
                $sql = "SELECT COUNT(*) AS CUOTAS, SUM(CASE WHEN SAL<IPA THEN 1 ELSE 0 END) AS CUOTAS_PEND, SUM(IPA) AS TOTAL, SUM(SAL) AS PAGADO FROM " . $this->db . ".dbo.ACOBYPAG_CUOTAS "
                        . "WHERE emp={$dt['EMP']} and tip='{$dt['TIP']}' and TCO='{$dt['TCO']}' and SUC={$dt['SUC']} and nco={$dt['NCO']}";
                $data2 = parent::query($sql);
                $data[$k] = array_merge($data[$k], $data2[0]);
            }
            
            return $data;
        } else
            return FALSE;
        
        $sql = "SELECT o.EMP, o.TIP, o.TCO, o.SUC, o.NCO, o.id_operacion, o.id_cliente, a.ncu, a.IPA, a.SAL FROM " . $this->db . ".dbo.a_operaciones o
                join " . $this->db . ".dbo.ACOBYPAG_CUOTAS a on o.emp=a.emp and o.tip=a.tip and o.TCO=a.TCO and o.SUC=a.suc and o.nco=a.nco where $where";
        
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            return $data;
        } else
            return FALSE;
    }

    public function actualizarCobranzas($nroCliente, $nroOperacion, $importe, $data_pos) {
        
        $sql = "SELECT * FROM " . $this->db . ".dbo.a_operaciones WHERE ID_OPERACION=$nroOperacion AND ID_CLIENTE=$nroCliente AND TCO='PR'";
        
        $operacion = parent::query($sql);
        if ($operacion && count($operacion) > 0) {
            $emp = $operacion[0]['EMP'];
            $tip = $operacion[0]['TIP'];
            $tco = $operacion[0]['TCO'];
            $suc = $operacion[0]['SUC'];
            $nco = $operacion[0]['NCO'];
            
            $sql = "SELECT IPA, SAL, NCU  FROM " . $this->db . ".dbo.acobypag_cuotas WHERE EMP=$emp and TIP='$tip' and TCO='$tco' and SUC=$suc and NCO=$nco AND IPA > SAL ORDER BY NCU ASC";
            $cuotas = parent::query($sql);
            if ($operacion && count($operacion) > 0) {
                $sql = "SELECT ISNULL(MAX(ID), 0) AS ID_COB FROM " . $this->db . ".dbo.a_cobranzas";
                $id_cob = parent::query($sql);
                $id_cob = $id_cob[0]['ID_COB'] + 1;
                
                $saldo = $importe;
                $fecha = date('Ymd H:i');
                foreach ($cuotas as $cuota) {
                    $ncu = $cuota['NCU'];
                    if (($cuota['IPA'] - $cuota['SAL'])  > $saldo) {
                        $importe = $saldo;
                    } else {
                        $importe = $cuota['IPA'] - $cuota['SAL'];
                    }
                    $saldo -= $importe;
                    
                    
                    $sql = "UPDATE " . $this->db . ".dbo.acobypag_cuotas SET fer='$fecha', sal=(sal + $importe) WHERE EMP=$emp and TIP='$tip' and TCO='$tco' and SUC=$suc and NCO=$nco AND NCU=$ncu";
                    parent::query($sql);
                    
                    $arr_cobranza = array(
                        'ID' => $id_cob,
                        'EMP' => $data_pos['EMP'],
                        'ID_CLIENTE' => $nroCliente,
                        'ID_OPERACION' => $nroOperacion,
                        'FECHA' => $fecha,
                        'CUOTA' => $cuota['NCU'],
                        'IMPORTE' => $importe,
                        'ESTADO' => 0,
                        'ID_CAJERO' => $data_pos['COD_VEN'],
                        'PLA' => $data_pos['PLA'],
                        'LUG' => $data_pos['LUG']
                    );
                    
                    parent::insert($this->db . ".dbo.a_cobranzas", $arr_cobranza);
                    
                    if (!$saldo) {
                        break;
                    }
                    ++$id_cob;
                }
                
                return TRUE;
            }
        }

        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            return $data;
        } else
            return false;
    }

    public function insertaPresu($algo, $algo2) {
        /*Busco el ultimo id_operacion en el HO, para que tenga ese numero en el BO*/
        $sql = "SELECT max(id_operacion) as ultimoId FROM " . $this->db . ".dbo.A_OPERACIONES";
        $data = parent::query($sql);
        $ultimo_id = $data[0]['ultimoId'] + 1;

        parent::init();
        parent::query("INSERT INTO " . $this->db . ".dbo.AMAEFACT_PRESU (BOE,CYV,EMP, TIP, TCO, SUC, NCO, NMF, COLA, CARTON, ERE, COVD, TOT, CLI, ID_DOMICILIO, CUOTAS_TOTALES, CAPITAL, INTERES, SALDO_A_VENCER, ID_EQUIPO) VALUES ('" . $algo['BOE'] . "','V','" . $algo['EMP'] . "', '" . $algo['TIP'] . "', '" . $algo['TCO'] . "', '" . $algo['SUC'] . "', '" . $algo['NCO'] . "', '" . $algo['NMF'] . "','" . $algo['COLA'] . "', '" . $algo['CARTON'] . "','" . $algo['ERE'] . "','" . $algo['COVD'] . "','" . $algo['TOT'] . "','" . $algo['CLI'] . "','" . $algo['ID_DOMICILIO'] . "','" . $algo['CUOTAS_TOTALES'] . "'," . $algo['CAPITAL'] . "," . $algo['INTERES'] . "," . $algo['SALDO_A_VENCER'] . "," . $algo['ID_EQUIPO'] . ")");
        parent::query("INSERT INTO " . $this->db . ".dbo.A_OPERACIONES (ID_OPERACION, EMP, ID_CLIENTE, TIP, TCO, SUC, NCO, FEC, E_HD, C_HD) VALUES ('" . $ultimo_id . "', '" . $algo2['EMP'] . "', '" . $algo2['ID_CLIENTE'] . "', '" . $algo2['TIP'] . "', '" . $algo2['TCO'] . "', '" . $algo2['SUC'] . "','" . $algo2['NCO'] . "', '" . $algo2['FEC'] . "','" . $algo2['EMP'] . "','S')");
        parent::commit();
        // Guardo las unificaciones
        if(isset($algo['UNIFICACIONES'])){
            $unificaciones = explode(',', $algo['UNIFICACIONES']);
            foreach($unificaciones as $unificacion){
                parent::init();
                $sql = "INSERT INTO " . $this->db . ".dbo.A_UNIFICAR (ID_OPERACION_ORIGEN, ID_OPERACION, EMP, TIP, TCO, SUC, NCO, FEC)";
                $sql .= " VALUES (".$unificacion.", 0, ".$algo['EMP'].", '".$algo['TIP']."', '".$algo['TCO']."', ".$algo['SUC'].", ".$algo['NCO'].", GETDATE())";
                parent::query($sql);
                parent::commit();
            }
        }
        return $ultimo_id;
    }

    public function insertaPresuTodos($presupuestos) {
        $arr_ret = array();
        
        
        foreach ($presupuestos as $pp) {
            parent::init();
            
            $sql = "SELECT TOP 1 id_operacion FROM " . $this->db . ".dbo.A_OPERACIONES WHERE EMP='" . $pp['EMP'] . "' AND ID_CLIENTE='" . $pp['CLI'] . "' AND TIP='" . $pp['TIP'] . "' AND TCO='" . $pp['TCO'] . "' AND SUC='" . $pp['SUC'] . "' AND NCO='" . $pp['NCO'] . "'";
            $ya_operado = parent::query($sql);
            
            if (isset($ya_operado[0])) {
                $ultimo_id = $ya_operado[0]['id_operacion'];
            } else {
                /*Busco el ultimo id_operacion en el HO, para que tenga ese numero en el BO*/
                $sql = "SELECT max(id_operacion) as ultimoId FROM " . $this->db . ".dbo.A_OPERACIONES";
                $data = parent::query($sql);
                $ultimo_id = $data[0]['ultimoId'] + 1;
            }
            
            $pp2 = array(
                "ID_OPERACION" => $ultimo_id,
                "EMP" => $pp['EMP'],
                "ID_CLIENTE" => $pp['CLI'],
                "TIP" => $pp['TIP'],
                "TCO" => $pp['TCO'],
                "SUC" => $pp['SUC'],
                "NCO" => $pp['NCO'],
                "FEC" => str_replace('-', '', $pp['FECHA_1C']),
                "E_HD" => "",
                "C_HD" => ""
            );
            
            if (!isset($ya_operado[0])) {
                if(isset($pp['UNIFICACIONES'])) {
                    $unificaciones = $pp['UNIFICACIONES'];
                    unset($pp['UNIFICACIONES']);
                } else {
                    $unificaciones = FALSE;
                }
                
                parent::query("INSERT INTO " . $this->db . ".dbo.AMAEFACT_PRESU (BOE,CYV,EMP, TIP, TCO, SUC, NCO, NMF, COLA, CARTON, ERE, COVD, TOT, CLI, ID_DOMICILIO, CUOTAS_TOTALES, CAPITAL, INTERES, SALDO_A_VENCER, ID_EQUIPO, PRIMER_PAGO, COMBO_POWER) VALUES ('" . $pp['BOE'] . "','V','" . $pp['EMP'] . "', '" . $pp['TIP'] . "', '" . $pp['TCO'] . "', '" . $pp['SUC'] . "', '" . $pp['NCO'] . "', '" . $pp['NMF'] . "','" . $pp['COLA'] . "', '" . $pp['CARTON'] . "','" . $pp['ERE'] . "','" . $pp['COVD'] . "','" . $pp['TOT'] . "','" . $pp['CLI'] . "','" . $pp['ID_DOMICILIO'] . "','" . $pp['CUOTAS_TOTALES'] . "'," . $pp['CAPITAL'] . "," . $pp['INTERES'] . "," . $pp['SALDO_A_VENCER'] . "," . $pp['ID_EQUIPO'] . "," . $pp['PRIMER_PAGO'] . "," . $pp['COMBO_POWER'] . ")");
                parent::query("INSERT INTO " . $this->db . ".dbo.A_OPERACIONES (ID_OPERACION, EMP, ID_CLIENTE, TIP, TCO, SUC, NCO, FEC, E_HD, C_HD) VALUES ('" . $pp2['ID_OPERACION'] . "', '" . $pp2['EMP'] . "', '" . $pp2['ID_CLIENTE'] . "', '" . $pp2['TIP'] . "', '" . $pp2['TCO'] . "', '" . $pp2['SUC'] . "','" . $pp2['NCO'] . "', '" . $pp2['FEC'] . "','" . $pp2['EMP'] . "','S')");
                
                if ($unificaciones) {
                    foreach ($unificaciones as $unificacion) {
                        $sql = "INSERT INTO " . $this->db . ".dbo.A_UNIFICAR (ID_OPERACION_ORIGEN, ID_OPERACION, EMP, TIP, TCO, SUC, NCO, FEC)";
                        $sql .= " VALUES (".$unificacion.", 0, ".$pp['EMP'].", '".$pp['TIP']."', '".$pp['TCO']."', ".$pp['SUC'].", ".$pp['NCO'].", GETDATE())";
                        parent::query($sql);
                    }
                }
            }
            parent::commit();

            $arr_ret[] = $pp2;
        }
        return $arr_ret;
    }

    public function BuscaPresusAprobados($tip, $tco, $nco, $suc) {
        $sql = "select a.* from " . $this->db . ".dbo.AMAEFACT_PRESU a
                JOIN " . $this->db . ".dbo.A_OPERACIONES ap ON a.tip=ap.tip and a.tco=ap.tco and a.nco=ap.nco and a.suc=ap.suc
                WHERE a.cola=2 and a.TIP='$tip' and a.tco='$tco' and a.NCO='$nco' and a.SUC=$suc";
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            return $data;
        } else
            return false;
    }

    public function actualizaPresuFact($tip, $tco, $nco, $suc) {
        $sql = "update " . $this->db . ".dbo.amaefact_presu set cola=3 where TIP='$tip' and TCO='$tco' and NCO=$nco and SUC=$suc";
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            return $data;
        } else
            return false;
    }

    public function actualizaPresuRem($tip, $tco, $nco, $suc, $cola) {
        $sql = "update " . $this->db . ".dbo.amaefact_presu set cola=$cola where TIP='$tip' and TCO='$tco' and NCO=$nco and SUC=$suc";
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            return $data;
        } else
            return false;
    }

    public function reservaCodHo() {
        $sql = "Exec " . $this->db . ".dbo.BUSCAR_ULT_NRO_DISPONIBLE_CLI @COD=0";
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            $cod = $data[0][''];
            parent::query("INSERT INTO " . $this->db . ".dbo.CLIENTES (COD,E_HD,C_HD) VALUES ('$cod','','S')");
            parent::query("INSERT INTO " . $this->db . ".dbo.CLI_DOMICILIO (ID_CLIENTE,ID,E_HD,C_HD) VALUES ('$cod','1','','S')");
            parent::query("Exec " . $this->db . ".dbo.ALTA_CLI_CUE @CLIENTE=$cod");
            parent::commit();
            parent::query("UPDATE " . $this->db . ".dbo.CLI_CUE SET C_HD='T' WHERE COD=" . $cod);
            return $data;
        } else {
            return false;
        }
    }

    public function getPrestaPendiente($cli, $emp, $tip, $tco, $suc, $nco) {
        $sql = "select count(*) as CANT_CUOTAS from " . $this->db . ".dbo.acobypag_cuotas where emp=$emp and cli=$cli and tip='$tip' and tco='$tco' and suc=$suc and nco=$nco and MOTIVO='ORI'";
        $data1 = parent::query($sql);
        $sql = "select * from " . $this->db . ".dbo.acobypag_cuotas where emp=$emp and cli=$cli and tip='$tip' and tco='$tco' and suc=$suc and nco=$nco AND MOTIVO='ORI'";
        $data2 = parent::query($sql);
        $sql = "select * from " . $this->db . ".dbo.acobypag_cuotas where emp=$emp and cli=$cli and tip='$tip' and tco='$tco' and suc=$suc and nco=$nco and rec<>0 AND MOTIVO='ORI'";
        $data3 = parent::query($sql);
        $sql = "select SUM(IPA) AS SALDO from " . $this->db . ".dbo.acobypag_cuotas where emp=$emp and cli=$cli and tip='$tip' and tco='$tco' and suc=$suc and nco=$nco and MOTIVO IN ('ORI', 'COB')";
        $data4 = parent::query($sql);
        $sql = "select ESTADO from " . $this->db . ".dbo.a_operaciones where emp=$emp and id_cliente=$cli and tip='$tip' and tco='$tco' and suc=$suc and nco=$nco";
        $data5 = parent::query($sql);
        if (($data1 && count($data1) > 0) && ($data2 && count($data2) > 0) && $data5[0]['ESTADO'] == '') {
            $data['CANT_CUOTAS'] = $data1[0]['CANT_CUOTAS'];
            $data['TOTAL_CUOTAS'] = $data2;
            $data['PAGADAS'] = $data3;
            $data['SALDO'] = $data4[0]['SALDO'];
            return $data;
        } else {
            return false;
        }
    }

    public function insertUpdatePersonaVeraz($array) {
        $sql = "select DOC from " . $this->db . ".dbo.veraz_consultas where DOC=" . $array['DOC'] . " AND EMP=". $array['EMP'];
        $data = parent::query($sql);
        if ($data && count($data) > 0) {
            $sql = "update " . $this->db . ".dbo.veraz_consultas set DOC='" . $array['DOC'] . "', SEXO='" . $array['SEXO'] . "',NOMBRE='" . $array['NOMBRE'] . "',CATEGORIA='" . $array['CATEGORIA'] . "',EXPLICACION='" . $array['EXPLICACION'] . "',SCORE_VERAZ='" . $array['SCORE_VERAZ'] . "',SCORE_POBLACION='" . $array['SCORE_POBLACION'] . "',INGRESO_NETO='" . $array['INGRESO_NETO'] . "',ANTIGUEDAD_LABORAL='" . $array['ANTIGUEDAD_LABORAL'] . "',CUOTA='" . $array['CUOTA'] . "',FECHA_NACIMIENTO='" . $array['FECHA_NACIMIENTO'] . "',RCI_VERAZ='" . $array['RCI_VERAZ'] . "',CUIT='" . $array['CUIT'] . "',ULTIMO_TRABAJO='" . $array['ULTIMO_TRABAJO'] . "',INFORMADO_HASTA='" . $array['INFORMADO_HASTA'] . "',INCOME_PREDICTOR='" . $array['INCOME_PREDICTOR'] . "',JOB_CATEGORY='" . $array['JOB_CATEGORY'] . "',MONTO_AUTORIZADO='" . $array['MONTO_AUTORIZADO'] . "',FECHA_CONSULTA='" . $array['FECHA_CONSULTA'] . "', OPE='" . $array['OPE'] . "' where DOC=" . $array['DOC'] . " AND EMP=". $array['EMP'];
            $data = parent::query($sql);
            return true;
        } else {
            parent::init();
            $sql = "insert into " . $this->db . ".dbo.veraz_consultas (DOC, SEXO, NOMBRE, CATEGORIA, EXPLICACION, SCORE_VERAZ, SCORE_POBLACION, INGRESO_NETO, ANTIGUEDAD_LABORAL, CUOTA, FECHA_NACIMIENTO, RCI_VERAZ, CUIT, ULTIMO_TRABAJO, INFORMADO_HASTA, INCOME_PREDICTOR, JOB_CATEGORY, MONTO_AUTORIZADO, FECHA_CONSULTA, EMP, OPE) values ('" . $array['DOC'] . "','" . $array['SEXO'] . "','" . $array['NOMBRE'] . "','" . $array['CATEGORIA'] . "','" . $array['EXPLICACION'] . "','" . $array['SCORE_VERAZ'] . "','" . $array['SCORE_POBLACION'] . "','" . $array['INGRESO_NETO'] . "','" . $array['ANTIGUEDAD_LABORAL'] . "','" . $array['CUOTA'] . "','" . $array['FECHA_NACIMIENTO'] . "','" . $array['RCI_VERAZ'] . "','" . $array['CUIT'] . "','" . $array['ULTIMO_TRABAJO'] . "','" . $array['INFORMADO_HASTA'] . "','" . $array['INCOME_PREDICTOR'] . "','" . $array['JOB_CATEGORY'] . "','" . $array['MONTO_AUTORIZADO'] . "','" . $array['FECHA_CONSULTA'] . "',".$array['EMP'].",".$array['OPE'].")";
            parent::query($sql);
            parent::commit();
            return TRUE;
        }
    }

    public function obtenerOperaciones($idCliente, $totalFinanciacion, $cantidadCuotas, $tipoFinanciacion) {
        $importeCuota = $totalFinanciacion / $cantidadCuotas;
        $query = "select *, .1 as interes from ( select id_operacion as id, sum(case when motivo = 'ori' then ipa else 0 end) as monto, sum(ipa) as saldo, ref," . "\r\n";
        $query .= " (select max(ref) from " . $this->db . ".dbo.acobypag_cuotas where emp = c.emp and tip = c.tip and tco = c.tco and suc = c.suc and nco = c.nco) as maxref," . "\r\n";
        $query .= " (select count(1) from " . $this->db . ".dbo.acobypag_cuotas where emp = c.emp and tip = c.tip and tco = c.tco and suc = c.suc and nco = c.nco" . "\r\n";
        $query .= " and ref = c.ref and motivo = 'ori') as ccu, pe.estado, (select max(ipa) from " . $this->db . ".dbo.acobypag_cuotas where emp = c.emp" . "\r\n";
        $query .= " and tip = c.tip and tco = c.tco and suc = c.suc and nco = c.nco and ref = c.ref and motivo = 'ori' and NCU != 1) as importe" . "\r\n";
        $query .= " from " . $this->db . ".dbo.acobypag_cuotas c inner join " . $this->db . ".dbo.a_operaciones o" . "\r\n";
        $query .= " on c.emp = o.emp and c.tip = o.tip and c.tco = o.tco and c.suc = o.suc and c.nco = o.nco and c.cli = " . $idCliente . "" . "\r\n";
        $query .= " inner join " . $this->db . ".dbo.amaefact_presu_ext pe on pe.emp = o.emp and pe.tip = o.tip and pe.tco = o.tco and pe.suc = o.suc and pe.nco = o.nco" . "\r\n";
        $query .= " inner join " . $this->db . ".dbo.amaefact_presu p on p.emp = o.emp and p.tip = o.tip and p.tco = o.tco and p.suc = o.suc and p.nco = o.nco" . "\r\n";
        // Debe coincidir el tipo de financiación
        $query .= " and p.boe = '".$tipoFinanciacion."'"."\r\n";
        $query .= " group by id_operacion, ref, c.emp, c.tip, c.tco, c.suc, c.nco, pe.estado ) a where ref = maxref";
        // Se debe haber cancelado al menos la mitad de la operación
        $query .= " and saldo > 0 and saldo <= monto / 2"."\r\n";
        // El nuevo importe de la cuota debe ser al menos un 25% mas que el importe de la cuota que se estaba pagando para la operación
        $query .= " and importe < ( importe + ".$importeCuota." ) / 1.25";
        $data = parent::query($query);
        return $data;
    }

}
