<?php

// mssql.php - Microsoft SQL Server queries

$QUERY = array(
	'admin-list-components' =>
		'select '.
			'c.component_id, '.
			'c.component_name, '.
			'c.created_date, '.
			'c.owner, '.
			'c.active, '.
			'sort_order, '.
			'count(bug_id) as bug_count '.
		'from '.
			TBL_COMPONENT.' c '.
			'left join '.TBL_BUG.' b on b.component_id = c.component_id '.
		'where '.
			'c.project_id = %s '.
		'group by '.
			'c.component_id, '.
			'c.component_name, '.
			'c.created_date, '.
			'c.active',
	'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.' b on b.database_id = d.database_id '.
		'group by '.
			'd.database_id, '.
			'database_name, '.
			'sort_order '.
		'order by '.
			'%s %s',
	'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 on ug.group_id = ag.group_id '.
			'left join '.TBL_AUTH_USER.' au on ug.user_id = au.user_id '.
		'where '.
			' %s '.
		'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.' b on s.os_id = b.os_id '.
		'group by '.
			's.os_id, '.
			'os_name, '.
			'regex, '.
			'sort_order '.
		'order by '.
			'%s %s',
	'admin-list-priorities' =>
		'select '.
			'p.priority_id, '.
			'priority_name, '.
			'priority_desc, '.
			'priority_color, '.
			'sort_order, '.
			'count(bug_id) as bug_count '.
		'from '.
			TBL_PRIORITY.' p '.
			'left join '.TBL_BUG.' b on b.priority = p.priority_id '.
		'group by '.
			'p.priority_id, '.
			'priority_name, '.
			'priority_desc, '.
			'priority_color, '.
			'sort_order '.
		'order by '.
			'%s %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.' b on b.resolution_id = s.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.' b on b.severity_id = s.severity_id '.
		'group by '.
			's.severity_id, '.
			'severity_name, '.
			'severity_desc, '.
			'severity_color, '.
			'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.' b on b.site_id = s.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, '.
			'bug_open, '.
			'count(bug_id) as bug_count '.
		'from '.
			TBL_STATUS.' s '.
			'left join '.TBL_BUG.' b on b.status_id = s.status_id '.
		'group by '.
			's.status_id, '.
			'status_name, '.
			'status_desc, '.
			'sort_order, '.
			'bug_open '.
		'order by '.
			'%s %s',
	'admin-list-versions' =>
		'select '.
			'v.version_id, '.
			'v.version_name, '.
			'v.created_date, '.
			'v.active, '.
			'sort_order, '.
			'count(bug_id) as bug_count '.
		'from '.
			TBL_VERSION.' v '.
			'left join '.TBL_BUG.' b on b.version_id = v.version_id '.
		'where '.
			'v.project_id = %s '.
		'group by '.
			'v.version_id, '.
			'v.version_name, '.
			'v.created_date, '.
			'v.active',
	'admin-show-component' =>
		'select '.
			'c.*, '.
			'p.project_name as project_name '.
		'from '.
			TBL_COMPONENT.' c  '.
			'left join '.TBL_PROJECT.' p on p.project_id = c.project_id '.
		'where '.
			'component_id = \'%s\'',
	'admin-show-version' =>
		'select '.
			'v.*, '.
			'p.project_name as project_name '.
		'from '.
			TBL_VERSION.' v '.
			'left join '.TBL_PROJECT.' p on p.project_id = v.project_id'.
		'where '.
			'version_id = \'%s\'',
	'admin-user-groups' =>
		'select '.
			'ug.group_id '.
		'from '.
			TBL_USER_GROUP.' ug '.
			'left join '.TBL_AUTH_GROUP.' g on g.group_id = ug.group_id '.
		'where '.
			'user_id = %s ',
	'bug-cc-list' =>
		'select '.
			'email '.
		'from '.
			TBL_BUG_CC.' b '.
			'left join '.TBL_AUTH_USER.' u on u.user_id = b.user_id, '.
			TBL_USER_PREF.' p '.
		'where '.
			'bug_id = %s '.
			'and u.user_id = p.user_id '.
			'and email_notices = 1',
	'bug-history' =>
		'select '.
			'bh.*, '.
			'login '.
		'from '.
			TBL_BUG_HISTORY.' bh '.
			'left join '.TBL_AUTH_USER.' on bh.created_by = user_id '.
		'where '.
			'bug_id = %s '.
		'order by '.
			'bh.created_date',
	'bug-printable' =>
		'select '.
			'b.*, '.
			'reporter.login as reporter, '.
			'owner.login as owner, '.
			'project_name, '.
			'component_name, '.
			'version_name, '.
			'severity_name, '.
			'priority_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_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_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_PRIORITY.' priority, '.
			TBL_COMPONENT.' c, '.
			TBL_PROJECT.' p '.
		'where '.
			'b.bug_id = %s '.
			'and b.project_id not in (%s) '.
			'and b.severity_id = sv.severity_id '.
			'and b.priority = priority.priority_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-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_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '.
			'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '.
			TBL_SEVERITY.' sv, '.
			TBL_STATUS.' st, '.
			TBL_SITE.' site, '.
			TBL_PRIORITY.' prio '.
		'where '.
			'b.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'.
			'and b.priority = prio.priority_id',
	'functions-bug-cc' =>
		'select '.
			'b.user_id, '.
                        'login '.
		'from '.
			TBL_BUG_CC.' b '.
			'left join '.TBL_AUTH_USER.' a on a.user_id = b.user_id '.
		'where '.
			'bug_id = %s',
	'functions-project-js' =>
		'select '.
			'p.project_id, '.
			'p.project_name '.
		'from '.
			TBL_PROJECT.' p '.
			'left join '.TBL_PROJECT_GROUP.' on pg.project_id = p.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',
	'include-template-bookmark' =>
		"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 on s.status_id = b.status_id ".
			TBL_BOOKMARK." w ".
		"where ".
			"w.user_id=%s ".
			"AND w.bug_id = b.bug_id",

	'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 on s.status_id = b.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 on s.status_id = b.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.
		" order by sort_order",
	'index-projsummary-6' =>
		'%s, count(bug_id) as "Total" '.
		'from '.TBL_BUG.' b '.
		'left join '.TBL_PROJECT.' p on b.project_id = p.project_id '.
		'where b.project_id not in (%s) '.
		'group by b.project_id, project_name '.
		'order by project_name',
	'join-where' =>
		'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_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_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 '.
			'%s, '.
			TBL_SEVERITY.' severity, '.
			TBL_STATUS.' status, '.
			TBL_OS.' os, '.
			TBL_SITE.' site, '.
			TBL_VERSION.' version, '.
			TBL_COMPONENT.' component, '.
			TBL_PROJECT.' project, '.
			TBL_PRIORITY.' priority '.
		'where '.
			'b.severity_id = severity.severity_id '.
			'and b.priority = priority.priority_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 '.
		'group by '.
			'b.bug_id '.
		'order by '.
			'%s %s, '.
			'b.bug_id asc',
	'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 '.
			'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id ',
	'query-list-bugs-count-join' =>
		'where ',
	'report-resbyeng-1' =>
		'select u.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',
	);

?>