<?php
// oci8.php - Oracle queries
// Maintained by Nitai Fraenkel
$QUERY = array(
'admin-list-components' =>
'select '.
'c.component_id, '.
'component_name, '.
'c.created_date, '.
'owner, '.
'active, '.
'sort_order, '.
'count(bug_id) as bug_count '.
'from '.
TBL_COMPONENT.' c, '.
TBL_BUG.' b '.
'where '.
'c.project_id = %s '.
'and c.component_id = b.component_id(+) '.
'group by '.
'c.component_id, '.
'c.component_name, '.
'c.created_date, '.
'c.owner, '.
'c.active, ' .
'sort_order',
'admin-list-databases' =>
'select '.
'd.database_id, '.
'database_name, '.
'sort_order, '.
'count(bug_id) as bug_count '.
'from '.
TBL_DATABASE.' d, '.
TBL_BUG.' b '.
'where '.
'd.database_id = b.database_id(+) '.
'group by '.
'd.database_id, '.
'database_name, '.
'sort_order '.
'order by '.
'%s %s',
'admin-list-groups' =>
'select '.
'ag.group_id, '.
'ag.group_name, '.
'ag.locked, '.
'count(ug.group_id) as count '.
'from '.
TBL_AUTH_GROUP.' ag, '.
TBL_USER_GROUP.' ug, '.
TBL_AUTH_USER.' au '.
'where '.
'ag.group_id = ug.group_id(+) '.
'and ug.user_id = au.user_id(+) '.
'and %s '.
'group by '.
'ag.group_id, '.
'ag.group_name, '.
'ag.locked '.
'order by '.
'%s %s',
'admin-list-oses' =>
'select '.
's.os_id, '.
's.os_name, '.
's.regex, '.
's.sort_order, '.
'count(b.bug_id) as bug_count '.
'from '.
TBL_OS.' s, '.
TBL_BUG.' b '.
'where '.
's.os_id = b.os_id(+) '.
'group by '.
's.os_id, '.
's.os_name, '.
's.regex, '.
's.sort_order '.
'order by '.
's.%s %s',
'admin-list-priorities' =>
'select '.
'p.priority_id, '.
'p.priority_name, '.
'p.priority_desc, '.
'p.priority_color, '.
'p.sort_order, '.
'count(b.bug_id) as bug_count '.
'from '.
TBL_PRIORITY.' p, '.
TBL_BUG.' b '.
'where '.
'p.priority_id = b.priority(+) '.
'group by '.
'p.priority_id, '.
'p.priority_name, '.
'p.priority_desc, '.
'p.priority_color, '.
'p.sort_order '.
'order by '.
'p.%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, '.
TBL_BUG.' b '.
'where '.
's.resolution_id = b.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, '.
TBL_BUG.' b '.
'where '.
's.severity_id = b.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, '.
TBL_BUG.' b '.
'where '.
's.site_id = b.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, '.
TBL_BUG.' b '.
'where '.
's.status_id = b.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, '.
'version_name, '.
'v.created_date, '.
'active, '.
'sort_order, '.
'count(bug_id) as bug_count '.
'from '.
TBL_VERSION.' v, '.
TBL_BUG.' b '.
'where '.
'v.project_id = %s '.
'and v.version_id = b.version_id(+) '.
'group by '.
'v.version_id, '.
'v.version_name, '.
'v.created_date, '.
'v.active, '.
'sort_order',
'admin-show-component' =>
'select '.
'c.*, '.
'p.project_name as project_name '.
'from '.
TBL_COMPONENT.' c, '.
TBL_PROJECT.' p '.
'where '.
'p.project_id = c.project_id(+) '.
'and component_id = \'%s\'',
'admin-show-version' =>
'select '.
'v.*, '.
'p.project_name as project_name '.
'from '.
TBL_VERSION.' v, '.
TBL_PROJECT.' p '.
'where '.
'p.project_id = v.project_id(+) '.
'and version_id = \'%s\'',
'admin-user-groups' =>
'select '.
'ug.group_id '.
'from '.
TBL_USER_GROUP.' ug, '.
TBL_AUTH_GROUP.' g '.
'where '.
'g.group_id = ug.group_id(+) '.
'and user_id = %s ',
'bug-cc-list' =>
'select '.
'email '.
'from '.
TBL_BUG_CC.' bc, '.
TBL_AUTH_USER.' u, '.
TBL_USER_PREF.' p '.
'where '.
'u.user_id = bc.user_id(+) '.
'and u.user_id = p.user_id '.
'and email_notices = 1 '.
'and bug_id = %s',
'bug-history' =>
'select '.
'bh.*, '.
'login '.
'from '.
TBL_BUG_HISTORY.' bh, '.
TBL_AUTH_USER.' '.
'where '.
'user_id = bh.created_by(+) '.
'and bug_id = %s '.
'order by '.
'bh.created_date',
'bug-printable' =>
'select '.
'b.*, '.
'reporter.login as reporter, '.
'owner.login as owner, '.
'p.project_name, '.
'c.component_name, '.
'v.version_name, '.
's.severity_name, '.
'o.os_name, '.
's.status_name, '.
'r.resolution_name '.
'from '.
TBL_BUG.' b, '.
TBL_AUTH_USER.' owner, '.
TBL_AUTH_USER.' reporter, '.
TBL_BOOKMARK.' bookmark, '.
TBL_RESOLUTION.' r, '.
TBL_SEVERITY.' sv, '.
TBL_STATUS.' st, '.
TBL_OS.' os, '.
TBL_VERSION.' v, '.
TBL_PRIORITY.' priority, '.
TBL_COMPONENT.' c, '.
TBL_PROJECT.' p '.
'where '.
'b.assigned_to = owner.user_id(+) '.
'and b.created_by = reporter.user_id(+) '.
'and b.bug_id = bookmark.bug_id(+) '.
'and b.resolution_id = r.resolution_id(+) '.
'and 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, '.
'st.status_name, '.
'r.resolution_name '.
'from '.
TBL_BUG.' b, '.
TBL_AUTH_USER.' owner, '.
TBL_AUTH_USER.' reporter, '.
TBL_BOOKMARK.' bookmark, '.
TBL_RESOLUTION.' r, '.
TBL_SEVERITY.' sv, '.
TBL_STATUS.' st, '.
TBL_SITE.' site, '.
TBL_PRIORITY.' prio '.
'where '.
'b.resolution_id = r.resolution_id(+) '.
'and b.assigned_to = owner.user_id(+) '.
'and b.created_by = reporter.user_id(+) '.
'and b.bug_id = bookmark.bug_id(+) '.
'and 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, '.
TBL_AUTH_USER.' u '.
'where '.
// 26-Feb-2008 IBS - replace commentted out a line with the one following it.
// 'phpbt_auth_user.user_id = b.user_id(+) '.
'u.user_id = b.user_id(+) '.
'and bug_id = %s',
'functions-project-js' =>
'select '.
'p.project_id, '.
'project_name '.
'from '.
TBL_PROJECT.' p, '.
TBL_PROJECT_GROUP.' pg '.
'where '.
'p.project_id = pg.project_id(+) '.
'and 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 ".
// 26-Feb-2008 IBS - Replace commented 2 line2 with the two following it.
// "sum(decode( s.status_id in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ".
// "sum(decode( s.status_id not in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 0, 1 )), ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 1, 0 )) ".
"from ".
TBL_BUG." b, ".
TBL_STATUS." s, ".
TBL_BOOKMARK." w ".
"where ".
"b.status_id = s.status_id (+) ".
"AND w.user_id=%s ".
"AND w.bug_id = b.bug_id",
'include-template-owner' =>
"SELECT ".
// 26-Feb-2008 IBS - Replace commented 2 line2 with the two following it.
// "sum(decode( s.status_id in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ".
// "sum(decode( s.status_id not in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 0, 1 )), ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 1, 0 )) ".
'from '.
TBL_BUG.' b, '.
TBL_STATUS.' s '.
'where '.
'b.status_id = s.status_id (+) '.
'and b.assigned_to = %s',
'include-template-reporter' =>
"SELECT ".
// 26-Feb-2008 IBS - Replace commented 2 line2 with the two following it.
// "sum(decode( s.status_id in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )) , ".
// "sum(decode( s.status_id not in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )) ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 0, 1 )), ".
"sum(decode(instr('" .OPEN_BUG_STATUSES."', s.status_id ) , 0, 1, 0 )) ".
'from '.
TBL_BUG.' b, '.
TBL_STATUS.' s '.
'where '.
'b.status_id = s.status_id (+) '.
'and b.created_by = %s',
'index-projsummary-1' =>
'select b.project_id, p.project_name as "Project", '.
'sum(decode( b.resolution_id, 0, 1, 0)) as "Open"',
'index-projsummary-2' =>
"select b.resolution_name, ",
'index-projsummary-3' =>
"', sum(decode( b.resolution_id, '",
'index-projsummary-4' =>
"', 1, 0)) as \"'",
'index-projsummary-5' =>
"from ".TBL_RESOLUTION." b".
" order by b.sort_order",
'index-projsummary-6' =>
"%s, count(bug_id) as \"Total\" ".
'from '.TBL_BUG.' b, '.TBL_PROJECT.' p '.
'where b.project_id = p.project_id(+) '.
'and b.project_id not in (%s) '.
'group by b.project_id, p.project_name '.
'order by p.project_name',
'join-where' =>
'and',
// 26-Feb-2008 IBS Comment out the following two links (invlid group by).
// 04-Oct-2008 BN Comment out COMMENT/ATTACHMENT/VOTE which are aggregates
'query-list-bugs' =>
'select '.
'%s '.
'from '.
TBL_BUG.' b, '.
TBL_AUTH_USER.' owner, '.
TBL_AUTH_USER.' reporter, '.
TBL_AUTH_USER.' lastmodifier, '.
// TBL_COMMENT.' a_comment, '.
// TBL_ATTACHMENT.' attachment, '.
// TBL_BUG_VOTE.' vote, '.
TBL_BOOKMARK.' bookmark, '.
TBL_RESOLUTION.' resolution, '.
TBL_DATABASE.' database, '.
TBL_VERSION.' version2, '.
TBL_VERSION.' version3, '.
TBL_SEVERITY.' severity, '.
TBL_STATUS.' status, '.
TBL_OS.' os, '.
TBL_SITE.' site, '.
TBL_VERSION.' version, '.
TBL_COMPONENT.' component, '.
TBL_PROJECT.' project, '.
TBL_PRIORITY.' priority '.
'%s '. // query.php adds joins here
'where '.
'b.assigned_to = owner.user_id(+) '.
'and b.created_by = reporter.user_id(+) '.
'and b.last_modified_by = lastmodifier.user_id(+) '.
// 'and b.bug_id = a_comment.bug_id(+) '.
// 'and b.bug_id = attachment.bug_id(+) '.
// 'and b.bug_id = vote.bug_id(+) '.
'and b.bug_id = bookmark.bug_id(+) '.
'and b.resolution_id = resolution.resolution_id(+) '.
'and b.database_id = database.database_id(+) '.
'and b.to_be_closed_in_version_id = version2.version_id(+) '.
'and b.closed_in_version_id = version3.version_id(+) '.
'and 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 '. // query.php adds where clauses here
// 'group by '.
// 'b.bug_id '.
'order by '.
'%s %s, '.
'b.bug_id asc',
'query-list-bugs-count' =>
'select '.
'count(*) '.
'from '.
TBL_BUG.' b, '.
TBL_AUTH_USER.' owner, '.
TBL_AUTH_USER.' reporter, '.
TBL_BOOKMARK.' bookmark '.
'where '.
'b.assigned_to = owner.user_id(+) '.
'and b.created_by = reporter.user_id(+) '.
'and b.bug_id = bookmark.bug_id(+) ',
'query-list-bugs-count-join' =>
'and ',
'report-resbyeng-1' =>
'select email as "Assigned To", '.
'sum(decode( b.resolution_id, 0, 1, 0)) as "Open"',
'report-resbyeng-2' =>
"select b.resolution_name, ",
'report-resbyeng-3' =>
"', sum(decode( b.resolution_id, '",
'report-resbyeng-4' =>
"', 1, 0)) as \"'",
'report-resbyeng-5' =>
"from ".TBL_RESOLUTION." b",
'report-resbyeng-6' =>
'%s, count(bug_id) as "Total" '.
'from '.TBL_BUG.' b, '.TBL_AUTH_USER.' u '.
'where b.assigned_to = u.user_id(+) %s '.
'group by assigned_to, u.email',
);
?>