<?php 
/*
*
* GENERATION CSV COREGISTRATION
*
*/

//dev
$PARAM_hote 		= 'localhost';
$PARAM_port 		= '5434';
$PARAM_nom_bd 		= 'fl-network';
$PARAM_utilisateur 	= 'postgres';
$PARAM_mot_passe	= 'passsql'; 
$mail_dev 		= 'simon.garny@flinteractive.fr';
$headers 		= 'From: dev csv <root@flinteractive.fr>;';
$headers		.= 'Content-Type: text/plain; charset="utf-8";';

//prod
$PARAM_port             = '5432';
$PARAM_mot_passe        = 'SQL1281';

try{
	$db = new PDO('pgsql:host='.$PARAM_hote.';port='.$PARAM_port.';dbname='.$PARAM_nom_bd, $PARAM_utilisateur, $PARAM_mot_passe);
}
catch(Exception $e){
	die(date('Y-m-d H:i:s')."-> erreur connexion BD.\n");
}

//inclusion de PHPMailer 
include('/usr/share/php/libphp-phpmailer/class.phpmailer.php');

//On recupere l'ID de campagne
if(!isset($argv[1])){
	die(date('Y-m-d H:i:s')."-> numero campagne manquant\n");

}elseif(sizeof($argv) > 2){
	die(date('Y-m-d H:i:s')."-> trop de numeros\n");
}else{
	$id_campagne = $argv[1];	
}

echo date('Y-m-d H:i:s')."-> start centralisation coreg ".$id_campagne." \n";

//On vérifie qu'il n'y ai pas déjà une execution précédente avec cette campagne
$sql = "SELECT journalisation_csv_id_campagne, journalisation_csv_heure_debut_execution,journalisation_csv_heure_fin_execution, journalisation_csv_chemin, journalisation_csv_etat
        FROM coregistration.journalisation_csv
        WHERE journalisation_csv_id_campagne = ?
        AND journalisation_csv_etat = 0
	ORDER BY journalisation_csv_heure_debut_execution DESC
	LIMIT 1;";
$res = $db->prepare($sql);
$res->execute(array($id_campagne));
$execution = $res->fetch();

//if($execution){
        //mail($mail_dev, 'Probleme execution csv campagne coreg '.$id_campagne, 'Un script d\'envoi de csv coregistration pour la campagne n°'.$id_campagne.' a deja ete lance le '.$execution['journalisation_csv_heure_debut_execution'],$headers);
        //die(date('Y-m-d H:i:s')."-> script ID ".$id_campagne." deja lance\n");
//}

//On vérifie que la campagne existe bien et que le parametrage est pour le CSV
	$sql = "SELECT campagne_annonceur_id, campagne_annonceur_nom,  campagne_annonceur_type_envoi, campagne_annonceur_jour_envoi, campagne_annonceur_heure_envoi, campagne_annonceur_destinataires, campagne_annonceur_nom_fichier, delimiteur_caractere, separateur_caractere, annonceur_societe, campagne_annonceur_quota, campagne_annonceur_volume_en_cours, campagne_annonceur_quota_quotidien
	FROM coregistration.campagne_annonceur
	INNER JOIN general.delimiteur ON delimiteur_id = campagne_annonceur_delimiteur
	INNER JOIN general.separateur ON separateur_id = campagne_annonceur_separateur
	INNER JOIN general.annonceur ON annonceur_id = campagne_annonceur_annonceur
	WHERE campagne_annonceur_id = ?
	AND campagne_annonceur_type_envoi = 'csv'
	AND campagne_annonceur_active IS TRUE;";

$res = $db->prepare($sql);
$res->execute(array($id_campagne));
$campagne = $res->fetch();

if(!$campagne) {
	die(date('Y-m-d H:i:s')."-> campagne inexistante\n");
}

$dir_path = "/var/scripts/flnetwork_coregistration/csv/travail/";
if(!is_dir($dir_path.$id_campagne)){

	$result = mkdir($dir_path.$id_campagne,0777); 
	if($result==1){
		echo date('Y-m-d H:i:s')."-> dossier ".$dir_path.$id_campagne." cree.\n";
	} else {
		die(date('Y-m-d H:i:s')."-> probleme creation dossier ".$dir_path.$id_campagne.".\n");
	}
}

//On commence l'execution du script en inserant dans le journal
$debut_execution = date('Y-m-d H:i:s');
$sql = "INSERT INTO coregistration.journalisation_csv
        VALUES (?,?,?,?,?)";
