<?php
    require('conexion.php');
    $id_prod = $_GET['prod'];
    $from_date = $_GET['dateIni'];
    $to_date = $_GET['dateFin'];
   
    $unions="";
    $timestamp = strtotime($to_date);
    $year = date("Y", $timestamp);

    
    $timestamp2 = strtotime($from_date);
    $year2 = date("Y", $timestamp);

    $values = 0;
    if ($year == $year2) {
        $query = query("SHOW TABLES LIKE 'historico_$year';");
        if ($query->num_rows > 0 ) {
            $nota=query("SELECT COUNT('RES') AS 'RES', SUM(TOTALTAREFE) 'TOTALTAREFE', SUM(PERSONAS) 'PERSONAS', SUM(CANT) 'CANT' FROM (SELECT COUNT(*) 'RES', SUM(pd.pro_precio_venta * pdn.pdn_cantidad) 'TOTALTAREFE',
                    ntv.ntv_no_personas 'PERSONAS', SUM(pdn.pdn_cantidad) 'CANT'  FROM historico_$year AS pdn
                INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
                INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
                WHERE pdn.pdn_estatus NOT IN ('eliminado','cancelado') AND ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.id_producto='$id_prod'
                            AND ntv_no_mesa!='' GROUP BY ntv.id) AS result;");
            $resultado=mysqli_fetch_assoc($nota);

            $qry_mesas=query("SELECT COUNT(RES) 'RES'
                FROM (SELECT COUNT(*) 'RES' 
                FROM historico_$year AS pdn
                    INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
                    INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
                WHERE ntv.ntv_fecha_creacion 
                BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND ntv_no_mesa!='' AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
                AND pdn.id_producto='$id_prod' GROUP BY ntv.id) AS result;"); 
            $mesas=mysqli_fetch_assoc($qry_mesas);

            $qry_efect=query("SELECT ifnull(SUM(ntv_efectivo),0) 'RES' 
            FROM historico_$year pdn 
            INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
            INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
            WHERE ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
            and ntv_id_tipo_pago!=1 AND pdn.id_producto='$id_prod'"); 
            $efect=mysqli_fetch_assoc($qry_efect);

            $qry_tarj=query("SELECT ifnull(SUM(ntv_efectivo),0) 'RES' 
            FROM historico_$year AS pdn 
            INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
            INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
            WHERE ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
            and ntv_id_tipo_pago!=2 AND pdn.id_producto='$id_prod'");
            $tarj=mysqli_fetch_assoc($qry_tarj);

            $notas= $resultado['RES'];
            $total_efe_tar= $resultado['TOTALTAREFE'];
            $personas = $resultado['PERSONAS'];
            $mesas=$mesas['RES'];
            $efect= $efect['RES'];
            $tarj= $tarj['RES'];
            $cant= $resultado['CANT'];
            
            $datos = [
                'notas' => $notas,
                'total_efe_tar' =>  number_format($total_efe_tar),
                'personas' => $personas,
                'mesas' => $mesas,
                'efect' =>  number_format($efect),
                'tarj' =>  number_format($tarj),
                'cant' =>  number_format($cant),
            ];
            
        }else{
            $nota=query("SELECT COUNT('RES') AS 'RES', SUM(TOTALTAREFE) 'TOTALTAREFE', SUM(PERSONAS) 'PERSONAS', SUM(CANT) 'CANT' FROM (SELECT COUNT(*) 'RES', SUM(pd.pro_precio_venta * pdn.pdn_cantidad) 'TOTALTAREFE',
                    ntv.ntv_no_personas 'PERSONAS', SUM(pdn.pdn_cantidad) 'CANT'  FROM productos_nota AS pdn
                INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
                INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
                WHERE (pdn.impreso!=0 or pdn.impreso='IC')AND ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.id_producto='$id_prod'
                            AND ntv_no_mesa!='' GROUP BY ntv.id) AS result;");
            $resultado=mysqli_fetch_assoc($nota);

            $qry_mesas=query("SELECT COUNT(RES) 'RES'
                FROM (SELECT COUNT(*) 'RES' 
                FROM productos_nota AS pdn
                    INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
                    INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
                WHERE ntv.ntv_fecha_creacion 
                BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND ntv_no_mesa!='' AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
                AND pdn.id_producto='$id_prod' GROUP BY ntv.id) AS result;"); 
            $mesas=mysqli_fetch_assoc($qry_mesas);

            $qry_efect=query("SELECT ifnull(SUM(ntv_efectivo),0) 'RES' 
            FROM productos_nota pdn 
            INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
            INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
            WHERE ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
            and ntv_id_tipo_pago!=1 AND pdn.id_producto='$id_prod'"); 
            $efect=mysqli_fetch_assoc($qry_efect);

            $qry_tarj=query("SELECT ifnull(SUM(ntv_efectivo),0) 'RES' 
            FROM productos_nota AS pdn 
            INNER JOIN notas_venta AS ntv ON ntv.id = pdn.id_nota_venta
            INNER JOIN productos AS pd ON pd.id = pdn.id_producto 
            WHERE ntv.ntv_fecha_creacion 
            BETWEEN '$from_date' AND '$to_date' AND ntv_estatus IN ('finalizado','impreso') AND pdn.pdn_estatus NOT IN ('eliminado','cancelado')
            and ntv_id_tipo_pago!=2 AND pdn.id_producto='$id_prod'");
            $tarj=mysqli_fetch_assoc($qry_tarj);

            $notas= $resultado['RES'];
            $total_efe_tar= $resultado['TOTALTAREFE'];
            $personas = $resultado['PERSONAS'];
            $mesas=$mesas['RES'];
            $efect= $efect['RES'];
            $tarj= $tarj['RES'];
            $cant= $resultado['CANT'];
            
            $datos = [
                'notas' => $notas,
                'total_efe_tar' =>  number_format($total_efe_tar),
                'personas' => $personas,
                'mesas' => $mesas,
                'efect' =>  number_format($efect),
                'tarj' =>  number_format($tarj),
                'cant' =>  number_format($cant),
            ];
            
        }
    }
    
    
   
    echo json_encode($datos);
?>