/**
 * @(#)file    PayMentList.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 PayMentListBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
   //     //WAFLogger.debug("BidIngListBiz :  ~" + storage.getUser());
	//	//WAFLogger.debug("Outside event : #" + storage.getEvent());
		//WAFLogger.debug("Outside params : " + params.get("rmdr_amt"));
		boolean success = false;
		
		
		if(storage.compareEvent("")){
			ValueObject vo = getPurchaseStatus_sheetCombo();
			storage.addObject("selectText", vo.getObject("DETAILCD"));
			storage.addObject("selectValue", vo.getObject("DETAILNM"));

			RowSet payGroupRowSet=getPayGroupList();
			storage.addRowSet("PAYGROUPLIST", payGroupRowSet);
			
			RowSet payTermsAllRowSet=getPayTermsListAll();
			storage.addRowSet("PAYTERMSALL", payTermsAllRowSet);
			
			changeTermsCode();
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			ValueObject vo = getPurchaseStatus_sheetCombo();
			storage.addObject("selectText", vo.getObject("DETAILCD"));
			storage.addObject("selectValue", vo.getObject("DETAILNM"));		
			
			RowSet payGroupRowSet=getPayGroupList();
			storage.addRowSet("PAYGROUPLIST", payGroupRowSet);
			
			RowSet payTermsAllRowSet=getPayTermsListAll();
			storage.addRowSet("PAYTERMSALL", payTermsAllRowSet);
			
			changeTermsCode();
			
			params.set("sheet1", getPayList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		
		else if(storage.compareEvent("UPDATE_DATA")){
			String[] status = super.gets("sheet1.status");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = statusUpdate(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();
			}
         
		}
	}

	
	
	/**
	 * PayMentList 조회
	 * @param Params
	 * @return
	 */
	private RowSet getPayList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("					SELECT A.DEPT_CD																										\n");
        	sqlstr.append("						, A.DEPT_NAME																										\n");
        	sqlstr.append("						, A.ORD_DOC_NO																										\n");
        	sqlstr.append("					 	, A.CST_DOC_NAME                                                             -- 계약명								\n");
        	sqlstr.append("						, C.MEMBER_NAME                                                              -- 업체명								\n");
        	sqlstr.append("						, F_GET_D_CURRENCY(A.ORD_SUPPLY_AMT, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) AS ORD_SUPPLY_AMT		-- 계약금액			\n");
        	sqlstr.append("						, F_GET_D_CURRENCY(B.CUR_REQ_AMT, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) AS CUR_REQ_AMT        	-- 청구액				\n");
        	sqlstr.append("						, B.TAX_DY                                                                   -- 증빙일자								\n");
        	sqlstr.append("						, A.CURRENCY_GB                                                              -- 통화									\n");
        	sqlstr.append("						, B.TAX_METHOD_CD                                                            -- 발행구분(1:역발행, 2:순발행, 3:Invoice)	\n");
        	sqlstr.append("						, A.PAY_GROUP  AS CONT_PAY_GROUP                                             -- 계약 Pay group						\n");
        	sqlstr.append("						, A.PAYMENT_TERMS AS CONT_PAYMENT_TERMS                                      -- 계약 Payment_terms					\n");
        	sqlstr.append("						, F_GET_CODE_NAME('004', A.PAYMENT_TERMS) AS  CONT_PAYMENT_TERMS_NAME  -- 계약 Payment terms name				\n");
        	sqlstr.append("						, B.PAY_GROUP AS PAY_GROUP                                                   -- pay group							\n");
        	sqlstr.append("						, B.PAYMENT_TERMS  AS PAYMENT_TERMS                                          -- payment terms						\n");
        	sqlstr.append("						, F_GET_CODE_NAME('004', B.PAYMENT_TERMS) AS  PAYMENT_TERMS_NAME       -- payment terms name					\n");
        	sqlstr.append("						, B.REQ_STATUS                                                               -- 대금 상태								\n");
        	sqlstr.append("						, B.PUR_CONT_MOD_ID																									\n");
        	sqlstr.append("					FROM PUR2000T A , PUR1100T B,  CONT_PUR_SUPP C																			\n");
        	sqlstr.append("						WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																			\n");
        	sqlstr.append("							AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID																		\n");
        	sqlstr.append("							AND C.SIGN_SEQ = '2'																							\n");
        	
        	if(!param.get("payDt").isEmpty()){			//증빙년월
        		sqlstr.append("							AND TAX_DY LIKE TO_CHAR(TO_DATE(?,'yyyy-mm'),'yyyymm') || '%'																					\n");
        		search.addParameter(params.get("payDt"));
        	}
        	
        	if(param.get("allSearchFlag").isEmpty()){		//모두 조회
        		sqlstr.append("							AND A.PAYMENT_TERMS <> B.PAYMENT_TERMS																			\n");
         	}
        	
        	// 현장
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND A.DEPT_CD = ? 																												\n");
    			search.addParameter(params.get("P_DEPT_CD"));
        	}
        	
        	if(!param.get("P_BUILD_NM").isEmpty()){
        		sqlstr.append("   AND UPPER(A.CST_DOC_NAME) LIKE UPPER('%"+param.get("P_BUILD_NM")+"%')				\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();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	
	public RowSet getPayGroupList(){
        Search search = new SearchImpl();
        RowSet rowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	sqlstr.append("					SELECT DETAILCD	AS CODE						\n");
        	sqlstr.append("							, DETAILNM AS NAME  				\n");
        	sqlstr.append("						FROM EBID_COM911T       				\n");
        	sqlstr.append("					 	WHERE BASECD 		= '005' 			\n");
             
    		search.setStatement(sqlstr.toString());
    		rowSet = search.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( rowSet == null ){
				rowSet = new RowSetImpl();
            }
            
			return rowSet;
		}
	}
	
	public RowSet getPayTermsList(String param){
        Search search = new SearchImpl();
        RowSet rowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	sqlstr.append("					SELECT CHG_CODE02	AS CODE						\n");
        	sqlstr.append("							, CHG_CODE03 	AS NAME  				\n");
        	sqlstr.append("							, CHG_CODE01   AS PARENT_CODE  				\n");
        	sqlstr.append("						FROM COM911T       				\n");
        	sqlstr.append("					 	WHERE BASECD 		= '006' 			\n");
        	sqlstr.append("					 	AND CHG_CODE01 		=  ? 			\n");
            
        	search.addParameter(param);
        	
    		search.setStatement(sqlstr.toString());
    		rowSet = search.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( rowSet == null ){
				rowSet = new RowSetImpl();
            }
            
			return rowSet;
		}
	}
	
	public RowSet getPayTermsListAll(){
        Search search = new SearchImpl();
        RowSet rowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	sqlstr.append("					SELECT CHG_CODE02	AS CODE						\n");
        	sqlstr.append("							, CHG_CODE03 	AS NAME  				\n");
        	sqlstr.append("						FROM COM911T       				\n");
        	sqlstr.append("					 	WHERE BASECD 		= '006' 			\n");
        	
    		search.setStatement(sqlstr.toString());
    		rowSet = search.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( rowSet == null ){
				rowSet = new RowSetImpl();
            }
            
			return rowSet;
		}
	}
	
	public void changeTermsCode(){
		String[] strArr={"01", "02", "11", "21", "22", "23", "30", "41", "42", "43", "55", "61", "71", "72", "73", "74", "91"};
		for(int i=0;i<strArr.length;i++){
			String payTerms_cd="";
			String payTerms_name="";
			String payTerms_pcd="";
			RowSet payTermsRowSet=getPayTermsList(strArr[i]);
			for(int j=0;j<payTermsRowSet.size();j++){
				if(j+1<payTermsRowSet.size()){
					payTerms_cd = payTerms_cd+(payTermsRowSet.getRow(j).get("CODE"))+"|";
					payTerms_name = payTerms_name+(payTermsRowSet.getRow(j).get("NAME"))+"|";
				}else{
					payTerms_cd = payTerms_cd+(payTermsRowSet.getRow(j).get("CODE"));
					payTerms_name = payTerms_name+(payTermsRowSet.getRow(j).get("NAME"));
				}
		    }
			params.set("PAYTERMSLIST_"+strArr[i]+"_cd", payTerms_cd);
			params.set("PAYTERMSLIST_"+strArr[i]+"_name", payTerms_name);
		}
	}
	
	public RowSet getPurchaseStatus_searchCombo(){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT DETAILCD, DETAILNM             		\n"
                    + " FROM EBID_COM911T      						\n"
                    + " WHERE BASECD = 'PURCHASE_STATUS_CD'   		\n"
		            + " ORDER BY DETAILCD      						\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
	public ValueObject getPurchaseStatus_sheetCombo(){

		ValueObject vo = new ValueObject();
		try{
			RowSet rowSet = getPurchaseStatus_searchCombo();
			
			InitCombo initCombo  = new InitCombo();
			initCombo.makeComboData(rowSet,"DETAILCD", "DETAILNM");
		
			vo.set("DETAILCD", initCombo.getCode());
			vo.set("DETAILNM", initCombo.getCodeName());
		    
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if( vo == null ){
				vo = new ValueObject();
            }
			return vo;
		}
	}
	
	
    public boolean statusUpdate(Connection connection, ValueObject sqlParams) throws Exception  {
        PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE PUR1100T SET    					\n");
	        sql.append("         PAY_GROUP = ?,     			\n");
	        sql.append("         PAYMENT_TERMS = ?      	\n");
	        sql.append("  WHERE PUR_CONT_MOD_ID = ?         			\n");
	        sql.append(" 	AND TAX_DY = ?						\n");
			
	        pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("PAY_GROUP"));
			pstmt.setString(paramIdx++, sqlParams.get("PAYMENT_TERMS"));
			pstmt.setString(paramIdx++, sqlParams.get("PUR_CONT_MOD_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("TAX_DY"));
			
			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);
			}
		}
    }
}