<?php
    //configurações do banco
    ini_set('max_execution_time', 300);
    include_once("../../function/conexao.php");
    include_once '../../Classes/PHPExcel/IOFactory.php';
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objPHPExcel = $objReader->load("../../Templates/PedidoCompra.xlsx");
    // Create new PHPExcel object
    //$objPHPExcel = new PHPExcel();
    // Set document properties

    if(isset($_POST['PDC'])){
    $Dados = $_POST['PDC'];
    //atualização dos dados do pedido
    $data1 = explode("/",$Dados[10]);
    $Dados[10] = substr($data1[2],0,4)."/$data1[1]/$data1[0]";
    if(empty($Dados[16])){
        $Dados[16]="0.00";
    }
    if(empty($Dados[9])){
        $Dados[9]="0.00";
    }
    if(empty($Dados[16])){
        $Dados[16]="0.00";
    }
    if(empty($Dados[17])){
        $Dados[17]="0.00";
    }
    //if($_POST['btPDC']=="ATUALIZAR"){
    $sql_up = "update financeiro_pedido_compra set
    pgto = '$Dados[12]',
    contato='$Dados[3]',
    data='$Dados[10]',
    desconto='$Dados[16]',
    IPI='$Dados[9]',
    FRETE='$Dados[17]',
    status='GERADO',
    data_modif=now(),
    usuario='$_SESSION[Usuario]' where pedidopk = $Dados[1]";
    mysql_query($sql_up);
    //echo $sql_up;
    if ($error = mysql_errno()) die("<script>alert("1 - Erro UP, informe ao atualizar Administrador");history.back();</script>");
    //}
    $sql = "select nome,endereco1,num_end1,bairro1,cep1,cidade1,uf1,cgc,insc_est,concat(ddd1,'-',num1) as tel,cond_pgfk from financeiro_cliente where cgc='".$Dados[0]."'";
    $DadosCliente = mysql_fetch_assoc(mysql_query($sql,$conexao));
    // SELECIONAR OS DADOS PARA PLANILHA
    $query = mysql_query($sql,$conexao);
    // INICIAMOS A CRIAÇÃO DA TABELA
    $TotIPI = ($Dados[9]/100)*$Dados[8];
    $TotIPI = number_format($TotIPI, 2, ',', ' ');
    $Total = $Dados[8] + $TotIPI;
    $Dados[11] = strtoupper($Dados[11]);
    $Dados[6] = strtoupper($Dados[6]);
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("C3", "$DadosCliente[nome]")
                ->setCellValue("C4", "$DadosCliente[endereco1], N°: $DadosCliente[num_end1]")
                ->setCellValue("C5", "$DadosCliente[bairro1]")
                ->setCellValue("C6", "$DadosCliente[cep1]")
                ->setCellValue("I4", "$DadosCliente[cidade1]")
                ->setCellValue("I5", "$DadosCliente[uf1]")
                ->setCellValue("I6", "$DadosCliente[cgc]")
                ->setCellValue("I7", "$DadosCliente[insc_est]")
                ->setCellValue("I8", "$DadosCliente[tel]")
                ->setCellValue("C10", "$Dados[1]") //pcn
                ->setCellValue("F10", "$Dados[2]")//data emissao
                ->setCellValue("I10", "$Dados[12]")
                ->setCellValue("C7", "$Dados[3]");//ordem
    $select=mysql_query("select item,concat(substr(mpfk,3,2),codigo),descricao,qtd,un,vlrun,vlrtot from financeiro_pedido_compra_item where pedidofk = '$Dados[1]' order by item") ;
    $row =14;
    $Total=0;
    $data1 = explode("/",$Dados[10]);
    $Dados[10] = substr($data1[2],0,4)."/$data1[1]/$data1[0]";
    while($Itens = mysql_fetch_array($select)){
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("A$row", "$Itens[0]")//codigo
                ->setCellValue("C$row", "$Itens[1]")//codigo
                ->setCellValue("E$row", "$Itens[2]]")//descricao
                ->setCellValue("J$row", "$Itens[3]")//qtd
                ->setCellValue("K$row", "$Itens[4]")//un
                ->setCellValue("L$row", "$Itens[5]")//Val Un
                ->setCellValue("N$row", "$Itens[6]")//Total
                ->setCellValue("O$row", "$Dados[9]")//ipi
                ->setCellValue("P$row", "$Itens[3]")//qtd
                ->setCellValue("Q$row", "$Dados[10]");//data entrega
    $objPHPExcel->getActiveSheet()->mergeCells("C$row:D$row");
    $objPHPExcel->getActiveSheet()->mergeCells("L$row:M$row");
    $Char = strlen($Itens[2]);
    if($Char>43){
        $IntDiv=(int)($Char/43);
        $row2=$row+$IntDiv;
        $objPHPExcel->getActiveSheet()->mergeCells("E$row:I$row2");
        $row+=$IntDiv+1;
    }else{
        $objPHPExcel->getActiveSheet()->mergeCells("E$row:I$row");
        $row++;
    }
    //$objPHPExcel->getActiveSheet()->getStyle("E$row")->getAlignment()->setWrapText(false);
    $Total+=$Itens[6];
    }
    //}/*
    $TotIPI = $Total * ($Dados[9]/100);
    $TotalGeral = $Total+$TotIPI+$Dados[17];
    $TotalGeral=$TotalGeral-$Dados[16];
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue("L38", "$Total")//codigo
                ->setCellValue("P38", "$TotIPI")
                ->setCellValue("Q38", $TotalGeral)
                ->setCellValue("N38", "$Dados[17]");//codigo
    $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(12);
    $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(12);
    $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(12);
    $objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(12);
    $objPHPExcel->getActiveSheet()->getRowDimension('7')->setRowHeight(12);
    $objPHPExcel->getActiveSheet()->getRowDimension('8')->setRowHeight(12);
    $arquivo = 'PedidoCompra'.date("Ymd")."_ $Dados[1]";
    // Criamos uma tabela HTML com o formato da planilha
    }
    $objPHPExcel->getActiveSheet()->setTitle('Custo');
    $objPHPExcel->setActiveSheetIndex(0);
    header('Content-Type: application/vnd.openxmlformats-officedocument.SpreadsheetML.Sheet');
            header("Content-Disposition: attachment;filename="$arquivo.xlsx"");
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
            header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header ('Pragma: public'); // HTTP/1.0
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save('php://output');
    exit;