<?php
/**
 * Home module - available from back-office only
 * 
 * MySQL queries related to home module
 * 
 * @package linea21\core\home
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * 'Home' module is used into the back-office
 */

$req_yp = "SELECT yellowp_name, yellowp_id FROM " . T_YELLOWPAGES . " WHERE yellowp_statut='D';";

$req_news = "SELECT N.news_title, news_id, news_published_date FROM " . T_NEWS . " AS  N WHERE news_statut='P' ORDER BY news_published_date DESC LIMIT 0,3;";

$req_publi = "SELECT publi_title, publi_resume, publi_id, publi_published_date FROM " . T_PUBLI . " WHERE publi_statut='P' ORDER BY publi_published_date DESC LIMIT 0,1;";

$req_rep = "SELECT  workrep_title, workrep_resume, workrep_id, workrep_published_date FROM " . T_WORK_REP . " LEFT OUTER JOIN " . T_WORK . " ON workshop_id = workrep_workshop_id WHERE workrep_statut='P' AND workshop_statut = 'P' ORDER BY workrep_published_date DESC LIMIT 0,1;";

function SQL_getUser($date)
{
  $req_user = "SELECT user_login, user_id , user_date_crea, profile_firstname, profile_lastname FROM " . T_USER . " LEFT OUTER JOIN " . T_PROFILE . " ON profile_id = user_profile WHERE user_date_crea > '" . $date . "' AND user_validity='Y'";
  return $req_user;
}

function SQL_get_indicators_to_update() {
    
    $q = 	"SELECT I.sdii_name, sdiv_id, sdii_id, I.sdii_frequency, MAX( sdiv_date_published ) AS datep, DATE_FORMAT(MAX( sdiv_date_published ), '".toStringSqlDate()."') AS datep_display, COUNT( sdiv_date_published ), DATEDIFF(NOW(), DATE_ADD(MAX( sdiv_date_published ), INTERVAL I.sdii_frequency DAY )) as date_diff, DATE_ADD( MAX( sdiv_date_published ) , INTERVAL I.sdii_frequency DAY ) as supposed_date, S.scale_denomination, S.scale_id
			FROM " . T_SDI_VALUE . " AS V
			LEFT OUTER JOIN " . T_SDI_INFO . " AS I ON V.sdiv_sdi_info = I.sdii_id
			LEFT OUTER JOIN " . T_SCALE . " AS S ON V.sdiv_scale = S.scale_id
			WHERE sdiv_statut <> 'E' AND sdii_statut <> 'E' AND I.sdii_frequency<>0 AND S.scale_statut <> 'E' AND I.sdii_frequency <> 0
			GROUP BY I.sdii_name, S.scale_denomination
			HAVING DATE_ADD( MAX( sdiv_date_published ) , INTERVAL I.sdii_frequency DAY ) < NOW()
			ORDER BY S.scale_id ";

    return $q;
}

function SQL_get_indicators_by_projects($projects) {

    $q = "SELECT I.sdii_name, sdiv_id, sdii_id, jps_project_id, I.sdii_frequency, MAX( sdiv_date_published ) AS datep, DATE_FORMAT(MAX( sdiv_date_published ), '".toStringSqlDate()."') AS datep_display, COUNT( sdiv_date_published ), DATEDIFF(NOW(), DATE_ADD(MAX( sdiv_date_published ), INTERVAL I.sdii_frequency DAY )) as date_diff, DATE_ADD( MAX( sdiv_date_published ) , INTERVAL I.sdii_frequency DAY ) as supposed_date, S.scale_denomination, S.scale_id FROM l21_sdi_value AS V 
            LEFT OUTER JOIN  " . T_SDI_INFO . "  AS I ON V.sdiv_sdi_info = I.sdii_id 
            LEFT OUTER JOIN " . T_SCALE . " AS S ON V.sdiv_scale = S.scale_id 
            RIGHT JOIN " . J_PROJECT_SDI . " AS J ON J.jps_sdi_id = sdii_id
            WHERE sdiv_statut <> 'E' AND sdii_statut <> 'E' AND I.sdii_frequency <> 0
            AND jps_project_id IN  ($projects) 
            GROUP BY I.sdii_name, S.scale_denomination ORDER BY S.scale_id;";

    return $q;
}

/**
 * get associated projects to a given indicator 
 * @param integer $id
 * @return string
 */
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_get_project_tasks_by_user($user_id, $status = false) {
    
    if($status) $status_filter = " AND ". sql_status_filter('T.task_status', $status);
    else $status_filter = "";
    
    $q =    "SELECT T.task_id, T.task_project_id, T.task_label, T.task_status, T.task_reminder_freq, task_reminder_lastdate, T.task_date_done, project_name, 
            DATE_FORMAT(T.task_date_crea, '".toStringSqlDate()."') AS task_date_crea_display,
            DATE_FORMAT(T.task_last_modify, '".toStringSqlDate('long')."') AS task_last_modify_display,
            DATE_FORMAT(T.task_date_done, '".toStringSqlDate()."') AS task_date_done_display,
            DATE_FORMAT(T.task_reminder_lastdate, '".toStringSqlDate()."') AS task_reminder_lastdate_display,
            TIMESTAMPDIFF(MONTH,T.task_date_crea,T.task_date_done) AS duration
            FROM " . T_PROJECT_TASK . " AS T
            LEFT OUTER JOIN ".T_PROJECT." AS P on T.task_project_id = P.project_id
            LEFT OUTER JOIN ".J_PROJECT_MANAGER." AS J on J.jpm_project_id = T.task_project_id
            WHERE jpm_manager_id = '".$user_id."' AND task_deleted ='N'". $status_filter."
            ORDER  BY FIELD (T.task_status, 'todo', 'wip', 'done'), T.task_project_id ASC;";
    
    return $q;
}

function SQL_get_user_projects($user_id) {
    
    $q = "SELECT * FROM ". T_PROJECT ."
      		LEFT OUTER JOIN " . J_PROJECT_MANAGER . " ON project_id = jpm_project_id
      		WHERE jpm_manager_id ='".$user_id. "' AND project_statut <> 'E' ORDER BY project_published_date DESC;";
    
    return $q;
}


function SQL_getProjects()
{
  $q =  "SELECT project_name, project_id, project_estimated_date, DATE_FORMAT(project_estimated_date, '".
  toStringSqlDate()."') AS project_estimated_date_display FROM "  . T_PROJECT .
          " WHERE project_statut='P' AND project_estimated_date <> '0001-01-01'".
          " ORDER BY project_estimated_date ASC LIMIT 0,3;";
  return $q;
}

function SQL_getWorkgroupsPendingUsers()
{
	$q =  "SELECT W.workshop_denomination, W.workshop_id, U.user_id, U.user_login, P.profile_firstname, P.profile_lastname, P.profile_email, P.profile_avatar ".
				"FROM "  . J_WORK_USERS . " ".
				"LEFT OUTER JOIN " . T_USER . " AS U ON jwu_user_id = U.user_id ".
				"LEFT OUTER JOIN " . T_PROFILE . " AS P ON jwu_user_id = P.profile_id ".
				"LEFT OUTER JOIN " . T_WORK . " AS W ON jwu_workshop_id = W.workshop_id ".
				" WHERE jwu_user_right='A' ".
				" ORDER BY workshop_denomination DESC, user_login DESC;";
	return $q;
}

?>