<?php
/**
 * Indicator / SDI module
 * 
 * MySQL queries related to publication module
 * 
 * @package linea21\modules\sdi
 * @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_add = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_dependencies<".(MAX_SCALE_LEVEL-1)." ORDER BY scale_id;";

$req_quickbox_list = "SELECT sdii_id, sdii_name, DATE_FORMAT(sdii_last_modify, '".toStringSqlDate('long')."') AS sdii_last_modify, sdii_date_crea AS sdii_date_crea2, DATE_FORMAT(sdii_date_crea, '".toStringSqlDate()."') AS sdii_date_crea
FROM " . T_SDI_INFO . "
WHERE sdii_statut <> 'E'
ORDER BY sdii_date_crea2 DESC
LIMIT 0 , 10;";

$req_quickbox_mod_list = "SELECT sdii_id, sdii_name, DATE_FORMAT(sdii_last_modify, '".toStringSqlDate('long')."') AS sdii_last_modify, sdii_last_modify AS sdii_last_modify2, DATE_FORMAT(sdii_date_crea, '".toStringSqlDate()."') AS sdii_date_crea
FROM " . T_SDI_INFO . "
WHERE sdii_statut <> 'E'
ORDER BY sdii_last_modify2 DESC
LIMIT 0 , 10;";

function SQL_getOnesdiname($id, $force_status = false) {

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

  $q = "SELECT sdii_name FROM " . T_SDI_INFO . " WHERE sdii_id='" . $id . "'". $force_status.";";
  return $q;
}

function SQL_getAllsdiByLevel($id) {
  $q= "SELECT COUNT(sdii_id) AS countsdi FROM " . T_SDI_INFO . " WHERE sdii_level=$id AND sdii_statut <> 'E';";
  return $q;
}

function SQL_getListSdi($filter, $valueparam = -1)
{
  $filter=strtoupper($filter);

  $q = "SELECT II.sdii_id, II.sdii_name, II.sdii_type, II.sdii_description, II.sdii_to_dashboard, II.sdii_max_value, II.sdii_min_value, II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_unit, L.level_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_level
		FROM ". T_SDI_INFO ." AS II LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
		LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id";
  if($filter==-1) {
    $q.=" WHERE II.sdii_statut<>'E';";
  }	else	{

    switch ($filter) {
      case 'T':
        $q.=" WHERE L.level_name LIKE'".$valueparam."'";
        $q.=" AND II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
        break;
      case 'S':
        $valueparam=strtoupper($valueparam);
        $q.=" WHERE II.sdii_statut='".$valueparam."' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
        break;
      case 'R':
        $q.=" WHERE II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
        break;
      case 'L':
       	$q.=" WHERE II.sdii_level =".$valueparam."";
       	$q.=" AND II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
       	break;
    }
  }
  return $q;
}


function SQL_get_associated_projects( $id )
{
    
    $q = "SELECT project_id, project_name, project_range FROM " . T_PROJECT . " P LEFT JOIN ".J_PROJECT_SDI." PS ON P.project_id = PS.jps_project_id WHERE jps_sdi_id = '" . $id ."' ORDER BY project_range ASC, project_name ASC";
    
    return $q;
}


function SQL_getInfoSdi($id)
{
  $q = "SELECT II.sdii_id, II.sdii_type, II.sdii_nature, II.sdii_dashboard_viz, II.sdii_detail_viz, II.sdii_mcolor, II.sdii_tcolor, II.sdii_value_type, II.sdii_name, II.sdii_description, II.sdii_comment, L.level_name, L.level_label, II.sdii_level,
		II.sdii_range, II.sdii_goal, II.sdii_consulting, II.sdii_to_dashboard, II.sdii_unit, II.sdii_max_value, II.sdii_min_value,
		II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_frequency, II.sdii_provider, IP.sdip_name, II.sdii_evaluation, II.sdii_reglementation,
		DATE_FORMAT(II.sdii_date_crea, '".toStringSqlDate('long')."' ) AS date_c, II.sdii_statut
		FROM ". T_SDI_INFO  ." AS II LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
		LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id";
  $q.=" WHERE II.sdii_id='".$id."';";

  return $q;
}

function SQL_getRules( $id_idd, $sql_object, $ID=-1 )
{
  if ($ID==-1) {
    $sdi_object_func= new sdi;
    $data= $sdi_object_func -> _GetKeysSdi($id_idd,$sql_object);
    $ID=$data[0]['sdii_reglementation'];
  }
  $q = "SELECT sdir_id, sdir_title, sdir_body, sdir_referer_uri, sdir_mask_uri,
		DATE_FORMAT(sdir_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdir_last_modify, '".toStringSqlDate('long')."' ) AS date_m
		FROM ". T_SDI_RULES ." WHERE sdir_id=".$ID.";";

  return $q;
}

function SQL_getProvider( $id_idd, $sql_object, $ID=-1)
{
  if ($ID==-1) {
    $sdi_object_func= new sdi;
    $data=$sdi_object_func -> _GetKeysSdi($id_idd,$sql_object);
    $ID=$data[0]['sdii_provider'];
  }
  $q = "SELECT sdip_id, sdip_name, sdip_service, sdip_description, sdip_incharge,
		sdip_address, sdip_phone, sdip_fax, sdip_email, DATE_FORMAT(sdip_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdip_last_modify, '".toStringSqlDate('long')."' ) AS date_m
		FROM ". T_SDI_PROVIDER ." WHERE sdip_id=".$ID.";";

  return $q;
}

function SQL_getEvaluation( $id_idd, $sql_object, $ID=-1)
{
  if ($ID==-1) {
    $sdi_object_func= new sdi;
    $data=$sdi_object_func -> _GetKeysSdi($id_idd,$sql_object);
    $ID=$data[0]['sdii_evaluation'];
  }
  $q = "SELECT sdie_id, sdie_scale_compare, sdie_fiability, sdie_accessibility, sdie_lisibility,
		sdie_relevance, sdie_global_performance, DATE_FORMAT(sdie_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdie_last_modify, '".toStringSqlDate('long')."' ) AS date_m
		FROM ". T_SDI_EVAL ." WHERE sdie_id=".$ID.";";

  return $q;
}


?>