404

D7net
:/ proc / thread-self / cwd / adm / vendor / api /
Filename : planilha_negociacao.php
back
<?php
//session_start();



	
//Credenciais de acesso ao BD HOST

//define('HOST', 'crsys.ml:3306');
define('HOST', '159.65.100.255');

define('USER', 'carlos');
define('PASS', 'Crm8751M@');
define('DBNAME', 'rosario');
/*
define('HOST','localhost:3308');
define('USER', 'root');
define('PASS', '');
define('DBNAME', 'rosario');
*/
ini_set('display_errors',1);

ini_set('display_startup_erros',1);

error_reporting(E_ALL);



// conecta ao banco de dados

$link = mysqli_connect(HOST, USER, PASS);



if (!$link) {

    die('Não conseguiu conectar: ' . mysql_error());

}



// seleciona o banco devcodes

$db_selected = mysqli_select_db( $link,DBNAME);



if (!$db_selected) {

    die ('Não pode selecionar o banco '.DBNAME.' : ' . mysql_error());

}
$repres_id = $_GET['repres_id'];
$data= $_GET['data'];
$sugestao_id= $_GET['sugestao_id'];

//$_SESSION['id_user']
$sql = "select f.nome_fantasia nome_forn,
                                       p.data_cotacao data_cotacaoi,
                                       date_format(p.data_cotacao, '%d/%m/%Y') data_cotacao_d,
                                       date_format(p.data_cotacao, '%d_%m_%Y') data_cotacao_m,
                                       p.sugestao_id,
                                       p.cotacao_id,
				       p.repres_id,	
                                       u.apelido repres_nome,
                                       p.prod_cod, 
                                         p.qtde,
                                       cast(p.ean as char) ean,
                                       m.nome marca_nome,
                                       pr.nome prod_nome,
                                       p.preco_cotado preco_cotado,
                                       (p.preco_cotado * p.qtde) total_cotado,
                                       p.tenho_preco,
                                       (p.preco_desejado * p.qtde) total_tenho,
                                       p.dife,
                                       case when gn.nome is null then
                                         m.nome
					else
                                         gn.nome
					end
                                       nome_planilha 
                                       from  ct_pedido_simulado p
                                  inner join ro_marca m
                                    on m.id = p.marca_id     
                                  left join ct_grupo_negocia_marca gnm
                                   on gnm.marca_id = p.marca_id 
                                  left join ct_grupo_negocia gn
                                    on gn.id = gnm.grupo_id
                                 inner join ct_fornecedor f
                                 on f.forn_id = p.forn_id
                                  inner join ro_produto pr
                                    on pr.id = p.prod_cod
                                  inner join adms_usuarios u
                                     on u.id = p.repres_id
                                 where p.data_cotacao = '{$data}'
                                   and p.repres_id = {$repres_id}
                                   and p.sugestao_id = {$sugestao_id}
                                   order by m.nome, pr.nome";

$res = mysqli_query($link,$sql);
$row_cnt = $res->num_rows;

