/** * @(#)file PoDeciSignDriver.java * @(#)author Lee beomgeun * @(#)version 1.0 * @(#)date Aug 1, 2005 * @(#)since JDK 1.4.2 * * Copyright (c) www.dcchem.co.kr, Inc. * All rights reserved. * This software is the proprietary information of dcchem, 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.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.SACDOCreatePDF4; import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU; import weblogic.jdbc.vendor.oracle.OracleThinBlob; /** * * @version 1.0 * @author Beomgeun Lee */ public class PaymentBuySignDriver implements CommonSignInterface { /** * 테슽 파일 * */ public PaymentBuySignDriver() { 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("ACCSLIP_NO", ""); signUser.set("SLIP_DT", signDoc.get("KEY_COL4")); signUser.set("SLIP_NO", signDoc.get("KEY_COL5")); insertSign002t(connection, signUser); } updatePur1100t(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 { updatePur1100t(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 { updatePur1100t(connection, signDoc, new RowSetImpl()); } /* * (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 { System.out.println("5"); } 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"); System.out.println(sqlstr.toString()); System.out.println("=>"+vo.get("SIGN_SEQ")); System.out.println("=>"+vo.get("DOC_ID")); System.out.println("=>"+vo.get("APPROVAL_DATE")); System.out.println("=>"+vo.get("APPROVAL_SABUN")); System.out.println("=>"+vo.get("APPROVAL_USER")); System.out.println("=>"+vo.get("A_POSITION")); System.out.println("=>"+vo.get("SIGN_RESULT")); System.out.println("=>"+vo.get("SLIP_DEPT")); System.out.println("=>"+vo.get("SLIP_DT")); System.out.println("=>"+vo.get("SLIP_NO")); 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() + " --> updateSac01001() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public void updatePur1100t(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception { Persistent persistent1 = new PersistentImpl(connection); StringBuffer sqlstr = null; StringBuffer sqlstr1 = null; try { sqlstr = new StringBuffer(); sqlstr1 = new StringBuffer(); String status = ""; int complete = 0; if(vo.get("SIGN_DECI_CLASS").equals("14")){ status = "10"; //완료 }else { status = "99"; //반려 complete = 1; } sqlstr1.append(" UPDATE PUR1100T SET \n"); sqlstr1.append(" REQ_STATUS = ? \n"); sqlstr1.append(" WHERE PAYREQ_ID = ? \n"); persistent1.setStatement(sqlstr1.toString()); persistent1.addParameter(status); persistent1.addParameter(vo.get("KEY_COL2")); persistent1.execute(); //전표집계처리 if(complete == 0){ ValueObject pur1100tData = getPur1100t(vo.get("KEY_COL2"), vo.get("APPROVAL_SABUN")); pur1100tData.set("slipNo", vo.get("KEY_COL5")); pur1100tData.set("DOC_ID", vo.get("DOC_ID")); //업체평가 상태값 처리 updateAppr(connection, pur1100tData); getPayJipge(connection, pur1100tData, signUserInfo); } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } /** 전표 생성 Procedure( PUR_INVOICE_CREATE) 수행 * @throws Exception */ public void callPurInvoiceCreate(Connection con, String prp_req_id, String userID, String slipNo) throws Exception { String message = ""; String SPStateMent = ""; CallableStatement cs = null; try { SPStateMent = "{call PUR_INVOICE_CREATE2(?,?,?)}"; cs = con.prepareCall(SPStateMent); cs.setString(1, prp_req_id); // 가불금 ID cs.setString(2, userID); // USER ID cs.setString(3, slipNo); // USER ID 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 void createSMbill(Connection con, String prp_req_id, String userID, String payreqId, String ordDesc, String batchId) throws Exception { String message = ""; String SPStateMent = ""; CallableStatement cs = null; try { SPStateMent = "{call PUR_BILL_CREATE2(?,?,?,?,?)}"; cs = con.prepareCall(SPStateMent); cs.setString(1, prp_req_id); // 가불금 ID cs.setString(2, userID); // USER ID cs.setString(3, payreqId); cs.setString(4, ordDesc.substring(10)); cs.setString(5, 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 boolean getPayJipge(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception { Persistent persistent_i = null; Persistent persistent_i_p = null; Persistent persistent_u = null; String sqlstr_i = ""; String sqlstr_i_p = ""; String sqlstr_u = ""; try{ persistent_i = new PersistentImpl(connection); persistent_i_p = new PersistentImpl(connection); persistent_u = new PersistentImpl(connection); int jip_count = getPayJipgeCount(connection, vo).getInt("jip_count"); sqlstr_i = " INSERT INTO PUR1040T ( \n" + " TMPSLIP_ID -- 자재대금ID \n" + " ,DEPT_CD -- 현장코드 \n" + " ,PREPAY_GB -- 선급입고구분 \n" + " ,ENTER_DT -- 거래일자 \n" + " ,PROOF_CD -- 증빙코드 \n" + " ,ORD_DESC -- 적요 \n" + " ,MAT_AMT -- 구매금액 \n" + " ,MAT_SUPPLY_AMT -- 공급가액 \n" + " ,MAT_SURTAX_AMT -- 부가세 \n" + " ,PREPAY_SUB_AMT -- 선급금공제액 \n" + " ,DEALR_CD -- 거래처코드 \n" + " ,DCERTI_NO -- 사업자번호 \n" + " ,TAX_DATE -- 세금계산서일자 \n" + " ,ELECTAX_YN -- 전자세금계산서여부 \n" + " ,PAYITEM -- PAY ITEM \n" + " ,COSTCODE -- COST CODE \n" + " ,COSTTYPE -- COST TYPE \n" + " ,PAY_GROUP -- PAY_GROUP \n" + " ,PAYMENT_TERMS -- PAYMENT_TERMS \n" + " ,TMPNATFLG -- 계정구분 \n" + " ,TMPSLIP_YN -- 전표생성유무 \n" + " ,SLIP_YN -- 결의서작성유무 \n" + " ,CURRENCY_GB -- 통화구분 \n" + " ,PUR_CONT_ID -- 발주계약ID \n" + " ,PAYREQ_ID -- 지급신청ID \n" + " ,CRE_BY -- 등록자 \n" + " ,CRE_DATE -- 등록일시 \n" + " ) \n" + " VALUES ( \n" + " ? \n" + " ,? -- 현장코드 \n" + " ,? -- 선급입고구분 \n" + " ,TO_DATE(?, 'YYYYMMDD') -- 거래일자 \n" + " ,? -- 증빙코드 \n" + " ,? -- 적요 \n" + " ,? -- 구매금액 \n" + " ,? -- 공급가액 \n" + " ,? -- 부가세 \n" + " ,? -- 선급금공제액 \n" + " ,? -- 거래처코드 \n" + " ,? -- 사업자번호 \n" + " ,TO_DATE(?, 'YYYYMMDD') -- 세금계산서일자 \n" + " ,'Y' -- 전자세금계산서여부 \n" + " ,? -- PAY ITEM \n" + " ,? -- COST CODE \n" + " ,? -- COST TYPE \n" + " ,? -- PAY_GROUP \n" + " ,? -- PAYMENT_TERMS \n" + " ,? -- 계정구분 \n" + " ,? -- 전표생성유무 \n" + " ,? -- 결의서작성유무 \n" + " ,? -- 통화구분 \n" + " ,? -- 발주계약ID \n" + " ,? -- 지급신청ID \n" + " ,? -- 등록자 \n" + " ,SYSDATE ) \n" ; sqlstr_i_p = " INSERT INTO PUR1040T ( \n" + " TMPSLIP_ID -- 자재대금ID \n" + " ,DEPT_CD -- 현장코드 \n" + " ,PREPAY_GB -- 선급입고구분 \n" + " ,ENTER_DT -- 거래일자 \n" + " ,PROOF_CD -- 증빙코드 \n" + " ,ORD_DESC -- 적요 \n" + " ,MAT_AMT -- 구매금액 \n" + " ,MAT_SUPPLY_AMT -- 공급가액 \n" + " ,MAT_SURTAX_AMT -- 부가세 \n" + " ,PREPAY_SUB_AMT -- 선급금공제액 \n" + " ,DEALR_CD -- 거래처코드 \n" + " ,DCERTI_NO -- 사업자번호 \n" + " ,TAX_DATE -- 세금계산서일자 \n" + " ,ELECTAX_YN -- 전자세금계산서여부 \n" + " ,PAYITEM -- PAY ITEM \n" + " ,COSTCODE -- COST CODE \n" + " ,COSTTYPE -- COST TYPE \n" + " ,PAY_GROUP -- PAY_GROUP \n" + " ,PAYMENT_TERMS -- PAYMENT_TERMS \n" + " ,TMPNATFLG -- 계정구분 \n" + " ,TMPSLIP_YN -- 전표생성유무 \n" + " ,SLIP_YN -- 결의서작성유무 \n" + " ,CURRENCY_GB -- 통화구분 \n" + " ,PUR_CONT_ID -- 발주계약ID \n" + " ,PAYREQ_ID -- 지급신청ID \n" + " ,CRE_BY -- 등록자 \n" + " ,CRE_DATE -- 등록일시 \n" + " ) \n" + " VALUES ( \n" + " ? \n" + " ,? -- 현장코드 \n" + " ,? -- 선급입고구분 \n" + " ,TO_DATE(?, 'YYYYMMDD') -- 거래일자 \n" + " ,? -- 증빙코드 \n" + " ,? -- 적요 \n" + " ,? -- 구매금액 \n" + " ,? -- 공급가액 \n" + " ,? -- 부가세 \n" + //2015.9.11 불공제일때 선급공제 부가세 입력. " ,? -- 선급금공제액 \n" + " ,? -- 거래처코드 \n" + " ,? -- 사업자번호 \n" + " ,TO_DATE(?, 'YYYYMMDD') -- 세금계산서일자 \n" + " ,'Y' -- 전자세금계산서여부 \n" + " ,? -- PAY ITEM \n" + " ,? -- COST CODE \n" + " ,? -- COST TYPE \n" + " ,? -- PAY_GROUP \n" + " ,? -- PAYMENT_TERMS \n" + " ,? -- 계정구분 \n" + " ,? -- 전표생성유무 \n" + " ,? -- 결의서작성유무 \n" + " ,? -- 통화구분 \n" + " ,? -- 발주계약ID \n" + " ,? -- 지급신청ID \n" + " ,? -- 등록자 \n" + " ,SYSDATE ) \n" ; sqlstr_u = " UPDATE PUR1100T SET TRANS_CD = 'Y' WHERE PAYREQ_ID = ? "; RowSet rowSet = getPayReqList(connection, vo); RowSet preRowSet = getPayPreList(connection, vo); persistent_i.setStatement(sqlstr_i); persistent_i_p.setStatement(sqlstr_i_p); persistent_u.setStatement(sqlstr_u); String pay_group = ""; String payment_term = ""; String pay_item = ""; String cost_code = ""; String cost_type = ""; String billDiv = ""; String PAYREQ_ID = ""; //2018.08.02 String PAYREQ_YN = "N"; if(jip_count > 0){ //선급공제액 전표집계 if(preRowSet.size() > 0){ for(int j = 0; j < preRowSet.size(); j++){ //자재대금ID String tmpslipId_i_p = getTmpslipId(); persistent_i_p.addParameter(tmpslipId_i_p); persistent_i_p.addParameter(preRowSet.getRow(j).get("DEPT_CD")); persistent_i_p.addParameter(preRowSet.getRow(j).get("PREPAY_GB")); persistent_i_p.addParameter(preRowSet.getRow(j).get("REQ_DATE")); //거래일자 persistent_i_p.addParameter(preRowSet.getRow(j).get("GUA_GB")); //증빙 persistent_i_p.addParameter(preRowSet.getRow(j).get("ORD_DESC")); //적요 persistent_i_p.addParameter(0); //구매금액 persistent_i_p.addParameter(0); //공급가액 persistent_i_p.addParameter(0); //부가세 2015.9.11 불공제일때 선급 부가세 입력 persistent_i_p.addParameter(preRowSet.getRow(j).get("CUR_PREPAY_DEDUCTAMT")); //선급공제액 persistent_i_p.addParameter(preRowSet.getRow(j).get("CORP_ID")); persistent_i_p.addParameter(preRowSet.getRow(j).get("CORP_REG_NO")); //사업자번호 persistent_i_p.addParameter(preRowSet.getRow(j).get("REQ_DATE")); //세금계산서일자 persistent_i_p.addParameter(preRowSet.getRow(j).get("PAY_ITEM")); persistent_i_p.addParameter(preRowSet.getRow(j).get("COST_CODE")); persistent_i_p.addParameter(preRowSet.getRow(j).get("COST_TYPE")); persistent_i_p.addParameter(preRowSet.getRow(j).get("PAY_GROUP")); persistent_i_p.addParameter(preRowSet.getRow(j).get("PAYMENT_TERMS")); persistent_i_p.addParameter(preRowSet.getRow(j).get("ACCT_GB")); //계정구분 persistent_i_p.addParameter("N"); //전표생성유무 persistent_i_p.addParameter("N"); //결의서작성유무 persistent_i_p.addParameter(preRowSet.getRow(j).get("CURRENCY_GB")); persistent_i_p.addParameter(preRowSet.getRow(j).get("PUR_CONT_ID")); persistent_i_p.addParameter(preRowSet.getRow(j).get("PAYREQ_ID")); persistent_i_p.addParameter(vo.getInt("USERID")); persistent_i_p.execute(); persistent_i_p.clearParameters(); //2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생 PAYREQ_ID = preRowSet.getRow(j).get("PAYREQ_ID"); //증빙생성 prcDoc020t(connection, tmpslipId_i_p, vo.getInt("USERID"), tmpslipId_i_p, preRowSet.getRow(j).get("CUR_PREPAY_DEDUCTAMT")); } } if(rowSet.size() > 0){ for(int i = 0; rowSet.size() > i; i++){ //자재대금ID String tmpslipId_i = getTmpslipId(); pay_group = rowSet.getRow(i).get("PAY_GROUP"); payment_term = rowSet.getRow(i).get("PAYMENT_TERMS"); pay_item = rowSet.getRow(i).get("PAY_ITEM"); cost_code = rowSet.getRow(i).get("COST_CODE"); cost_type = rowSet.getRow(i).get("COST_TYPE"); persistent_i.addParameter(tmpslipId_i); persistent_i.addParameter(rowSet.getRow(i).get("DEPT_CD")); persistent_i.addParameter(rowSet.getRow(i).get("PREPAY_GB")); persistent_i.addParameter(rowSet.getRow(i).get("REQ_DATE")); //거래일자 persistent_i.addParameter(rowSet.getRow(i).get("GUA_GB")); //증빙 persistent_i.addParameter(rowSet.getRow(i).get("ORD_DESC")); //적요 persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_APPLYAMT")); //구매금액 persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_AMT")); //공급가액 persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_VATAMT")); //부가세 persistent_i.addParameter(0); //선급공제액 persistent_i.addParameter(rowSet.getRow(i).get("CORP_ID")); persistent_i.addParameter(rowSet.getRow(i).get("CORP_REG_NO")); //사업자번호 persistent_i.addParameter(rowSet.getRow(i).get("REQ_DATE")); //세금계산서일자 persistent_i.addParameter(pay_item); persistent_i.addParameter(cost_code); persistent_i.addParameter(cost_type); persistent_i.addParameter(pay_group); persistent_i.addParameter(payment_term); persistent_i.addParameter(rowSet.getRow(i).get("ACCT_GB")); //계정구분 persistent_i.addParameter("N"); //전표생성유무 persistent_i.addParameter("N"); //결의서작성유무 persistent_i.addParameter(rowSet.getRow(i).get("CURRENCY_GB")); persistent_i.addParameter(rowSet.getRow(i).get("PUR_CONT_ID")); persistent_i.addParameter(rowSet.getRow(i).get("PAYREQ_ID")); persistent_i.addParameter(vo.getInt("USERID")); persistent_i.execute(); persistent_i.clearParameters(); //2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생 PAYREQ_YN = "Y"; persistent_u.addParameter(rowSet.getRow(i).get("PAYREQ_ID"));//2018.08.02 persistent_u.execute(); persistent_u.clearParameters(); //증빙생성 prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0"); } } //2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생 if(PAYREQ_YN.equals("N")){ persistent_u.addParameter(PAYREQ_ID);//2018.08.02 persistent_u.execute(); persistent_u.clearParameters(); } try { if(vo.get("PAY_DIV").equals("1")) { InterfacePurERPiU.updateErpIuItem(vo, signUserInfo); }else { InterfacePurERPiU.updateErpIuInstall(vo, signUserInfo); } }catch(Exception e) { connection.rollback(); throw new Exception("ERPIU insert Error : "+e.getMessage()); } }else{ return false; } return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage()); WAFLogger.error(e); throw e; } } public void prcDoc020t(Connection con, String tmpslipID, int userID, String tempslip_id, String prepay_sub_amt) throws Exception { String strResult = null; //String strFileName = null; //String message = ""; String SPStateMent = ""; CallableStatement cs = null; String arrDocNm [] = {"자재 계산서"}; String arrProofGu [] = {"30"}; //WAFLogger.debug("\n\t #################### params = [\n"+ params +"\n"); try { // 증빙정보 생성 프로시저 // - 자재 계산서만 새로 생성해야만 한다. SPStateMent = "{call SAC_DOCUMENTATIVE.prc_purinvoicebiz3(?, ?, ?, ?, ?)}"; cs = con.prepareCall(SPStateMent); // 라인당, 기성고계산서 한라인만 생성. for(int iCount=0; iCount getSeq() : " + e.getMessage()); // System.out.println(e.getMessage()); WAFLogger.error(e); throw e; }finally{ if(vo == null) vo = new ValueObject(); } return vo; } private RowSet getPayReqList(Connection connection, ValueObject params) throws Exception { RowSet rowSet = new RowSetImpl(); String sqlstr = ""; Persistent persistent_c = null; try{ persistent_c = new PersistentImpl(connection); sqlstr = " SELECT \n" + " B.DEPT_CD DEPT_CD --현장코드 \n" + " , '2' AS PREPAY_GB --선급입고구분 \n" + " ,TO_CHAR(B.ORD_CON_DATE, 'YYYYMMDD') ORD_CON_DATE --거래일자 \n" + " ,A.GUA_GB GUA_GB --증빙코드 \n" + " ,CASE WHEN A.DEPOSIT_CD = '1' AND (SELECT AC_UNIT_CD \n" + " FROM ETEC_ERPIF.ACZ10100 \n" + " WHERE DEPT_CD = B.DEPT_CD ) = '10' \n" + " THEN '21' \n" + " WHEN A.DEPOSIT_CD = '1' AND (SELECT AC_UNIT_CD \n" + " FROM ETEC_ERPIF.ACZ10100 \n" + " WHERE DEPT_CD = B.DEPT_CD ) <> '10' \n" + " THEN '22' \n" + " ELSE A.PAY_GROUP \n" + " END PAY_GROUP --선급금PAY_GROUP \n" + " ,A.PAYMENT_TERMS PAYMENT_TERMS --PAYMENT_TERMS \n" + " ,CASE WHEN A.DEPOSIT_CD = 1 THEN \n" + " F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 선급금(' || TO_NUMBER(A.DEPOSIT_SEQ) || '차) ' || B.CST_DOC_NAME \n" + " WHEN A.DEPOSIT_CD = 2 THEN \n" + " F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 중도금(' || TO_NUMBER(A.DEPOSIT_SEQ ) || '차) ' || B.CST_DOC_NAME \n" + " ELSE F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 잔금(' || TO_NUMBER(A.DEPOSIT_SEQ ) || '차) ' || B.CST_DOC_NAME END \n" + " AS ORD_DESC \n" + " ,B.ORD_AMT ORD_AMT --구매금액 \n" + " ,B.ORD_SUPPLY_AMT ORD_SUPPLY_AMT --공급가액 \n" + " ,A.CUR_REQ_VATAMT CUR_REQ_VATAMT --부가세 \n" + " ,F_GET_DEALR_CD_EBID(B.CORP_ID) CORP_ID --거래처코드 \n" + " ,F_GET_DEALR_NO_EBID(B.CORP_ID) CORP_REG_NO --사업자번호 \n" + " ,B.PAY_ITEM AS PAY_ITEM \n" + " ,B.COST_TYPE AS COST_TYPE \n" + " ,B.COST_CODE AS COST_CODE \n" + " ,B.CURRENCY_GB CURRENCY_GB --통화구분 \n" + " ,A.PUR_CONT_ID PUR_CONT_ID --발주계약ID \n" + " ,A.PAYREQ_ID PAYREQ_ID --지급신청ID \n" + " ,A.TAX_DY as REQ_DATE -- 작성일자 \n" + " ,B.CON_GB -- 계약구분 \n" + " ,B.ELEC_CON_YN -- 전자계약여부 \n" + " ,A.CUR_PREPAY_APPLYAMT AS CUR_PREPAY_DEDUCTAMT -- 금회선급공제 공급가액 \n" + " ,A.CUR_REQ_AMT + A.CUR_REQ_VATAMT CUR_REQ_APPLYAMT -- 금회청구공급가액 \n" + " ,A.CUR_PAYING_AMT -- 금회지급예정액 \n" + " ,A.CUR_REQ_AMT -- 금회청구액 \n" + " ,A.ACCT_GB --분계유형 \n" + " ,A.TAX_METHOD_CD \n" + " FROM PUR1100T A \n" + " LEFT OUTER JOIN PUR1000T B ON A.PUR_CONT_ID = B.PUR_CONT_ID \n" + " LEFT OUTER JOIN SUPP_INFO C ON B.CORP_ID = C.CORP_ID \n" + " WHERE B.DEPT_CD = '"+params.get("dept_cd")+"' \n" + " AND A.TRANS_CD = 'N' \n" + " AND A.REQ_STATUS = '10' \n" + " AND C.JOB_DIV = 'P' \n" + " AND TO_CHAR(TO_DATE(A.TAX_DY, 'YYYY-MM-DD'), 'YYYY-MM') = '"+params.get("p_enter_dt")+"' \n" + " AND A.CUR_REQ_AMT > 0 \n" ; //2017.04.28추가. 금회청구액 >0경우만 전표발생. 0원전표 금지. System.out.println(sqlstr); persistent_c.setStatement(sqlstr); rowSet = persistent_c.query(); }catch (WAFSQLException se) { WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + se.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(se); throw se; } finally { if(rowSet == null){ rowSet = new RowSetImpl(); } } return rowSet; } private RowSet getPayPreList(Connection connection, ValueObject params) throws Exception{ RowSet rowSet = new RowSetImpl(); String sqlstr = ""; Persistent persistent_c = null; //ValueObject user = storage.getUser(); try{ persistent_c = new PersistentImpl(connection); sqlstr = " SELECT \n" + " B.DEPT_CD DEPT_CD --현장코드 \n" + " ,'2' AS PREPAY_GB --선급입고구분 \n" + " ,TO_CHAR(B.ORD_CON_DATE, 'YYYYMMDD') ORD_CON_DATE --거래일자 \n" + " ,A.GUA_GB GUA_GB --증빙코드 \n" + " ,A.PAY_GROUP PAY_GROUP --PAY_GROUP \n" + " ,A.PAYMENT_TERMS PAYMENT_TERMS --PAYMENT_TERMS \n" + " ,CASE WHEN A.DEPOSIT_CD = '1' THEN \n" + " F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 선급금(' || TO_NUMBER(A.DEPOSIT_SEQ) || '차) ' || B.CST_DOC_NAME \n" + " WHEN A.DEPOSIT_CD = '2' THEN \n" + " F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 중도금선급공제(' || TO_NUMBER(A.DEPOSIT_SEQ ) || '차) ' || B.CST_DOC_NAME \n" + " ELSE F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 잔금(' || TO_NUMBER(A.DEPOSIT_SEQ ) || '차) ' || B.CST_DOC_NAME END \n" + " AS ORD_DESC --적요 \n" + " ,B.ORD_AMT ORD_AMT --구매금액 \n" + " ,B.ORD_SUPPLY_AMT ORD_SUPPLY_AMT --공급가액 \n" + " ,A.CUR_REQ_VATAMT CUR_REQ_VATAMT --부가세 \n" + " ,F_GET_DEALR_CD_EBID(B.CORP_ID) CORP_ID --거래처코드 \n" + " ,F_GET_DEALR_NO_EBID(B.CORP_ID) CORP_REG_NO --사업자번호 \n" + " ,B.PAY_ITEM AS PAY_ITEM \n" + " ,B.COST_TYPE AS COST_TYPE \n" + " ,B.COST_CODE AS COST_CODE \n" + " ,B.CURRENCY_GB CURRENCY_GB --통화구분 \n" + " ,A.PUR_CONT_ID PUR_CONT_ID --발주계약ID \n" + " ,A.PAYREQ_ID PAYREQ_ID --지급신청ID \n" + " ,A.TAX_DY as REQ_DATE -- 작성일자 \n" + " ,B.CON_GB -- 계약구분 \n" + " ,B.ELEC_CON_YN -- 전자계약여부 \n" + " ,A.CUR_PREPAY_APPLYAMT AS CUR_PREPAY_DEDUCTAMT -- 금회선급공제 공급가액 \n" + " ,A.CUR_PREPAY_VAT AS CUR_PREPAY_VAT -- 금회선급공제 vat 2015.9.11 선급공제 부가세 추가 \n" + " ,A.CUR_REQ_APPLYAMT -- 금회청구공급가액 \n" + " ,A.CUR_PAYING_AMT -- 금회지급예정액 \n" + " ,A.CUR_REQ_AMT -- 금회청구액 \n" + " ,A.ACCT_GB --분계유형 \n" + " FROM PUR1100T A \n" + " LEFT OUTER JOIN PUR1000T B ON A.PUR_CONT_ID = B.PUR_CONT_ID \n" + " LEFT OUTER JOIN SUPP_INFO C ON B.CORP_ID = C.CORP_ID \n" + " WHERE B.DEPT_CD = '"+params.get("dept_cd")+"' \n" + " AND A.TRANS_CD = 'N' \n" + " AND A.REQ_STATUS = '10' \n" + " AND C.JOB_DIV = 'P' \n" + " AND TO_CHAR(TO_DATE(A.TAX_DY, 'YYYY-MM-DD'), 'YYYY-MM') = '"+params.get("p_enter_dt")+"' \n"+ " AND A.CUR_PREPAY_DEDUCTAMT > 0 \n"; System.out.println(sqlstr); persistent_c.setStatement(sqlstr); rowSet = persistent_c.query(); }catch (WAFSQLException se) { WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + se.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(se); throw se; } finally { if(rowSet == null){ rowSet = new RowSetImpl(); } } return rowSet; } public ValueObject getPur1100t(String payreqId, String appSabun) throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; ValueObject row = null; try{ sqlstr = " SELECT \n " +" A.DEPT_CD, --현장코드 \n " +" B.PUR_CONT_ID, \n " +" B.PAYREQ_ID, \n " +" B.PAY_DIV, \n " +" TO_CHAR(TO_DATE(B.TAX_DY,'yyyymmdd'), 'YYYY-MM') as P_ENTER_DT, \n " +" (SELECT MAX(ACCT_ID) FROM PUB0120T A \n " +" WHERE USER_SABUN = '"+appSabun+"') AS USERID, \n " +" D.ARR_ID, \n " +" A.CORP_ID \n " +" ,TO_CHAR(SYSDATE,'YYYYMMDD') AS DT_CHECK_APP \n " +" ,(SELECT MAX(USER_NAME) FROM PUB0120T WHERE USER_SABUN = '"+appSabun+"') AS NM_CHECK_APP \n " +" ,'"+appSabun+"' AS ID_CHECK_APP \n " //2019.04.03 신용길부장님 수정요청 +" ,ROUND(B.CUR_ACMP_APPLYAMT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS SAMT_BUILT \n " +" ,ROUND(B.CUR_ACMP_VAT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS VAMT_BUILT \n " +" ,ROUND((B.CUR_ACMP_APPLYAMT + B.CUR_ACMP_VAT ) * NVL(B.CUR_ACMP_EXCRATE,1),0) AS AMT_BUILT \n " +" ,ROUND(B.CUR_PREPAY_APPLYAMT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS SAMT_DEDUCT \n " +" ,ROUND(B.CUR_PREPAY_VAT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS VAMT_DEDUCT \n " +" ,ROUND((B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT) * NVL(B.CUR_ACMP_EXCRATE,1),0) AS AMT_DEDUCT \n " +" ,B.CUR_ACMP_APPLYAMT AS SAMT_BUILT_CURR \n " +" ,B.CUR_ACMP_VAT AS VAMT_BUILT_CURR \n " +" ,(B.CUR_ACMP_APPLYAMT + B.CUR_ACMP_VAT ) AS AMT_BUILT_CURR \n " +" ,B.CUR_PREPAY_APPLYAMT AS SAMT_DEDUCT_CURR \n " +" ,B.CUR_PREPAY_VAT AS VAMT_DEDUCT_CURR \n " +" ,B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT AS AMT_DEDUCT_CURR \n " +" ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS \n " +" ,B.TAX_DY AS DT_BUILT \n " +" ,B.TAX_DY AS DT_STRG_IN \n " +" ,NVL(B.CUR_ACMP_EXCRATE,1) AS UNT_CURR \n " //2019.07.29 ERP 자동분개를 위한 계정코드 +" ,CASE WHEN A.CURRENCY_GB = 'KRW' THEN \n" +" CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(E.CHG_CODE16, '') \n" +" ELSE NVL(E.CHG_CODE14, '') \n" +" END \n" +" ELSE CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(E.CHG_CODE17, '') \n" +" ELSE NVL(E.CHG_CODE15, '') \n" +" END \n" +" END AS CD_COST_TRADE \n" +" FROM PUR1000T A, PUR1100T B, SUPP_APPR D, (SELECT DETAILCD, CHG_CODE14, CHG_CODE15, CHG_CODE16, CHG_CODE17 FROM EBID_COM911T WHERE BASECD = 'PUR-ACCT_GB' ) E \n " +" WHERE A.PUR_CONT_ID = B.PUR_CONT_ID \n " +" AND A.ORD_DOC_NO = D.ORD_DOC_NO(+) \n " +" AND A.CORP_ID = D.CORP_ID(+) \n " +" AND A.ACCT_GB = E.DETAILCD(+) \n " +" AND B.PAYREQ_ID = '"+payreqId+"' \n "; //2019.04.03 신용길부장님 수정요청 //+" ,B.CUR_PREPAY_APPLYAMT AS SAMT_DEDUCT \n " //+" ,B.CUR_PREPAY_VAT AS VAMT_DEDUCT \n " //+" ,(B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT) AS AMT_DEDUCT \n " // +" ,(B.CUR_REQ_AMT+B.CUR_REQ_VATAMT) * NVL(B.CUR_ACMP_EXCRATE,1) AS AMT_BUILT \n " //+" ,B.CUR_REQ_AMT * NVL(B.CUR_ACMP_EXCRATE,1) AS SAMT_BUILT \n " //+" ,B.CUR_REQ_VATAMT * NVL(B.CUR_ACMP_EXCRATE,1) AS VAMT_BUILT \n " //+" ,(B.CUR_REQ_AMT+B.CUR_REQ_VATAMT) AS AMT_BUILT_CURR \n " //+" ,B.CUR_REQ_AMT AS SAMT_BUILT_CURR \n " //+" ,B.CUR_REQ_VATAMT AS VAMT_BUILT_CURR \n " System.out.println(sqlstr); search.setStatement(sqlstr); rowSet = search.execute(); row = RowSetUtility.getValueObject(rowSet.getRow(0)); }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getPur1100t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return row; } public String getTmpslipId() throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; ValueObject row = null; String resultId = ""; try{ sqlstr = " SELECT PUR1040T_S.NEXTVAL AS TMPSLIP_ID FROM DUAL \n"; System.out.println(sqlstr); search.setStatement(sqlstr); rowSet = search.execute(); row = RowSetUtility.getValueObject(rowSet.getRow(0)); resultId = row.get("TMPSLIP_ID"); }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return resultId; } /** * 대금이 선급금일시 pay_group, payment_terms 변경 * @param * @return ValueObject */ public ValueObject getPayGroup(Connection connection, String dept_cd) throws Exception { Persistent persistent_c = null; ValueObject vo = new ValueObject(); ValueObject row = null; String sqlstr = ""; try{ persistent_c = new PersistentImpl(connection); sqlstr += " SELECT \n" + " DETAILCD \n" + " ,DETAILNM \n" + " FROM COM911T \n" + " WHERE BASECD = '005' \n" + " AND CHG_CODE04 = CASE \n" + " WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = '"+dept_cd+"' ) = '60' THEN '60' \n" + " WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = '"+dept_cd+"' ) <> '10' THEN '20' \n" + " ELSE '10' END || '_C' \n" ; persistent_c.setStatement(sqlstr); System.out.println(sqlstr); row = RowSetUtility.getValueObject(persistent_c.query().getRow(0)); vo.set("DETAILCD", row.get("DETAILCD")); }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getSeq() : " + e.getMessage()); // System.out.println(e.getMessage()); WAFLogger.error(e); throw e; }finally{ if(vo == null) vo = new ValueObject(); } return vo; } private void updateAppr(Connection connection, ValueObject pur1100tData) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); String status = "02"; sqlstr.append(" UPDATE SUPP_APPR SET \n"); sqlstr.append(" REGU_STATUS_CD = '02' \n"); sqlstr.append(" WHERE CORP_ID = ? \n"); sqlstr.append(" AND ARR_ID = ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(pur1100tData.get("CORP_ID")); persistent.addParameter(pur1100tData.get("ARR_ID")); persistent.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updateAppr() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } }