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); }
@@ -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); } 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); 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); } 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(); 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; } } 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; } } 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 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"> 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> 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> 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>
