/** * @(#)file PaymentBuyPrepaySignDriver.java * @(#)author Ok seung hyun * @(#)version 1.0 * @(#)date 2014-09-13 * @(#)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 com.udapsoft.waf.system.HandlerStorage; 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.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.common.sender.MessageSend; import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU; import weblogic.jdbc.vendor.oracle.OracleThinBlob; /** * getPur1030t * @version 1.0 * @author Beomgeun Lee */ public class PaymentBuyPrepaySignDriver implements CommonSignInterface { /** * 테슽 파일 * */ public PaymentBuyPrepaySignDriver() { 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 { } /* * (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 { } /* * (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")); if( signDoc.get("SIGN_TYPE").equals("2") ){ signUser.set("SLIP_DEPT", ""); signUser.set("ACCSLIP_NO", signDoc.get("KEY_COL1")); }else { 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); } updatePur1030t(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 { updatePur1030t(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 { updatePur1030t(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 updatePur1030t(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 = ""; int complete = 0; sqlstr.append(" SELECT REQ_DIV_CD \n"); //신청구분코드(1:신청, 2:부분신청, 3:포기) sqlstr.append(" FROM PUR1030T \n"); sqlstr.append(" WHERE PREPAY_ID = ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(vo.get("KEY_COL2")); String div = RowSetUtility.getValueObject(persistent.query().getRow(0)).get("REQ_DIV_CD"); if(vo.get("SIGN_DECI_CLASS").equals("14")){ status = "10"; //구매 선급완료 if("3".equals(div)){ status = "11"; //구매 포기완료 } }else { status = "91"; //구배 선급반려 complete = 1; } //구매)선급내역에 상태값 변경 sqlstr1.append(" UPDATE PUR1030T SET \n"); sqlstr1.append(" PREPAY_PROC_GB = ? \n"); sqlstr1.append(" WHERE PREPAY_ID = ? \n"); persistent1.setStatement(sqlstr1.toString()); persistent1.addParameter(status); persistent1.addParameter(vo.get("KEY_COL2")); persistent1.execute(); ValueObject getPur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); if(getPur1030tData.get("CHG_DIV_CD").equals("")){ //2019.05.10 신규=NULL, 추가='Y' if(!"3".equals(div)){ //신청, 부분신청 //전표집계처리 if(complete == 0){ //ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); getPur1030tData.set("slipNo", vo.get("KEY_COL5")); getPur1030tData.set("DOC_ID", vo.get("DOC_ID")); getPayJipge(connection, getPur1030tData, signUserInfo); } }else{//포기 //ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); updatePayPlan(connection, getPur1030tData); } }else{ //2018.07.27 추가 선급금 if(!"3".equals(div)){ //신청, 부분신청 //전표집계처리 if(complete == 0){ //ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); getPur1030tData.set("slipNo", vo.get("KEY_COL5")); getPur1030tData.set("DOC_ID", vo.get("DOC_ID")); getPayJipgeTybeB(connection, getPur1030tData, signUserInfo); //추가된 선급금 cont_pur_payplan insert } }else{ //포기 //2019.05.10 추가선급금 포기는 아무일 없음. //ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); } } //2018.08.02 메일발송 전표발행 완료후 if(vo.get("SIGN_DECI_CLASS").equals("14")){ Send(vo.get("KEY_COL1")); } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + 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_CREATE1(?,?,?)}"; cs = con.prepareCall(SPStateMent); cs.setString(1, prp_req_id); cs.setString(2, userID); cs.setString(3, slipNo); 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_CREATE1(?,?,?,?,?)}"; 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; Persistent persistent_u01 = null; String sqlstr_i = ""; String sqlstr_i_p = ""; String sqlstr_u = ""; String sqlstr_u01 = ""; try{ persistent_i = new PersistentImpl(connection); persistent_i_p = new PersistentImpl(connection); persistent_u = new PersistentImpl(connection); persistent_u01 = 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" + " ,PREPAY_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" + " ,PREPAY_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_u = " UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ? AND PAY_SEQ = ? "; sqlstr_u01 = " UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ? AND PAY_SEQ = ? AND PAY_DIV_CD = '01'"; RowSet rowSet = getPayReqList(connection, vo); persistent_i.setStatement(sqlstr_i); persistent_i_p.setStatement(sqlstr_i_p); persistent_u.setStatement(sqlstr_u); persistent_u01.setStatement(sqlstr_u01); String pay_group = ""; String payment_term = ""; String pay_item = ""; String cost_code = ""; String cost_type = ""; String billDiv = ""; if(jip_count > 0){ ValueObject contPurPnalData01 = getContPurPayplan01(vo.get("PUR_CONT_MOD_ID")); ValueObject contPurPnalDataMax = getContPurPayplanMax(vo.get("PUR_CONT_MOD_ID")); if(rowSet.size() > 0){ for(int i = 0; rowSet.size() > i; i++){ if(contPurPnalData01.size() > 0){ double paySuppluAmt01 = contPurPnalData01.getDouble("PAY_SUPPLY_AMT"); double curReqAmt = rowSet.getRow(i).getDouble("R_P_S_A"); double paySuppluAmtMax = contPurPnalDataMax.getDouble("PAY_SUPPLY_AMT"); double culSuppluAmt = paySuppluAmt01 - curReqAmt; if(culSuppluAmt != 0){ paySuppluAmtMax = paySuppluAmtMax + culSuppluAmt; persistent_u.addParameter(paySuppluAmtMax); persistent_u.addParameter(vo.get("PUR_CONT_MOD_ID")); persistent_u.addParameter(contPurPnalDataMax.get("PAY_SEQ")); System.out.println(sqlstr_u); persistent_u.execute(); persistent_u.clearParameters(); persistent_u01.addParameter(curReqAmt); persistent_u01.addParameter(vo.get("PUR_CONT_MOD_ID")); persistent_u01.addParameter(contPurPnalDataMax.get("MINPAY_SEQ")); System.out.println(sqlstr_u01); persistent_u01.execute(); persistent_u01.clearParameters(); } } //자재대금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("REQ_PREPAY_AMT")); //구매금액 persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT")); //공급가액 persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SURTAX_AMT")); //부가세 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(); //증빙생성 prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0"); //전표생성프로시저호출 //callPurInvoiceCreate(connection, tmpslipId_i, vo.get("USERID"), vo.get("slipNo")); if(rowSet.getRow(i).get("TAX_METHOD_CD").equals("1")){ //역발행(SGC이앤씨 발행)일때 발행 billDiv = "1"; //세금계산서 발행 //createSMbill(connection, tmpslipId_i, vo.get("USERID"), rowSet.getRow(i).get("PAYREQ_ID"), rowSet.getRow(i).get("ORD_DESC"), batchId); } //세금계산서 번호업테이트 //update1040tConId(connection, tmpslipId_i, rowSet.getRow(i).get("PAYREQ_ID")); } } String paySeq = getErpPaySeq(vo.get("YY_PREPAY")); vo.set("NO_PREPAY", paySeq); try { InterfacePurERPiU.insertPrepayInfo(vo, signUserInfo); }catch(Exception e) { connection.rollback(); throw new Exception("ERPIU insert Error"+e.getMessage()); } if(billDiv.equals("1")){ connection.commit(); //세금계산서 역발행 요청 //apProcess(connection, batchId); } }else{ //storage.setMessage("집계할수 있는 내용이 없습니다."); return false; } return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + e.getMessage()); WAFLogger.error(e); throw e; //return false; } } //집계처리 public boolean getPayJipgeTybeB(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception { Persistent persistent_i = null; Persistent persistent_i_p = null; String sqlstr_i = ""; String sqlstr_i_p = ""; try{ persistent_i = new PersistentImpl(connection); persistent_i_p = 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" + " ,PREPAY_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" + " ,PREPAY_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" ; RowSet rowSet = getPayReqList(connection, vo); persistent_i.setStatement(sqlstr_i); persistent_i_p.setStatement(sqlstr_i_p); String pay_group = ""; String payment_term = ""; String pay_item = ""; String cost_code = ""; String cost_type = ""; if(jip_count > 0){ if(rowSet.size() > 0){ for(int i = 0; rowSet.size() > i; i++){ if(vo.get("PAY_DIV").equals("2")){ savePlanData(connection,vo, rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT"));//insert cont_pur_payplan } //자재대금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("REQ_PREPAY_AMT")); //구매금액 persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT")); //공급가액 persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SURTAX_AMT")); //부가세 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(); //증빙생성 prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0"); //전표생성프로시저호출 //callPurInvoiceCreate(connection, tmpslipId_i, vo.get("USERID"), vo.get("slipNo")); //세금계산서 번호업테이트 //update1040tConId(connection, tmpslipId_i, rowSet.getRow(i).get("PAYREQ_ID")); } } String paySeq = getErpPaySeq(vo.get("YY_PREPAY")); vo.set("NO_PREPAY", paySeq); try { InterfacePurERPiU.insertPrepayInfo(vo, signUserInfo); }catch(Exception e) { connection.rollback(); throw new Exception("ERPIU insert Error"+e.getMessage()); } }else{ //storage.setMessage("집계할수 있는 내용이 없습니다."); return false; } return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + e.getMessage()); WAFLogger.error(e); throw e; //return false; } } public void prcDoc020t(Connection con, String tmpslipID, int userID, String tempslip_id, String prepay_sub_amt) throws Exception { String strResult = null; 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 A.PREPAY_ID \n" + " ,B.DEPT_CD DEPT_CD -- 현장코드 \n" + // 2015.9.10 Mapping Dept변경 (원B.DEPT_CD) " ,'1' AS PREPAY_GB --선급입고구분 \n" + " ,TO_CHAR(A.PREPAY_DATE, 'YYYYMMDD') ORD_CON_DATE --거래일자 \n" + " ,B.GUA_GB GUA_GB --증빙코드 \n" + " ,CASE WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = B.DEPT_CD ) = '10' THEN '21' \n" + " WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = B.DEPT_CD ) <> '10' THEN '22' \n" + " ELSE B.PAY_GROUP \n" + " END PAY_GROUP --선급금PAY_GROUP \n" + " ,B.PAYMENT_TERMS PAYMENT_TERMS --PAYMENT_TERMS \n" + " ,F_GET_REMARK_DATE(A.PREPAY_DATE) || ' 선급금(' || TO_NUMBER(A.PREPAY_NO) || '차) ' || B.CST_DOC_NAME AS ORD_DESC \n" + " ,B.ORD_AMT ORD_AMT --구매금액 \n" + " ,A.PREPAY_SUPPLY_AMT ORD_SUPPLY_AMT --부가세 \n" + " ,A.PREPAY_SURTAX_AMT CUR_REQ_VATAMT --부가세 \n" + " ,F_GET_DEALR_CD_EBID(C.CORP_ID) CORP_ID --거래처코드 \n" + " ,F_GET_DEALR_NO_EBID(C.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.PREPAY_ID PAYREQ_ID --선급신청ID \n" + " ,A.TAX_DY as REQ_DATE -- 작성일자 \n" + " ,B.CON_GB -- 계약구분 \n" + " ,B.ACCT_GB -- 분개구분 \n" + " ,B.ELEC_CON_YN -- 전자계약여부 \n" + " ,A.REQ_PREPAY_AMT AS REQ_PREPAY_AMT \n" + " ,A.REQ_PREPAY_SUPPLY_AMT AS REQ_PREPAY_SUPPLY_AMT \n" + " ,A.REQ_PREPAY_SURTAX_AMT AS REQ_PREPAY_SURTAX_AMT \n" + " ,1182 AS CUR_PAYING_AMT -- 금회지급예정액 \n" + " ,A.PREPAY_SUPPLY_AMT AS CUR_REQ_AMT -- 금회청구액 \n" + " ,A.REQ_PREPAY_SUPPLY_AMT AS R_P_S_A \n" + " ,A.TAX_METHOD_CD \n" + " FROM PUR1030T A, PUR2000T B, SUPP_INFO C \n" + " WHERE 1=1 \n" + " AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n" + " AND B.CORP_ID = C.CORP_ID \n" + " AND A.PREPAY_PROC_GB = '10' \n" + " AND A.PREPAY_ID = '"+params.get("PREPAY_ID")+"' \n" ; System.out.println(sqlstr); persistent_c.setStatement(sqlstr); rowSet = persistent_c.query(); }catch (WAFSQLException se) { WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + se.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(se); throw se; } finally { if(rowSet == null){ rowSet = new RowSetImpl(); } } return rowSet; } public ValueObject getPur1030t(String purContModId, String prepayId, String signInterfaceId) throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); ValueObject row = null; try{ sqlstr.append("SELECT B.PUR_CONT_ID \n"); sqlstr.append(" ,B.PUR_CONT_MOD_ID \n"); sqlstr.append(" ,( \n"); sqlstr.append(" SELECT MAX(PAY_SEQ) \n"); sqlstr.append(" FROM CONT_PUR_PAYPLAN \n"); sqlstr.append(" WHERE PAY_DIV_CD = '01' \n"); sqlstr.append(" AND PUR_CONT_ID = B.PUR_CONT_ID \n"); sqlstr.append(" AND PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n"); sqlstr.append(" ) AS PAY_SEQ \n"); sqlstr.append(" ,B.PREPAY_ID \n"); sqlstr.append(" ,A.DEPT_CD \n"); sqlstr.append(" ,B.CHG_DIV_CD \n"); sqlstr.append(" ,A.PAY_DIV \n"); sqlstr.append(" ,TO_CHAR(TO_DATE(B.TAX_DY, 'YYYYMMDD'), 'YYYY-MM') AS P_ENTER_DT \n"); sqlstr.append(" ,F.CRE_BY AS USERID \n"); sqlstr.append(" ,TO_CHAR(PREPAY_DATE,'YYYY') AS YY_PREPAY \n"); sqlstr.append(" ,'' AS NO_PREPAY \n"); sqlstr.append(" ,'001' AS SQ_PREPAY \n"); sqlstr.append(" ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY \n"); sqlstr.append(" ,NVL(C.ERP_SITE_CD,A.DEPT_CD) AS CD_SITE \n"); sqlstr.append(" ,'01' AS WM_CM00308 \n"); sqlstr.append(" ,TO_CHAR(PREPAY_DATE,'YYYYMMDD') AS DT_PREPAY \n"); sqlstr.append(" ,'01' AS WM_CM00301 \n"); sqlstr.append(" ,A.ERP_NO_ECUT AS NO_ECUT \n"); sqlstr.append(" ,TO_CHAR(A.CST_DATE,'YYYYMM') AS YM_ECUT \n"); sqlstr.append(" ,E.ERP_CD_COOP AS CD_COOP \n"); sqlstr.append(" ,REPLACE(E.DCERTI_NO,'-','') AS NO_COMPANY \n"); sqlstr.append(" ,NVL(B.REQ_PREPAY_SUPPLY_AMT,0)*F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS AMT_PREPAY \n");//2019.03.07 sqlstr.append(" ,NVL(B.REQ_PREPAY_SURTAX_AMT,0)*F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS VAT_PREPAY \n");//2019.03.07 sqlstr.append(" ,TO_CHAR(B.CRE_DATE,'YYYYMMDD') AS DT_REQU \n"); sqlstr.append(" ,GET_USER_NM(B.CRE_BY) AS NM_REQU \n"); sqlstr.append(" ,TO_CHAR(F.CRE_DATE,'YYYYMMDD') AS DT_CONFIRM \n"); sqlstr.append(" ,GET_USER_NM(F.CRE_BY) AS NM_CONFIRM \n"); sqlstr.append(" ,PREPAY_DESC AS DESC_PREPAY \n"); sqlstr.append(" ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT \n"); sqlstr.append(" ,TO_CHAR(B.CRE_DATE,'YYYYMMDD') AS DTS_INSERT \n"); sqlstr.append(" ,NVL(G.CHG_CODE10,A.CURRENCY_GB) AS CURR_SOUR \n"); sqlstr.append(" ,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS UNT_CURR \n"); //EXCHANGE AS 2019.03.07 sqlstr.append(" ,NVL(B.REQ_PREPAY_SUPPLY_AMT,0) AS SAMT_CURR_PREPAY \n"); sqlstr.append(" ,NVL(B.REQ_PREPAY_SURTAX_AMT,0) AS VAMT_CURR_PREPAY \n"); sqlstr.append(" ,'Y' AS YN_BAN \n"); sqlstr.append(" ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS \n"); //2019.07.29 ERP 자동분개를 위한 계정코드 sqlstr.append(" ,CASE WHEN A.CURRENCY_GB = 'KRW' THEN \n"); sqlstr.append(" CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE12, '') \n"); sqlstr.append(" ELSE NVL(J.CHG_CODE10, '') \n"); sqlstr.append(" END \n"); sqlstr.append(" ELSE CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE13, '') \n"); sqlstr.append(" ELSE NVL(J.CHG_CODE11, '') \n"); sqlstr.append(" END \n"); sqlstr.append(" END AS CD_COST_TRADE \n"); sqlstr.append("FROM PUR2000T A \n"); sqlstr.append(" ,PUR1030T B \n"); sqlstr.append(" ,ETEC_ERPIF.SITE_MAPPING C \n"); sqlstr.append(" ,CONT_PUR_SUPP D \n"); sqlstr.append(" ,ETEC_ERPIF.ACZ10800 E \n"); sqlstr.append(" ,( \n"); sqlstr.append(" SELECT CRE_BY, CRE_DATE \n"); sqlstr.append(" FROM SIGN001T \n"); sqlstr.append(" WHERE SIGN_INTERFACE_ID = "+signInterfaceId+" \n"); sqlstr.append(" ) F \n"); sqlstr.append(" ,( \n"); sqlstr.append(" SELECT DETAILCD, CHG_CODE10 \n"); sqlstr.append(" FROM EBID_COM911T \n"); sqlstr.append(" WHERE BASECD = '911' \n"); sqlstr.append(" ) G \n"); //sqlstr.append(" ,BID_SUPP H \n");2019.03.07 sqlstr.append(" ,SUPP_INFO I \n"); sqlstr.append(" ,(SELECT DETAILCD, CHG_CODE10, CHG_CODE11, CHG_CODE12, CHG_CODE13 FROM EBID_COM911T WHERE BASECD = 'PUR-ACCT_GB' ) J \n"); sqlstr.append("WHERE A.PUR_CONT_ID = B.PUR_CONT_ID \n"); sqlstr.append("AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n"); sqlstr.append("AND A.DEPT_CD = C.EBID_SITE_CD(+) \n"); sqlstr.append("AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID \n"); sqlstr.append("AND D.CORP_ID != 0 \n"); sqlstr.append("AND D.CORP_ID = I.CORP_ID \n"); sqlstr.append("AND REPLACE(I.CORP_REG_NO,'-','') = REPLACE(E.DCERTI_NO,'-','') \n"); sqlstr.append("AND E.ADDITEM20 = 'P' \n"); //2019.02.26 sqlstr.append("AND A.CURRENCY_GB = G.DETAILCD(+) \n"); //sqlstr.append("AND A.BID_NO = H.BID_NO \n");//2019.02.26 //sqlstr.append("AND A.BID_DEG = H.BID_DEG \n");//2019.02.26 //sqlstr.append("AND D.CORP_ID = TO_NUMBER(H.VENDOR_CD) \n");//2019.02.26 sqlstr.append("AND A.ACCT_GB = J.DETAILCD(+) \n"); sqlstr.append("AND B.PREPAY_ID = "+prepayId+" \n"); sqlstr.append("AND B.PUR_CONT_MOD_ID = "+purContModId+" \n"); System.out.println(sqlstr.toString()); search.setStatement(sqlstr.toString()); rowSet = search.execute(); row = RowSetUtility.getValueObject(rowSet.getRow(0)); }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getPur1030t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return row; } public ValueObject getContPurPayplanMax(String purContModId) throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; ValueObject row = null; try{ sqlstr = " SELECT B.PAY_SUPPLY_AMT , A.PAY_SEQ, A.MINPAY_SEQ \n" + " FROM ( \n" + " SELECT PUR_CONT_MOD_ID, MIN(PAY_SEQ) AS MINPAY_SEQ, MAX(PAY_SEQ) AS PAY_SEQ, MAX(PAY_SUPPLY_AMT) AS PAY_SUPPLY_AMT \n" + " FROM CONT_PUR_PAYPLAN \n" + " WHERE 1=1 \n" + " AND PUR_CONT_MOD_ID = '"+purContModId+"' \n" + " GROUP BY PUR_CONT_MOD_ID \n" + " ) A, \n" + " CONT_PUR_PAYPLAN B \n" + " WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n" + " AND A.PAY_SEQ = B.PAY_SEQ \n" ; System.out.println(sqlstr); search.setStatement(sqlstr); rowSet = search.execute(); if(rowSet.size() > 0){ row = RowSetUtility.getValueObject(rowSet.getRow(0)); } }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getContPurPayplanMax() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return row; } public ValueObject getContPurPayplan01(String purContModId) throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; ValueObject row = null; try{ sqlstr =" SELECT PUR_CONT_MOD_ID, PAY_SEQ, PUR_CONT_ID, PAY_SUPPLY_AMT, GUAR_YN, WARR_GUBUN, VP_YN, PAY_DIV_CD, PAYREQ_ID, PAY_YN \n" + " FROM CONT_PUR_PAYPLAN \n" + " WHERE PAY_DIV_CD = '01' \n" + " AND PUR_CONT_MOD_ID = '"+purContModId+"' \n" ; System.out.println(sqlstr); search.setStatement(sqlstr); rowSet = search.execute(); if(rowSet.size() > 0){ row = RowSetUtility.getValueObject(rowSet.getRow(0)); } }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getContPurPayplan() : " + 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() + " --> updatePur1030t() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; }finally{ if(row == null) row = new ValueObject(); } return resultId; } 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; } /** * 대금이 선급금일시 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; } public void updatePayPlan(Connection con, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(con); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); //구매)선급내역에 상태값 변경 sqlstr.append(" UPDATE CONT_PUR_PAYPLAN SET \n"); sqlstr.append(" PAY_YN = 'N', \n"); sqlstr.append(" PAY_SUPPLY_AMT = '0' \n"); sqlstr.append(" WHERE PUR_CONT_ID = ? AND PAY_DIV_CD = '01' \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(vo.get("PUR_CONT_ID")); persistent.execute(); }catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updatePayPlan() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } /** * 대금계획 추가 * @param connection * @param valueObject * @throws Exception */ public boolean savePlanData(Connection connection, ValueObject vo, String amt) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sql = new StringBuffer(); try { sql.append(" INSERT \n "); sql.append(" INTO CONT_PUR_PAYPLAN \n "); sql.append(" ( \n "); sql.append(" PUR_CONT_MOD_ID \n "); sql.append(" ,PAY_SEQ \n "); sql.append(" ,PUR_CONT_ID \n "); sql.append(" ,PAY_SUPPLY_AMT \n "); sql.append(" ,PAY_DIV_CD \n "); sql.append(" ,PAY_YN \n "); sql.append(" ,CRE_BY \n "); sql.append(" ,CRE_DATE \n "); sql.append(" ) \n "); sql.append(" VALUES \n "); sql.append(" ( ? \n "); //pur_cont_mod_id sql.append(" , (select nvl(max(pay_seq), 0) + 1 from CONT_PUR_PAYPLAN where PUR_CONT_MOD_ID = ?) \n "); //pay_seq sql.append(" , ? \n "); //pur_cont_id sql.append(" , ? \n "); //pay_supply_amt sql.append(" , '01' \n "); //pay_div_cd sql.append(" , 'Y' \n "); //pay_yn sql.append(" , '99996' \n "); sql.append(" , SYSDATE \n "); sql.append(" ) \n "); //WAFLogger.debug("DDDDDDDDDDDDDDDD" + sqlParams.get("PUR_CONT_MOD_ID")); persistent.setStatement(sql.toString()); int paramIdx = 1; persistent.addParameter(vo.get("PUR_CONT_MOD_ID")); persistent.addParameter(vo.get("PUR_CONT_MOD_ID")); persistent.addParameter(vo.get("PUR_CONT_ID")); persistent.addParameter(amt); persistent.execute(); return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> savePlanData() : " + e.getMessage()); WAFLogger.error("SQL : " + sql); WAFLogger.error(e); throw e; } } //20115.10.6 선급완료후 email발송 public void Send(String PUR_CONT_MOD_ID) throws Exception { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); RowSet rsUserInfo = getUserInfo(PUR_CONT_MOD_ID); boolean email = false; StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); if( rsUserInfo.size() > 0 && !rsUserInfo.getRow(0).get("MAT_MAIL").isEmpty()){ mailtitle.append("* 선급금 신청 되었습니다."); mailtitlein.append("* 선급금 신청 되었습니다."); mailContent.append(""); mailContent.append("선급금 신청 되었습니다. "); mailContent.append("경영정보의 전표마감 현황 확인 바랍니다.
"); mailContent.append("1. 현 장: (" + rsUserInfo.getRow(0).get("DEPT_CD") + ")" + rsUserInfo.getRow(0).get("DEPT_NAME") + "
"); mailContent.append("2. 계약번호: " + rsUserInfo.getRow(0).get("CONT_NO") + "
"); mailContent.append("3. 계 약 명: " + rsUserInfo.getRow(0).get("CONT_NAME") + "
"); mailContent.append("
"); mailContent.append(""); email = true; if(email){ //구매팀, 회계팀 messageSend.sendEmail(rsUserInfo.getRow(0).get("MAT_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); messageSend.sendEmail(rsUserInfo.getRow(0).get("ACT_MAIL1"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); if (!rsUserInfo.getRow(0).get("ACT_MAIL2").equals("NA") ){ messageSend.sendEmail(rsUserInfo.getRow(0).get("ACT_MAIL2"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } } } } //2015.10.6 구매, 회계 담당자 정보 private RowSet getUserInfo(String PUR_CONT_MOD_ID) throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" SELECT B.EMAIL MAT_MAIL \n"); sqlstr.append(" ,NVL(D.CHG_CODE08, 'NA') ACT_MAIL1 \n"); sqlstr.append(" ,NVL(D.CHG_CODE09, 'NA') ACT_MAIL2 \n"); sqlstr.append(" ,A.DEPT_CD DEPT_CD \n"); sqlstr.append(" ,A.DEPT_NAME DEPT_NAME \n"); sqlstr.append(" ,A.ORD_DOC_NO CONT_NO \n"); sqlstr.append(" ,A.CST_DOC_NAME CONT_NAME \n"); sqlstr.append(" FROM PUR2000T A \n"); sqlstr.append(" ,CONT_PUR_SUPP B \n"); sqlstr.append(" ,ETEC_ERPIF.ACZ10100 C \n"); sqlstr.append(" ,EBID_COM911T D \n"); sqlstr.append(" WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n"); sqlstr.append(" AND A.DEPT_CD = C.DEPT_CD \n"); sqlstr.append(" AND C.AC_UNIT_CD = D.DETAILCD \n"); sqlstr.append(" AND D.BASECD = '101' \n"); sqlstr.append(" AND A.PUR_CONT_MOD_ID = '" + PUR_CONT_MOD_ID + "' \n"); sqlstr.append(" AND B.SIGN_SEQ = 1 \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } 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 String getErpPaySeq(String YY_PREPAY) throws Exception { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); String rtnStr = ""; try{ sqlstr.append("SELECT TO_CHAR(NVL(MAX(TO_NUMBER(NO_PREPAY)),0)+1,'FM00000') AS NO_PREPAY \n"); sqlstr.append("FROM WM_E_EXECUTE_PREPAY@ERPIU \n"); sqlstr.append("WHERE YY_PREPAY = '"+YY_PREPAY+"' \n"); System.out.println(sqlstr.toString()); search.setStatement(sqlstr.toString()); rowSet = search.execute(); if( rowSet != null ) { rtnStr = rowSet.getRow(0).get("NO_PREPAY"); } }catch(Exception e){ WAFLogger.error(this.getClass().getName() + " --> getErpPaySeq() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } return rtnStr; } }