/**
 * Created on 2003/11/12
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package jp.co.sint.servlet.mallmgr;

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

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.UIDeliveryNameListCond;
import jp.co.sint.beans.mallmgr.UIRegDeliveryName;
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.SIPrepared;
import jp.co.sint.database.SISpcType;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
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.SIStringUtil;

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

/**
 * @author arai
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
public class SIRegDeliveryNameSrv 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 修正
      
      UIRegDeliveryName deliveryName = new UIRegDeliveryName();
      session.removeAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME);
      if(SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){//一覧と検索などの画面
        
        //データの取得とデータのチェック
        UIDeliveryNameListCond deliveryNameList = new UIDeliveryNameListCond();
        deliveryNameList.init(request,urlParam);//7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_LIST_NAME, deliveryNameList);
        deliveryName.init(request,urlParam);//7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
        forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
      }else if(SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)){//レコードの修正
        //データの取得とデータのチェック
        deliveryName.initModify(request,urlParam);//7.1.1 ST0236 修正
        if(!deliveryName.validateModify(request,databaseConnection.getConnection(),urlParam)){//エラーがあれば //7.1.1 ST0236 修正
          session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
          forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
        }else{//エラーがなければ
          try{
            //レコードの修正
            updateTableData(databaseConnection.getConnection(), deliveryName);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }catch(SIDBAccessException e){
            session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }
        }
      }else if(SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)){//新規のレコード
        //データの取得とデータのチェック
        deliveryName.initInsert(request,urlParam);//7.1.1 ST0236 修正
        if(!deliveryName.validateInsert(request, databaseConnection.getConnection())){//エラーがあれば
          session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
          forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
        }else{//エラーがなければ
          try{
            //データレコードの作成
            insertTableData(databaseConnection.getConnection(), deliveryName);
            insertTableData_feeRuleMTbl(databaseConnection.getConnection(), deliveryName);
            insertTableData_deliveryFeeMTbl(databaseConnection.getConnection(), deliveryName);
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){throw new SIDBAccessException("insert data error.");}
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }catch (SIDuplicateKeyException e){
            session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.insert.duplicate"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }catch(SIDBAccessException e){
            session.setAttribute(SIConfig.SISESSION_MAN_DELIVERYNAME_EDIT_NAME, deliveryName);
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }
        }
      }else if(SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)){//レコードの削除
        //キーデータの取得とデータのチェック
        deliveryName.initModify(request,urlParam);//7.1.1 ST0236 修正
        if(!deliveryName.validateDelete(request, databaseConnection.getConnection())){//エラーがあれば
          forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
        }else{//エラーがなければ
          Connection connection =databaseConnection.getConnection();
          try{
            //レコードの削
            deleteTableData(connection, deliveryName);
            try {connection.commit();}catch(SQLException ee){}
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }catch (SIDBAccessException e){
            try {connection.rollback();}catch(SQLException ee){}
            e.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            forwardKey(request, response, "webshop.jsp.manager.deliveryname.list");
          }
        }
      }
    }catch (SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
    databaseConnection.close();
  }

  public void insertTableData(Connection lConnection, UIRegDeliveryName deliveryName) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableData(lConnection, deliveryName, false);
  }

  /**
   * <b>insertTableData</b>
   * データベースにレコードを作成します。
   * @param lConnection DBへのコネクション
   * @param regStndrdName 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertTableData(Connection lConnection, UIRegDeliveryName deliveryName, boolean shopCopy) throws SIDuplicateKeyException, SIDBAccessException {
    //SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("DeliveryTypeMtbl");
    //SIDatabaseConnection databaseConnection = new SIDatabaseConnection(); //DBへのコネクションの作成
    
    lRec.add("mallShopCode", deliveryName.getMallShopCode()); //ショップコード
    lRec.add("deliveryTypeCode", deliveryName.getDeliveryTypeCode());
    lRec.add("deliveryTimeFlg", deliveryName.getDeliveryTimeFlg());
    if(!shopCopy){
      lRec.add("deliveryTypeName", deliveryName.getDeliveryTypeName());
    }else{
      lRec.add("deliveryTypeName",  SIStringUtil.null2Space(SIDBUtil.getFirstData(lConnection, " select deliveryTypeName from deliveryTypeMTbl where mallShopCode='0' and deliveryTypeCode=0 ")));
    }
    //データの更新
    lRec.execute(lConnection);
  }
  
  public void insertTableData_deliveryFeeMTbl(Connection lConnection, UIRegDeliveryName deliveryName) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableData_deliveryFeeMTbl(lConnection, deliveryName, false);
  }
  
  /**
   * <b>insertTableData_deliveryFeeMTbl</b>
   * データベースにレコードを作成します。
   * @param lConnection DBへのコネクション
   * @param regStndrdName 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertTableData_deliveryFeeMTbl(Connection lConnection, UIRegDeliveryName deliveryName, boolean shopCopy) throws SIDuplicateKeyException, SIDBAccessException {
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    SITableCondition DD = null;
    
    lSqlBuf = new StringBuffer();
    lSqlBuf.append(" select * from deliveryFeeMTbl where 1=1 and deliveryTypeCode=0"); //デフォルト値
    
    SITableConditionManager lConditionMan = new SITableConditionManager();
    if(!shopCopy){
      DD = new SITableCondition("", "mallshopcode", deliveryName.getMallShopCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    }else{
      DD = new SITableCondition("", "mallshopcode", "0", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    }
    
    DD.setBlankEnable(true);
    lConditionMan.add(DD);
    
    lSqlBuf.append(lConditionMan.getCondtionSQL());
    log.debug("UIDeliveryTimetableListCond lSqlBuf=" + lSqlBuf.toString());
    
    //SISpcType lSpcType = new SISpcType();
    SIPrepared lRec = new SIPrepared("table.deliveryName_DeliveryFeeMTbl.insert.sql");
    
    //検索の条件
    //出力順
    //実行
    try{
      statement = lConnection.createStatement();
      resultSet = statement.executeQuery(lSqlBuf.toString());
      
      while(resultSet.next()){
        lRec = new SIPrepared("table.deliveryName_DeliveryFeeMTbl.insert.sql");
        if(!shopCopy){
          lRec.add(resultSet.getString("mallShopCode"));
        }else{
          lRec.add(deliveryName.getMallShopCode());
        }
        /*修正 postgres8.x対応 ここから
        lRec.add(deliveryName.getDeliveryTypeCode());
        lRec.add(resultSet.getString("prefectureCode"));
        lRec.add(resultSet.getString("prefectureName"));
        lRec.add(resultSet.getString("size1"));
        lRec.add(resultSet.getString("size2"));
        lRec.add(resultSet.getString("size3"));
        lRec.add(resultSet.getString("size4"));
        lRec.add(resultSet.getString("size5"));
        lRec.add(resultSet.getString("size6"));*/
        lRec.add(Integer.parseInt(deliveryName.getDeliveryTypeCode()));
        lRec.add(resultSet.getInt("prefectureCode"));
        lRec.add(resultSet.getString("prefectureName"));
        lRec.add(resultSet.getInt("size1"));
        lRec.add(resultSet.getInt("size2"));
        lRec.add(resultSet.getInt("size3"));
        lRec.add(resultSet.getInt("size4"));
        lRec.add(resultSet.getInt("size5"));
        lRec.add(resultSet.getInt("size6"));
        /* 修正 postgres8.x対応 ここまで*/
        lRec.add(resultSet.getInt("size7"));
        lRec.add(resultSet.getInt("size8"));
        lRec.add(resultSet.getInt("size9"));
        lRec.add(resultSet.getInt("size10"));
        //データの更新
        lRec.execute(lConnection);
      }
    }catch(Exception ex){
      log.debug("exception lSqlBuf = " + lSqlBuf.toString());
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(resultSet, statement);
    }
  }
  
  public void insertTableData_feeRuleMTbl(Connection lConnection, UIRegDeliveryName deliveryName) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableData_feeRuleMTbl(lConnection, deliveryName, false);
  }
  
  /**
   * <b>insertTableData_feeRuleMTbl</b>
   * データベースにレコードを作成します。
   * @param lConnection DBへのコネクション
   * @param regStndrdName 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertTableData_feeRuleMTbl(Connection lConnection, UIRegDeliveryName deliveryName, boolean shopCopy) throws SIDuplicateKeyException, SIDBAccessException {
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer strBuf = new StringBuffer();
    SITableCondition DD = null;
    
    strBuf = new StringBuffer();
    strBuf.append(" select * from feeRuleMTbl where 1=1 and deliveryTypeCode=0"); //デフォルト値
    
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    if(!shopCopy){
      DD = new SITableCondition("", "mallshopcode", deliveryName.getMallShopCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    }else{
      DD = new SITableCondition("", "mallshopcode", "0", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    }
    
    DD.setBlankEnable(true);
    lConditionMan.add(DD);
    
    strBuf.append(lConditionMan.getCondtionSQL());
    log.debug("feeRuleMTbl sqlStatement=" + strBuf.toString());
    
    //SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("feeRuleMTbl");
    
    //検索の条件
    //出力順
    //実行
    try {
      statement = lConnection.createStatement();
      resultSet = statement.executeQuery(strBuf.toString());
      
      while(resultSet.next()){
        lRec = new SIInsertRec("feeRuleMTbl");
        
        if(!shopCopy){
          lRec.add("mallShopCode", resultSet.getString("mallShopCode"));
        }else{
          lRec.add("mallShopCode", deliveryName.getMallShopCode());
        }
        
        lRec.add("deliveryTypeCode", deliveryName.getDeliveryTypeCode());
        lRec.add("freeflg", resultSet.getString("freeflg"));
        lRec.add("price", resultSet.getString("price"));
        lRec.add("multiBuyFlg", resultSet.getString("multiBuyFlg"));
        lRec.add("unitPrice", resultSet.getString("unitPrice"));
        
        //データの更新
        lRec.execute(lConnection);
      }
      
    }catch(Exception ex){
      log.debug("exception sql = " + strBuf.toString());
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(resultSet, statement);
    }
  }
  
  /**
   * <b>updateTableData</b>
   * データベースにレコードを修正します。
   * @param lConnection DBへのコネクション
   * @param regStndrdName 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  private void updateTableData(Connection lConnection, UIRegDeliveryName deliveryName) throws SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("DeliveryTypeMtbl");
    
    lRec.addCondition("mallShopCode", deliveryName.getRegMallShopCode());
    lRec.addCondition("deliveryTypeCode", deliveryName.getRegDeliveryTypeCode());
    lRec.add("deliveryTypeName", deliveryName.getRegDeliveryTypeName());
    lRec.add("deliveryTimeFlg", deliveryName.getRegDeliveryTimeFlg());
    
    try{
      lRec.execute(lConnection);
      lConnection.commit();
    }catch(SIDuplicateKeyException e){
    }catch(SQLException sqle){
      sqle.printStackTrace();
      throw new SIDBAccessException("modify data error.");
    }
  }
  
  private void deleteTableData(Connection lConnection, UIRegDeliveryName deliveryName) throws SIDBAccessException {
    deleteTableData(lConnection, deliveryName,false);
  }
  
  /**
   * <b>deleteTableData</b>
   * データベースにレコードを削除します。
   * @param lConnection DBへのコネクション
   * @param regStndrdName 削除するデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void deleteTableData(Connection lConnection, UIRegDeliveryName deliveryName,boolean shopCopy) throws SIDBAccessException {
    try{
      SIDeleteRec lRec = new SIDeleteRec("DeliveryTypeMtbl");
      lRec.addCondition("mallShopCode", deliveryName.getRegMallShopCode());
      if(!shopCopy){
        lRec.addCondition("deliveryTypeCode", deliveryName.getRegDeliveryTypeCode());
      }
      lRec.execute(lConnection);
      
      lRec = new SIDeleteRec("DeliveryTimeMtbl");
      lRec.addCondition("mallShopCode", deliveryName.getRegMallShopCode());
      if(!shopCopy){
        lRec.addCondition("deliveryTypeCode", deliveryName.getRegDeliveryTypeCode());
      }
      lRec.execute(lConnection);
      
      lRec = new SIDeleteRec("FeeRuleMTbl");
      lRec.addCondition("mallShopCode", deliveryName.getRegMallShopCode());
      if(!shopCopy){
        lRec.addCondition("deliveryTypeCode", deliveryName.getRegDeliveryTypeCode());
      }
      lRec.execute(lConnection);
      
      lRec = new SIDeleteRec("DeliveryFeeMtbl");
      lRec.addCondition("mallShopCode", deliveryName.getRegMallShopCode());
      if(!shopCopy){
        lRec.addCondition("deliveryTypeCode", deliveryName.getRegDeliveryTypeCode());
      }
      lRec.execute(lConnection);
      
    }catch (SIDuplicateKeyException e){}
  }
  
  public void destroy() {
  }
}