<?php
session_start();
$currentBaseDir = dirname($_SERVER['PHP_SELF']);
include($_SESSION['basedir']."core.php");

$svaTool = new svaTool();
$filenamexls = "";


$errorCode = 0;

$password = NULL;
$confPasswd = NULL;
$startDateTime = addslashes($_REQUEST['startDateTime']);
$endDateTime = addslashes($_REQUEST['endDateTime']);
$status = addslashes($_REQUEST['status']);


$res =  $dba->getBasicCallReasonStats( $_SESSION['right'], $status, $startDateTime, $endDateTime) ;
$total = count($res);
$totalTicketCount = $dba->getBasicCallReasonStatsCount( $_SESSION['right'], $status, $startDateTime, $endDateTime) ;

$reportres = $dba->getActivityReport( $_SESSION['right'], $startDateTime, $endDateTime, $status);

$search = array("-","_",":"," "); 

$sdatetime = str_replace($search,"",$startDateTime);
$edatetime = str_replace($search,"",$endDateTime);  


$filenamexls = "../../export/Rapport_activite_du_".$sdatetime."_".$edatetime."_".date("Ymdhis").".xls";
$filenamepdf = "../../export/Rapport_activite_du_".$sdatetime."_".$edatetime."_".date("Ymdhis").".pdf";  


$filenamexlsexp = "export/Rapport_activite_du_".$sdatetime."_".$edatetime."_".date("Ymdhis").".xls";
$filenamepdfexp = "export/Rapport_activite_du_".$sdatetime."_".$edatetime."_".date("Ymdhis").".pdf";  




$objPHPExcel = new PHPExcel();

$border_style= array('borders' => array('right' => array('style' => 
PHPExcel_Style_Border::BORDER_THICK,'color' => array('argb' => '766f6e'),)));

$sheet = $objPHPExcel->getActiveSheet();


$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('G1', 'HISTORIQUE DES APPELS HELP DESK DU '.$svaTool->getDateTemplateFr($sdatetime) ." AU ".$svaTool->getDateTemplateFr($edatetime)) 
            ->setCellValue('C3', 'Préocupation')
            ->setCellValue('D3', 'Nombre')
			->setCellValue('G3', 'Pourcentage (%)') ;


echo '<table border="1" width="100%">
    <tr>
        <td colspan="20" align="center">HISTORIQUE DES APPELS HELP DESK DU <b>'.$svaTool->getDateTemplateFr($sdatetime) .'</b> AU <b>'.$svaTool->getDateTemplateFr($edatetime).'</b></td>
        
    </tr>
    <tr>
        <td colspan="20" align="center">
        
      <table >
        
         
       <tr>
        <td> <a href="'.$filenamexlsexp.'">Télécharger</a> </td>       
       
       
      </tr>
      
       </table>
        


   </td>
        
    </tr>
    
    <tr>
         <td colspan="20" align="center">
             <table width="60%">
                 <tr>
                     <td width="40%" ><b>Préoccupation</b></td>
                     <td width="40%"><b>Nombre</b></td>
                     <td width="20%"><b>Pourcentage (%)</b></td>
                     
                 </tr>
           ';





        
			
$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true)->setSize(16)->setName('Candara');			
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D3:F3');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G3:K3');
$rowCount = 3;

$excludeMotifIds = "''";

for ($i = 0; $i < $total; $i++){
	
	if($i == 0 ) $excludeMotifIds = "";
	
	$rowCount++;
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $res[$i]['name']);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $res[$i]['total']);
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D'.$rowCount.':F'.$rowCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, round(($res[$i]['total']/$totalTicketCount)*100,2));
	
        
        echo '<tr>
                     <td>'.$res[$i]['name'].'</td>
                     <td>'.$res[$i]['total'].'</td>
                     <td>'.round(($res[$i]['total']/$totalTicketCount)*100,2).'</td>
                     
                 </tr>';
	
	
	//$excludeMotifIds = $excludeMotifIds."'".$res[$i]['name']."'";
	
	 $excludeMotifIds = $excludeMotifIds."'".$res[$i]['motif_id_n']."',";
    
	
}
$objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount.':R'.$rowCount)->getFont()->setBold(true)->setSize(11)->setName('Candara');







$excludeMotifIds = rtrim($excludeMotifIds,",");

$res =  $dba->getExcludeCallReasonStats( $excludeMotifIds) ;
$total = count($res);

