/** * @(#)file PrepaymentCompletedSignDriver.java * @(#)author OK * @(#)version 1.0 * @(#)date 2014-09-25 * @(#)since JDK 1.6.21 * * Copyright (c) www.udapsoft.co.kr, Inc. * 대급지급신청서(준공) 처리 드라이브 * */ package kr.co.udapsoft.common.commonSign.driver; import java.io.OutputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.Types; import kr.co.hsnc.common.base.WAFLogger; import kr.co.hsnc.common.logger.Logger; import kr.co.hsnc.common.sql.Row; import kr.co.hsnc.common.sql.RowSet; import kr.co.hsnc.common.sql.RowSetImpl; import kr.co.hsnc.common.sql.WAFSQLException; import kr.co.hsnc.common.sql.persistent.Persistent; import kr.co.hsnc.common.sql.persistent.PersistentImpl; import kr.co.hsnc.common.sql.search.Search; import kr.co.hsnc.common.sql.search.SearchImpl; import kr.co.hsnc.common.sql.util.RowSetUtility; import kr.co.hsnc.common.util.StringUtil; import kr.co.hsnc.common.util.ValueObject; import kr.co.udapsoft.common.commonSign.CommonSignInterface; import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF7; import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF8; import weblogic.jdbc.vendor.oracle.OracleThinBlob; public class PrepaymentCompletedSignDriver implements CommonSignInterface { /** * */ public PrepaymentCompletedSignDriver() { super(); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doCallSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doCallSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("1"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doStartSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doStartSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("2"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doFirstFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFirstFinishSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("4"); /* 운영에 반영할때는 변경해야함*/ RowSet signUserInfo = getIFSignUser(signDoc.get("DOC_ID")); for ( int j = 0 ; j < signUserInfo.size() ; j ++ ) { ValueObject signUser = new ValueObject(); signUser.set("SIGN_SEQ", ""+(j+1)); signUser.set("DOC_ID", signDoc.get("DOC_ID")); signUser.set("APPROVAL_DATE", signUserInfo.getRow(j).get("PROCESSDATE")); signUser.set("APPROVAL_SABUN", signUserInfo.getRow(j).get("SABUN")); signUser.set("APPROVAL_USER", signUserInfo.getRow(j).get("APRMEMBERNAME")); signUser.set("A_POSITION", signUserInfo.getRow(j).get("APRMEMBERJOBTITLE")); signUser.set("SIGN_RESULT", signUserInfo.getRow(j).get("APRSTATE")); signUser.set("SIGN_TYPE", signDoc.get("SIGN_TYPE")); signUser.set("ACCT_TYPE", signDoc.get("ACCT_TYPE")); signUser.set("SLIP_DEPT", signDoc.get("KEY_COL2")); signUser.set("ACCSLIP_NO", ""); signUser.set("SLIP_DT", signDoc.get("KEY_COL4")); signUser.set("SLIP_NO", signDoc.get("KEY_COL5")); insertSign002t(connection, signUser); } updateSUB1040T(connection, signDoc); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFinishSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("4"); /* 운영에 반영할때는 변경해야함*/ RowSet signUserInfo = getIFSignUser(signDoc.get("DOC_ID")); for ( int j = 0 ; j < signUserInfo.size() ; j ++ ) { ValueObject signUser = new ValueObject(); signUser.set("SIGN_SEQ", ""+(j+1)); signUser.set("DOC_ID", signDoc.get("DOC_ID")); signUser.set("APPROVAL_DATE", signUserInfo.getRow(j).get("PROCESSDATE")); signUser.set("APPROVAL_SABUN", signUserInfo.getRow(j).get("SABUN")); signUser.set("APPROVAL_USER", signUserInfo.getRow(j).get("APRMEMBERNAME")); signUser.set("A_POSITION", signUserInfo.getRow(j).get("APRMEMBERJOBTITLE")); signUser.set("SIGN_RESULT", signUserInfo.getRow(j).get("APRSTATE")); signUser.set("SIGN_TYPE", signDoc.get("SIGN_TYPE")); signUser.set("ACCT_TYPE", signDoc.get("ACCT_TYPE")); signUser.set("SLIP_DEPT", signDoc.get("KEY_COL2")); signUser.set("ACCSLIP_NO", ""); signUser.set("SLIP_DT", signDoc.get("KEY_COL4")); signUser.set("SLIP_NO", signDoc.get("KEY_COL5")); insertSign002t(connection, signUser); } updateSUB1040T(connection, signDoc); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFirstRejectSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("5"); updateSUB1040T(connection, signDoc); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doRejectSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("6"); updateSUB1040T(connection, signDoc); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doCancelSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doCancelSign(Connection connection, ValueObject signDoc) throws Exception { } private RowSet getIFSignUser(String docId) throws Exception{ Search search = new SearchImpl(); search.setDSName("jdbc/gwsql"); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" SELECT APRSTATE \n"); sqlstr.append(" ,REPLACE(APRMEMBERID,SUBSTRING(APRMEMBERID,1,4),'') AS SABUN \n"); sqlstr.append(" ,APRMEMBERNAME \n"); sqlstr.append(" ,APRMEMBERJOBTITLE \n"); sqlstr.append(" ,APRMEMBERDEPTNAME \n"); sqlstr.append(" ,REPLACE(CONVERT(VARCHAR(10),PROCESSDATE,120),'-','') AS PROCESSDATE \n"); sqlstr.append(" FROM TBENDAPRLINEINFO \n"); sqlstr.append(" WHERE APRTYPE = 'A03001' \n"); sqlstr.append(" AND DOCID = '"+docId+"' \n"); sqlstr.append(" ORDER BY APRMEMBERSN \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); System.out.println(sqlstr.toString()); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } public void insertSign002t(Connection connection, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); sqlstr.append(" INSERT INTO SIGN002T \n"); sqlstr.append(" (SIGN_HIS_ID \n"); sqlstr.append(" ,SIGN_SEQ \n"); sqlstr.append(" ,DOC_ID \n"); sqlstr.append(" ,APPROVAL_DATE \n"); sqlstr.append(" ,APPROVAL_SABUN \n"); sqlstr.append(" ,APPROVAL_USER \n"); sqlstr.append(" ,A_POSITION \n"); sqlstr.append(" ,SIGN_RESULT \n"); sqlstr.append(" ,SLIP_DEPT \n"); sqlstr.append(" ,SLIP_DT \n"); sqlstr.append(" ,SLIP_NO \n"); sqlstr.append(" ,SIGN_TYPE \n"); sqlstr.append(" ,COMON_DCR \n"); sqlstr.append(" ) VALUES \n"); sqlstr.append(" ( \n"); sqlstr.append(" SIGN002T_S.NEXTVAL \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,TO_DATE(?,'YYYYMMDD') \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,? \n"); sqlstr.append(" ,1 \n"); sqlstr.append(" ,2 \n"); sqlstr.append(" ) \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(vo.get("SIGN_SEQ")); persistent.addParameter(vo.get("DOC_ID")); persistent.addParameter(vo.get("APPROVAL_DATE")); persistent.addParameter(vo.get("APPROVAL_SABUN")); persistent.addParameter(vo.get("APPROVAL_USER")); persistent.addParameter(vo.get("A_POSITION")); persistent.addParameter(vo.get("SIGN_RESULT")); persistent.addParameter(vo.get("SLIP_DEPT")); persistent.addParameter(vo.get("SLIP_DT")); persistent.addParameter(vo.get("SLIP_NO")); persistent.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> insertSign002t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public void updateSUB1040T(Connection connection, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(connection); Persistent persistent1 = new PersistentImpl(connection); StringBuffer sqlstr = null; StringBuffer sqlstr1 = null; try { sqlstr = new StringBuffer(); sqlstr1 = new StringBuffer(); //2018.05.18준공평가에 따라 상태값 변경 sqlstr.append(" UPDATE SUB1040T \n"); sqlstr.append(" SET STATUS_CD = ? \n"); sqlstr.append(" , UPD_DATE = SYSDATE \n"); sqlstr.append(" WHERE ACMP_ID = ? \n"); persistent.setStatement(sqlstr.toString()); int complete = 0; if ( vo.get("SIGN_DECI_CLASS").equals("24") || vo.get("SIGN_DECI_CLASS").equals("14") ) { persistent.addParameter("10"); // 완료 } else { persistent.addParameter("91"); // 반려 complete = 1; } persistent.addParameter(vo.get("KEY_COL1")); persistent.execute(); if(complete == 0){ //2018.05.18준공평가 완료이면 거래처 상태값 변경 sqlstr1.append(" MERGE \n"); sqlstr1.append(" INTO SUPP_INFO A \n"); sqlstr1.append(" USING (SELECT A1.CORP_ID,DECODE(A1.ITEM_STD_CD, 'C', '91', 'D', '99', 'X') STS \n"); sqlstr1.append(" FROM SUPP_APPR A1 \n"); sqlstr1.append(" ,SUB1040T B1 \n"); sqlstr1.append(" WHERE A1.CONT_ID = B1.SUB_CONT_ID \n"); sqlstr1.append(" AND B1.ACMP_ID IN (?) \n"); sqlstr1.append(" AND A1.DOC_CD IN (12,16) ) B \n"); sqlstr1.append(" ON (A.CORP_ID = B.CORP_ID \n"); sqlstr1.append(" AND A.JOB_DIV = 'O' \n"); sqlstr1.append(" AND B.STS IN ('91', '99') \n"); sqlstr1.append(" AND A.STATUS IN ('02', '03')) \n"); sqlstr1.append(" WHEN MATCHED THEN \n"); sqlstr1.append(" UPDATE \n"); sqlstr1.append(" SET A.STATUS = '91' \n"); sqlstr1.append(" ,A.CAN_DT = SYSDATE \n"); sqlstr1.append(" ,A.BAN_BID = DECODE(B.STS, '91', ADD_MONTHS(SYSDATE,12) , '99', TO_DATE('9999-12-31')) \n"); sqlstr1.append(" ,A.UPD_DATE = SYSDATE \n"); persistent1.setStatement(sqlstr1.toString()); persistent1.addParameter(vo.get("KEY_COL1")); persistent1.execute(); //대급지급 신청서(준공) 전표집계 prepaymentComplete1(connection, vo); } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updateSUB1040T() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public void prepaymentComplete1(Connection connection, ValueObject vo) throws Exception { Search search = new SearchImpl(); RowSet listRowSet = null; StringBuffer sql = new StringBuffer(); ValueObject selectResult = new ValueObject(); try { /* 2015-02-23 정승우 2017.5.19 준공기성은 외주팀. 회계팀 요청사항. * 증빙번호에 들어갈 사용자 정보 변경 * 준공시 계약담당자가 증빙번호에 들어가도록 변경 * */ sql.append("SELECT B.DEPT_CD \n"); sql.append(" ,A.CON_CHARGE_ID as ACCT_ID \n"); sql.append(" ,B.ACMP_ID \n"); sql.append("FROM SUB1000T A, \n"); sql.append(" SUB1040T B \n"); sql.append("WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sql.append("AND B.ACMP_ID = "+vo.get("KEY_COL1")+" \n"); search.setStatement(sql.toString()); listRowSet = search.execute(); selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0)); doJipge(connection, selectResult); RowSet result = getListRow(connection, selectResult); String acctId = selectResult.get("ACCT_ID"); for( int i = 0 ; i < result.size() ; i++ ) { Row row = result.getRow(i); //외주기성고 PDF작성 prcDoc020t(connection, row.get("TMPSLIP_ID"), acctId, selectResult.get("ACMP_ID"), row.get("END_YN")); } }catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> prepaymentMiddle() : " + e.getMessage()); //WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public RowSet getListRow(Connection connection, ValueObject vo) throws Exception { Persistent persistent_c = null; Search search = new SearchImpl(); RowSet listRowSet = null; StringBuffer sql = new StringBuffer(); try { persistent_c = new PersistentImpl(connection); sql.append(" SELECT MAIN.ACMP_ID -- 기성ID \n"); sql.append(" , '' CHK -- \n"); sql.append(" , MAIN.TMPSLIP_ID -- 외주기성전표 ID \n"); sql.append(" , TO_CHAR(MAIN.PROOFDATE, 'YYYY-MM-DD') PROOFDATE -- 거래일자 \n"); sql.append(" , MAIN.DEPT_CD -- 현장코드 \n"); sql.append(" , CONTRACT.DEPT_NAME -- 현장명 \n"); sql.append(" , CONTRACT.CON_DOC_NO -- 외주계약번호 \n"); sql.append(" , CONTRACT.BUILD_NAME -- 계약명 \n"); sql.append(" , MAIN.DCERTI_NO -- 사업자번호 \n"); sql.append(" , SUBVENDOR.CORP_KOR -- 사업자명 \n"); sql.append(" , SUBVENDOR.CORP_ID -- 협력업체_ID \n"); sql.append(" , MAIN.PREREQTFLG -- 선급/기성구분 \n"); sql.append(" , MAIN.ACMP_NO -- 기성차수 \n"); sql.append(" , MAIN.PROOF_CLASS -- 증빙구분 \n"); sql.append(" , MAIN.ORDACCDESC -- 적요 \n"); sql.append(" , MAIN.CON_CUR_CD -- 계약통화 \n"); sql.append(" , MAIN.SUPPLY_AMT -- 공급가 \n"); sql.append(" , MAIN.SURTAX_AMT -- 부가세 \n"); sql.append(" , MAIN.SUPPLY_AMT + MAIN.SURTAX_AMT REQ_AMT -- 합계 \n"); sql.append(" , CONTRACT.CON_CHARGE_ID -- 담당자 ID \n"); sql.append(" , CONTRACT.CON_CHARGE_NAME -- 담당자 명 \n"); sql.append(" , MAIN.DEALR_CD -- 거래처코드 \n"); sql.append(" , MAIN.PAYITEM -- PAY ITEM \n"); sql.append(" , MAIN.COSTCODE -- COST CODE \n"); sql.append(" , MAIN.COSTTYPE -- COST TYPE \n"); sql.append(" , MAIN.ACC_CLASS -- 계정구분 \n"); sql.append(" , MAIN.PAY_GROUP -- PAY_GROUP \n"); sql.append(" , MAIN.PAYMENT_TERMS -- PAYMENT_TERMS \n"); sql.append(" , MAIN.TMPSLIP_DEPT -- 작성부서 \n"); sql.append(" , MAIN.TMPSLIP_DT -- 작성일자 \n"); sql.append(" , MAIN.TMPSLIP_NO -- 작성번호 \n"); sql.append(" , MAIN.TMPSLIP_YN -- 전표생성유무 \n"); sql.append(" , MAIN.SLIP_YN -- 결의서작성유무 \n"); sql.append(" , MAIN.PREPAY_ID -- 선급ID \n"); sql.append(" , ACMP.CON_CORP_ID -- 계약업체 ID \n"); sql.append(" , ACMP.ACMP_YYYYMM -- 기성년월 \n"); sql.append(" , ACMP.ACMP_NO CHASU -- 차수 \n"); sql.append(" , ACMP.TAX_METHOD_CD \n"); sql.append(" , CONTRACT.SUB_CONT_ID -- 외주게약서 ID \n"); sql.append(" , CASE \n");//2017.08.03 잔여선급금 0일때 준공. sql.append(" WHEN ACMP.TOT_FUND_AMT >= ACMP.CON_SUPPLY_AMT AND (ACMP.TOT_PAY_DEDUCT_RATE = 100 OR ACMP.TOT_PAY_DEDUCT_RATE = 0) THEN 'J' \n"); sql.append(" ELSE 'K' \n"); sql.append(" END END_YN \n"); sql.append(" \n"); sql.append(" FROM SUB1050T MAIN -- << 집계 >> \n"); sql.append(" LEFT OUTER JOIN SUB1040T ACMP -- << 기성내역 >> \n"); sql.append(" ON MAIN.ACMP_ID = ACMP.ACMP_ID \n"); sql.append(" LEFT OUTER JOIN SUB1020T SUBVENDOR -- << 계약업체 >> \n"); sql.append(" ON ACMP.CON_CORP_ID = SUBVENDOR.CON_CORP_ID \n"); sql.append(" LEFT OUTER JOIN SUB2000T CONTRACT -- << 계약서 >> \n"); sql.append(" ON ACMP.SUB_CONT_ID = CONTRACT.SUB_CONT_ID \n"); sql.append(" AND ACMP.MOD_NO = CONTRACT.MOD_NO \n"); sql.append(" WHERE PREREQTFLG IN ('ACMP', 'PREPAY_DED') -- PREPAY:선급, ACMP:기성, PREPAY_DED: 선급금공제 \n"); sql.append(" AND MAIN.ACMP_ID = "+vo.get("ACMP_ID")+" \n"); sql.append(" ORDER BY PROOFDATE desc, MAIN.ACMP_ID, DEPT_NAME, CON_DOC_NO, CORP_KOR, MAIN.PREREQTFLG \n"); System.out.println(sql.toString()); //search.setStatement(sql.toString()); //listRowSet = search.execute(); //selectResult = RowSetUtility.getValueObject(search.execute().getRow(0)); persistent_c.setStatement(sql.toString()); listRowSet = persistent_c.query(); //selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0)); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( listRowSet == null ) listRowSet = new RowSetImpl(); } return listRowSet; } private void doJipge(Connection connection, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(connection); Persistent persistent2 = new PersistentImpl(connection); Persistent persistent3 = new PersistentImpl(connection); Persistent persistent4 = new PersistentImpl(connection); Persistent persistent_u = new PersistentImpl(connection); StringBuffer sql = new StringBuffer(); StringBuffer sql2 = new StringBuffer(); StringBuffer sql3 = new StringBuffer(); StringBuffer sql4 = new StringBuffer(); StringBuffer sql_u = new StringBuffer(); prepareSql(sql); prepareSql2(sql2); prepareSql3(sql3); prepareSql4(sql4); updateSql(sql_u); persistent.setStatement(sql.toString()); persistent2.setStatement(sql2.toString()); persistent3.setStatement(sql3.toString()); persistent4.setStatement(sql4.toString()); persistent_u.setStatement(sql_u.toString()); try { //ValueObject user = storage.getUser(); RowSet rs = getListRowSet(connection, vo); if(rs.size() > 0){ for(int i = 0; rs.size() > i; i++){ // 기성집계 과세 persistent.addParameter(vo.get("ACCT_ID")); persistent.addParameter(rs.getRow(i).get("ACMP_ID")); persistent.execute(); persistent.clearParameters(); // 선급금 공제 집계 과세 (선급금공제금액이 있는 놈만 집계) persistent2.addParameter(vo.get("ACCT_ID")); persistent2.addParameter(rs.getRow(i).get("ACMP_ID")); persistent2.execute(); persistent2.clearParameters(); // 기성집계 면세 persistent3.addParameter(vo.get("ACCT_ID")); persistent3.addParameter(rs.getRow(i).get("ACMP_ID")); persistent3.execute(); persistent3.clearParameters(); // 선급금 공제 집계 면세 (선급금공제금액이 있는 놈만 집계) persistent4.addParameter(vo.get("ACCT_ID")); persistent4.addParameter(rs.getRow(i).get("ACMP_ID")); persistent4.execute(); persistent4.clearParameters(); // 전표집계유무 상태값 변경 persistent_u.addParameter(rs.getRow(i).get("ACMP_ID")); persistent_u.execute(); persistent_u.clearParameters(); } } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> doJipge() : " + e.getMessage()); WAFLogger.error(e); throw e; } } //면세 private void prepareSql(StringBuffer sql) { sql.append(" INSERT INTO SUB1050T(TMPSLIP_ID, PROOFDATE, DEPT_CD, PREREQTFLG, ACMP_NO, PROOF_CLASS, ORDACCDESC, SUPPLY_AMT, SURTAX_AMT, DEALR_CD, DCERTI_NO, TAX_DATE, ELECTAX_YN, PAYITEM, COSTCODE, COSTTYPE, ACC_CLASS, PAY_GROUP, PAYMENT_TERMS, TMPSLIP_DEPT, TMPSLIP_DT, TMPSLIP_NO, TMPSLIP_YN, SLIP_YN, CON_CUR_CD, ACMP_ID, PREPAY_ID, CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, CORP_ID, TAX_GB) \n"); sql.append(" SELECT SUB1050T_S.NEXTVAL -- ID \n"); sql.append(" , A.TAX_DY -- 기성일자 \n"); sql.append(" , A.DEPT_CD -- 현장코드 \n"); sql.append(" , 'ACMP' -- 선급기성구분 (ACMP= 기성) \n"); sql.append(" , A.ACMP_NO -- 기성차수 \n"); sql.append(" , DECODE(B.PROOF_CD,'21','26', B.PROOF_CD) -- 증빙구분 \n"); sql.append(" , A.TAX_DY || ' 외주기성전표 - ' || A.ACMP_NO \n"); sql.append(" || ' (' || BUILD_NAME || ') ' -- 적요 \n"); sql.append(" , A.REQU_SUPPLY_NAMT -- 면세 \n"); sql.append(" , 0 -- 면세보증금 \n"); sql.append(" , F_GET_DEALR_CD_EBID(C.CORP_ID) AS DEALR_CD -- 거래처코드 \n"); sql.append(" , F_GET_DEALR_NO_EBID(C.CORP_ID) AS DCERTI_NO -- 사업자번호 \n"); sql.append(" , TO_DATE(A.TAX_DY, 'YYYY-MM-DD') -- 세금계산서일자 \n"); sql.append(" , 'Y' -- 전자계약여부 \n"); sql.append(" , B.PAY_ITEM -- PAY_ITEM \n"); sql.append(" , B.COST_CODE -- COST_CODE \n"); sql.append(" , B.COST_TYPE -- COST_TYPE \n"); sql.append(" , B.ACCT_GB -- 계정구분 (=분개유형 ) \n"); sql.append(" , B.PAY_GROUP -- PAY_GROUP \n"); sql.append(" , B.PAYMENT_TERMS -- PAYMENT_TERMS \n"); sql.append(" , NULL -- 작성부서 \n"); sql.append(" , NULL -- 작성일자 \n"); sql.append(" , NULL -- 작성번호 \n"); sql.append(" , 'N' -- 전표생성유무 \n"); sql.append(" , 'N' -- 결의서작성유무 \n"); sql.append(" , B.CON_CUR_CD -- 계약통화 \n"); sql.append(" , A.ACMP_ID -- 기성ID \n"); sql.append(" , NULL -- 선급ID \n"); sql.append(" , ? -- 등록자 \n"); sql.append(" , SYSDATE -- 등록일시 \n"); sql.append(" , NULL -- 수정자 \n"); sql.append(" , NULL -- 수정일시 \n"); sql.append(" , C.CORP_ID -- 협력업체ID \n"); sql.append(" , 'N' -- 면과세 구분 \n"); sql.append(" FROM SUB1040T A -- << 기성내역 >> \n"); sql.append(" ,SUB2000T B -- << 계약서 >> \n"); sql.append(" ,SUB1020T C -- << 업체 >> \n"); sql.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sql.append(" AND A.MOD_NO = B.MOD_NO \n"); sql.append(" AND A.SUB_CONT_ID = C.SUB_CONT_ID \n"); sql.append(" AND A.ACMP_ID = ? \n"); sql.append(" AND A.REQU_SUPPLY_NAMT > 0 -- 면세금액이 있음 \n"); sql.append(" AND A.STATUS_CD = '10' -- 결제완료된 내역 \n"); System.out.println(sql.toString()); } private void prepareSql2(StringBuffer sql) { sql.append(" INSERT INTO SUB1050T(TMPSLIP_ID, PROOFDATE, DEPT_CD, PREREQTFLG, ACMP_NO, PROOF_CLASS, ORDACCDESC, SUPPLY_AMT, SURTAX_AMT, DEALR_CD, DCERTI_NO, TAX_DATE, ELECTAX_YN, PAYITEM, COSTCODE, COSTTYPE, ACC_CLASS, PAY_GROUP, PAYMENT_TERMS, TMPSLIP_DEPT, TMPSLIP_DT, TMPSLIP_NO, TMPSLIP_YN, SLIP_YN, CON_CUR_CD, ACMP_ID, PREPAY_ID, CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, CORP_ID, TAX_GB) \n"); sql.append(" SELECT SUB1050T_S.NEXTVAL -- ID \n"); sql.append(" , A.TAX_DY -- 기성일자 \n"); sql.append(" , A.DEPT_CD -- 현장코드 \n"); sql.append(" , 'PREPAY_DED' -- 선급기성구분 (ACMP= 기성) \n"); sql.append(" , A.ACMP_NO -- 기성차수 \n"); sql.append(" ,'Z99' -- 증빙구분 \n"); sql.append(" , A.TAX_DY || ' 외주선급공제전표 - ' || A.ACMP_NO \n"); sql.append(" || ' (' || BUILD_NAME || ') ' -- 적요 \n"); sql.append(" , A.PAY_DEDUCT_NAMT -- 선급공제금_면세 \n"); sql.append(" , 0 -- 부가세 \n"); sql.append(" , F_GET_DEALR_CD_EBID(C.CORP_ID) AS DEALR_CD -- 거래처코드 \n"); sql.append(" , F_GET_DEALR_NO_EBID(C.CORP_ID) AS DCERTI_NO -- 사업자번호 \n"); sql.append(" , TO_DATE(A.TAX_DY, 'YYYY-MM-DD') -- 세금계산서일자 \n"); sql.append(" , 'Y' -- 전자계약여부 \n"); sql.append(" , B.PAY_ITEM -- PAY_ITEM \n"); sql.append(" , B.COST_CODE -- COST_CODE \n"); sql.append(" , B.COST_TYPE -- COST_TYPE \n"); sql.append(" , B.ACCT_GB -- 계정구분 (=분개유형 ) \n"); sql.append(" , CASE E.AC_UNIT_CD WHEN '10' THEN '22' \n"); sql.append(" ELSE '21' \n"); sql.append(" END -- PAY_GROUP \n"); sql.append(" , B.PAYMENT_TERMS -- PAYMENT_TERMS \n"); sql.append(" , NULL -- 작성부서 \n"); sql.append(" , NULL -- 작성일자 \n"); sql.append(" , NULL -- 작성번호 \n"); sql.append(" , 'N' -- 전표생성유무 \n"); sql.append(" , 'N' -- 결의서작성유무 \n"); sql.append(" , B.CON_CUR_CD -- 계약통화 \n"); sql.append(" , A.ACMP_ID -- 기성ID \n"); sql.append(" , NULL -- 선급ID \n"); sql.append(" , ? -- 등록자 \n"); sql.append(" , SYSDATE -- 등록일시 \n"); sql.append(" , NULL -- 수정자 \n"); sql.append(" , NULL -- 수정일시 \n"); sql.append(" , C.CORP_ID -- 협력업체ID \n"); sql.append(" , 'N' -- 면과세구분 \n"); sql.append(" FROM SUB1040T A -- << 기성내역 >> \n"); sql.append(" ,SUB2000T B -- << 계약서 >> \n"); sql.append(" ,SUB1020T C -- << 업체 >> \n"); sql.append(" ,ETEC_ERPIF.ACZ10100 E -- << 사업부 >> \n"); sql.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sql.append(" AND A.MOD_NO = B.MOD_NO \n"); sql.append(" AND A.SUB_CONT_ID = C.SUB_CONT_ID \n"); sql.append(" AND A.ACMP_ID = ? \n"); sql.append(" AND A.PAY_DEDUCT_NAMT > 0 -- 선급공제액_면세금액이 있음 \n"); sql.append(" AND A.DEPT_CD = E.DEPT_CD \n"); sql.append(" AND A.STATUS_CD = '10' -- 결제완료된 내역 \n"); System.out.println(sql.toString()); } private void prepareSql3(StringBuffer sql) { sql.append(" INSERT INTO SUB1050T(TMPSLIP_ID, PROOFDATE, DEPT_CD, PREREQTFLG, ACMP_NO, PROOF_CLASS, ORDACCDESC, SUPPLY_AMT, SURTAX_AMT, DEALR_CD, DCERTI_NO, TAX_DATE, ELECTAX_YN, PAYITEM, COSTCODE, COSTTYPE, ACC_CLASS, PAY_GROUP, PAYMENT_TERMS, TMPSLIP_DEPT, TMPSLIP_DT, TMPSLIP_NO, TMPSLIP_YN, SLIP_YN, CON_CUR_CD, ACMP_ID, PREPAY_ID, CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, CORP_ID, TAX_GB) \n"); sql.append(" SELECT SUB1050T_S.NEXTVAL -- ID \n"); sql.append(" , A.TAX_DY -- 기성일자 \n"); sql.append(" , A.DEPT_CD -- 현장코드 \n"); sql.append(" , 'ACMP' -- 선급기성구분 (ACMP= 기성) \n"); sql.append(" , A.ACMP_NO -- 기성차수 \n"); sql.append(" , B.PROOF_CD -- 증빙구분 \n"); sql.append(" , A.TAX_DY || ' 외주기성전표 - ' || A.ACMP_NO \n"); sql.append(" || ' (' || BUILD_NAME || ') ' -- 적요 \n"); sql.append(" , A.REQU_SUPPLY_TAMT -- 과세 \n"); sql.append(" , A.REQU_SURTAX_AMT -- 과세보증금 \n"); sql.append(" , F_GET_DEALR_CD_EBID(C.CORP_ID) AS DEALR_CD -- 거래처코드 \n"); sql.append(" , F_GET_DEALR_NO_EBID(C.CORP_ID) AS DCERTI_NO -- 사업자번호 \n"); sql.append(" , TO_DATE(A.TAX_DY, 'YYYY-MM-DD') -- 세금계산서일자 \n"); sql.append(" , 'Y' -- 전자계약여부 \n"); sql.append(" , B.PAY_ITEM -- PAY_ITEM \n"); sql.append(" , B.COST_CODE -- COST_CODE \n"); sql.append(" , B.COST_TYPE -- COST_TYPE \n"); sql.append(" , B.ACCT_GB -- 계정구분 (=분개유형 ) \n"); sql.append(" , B.PAY_GROUP -- PAY_GROUP \n"); sql.append(" , B.PAYMENT_TERMS -- PAYMENT_TERMS \n"); sql.append(" , NULL -- 작성부서 \n"); sql.append(" , NULL -- 작성일자 \n"); sql.append(" , NULL -- 작성번호 \n"); sql.append(" , 'N' -- 전표생성유무 \n"); sql.append(" , 'N' -- 결의서작성유무 \n"); sql.append(" , B.CON_CUR_CD -- 계약통화 \n"); sql.append(" , A.ACMP_ID -- 기성ID \n"); sql.append(" , NULL -- 선급ID \n"); sql.append(" , ? -- 등록자 \n"); sql.append(" , SYSDATE -- 등록일시 \n"); sql.append(" , NULL -- 수정자 \n"); sql.append(" , NULL -- 수정일시 \n"); sql.append(" , C.CORP_ID -- 협력업체ID \n"); sql.append(" , 'T' -- 면과세구분 \n"); sql.append(" FROM SUB1040T A -- << 기성내역 >> \n"); sql.append(" ,SUB2000T B -- << 계약서 >> \n"); sql.append(" ,SUB1020T C -- << 업체 >> \n"); sql.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sql.append(" AND A.MOD_NO = B.MOD_NO \n"); sql.append(" AND A.SUB_CONT_ID = C.SUB_CONT_ID \n"); sql.append(" AND A.ACMP_ID = ? \n"); sql.append(" AND A.REQU_SUPPLY_TAMT > 0 -- 과세금액이 있음 \n"); sql.append(" AND A.STATUS_CD = '10' -- 결제완료된 내역 \n"); System.out.println(sql.toString()); } private void prepareSql4(StringBuffer sql) { sql.append(" INSERT INTO SUB1050T(TMPSLIP_ID, PROOFDATE, DEPT_CD, PREREQTFLG, ACMP_NO, PROOF_CLASS, ORDACCDESC, SUPPLY_AMT, SURTAX_AMT, DEALR_CD, DCERTI_NO, TAX_DATE, ELECTAX_YN, PAYITEM, COSTCODE, COSTTYPE, ACC_CLASS, PAY_GROUP, PAYMENT_TERMS, TMPSLIP_DEPT, TMPSLIP_DT, TMPSLIP_NO, TMPSLIP_YN, SLIP_YN, CON_CUR_CD, ACMP_ID, PREPAY_ID, CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, CORP_ID, TAX_GB) \n"); sql.append(" SELECT SUB1050T_S.NEXTVAL -- ID \n"); sql.append(" , A.TAX_DY -- 기성일자 \n"); sql.append(" , A.DEPT_CD -- 현장코드 \n"); sql.append(" , 'PREPAY_DED' -- 선급기성구분 (ACMP= 기성) \n"); sql.append(" , A.ACMP_NO -- 기성차수 \n"); sql.append(" ,'Z99' -- 증빙구분 \n"); sql.append(" , A.TAX_DY || ' 외주선급공제전표 - ' || A.ACMP_NO \n"); sql.append(" || ' (' || BUILD_NAME || ') ' -- 적요 \n"); sql.append(" , A.PAY_DEDUCT_TAMT -- 선급공제금_과세 \n"); sql.append(" , 0 -- 부가세 \n"); sql.append(" , F_GET_DEALR_CD_EBID(C.CORP_ID) AS DEALR_CD -- 거래처코드 \n"); sql.append(" , F_GET_DEALR_NO_EBID(C.CORP_ID) -- 사업자번호 \n"); sql.append(" , TO_DATE(A.TAX_DY, 'YYYY-MM-DD') -- 세금계산서일자 \n"); sql.append(" , 'Y' -- 전자계약여부 \n"); sql.append(" , B.PAY_ITEM -- PAY_ITEM \n"); sql.append(" , B.COST_CODE -- COST_CODE \n"); sql.append(" , B.COST_TYPE -- COST_TYPE \n"); sql.append(" , B.ACCT_GB -- 계정구분 (=분개유형 ) \n"); sql.append(" , CASE E.AC_UNIT_CD WHEN '10' THEN '22' \n"); sql.append(" ELSE '21' \n"); sql.append(" END -- PAY_GROUP \n"); sql.append(" , B.PAYMENT_TERMS -- PAYMENT_TERMS \n"); sql.append(" , NULL -- 작성부서 \n"); sql.append(" , NULL -- 작성일자 \n"); sql.append(" , NULL -- 작성번호 \n"); sql.append(" , 'N' -- 전표생성유무 \n"); sql.append(" , 'N' -- 결의서작성유무 \n"); sql.append(" , B.CON_CUR_CD -- 계약통화 \n"); sql.append(" , A.ACMP_ID -- 기성ID \n"); sql.append(" , NULL -- 선급ID \n"); sql.append(" , ? -- 등록자 \n"); sql.append(" , SYSDATE -- 등록일시 \n"); sql.append(" , NULL -- 수정자 \n"); sql.append(" , NULL -- 수정일시 \n"); sql.append(" , C.CORP_ID -- 협력업체ID \n"); sql.append(" , 'T' -- 면과세구분 \n"); sql.append(" FROM SUB1040T A -- << 기성내역 >> \n"); sql.append(" ,SUB2000T B -- << 계약서 >> \n"); sql.append(" ,SUB1020T C -- << 업체 >> \n"); sql.append(" ,ETEC_ERPIF.ACZ10100 E -- << 사업부 >> \n"); sql.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sql.append(" AND A.MOD_NO = B.MOD_NO \n"); sql.append(" AND A.SUB_CONT_ID = C.SUB_CONT_ID \n"); sql.append(" AND A.ACMP_ID = ? \n"); sql.append(" AND A.PAY_DEDUCT_TAMT > 0 -- 선급공제액_과세금액이 있음 \n"); sql.append(" AND A.DEPT_CD = E.DEPT_CD \n"); sql.append(" AND A.STATUS_CD = '10' -- 결제완료된 내역 \n"); System.out.println(sql.toString()); } private void updateSql(StringBuffer sql){ sql.append(" UPDATE SUB1040T SET TRANS_CD = 'Y' \n"); sql.append(" WHERE ACMP_ID = ? \n"); sql.append(" AND STATUS_CD = '10' \n"); } private RowSet getListRowSet(Connection connection, ValueObject vo) throws Exception { RowSet listRowSet = null; StringBuffer sql = new StringBuffer(); Persistent persistent_c = null; ValueObject row = null; try { persistent_c = new PersistentImpl(connection); sql.append(" SELECT MAIN.ACMP_ID -- 기성아이디 \n"); sql.append(" FROM SUB1040T MAIN -- << 기성내역 >> \n"); sql.append(" WHERE MAIN.DEPT_CD = ? \n"); sql.append(" AND MAIN.TRANS_CD = 'N' \n"); sql.append(" AND MAIN.STATUS_CD = '10' \n"); sql.append(" AND MAIN.ACMP_ID = ? \n"); System.out.println(sql.toString()); persistent_c.setStatement(sql.toString()); persistent_c.addParameter(vo.get("dept_cd")); persistent_c.addParameter(vo.get("ACMP_ID")); listRowSet = persistent_c.query(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage()); WAFLogger.error("SQL : " + sql.toString()); WAFLogger.error(e); throw e; } finally { if( listRowSet == null ) listRowSet = new RowSetImpl(); } return listRowSet; } private void prcDoc020t(Connection con, String tmpslipID, String userID, String acmp_id, String endYn) throws Exception { String strResult = null; String strFileName = null; String message = ""; String SPStateMent = ""; CallableStatement cs = null; String arrDocNm [] = {"외주기성고 계산서"}; String arrProofGu [] = {"12"}; //WAFLogger.debug("\n\t #################### params = [\n"+ params +"\n"); try { // 증빙정보 생성 프로시저 // - 외주 기성고 계산서만 새로 생성해야만 한다. SPStateMent = "{call SAC_DOCUMENTATIVE.prc_subinvoicebiz2(?, ?, ?, ?, ?)}"; cs = con.prepareCall(SPStateMent); // 라인당, 기성고계산서 한라인만 생성. for(int iCount=0; iCount" + SPStateMent+ " : \n" + e.getMessage()); //WAFLogger.error(e); message = this.getClass().getName() + "." + SPStateMent + " : \n" + e.getMessage(); message = StringUtil.replace(message, "\"", ""); //storage.setDetailMessage(message); throw e; } finally{ try { if (cs != null) cs.close(); } catch (Exception ex_close) { WAFLogger.error(ex_close); } } } public void createSMbill(Connection con, String prp_req_id, String userID, String payreqId, String batchId) throws Exception { String message = ""; String SPStateMent = ""; CallableStatement cs = null; try { SPStateMent = "{call PUR_BILL_CREATE5(?,?,?,?)}"; //준공 cs = con.prepareCall(SPStateMent); cs.setString(1, prp_req_id); // 가불금 ID cs.setString(2, userID); // USER ID cs.setString(3, payreqId); cs.setString(4, batchId); cs.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() +" -->" + SPStateMent+ " : \n" + e.getMessage()); WAFLogger.error(e); message = this.getClass().getName() + "." + SPStateMent + " : \n" + e.getMessage(); message = StringUtil.replace(message, "\"", ""); //storage.setDetailMessage(message); throw e; } finally{ try { if (cs != null) cs.close(); } catch (Exception ex_close) { WAFLogger.error(ex_close); } } } public String getBatchId() throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; ValueObject row = null; String resultId = ""; try{ sqlstr = " SELECT TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS') AS BATCH_ID FROM DUAL \n"; System.out.println(sqlstr); search.setStatement(sqlstr); rowSet = search.execute(); row = RowSetUtility.getValueObject(rowSet.getRow(0)); resultId = row.get("BATCH_ID"); }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getBatchId() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return resultId; } }