<?php
	header('Content-type: application/vnd.ms-excel; charset=UTF-8');
	header('Content-Disposition: attachment;filename=ResumenVenta.xls');
	header('Pragma: no-cache');
	header('Expires: 0');

	// session_start();
	include('../../../php/conn.php');

	// if(!isset($_SESSION["id"])){
	// 	header('Location: index.php');
	// }

	mysqli_query($enlace, "SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
	mysqli_query($enlacehost, "SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
	mysqli_query($enlace, "SET NAMES 'utf8'");
?>

<!DOCTYPE html>
<html lang="es">
	<head>
		<meta charset="utf-8">
	</head>

	<body class="nav-md footer_fixed">
 		<div class="container body">
			<div class="main_container">
				<?php

					// Recuperando las variables enviadas
						$FI = $_GET["FI"];
						$FF = $_GET["FF"];
						$DC = $_GET["DC"];
						$NC = $_GET["NC"];
						$S = $_GET["S"];
						$NM = $_GET["NM"];

				?>

					<font size = "3"><b>
						RESUMEN DE VENTAS
					</b></font>

					<br/>

					<font size = "2">
						Fecha de inicio: <?php echo $FI; ?>
					</font>
					<br/>
					<font size = "2">
						Fecha de fin: <?php echo $FF; ?>
					</font>

					<br/>
					<br/>

					<table id="datatable" class="table table-striped jambo_table bulk_action" border=1px bordercolor="#aeaaaa">
						<thead>
							<tr>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Item
                                </center></th>
                                <th rowspan="2" style=" border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Oficina
                                </center></th>
                                <th rowspan="2" style=" border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    N° Recibo
                                </center></th>
                                <th colspan="3" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Cliente
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Fecha Recepción
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Fecha Hora Entrega
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Medio Pago
                                </center></th>
                                <th colspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Muestra
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Elementos Análisis
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Peso Muestra (Kg)
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Estado Muestra
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Detalle Envase
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Tipo Muestra
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Tipo Análisis
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Precio Exceso
                                </center></th>
                                <th rowspan="2" style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #2F75B5; color: #ffffff; vertical-align: middle;"><center>
                                    Total Venta
                                </center></th>
                            </tr>

                            <tr>
                                <th style="border: solid; border-width: 1px; border-color: #A6ABAB; vertical-align: middle; background-color: #C2E0F2; color: #595959"><center>
                                    Nombres
                                </center></th>
                                <th style="border: solid; border-width: 1px; border-color: #A6ABAB; vertical-align: middle; background-color: #C2E0F2; color: #595959"><center>
                                    DNI / RUC
                                </center></th>
                                <th style="border: solid; border-width: 1px; border-color: #A6ABAB; vertical-align: middle; background-color: #C2E0F2; color: #595959"><center>
                                    Teléfonos
                                </center></th>
                                <th style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #C2E0F2; vertical-align: middle; color: #595959"><center>
                                    Cód. Interno
                                </center></th>
                                <th style="border: solid; border-width: 1px; border-color: #A6ABAB; background-color: #C2E0F2; vertical-align: middle; color: #595959"><center>
                                    Nombre
                                </center></th>
                            </tr>
						</thead>

						<tbody>

				<?php
                    $c = 1;

				    $q_resultados = "SELECT CP.Id_Pedido,
                                            DP.Id AS Id_Detalle,
                                            CP.Sucursal,
                                            S.des_sucursal,
                                            PV.recibo_correlativo_sucursal + '-' + RIGHT('00000000' + CONVERT(VARCHAR, PV.recibo_correlativo_codigo), 8) AS NUM_RECIBO,
                                            CP.Nombre_Cliente,
                                            CP.Documento_Cliente,
                                            C.cNumeroTelefono + CASE WHEN LEN(cSegundoNumeroTelefono) > 0 THEN ' - ' + cSegundoNumeroTelefono ELSE '' END AS TELEFONO_CLIENTE,
                                            CONVERT(VARCHAR, CP.FechaHora_Recepcion, 103) AS FechaHora_Recepcion,
                                            CONVERT(VARCHAR, CP.FechaHora_Entrega, 103) + ' ' + SUBSTRING(CONVERT(VARCHAR, CP.FechaHora_Entrega, 108), 1, 5) AS FechaHora_Entrega,

                                            (SELECT COUNT(_DP.Id)
                                               FROM _max_labperu_DetallePedido _DP
                                              WHERE _DP.Id_Pedido = DP.Id_Pedido) AS TOTAL_MUESTRAS,

                                            DP.Cod_Interno,
                                            DP.Nom_Muestra,
                                            DP.Elemento_Analisis,
                                            DP.Peso_Muestra,
                                            EM.Nombre_EstadoMuestra,
                                            DE.Nombre_DetalleEnvase,
                                            TM.Nombre_TipoMuestra,
                                            TA.Des_TipoAnalisis,
                                            DP.Precio_Exceso,

                                            DP.Precio_Final AS Total_Venta,
                                            CASE WHEN CP.Incluir_IGV = 1 THEN 'SI' ELSE 'NO' END AS INC_IGV,
                                            CP.usu_usuario,

                                            COALESCE(DAR.Cod_EstadoResultadosCliente, 0) AS Cod_EstadoResultadosCliente,
                                            CONVERT(VARCHAR, CP.FechaHora_Recepcion, 103) AS FechaHora_Recepcion2,
                                            CONVERT(VARCHAR, DAR.Fecha_Registro, 103) AS FechaHora_Entrega2,
                                            DAR.Ley_Au_gt,
                                            DAR.Ley_Au_ot,
                                            DAR.Ley_Ag_gt,
                                            DAR.Ley_Ag_ot,
                                            DAR.Porc_CU,
                                            DAR.Porc_PB,
                                            DAR.Porc_ZN,
                                            DAR.Porc_AS,
                                            DAR.Porc_SB,
                                            DAR.Porc_S,
                                            DAR.Porc_FE,
                                            DAR.Porc_H2O,
                                            CONVERT(VARCHAR, CIE.Fecha_Creacion, 103) AS Fecha_Informe,
                                            DAR.Observacion_Reporte,
                                            MP.des_mediopago
                                       FROM _max_labperu_CabeceraPedido CP
                                            INNER JOIN _max_labperu_PagoVenta PV ON CP.Id_Pedido = PV.Id_Pedido
                                            INNER JOIN _max_labperu_Sucursales S ON CP.Sucursal = S.Id
                                            INNER JOIN maestroCliente C ON CP.iIdCliente = C.iIdCliente
                                            INNER JOIN _max_labperu_DetallePedido DP ON CP.Id_Pedido = DP.Id_Pedido
                                            LEFT JOIN _max_labperu_DetalleAnalisisResultados DAR ON DP.Id_Pedido = DAR.Id_Pedido
                                            AND DP.Id = DAR.Id_Detalle_Pedido
                                            LEFT JOIN _max_labperu_CorrelativoInformeEnsayos CIE ON DAR.Id_Pedido = CIE.Id_Pedido
                                            AND DP.Id = CIE.Id_Detalle_Pedido
                                            INNER JOIN _max_labperu_EstadosMuestra EM ON DP.Estado_Muestra = EM.Id
                                            INNER JOIN _max_labperu_DetalleEnvase DE ON DP.Detalle_Envase = DE.Id
                                            INNER JOIN _max_labperu_TiposMuestra TM ON DP.Tipo_Muestra = TM.Id
                                            INNER JOIN _max_labperu_TipoAnalisis TA ON DP.Tipo_Analisis = TA.Id
                                            INNER JOIN _max_labperu_MedioPago MP ON PV.Id_MedioPago = MP.Id
                                      WHERE CONVERT(VARCHAR, CP.FechaHora_Recepcion, 23) BETWEEN '".$FI."' AND '".$FF."'";

                    if (strlen($DC) > 0){
                        $q_resultados .= " AND CP.Documento_Cliente LIKE '%".trim($DC)."%'";
                    }

                    if (strlen($NC) > 0){
                        $q_resultados .= " AND CP.Nombre_Cliente LIKE '%".trim($NC)."%'";
                    }

                    if (strlen($S) > 0){
                        $q_resultados .= " AND CP.Sucursal = ".$S;
                    }

                    if (strlen($NM) > 0){
                        $q_resultados .= " AND DP.Nom_Muestra LIKE '%".trim($NM)."%'";
                    }

                    $q_resultados .= " ORDER BY CP.FechaHora_Recepcion DESC, DP.Id";

                    if ($res_resultados = sqlsrv_query($enlacehost, $q_resultados)) {
                        while($row_resultados = sqlsrv_fetch_array($res_resultados)) {

                    ?>

                        <tr>
                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; text-align: right"><?php echo $c; ?></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242; background-color: <?php echo (($row_resultados["Sucursal"] == 1) ? '#D1DBBD' : '#D89196'); ?>"><center><b> <?php echo $row_resultados["des_sucursal"]; ?> </b></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["NUM_RECIBO"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242;"><?php echo $row_resultados["Nombre_Cliente"]; ?></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242;"><?php echo $row_resultados["Documento_Cliente"]; ?></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242;"><?php echo $row_resultados["TELEFONO_CLIENTE"]; ?></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["FechaHora_Recepcion"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["FechaHora_Entrega"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["des_mediopago"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242;"><center><?php echo $row_resultados["Cod_Interno"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle; color: #3F4242;"><center><?php echo $row_resultados["Nom_Muestra"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Elemento_Analisis"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Peso_Muestra"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Nombre_EstadoMuestra"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Nombre_DetalleEnvase"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Nombre_TipoMuestra"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo $row_resultados["Des_TipoAnalisis"]; ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo round($row_resultados["Precio_Exceso"]); ?></center></td>

                            <td style="cursor: pointer; border: solid; border-width: 1px; border-color: #D9D9D9; vertical-align: middle;"><center><?php echo round($row_resultados["Total_Venta"], 2); ?></center></td>

                        </tr>

                    <?php

                            $c += 1;

                        }
                    }
				?>

						</tbody>
					</table>
			</div>
		</div>
	</div>
</html>