/**
 * @(#)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.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 ScoreManageListBiz extends Biz {
	
    public ScoreManageListBiz(HandlerStorage storage) {

        super(storage);        
    }
    public void doBiz() throws Exception {
        
    	
        if(storage.compareEvent(""))
        {
            ValueObject secoreCnt = new ValueObject();
            RowSet secoreYear = null;
            
            secoreCnt = geTsecoreCnt(params);
//            secoreYear = getSecoreYear(params);
//            reportList = getReportList(params);
            
            
            params.set("secoreCnt", secoreCnt);
            storage.addRowSet("secoreYear", secoreYear);
        }
        else if(storage.compareEvent("GET_LIST"))
        {
            params.set("sheet1", getSecoreList(params));
            storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        }
        else if(storage.compareEvent("GET_REPORT_GUBUN"))
        {
            RowSet getreportgubun = getreportgubun(params);
            storage.addRowSet( "ROWSET", getreportgubun );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_ADM_YEAR"))
        {
            RowSet getrAdmYear = getrAdmYear(params);
            storage.addRowSet( "ROWSET", getrAdmYear );
            storage.setSuccess(true);
        }
        else if(storage.compareEvent("GET_ADM_YEAR_CNT"))
        {
            ValueObject getAdmYearAdd = new ValueObject(); 
            getAdmYearAdd = getAdmYearAdd(params);
            storage.addValueObject( "VALUEOBJECT", getAdmYearAdd );
            storage.setSuccess(true);
        }
        
        
        

    }
    
    
    
    public ValueObject getAdmYearAdd(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            sqlstr.append("     SELECT COUNT(*) CNT                                      \n")
                   .append("          FROM APPR_STD                                 \n")
                   .append("          WHERE ADM_YEAR = ? +1                              \n")
                   .append("          	AND DOC_CD = ?                          \n");
            
            
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("p_adm_year"));
            search.addParameter(params.get("p_doc_gubun"));
        
            System.out.println(sqlstr.toString());
            System.out.println(search.getParameters());
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("geTsecoreCnt===>" + e);
            
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }
    
    
    
    
    public ValueObject geTsecoreCnt(ValueObject params){
        Search search = new SearchImpl();
        ValueObject vo = null;
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();
        
        
        try{
            
            sqlstr.append("     SELECT COUNT(*) CNT                                       \n")
                   .append("          FROM APPR_STD                                 \n")
                   .append("          WHERE ADM_YEAR = TO_CHAR(SYSDATE, 'YYYY') +1                                \n")
                   .append("          ORDER BY DOC_CD ASC, APPR_CD ASC                            \n");
            
            
            search.setStatement(sqlstr.toString());
        
            System.out.println(sqlstr.toString());
            System.out.println(search.getParameters());
            
            rowSet = search.execute();
            
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("geTsecoreCnt===>" + e);
            
        }finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
    }
    
    
    
    /**
     * 목록 가져오기
     * @param Params
     * @return
     */
    private RowSet getreportgubun(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        System.out.println(params.get("p_job_div_gubun"));
        try {
            sqlStr.append("        SELECT MAX(DETAILCD) DETAILCD, MAX(DETAILNM) DETAILNM FROM EBID_COM911T A, APPR_STD B                                                         \n");
            sqlStr.append("         WHERE A.DETAILCD = B.DOC_CD                                                                                                                                      \n");
            sqlStr.append("         AND  BASECD = 'SUPP_REPORT'                                                                                                                                     \n");
            sqlStr.append("         AND ADM_YEAR = ?                                                                                                                              \n");
            if(params.get("p_job_div_gubun").equals("P"))
            {
                sqlStr.append("         AND DETAILCD IN ('01', '02', '03', '04', '05', '06')                                                                                                                                       \n");
            }
            else if(params.get("p_job_div_gubun").equals("O"))
            {
                sqlStr.append("         AND DETAILCD IN ('11', '12' , '13', '14', '15', '16'    )  --2015.07.24 mklee , '13', '14', '15', '16'                                                                                                                                   \n");
            }else{
            	sqlStr.append("         AND DETAILCD IN ('01', '02', '03', '04', '05', '06')                                                                                                                                       \n");
            }
            
            sqlStr.append("        GROUP BY DETAILCD                                                                                                           \n");
            sqlStr.append("         ORDER BY DETAILCD ASC                                                                                                                \n");
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("p_adm_year"));
            System.out.println(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            System.out.println("getreportgubun=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    
    private RowSet getrAdmYear(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("        SELECT  MAX(ADM_YEAR) ADM_YEAR, TO_CHAR(SYSDATE, 'YYYY') +1                                                                                                                                                 \n");
            sqlStr.append("                 FROM APPR_STD                                                                                                                                                   \n");
            sqlStr.append("                 WHERE 1=1 --WHERE ADM_YEAR = TO_CHAR(SYSDATE, 'YYYY')                                                                                                                                                   \n");
            if(params.get("p_job_div_gubun").equals("P"))
            {
                sqlStr.append("         AND DOC_CD IN ('01', '02', '03', '04', '05', '06')                                                                                                                                       \n");
            }
            else
            {
                sqlStr.append("         AND DOC_CD IN ('11', '12', '13', '14', '15', '16')   --2015.07.24 mklee                                                                                                                                      \n");
            }
            sqlStr.append("                 GROUP BY ADM_YEAR                                                                                                                                                  \n");
            
            
            search.setStatement(sqlStr.toString());
//            search.addParameter(params.get("p_corp_id"));
            System.out.println(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            System.out.println("getSecoreYear=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    
    
    /**
     * 목록 가져오기
     * @param Params
     * @return
     */
    private RowSet getSecoreList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        try {
            sqlStr.append("        SELECT                                                                                                                     \n");
            sqlStr.append("        ADM_YEAR, GUBUN_1, GUBUN_2, GUBUN_3, APPR_CD_1, APPR_CD_2, APPR_CD_3,                       \n");
            sqlStr.append("        DOC_CD, DEPTH, STOCK_RATE, A_CONTENT, A_RATE, B_CONTENT, B_RATE, C_CONTENT, C_RATE, D_CONTENT, D_RATE, E_CONTENT, E_RATE, \"NOTI\", PARENT_LEVEL, DEL_YN,                    \n");
            sqlStr.append("       A_CAL_VAL, B_CAL_VAL, C_CAL_VAL, D_CAL_VAL, E_CAL_VAL          																																					   			      \n");
            if(params.get("p_doc_gubun").equals("12"))	
            {
                sqlStr.append("       , STOCK_RATE_M                 \n");
            }
            else
            {
                sqlStr.append("        , (SELECT STOCK_RATE FROM APPR_STD WHERE ADM_YEAR = ? AND DOC_CD = ?  AND APPR_CD = APPR_CD_2) STOCK_RATE_M                 \n");
            }
            sqlStr.append("                       FROM  (                                                                                                                           \n");
            sqlStr.append("                         SELECT  ADM_YEAR,                                                                                                                            \n");
            sqlStr.append("                                LAST_VALUE (GUBUN_1 IGNORE NULLS) OVER (ORDER BY APPR_CD)  GUBUN_1,                                                                                                                           \n");
            sqlStr.append("                                LAST_VALUE (GUBUN_2 IGNORE NULLS) OVER (ORDER BY APPR_CD) GUBUN_2,                                                                                                                           \n");
            sqlStr.append("                                GUBUN_3,                                                                                                                           \n");
            sqlStr.append("                                LAST_VALUE (APPR_CD_1 IGNORE NULLS) OVER (ORDER BY APPR_CD) APPR_CD_1,                                                                                                                           \n");
            sqlStr.append("                                LAST_VALUE (APPR_CD_2 IGNORE NULLS) OVER (ORDER BY APPR_CD) APPR_CD_2,                                                                                                                           \n");
            sqlStr.append("                                APPR_CD_3,                                                                                                                           \n");
            sqlStr.append("                                DOC_CD,DEPTH, STOCK_RATE,                                                                                                                           \n");
            sqlStr.append("                                A_CONTENT,  REPLACE(A_RATE, '점', '') A_RATE , B_CONTENT, REPLACE(B_RATE, '점', '') B_RATE, C_CONTENT, REPLACE(C_RATE, '점', '') C_RATE,                                             \n");
            sqlStr.append("                                D_CONTENT, REPLACE(D_RATE, '점', '') D_RATE, E_CONTENT, REPLACE(E_RATE, '점', '') E_RATE, \"NOTI\", PARENT_LEVEL, DEL_YN,                \n");
            sqlStr.append("                             	A_CAL_VAL, B_CAL_VAL, C_CAL_VAL, D_CAL_VAL, E_CAL_VAL                   \n");
            if(params.get("p_doc_gubun").equals("12"))
            {
                sqlStr.append("                               , STOCK_RATE_M                 \n");
            }
            sqlStr.append("                         FROM                                                                                                                            \n");
            sqlStr.append("                         (                                                                                                                           \n");
            sqlStr.append("                             SELECT                                                                                                                            \n");
            sqlStr.append("                                         DECODE (DEPTH, '1', APPR_ACLS, '') GUBUN_1,                                                                                                                           \n");
            sqlStr.append("                                         DECODE (DEPTH, '2', APPR_ACLS, '') GUBUN_2,                                                                                                                           \n");
            sqlStr.append("                                         DECODE (DEPTH, '3', APPR_ACLS, '') GUBUN_3,                                                                                                                            \n");
            sqlStr.append("                                         DECODE (DEPTH, '1', APPR_CD, '') APPR_CD_1,                                                                                                                           \n");
            sqlStr.append("                                         DECODE (DEPTH, '2', APPR_CD, '') APPR_CD_2,                                                                                                                           \n");
            sqlStr.append("                                         DECODE (DEPTH, '3', APPR_CD, '') APPR_CD_3,                                                                                                                           \n");
            if(params.get("p_doc_gubun").equals("12"))
            {
                sqlStr.append("                               DECODE (DEPTH, '2', STOCK_RATE, '') STOCK_RATE_M,                 \n");
            }
            sqlStr.append("                                         APPR_CD,                                                                                                                           \n");
            sqlStr.append("                                         DOC_CD, DEPTH,STOCK_RATE,                                                                                                                           \n");
            sqlStr.append("                                         A_CONTENT, A_RATE, B_CONTENT, B_RATE, C_CONTENT, C_RATE, D_CONTENT, D_RATE, E_CONTENT, E_RATE, \"NOTI\", PARENT_LEVEL,               \n");
            sqlStr.append("                                         ADM_YEAR, DEL_YN, A_CAL_VAL, B_CAL_VAL, C_CAL_VAL, D_CAL_VAL, E_CAL_VAL             \n");
            sqlStr.append("                              FROM                                                                                                                           \n");
            sqlStr.append("                             (                                                                                                                           \n");
            sqlStr.append("                             SELECT ADM_YEAR, DOC_CD, APPR_CD, APPR_LCD, APPR_MCD, APPR_ACLS, PARENT_LEVEL, DEPTH, DOC_CD || APPR_CD TEST_APPR_CD, DOC_CD || PARENT_LEVEL TEST_LEVEL,   \n");
            sqlStr.append("                             STOCK_RATE, A_CONTENT, A_RATE, B_CONTENT, B_RATE, C_CONTENT, C_RATE, D_CONTENT, D_RATE, E_CONTENT, E_RATE, \"NOTI\"  , DEL_YN,                    \n");
            sqlStr.append("                             A_CAL_VAL, B_CAL_VAL, C_CAL_VAL, D_CAL_VAL, E_CAL_VAL                   \n");
            sqlStr.append("                             FROM APPR_STD                                                                                                                            \n");
            sqlStr.append("                             WHERE 1=1                                                                                                                           \n");
            sqlStr.append("                             AND ADM_YEAR = ?                                                                                                                           \n");
            sqlStr.append("                             ORDER BY DOC_CD ASC, APPR_CD ASC                                                                                                                           \n");
            sqlStr.append("                             )                                                                                                                           \n");
            sqlStr.append("                             START WITH PARENT_LEVEL = 'S'                                                                                                                                                \n");
            sqlStr.append("                              CONNECT BY PRIOR TEST_APPR_CD = TEST_LEVEL                                                                                                                           \n");
            sqlStr.append("                             AND LEVEL <= 3                                                                                                                                                                                 \n");
            sqlStr.append("                             ORDER SIBLINGS BY TEST_APPR_CD ASC                                                                                                                           \n");
            sqlStr.append("                        )                                                                                                                                                                                        \n");
            sqlStr.append("                         WHERE DOC_CD = ?                                                                                                                           \n");
            sqlStr.append("                         )                                                                                                                                                                        \n");
            if(params.get("p_doc_gubun").equals("12") || params.get("p_doc_gubun").equals("16"))
            {
                sqlStr.append("                         WHERE DEPTH = '2'                                                                                                                                                          \n");
            }
            else
            {
                sqlStr.append("                         WHERE DEPTH = '3'                                                                                                                                                          \n");
            }
            
            search.setStatement(sqlStr.toString());
            if(!params.get("p_doc_gubun").equals("12"))
            {
                search.addParameter(params.get("p_adm_year"));
                search.addParameter(params.get("p_doc_gubun"));
            }
            search.addParameter(params.get("p_adm_year"));
            search.addParameter(params.get("p_doc_gubun"));
            System.out.println(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            System.out.println("getSecoreList=========>" + se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            se.printStackTrace();
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }

}

