<?php
    session_start();
	ob_start();

	$id_users = isset($_SESSION['id_users']) ? $_SESSION['id_users']: "";	
	$nome_user = isset($_SESSION['nome']) ? $_SESSION['nome']: "";	
	$email_users = isset($_SESSION['email']) ? $_SESSION['email']: "";	
	$pass_users = isset($_SESSION['pass']) ? $_SESSION['pass']: "";	
	$logado = isset($_SESSION['logado']) ? $_SESSION['logado']: "N";	

	if ($logado == "N" && $id_users == ""){	   
		echo  "<script type='text/javascript'>
					location.href='login.php'
				</script>";	
		exit();
	}
	
	require 'conexion.php';
	require_once('functions.php');

	//$leadTime = '7';
	//$dtAtual_LeadTime = date('d-m-Y', strtotime("+$leadTime days",strtotime($data_atual)));
	//$UmaSemana = date('d-m-Y', strtotime("+1 week",strtotime($data_atual)));
	
	$data_atual = date('Y-m-d');
	
	IF(isset($_POST['relatorio_estatisticas_executa'])){
		$entregaDe = $_POST["entregaDe"];
		$entregaAte = $_POST["entregaAte"];
		$producaoDe = $_POST["producaoDe"];
		$producaoAte = $_POST["producaoAte"];
		$cliente = $_POST["cliente"];		
	} ELSE {
		$entregaDe = date('Y-m-d');
		$entregaAte = date('Y-m-d', strtotime("+1 week",strtotime($data_atual)));
		$producaoDe = date('Y-m-d', strtotime("-1 week",strtotime($data_atual)));
		$producaoAte = date('Y-m-d');
		$cliente = '';
	}

	IF ( $producaoDe == '' ){
		$producaoDe = '2000/01/01';
		$producaoAte = '3000/01/01';
	}
	IF ( $entregaDe == '' ){
		$entregaDe = '2000/01/01';
		$entregaAte = '3000/01/01';
	}
		
	$sql1 = "SELECT
			companies.popular_name,	
			pedidos.nr_pedido,
			ordem_corte.id_oc as ordem_corte,
			DATE_FORMAT(pedidos.data_entrega, '%d/%m/%Y') AS data_entrega,			
			DATE_FORMAT(producao.producao_inicio, '%d/%m/%Y') AS producao_inicio,
			grade.somagerala,
			DATEDIFF( data_entrega , CURDATE() ) as dif
		FROM
			ordem_corte
			INNER JOIN pedidos ON pedidos.id = ordem_corte.id_pedido
			INNER JOIN companies ON pedidos.nr_company = companies.id
			INNER JOIN producao ON ordem_corte.id_oc = producao.id_oc
			INNER JOIN grade ON pedidos.id = grade.id_pedido
		WHERE 
			data_entrega BETWEEN date('$entregaDe') AND date('$entregaAte') AND
			date(producao.producao_inicio) BETWEEN date('$producaoDe') AND date('$producaoAte')	AND
			companies.popular_name like UPPER('%$cliente%')			
		GROUP BY 
			ordem_corte.id_oc
		ORDER BY 
			pedidos.data_entrega DESC"; 
			
			//ORDER BY ordem_corte DESC INTO OUTFILE "c:/agksis/relatorios/relatorio.xls" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

    include(HEADER_TEMPLATE);  
	//echo $sql1;
