/**
 * Copyright (c) 2003-2005 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.UIRegCompany;
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.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.SIDateTime;

import org.apache.log4j.Category;
import jp.co.sint.tools.SIURLParameter;

public class SIRegCompanySrv 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);
    
    try {
      String actionName = this.getActionName(urlParam);// 画面からのアクション
      
      session.removeAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME);
      
      if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {
        forwardKey(request, response, "webshop.jsp.manager.company");
      } else if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)) {// レコードの更新
        // データの取得とデータのチェック
        UIRegCompany regCompany = new UIRegCompany();
        regCompany.initModify(request, urlParam);
        if (!regCompany.validateModify(request)) {// エラーがあれば
          session.setAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME, regCompany);
          forwardKey(request, response, "webshop.jsp.manager.company");
        } else {// エラーがなければ
          try {
            // レコードの修正
            updateTableData(databaseConnection.getConnection(), regCompany);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
            forwardKey(request, response, "webshop.jsp.manager.company");
          } catch (SIDBAccessException e) {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
            session.setAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME, regCompany);
            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.company");
          }
        }
      } else if (SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)) {// レコードの新規作成
        // データの取得とデータのチェック
        UIRegCompany regCompany = new UIRegCompany();
        regCompany.initInsert(request, urlParam);
        if (!regCompany.validateInsert(request, databaseConnection.getConnection())) {// エラーがあれば
          session.setAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME, regCompany);
          forwardKey(request, response, "webshop.jsp.manager.company");
        } else {// エラーがなければ
          try {
            // データレコードの作製
            insertTableData(databaseConnection.getConnection(), regCompany);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
            forwardKey(request, response, "webshop.jsp.manager.company");
          } catch (SIDuplicateKeyException e) {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {
            }
            session.setAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME, regCompany);
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.insert.duplicate2", "支店マスタ"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            forwardKey(request, response, "webshop.jsp.manager.company");
          } catch (SIDBAccessException e) {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
            session.setAttribute(SIConfig.SISESSION_MAN_COMPANY_EDIT_NAME, regCompany);
            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.company");
          }
        }
      } else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)) {// レコードの削除
        // キーデータの取得とデータのチェック
        UIRegCompany regCompany = new UIRegCompany();
        regCompany.initDelete(request, urlParam);
        if (!regCompany.validateDelete(request, databaseConnection.getConnection())) {// エラーがあれば
          forwardKey(request, response, "webshop.jsp.manager.company");
        } else {// エラーがなければ
          try {
            // レコードの削除
            deleteTableData(databaseConnection.getConnection(), regCompany);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
            forwardKey(request, response, "webshop.jsp.manager.company");
          } catch (SIDBAccessException e) {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
            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.company");
          }
        }
      }
    } 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 regCompany 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableData(Connection lConnection, UIRegCompany regCompany) throws SIDuplicateKeyException, SIDBAccessException {
    SIDateTime lDateTime = new SIDateTime();
    
    SIInsertRec lRec = new SIInsertRec("BranchTbl");
    lRec.add("BranchCode", regCompany.getRegCompanyCodeTxt());// 支店コード
    lRec.add("BranchName", regCompany.getRegCompanyNameTxt());// 支店名
    lRec.add("EMail", regCompany.getRegCompanyMailTxt());// メールアドレス
    lRec.add("BranchFlg", regCompany.getRegCompanyTypeTxt());// 区分
    lRec.add("PublicFlg", regCompany.getRegPublicFlgTxt());// 公開フラグ
    lRec.add("PriorityBase", regCompany.getRegPriorityBase());// 引き当て優先順位ベース
    lRec.add("InitDatetime", lDateTime.getFullDateTime());// 登録日時
    lRec.add("UpdateDatetime", lDateTime.getFullDateTime());// 更新日時
    
    StringBuffer lSql1 = new StringBuffer("INSERT INTO storestocktbl ");
    lSql1.append("SELECT 0,cmdtycode,individualcode,").append(regCompany.getRegCompanyCodeTxt());
    lSql1.append(",26,0,current_date,current_date FROM individualtbl");
    
    StringBuffer lSql2 = new StringBuffer("INSERT INTO stockrecordtbl ");
    lSql2.append("SELECT cmdtycode,individualcode,").append(regCompany.getRegCompanyCodeTxt());
    lSql2.append(",CASE WHEN TO_CHAR(current_date,'mm') < '05' THEN TO_CHAR(current_timestamp - '1 years'::interval,'yyyy')");
    lSql2.append(" ELSE TO_CHAR(current_date,'yyyy') END,0,0,0,0,0,0,0,0,0,0,0,0 FROM individualtbl");
    lSql2.append(" WHERE EXISTS(SELECT individualcode FROM stockrecordtbl WHERE stockrecordtbl.individualcode=individualtbl.individualcode)");
    
    /*
    StringBuffer lSql3 = new StringBuffer("INSERT INTO inventorystocktbl ");
    lSql3.append("SELECT cmdtycode,individualcode,").append(regCompany.getRegCompanyCodeTxt());
    lSql3.append(",CASE WHEN TO_CHAR(current_date,'mm') < '05' THEN TO_CHAR(current_timestamp - '1 years'::interval,'yyyy')");
    lSql3.append(" ELSE TO_CHAR(current_date,'yyyy') END,0,0,0,0,0,0,0,0,0,0,0,0 FROM individualtbl");
    lSql3.append(" WHERE EXISTS(SELECT individualcode FROM inventorystocktbl WHERE inventorystocktbl.individualcode=individualtbl.individualcode)");
    */
    
    // データの更新
    lRec.execute(lConnection);
    SIDBUtil.execSQL(lConnection, lSql1.toString());
    SIDBUtil.execSQL(lConnection, lSql2.toString());
    //SIDBUtil.execSQL(lConnection, lSql3.toString());
    
  }
  
  /**
   * <b>updateTableData</b> データベースのレコードを修正します。
   * 
   * @param lConnection DBへのコネクション
   * @param regCompany 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void updateTableData(Connection lConnection, UIRegCompany regCompany) throws SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("BranchTbl");
    SIDateTime lDateTime = new SIDateTime();
    
    lRec.addCondition("BranchCode", regCompany.getRegCompanyCodeTxt());// 支店コード
    lRec.add("BranchName", regCompany.getRegCompanyNameTxt());// 支店名
    lRec.add("EMail", regCompany.getRegCompanyMailTxt());// メールアドレス
    lRec.add("PublicFlg", regCompany.getRegPublicFlgTxt());// 公開フラグ
    lRec.add("PriorityBase", regCompany.getRegPriorityBase());// 引き当て優先順位ベース
    lRec.add("PostCode1", regCompany.getRegPostCode1());// 郵便番号１
    lRec.add("PostCode2", regCompany.getRegPostCode2());// 郵便番号２
    lRec.add("Address1", regCompany.getRegAddress1());// 住所１
    lRec.add("Address2", regCompany.getRegAddress2());// 住所２
    lRec.add("Address3", regCompany.getRegAddress3());// 住所３
    lRec.add("Tel1", regCompany.getRegTel1());// 電話番号１
    lRec.add("Tel2", regCompany.getRegTel2());// 電話番号２
    lRec.add("Fax1", regCompany.getRegFax1());// FAX番号１
    lRec.add("Fax2", regCompany.getRegFax2());// FAX番号２
    lRec.add("UpdateDatetime", lDateTime.getFullDateTime());// 更新日時
    
    try {
      lRec.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      e.printStackTrace();
      throw new SIDBAccessException();
    }
  }
  
  /**
   * <b>deleteTableData</b> データベースのレコードを削除します。
   * 
   * @param lConnection DBへのコネクション
   * @param regCompany 削除するデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  
  private void deleteTableData(Connection lConnection, UIRegCompany regCompany) throws SIDBAccessException {
    try {
      SIDeleteRec lRec = new SIDeleteRec("BranchTbl");
      lRec.addCondition("BranchCode", regCompany.getRegCompanyCodeTxt());// 支店コード
      lRec.execute(lConnection);
      
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException("delete data error.");
    }
  }
  
  public void destroy() {
  }
}