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/beans/tosys/etc1/TotsysEtc.java

335 lines
11 KiB
Raw Permalink Blame History

package kr.co.kihyun.beans.tosys.etc1;
import java.sql.*;
import kr.co.kihyun.beans.entity.SysAuth;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import kr.co.kihyun.db.*;
public class TotsysEtc extends CommonDBManager
{
private static final Logger LOG=LoggerFactory.getLogger(TotsysEtc.class);
private String sql="";
public int count = 0;
public String[] popupId=null;
public int tCount=0;
public int getCount(){
return tCount;
}
public String[] getPopupId(){
return popupId;
}
public void getPopupNotice()
{
ResultSet rs=null;
try
{
sql = "SELECT ID, COUNT(*) OVER() AS CNT FROM MOUMI_BOARD WHERE GROUP_ID='ID_SYSTEM_NOTICE' AND POPUP_YN='Y'";
rs = execQuery(sql);
if(rs != null){
int ii = 0;
while(rs.next()){
if(ii == 0){
count = rs.getInt("CNT");
tCount = count;
popupId = new String[count];
}
popupId[ii] = rs.getString("ID");
ii ++;
}
}else{
count = 0;
tCount = count;
}
}catch(SQLException sqlEx){
LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
throw new RuntimeException(sqlEx);
}catch (Exception ex){
throw new RuntimeException(ex);
}finally{
if(null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
execClose();
}
}
public int getDocCount(String id,String dept,SysAuth level)
{
int retCnt=0;
ResultSet rs = null;
try
{
String grade = "";
sql = "SELECT GRADE FROM MOUMI_DEPT WHERE ID = ? ";
rs=execQuery(sql, dept);
if(rs.next()){
grade=rs.getString(1);
}
close(rs);
pstmt.close();
if(level==SysAuth.SYSADM)
{
sql = "SELECT COUNT(*) ";
sql += "FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC ";
sql += "WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
sql += "AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
sql += "and DC.START_DATE < SYSDATE " ;
sql += "and DC.ACPT_LIMIT = AC.ID ";
sql += "AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
}
else if(level==SysAuth.SUB_SYSADM && (null == grade || "".equals(grade)))
{
sql="SELECT COUNT(*) ";
sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC ";
sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1) OR DC.ACC_AUTH=2) ";
sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
sql+="and DC.START_DATE < SYSDATE " ;
sql+="and DC.ACPT_LIMIT = AC.ID ";
sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
}
else if(level==SysAuth.TOTPER && (null == grade || "".equals(grade)))
{
sql="SELECT COUNT(*) ";
sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_DEPT DT, MOUMI_DEPT GR ";
sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
sql+="AND DC.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID ";
sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1) ";
sql+="OR (GR.ID = (SELECT DT.ORGAN FROM MOUMI_DEPT DT WHERE DT.ID = '"+dept+"') AND DC.ACC_AUTH=2)) ";
sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
sql+="and DC.START_DATE < SYSDATE " ;
sql+="and DC.ACPT_LIMIT = AC.ID ";
sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
} else
{
sql="SELECT COUNT(*) ";
sql+="FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_DEPT DT, MOUMI_DEPT GR ";
sql+="WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 ";
sql+="AND DC.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID ";
sql+="AND (DC.USER_ID='"+id+"' OR (DC.DEPT_ID='"+dept+"' AND DC.ACC_AUTH=1)) ";
// sql+="OR (GR.ID = (SELECT DT.ORGAN FROM MOUMI_DEPT DT WHERE DT.ID = '"+dept+"') AND DC.ACC_AUTH=2)) ";
sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
sql+="and DC.START_DATE < SYSDATE " ;
sql+="and DC.ACPT_LIMIT = AC.ID ";
sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
}
rs=execQuery(sql);
if(rs.next()){
retCnt = rs.getInt(1);
}
close(rs);
pstmt.close();
}catch (SQLException sqlEx){
LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
throw new RuntimeException(sqlEx);
}catch (Exception ex) {
throw new RuntimeException(ex);
}finally {
if(null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
execClose();
return retCnt;
}
}
public int getReportCount(String id, String dept)
{
int retCnt=0;
ResultSet rs = null;
try
{
String grade = "";
String organ = "";
String upper = "";
sql = "SELECT GRADE,ORGAN,UPPER_DEPT FROM MOUMI_DEPT WHERE ID = ? ";
rs = execQuery(sql, dept);
if(rs.next()){
grade = rs.getString(1);
organ = rs.getString(2);
upper = rs.getString(3);
}
close(rs);
pstmt.close();
if(grade==null || "".equals(grade))
{
sql="SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' ";
sql+="AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) ";
sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) ";
sql+="AND DC.START_DATE < SYSDATE ";
sql+="AND DC.ACPT_LIMIT = AC.ID ";
sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
//sql+="AND (RT.DEPT_ID='"+dept+"' OR RT.DEPT_ID='"+organ+"' OR RT.DEPT_ID='"+upper+"') ";
sql+="AND (((RT.DEPT_ID='"+dept+"' OR RT.DEPT_ID='"+organ+"' OR RT.DEPT_ID='"+upper+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) ";
}
else if(grade!=null || "".equals(grade))
{
sql="SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' ";
sql+="AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) ";
sql+="AND (DC.END_DATE IS NOT NULL AND DC.END_DATE>SYSDATE) ";
sql+="AND DC.START_DATE < SYSDATE ";
sql+="AND DC.ACPT_LIMIT = AC.ID ";
sql+="AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) ";
//sql+="AND RT.DEPT_ID='"+dept+"' ";
sql+="AND ((rt.dept_id in ('"+dept+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) ";
}
rs = execQuery(sql);
if(rs.next()){
retCnt = rs.getInt(1);
}
close(rs);
pstmt.close();
}
catch (SQLException sqlEx)
{
LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
throw new RuntimeException(sqlEx);
}
catch (Exception ex) {
throw new RuntimeException(ex);
}
finally {
if(null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
execClose();
return retCnt;
}
}
public String getRootName(String id)
{
String sql="";
String return_value = "";
ResultSet rs = null;
try
{
sql="select d1.id||'*'||d2.name||'-'||d1.name from moumi_dept d1,moumi_dept d2 ";
sql+="where d1.id in ('"+id+"') and d2.id=d1.organ";
rs = execQuery(sql);
if(rs.next()){
return_value = rs.getString(1);
}
}catch (SQLException e){
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}finally {
if(null != rs)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
execClose();
return return_value;
}
}
public String[] getGroupElement(String[] id)
{
int ii=0,jj=0;
boolean flag=false;
if(id==null || id.length==0) return null;
String tmp="";
String[] tmpArray=new String[id.length];
for(ii=0;ii<id.length;ii++)
{
if(id[ii]==null || id[ii].length()!=10) continue;
if(id[ii].substring(2).equals("00000000")||id[ii].substring(2).equals("00000001"))
{
tmpArray[jj++]=getRootName(id[ii]);
flag = true;
}
if(ii==0) tmp="'"+id[ii]+"'";
else tmp+=",'"+id[ii]+"'";
}
ii=jj;
ResultSet rs = null;
try
{
sql="select d1.id||'*'||d2.name||'-'||d3.name||'-'||d1.name from moumi_dept d1,moumi_dept d2,moumi_dept d3 ";
sql+="where d1.id in ("+tmp+") and d2.id=d1.organ and d3.id=d1.upper_dept";
rs=execQuery(sql);
while(rs.next())
{
tmpArray[ii++]=rs.getString(1);
}
}
catch (SQLException sqlEx)
{
LOG.error("\nSQLState-{}\nErr Code-{}\nmsg-{}\nsql-{}",
new Object[] { sqlEx.getSQLState(),sqlEx.getErrorCode(),sqlEx.getMessage(),sql });
throw new RuntimeException(sqlEx);
}
catch (Exception ex) { throw new RuntimeException(ex); }
finally
{
if(null != rs)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
execClose();
return tmpArray;
}
}
public static String[] getDeliToArr(String str, String deli)
{
String[] arr=null;
if(str==null || deli==null || deli.length()==0) return null;
String temp=str;
int start=0; int cnt=0;
while(temp.indexOf(deli)>=0)
{
String test=temp.substring(start,temp.indexOf(deli));
temp=temp.substring(temp.indexOf(deli)+1,temp.length());
cnt++;
}
arr=new String[cnt]; int ii = 0; temp=str; start=0;
while(temp.indexOf(deli)>=0)
{
arr[ii]=temp.substring(start,temp.indexOf(deli));
temp=temp.substring(temp.indexOf(deli)+deli.length(),temp.length());
ii++;
}
return arr;
}
}