$res = $db->prepare($sql);
//$res->execute(array($id_campagne, $debut_execution, null, '',0));

//On recupere les parametres de la campagne
$nom_fichier_csv 	= $campagne['campagne_annonceur_nom_fichier'];
$jour_envoi		= $campagne['campagne_annonceur_jour_envoi'];
$heure_envoi		= $campagne['campagne_annonceur_heure_envoi'];
$destinataires		= $campagne['campagne_annonceur_destinataires'];
$delimiteur 		= $campagne['delimiteur_caractere'];
$separateur 		= $campagne['separateur_caractere'];
$nom_campagne		= $campagne['annonceur_societe'];
$nom_annonceur		= $campagne['campagne_annonceur_nom'];

$path_modifie = null;

$date_fin = '2016-04-12';//date("Y-m-d");
$date_debut = '2016-04-10';

//On calcule la fourchette de temps
/*if($jour_envoi == 0){

	//Si on envoie tous les jours de la semaine
	$date_debut = date('Y-m-d', time() - 86400);

}else{
	//Si on envoie une fois par semaine
	$date_debut = date('Y-m-d', time() - 86400 * 7);
}*/

//quota quotidien
$limit = 9999;
if ($campagne['campagne_annonceur_quota_quotidien'] > -1) {
	$limit = $campagne['campagne_annonceur_quota_quotidien'];
}

//Recuperation de l'ensemble des profils pour une campagne donnee
$sql="SELECT coregistration_campagne_annonceur, coregistration_affichage_date, coregistration_coregistration_affichage, site_nom,
      array_to_string(array_agg(case when champ_libelle = 'date_naissance' then 'jour§mois§annee' else champ_libelle end order by coregistration_champ), '§') as coreg_libelles,
      array_to_string(array_agg(coregistration_champ order by coregistration_champ), '§') as champs_coreg,
      array_to_string(array_agg(case when champ_libelle = 'date_naissance' then concat(	
			            EXTRACT(day FROM general.cast_to_date(coregistration_valeur)), '§',
                                    EXTRACT(month FROM general.cast_to_date(coregistration_valeur)), '§',
                                    EXTRACT(year FROM general.cast_to_date(coregistration_valeur))
				)
		else coregistration_valeur 
		end 
		order by coregistration_champ), '§') as valeurs_coreg
     FROM coregistration.coregistration
                LEFT JOIN coregistration.champ ON coregistration_champ = champ_id
                INNER JOIN coregistration.coregistration_affichage ON coregistration_affichage_id = coregistration_coregistration_affichage
                INNER JOIN coregistration.campagne_postulation ON campagne_postulation_annonceur = ? AND campagne_postulation_site = coregistration_affichage_site
                INNER JOIN general.site ON site_id = coregistration_affichage_site
                WHERE coregistration_campagne_annonceur = ?         
		AND coregistration_affichage_date BETWEEN ? AND ?
                AND coregistration_eligible = 6
                AND campagne_postulation_production = TRUE
                AND coregistration_affichage_production = TRUE
                AND coregistration_coregistration_affichage > campagne_postulation_id_test
    GROUP BY coregistration.coregistration_campagne_annonceur, coregistration.coregistration_coregistration_affichage,coregistration_affichage_date,site.site_nom
    ORDER BY coregistration_coregistration_affichage DESC
    LIMIT $limit;";
$res = $db->prepare($sql);
$res->execute(array($id_campagne, $id_campagne, $date_debut, $date_fin));
//$res->execute(array($id_campagne, $id_campagne));
$profils = $res->fetchAll();

$nbr_profils = 0;

