/**
 * Copyright (c) 2003-2004 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.servlet.mallmgr;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SISrchCtgry;
import jp.co.sint.beans.mallmgr.UICtgryListCond;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDeleteRec;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.database.SISpcType;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;
import jp.co.sint.tools.SIURLParameter;// 7.1.1 ST0236 追加

/**
 * @version $Id: SIRegCtrgySrv.java,v 1.0 2003/09/25 Exp $
 * @author Jinwang Chen
 * <br>Description:
 * <p>History</p>
 * <p>Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason</p>
 * ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Jinwang Chen 2003/09/25 14:00:38 Original
 */
public class SIRegCtgrySrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  /**
   * <b>doUpdate</b> HTTP リクエストの処理
   * 
   * @param request リクエスト
   * @param response
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    if (!manLogin.isLogin()) {
      forwardKey(request, response, "webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session = request.getSession(true);// セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加
    
    try {
      String actionName = this.getActionName(urlParam);// 画面からのアクション //7.1.1 ST0236 修正
      String editMode = this.getEditMode(urlParam); // DBへの編集モード //7.1.1 ST0236 修正
      log.debug("actionName=" + actionName + ",editMode=" + editMode);
      
      UICtgryListCond ctgryList = new UICtgryListCond();// カテゴリ商品のリスト
      
      if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 商品カテゴリ関連リスト
        ctgryList.init(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)) {// レコードの削除
        // データの取得
        ctgryList = new UICtgryListCond();
        ctgryList.initReg(request, urlParam);// 7.1.1 ST0236 修正
        if (ctgryList.validateDelete(request, databaseConnection.getConnection())) {// 問題が無ければ
          try {
            deleteTableData(databaseConnection.getConnection(), ctgryList);
            ctgryList.initReset(request, databaseConnection.getConnection());
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
            session.removeAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME);
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else if (SIConfig.SIACTION_RESERVE.equalsIgnoreCase(actionName)){ //ポイントルールの予約
        ctgryList = new UICtgryListCond();
        ctgryList.initReg(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        if(ctgryList.validateInsertBonus(request, databaseConnection.getConnection())){
          try {
            insertBonusData(databaseConnection.getConnection(), ctgryList);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
            session.removeAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME);
          } catch (SIDuplicateKeyException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else if (SIConfig.SIACTION_CANCEL.equalsIgnoreCase(actionName)){ //予約中ポイントルールのキャンセル
        ctgryList = new UICtgryListCond();
        ctgryList.init(request, databaseConnection.getConnection(), urlParam);
        try {
          deleteBonusData(databaseConnection.getConnection(), ctgryList);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
          session.removeAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME);
        } catch (SIDBAccessException sqle) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          sqle.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
        }
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){ //執行中ポイントルールの選択
        ctgryList = new UICtgryListCond();
        ctgryList.init(request, databaseConnection.getConnection(), urlParam);
        ctgryList.initDetail(request, databaseConnection.getConnection());
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)){ //執行中ポイントルールの終了期間変更
        ctgryList = new UICtgryListCond();
        ctgryList.initReg(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        if(ctgryList.validateUpdateBonus(request, databaseConnection.getConnection())){
          try {
            updateBonusData(databaseConnection.getConnection(), ctgryList);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
            session.removeAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME);
          } catch (SIDuplicateKeyException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      } else {// DBへのデータ登録
        // データの取得
        ctgryList.initReg(request, urlParam);// 7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME, ctgryList);
        // データのチェック
        if (ctgryList.validateReg(request, databaseConnection.getConnection())) {
          try {
            if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) {
              insertTableData(databaseConnection.getConnection(), ctgryList);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            } else {
              updateTableData(databaseConnection.getConnection(), ctgryList);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            }
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
            session.removeAttribute(SIConfig.SISESSION_MAN_CTGRY_LIST_NAME);
          } catch (SIDuplicateKeyException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.insert.duplicate2", ctgryList.getCtgryCode()));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.ctgry.list");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * <b>insertTableData</b> 新規のカテゴリレコードを作成します。
   * 
   * @param lConnection DBへのコネクション
   * @param lCtgryList 選択したカテゴリコード
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  // 7.2.0 ST0306 private → public static
  public static void insertTableData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDuplicateKeyException, SIDBAccessException {
    // 1.カテゴリテーブルに新しいレコードの作成
    SIInsertRec lInsertRec = new SIInsertRec("CtgryMTbl");
    lInsertRec.add("CtgryCode", lCtgryList.getCtgryCode());
    lInsertRec.add("ParentCtgryCode", lCtgryList.getParentCtgryCode());
    lInsertRec.add("CtgryName", lCtgryList.getCtgryName());
    lInsertRec.add("CmdtyFlag", lCtgryList.getCmdtyFlagTxt());// 7.2.0 ST0301 追加
    lInsertRec.execute(lConnection);
    
    // 2.商品検索条件テーブルに三つレコードの作成
    updateSrchCtgryTable(lConnection, lCtgryList);
  }
  
  private void insertBonusData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDuplicateKeyException, SIDBAccessException {
    SIInsertRec lInsertRec = new SIInsertRec("BonusPointTbl");
    lInsertRec.add("CtgryCode", lCtgryList.getCtgryCode());
    lInsertRec.add("BonusPointRate", lCtgryList.getBonusPointRate());
    lInsertRec.add("BonusFromDate", lCtgryList.getBonusFromDate());
    lInsertRec.add("BonusToDate", lCtgryList.getBonusToDate());
    lInsertRec.execute(lConnection);
  }
  
  /**
   * <b>updateTableData</b> カテゴリレコードの更新（UPDATE）を行います。
   * 
   * @param lConnection DBへのコネクション
   * @param lCtgryList 選択したカテゴリコード
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  // 7.2.0 ST0306 修正 ここから
  private void updateTableData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDuplicateKeyException, SIDBAccessException {
    updateTableData(lConnection, lCtgryList, true);
  }
  
  /**
   * <b>updateTableData</b> カテゴリレコードの更新（UPDATE）を行います。
   * 
   * @param lConnection DBへのコネクション
   * @param lCtgryList 選択したカテゴリコード
   * @param srchCtgryUpdFlg 商品検索条件テーブルの更新を行うかどうかのフラグ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void updateTableData(Connection lConnection, UICtgryListCond lCtgryList, boolean srchCtgryUpdFlg) throws SIDuplicateKeyException, SIDBAccessException {
    // 7.2.0 ST0306 修正 ここまで
    SIModifyRec lRec = new SIModifyRec("CtgryMTbl");
    
    try {
      lRec.addCondition("CtgryCode", lCtgryList.getCtgryCode());// カテゴリコード
      
      lRec.add("ParentCtgryCode", lCtgryList.getParentCtgryCode());// 親カテゴリコード
      lRec.add("CtgryName", lCtgryList.getCtgryName());// カテゴリ名
      lRec.add("CmdtyFlag", lCtgryList.getCmdtyFlagTxt());// 商品の初期表示 //7.2.0 ST0301 追加
      // 1.カテゴリテーブルの更新
      lRec.execute(lConnection);
      
      // 2.商品検索条件テーブルの更新
      // 7.2.0 ST0306 修正 ここから
      if (srchCtgryUpdFlg) {
        updateSrchCtgryTable(lConnection, lCtgryList);
      }
      // 7.2.0 ST0306 修正 ここまで
      
    } catch (SIDuplicateKeyException sqle) {
      throw new SIDBAccessException(sqle);
    }
  }
  
  private void updateBonusData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDuplicateKeyException, SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("BonusPointTbl");
    
    try {
      lRec.addCondition("CtgryCode", lCtgryList.getCtgryCode());// カテゴリコード
      lRec.addCondition("BonusFromDate", lCtgryList.getBonusFromDate());// ボーナス開始日
      lRec.add("BonusToDate", lCtgryList.getBonusToDate());// ボーナス終了日
      
      lRec.execute(lConnection);
      
    } catch (SIDuplicateKeyException sqle) {
      throw new SIDBAccessException(sqle);
    }
  }
  
  /**
   * <b>deleteTableData</b> 選択したカテゴリコードを商品カテゴリ関連テーブルから削除します。
   * 
   * @param lConnection DBへのコネクション
   * @param lCtgryList 選択したカテゴリコード
   * @return なし
   * @throws SIDBAccessException
   */
  private void deleteTableData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDBAccessException {
    SIDeleteRec lRec = new SIDeleteRec("CtgryMTbl");
    try {
      lRec.addCondition("CtgryCode", lCtgryList.getCtgryCode());// カテゴリコード
      lRec.execute(lConnection);
      
      lRec = new SIDeleteRec("SrchCtgryMTbl");
      lRec.addCondition("CtgryCode", lCtgryList.getCtgryCode());// カテゴリコード
      lRec.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  private void deleteBonusData(Connection lConnection, UICtgryListCond lCtgryList) throws SIDBAccessException {
    String sql = "DELETE FROM bonuspointtbl WHERE bonusfromdate > current_date AND ctgrycode="+SIDBUtil.SQL2Str(lCtgryList.getCtgryCode());
    SIDBUtil.execSQL(lConnection, sql);
  }
  
  // 7.2.0 ST0306 private → public static
  public static void updateSrchCtgryTable(Connection lConnection, UICtgryListCond lCtgryList) throws SIDBAccessException {
    SISrchCtgry lSrchCtgry = lCtgryList.getSrchCtgry();
    
    SIDeleteRec lDeleteRec = new SIDeleteRec("SrchCtgryMTbl");
    SIInsertRec lInsertRec = new SIInsertRec("CtgryMTbl");
    
    try {
      // 1.まず、レコードの削除
      lDeleteRec.addCondition("CtgryCode", lCtgryList.getCtgryCode());// カテゴリコード
      lDeleteRec.execute(lConnection);
      
      // 2.商品検索条件テーブルに三つレコードの作成
      if (SIUtil.isNotNull(lSrchCtgry.getSrchKeyName1())) {
        lInsertRec = new SIInsertRec("SrchCtgryMTbl");
        lInsertRec.add("CtgryCode", lCtgryList.getCtgryCode());
        lInsertRec.add("SrchKeyCode", 1);
        lInsertRec.add("SrchKeyName", lSrchCtgry.getSrchKeyName1());
        lInsertRec.add("DispType", lSrchCtgry.getDispType1());
        lInsertRec.execute(lConnection);
      }
      
      if (SIUtil.isNotNull(lSrchCtgry.getSrchKeyName2())) {
        lInsertRec = new SIInsertRec("SrchCtgryMTbl");
        lInsertRec.add("CtgryCode", lCtgryList.getCtgryCode());
        lInsertRec.add("SrchKeyCode", 2);
        lInsertRec.add("SrchKeyName", lSrchCtgry.getSrchKeyName2());
        lInsertRec.add("DispType", lSrchCtgry.getDispType2());
        lInsertRec.execute(lConnection);
      }
      
      if (SIUtil.isNotNull(lSrchCtgry.getSrchKeyName3())) {
        lInsertRec = new SIInsertRec("SrchCtgryMTbl");
        lInsertRec.add("CtgryCode", lCtgryList.getCtgryCode());
        lInsertRec.add("SrchKeyCode", 3);
        lInsertRec.add("SrchKeyName", lSrchCtgry.getSrchKeyName3());
        lInsertRec.add("DispType", lSrchCtgry.getDispType3());
        lInsertRec.execute(lConnection);
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  public void destroy() {}
}