/**
 * @(#)file    ChgItemBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-08-11
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.payment.outside.biz;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.code.CommCodeManager;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class PayItemBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		
		boolean success = false;
		
		//변경 내역
		if(storage.compareEvent("")){ 
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getItemList(params));			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));			
		}else if(storage.compareEvent("DATA_SAVE")){
			String[] status = super.gets("sheet1.STATUS");
			try {
				super.conn.begin();
				Connection con = super.conn.getConnection();
				
				//배치값 생성 
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i]) || IBSheet7Utils.equalStatusUpdate(status[i]) ) {
						success = updateData(con, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i) ); 
					}
				}		
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
	}
	/**
	 * 계약  내역 목록
	 * @param Params
	 * @return
	 */
	private RowSet getItemList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sql = new StrBuffer();
        try{
        	sql.append(" SELECT C.ITEM_NAME                                                         \n"); 
            sql.append("       ,NVL(D.BEF_TAMT  , NVL(E.BEF_TAMT,0)) BEF_TAMT                              \n"); 
            sql.append("       ,NVL(D.BEF_NAMT  , NVL(E.BEF_NAMT,0)) BEF_NAMT                              \n"); 
            sql.append("       ,D.CUR_TAMT                                                          \n"); 
            sql.append("       ,D.CUR_NAMT                                                          \n"); 
            sql.append("       ,NVL( D.TOT_TAMT ,  E.BEF_TAMT )   TOT_TAMT                          \n"); 
            sql.append("       ,NVL( D.TOT_NAMT ,  E.BEF_NAMT )   TOT_NAMT                          \n"); 
            sql.append("       ,C.CD_CSTR                                                           \n"); 
            sql.append("       ,C.DL_CSTR                                                           \n"); 
            sql.append("       ,C.MATERIAL_CD                                                       \n"); 
            sql.append("       ,B.SUB_CONT_ID                                                       \n"); 
            sql.append("       ,A.ACMP_ID                                                           \n"); 
            sql.append("       ,DECODE(NVL(B.CON_SUPPLY_TAMT, 0), 0, 0, B.CON_SUPPLY_NAMT /  B.CON_SUPPLY_TAMT) RATE                           \n");
            sql.append("       ,C.SUPPLY_TAMT                                                       \n");
            sql.append("       ,C.SUPPLY_NAMT                                                       \n");
            sql.append("       ,B.DEPT_CD CD_SITE                                                   \n");
            sql.append("  FROM SUB1040T A                                                           \n"); 
            sql.append("      ,SUB2000T B                                                           \n"); 
            sql.append("      ,CONT_OUT_ITEM C                                                      \n"); 
            sql.append("      ,SUB1041T D                                                           \n"); 
            sql.append("      ,(SELECT MAX(A.SUB_CONT_ID) SUB_CONT_ID                               \n");
            sql.append("	       	  ,A.MATERIAL_CD                                                \n");
            sql.append("	       	  ,SUM(CUR_TAMT) BEF_TAMT                                       \n");
            sql.append("	       	  ,SUM(CUR_NAMT)  BEF_NAMT                                      \n");
            sql.append("          FROM SUB1041T A                                                   \n");
            sql.append("              ,SUB1040T B                                                   \n");
            sql.append("         WHERE A.ACMP_ID = B.ACMP_ID                                        \n");
            sql.append("           AND A.SUB_CONT_ID = " + params.get("SUB_CONT_ID") +"             \n");
            sql.append("           AND B.STATUS_CD = '10'                                           \n");
            sql.append("         GROUP BY A.MATERIAL_CD ) E                                         \n");
            sql.append("  WHERE A.SUB_CONT_ID = B.SUB_CONT_ID                                       \n"); 
            sql.append("    AND A.MOD_NO = B.MOD_NO                                                 \n"); 
            sql.append("    AND B.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID                               \n"); 
            sql.append("    AND D.ACMP_ID(+) = " + params.get("ACMP_ID") +"                         \n"); 
            sql.append("    AND C.MATERIAL_CD = D.MATERIAL_CD(+)                                    \n");
            sql.append("    AND C.MATERIAL_CD = E.MATERIAL_CD(+)                                    \n");
            sql.append("    AND A.ACMP_ID = " + params.get("ACMP_ID") +"                            \n"); 
            sql.append("  ORDER BY C.MATERIAL_CD                                                    \n");
            
            System.out.println(sql.toString());
            search.setStatement(sql.toString());
            WAFLogger.debug(sql.toString());
			
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	private boolean updateData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("MERGE INTO SUB1041T A  		           			                    \n");
			sql.append("USING DUAL  					                                    \n");
			sql.append("   ON (A.ACMP_ID = ? 					                            \n");
			sql.append("  AND A.CD_CSTR = ?    					                            \n");
			sql.append("  AND A.DL_CSTR = ? )  					                            \n");
			sql.append(" WHEN MATCHED THEN  	 			                                \n");
			sql.append("      UPDATE           					                            \n");
			sql.append("      SET BEF_TAMT      = ?   				                        \n");
			sql.append("         ,BEF_NAMT      = ?   				                        \n");
			sql.append("         ,CUR_TAMT      = ?   				                        \n");
			sql.append("         ,CUR_NAMT      = ROUND(?,?)		                        \n");
			sql.append("         ,TOT_TAMT      = ? 					                    \n");
			sql.append("         ,TOT_NAMT      = ROUND(?,?)			                    \n");
			sql.append("         ,UPD_BY        = ?  					                    \n");
			sql.append("         ,UPD_DATE      =  SYSDATE		 	                        \n");
			sql.append("         ,CD_SITE       = ?		 	                                \n");
			sql.append("  WHEN NOT MATCHED THEN  					                        \n");
			sql.append("       INSERT (ACMP_ID, CD_CSTR, DL_CSTR, SUB_CONT_ID, MATERIAL_CD  \n");
			sql.append("		      ,BEF_TAMT, BEF_NAMT, CUR_TAMT, CUR_NAMT, TOT_TAMT   	\n");
			sql.append("		      ,TOT_NAMT, CRE_BY, CRE_DATE, CD_SITE )               	\n");
			sql.append("        VALUES(? , ?, ?, ?, ?, 			     	                    \n");
			sql.append("               ? , ?, ?, ROUND(?,?), ?, 	                        \n");
			sql.append("               ROUND(?,?) , ? , SYSDATE, ?  )                       \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
		    
			pstmt.setString(paramIdx++, sqlParams.get("ACMP_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CD_CSTR"));
 			pstmt.setString(paramIdx++, sqlParams.get("DL_CSTR"));
			pstmt.setString(paramIdx++, sqlParams.get("BEF_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("BEF_NAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("CUR_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("CUR_NAMT"));
			pstmt.setString(paramIdx++, params.get("digit"));			
			pstmt.setString(paramIdx++, sqlParams.get("TOT_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("TOT_NAMT"));
			pstmt.setString(paramIdx++, params.get("digit"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CD_SITE"));
			pstmt.setString(paramIdx++, sqlParams.get("ACMP_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CD_CSTR"));
			pstmt.setString(paramIdx++, sqlParams.get("DL_CSTR"));
			pstmt.setString(paramIdx++, sqlParams.get("SUB_CONT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("MATERIAL_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("BEF_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("BEF_NAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("CUR_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("CUR_NAMT"));
			pstmt.setString(paramIdx++, params.get("digit"));
			pstmt.setString(paramIdx++, sqlParams.get("TOT_TAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("TOT_NAMT"));
			pstmt.setString(paramIdx++, params.get("digit"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CD_SITE"));
			
			
			System.out.println(sql.toString());
			System.out.println(pstmt.getParameterMetaData());
			
			pstmt.executeUpdate();
			return true;
			
		} catch (Exception e) {
			WAFLogger.error(e);
			System.out.println(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
}