for ($i = 0; $i < $total; $i++){
	$rowCount++;
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $res[$i]['name']);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, 0);
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D'.$rowCount.':F'.$rowCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, 0);
        
         echo '<tr>
                     <td>'.$res[$i]['name'].'</td>
                     <td>0</td>
                     <td>0</td>
                     
                 </tr>';
	
}


 $objPHPExcel->getActiveSheet()->getStyle('C4:C'.$rowCount)->getFont()->setBold(false)->setSize(12)->setName('Arial1');
 
 $objPHPExcel->getActiveSheet()->getStyle('D4:G'.$rowCount)->getFont()->setBold(false)
                                ->setName('Candara')
                                ->setSize(12);


    $rowCount++;
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, "Total Appels");
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $totalTicketCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, 100);
        
        echo '<tr>
                     <td><b>Total Appels<b></td>
                     <td><b style="color:red">'.$totalTicketCount.'</b></td>
                     <td><b style="color:red">100</b></td>
                     
                 </tr>
                 

           ';
        
        
        
  
	
	$objPHPExcel->getActiveSheet()->getStyle('D'.$rowCount.':G'.$rowCount)->getFont()->setBold(true)
                                ->setName('Candara')
                                ->setSize(14)
                                ->getColor()->setRGB('FF2626');
	
	$objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setBold(true)
                                ->setName('Arial1')
                                ->setSize(12);

	
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D'.$rowCount.':F'.$rowCount);
	
	 $objPHPExcel->getActiveSheet()
                ->getStyle('C3:G'.$rowCount)
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	
	$rowCount++;
	$rowCount++;
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B'.$rowCount.':C'.$rowCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, "Total Appels");
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $totalTicketCount);
	
	$objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount.':D'.$rowCount)->getFont()->setBold(true)
                                ->setName('Candara')
                                ->setSize(14)
                                ->getColor()->setRGB('FF2626');
        
          echo '
              <tr>
                     <td colspan="3">&nbsp;</td>
                    

                     
                 </tr>


                <tr>
                     <td rowspan="2">Total Appels</td>
                     <td colspan="2" rowspan="2">'.$totalTicketCount.'</td>

                     
                 </tr>
                 
                    <tr>
                     <td colspan="3">&nbsp;</td>
                    

                     
                 </tr>
                 
  <tr>
                     <td colspan="3">&nbsp;</td>
                    

                     
                 </tr>
                 
            </table>
             
             
             
         </td>
    </tr>
                 

           ';
        
        echo ' 
            <tr>
            
  <td colspan="20" align="center"> 
  
   <table  width="90%" border="1">
               <tr>
                     <td colspan="4">&nbsp;</td>

                     <td colspan="3" align="center"><b>Entrants</b></td>
                      
                      <td colspan="3" align="center"><b>Sortants</b></td>

                      
                     <td colspan="9">&nbsp;</td>

                     
                 </tr>
                 <tr>
                     <td><b>N°</b></td>
                     <td><b>Nom & Prénoms</b></td>
                     <td><b>Société </b></td>
                      <td><b>N°</b></td>
                     <td><b>Appels</b></td>
                     <td><b>E-mail </b></td>
                      <td><b>Skype</b></td>
                      
                      <td><b>Appels</b></td>
                     <td><b>E-mail </b></td>
                      <td><b>Skype</b></td>
                      
                     <td><b>Module</b></td>
                     <td><b>Préoccupation </b></td>
                      <td><b>Observation</b></td>
                     <td><b>E-mail</b></td>
                     <td><b>Numéro de téléphone </b></td>
                     
                     <td><b>Déclarant </b></td>
                      <td><b>Importateur/Exportateur</b></td>
                     <td><b>Heure d\'appel</b></td>
                     <td><b>Agent </b></td>
                     
                 </tr>
                 
          ';
	$rowCount++;
	$rowCount++;
	$rowCount++;
	$tblstart = $rowCount;
	$cdcmpt = $rowCount +1 ;
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, "N°");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$rowCount.':A'.$cdcmpt);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, "Nom & Prénoms");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B'.$rowCount.':B'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, "Société");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C'.$rowCount.':C'.$cdcmpt);
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('D'.$rowCount.':F'.$rowCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, "Entrants");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G'.$rowCount.':I'.$rowCount);
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, "Sortants");
	$objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount, "Modules");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('J'.$rowCount.':J'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowCount, "Préoccupation");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('K'.$rowCount.':K'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowCount, "Observation");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('L'.$rowCount.':L'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowCount, "E-mail");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('M'.$rowCount.':M'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowCount, "N° de Téléphone");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('N'.$rowCount.':N'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowCount, "Déclarant");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('O'.$rowCount.':O'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowCount, "Importateur/Exportateur");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('P'.$rowCount.':P'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowCount, "Heure d'appel");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('Q'.$rowCount.':Q'.$cdcmpt);
	$objPHPExcel->getActiveSheet()->SetCellValue('R'.$rowCount, "Agent");
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('R'.$rowCount.':R'.$cdcmpt);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':R'.$rowCount)->getFont()->setBold(true)->setSize(11)->setName('Candara');	
	
	$objPHPExcel->getActiveSheet()->getStyle('D'.$cdcmpt.':I'.$cdcmpt)->getFont()->setBold(true)->setSize(11)->setName('Candara');

	$rowCount++;
	$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, "Appel");
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, "E-mail");
	$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, "Skype");
	
	$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, "Appel");
	$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, "E-mail");
	$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, "Skype");
	$rowCount++;



