<?php
// david.han-sze-chuen@airliquide.com 19/07/2004
// WARNING : oftenly, these queries are completed with the sprintf() function.
// So, be careful with the '%' character. Use '%%' to de-specialize this char.
$QUERY = array(
	'admin-list-groups' => 'select ag.group_id, group_name, locked, '.
		'count(ug.group_id) as count '.
		'from '.TBL_AUTH_GROUP.' ag '.
		'left join '.TBL_USER_GROUP.' ug using (group_id) '.
		'left join '.TBL_AUTH_USER.' using (user_id) '.
		'group by ag.group_id, group_name, locked '.
		'order by %s %s',
	'admin-list-oses' => 'select s.os_id, os_name, regex, sort_order, '.
		'count(bug_id) as bug_count '.
		'from '.TBL_OS.' s '.
		'left join '.TBL_BUG.' using (os_id) '.
		'group by s.os_id, os_name, regex, sort_order '.
		'order by %s %s',
	'admin-show-version' => 'select v.*, p.project_name as project_name '.
		'from '.TBL_VERSION.' v left join '.TBL_PROJECT.' p using(project_id) '.
		'where version_id = \'%s\'',
	'admin-show-component' => 'select c.*, p.project_name as project_name '.
		'from '.TBL_COMPONENT.' c  left join '.TBL_PROJECT.' p using (project_id) '.
		'where component_id = \'%s\'',
	'admin-list-resolutions' => 'select s.resolution_id, resolution_name, '.
		'resolution_desc, sort_order, count(bug_id) as bug_count '.
		'from '.TBL_RESOLUTION. ' s left join '.TBL_BUG.' using (resolution_id) '.
		'group by s.resolution_id, resolution_name, resolution_desc, sort_order '.
		'order by %s %s',
	'admin-list-severities' => 'select s.severity_id, severity_name, '.
		'severity_desc, severity_color, sort_order, count(bug_id) as bug_count '.
		'from '.TBL_SEVERITY. ' s left join '.TBL_BUG.' using (severity_id) '.
		'group by s.severity_id, severity_name, severity_desc, severity_color, '.
		'sort_order '.
		'order by %s %s',
	'admin-list-databases' => 'select d.database_id, database_name, '.
		'sort_order, count(bug_id) as bug_count '.
		'from '.TBL_DATABASE. ' d left join '.TBL_BUG.' using (database_id) '.
		'group by d.database_id, database_name, sort_order '.
		'order by %s %s',
	'admin-list-sites' => 'select s.site_id, site_name, sort_order, '.
		'count(bug_id) as bug_count from '.TBL_SITE. ' s left join '.
		TBL_BUG.' using (site_id) group by s.site_id, site_name, sort_order '.
		'order by %s %s',
	'admin-list-statuses' => 'select s.status_id, status_name, status_desc, '.
		'sort_order, count(bug_id) as bug_count '.
		'from '.TBL_STATUS.' s left join '. TBL_BUG.' using (status_id) '.
		'group by s.status_id, status_name, status_desc, sort_order '.
		'order by %s %s',
	'admin-user-groups' => 'select ug.group_id '.
		'from '.TBL_USER_GROUP.' ug left join '.TBL_AUTH_GROUP.' g using (group_id) '.
		'where user_id = %s and group_name <> \'User\'',
	'bug-history' => 'select bh.*, login '.
		'from '.TBL_FILE_HISTORY.' bh '.
		'left join '. TBL_AUTH_USER.' on bh.created_by = user_id '.
		'where bug_id = %s order by bh.created_date',
	'bug-cc-list' => 'select email '.
		'from '.TBL_BUG_CC.' left join '. TBL_AUTH_USER.' u using(user_id), '.
		TBL_USER_PREF.' p '.
		'where bug_id = %s and u.user_id = p.user_id and email_notices = 1',
	'bug-printable' => 'select b.*, reporter.login as reporter, '.
		'owner.login as owner, close_vesion_name.version_name as close_version_name, project_name, component_name, v.version_name as version_name, '.
		'severity_name, os_name, status_name, resolution_name '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '.
		'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '.
		'left join '.TBL_VERSION.' close_vesion_name on b.closed_in_version_id = close_vesion_name.version_id '.
		'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '.
		TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_OS.' os, '. TBL_VERSION.' v, '.
		TBL_COMPONENT.' c, '.TBL_PROJECT.' p '.
		'where bug_id = %s and b.project_id not in (%s) '.
		'and b.severity_id = sv.severity_id '.
		'and b.os_id = os.os_id and b.version_id = v.version_id '.
		'and b.component_id = c.component_id and b.project_id = p.project_id '.
		'and b.status_id = st.status_id',
	// bug-prev-next is no more used
  /*
	'bug-prev-next' => 'select bug_id, reporter.login as reporter, '.
		'owner.login as owner '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '.
		'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '.
		'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '.
		'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id, '.
		TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '.
		TBL_VERSION.' version, '.TBL_COMPONENT.' component, '.
		TBL_PROJECT.' project, '.TBL_SITE.' site '.
		'where b.severity_id = severity.severity_id '.
		'and b.status_id = status.status_id and b.os_id = os.os_id '.
		'and b.version_id = version.version_id '.
		'and b.component_id = component.component_id '.
		'and b.project_id = project.project_id and %s '.
		'and b.site_id = site.site_id '.
		'and bug_id <> %s '.
		'order by %s %s, bug_id asc',
		*/
	'bug-show-bug' =>  'select b.*, reporter.login as reporter, '.
		'owner.login as owner, status_name, resolution_name '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '.
		'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '.
		'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '.
		TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_SITE.' site '.
		'where bug_id = %s and b.project_id not in (%s) '.
		'and b.site_id = site.site_id '.
		'and b.severity_id = sv.severity_id and b.status_id = st.status_id',
	'functions-bug-cc' => 'select b.user_id, login '.
		'from '.TBL_BUG_CC.' b left join '. TBL_AUTH_USER.' using(user_id) '.
		'where bug_id = %s',
	'functions-project-js' => 'select p.project_id, project_name '.
		'from '.TBL_PROJECT. ' p '.
		'left join '.TBL_PROJECT_GROUP.' pg using(project_id) '.
		'where active = 1 and (pg.project_id is null or pg.group_id in (%s)) '.
		'group by p.project_id, p.project_name '.
		'order by project_name',
//TODO : update the search of opened/closed files 
	'include-template-owner' => "SELECT sum(CASE WHEN s.status_id ".
		"in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ".
		"sum(CASE WHEN s.status_id ".
		"not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ".
		"from ".TBL_BUG." b left join ".TBL_STATUS." s using(status_id) ".
		"where assigned_to = %s",
	'include-template-reporter' => "SELECT sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ".
		"sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ".
		"from ".TBL_BUG." b left join ".TBL_STATUS." s using(status_id) ".
		"where created_by = %s",
	'index-projsummary-1' => 'select project_name as "Project", '.
		'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"',
	'index-projsummary-2' => "select resolution_name, ",
	'index-projsummary-3' => "', sum(case when resolution_id = '",
	'index-projsummary-4' => "' then 1 else 0 end) as \"'",
	'index-projsummary-5' => " from ".TBL_RESOLUTION,
	'index-projsummary-6' => '%s, count(bug_id) as "Total" '.
		'from '.TBL_BUG. ' b left join '.TBL_PROJECT.' p using (project_id) '.
		'where b.project_id not in (%s) group by b.project_id, project_name '.
		'order by project_name',
	'index-projstatus-1' => 'select project_name as "Project"',
	'index-projstatus-2' => "select status_name, ",
	'index-projstatus-3' => "', sum(case when status_id = '",
	'index-projstatus-4' => "' then 1 else 0 end) as \"'",
	'index-projstatus-5' => " from ".TBL_STATUS." order by sort_order",
	'index-projstatus-6' => '%s, count(bug_id) as "Total" '.
		'from '.TBL_BUG. ' b left join '.TBL_PROJECT.' p using (project_id) '.
		'where b.project_id not in (%s) group by b.project_id, project_name '.
		'order by project_name',
	'query-list-bugs-count' => 'select count(*) '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '.
		'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id ',
	'query-list-bugs-count-join' => 'where ',
	'query-list-bugs' => 'select %s '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '.
		'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '.
		'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '.
		'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '.
		'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '.
		'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '.
		'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '.
		TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '.TBL_SITE.' site, '.
		TBL_VERSION.' version, '.TBL_COMPONENT.' component, '.TBL_PROJECT.' project '.
		'where b.severity_id = severity.severity_id '.
		'and b.status_id = status.status_id and b.os_id = os.os_id '.
		'and b.site_id = site.site_id and b.version_id = version.version_id '.
		'and b.component_id = component.component_id '.
		'and b.project_id = project.project_id %s '.
		'%s order by %s %s, bug_id asc',
	'report-resbyeng-1' => 'select email as "Assigned To", '.
		'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"',
	'report-resbyeng-2' => "select resolution_name, ",
	'report-resbyeng-3' => "', sum(case when resolution_id = '",
	'report-resbyeng-4' => "' then 1 else 0 end) as \"'",
	'report-resbyeng-5' => " from ".TBL_RESOLUTION,
	'report-resbyeng-6' => '%s, count(bug_id) as "Total" '.
		'from '.TBL_BUG. ' b '.
		'left join '.TBL_AUTH_USER.' u on assigned_to = user_id %s '.
		'group by assigned_to, u.email',
	'join-where' => 'where',
	'admin-list-components' => 'select c.component_id, component_name, '.
		'c.created_date, active, count(bug_id) as bug_count '.
		'from '.TBL_COMPONENT.' c left join '.TBL_BUG.' b using(component_id) '.
		'where c.project_id = %s '.
		'group by c.component_id, c.component_name, c.created_date, c.active',
	'admin-list-versions' => 'select v.version_id, version_name, '.
		'v.created_date, active, count(bug_id) as bug_count '.
		'from '.TBL_VERSION.' v left join '.TBL_BUG.' b using(version_id) '.
		'where v.project_id = %s '.
		'group by v.version_id, v.version_name, v.created_date, v.active',
	/*
	* queries for the dynamic field management
	*/
	/*
	* Table managment
	*/
	'init_file_table' => 
		"CREATE TABLE %s (
		bug_id int(10) unsigned NOT NULL default '0',
		title varchar(100) NOT NULL default '',
		description text NOT NULL,
		url varchar(255) NOT NULL default '',
		status_id tinyint(3) unsigned NOT NULL default %s,
		created_by int(10) unsigned NOT NULL default '0',
		created_date bigint(20) unsigned NOT NULL default '0',
		last_modified_by int(10) unsigned NOT NULL default '0',
		last_modified_date bigint(20) unsigned NOT NULL default '0',
		browser_string varchar(255) NOT NULL default '',
		closed_date bigint(20) unsigned NOT NULL default '0',
		PRIMARY KEY (bug_id)
		) 
		",
	'field_type_name' => array(
		INTEGER_TYPE => "INT",
		FLOAT_TYPE => "DECIMAL",
		STRING_TYPE => array(
			"VARCHAR" => "VARCHAR",
			"TEXT" => "TEXT"
		),
		SELECTBOX_TYPE => "INT(10) UNSIGNED",
		SELECT_TWINBOX_TYPE => "INT(10) UNSIGNED",
		TIMESTAMP_TYPE => "BIGINT(20) UNSIGNED",
	),
	'alter_table_create_field' => "ALTER TABLE ! ". // table_name
							   "ADD ! ! DEFAULT ? NOT NULL", // field name, field type, default value
	'alter_table_change_field' => "ALTER TABLE ! ". // table_name
							      "CHANGE ! ! ! DEFAULT ? NOT NULL ", // old field name, new field name, type, type param (ex: length), default value
	'alter_table_drop_field' => "ALTER TABLE ! ". // table name
								"DROP ! ", // field name
	'insert_file_index' => "INSERT INTO " . TBL_FILE_INDEX . "(file_id, project_id) VALUES(?, ?)",
	'insert_autoprepare' => "INSERT INTO !(!) VALUES(!)", // table name, insert columns, insert values
	
	'get_all_non_public_projects' => "SELECT * FROM " . TBL_PROJECT . " WHERE project_id != %s",
	
	//'update_autoprepare' => "UPDATE ! SET !", // update <table_name> set <column_name>=<insert_value>
	/*
	* Access queries
	*/	
	// get the public project row
	'get_public_project_info' => 'select %s from ' . TBL_PROJECT . 
		' p where p.project_name = \'' . PUBLICFIELDS_PROJECT_NAME . '\'',
	// get a project row
	'get_project_info' => 'select %s from ' . TBL_PROJECT . 
		' p where p.project_id = \'%s\'',
	// get project_id
	'get_project_id_with_bug_id' => "SELECT fi.project_id AS project_id FROM " . 
		TBL_FILE_INDEX . " fi, " . TBL_PROJECT . " pj WHERE fi.project_id = pj.project_id AND fi.file_id = %s ",
	// get selectbox values
    // 'get_select_box_values' => "SELECT sv.* FROM mplus_field_conf fc, mplus_selectbox_value sv " .
	//							"WHERE fc.field_conf_id in (%s) AND sv.field_conf_id = fc.field_conf_id",
	'get_select_box_values' => "SELECT sv.* FROM " . TBL_SELECTBOX_VALUE . " sv " .
								"WHERE sv.field_conf_id in (%s)",
	
	// get selectbox options
	'get_select_box_options' => "SELECT so.* FROM " . TBL_SELECTBOX_VALUE . " sv, " . TBL_SELECTBOX_OPTION . " so " . 
								"WHERE sv.sv_id in (%s) AND sv.sv_id = so.sv_id ",
	'get_file_by_id' => "SELECT f.* FROM %s f WHERE f.bug_id IN (%s)",
	'get_valid_project_id_and_name' => "SELECT project_id, project_name FROM " . TBL_PROJECT . " WHERE project_id not in (%s) ",
	'get_project_field' => "SELECT title FROM " . TBL_FIELD_CONF . " where project_id = %s order by list_order",
	// assignation
	'get_assignated_user_id' => "SELECT u.* FROM " . TBL_ASSIGNATION . " a, " . TBL_AUTH_USER . 
					" u WHERE u.user_id = a.user_id AND a.file_id IN (%s) ORDER BY first_name, last_name",
	// find the user_ids of the admins of a project
	'get_project_admin_user_ids' => "SELECT DISTINCT pp.user_id FROM " . TBL_PROJECT_PERM . " pp, " . TBL_AUTH_PERM . " ap "
		. " WHERE ap.perm_name = 'Admin' AND ap.perm_id = pp.perm_id AND pp.project_id = %s",
	// get selectbox data of a field with a given option value
	'get_selectbox_from_option' => "SELECT sv.* FROM " . TBL_SELECTBOX_VALUE . " sv, " . TBL_SELECTBOX_OPTION . " so, " . TBL_FIELD_CONF 
		. " fc WHERE fc.field_name = %s AND fc.field_conf_id = sv.field_conf_id AND sv.sv_id = so.sv_id AND so.option_name = %s ORDER BY sv_order",
	// get selectbox data of a field
	'get_selectbox' => "SELECT sv.* FROM " . TBL_SELECTBOX_VALUE . " sv, " . TBL_FIELD_CONF 
		. " fc WHERE fc.field_name = %s AND fc.field_conf_id = sv.field_conf_id"
);

?>
