<?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_sdi = 	"SELECT I.sdii_name, sdiv_id, sdii_id, I.sdii_frequency, MAX( sdiv_date_published ) AS datep, COUNT( sdiv_date_published ) , 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'
			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 ";

$req_news = "SELECT N.news_title, news_id 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 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 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_getProjects()
{
  $q =  "SELECT project_name, project_id, 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;
}

?>