jskim
/
moumiKnu
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
knu project
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
4 Commits
1 Branch
0 Tags
69 MiB
 
 
 
 
 
 
Tag: Branch: Tree: main
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
moumiKnu/src_bak/kr/co/kihyun/service/UserPartService.java

276 lines
18 KiB
Raw Permalink Blame History

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package kr.co.kihyun.service;
import java.util.List;
import java.util.Map;
import javax.jdo.PersistenceManager;
import javax.jdo.Query;
import kr.co.kihyun.beans.entity.MDept;
import kr.co.kihyun.beans.entity.SysAuth;
import kr.co.kihyun.lang.MString;
import kr.co.kihyun.service.vo.UserPartVO;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
/**
*
* @author Kts
*/
public class UserPartService extends BaseService {
// private static final Logger LOG = LoggerFactory.getLogger(UserPartService.class);
/**
* 기본생성자
*/
public UserPartService() {
}
/**
* 생성자
* @param pm PersistenceManager
*/
public UserPartService(PersistenceManager pm) {
super(pm);
}
/**
* 검색어를 사용하여 사용자그룹목록을 반환하는 함수
* @param params - 검색조건맵(partName:그룹명, regDate:등록일, usrName:생성자, usrId:생성자id)
*/
public void createUserPartListSearchQuery(Map params) {
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbSearch = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
String userId = (String)params.get("usrId");
String dtID = (String)params.get("dtID");
String type = (String)params.get("type");
int sysAuth = (Integer)params.get("sysAuth");
MDept mDept = pm.getObjectById(MDept.class, dtID);
String grade = mDept.getGrade().toString();
//================================== Search Query ==================================
sbSearch.append("SELECT \n");
sbSearch.append(" PART.ID AS id \n");
sbSearch.append(" , PART.HANDYID AS handyId \n");
sbSearch.append(" , PART.USER_ID AS userId \n");
sbSearch.append(" , PART.NAME AS name \n");
sbSearch.append(" , PART.REG_DATE AS regDate \n");
sbSearch.append(" , PART.DES AS des \n");
sbSearch.append(" , PART.USE_YN AS useYn \n");
sbSearch.append(" , PART.MOD_DATE AS modDate \n");
sbSearch.append(" , SP_CM_DEC_FU(USR.NAME) AS userName \n");
sbSearch.append(" , DPT.NAME AS deptName \n");
// sbSearch.append(" , ROWNUMBER() OVER(ORDER BY PART.NAME ASC) AS rowNo \n");
// 2014.10.06 BY YOUNJUN CHO (각 그룹의 하위 부서갯수 구하는 서브쿼리 수정, 폐교된 부서인 경우 카운트하지 않도록 처리.)
sbSearch.append(",( \n");
sbSearch.append(" SELECT COUNT(UD.USER_DEPT_ID) AS CNT \n");
sbSearch.append(" FROM MOUMI_USER_PART PART INNER JOIN MOUMI_USER_DEPT UD \n");
sbSearch.append(" ON UD.PART_ID = PART.ID \n");
sbSearch.append(" INNER JOIN MOUMI_DEPT DT \n");
sbSearch.append(" ON UD.ID = DT.ID \n");
sbSearch.append(" AND DT.IS_OUT = 'N' \n");
// sbSearch.append(") \n");
// sbSearch.append("+ ( \n");
// sbSearch.append(" SELECT COUNT(UU.USER_USER_ID) AS CNT \n");
// sbSearch.append(" FROM MOUMI_USER_USER UU \n");
// sbSearch.append(" INNER JOIN MOUMI_DEPT DT \n");
// sbSearch.append(" ON UU.PART_ID = PART.ID \n");
// sbSearch.append(" AND UU.ID = DT.ID \n");
// sbSearch.append(" AND DT.IS_OUT = 'N' \n");
sbSearch.append(") AS groupCount \n");
sbSearch.append("FROM \n");
sbSearch.append(" MOUMI_USER_PART PART \n");
sbSearch.append(" INNER JOIN \n");
sbSearch.append(" MOUMI_MUSER USR \n");
sbSearch.append("ON PART.USER_ID = USR.ID \n");
sbSearch.append(" INNER JOIN \n");
sbSearch.append(" MOUMI_DEPT DPT \n");
sbSearch.append("ON USR.DEPT_ID = DPT.ID \n");
if( sysAuth == SysAuth.SUB_SYSADM.ordinal() && MString.isNull(grade)){
sbSearch.append(" INNER JOIN \n");
sbSearch.append(" MOUMI_DEPT GR \n");
sbSearch.append("ON DPT.ORGAN = GR.ID \n");
}
//================================== Count Query ==================================
sbCount.append("SELECT COUNT(PART.ID) AS CNT \n");
sbCount.append("FROM \n");
sbCount.append(" MOUMI_USER_PART PART \n");
if( (sysAuth == SysAuth.SUB_SYSADM.ordinal() || sysAuth == SysAuth.SUB_TOTADM.ordinal()) && MString.isNull(grade)){
sbCount.append(" , MOUMI_MUSER USR \n");
sbCount.append(" , MOUMI_DEPT DPT \n");
sbCount.append(" , MOUMI_DEPT GR \n");
}
//=====================================================================================
//================================== Where Query ==================================
sbWhere.append("WHERE 1=1 \n");
if( "name".equals(type)) {
sbWhere.append(" AND PART.NAME LIKE '%' || :partName || '%' \n");
}else if("regDate".equals(type)) {
sbWhere.append(" AND PART.REG_DATE BETWEEN CAST( :regDate || ' 00:00:00' AS DATE) \n");
sbWhere.append(" AND CAST( :regDate || ' 23:59:59' AS DATE) \n");
}else if("regPerson".equals(type)) {
sbWhere.append(" AND EXISTS( \n");
sbWhere.append(" SELECT '' \n");
sbWhere.append(" FROM MOUMI_MUSER F \n");
sbWhere.append(" WHERE F.ID = PART.USER_ID \n");
sbWhere.append(" AND F.NAME = SP_CM_ENC_FU(:usrName) \n");
sbWhere.append(" ) \n");
}
if( userId != null && !userId.equals("") && sysAuth == SysAuth.TOTPER.ordinal()) {
sbWhere.append(" AND PART.USER_ID = :usrId \n");
}else if( sysAuth == SysAuth.REPOADM.ordinal() && !MString.isNull(grade)){
sbWhere.append(" AND PART.USER_ID = :usrId \n");
}else if( sysAuth == SysAuth.SUB_SYSADM.ordinal() && MString.isNull(grade)){
sbWhere.append(" AND PART.USER_ID = USR.ID \n");
sbWhere.append(" AND USR.DEPT_ID = DPT.ID \n");
sbWhere.append(" AND DPT.ORGAN = GR.ID \n");
sbWhere.append(" AND (part.user_id = :usrId or gr.id = ( SELECT OGR.id \n");
sbWhere.append(" FROM MOUMI_MUSER US \n");
sbWhere.append(" , MOUMI_DEPT DT \n");
sbWhere.append(" , MOUMI_DEPT OGR \n");
sbWhere.append(" WHERE US.ID= :usrId \n");
sbWhere.append(" AND US.DEPT_ID = DT.ID \n");
sbWhere.append(" AND DT.ORGAN = OGR.ID )) \n");
}else if( sysAuth == SysAuth.SUB_TOTADM.ordinal() && MString.isNull(grade)){
sbWhere.append(" AND PART.USER_ID = USR.ID \n");
sbWhere.append(" AND USR.DEPT_ID = DPT.ID \n");
sbWhere.append(" AND (part.user_id = :usrId or dpt.id = ( SELECT dt.id \n");
sbWhere.append(" FROM MOUMI_MUSER US \n");
sbWhere.append(" , MOUMI_DEPT DT \n");
sbWhere.append(" WHERE US.ID= :usrId \n");
sbWhere.append(" AND US.DEPT_ID = DT.ID)) \n");
}
sbSearch.append(sbWhere);
//================================== Order by ==================================
sbSearch.append(" ORDER BY PART.NAME ASC \n");
sbCount.append(sbWhere);
//레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장
this.setCountQuery(sbCount);
//조건맵을 멤버변수에 저장
this.setParams(params);
//검색쿼리를 멤버변수에 저장
this.setSearchQuery(sbSearch);
}
/**
* 검색어를 사용하여 사용자그룹목록을 반환하는 함수
* @param params - 검색조건맵(partName:그룹명, regDate:등록일, usrName:생성자, usrId:생성자id)
* @return List - 사용자그룹목록(사용자ID가 NULL인 경우 모든 그룹-관리자용, NOT_NULL인 경우 사용자가 만든 그룹)
*/
public List<UserPartVO> getUserPartListWithSearchWord(Map params) {
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbSearch = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
String userId = (String)params.get("usrId");
String type = (String)params.get("type");
//================================== Search Query ==================================
sbSearch.append("SELECT \n");
sbSearch.append(" PART.ID AS id \n");
sbSearch.append(" , PART.HANDYID AS handyId \n");
sbSearch.append(" , PART.USER_ID AS userId \n");
sbSearch.append(" , PART.NAME AS name \n");
sbSearch.append(" , PART.REG_DATE AS regDate \n");
sbSearch.append(" , PART.DES AS des \n");
sbSearch.append(" , PART.USE_YN AS useYn \n");
sbSearch.append(" , PART.MOD_DATE AS modDate \n");
sbSearch.append(" , SP_CM_DEC_FU(USR.NAME) AS userName \n");
sbSearch.append(" , DPT.NAME AS deptName \n");
sbSearch.append(" , ROWNUMBER() OVER(ORDER BY PART.NAME ASC) AS rowNo \n");
// 2014.09.18 BY YOUNJUN CHO (각 그룹의 하위 부서갯수 구하는 서브쿼리 수정, 폐교된 부서인 경우 카운트하지 않도록 처리.)
sbSearch.append(",( \n");
sbSearch.append(" SELECT COUNT(UD.USER_DEPT_ID) AS CNT \n");
sbSearch.append(" FROM MOUMI_USER_DEPT UD \n");
sbSearch.append(" INNER JOIN MOUMI_DEPT DT \n");
sbSearch.append(" ON UD.PART_ID = PART.ID \n");
sbSearch.append(" AND UD.ID = DT.ID \n");
sbSearch.append(" AND DT.IS_OUT = 'N' \n");
sbSearch.append(") \n");
sbSearch.append("+ ( \n");
sbSearch.append(" SELECT COUNT(UU.USER_USER_ID) AS CNT \n");
sbSearch.append(" FROM MOUMI_USER_USER UU \n");
sbSearch.append(" INNER JOIN MOUMI_DEPT DT \n");
sbSearch.append(" ON UU.PART_ID = PART.ID \n");
sbSearch.append(" AND UU.ID = DT.ID \n");
sbSearch.append(" AND DT.IS_OUT = 'N' \n");
sbSearch.append(") AS groupCount \n");
sbSearch.append("FROM \n");
sbSearch.append(" MOUMI_USER_PART PART \n");
sbSearch.append(" INNER JOIN \n");
sbSearch.append(" MOUMI_MUSER USR \n");
sbSearch.append("ON PART.USER_ID = USR.ID \n");
sbSearch.append(" INNER JOIN \n");
sbSearch.append(" MOUMI_DEPT DPT \n");
sbSearch.append("ON USR.DEPT_ID = DPT.ID \n");
//================================== Count Query ==================================
sbCount.append("SELECT COUNT(PART.ID) AS CNT \n");
sbCount.append("FROM \n");
sbCount.append(" MOUMI_USER_PART PART \n");
if("regPerson".equals(type)) {
sbCount.append(" INNER JOIN \n");
sbCount.append(" MOUMI_MUSER USR \n");
sbCount.append("ON PART.USER_ID = USR.ID \n");
}
//=====================================================================================
//================================== Where Query ==================================
sbWhere.append("WHERE \n");
if("0".equals(type)) {
sbWhere.append(" 1 = 1 \n");
}else if("name".equals(type)) {
sbWhere.append(" PART.NAME LIKE '%' || :partName || '%' \n");
}else if("regDate".equals(type)) {
sbWhere.append(" PART.REG_DATE BETWEEN CAST( :regDate || ' 00:00:00' AS DATE) \n");
sbWhere.append(" AND CAST( :regDate || ' 23:59:59' AS DATE) \n");
}else if("regPerson".equals(type)) {
sbWhere.append(" USR.NAME = SP_CM_ENC_FU(:usrName) \n");
}
if( userId != null && !userId.equals("")) {
sbWhere.append(" AND PART.USER_ID = :usrId \n");
}
sbSearch.append(sbWhere);
//================================== Order by ==================================
sbSearch.append(" ORDER BY PART.NAME ASC \n");
sbCount.append(sbWhere);
//레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장
//페이징처리함수 호출전에 등록 필요.
this.setCountQuery(sbCount);
//레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장
this.setParams(params);
//페이징처리쿼리생성함수 호출
StringBuilder pagenatingQuery = this.procPagenatingQuery(sbSearch, (Integer)params.get("endNo"));
Query query = pm.newQuery("javax.jdo.query.SQL",pagenatingQuery.toString());
query.setResultClass(UserPartVO.class);
List list = (List)query.executeWithMap(params);
return list;
}
}