/**
 * 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.beans.mallmgr.UIRankingList;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDateTimeType;
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.SIDateTime;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIHTMLUtil;

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

/**
 * @version $Id: SIRegRankingSrv.java,v 1.0 2004/01/29 Exp $
 * @author  Arai Makoto
 * <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>
 * arai   2003/08/27
 */
public class SIRegRankingSrv 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 {
      Connection connection;

	  String actionName=this.getActionName(urlParam);//画面からのアクション	//7.1.1 ST0236 修正ン
	  String editMode = this.getEditMode(urlParam); //DBへの編集モード	//7.1.1 ST0236 修正

      UIRankingList regList = new UIRankingList();

      //データベースへのコネクションの作成
      connection = databaseConnection.getConnection();

      if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {
        //データの取得とデータのチェック
		regList.init(urlParam);//7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_RANKING_LIST_NAME, regList);
        forwardKey(request, response, "webshop.jsp.manager.ranking.list");
      } else {
        //データの取得
		regList.init(urlParam);//7.1.1 ST0236 修正
        //データのチェック
		if (regList.validate(request, connection,urlParam) == false) {//7.1.1 ST0236 修正
          session.setAttribute(SIConfig.SISESSION_MAN_RANKING_LIST_NAME, regList);
          request.setAttribute("remodify", SIConfig.SIACTION_REMODIFY);
          forwardKey(request, response, "webshop.jsp.manager.ranking.edit");
        } else {
          regList.setActionNameTxt(SIConfig.SIACTION_LIST);
          session.setAttribute(SIConfig.SISESSION_MAN_RANKING_LIST_NAME, regList);
          regList = (UIRankingList) session.getAttribute(SIConfig.SISESSION_MAN_RANKING_LIST_NAME);
          try {
            if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)){
              insertTableData(connection, regList);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
                                   SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            }else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)){
              updateTableData(connection, regList);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
                                   SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            }else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_DELETE)){
        //ヒモ付けられているデータがない場合のみ削除を行います。
        if(regList.validateData(connection)) {
          deleteTableData(connection, regList);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
                     SIErrorFactory.getErrorMsg("manager.message.success.delete"));
        }else{
          if(regList.validateDataExist(connection)){
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.delete.lock2"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }else{
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
                       SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          }
        }
            }else log.error("no known edit mode.editMode=" + editMode);
            forwardKey(request, response, "webshop.jsp.manager.ranking.result");
          } catch (SIDuplicateKeyException sqle) {
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.insert.duplicate"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
            forwardKey(request, response, "webshop.jsp.manager.ranking.edit");
          } catch (SIDBAccessException sqle) {
            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.ranking.edit");
          }
        }
      }
    }catch (SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }

  }

  /**
   * insertTableData
   * データベースにレコードを作成します。
   * @param Connection
   * @param UIRankingList
   * @return
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertTableData(Connection connection, UIRankingList regList) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("MemberLevelMtbl");
    String lNow=(new SIDateTime()).getFullDateTime();

    lRec.add("memberlevel", regList.getMemberLevel());
    lRec.add("memberlevelname", regList.getMemberLevelName());
    lRec.add("discountrate", regList.getDiscountRate());
    if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      lRec.add("INITDATETIME", lNow);
      lRec.add("UPDATEDATETIME", lNow);
    }else{
      lRec.add("INITDATETIME",new SIDateTimeType(lNow));
      lRec.add("UPDATEDATETIME",new SIDateTimeType(lNow));
    }

    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(connection);

    try {
      connection.commit();
    } catch (SQLException sqle) {
      throw new SIDBAccessException("insert data error.");
    }
  }

  /**
   * updateTableData
   * データベースにレコードを修正します。
   * @param Connection
   * @param UIRankingList
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  private void updateTableData(Connection connection, UIRankingList regList) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    SIModifyRec lRec = new SIModifyRec("MemberLevelMtbl");
    String now = (new SIDateTime()).getFullDateTime();

    lRec.addCondition("memberlevelcode", regList.getMemberLevelCode());
    lRec.add("memberlevel", regList.getMemberLevel());
    lRec.add("memberlevelname", regList.getMemberLevelName());
    lRec.add("discountrate", regList.getDiscountRate());
    if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      lRec.add("updatedatetime",now);
    }else{
      lRec.add("updatedatetime",new SIDateTimeType(now));
    }
    log.debug("update sql=" + lRec.getSQL());

    lRec.execute(connection);
    try {
      connection.commit();
    } catch (SQLException sqle) {
      throw new SIDBAccessException("update data error.");
    }
  }

  /**
   * deleteTableData
   * データを取得します。
   * @param connection
   * @param regList
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void deleteTableData(Connection connection, UIRankingList regList) throws SIDuplicateKeyException, SIDBAccessException {
    SIDeleteRec lDeleteRec = new SIDeleteRec();
    StringBuffer lSqlBuf = new StringBuffer();

	lSqlBuf.append("DELETE FROM MemberLevelMtbl WHERE memberlevelcode=");
	lSqlBuf.append(" (SELECT a.memberlevelcode FROM MemberLevelMtbl a ");
	lSqlBuf.append("LEFT OUTER JOIN (SELECT memberlevelcode,COUNT(custcode) as membercount FROM custtbl GROUP BY memberlevelcode ) b ");
	lSqlBuf.append("ON (a.memberlevelcode = b.memberlevelcode) ");
	lSqlBuf.append(" WHERE a.memberlevelcode=").append(regList.getMemberLevelCode()).append(")");

    lDeleteRec.setSQL(lSqlBuf.toString());

    //データの更新
    log.debug("delete sql=" + lDeleteRec.getSQL());
    lDeleteRec.execute(connection);

    try {
      connection.commit();
    } catch (SQLException sqle) {
      throw new SIDBAccessException("delete data error.");
    }
  }

  public void destroy() {
  }
}