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.
390 lines
17 KiB
390 lines
17 KiB
package kr.co.kihyun.beans.user; |
|
|
|
import java.sql.ResultSet; |
|
import java.sql.SQLException; |
|
import java.io.FileReader; |
|
import java.io.IOException; |
|
import kr.co.kihyun.beans.entity.MUser; |
|
import java.sql.ResultSetMetaData; |
|
import javax.jdo.PersistenceManager; |
|
import org.slf4j.Logger; |
|
import org.slf4j.LoggerFactory; |
|
import kr.co.kihyun.db.DBManager; |
|
import kr.co.kihyun.db.*; |
|
import java.sql.*; |
|
import javax.jdo.Transaction; |
|
import kr.co.kihyun.beans.entity.util.MPersistenceManager; |
|
import kr.co.kihyun.beans.entity.util.PMF; |
|
|
|
public class MultiCheck extends DBManager { |
|
|
|
private static final Logger LOG = LoggerFactory.getLogger(MultiCheck.class); |
|
|
|
int cnt = 0; |
|
int count = 0; |
|
String[] array; |
|
String[] nameList; |
|
String[] getPassword; |
|
String[] deptID; |
|
String[] userId; |
|
String[] deptName; |
|
String[] adpInsttCode; |
|
String[] pNo; |
|
String[] kraOrgNm; |
|
String[] sOrginDeptFlag; //2014.08.19 Add by KWON,HAN : 원소속 부서 구분 |
|
|
|
int tcount = 0; |
|
ResultSet rs = null; |
|
ResultSetMetaData rsmd = null; |
|
int start = 0; |
|
int limit = 30; |
|
|
|
public void IdCheck(String id) throws Exception { |
|
StringBuilder sql = new StringBuilder(250); |
|
|
|
String sDualPostYn = ""; |
|
MUser mUser = null; |
|
|
|
PersistenceManager pm = new MPersistenceManager(PMF.get().getPersistenceManager()); |
|
Transaction tx = pm.currentTransaction(); |
|
|
|
try { |
|
tx.begin(); |
|
|
|
mUser = pm.detachCopy(pm.getObjectById(MUser.class, id)); |
|
sDualPostYn = mUser.getDualPostYn(); |
|
if (sDualPostYn == null) { |
|
sDualPostYn = ""; |
|
} |
|
|
|
tx.rollback(); |
|
|
|
//return executeLogin(userId, userPassword, ip, session, deptId); |
|
} catch (Exception e) { |
|
LOG.error("User '{}' IdCheck mUser failed: {}", id, e.getMessage()); |
|
//return 11; |
|
} finally { |
|
if (tx.isActive()) { |
|
tx.rollback(); |
|
} |
|
pm.close(); |
|
} |
|
|
|
FileReader in = null; |
|
|
|
try { |
|
|
|
// in = new FileReader("/edudocs/wasapps/atswas/moumi_gacp.properties"); //운영서버용 |
|
in = new FileReader("C:/edusys/moum00/was/moumi_gacp.properties"); //로컬서버용 |
|
|
|
//in = new FileReader("D:/NetBeansProjectsNeis/moumi5-war/src/main/webapp/moumi_gacp.properties"); //2014.08.19 Add By KWON,HAN |
|
// in = new FileReader("E:/02. Source/NEIS/moumi5-war/src/main/webapp/moumi_gacp.properties"); //2014.09.30 Add By Youngjun Cho |
|
|
|
int c; |
|
String s = new String(); |
|
|
|
while ((c = in.read()) != -1) { |
|
s = s + (char) c; |
|
} |
|
|
|
String[] data = s.split(","); |
|
String[] subData = null; |
|
|
|
String connection1 = data[0]; |
|
String connection2 = data[1]; |
|
String connection3 = data[2]; |
|
String loginTimeout = "10"; |
|
String maxActive = "100"; |
|
String maxIdle = "25"; |
|
String maxWait = "10"; |
|
|
|
String url = connection1; //개발 |
|
String user = connection2; |
|
String password = connection3; |
|
|
|
|
|
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); |
|
|
|
|
|
//37.패스워드 평문 저장_CWE-256 Comment by YOUNGJUN,CHO |
|
// - 취약점의 해결책으로는 DB 연결시 암호화된 패스워드를 복호화해서 사용하라고 되어있음. |
|
// 현재 properties 파일에 평문으로 패스워드가 저장되어있는데, 이것을 암호화하여 시도별로 서버관리자가 properties 파일을 수정하거나 개발사업단에서 파일 수정 후 배포를 해야 한다. |
|
// 현재 테스트를 해볼 환경이 되지 않아 2014년도 추가개발에서는 해당 취약점에 대한 처리를 진행하지 않는것으로 함. |
|
// |
|
// - 향후 암호화 도입시 제안사항 |
|
// AES 128 이나 AES 256 알고리즘으로 진행하면 될거 같다. |
|
// 현재 프로젝트 소스에서 AES 128 은 사용이 가능하다. |
|
// 256 은 암호화 키를 32 bytes 로 해야하는데, illegal key size exception 이 발생한다. |
|
// 이를 해결하려면 256 암호화를 지원하는 unlimited strength crypto file(local_policy.jar, US_export_policy.jar) 이 필요하다. |
|
//===================================================== |
|
con = DriverManager.getConnection(url, user, password); |
|
|
|
|
|
//32.J2EE 직접 연결 관리_CWE-245 Comment by YOUNGJUN,CHO |
|
// context.xml 에 'jdbc/MoumiOB2' 라는 이름으로 GACKWD 에 접속하는 연결문자열을 정의해두고, lookup 을 통해 DB에 connection 을 할 수 있다. |
|
// 그러나 실제 운영상에선 tomcat 이 아니라 jeus 를 사용하는데, 현재 테스트를 해볼 환경이 되지 않아 2014년도 추가개발에서는 해당 취약점에 대한 처리를 진행하지 않는것으로 함. |
|
// - context.xml 파일에 추가했던 내용 |
|
// <Resource auth="Container" driverClassName="com.ibm.db2.jcc.DB2Driver" loginTimeout="10" maxActive="100" maxIdle="25" maxWait="10" name="jdbc/MoumiOB2" password="gacdbmgr" testOnBorrow="true" type="javax.sql.DataSource" url="jdbc:db2://10.50.9.140:11663/GACKWD:deferPrepares=false:currentSchema=GACDBMGR;" username="gacdbmgr" validationQuery="select * from sysibm.SYSDUMMY1"/> |
|
/* |
|
InitialContext initCtx = new InitialContext(); |
|
DataSource ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/MoumiOB2"); |
|
con = ds.getConnection(); |
|
*/ |
|
//===================================================== |
|
|
|
|
|
//2014.08.19 Update by KWON,HAN |
|
/* ORIGN_DEPT_FLAG */ |
|
/* N1 : 지방공무원 (겸임) */ |
|
/* N2 : 지방공무원(파견) */ |
|
/* N3 : 교원(겸임) */ |
|
/* N4 : 교원(파견) */ |
|
/* Y : 원소속 */ |
|
/* N5 : 병설유치원 */ |
|
//2015.01.26 Update by jskim |
|
/* 중복으로 겸임정보가 조회되는 문제로 겸임 파견기관은 모두 N1, 1로 수정 */ |
|
sql.append(" select us.USRID, dual.adp_dept_code, dual.adp_dptnm, dual.p_no \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" from TN_GPSEGOAD001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") |
|
.append(" where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE \n") |
|
.append(" and ((dual.ADP_RELIS_YMD is not null and dual.ADP_RELIS_YMD >= to_char(sysdate, 'YYYYMMDD'))" + |
|
" or (dual.ADP_RELIS_YMD is null and dual.ADP_RELIS_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") |
|
.append(" AND dual.GNFD_TRT_CODE = '2' \n") |
|
.append(" AND us.USRID='" + id + "' \n") |
|
.append(" AND DUAL.ADP_DEPT_CODE IS NOT NULL \n") |
|
.append(" union \n") |
|
.append(" select us.USRID, dual.DSPTC_DEPT_CODE, dual.dsptc_dptnm, dual.p_no \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" from TN_GPSEGODS001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt where us.P_NO = dual.P_NO \n") |
|
.append(" and us.PSITN_ORG_CODE = dt.ORG_CODE " ) |
|
.append(" and ((dual.DSPTC_END_YMD is not null and dual.DSPTC_END_YMD >= to_char(sysdate, 'YYYYMMDD'))" + |
|
" or (dual.DSPTC_END_YMD is null and dual.DSPTC_END_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") |
|
.append(" AND dual.GNFD_TRT_CODE = '2' \n") |
|
.append(" AND us.USRID='" + id + "' \n") |
|
.append(" AND DUAL.DSPTC_DEPT_CODE IS NOT NULL \n") |
|
.append(" union \n") |
|
.append(" select us.USRID, dual.adp_dept_code, dual.adp_dptnm, dual.p_no \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" from TN_TPSEGOMR002 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE \n") |
|
.append(" and ((dual.ADP_RELIS_YMD is not null and dual.ADP_RELIS_YMD >= to_char(sysdate, 'YYYYMMDD'))" + |
|
" OR (dual.ADP_RELIS_YMD is null and dual.ADP_RELIS_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) ") |
|
.append(" AND dual.GNFD_TRT_CODE = '2' \n") |
|
.append(" AND us.USRID='" + id + "' \n") |
|
.append(" AND DUAL.ADP_DEPT_CODE IS NOT NULL \n") |
|
.append(" union \n") |
|
.append(" select us.USRID, dual.DSPTC_DEPT_CODE, dual.dsptc_dptnm, dual.p_no \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" from TN_TPSEGOTC001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") |
|
.append(" where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE " ) |
|
.append(" and ((dual.DSPTC_END_YMD is not null and dual.DSPTC_END_YMD >= to_char(sysdate, 'YYYYMMDD'))" + |
|
" OR (dual.DSPTC_END_YMD is null and dual.DSPTC_END_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") |
|
.append(" AND dual.GNFD_TRT_CODE = '2' \n") |
|
.append(" AND us.USRID='" + id + "' \n") |
|
.append(" AND DUAL.DSPTC_DEPT_CODE IS NOT NULL \n") |
|
.append(" union \n") |
|
.append(" select us.USRID,dt.ORG_CODE,dt.KRA_ORG_NM,us.P_NO \n") |
|
.append(" , 'Y' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '0' AS ORDER_BY \n") |
|
.append(" from TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") |
|
.append(" where us.PSITN_ORG_CODE = dt.ORG_CODE AND us.USRID='" + id + "' \n"); |
|
|
|
//2014.08.19 Add by KWON,HAN |
|
if ("Y".equals(sDualPostYn)) { |
|
sql.append(" union \n") |
|
.append(" SELECT '" + id + "' AS USRID \n") |
|
.append(" , A.ORG_CODE /* 병설유치원 부서ID */ \n") |
|
.append(" --, A.EAAA_YN /* 병설유치원 구분 */ \n") |
|
.append(" --, A.EAAA_SCHUL_CODE /* 원소속 부서ID */ \n") |
|
.append(" , DT.KRA_ORG_NM \n") |
|
.append(" , (SELECT T.P_NO FROM TN_SYMUSMUM001 T WHERE T.USRID = '" + id + "' AND ROWNUM = 1 ) AS P_NO \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" FROM TN_SYMOCMSI001 A \n") |
|
.append(" , TC_SYMOCMOC000 DT \n") |
|
.append(" WHERE A.EAAA_YN = 'Y' \n") |
|
.append(" AND A.ABSCH_YN != 'Y' \n") |
|
.append(" AND A.EAAA_SCHUL_CODE IS NOT NULL \n") |
|
.append(" AND A.EAAA_SCHUL_CODE IN ( SELECT DT.ORG_CODE \n") |
|
.append(" FROM TN_SYMUSMUM001 US, TC_SYMOCMOC000 DT \n") |
|
.append(" WHERE US.PSITN_ORG_CODE = DT.ORG_CODE \n") |
|
.append(" AND US.USRID = '" + id + "' ) \n") |
|
.append(" AND A.ORG_CODE = DT.ORG_CODE(+) \n") |
|
|
|
/* 분교 겸임업무 조회 2018.04.04 */ |
|
.append(" union \n") |
|
.append(" SELECT '" + id + "' AS USRID \n") |
|
.append(" , A.ORG_CODE /* 분교 부서ID */ \n") |
|
.append(" --, A.EAAA_YN /* 분교 구분 */ \n") |
|
.append(" --, A.EAAA_SCHUL_CODE /* 원소속 부서ID */ \n") |
|
.append(" , DT.KRA_ORG_NM \n") |
|
.append(" , (SELECT T.P_NO FROM TN_SYMUSMUM001 T WHERE T.USRID = '" + id + "' AND ROWNUM = 1 ) AS P_NO \n") |
|
.append(" , 'N1' AS ORIGN_DEPT_FLAG \n") |
|
.append(" , '1' AS ORDER_BY \n") |
|
.append(" FROM TN_SYMOCMSI001 A \n") |
|
.append(" , TC_SYMOCMOC000 DT \n") |
|
.append(" WHERE A.BNHH_YN = 'Y' \n") |
|
.append(" AND A.ABSCH_YN != 'Y' \n") |
|
.append(" AND A.MAISC_ORG_CODE IS NOT NULL \n") |
|
.append(" AND A.MAISC_ORG_CODE IN ( SELECT DT.ORG_CODE \n") |
|
.append(" FROM TN_SYMUSMUM001 US, TC_SYMOCMOC000 DT \n") |
|
.append(" WHERE US.PSITN_ORG_CODE = DT.ORG_CODE \n") |
|
.append(" AND US.USRID = '" + id + "' ) \n") |
|
.append(" AND A.ORG_CODE = DT.ORG_CODE(+) \n"); |
|
} |
|
|
|
sql.append(" ORDER BY ORDER_BY \n"); |
|
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
|
|
|
System.out.println("sql -------------- " + sql.toString()); |
|
rs = execQuery(sql.toString()); |
|
|
|
if (rs != null) { |
|
rs.last(); |
|
tcount = rs.getRow(); |
|
rs.beforeFirst(); |
|
|
|
userId = new String[tcount]; |
|
adpInsttCode = new String[tcount]; |
|
deptName = new String[tcount]; |
|
pNo = new String[tcount]; |
|
sOrginDeptFlag = new String[tcount]; //2014.08.19 Add by KWON,HAN |
|
|
|
int i = 0; |
|
while(rs.next()){ |
|
userId[i] = rs.getString(1); |
|
adpInsttCode[i] = rs.getString(2); |
|
deptName[i] = rs.getString(3); |
|
pNo[i] = rs.getString(4); |
|
sOrginDeptFlag[i] = rs.getString(5); //2014.08.19 Add by KWON,HAN |
|
i++; |
|
}; |
|
} |
|
|
|
} catch (ClassNotFoundException cnfex) { |
|
cnfex.printStackTrace(); |
|
} catch (IOException ioex) { |
|
ioex.printStackTrace(); |
|
} catch (IllegalAccessException iaex) { |
|
iaex.printStackTrace(); |
|
} catch (InstantiationException istex) { |
|
istex.printStackTrace(); |
|
} catch (SQLException ex) { |
|
ex.printStackTrace(); |
|
} finally { |
|
close(rs); |
|
execClose(); |
|
} |
|
} |
|
|
|
public int deptUpdate(String id, String DeptID) throws SQLException { |
|
String sql = ""; |
|
int i = 0; |
|
try { |
|
sql = "UPDATE MOUMI_MUSER SET DEPT_ID = '" + DeptID + "' WHERE ID = '" + id + "'"; |
|
i = execUpdate(sql); |
|
} catch (SQLException sqlEx) { |
|
sqlEx.printStackTrace(); |
|
//================================================ |
|
} catch (Exception ex) { |
|
ex.printStackTrace(); |
|
} finally { |
|
close(rs); |
|
execClose(); |
|
} |
|
return i; |
|
} |
|
|
|
/* |
|
2014.08.20 Add by KWON,HAN |
|
*/ |
|
public int deptUpdateOrign(String id, String DeptID, String orignDeptFlag) throws SQLException { |
|
String sql = ""; |
|
int i = 0; |
|
|
|
try { |
|
sql = sql + "UPDATE MOUMI_MUSER \n"; |
|
sql = sql + " SET DEPT_ID = ? \n"; |
|
|
|
if ("Y".equals(orignDeptFlag)) { |
|
sql = sql + " , ORIGN_DEPT_ID = NULL \n"; |
|
} else { |
|
sql = sql + " , ORIGN_DEPT_ID = DEPT_ID \n"; |
|
} |
|
sql = sql + " WHERE ID = ? \n"; |
|
|
|
i = execUpdate(sql.toString(), DeptID, id); |
|
|
|
} catch (SQLException sqlEx) { |
|
sqlEx.printStackTrace(); |
|
//================================================ |
|
} catch (Exception ex) { |
|
ex.printStackTrace(); |
|
} finally { |
|
close(rs); |
|
execClose(); |
|
} |
|
|
|
return i; |
|
} |
|
|
|
/* |
|
* 2014.09.29 Add by KWON,HAN : 인사이동 후 이관여부 칼럼의 값 업데이트하기 |
|
*/ |
|
public int updateModYnOfMuser(String id, String sModYn) throws SQLException { |
|
String sql = ""; |
|
int i = 0; |
|
try { |
|
sql = "UPDATE MOUMI_MUSER SET MOD_YN = '" + sModYn + "' WHERE ID = '" + id + "'"; |
|
i = execUpdate(sql); |
|
} catch (SQLException sqlEx) { |
|
sqlEx.printStackTrace(); |
|
} catch (Exception ex) { |
|
ex.printStackTrace(); |
|
} finally { |
|
close(rs); |
|
execClose(); |
|
|
|
} |
|
return i; |
|
} |
|
|
|
public String[] getNameList() { |
|
return nameList; |
|
} |
|
|
|
public String[] getDeptID() { |
|
return adpInsttCode; |
|
} |
|
|
|
public String[] getUserId() { |
|
return userId; |
|
} |
|
|
|
public String[] getDeptName() { |
|
return deptName; |
|
} |
|
|
|
public String[] getPno() { |
|
return pNo; |
|
} |
|
|
|
public int getCount() { |
|
return tcount; |
|
} |
|
|
|
//2014.08.19 Add by KWON,HAN :원소속 부서 구분 |
|
/* ORIGN_DEPT_FLAG */ |
|
/* N1 : 지방공무원 (겸임) */ |
|
/* N2 : 지방공무원(파견) */ |
|
/* N3 : 교원(겸임) */ |
|
/* N4 : 교원(파견) */ |
|
/* Y : 원소속 */ |
|
/* N5 : 병설유치원 */ |
|
public String[] getOrignDeptFlag() { |
|
return sOrginDeptFlag; |
|
} |
|
}
|
|
|