<?php
/**
 * Comment module
 * 
 * MySQL queries related to comment module
 * 
 * @package linea21\core
 * @author $Author$ - Simon Georget <simon@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */


function SQL_getTotalCountNumber() {
  $q =	"SELECT COUNT(comment_id) AS nb FROM " . T_COMM . " WHERE comment_status='P' OR comment_status='D' OR comment_status='E';";

  return $q;
}

function SQL_getCommentsNumber($a) {

  $q =	"SELECT COUNT(comment_id) AS nb FROM " . T_COMM . " WHERE comment_module='".$a['module']."' AND comment_module_id='".$a['module_id']."' AND comment_status='P';";

  return $q;
}

function SQL_getComments($options) {

  $status = ' AND ';

  if(isset($options['status'])) {
    for($i=0; $i < count($options['status']); $i++) {
      if($i != 0) $prefix = ' OR '; else $prefix='(';
      $status .= $prefix."comment_status='" . $options['status'][$i] . "'";
    }
    $status .= ')';
  } else {
    $status .="comment_status='P'";
  }
  if(isset($options['given_id'])) {
    $given_id = " AND comment_id ='".$options['given_id']."'";
  } else {
    $given_id = '';
  }
  if(isset($options['module'])) {
    $module_filter = " AND comment_module ='".$options['module']."' AND comment_module_id ='".$options['module_id']."'";
  } else {
    $module_filter = '';
  }
  isset($options['order_by']) ? $options['order_by'] =  $options['order_by'] : $options['order_by'] = 'DESC';

  $q =	"SELECT C.*, U.user_id, U.user_login, P.profile_avatar, P.profile_firstname, P.profile_lastname, C.comment_date_crea, ".
            "UNIX_TIMESTAMP(C.comment_date_crea) AS comment_date_timestamp, ".
            "DATE_FORMAT(C.comment_date_crea, '".toStringSqlDate('long')."') AS comment_date_crea_display ".
            "FROM " . T_COMM . " AS C ".
            "LEFT OUTER JOIN " . T_USER . " AS U ON C.comment_user_id = U.user_id ".
            "LEFT OUTER JOIN " . T_PROFILE . " AS P ON P.profile_id = U.user_profile ".
    		"WHERE 1".$module_filter . $status . $given_id . " ORDER BY comment_date_crea ".$options['order_by']." ;";

  return $q;
}

function SQL_getModuleComments($options) {

  switch ($options['module']) {
    case 'news':
      $q  = "LEFT OUTER JOIN " . T_NEWS . " AS N ON N.news_id = C.comment_module_id ";
      $field = "N.news_title";
      break;
    case 'project':
      $q  = "LEFT OUTER JOIN " . T_PROJECT . " AS PR ON PR.project_id = C.comment_module_id ";
      $field = "PR.project_name";
      break;
    case 'workgroups':
      $q  = "LEFT OUTER JOIN " . T_WORK . " AS W ON W.workshop_id = C.comment_module_id ";
      $field = "W.workshop_denomination";
      break;
    case 'indicator':
      $q  = "LEFT OUTER JOIN " . T_SDI_INFO . " AS S ON S.sdii_id = C.comment_module_id ";
      $field = "S.sdii_name";
      break;
    case 'publication':
      $q  = "LEFT OUTER JOIN " . T_PUBLI . " AS PU ON PU.publi_id = C.comment_module_id ";
      $field = "PU.publi_title";
      break;
    case 'report':
      $q  = "LEFT OUTER JOIN " . T_WORK_REP  . " AS R ON R.workrep_id = C.comment_module_id ";
      $field = "R.workrep_title";
      break;
    case 'files':
      $q  = "";
      $field = "SUBSTRING_INDEX(C.comment_module_id, '/', -1)";
      break;
    case 'contribute':
      $q  = "";
      $field = "C.comment_module_id";
      break;
    default:
      die($options['module'] . ' is not a valid option.');
      break;
  }
  $status = ' AND ';

  if(isset($options['status'])) {
    for($i=0; $i < count($options['status']); $i++) {
      if($i != 0) $prefix = ' OR '; else $prefix='(';
      $status .= $prefix."comment_status='" . $options['status'][$i] . "'";
    }
    $status .= ')';
  } else {
    $status .="comment_status='P'";
  }
  if(isset($options['given_id'])) {
    $given_id = " AND comment_id='".$options['given_id']."'";
  } else {
    $given_id = '';
  }
  isset($options['order_by']) ? '' : $options['order_by'] = 'DESC';

  if(isset($options['limit']) && is_numeric($options['limit'])) {
    isset($options['start']) ? $start = isset($options['start']) : $start = 0;
    $limit = ' LIMIT '.$start.', '. $options['limit'];
  } else {
    $limit = '';
  }

  $q =	"SELECT C.*, U.user_id, U.user_login, P.profile_avatar, P.profile_firstname, P.profile_lastname, ". $field ." AS item_title, C.comment_date_crea, ".
            "UNIX_TIMESTAMP(C.comment_date_crea) AS comment_date_timestamp, ".
            "DATE_FORMAT(C.comment_date_crea, '".toStringSqlDate('long')."') AS comment_date_crea_display ".
            "FROM " . T_COMM . " AS C ".
            "LEFT OUTER JOIN " . T_USER . " AS U ON C.comment_user_id = U.user_id ".
            "LEFT OUTER JOIN " . T_PROFILE . " AS P ON P.profile_id = U.user_profile ".
  $q .
    		"WHERE comment_module='".$options['module']."'" . $status . $given_id . " ORDER BY comment_date_crea ".$options['order_by']. $limit. " ;";

  return $q;
}

