I have an excel report, how do I extract by date?

Asked

Viewed 45 times

0

I have an excel report that does a BD search and brings all the information.

how do I put in the form in php the user inform the date and search in the BD only between those dates and mount excel?

my Cód is 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>';
php mysql

1 answer

1

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

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

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

Don’t forget to convert the received date into a valid format for the field type in your table.

My answer is based on the possibility that your date field is of the Date or Timestamp type.

A very valid tip

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

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.