$reportrescnt = count($reportres);
for ($i = 0; $i < $reportrescnt; $i++){
	
    
    echo '<tr> ';
    
    
                     
                    
                     
                 
	
	$rcount = $i+1;
	$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $rcount);
        echo '<td>'.$rcount.'</td>';
        
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $reportres[$i]['customer_name']);
    echo '<td>'.$reportres[$i]['customer_name'].'</td>';
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $reportres[$i]['company_name_v']);
	
        echo '<td>'.$reportres[$i]['company_name_v'].'</td>';
        
	if($reportres[$i]['call_direction_n'] == 0){
		
		if($reportres[$i]['call_type_v'] == "Appel"){
			
			$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, "X");
                        echo '<td>X</td>';
		}
		elseif($reportres[$i]['call_type_v'] == "Mail"){
			
			$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, "X");
                        echo '<td>X</td>';
			
		}
		else {
			
			$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, "X");
                        echo '<td>X</td>';
		}
		
		
		
	}
	else {
		
		if($reportres[$i]['call_type_v'] == "Appel"){
			
			$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, "X");
                        echo '<td>X</td>';
		}
		elseif($reportres[$i]['call_type_v'] == "Mail"){
			
			$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, "X");
                        echo '<td>X</td>';
			
		}
		else {
			
			$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, "X");
                        echo '<td>X</td>';
		}
		
		
	}
	
	
	$objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount, strtoupper($reportres[$i]['motif_name_v']));
        echo '<td>'.$reportres[$i]['motif_name_v'].'</td>';
	
	
	$objPHPExcel->getActiveSheet()->getStyle('J'.$rowCount)->getFont()->setBold(true)
                                ->setName('Century Schoolbook L')
                                ->setSize(14)
                                ->getColor()->setRGB('FF2626');
								
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
	$objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowCount, $reportres[$i]['customer_query']);
        echo '<td>'.$reportres[$i]['customer_query'].'</td>';
	$objPHPExcel->getActiveSheet()->SetCellValue('L'.$rowCount, $reportres[$i]['agent_response']);
        echo '<td>'.$reportres[$i]['agent_response'].'</td>';
	
	$objPHPExcel->getActiveSheet()->getStyle('K'.$rowCount.':L'.$rowCount)
    ->getAlignment()->setWrapText(true);
	
	
	$objPHPExcel->getActiveSheet()->SetCellValue('M'.$rowCount, $reportres[$i]['email_id_v']);
        echo '<td>'.$reportres[$i]['email_id_v'].'</td>';
	$objPHPExcel->getActiveSheet()->SetCellValue('N'.$rowCount, $reportres[$i]['contact']);
        echo '<td>'.$reportres[$i]['contact'].'</td>';
	
	if($reportres[$i]['type_v'] == 'Déclarant'){
		
		$objPHPExcel->getActiveSheet()->SetCellValue('O'.$rowCount, "X");
                echo '<td>X</td>';
	}
	
	if($reportres[$i]['type_v'] == 'Importateur / Exportateur'){
		
		$objPHPExcel->getActiveSheet()->SetCellValue('P'.$rowCount, "X");
                echo '<td>X</td>';
	}
	
	
	$objPHPExcel->getActiveSheet()->SetCellValue('Q'.$rowCount, $reportres[$i]['call_date']);
        echo '<td>'.$reportres[$i]['call_date'].'</td>';
	$objPHPExcel->getActiveSheet()->SetCellValue('R'.$rowCount, $reportres[$i]['agent_name']);
        echo '<td>'.$reportres[$i]['agent_name'].'</td>';
	
	
    $rowCount++;
	
    echo '</tr>';
}


  echo ' 
      
      <tr>
                     <td colspan="19">&nbsp;</td>
                    
                 </tr>
      



    </table>
  


  </td>
            

           </tr>
            
             
    
</table>';


$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);

 $objPHPExcel->getActiveSheet()
                ->getStyle('A'.$tblstart.':R'.$rowCount)
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);		
			
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('UserList');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel5)
/*
header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment;filename="userList.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified 
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
*/
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save($filenamexls);
/*
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save($filenamepdf);
*/



/*

//print_r($_REQUEST);
$res =  $dba->getCallReasonStats( $_SESSION['right'], $callreasonId, $startDateTime, $endDateTime) ;
$data = "";

 $cmpt = 1;
					 $total = count($res) ;
					 for ($i = 0; $i < $total; $i++) {
						$data = $data.
						        '{
									"Date": "'.$svaTool->escapeJsonString($res[$i]['eventDateTime']).'",
									"CallReason": "'.$svaTool->escapeJsonString($res[$i]['name']).'",
									"Total": "'.$res[$i]['total'].'"
								 }
					         	';
						 
						 if($i+1 <  $total ) $data = $data.",";
						 
					 }
					 
			echo "[$data]";
			
			*/

?>

