/**
 * 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.UICmdtyListCond;
import jp.co.sint.beans.mallmgr.UIRegStock;
import jp.co.sint.beans.mallmgr.UIStockListCond;
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.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.SICSVWrite;
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;

/**
 * @version $Id: SIRegStockSrv.java,v 1.0 2003/8/28 Exp $
 * @author  Arai
 * <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/8/28  Original
 */
public class SIRegStockSrv 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 {
      Connection connection;
      
      String actionName = this.getActionName(urlParam);//画面からのアクション
      String editMode = this.getEditMode(urlParam); //DBへの編集モード
      
      UIStockListCond regList = new UIStockListCond();
      UIRegStock regStock = new UIRegStock();
      
      //データベースへのコネクションの作成
      connection = databaseConnection.getConnection();
      
      if (SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)) {
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName) || (SIUtil.isNull(actionName) & SIUtil.isNull(editMode))) {
        regList.init(request,urlParam);
        regList.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_LIST_NAME, regList);
        forwardKey(request, response, "webshop.jsp.manager.stock.list");
      } else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {// CSVファイルへの出力
        regList = (UIStockListCond) session.getAttribute(SIConfig.SISESSION_MAN_STOCK_LIST_NAME);
          if (regList == null) regList = new UIStockListCond();
          produceCmdtyCSVFile(request, response, databaseConnection.getConnection(), regList);
          if (!response.isCommitted()) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
            forwardKey(request, response, "webshop.jsp.manager.stock.list");
          }
      } else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)) {
        regList.init(request,urlParam);
        regStock = new UIRegStock(regList.getStockCode());
        regStock.reset(connection);
        regStock.setEditModeTxt(SIConfig.SIEDIT_MODE_UPDATE);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME, regStock);
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)) {
        regStock = new UIRegStock();
        regStock.setEditModeTxt(SIConfig.SIEDIT_MODE_INSERT);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME, regStock);
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)) {
        if (editMode.equals(SIConfig.SIEDIT_MODE_UPDATE)){
          regStock = new UIRegStock((String) urlParam.getParam("stockCode"));
          regStock.reset(connection);
        } else if (editMode.equals(SIConfig.SIEDIT_MODE_INSERT)) {
          regStock = new UIRegStock();
        }
        regStock.setEditModeTxt(editMode);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME, regStock);
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {//確認画面から明細画面に戻る
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_RETURN.equalsIgnoreCase(actionName)) {//明細画面、完了画面から一覧画面へ戻る
        forwardKey(request, response, "webshop.jsp.manager.stock.list");
      } else if (SIConfig.SIACTION_DISPMODE.equals(actionName)){
        regList.init(request,urlParam);
        try {
          updateTableData2(regList.getStockCode(), connection);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.modify"));
          try {connection.commit();} catch (SQLException sqle) {}
        } catch (SIDuplicateKeyException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          errorSetting(request, regStock, editMode);
          forwardKey(request, response, "webshop.jsp.manager.stock.list");
        } catch (SIDBAccessException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          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.stock.list");
        }
        forwardKey(request, response, "webshop.jsp.manager.stock.list");
      } else if (SIConfig.SIACTION_UPDATE.equals(actionName)){
        regStock.init(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME, regStock);
        try {
          updateTableData3(regStock, connection);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.freeword","「注意事項」を更新しました。"));
          try {connection.commit();} catch (SQLException sqle) {}
        } catch (SIDuplicateKeyException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          errorSetting(request, regStock, editMode);
          forwardKey(request, response, "webshop.jsp.manager.stock.edit");
        } catch (SIDBAccessException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          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.stock.edit");
        }
        forwardKey(request, response, "webshop.jsp.manager.stock.edit");
      } else if (SIConfig.SIACTION_DELETE.equals(actionName)){
        regList.init(request,urlParam);
        try {
          deleteTableData(regList.getStockCode(), connection);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          try {connection.commit();} catch (SQLException sqle) {}
        } catch (SIDuplicateKeyException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          errorSetting(request, regStock, editMode);
          forwardKey(request, response, "webshop.jsp.manager.stock.list");
        } catch (SIDBAccessException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          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.stock.list");
        }
        forwardKey(request, response, "webshop.jsp.manager.stock.list");
      } else if (SIConfig.SIACTION_CONFIRM.equals(actionName)){
        regStock.init(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME, regStock);
        if (regStock.validate(request,connection)) {
          forwardKey(request, response, "webshop.jsp.manager.stock.confirm");
        } else {
          forwardKey(request, response, "webshop.jsp.manager.stock.edit");
        }
      } else if (SIConfig.SIACTION_REGIST.equals(actionName)){
        regStock = (UIRegStock)session.getAttribute(SIConfig.SISESSION_MAN_STOCK_EDIT_NAME);
        try {
          if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) {
            insertTableData(regStock, connection);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try {connection.commit();} catch (SQLException sqle) {}
          } else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)) {
            updateTableData(regStock, connection);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            try {connection.commit();} catch (SQLException sqle) {}
          } else log.error("no known edit mode.editMode=" + editMode);
          forwardKey(request, response, "webshop.jsp.manager.stock.result");
        } catch (SIDuplicateKeyException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          errorSetting(request, regStock, editMode);
          forwardKey(request, response, "webshop.jsp.manager.stock.edit");
        } catch (SIDBAccessException sqle) {
          try {connection.rollback();} catch (SQLException e) {}
          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.stock.edit");
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * errorSetting
   * エラー発生時の入力値を保持します。
   * @param HttpServletRequest , UIRegWrapping , String
   * @return
   * @throws
   */
  public void errorSetting(HttpServletRequest request, UIRegStock regStock, String editMode) {
    HttpSession session=request.getSession(true);//セッションの取得
    
    if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) {
      regStock.setActionNameTxt(SIConfig.SIACTION_NEW);
    } else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)) {
      regStock.setActionNameTxt(SIConfig.SIACTION_LIST);
    }
    session.setAttribute(SIConfig.SISESSION_MAN_STOCK_LIST_NAME, regStock);
    request.setAttribute("remodify", SIConfig.SIACTION_REMODIFY);
  }
  
  /**
  * insertTableData
  * コードを作成します。
  * @param UIStockList
  * @param Connection
  * @return なし
  * @throws SIDuplicateKeyException
  * @throws SIDBAccessException
  */
  public void insertTableData(UIRegStock regStock, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    SIInsertRec lRec = new SIInsertRec("StockMTbl");
    
    lRec.add("StockCode", regStock.getStockCode());
    lRec.add("StockName", regStock.getStockName());
    lRec.add("StockPronName", regStock.getStockPronName());
    lRec.add("PresidentName", regStock.getPresidentName());
    lRec.add("PresidentPronName", regStock.getPresidentPronName());
    lRec.add("Address1", regStock.getAddress1());
    lRec.add("Address2", regStock.getAddress2());
    lRec.add("Address3", regStock.getAddress3());
    lRec.add("PostCode1", regStock.getPostCode1());
    lRec.add("PostCode2", regStock.getPostCode2());
    lRec.add("Email", regStock.getEmail());
    lRec.add("Email2", regStock.getEmail2());
    lRec.add("Email3", regStock.getEmail3());
    lRec.add("Tel", regStock.getTel());
    lRec.add("Fax", regStock.getFax());
    lRec.add("DealerStation", regStock.getDealerStation());
    lRec.add("DealerStaff", regStock.getDealerStaff());
    lRec.add("ChargeCode", regStock.getChargeCode());
    lRec.add("Capital", regStock.getCapital());
    lRec.add("EstablishDate", regStock.getEstablishDate());
    lRec.add("AccountingPeriod", regStock.getAccountingPeriod());
    lRec.add("FinancialInst", regStock.getFinancialInst());
    lRec.add("FinancialBranch", regStock.getFinancialBranch());
    lRec.add("AccountHolder", regStock.getAccountHolder());
    lRec.add("AccountType", regStock.getAccountType());
    lRec.add("AccountNumber", regStock.getAccountNumber());
    lRec.add("CloseType", regStock.getCloseType());
    lRec.add("CloseLimit", regStock.getCloseLimit());
    lRec.add("PayType", regStock.getPayType());
    lRec.add("PayLimit", regStock.getPayLimit());
    lRec.add("Memo", regStock.getMemo());
    
    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(connection);
  }
  
  /**
  * updateTableData
  * レコードを修正します。
  * @param UIStockList
  * @param Connection
  * @return なし
  * @throws SIDuplicateKeyException
  * @throws SIDBAccessException
  */
  private void updateTableData(UIRegStock regStock, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("StockMTbl");
    
    lRec.addCondition("StockCode", regStock.getStockCode());
    lRec.add("StockName", regStock.getStockName());
    lRec.add("StockPronName", regStock.getStockPronName());
    lRec.add("PresidentName", regStock.getPresidentName());
    lRec.add("PresidentPronName", regStock.getPresidentPronName());
    lRec.add("Address1", regStock.getAddress1());
    lRec.add("Address2", regStock.getAddress2());
    lRec.add("Address3", regStock.getAddress3());
    lRec.add("PostCode1", regStock.getPostCode1());
    lRec.add("PostCode2", regStock.getPostCode2());
    lRec.add("Email", regStock.getEmail());
    lRec.add("Email2", regStock.getEmail2());
    lRec.add("Email3", regStock.getEmail3());
    lRec.add("Tel", regStock.getTel());
    lRec.add("Fax", regStock.getFax());
    lRec.add("DealerStation", regStock.getDealerStation());
    lRec.add("DealerStaff", regStock.getDealerStaff());
    lRec.add("ChargeCode", regStock.getChargeCode());
    lRec.add("Capital", regStock.getCapital());
    lRec.add("EstablishDate", regStock.getEstablishDate());
    lRec.add("AccountingPeriod", regStock.getAccountingPeriod());
    lRec.add("FinancialInst", regStock.getFinancialInst());
    lRec.add("FinancialBranch", regStock.getFinancialBranch());
    lRec.add("AccountHolder", regStock.getAccountHolder());
    lRec.add("AccountType", regStock.getAccountType());
    lRec.add("AccountNumber", regStock.getAccountNumber());
    lRec.add("CloseType", regStock.getCloseType());
    lRec.add("CloseLimit", regStock.getCloseLimit());
    lRec.add("PayType", regStock.getPayType());
    lRec.add("PayLimit", regStock.getPayLimit());
    lRec.add("Memo", regStock.getMemo());
    
    log.debug("update sql=" + lRec.getSQL());
    lRec.execute(connection);
  }
  
  private void updateTableData2(String lStockCode, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    String flg = SIDBUtil.getFirstData(connection, "SELECT EnableFlg FROM StockMTbl WHERE StockCode="+SIDBUtil.SQL2Str(lStockCode));
    if (SIUtil.isNotNull(flg)&&"0".equals(flg)) flg = "1";
    else if (SIUtil.isNotNull(flg)&&"1".equals(flg)) flg = "0";
    else throw new SIDBAccessException();
    
    SIModifyRec lRec = new SIModifyRec("StockMTbl");
    
    lRec.addCondition("StockCode", lStockCode);
    lRec.add("EnableFlg", flg);
    
    log.debug("update sql=" + lRec.getSQL());
    lRec.execute(connection);
  }
  
  private void updateTableData3(UIRegStock regStock, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec("StockMTbl");
    
    lRec.addCondition("StockCode", regStock.getStockCode());
    lRec.add("Memo", regStock.getMemo());
    
    log.debug("update sql=" + lRec.getSQL());
    lRec.execute(connection);
  }
  
  /**
  * deleteTableData
  * レコードを削除します。
  * @param UIStockList
  * @param Connection
  * @return なし
  * @throws SIDuplicateKeyException
  * @throws SIDBAccessException
  */
  public void deleteTableData(String lStockCode, Connection connection) throws SIDuplicateKeyException, SIDBAccessException {
    SIDeleteRec lDeleteRec = new SIDeleteRec("StockMTbl");
    
    lDeleteRec.addCondition("StockCode", lStockCode);
    
    log.debug("delete sql=" + lDeleteRec.getSQL());
    lDeleteRec.execute(connection);
  }
  
  /**
   * <b>produceStockCSVFile</b> CSVファイルを作成します。
   * 
   * @param response クライアントまでのresponse
   * @param lConnection DBへのコネクション
   * @param stockList CSVファイルに出力するデータセット
   * @return なし
   * @throws なし
   */
  private boolean produceCmdtyCSVFile(HttpServletRequest request, HttpServletResponse response, Connection lConnection, UIStockListCond stockList) {
    
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_STOCK_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer("SELECT a.* ");
    lSqlBuf.append(",c.chargeName AS chargeCodeName ");
    lSqlBuf.append(",CASE a.AccountType WHEN 0 THEN '普通' WHEN 1 THEN '当座' ELSE NULL END AS AccountTypeName ");
    lSqlBuf.append(",CASE a.CloseType WHEN 0 THEN '当月' WHEN 1 THEN '翌月' ELSE NULL END AS CloseTypeName ");
    lSqlBuf.append(",CASE a.PayType WHEN 0 THEN '当月' WHEN 1 THEN '翌月' ELSE NULL END AS PayTypeName ");
    lSqlBuf.append(",CASE a.EnableFlg WHEN 0 THEN '非表示' WHEN 1 THEN '表示' ELSE NULL END AS EnableFlgName ");
    
    lSqlBuf.append("FROM stockmtbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,COUNT(stockcode) as cmdtyCount FROM individualtbl GROUP BY stockcode) b ON a.stockcode = b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append("WHERE 1=1 ").append(stockList.getConditionSQL());
    lSqlBuf.append("ORDER BY a.stockcode DESC");
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    // タイトルとFieldの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_STOCK_INX));
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
    return true;
  }
  
  public void destroy() {
  }
}