/**
 * @(#)file    PdfViewerBiz.java
 * @(#)author  lee in oh
 * @(#)version 1.0
 * @(#)date    2018-10-30
 * @(#)since   JDK1.6
 *
 * Copyright (c)  www.sdcit.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.common.file.biz;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import weblogic.jdbc.vendor.oracle.OracleThinBlob;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF4;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF6;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF7;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF8;


public class PdfViewerBiz extends Biz {

	public PdfViewerBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		if(storage.compareEvent("CREATE_PDF")){
			try{
				super.conn.begin();
				
				Connection con = super.conn.getConnection();
				
				RowSet result = getListRow1(con);
			 
				for( int i = 0 ; i < result.size() ; i++ ) {
					Row row = result.getRow(i);
	            	
					storage.addValueObject("pdf",  RowSetUtility.getValueObject(result.getRow(i)));
					 
	            	params.set("PROOF_DEPT", row.get("PROOF_DEPT")); 
	            	params.set("PROOFDATE",row.get("PROOFDATE"));
	            	params.set("PROOF_NO",row.get("PROOF_NO"));
	            	params.set("PROOF_SEQ",Integer.parseInt(row.get("PROOF_SEQ")));
	            	// proof_dept, proofdate, proof_no, proof_seq  
					OutputStream outputStream = getOS(con, row.get("PROOF_DEPT"), row.get("PROOFDATE"), row.get("PROOF_NO"), Integer.parseInt(row.get("PROOF_SEQ")));
					
					//20 외주선급 
					//01 외주기성
					//21 구매선급  
					//02 구매기성
				    System.out.println(row.get("JOB_GU")); 
					if (params.get("JOB_GU").equals("21")){//구매선급
						SACDOCreatePDF4 sacDOCreatePDF4 = new SACDOCreatePDF4();  //구매선급 
						sacDOCreatePDF4.doBiz1(con, outputStream, row.get("PREPAY_ID") , null);
					}else if(params.get("JOB_GU").equals("02")){//구매대금 
						SACDOCreatePDF4 sacDOCreatePDF4 = new SACDOCreatePDF4();  //구매대금
						sacDOCreatePDF4.doBiz1(con, outputStream, row.get("ACMP_ID") , null);
					}else if(params.get("JOB_GU").equals("20")){//외주선급
						SACDOCreatePDF6 sacDOCreatePDF6 = new SACDOCreatePDF6();  //외주선급
						sacDOCreatePDF6.doBiz1(con, outputStream, row.get("PREPAY_ID") , null);
					}else if (params.get("JOB_GU").equals("01")){//외주기성 
						if (row.get("END_YN").equals("Y")) {
							SACDOCreatePDF7 sacDOCreatePDF7 = new SACDOCreatePDF7();  //외주준공
							sacDOCreatePDF7.doBiz1(con, outputStream, row.get("ACMP_ID") , null);
						}else{
							SACDOCreatePDF8 sacDOCreatePDF8 = new SACDOCreatePDF8();   //외주중도 
							sacDOCreatePDF8.doBiz1(con, outputStream, row.get("ACMP_ID") , null);
						}
					} 
	        	}				  
	
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		} else{
			try{
				super.conn.begin();
				
				Connection con = super.conn.getConnection();
			    RowSet result = getListRow1(con);
			    
			    if (result.size() > 0) {
			    	storage.addValueObject("pdf",  RowSetUtility.getValueObject(result.getRow(0)));
			    } 
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
	}
	
	public static OutputStream getOS(Connection con, String proof_dept, String proof_dt, String proof_no, 
			int proof_seq) throws Exception {
		OutputStream outputStream = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        
        Statement stmt = null;
        
		try {

            String strSQL = "SELECT photo "
				+ " FROM DOC021T "
				+ " WHERE proof_dept = '"+ proof_dept +"'"
				+ "   AND TO_CHAR (proofdate, 'yyyymmdd') = '"+ proof_dt +"' "
				+ "   AND proof_no = " + proof_no
				+ "   AND proof_seq = "+ proof_seq
				+ "   AND proc_gu = '30' " 
				+ " FOR UPDATE";
			
            System.out.println("*****" + strSQL );
			pstmt = con.prepareStatement(strSQL);
			resultSet = pstmt.executeQuery();

			if (resultSet.next()) {
				OracleThinBlob blob = (OracleThinBlob)resultSet.getBlob(1);
				
				outputStream = blob.getBinaryOutputStream();
			} else {
				throw new Exception("증빙파일을 저장할 레코드가 존재하지 않습니다.");
			}

		} catch (Exception e) {
			WAFLogger.error(e);

		} finally {
			if( stmt   != null ) stmt.close();
			resultSet.close();
			pstmt.close();
			return outputStream;
		} 
	}
	
public RowSet getListRow1(Connection connection) throws Exception {
        
    	Persistent pSelect = null;
        StringBuffer sql = new StringBuffer();
        RowSet resultRowset = null;
        
        try {
        	
        	pSelect = new PersistentImpl(connection);
        	//erp_opycD 중도
        	//erp_opyeD 준공
        	//DOC020T.JOB_GU 01외주기성, 02 자재대금 20 외주선급 21 자재선급 
        	if (params.get("JOB_GU").equals("01")){ //01외주기성
        		sql.append("  SELECT A.PREPAY_ID                                                                  \n"); //외주기성 
            	sql.append("        ,A.ACMP_ID                                                                    \n");  
            	sql.append("        ,C.PROOF_DEPT                                                                 \n");  
            	sql.append("        ,to_char(C.PROOFDATE,'yyyymmdd') PROOFDATE                                    \n");  
            	sql.append("        ,C.PROOF_NO                                                                   \n");  
            	sql.append("        ,C.PROOF_SEQ                                                                  \n");  
            	sql.append("        ,B.JOB_GU                                                                     \n"); 
            	//sql.append("        ,DECODE(D.SIGN_CLASS_CD,'erp_opycD', 'N', 'Y')  END_YN  	                  \n");
            	sql.append("        ,CASE 													                      \n");//2017.08.03 잔여선급금 0일때 준공. 
                sql.append("            WHEN E.TOT_FUND_AMT >= E.CON_SUPPLY_AMT  THEN 'Y'                         \n");//AND (E.TOT_PAY_DEDUCT_RATE = 100 OR E.TOT_PAY_DEDUCT_RATE = 0)                              
                sql.append("            ELSE 'N'                                                 				  \n");
                sql.append("       	END  END_YN 	                                                       		  \n");
            	sql.append("    FROM SUB1050T A                                                                   \n"); 
            	sql.append("       , DOC020T B                                                                    \n");
            	sql.append("       , DOC021T C   		                                                          \n");
            	sql.append("       , SIGN001T D   		                                                          \n");  
            	sql.append("       , SUB1040T E   		                                                          \n"); 
            	sql.append("   WHERE A.TMPSLIP_ID = B.TMPSLIP_ID   	                                              \n");
            	sql.append("     AND B.PROOF_DEPT = C.PROOF_DEPT   	                                              \n");
            	sql.append("     AND B.PROOFDATE  = C.PROOFDATE                                                   \n");
            	sql.append("     AND B.PROOF_NO = C.PROOF_NO                                                      \n");
            	sql.append("     AND TO_CHAR(A.ACMP_ID) = D.KEY_COL1                                              \n");
            	sql.append("     AND D.SIGN_CLASS_CD IN ('erp_opycD', 'erp_opyeD')                                \n");
            	sql.append("     AND A.ACMP_ID = E.ACMP_ID                                                        \n"); 
            	sql.append("     AND A.ACMP_ID = '" + params.get("ACMP_ID") + "'                                  \n"); 
            	sql.append("     AND B.JOB_GU = '01'                                                              \n"); 
        	}else if (params.get("JOB_GU").equals("02")){//02 자재대금
        		sql.append("  SELECT A.PREPAY_ID                                                                  \n"); //구매선급,
            	sql.append("        ,A.TMPSLIP_ID ACMP_ID                                                           \n");  
            	sql.append("        ,C.PROOF_DEPT                                                                 \n"); 
            	sql.append("        ,to_char(C.PROOFDATE,'yyyymmdd') PROOFDATE                                    \n");   
            	sql.append("        ,C.PROOF_NO                                                                   \n");  
            	sql.append("        ,C.PROOF_SEQ                                                                  \n");   
            	sql.append("        ,B.JOB_GU                                                                     \n"); 
            	sql.append("        ,''  END_YN  	                                                              \n");
            	sql.append("    FROM PUR1040T A                                                                   \n");
            	sql.append("       , DOC020T B                                                                    \n");
            	sql.append("       , DOC021T C   		                                                          \n");
            	sql.append("   WHERE A.TMPSLIP_ID = B.TMPSLIP_ID   	                                              \n");
            	sql.append("     AND B.PROOF_DEPT = C.PROOF_DEPT   	                                              \n");
            	sql.append("     AND B.PROOFDATE  = C.PROOFDATE                                                   \n");
            	sql.append("     AND B.PROOF_NO = C.PROOF_NO                                                      \n");
            	sql.append("     AND A.PAYREQ_ID = '" + params.get("ACMP_ID") + "'                                \n"); 
            	sql.append("     AND B.JOB_GU IN ( '02' )                                                         \n"); 
        	}else if (params.get("JOB_GU").equals("20")){//20 외주선급
        		sql.append("  SELECT A.PREPAY_ID                                                                  \n"); //외주선급 
            	sql.append("        ,A.ACMP_ID                                                                    \n");  
            	sql.append("        ,C.PROOF_DEPT                                                                 \n");  
            	sql.append("        ,to_char(C.PROOFDATE,'yyyymmdd') PROOFDATE                                    \n");  
            	sql.append("        ,C.PROOF_NO                                                                   \n");  
            	sql.append("        ,C.PROOF_SEQ                                                                  \n");   
            	sql.append("        ,B.JOB_GU                                                                     \n"); 
            	sql.append("        ,DECODE(D.SIGN_CLASS_CD,'erp_opycD', 'N', 'Y')  END_YN  	                  \n");
            	sql.append("    FROM SUB1050T A                                                                   \n");
            	sql.append("       , DOC020T B                                                                    \n");
            	sql.append("       , DOC021T C   		                                                          \n");
            	sql.append("       , SIGN001T D   		                                                          \n");
            	sql.append("   WHERE A.TMPSLIP_ID = B.TMPSLIP_ID   	                                              \n");
            	sql.append("     AND B.PROOF_DEPT = C.PROOF_DEPT   	                                              \n");
            	sql.append("     AND B.PROOFDATE  = C.PROOFDATE                                                   \n");
            	sql.append("     AND B.PROOF_NO = C.PROOF_NO                                                      \n");
            	sql.append("     AND TO_CHAR(A.ACMP_ID) = D.KEY_COL1                                              \n");
            	sql.append("     AND D.SIGN_CLASS_CD IN ('ebid_oprpyD')                                           \n");
            	sql.append("     AND A.PREPAY_ID = '" + params.get("ACMP_ID") + "'                                \n"); 
            	sql.append("     AND B.JOB_GU = '01'                                                              \n"); 
          }
        	else if (params.get("JOB_GU").equals("21")){// 21 자재선급
        		sql.append("  SELECT A.PREPAY_ID                                                                  \n"); //구매선급,
            	sql.append("        ,A.PAYREQ_ID ACMP_ID                                                          \n");  
            	sql.append("        ,C.PROOF_DEPT                                                                 \n"); 
            	sql.append("        ,to_char(C.PROOFDATE,'yyyymmdd') PROOFDATE                                    \n");   
            	sql.append("        ,C.PROOF_NO                                                                   \n");  
            	sql.append("        ,C.PROOF_SEQ                                                                  \n");   
            	sql.append("        ,B.JOB_GU                                                                     \n"); 
            	sql.append("        ,''  END_YN  	                                                              \n");
            	sql.append("    FROM PUR1040T A                                                                   \n");
            	sql.append("       , DOC020T B                                                                    \n");
            	sql.append("       , DOC021T C   		                                                          \n");
            	sql.append("   WHERE A.TMPSLIP_ID = B.TMPSLIP_ID   	                                              \n");
            	sql.append("     AND B.PROOF_DEPT = C.PROOF_DEPT   	                                              \n");
            	sql.append("     AND B.PROOFDATE  = C.PROOFDATE                                                   \n");
            	sql.append("     AND B.PROOF_NO = C.PROOF_NO                                                      \n");
            	sql.append("     AND A.PREPAY_ID = '" + params.get("ACMP_ID") + "'                                \n");
            	sql.append("     AND B.JOB_GU IN ( '02')                                                          \n"); 
        	}
        	
        	System.out.println(sql.toString());
        	pSelect.setStatement(sql.toString());
        	
        	resultRowset = pSelect.query();
        	
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRow() : \n" + e.getMessage());
            WAFLogger.error(e);
            
        } finally {
            if( resultRowset == null ) resultRowset = null;
        }
        
        return resultRowset;
    }

}
