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

     $currentDate=date('Y-m-d');
     $filename = "Leads_MariaHelena ".$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 Genero \t Telefone \t Data Nascimento \t Desejo \t Horario Contatar\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='MariaHelena' and Resposta='Email Enviado' and date(HorarioEnvio) >= date('2015-01-01')");
     $result = mysqli_query($con,"SELECT * FROM LeadCampanha where Campanha='MariaHelena' and date(HorarioEnvio) >= date('2015-01-01')");

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


     // $result = mysqli_query($con,"select * from OMH_registosLaura where TELEFONE!='' and Horario!='' group by EMAIL order by DataHoraRegisto desc");
     $Sql = "
          SELECT * FROM (
               select * from OMH_registosLaura where TELEFONE!='' and Horario!='' order by DataHoraRegisto desc) t
          GROUP BY t.EMAIL
     ";
     $result = mysqli_query($con,$Sql);

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

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

          // $row = mysqli_fetch_array($result, MYSQL_ASSOC);
          $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']);
          $res[$i]['DESEJO'] = utf8_decode($row['DESEJO']);

          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]['GENDER']."\t".$res[$i]['TELEFONE']."\t".$res[$i]['BDATE']."\t".$res[$i]['DESEJO']."\t".$res[$i]['Horario']."\t".$res[$i]['dataHoraRegisto']."\t".$res[$i]['Estado']."\t".$res[$i]['Anotacoes']."\t".$res[$i]['ValorVenda'];

                echo "\n";
          }

     }


     mysqli_close($con);

?>