File Source for mysql.inc.php

<?php
/**
* @package linea21.modules
* @subpackage dashboard
* @author linea21 <info@linea21.com>
* @version $id SVN
* @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');
}
// //////////
function SQL_getquickbox_list($scale_id)
{
$req_quickbox_list = "SELECT sdiv_date_crea, MAX(sdiv_date_crea) AS date_c_max,
DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS
date_m, I.sdii_name, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, sdiv_statut, V.sdiv_sdi_info, I.sdii_unit, sdiv_id, sdiv_value, sdiv_scale
FROM " . T_SDI_VALUE . " as V LEFT OUTER JOIN " . T_SDI_INFO . " as I ON I.sdii_id=V.sdiv_sdi_info
WHERE sdiv_statut<>'E' AND sdii_statut<>'E' AND sdiv_scale=$scale_id
GROUP BY sdiv_sdi_info, sdiv_scale
ORDER BY date_c_max DESC
LIMIT 0 , 10 ;";
return $req_quickbox_list;
}
function SQL_getquickbox_mod_list($scale_id)
{
$req_quickbox_list = "SELECT I.sdii_name, sdiv_value, sdiv_id, sdiv_scale, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p,
DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS
date_m, sdiv_statut, V.sdiv_sdi_info, I.sdii_unit, MAX(sdiv_last_modify) AS date_m_max, sdiv_last_modify
FROM " . T_SDI_INFO . " as I LEFT OUTER JOIN " . T_SDI_VALUE . " as V ON I.sdii_id=V.sdiv_sdi_info
WHERE sdiv_statut<>'E' AND sdii_statut<>'E' AND sdiv_scale=$scale_id
GROUP BY sdiv_sdi_info, sdiv_scale
ORDER BY date_m_max DESC LIMIT 0 , 10 ;";
return $req_quickbox_list;
}
function SQL_getListtheme()
{
$q = "SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN " . T_SD_THEME . " AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut <> 'E' ORDER BY sdtheme_range ASC, sdtheme_name ASC, theme_name ASC;";
return $q;
}
// necessary because function can be already declared in project page
if(!function_exists('SQL_getSDThemes')) {
function SQL_getSDThemes() {
$q = "SELECT sdtheme_id, sdtheme_name, sdtheme_description FROM " . T_SD_THEME . " ORDER BY sdtheme_range ASC, sdtheme_name ASC;";
return $q;
}
}
function SQL_getListProjects()
{
// return all projects
// $q = "SELECT DISTINCT P.project_id, P.project_name FROM " . T_PROJECT . " AS P LEFT OUTER JOIN ". J_PROJECT_SDI . " as JPS on P.project_id =JPS.jps_project_id WHERE P.project_statut = 'P' ORDER BY project_range ASC, project_name DESC;";
// only return projects with indicators
$q = "SELECT DISTINCT P.project_id, P.project_name FROM " . T_PROJECT . " AS P, ". J_PROJECT_SDI . " as JPS WHERE P.project_statut = 'P' AND P.project_id =JPS.jps_project_id ORDER BY project_range ASC, project_name ASC;";
return $q;
}
$req_list_theme = "SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN " . T_SD_THEME . " AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut <> 'E' ORDER BY sdtheme_range ASC, sdtheme_name ASC, theme_name ASC;";
$req_list_scale = "SELECT * FROM " . T_SCALE . " WHERE scale_statut='P' OR scale_statut='D' ORDER BY scale_dependencies ASC, scale_denomination ASC;";
function SQL_getonescale($scale_id)
{
$q = "SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_id=" . $scale_id . ";";
return $q;
}
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, sdii_description FROM " . T_SDI_INFO . " WHERE sdii_id='" . $id . "'". $force_status.";";
return $q;
}
function SQL_getOnesditheme($theme_id)
{
$q = "SELECT theme_name FROM " . T_THEME . " WHERE theme_id=$theme_id ;";
return $q;
}
function SQL_getAllsditheme($theme_id)
{
$q = "SELECT COUNT(sdii_id) AS countsdi FROM " . T_SDI_INFO . " WHERE sdii_theme=$theme_id AND sdii_statut <> 'E';";
return $q;
}
function SQL_getIndicatorsAlpha($force_status = false) {
if($force_status) $force_status = " AND ". sql_status_filter('sdii_statut', $force_status);
else $force_status = "";
$q = "SELECT sdii_id, sdii_name
FROM " . T_SDI_INFO . "
WHERE 1 = 1 ". $force_status." ORDER BY sdii_name ASC;";
return $q;
}
function SQL_getListSdi($filter = -1, $valueparam)
{
$filter = strtoupper($filter);
$q = "SELECT II.sdii_id, II.sdii_type, II.sdii_dashboard_viz, II.sdii_detail_viz, II.sdii_name, 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, T.theme_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_theme, JPS.jps_sdi_id, JPS.jps_project_id
FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN " . T_THEME . " as T on II.sdii_theme=T.theme_id
LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id
LEFT OUTER JOIN " . J_PROJECT_SDI . " as JPS on II.sdii_id =JPS.jps_sdi_id";
switch ($filter) {
case 'T':
$q .= " WHERE T.theme_name LIKE'" . $valueparam . "'";
$q .= "AND II.sdii_statut<>'E';";
break;
case 'S':
$valueparam = strtoupper($valueparam);
$q .= " WHERE II.sdii_statut='" . $valueparam . "'";
break;
case 'R':
$q .= " WHERE II.sdii_statut<>'E'
ORDER BY II.sdii_theme DESC
";
break;
case 'P':
$q .= " WHERE II.sdii_statut='P'
ORDER BY II.sdii_theme DESC
";
break;
default:
$q .= " WHERE II.sdii_statut<>'E';";
}
return $q;
}
function SQL_getInfoSdi($id)
{
$q = "SELECT II.sdii_id, II.sdii_type, II.sdii_dashboard_viz, II.sdii_detail_viz, II.sdii_name, II.sdii_description, II.sdii_comment, T.theme_name, II.sdii_theme,
II.sdii_range, II.sdii_goal, II.sdii_consulting, 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_date_crea, II.sdii_last_modify, II.sdii_statut
FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN " . T_THEME . " as T on II.sdii_theme=T.theme_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_getProjectSdiValues($id) {
$query = "SELECT II.sdii_id, II.sdii_name, II.sdii_dashboard_viz, II.sdii_detail_viz, 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, T.theme_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_theme
FROM " . T_SDI_INFO . " AS II
LEFT OUTER JOIN " . J_PROJECT_SDI . " as JPS on II.sdii_id =JPS.jps_sdi_id
LEFT OUTER JOIN " . T_THEME . " as T on II.sdii_theme=T.theme_id
LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id
WHERE II.sdii_statut<>'E' AND JPS.jps_project_id='".$id."';";
return $query;
}
function SQL_GetKeysSdi($sdi_id, $sql_object)
{
$q = "SELECT sdii_provider, sdii_evaluation, sdii_reglementation
FROM " . T_SDI_INFO . "
WHERE sdii_id=" . $sdi_id . ";";
$result = $sql_object->DBSelect($q);
return $result;
}
function SQL_GetKeysSdi2($sdi_id)
{
$q = "SELECT sdii_provider, sdii_evaluation, sdii_reglementation
FROM " . T_SDI_INFO . "
WHERE sdii_id=" . $sdi_id . ";";
return $q;
}
function SQL_getRules($id_idd, $sql_object, $ID = -1)
{
if ($ID == -1) {
$data = SQL_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) {
$data = SQL_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) {
$data = SQL_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;
}
function SQL_getAllValue($filter = -1, $scale_id, $indicator_id, $sort = 'DESC')
{
$filter = strtoupper($filter);
$q = "SELECT sdiv_id, sdiv_value, sdiv_threshold, sdiv_comment, sdiv_comment_display, sdiv_scale, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut
FROM " . T_SDI_VALUE . " WHERE sdiv_sdi_info=" . $indicator_id ;
switch ($filter) {
case 'D':
$q .= " AND sdiv_date_published LIKE '" . $scale_id . "';";
break;
case 'SCP':
$q .= " AND sdiv_statut='P'";
$q .= " AND sdiv_scale=" . $scale_id;
$q .= " ORDER BY sdiv_date_published ".$sort.";";
break;
case 'SCA':
$q .= " AND sdiv_statut<>'E'";
$q .= " AND sdiv_scale=" . $scale_id;
$q .= " ORDER BY sdiv_date_published ".$sort.";";
break;
case 'SCD':
$q .= " AND sdiv_statut='P'";
$q .= " AND sdiv_scale=" . $scale_id;
$q .= " ORDER BY sdiv_date_published ".$sort.";";
break;
case 'ST':
$q .= " AND sdiv_statut='" . $scale_id . "';";
break;
default:
$q .= " AND sdiv_statut<>'E'";
}
return $q;
}
function SQL_getOneValue($id)
{
$q = "SELECT sdiv_value, sdiv_scale, sdiv_threshold, sdiv_comment, sdiv_comment_display, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut
FROM " . T_SDI_VALUE . " WHERE sdiv_id=" . $id . "";
return $q;
}
function SQL_getlastInsertByPublicationDate($id, $scale_id)
{
$q = "SELECT sdiv_value, sdiv_scale, sdiv_threshold, sdiv_sdi_info, sdiv_comment, sdiv_comment_display, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut
FROM " . T_SDI_VALUE . " WHERE sdiv_sdi_info=" . $id . " AND sdiv_scale=" . $scale_id . " ORDER BY sdiv_date_published DESC LIMIT 0, 1;";
return $q;
}
function SQL_getInfoSdiforvalue($id)
{
$q = "SELECT II.sdii_id, II.sdii_detail_viz, II.sdii_unit, II.sdii_max_value, II.sdii_min_value,
II.sdii_threshold_value, II.sdii_threshold_relative
FROM " . T_SDI_INFO . " AS II" ;
$q .= " WHERE II.sdii_id=" . $id . ";";
return $q;
}
function SQL_getlistscale($scale_id)
{
$req_sup = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_statut<>'I' AND scale_id<>'$scale_id' AND scale_dependencies<" . (MAX_SCALE_LEVEL-1) . " ORDER BY scale_id;";
return $req_sup;
}
// /////////////////////////////////
// AJOUT GENERATION DE RAPPORT
// /////////////////////////////////
function GetAllIndicatorsInfo ($status, $type, $sdi_array = -1)
{
$mask = '';
if ($status == 'ALL') $mask .= 'WHERE sdii_statut <> \'E\'';
else $mask .= 'WHERE sdii_statut = \'P\'';
if ($type != 'ALL') {
$sep = ' AND ';
for($i = 0; $i < count($sdi_array); $i++) {
$mask .= $sep . 'sdii_id = ' . $sdi_array[$i] . '';
$sep = ' OR ';
}
}
$q = "SELECT I . * , P . * , E . * , R . * , T . * , SDT . *
FROM " .T_SDI_INFO. " AS I
LEFT OUTER JOIN ".T_SDI_PROVIDER." AS P ON P.sdip_id = I.sdii_provider
LEFT OUTER JOIN ".T_SDI_EVAL." AS E ON E.sdie_id = I.sdii_evaluation
LEFT OUTER JOIN ".T_SDI_RULES." AS R ON R.sdir_id = I.sdii_reglementation
LEFT OUTER JOIN ".T_THEME." AS T ON T.theme_id = I.sdii_theme
LEFT OUTER JOIN ".T_SD_THEME." AS SDT ON SDT.sdtheme_id = T.theme_sd_theme ";
$q .= $mask;
$q .= " ORDER BY sdtheme_name ASC, sdii_name ASC;";
return $q;
}
function SQL_getSdiValue($id_idd, $status, $type, $scale_array = -1)
{
$mask = '';
if ($status == 'ALL') $mask .= 'AND sdiv_statut <> \'E\'';
else $mask .= 'AND sdiv_statut = \'P\'';
if ($type != 'ALL') {
$sep = ' AND ';
for($i = 0; $i < count($scale_array); $i++) {
$mask .= $sep . 'sdiv_scale = ' . $scale_array[$i] . '';
$sep = ' OR ';
}
}
$q = "SELECT sdiv_id, sdiv_value, sdiv_scale,
DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p,
DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c,
DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut,
scale_denomination
FROM " . T_SDI_VALUE . "
LEFT OUTER JOIN " . T_SCALE . " ON scale_id = sdiv_scale
WHERE sdiv_sdi_info=' . $id_idd . '";
$q .= $mask;
$q .= ' ORDER BY scale_id ASC, scale_denomination ASC, sdiv_date_published DESC;';
return $q;
}
function SQL_getAllScale()
{
$q = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_statut<>'I' ORDER BY scale_dependencies ASC, scale_id ASC;";
return $q;
}
?>

Source for file mysql.inc.php