/**
* @(#)file SignThreadBiz.java
* @(#)author DaeJin Lee
* @(#)version 1.0
* @(#)date Jun 10, 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;
import java.sql.Connection;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowImpl;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFConnection;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
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.util.ValueObject;
import java.text.SimpleDateFormat;
import java.util.Date;
import kr.co.udapsoft.common.sender.MessageSend;
import com.udapsoft.waf.system.HandlerStorage;
import oracle.toplink.internal.ox.StrBuffer;
/**
*
* @version 1.0
* @author DaeJin Lee
*/
public class CommonMailThreadBiz {
private WAFConnection conn = null;
public CommonMailThreadBiz() {
this.conn = new WAFConnectionImpl();
}
/**
* 전자결재 처리결과 수신 Biz
*/
public void doBiz() throws Exception {
try {
long time = System.currentTimeMillis();
SimpleDateFormat dayTime = new SimpleDateFormat("HHmm");
String str = dayTime.format(new Date(time));
if (str.equals("0800") || str.equals("0801")){ //오전08시 실행.
try{
RowSet rowTread = getBidThread();
sendBidMail(rowTread); //당일 마감입찰
RowSet rowCont = getContThread();
sendContMail(rowCont); //계약 20일 초과 하도급미발행
//2019.11.29 추가 Start
//RowSet rowPurContEnd = getPurContEndThread();
//sendContEndMail(rowPurContEnd); //구매계약 만료 30일전 알림
RowSet rowOutContEnd = getOutContEndThread();
sendContEndMail(rowOutContEnd); //외주계약 만료 30일전 알림
//2019.11.29 추가 End
}
catch(Exception se){
WAFLogger.error(this.getClass().getName() + " --> 입찰마감오류() : " + se.getMessage());
WAFLogger.error(se);
throw se;
}
}
try{
conn.begin();
//구매변경계약 업데이트
Row row = new RowImpl();
String maxPurContid = new String();
RowSet rowChgTread = getChgContThread();
for ( int i = 0 ; i < rowChgTread.size() ; i ++ ) {
row = rowChgTread.getRow(i); //구매 변경계약 PUR2000T로 INSERT
maxPurContid = getMaxPurCntId();
addChangeContItem(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID")); //변경계약 아이템 저장
addChangeContPur(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID") ); //변경 계약 추가
addChangeContSupport(conn.getConnection(), row.get("PUR_CONT_MOD_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID")); //변경 계약 업체추가
}
RowSet rowMail = getMailThread(); //발주의뢰, 변경계약 메일알림
sendMail(rowMail);
updateMail(conn.getConnection(), rowMail);
}
catch(Exception se){
WAFLogger.error(this.getClass().getName() + " --> doBiz() : " + se.getMessage());
WAFLogger.error(se);
conn.setRollback();
throw se;
} finally {
conn.end();
}
} catch(Exception se) {
WAFLogger.error(this.getClass().getName() + " --> setInsert() : " + se.getMessage());
WAFLogger.error(se);
throw se;
}
}
private RowSet getBidThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
//개찰알림메일
sqlstr.append(" SELECT A.DEPT_CD \n");
sqlstr.append(" ,A.DEPT_NM \n");
sqlstr.append(" ,A.BID_MASTER_NM \n");
sqlstr.append(" ,A.BID_NO \n");
sqlstr.append(" ,A.BID_DEG \n");
sqlstr.append(" ,TO_CHAR(A.BID_TO_DT , 'YYYY-MM-DD HH24:MI') BID_TO_DT \n");
sqlstr.append(" ,A.PUREMPNO \n");
sqlstr.append(" ,B.USER_SABUN \n");
sqlstr.append(" ,B.USER_NAME \n");
sqlstr.append(" ,B.HAND_TEL_NO \n");
sqlstr.append(" ,B.E_MAIL \n");
sqlstr.append(" ,A.OPEN_EMPNM \n");
sqlstr.append(" FROM BID_MASTER A \n");
sqlstr.append(" ,PUB0120T B \n");
sqlstr.append(" WHERE A.PUREMPNO = B.ACCT_ID \n");
sqlstr.append(" AND A.ONBID_YN = 'Y' \n");
sqlstr.append(" AND A.STATUS_CD = '04' \n");
sqlstr.append(" AND DEMANDED_KIND != 'O' \n");
sqlstr.append(" AND TO_CHAR(BID_TO_DT, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD') \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;
}
}
private RowSet getContThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
//하도급 지급보증 발급. 계약일로 부터 20일 .
sqlstr.append("SELECT A.DEPT_CD \n");
sqlstr.append(" ,A.DEPT_NAME \n");
sqlstr.append(" ,A.ORD_DOC_NO \n");
sqlstr.append(" ,A.CST_DOC_NAME \n");
sqlstr.append(" ,TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') ORD_CON_DATE \n");
sqlstr.append(" ,A.CON_CHARGE_ID \n");
sqlstr.append(" ,B.USER_NAME \n");
sqlstr.append(" ,B.HAND_TEL_NO \n");
sqlstr.append(" ,B.E_MAIL \n");
sqlstr.append(" ,C.VENDCD \n");
sqlstr.append(" ,C.MEMBER_NAME \n");
sqlstr.append(" ,D.PUR_CONT_MOD_ID \n");
sqlstr.append(" FROM PUR2000T A \n");
sqlstr.append(" ,PUB0120T B \n");
sqlstr.append(" ,CONT_PUR_SUPP C \n");
sqlstr.append(" ,CONT_PUR_WARR D \n");
sqlstr.append(" WHERE A.CON_CHARGE_ID = B.ACCT_ID \n");
sqlstr.append(" AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID \n");
sqlstr.append(" AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+) \n");
sqlstr.append(" AND A.STATUS_CD = '50' \n");
sqlstr.append(" AND A.TEMPLATE_CD = '15' \n");
sqlstr.append(" AND C.SIGN_SEQ = 2 \n");
sqlstr.append(" AND D.WARR_GUBUN(+) = '2' \n");
sqlstr.append(" AND D.PUR_CONT_MOD_ID IS NULL \n");
sqlstr.append(" AND TO_DATE(A.ORD_CON_DATE) + 19 > SYSDATE \n");//계약일 20일이후 부터 . 금액제한 확인
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;
}
}
private RowSet getMailThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
//발주의뢰, 변경계약 메일발송
sqlstr.append("SELECT A.MAIL_ID --발주의뢰 \n");
sqlstr.append(" ,A.SEND_GB \n");
sqlstr.append(" ,GET_DEPT_NM(B.DEPT_CD) DEPT_NM \n");
sqlstr.append(" ,B.DEMANDED_NO DEM_NO \n");
sqlstr.append(" ,B.DEMANDED_TITLE DEM_TITLE \n");
sqlstr.append(" ,A.ACCT_ID \n");
sqlstr.append(" ,A.ADDR E_MAIL \n");
sqlstr.append(" FROM T_MAIL A \n");
sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n");
sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n");
sqlstr.append(" AND A.ATTR_1 = 0 \n");
sqlstr.append(" AND A.SEND_GB = 'ORD' \n");
sqlstr.append(" UNION ALL --외주변경 \n");
sqlstr.append("SELECT A.MAIL_ID \n");
sqlstr.append(" ,A.SEND_GB \n");
sqlstr.append(" ,B.DEPT_NAME DEPT_NM \n");
sqlstr.append(" ,B.CON_DOC_NO DEM_NO \n");
sqlstr.append(" ,B.BUILD_NAME DEM_TITLE \n");
sqlstr.append(" ,A.ACCT_ID \n");
sqlstr.append(" ,A.ADDR E_MAIL \n");
sqlstr.append(" FROM T_MAIL A \n");
sqlstr.append(" ,SUB2000T B \n");
sqlstr.append(" WHERE A.DEM_ID = B.SUB_CONT_MOD_ID \n");
sqlstr.append(" AND A.ATTR_1 = 0 \n");
sqlstr.append(" AND A.SEND_GB = 'OCHG' \n");
sqlstr.append(" UNION ALL --구매변경 \n");
sqlstr.append("SELECT A.MAIL_ID \n");
sqlstr.append(" ,A.SEND_GB \n");
sqlstr.append(" ,C.DEPT_NAME DEPT_NM \n");
sqlstr.append(" ,C.ORD_DOC_NO DEM_NO \n");
sqlstr.append(" ,C.CST_DOC_NAME DEM_TITLE \n");
sqlstr.append(" ,D.ACCT_ID \n");
sqlstr.append(" ,D.E_MAIL E_MAIL \n");
sqlstr.append(" FROM T_MAIL A \n");
sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n");
sqlstr.append(" ,PUR1000T C \n");
sqlstr.append(" ,PUB0120T D \n");
sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n");
sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n");
sqlstr.append(" AND C.CON_CHARGE_ID = D.ACCT_ID \n");
sqlstr.append(" AND A.ATTR_1 = 0 \n");
sqlstr.append(" AND A.SEND_GB = 'PCHG' \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;
}
}
private void updateMail(Connection connection,RowSet rowTread) throws Exception{
Persistent persistent = new PersistentImpl(connection);
StringBuffer sqlstr = new StringBuffer();
Row row = new RowImpl();
try {
sqlstr.append("UPDATE T_MAIL \n");
sqlstr.append(" SET ATTR_1 = 1 \n");
sqlstr.append(" ,UPD_DATE = SYSDATE \n");
sqlstr.append(" WHERE MAIL_ID = ? \n");
persistent.setStatement(sqlstr.toString());
for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
row = rowTread.getRow(i);
persistent.addParameter(row.get("MAIL_ID"));
persistent.execute();
persistent.clearParameters();
}
}
catch(WAFSQLException se) {
Logger.err.println("SQL : \n" + se.getStatement());
Logger.err.println("PARAM : \n" + se.getParameter());
throw se;
}
}
private void sendBidMail(RowSet rowTread) throws Exception{
try {
HandlerStorage Stroage = new HandlerStorage();
MessageSend messageSend = new MessageSend(Stroage);
StringBuffer mailtitle = new StringBuffer();
StringBuffer mailtitlein = new StringBuffer();
StringBuffer mailContent = new StringBuffer();
Row row = new RowImpl();
mailtitle.append("*[알림]금일 입찰마감 안내입니다. ");
mailtitlein.append("*[알림]금일 입찰마감 안내입니다. ");
for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("금일 입찰이 마감되니 확인 후 조치하여 주시기 바랍니다.
");
mailContent.append("
");
mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
");
mailContent.append("입찰번호: " + row.get("BID_NO") + "-" + row.get("BID_DEG") + "
");
mailContent.append("입찰명: " + row.get("BID_MASTER_NM") + "
");
mailContent.append("마감시간: " + row.get("BID_TO_DT") + "
");
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
}
mailContent = new StringBuffer();
}
}
catch(Exception e) {
Logger.err.println("CommonMailThreadBiz.sendBidMail() 실행중 오류가 발생 했습니다.\\n" + e.getMessage());
throw e;
}
}
private void sendContMail(RowSet rowTread){
try {
HandlerStorage Stroage = new HandlerStorage();
MessageSend messageSend = new MessageSend(Stroage);
StringBuffer mailtitle = new StringBuffer();
StringBuffer mailtitlein = new StringBuffer();
StringBuffer mailContent = new StringBuffer();
Row row = new RowImpl();
mailtitle.append("*[알림]하도급 지급보증 발급을 처리해 주십시요.");
mailtitlein.append("*[알림]하도급 지급보증 발급을 처리해 주십시요.");
for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("건설공사 표준하도급 계약이후 하도급지급보증 발급이 처리되지 않았습니다. 확인 후 조치하여 주시기 바랍니다.
");
mailContent.append("
");
mailContent.append("프로젝트: " + row.get("DEPT_NAME") + "
");
mailContent.append("계약번호: " + row.get("ORD_DOC_NO") + "
");
mailContent.append("계약명: " + row.get("CST_DOC_NAME") + "
");
mailContent.append("거래처: " + row.get("MEMBER_NAME") + "
");
mailContent.append("계약일자: " + row.get("ORD_CON_DATE") + "
");
mailContent.append(row.get("E_MAIL"));
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
}
mailContent = new StringBuffer();
}
}
catch(Exception e) {
Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage());
}
}
private void sendMail(RowSet rowTread){
try {
HandlerStorage Stroage = new HandlerStorage();
MessageSend messageSend = new MessageSend(Stroage);
StringBuffer mailtitle = new StringBuffer();
StringBuffer mailtitlein = new StringBuffer();
StringBuffer mailContent = new StringBuffer();
Row row = new RowImpl();
for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
row = new RowImpl();
row = rowTread.getRow(i);
if (row.get("SEND_GB").equals("ORD")){
mailtitle.append("*[알림]발주의뢰가 등록 되었습니다. ");
mailtitlein.append("*[알림]발주의뢰가 등록 되었습니다. ");
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("발주의뢰가 등록되었습니다.
");
mailContent.append("
");
mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
");
mailContent.append("발주의뢰번호: " + row.get("DEM_NO") + "
");
mailContent.append("발주의뢰명: " + row.get("DEM_TITLE") + "
");
mailContent.append("전자조달시스템의 발주의뢰 접수 확인 바랍니다.
");
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
}
mailContent = new StringBuffer();
mailtitle = new StringBuffer();
mailtitlein = new StringBuffer();
}else if(row.get("SEND_GB").equals("PCHG")){
mailtitle.append("*[알림]변경계약이 등록 되었습니다. ");
mailtitlein.append("*[알림]변경계약이 등록 되었습니다. ");
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("변경계약 요청건이 있습니다.
");
mailContent.append("
");
mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
");
mailContent.append("계약번호: " + row.get("DEM_NO") + "
");
mailContent.append("계약명: " + row.get("DEM_TITLE") + "
");
mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.
");
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
}
mailContent = new StringBuffer();
mailtitle = new StringBuffer();
mailtitlein = new StringBuffer();
}else if(row.get("SEND_GB").equals("OCHG")){
mailtitle.append("*[알림]변경계약이 등록 되었습니다. ");
mailtitlein.append("*[알림]변경계약이 등록 되었습니다. ");
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("변경계약 요청건이 있습니다.
");
mailContent.append("
");
mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
");
mailContent.append("계약번호: " + row.get("DEM_NO") + "
");
mailContent.append("계약명: " + row.get("DEM_TITLE") + "
");
mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.
");
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
}
mailContent = new StringBuffer();
mailtitle = new StringBuffer();
mailtitlein = new StringBuffer();
}
}
}
catch(Exception e) {
Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage());
}
}
private RowSet getChgContThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
//구매변경계약
sqlstr.append(" SELECT A.DEM_ID DEMANDED_ID \n");
sqlstr.append(" ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n");
sqlstr.append(" FROM T_MAIL A \n");
sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n");
sqlstr.append(" ,PUR2000T C \n");
sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n");
sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n");
sqlstr.append(" AND A.SEND_GB = 'PCHG' \n");
sqlstr.append(" AND A.ATTR_1 = 0 \n");
sqlstr.append(" AND C.STATUS_CD = '50' \n");
sqlstr.append(" AND NVL(DIV_PO_YN,'N') != 'Y' \n"); //단가계약 아닌자료
sqlstr.append(" GROUP BY A.DEM_ID \n");
//단가계약 추가 2019.04.15
sqlstr.append(" UNION ALL \n");
sqlstr.append(" SELECT MAX(A.DEM_ID) DEMANDED_ID \n");
sqlstr.append(" ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n");
sqlstr.append(" FROM T_MAIL A \n");
sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n");
sqlstr.append(" ,PUR2000T C \n");
sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n");
sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n");
sqlstr.append(" AND A.SEND_GB = 'PCHG' \n");
sqlstr.append(" AND A.ATTR_1 = 0 \n");
sqlstr.append(" AND C.STATUS_CD = '50' \n");
sqlstr.append(" AND DIV_PO_YN = 'Y' \n"); //단가계약 자료
sqlstr.append(" GROUP BY C.PUR_CONT_ID \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;
}
}
private String getMaxPurCntId() {
Search search = new SearchImpl();
String sqlstr = "";
try {
sqlstr = "SELECT PUR2000T_S.NEXTVAL AS MAX_PUR_CONT_MOD_ID \n"
+ " FROM DUAL\n";
search.setStatement(sqlstr);
return search.execute().getRow(0).get("MAX_PUR_CONT_MOD_ID");
}
catch(Exception e) {
WAFLogger.error(this.getClass().getName() + " --> getMaxPurCntId() : " + e.getMessage());
WAFLogger.error("SQL : " + search.getStatement());
WAFLogger.error(e);
return null;
}
}
public boolean addChangeContPur(Connection connection, String DEMANDED_ID, String maxPurContid, String prePurContid) throws Exception {
Persistent persistent = new PersistentImpl(connection);
StrBuffer sqlstr = new StrBuffer();
try {
sqlstr.append(" INSERT INTO PUR2000T \n");
sqlstr.append(" ( PUR_CONT_MOD_ID \n");
sqlstr.append(" , PUR_CONT_ID \n");
sqlstr.append(" , MOD_NO \n");
sqlstr.append(" , MOD_GB \n");
sqlstr.append(" , MOD_RSN \n");
sqlstr.append(" , MOD_REQ_DATE \n");
sqlstr.append(" , MOD_CORP_RSN \n");
sqlstr.append(" , DEPT_CD \n");
sqlstr.append(" , DEPT_NAME \n");
sqlstr.append(" , CON_GB \n");
sqlstr.append(" , ORD_DOC_NO \n");
sqlstr.append(" , CST_DOC_NAME \n");
sqlstr.append(" , REQ_DEPT_CD \n");
sqlstr.append(" , REQ_DATE \n");
sqlstr.append(" , REQ_USERID \n");
sqlstr.append(" , REQ_USERNM \n");
sqlstr.append(" , REQ_DOC_NO \n");
sqlstr.append(" , CST_DATE \n");
sqlstr.append(" , CON_IN_DATE \n");
sqlstr.append(" , ELEC_CON_YN \n");
sqlstr.append(" , ORD_CON_DATE \n");
sqlstr.append(" , PAY_COND_GB \n");
sqlstr.append(" , PAY_CONTENT \n");
sqlstr.append(" , LEAD_COND_GB \n");
sqlstr.append(" , LEAD_PLACE \n");
sqlstr.append(" , CORP_ID \n");
sqlstr.append(" , ORD_START_DATE \n");
sqlstr.append(" , ORD_END_DATE \n");
sqlstr.append(" , ORD_SEND_DATE \n");
sqlstr.append(" , ORD_AMT \n");
sqlstr.append(" , ORD_SUPPLY_AMT \n");
sqlstr.append(" , ORD_SURTAX_AMT \n");
sqlstr.append(" , PREPAY_AMT \n");
sqlstr.append(" , MID_AMT \n");
sqlstr.append(" , RMDR_AMT \n");
sqlstr.append(" , PREPAY_EXE_YN \n");
sqlstr.append(" , PREPAY_GUAR_AMT \n");
sqlstr.append(" , CON_GUAR_AMT_RATE \n");
sqlstr.append(" , CON_GUAR_AMT \n");
sqlstr.append(" , CON_GUAR_TERM \n");
sqlstr.append(" , DEF_GUAR_AMT_RATE \n");
sqlstr.append(" , DEF_GUAR_AMT \n");
sqlstr.append(" , DEF_GUAR_TERMS \n");
sqlstr.append(" , DEF_GUAR_TERMS_TXT \n");
sqlstr.append(" , DEF_GUAR_TERMS2 \n");
sqlstr.append(" , DEF_GUAR_TERMS_TXT2 \n");
sqlstr.append(" , DEL_RATE_GB \n");
sqlstr.append(" , CON_CHARGE_ID \n");
sqlstr.append(" , CON_CHARGE_NAME \n");
sqlstr.append(" , CON_DISPLAY_TXT \n");
sqlstr.append(" , ADD_FILE_KIND \n");
sqlstr.append(" , CORP_SIGN_DATE \n");
sqlstr.append(" , COMPANY_SIGN_DATE \n");
sqlstr.append(" , ENT_CLS_YN \n");
sqlstr.append(" , PAY_GROUP \n");
sqlstr.append(" , PAYMENT_TERMS \n");
sqlstr.append(" , ACCT_GB \n");
sqlstr.append(" , GUA_GB \n");
sqlstr.append(" , CURRENCY_GB \n");
sqlstr.append(" , PAY_ITEM \n");
sqlstr.append(" , COST_CODE \n");
sqlstr.append(" , COST_TYPE \n");
sqlstr.append(" , ONSHORE_CD \n");
sqlstr.append(" , CONTRACTID \n");
sqlstr.append(" , G_ID \n");
sqlstr.append(" , P_ID \n");
sqlstr.append(" , PREPAY_AMT_RATE \n");
sqlstr.append(" , PREPAY_PAYMENT_NM \n");
sqlstr.append(" , MID_AMT_RATE \n");
sqlstr.append(" , MID_PAYMENT_NM \n");
sqlstr.append(" , MID_PAYMENT_TXT \n");
sqlstr.append(" , RMDR_AMT_RATE \n");
sqlstr.append(" , RMDR_PAYMENT_NM \n");
sqlstr.append(" , RMDR_PAYMENT_TXT \n");
sqlstr.append(" , PREPAY_GUAR_AMT_RATE \n");
sqlstr.append(" , OVERSEA_GB \n");
sqlstr.append(" , PUR_LIST_GB \n");
sqlstr.append(" , CON_RUN_STS \n");
sqlstr.append(" , BID_NO \n");
sqlstr.append(" , BID_DEG \n");
sqlstr.append(" , STATUS_CD \n");
sqlstr.append(" , TEMPLATE_CD \n");
sqlstr.append(" , MODI_REQ_REASON \n");
sqlstr.append(" , MODI_REQ_DT \n");
sqlstr.append(" , CON_CLS_YN \n");
sqlstr.append(" , PAY_DIV \n");
sqlstr.append(" , DEM_ID \n");
sqlstr.append(" , IN_SPE_INFO \n");
sqlstr.append(" , IN_CHG_INFO \n");
sqlstr.append(" , FRST_CON_AMT \n");
sqlstr.append(" , FRST_EXE_AMT \n");
sqlstr.append(" , CHG_CON_AMT \n");
sqlstr.append(" , CHG_EXE_AMT \n");
sqlstr.append(" , CRE_BY \n");
sqlstr.append(" , CRE_DATE \n");
sqlstr.append(" , DIV_PO_YN ,ERP_NO_ECUT ) \n"); //2019.04.26 신용길부장님 요청. 같은계약은 ERP_NO_ECUT동일하게.
sqlstr.append(" SELECT ? \n");
sqlstr.append(" , A.PUR_CONT_ID \n");
sqlstr.append(" , MOD_NO + 1 \n");
sqlstr.append(" , B.MOD_GB \n");
sqlstr.append(" , NULL \n");
sqlstr.append(" , NULL \n");
sqlstr.append(" , NULL \n");
sqlstr.append(" , A.DEPT_CD \n");
sqlstr.append(" , A.DEPT_NAME \n");
sqlstr.append(" , A.CON_GB \n");
sqlstr.append(" , A.ORD_DOC_NO \n");
sqlstr.append(" , A.CST_DOC_NAME \n");
sqlstr.append(" , A.REQ_DEPT_CD \n");
sqlstr.append(" , A.REQ_DATE \n");
sqlstr.append(" , A.REQ_USERID \n");
sqlstr.append(" , A.REQ_USERNM \n");
sqlstr.append(" , A.REQ_DOC_NO \n");
sqlstr.append(" , A.CST_DATE \n");
sqlstr.append(" , A.CON_IN_DATE \n");
sqlstr.append(" , A.ELEC_CON_YN \n");
sqlstr.append(" , A.ORD_CON_DATE \n");
sqlstr.append(" , A.PAY_COND_GB \n");
sqlstr.append(" , A.PAY_CONTENT \n");
sqlstr.append(" , A.LEAD_COND_GB \n");
sqlstr.append(" , A.LEAD_PLACE \n");
sqlstr.append(" , A.CORP_ID \n");
sqlstr.append(" , A.ORD_START_DATE \n");
sqlstr.append(" , TO_DATE(B.AFT_CONT_DATE,'YYYYMMDD') AS ORD_END_DATE \n");
sqlstr.append(" , A.ORD_SEND_DATE \n");
sqlstr.append(" , CASE WHEN A.ORD_SURTAX_AMT > 0 THEN SUM_ITEM_AMT + TRUNC(SUM_ITEM_AMT / 10, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) -- 발주계약금액 \n");
sqlstr.append(" ELSE SUM_ITEM_AMT END ORD_AMT -- 발주계약금액 \n");
sqlstr.append(" , SUM_ITEM_AMT \n");
sqlstr.append(" , CASE WHEN A.ORD_SURTAX_AMT > 0 THEN TRUNC(SUM_ITEM_AMT / 10,F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) -- 부가세 \n");
sqlstr.append(" ELSE 0 END ORD_SURTAX_AMT -- 부가세 \n");
sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (PREPAY_AMT_RATE / 100), F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) PREPAY_AMT -- 선급금 \n");
sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (MID_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) MID_AMT -- 중도금 \n");
sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (RMDR_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) RMDR_AMT -- 잔금 \n");
sqlstr.append(" , A.PREPAY_EXE_YN \n");
sqlstr.append(" , A.PREPAY_GUAR_AMT \n");
sqlstr.append(" , A.CON_GUAR_AMT_RATE \n");
sqlstr.append(" , A.CON_GUAR_AMT \n");
sqlstr.append(" , A.CON_GUAR_TERM \n");
sqlstr.append(" , A.DEF_GUAR_AMT_RATE \n");
sqlstr.append(" , A.DEF_GUAR_AMT \n");
sqlstr.append(" , A.DEF_GUAR_TERMS \n");
sqlstr.append(" , A.DEF_GUAR_TERMS_TXT \n");
sqlstr.append(" , A.DEF_GUAR_TERMS2 \n");
sqlstr.append(" , A.DEF_GUAR_TERMS_TXT2 \n");
sqlstr.append(" , A.DEL_RATE_GB \n");
sqlstr.append(" , A.CON_CHARGE_ID \n");
sqlstr.append(" , A.CON_CHARGE_NAME \n");
sqlstr.append(" , A.CON_DISPLAY_TXT \n");
sqlstr.append(" , A.ADD_FILE_KIND \n");
sqlstr.append(" , A.CORP_SIGN_DATE \n");
sqlstr.append(" , A.COMPANY_SIGN_DATE \n");
sqlstr.append(" , A.ENT_CLS_YN \n");
sqlstr.append(" , A.PAY_GROUP \n");
sqlstr.append(" , A.PAYMENT_TERMS \n");
sqlstr.append(" , A.ACCT_GB \n");
sqlstr.append(" , A.GUA_GB \n");
sqlstr.append(" , A.CURRENCY_GB \n");
sqlstr.append(" , A.PAY_ITEM \n");
sqlstr.append(" , A.COST_CODE \n");
sqlstr.append(" , A.COST_TYPE \n");
sqlstr.append(" , A.ONSHORE_CD \n");
sqlstr.append(" , A.CONTRACTID \n");
sqlstr.append(" , A.G_ID \n");
sqlstr.append(" , A.P_ID \n");
sqlstr.append(" , A.PREPAY_AMT_RATE \n");
sqlstr.append(" , A.PREPAY_PAYMENT_NM \n");
sqlstr.append(" , A.MID_AMT_RATE \n");
sqlstr.append(" , A.MID_PAYMENT_NM \n");
sqlstr.append(" , A.MID_PAYMENT_TXT \n");
sqlstr.append(" , A.RMDR_AMT_RATE \n");
sqlstr.append(" , A.RMDR_PAYMENT_NM \n");
sqlstr.append(" , A.RMDR_PAYMENT_TXT \n");
sqlstr.append(" , A.PREPAY_GUAR_AMT_RATE \n");
sqlstr.append(" , A.OVERSEA_GB \n");
sqlstr.append(" , A.PUR_LIST_GB \n");
sqlstr.append(" , A.CON_RUN_STS \n");
sqlstr.append(" , A.BID_NO \n");
sqlstr.append(" , A.BID_DEG \n");
sqlstr.append(" , '00' \n");
sqlstr.append(" , NULL \n");
sqlstr.append(" , A.MODI_REQ_REASON \n");
sqlstr.append(" , A.MODI_REQ_DT \n");
sqlstr.append(" , 1 \n");
sqlstr.append(" , A.PAY_DIV \n");
sqlstr.append(" , B.DEMANDED_ID \n");
sqlstr.append(" , B.P_REMARK \n");
sqlstr.append(" , B.S_REMARK \n");
sqlstr.append(" , A.CHG_CON_AMT \n");
sqlstr.append(" , B.BEF_BUG_AMT \n");
sqlstr.append(" , A.CHG_CON_AMT \n");
sqlstr.append(" , B.AFT_BUG_AMT \n");
sqlstr.append(" , '11111' \n");
sqlstr.append(" , SYSDATE \n");
sqlstr.append(" , A.DIV_PO_YN,A.ERP_NO_ECUT \n");
sqlstr.append(" FROM PUR2000T A \n");
sqlstr.append(" , PMM_DEMANDED_REQUEST B \n");
sqlstr.append(" , ( SELECT PUR_CONT_MOD_ID \n");
sqlstr.append(" , SUM(ITEM_AMT) AS SUM_ITEM_AMT \n");
sqlstr.append(" FROM CONT_PUR_ITEM \n");
sqlstr.append(" WHERE PUR_CONT_MOD_ID = ? \n");
sqlstr.append(" GROUP BY PUR_CONT_MOD_ID ) C \n");
sqlstr.append(" WHERE A.REQ_DOC_NO = B.DEMANDED_NO \n");
sqlstr.append(" AND A.PUR_CONT_MOD_ID = ? \n");
sqlstr.append(" AND B.DEMANDED_ID = ? \n");
persistent.setStatement(sqlstr.toString());
persistent.addParameter(maxPurContid);
persistent.addParameter(prePurContid);
persistent.addParameter(prePurContid);
persistent.addParameter(DEMANDED_ID);
//System.out.println(sqlstr.toString());
persistent.execute();
return true;
}
catch(Exception e) {
WAFLogger.error(this.getClass().getName() + " --> addChangeContPur() : " + e.getMessage());
WAFLogger.error("SQL : " + sqlstr);
WAFLogger.error(e);
throw e;
}
}
public boolean addChangeContSupport(Connection connection, String PUR_CONT_MOD_ID, String maxPurContid, String prePurContid ) throws Exception {
//WAFLogger.debug("valueObject : " + valueObject);
Persistent persistent = new PersistentImpl(connection);
StrBuffer sqlstr = new StrBuffer();
try {
sqlstr.append("INSERT INTO CONT_PUR_SUPP \n");
sqlstr.append(" ( PUR_CONT_MOD_ID \n");
sqlstr.append(" , CORP_ID \n");
sqlstr.append(" , SIGN_SEQ \n");
sqlstr.append(" , VENDCD \n");
sqlstr.append(" , MEMBER_NAME \n");
sqlstr.append(" , BOSS_NAME \n");
sqlstr.append(" , POST_CODE \n");
sqlstr.append(" , ADDRESS \n");
sqlstr.append(" , TEL_NUM \n");
sqlstr.append(" , MEMBER_SLNO \n");
sqlstr.append(" , USER_NAME \n");
sqlstr.append(" , DIVISION \n");
sqlstr.append(" , POSITION \n");
sqlstr.append(" , HP \n");
sqlstr.append(" , EMAIL \n");
sqlstr.append(" , CRE_BY \n");
sqlstr.append(" , CRE_DATE ) \n");
sqlstr.append("SELECT ? \n");
sqlstr.append(" , A.CORP_ID \n");
sqlstr.append(" , A.SIGN_SEQ \n");
sqlstr.append(" , A.VENDCD \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.CORP_KOR, A.MEMBER_NAME), C.CORP_KOR) AS MEMBER_NAME \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.REP_NAME, A.BOSS_NAME), C.REP_NAME) AS BOSS_NAME \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.POST_NO, A.POST_CODE), C.POST_NO) AS POST_CODE \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.ADDRESS, A.ADDRESS), C.ADDRESS) AS ADDRESS \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.CORP_TEL, A.TEL_NUM), A.TEL_NUM) AS TEL_NUM \n");
sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.COPR_NO, A.MEMBER_SLNO), C.COPR_NO) AS MEMBER_SLNO \n");
sqlstr.append(" , A.USER_NAME \n");
sqlstr.append(" , A.DIVISION \n");
sqlstr.append(" , A.POSITION \n");
sqlstr.append(" , A.HP \n");
sqlstr.append(" , A.EMAIL \n");
sqlstr.append(" , '11111' \n");
sqlstr.append(" , SYSDATE \n");
sqlstr.append(" FROM CONT_PUR_SUPP A, SUPP_INFO B , HEAD_OFFICE_INFO C \n");
sqlstr.append(" WHERE A.CORP_ID = B.CORP_ID (+) \n");
sqlstr.append(" AND A.PUR_CONT_MOD_ID= ? \n");
persistent.setStatement(sqlstr.toString());
persistent.addParameter(maxPurContid);
persistent.addParameter(PUR_CONT_MOD_ID);
persistent.execute();
return true;
}
catch(Exception e) {
WAFLogger.error(this.getClass().getName() + " --> addChangeContSupport() : " + e.getMessage());
WAFLogger.error("SQL : " + sqlstr);
WAFLogger.error(e);
throw e;
}
}
/**
* 변경 계약 업체 정보 추가
* @param connection
* @param valueObject
* @throws Exception
*/
public boolean addChangeContItem(Connection connection, String demanded_id, String maxPurContid, String prePurContid) throws Exception {
//WAFLogger.debug("valueObject : " + valueObject);
Persistent persistent = new PersistentImpl(connection);
StrBuffer sqlstr = new StrBuffer();
try {
sqlstr.append("INSERT INTO CONT_PUR_ITEM \n");
sqlstr.append(" ( PUR_CONT_MOD_ID \n");
sqlstr.append(" , MATERIAL_CD \n");
sqlstr.append(" , PUR_CONT_ID \n");
sqlstr.append(" , ITEM_NAME \n");
sqlstr.append(" , ITEM_SPEC \n");
sqlstr.append(" , ITEM_UNIT \n");
sqlstr.append(" , ITEM_QTY \n");
sqlstr.append(" , ITEM_PRICE \n");
sqlstr.append(" , ITEM_AMT \n");
sqlstr.append(" , CRE_BY \n");
sqlstr.append(" , CRE_DATE \n");
sqlstr.append(" ) \n");
sqlstr.append("SELECT ? \n");
sqlstr.append(" , B.MATERIAL_CD \n");
sqlstr.append(" , C.PUR_CONT_ID \n");
sqlstr.append(" , D.MATERIAL_NAME AS ITEM_NAME \n");
sqlstr.append(" , D.MATERIAL_SPEC AS ITEM_SPEC \n");
sqlstr.append(" , D.UNIT_KIND AS ITEM_UNIT \n");
sqlstr.append(" , DEMANDED_QTY \n");
sqlstr.append(" , DEMANDED_PRI \n");
sqlstr.append(" , DEMANDED_AMT \n");
sqlstr.append(" , '11111' \n");
sqlstr.append(" , SYSDATE \n");
sqlstr.append(" FROM PMM_DEMANDED_REQUEST A \n");
sqlstr.append(" , PMM_DEMANDED_ITEM B \n");
sqlstr.append(" , PUR2000T C \n");
sqlstr.append(" , PMM_COMM_MATERIAL_MASTER D \n");
sqlstr.append(" ,(SELECT MAX(A1.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n");
sqlstr.append(" FROM PUR2000T A1, PMM_DEMANDED_REQUEST B1 \n");
sqlstr.append(" WHERE A1.REQ_DOC_NO = B1.DEMANDED_NO \n");
sqlstr.append(" AND A1.STATUS_CD = '50' \n");
sqlstr.append(" AND B1.DEMANDED_ID = ? ) E \n");
sqlstr.append(" WHERE A.DEMANDED_ID = B.DEMANDED_ID \n");
sqlstr.append(" AND A.DEMANDED_NO = C.REQ_DOC_NO \n");
sqlstr.append(" AND C.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID \n");
sqlstr.append(" AND B.MATERIAL_CD = D.MATERIAL_CD \n");
sqlstr.append(" AND A.DEMANDED_ID= ? \n");
persistent.setStatement(sqlstr.toString());
persistent.addParameter(maxPurContid);
persistent.addParameter(demanded_id);
persistent.addParameter(demanded_id);
persistent.execute();
return true;
}
catch(Exception e) {
WAFLogger.error(this.getClass().getName() + " --> addChangeContItem() : " + e.getMessage());
WAFLogger.error("SQL : " + sqlstr);
WAFLogger.error(e);
throw e;
}
}
private RowSet getPurContEndThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
sqlstr.append(" -- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n");
sqlstr.append(" SELECT A.CON_DOC_NO \n");
sqlstr.append(" , A.DEPT_NAME \n");
sqlstr.append(" , A.BUILD_NAME \n");
sqlstr.append(" , A.END_WORK_DATE \n");
sqlstr.append(" , B.MEMBER_NAME \n");
sqlstr.append(" , C.E_MAIL \n");
sqlstr.append(" FROM ( \n");
sqlstr.append(" SELECT A.PUR_CONT_MOD_ID \n");
sqlstr.append(" ,A.REQ_USERID \n");
sqlstr.append(" ,A.ORD_DOC_NO AS CON_DOC_NO \n");
sqlstr.append(" ,A.DEPT_NAME \n");
sqlstr.append(" ,A.CST_DOC_NAME AS BUILD_NAME \n");
sqlstr.append(" ,'~ ' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n");
sqlstr.append(" FROM PUR2000T A \n");
sqlstr.append(" , (SELECT PUR_CONT_ID, MAX(MOD_NO) AS MOD_NO \n");
sqlstr.append(" FROM PUR2000T \n");
sqlstr.append(" WHERE STATUS_CD = '50' \n");
sqlstr.append(" AND PUR_CONT_ID IN (SELECT PUR_CONT_ID FROM PUR2000T WHERE TEMPLATE_CD = '15') -- 건설공사 표준하도급계약서 \n");
sqlstr.append(" GROUP BY PUR_CONT_ID) B \n");
sqlstr.append(" WHERE A.PUR_CONT_ID = B.PUR_CONT_ID \n");
sqlstr.append(" AND A.MOD_NO = B.MOD_NO \n");
sqlstr.append(" AND A.STATUS_CD = '50' -- 계약완료 \n");
sqlstr.append(" AND TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n");
sqlstr.append(" ) A, CONT_PUR_SUPP B, PUB0120T C \n");
sqlstr.append(" WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n");
sqlstr.append(" AND B.SIGN_SEQ = 2 \n");
sqlstr.append(" AND A.REQ_USERID = C.ACCT_ID \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;
}
}
private RowSet getOutContEndThread() throws Exception{
Search search = new SearchImpl();
RowSet rowSet = new RowSetImpl();
StringBuffer sqlstr = new StringBuffer();
try {
sqlstr.append(" -- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n");
sqlstr.append(" SELECT A.CON_DOC_NO \n");
sqlstr.append(" , A.DEPT_NAME \n");
sqlstr.append(" , A.BUILD_NAME \n");
sqlstr.append(" , A.END_WORK_DATE \n");
sqlstr.append(" , B.MEMBER_NAME \n");
sqlstr.append(" , C.E_MAIL \n");
sqlstr.append(" FROM ( \n");
sqlstr.append(" SELECT A.SUB_CONT_MOD_ID \n");
sqlstr.append(" ,A.REQ_USERID \n");
sqlstr.append(" ,A.CON_DOC_NO \n");
sqlstr.append(" ,A.DEPT_NAME \n");
sqlstr.append(" ,A.BUILD_NAME \n");
sqlstr.append(" ,'~ ' || TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n");
sqlstr.append(" FROM SUB2000T A \n");
sqlstr.append(" , (SELECT SUB_CONT_ID, MAX(MOD_NO) AS MOD_NO \n");
sqlstr.append(" FROM SUB2000T \n");
sqlstr.append(" WHERE STATUS_CD = '50' \n");
sqlstr.append(" GROUP BY SUB_CONT_ID) B \n");
sqlstr.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n");
sqlstr.append(" AND A.MOD_NO = B.MOD_NO \n");
sqlstr.append(" AND A.STATUS_CD = '50' -- 계약완료 \n");
sqlstr.append(" AND TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n");
sqlstr.append(" ) A, CONT_OUT_SUPP B, PUB0120T C \n");
sqlstr.append(" WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID \n");
sqlstr.append(" AND B.SIGN_SEQ = 2 \n");
sqlstr.append(" AND A.REQ_USERID = C.ACCT_ID \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;
}
}
private void sendContEndMail(RowSet rowTread){
try {
HandlerStorage Stroage = new HandlerStorage();
MessageSend messageSend = new MessageSend(Stroage);
StringBuffer mailtitle = new StringBuffer();
StringBuffer mailtitlein = new StringBuffer();
StringBuffer mailContent = new StringBuffer();
Row row = new RowImpl();
mailtitle.append("*[알림]계약기간 만료 예정");
mailtitlein.append("*[알림]계약기간 만료 예정");
for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
row = rowTread.getRow(i);
mailContent.append("");
mailContent.append("다음 하도급 계약의 계약기간이 30일 후 만료 예정이오니 ERP에서 변경계약을 요청하시기 바랍니다.
");
mailContent.append("
");
mailContent.append("1.프로젝트: " + row.get("DEPT_NAME") + "
");
mailContent.append("2.계약번호: " + row.get("CON_DOC_NO") + "
");
mailContent.append("3.계약명: " + row.get("BUILD_NAME") + "
");
mailContent.append("4.계약기간: " + row.get("END_WORK_DATE") + "
");
mailContent.append("5.계약업체: " + row.get("MEMBER_NAME") + "
");
mailContent.append("6.비고:
");
mailContent.append(" 1)계약기간 만료일에 준공 예정일 경우 해당 없음.
");
mailContent.append(" 2)계약기간이 유효한 계약만 기성 신청가능하므로 실제 준공 예상 시점까지 계약기간 연장 필요.
");
mailContent.append(" 3)eTEC Standard에 따라 변경 계약에 필요한 자료를 ERP에 첨부 후 변경계약 요청.
");
mailContent.append("
");
mailContent.append("");
if (row.get("E_MAIL").length() > 4 ){
messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "NO_LINK");
}
mailContent = new StringBuffer();
}
}
catch(Exception e) {
Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage());
}
}
}