/**
 * @(#)file    ScoreManageListBiz.java
 * @(#)author  lsm
 * @(#)version 1.0
 */
package kr.co.udapsoft.ebid.buyer.sycs.common.biz;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.HTMLMaker;
import kr.co.udapsoft.ebid.buyer.sycs.security.biz.SYSRightCommon;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;
import kr.co.hsnc.common.base.WAFLogger;
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;
public class ScoreManageListSaveBiz extends Biz {
	
    public ScoreManageListSaveBiz(HandlerStorage storage) {

        super(storage);        
    }
    public void doBiz() throws Exception {
        
    	System.out.println("????");
        
        if(storage.compareEvent("SAVE_DATA"))
        {
            ValueObject secoreCnt = new ValueObject();
            boolean success = false;
            
            
            String[] status = super.gets("sheet1.STATUS");
            
            for (int i = 0; i < status.length; i++) 
            {
                if (IBSheet7Utils.equalStatusInsert(status[i])) 
                {
                    success = saveItemData(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                    
                    success = updateItemData_M(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
                
                if (IBSheet7Utils.equalStatusUpdate(status[i])) 
                {
                    success = updateItemData_L(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                    success = updateItemData_M(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                    success = updateItemData(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
                
                if (IBSheet7Utils.equalStatusDelete(status[i])) 
                {
                    success = deleteItemData(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
                }
            }
            
            super.storage.setSuccess(true);
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
        else if(storage.compareEvent("SAVE_NEW_TEST"))
        { 
        	boolean success = false;
        	
        	success = saveNewItem(params);
        	
        	storage.setSuccess(success);
        }

    }
    
    
    
    private boolean saveNewItem(ValueObject Params) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    INSERT INTO APPR_STD								        		 \n");
            sql.append("    VALUE(             													 \n");
            sql.append("      SELECT              												 \n");
			sql.append("		TO_CHAR(SYSDATE,'YYYY')                				             \n");        
			sql.append("		,DOC_CD                         								 \n");
			sql.append("		,APPR_CD                     									 \n");
			sql.append("		,APPR_LCD                        				 				 \n");
			sql.append("		,APPR_MCD                         							     \n");
			sql.append("		,APPR_ACLS                       				  				 \n");
			sql.append("		,STOCK_RATE                         			 \n");
			sql.append("		,A_CONTENT                         				 \n");
			sql.append("		,A_RATE                         				 \n");
			sql.append("		,B_CONTENT                         				 \n");
			sql.append("		,B_RATE                         				 \n");
			sql.append("		,C_CONTENT                         				 \n");
			sql.append("		,C_RATE                         				 \n");
			sql.append("		,D_CONTENT                         				 \n");
			sql.append("		,D_RATE                         				 \n");
			sql.append("		,E_CONTENT                         				 \n");
			sql.append("		,E_RATE                         				 \n");
			sql.append("		,NOTI                         				     \n");
			sql.append("		,PARENT_LEVEL                         		     \n");
			sql.append("		,DEPTH                         					 \n");
			sql.append("		,CRE_BY                         				 \n");
			sql.append("		,SYSDATE                         				 \n");
			sql.append("		,UPD_BY                         				 \n");
			sql.append("		,SYSDATE                         				 \n");
			sql.append("		,DEL_YN                         				 \n");
			sql.append("		,A_CAL_VAL                         				 \n");
			sql.append("		,B_CAL_VAL                         				 \n");
			sql.append("		,C_CAL_VAL                         				 \n");
			sql.append("		,D_CAL_VAL                         				 \n");
			sql.append("		,E_CAL_VAL                         				 \n");
			sql.append("		 FROM APPR_STD         				             \n");
			sql.append("		 WHERE  ADM_YEAR = ?         				     \n");
			sql.append("		   AND  DOC_CD = ?         				         \n");
            sql.append("       )                        					     \n");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(params.get("p_adm_year"));
            persistent.addParameter(params.get("p_doc_gubun"));
            
            
            System.out.println(sql.toString());
            System.out.println(persistent.getParameters());
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("saveNewItem===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> saveNewItem() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
    
    
    
    private boolean saveItemData(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    INSERT INTO APPR_STD         \n");
            sql.append("    (                                                    \n");
            sql.append("          ADM_YEAR                             \n");
            sql.append("        , DOC_CD                             \n");
            sql.append("        , APPR_CD                   \n");
            sql.append("        , APPR_LCD                   \n");
            sql.append("        , APPR_MCD                   \n");
            sql.append("        , APPR_ACLS                   \n");
            sql.append("        , STOCK_RATE                   \n");
            sql.append("        , A_CONTENT                   \n");
            sql.append("        , A_RATE                   \n");
            sql.append("        , B_CONTENT                   \n");
            sql.append("        , B_RATE                   \n");
            sql.append("        , C_CONTENT                   \n");
            sql.append("        , C_RATE                   \n");
            sql.append("        , D_CONTENT                   \n");
            sql.append("        , D_RATE                   \n");
            sql.append("        , E_CONTENT                   \n");
            sql.append("        , E_RATE                   \n");
            sql.append("        , NOTI                   \n");
            sql.append("        , PARENT_LEVEL                   \n");
            sql.append("        , DEPTH                   \n");
            sql.append("        , DEL_YN                   \n");
            sql.append("        , CRE_BY                   \n");
            sql.append("        , CRE_DATE              \n");
            sql.append("       )VALUES(              \n");
            sql.append("         ?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,?              \n");
            sql.append("        ,'Y'            \n");
            sql.append("        ,?              \n");
            sql.append("        ,SYSDATE              \n");
            sql.append("       )                              \n");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("ADM_YEAR"));
            persistent.addParameter(sqlParams.get("DOC_CD"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("APPR_CD_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("APPR_CD_3"));
            }
            persistent.addParameter(sqlParams.get("APPR_CD_1"));
            persistent.addParameter(sqlParams.get("APPR_CD_2"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("GUBUN_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("GUBUN_3"));
            }
            persistent.addParameter(sqlParams.get("STOCK_RATE"));
            persistent.addParameter(sqlParams.get("A_CONTENT"));
            persistent.addParameter(sqlParams.get("A_RATE"));
            persistent.addParameter(sqlParams.get("B_CONTENT"));
            persistent.addParameter(sqlParams.get("B_RATE"));
            persistent.addParameter(sqlParams.get("C_CONTENT"));
            persistent.addParameter(sqlParams.get("C_RATE"));
            persistent.addParameter(sqlParams.get("D_CONTENT"));
            persistent.addParameter(sqlParams.get("D_RATE"));
            persistent.addParameter(sqlParams.get("E_CONTENT"));
            persistent.addParameter(sqlParams.get("E_RATE"));
            persistent.addParameter(sqlParams.get("NOTI"));
            persistent.addParameter(sqlParams.get("PARENT_LEVEL"));
            persistent.addParameter(sqlParams.get("DEPTH"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("saveItemData===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> updateItemData() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
    
    
    private boolean updateItemData_L(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    UPDATE APPR_STD SET          \n");
            sql.append("          APPR_ACLS   = ?                \n");
//            sql.append("          STOCK_RATE   = ?                \n");
            sql.append("        , UPD_BY   = ?                \n");
            sql.append("        , UPD_DATE   = SYSDATE               \n");
            sql.append("        WHERE ADM_YEAR = ?  AND DOC_CD = ? AND APPR_CD = ?                        \n");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("GUBUN_1"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            persistent.addParameter(params.get("p_adm_year"));
            persistent.addParameter(params.get("p_doc_gubun"));
            persistent.addParameter(sqlParams.get("APPR_CD_1"));
            
            
            
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("updateItemData_L===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> updateItemData_L() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
    
    private boolean updateItemData_M(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    UPDATE APPR_STD SET          \n");
            sql.append("          APPR_ACLS   = ?                \n");
            sql.append("        , STOCK_RATE   = ?                \n");
            sql.append("        , UPD_BY   = ?                \n");
            sql.append("        , UPD_DATE   = SYSDATE               \n");
            sql.append("        WHERE ADM_YEAR = ?  AND DOC_CD = ? AND APPR_CD = ?                        \n");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("GUBUN_2"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("STOCK_RATE"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("STOCK_RATE_M"));
            }
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            persistent.addParameter(params.get("p_adm_year"));
            persistent.addParameter(params.get("p_doc_gubun"));
            persistent.addParameter(sqlParams.get("APPR_CD_2"));
            
            
            
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("updateItemData_M===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> updateItemData_M() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
    
    private boolean updateItemData(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    UPDATE APPR_STD SET          \n");
            sql.append("          ADM_YEAR = ?                            \n");
            sql.append("        , DOC_CD = ?                            \n");
            sql.append("        , APPR_CD       = ?            \n");
            sql.append("        , APPR_LCD   = ?                \n");
            sql.append("        , APPR_MCD   = ?                \n");
            sql.append("        , APPR_ACLS   = ?                \n");
            sql.append("        , STOCK_RATE   = ?                \n");
            sql.append("        , A_CONTENT   = ?                \n");
            sql.append("        , A_RATE   = ?                \n");
            sql.append("        , A_CAL_VAL   = ?                \n");
            sql.append("        , B_CONTENT   = ?                \n");
            sql.append("        , B_RATE   = ?                \n");
            sql.append("        , B_CAL_VAL   = ?                \n");
            sql.append("        , C_CONTENT   = ?                \n");
            sql.append("        , C_RATE   = ?                \n");
            sql.append("        , C_CAL_VAL   = ?                \n");
            sql.append("        , D_CONTENT   = ?                \n");
            sql.append("        , D_RATE   = ?                \n");
            sql.append("        , D_CAL_VAL   = ?                \n");
            sql.append("        , E_CONTENT   = ?                \n");
            sql.append("        , E_RATE   = ?                \n");
            sql.append("        , E_CAL_VAL   = ?                \n");
            sql.append("        , NOTI   = ?                \n");
            sql.append("        , PARENT_LEVEL   = ?                \n");
            sql.append("        , DEPTH   = ?                \n");
            sql.append("        , DEL_YN = ?               \n"); 
            sql.append("        , UPD_BY   = ?                \n");
            sql.append("        , UPD_DATE   = SYSDATE               \n");
            sql.append("        WHERE ADM_YEAR = ?  AND DOC_CD = ? AND APPR_CD = ?                        \n");
            
            persistent.setStatement(sql.toString());
            persistent.addParameter(sqlParams.get("ADM_YEAR"));
            persistent.addParameter(sqlParams.get("DOC_CD"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("APPR_CD_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("APPR_CD_3"));
            }
            persistent.addParameter(sqlParams.get("APPR_CD_1"));
            persistent.addParameter(sqlParams.get("APPR_CD_2"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("GUBUN_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("GUBUN_3"));
            }
            persistent.addParameter(sqlParams.get("STOCK_RATE"));
            persistent.addParameter(sqlParams.get("A_CONTENT"));
            persistent.addParameter(sqlParams.get("A_RATE"));
            persistent.addParameter(sqlParams.get("A_CAL_VAL"));
            persistent.addParameter(sqlParams.get("B_CONTENT"));
            persistent.addParameter(sqlParams.get("B_RATE"));
            persistent.addParameter(sqlParams.get("B_CAL_VAL"));
            persistent.addParameter(sqlParams.get("C_CONTENT"));
            persistent.addParameter(sqlParams.get("C_RATE"));
            persistent.addParameter(sqlParams.get("C_CAL_VAL"));
            persistent.addParameter(sqlParams.get("D_CONTENT"));
            persistent.addParameter(sqlParams.get("D_RATE"));
            persistent.addParameter(sqlParams.get("D_CAL_VAL"));
            persistent.addParameter(sqlParams.get("E_CONTENT"));
            persistent.addParameter(sqlParams.get("E_RATE"));
            persistent.addParameter(sqlParams.get("E_CAL_VAL"));
            persistent.addParameter(sqlParams.get("NOTI"));
            persistent.addParameter(sqlParams.get("PARENT_LEVEL"));
            persistent.addParameter(sqlParams.get("DEPTH"));
            persistent.addParameter(sqlParams.get("DEL_YN"));
            persistent.addParameter(storage.getUser().get("acct_id"));
            
            persistent.addParameter(params.get("p_adm_year"));
            persistent.addParameter(params.get("p_doc_gubun"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("APPR_CD_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("APPR_CD_3"));
            }
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("updateItemData===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> updateItemData() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
    
    private boolean deleteItemData(ValueObject sqlParams) throws Exception {
        Persistent persistent = null;
        StringBuffer sql = new StringBuffer();
        
        
        try {
            
            conn.begin();
            persistent = new PersistentImpl(super.conn.getConnection());
            
            sql.append("    DELETE FROM APPR_STD           \n");
            sql.append("        WHERE ADM_YEAR = ?  AND DOC_CD = ? AND APPR_CD = ?                        \n");
            
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(params.get("p_adm_year"));
            persistent.addParameter(params.get("p_doc_gubun"));
            if(params.get("p_doc_gubun").equals("12"))
            {
                persistent.addParameter(sqlParams.get("APPR_CD_2"));
            }
            else
            {
                persistent.addParameter(sqlParams.get("APPR_CD_3"));
            }
            
            
            
            
            
            persistent.execute();
            persistent.clearParameters();
            
            return true;
        } catch(Exception e) {
//          e.printStackTrace();
            System.out.println("deleteItemData===>" + e);
            WAFLogger.error(this.getClass().getName() + " --> deleteItemData() : " + e.getMessage());
            WAFLogger.error("SQL : " + persistent.toString());
            WAFLogger.error(e);
            conn.setRollback();
            throw e;
            
        }
        
        finally {
            super.conn.end();
        }
    }   
    
}
