I have a report in excel, how do I extract by date?

0

I have a report in excel that does a search in the DB and brings all the information.

How do I put in form in php the user enter the date and search in the BD just between those dates and mount the excel?

My code looks like this:

 $arquivo = 'relatorio_de_vendas_geral.xls';
 $tabela = '<table border="1">';
 $tabela .= '<tr>';
 $tabela .= '</tr>';
 $tabela .= '<tr>';
 $tabela .= '<td><b>Cliente</b></td>';
 $tabela .= '<td><b>Usuário</b></td>';
 $tabela .= '<td><b>Data</b></td>';
 $tabela .= '<td><b>Objeto</b></td>';
 $tabela .= '<td><b>Entrega</b></td>';
 $tabela .= '<td><b>Produto</b></td>';
 $tabela .= '<td><b>Quantidade</b></td>';
 $tabela .= '<td><b>Unitário</b></td>';
 $tabela .= '<td><b>Total</b></td>';
 $tabela .= '<td><b>Frete</b></td>';
 $tabela .= '<td><b>Frete Real</b></td>';
 $tabela .= '<td><b>NF</b></td>';
 $tabela .= '<td><b>Local</b></td>';
 $tabela .= '<td><b>Forma de Pagamento</b></td>';
 $tabela .= '<td><b>Observação</b></td>';
 $tabela .= '<td><b>ID do Registro</b></td>';
 $tabela .= '</tr>';
 $resultado = mysql_query("
SELECT 
  c.id,
  c.clienteVa cliente,
  c.userml,
  c.dtConcat,
  c.rastreador,
  c.entrega,
  c.nf,
  c.tipo_pagto,
  c.descricao,
  c.frete,
  c.frete_real,
  c.origem,
  p.nome,
  d.valor_unid,
  d.qtd,
  d.valor_total
FROM 
  'lc_controle' c
INNER JOIN 'lc_detalhe' d
  ON d.controle_id = c.id
INNER JOIN 'lc_cat' p
  ON p.id = d.cat_id  
WHERE
  c.ano = 2017  AND
  c.tipo = 0    AND
  c.clienteAt = 0
UNION ALL 
SELECT 
  c.id,
  cli.nome cliente,
  c.userml,
  c.dtConcat,
  c.rastreador,
  c.entrega,
  c.nf,
  c.tipo_pagto,
  c.descricao,
  c.frete,
  c.frete_real,
  c.origem,
  p.nome,
  d.valor_unid,
  d.qtd,
  d.valor_total
FROM 
  'lc_controle' c
INNER JOIN 'lc_detalhe' d
  ON d.controle_id = c.id
INNER JOIN 'lc_cat' p
  ON p.id = d.cat_id
INNER JOIN 'clientes' cli
  ON cli.id = c.clienteAt     
WHERE
  c.ano = 2017  AND
  c.tipo = 0    AND
  c.clienteAt <> 0
  ORDER BY dtConcat ASC
");
 while($dados = mysql_fetch_array($resultado))
 {
  $tabela .= '<tr>';
  $tabela .= '<td>'.$dados['cliente'].'</td>';
  $tabela .= '<td>'.$dados['userml'].'</td>';
  $tabela .= '<td>'.$dados["dtConcat"];
  $tabela .= '<td>'.$dados['rastreador'].'</td>';
  $tabela .= '<td>'.$dados['entrega'].'</td>';
  $tabela .= '<td>'.$dados['nome'].'</td>';
  $tabela .= '<td>'.$dados['qtd'].'</td>';
  $tabela .= '<td>'.$dados['valor_unid'] = number_format($dados['valor_unid'], 2, ',', '.').'</td>';
  $tabela .= '<td>'.$dados['valor_total'] = number_format($dados['valor_total'], 2, ',', '.').'</td>';
  $tabela .= '<td>'.$dados['frete'] = number_format($dados['frete'], 2, ',', '.').'</td>';
  $tabela .= '<td>'.$dados['frete_real'] = number_format($dados['frete_real'], 2, ',', '.').'</td>';
  $tabela .= '<td>'.$dados['nf'].'</td>';
  $tabela .= '<td>'.$dados['origem'].'</td>';
  $tabela .= '<td>'.$dados['tipo_pagto'].'</td>';
  $tabela .= '<td>'.$dados['descricao'].'</td>';
  $tabela .= '<td>'.$dados['id'].'</td>';
  $tabela .= '</tr>';
 }
 $tabela .= '</table>';
    
asked by anonymous 15.12.2017 / 15:02

1 answer

1

To resolve the issue, you must have, in your form, fields to inform the period:

Once you have done this, receive these fields in PHP and add them to your query:

"... and dtConcat between '$ datainicial' and '$ dataFinal'"

Do not forget to convert the received date into a valid format for the field type in your table.

My response is based on whether your date field is of type Date or Timestamp.

A pretty good tip

Do not use mysql_query. Prefer to use lib mysqli or PDO, for security measure of your program.

    
15.12.2017 / 17:03