<?php
//////////// Check Inclusion de pages ////////////
if (!function_exists('AuthenthificationProcess')){
	include_once("../lib/lib_common.php");
	ReloadIndex('admin');
}
////////////

$req_quickbox_add = "SELECT newsletter_id, substring(newsletter_title from 1 for 50) as newsletter_title, to_char(newsletter_date_crea, 'DD-MM-YYYY à HH24:MI:SS') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut  = 'P' OR newsletter_statut = 'D'  ORDER BY newsletter_date_crea DESC OFFSET 0 LIMIT 10;";
$req_quickbox_sup = "SELECT newsletter_id,  substring(newsletter_title from 1 for 50) as newsletter_title, to_char(newsletter_last_modify, 'DD-MM-YYYY à HH24:MI:SS') AS newsletter_last_modify_display, to_char(newsletter_date_crea, 'DD-MM-YYYY') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut = 'E' ORDER BY newsletter_last_modify DESC OFFSET 0 LIMIT 10;";
$req_quickbox_mod = "SELECT newsletter_id, substring(newsletter_title from 1 for 50) as newsletter_title, to_char(newsletter_last_modify, 'DD-MM-YYYY à HH24:MI:SS') AS newsletter_last_modify_display, to_char(newsletter_date_crea, 'DD-MM-YYYY') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut  <> 'E' ORDER BY newsletter_last_modify DESC OFFSET 0 LIMIT 10;";
$req_quickbox_list = "SELECT newsletter_id,  substring(newsletter_title from 1 for 50) as newsletter_title, to_char(newsletter_published_date, 'DD-MM-YYYY à HH24:MI:SS') AS newsletter_published_date_display, to_char(newsletter_date_crea, 'DD-MM-YYYY') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut = 'P' ORDER BY newsletter_published_date DESC OFFSET 0 LIMIT 10;";


function SQL_getoneCompleteNewsLetter($newsletter_id) {
	$requete =	"SELECT newsletter_title, newsletter_body, newsletter_statut, newsletter_posted_by, ".
				"user_login, ".
				"to_char(newsletter_date_crea, 'DD-MM-YYYY') AS newsletter_date_crea_display, ".
				"to_char(newsletter_last_modify, 'DD-MM-YYYY à HH24:MI:SS') AS newsletter_last_modify_display, ".
				"to_char(newsletter_published_date, 'DD-MM-YYYY') AS newsletter_published_date_display ".
				"FROM " . T_NEWSLETTER . " ".
				"LEFT OUTER JOIN ".T_USER." on newsletter_posted_by=user_id ".
				"WHERE newsletter_id='".$newsletter_id."' AND newsletter_statut<>'N';";
				#echo($requete);
	return $requete;
}


function SQL_getNewsletterList($debut, $limite, $statut) {
	switch ($statut) {
		case 'public':
			$mask=" WHERE newsletter_statut = 'P'";
			$orderby="newsletter_published_date DESC";
			break;
		case 'draft':
			$mask=" WHERE newsletter_statut = 'D'";
			$orderby="newsletter_date_crea DESC";
			break;
		case 'waiting':
			$mask=" WHERE newsletter_statut = 'W'";
			$orderby="newsletter_date_crea DESC";
			break;
		default:
			$mask=" WHERE newsletter_statut <> 'E'";
			$orderby="newsletter_date_crea DESC";
			break;
		}
	$requete= "SELECT newsletter_id, substring(newsletter_title from 1 for 60) AS newsletter_title, ".
			  "newsletter_statut, newsletter_posted_by, user_login, ".
			  "to_char(newsletter_date_crea, 'DD-MM-YYYY') AS newsletter_date_crea_display, ".
			  "to_char(newsletter_published_date, 'DD-MM-YYYY') AS newsletter_published_date_display, ".
			  "to_char(newsletter_last_modify, 'DD-MM-YYYY') AS newsletter_last_modify_display ".
			  "FROM " . T_NEWSLETTER ." ".
			  "LEFT OUTER JOIN ".T_USER." AS U on newsletter_posted_by=U.user_id ".
			  
			  $mask." ORDER BY ".$orderby." OFFSET ".$debut." LIMIT ".$limite.";";
	#echo $requete;
	return $requete;
}

function SQL_getCountNewsletterList($statut) {
	switch ($statut) {
		case 'public':
			$mask="WHERE newsletter_statut = 'P'";
			break;
		case 'draft':
			$mask="WHERE newsletter_statut = 'D'";
			break;
		case 'waiting':
			$mask="WHERE newsletter_statut = 'AA'";
			break;
		default:
			$mask=" WHERE newsletter_statut <> 'E'";
		}
	$requete=	"SELECT COUNT(newsletter_id) AS num_rows FROM " . T_NEWSLETTER ." ";
	$requete.=$mask.";";
	return $requete;
}


function SQL_getNewsletterTitle($news_id) {
	$requete =	"SELECT newsletter_title, N.newsletter_posted_by, N.newsletter_statut FROM " . T_NEWSLETTER . " AS  N WHERE newsletter_id='".$news_id."';";
	return $requete;
}


function SQL_GetEmails4Sending() {
	$requete = 'SELECT emailcol_id, emailcol_email from '.T_EMAIL_COL.' WHERE emailcol_validity =\'Y\'';
	return $requete;
}

function SQL_GetNewsletter2Send() {
	$requete = 'SELECT * from '.T_NEWSLETTER.' WHERE newsletter_statut =\'W\'';
	return $requete;
}

?>