if(sizeof($profils)>0){

	//On remplace les marqueurs par leur valeurs dans le nom du fichier
	$search_nom_fichier = array('FLI:MOIS_ENVOI','FLI:ANNEE_ENVOI','FLI:JOUR_ENVOI','FLI:NOM_CAMPAGNE','FLI:NOM_ANNONCEUR');
	$replace_nom_fichier = array(date("m"), date("Y"), date("d"), $nom_campagne, $nom_annonceur);

	$nom_fichier_csv_annonceur = str_replace($search_nom_fichier, $replace_nom_fichier, $nom_fichier_csv);
		
	//on cree un fichier extension temp
	$nom_fichier_csv = $id_campagne.'.temp';
	
	//Permet de recuperer les blocs pour le CSV
	$sql = "SELECT campagne_csv_id_campagne, campagne_csv_intitule, campagne_csv_nom, campagne_csv_ordre
		FROM coregistration.campagne_csv
		WHERE campagne_csv_id_campagne = ?
		ORDER BY campagne_csv_ordre;";

	$res = $db->prepare($sql);
	$res->execute(array($id_campagne));
	$blocs_csv = $res->fetchAll();
	
	if(sizeof($blocs_csv > 0)){

		$headers = '';
		$pattern = '';
		$blocs = array();
		foreach($blocs_csv as $bloc){

			$headers .= $delimiteur.$bloc['campagne_csv_intitule'].$delimiteur.$separateur;
			$pattern .= $delimiteur.$bloc['campagne_csv_nom'].$delimiteur.$separateur; 
			$blocs[] = $bloc['campagne_csv_nom'];
		}

		$headers = substr($headers,0,-strlen($separateur))."\n"; 
		$pattern = substr($pattern,0,-strlen($separateur))."\n";
	
		$path_fichier = $dir_path.$id_campagne."/".$nom_fichier_csv;	
		$fp = fopen($path_fichier, 'w');

		
		$replace_csv = array('email', 'prenom', 'nom', 'civilite', 'adresse', 'code_postal', 'ville', 'pays', 'telephone', 'jour', 'mois', 'annee', 'profession', 'ip');
		$search_csv = array('FLI:EMAIL', 'FLI:PRENOM', 'FLI:NOM', 'FLI:CIVILITE', 'FLI:ADRESSE', 'FLI:CODE_POSTAL', 'FLI:VILLE', 'FLI:PAYS', 'FLI:TELEPHONE', 'FLI:JOUR','FLI:MOIS', 'FLI:ANNEE', 'FLI:PROFESSION', 'FLI:IP'); 	
		
		$liste_valeurs_questions = '';
		$liste_valeurs = '';
		$nb_profils = 0;
		
		foreach($profils as $profil) {
			//On recupere l'ensemble des questions et reponses 
			$sql = "select coregistration_reponse_affichage, concat('FLI:SQ',question_id) as question_id,
				array_to_string(array_agg(coregistration_reponse_id_reponse), ',') || '' as coregistration_reponse_id_reponse,
				array_to_string(array_agg(question_type order by question_id), ',') || '' as question_type,
				array_to_string(array_agg(reponse_valeur order by question_id), ',') || '' as reponse_valeur
				from (
					select coregistration_reponse_affichage, question_id, coregistration_reponse_id_reponse, question_type,
					case when question_type = 'text' then coregistration_reponse_id_reponse
					when question_type = 'telephone' then coregistration_reponse_id_reponse
					else reponse_valeur
					end as reponse_valeur
					from coregistration_reponse cr
					join question q on q.question_id = cr.coregistration_reponse_id_question
					left join reponse r on cast(r.reponse_id as character varying) = coregistration_reponse_id_reponse
					where coregistration_reponse_affichage = ?
					group by question_id, coregistration_reponse_affichage, cr.coregistration_reponse_id_reponse, r.reponse_valeur 
					order by question_id
				) x group by coregistration_reponse_affichage,question_id;"; 

			$res = $db->prepare($sql);
			$res->execute(array($profil['coregistration_coregistration_affichage']));
			$res_fetch = $res->fetchAll();
			
			$array = array();
			foreach($res_fetch as $res){

				$id = $res['question_id'];
				$array[$id] = $res['reponse_valeur'];	
					
			}

			$coreg_lib = explode("§", $profil['coreg_libelles']);
			$coreg_val = explode("§", $profil['valeurs_coreg']);	

			$liste_fli = str_replace($replace_csv, $search_csv, $coreg_lib);
			$liste_courant = str_replace($liste_fli, $coreg_val, $pattern);
			$liste_valeurs_questions = str_replace(array_keys($array), $array, $liste_courant); 
			
			$liste_valeurs .= $liste_valeurs_questions;

			//gestion date collecte
			$date_collecte = strtotime($profil['coregistration_affichage_date']);
			$liste_fi = str_replace(
				array('FLI:COLLECTE_ANNEE', 'FLI:COLLECTE_MOIS', 'FLI:COLLECTE_JOUR', 'FLI:COLLECTE_HEURE', 'FLI:COLLECTE_MINUTE', 'FLI:COLLECTE_SECONDE'),
				array(date('Y', $date_collecte), date('m', $date_collecte), date('d', $date_collecte), date('H', $date_collecte), date('i', $date_collecte), date('s', $date_collecte)),
				$liste_valeurs
			);
			echo date('Y-m-d H:i:s')."-> profil ID: ".$profil['coregistration_coregistration_affichage']." ajoute\n";
			$nbr_profils ++;
		}
	
		fwrite($fp, utf8_encode($headers.$liste_fi));
		fclose($fp);
		echo date('Y-m-d H:i:s')."-> ".$nbr_profils." profils recuperes\n";

		//on prepare l'envoi du mail
		$data = array(
			'destinataires' => 'simon.garny@flinteractive.fr',//$destinataires,
			'sujet' 	=> 'FLInteractive - coreg CSV campagne '.$nom_annonceur,
			'message' 	=> "Bonjour, <br/><br/>
				Vous trouverez ci-joint le fichier csv pour la campagne <strong>".$nom_annonceur."</strong> sur la p&eacute;riode du <strong>".date("Y-m-d",strtotime($date_debut))."</strong> au <strong>".$date_fin."</strong> exclu.<br/>
				Nombre de profils collect&eacute;s: <strong>".$nbr_profils."</strong><br/><br/>
  				Cordialement. <br/><br/>",

			'path_fichier'	=> $path_fichier,
			'fichier_temp'	=> $nom_fichier_csv,
			'nom_fichier'	=> $nom_fichier_csv_annonceur
		);

		//on envoie le mail
		if(envoyer_mail($data)){

			echo date('Y-m-d H:i:s')."-> mail (ID: ".$id_campagne."; DESTS: ".$data['destinataires'].")\n";
			
			//on renomme le fichier .temp en .csv
			$path_modifie = "/var/scripts/flnetwork_coregistration/csv/travail/".$id_campagne."/".date("Y_m_d_H_i_s_").$nom_fichier_csv_annonceur;
			rename($path_fichier, $path_modifie);
			echo date('Y-m-d H:i:s')."-> ".$path_modifie." enregistre\n";

		}else{
			mail($mail_dev, 'Probleme envoi mail CSV', 'L\'envoi du mail pour le CSV de la campagne '.$id_campagne.' n\'a pas fonctionne.',$headers); 
			echo date('Y-m-d H:i:s')."-> echec mail campagne n°".$id_campagne."\n";
		}

	}else{
		mail($mail_dev, 'Problème blocs CSV', 'Les blocs pour l\'envoi du CSV de la campagne n°'.$id_campagne.' ne sont pas corrects.', $headers);
		echo date('Y-m-d H:i:s')."-> probleme bloc csv campagne n°".$id_campagne."\n";
	}	
	
}else{
 	mail($mail_dev, 'Problème nombre profils', 'Aucun profil collecte (campagne '.$nom_annonceur.' avec le n°'.$id_campagne.').', $headers);
	echo date('Y-m-d H:i:s')."-> aucun profil campagne n°".$id_campagne.".\n";
}	

