/**
 * @(#)file    ContPriceSearch.java
 * @(#)author  Seokjin
 * @(#)version 1.0
 * @(#)date    2015-01-09
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;


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

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.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 ContBudgetBiz extends Biz {

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

	public void doBiz() throws Exception {
		if(storage.compareEvent("")) {
			RowSet rowSet = getRowSet();
			super.storage.addRowSet("ROWSET", rowSet);
		}else if(storage.compareEvent("BUDGET_CONTROL")){
			params.set("sheet1", getBudgetList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
        }else if(storage.compareEvent("PRE_WORK")){
        	boolean success = false;
        	try {
        		super.conn.begin();
        		success = updatePreWork(super.conn.getConnection());
        	} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
			super.storage.setSuccess(success);
        }else if(storage.compareEvent("BUDGET_SAVE")){
        	boolean success = false;
        	String[] status = super.gets("sheet1.STATUS");
        	try {
        		super.conn.begin();
    			for (int i = 0; i < status.length; i++) {
    				if (IBSheet7Utils.equalStatusInsert(status[i])) {
    					success = InsertData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
    					success = updateData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusDelete(status[i])) {
    					success = deleteData(super.conn.getConnection(), 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();
			}
        }
	}
	
	private RowSet getRowSet() {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	/**
        	 * 2018.11.26
        	 * 이인오
        	 * 사용안할예정 경영정보 디비링크 삭제
        	 */
        	sqlstr.append("	 SELECT PAYITEM,'(' || PAYITEM || ')' || ESTDESC  ESTDESC	\n");
        	sqlstr.append("	   FROM COP010T!CCSIF       \n");
        	sqlstr.append("	  WHERE COSTTYPE = 'A'  	\n");
        	sqlstr.append("	    AND REFNO IN (10, 99) 	\n");
        	sqlstr.append("	    AND SUBSTR(PAYITEM,1,1) IN ( 'P')	\n"); //  2017.04.04 'T' 외주제외
        	sqlstr.append("	  ORDER BY PAYITEM         	\n");
        	
        	search.setStatement(sqlstr);
            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 RowSet getBudgetList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT A.DEPT_CD                                                                               \n");
        	sqlstr.append("      , B.DEPT_NM                                                                               \n");
        	sqlstr.append("      , A.PAY_ITEM                                                                              \n");
        	sqlstr.append("      , A.PAY_ITEM_NAME                                                                         \n");
        	sqlstr.append("      , A.ITEM_NAME                                                                             \n");
        	sqlstr.append("      , A.PAY_ITEM_SEQ                                                                          \n");
        	sqlstr.append("      , A.ITEM_NAME                                                                             \n");
        	sqlstr.append("      , A.EXEAMT                                                                                \n");
        	sqlstr.append("      , A.FILE_GB                                                                               \n");
        	sqlstr.append("      , A.KIND_GB                                                                               \n");
        	sqlstr.append("      , A.UPD_DATE                                                                              \n");
        	sqlstr.append("      , A.ORDER_SEQ                                                                             \n");
        	sqlstr.append("   FROM BCS_MASTER A                                                                            \n");
        	sqlstr.append("       ,ETEC_ERPIF.ACZ10100 B                                                                        \n");
        	sqlstr.append("  WHERE A.DEPT_CD = B.DEPT_CD                                                                   \n");
        	sqlstr.append("    AND A.DEPT_CD  = '" + params.get("P_DEPT_CD") + "'							               \n");
        	sqlstr.append("    AND A.KIND_GB LIKE '" + params.get("P_KIND_GB")+ "%'              					       \n");
    		sqlstr.append(" ORDER BY  A.KIND_GB DESC ,A.ORDER_SEQ                                                          \n");
    		
       
        	search.setStatement(sqlstr.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 InsertData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
	
			sql.append("   INSERT INTO BCS_MASTER(                      \n");
			sql.append("          DEPT_CD                               \n");
			sql.append("         ,PAY_ITEM                              \n");
			sql.append("         ,PAY_ITEM_NAME                         \n");
			sql.append("         ,PAY_ITEM_SEQ                          \n");
			sql.append("         ,ITEM_NAME                             \n");
			sql.append("         ,EXEAMT                                \n");
			sql.append("         ,FILE_GB                               \n");
			sql.append("         ,KIND_GB                               \n");
			sql.append("         ,CRE_DATE                              \n");
			sql.append("         ,CRE_BY                                \n");
			sql.append("         ,UPD_DATE                              \n");
			sql.append("         ,UPD_BY                                \n");
			sql.append("         ,ORDER_SEQ                             \n");
			sql.append("   )VALUES(                                     \n");
			sql.append("        ?                                                    \n");  //DEPT_CD        
			sql.append("       ,?                                                    \n");  //PAY_ITEM       
			sql.append("       ,?                                                    \n");  //PAY_ITEM_NAME  
			sql.append("       ,(SELECT DECODE(COUNT(*), 0, 1, MAX(PAY_ITEM_SEQ) + 1)  FROM BCS_MASTER WHERE DEPT_CD = ? ) \n");  //PAY_ITEM_SEQ   
			sql.append("       ,?                                                    \n");  //ITEM_NAME      
			sql.append("       ,?                                                    \n");  //EXEAMT         
			sql.append("       ,?                                                    \n");  //FILE_GB        
			sql.append("       ,?                                                    \n");  //KIND_GB        
			sql.append("       ,SYSDATE                                              \n");  //CRE_DATE       
			sql.append("       ,?                                                    \n");  //CRE_BY         
			sql.append("       ,SYSDATE                                              \n");  //UPD_DATE       
			sql.append("       ,?                                                    \n");  //UPD_BY         
			sql.append("       ,DECODE(?,0, (SELECT DECODE(COUNT(*), 0, 1, MAX(PAY_ITEM_SEQ) + 1)  FROM BCS_MASTER WHERE DEPT_CD = ? ), ?) \n");  //ORDER_SEQ         
			sql.append("   )                                                         \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_NAME"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("ITEM_NAME"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("EXEAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("FILE_GB"));
			pstmt.setString(paramIdx++, sqlParams.get("KIND_GB"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setInt(paramIdx++, sqlParams.getInt("ORDER_SEQ"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setInt(paramIdx++, sqlParams.getInt("ORDER_SEQ"));
			pstmt.executeUpdate();                          
			return true;                                      
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean updateData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE BCS_MASTER SET                        \n");
			sql.append("          PAY_ITEM     = ?                    \n");
			sql.append("         ,ITEM_NAME     = ?                    \n");
			sql.append("         ,EXEAMT        = ?                    \n");
			sql.append("         ,FILE_GB       = ?                    \n");
			sql.append("         ,KIND_GB       = ?                    \n");
			sql.append("         ,UPD_DATE      = SYSDATE              \n");
			sql.append("         ,UPD_BY        = ?                    \n");
			sql.append("         ,PAY_ITEM_NAME = ?                    \n");
			sql.append("         ,ORDER_SEQ     = ?                    \n");
			sql.append("  WHERE DEPT_CD         = ?                    \n");
			sql.append("    AND PAY_ITEM_SEQ    = ?                    \n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM"));
			pstmt.setString(paramIdx++, sqlParams.get("ITEM_NAME"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("EXEAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("FILE_GB"));
			pstmt.setString(paramIdx++, sqlParams.get("KIND_GB"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_NAME"));
			pstmt.setInt(paramIdx++, sqlParams.getInt("ORDER_SEQ"));
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
	 
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	
	private boolean deleteData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM BCS_MASTER WHERE DEPT_CD = ? AND PAY_ITEM_SEQ = ?			\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean updatePreWork(Connection connection) throws Exception {
		PreparedStatement pstmt = null;
		
		
		try {
			StringBuffer sql1 = new StringBuffer();
			StringBuffer sql2 = new StringBuffer();
			StringBuffer sql3 = new StringBuffer();
			sql1.append("  UPDATE BCS_MASTER                                   \n");
			sql1.append("     SET DEPT_CD  = ?                                 \n");
			sql1.append("        ,UPD_DATE = SYSDATE                           \n");
			sql1.append("        ,UPD_BY   = ?                                 \n");
			sql1.append("   WHERE DEPT_CD  = ?     \n");
	        
	        pstmt = connection.prepareStatement(sql1.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.executeUpdate();
			
	        sql2.append("  UPDATE BCS_MR                                       \n");
			sql2.append("     SET DEPT_CD  = ?                                 \n");
			sql2.append("        ,MR_ID  = NVL((SELECT MAX(MR_ID) FROM BCS_MR WHERE DEPT_CD = ? ),0) + ROWNUM \n");
			sql2.append("        ,UPD_DATE = SYSDATE                           \n");
			sql2.append("        ,UPD_BY   = ?                                 \n");
			sql2.append("   WHERE DEPT_CD  = ?     \n");
	        pstmt = connection.prepareStatement(sql2.toString());
	        paramIdx = 1;
	        pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
	        pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.executeUpdate();
	        
			sql3.append("  UPDATE BCS_CO                                       \n");
			sql3.append("     SET DEPT_CD  = ?                                 \n");
			sql3.append("        ,SEQ  = NVL((SELECT MAX(SEQ) FROM BCS_CO WHERE DEPT_CD = ? ),0) + ROWNUM        \n");
			sql3.append("        ,UPD_DATE = SYSDATE                           \n");
			sql3.append("        ,UPD_BY   = ?                                 \n");
			sql3.append("   WHERE DEPT_CD  = ?      \n");
	        				 
	        pstmt = connection.prepareStatement(sql3.toString());
	        paramIdx = 1;
	        pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
	        pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
}