/** * @(#)file PrepaymentMiddleSignDriver.java * @(#)author OK * @(#)version 1.0 * @(#)date 2014-09-15 * @(#)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.config.WAFConfig; 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.SACDOCreatePDF6; import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU; import weblogic.jdbc.vendor.oracle.OracleThinBlob; public class PrepaymentSignDriver implements CommonSignInterface { /** * */ public PrepaymentSignDriver() { 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("3"); /* 운영에 반영할때는 변경해야함*/ RowSet signUserInfo = getIFSignUser(signDoc.get("DOC_ID")); // 2020.02.17 결재정보가 없는 경우 예외처리 if(signUserInfo.size() == 0){ throw new Exception("결재정보가 없습니다."); } 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_COL3")); //signUser.set("ACC_SLIP_NO", acctSlipNo); signUser.set("ACCSLIP_NO", ""); signUser.set("SLIP_DT", signDoc.get("KEY_COL4")); signUser.set("SLIP_NO", signDoc.get("KEY_COL5")); insertSign002t(connection, signUser); } updateSUB1030T(connection, signDoc, signUserInfo); } /* * (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"); } /* * (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 { updateSUB1030T(connection, signDoc, new RowSetImpl()); } /* * (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 { //u pdateSUB1040T(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()); return rowSet; }catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } } 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(" ,ACC_SLIP_NO \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"); 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.addParameter(vo.get("ACC_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 updateSUB1030T(Connection connection, ValueObject vo, RowSet signUserInfo) 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(); String status = ""; sqlstr1.append(" SELECT REQ_DIV_CD \n"); sqlstr1.append(" FROM SUB1030T \n"); sqlstr1.append(" WHERE PREPAY_ID = ? \n"); persistent1.setStatement(sqlstr1.toString()); persistent1.addParameter(vo.get("KEY_COL2")); String div = RowSetUtility.getValueObject(persistent1.query().getRow(0)).get("REQ_DIV_CD"); sqlstr.append(" UPDATE SUB1030T \n"); sqlstr.append(" SET STATUS_CD = ? \n"); sqlstr.append(" , UPD_DATE = SYSDATE \n"); sqlstr.append(" WHERE PREPAY_ID IN (?) \n"); persistent.setStatement(sqlstr.toString()); int complete = 0; if ( vo.get("SIGN_DECI_CLASS").equals("14")) { status = "10"; // 완료 if("3".equals(div)){ status = "21"; //구매 포기완료 } } else { //persistent.addParameter("91"); // 반려 status = "91"; complete = 1; } persistent.addParameter(status); persistent.addParameter(vo.get("KEY_COL2")); persistent.execute(); if(!"3".equals(div)){ //전표집계처리 if(complete == 0){ //외주 선급금 전표집계 //선급금포기각서 첨부파일 복사처리 //doSendCCSFile(connection, "SUPP_PREPAYMENTS_GIVEUP", vo.get("KEY_COL2")); // //doSendCCSFile(connection, "SUPP_PREPAYMENTS_BILLING", vo.get("KEY_COL2")); // //doSendCCSFile(connection, "SUPP_TAX_BILL", vo.get("KEY_COL2")); // //doSendCCSFile(connection, "INVOICE", vo.get("KEY_COL2")); prepaymentJipge(connection, vo, signUserInfo); } } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updateSUB1030T() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public void prepaymentJipge(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception { Search search = new SearchImpl(); RowSet listRowSet = null; StringBuffer sql = new StringBuffer(); ValueObject selectResult = new ValueObject(); try { sql.append(" SELECT A.DEPT_CD \n"); sql.append(" ,B.SUB_CONT_ID \n"); sql.append(" ,A.SUB_CONT_MOD_ID \n"); sql.append(" ,A.ERP_NO_CONT \n"); sql.append(" ,A.ERP_SQ_CONT \n"); sql.append(" ,B.PREPAY_ID \n"); sql.append(" ,TO_CHAR(TO_DATE(B.TAX_DY,'YYYYMMDD'), 'YYYY-MM') AS P_ENTER_DT \n"); sql.append(" ,(SELECT A.USER_SABUN FROM PUB0120T A WHERE ACCT_ID = C.CRE_BY) AS USER_SABUN \n"); sql.append(" ,(SELECT A.USER_NAME FROM PUB0120T A WHERE ACCT_ID = C.CRE_BY) AS USER_NAME \n"); sql.append(" ,C.CRE_BY AS ACCT_ID \n"); sql.append(" ,C.CRE_DATE AS REQ_DATE \n"); sql.append(" ,TO_CHAR(SYSDATE,'YYYYMMDD') AS APPLY_DATE \n"); sql.append(" ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT \n"); sql.append(" ,B.TAX_METHOD_CD AS TAX_METHOD_CD \n"); sql.append(" ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY \n"); sql.append(" ,NVL(D.ERP_SITE_CD,A.DEPT_CD) AS CD_SITE \n"); sql.append(" ,'01' AS CD_CM00215 \n"); sql.append(" ,A.ERP_NO_CONT AS NO_CONT \n"); sql.append(" ,A.ERP_SQ_CONT AS SQ_CONT \n"); sql.append(" ,TO_CHAR(B.PREPAY_DATE,'YYYYMMDD') AS DT_PREPAY \n"); sql.append(" ,FLOOR(B.REQ_PREPAY_SUPPLY_TAMT * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS AMT_PREPAY \n"); sql.append(" ,FLOOR(DECODE(A.PROOF_CD,'Z99',0,B.REQ_PREPAY_SURTAX_AMT) * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS VAT_PREPAY \n"); sql.append(" ,FLOOR(B.REQ_PREPAY_SUPPLY_NAMT * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS FAMT_PREPAY \n"); sql.append(" ,(SELECT CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911' AND DETAILCD = B.CON_CUR_CD) AS CURR_SOUR \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS UNT_CURR \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,B.REQ_PREPAY_SUPPLY_TAMT) AS AMT_CURR \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,DECODE(A.PROOF_CD,'Z99',0,B.REQ_PREPAY_SURTAX_AMT)) AS VAMT_CURR \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,B.REQ_PREPAY_SUPPLY_NAMT) AS FAMT_CURR \n"); sql.append(" ,'Y' AS YN_BAN \n"); sql.append(" ,NVL(E.CHG_CODE10,B.CON_CUR_CD) AS CD_EXCH \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS RT_EXCH \n"); sql.append(" ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_DATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS DT_EXCH \n"); sql.append(" ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS \n"); //2019.07.29 ERP 자동분개를 위한 계정코드 sql.append(" ,CASE WHEN A.CON_CUR_CD = 'KRW' THEN NVL(F.CHG_CODE10, '') \n"); sql.append(" ELSE NVL(F.CHG_CODE11, '') \n"); sql.append(" END AS CD_COST_TRADE \n"); sql.append(" FROM SUB2000T A \n"); sql.append(" ,SUB1030T B \n"); sql.append(" ,( \n"); sql.append(" SELECT MAX(SIGN_INTERFACE_ID) \n"); sql.append(" ,MAX(KEY_COL2) AS KEY_COL2 \n"); sql.append(" ,MAX(CRE_BY) AS CRE_BY \n"); sql.append(" ,MAX(TO_CHAR(CRE_DATE,'YYYYMMDD')) AS CRE_DATE \n"); sql.append(" FROM SIGN001T \n"); sql.append(" WHERE KEY_COL2 = '"+vo.get("KEY_COL2")+"' \n"); sql.append(" AND SIGN_CLASS_CD = 'ebid_oprpyD' \n"); sql.append(" GROUP BY KEY_COL2 \n"); sql.append(" ) C \n"); sql.append(" ,ETEC_ERPIF.SITE_MAPPING D \n"); sql.append(" ,(SELECT DETAILCD, CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911') E \n"); sql.append(",(SELECT DETAILCD, CHG_CODE10, CHG_CODE11, CHG_CODE12, CHG_CODE13 FROM EBID_COM911T WHERE BASECD = 'SUB_ACCT_GB' ) F \n"); sql.append(" WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID \n"); sql.append(" AND B.PREPAY_ID = C.KEY_COL2 \n"); sql.append(" AND A.DEPT_CD = D.EBID_SITE_CD(+) \n"); sql.append(" AND B.CON_CUR_CD = E.DETAILCD(+) \n"); sql.append(" AND A.ACCT_GB = F.DETAILCD(+) \n"); sql.append(" AND B.PREPAY_ID = '"+vo.get("KEY_COL2")+"' \n"); search.setStatement(sql.toString()); //search.addParameter(vo.get("KEY_COL3")); listRowSet = search.execute(); selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0)); doJipge(connection, selectResult); String userId = selectResult.get("ACCT_ID"); RowSet result = getListRow1(connection, selectResult); for( int i = 0 ; i < result.size() ; i++ ) { Row row = result.getRow(i); //외주선급금 PDF작성 preInvoiceDoc020t(connection, row.get("TMPSLIP_ID"), userId); } boolean erpSucc = false; selectResult.set("DOC_ID",vo.get("DOC_ID")); String sqPrepay = getSqPrepay(selectResult); selectResult.set("SQ_PREPAY", sqPrepay); erpSucc = InterfaceOutERPiU.insertPrepayInfo(selectResult, signUserInfo); if(!erpSucc) { connection.rollback(); throw new Exception("ERPIU insert Error"); } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> prepaymentMiddle() : " + e.getMessage()); //WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public ValueObject getListRow(Connection connection, ValueObject vo) throws Exception { Persistent pSelect = null; StringBuffer sql = new StringBuffer(); ValueObject selectResult = new ValueObject(); try { pSelect = new PersistentImpl(connection); sql.append(" SELECT 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.ACMP_ID -- 기성ID \n"); sql.append(" , MAIN.PREPAY_ID -- 선급ID \n"); sql.append(" , PREPAY.CON_CORP_ID -- 계약업체 ID \n"); sql.append(" , CONTRACT.SUB_CONT_ID -- 외주게약서 ID \n"); sql.append(" , CASE \n"); sql.append(" WHEN (SLIP.STATUS_CD = '1') AND (SLIP.RETURN_CD = 'Y') THEN '-1' \n"); sql.append(" ELSE SLIP.STATUS_CD \n"); sql.append(" END STATUS_CD \n"); sql.append(" \n"); sql.append(" FROM SUB1050T MAIN \n"); sql.append(" , SUB1030T PREPAY \n"); sql.append(" , SUB1020T SUBVENDOR \n"); sql.append(" , SUB1000T CONTRACT \n"); sql.append(" , SAC01001 SLIP \n"); sql.append(" WHERE PREREQTFLG = 'PREPAY' \n"); sql.append(" AND MAIN.PREPAY_ID = PREPAY.PREPAY_ID \n"); sql.append(" AND PREPAY.CON_CORP_ID = SUBVENDOR.CON_CORP_ID \n"); sql.append(" AND PREPAY.SUB_CONT_ID = CONTRACT.SUB_CONT_ID \n"); sql.append(" AND MAIN.TMPSLIP_DEPT = SLIP.TMPSLIP_DEPT(+) \n"); sql.append(" AND MAIN.TMPSLIP_DT = SLIP.TMPSLIP_DT(+) \n"); sql.append(" AND MAIN.TMPSLIP_NO = SLIP.TMPSLIP_NO(+) \n"); sql.append(" AND SUBVENDOR.MAIN_CORP = 'Y' \n"); sql.append(" AND MAIN.PREPAY_ID = "+vo.get("PREPAY_ID")+" \n"); sql.append(" ORDER BY PROOFDATE desc, DEPT_NAME, CON_DOC_NO, CORP_KOR \n"); System.out.println(sql.toString()); pSelect.setStatement(sql.toString()); selectResult = RowSetUtility.getValueObject(pSelect.query().getRow(0)); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getListRow() : \n" + e.getMessage()); //WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( selectResult == null ) selectResult = new ValueObject(); } return selectResult; } public RowSet getListRow1(Connection connection, ValueObject vo) throws Exception { Persistent pSelect = null; StringBuffer sql = new StringBuffer(); RowSet resultRowset = null; try { pSelect = new PersistentImpl(connection); sql.append(" SELECT 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.ACMP_ID -- 기성ID \n"); sql.append(" , MAIN.PREPAY_ID -- 선급ID \n"); sql.append(" , PREPAY.CON_CORP_ID -- 계약업체 ID \n"); sql.append(" , CONTRACT.SUB_CONT_ID -- 외주게약서 ID \n"); //sql.append(" , CASE \n"); //sql.append(" WHEN (SLIP.STATUS_CD = '1') AND (SLIP.RETURN_CD = 'Y') THEN '-1' \n"); //sql.append(" ELSE SLIP.STATUS_CD \n"); //sql.append(" END STATUS_CD \n"); sql.append(" \n"); sql.append(" FROM SUB1050T MAIN \n"); sql.append(" , SUB1030T PREPAY \n"); sql.append(" , SUB1020T SUBVENDOR \n"); sql.append(" , SUB1000T CONTRACT \n"); //sql.append(" , SAC01001 SLIP \n"); sql.append(" WHERE PREREQTFLG = 'PREPAY' \n"); sql.append(" AND MAIN.PREPAY_ID = PREPAY.PREPAY_ID \n"); sql.append(" AND PREPAY.CON_CORP_ID = SUBVENDOR.CON_CORP_ID \n"); sql.append(" AND PREPAY.SUB_CONT_ID = CONTRACT.SUB_CONT_ID \n"); //sql.append(" AND MAIN.TMPSLIP_DEPT = SLIP.TMPSLIP_DEPT(+) \n"); //sql.append(" AND MAIN.TMPSLIP_DT = SLIP.TMPSLIP_DT(+) \n"); //sql.append(" AND MAIN.TMPSLIP_NO = SLIP.TMPSLIP_NO(+) \n"); sql.append(" AND SUBVENDOR.MAIN_CORP = 'Y' \n"); sql.append(" AND MAIN.PREPAY_ID = "+vo.get("PREPAY_ID")+" \n"); //sql.append(" AND SLIP.STATUS_cd IS null \n"); sql.append(" AND MAIN.TMPSLIP_YN = 'N' \n"); sql.append(" ORDER BY PROOFDATE desc, DEPT_NAME, CON_DOC_NO, CORP_KOR \n"); System.out.println(sql.toString()); pSelect.setStatement(sql.toString()); //selectResult = RowSetUtility.getValueObject(pSelect.query().getRow(0)); resultRowset = pSelect.query(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getListRow() : \n" + e.getMessage()); //WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( resultRowset == null ) resultRowset = null; } return resultRowset; } private void doJipge(Connection connection, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sql = new StringBuffer(); prepareSql(sql); persistent.setStatement(sql.toString()); try { //ValueObject user = storage.getUser(); persistent.addParameter(vo.get("ACCT_ID")); persistent.addParameter(vo.get("dept_cd")); persistent.addParameter(vo.get("PREPAY_ID")); persistent.addParameter(vo.get("ACCT_ID")); persistent.addParameter(vo.get("dept_cd")); persistent.addParameter(vo.get("PREPAY_ID")); System.out.println("1="+vo.get("ACCT_ID")); System.out.println("2="+vo.get("dept_cd")); System.out.println("3="+vo.get("PREPAY_ID")); System.out.println("4="+vo.get("ACCT_ID")); System.out.println("5="+vo.get("dept_cd")); System.out.println("6="+vo.get("PREPAY_ID")); persistent.execute(); persistent.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 , A.* \n"); sql.append(" FROM ( \n"); sql.append(" SELECT TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS PROOFDATE \n"); sql.append(" , F_GET_R_DEPT_CD(CONTRACT.DEPT_CD) DEPT_CD --2015.11.5 본코드 대체 \n"); sql.append(" , 'PREPAY' PREREQTFLG \n"); sql.append(" , 0 ACMP_NO \n"); sql.append(" , CONTRACT.PROOF_CD PROOF_CLASS \n"); sql.append(" , TO_CHAR(MAIN.PREPAY_DATE, 'YYMMDD') || ' 외주선급전표(' || CONTRACT.BUILD_NAME || ')' ORDACCDESC \n"); sql.append(" , MAIN.REQ_PREPAY_SUPPLY_TAMT SUPPLY_AMT \n"); sql.append(" , MAIN.REQ_PREPAY_SURTAX_AMT SURTAX_AMT \n"); sql.append(" , F_GET_DEALR_CD_EBID(SUBVENDOR.CORP_ID) DEALR_CD \n"); sql.append(" , F_GET_DEALR_NO_EBID(SUBVENDOR.CORP_ID) DCERTI_NO \n"); sql.append(" , TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS TAX_DATE \n"); sql.append(" , 'Y' ELECTAX_YN \n"); sql.append(" , CONTRACT.PAY_ITEM PAYITEM \n"); sql.append(" , CONTRACT.COST_CODE COSTCODE \n"); sql.append(" , CONTRACT.COST_TYPE COSTTYPE \n"); sql.append(" , CONTRACT.ACCT_GB ACC_CLASS \n"); sql.append(" , CASE SAUP.AC_UNIT_CD \n"); sql.append(" WHEN '10' THEN '22' \n"); sql.append(" ELSE '21' \n"); sql.append(" END PAY_GROUP \n"); sql.append(" , CONTRACT.PAYMENT_TERMS PAYMENT_TERMS \n"); sql.append(" , NULL TMPSLIP_DEPT \n"); sql.append(" , NULL TMPSLIP_DT \n"); sql.append(" , NULL TMPSLIP_NO \n"); sql.append(" , 'N' TMPSLIP_YN \n"); sql.append(" , 'N' SLIP_YN \n"); sql.append(" , MAIN.CON_CUR_CD CON_CUR_CD \n"); sql.append(" , NULL ACMP_ID \n"); sql.append(" , MAIN.PREPAY_ID PREPAY_ID \n"); sql.append(" , ? CRE_BY \n"); sql.append(" , SYSDATE CRE_DATE \n"); sql.append(" , NULL UPD_BY \n"); sql.append(" , NULL UPD_DATE \n"); sql.append(" , SUBVENDOR.CORP_ID CORP_ID \n"); sql.append(" , 'T' TAX_GB \n"); sql.append(" FROM SUB1030T MAIN \n"); sql.append(" , SUB1020T SUBVENDOR \n"); sql.append(" , SUB1000T CONTRACT \n"); sql.append(" , ETEC_ERPIF.ACZ10100 SAUP \n"); sql.append(" WHERE 1=1 \n"); sql.append(" AND MAIN.CON_CORP_ID = SUBVENDOR.CON_CORP_ID \n"); sql.append(" AND MAIN.SUB_CONT_ID = CONTRACT.SUB_CONT_ID \n"); sql.append(" AND CONTRACT.DEPT_CD = SAUP.DEPT_CD \n"); sql.append(" AND SUBVENDOR.MAIN_CORP = 'Y' \n"); sql.append(" AND CONTRACT.DEPT_CD = ? \n"); sql.append(" AND MAIN.STATUS_CD = '10' \n"); sql.append(" AND MAIN.PREPAY_ID = ? \n"); sql.append(" AND MAIN.PREPAY_SUPPLY_TAMT > 0 \n"); sql.append(" UNION ALL \n"); sql.append(" SELECT TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS PROOFDATE \n"); sql.append(" , F_GET_R_DEPT_CD(CONTRACT.DEPT_CD ) DEPT_CD --2015.11.5 본코드대체 \n"); sql.append(" , 'PREPAY' PREREQTFLG \n"); sql.append(" , 0 ACMP_NO \n"); sql.append(" , DECODE(CONTRACT.PROOF_CD,'21','26', CONTRACT.PROOF_CD) PROOF_CLASS \n"); sql.append(" , TO_CHAR(MAIN.PREPAY_DATE, 'YYMMDD') || ' 외주선급전표(' || CONTRACT.BUILD_NAME || ')' ORDACCDESC \n"); sql.append(" , MAIN.REQ_PREPAY_SUPPLY_NAMT SUPPLY_AMT \n"); sql.append(" , 0 SURTAX_AMT \n"); sql.append(" , F_GET_DEALR_CD_EBID(SUBVENDOR.CORP_ID) DEALR_CD \n"); sql.append(" , F_GET_DEALR_NO_EBID(SUBVENDOR.CORP_ID) DCERTI_NO \n"); sql.append(" , TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS TAX_DATE \n"); sql.append(" , 'Y' ELECTAX_YN \n"); sql.append(" , CONTRACT.PAY_ITEM PAYITEM \n"); sql.append(" , CONTRACT.COST_CODE COSTCODE \n"); sql.append(" , CONTRACT.COST_TYPE COSTTYPE \n"); sql.append(" , CONTRACT.ACCT_GB ACC_CLASS \n"); sql.append(" , CASE SAUP.AC_UNIT_CD \n"); sql.append(" WHEN '10' THEN '22' \n"); sql.append(" ELSE '21' \n"); sql.append(" END PAY_GROUP \n"); sql.append(" , CONTRACT.PAYMENT_TERMS PAYMENT_TERMS \n"); sql.append(" , NULL TMPSLIP_DEPT \n"); sql.append(" , NULL TMPSLIP_DT \n"); sql.append(" , NULL TMPSLIP_NO \n"); sql.append(" , 'N' TMPSLIP_YN \n"); sql.append(" , 'N' SLIP_YN \n"); sql.append(" , MAIN.CON_CUR_CD CON_CUR_CD \n"); sql.append(" , NULL ACMP_ID \n"); sql.append(" , MAIN.PREPAY_ID PREPAY_ID \n"); sql.append(" , ? CRE_BY \n"); sql.append(" , SYSDATE CRE_DATE \n"); sql.append(" , NULL UPD_BY \n"); sql.append(" , NULL UPD_DATE \n"); sql.append(" , SUBVENDOR.CORP_ID CORP_ID \n"); sql.append(" , 'N' TAX_GB \n"); sql.append(" FROM SUB1030T MAIN \n"); sql.append(" , SUB1020T SUBVENDOR \n"); sql.append(" , SUB1000T CONTRACT \n"); sql.append(" , ETEC_ERPIF.ACZ10100 SAUP \n"); sql.append(" WHERE 1=1 \n"); sql.append(" AND MAIN.CON_CORP_ID = SUBVENDOR.CON_CORP_ID \n"); sql.append(" AND MAIN.SUB_CONT_ID = CONTRACT.SUB_CONT_ID \n"); sql.append(" AND CONTRACT.DEPT_CD = SAUP.DEPT_CD \n"); sql.append(" AND SUBVENDOR.MAIN_CORP = 'Y' \n"); sql.append(" AND CONTRACT.DEPT_CD = ? \n"); sql.append(" AND MAIN.STATUS_CD = '10' \n"); sql.append(" AND MAIN.PREPAY_ID = ? \n"); sql.append(" AND MAIN.PREPAY_SUPPLY_NAMT > 0 \n"); sql.append(" ) A \n"); sql.append(" \n"); System.out.println(sql.toString()); } private void preInvoiceDoc020t(Connection con, String tmpslipID, String userID) throws Exception { String strResult = null; String message = ""; String SPStateMent = ""; CallableStatement cs = null; String arrDocNm [] = {"외주선급금계산서"}; String arrProofGu [] = {"18"}; //WAFLogger.debug("\n\t #################### params = [\n"+ params +"\n"); try { // 증빙정보 생성 프로시저 // - 외주 기성고 계산서만 새로 생성해야만 한다. SPStateMent = "{call SAC_DOCUMENTATIVE.prc_subinvoicebiz4TypeA(?, ?, ?, ?, ?)}"; cs = con.prepareCall(SPStateMent); // 라인당, 기성고계산서 한라인만 생성. for(int iCount=0; iCount 0 ) { sqPrepay = rowSet.getRow(0).get("SQ_PREPAY"); }else { throw new Exception("ERPIU SQ_PREPAY 채번 실패"); } return sqPrepay; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getSqPrepay() : \n" + e.getMessage()); //WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); throw e; } } }