<?php
header('Content-Type: application/json');
if (isset($_REQUEST['codemp'])){$codemp=$_REQUEST['codemp'];}else{$codemp="";}
if($codemp=='ECOS'){
    $host = "66.70.255.14";  // Cambia esto por tu host
    $usuario = "apli_edu_ecos";    // Cambia esto por tu usuario
    $password = "ZC2KlhsjTqGi";   // Cambia esto por tu contraseña
    $dbname   = "apli_edu_ecos";  // Cambia esto por tu base de datos
}else{
    $host = "69.197.152.107";  // Cambia esto por tu host
    $usuario = "usufe";    // Cambia esto por tu usuario
    $password = "Naty@2012*";   // Cambia esto por tu contraseña
    $dbname   = "ecos_corp";  // Cambia esto por tu base de datos

}




try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $usuario, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    $anio = isset($_GET['anio']) ? intval($_GET['anio']) : date("Y");
    $mes = isset($_GET['mes']) ? intval($_GET['mes']) : null;

    // Consulta SQL
    $sql = "
        SELECT 
            nc.nriper AS id_empleado, 
            nc.nomper AS nombre_empleado,
            nc.vlrsal AS basico,
            DATE_FORMAT(nc.inicmp, '%Y-%m') AS periodo,
            (SELECT SUM(nc2.dialab) 
     FROM nomcmp nc2 
     WHERE nc2.nriper = nc.nriper 
     AND DATE_FORMAT(nc2.inicmp, '%Y-%m') = DATE_FORMAT(nc.inicmp, '%Y-%m')
     AND nc2.estcmp != 'ELIMINADO') AS dias_laborados,
            SUM(CASE WHEN np.codknp = '001' THEN np.devnom ELSE 0 END) AS salario,
            SUM(CASE WHEN np.codknp = '002' THEN np.devnom ELSE 0 END) AS subsidio_transporte,
            SUM(CASE WHEN kn.tipknp = 'D' AND kn.chksal = 0 and kn.chksub=0 THEN np.devnom ELSE 0 END) AS bonificacion,
            SUM(CASE WHEN kn.tipknp = 'D' AND kn.chksal = 1 THEN np.devnom ELSE 0 END) AS base_parafiscales,
            npref.salmin AS salario_minimo_referencia,
            npref.subtra AS subsidio_referencia
        FROM nomcmp nc
        JOIN nompre np ON nc.nriper = np.nriper 
                      AND nc.codfte = np.codfte 
                      AND nc.nrocmp = np.nrocmp
        JOIN knpnom kn ON np.codknp = kn.codknp
        JOIN nompar npref ON YEAR(nc.inicmp) = npref.anonom
        WHERE YEAR(nc.inicmp) = :anio 
          " . ($mes ? "AND MONTH(nc.inicmp) = :mes" : "") . "
          AND nc.estcmp != 'ELIMINADO'
          AND np.estdet != 'ELIMINADO'
        GROUP BY id_empleado, nombre_empleado, periodo, salario_minimo_referencia, subsidio_referencia
        ORDER BY nombre_empleado, periodo;
    ";

    // Preparar y ejecutar consulta
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":anio", $anio, PDO::PARAM_INT);
    if ($mes) {
        $stmt->bindParam(":mes", $mes, PDO::PARAM_INT);
    }
    $stmt->execute();
    $resultados = $stmt->fetchAll();

    // Ajuste de valores mínimos y cálculo de prestaciones
    foreach ($resultados as &$row) {
        $row['devengado'] = $row['base_parafiscales'] ;
        if ($row['base_parafiscales'] < $row['salario_minimo_referencia']) {
            $row['base_parafiscales'] = $row['salario_minimo_referencia'];
        }
        if ($row['subsidio_transporte'] < $row['subsidio_referencia']) {
            $row['subsidio_transporte'] = $row['subsidio_referencia'];
        }

        $row['pension'] = round($row['base_parafiscales'] * 0.16,0);
        $row['arl'] = round($row['base_parafiscales'] * 0.0052,0);
        $row['ccf'] = round($row['base_parafiscales'] * 0.04,0);
        $row['prima'] = round(($row['salario'] + $row['subsidio_transporte']) * 0.0833,0);
        $row['cesantias'] = round(($row['salario'] + $row['subsidio_transporte'])  * 0.0833,0);
        $row['intereses_cesantias'] = round($row['cesantias'] * 0.12,0);
        $row['vacaciones'] = round($row['base_parafiscales'] * 0.0417,0);
        $row['total'] = $row['devengado'] + $row['subsidio_transporte'] + $row['bonificacion'] +  $row['pension'] +  $row['ccf'] + 
                        $row['arl'] + $row['prima'] + $row['cesantias'] + $row['intereses_cesantias'] + $row['vacaciones'];

        // Aplicar separación de miles
        /*foreach ($row as $key => $value) {
            if (is_numeric($value)) {
                $row[$key] = number_format($value, 0, '.', ',');
            }
        }*/
    }

    
    // Enviar JSON al frontend
    echo json_encode($resultados);

} catch (Exception $e) {
    echo json_encode(["error" => "Error en la consulta: " . $e->getMessage()]);
}
?>