//On met a jour la journalisation avec le chemin et l'etat
$fin_execution = date('Y-m-d H:i:s');
$sql = "UPDATE coregistration.journalisation_csv
	SET journalisation_csv_etat = 1, journalisation_csv_heure_fin_execution = ?, journalisation_csv_chemin = ?
	WHERE journalisation_csv_id_campagne = ?
	AND journalisation_csv_heure_debut_execution = ?
	AND journalisation_csv_etat = 0;";
$res = $db->prepare($sql);
$res->execute(array($fin_execution, $path_modifie, $id_campagne, $debut_execution));
echo date('Y-m-d H:i:s')."-> fin centralisation coreg ".$id_campagne." \n";


function envoyer_mail($data){	
	
	$mail = new PHPMailer();
	$mail->IsHTML(true);
	$mail->Charset = "UTF-8";

	//expediteur
	$mail->From = 'livraison@flinteractive.fr';
	$mail->FromName = 'FLInteractive CSV';

	$mail->SMTPDebug = false;

	//destinataires
	$dests = trim($data['destinataires']);
	$liste_dests = explode(';', $dests);
	
	foreach($liste_dests as $dest){
		$mail->AddAddress($dest);
	}
		
	//autres destinataires
	$mail->AddAddress('simon.garny@flinteractive.fr');

	//sujet
	$mail->Subject = $data['sujet'];

	//corps
	$mail->Body = $data['message'];

	//ajout du fichier en pièce jointe
	$mail->AddAttachment($data['path_fichier'], $data['nom_fichier']);

	//envoi mail et test
	if(!$mail->Send()){
		//erreur
		return false;
	}

	return true;
	
}
?>