/** 
 * Created on 2003/09/25
 *
 * 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.io.PrintWriter;
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.SIMallShop;
import jp.co.sint.beans.mallmgr.UIRegMall;
import jp.co.sint.config.SICSVConf;
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.SIDuplicateKeyException;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.database.SISpcType;
import jp.co.sint.database.SITableCondition;// 7.2.0 ST1051 追加
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SICSVWrite;
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 jp.co.sint.tools.SIUtil;

import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import jp.co.sint.database.SIDBUtil;

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 SIRegMallSrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  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);// セッションの取得
    
    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();
    
    SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加
    String actionName = this.getActionName(urlParam);// 画面からのアクション //7.1.1 ST0236 修正
    String editMode = this.getEditMode(urlParam); // DBへの編集モード //7.1.1 ST0236 修正
    
    UIRegMall regList = new UIRegMall();
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();
    try {
      Connection connection = databaseConnection.getConnection();
      
      if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)) {
        // 編集画面
        regList.init(urlParam);// 7.1.1 ST0236 修正
        forwardKey(request, response, "webshop.jsp.manager.mall.edit");
      } else if (SIConfig.SIACTION_ZIPDIC.equalsIgnoreCase(actionName)) {// 郵便番号辞書ソフトから県名などを検索
        regList.init(urlParam);// 7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
        forwardKey(request, response, "webshop.jsp.manager.mall.edit");
      } else if (SIConfig.SIACTION_REMODIFY.equalsIgnoreCase(actionName)) {
        // 編集画面
        regList = (UIRegMall) session.getAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME);
        regList.setActionNameTxt(SIConfig.SIACTION_REMODIFY);
        session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
        forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        // 7.1.1 ST0176 追加 ここから
      } else if (SIConfig.SIACTION_CTGRY_AMOUNT.equalsIgnoreCase(actionName)) {// カテゴリ件数の集計
        if (execCtgryAmount(connection)) {// バッチの処理
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.batch", "カテゴリ件数"));
        } else {
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
        }
        regList = new UIRegMall();// 再セット
        regList.reset(connection);
        regList.setActionNameTxt(SIConfig.SIACTION_REMODIFY);
        SIConfig.SIMALL = (SIMallShop) regList;
        session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
        forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        // 7.1.1 ST0176 追加 ここまで
      } else if (SIConfig.SIACTION_RANKING.equalsIgnoreCase(actionName)) {// ランキング集計
        regList.reset(connection);
        regList.initRanking(urlParam);// 入力した集計データの取得 //7.1.1 ST0236 修正
        regList.setActionNameTxt(SIConfig.SIACTION_REMODIFY);
        session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
        
        if (regList.validateRanking(request, connection) == false) {// 集計データのチェック
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        } else {
          try {
            updateRankingTableData(regList, connection);// 集計のパラメータデータの更新
            connection.commit();
            if (execRanking(connection)) {// バッチの処理
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.ranking"));
            } else {
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("database.execute.error"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
            }
            
            regList = new UIRegMall();// 再セット
            regList.reset(connection);
            regList.setActionNameTxt(SIConfig.SIACTION_REMODIFY);
            SIConfig.SIMALL = (SIMallShop) regList;
            session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
            forwardKey(request, response, "webshop.jsp.manager.mall.edit");
          } catch (SIDuplicateKeyException e1) {
            e1.printStackTrace();
            connection.rollback();
            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.mall.edit");
          } catch (SIDBAccessException e1) {
            e1.printStackTrace();
            connection.rollback();
            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.mall.edit");
          }
        }
      } else if (SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)) {
        // 確認画面
        regList.init(urlParam);// 7.1.1 ST0236 修正
        
        if (regList.validate(request, connection) == false) {
          regList.setActionNameTxt(SIConfig.SIACTION_REMODIFY);
          session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        } else {
          session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
          forwardKey(request, response, "webshop.jsp.manager.mall.confirm");
        }
      } else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {//原価比較CSV出力
        produceCostCompareCSVFile(request, response, databaseConnection.getConnection());
        if (!response.isCommitted()) {
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        }
      } else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)) {//原価集計、入出庫データ、受注データ、商品マスタ更新
        try {
          execCalculation(connection);
          connection.commit();
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","原価集計が完了しました。"));
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        } catch (SIDuplicateKeyException e1) {
          e1.printStackTrace();
          connection.rollback();
          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.mall.edit");
        } catch (SIDBAccessException e2) {
          e2.printStackTrace();
          connection.rollback();
          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.mall.edit");
        } catch (SQLException e3) {
          e3.printStackTrace();
          connection.rollback();
          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.mall.edit");
        }
      } else if (SIConfig.SIACTION_SPECIAL.equalsIgnoreCase(actionName)) {//原価記録
        try {
          if (execCostRecord(connection)) {
            connection.commit();
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","原価記録が完了しました。"));
          } else {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","未集計データがあるため原価記録は完了しませんでした。"));
          }
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        } catch (SIDuplicateKeyException e1) {
          e1.printStackTrace();
          connection.rollback();
          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.mall.edit");
        } catch (SIDBAccessException e2) {
          e2.printStackTrace();
          connection.rollback();
          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.mall.edit");
        } catch (SQLException e3) {
          e3.printStackTrace();
          connection.rollback();
          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.mall.edit");
        }
      } else if (SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)) {//締処理
        regList.initRecord(urlParam);
        if (regList.validateRecord(request, connection)) {
          try {
            execRecord(regList, connection);// 締処理
            SIDateTime lDate;
            try{
              lDate = new SIDateTime(regList.getRecordDateYear()+"/"+SIUtil.lFillIn(regList.getRecordDateMonth(),2)+"/01",SIConfig.SIDATE_FORMAT);
              lDate.addMonth(1);
            } catch(Exception e) {
              throw new SIDBAccessException();
            }
            SIHacchuSrv.executeClose(connection, lDate.getFullDate(), manLogin.getUserCode());
            connection.commit();
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.record", regList.getRecordDateYear(), regList.getRecordDateMonth()));
            session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
            forwardKey(request, response, "webshop.jsp.manager.mall.edit");
          } catch (SIDuplicateKeyException e1) {
            e1.printStackTrace();
            connection.rollback();
            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.mall.edit");
          } catch (SIDBAccessException e2) {
            e2.printStackTrace();
            connection.rollback();
            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.mall.edit");
          } catch (SQLException e3) {
            e3.printStackTrace();
            connection.rollback();
            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.mall.edit");
          }
        } else {
          session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        }
      } else {
        // DBへのデータ登録
        // データの取得
        regList = (UIRegMall) session.getAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME);
        // データのチェック
        if (regList.validate(request, connection) == false) {
          forwardKey(request, response, "webshop.jsp.manager.mall.edit");
        } else {
          regList.setActionNameTxt(SIConfig.SIACTION_CONFIRM);
          regList.setEditModeTxt(editMode);
          session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
          try {
            if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)) {
              updateTableData(regList, connection);
              connection.commit();
              regList = new UIRegMall();
              regList.reset(connection);
              session.setAttribute(SIConfig.SISESSION_MAN_MALL_EDIT_NAME, regList);
              SIConfig.SIMALL = (SIMallShop) regList;
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            } else log.error("no known edit mode.editMode=" + editMode);
            forwardKey(request, response, "webshop.jsp.manager.mall.result");
          } catch (SIDuplicateKeyException sqle) {
            connection.rollback();
            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.mall.edit");
          } catch (SIDBAccessException sqle) {
            connection.rollback();
            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.mall.edit");
          }
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * updateTableData データベースにレコードを修正します。
   * 
   * @param
   * @return
   * @throws
   */
  private void updateTableData(UIRegMall regList, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    
    SISpcType lSpcType = new SISpcType();
    SIModifyRec lRec = new SIModifyRec("mallshopmtbl");
    
    lRec.addCondition("mallshopcode", regList.getMallShopCode());
    
    lRec.add("status", regList.getStatus());
    lRec.add("mallshopname", regList.getMallShopName());
    lRec.add("mallshopnamekana", regList.getMallShopNameKana());
    lRec.add("adminname", regList.getAdminName());
    lRec.add("adminpass", regList.getAdminPass());
    lRec.add("frontTitle", regList.getFrontTitle());// フロントタイトル
    lRec.add("abbvFrontShopName", regList.getAbbvFrontShopName());
    lRec.add("frontShopNameKana", regList.getFrontShopNameKana());
    lRec.add("departname", regList.getDepartName());
    lRec.add("postcode1", regList.getPostCode1());
    lRec.add("postcode2", regList.getPostCode2());
    lRec.add("address1", regList.getAddress1());
    lRec.add("address2", regList.getAddress2());
    lRec.add("address3", regList.getAddress3());
    lRec.add("tel", regList.getTel());
    lRec.add("fax", regList.getFax());
    lRec.add("email", regList.getEmail());
    lRec.add("chargeusername", regList.getChargeUserName());
    // 7.2.0 ST0539 追加 ここから
    lRec.add("custAttribute0", regList.getCustAttribute0());
    lRec.add("custAttribute1", regList.getCustAttribute1());
    lRec.add("custAttribute2", regList.getCustAttribute2());
    // 7.2.0 ST0539 追加 ここまで
    lRec.add("feeTaxFlg", regList.getFeeTaxFlg());
    lRec.add("deliveryTaxFlg", regList.getDeliveryTaxFlg());
    lRec.add("pointenableflg", regList.getPointEnableFlg());
    lRec.add("custcancelenableflg", regList.getCustCancelEnableFlg());// 7.2.0 ST0276 追加
    lRec.add("discountfromdate", regList.getDiscountFromDate());
    lRec.add("discounttodate", regList.getDiscountToDate());
    lRec.add("discountrate", regList.getDiscountRate());
    lRec.add("instockflg", regList.getInStockFlg());
    lRec.add("daysOfNew", regList.getDaysOfNew());
    lRec.add("amountbyadvice", regList.getAmountByAdvice());
    lRec.add("giftflg", regList.getGiftFlg());
    lRec.add("sSLURL", regList.getSSLURL());
    lRec.add("MDKMERCHANTID", regList.getMdkMerchantID());
    lRec.add("MDKSIGNATUREKEY", regList.getMdkSignatureKey());
    lRec.add("MDKPAYMENTLIMITDAYS", regList.getMdkPaymentLimitDays());
    
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("UpdateDateTime", lSpcType);
    
    lRec.add("RankingDispFlg", regList.getRankingDispFlg());
    lRec.add("RankingFlg", regList.getRankingFlg());
    lRec.add("RankingCount", regList.getRankingCount());
    lRec.add("RankingDays", regList.getRankingDays());
    
    lRec.add("DISCOUNTTYPE", regList.getDiscountType());
    lRec.add("MEMBERDISCOUNTTYPE", regList.getMemberDiscountType());
    lRec.add("SALESDISCOUNTTYPE", regList.getSalesDiscountType());
    
    log.debug("update sql=" + lRec.getSQL());
    
    lRec.execute(connection);
    
    // 商品.一括値引フラグの更新（変更されている場合のみ）
    if (!regList.getDiscountType().equals(regList.getOrgDiscountType())) {
      
      // 更新するテーブルのセット
      lRec = new SIModifyRec("CMDTYMTBL");
      
      // 条件のセット
      lRec.addCondition("SHOPCODE", regList.getMallShopCode());
      
      // 「しない」に変更されたとき（全ての商品を「しない」に更新）
      if (regList.getDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("DISCOUNTFLG", "0");
        // 「しない」から変更された（全ての商品を「する」に更新）
      } else if (regList.getOrgDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("DISCOUNTFLG", regList.getDiscountType());
        // 「する」中で変更された場合
      } else {
        // 条件の追加（「しない」商品は「しない」のまま）
        lRec.addCondition(new SITableCondition("", "DISCOUNTFLG", "0", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        // 更新する列と値のセット
        lRec.add("DISCOUNTFLG", regList.getDiscountType());
      }
      
      // 更新の実行
      lRec.execute(connection);
      
      // 修正後の値セット
      regList.setOrgDiscountType(regList.getDiscountType());
    }
    // 商品.会員値引フラグの更新（変更されている場合のみ）
    if (!regList.getMemberDiscountType().equals(regList.getOrgMemberDiscountType())) {
      
      // 更新するテーブルのセット
      lRec = new SIModifyRec("CMDTYMTBL");
      
      // 条件のセット
      lRec.addCondition("SHOPCODE", regList.getMallShopCode());
      
      // 「しない」に変更されたとき（全ての商品を「しない」に更新）
      if (regList.getMemberDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("MEMBERDISCOUNTFLG", "0");
        // 「しない」から変更された（全ての商品を「する」に更新）
      } else if (regList.getOrgMemberDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("MEMBERDISCOUNTFLG", regList.getMemberDiscountType());
        // 「する」中で変更された場合
      } else {
        // 条件の追加（「しない」商品は「しない」のまま）
        lRec.addCondition(new SITableCondition("", "MEMBERDISCOUNTFLG", "0", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        // 更新する列と値のセット
        lRec.add("MEMBERDISCOUNTFLG", regList.getMemberDiscountType());
      }
      
      // 更新の実行
      lRec.execute(connection);
      
      // 修正後の値セット
      regList.setOrgMemberDiscountType(regList.getMemberDiscountType());
    }
    // 商品.特価値引フラグの更新（変更されている場合のみ）
    if (!regList.getSalesDiscountType().equals(regList.getOrgSalesDiscountType())) {
      
      // 更新するテーブルのセット
      lRec = new SIModifyRec("CMDTYMTBL");
      
      // 条件のセット
      lRec.addCondition("SHOPCODE", regList.getMallShopCode());
      
      // 「しない」に変更されたとき（全ての商品を「しない」に更新）
      if (regList.getSalesDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("SALESDISCOUNTFLG", "0");
        // 「しない」から変更された（全ての商品を「する」に更新）
      } else if (regList.getOrgSalesDiscountType().equals("0")) {
        // 更新する列と値のセット
        lRec.add("SALESDISCOUNTFLG", regList.getSalesDiscountType());
      }
      
      // 更新の実行
      lRec.execute(connection);
      
      // 修正後の値セット
      regList.setOrgSalesDiscountType(regList.getSalesDiscountType());
    }
    // 7.2.0 ST1051 追加 ここまで
    
    try {
      connection.commit();
    } catch (SQLException sqle) {
      throw new SIDBAccessException("update data error.");
    }
    
  }
  
  /**
   * updateTableData データベースにレコードを修正します。
   * 
   * @param
   * @return
   * @throws
   */
  private void updateRankingTableData(UIRegMall regList, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("MallShopmTbl");
    SISpcType lSpcType = new SISpcType();
    
    lRec.addCondition("mallshopcode", regList.getMallShopCode());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("UpdateDateTime", lSpcType);
    lRec.add("RankingDispFlg", regList.getRankingDispFlg());
    lRec.add("RankingFlg", regList.getRankingFlg());
    lRec.add("RankingCount", regList.getRankingCount());
    lRec.add("RankingDays", regList.getRankingDays());
    
    lRec.execute(connection);
  }
  
  /**
   * execRanking 人気順位の集計のバッチを実行します。
   * 
   * @param なし
   * @return バッチの実行の結果、
   * @throws
   */
  public boolean execRanking(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    CallableStatement lCallStatement = null;
    int ii = 0;
    boolean lResult = false;
    
    try {
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lStatement = lConnection.createStatement();
        lResultSet = lStatement.executeQuery("SELECT * FROM RankingFunc()");
        if (lResultSet.next()) {
          ii = lResultSet.getInt(1);
        }
        lConnection.commit();
      } else {
        lCallStatement = lConnection.prepareCall("{CALL RANKINGPROC(?)}");
        lCallStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        lCallStatement.execute();
        ii = lCallStatement.getInt(1);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
      if (lCallStatement != null) {
        try {
          lCallStatement.close();
        } catch (SQLException sqle) {
          sqle.printStackTrace();
        }
      }
    }
    if (ii == 1) lResult = true;
    
    return lResult;
  }
  
  /**
   * updateTableData データベースにレコードを修正します。
   * 
   * @param
   * @return
   * @throws
   */
  private void execRecord(UIRegMall regList, Connection connection) throws SIDuplicateKeyException, SIDBAccessException, SQLException {
    StringBuffer recordSql05 = new StringBuffer();// 追加
    StringBuffer recordSql1 = new StringBuffer();// 帳簿在庫を締処理在庫テーブルに記録
    StringBuffer recordSql15 = new StringBuffer();// 追加
    StringBuffer recordSql2 = new StringBuffer();// 棚卸ヘッダを作成
    StringBuffer recordSql3 = new StringBuffer();// 締処理在庫テーブルを作成（1月専用）
    //StringBuffer recordSql4 = new StringBuffer();// 棚卸在庫テーブルを作成（1月専用）
    //StringBuffer recordSql5 = new StringBuffer();// 原価履歴テーブルを作成
    //原価履歴テーブルはクーロンで作成するよう改修
    StringBuffer recordSql1Append = new StringBuffer();// 未来日付の入出庫数量を締処理在庫テーブルに加算するための差分
    
    String amountName = "amount" + Long.parseLong(regList.getRecordDateMonth());
    String seasonName = regList.getRecordDateYear() + SIUtil.lFillIn(regList.getRecordDateMonth(), 2);
    String yearName = regList.getRecordDateYear();
    
    recordSql05.append("INSERT INTO stocktemp SELECT cmdtycode,individualcode,branchcode,realamount FROM realstockvw WHERE realamount != 0");
    
    recordSql1.append("UPDATE stockrecordtbl SET ").append(amountName).append(" = ");
    recordSql1.append("(SELECT realamount FROM stocktemp WHERE stocktemp.individualcode=stockrecordtbl.individualcode ");
    recordSql1.append("AND stocktemp.branchcode = stockrecordtbl.branchcode AND stocktemp.cmdtycode=stockrecordtbl.cmdtycode) ");
    recordSql1.append("FROM stocktemp WHERE stockrecordtbl.stockyear = ").append(SIDBUtil.SQL2Str(yearName," "));
    recordSql1.append("AND stockrecordtbl.individualcode=stocktemp.individualcode ");
    recordSql1.append("AND stockrecordtbl.branchcode=stocktemp.branchcode AND stockrecordtbl.cmdtycode=stocktemp.cmdtycode");
    
    recordSql15.append("TRUNCATE stocktemp");
    
    /*
    recordSql1.append("UPDATE stockrecordtbl SET ").append(amountName).append(" = ");
    recordSql1.append("(SELECT realamount FROM realstockvw WHERE realstockvw.individualcode=stockrecordtbl.individualcode ");
    recordSql1.append("AND realstockvw.branchcode = stockrecordtbl.branchcode AND realstockvw.cmdtycode=stockrecordtbl.cmdtycode) ");
    recordSql1.append("FROM realstockvw WHERE stockrecordtbl.stockyear = ").append(SIDBUtil.SQL2Str(yearName," "));
    recordSql1.append("AND realstockvw.realamount != 0 AND stockrecordtbl.individualcode=realstockvw.individualcode ");
    recordSql1.append("AND stockrecordtbl.branchcode=realstockvw.branchcode AND stockrecordtbl.cmdtycode=realstockvw.cmdtycode");
    */
    
    recordSql1Append.append("UPDATE stockrecordtbl SET ").append(amountName).append(" = ").append(amountName).append(" + append.amount ");
    recordSql1Append.append("FROM (SELECT cmdtycode,individualcode,branchcode,sum(amount) AS amount FROM ");
    //処理日が翌月1日以降の入庫をマイナス（仕入以外）
    recordSql1Append.append("(SELECT cmdtycode,individualcode,storebranch AS branchcode,sum(CASE delflg WHEN '3' THEN 1 ELSE -1 END * amount) AS amount FROM storehistorytbl ");
    recordSql1Append.append("WHERE calcdatetime >= ").append(SIDBUtil.SQL2Str(regList.getRecordNextDate()," "));
    recordSql1Append.append("AND amount <> 0 AND delflg IN('1','2','3','4') AND storetype <> 0 GROUP BY cmdtycode,individualcode,storebranch ");
    //入庫日が翌月1日以降の入庫をマイナス（仕入）
    recordSql1Append.append("UNION ALL SELECT cmdtycode,individualcode,storebranch AS branchcode,sum(CASE delflg WHEN '3' THEN 1 ELSE -1 END * amount) AS amount FROM storehistorytbl ");
    recordSql1Append.append("WHERE initdatetime >= ").append(SIDBUtil.SQL2Str(regList.getRecordNextDate()," "));
    recordSql1Append.append("AND amount <> 0 AND delflg IN('1','2','3','4') AND storetype = 0 GROUP BY cmdtycode,individualcode,storebranch ");
    //処理日が翌月1日以降の出庫をプラス（販売以外）
    recordSql1Append.append("UNION ALL SELECT cmdtycode,individualcode,shipbranch AS branchcode,sum(CASE delflg WHEN '3' THEN -1 ELSE 1 END * amount) AS amount FROM shiphistorytbl ");
    recordSql1Append.append("WHERE calcdatetime >= ").append(SIDBUtil.SQL2Str(regList.getRecordNextDate()," "));
    recordSql1Append.append("AND amount <> 0 AND delflg IN('1','2','3','4') AND shiptype <> 0 GROUP BY cmdtycode,individualcode,shipbranch ");
    //出庫日が翌月1日以降の出庫をプラス（販売）
    recordSql1Append.append("UNION ALL SELECT cmdtycode,individualcode,shipbranch AS branchcode,sum(CASE delflg WHEN '3' THEN -1 ELSE 1 END * amount) AS amount FROM shiphistorytbl ");
    recordSql1Append.append("WHERE initdatetime >= ").append(SIDBUtil.SQL2Str(regList.getRecordNextDate()," "));
    recordSql1Append.append("AND amount <> 0 AND delflg IN('1','2','3','4') AND shiptype = 0 GROUP BY cmdtycode,individualcode,shipbranch) list ");
    recordSql1Append.append("GROUP BY cmdtycode,individualcode,branchcode HAVING sum(amount) <> 0) append ");
    recordSql1Append.append("WHERE stockrecordtbl.stockyear = ").append(SIDBUtil.SQL2Str(yearName," "));
    recordSql1Append.append("AND stockrecordtbl.cmdtycode = append.cmdtycode ");
    recordSql1Append.append("AND stockrecordtbl.individualcode = append.individualcode ");
    recordSql1Append.append("AND stockrecordtbl.branchcode = append.branchcode ");
    
    recordSql2.append("INSERT INTO inventorytbl SELECT DISTINCT branchcode,").append(SIDBUtil.SQL2Str(seasonName));
    recordSql2.append(",0,CURRENT_DATE FROM stockrecordtbl WHERE ").append(amountName).append(" != 0");
    
    recordSql3.append("INSERT INTO stockrecordtbl (cmdtycode,individualcode,branchcode,stockyear) SELECT cmdtycode,individualcode,branchcode,");
    recordSql3.append(SIDBUtil.SQL2Str(yearName," FROM storestocktbl"));
    
    //recordSql4.append("INSERT INTO inventorystocktbl (cmdtycode,individualcode,branchcode,inventoryyear) SELECT cmdtycode,individualcode,branchcode,");
    //recordSql4.append(SIDBUtil.SQL2Str(yearName," FROM storestocktbl"));
    
    /*
    recordSql5.append("INSERT INTO costhistorytbl SELECT cmdtycode,individualcode,").append(SIDBUtil.SQL2Str(seasonName));
    recordSql5.append(",purchaseprice,processingexpence FROM individualtbl");
    */
    
    if (regList.getRecordDateMonth().equals("01")) {// 1月の締処理時に該当年分の締処理在庫、棚卸在庫テーブルを作成
      SIDBUtil.execSQL(connection, recordSql3.toString());
      //SIDBUtil.execSQL(connection, recordSql4.toString());
    }
    SIDBUtil.execSQL(connection, recordSql05.toString());
    SIDBUtil.execSQL(connection, recordSql1.toString());
    SIDBUtil.execSQL(connection, recordSql15.toString());
    SIDBUtil.execSQL(connection, recordSql1Append.toString());
    SIDBUtil.execSQL(connection, recordSql2.toString());
    //SIDBUtil.execSQL(connection, recordSql5.toString());
  }
  
  // 7.1.1 ST0176 追加 ここから
  /**
   * execCtgryAmount カテゴリ件数の集計のバッチを実行します。
   * 
   * @param なし
   * @return バッチの実行の結果、
   * @throws
   */
  public boolean execCtgryAmount(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    CallableStatement lCallStatement = null;
    int ii = 0;
    boolean lResult = false;
    
    try {
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lStatement = lConnection.createStatement();
        lResultSet = lStatement.executeQuery("SELECT * FROM CtgryAmountFunc()");
        if (lResultSet.next()) {
          ii = lResultSet.getInt(1);
        }
        lConnection.commit();
      } else {
        lCallStatement = lConnection.prepareCall("{CALL CTGRYAMOUNTPROC(?)}");
        lCallStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        lCallStatement.execute();
        ii = lCallStatement.getInt(1);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
      if (lCallStatement != null) {
        try {
          lCallStatement.close();
        } catch (SQLException sqle) {
          sqle.printStackTrace();
        }
      }
    }
    if (ii == 1) lResult = true;
    
    return lResult;
  }
  
  private void execCalculation(Connection connection) throws SIDuplicateKeyException, SIDBAccessException, SQLException {
      StringBuffer calcSql1 = new StringBuffer();
      StringBuffer calcSql2 = new StringBuffer();
      StringBuffer calcSql3 = new StringBuffer();
      
      //原価集計SQL１（予定原価更新）
      calcSql1.append("UPDATE individualtbl SET purchaseprice=trunc((coalesce(c.purchaseprice,0)*coalesce(s.amount,0)+v.purchaseprice)/(coalesce(s.amount,0)+v.amount)) ");
      calcSql1.append(",processingexpence=trunc((coalesce(c.processingexpence,0)*coalesce(s.amount,0)+v.processingexpence)/(coalesce(s.amount,0)+v.amount)) ");
      calcSql1.append("FROM ");
      calcSql1.append("individualtbl i,costcalculationvw v LEFT OUTER JOIN ");
      calcSql1.append("(SELECT s.individualcode, ");
      calcSql1.append("CASE substring(i.season,5,2) WHEN '01' THEN sum(s.amount1) WHEN '02' THEN sum(s.amount2) ");
      calcSql1.append("WHEN '03' THEN sum(s.amount3) WHEN '04' THEN sum(s.amount4) WHEN '05' THEN sum(s.amount5) ");
      calcSql1.append("WHEN '06' THEN sum(s.amount6) WHEN '07' THEN sum(s.amount7) WHEN '08' THEN sum(s.amount8) ");
      calcSql1.append("WHEN '09' THEN sum(s.amount9) WHEN '10' THEN sum(s.amount10) WHEN '11' THEN sum(s.amount11) ");
      calcSql1.append("ELSE sum(s.amount12) END AS amount ");
      calcSql1.append("FROM stockrecordtbl s,(SELECT max(season) AS season FROM inventorytbl) i ");
      calcSql1.append("WHERE s.stockyear=substring(i.season,1,4) ");
      calcSql1.append("GROUP BY i.season,s.individualcode) s ");
      calcSql1.append("ON v.individualcode=s.individualcode ");
      calcSql1.append("LEFT OUTER JOIN ");
      calcSql1.append("(SELECT c.individualcode,c.purchaseprice,c.processingexpence FROM costhistorytbl c,(SELECT max(season) AS season FROM inventorytbl) i WHERE c.season=i.season) c ");
      calcSql1.append("ON v.individualcode=c.individualcode ");
      calcSql1.append("WHERE v.individualcode=i.individualcode AND coalesce(s.amount,0)+v.amount<>0 ");
      calcSql1.append("AND (trunc((coalesce(c.purchaseprice,0)*coalesce(s.amount,0)+v.purchaseprice)/(coalesce(s.amount,0)+v.amount))<>i.purchaseprice ");
      calcSql1.append(" OR trunc((coalesce(c.processingexpence,0)*coalesce(s.amount,0)+v.processingexpence)/(coalesce(s.amount,0)+v.amount))<>i.processingexpence) ");
      calcSql1.append("AND individualtbl.individualcode=v.individualcode");
      
      //原価集計SQL２（出庫履歴更新）
      calcSql2.append("UPDATE shiphistorytbl SET purchaseprice=shiphistorytbl.amount*individualtbl.purchaseprice,processingexpence=shiphistorytbl.amount*individualtbl.processingexpence ");
      calcSql2.append("FROM individualtbl ");
      calcSql2.append("WHERE individualtbl.individualcode=shiphistorytbl.individualcode AND individualtbl.amountflg='1' ");
      calcSql2.append("AND shiphistorytbl.shiptype IN ('0','1','4','8','13','14','15','16') ");
      calcSql2.append("AND shiphistorytbl.delflg IN ('1','4') ");
      calcSql2.append("AND NOT(shiphistorytbl.shiptype='0' AND shiphistorytbl.ordercode IN (SELECT ordercode FROM orderlatestvw WHERE status='2')) ");
      calcSql2.append("AND shiphistorytbl.initdatetime >= (SELECT (substring(max(season),1,4)||'-'||substring(max(season),5,2)||'-01')::date + '1 month'::interval FROM inventorytbl) ");
      calcSql2.append("AND shiphistorytbl.initdatetime < (SELECT (substring(max(season),1,4)||'-'||substring(max(season),5,2)||'-01')::date + '2 month'::interval FROM inventorytbl) ");
      calcSql2.append("AND shiphistorytbl.amount<>0 ");
      calcSql2.append("AND (shiphistorytbl.purchaseprice<>shiphistorytbl.amount*individualtbl.purchaseprice OR shiphistorytbl.processingexpence<>shiphistorytbl.amount*individualtbl.processingexpence)");
      
      //原価集計SQL３（受注明細更新）
      calcSql3.append("UPDATE orderdetailtbl SET purchaseprice=individualtbl.purchaseprice,processingexpence=individualtbl.processingexpence ");
      calcSql3.append("FROM individualtbl,ordertbl ");
      calcSql3.append("WHERE individualtbl.individualcode=orderdetailtbl.individualcode AND individualtbl.amountflg='1' ");
      calcSql3.append("AND orderdetailtbl.ordercode=ordertbl.ordercode AND orderdetailtbl.orderbranchcode=ordertbl.orderbranchcode ");
      calcSql3.append("AND ordertbl.status='1' AND ordertbl.enabledflg='1' ");
      calcSql3.append("AND orderdetailtbl.shippmentdate >= (SELECT (substring(max(season),1,4)||'-'||substring(max(season),5,2)||'-01')::date + '1 month'::interval FROM inventorytbl) ");
      calcSql3.append("AND orderdetailtbl.shippmentdate < (SELECT (substring(max(season),1,4)||'-'||substring(max(season),5,2)||'-01')::date + '2 month'::interval FROM inventorytbl) ");
      calcSql3.append("AND (orderdetailtbl.purchaseprice<>individualtbl.purchaseprice OR orderdetailtbl.processingexpence<>individualtbl.processingexpence)");
      
      SIDBUtil.execSQL(connection, calcSql1.toString());
      SIDBUtil.execSQL(connection, calcSql2.toString());
      SIDBUtil.execSQL(connection, calcSql3.toString());
  }
  
  private boolean execCostRecord(Connection connection) throws SIDuplicateKeyException, SIDBAccessException, SQLException {
    StringBuffer recordSql = new StringBuffer();
    recordSql.append("INSERT INTO costhistorytbl SELECT cmdtycode,individualcode,TO_CHAR(current_date + '-1 month'::interval,'YYYYMM'),purchaseprice,processingexpence FROM individualtbl");
    
    if (SIDBUtil.hasData(connection, "SELECT * FROM costcomparevw WHERE checkflg='変動あり'")) {
      return false;
    } else {
      SIDBUtil.execSQL(connection, recordSql.toString());
      return true;
    }
  }
  
  private boolean produceCostCompareCSVFile(HttpServletRequest request, HttpServletResponse response, Connection lConnection) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_COSTCOMPARE_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の設定
    lCsv.setSqlStatement("SELECT * FROM costcomparevw");
    // タイトルとFieldの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_COSTCOMPARE_LIST_INX));
    if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {} else {
      lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_COSTCOMPARE_LIST_INX));
    }
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
    return true;
  }
  // 7.1.1 ST0176 追加 ここまで
  public void destroy() {}
}