From 49cc431b9caedd37b10d017867b014658cbeafba Mon Sep 17 00:00:00 2001
From: 이민희 <mhlee@maprex.co.kr>
Date: 화, 07 12월 2021 09:31:26 +0900
Subject: [PATCH] - 이슈, 프로젝트 - 담당자 -> 담당부서 수정 - 프로젝트 관리자가 아닐경우 이슈리스트에 본인이 소속된 부서가 담당인 이슈만 표시
---
src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java | 35 +
src/main/resources/mybatis/query-template/project-template.xml | 319 +++++++++++
src/main/resources/mybatis/query-template/workspace-template.xml | 157 +++++
src/main/java/kr/wisestone/owl/service/impl/WidgetServiceImpl.java | 66 ++
src/main/resources/mybatis/query-template/department-template.xml | 3
src/main/java/kr/wisestone/owl/service/impl/ProjectServiceImpl.java | 18
src/main/java/kr/wisestone/owl/web/condition/IssueCondition.java | 10
src/main/java/kr/wisestone/owl/web/condition/ProjectCondition.java | 9
src/main/resources/mybatis/query-template/issue-template.xml | 458 +++++++++++++++++
src/main/java/kr/wisestone/owl/mapper/WidgetMapper.java | 22
src/main/resources/mybatis/query-template/widget-template.xml | 405 ++++++++++++++-
src/main/java/kr/wisestone/owl/mapper/IssueMapper.java | 4
12 files changed, 1,430 insertions(+), 76 deletions(-)
diff --git a/src/main/java/kr/wisestone/owl/mapper/IssueMapper.java b/src/main/java/kr/wisestone/owl/mapper/IssueMapper.java
index d461d7f..5c6f7e8 100644
--- a/src/main/java/kr/wisestone/owl/mapper/IssueMapper.java
+++ b/src/main/java/kr/wisestone/owl/mapper/IssueMapper.java
@@ -17,6 +17,8 @@
public interface IssueMapper {
List<Map<String, Object>> find(IssueCondition issueCondition);
+ List<Map<String, Object>> findByDepartment(IssueCondition issueCondition);
+
List<Map<String, Object>> findByCustomFieldValue(IssueCustomFieldValueCondition issueCustomFieldValueCondition);
Long count(IssueCondition issueCondition);
@@ -46,5 +48,7 @@
List<Map<String, Object>> getAllTaskUser(IssueCondition taskCondition);
Long countByIssueTypeIdAndDate(IssueTypeCondition issueTypeCondition);
+
+ Long countByDepartment(IssueCondition issueCondition);
}
diff --git a/src/main/java/kr/wisestone/owl/mapper/WidgetMapper.java b/src/main/java/kr/wisestone/owl/mapper/WidgetMapper.java
index 46fdab8..fad95ab 100644
--- a/src/main/java/kr/wisestone/owl/mapper/WidgetMapper.java
+++ b/src/main/java/kr/wisestone/owl/mapper/WidgetMapper.java
@@ -80,4 +80,26 @@
List<Map<String, Object>> findSeverityIssues(WidgetCondition widgetCondition);
//以묒슂�룄 �뱶濡��떎�슫 蹂� �빆紐� 媛��닔
Long countSearchIssue(WidgetCondition widgetCondition);
+
+ Long countRemainIssueByDepartment(WidgetCondition widgetCondition);
+
+ Long countTodayDelayIssueByDepartment(WidgetCondition widgetCondition);
+
+ Long countNoAssigneeIssueByDepartment(WidgetCondition widgetCondition);
+
+ Long countTodayRegisterIssueByDepartment(WidgetCondition widgetCondition);
+
+ Long countAssigneeIssueByDepartment(WidgetCondition widgetCondition);
+
+ Long countCompleteIssueByDepartment(WidgetCondition widgetCondition);
+
+ List<Map<String, Object>> countSeverityIssueByDepartment(WidgetCondition widgetCondition);
+
+ List<Map<String, Object>> findSeverityIssuesByDepartment(WidgetCondition widgetCondition);
+
+ Long countSearchIssueByDepartment(WidgetCondition widgetCondition);
+
+ List<Map<String, Object>> findProjectMemberIssueByDepartment(WidgetCondition widgetCondition);
+
+ List<Map<String, Object>> findMyAssigneeIssueByDepartment(WidgetCondition makeWidgetCondition);
}
diff --git a/src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java
index bb52d12..f87d9fb 100644
--- a/src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java
+++ b/src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java
@@ -18,6 +18,7 @@
import kr.wisestone.owl.mapper.IssueMapper;
import kr.wisestone.owl.mapper.ProjectMapper;
import kr.wisestone.owl.repository.IssueRepository;
+import kr.wisestone.owl.repository.UserDepartmentRepository;
import kr.wisestone.owl.service.*;
import kr.wisestone.owl.util.*;
import kr.wisestone.owl.util.DateUtil;
@@ -163,6 +164,9 @@
@Autowired
private SimpMessagingTemplate simpMessagingTemplate;
+
+ @Autowired
+ private UserDepartmentRepository userDepartmentRepository;
@Override
protected JpaRepository<Issue, Long> getRepository() {
@@ -644,6 +648,19 @@
}
}
+ void SetMyDepartmentId(IssueCondition issueCondition){
+ Long loginId = issueCondition.getLoginUserId();
+ List<Long> myDepartmentIds = Lists.newArrayList();
+ List<UserDepartment> myDepartments = this.userDepartmentRepository.findByUserId(loginId);
+
+ if(myDepartments != null && myDepartments.size() > 0){
+ for(UserDepartment myDepartment : myDepartments){
+ myDepartmentIds.add(myDepartment.getDepartmentId());
+ }
+ }
+ issueCondition.setMyDepartmentIds(myDepartmentIds);
+ }
+
// �씠�뒋 紐⑸줉�쓣 議고쉶�븳�떎.
@Override
@Transactional(readOnly = true)
@@ -674,12 +691,25 @@
List<String> issueKeys = Lists.newArrayList(issueIds);
issueCondition.setIssueIds(issueKeys);
- List<Map<String, Object>> results = this.issueMapper.find(issueCondition);
+ issueCondition.setLoginUserId(this.webAppUtil.getLoginId());
+ issueCondition.setWorkspaceId(this.userService.getUser(this.webAppUtil.getLoginId()).getLastWorkspaceId());
+ User user = this.webAppUtil.getLoginUserObject();
+
+ List<Map<String, Object>> results = Lists.newArrayList();
+ Long totalCount = 0L;
+
+ if (this.userWorkspaceService.checkWorkspaceManager(user)) {
+ results = this.issueMapper.find(issueCondition);
+ totalCount = this.issueMapper.count(issueCondition);
+ } else{
+ this.SetMyDepartmentId(issueCondition);
+ results = this.issueMapper.findByDepartment(issueCondition);
+ totalCount = this.issueMapper.countByDepartment(issueCondition);
+ }
// �뒠�떇 �쟾 - 0.8, 0.9, 0.9, 0.9, 0.9
/*StopWatch serviceStart = new StopWatch();
serviceStart.start();*/
- Long totalCount = this.issueMapper.count(issueCondition);
// �뒠�떇 �쟾 - 1.1, 1.1, 1.3, 1.2
/*serviceStart.stop();
@@ -689,7 +719,6 @@
// �씠�뒋 �븘�씠�뵒 珥덇린�솕
issueCondition.setIssueIds(Lists.newArrayList());
// Map �뿉 �엳�뒗 �뜲�씠�꽣瑜� IssueVo �뜲�씠�꽣濡� 蹂��솚�븳�떎.
- User user = this.webAppUtil.getLoginUserObject();
this.setMapToIssueVo(results, issueVos, issueCondition, user);
this.setCountDownIssues(results, issueVos);
diff --git a/src/main/java/kr/wisestone/owl/service/impl/ProjectServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/ProjectServiceImpl.java
index 303dc8a..eb33e80 100644
--- a/src/main/java/kr/wisestone/owl/service/impl/ProjectServiceImpl.java
+++ b/src/main/java/kr/wisestone/owl/service/impl/ProjectServiceImpl.java
@@ -11,12 +11,14 @@
import kr.wisestone.owl.mapper.ProjectMapper;
import kr.wisestone.owl.repository.ProjectClosureRepository;
import kr.wisestone.owl.repository.ProjectRepository;
+import kr.wisestone.owl.repository.UserDepartmentRepository;
import kr.wisestone.owl.service.*;
import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.ConvertUtil;
import kr.wisestone.owl.util.DateUtil;
import kr.wisestone.owl.vo.*;
import kr.wisestone.owl.web.condition.ProjectCondition;
+import kr.wisestone.owl.web.condition.WidgetCondition;
import kr.wisestone.owl.web.form.ProjectForm;
import kr.wisestone.owl.web.view.ExcelView;
import org.apache.commons.lang3.StringUtils;
@@ -48,7 +50,7 @@
private DepartmentService departmentService;
@Autowired
- private UserDepartmentService userDepartmentService;
+ private UserDepartmentRepository userDepartmentRepository;
@Autowired
private ProjectRoleService projectRoleService;
@@ -328,6 +330,19 @@
}
}
+ void SetMyDepartmentId(ProjectCondition projectCondition){
+ Long loginId = projectCondition.getLoginUserId();
+ List<Long> myDepartmentIds = Lists.newArrayList();
+ List<UserDepartment> myDepartments = this.userDepartmentRepository.findByUserId(loginId);
+
+ if(myDepartments != null && myDepartments.size() > 0){
+ for(UserDepartment myDepartment : myDepartments){
+ myDepartmentIds.add(myDepartment.getDepartmentId());
+ }
+ }
+ projectCondition.setMyDepartmentIds(myDepartmentIds);
+ }
+
// �봽濡쒖젥�듃 紐⑸줉�쓣 議고쉶�븳�떎.
@Override
@Transactional(readOnly = true)
@@ -352,6 +367,7 @@
totalCount = this.projectMapper.countByWorkspaceManager(condition);
}
else {
+ this.SetMyDepartmentId(condition);
results = this.projectMapper.find(condition);
totalCount = this.projectMapper.count(condition);
}
diff --git a/src/main/java/kr/wisestone/owl/service/impl/WidgetServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/WidgetServiceImpl.java
index 418273c..b83d5e1 100644
--- a/src/main/java/kr/wisestone/owl/service/impl/WidgetServiceImpl.java
+++ b/src/main/java/kr/wisestone/owl/service/impl/WidgetServiceImpl.java
@@ -243,15 +243,28 @@
Long delayIssue = 0L; // 吏��뿰�맂 �씠�뒋
Long completeIssue = 0L; // �셿猷뚮맂 �씠�뒋
+ widgetCondition.setLoginUserId(this.webAppUtil.getLoginId());
+ widgetCondition.setWorkspaceId(this.userService.getUser(this.webAppUtil.getLoginId()).getLastWorkspaceId());
+ User user = this.webAppUtil.getLoginUserObject();
+
if (widgetCondition.getProjectIds().size() > 0) {
SetMeAndDownProjectIds(widgetCondition.getProjectIds(), widgetCondition);
-
- remainIssue = this.widgetMapper.countRemainIssue(widgetCondition); // �옍�뿬 �씠�뒋
- delayIssue = this.widgetMapper.countTodayDelayIssue(widgetCondition); // 吏��뿰�맂 �씠�뒋
- assigneeIssue = this.widgetMapper.countAssigneeIssue(widgetCondition); // �븷�떦�맂 �씠�뒋
- registerIssue = this.widgetMapper.countTodayRegisterIssue(widgetCondition); // �벑濡앺븳 �씠�뒋
- noAssigneeIssue = this.widgetMapper.countNoAssigneeIssue(widgetCondition); // 誘명븷�떦 �씠�뒋
- completeIssue = this.widgetMapper.countCompleteIssue(widgetCondition); // �셿猷뚮맂 �씠�뒋
+ if (this.userWorkspaceService.checkWorkspaceManager(user)) {
+ remainIssue = this.widgetMapper.countRemainIssue(widgetCondition); // �옍�뿬 �씠�뒋
+ delayIssue = this.widgetMapper.countTodayDelayIssue(widgetCondition); // 吏��뿰�맂 �씠�뒋
+ assigneeIssue = this.widgetMapper.countAssigneeIssue(widgetCondition); // �븷�떦�맂 �씠�뒋
+ registerIssue = this.widgetMapper.countTodayRegisterIssue(widgetCondition); // �벑濡앺븳 �씠�뒋
+ noAssigneeIssue = this.widgetMapper.countNoAssigneeIssue(widgetCondition); // 誘명븷�떦 �씠�뒋
+ completeIssue = this.widgetMapper.countCompleteIssue(widgetCondition); // �셿猷뚮맂 �씠�뒋
+ } else {
+ SetMyDepartmentId(widgetCondition);
+ remainIssue = this.widgetMapper.countRemainIssueByDepartment(widgetCondition); // �옍�뿬 �씠�뒋
+ delayIssue = this.widgetMapper.countTodayDelayIssueByDepartment(widgetCondition); // 吏��뿰�맂 �씠�뒋
+ assigneeIssue = this.widgetMapper.countAssigneeIssueByDepartment(widgetCondition); // �븷�떦�맂 �씠�뒋
+ registerIssue = this.widgetMapper.countTodayRegisterIssueByDepartment(widgetCondition); // �벑濡앺븳 �씠�뒋
+ noAssigneeIssue = this.widgetMapper.countNoAssigneeIssueByDepartment(widgetCondition); // 誘명븷�떦 �씠�뒋
+ completeIssue = this.widgetMapper.countCompleteIssueByDepartment(widgetCondition); // �셿猷뚮맂 �씠�뒋
+ }
}
Map<String, Object> results = new HashMap<>();
@@ -384,7 +397,6 @@
totalCount = this.widgetMapper.countMyAssigneeIssue(widgetCondition);
}
-
// 0.156 - 0.166
int totalPage = (int) Math.ceil((totalCount - 1) / pageable.getPageSize()) + 1;
@@ -481,9 +493,18 @@
// �쐞�젽 寃��깋 議곌굔�쓣 留뚮뱾怨� �쟾泥� �봽濡쒖젥�듃 �젙蹂대�� 由ы꽩�븳�떎.
Map<String, Object> results = this.makeWidgetConditionAllProject(widgetCondition, getWidgetCondition);
+ widgetCondition.setLoginUserId(this.webAppUtil.getLoginId());
+ widgetCondition.setWorkspaceId(this.userService.getUser(this.webAppUtil.getLoginId()).getLastWorkspaceId());
+ User user = this.webAppUtil.getLoginUserObject();
+
if (widgetCondition.getProjectId() != null) {
SetMeAndDownProjectIds(widgetCondition.getProjectIds(), widgetCondition);
- List<Map<String, Object>> projectMemberIssues = this.widgetMapper.findProjectMemberIssue(widgetCondition);
+ List<Map<String, Object>> projectMemberIssues = Lists.newArrayList();
+ if (this.userWorkspaceService.checkWorkspaceManager(user)) {
+ projectMemberIssues = this.widgetMapper.findProjectMemberIssue(widgetCondition);
+ } else {
+ projectMemberIssues = this.widgetMapper.findProjectMemberIssueByDepartment(widgetCondition);
+ }
for (Map<String, Object> projectMemberIssue : projectMemberIssues) {
//String departmentName = MapUtil.getString(projectMemberIssue, "departmentName");
@@ -864,12 +885,22 @@
List<Map<String, Object>> severityIssues = Lists.newArrayList();
Long totalCount = 0L;
+ widgetCondition.setLoginUserId(this.webAppUtil.getLoginId());
+ widgetCondition.setWorkspaceId(this.userService.getUser(this.webAppUtil.getLoginId()).getLastWorkspaceId());
+ User user = this.webAppUtil.getLoginUserObject();
+
if (widgetCondition.getProjectIds().size() > 0) {
SetMeAndDownProjectIds(widgetCondition.getProjectIds(), widgetCondition);
-
- severityCounts = this.widgetMapper.countSeverityIssue(widgetCondition);
- severityIssues = this.widgetMapper.findSeverityIssues(widgetCondition);
- totalCount = this.widgetMapper.countSearchIssue(widgetCondition);
+ if (this.userWorkspaceService.checkWorkspaceManager(user)) {
+ severityCounts = this.widgetMapper.countSeverityIssue(widgetCondition);
+ severityIssues = this.widgetMapper.findSeverityIssues(widgetCondition);
+ totalCount = this.widgetMapper.countSearchIssue(widgetCondition);
+ }else {
+ SetMyDepartmentId(widgetCondition);
+ severityCounts = this.widgetMapper.countSeverityIssueByDepartment(widgetCondition);
+ severityIssues = this.widgetMapper.findSeverityIssuesByDepartment(widgetCondition);
+ totalCount = this.widgetMapper.countSearchIssueByDepartment(widgetCondition);
+ }
}
Long criticalIssueCount = 0L, majorIssueCount = 0L, minorIssueCount = 0L, trivialIssueCount = 0L;
@@ -1027,10 +1058,19 @@
// �굹�뿉寃� �븷�떦�맂 �씠�뒋 �젙蹂대�� �뿊��濡� �떎�슫濡쒕뱶 �븳�떎.
private ExportExcelVo downloadExcelMyAssigneeIssue() {
WidgetCondition widgetCondition = this.makeWidgetCondition();
+
+ /*widgetCondition.setLoginUserId(this.webAppUtil.getLoginId());
+ widgetCondition.setWorkspaceId(this.userService.getUser(this.webAppUtil.getLoginId()).getLastWorkspaceId());
+ User user = this.webAppUtil.getLoginUserObject();*/
+
// �굹�뿉寃� �븷�떦�맂 �씠�뒋
List<Map<String, Object>> assigneeIssues = Lists.newArrayList();
if (widgetCondition.getProjectIds().size() > 0) {
+ //if (this.userWorkspaceService.checkWorkspaceManager(user)) {
assigneeIssues = this.widgetMapper.findMyAssigneeIssue(this.makeWidgetCondition());
+ /*} else {
+ assigneeIssues = this.widgetMapper.findMyAssigneeIssueByDepartment(this.makeWidgetCondition());
+ }*/
}
ExportExcelVo excelInfo = new ExportExcelVo();
diff --git a/src/main/java/kr/wisestone/owl/web/condition/IssueCondition.java b/src/main/java/kr/wisestone/owl/web/condition/IssueCondition.java
index 6b0c747..911ce7d 100644
--- a/src/main/java/kr/wisestone/owl/web/condition/IssueCondition.java
+++ b/src/main/java/kr/wisestone/owl/web/condition/IssueCondition.java
@@ -51,7 +51,7 @@
private List<Map<String, Object>> issueHostingField = Lists.newArrayList();
private List<Long> statusIds = Lists.newArrayList();
private List<Long> excludeIds = Lists.newArrayList();
-
+ private List<Long> myDepartmentIds; // �궡媛� �냽�빐�엳�뒗 遺��꽌 ID
public IssueCondition(){}
// ���떆蹂대뱶 �쐞湲곌�由� �쐞�젽�뿉�꽌 �궗�슜
@@ -475,4 +475,12 @@
public void setUseValue(String useValue) {
this.useValue = useValue;
}
+
+ public List<Long> getMyDepartmentIds() {
+ return myDepartmentIds;
+ }
+
+ public void setMyDepartmentIds(List<Long> myDepartmentIds) {
+ this.myDepartmentIds = myDepartmentIds;
+ }
}
diff --git a/src/main/java/kr/wisestone/owl/web/condition/ProjectCondition.java b/src/main/java/kr/wisestone/owl/web/condition/ProjectCondition.java
index 9486dc6..c59b8b4 100644
--- a/src/main/java/kr/wisestone/owl/web/condition/ProjectCondition.java
+++ b/src/main/java/kr/wisestone/owl/web/condition/ProjectCondition.java
@@ -26,6 +26,7 @@
private List<Long> excludeIds = Lists.newArrayList();
private List<Long> userIds = Lists.newArrayList();
private List<Long> departmentIds = Lists.newArrayList();
+ private List<Long> myDepartmentIds; // �궡媛� �냽�빐�엳�뒗 遺��꽌 ID
public ProjectCondition(){}
@@ -178,4 +179,12 @@
public void setDepartmentIds(List<Long> departmentIds) {
this.departmentIds = departmentIds;
}
+
+ public List<Long> getMyDepartmentIds() {
+ return myDepartmentIds;
+ }
+
+ public void setMyDepartmentIds(List<Long> myDepartmentIds) {
+ this.myDepartmentIds = myDepartmentIds;
+ }
}
diff --git a/src/main/resources/mybatis/query-template/department-template.xml b/src/main/resources/mybatis/query-template/department-template.xml
index eb3bea2..9b97cbb 100644
--- a/src/main/resources/mybatis/query-template/department-template.xml
+++ b/src/main/resources/mybatis/query-template/department-template.xml
@@ -67,8 +67,7 @@
</choose>
</select>
- <select id="findProjectDepartment
-" resultType="java.util.HashMap"
+ <select id="findProjectDepartment" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.UserCondition">
select DISTINCT (d.id) as departmentId, d.department_name as departmentName from department d
inner join project_role_department prd on prd.department_id = d.id
diff --git a/src/main/resources/mybatis/query-template/issue-template.xml b/src/main/resources/mybatis/query-template/issue-template.xml
index f5d76f5..bbf5ef7 100644
--- a/src/main/resources/mybatis/query-template/issue-template.xml
+++ b/src/main/resources/mybatis/query-template/issue-template.xml
@@ -42,7 +42,7 @@
INNER JOIN priority priority FORCE INDEX(PRIMARY) ON issue.priority_id = priority.id
INNER JOIN severity severity FORCE INDEX(PRIMARY) ON issue.severity_id = severity.id
LEFT OUTER JOIN issue_custom_field_value issue_custom FORCE INDEX(issueIdIndex) ON issue.id = issue_custom.issue_id
- LEFT OUTER JOIN issue_department issued FORCE INDEX(issueIdIndex) ON issue.id = issued.issue_id
+ LEFT OUTER JOIN issue_department isd FORCE INDEX(issueIdIndex) ON issue.id = isd.issue_id
LEFT OUTER JOIN issue_user issue_user FORCE INDEX(issueIdIndex) ON issue.id = issue_user.issue_id
LEFT OUTER JOIN (SELECT issue_id, COUNT(id) as attachedFileCount FROM attached_file GROUP BY issue_id)
temp_attached_file on (temp_attached_file.issue_id = issue.id)
@@ -159,7 +159,7 @@
<choose>
<when test="departmentIds.size != 0">
- AND issue_department.department_id IN
+ AND isd.department_id IN
<foreach collection="departmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
@@ -201,7 +201,198 @@
</if>
</select>
- <select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.IssueCondition">
+ <select id="findByDepartment" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.IssueCondition">
+ SELECT
+ DISTINCT issue.id as id,
+ issue.register_id as registerId,
+ issue.reverse_index as reverseIndex,
+ issue.title as title,
+ issue.description as description,
+ issue.parent_issue_id as parentIssueId,
+ issue.start_date as startDate,
+ issue.complete_date as completeDate,
+ issue.issue_number as issueNumber,
+ issue.register_date as registerDate,
+ SUBSTRING(issue.modify_date, 1, 19) as modifyDate,
+ project.id as projectId,
+ project.name as projectName,
+ project.project_key as projectKey,
+ issue_type.id as issueTypeId,
+ issue_type.name as issueTypeName,
+ issue_status.id as issueStatusId,
+ issue_status.issue_status_type as issueStatusType,
+ issue_status.name as issueStatusName,
+ issue_status.color as issueStatusColor,
+ priority.id as priorityId,
+ priority.name as priorityName,
+ priority.color as priorityColor,
+ severity.id as severityId,
+ severity.name as severityName,
+ severity.color as severityColor,
+ GROUP_CONCAT(issue_custom.use_value) AS useValue,
+ IFNULL(temp_attached_file.attachedFileCount, 0) as attachedFileCount,
+ IFNULL(temp_issue_comment.issueCommentCount, 0) as issueCommentCount
+ FROM issue issue FORCE INDEX(reverseIndex)
+ INNER JOIN project project FORCE INDEX(workspaceIdIndex) ON issue.project_id = project.id
+ INNER JOIN workspace workspace ON workspace.id = project.workspace_id
+ INNER JOIN issue_status issue_status FORCE INDEX(PRIMARY) ON issue.issue_status_id = issue_status.id
+ INNER JOIN issue_type issue_type FORCE INDEX(PRIMARY) ON issue.issue_type_id = issue_type.id
+ INNER JOIN priority priority FORCE INDEX(PRIMARY) ON issue.priority_id = priority.id
+ INNER JOIN severity severity FORCE INDEX(PRIMARY) ON issue.severity_id = severity.id
+ INNER JOIN issue_department isd ON isd.issue_id = issue.id
+ INNER JOIN department d ON d.id = isd.department_id
+ LEFT OUTER JOIN issue_custom_field_value issue_custom FORCE INDEX(issueIdIndex) ON issue.id = issue_custom.issue_id
+ LEFT OUTER JOIN (SELECT issue_id, COUNT(id) as attachedFileCount FROM attached_file GROUP BY issue_id)
+ temp_attached_file on (temp_attached_file.issue_id = issue.id)
+ LEFT OUTER JOIN (SELECT issue_id, COUNT(id) as issueCommentCount FROM issue_comment GROUP BY issue_id)
+ temp_issue_comment on (temp_issue_comment.issue_id = issue.id)
+ WHERE 1=1
+ <if test="keyWord != null and !keyWord.equals('') ">
+ AND issue.title like CONCAT('%',#{keyWord},'%')
+ OR issue.description like CONCAT('%',#{keyWord},'%')
+ OR issue.start_date like CONCAT('%',#{keyWord},'%')
+ OR issue.complete_date like CONCAT('%',#{keyWord},'%')
+ OR issue.issue_number like CONCAT('%',#{keyWord},'%')
+ OR issue.register_date like CONCAT('%',#{keyWord},'%')
+ OR project.name like CONCAT('%',#{keyWord},'%')
+ OR project.project_key like CONCAT('%',#{keyWord},'%')
+ OR issue_type.name like CONCAT('%',#{keyWord},'%')
+ OR issue_status.issue_status_type like CONCAT('%',#{keyWord},'%')
+ OR issue_status.name like CONCAT('%',#{keyWord},'%')
+ OR priority.name like CONCAT('%',#{keyWord},'%')
+ OR severity.name like CONCAT('%',#{keyWord},'%')
+ OR issue_custom.use_value like CONCAT('%',#{keyWord},'%')
+ </if>
+
+ <if test="title != null and !title.equals('')">
+ AND issue.title like CONCAT('%',#{title},'%')
+ </if>
+
+ <if test="description != null and !description.equals('')">
+ AND issue.description like CONCAT('%',#{description},'%')
+ </if>
+
+ <if test="issueNumber != null and !issueNumber.equals('')">
+ AND issue.issue_number = #{issueNumber}
+ </if>
+
+ <if test="beginRegisterDate != null and !beginRegisterDate.equals('')">
+ ANd issue.register_date >= #{beginRegisterDate}
+ </if>
+
+ <if test="endRegisterDate != null and !endRegisterDate.equals('')">
+ ANd issue.register_date <![CDATA[ <= ]]> #{endRegisterDate}
+ </if>
+
+ <if test="beginStartDate != null and !beginStartDate.equals('')">
+ ANd issue.start_date >= #{beginStartDate}
+ </if>
+
+ <if test="endStartDate != null and !endStartDate.equals('')">
+ ANd issue.start_date <![CDATA[ <= ]]> #{endStartDate}
+ </if>
+
+ <if test="beginCompleteDate != null and !beginCompleteDate.equals('')">
+ ANd issue.complete_date >= #{beginCompleteDate}
+ </if>
+
+ <if test="endCompleteDate != null and !endCompleteDate.equals('')">
+ ANd issue.complete_date <![CDATA[ <= ]]> #{endCompleteDate}
+ </if>
+
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ <otherwise>
+ AND 1 != 1
+ </otherwise>
+ </choose>
+
+ <choose>
+ <when test="projectIds.size != 0">
+ AND project.id IN
+ <foreach collection="projectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueStatusIds.size != 0">
+ AND issue_status.id IN
+ <foreach collection="issueStatusIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueTypeIds.size != 0">
+ AND issue_type.id IN
+ <foreach collection="issueTypeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="priorityIds.size != 0">
+ AND priority.id IN
+ <foreach collection="priorityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="severityIds.size != 0">
+ AND severity.id IN
+ <foreach collection="severityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="registerIds.size != 0">
+ AND issue.register_id IN
+ <foreach collection="registerIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueIds.size != 0">
+ AND issue.id IN
+ <foreach collection="issueIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND issue.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND issue.parent_issue_id IS NULL
+ AND issue.reverse_index <![CDATA[ < ]]> 0
+ AND workspace.id = #{workspaceId}
+ GROUP BY issue.id
+ ORDER BY issue.register_date DESC
+ <if test="page != null and !page.equals('')">
+ limit #{pageSize} offset #{page};
+ </if>
+ </select>
+
+ <!--<select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.IssueCondition">
SELECT
COUNT(DISTINCT issue.id)
FROM issue issue
@@ -301,6 +492,263 @@
<choose>
<when test="departmentIds.size != 0">
AND issue_department.department_id IN
+ <foreach collection="departmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="registerIds.size != 0">
+ AND issue.register_id IN
+ <foreach collection="registerIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueIds.size != 0">
+ AND issue.id IN
+ <foreach collection="issueIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND issue.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>-->
+
+ <select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.IssueCondition">
+ SELECT
+ COUNT(DISTINCT issue.id)
+ FROM issue issue
+ LEFT OUTER JOIN issue_department isd ON issue.id = isd.issue_id
+ WHERE 1=1
+ AND issue.parent_issue_id IS NULL
+ <if test="title != null and !title.equals('') ">
+ AND issue.title like CONCAT('%',#{title},'%')
+ </if>
+
+ <if test="description != null and !description.equals('')">
+ AND issue.description like CONCAT('%',#{description},'%')
+ </if>
+
+ <if test="issueNumber != null and !issueNumber.equals('')">
+ AND issue.issue_number = #{issueNumber}
+ </if>
+
+ <if test="beginRegisterDate != null and !beginRegisterDate.equals('')">
+ ANd issue.register_date >= #{beginRegisterDate}
+ </if>
+
+ <if test="endRegisterDate != null and !endRegisterDate.equals('')">
+ ANd issue.register_date <![CDATA[ <= ]]> #{endRegisterDate}
+ </if>
+
+ <if test="beginStartDate != null and !beginStartDate.equals('')">
+ ANd issue.start_date >= #{beginStartDate}
+ </if>
+
+ <if test="endStartDate != null and !endStartDate.equals('')">
+ ANd issue.start_date <![CDATA[ <= ]]> #{endStartDate}
+ </if>
+
+ <if test="beginCompleteDate != null and !beginCompleteDate.equals('')">
+ ANd issue.complete_date >= #{beginCompleteDate}
+ </if>
+
+ <if test="endCompleteDate != null and !endCompleteDate.equals('')">
+ ANd issue.complete_date <![CDATA[ <= ]]> #{endCompleteDate}
+ </if>
+
+ <choose>
+ <when test="projectIds.size != 0">
+ AND issue.project_id IN
+ <foreach collection="projectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueStatusIds.size != 0">
+ AND issue.issue_status_id IN
+ <foreach collection="issueStatusIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueTypeIds.size != 0">
+ AND issue.issue_type_id IN
+ <foreach collection="issueTypeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="priorityIds.size != 0">
+ AND issue.priority_id IN
+ <foreach collection="priorityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="severityIds.size != 0">
+ AND issue.severity_id IN
+ <foreach collection="severityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="departmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="departmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="registerIds.size != 0">
+ AND issue.register_id IN
+ <foreach collection="registerIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueIds.size != 0">
+ AND issue.id IN
+ <foreach collection="issueIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND issue.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
+ <select id="countByDepartment" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.IssueCondition">
+ SELECT
+ COUNT(DISTINCT issue.id)
+ FROM issue issue
+ LEFT OUTER JOIN issue_department isd ON issue.id = isd.issue_id
+ WHERE 1=1
+ AND issue.parent_issue_id IS NULL
+ <if test="title != null and !title.equals('') ">
+ AND issue.title like CONCAT('%',#{title},'%')
+ </if>
+
+ <if test="description != null and !description.equals('')">
+ AND issue.description like CONCAT('%',#{description},'%')
+ </if>
+
+ <if test="issueNumber != null and !issueNumber.equals('')">
+ AND issue.issue_number = #{issueNumber}
+ </if>
+
+ <if test="beginRegisterDate != null and !beginRegisterDate.equals('')">
+ ANd issue.register_date >= #{beginRegisterDate}
+ </if>
+
+ <if test="endRegisterDate != null and !endRegisterDate.equals('')">
+ ANd issue.register_date <![CDATA[ <= ]]> #{endRegisterDate}
+ </if>
+
+ <if test="beginStartDate != null and !beginStartDate.equals('')">
+ ANd issue.start_date >= #{beginStartDate}
+ </if>
+
+ <if test="endStartDate != null and !endStartDate.equals('')">
+ ANd issue.start_date <![CDATA[ <= ]]> #{endStartDate}
+ </if>
+
+ <if test="beginCompleteDate != null and !beginCompleteDate.equals('')">
+ ANd issue.complete_date >= #{beginCompleteDate}
+ </if>
+
+ <if test="endCompleteDate != null and !endCompleteDate.equals('')">
+ ANd issue.complete_date <![CDATA[ <= ]]> #{endCompleteDate}
+ </if>
+
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="projectIds.size != 0">
+ AND issue.project_id IN
+ <foreach collection="projectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueStatusIds.size != 0">
+ AND issue.issue_status_id IN
+ <foreach collection="issueStatusIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="issueTypeIds.size != 0">
+ AND issue.issue_type_id IN
+ <foreach collection="issueTypeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="priorityIds.size != 0">
+ AND issue.priority_id IN
+ <foreach collection="priorityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="severityIds.size != 0">
+ AND issue.severity_id IN
+ <foreach collection="severityIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="departmentIds.size != 0">
+ AND isd.department_id IN
<foreach collection="departmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
@@ -470,8 +918,8 @@
d.id AS id,
d.department_name AS departmentName
FROM issue i
- INNER JOIN issue_department issued ON issued.issue_id = i.id
- INNER JOIN department d ON d.id = issued.department_id
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
WHERE 1=1
<choose>
<when test="issueIds.size != 0">
diff --git a/src/main/resources/mybatis/query-template/project-template.xml b/src/main/resources/mybatis/query-template/project-template.xml
index c896e98..100ae59 100644
--- a/src/main/resources/mybatis/query-template/project-template.xml
+++ b/src/main/resources/mybatis/query-template/project-template.xml
@@ -3,7 +3,7 @@
<mapper namespace="kr.wisestone.owl.mapper.ProjectMapper">
- <select id="find" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ <!--<select id="find" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
SELECT
DISTINCT p.id as id,
p.name as name,
@@ -57,9 +57,77 @@
<if test="page != null and !page.equals('')">
limit #{pageSize} offset #{page};
</if>
+ </select>-->
+
+ <select id="find" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ SELECT
+ DISTINCT p.id as id,
+ p.name as name,
+ p.description as description,
+ p.status as status,
+ p.start_date as startDate,
+ p.end_date as endDate,
+ p.project_key as projectKey,
+ pc.parent_project_id as parentProjectId,
+ CASE p.default_yn WHEN 'Y' THEN 'true' ELSE 'false' END as defaultYn
+ FROM
+ project p
+ INNER JOIN project_role pr on p.id = pr.project_id
+ INNER JOIN project_role_department prd on prd.project_role_id = pr.id
+ INNER JOIN department d on d.id = prd.department_id
+ INNER JOIN workspace ws on ws.id = p.workspace_id
+ LEFT JOIN project_closure pc ON p.id = pc.project_id
+ WHERE if (pc.parent_project_id > -1, pc.parent_project_id, -1) = -1
+ AND prd.department_id = d.id
+ <if test="name != '' and name != null">
+ AND p.name like CONCAT('%',#{name},'%')
+ </if>
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND prd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ <otherwise>
+ AND 1 != 1
+ </otherwise>
+ </choose>
+
+ <choose>
+ <when test="roleTypes.size != 0">
+ AND pr.role_type IN
+ <foreach collection="roleTypes" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="statuses.size != 0">
+ AND p.status IN
+ <foreach collection="statuses" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND p.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND ws.id = #{workspaceId}
+ ORDER BY p.id desc
+ <if test="page != null and !page.equals('')">
+ limit #{pageSize} offset #{page};
+ </if>
</select>
- <select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ <!--<select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
SELECT
COUNT(DISTINCT p.id)
FROM
@@ -69,6 +137,51 @@
INNER JOIN workspace ws on ws.id = p.workspace_id
LEFT JOIN project_closure pc on p.id = pc.project_id
WHERE pru.user_id = #{loginUserId}
+ AND if (pc.parent_project_id > -1, pc.parent_project_id, -1) = -1
+ <if test="name != '' and name != null">
+ AND p.name like CONCAT('%',#{name},'%')
+ </if>
+
+ <choose>
+ <when test="roleTypes.size != 0">
+ AND pr.role_type IN
+ <foreach collection="roleTypes" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="statuses.size != 0">
+ AND p.status IN
+ <foreach collection="statuses" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND p.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND ws.id = #{workspaceId}
+ </select>-->
+
+ <select id="count" resultType="java.lang.Long" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ SELECT
+ COUNT(DISTINCT p.id)
+ FROM
+ project p
+ INNER JOIN project_role pr on p.id = pr.project_id
+ INNER JOIN project_role_department prd on prd.project_role_id = pr.id
+ INNER JOIN department d on d.id = prd.department_id
+ INNER JOIN workspace ws on ws.id = p.workspace_id
+ LEFT JOIN project_closure pc on p.id = pc.project_id
+ WHERE prd.department_id = d.id
AND if (pc.parent_project_id > -1, pc.parent_project_id, -1) = -1
<if test="name != '' and name != null">
AND p.name like CONCAT('%',#{name},'%')
@@ -218,7 +331,7 @@
<!-- �빐�떦 �뾽臾� 怨듦컙�뿉�꽌 李몄뿬�븯怨� �엳�뒗 吏꾪뻾以묒씤 �봽濡쒖젥�듃瑜� 議고쉶�븳�떎 -->
- <select id="findByWorkspaceIdAndIncludeProjectAll" resultType="java.util.HashMap"
+ <!--<select id="findByWorkspaceIdAndIncludeProjectAll" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
SELECT
DISTINCT p.id as id,
@@ -254,10 +367,49 @@
</foreach>
</when>
</choose>
+ </select>-->
+
+ <select id="findByWorkspaceIdAndIncludeProjectAll" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ SELECT
+ DISTINCT p.id as id,
+ p.name as name,
+ p.description as description,
+ p.status as status,
+ p.start_date as startDate,
+ p.end_date as endDate,
+ p.project_key as projectKey,
+ CASE p.default_yn WHEN 'Y' THEN 'true' ELSE 'false' END as defaultYn
+ FROM
+ project p
+ INNER JOIN project_role pr on p.id = pr.project_id
+ INNER JOIN project_role_department prd on prd.project_role_id = pr.id
+ INNER JOIN department d on d.id = prd.department_id
+ WHERE prd.department_id = d.id
+ AND p.workspace_id = #{workspaceId}
+ <if test="name != '' and name != null">
+ AND p.name like CONCAT('%',#{name},'%')
+ </if>
+ <choose>
+ <when test="statuses.size != 0">
+ AND p.status IN
+ <foreach collection="statuses" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND p.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
</select>
<!-- �빐�떦 �뾽臾� 怨듦컙�뿉�꽌 李몄뿬�븯怨� �엳�뒗 吏꾪뻾以묒씤 �봽濡쒖젥�듃瑜� 議고쉶�븳�떎 -->
- <select id="findByWorkspaceIdAndIncludeProject" resultType="java.util.HashMap"
+ <!--<select id="findByWorkspaceIdAndIncludeProject" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
SELECT
DISTINCT p.id as id,
@@ -296,9 +448,51 @@
</foreach>
</when>
</choose>
+ </select>-->
+
+ <select id="findByWorkspaceIdAndIncludeProject" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ SELECT
+ DISTINCT p.id as id,
+ p.name as name,
+ p.description as description,
+ p.status as status,
+ p.start_date as startDate,
+ p.end_date as endDate,
+ p.project_key as projectKey,
+ pc.parent_project_id as parentProjectId,
+ CASE p.default_yn WHEN 'Y' THEN 'true' ELSE 'false' END as defaultYn
+ FROM
+ project p
+ INNER JOIN project_role pr on p.id = pr.project_id
+ INNER JOIN project_role_department prd on prd.project_role_id = pr.id
+ INNER JOIN department d on d.id = prd.department_id
+ LEFT JOIN project_closure pc ON p.id = pc.project_id
+ WHERE prd.department_id = d.id
+ AND if (pc.parent_project_id > -1, pc.parent_project_id, -1) = -1
+ AND p.workspace_id = #{workspaceId}
+ <if test="name != '' and name != null">
+ AND p.name like CONCAT('%',#{name},'%')
+ </if>
+ <choose>
+ <when test="statuses.size != 0">
+ AND p.status IN
+ <foreach collection="statuses" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="excludeIds.size != 0">
+ AND p.id NOT IN
+ <foreach collection="excludeIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
</select>
- <select id="checkIncludeProject" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ <!--<select id="checkIncludeProject" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
SELECT
DISTINCT p.id as id
FROM
@@ -308,6 +502,18 @@
INNER JOIN user u on u.id = pru.user_id
WHERE 1=1
AND p.id = #{id} AND u.id = #{loginUserId}
+ </select>-->
+
+ <select id="checkIncludeProject" resultType="java.util.HashMap" parameterType="kr.wisestone.owl.web.condition.ProjectCondition">
+ SELECT
+ DISTINCT p.id as id
+ FROM
+ project p
+ INNER JOIN project_role pr on p.id = pr.project_id
+ INNER JOIN project_role_department prd on prd.project_role_id = pr.id
+ INNER JOIN department d on d.id = prd.department_id
+ WHERE 1=1
+ AND p.id = #{id}
</select>
<select id="findChildrenProject" resultType="java.util.HashMap" parameterType="long">
@@ -327,14 +533,109 @@
</select>
<!-- �봽濡쒖젥�듃 �궘�젣 -->
+ <!--<delete id="deleteProject" parameterType="java.util.HashMap">
+ <!– �봽濡쒖젥�듃�뿉 �뿰寃곕맂 �궗�슜�옄 �젙�쓽 �븘�뱶 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_type_custom_field WHERE project_id = #{projectId};
+
+ <choose>
+ <when test="projectRoleIds.size != 0">
+ <!– �봽濡쒖젥�듃 李몄뿬 �궗�슜�옄 �궘�젣 –>
+ DELETE FROM project_role_user WHERE project_role_id IN (
+ <foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �봽濡쒖젥�듃 沅뚰븳 �뿰寃� �젙蹂� �궘�젣 –>
+ DELETE FROM project_role_permission WHERE project_role_id IN(
+ <foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <!– �봽濡쒖젥�듃 �뿭�븷 �궘�젣 –>
+ DELETE FROM project_role WHERE project_id = #{projectId};
+
+ <!– �씠�뒋 怨좎쑀 踰덊샇 �깮�꽦 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_number_generator WHERE project_id = #{projectId};
+
+ <!– �씠�뒋 �씠�젰 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_history WHERE project_id = #{projectId};
+
+ <!– �씠�뒋 踰꾩쟾 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_version WHERE project_id = #{projectId};
+
+ <!– �뾽臾� 怨듦컙�뿉 珥덈��븳 �봽濡쒖젥�듃 �젙蹂� �궘�젣 –>
+ DELETE FROM user_invite_project WHERE project_id = #{projectId};
+
+ <choose>
+ <when test="issueIds.size != 0">
+ <!– �씠�뒋 �궗�슜�옄 �젙�쓽 �븘�뱶 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_custom_field_value WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 �뙎湲� �궘�젣 –>
+ DELETE FROM issue_comment WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 由ъ뒪�겕 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_risk WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 �떞�떦�옄 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_user WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– 愿��떖 �씠�뒋 �젙蹂� �궘�젣 –>
+ DELETE FROM user_like_issue WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 諛쒖깮 �삁�빟 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_reservation WHERE issue_id IN (
+ <foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ </when>
+ </choose>
+
+ <!– �씠�뒋 怨좎쑀 踰덊샇 �깮�꽦 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_number_generator WHERE project_id = #{projectId};
+
+ <!– �씠�뒋 �궘�젣 –>
+ DELETE FROM issue WHERE project_id = #{projectId};
+
+ <!– �봽濡쒖젥�듃 �궘�젣 –>
+ DELETE FROM project WHERE id = #{projectId};
+
+ </delete>-->
+
<delete id="deleteProject" parameterType="java.util.HashMap">
<!-- �봽濡쒖젥�듃�뿉 �뿰寃곕맂 �궗�슜�옄 �젙�쓽 �븘�뱶 �젙蹂� �궘�젣 -->
DELETE FROM issue_type_custom_field WHERE project_id = #{projectId};
<choose>
<when test="projectRoleIds.size != 0">
- <!-- �봽濡쒖젥�듃 李몄뿬 �궗�슜�옄 �궘�젣 -->
- DELETE FROM project_role_user WHERE project_role_id IN (
+ <!-- �봽濡쒖젥�듃 李몄뿬 遺��꽌 �궘�젣 -->
+ DELETE FROM project_role_department WHERE project_role_id IN (
<foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
#{item}
</foreach>
@@ -387,8 +688,8 @@
</foreach>
);
- <!-- �씠�뒋 �떞�떦�옄 �젙蹂� �궘�젣 -->
- DELETE FROM issue_user WHERE issue_id IN (
+ <!-- �씠�뒋 �떞�떦遺��꽌 �젙蹂� �궘�젣 -->
+ DELETE FROM issue_department WHERE issue_id IN (
<foreach collection="issueIds" item="item" index="index" separator="," open="" close="">
#{item}
</foreach>
diff --git a/src/main/resources/mybatis/query-template/widget-template.xml b/src/main/resources/mybatis/query-template/widget-template.xml
index b482c87..02b923b 100644
--- a/src/main/resources/mybatis/query-template/widget-template.xml
+++ b/src/main/resources/mybatis/query-template/widget-template.xml
@@ -24,6 +24,32 @@
</choose>
</select>
+ <select id="countRemainIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT
+ COUNT(i.id) FROM issue i
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
+ WHERE EXISTS(SELECT 1 FROM issue_status iss WHERE iss.issue_status_type != 'CLOSE' AND i.issue_status_id =
+ iss.id)
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds != null and myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
<!-- 吏��뿰 �씠�뒋 媛쒖닔 -->
<select id="countTodayDelayIssue" resultType="java.lang.Long"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
@@ -34,6 +60,33 @@
<when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
AND i.project_id IN
<foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND i.complete_date IS NOT NULL
+ AND i.complete_date <![CDATA[ < ]]> #{completeDate}
+ </select>
+
+ <select id="countTodayDelayIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT
+ COUNT(i.id) FROM issue i
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
+ where exists(select 1 from issue_status iss where iss.id = i.issue_status_id and iss.issue_status_type != 'CLOSE')
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds != null and myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
@@ -72,10 +125,39 @@
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
SELECT
COUNT(i.id) FROM issue i
- WHERE EXISTS(SELECT 1 FROM issue_department id WHERE id.issue_id = i.id
+ WHERE EXISTS(SELECT 1 FROM issue_department isd WHERE isd.issue_id = i.id
<choose>
<when test="myDepartmentIds.size != 0">
- AND id.department_id IN
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ <otherwise>
+ AND 1 != 1
+ </otherwise>
+ </choose>
+ ) AND EXISTS(SELECT 1 FROM issue_status iss WHERE iss.id = i.issue_status_id and iss.issue_status_type != 'CLOSE')
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
+ <select id="countAssigneeIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT
+ COUNT(i.id) FROM issue i
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
+ WHERE EXISTS(SELECT 1 FROM issue_department isd WHERE isd.issue_id = i.id
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
@@ -112,6 +194,33 @@
AND i.register_date BETWEEN (CURDATE()) AND (CURDATE() + INTERVAL 1 DAY)
</select>
+ <select id="countTodayRegisterIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ select
+ count(i.id) as todayCount from issue i
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
+ where
+ i.register_id = #{loginUserId}
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds != null and myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND i.register_date BETWEEN (CURDATE()) AND (CURDATE() + INTERVAL 1 DAY)
+ </select>
+
<!-- 誘명븷�떦 �씠�뒋 媛쒖닔 -->
<!--<select id="countNoAssigneeIssue" resultType="java.lang.Long"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
@@ -136,7 +245,7 @@
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
SELECT
COUNT(DISTINCT i.id) FROM issue i
- LEFT OUTER JOIN issue_department id ON id.issue_id = i.id
+ LEFT OUTER JOIN issue_department isd ON isd.issue_id = i.id
WHERE
EXISTS(select 1 from issue_status iss where iss.id = i.issue_status_id and iss.issue_status_type != 'CLOSE')
<choose>
@@ -147,7 +256,33 @@
</foreach>
</when>
</choose>
- AND id.id IS NULL
+ AND isd.id IS NULL
+ </select>
+
+ <select id="countNoAssigneeIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT
+ COUNT(DISTINCT i.id) FROM issue i
+ LEFT OUTER JOIN issue_department isd ON isd.issue_id = i.id
+ WHERE
+ EXISTS(select 1 from issue_status iss where iss.id = i.issue_status_id and iss.issue_status_type != 'CLOSE')
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds != null and myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ AND isd.id IS NULL
</select>
<!-- �셿猷뚮맂 �씠�뒋 媛쒖닔 -->
@@ -161,6 +296,32 @@
<when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
AND i.project_id IN
<foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
+ <select id="countCompleteIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ select
+ count(*) as issueCount from issue i
+ inner join issue_status iss on iss.id = i.issue_status_id
+ INNER JOIN issue_department isd ON isd.issue_id = i.id
+ INNER JOIN department d ON d.id = isd.department_id
+ where iss.issue_status_type = 'CLOSE'
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds != null and myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
@@ -328,9 +489,9 @@
count(case when iss.issue_status_type = 'CLOSE' THEN 1 END) as 'close',
count(case when iss.issue_status_type != 'CLOSE' THEN 1 END) as 'remain',
(select concat(u.name, "%", u.account, "%", u.profile) from user u
- inner join project_role_user pru on pru.user_id = u.id
- inner join project_role pr on pr.id = pru.project_role_id
- where pr.project_id = p.id and pr.role_type = '02'
+ inner join project_role_user pru on pru.user_id = u.id
+ inner join project_role pr on pr.id = pru.project_role_id
+ where pr.project_id = p.id and pr.role_type = '02'
) as managerInfo
,
(
@@ -374,19 +535,16 @@
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
select
count(i.id) as todayCount from issue i where
- exists(select 1 from issue_department id where id.issue_id = i.id
+ exists(select 1 from issue_department isd where isd.issue_id = i.id
<choose>
<when test="myDepartmentIds.size != 0">
- AND id.department_id IN
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
- <otherwise>
- AND 1 != 1
- </otherwise>
</choose>
- and id.register_date
+ and isd.register_date
BETWEEN (CURDATE()) AND (CURDATE() + INTERVAL 1 DAY))
<choose>
<when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
@@ -425,7 +583,7 @@
select
count(distinct i.id)
from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
WHERE 1=1
<choose>
@@ -438,14 +596,11 @@
</choose>
<choose>
<when test="myDepartmentIds.size != 0">
- AND id.department_id IN
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
- <otherwise>
- AND 1 != 1
- </otherwise>
</choose>
And iss.issue_status_type != 'CLOSE'
</select>
@@ -483,7 +638,38 @@
</if>
</select>-->
- <!-- �궡媛� �떞�떦�븯�뒗 �씠�뒋 - �떞�떦遺��꽌 -->
+ <!-- �궡媛� �떞�떦�븯�뒗 �씠�뒋 - �떞�떦遺��꽌 -->
+ <!--<select id="findMyAssigneeIssue" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ select
+ distinct i.id as id,
+ i.title as title,
+ p.name as projectName,
+ CONCAT(p.project_key, '-', i.issue_number) AS issueKey,
+ p.project_key as projectKey,
+ i.issue_number as issueNumber,
+ iss.name as issueStatusName,
+ replace(i.complete_date, "-", ".") as completeDate,
+ replace(SUBSTRING(i.register_date, 1, 10), "-", ".") as registerDate
+ from issue i
+ inner join issue_status iss on iss.id = i.issue_status_id
+ inner join project p on p.id = i.project_id
+ WHERE 1=1
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND p.id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ and iss.issue_status_type != 'CLOSE'
+ GROUP by i.id
+ <if test="page != null and !page.equals('')">
+ limit #{pageSize} offset #{page};
+ </if>
+ </select>-->
+
<select id="findMyAssigneeIssue" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
select
@@ -497,7 +683,7 @@
replace(i.complete_date, "-", ".") as completeDate,
replace(SUBSTRING(i.register_date, 1, 10), "-", ".") as registerDate
from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
inner join project p on p.id = i.project_id
WHERE 1=1
@@ -511,14 +697,11 @@
</choose>
<choose>
<when test="myDepartmentIds.size != 0">
- AND id.department_id IN
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
- <otherwise>
- AND 1 != 1
- </otherwise>
</choose>
and iss.issue_status_type != 'CLOSE'
GROUP by i.id
@@ -854,17 +1037,17 @@
d.department_name as departmentName,
p.name as projectName,
(select count(*) from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
- where id.department_id = d.id and i.project_id = p.id and iss.issue_status_type = 'CLOSE') as completeCount,
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type = 'CLOSE') as completeCount,
(select count(*) from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
- where id.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE') as remainCount,
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE') as remainCount,
(select count(*) from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
- where id.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE' and i.complete_date is not null and i.complete_date <![CDATA[ < ]]> now()) as delayCount
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE' and i.complete_date is not null and i.complete_date <![CDATA[ < ]]> now()) as delayCount
from
department d
inner join project_role_department prd on prd.department_id = d.id
@@ -875,6 +1058,48 @@
<when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
p.id IN
<foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ and p.status = '02';
+ </select>
+
+ <select id="findProjectMemberIssueByDepartment" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ select
+ d.department_name as departmentName,
+ p.name as projectName,
+ (select count(*) from issue i
+ inner join issue_department isd on isd.issue_id = i.id
+ inner join issue_status iss on iss.id = i.issue_status_id
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type = 'CLOSE') as completeCount,
+ (select count(*) from issue i
+ inner join issue_department isd on isd.issue_id = i.id
+ inner join issue_status iss on iss.id = i.issue_status_id
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE') as remainCount,
+ (select count(*) from issue i
+ inner join issue_department isd on isd.issue_id = i.id
+ inner join issue_status iss on iss.id = i.issue_status_id
+ where isd.department_id = d.id and i.project_id = p.id and iss.issue_status_type != 'CLOSE' and i.complete_date is not null and i.complete_date <![CDATA[ < ]]> now()) as delayCount
+ from
+ department d
+ inner join project_role_department prd on prd.department_id = d.id
+ inner join project_role pr on pr.id = prd.project_role_id
+ inner join project p on p.id = pr.project_id
+ WHERE
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ p.id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND prd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
@@ -946,18 +1171,15 @@
<select id="findMyAssigneeCompleteIssue" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
select i.project_id as projectId, count(distinct i.id) as issueCount from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
<choose>
<when test="myDepartmentIds.size != 0">
- AND id.department_id IN
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
- <otherwise>
- AND 1 != 1
- </otherwise>
</choose>
and iss.issue_status_type = 'CLOSE'
<choose>
@@ -993,19 +1215,16 @@
<select id="findMyAssigneeRemainIssue" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
select i.project_id as projectId, count(distinct i.id) as issueCount from issue i
- inner join issue_department id on id.issue_id = i.id
+ inner join issue_department isd on isd.issue_id = i.id
inner join issue_status iss on iss.id = i.issue_status_id
where iss.issue_status_type != 'CLOSE'
<choose>
<when test="myDepartmentIds.size != 0">
- id.department_id IN
+ AND isd.department_id IN
<foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</when>
- <otherwise>
- AND 1 != 1
- </otherwise>
</choose>
<choose>
<when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
@@ -1057,6 +1276,39 @@
</choose>
</select>
+ <select id="countSeverityIssueByDepartment" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT
+ COUNT(case when s.id = 1 then 'CRITICAL' END) AS 'critical',
+ COUNT(case when s.id = 2 then 'MAJOR' END) AS 'major',
+ COUNT(case when s.id = 3 then 'MINOR' END) AS 'minor',
+ COUNT(case when s.id = 4 then 'TRIVIAL' END) AS 'trivial'
+ FROM issue i
+ INNER JOIN project p ON p.id = i.project_id
+ INNER JOIN workspace w ON w.id = p.workspace_id
+ INNER JOIN severity s ON s.id = i.severity_id
+ INNER JOIN issue_status iss ON iss.id = i.issue_status_id
+ INNER JOIN issue_department isd on isd.issue_id = i.id
+ WHERE w.id = #{workspaceId}
+ AND iss.issue_status_type != 'CLOSE'
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
<!-- 以묒슂�룄 �씠�뒋 紐⑸줉 -->
<select id="findSeverityIssues" resultType="java.util.HashMap"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
@@ -1092,6 +1344,49 @@
</if>
</select>
+ <select id="findSeverityIssuesByDepartment" resultType="java.util.HashMap"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT i.title AS title,
+ s.name AS severityName,
+ p.name AS projectName,
+ s.color AS severityColor,
+ replace(i.start_date, "-", ".") AS startDate,
+ replace(i.complete_date, "-", ".") AS completeDate,
+ i.issue_number AS issueNumber,
+ p.project_key AS projectKey,
+ p.name AS projectName,
+ iss.name AS issueStatusName,
+ CONCAT(p.project_key, '-', i.issue_number) AS issueKey
+ FROM issue i
+ INNER JOIN project p ON p.id = i.project_id
+ INNER JOIN workspace w ON w.id = p.workspace_id
+ INNER JOIN severity s ON s.id = i.severity_id
+ INNER JOIN issue_status iss ON iss.id = i.issue_status_id
+ INNER JOIN issue_department isd on isd.issue_id = i.id
+ WHERE w.id = #{workspaceId}
+ AND s.id = #{severityId}
+ AND iss.issue_status_type != 'CLOSE'
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <if test="page != null and !page.equals('')">
+ limit #{pageSize} offset #{page};
+ </if>
+ </select>
+
<!-- 以묒슂�룄 �빆紐� 蹂� 媛��닔 -->
<select id="countSearchIssue" resultType="java.lang.Long"
parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
@@ -1114,6 +1409,36 @@
</choose>
</select>
+ <select id="countSearchIssueByDepartment" resultType="java.lang.Long"
+ parameterType="kr.wisestone.owl.web.condition.WidgetCondition">
+ SELECT COUNT(distinct i.id)
+ FROM issue i
+ INNER JOIN project p ON p.id = i.project_id
+ INNER JOIN workspace w ON w.id = p.workspace_id
+ INNER JOIN severity s ON s.id = i.severity_id
+ INNER JOIN issue_status iss ON iss.id = i.issue_status_id
+ INNER JOIN issue_department isd on isd.issue_id = i.id
+ WHERE w.id = #{workspaceId}
+ AND s.id = #{severityId}
+ AND iss.issue_status_type != 'CLOSE'
+ <choose>
+ <when test="meAndDownProjectIds != null and meAndDownProjectIds.size != 0">
+ AND i.project_id IN
+ <foreach collection="meAndDownProjectIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ <choose>
+ <when test="myDepartmentIds.size != 0">
+ AND isd.department_id IN
+ <foreach collection="myDepartmentIds" item="item" index="index" separator="," open="(" close=")">
+ #{item}
+ </foreach>
+ </when>
+ </choose>
+ </select>
+
<!-- 13踰� �쐞�젽 �걹 -->
</mapper>
diff --git a/src/main/resources/mybatis/query-template/workspace-template.xml b/src/main/resources/mybatis/query-template/workspace-template.xml
index 52ea13f..ac1f85f 100644
--- a/src/main/resources/mybatis/query-template/workspace-template.xml
+++ b/src/main/resources/mybatis/query-template/workspace-template.xml
@@ -4,6 +4,159 @@
<mapper namespace="kr.wisestone.owl.mapper.WorkspaceMapper">
<!-- �뾽臾� 怨듦컙 �궘�젣 -->
+ <!--<delete id="deleteWorkspace" parameterType="java.util.HashMap">
+ <choose>
+ <when test="workflowIds.size != 0">
+ <!– �썙�겕�뵆濡쒖슦 �뿰寃� �젙蹂� �궘�젣 –>
+ DELETE FROM workflow_transition WHERE workflow_id IN (
+ <foreach collection="workflowIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <choose>
+ <when test="userInviteIds.size != 0">
+ <!– �뾽臾� 怨듦컙�뿉 珥덈��븳 �봽濡쒖젥�듃 �젙蹂� �궘�젣 –>
+ DELETE FROM user_invite_project WHERE user_invite_id IN (
+ <foreach collection="userInviteIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <choose>
+ <when test="projectRoleIds.size != 0">
+ <!– �봽濡쒖젥�듃 李몄뿬 �궗�슜�옄 �궘�젣 –>
+ DELETE FROM project_role_user WHERE project_role_id IN (
+ <foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �봽濡쒖젥�듃 沅뚰븳 �뿰寃� �젙蹂� �궘�젣 –>
+ DELETE FROM project_role_permission WHERE project_role_id IN(
+ <foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <choose>
+ <when test="projectIds.size != 0">
+
+ <!– �봽濡쒖젥�듃 �뿭�븷 �궘�젣 –>
+ DELETE FROM project_role WHERE project_id IN (
+ <foreach collection="projectIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �봽濡쒖젥�듃�뿉 �뿰寃곕맂 �궗�슜�옄 �젙�쓽 �븘�뱶 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_type_custom_field WHERE project_id IN (
+ <foreach collection="projectIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 怨좎쑀 踰덊샇 �깮�꽦 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_number_generator WHERE project_id IN (
+ <foreach collection="projectIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 �씠�젰 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_history WHERE project_id IN (
+ <foreach collection="projectIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �씠�뒋 �궘�젣 –>
+ DELETE FROM issue WHERE project_id IN (
+ <foreach collection="projectIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <choose>
+ <when test="customFieldIds.size != 0">
+
+ <!– �씠�뒋 �궗�슜�옄 �젙�쓽 �븘�뱶 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_custom_field_value WHERE custom_field_id IN (
+ <foreach collection="customFieldIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+
+ <!– �궗�슜�옄 �젙�쓽 �븘�뱶 媛� �궘�젣 –>
+ DELETE FROM custom_field_value WHERE custom_field_id IN (
+ <foreach collection="customFieldIds" item="item" index="index" separator="," open="" close="">
+ #{item}
+ </foreach>
+ );
+ </when>
+ </choose>
+
+ <!– �궗�슜�옄 �젙�쓽 �븘�뱶 �궘�젣 –>
+ DELETE FROM custom_field WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 �긽�깭 �궘�젣 –>
+ DELETE FROM issue_status WHERE workspace_id = #{workspaceId};
+
+ <!– �썙�겕�뵆濡쒖슦 �궘�젣 –>
+ DELETE FROM workflow WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 ���엯 �궘�젣 –>
+ DELETE FROM issue_type WHERE workspace_id = #{workspaceId};
+
+ <!– �봽濡쒖젥�듃 �궘�젣 –>
+ DELETE FROM project WHERE workspace_id = #{workspaceId};
+
+ <!– �뾽臾� 怨듦컙�뿉 珥덈� �젙蹂� �궘�젣 –>
+ DELETE FROM user_invite WHERE workspace_id = #{workspaceId};
+
+ <!– �뾽臾� 怨듦컙 李몄뿬�옄 �궘�젣 –>
+ DELETE FROM user_workspace WHERE workspace_id = #{workspaceId} AND manager_yn = 'N';
+
+ <!– �씠�뒋 �떞�떦�옄 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_user WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 由ъ뒪�겕 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_risk WHERE workspace_id = #{workspaceId};
+
+ <!– 愿��떖 �씠�뒋 �젙蹂� �궘�젣 –>
+ DELETE FROM user_like_issue WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 �뙎湲� �궘�젣 –>
+ DELETE FROM issue_comment WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 寃��깋 議곌굔 �궘�젣 –>
+ DELETE FROM issue_search WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 �뀒�씠釉� �꽕�젙 �궘�젣 –>
+ DELETE FROM issue_table_config WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 踰꾩쟾 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_version WHERE workspace_id = #{workspaceId};
+
+ <!– �슦�꽑 �닚�쐞 �젙蹂� �궘�젣 –>
+ DELETE FROM priority WHERE workspace_id = #{workspaceId};
+
+ <!– 以묒슂�룄 �젙蹂� �궘�젣 –>
+ DELETE FROM severity WHERE workspace_id = #{workspaceId};
+
+ <!– �씠�뒋 諛쒖깮 �삁�빟 �젙蹂� �궘�젣 –>
+ DELETE FROM issue_reservation WHERE workspace_id = #{workspaceId};
+
+ </delete>-->
+
<delete id="deleteWorkspace" parameterType="java.util.HashMap">
<choose>
<when test="workflowIds.size != 0">
@@ -29,8 +182,8 @@
<choose>
<when test="projectRoleIds.size != 0">
- <!-- �봽濡쒖젥�듃 李몄뿬 �궗�슜�옄 �궘�젣 -->
- DELETE FROM project_role_user WHERE project_role_id IN (
+ <!-- �봽濡쒖젥�듃 李몄뿬 遺��꽌 �궘�젣 -->
+ DELETE FROM project_role_department WHERE project_role_id IN (
<foreach collection="projectRoleIds" item="item" index="index" separator="," open="" close="">
#{item}
</foreach>
--
Gitblit v1.8.0