function SQL_getRegisteredSubscribers($options) {

  $q = 	"SELECT P.profile_email AS user_email FROM ". T_COMM . " AS C ".
        "LEFT OUTER JOIN " . T_USER . " AS U ON C.comment_user_id = U.user_id ".
        "LEFT OUTER JOIN " . T_PROFILE . " AS P ON P.profile_id = U.user_profile ".
  		"WHERE comment_module='".$options['module']."' AND comment_module_id='".$options['module_id']."' AND comment_email = '' AND comment_notification = 'Y' AND user_validity = 'Y';";

  return $q;

}

function SQL_getNonRegisteredSubscribers($options) {

  $q = "SELECT C.comment_email AS user_email FROM ". T_COMM . " AS C WHERE comment_module='".$options['module']."' AND comment_module_id='".$options['module_id']."' AND comment_email <> '' AND comment_status <> 'D' AND comment_notification = 'Y';";

  return $q;

}

function SQL_getUserInfo($id) {

  $q = "SELECT user_login, profile_email FROM ". T_USER . " AS U LEFT OUTER JOIN " . T_PROFILE . " AS P ON P.profile_id = U.user_profile WHERE user_id = '".$id."' ";

  return $q;
}

function SQL_getCommentItem($options) {

  switch ($options['module']) {
    case 'news':
      $q  = "SELECT news_title AS item_title FROM " . T_NEWS . " WHERE news_id = '".$options['module_id']."';";
      $field = "N.news_title";
      break;
    case 'project':
      $q  = "SELECT project_name AS item_title FROM " . T_PROJECT . " WHERE project_id = '".$options['module_id']."';";
      break;
    case 'workgroups':
      $q  = "SELECT workshop_denomination AS item_title FROM " . T_WORK . " WHERE workshop_id = '".$options['module_id']."';";
      break;
    case 'indicator':
      $q  = "SELECT sdii_name AS item_title FROM " . T_SDI_INFO . " WHERE sdii_id = '".$options['module_id']."';";
      break;
    case 'publication':
      $q  = "SELECT publi_title AS item_title FROM " . T_PUBLI . " WHERE publi_id = '".$options['module_id']."';";
      break;
    case 'report':
      $q  = "SELECT workrep_title AS item_title FROM " . T_WORK_REP  . " WHERE workrep_id = '".$options['module_id']."';";
      break;
    case 'files':
      $q = 	"SELECT SUBSTRING_INDEX(comment_module_id, '/', -1) AS item_title FROM " . T_COMM . " WHERE comment_module_id = '".$options['module_id']."';";
      break;
    case 'contribute':
      $q = 	"SELECT comment_module_id AS item_title FROM " . T_COMM . " WHERE comment_module_id = '".$options['module_id']."';";
      break;
    default:
      die($options['module'] . ' is not a valid option.');
      break;
  }
  return $q;

}