<?php

     date_default_timezone_set("Portugal");
     session_start();
     $per = explode(",", $_SESSION['password']);
     $idUser = $per[0];
     $nivelUser = $per[3];

     $currentDate=date('Y-m-d');
     $filename = "Leads_CalcioMais ".$currentDate;
   
     $con = mysqli_connect("localhost", "bluemark_OMHwp", "dtZtX8e9Xd1X", "bluemark_OMHwp")or die(json_encode("unconnected"));

     //coverte dados da tabela para utf8 devido aos acentos nos textos da tabela
     if (!mysqli_set_charset($con, "utf8")) 
     {
           json_encode("erro de caracter");
     } 

     //header info for browser
     header("Content-Type: application/xls");    
     header("Content-Disposition: attachment; filename=$filename.xls"); 
     header("Pragma: no-cache"); 
     header("Expires: 0");

     /*******Start of Formatting for Excel*******/   
     //define separator (defines columns in excel & tabs in word)
     $sep = "\t"; //tabbed character

     //start of printing column names as names of MySQL fields

     $ant= utf8_decode('Anotações');
     $vn= utf8_decode('Volume de Negócio');

     echo  "Email \t Nome \t Telefone \t Data Nascimento \t Data Hora Registo \t Estado \t Anotacoes \t Valor das vendas";
    
     echo "\n";
     //end of printing column names  

     //$result = mysqli_query($con,"SELECT * FROM LeadCampanha where Campanha='CalcioMais' and date(HorarioEnvio) >= date('2019-11-01')");
     $result = mysqli_query($con,"SELECT * FROM LeadCampanha Where Campanha='CalcioMais' UNION SELECT * FROM LeadCampanha_archive Where Campanha='CalcioMais'");

     while ($row = mysqli_fetch_assoc($result)) // mysqli_fetch_array($result, MYSQL_ASSOC)) 
     {
          $leads[]=$row;
     }


     // $result = mysqli_query($con,"select * from Leads_CalcioMais group by Email order by DataHoraRegisto desc");
     $result = mysqli_query($con,"select * from Leads_CalcioMais group by Email, Phone order by DataHoraRegisto desc");

     // while ($row = mysqli_fetch_assoc($result)) // mysqli_fetch_array($result, MYSQL_ASSOC)) 
     // {
     //      for($i=0;$i<sizeof($leads);$i++)
     //      {
     //            if($leads[$i]['Lead']==$row['Email'])
     //            {
     //                 $row['DataHoraRegisto']=$leads[$i]['HorarioEnvio'];
     //                 $res[]=$row;
     //            }
     //      }
     // }

     while ($row = mysqli_fetch_assoc($result)) // mysqli_fetch_array($result, MYSQL_ASSOC)) 
     {
          for($i=0;$i<sizeof($leads);$i++)
          {
               if($leads[$i]['Lead']==$row['Email'] && $leads[$i]['DataHoraRegisto']==$row['DataHoraRegisto'])
               {
                    $row['DataHoraRegisto']=$leads[$i]['HorarioEnvio'];
               }
          }

          //tirar testes
          if($row['FName'] == 'prueba' || $row['FName'] == 'pruebita') {
               continue;
          }

          //tirar aqueles que não têm número
          if(empty($row['Phone'])) {
               continue;
          }

          $res[]=$row;
     }

     for($i=0;$i<sizeof($res);$i++)
     {
          //$result = mysqli_query($con,"select * from LM_CalcioMais_History where Lead='".$res[$i]['Email']."' order by DataHoraRegisto desc ");
          $result = mysqli_query($con,"select * from LM_CalcioMais_History where LeadID='".$res[$i]['ID']."' order by DataHoraRegisto desc ");

          $row = mysqli_fetch_assoc($result);
          $res[$i]['FName'] = utf8_decode($res[$i]['FName']);
          $res[$i]['LName'] = utf8_decode($res[$i]['LName']);
          $res[$i]['ValorVenda'] = utf8_decode($row['ValorVenda']);
          $res[$i]['Anotacoes'] = utf8_decode($row['Anotacoes']);
          $res[$i]['Estado'] = utf8_decode($row['Estado']);

          if($nivelUser==2  || $res[$i]['Estado']=='Recebido' || ($res[$i]['Estado']!='Recebido' && $idUser==$res[$i]['UserID'] ))
          {
                echo $res[$i]['Email']."\t".$res[$i]['FName']." ".$res[$i]['LName']."\t".$res[$i]['Phone']."\t".$res[$i]['BData']."\t".$res[$i]['DataHoraRegisto']."\t".$res[$i]['Estado']."\t".$res[$i]['Anotacoes']."\t".$res[$i]['ValorVenda'];

                echo "\n";
          }

     }


     mysqli_close($con);

?>