?>
<?php
require $_SERVER['DOCUMENT_ROOT']."/rosario/adm/vendor/autoload.php"; 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PHPOffice\phpspreadsheet\Shared\Date;
?>
<?php
if($row_cnt > 0){

?>
<?php


$spreadsheet = '';


date_default_timezone_set ("America/Sao_Paulo");  
 $datahoje = date("YmdHis");
 $styleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '00000000'],
            
        ],
    ],
     
];

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->getActiveSheet()->getStyle('A1:L2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getColumnDimension('A')->setWidth(80,'px');
$sheet->getColumnDimension('B')->setWidth(70,'px');
$sheet->getColumnDimension('C')->setWidth(70,'px');
$sheet->getColumnDimension('D')->setWidth(70,'px');
$sheet->getColumnDimension('E')->setWidth(70,'px');
$sheet->getColumnDimension('F')->setWidth(70,'px');
$sheet->getColumnDimension('G')->setWidth(100,'px');
$sheet->getColumnDimension('H')->setWidth(400,'px');
$sheet->getColumnDimension('I')->setWidth(100,'px');
$sheet->getColumnDimension('J')->setWidth(100,'px');
$sheet->getColumnDimension('K')->setWidth(120,'px');
$sheet->getColumnDimension('L')->setWidth(150,'px');
$spreadsheet->getActiveSheet()->mergeCells('A1:L1');
$sheet->getStyle('A1:L2')->getFont()->setBold(true);
$sheet->setCellValue('A2', 'DATA ');
$sheet->setCellValue('B2', 'REPRES ');
$sheet->setCellValue('C2', 'ID CT');
$sheet->setCellValue('D2', 'ID SUG');
$sheet->setCellValue('E2', 'COD INT');
$sheet->setCellValue('F2', 'QTDE');
$sheet->setCellValue('G2', 'CODIGO EAN');
$sheet->setCellValue('H2', 'DESCRIÇÃO');

$sheet->setCellValue('J2', 'PRE�O NEGOCIADO');
$sheet->setCellValue('K2', 'DIFE.TOTAL');
$sheet->setCellValue('L2', 'MARCA');
 $sn=3;
$total_preco_cotado = 0;
$total_preco_tenho = 0;


while ($row = $res->fetch_assoc()) {
    
    if($sn == 3){ 
        $nome = $row['nome_forn'];  
        $repres_id = $row['repres_id']; 
        $repres_nome = $row['repres_nome'];
        $marca_nome = $row['marca_nome'];
        $cab_plan = $row['nome_planilha'];
        $sheet->setCellValue('I2',  $row['nome_forn']);
        //$sugestao_id = $arr[0]['sugestao_id'];
        $cabec = "ROSÁRIO - PEDIDO DE NEGOCIAÇÃO ". $cab_plan. ' '. $row['data_cotacaoi']. ' '.$repres_nome. ' Pedido: '.$row['cotacao_id'].'/'. $row['sugestao_id'];
        $nomePlanilha = "negociacao_".$cab_plan.'_'.$repres_id.'_'. $row['data_cotacao_m'].'_'. $row['cotacao_id'].'_'. $row['sugestao_id'].'.xlsx'; 
        $sheet->setCellValue('A1', $cabec);
    }
         $sheet->setCellValue('A'.$sn, $row['data_cotacao_d']);
         $sheet->setCellValue('B'.$sn, $row['repres_id']);
         $sheet->setCellValue('C'.$sn, $row['cotacao_id']);
         $sheet->setCellValue('D'.$sn, $row['sugestao_id']);
         $sheet->setCellValue('E'.$sn, $row['prod_cod']);
         $sheet->setCellValue('F'.$sn, $row['qtde']);
         $sheet->getStyle('G'.$sn)->getNumberFormat()->setFormatCode('0000000000000');
          
         $sheet->setCellValue('G'.$sn, $row['ean']);
         $sheet->setCellValue('H'.$sn, $row['prod_nome']);
         $sheet->getStyle('I'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');
         $sheet->setCellValue('I'.$sn, $row['preco_cotado']);
         $sheet->getStyle('J'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');
         $sheet->setCellValue('J'.$sn, $row['tenho_preco']);
       //  $sheet->setCellValue('K'.$sn, "=if(J".$sn.">0,(I".$sn."-J".$sn.")*F".$sn.",0)");
        // SE(J3>0;SE(I3>J3;(I3-J3)*F3;0))
           $sheet->setCellValue('K'.$sn, "=if(I".$sn." > J".$sn.", F".$sn."*I".$sn."-F".$sn."*J".$sn.",0)");
   //      $sheet->setCellValue('k'.$sn, $row['dife']);
         $sheet->setCellValue('L'.$sn, $row['marca_nome']);
//         $sheet->setCellValue('J'.$sn, "=(H".$sn."-I".$sn.")*E".$sn);
         $total_preco_cotado += ($row['preco_cotado'] * $row['qtde']);
         $total_preco_tenho += ($row['tenho_preco'] * $row['qtde']);
                  
	$sn++;				 
}        
$sheet->getStyle('A1:K'.$sn)->applyFromArray($styleArray);
$fim = $sn -1 ;
$sheet->getStyle('H'.$sn)->getFont()->setBold(true);
$sheet->setCellValue('H'.$sn, 'TOTAIS');
$sheet->getStyle('I'.$sn)->getFont()->setBold(true);
$sheet->getStyle('I'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');
$sheet->setCellValue('I'.$sn, '=SUMPRODUCT(F3:F'.$fim.',I3:I'.$fim.')');
$sheet->getStyle('J'.$sn)->getFont()->setBold(true);
$sheet->getStyle('J'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');
//$sheet->setCellValue('I'.$sn, '=SUMPRODUCT(E3:E'.$fim.',I3:I'.$fim.')');
//$sheet->setCellValue('K'.$sn, '=SUMPRODUCT(F3:F'.$fim.',I3:I'.$fim.')-SUMPRODUCT(F3:F'.$fim.',J3:J'.$fim.')');
$sheet->setCellValue('K'.$sn, '=sum(K3'.':K'.$fim.')');
$sheet->getStyle('K'.$sn)->getFont()->setBold(true);
$sheet->getStyle('K'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');
$fim = $sn;
//$sn++;				 
//$sheet->getStyle('H'.$sn)->getFont()->setBold(true);
//$sheet->setCellValue('E'.$sn, 'DIFERENÇA');
//$sheet->getStyle('H'.$sn)->getFont()->setBold(true);
//$sheet->getStyle('H'.$sn)->getNumberFormat()->setFormatCode('##,###,###,##0.00');

$sheet->getStyle('A2:L'.$sn)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$sheet->getStyle('A2:I'.$sn)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);
$sheet->getStyle('H2:L'.$sn)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);


$writer = new Xlsx($spreadsheet);

$local = $_SERVER['DOCUMENT_ROOT']."/rosario/adm/".$nomePlanilha;
$writer->save($local);



header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$nomePlanilha.'"'); 
header('Cache-Control: max-age=0');

ob_end_clean();
ob_start();
flush();
$writer->save('php://output');
 unlink($local);
 
$UrlDestino = URLADM . 'con-ct-responde-negociacao/listar';
header("Location: $UrlDestino");
}else{
     $_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                ERRO: Não encontrou nenhuma Negociação !
                <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                <span aria-hidden='true'>&times;</span></button>
            </div>";
    echo $_SESSION['msg'];
    //$UrlDestino = URLADM . 'con-ct-responde-negociacao/listar';
    //header("Location: $UrlDestino");
}
 die;  

?>