public function getByParameters($parameters, $securitySchemeUserId = null, $queryWherePart = null, $loggedInUserId = null)
{
$parameterType = '';
$parameterArray = array();
$query = 'SELECT SQL_CALC_FOUND_ROWS issue_main_table.id, issue_main_table.nr, issue_main_table.summary, issue_main_table.description, issue_main_table.environment, ' . 'user_reported.id as reporter, user_reported.first_name as ur_first_name, user_reported.last_name as ur_last_name, user_reported.avatar_picture as reporter_avatar_picture, ' . 'user_assigned.first_name as ua_first_name, user_assigned.last_name as ua_last_name, user_assigned.id as assignee, user_assigned.avatar_picture as assignee_avatar_picture, ' . 'project.code as project_code, project.name as project_name, issue_main_table.project_id as issue_project_id, issue_type.id as issue_type_id, issue_type.icon_name as issue_type_icon_name, issue_type.description as issue_type_description, ' . 'issue_priority.id as priority, issue_priority.color as priority_color, issue_priority.icon_name as issue_priority_icon_name, issue_priority.description as issue_priority_description, issue_priority.name as priority_name, ' . 'issue_status.id as status, issue_status.name as status_name, ' . 'issue_type.id as type, issue_type.name as type_name, ' . 'issue_resolution.name as resolution_name, issue_main_table.resolution_id as resolution, issue_main_table.parent_id, issue_main_table.security_scheme_level_id as security_level, ' . 'issue_security_scheme_level.name as security_level_name, ' . 'issue_main_table.user_assigned_id as issue_assignee, ' . 'issue_main_table.original_estimate, issue_main_table.remaining_estimate, issue_main_table.user_reported_ip, ';
if (isset($parameters['sprint'])) {
$query .= 'issue_parent.status_id as parent_status_id, issue_parent.user_assigned_id as parent_assignee, ' . 'issue_parent.nr as parent_nr, issue_parent.summary as parent_summary, project_parent.code as parent_project_code, ' . 'CONCAT(coalesce(issue_parent.id, \'z\'), issue_main_table.id) as sort_sprint, ';
}
if ($securitySchemeUserId) {
// deal with security scheme level
// 1. user in security scheme level data
$query .= '(SELECT max(issue_security_scheme_level_data.id)
from issue_security_scheme_level_data
left join general_user on general_user.id = issue_security_scheme_level_data.user_id
where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id
and general_user.id = ?) as security_check1, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 2. user in group security scheme level data
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data ' . 'left join `general_group` on general_group.id = issue_security_scheme_level_data.group_id ' . 'left join `general_group_data` on general_group_data.group_id = `general_group`.id ' . 'left join general_user on general_user.id = general_group_data.user_id ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'general_user.id = ?) as security_check2, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 3. permission role in security scheme level data - user
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data ' . 'left join project_role_data on project_role_data.permission_role_id = issue_security_scheme_level_data.permission_role_id ' . 'left join general_user on general_user.id = project_role_data.user_id ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'general_user.id = ?) as security_check3, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 4. permission role in security scheme level data - group
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data ' . 'left join project_role_data on project_role_data.permission_role_id = issue_security_scheme_level_data.permission_role_id ' . 'left join `general_group` on general_group.id = project_role_data.group_id ' . 'left join `general_group_data` on general_group_data.group_id = `general_group`.id ' . 'left join general_user on general_user.id = general_group_data.user_id ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'general_user.id = ?) as security_check4, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 5. current_assignee in security scheme level data
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data, general_user ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'issue_security_scheme_level_data.current_assignee is not null and ' . 'issue_main_table.user_assigned_id is not null and ' . 'issue_main_table.user_assigned_id = general_user.id and ' . 'general_user.id = ?) as security_check5, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 6. reporter in security scheme level data
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data, general_user ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'issue_security_scheme_level_data.reporter is not null and ' . 'issue_main_table.user_reported_id is not null and ' . 'issue_main_table.user_reported_id = general_user.id and ' . 'general_user.id = ?) as security_check6, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
// 7. project_lead in security scheme level data
$query .= '(SELECT max(issue_security_scheme_level_data.id) ' . 'from issue_security_scheme_level_data, project, general_user ' . 'where issue_security_scheme_level_data.issue_security_scheme_level_id = issue_main_table.security_scheme_level_id and ' . 'project.id = issue_main_table.project_id and ' . 'project.lead_id = general_user.id and ' . 'issue_security_scheme_level_data.project_lead is not null and ' . 'project.lead_id is not null and ' . 'general_user.id = ?) as security_check7, ';
$parameterType .= 'i';
$parameterArray[] = $securitySchemeUserId;
}
$query .= 'issue_main_table.date_created, issue_main_table.date_updated, issue_main_table.date_resolved, issue_main_table.date_due as due_date ' . 'from yongo_issue as issue_main_table ' . 'LEFT JOIN issue_priority ON issue_main_table.priority_id = issue_priority.id ' . 'LEFT JOIN issue_type ON issue_main_table.type_id = issue_type.id ' . 'LEFT JOIN issue_status ON issue_main_table.status_id = issue_status.id ' . 'LEFT JOIN issue_resolution ON issue_main_table.resolution_id = issue_resolution.id ' . 'LEFT JOIN issue_component ON issue_main_table.id = issue_component.issue_id ' . 'LEFT JOIN issue_version ON issue_main_table.id = issue_version.issue_id ' . 'LEFT JOIN project ON issue_main_table.project_id = project.id ' . 'left join permission_scheme_data on permission_scheme_data.permission_scheme_id = project.permission_scheme_id ' . 'LEFT join general_user AS user_reported ON issue_main_table.user_reported_id = user_reported.id ' . 'LEFT join general_user AS user_assigned ON issue_main_table.user_assigned_id = user_assigned.id ' . 'LEFT JOIN issue_security_scheme_level ON issue_security_scheme_level.id = issue_main_table.security_scheme_level_id ' . 'LEFT JOIN yongo_issue_sla ON yongo_issue_sla.yongo_issue_id = issue_main_table.id ';
if (isset($parameters['backlog'])) {
$query .= 'LEFT JOIN agile_board_sprint_issue ON agile_board_sprint_issue.issue_id = issue_main_table.id ';
$query .= 'LEFT JOIN agile_board_sprint ON agile_board_sprint.id = agile_board_sprint_issue.agile_board_sprint_id ';
}
if (isset($parameters['sprint'])) {
$query .= 'LEFT JOIN agile_board_sprint_issue ON agile_board_sprint_issue.issue_id = issue_main_table.id ';
$query .= 'LEFT JOIN yongo_issue issue_parent on issue_parent.id = issue_main_table.parent_id ';
$query .= 'LEFT JOIN project project_parent on project_parent.id = issue_parent.project_id ';
}
$queryWhere = '';
if (isset($parameters['search_query']) && $parameters['search_query'] != '') {
$query_where_part_arr = array();
if (isset($parameters['summary_flag']) && $parameters['summary_flag'] == 1) {
$query_where_part_arr[] = " issue_main_table.summary LIKE ? ";
$parameterType .= 's';
$parameterArray[] = "%" . $parameters['search_query'] . "%";
}
if (isset($parameters['description_flag']) && $parameters['description_flag'] == 1) {
$query_where_part_arr[] = " issue_main_table.description LIKE ? ";
$parameterType .= 's';
$parameterArray[] = "%" . $parameters['search_query'] . "%";
}
// also search in assignee and reporter
$query_where_part_arr[] = " CONCAT(user_assigned.first_name, user_assigned.last_name) LIKE ? ";
$parameterType .= 's';
$parameterArray[] = "%" . $parameters['search_query'] . "%";
$query_where_part_arr[] = " CONCAT(user_reported.first_name, user_reported.last_name) LIKE ? ";
$parameterType .= 's';
$parameterArray[] = "%" . $parameters['search_query'] . "%";
if ($query_where_part_arr) {
$queryWhere .= '(' . implode(' OR ', $query_where_part_arr) . ') AND ';
}
}
if (isset($parameters['project'])) {
if (is_array($parameters['project'])) {
$projectWithAssigneeReporterBrowsePermission = array();
$queryProjectPartReporter = array();
$queryProjectPartAssignee = array();
for ($i = 0; $i < count($parameters['project']); $i++) {
$permissions = UbirimiContainer::get()['repository']->get(PermissionScheme::class)->getDataByProjectIdAndPermissionId($parameters['project'][$i], Permission::PERM_BROWSE_PROJECTS);
while ($permissions && ($permission = $permissions->fetch_array(MYSQLI_ASSOC))) {
if ($permission['reporter'] == 1) {
$queryProjectPartReporter[] = '(issue_main_table.user_reported_id = ' . $loggedInUserId . ' and issue_main_table.project_id = ' . $parameters['project'][$i] . ')';
$projectWithAssigneeReporterBrowsePermission[] = $parameters['project'][$i];
}
if ($permission['current_assignee'] == 1) {
$queryProjectPartAssignee[] = '(issue_main_table.user_assigned_id = ' . $loggedInUserId . ' and issue_main_table.project_id = ' . $parameters['project'][$i] . ')';
$projectWithAssigneeReporterBrowsePermission[] = $parameters['project'][$i];
}
}
}
$projectWithAssigneeReporterBrowsePermission = array_unique($projectWithAssigneeReporterBrowsePermission);
$queryWhereReporter = '';
$queryWhereAssignee = '';
if (count($queryProjectPartReporter)) {
$queryWhereReporter = '(' . implode(' OR ', $queryProjectPartReporter) . ')';
}
if (count($queryProjectPartAssignee)) {
$queryWhereAssignee = '(' . implode(' OR ', $queryProjectPartAssignee) . ')';
}
$queryPartReporterAssignee = array();
if (!empty($queryWhereReporter)) {
$queryPartReporterAssignee[] = $queryWhereReporter;
}
if (!empty($queryWhereAssignee)) {
$queryPartReporterAssignee[] = $queryWhereAssignee;
}
if (count($projectWithAssigneeReporterBrowsePermission)) {
if (Util::array_equal($parameters['project'], $projectWithAssigneeReporterBrowsePermission)) {
$queryWhere .= ' (issue_main_table.project_id IN (' . implode(',', $parameters['project']) . ') OR ';
} else {
$queryWhere .= ' (issue_main_table.project_id IN (' . implode(',', array_diff($parameters['project'], $projectWithAssigneeReporterBrowsePermission)) . ') OR ';
}
$queryWhere .= '(' . implode(' OR ', $queryPartReporterAssignee) . ')) ';
$queryWhere .= ' AND ';
} else {
$queryWhere .= ' issue_main_table.project_id IN (' . implode(',', $parameters['project']) . ') AND ';
}
} else {
$queryWhere .= ' issue_main_table.project_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['project'];
}
}
if (isset($parameters['issue_id'])) {
if (is_array($parameters['issue_id'])) {
$queryWhere .= ' issue_main_table.id IN (' . implode(', ', $parameters['issue_id']) . ') AND ';
} else {
$queryWhere .= ' issue_main_table.id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['issue_id'];
}
}
if (isset($parameters['nr'])) {
$queryWhere .= ' issue_main_table.nr = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['nr'];
}
if (isset($parameters['code_nr'])) {
$queryWhere .= " CONCAT(project.code, '-', issue_main_table.nr) = ? AND ";
$parameterType .= 's';
$parameterArray[] = $parameters['code_nr'];
}
if (isset($parameters['security_scheme_level'])) {
if ($parameters['security_scheme_level'] == -1) {
$queryWhere .= ' issue_main_table.security_scheme_level_id IS NULL AND ';
} else {
$queryWhere .= ' issue_main_table.security_scheme_level_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['security_scheme_level'];
}
}
if (isset($parameters['sprint'])) {
$queryWhere .= ' agile_board_sprint_issue.id is not null and agile_board_sprint_issue.agile_board_sprint_id = ? and ';
$parameterType .= 'i';
$parameterArray[] = $parameters['sprint'];
}
if (isset($parameters['board_id'])) {
$queryWhere .= ' agile_board_sprint.agile_board_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['board_id'];
}
if (isset($parameters['parent_id'])) {
if (is_array($parameters['parent_id'])) {
$queryWhere .= ' issue_main_table.parent_id IN (' . implode(', ', $parameters['parent_id']) . ') AND ';
} else {
$queryWhere .= ' issue_main_table.parent_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['parent_id'];
}
}
if (isset($parameters['backlog'])) {
$queryWhere .= ' issue_main_table.parent_id is null and agile_board_sprint_issue.issue_id IS NULL and ';
}
if (isset($parameters['helpdesk_flag'])) {
$queryWhere .= ' issue_main_table.helpdesk_flag = 1 and ';
}
if (isset($parameters['component'])) {
if (is_array($parameters['component'])) {
if (!in_array(-1, $parameters['component'])) {
$queryWhere .= ' issue_component.project_component_id IN (' . implode(',', $parameters['component']) . ') AND ';
}
} else {
if ($parameters['component'] != -1) {
$queryWhere .= ' issue_component.project_component_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['component'];
} else {
$queryWhere .= ' issue_component.project_component_id is null and ';
}
}
}
if (isset($parameters['version'])) {
if (is_array($parameters['version'])) {
if (!in_array(-1, $parameters['version'])) {
$queryWhere .= ' issue_version.project_version_id IN (' . implode(',', $parameters['version']) . ') AND ';
}
} else {
$queryWhere .= ' issue_version.project_version_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['version'];
}
}
if (isset($parameters['fix_version'])) {
if (is_array($parameters['fix_version'])) {
if (!in_array(-1, $parameters['fix_version'])) {
$queryWhere .= ' issue_version.project_version_id IN (' . implode(',', $parameters['fix_version']) . ') AND issue_version.affected_targeted_flag = ' . Issue::ISSUE_FIX_VERSION_FLAG . ' AND ';
}
} else {
$queryWhere .= ' issue_version.project_version_id = ? AND issue_version.affected_targeted_flag = ' . Issue::ISSUE_FIX_VERSION_FLAG . ' AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['fix_version'];
}
}
if (isset($parameters['affects_version'])) {
if (is_array($parameters['affects_version'])) {
if (!in_array(-1, $parameters['affects_version'])) {
$queryWhere .= ' issue_version.project_version_id IN (' . implode(',', $parameters['affects_version']) . ') AND issue_version.affected_targeted_flag = ' . Issue::ISSUE_AFFECTED_VERSION_FLAG . ' AND ';
}
} else {
$queryWhere .= ' issue_version.project_version_id = ? AND issue_version.affected_targeted_flag = ' . Issue::ISSUE_FIX_VERSION_FLAG . ' AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['affects_version'];
}
}
if (array_key_exists('assignee', $parameters)) {
if (is_array($parameters['assignee'])) {
for ($index = 0; $index < count($parameters['assignee']); $index++) {
if ($parameters['assignee'][$index] == 'current_user') {
$parameters['assignee'][$index] = $securitySchemeUserId;
}
}
if (!in_array(-1, $parameters['assignee'])) {
$queryWhere .= ' issue_main_table.user_assigned_id IN (' . implode(',', $parameters['assignee']) . ') AND ';
}
} else {
if ($parameters['assignee']) {
if ($parameters['assignee'] == 'current_user') {
$parameters['assignee'] = $securitySchemeUserId;
}
$queryWhere .= ' issue_main_table.user_assigned_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['assignee'];
} else {
if ($parameters['assignee'] === 0) {
$queryWhere .= ' issue_main_table.user_assigned_id is null and ';
}
}
}
}
if (array_key_exists('not_assignee', $parameters)) {
$queryWhere .= ' issue_main_table.user_assigned_id != ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['not_assignee'];
}
if (array_key_exists('date_due', $parameters)) {
$queryWhere .= ' issue_main_table.date_due = ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_due'];
}
if (array_key_exists('date_due_after', $parameters) && isset($parameters['date_due_after'])) {
$queryWhere .= ' issue_main_table.date_due >= ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_due_after'];
}
if (array_key_exists('date_due_before', $parameters) && isset($parameters['date_due_before'])) {
$queryWhere .= ' issue_main_table.date_due <= ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_due_before'];
}
if (array_key_exists('date_created_after', $parameters) && isset($parameters['date_created_after'])) {
$queryWhere .= ' issue_main_table.date_created >= ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_created_after'];
}
if (array_key_exists('date_updated_after', $parameters) && isset($parameters['date_updated_after'])) {
$queryWhere .= ' issue_main_table.date_updated >= ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_updated_after'];
}
if (array_key_exists('date_created_before', $parameters) && isset($parameters['date_created_before'])) {
$queryWhere .= ' issue_main_table.date_created <= ? AND ';
$parameterType .= 's';
$parameterArray[] = $parameters['date_created_before'];
}
if (isset($parameters['reporter'])) {
if (is_array($parameters['reporter'])) {
if (!in_array(-1, $parameters['reporter'])) {
$queryWhere .= ' issue_main_table.user_reported_id IN (' . implode(',', $parameters['reporter']) . ') AND ';
}
} else {
$queryWhere .= ' issue_main_table.user_reported_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['reporter'];
}
}
if (isset($parameters['client_id'])) {
$queryWhere .= ' project.client_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['client_id'];
}
if (isset($parameters['type'])) {
if (is_array($parameters['type'])) {
if (!in_array(-1, $parameters['type'])) {
$queryWhere .= ' issue_main_table.type_id IN (' . implode(',', $parameters['type']) . ') AND ';
}
} else {
$queryWhere .= ' issue_main_table.type_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['type'];
}
}
if (isset($parameters['priority'])) {
if (is_array($parameters['priority'])) {
if (!in_array(-1, $parameters['priority'])) {
$queryWhere .= ' issue_main_table.priority_id IN (' . implode(',', $parameters['priority']) . ') AND ';
}
} else {
$queryWhere .= ' issue_main_table.priority_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['priority'];
}
}
if (isset($parameters['status'])) {
if (is_array($parameters['status'])) {
if (!in_array(-1, $parameters['status'])) {
$queryWhere .= ' issue_main_table.status_id IN (' . implode(',', $parameters['status']) . ') AND ';
}
} else {
$queryWhere .= ' issue_main_table.status_id = ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['status'];
}
}
if (isset($parameters['not_status'])) {
if (is_array($parameters['not_status'])) {
$queryWhere .= ' issue_main_table.status_id NOT IN (' . implode(',', $parameters['not_status']) . ') AND ';
} else {
$queryWhere .= ' issue_main_table.status_id != ? AND ';
$parameterType .= 'i';
$parameterArray[] = $parameters['not_status'];
}
}
if (isset($parameters['resolution'])) {
$includeUnresolvedIssues = false;
$includeAllResolutions = false;
// any resolution
$index = array_search(-1, $parameters['resolution']);
if ($index !== false) {
$includeAllResolutions = true;
unset($parameters['resolution'][$index]);
}
// unresolved issues
$index = array_search(-2, $parameters['resolution']);
if ($index !== false) {
$includeUnresolvedIssues = true;
unset($parameters['resolution'][$index]);
}
$queryResolutionPart = array();
if ($includeAllResolutions) {
$queryResolutionPart[] = ' issue_main_table.resolution_id IS NOT NULL ';
}
if (count($parameters['resolution'])) {
$queryResolutionPart[] = ' issue_main_table.resolution_id IN (' . implode(',', $parameters['resolution']) . ') ';
}
if ($includeUnresolvedIssues) {
$queryResolutionPart[] = ' issue_main_table.resolution_id IS NULL ';
}
if (count($queryResolutionPart)) {
$queryWhere .= '( ' . implode(' OR ', $queryResolutionPart) . ' ) ';
}
}
if (strtoupper(substr($queryWhere, strlen($queryWhere) - 4, 4)) == 'AND ') {
$queryWhere = substr($queryWhere, 0, strlen($queryWhere) - 4);
}
if (strtoupper(substr($queryWherePart, strlen($queryWherePart) - 4, 4)) == 'AND ') {
$queryWherePart = substr($queryWherePart, 0, strlen($queryWherePart) - 4);
}
$sortColumns = array();
if (isset($parameters['sort'])) {
switch ($parameters['sort']) {
case 'code':
$sortColumns[] = 'issue_main_table.id';
break;
case 'type':
$sortColumns[] = 'type';
break;
case 'priority':
$sortColumns[] = 'priority';
break;
case 'status':
$sortColumns[] = 'status';
break;
case 'reporter':
$sortColumns[] = 'ur_first_name';
$sortColumns[] = 'ur_last_name';
break;
case 'summary':
$sortColumns[] = 'issue_main_table.summary';
break;
case 'reported_by':
$sortColumns[] = 'user_reported.id';
break;
case 'assignee':
$sortColumns[] = 'ua_first_name';
$sortColumns[] = 'ua_last_name';
break;
case 'created':
$sortColumns[] = 'issue_main_table.date_created';
break;
case 'updated':
$sortColumns[] = 'issue_main_table.date_updated';
break;
case 'parent':
$sortColumns[] = 'issue_main_table.parent_id';
break;
case 'sprint':
$sortColumns[] = 'sort_sprint';
break;
}
} else {
$sortColumns[] = 'issue_main_table.date_created';
}
if ($queryWherePart) {
$queryWhere .= $queryWherePart;
}
if ($queryWhere != '') {
$query .= ' WHERE ' . $queryWhere;
}
$query .= ' GROUP BY issue_main_table.id ';
if ($securitySchemeUserId) {
$query .= ' HAVING ((security_check1 > 0 or security_check2 > 0 or security_check3 > 0 or security_check4 > 0 or security_check5 > 0 or security_check6 > 0 or security_check7 > 0) ' . ' OR (issue_main_table.security_scheme_level_id is null and security_check1 is null and security_check2 is null and security_check3 is null and security_check4 is null and security_check5 is null and security_check6 is null and security_check7 is null)) ';
}
if (isset($parameters['sort']) && isset($parameters['sort_order']) && $sortColumns) {
for ($i = 0; $i < count($sortColumns); $i++) {
$sortColumns[$i] = $sortColumns[$i] . ' ' . $parameters['sort_order'];
}
}
if (count($sortColumns)) {
$query .= 'ORDER BY ' . implode(', ', $sortColumns);
}
if (isset($parameters['page'])) {
$query .= ' LIMIT ' . ($parameters['page'] - 1) * $parameters['issues_per_page'] . ', ' . $parameters['issues_per_page'];
}
// $queryTest = $query;
// for ($p = 0; $p < count($parameterArray); $p++) {
// $queryTest = preg_replace('/\?/', $parameterArray[$p], $queryTest, 1);
// }
// echo $querytest;
$stmt = UbirimiContainer::get()['db.connection']->prepare($query);
if ($queryWhere != '') {
$param_arr_ref = array();
foreach ($parameterArray as $key => $value) {
$param_arr_ref[$key] =& $parameterArray[$key];
}
if ($parameterType != '') {
call_user_func_array(array($stmt, "bind_param"), array_merge(array($parameterType), $param_arr_ref));
}
}
$stmt->execute();
$result = $stmt->get_result();
if (!$result->num_rows) {
return null;
}
if (isset($parameters['page'])) {
$q = "SELECT FOUND_ROWS() as count;";
$stmt = UbirimiContainer::get()['db.connection']->prepare($q);
$stmt->execute();
$result_total = $stmt->get_result();
$count = $result_total->fetch_array(MYSQLI_ASSOC);
return array($result, $count['count']);
} else {
if (isset($parameters['issue_id']) && !is_array($parameters['issue_id']) || isset($parameters['nr'])) {
$issueData = $result->fetch_array(MYSQLI_ASSOC);
$issueData['component'] = array();
$issueData['component_ids'] = array();
$issueData['affects_version'] = array();
$issueData['affects_version_ids'] = array();
$issueData['fix_version'] = array();
$issueData['fix_version_ids'] = array();
$components = UbirimiContainer::get()['repository']->get(IssueComponent::class)->getByIssueIdAndProjectId($issueData['id'], $issueData['issue_project_id'], 'array');
for ($i = 0; $i < count($components); $i++) {
$issueData['component'][] = $components[$i]['name'];
$issueData['component_ids'][] = $components[$i]['id'];
}
$affectsVersions = UbirimiContainer::get()['repository']->get(IssueVersion::class)->getByIssueIdAndProjectId($issueData['id'], $issueData['issue_project_id'], Issue::ISSUE_AFFECTED_VERSION_FLAG, 'array');
for ($i = 0; $i < count($affectsVersions); $i++) {
$issueData['affects_version'][] = $affectsVersions[$i]['name'];
$issueData['affects_version_ids'][] = $affectsVersions[$i]['id'];
}
$fixVersions = UbirimiContainer::get()['repository']->get(IssueVersion::class)->getByIssueIdAndProjectId($issueData['id'], $issueData['issue_project_id'], Issue::ISSUE_FIX_VERSION_FLAG, 'array');
for ($i = 0; $i < count($fixVersions); $i++) {
$issueData['fix_version'][] = $fixVersions[$i]['name'];
$issueData['fix_version_ids'][] = $fixVersions[$i]['id'];
}
return $issueData;
} else {
return $result;
}
}
}
