<?php 
header('Content-Type: application/json');
include("../../includes/connect.php");

/* query per export in csv 

INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

*/

$inizio = $_POST['start'];
$quanti = $_POST['length'];

$idCampaign = $_POST['campaignId'];
$tabellaLeads = "campaign" . $idCampaign . "_leads";
$tabellaAnwers = "campaign" . $idCampaign . "_user_answers";



// costruisco la query di ricerca

if($_POST['applyFilter'] != 0)
{

$join = "INNER JOIN $tabellaAnwers ON $tabellaLeads" . ".externalId = $tabellaAnwers" . ".externalId AND ";
$condizioni = "";

//echo $join; 
for($f = 0; $f < count($_POST['filterCount']); $f++)
{
if($_POST["filter$f"]['aId'] != 0)
{

$join .= $tabellaAnwers . ".questionId = " . $_POST["filter$f"]['qId'] . " AND $tabellaAnwers" . ".answerId = " . $_POST["filter$f"]['aId'];	

}
}
}
else
{
	$join = "";
	$condizioni = " WHERE 1";
}


if($_POST['searchByGender'])
{
	$condizioni .= " AND LOWER($tabellaLeads" . ".sesso) ='" . strtolower($_POST['searchByGender']) . "'";
}

if($_POST['searchCapFrom'])
{
	$condizioni .= " AND $tabellaLeads" . ".cap >= " . $_POST['searchCapFrom'];
}

if($_POST['searchCapTo'])
{
	$condizioni .= " AND $tabellaLeads" . ".cap <= " . $_POST['searchCapTo'];
}

if($_POST['searchByCity'])
{
	$condizioni .= " AND $tabellaLeads" . ".citta LIKE '%" . $_POST['searchByCity'] . "'";
}


//echo "SELECT * FROM $tabellaLeads $join $condizioni ORDER BY timestamp DESC LIMIT $inizio,$quanti";
$leadList = $connection->query("SELECT * FROM $tabellaLeads $join $condizioni ORDER BY timestamp DESC LIMIT $inizio,$quanti");
$leads = $leadList->fetchAll();

if($_POST['applyFilter'] != 0)
{
 $leadCount = $connection->query("SELECT COUNT(*) FROM $tabellaLeads $join $condizioni"); 
$leadCount = $leadCount->fetchColumn();
}
else
{
  $leadCount = $connection->query("SELECT COUNT(*) FROM $tabellaLeads $condizioni");
  $leadCount = $leadCount->fetchColumn();

}
$meta = $connection->query("SELECT meta_key,meta_value FROM campaigns_meta WHERE campaign_id = '$idCampaign' UNION ALL select 'txtLang' as meta_key,code as meta_value from languages where id=(select meta_value from campaigns_meta where campaign_id='$campaignid' and meta_key='language');");
$campaignMeta = $meta->fetchAll(PDO::FETCH_KEY_PAIR);


// domande pre-form
$questionSet = $campaignMeta['questionset'];
// domande post-form
$profilationSet = $campaignMeta['questionset_after'];


if($profilationSet != "")
{
	$profilationQ = $connection->query("SELECT id,text FROM profilation_questions WHERE sets_in = '$profilationSet'");
	$profilationQuestions = $profilationQ->fetchAll(PDO::FETCH_ASSOC);
	//print_r($profilationQuestions);

	$profilationA = $connection->query("SELECT question_id,id,text FROM profilation_answers WHERE sets_in = '$profilationSet'");
	$profilationAnswers = $profilationA->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
//print_r($profilationAnswers);
}
else
{
	$profilationQuestions = array();
}
?>
{
  "draw": <?php echo $_POST['draw']; ?>,
  "recordsTotal": <?php echo $leadCount ?>,
  "recordsFiltered": <?php echo $leadCount ?>,
  "data": [
  <?php 
  for($l = 0; $l < count($leads); $l++)
  {
  	$id = $leads[$l]['id'];
  	$externalId = explode("-",$leads[$l]['externalid'])[0];
  	// se la campagna ha la profilazione attiva cerco le risposte alle domande 

  	if($profilationSet != "")
  	{
  		$answersTable = "campaign" . $idCampaign . "_user_answers";
  		$userAnswers = $connection->query("SELECT questionId,answerId FROM $answersTable WHERE externalId = '$externalId'");
  		$userAnswers = $userAnswers->fetchAll(PDO::FETCH_KEY_PAIR);
  	}
  	?>

    [
      "<?php echo $leads[$l]['nome']; ?>",
      "<?php echo $leads[$l]['cognome']; ?>",
      "<?php echo $leads[$l]['sesso']; ?>",	
      "<?php echo $leads[$l]['nascita']; ?>",
      "<?php echo $leads[$l]['email']; ?>",	
      "<?php echo $leads[$l]['cellulare']; ?>",
      "<?php echo $leads[$l]['cap']; ?>",	
      "<?php echo $leads[$l]['citta']; ?>",	
      "<?php echo $leads[$l]['indirizzo']; ?>",
      "<?php echo $leads[$l]['civico']; ?>",	
      "<?php echo $leads[$l]['privacy']; ?>"<?php if(count($profilationQuestions) > 0){?>,<?php }?>
      <?php // genero le colonne che verranno poi nascoste per la ricerca
      for($pq = 0; $pq < count($profilationQuestions); $pq++)
      {
      	$questionId = $profilationQuestions[$pq]['id'];

      	if(isset($userAnswers[$questionId]))
      	{ 
      		echo "\"$userAnswers[$questionId]\""; 
      	}else
      	{
      		echo "\"N.D.\"";
      	}
      	if($pq < count($profilationQuestions)-1){?>,<?php } 
      }
  ?>
]<?php	if($l < count($leads)-1){?>,<?php }
											}
?>
]
}