Generate CSV with side-by-side results in PHP

Question

I’m trying to get comparative results on a CSV generated in the php but I can’t stack side by side the way EXPECTED RESULT of the attached image.

I made a code below that did not break the line when it changes to 2018

TABLE

CREATE TABLE `clientes_vendas` (
  `codigo` int(11) NOT NULL,
  `cliente_cnpj` varchar(40) NOT NULL,
  `codigo_cliente` int(11) NOT NULL,
  `codigo_repre` int(11) NOT NULL,
  `valor` decimal(10,2) NOT NULL DEFAULT '0.00',
  `dia` varchar(20) NOT NULL,
  `mes` varchar(40) NOT NULL,
  `ano` varchar(40) NOT NULL,
  `data` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CODE

while:                         

  if ($ano_atual2==0) {
      $ano_atual2 = $ano;
  }  
  $pula_linha="\n";

  if ( $ano != $ano_atual2 )  :
      $ano_atual2 = $ano;
      $dadosTXT2 = "$pula_linha$nome_repre;$mes;$ano$pula_linha";
      $arraDados[] = $dadosTXT2;
      $soma=0;
  endif;

   @$soma +=  $row['valor'];
   $somaConvert = number_format($soma,2,",",".");
   $dadosTXT2 = "$cnpj_cliente;$nome_cliente;$nome_repre;$mes;$ano;$valor$pula_linha";
   $arraDados[] = $dadosTXT2;

endwhile; 

$fp = fopen($csv, 'a');
$escreve1 = fwrite($fp,   utf8_decode($header));

foreach ($arraDados as $file) {
    $escreve2 = fwrite($fp,  $file);
}

fclose($fp);

EXPECTED RESULT: Display SUMS of customer sales side by side EXAMPLE: AUGUST 2017 , AUGUST 2018, SEPTEMBER 2017- SEPTEMBER 2018 but starting normally from January

Answer 1

$codigo_cliente = 0;
$pula_linha="\n";
while:                         

  if ($ano_atual2==0) {
      $ano_atual2 = $ano;
  }
  if ( $ano != $ano_atual2 )  :
      $ano_atual2 = $ano;
      $dadosTXT2 = "$pula_linha$nome_repre;$mes;$ano$pula_linha";
      $arraDados[] = $dadosTXT2;
      $soma=0;
  endif;

  if ($codigo_cliente == 0) {
      $codigo_cliente  = $row['codigo'];
      $linha = "$cnpj_cliente;$nome_cliente;$nome_repre;";
  }

  @$soma +=  $row['valor'];
   $somaConvert = number_format($soma,2,",",".");

  if ( $row['codigo']!= $codigo_cliente)  {
       $codigo_cliente  = $row['codigo'];
       $linha .= $pula_linha;
       $arraDados[] = $linha;
       $linha = "$cnpj_cliente;$nome_cliente;$nome_repre;$mes;$ano;$valor";
  }
  else{
    $linha .= "$mes;$ano;$valor;";
  }

endwhile; 

$fp = fopen($csv, 'a');
$escreve1 = fwrite($fp,   utf8_decode($header));

foreach ($arraDados as $file) {
    $escreve2 = fwrite($fp,  $file);
}

fclose($fp);

I hope I’ve helped or at least come close.

Answer 2

To answer your question I had to create other tables that were not informed to get the nome_representante and the nome_cliente. And to do tests I created several records, see:

I put as optional the input value of the beginning of the year and the end of the year to be analyzed, because you may want to perform a filter. See the line below:

$anoInicio = "2017";
$anoFim = "2018";

Finally, I created the code based on yours. Note that select is ordering, first by month, then by client code and then by year. This way you will get the expected result.

    $anoInicio = "2017";
    $anoFim = "2018";

    $query = mysqli_query($conn, "SELECT ano, mes, 
        (
            SELECT nome_cliente FROM cliente 
            WHERE cliente.codigo_cliente = clientes_vendas.codigo_cliente 
        ) AS 'nome_cliente', 
        (
            SELECT nome_representante FROM representante 
            WHERE representante.codigo_representante = clientes_vendas.codigo_representante 
            GROUP BY representante.codigo_representante
        ) AS 'nome_representante', 
        SUM(valor) AS 'total' FROM clientes_vendas 
        WHERE ano >= '$anoInicio' AND ano <= '$anoFim' 
        GROUP BY mes, codigo_cliente, ano ORDER BY codigo_cliente, mes, ano ASC"
        );

    $arraDados = array();
    $strTmpCliente = "";
    $mes = array('', 'Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez'); // formatador dos meses
    $x = 1;


    while($dados = mysqli_fetch_assoc($query)){

        if($strTmpCliente != $dados['nome_cliente']){
            $strTmpCliente = $dados['nome_cliente'];
            $arraDados[$strTmpCliente] = array();
            $arraDados[$strTmpCliente]['nome'] = $dados['nome_cliente'];
            $arraDados[$strTmpCliente]['representante'] = $dados['nome_representante'];
        }

        $strMesAno = $mes[$dados['mes']]."_".$dados['ano'];
        $arraDados[$strTmpCliente][$strMesAno] = number_format($dados['total'],2,",",".");

    }


    $header = "Cliente;Representante;";
    $mesAtual = 1;
    $anoAtual = (int)$anoInicio;

    $arrayDatas = array();

    for($x = 0; $x < 24; $x++){

        $data = $mes[$mesAtual]."_".$anoAtual;
        $arrayDatas[] = $data;
        $header .= str_replace("_", "'",$data).";";

        $alteraMes = $x % 2 == 0 ? false : true;
        if($alteraMes){
            $mesAtual++;
            $anoAtual = $anoInicio;
            continue;
        }
        $anoAtual = $anoFim;
    }
    $header = substr($header, 0, -1);
    $header .= "\n";

    $strDados = array();

    foreach($arraDados as $cliente ){

        $strTmp = $cliente['nome'].";".$cliente['representante'].";";
        for($i = 0; $i < count($arrayDatas); $i++){
            $data = $arrayDatas[$i];
            if(isset($cliente[$data])){
                $strTmp .= $cliente[$data].";";
                continue;
            }
            $strTmp .= "-;";
        }

        $strTmp = substr($strTmp, 0, -1);
        $strTmp .= "\n";
        $strDados[] = $strTmp;

    }

    $csv = "arquivo.csv";

    // escreve o cabeçalho
    $fp1 = fopen($csv, 'w');
    fwrite($fp1, $header);
    fclose($fp1);

    // escreve o resultado
    $fp2 = fopen($csv, 'a');
    foreach ($strDados as $file) {
        fwrite($fp2,  $file);
    }
    fclose($fp2);

The generated file was like this:

EDIT

I noticed that in the image that you posted that a few months were without sales. I went to take the test in my code and, as I imagined, it was a mistake. Because the values were filled sequentially.

I changed the code and now it is working correctly. I took the opportunity to insert the number_format that you are using. The result was this:

EDIT 2

Change that part that will work:

           if($strTmpCliente != $dados['nome_cliente']){
                $strTmpCliente = $dados['nome_cliente'];
                $arraDados[$strTmpCliente] = array();
                $arraDados[$strTmpCliente]['nome'] = $dados['nome_cliente'];
                $arraDados[$strTmpCliente]['representante'] = $dados['nome_representante'];
            }

            //---> o erro estava aqui
            $dadosMes = (int)$dados['mes']; // aqui ele transforma em inteiro 
            $strMesAno = $mes[$dadosMes]."_".$dados['ano']; // aqui ele grava a strinmg corretamente
            $arraDados[$strTmpCliente][$strMesAno] = number_format($dados['total'],2,",",".");