?>

   <form name="relatorio_estatisticas_executa" method="POST" action="" autocomplete="off" onload="">
		<div class="container-fluid">
			<div class="row">
				<div class="col-sm-2 text-left">
					<label for="entregaDe">Entrega de</label>
					<div class="input-group">
						<input type="date" class="form-control" id="entregaDe" name="entregaDe" value="<?php echo $entregaDe;?>" autofocus >
					</div>
				</div>
				<div class="col-sm-2 text-left">
					<label for="entregaAte">Entrega até</label>
					<div class="input-group">
						<input type="date"  class="form-control" id="entregaAte" name="entregaAte" value="<?php echo $entregaAte;?>" >
					</div>
				</div>				
				<div class="col-sm-2 text-left">
					<label for="producaoDe">Produção de</label>
					<div class="input-group">
						<input type="date"  class="form-control" id="producaoDe" name="producaoDe" value="<?php echo $producaoDe;?>" >
					</div>
				</div>		
				<div class="col-sm-2 text-left">
					<label for="producaoAte">Produção até</label>
					<div class="input-group">
						<input type="date"  class="form-control" id="producaoAte" name="producaoAte" value="<?php echo $producaoAte;?>" >
					</div>
				</div>
				
				<div class="col-sm-2 text-left">
					<label for="cli">Cliente</label>
					<div class="input-group">
						<input type="text"  class="form-control" id="cliente" name="cliente" value="<?php echo $cliente;?>" style='text-transform:uppercase'>
					</div>
				</div>
				

				<div class="col-sm-1 text-left">				
					<input type="submit" name="relatorio_estatisticas_executa" value="Gerar Relatório">	
				</div>
			</div>
			<hr>
			<br>

			<table class="table table-hover" id="t1" >
				<thead>
					<tr bgcolor="#cccccc">
						<th>O.C.</th>											
						<th>Cliente</th>
						<th>Pedido</th>
						<th>QTD</th>						
						<th>Entrega</th>	
						<th>Início Prod</th>
						<th>Meta</th>					
					</tr>
				</thead>
				
				<tbody>
					<?php 
					$resultado1 = $mysqli->query($sql1);
					while($row1 = $resultado1->fetch_array(MYSQLI_ASSOC)) { 
							$idOCx = $row1['ordem_corte'];
							$iniProd = $row1['producao_inicio'];							
					?>
						<tr style="font-weight:bold">
							<td><?php echo $row1['ordem_corte']; ?></td>						
							<td><?php echo $row1['popular_name']; ?></td>
							<td><?php echo $row1['nr_pedido']; ?></td>
							<td><?php echo $row1['somagerala']; ?></td>
							<td><?php echo $row1['data_entrega']; ?></td>
							<td><?php echo $row1['producao_inicio']; ?></td>						
							<td><?php echo $row1['dif']; ?></td>													
						</tr>
				</tbody>
				
							
					<?php 
						$sql2 = "SELECT
								companies.popular_name,
								pedidos.nr_pedido,
								ordem_corte.id_oc AS idOC,
								DATE_FORMAT(producao.producao_inicio,'%d/%m/%Y') AS producao_inicio,
								apontamentos.setor,
								apontamentos.obs,
								DATE_FORMAT(apontamentos.registro,'%d/%m/%Y %H:%i:%s') AS registro,
								grade.somagerala,
								DATEDIFF(producao_inicio,registro) AS dif_det1,
                                SUM(apontamentos.qtd) as qtd	
							FROM
								ordem_corte
								INNER JOIN pedidos ON pedidos.id = ordem_corte.id_pedido
								INNER JOIN companies ON pedidos.nr_company = companies.id
								INNER JOIN producao ON ordem_corte.id_oc = producao.id_oc
								INNER JOIN grade ON pedidos.id = grade.id_pedido
								INNER JOIN apontamentos ON apontamentos.id_producao = producao.id_producao
							WHERE 
								ordem_corte.id_oc = '$idOCx'           
								GROUP BY apontamentos.setor";	
								
							$resultado2 = $mysqli->query($sql2);
							while($row2 = $resultado2->fetch_array(MYSQLI_ASSOC)) { 	
							//echo "$sql2 <br>";							
					?>
						<tr>
							<td><?php echo $row2['setor']; ?></td>
							<td><?php echo $row2['registro']; ?></td>
							<td><?php echo $row2['qtd']; ?></td>

							<?php IF ( $row2['qtd'] < $row2['somagerala'] ) { ?>							
								<td><?php echo $row2['dif_det1']; ?></td>														
							<?php } ELSE { ?>							
								<td>	</td>														
							<?php } ?>							

						</tr>

						
				<?php } ?>
				
				<thead>
					<tr bgcolor="#cccccc">
						<th>O.C.</th>											
						<th>Cliente</th>
						<th>Pedido</th>
						<th>QTD</th>						
						<th>Entrega</th>	
						<th>Início Prod</th>
						<th>Meta</th>	
						<th></th>							
					</tr>
				</thead>

				<?php } ?>		
			</table>
		</div>					
	</form>


	
	<?php include(FOOTER_TEMPLATE); ?>
 <script>
    function somenteNumeros(num) {
        var er = /[^0-9.]/;
        er.lastIndex = 0;
        var campo = num;
        if (er.test(campo.value)) {
          campo.value = "";
        }
    }
 </script>
	