<?php
/**
 * Newsletter module
 * 
 * MySQL queries related to newsletter module
 * 
 * @package linea21\modules\newsletter
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

//////////// Check Inclusion de pages ////////////
if (!class_exists('auth')){
  include_once("../lib/lib_common.php");
  ReloadIndex('admin');
}
////////////

$req_quickbox_add = "SELECT newsletter_id, LEFT(newsletter_title, 50) as newsletter_title, DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate('long')."') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut  = 'P' OR newsletter_statut = 'D'  ORDER BY newsletter_date_crea DESC LIMIT 0 , 10;";
$req_quickbox_sup = "SELECT newsletter_id,  LEFT(newsletter_title, 50) as newsletter_title, DATE_FORMAT(newsletter_last_modify, '".toStringSqlDate('long')."') AS newsletter_last_modify_display, DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate()."') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut = 'E' ORDER BY newsletter_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_mod = "SELECT newsletter_id, LEFT(newsletter_title, 50) as newsletter_title, DATE_FORMAT(newsletter_last_modify, '".toStringSqlDate('long')."') AS newsletter_last_modify_display, DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate()."') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut  <> 'E' ORDER BY newsletter_last_modify DESC LIMIT 0 , 10;";
$req_quickbox_list = "SELECT newsletter_id,  LEFT(newsletter_title, 50) as newsletter_title, DATE_FORMAT(newsletter_published_date, '".toStringSqlDate('long')."') AS newsletter_published_date_display, DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate()."') AS newsletter_date_crea_display FROM " . T_NEWSLETTER . " WHERE newsletter_statut = 'P' ORDER BY newsletter_published_date DESC LIMIT 0 , 10;";

function SQL_get_latest_newletter() {
	
	$q = 'SELECT MAX(newsletter_published_date) as latest_date FROM ' . T_NEWSLETTER . ' WHERE newsletter_statut = \'P\';';
	
	return $q;
}

function SQL_getLatestNewsSince( $date ) {

	$q = "SELECT * FROM  " . T_NEWS . " WHERE news_statut = 'P' AND news_published_date > '". $date ."' ORDER BY news_range ASC, news_published_date DESC ;";

	return $q;
}

function SQL_getoneCompleteNewsLetter($newsletter_id, $force_status = false) {

	if($force_status) $force_status = " AND ". sql_status_filter('newsletter_statut', $force_status);
	else $force_status = "";

  $q =	"SELECT newsletter_id, newsletter_title, newsletter_body, newsletter_statut, newsletter_posted_by, ".
				"user_login, ".
				"DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate()."') AS newsletter_date_crea_display, ".
				"DATE_FORMAT(newsletter_last_modify, '".toStringSqlDate('long')."') AS newsletter_last_modify_display, ".
				"DATE_FORMAT(newsletter_published_date, '".toStringSqlDate()."') AS newsletter_published_date_display ".
				"FROM " . T_NEWSLETTER . " ".
				"LEFT OUTER JOIN ".T_USER." on newsletter_posted_by=user_id ".
				"WHERE newsletter_id='".$newsletter_id."'". $force_status.";";

  return $q;
}


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;
  }
  $q= "SELECT newsletter_id, LEFT(newsletter_title, 60) AS newsletter_title, ".
			  "newsletter_statut, newsletter_posted_by, user_login, ".
			  "DATE_FORMAT(newsletter_date_crea, '".toStringSqlDate()."') AS newsletter_date_crea_display, ".
			  "DATE_FORMAT(newsletter_published_date, '".toStringSqlDate()."') AS newsletter_published_date_display, ".
			  "DATE_FORMAT(newsletter_last_modify, '".toStringSqlDate()."') 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." LIMIT ".$debut." , ".$limite.";";
  #echo $q;
  return $q;
}

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'";
  }
  $q=	"SELECT COUNT(newsletter_id) AS num_rows FROM " . T_NEWSLETTER ." ";
  $q.=$mask.";";
  return $q;
}


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


function SQL_GetEmails4Sending() {
  $q = "SELECT emailcol_id, emailcol_email, emailcol_date_crea, DATE_FORMAT(emailcol_date_crea, '".toStringSqlDate()."') AS emailcol_date_crea_display from ".T_EMAIL_COL." WHERE emailcol_validity ='Y'";
  return $q;
}

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

/**
 * SQL_getNewsletter()
 * Return whether a wanted Newsletter - if given id
 * or the latest published
 * @param numeric $id
 */
function SQL_getNewsletter($id = null) {

	if($id == null) {
		$q = "SELECT *, DATE_FORMAT(newsletter_published_date, '".toStringSqlDate()."') AS published_date_display FROM ".T_NEWSLETTER." WHERE newsletter_published_date IN (SELECT max( newsletter_published_date ) FROM ".T_NEWSLETTER." WHERE newsletter_statut = 'P') AND newsletter_statut = 'P'";
	} else {
		$q = "SELECT *, DATE_FORMAT(newsletter_published_date, '".toStringSqlDate()."') AS published_date_display FROM ".T_NEWSLETTER." WHERE newsletter_id='".$id."' AND newsletter_statut = 'P'";
	}

	return $q;
}

function SQL_getPublishedNewsletters($exclude = null) {

	if($exclude != null && is_numeric($exclude)) $mask=' AND newsletter_id <>'.$exclude;
	else $mask='';
	$q = "SELECT *, DATE_FORMAT(newsletter_published_date, '".toStringSqlDate()."') AS published_date_display FROM ".T_NEWSLETTER." WHERE newsletter_statut = 'P'".$mask." ORDER BY newsletter_published_date DESC;";

	return $q;
}

?>