package jp.co.sint.servlet.mallmgr;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SIHacchuDetail;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIMailTemp;
import jp.co.sint.basic.SIPDFUtil;
import jp.co.sint.basic.SITax;
import jp.co.sint.beans.mallmgr.UIHacchuDetailListCond;
import jp.co.sint.beans.mallmgr.UIHacchuListCond;
import jp.co.sint.beans.mallmgr.UIHacchuPaymentListCond;
import jp.co.sint.beans.mallmgr.UIOrderListCond;
import jp.co.sint.beans.mallmgr.UIRegHacchu;
import jp.co.sint.beans.mallmgr.UIRegHacchuPayment;
import jp.co.sint.beans.mallmgr.UIStockHistory;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.config.SIPDFConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.docs.HacchuPdf;
import jp.co.sint.mail.SIMailUtil;
import jp.co.sint.mail.SISendMail;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICheckUtil;
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.SIFatalException;
import jp.co.sint.tools.SIFlagUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIStringUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

import com.lowagie.text.DocumentException;

import jp.co.sint.tools.SIURLParameter;

public class SIHacchuSrv extends SIServlet {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //決裁閾値
  private static String threshold = "100000";
  
  /**
   * <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);//画面からのアクション
      String editMode = this.getEditMode(urlParam);//DBへの編集モード
      
      UIHacchuListCond hacchuList = new UIHacchuListCond();
      UIRegHacchu hacchuEdit = new UIRegHacchu();
      UIRegHacchuPayment hacchuPayment = new UIRegHacchuPayment();
      UIHacchuDetailListCond hacchuDetailList = new UIHacchuDetailListCond();
      UIHacchuPaymentListCond hacchuPaymentList = new UIHacchuPaymentListCond();
      
      if(this.getServletPath(request).equals("/mallmgr/SIHacchuSrv")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuList.init(request, urlParam);
          hacchuList.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_HACCHU_LIST);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_LIST_NAME,hacchuList);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.list");
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.hacchu.list");
        }else if (SIConfig.SIACTION_APPROVAL.equals(actionName)){
          hacchuEdit.initModify(urlParam);
          hacchuEdit.reset(databaseConnection.getConnection());
          try {
            approvalData(databaseConnection.getConnection(),hacchuEdit);
            if (sendApproveMail(databaseConnection.getConnection(), hacchuEdit, manLogin.getUserCode())) {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.approve.mail"));
            } else {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.approve.mail2"));
            }
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.hacchu.list");
          }catch (SIDuplicateKeyException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.list");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.list");
          }
        }else if (SIConfig.SIACTION_REJECTION.equals(actionName)){
          hacchuEdit.initModify(urlParam);
          hacchuEdit.reset(databaseConnection.getConnection());
          try {
            cancelData(databaseConnection.getConnection(),hacchuEdit);
            if (sendRejectionMail(databaseConnection.getConnection(), hacchuEdit, manLogin.getUserCode())) {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.reject.mail"));
            } else {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.reject.mail2"));
            }
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.hacchu.list");
          }catch (SIDuplicateKeyException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.list");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.list");
          }
        }
      }else if(this.getServletPath(request).equals("/mallmgr/HacchuUpdate")){
        if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)){
          hacchuEdit.initModify(urlParam);
          hacchuEdit.reset(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
        }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){
          hacchuEdit.initModify(urlParam);
          hacchuEdit.reset(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.edit");
        }else if(SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDetail2(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
        }else if (SIConfig.SIACTION_REDO.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDeliveryFee(request, urlParam);
          String hacchuCode = (String) urlParam.getParam("hacchuCode");
          if(hacchuEdit.validateRedo(request,databaseConnection.getConnection())&&hacchuEdit.validateModify(request, databaseConnection.getConnection(),hacchuCode,true)){
            try {
              //データレコードの作製
              updateDeliveryFee(databaseConnection.getConnection(),hacchuEdit);
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.hacchu.edit");
            }catch (SIDuplicateKeyException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.hacchu.edit");
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.hacchu.edit");
            }
          }else{
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.hacchu.edit");
          }
        }else if(SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDetail2(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME,copyEditData(hacchuEdit));
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update.cmdty");
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          session.removeAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
        }else if (SIConfig.SIACTION_CONFIRM.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDetail2(request, urlParam);
          String hacchuCode = (String) urlParam.getParam("hacchuCode");
          if(hacchuEdit.validateDetail(request,false)&&hacchuEdit.validateModify(request, databaseConnection.getConnection(),hacchuCode,false)){
            hacchuEdit.setupDetailColl();
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.hacchu.con");
          }else{
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
          }
        }else if (SIConfig.SIACTION_REGIST.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          try {
            //データレコードの作製
            updateTableData(databaseConnection.getConnection(),hacchuEdit);
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.res");
          }catch (SIDuplicateKeyException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }
        }else if (actionName.equals(SIConfig.SIACTION_PDF)) {
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          String hacchuCode = hacchuEdit.getHacchuCode();
          UIRegHacchu hacchuEditTmp = new UIRegHacchu(hacchuCode,"");
          hacchuEditTmp.reset(databaseConnection.getConnection());
          String remarks = (String) urlParam.getParam("remarks");
          String updateFlg = (String) urlParam.getParam("updateFlg");
          this.producePDFFile(response, databaseConnection.getConnection(), hacchuEditTmp, remarks);
          if("1".equals(updateFlg)) {
            String outputDateTime = this.setOutputTime(databaseConnection.getConnection(), hacchuEditTmp, manLogin.getUserCode());
            hacchuEdit.setHacchuUpdateDateTime(outputDateTime);
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          }
          try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
          if (!response.isCommitted()) forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
        }else if (SIConfig.SIACTION_CANCEL.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          String hacchuCode = hacchuEdit.getHacchuCode();
          hacchuEdit = new UIRegHacchu(hacchuCode,"");
          hacchuEdit.reset(databaseConnection.getConnection());
          String updateMode = (String) urlParam.getParam("updateMode");
          try {
            String hacchuCodeTxt = (String) urlParam.getParam("hacchuCode");
            boolean isReturn = !updateMode.equals(SIConfig.SIORDER_STATUS_CANCEL);
            if (hacchuEdit.validateModify(request, databaseConnection.getConnection(), hacchuCodeTxt, isReturn)){
              if (updateMode.equals(SIConfig.SIORDER_STATUS_CANCEL)) {
                cancelData(databaseConnection.getConnection(),hacchuEdit);
                hacchuEdit.setStatus("0");
                session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
                request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.cancel"));
              } else if (updateMode.equals(SIConfig.SIORDER_STATUS_RETURN)) {
                if (hacchuEdit.validateReturn(request,databaseConnection.getConnection())){
                  returnData(databaseConnection.getConnection(),hacchuEdit);
                  hacchuEdit.setStatus("2");
                  session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
                  request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.return"));
                }
              } else if (updateMode.equals(SIConfig.SIORDER_STATUS_KEEPRETURN)) {
                if (hacchuEdit.validateReturn(request,databaseConnection.getConnection())){
                  returnData(databaseConnection.getConnection(),hacchuEdit);
                  String newHacchuCode = createHacchuReturn(databaseConnection.getConnection(), hacchuEdit.getHacchuCode());
                  hacchuEdit.setStatus("2");
                  session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
                  request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.return.hacchu",newHacchuCode));
                }
              }
            }
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.hacchu.edit");
          }catch (SIDuplicateKeyException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }
        }else if (SIConfig.SIACTION_MAIL.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          String hacchuCode = hacchuEdit.getHacchuCode();
          hacchuEdit = new UIRegHacchu(hacchuCode,"");
          hacchuEdit.reset(databaseConnection.getConnection());
          try {
            if (sendStoreMail(databaseConnection.getConnection(),hacchuEdit,manLogin.getUserCode())) {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.mail"));
              setStoreMailTime(databaseConnection.getConnection(), hacchuEdit);
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            } else {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.failure.mail"));
            }
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }
        }else if (SIConfig.SIACTION_MAIL2.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          String hacchuCode = hacchuEdit.getHacchuCode();
          hacchuEdit = new UIRegHacchu(hacchuCode,"");
          hacchuEdit.reset(databaseConnection.getConnection());
          String remarks = (String) urlParam.getParam("remarks");
          
          try {
            if (producePDFMail(databaseConnection.getConnection(),hacchuEdit,remarks)) {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.mail"));
              this.setHacchuMailTime(databaseConnection.getConnection(), hacchuEdit, manLogin.getUserCode());
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            } else {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.failure.mail"));
            }
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            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.hacchu.update");
          }
        }
      }else if(this.getServletPath(request).equals("/mallmgr/HacchuUpdateCmdty")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME);
          hacchuEdit.initCmdtyParam(request, urlParam);
          hacchuEdit.validateCmdty(request);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update.cmdty");
        }else if (SIConfig.SIACTION_ADD.equals(actionName)||SIConfig.SIACTION_MODIFY.equals(actionName)||SIConfig.SIACTION_DELETE.equals(actionName)){
          int index = 0;
          if (SIConfig.SIACTION_MODIFY.equals(actionName)) index=1;
          else if (SIConfig.SIACTION_DELETE.equals(actionName)) index=2;
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME);
          hacchuEdit.initItem(urlParam);
          if (hacchuEdit.validateItem(request, index)) {
            hacchuEdit.updateItemColl(databaseConnection.getConnection(),index);
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update.cmdty");
        }else if (SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME);
          if(hacchuEdit.validateItemlist(request)){
            hacchuEdit.setupItemBranch2(databaseConnection.getConnection());
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update.branch");
          }else{
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update.cmdty");
          }
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.hacchu.update.cmdty");
        }
      }else if(this.getServletPath(request).equals("/mallmgr/HacchuUpdateBranch")){
        if (SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME);
          hacchuEdit.initDetail(request, urlParam);
          if(hacchuEdit.validateDetail(request,true)){
            hacchuEdit.setupDetailColl();
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,copyEditData(hacchuEdit));
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update");
          }else{
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_UPDATE_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.hacchu.update.branch");
          }
        }
      }else if (this.getServletPath(request).equals("/mallmgr/newHacchu1")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuEdit.initStockParam(request, urlParam);
          hacchuEdit.validateStock(request);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
        }else if (SIConfig.SIACTION_SPECIAL.equalsIgnoreCase(actionName)){//受発注商品新規登録
          session.removeAttribute(SIConfig.SISESSION_MAN_HACCHU_LIST_NAME);
          hacchuEdit.setCmdtyType("0");
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
        }else if (SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit.initStockCode(databaseConnection.getConnection(), urlParam);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          if(hacchuEdit.validateStockCode(request)){
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
          }else{
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
          }
        }else if (SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)){//受注番号指定
          hacchuEdit.initStockCodeAdd(databaseConnection.getConnection(), urlParam);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          if(hacchuEdit.validateStockCodeAdd(request,databaseConnection.getConnection())){
            try {
              //データレコードの作製
              createHacchuData(databaseConnection.getConnection(),hacchuEdit);
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.new.hacchu.res");
            }catch (SIDuplicateKeyException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.new.hacchu1");
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              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.new.hacchu1");
            }
          }else{
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
          }
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
        }else{
          session.removeAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_HACCHU_LIST);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu1");
        }
      }else if (this.getServletPath(request).equals("/mallmgr/newHacchu2")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initCmdtyParam(request, urlParam);
          hacchuEdit.validateCmdty(request);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
        }else if (SIConfig.SIACTION_ADD.equals(actionName)||SIConfig.SIACTION_MODIFY.equals(actionName)||SIConfig.SIACTION_DELETE.equals(actionName)){
          int index = 0;
          if (SIConfig.SIACTION_MODIFY.equals(actionName)) index=1;
          else if (SIConfig.SIACTION_DELETE.equals(actionName)) index=2;
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initItem(urlParam);
          if (hacchuEdit.validateItem(request, index)) {
            hacchuEdit.updateItemColl(databaseConnection.getConnection(),index);
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
        }else if (SIConfig.SIACTION_ADD2.equals(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initItemAll(urlParam);
          if (hacchuEdit.validateItemAll(request)) {
            for (int i=0;i<hacchuEdit.getIndividualCodeAll().length;i++) {
              if (hacchuEdit.setItemOne(i)) hacchuEdit.updateItemColl(databaseConnection.getConnection(),0);
              else continue;
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
        }else if (SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          if(hacchuEdit.validateItemlist(request)){
            SILogin lLogin = SIHTMLUtil.getLogin(request);
            String stockBranch = (String) urlParam.getParam("stockBranch");
            hacchuEdit.initUser(databaseConnection.getConnection(), lLogin);
            hacchuEdit.setupItemBranch(databaseConnection.getConnection(),stockBranch);
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu3");
          }else{
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
          }
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.clearDetail();
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu2");
        }
      }else if (this.getServletPath(request).equals("/mallmgr/newHacchu3")){
        if (SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDetail(request, urlParam);
          hacchuEdit.initBranchAddress(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu3");
        }else if (SIConfig.SIACTION_NEXT.equalsIgnoreCase(actionName)){
          hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
          hacchuEdit.initDetail(request, urlParam);
          if(hacchuEdit.validateDetail(request,false)){
            hacchuEdit.setupDetailColl();
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu.con");
          }else{
            session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
            forwardKey(request,response,"webshop.jsp.manager.new.hacchu3");
          }
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.new.hacchu3");
        }
      }else if (this.getServletPath(request).equals("/mallmgr/newHacchuCon")){
        hacchuEdit = (UIRegHacchu) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME);
        try {
          //データレコードの作製
          insertTableData(databaseConnection.getConnection(),hacchuEdit);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          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.new.hacchu.res");
        }catch (SIDuplicateKeyException e){
          try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          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.new.hacchu3");
        }catch (SIDBAccessException e){
          try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
          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.new.hacchu3");
        }
      }else if (this.getServletPath(request).equals("/mallmgr/newHacchuRes")){
        forwardKey(request,response,"webshop.jsp.manager.hacchu.list");
      }else if(this.getServletPath(request).equals("/mallmgr/HacchuDetail")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuDetailList.init(request, urlParam);
          hacchuDetailList.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_SHIIRE_LIST);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_DETAIL_LIST_NAME,hacchuDetailList);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.detail.list");
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.hacchu.detail.list");
        }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){
          hacchuEdit.initHacchuDetail(urlParam);
          hacchuEdit.resetHacchuDetail(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_DETAIL_EDIT_NAME,hacchuEdit);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.detail.edit");
        } else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)) {
          hacchuDetailList = (UIHacchuDetailListCond)session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_DETAIL_LIST_NAME);
          if (hacchuDetailList==null) hacchuDetailList = new UIHacchuDetailListCond();
          hacchuDetailList.initUpdate(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_DETAIL_LIST_NAME,hacchuDetailList);
          if (hacchuDetailList.validateUpdate(request, databaseConnection.getConnection())) {
            try {
              updateShippmentDate(request, databaseConnection.getConnection(), hacchuDetailList);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
            } catch (SIDBAccessException sqle) {
              try {
                databaseConnection.getConnection().rollback();
              } catch (SQLException ee) {}
              sqle.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.hacchu.detail.list");
        } else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {// CSVファイルへの出力
          // 取得するCSVデータの取得
          hacchuDetailList = (UIHacchuDetailListCond) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_DETAIL_LIST_NAME);
          if (hacchuDetailList == null) hacchuDetailList = new UIHacchuDetailListCond();
          this.produceCSVFile4(response, databaseConnection.getConnection(), hacchuDetailList);
          if (!response.isCommitted()) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
            forwardKey(request, response, "webshop.jsp.manager.hacchu.detail.list");
          }
        }
      }else if(this.getServletPath(request).equals("/mallmgr/HacchuPayment")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          hacchuPaymentList.init(request, urlParam);
          hacchuPaymentList.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_LIST_NAME,hacchuPaymentList);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.list");
        }else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)){
          hacchuPaymentList = (UIHacchuPaymentListCond)session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_LIST_NAME);
          if(hacchuPaymentList==null) hacchuPaymentList = new UIHacchuPaymentListCond();
          hacchuPaymentList.initExecute(request, urlParam);
          if (hacchuPaymentList.validateExecute(request,databaseConnection.getConnection())) {
            try {
              executeClose(databaseConnection.getConnection(), hacchuPaymentList, manLogin.getUserCode());
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.record",hacchuPaymentList.getCloseDateYearCbo(),hacchuPaymentList.getCloseDateMonthCbo()));
              try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("database.execute.error"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_LIST_NAME,hacchuPaymentList);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.list");
        }else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//支払一覧表
          hacchuPaymentList = (UIHacchuPaymentListCond)session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_LIST_NAME);
          if(hacchuPaymentList==null) hacchuPaymentList = new UIHacchuPaymentListCond();
          this.produceCSVFile(response, databaseConnection.getConnection(), hacchuPaymentList);
          if (!response.isCommitted()) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
            forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.list");
          }
        }else if (SIConfig.SIACTION_CSV2.equalsIgnoreCase(actionName)){//支払明細票（一覧）
          hacchuPaymentList = (UIHacchuPaymentListCond)session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_LIST_NAME);
          if(hacchuPaymentList==null) hacchuPaymentList = new UIHacchuPaymentListCond();
          this.produceCSVFile2(response, databaseConnection.getConnection(), hacchuPaymentList);
          if (!response.isCommitted()) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
            forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.list");
          }
        }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){
          hacchuPayment.setStockCode((String)urlParam.getParam("stockCode"));
          hacchuPayment.reset(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME,hacchuPayment);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
        }else if (SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)){
          hacchuPayment = (UIRegHacchuPayment) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME);
          hacchuPayment.setLastClose((String)urlParam.getParam("lastClose"));
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME,hacchuPayment);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
        }else if (SIConfig.SIACTION_CSV3.equalsIgnoreCase(actionName)){//支払明細票（明細）
          hacchuPayment = (UIRegHacchuPayment) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME);
          if(hacchuPayment==null) hacchuPayment = new UIRegHacchuPayment();
          this.produceCSVFile3(response, databaseConnection.getConnection(), hacchuPayment);
          if (!response.isCommitted()) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
            forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
          }
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.list");
        }else if (SIConfig.SIACTION_UPDATE2.equalsIgnoreCase(actionName)){
          hacchuPayment = (UIRegHacchuPayment) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME);
          if (hacchuPayment==null) hacchuPayment = new UIRegHacchuPayment();
          hacchuPayment.init(request, urlParam);
          if (hacchuPayment.validateExecute(request,databaseConnection.getConnection())) {
            try {
              executeClose(databaseConnection.getConnection(), hacchuPayment, manLogin.getUserCode());
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.record",hacchuPaymentList.getCloseDateYearCbo(),hacchuPaymentList.getCloseDateMonthCbo()));
              try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("database.execute.error"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME,hacchuPayment);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
        }else if (SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)){
          hacchuPayment = (UIRegHacchuPayment) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME);
          if (hacchuPayment==null) hacchuPayment = new UIRegHacchuPayment();
          hacchuPayment.init(request, urlParam);
          if (hacchuPayment.validateInsert(request,databaseConnection.getConnection())) {
            try {
              insertPayment(databaseConnection.getConnection(), hacchuPayment, manLogin.getUserCode());
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.insert"));
              try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("database.execute.error"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME,hacchuPayment);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
        }else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)){
          hacchuPayment = (UIRegHacchuPayment) session.getAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME);
          if (hacchuPayment==null) hacchuPayment = new UIRegHacchuPayment();
          hacchuPayment.initDelete(request, urlParam);
          if (hacchuPayment.validateDelete(request,databaseConnection.getConnection())) {
            try {
              deletePayment(databaseConnection.getConnection(), hacchuPayment.getPaymentCode());
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.delete"));
              try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            }catch (SIDBAccessException e){
              try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
              session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_EDIT_NAME,hacchuEdit);
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("database.execute.error"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_HACCHU_PAYMENT_EDIT_NAME,hacchuPayment);
          forwardKey(request,response,"webshop.jsp.manager.hacchu.payment.edit");
        }
      }
    }catch (Exception e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }
  
  private void insertTableData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    
    String hacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
    hacchuEdit.setHacchuCode(hacchuCode);
    
    SIInsertRec insertRec = new SIInsertRec("hacchutbl");
    insertRec.add("hacchuCode", hacchuCode);
    insertRec.add("orderCode", "");
    insertRec.add("orderFlg", SIUtil.sub("1", hacchuEdit.getCmdtyType()));
    insertRec.add("stockCode", hacchuEdit.getStockCode());
    insertRec.add("stockName", hacchuEdit.getStockName());
    insertRec.add("branchCode", hacchuEdit.getBranchCode());
    insertRec.add("chargeCode", hacchuEdit.getChargeCode());
    insertRec.add("directFlg", hacchuEdit.getDirectFlg());
    insertRec.add("custName", hacchuEdit.getCustName());
    insertRec.add("salonName", hacchuEdit.getSalonName());
    insertRec.add("postCode1", hacchuEdit.getPostCode1());
    insertRec.add("postCode2", hacchuEdit.getPostCode2());
    insertRec.add("address1", hacchuEdit.getAddress1());
    insertRec.add("address2", hacchuEdit.getAddress2());
    insertRec.add("address3", hacchuEdit.getAddress3());
    insertRec.add("tel", hacchuEdit.getTel());
    insertRec.add("fax", hacchuEdit.getFax());
    insertRec.add("arrivalPlanDate", hacchuEdit.getArrivalPlanDate());
    insertRec.add("arrivalPlanTime", hacchuEdit.getArrivalPlanTime());
    insertRec.add("initDateTime", lDate.getFullDateTime());
    insertRec.add("updateDateTime", lDate.getFullDateTime());
    insertRec.add("hacchuOutputDateTime", "");
    insertRec.add("hacchuUpdateDateTime", "");
    insertRec.add("hacchuOutputUserCode", "");
    insertRec.add("hacchuMailFlg", "0");
    insertRec.add("storeMailDateTime", "");
    if (SICheckUtil.isGreater(hacchuEdit.getTotalOfPrice(), threshold)) {
      insertRec.add("status", "3");
    } else {
      insertRec.add("status", "1");
    }
    insertRec.add("layercode", "1");
    insertRec.add("enabledflg", "1");
    insertRec.add("TaxRate", SITax.getTaxRate(lConnection));
    insertRec.execute(lConnection);
    
    SIInsertRec insertRec2;
    Iterator detailIte = hacchuEdit.getDetailColl().iterator();
    while (detailIte.hasNext()) {
      SIHacchuDetail lDetail = (SIHacchuDetail) detailIte.next();
      
      insertRec2 = new SIInsertRec("hacchudetailtbl");
      insertRec2.add("hacchuCode", hacchuCode);
      insertRec2.add("hacchuIndividualCode", lDetail.getHacchuIndividualCode());
      insertRec2.add("hacchuBranchCode", lDetail.getHacchuBranchCode());
      insertRec2.add("individualCode", lDetail.getIndividualCode());
      insertRec2.add("cmdtyName", lDetail.getCmdtyName());
      insertRec2.add("colorName", lDetail.getColorName());
      insertRec2.add("branchCode", lDetail.getBranchCode());
      insertRec2.add("branchName", lDetail.getBranchName());
      insertRec2.add("shipFlg", "0");
      insertRec2.add("storeNumber", "");
      insertRec2.add("amount", lDetail.getAmount());
      insertRec2.add("amountAdd", lDetail.getAmountAddZero());
      insertRec2.add("purchasePrice", lDetail.getPurchasePrice());
      insertRec2.add("importCost", lDetail.getImportCost());
      insertRec2.add("processingExpence", lDetail.getProcessingExpence());
      insertRec2.add("deliveryFee", lDetail.getDeliveryFee());
      insertRec2.add("comment", lDetail.getComment());
      insertRec2.add("shippmentDate", "");
      insertRec2.add("layercode", "1");
      insertRec2.execute(lConnection);
    }
  }
  
  private void updateShippmentDate(HttpServletRequest lRequest,Connection lConnection,UIHacchuDetailListCond hacchuDetailList) throws SIDBAccessException,SIDuplicateKeyException{
    for (int i=0;i<hacchuDetailList.getCheck().length;i++) {
      String codeParam = hacchuDetailList.getCheck()[i];
      String[] list = codeParam.split("~");
      if (list.length!=3) continue;
      String hacchuCode = list[0];
      String hacchuIndividualCode = list[1];
      String hacchuBranchCode = list[2];
      
      StringBuffer lStr = new StringBuffer();
      lStr.append("SELECT * FROM hacchudetailtbl ");
      lStr.append("WHERE hacchuCode=").append(hacchuCode);
      lStr.append(" AND hacchuIndividualCode=").append(hacchuIndividualCode);
      lStr.append(" AND hacchuBranchCode=").append(hacchuBranchCode);
      lStr.append(" FOR UPDATE");
      
      SIDBUtil.hasData(lConnection, lStr.toString());//レコードロック
      
      SIHacchuDetail lDetail = new SIHacchuDetail(hacchuCode,hacchuIndividualCode,hacchuBranchCode);
      lDetail.reset(lConnection);
      
      UIRegHacchu lHeader = new UIRegHacchu();
      lHeader.setHacchuCode(hacchuCode);
      lHeader.reset(lConnection);
      try {
        if(SIUtil.isNotNull(lDetail.getShippmentDate())) {
          String datetime = new SIDateTime(lDetail.getShippmentDate(),SIConfig.SIDATE_FORMAT0).getFullDate();
          if (hacchuDetailList.getShippmentDate().equals(datetime)) return;//同日不可
        }
      }catch (Exception e){
        e.printStackTrace();
        return;
      }
      
      StringBuffer sql1 = new StringBuffer();
      sql1.append("SELECT hacchucode FROM hacchutbl ");
      sql1.append("WHERE status=2");
      sql1.append("  AND ordercode=").append(SIDBUtil.SQL2Str(lHeader.getOrderCode()));
      sql1.append("  AND stockcode=").append(SIDBUtil.SQL2Str(lHeader.getStockCode()));
      if (SIUtil.isNotNull(lHeader.getOrderCode()) && SIDBUtil.hasData(lConnection,sql1.toString())) lHeader.setOrderCode("");
      
      SIModifyRec lRec1 = new SIModifyRec("hacchudetailtbl");
      lRec1.addCondition("hacchuCode",hacchuCode);
      lRec1.addCondition("hacchuIndividualCode",hacchuIndividualCode);
      lRec1.addCondition("hacchuBranchCode",hacchuBranchCode);
      lRec1.addCondition("layerCode",lHeader.getLayerCode());
      lRec1.add("shippmentDate", hacchuDetailList.getShippmentDate());
      lRec1.execute(lConnection);
      
      if (SIUtil.isNotNull(lDetail.getShippmentDate())) {//仕入日消去時
        UIStockHistory lStoreData = new UIStockHistory();
        lStoreData.setCmdtyCode(SIDBUtil.getFirstData(lConnection, "SELECT cmdtycode FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lDetail.getIndividualCode())));
        lStoreData.setIndividualCode(lDetail.getIndividualCode());
        lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        lStoreData.setNewCharge(lHeader.getChargeCode());
        lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        lStoreData.setDeleteNo(lDetail.getStoreNumber());
        lStoreData.setInitDateTime(lDetail.getShippmentDate());
        
        if ("1".equals(lHeader.getCmdtyType())) {
          lStoreData.setNewBranch(lDetail.getBranchCode());
          lStoreData.setPurchasePrice(SIUtil.multi(lHeader.getUnitPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
          lStoreData.setProcessingExpence(SIUtil.multi(lHeader.getUnitProcessing(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
        } else {
          lStoreData.setNewBranch(lHeader.getBranchCode());
          lStoreData.setPurchasePrice(lHeader.getTotalPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()));
          lStoreData.setProcessingExpence(lDetail.getProcessingExpence());
        }
        String latestRate = SITax.getTaxRate(lConnection);
        if (!lHeader.getTaxRate().equals(latestRate)){
          try {
            String pp = SIUtil.div_LD(lStoreData.getPurchasePrice(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(latestRate, "100"), "1")) * 10000) / 10000)));
            String pe = SIUtil.div_LD(lStoreData.getProcessingExpence(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(latestRate, "100"), "1")) * 10000) / 10000)));
          }catch(SIFatalException e){
            e.printStackTrace();
          }
        }
        SIRegStockControlSrv.deleteStoreHacchu(lConnection,lStoreData,"1".equals(lHeader.getCmdtyType()));
        
        if (!"1".equals(lHeader.getCmdtyType())&&SIUtil.isNull(lHeader.getOrderCode())) {
          /* 受注がないので出庫データもないので取り消さない
          StringBuffer sql2 = new StringBuffer();
          sql2.append("SELECT storenumber FROM hacchudetailtbl ");
          sql2.append("WHERE shipflg=1 ");
          sql2.append("AND hacchucode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuCode()," "));
          sql2.append("AND hacchuindividualcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuIndividualCode()," "));
          sql2.append("AND hacchubranchcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuBranchCode()," "));
          sql2.append("AND layercode=").append(SIDBUtil.SQL2Str(lHeader.getLayerCode()));
          
          lStoreData.setDeleteNo(SIDBUtil.getFirstData(lConnection, sql2.toString()));
          
          SIRegStockControlSrv.deleteShipHacchu(lConnection,lStoreData);
          */
        }
        
      }
      
      if (SIUtil.isNotNull(hacchuDetailList.getShippmentDate())) {//仕入日設定時
        UIStockHistory lStoreData = new UIStockHistory();
        lStoreData.setCmdtyCode(SIDBUtil.getFirstData(lConnection, "SELECT cmdtycode FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lDetail.getIndividualCode())));
        lStoreData.setIndividualCode(lDetail.getIndividualCode());
        lStoreData.setNewCharge(lHeader.getChargeCode());
        lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        if (SIUtil.isNotNull(lHeader.getOrderCode())) {
          lStoreData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode()+"(受注"+lHeader.getOrderCode()+")");
        } else {
          lStoreData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode());
        }
        if ("1".equals(lHeader.getCmdtyType())) {
          lStoreData.setNewBranch(lDetail.getBranchCode());
          lStoreData.setPurchasePrice(SIUtil.multi(lHeader.getUnitPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
          lStoreData.setProcessingExpence(SIUtil.multi(lHeader.getUnitProcessing(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
        } else {
          lStoreData.setNewBranch(lHeader.getBranchCode());
          lStoreData.setPurchasePrice(lHeader.getTotalPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()));
          lStoreData.setProcessingExpence(lDetail.getProcessingExpence());
        }
        String storeTaxRate = SIDBUtil.getFirstData(lConnection, "SELECT tax("+SIDBUtil.SQL2Str(hacchuDetailList.getShippmentDate(),")"));
        if (!lHeader.getTaxRate().equals(storeTaxRate)){
          try {
            String pp = SIUtil.div_LD(lStoreData.getPurchasePrice(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(storeTaxRate, "100"), "1")) * 10000) / 10000)));
            String pe = SIUtil.div_LD(lStoreData.getProcessingExpence(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(storeTaxRate, "100"), "1")) * 10000) / 10000)));
          }catch(SIFatalException e){
            e.printStackTrace();
          }
        }
        String storeNumber = SIRegStockControlSrv.createNewStoreHacchu(lConnection, lStoreData, lHeader.getOrderCode() ,hacchuDetailList.getShippmentDate() ,"1".equals(lHeader.getCmdtyType()));
        
        SIModifyRec lRec3 = new SIModifyRec("hacchudetailtbl");
        lRec3.addCondition("hacchuCode",hacchuCode);
        lRec3.addCondition("hacchuIndividualCode",hacchuIndividualCode);
        lRec3.addCondition("hacchuBranchCode",hacchuBranchCode);
        lRec3.addCondition("layerCode",lHeader.getLayerCode());
        lRec3.add("storeNumber", storeNumber);
        lRec3.execute(lConnection);
        
        if (!"1".equals(lHeader.getCmdtyType()) && SIUtil.isNull(lHeader.getOrderCode())) {
          /* 受注がない場合は出庫データを作らない
          UIStockHistory lShipData = new UIStockHistory();
          lShipData.setIndividualCode(lDetail.getIndividualCode());
          lShipData.setNewCharge(lHeader.getChargeCode());
          lShipData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
          if (SIUtil.isNotNull(lHeader.getOrderCode())) {
            lShipData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode()+"(受注"+lHeader.getOrderCode()+")");
          } else {
            lShipData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode());
          }
          lShipData.setNewBranch(lHeader.getBranchCode());
          lShipData.setPurchasePrice(lHeader.getTotalPurchase(lDetail.getIndividualCode()));
          lShipData.setProcessingExpence(lDetail.getProcessingExpence());
          String shipNumber = SIRegStockControlSrv.createNewShipHacchu(lConnection, lStoreData, lHeader.getOrderCode(), hacchuDetailList.getShippmentDate());
          
          insertShipData(lConnection,lDetail,shipNumber);
          */
        } else if (!"1".equals(lHeader.getCmdtyType())) {
          try {
            StringBuffer lSql = new StringBuffer();
            lSql.append("SELECT b.ordercode,b.deliverycode,b.detailcode ");
            lSql.append("FROM orderlatestvw a,orderdetaillatestvw b ");
            lSql.append("WHERE a.ordercode=b.ordercode ");
            lSql.append("AND a.status='1' ");
            lSql.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lHeader.getOrderCode()," "));
            lSql.append("AND b.individualcode=").append(SIDBUtil.SQL2Str(lDetail.getIndividualCode()));
            lSql.append("AND b.amount=").append(SIDBUtil.SQL2Str(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
            lSql.append("AND b.shippmentdate IS NULL ");
            Statement lStatement = lConnection.createStatement();
            ResultSet lResultSet = lStatement.executeQuery(lSql.toString());
            if (lResultSet.next()&&SIUtil.isNotNull(lResultSet.getString("ordercode"))) {
              SIRegShippmentSrv.updateShippmentDate(lRequest,lConnection,lResultSet.getString("ordercode"),lResultSet.getString("deliverycode"),lResultSet.getString("detailcode"),hacchuDetailList.getShippmentDate());
            }
            SIDBUtil.close(lStatement, lResultSet);
          } catch (SQLException e) {
            throw new SIDBAccessException();
          }
        }
        SIModifyRec lRec = new SIModifyRec("IndividualTbl");
        lRec.addCondition("IndividualCode", lDetail.getIndividualCode());
        lRec.addCondition("AmountFlg", "1");
        lRec.add("ArrivalScheduleMsg", "入荷日未定");
        lRec.execute(lConnection);
      }
    }
  }
  
  private void insertShipData(Connection lConnection,SIHacchuDetail lDetail,String shipNumber) throws SIDBAccessException {
    StringBuffer lSql0 = new StringBuffer();
    lSql0.append("SELECT * FROM hacchudetailtbl b,hacchutbl a ");
    lSql0.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 AND b.shipflg=1 ");
    lSql0.append("AND b.hacchucode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuCode()," "));
    lSql0.append("AND b.hacchuindividualcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuIndividualCode()," "));
    lSql0.append("AND b.hacchubranchcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuBranchCode()," "));
    
    if (SIDBUtil.hasData(lConnection, lSql0.toString())) {
      StringBuffer lSql1 = new StringBuffer();
      lSql1.append("DELETE FROM hacchudetailtbl ");
      lSql1.append("WHERE shipflg=1 ");
      lSql1.append("AND EXISTS (SELECT * FROM hacchutbl a WHERE a.hacchucode=hacchudetailtbl.hacchucode AND a.layercode=hacchudetailtbl.layercode AND a.enabledflg=1) ");
      lSql1.append("AND hacchucode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuCode()," "));
      lSql1.append("AND hacchuindividualcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuIndividualCode()," "));
      lSql1.append("AND hacchubranchcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuBranchCode()," "));
      SIDBUtil.execSQL(lConnection, lSql1.toString());
    }
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("INSERT INTO hacchudetailtbl ");
    lSql2.append("SELECT b.hacchucode,b.hacchuindividualcode,b.hacchubranchcode, ");
    lSql2.append("b.individualcode,b.cmdtyname,b.colorname, ");
    lSql2.append("b.branchcode,b.branchname,1,").append(shipNumber).append(",b.amount,b.amountadd, ");
    lSql2.append("b.purchaseprice,b.processingexpence,b.deliveryfee,b.importcost,");
    lSql2.append("b.shippmentdate,b.comment,b.layercode ");
    lSql2.append("FROM hacchudetailtbl b,hacchutbl a ");
    lSql2.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 ");
    lSql2.append("AND b.hacchucode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuCode()," "));
    lSql2.append("AND b.hacchuindividualcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuIndividualCode()," "));
    lSql2.append("AND b.hacchubranchcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuBranchCode()," "));
    
    SIDBUtil.execSQL(lConnection, lSql2.toString());
    
  }
  
  private void updateTableData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    boolean isOrder = SIUtil.isNotNull(hacchuEdit.getOrderCode());
    
    if (SIDBUtil.hasData(lConnection, "SELECT * FROM hacchudetailtbl WHERE shippmentdate IS NOT NULL AND hacchucode="+hacchuEdit.getHacchuCode())) {
      throw new SIDBAccessException();
    }
    
    String status = "1";
    if (SICheckUtil.isGreater(hacchuEdit.getTotalOfPrice(), threshold) && !isOrder) {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT sum(b.purchaseprice*b.amount + CASE b.hacchubranchcode WHEN 1 THEN b.deliveryFee+b.importCost+b.processingExpence ELSE 0 END) ");
      lSql.append("FROM hacchutbl a,hacchudetailtbl b ");
      lSql.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 AND b.shipflg=0 ");
      lSql.append("AND a.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
      String beforePrice = SIDBUtil.getFirstData(lConnection, lSql.toString());
      if (SICheckUtil.isGreater(hacchuEdit.getTotalOfPrice(), beforePrice)) {
        status = "3";
      }else{
        status = hacchuEdit.getStatus();
      }
    } else {
      status = "1";
    }
    
    String layerCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(layercode),0) + 1 FROM hacchutbl WHERE hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    String taxRate = SIDBUtil.getFirstData(lConnection, "SELECT taxRate FROM hacchutbl WHERE enabledflg=1 AND hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    SIModifyRec modifyRec = new SIModifyRec("hacchutbl");
    modifyRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec.add("enabledflg", "0");
    modifyRec.execute(lConnection);
    
    SIInsertRec insertRec = new SIInsertRec("hacchutbl");
    insertRec.add("hacchuCode", hacchuEdit.getHacchuCode());
    insertRec.add("orderCode", hacchuEdit.getOrderCode());
    insertRec.add("orderFlg", SIUtil.sub("1", hacchuEdit.getCmdtyType()));
    insertRec.add("stockCode", hacchuEdit.getStockCode());
    insertRec.add("stockName", hacchuEdit.getStockName());
    insertRec.add("branchCode", hacchuEdit.getBranchCode());
    insertRec.add("chargeCode", hacchuEdit.getChargeCode());
    insertRec.add("directFlg", hacchuEdit.getDirectFlg());
    insertRec.add("custName", hacchuEdit.getCustName());
    insertRec.add("salonName", hacchuEdit.getSalonName());
    insertRec.add("postCode1", hacchuEdit.getPostCode1());
    insertRec.add("postCode2", hacchuEdit.getPostCode2());
    insertRec.add("address1", hacchuEdit.getAddress1());
    insertRec.add("address2", hacchuEdit.getAddress2());
    insertRec.add("address3", hacchuEdit.getAddress3());
    insertRec.add("tel", hacchuEdit.getTel());
    insertRec.add("fax", hacchuEdit.getFax());
    insertRec.add("arrivalPlanDate", hacchuEdit.getArrivalPlanDate());
    insertRec.add("arrivalPlanTime", hacchuEdit.getArrivalPlanTime());
    insertRec.add("initDateTime", hacchuEdit.getInitDateTime());
    insertRec.add("updateDateTime", lDate.getFullDateTime());
    insertRec.add("hacchuOutputDateTime", "");
    insertRec.add("hacchuUpdateDateTime", hacchuEdit.getHacchuUpdateDateTime());
    insertRec.add("hacchuOutputUserCode", "");
    insertRec.add("hacchuMailFlg", hacchuEdit.getHacchuMailFlg());
    insertRec.add("storeMailDateTime", "");
    insertRec.add("status", status);
    insertRec.add("layercode", layerCode);
    insertRec.add("enabledflg", "1");
    insertRec.add("taxRate", taxRate);
    insertRec.execute(lConnection);
    
    SIInsertRec insertRec2;
    Iterator detailIte = hacchuEdit.getDetailColl().iterator();
    if (hacchuEdit.getDetailColl()==null || hacchuEdit.getDetailColl().size()==0) {
      throw new SIDBAccessException();
    }
    
    while (detailIte.hasNext()) {
      SIHacchuDetail lDetail = (SIHacchuDetail) detailIte.next();
      
      insertRec2 = new SIInsertRec("hacchudetailtbl");
      insertRec2.add("hacchuCode", hacchuEdit.getHacchuCode());
      insertRec2.add("hacchuIndividualCode", lDetail.getHacchuIndividualCode());
      insertRec2.add("hacchuBranchCode", lDetail.getHacchuBranchCode());
      insertRec2.add("individualCode", lDetail.getIndividualCode());
      insertRec2.add("cmdtyName", lDetail.getCmdtyName());
      insertRec2.add("colorName", lDetail.getColorName());
      insertRec2.add("branchCode", lDetail.getBranchCode());
      insertRec2.add("branchName", lDetail.getBranchName());
      insertRec2.add("shipFlg", "0");
      insertRec2.add("storeNumber", "");
      insertRec2.add("amount", lDetail.getAmount());
      insertRec2.add("amountAdd", lDetail.getAmountAddZero());
      insertRec2.add("purchasePrice", lDetail.getPurchasePrice());
      insertRec2.add("importCost", lDetail.getImportCost());
      insertRec2.add("processingExpence", lDetail.getProcessingExpence());
      insertRec2.add("deliveryFee", lDetail.getDeliveryFee());
      insertRec2.add("comment", lDetail.getComment());
      insertRec2.add("shippmentDate", "");
      insertRec2.add("layercode", layerCode);
      insertRec2.execute(lConnection);
      
      if (SIUtil.isNotNull(hacchuEdit.getOrderCode())&&hacchuEdit.getCmdtyType().equals("0")) {
        String unitProcess = "0";
        try{
          unitProcess = SIUtil.div(lDetail.getProcessingExpence(), SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        }catch(SIFatalException e){
          throw new SIDBAccessException();
        }
        SIModifyRec modRec = new SIModifyRec("orderdetailtbl");
        modRec.addCondition("orderCode",hacchuEdit.getOrderCode());
        modRec.addCondition("individualCode", lDetail.getIndividualCode());
        modRec.add("purchasePrice", lDetail.getPurchasePrice());
        modRec.add("processingExpence", unitProcess);
        modRec.execute(lConnection);
      }
    }
  }
  
  private void updateDeliveryFee(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    String layerCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(layercode),0) FROM hacchutbl WHERE hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    Iterator itemIte = hacchuEdit.getItemColl().iterator();
    while (itemIte.hasNext()) {
      SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
      SIModifyRec lModRec = new SIModifyRec("hacchudetailtbl");
      lModRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
      lModRec.addCondition("individualCode", lDetail.getIndividualCode());
      lModRec.addCondition("layercode", layerCode);
      lModRec.add("deliveryFee", lDetail.getDeliveryFee());
      lModRec.execute(lConnection);
    }
  }
  
  private void approvalData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec = new SIModifyRec("hacchutbl");
    modifyRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec.addCondition("status", "3");
    modifyRec.addCondition("enabledflg", "1");
    modifyRec.add("updateDateTime", lDate.getFullDateTime());
    modifyRec.add("status", "1");
    modifyRec.execute(lConnection);
  }
  
  private void cancelData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec = new SIModifyRec("hacchutbl");
    modifyRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec.addCondition("enabledflg", "1");
    modifyRec.add("updateDateTime", lDate.getFullDateTime());
    modifyRec.add("status", "0");
    modifyRec.execute(lConnection);
  }
  
  private void returnData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec = new SIModifyRec("hacchutbl");
    modifyRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec.addCondition("status", "1");
    modifyRec.addCondition("enabledflg", "1");
    modifyRec.add("updateDateTime", lDate.getFullDateTime());
    modifyRec.add("status", "2");
    modifyRec.execute(lConnection);
    
    UIRegHacchu lHeader = new UIRegHacchu(hacchuEdit.getHacchuCode(),"");
    lHeader.reset(lConnection);
    String taxRate = SITax.getTaxRate(lConnection);
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT b.hacchucode,b.hacchuindividualcode,b.hacchubranchcode ");
    lSql.append("FROM hacchutbl a,hacchudetailtbl b ");
    lSql.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 AND b.shipflg=0 AND b.shippmentdate IS NOT NULL ");
    lSql.append("AND a.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      while (lResultSet.next()) {
        SIHacchuDetail lDetail = new SIHacchuDetail(lResultSet.getString(1),lResultSet.getString(2),lResultSet.getString(3));
        lDetail.reset(lConnection);
        
        UIStockHistory lStoreData = new UIStockHistory();
        lStoreData.setCmdtyCode(SIDBUtil.getFirstData(lConnection, "SELECT cmdtycode FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lDetail.getIndividualCode())));
        lStoreData.setIndividualCode(lDetail.getIndividualCode());
        lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        lStoreData.setNewCharge(lHeader.getChargeCode());
        lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
        lStoreData.setDeleteNo(lDetail.getStoreNumber());
        lStoreData.setInitDateTime(lDate.getFullDateTime());
        
        if ("1".equals(lHeader.getCmdtyType())) {
          lStoreData.setNewBranch(lDetail.getBranchCode());
          lStoreData.setPurchasePrice(SIUtil.multi(lHeader.getUnitPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
          lStoreData.setProcessingExpence(SIUtil.multi(lHeader.getUnitProcessing(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
        } else {
          lStoreData.setNewBranch(lHeader.getBranchCode());
          lStoreData.setPurchasePrice(lHeader.getTotalPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()));
          lStoreData.setProcessingExpence(lDetail.getProcessingExpence());
        }
        if (!taxRate.equals(lHeader.getTaxRate())){
          try{
            String pp = SIUtil.div_LD(lStoreData.getPurchasePrice(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(taxRate, "100"), "1")) * 10000) / 10000)));
            String pe = SIUtil.div_LD(lStoreData.getProcessingExpence(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
            lStoreData.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(taxRate, "100"), "1")) * 10000) / 10000)));
          }catch(Exception e){
            e.printStackTrace();
          }
        }
        SIRegStockControlSrv.deleteStoreHacchu(lConnection,lStoreData,"1".equals(lHeader.getCmdtyType()));
        
        if (!"1".equals(lHeader.getCmdtyType())&&SIUtil.isNull(lHeader.getOrderCode())) {
          StringBuffer sql = new StringBuffer();
          sql.append("SELECT storenumber FROM hacchudetailtbl ");
          sql.append("WHERE shipflg=1 ");
          sql.append("AND hacchucode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuCode()," "));
          sql.append("AND hacchuindividualcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuIndividualCode()," "));
          sql.append("AND hacchubranchcode=").append(SIDBUtil.SQL2Str(lDetail.getHacchuBranchCode()," "));
          sql.append("AND layercode=").append(SIDBUtil.SQL2Str(lHeader.getLayerCode()));
          
          lStoreData.setDeleteNo(SIDBUtil.getFirstData(lConnection, sql.toString()));
          SIRegStockControlSrv.deleteShipHacchu(lConnection,lStoreData);
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("UPDATE hacchudetailtbl SET shippmentdate=current_date FROM hacchutbl ");
    lSql2.append("WHERE hacchudetailtbl.hacchucode=hacchutbl.hacchucode AND hacchudetailtbl.layercode=hacchutbl.layercode AND hacchutbl.enabledflg=1 ");
    lSql2.append("AND hacchudetailtbl.shippmentdate IS NULL AND hacchutbl.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    SIDBUtil.execSQL(lConnection, lSql2.toString());
    
    String hacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
    
    StringBuffer lSql3 = new StringBuffer();
    lSql3.append("INSERT INTO hacchutbl ");
    lSql3.append("SELECT ").append(SIDBUtil.SQL2Str(hacchuCode)).append(",ordercode,orderflg, ");
    lSql3.append("stockcode,stockname,branchcode,chargecode,directflg,custname,postcode1,postcode2, ");
    lSql3.append("address1,address2,address3,tel,fax,").append(SIDBUtil.SQL2Str(lDate.getFullDateTime(),",NULL, "));
    lSql3.append(SIDBUtil.SQL2Str(lDate.getFullDateTime(),",")).append(SIDBUtil.SQL2Str(lDate.getFullDateTime(),","));
    lSql3.append("NULL,NULL,NULL,0,NULL,2,1,1,salonname,taxrate ");
    lSql3.append("FROM hacchutbl ");
    lSql3.append("WHERE enabledflg=1 AND hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    SIDBUtil.execSQL(lConnection, lSql3.toString());
    
    StringBuffer lSql4 = new StringBuffer();
    lSql4.append("INSERT INTO hacchudetailtbl ");
    lSql4.append("SELECT ").append(SIDBUtil.SQL2Str(hacchuCode)).append(",b.hacchuindividualcode,b.hacchubranchcode, ");
    lSql4.append("b.individualcode,b.cmdtyname,b.colorname,b.branchcode,b.branchname,b.shipflg,NULL, ");
    lSql4.append("-1*b.amount,-1*b.amountadd,b.purchaseprice,-1*processingexpence,-1*deliveryfee,-1*importcost,current_date,'',1 ");
    lSql4.append("FROM hacchutbl a,hacchudetailtbl b ");
    lSql4.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 ");
    lSql4.append("AND a.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    SIDBUtil.execSQL(lConnection, lSql4.toString());
  }
  
  private UIRegHacchu copyEditData(UIRegHacchu hacchuEdit) {
    UIRegHacchu copy = new UIRegHacchu();
    copy.setHacchuCode(hacchuEdit.getHacchuCode());
    copy.setOrderCode(hacchuEdit.getOrderCode());
    copy.setCmdtyType(hacchuEdit.getCmdtyType());
    copy.setStockCode(hacchuEdit.getStockCode());
    copy.setStockName(hacchuEdit.getStockName());
    copy.setBranchCode(hacchuEdit.getBranchCode());
    copy.setChargeCode(hacchuEdit.getChargeCode());
    copy.setBranchCodeDB(hacchuEdit.getBranchCodeDB());
    copy.setChargeCodeDB(hacchuEdit.getChargeCodeDB());
    copy.setArrivalPlanDate(hacchuEdit.getArrivalPlanDate());
    copy.setArrivalPlanTime(hacchuEdit.getArrivalPlanTime());
    copy.setDirectFlg(hacchuEdit.getDirectFlg());
    copy.setCustName(hacchuEdit.getCustName());
    copy.setSalonName(hacchuEdit.getSalonName());
    copy.setPostCode1(hacchuEdit.getPostCode1());
    copy.setPostCode2(hacchuEdit.getPostCode2());
    copy.setAddress1(hacchuEdit.getAddress1());
    copy.setAddress2(hacchuEdit.getAddress2());
    copy.setAddress3(hacchuEdit.getAddress3());
    copy.setTel(hacchuEdit.getTel());
    copy.setFax(hacchuEdit.getFax());
    copy.setHacchuMailFlg(hacchuEdit.getHacchuMailFlg());
    copy.setHacchuUpdateDateTime(hacchuEdit.getHacchuUpdateDateTime());
    copy.setStoreMailDateTime(hacchuEdit.getStoreMailDateTime());
    copy.setInitDateTime(hacchuEdit.getInitDateTime());
    copy.setUpdateDateTime(hacchuEdit.getUpdateDateTime());
    copy.setStatus(hacchuEdit.getStatus());
    
    Iterator itemIte = hacchuEdit.getItemColl().iterator();
    while (itemIte.hasNext()) {
      SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
      SIHacchuDetail lDetailCopy = new SIHacchuDetail();
      lDetailCopy.setHacchuCode(lDetail.getHacchuCode());
      lDetailCopy.setIndividualCode(lDetail.getIndividualCode());
      lDetailCopy.setCmdtyName(lDetail.getCmdtyName());
      lDetailCopy.setColorName(lDetail.getColorName());
      lDetailCopy.setBgprice(lDetail.getBgprice());
      lDetailCopy.setPurchasePrice(lDetail.getPurchasePrice());
      lDetailCopy.setImportCost(lDetail.getImportCost());
      lDetailCopy.setProcessingExpence(lDetail.getProcessingExpence());
      lDetailCopy.setDeliveryFee(lDetail.getDeliveryFee());
      lDetailCopy.setComment(lDetail.getComment());
      lDetailCopy.setAmount(lDetail.getAmount());
      lDetailCopy.setAmountAdd(lDetail.getAmountAdd());
      lDetailCopy.setBranchCode(lDetail.getBranchCode());
      lDetailCopy.setBranchName(lDetail.getBranchName());
      lDetailCopy.setBranchCodeList(lDetail.getBranchCodeList());
      lDetailCopy.setBranchNameList(lDetail.getBranchNameList());
      lDetailCopy.setBranchAmountList(lDetail.getBranchAmountList());
      lDetailCopy.setBranchAmountAddList(lDetail.getBranchAmountAddList());
      copy.getItemColl().add(lDetailCopy);
    }
    
    return copy;
  }
  
  public void producePDFFile(HttpServletResponse response, Connection conn, UIRegHacchu hacchuEdit, String remarks) throws IOException, DocumentException, SIDBAccessException {
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    File lOrderTempFile = new File("");
    boolean amountFlg = "1".equals(hacchuEdit.getCmdtyType());
    
    NumberFormat format = NumberFormat.getNumberInstance();
    
    //店舗在庫用テンプレート
    String template_a1 = (String) this.getServletContext().getRealPath("/docs/hacchu_a1.pdf");
    String template_a2 = (String) this.getServletContext().getRealPath("/docs/hacchu_a2.pdf");
    String template_a3 = (String) this.getServletContext().getRealPath("/docs/hacchu_a3.pdf");
    String template_a4 = (String) this.getServletContext().getRealPath("/docs/hacchu_a4.pdf");
    
    //受発注商品用テンプレート
    String template_b1 = (String) this.getServletContext().getRealPath("/docs/hacchu_b1.pdf");
    String template_b2 = (String) this.getServletContext().getRealPath("/docs/hacchu_b2.pdf");
    String template_b3 = (String) this.getServletContext().getRealPath("/docs/hacchu_b3.pdf");
    String template_b4 = (String) this.getServletContext().getRealPath("/docs/hacchu_b4.pdf");
    
    String template = "";
    
    if (amountFlg) {//店舗在庫商品
      if (hacchuEdit.getItemColl().size()<=6) template = template_a1;
      else template = template_a2;
    } else {//受注発注商品
      if (hacchuEdit.getItemColl().size()<=15) template = template_b1;
      else template = template_b2;
    }
    
    try {
      lOrderTempFile = File.createTempFile("order_" , ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    String lFileName = lOrderTempFile.getAbsolutePath();
    HacchuPdf hacchuPdf = new HacchuPdf();
    hacchuPdf.initialize(template, lOrderTempFile.getPath());
    hacchuPdf.fontSelect("Goth");
    
    hacchuPdf.writeStockName(hacchuEdit.getStockName());
    hacchuPdf.writeStockFax(SIDBUtil.getFirstData(conn, "SELECT fax FROM stockmtbl WHERE stockcode="+SIDBUtil.SQL2Str(hacchuEdit.getStockCode())));
    
    String arrival = hacchuEdit.getArrivalPlanDate();
    if (SIUtil.isNotNull(arrival)) {
      try{
        arrival = new SIDateTime(hacchuEdit.getArrivalPlanDate(),SIConfig.SIDATE_FORMAT0).getFullDateNen();
      }catch (Exception e) {}
      if ("1".equals(hacchuEdit.getArrivalPlanTime())) arrival = arrival + "　AM着";
      else if ("2".equals(hacchuEdit.getArrivalPlanTime())) arrival = arrival + "　PM着";
      hacchuPdf.writeArrivalPlan(arrival,amountFlg);
    }
    
    hacchuPdf.writeHacchuCode(hacchuEdit.getHacchuCode());
    hacchuPdf.writeHacchuDate(new SIDateTime().getFullDateNen());
    String chargeName = SIDBUtil.getFirstData(conn, "SELECT chargename FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    hacchuPdf.writeChargeName(chargeName);
    
    String totalOfPrice = SIDBUtil.getFirstData(conn, "SELECT sum(b.purchaseprice*b.amount) FROM hacchutbl a,hacchudetailtbl b WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 AND b.shipflg=0 AND a.hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    hacchuPdf.wtireTotalOfPrice("￥"+format.format(Integer.parseInt(totalOfPrice)),amountFlg);
    
    if (!amountFlg) {
      hacchuPdf.writeDirectFlg(SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_HACCHU_DIRECT_FLG_INX, hacchuEdit.getDirectFlg()));
      if ("0".equals(hacchuEdit.getDirectFlg())) {
        hacchuPdf.writeDeliveryAddressee(hacchuEdit.getSalonName(),hacchuEdit.getCustName());
        hacchuPdf.writeDeliveryPostCode("〒"+hacchuEdit.getPostCode1()+"-"+hacchuEdit.getPostCode2());
        hacchuPdf.writeDeliveryAddress(hacchuEdit.getAddress1()+hacchuEdit.getAddress2());
        hacchuPdf.writeDeliveryAddressEx(hacchuEdit.getAddress3());
        hacchuPdf.writeDeliveryTel("TEL："+hacchuEdit.getTel());
        hacchuPdf.writeDeliveryFax("FAX："+hacchuEdit.getTel());
      }
      hacchuPdf.writeOrderCode(hacchuEdit.getOrderCode());
      hacchuPdf.writeCustName(hacchuEdit.getCustName());
    }
    
    if (SIUtil.isNotNull(hacchuEdit.getChargeCode())) {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT '株式会社ビューティガレージ　'||(CASE b.otheraddrflg WHEN 1 THEN a.branchname||'本社' ELSE a.branchname END) AS name ");
      lSql.append(",'〒'||a.postcode1||'-'||a.postcode2 AS postcode ");
      lSql.append(",a.address1||a.address2 AS address ");
      lSql.append(",coalesce(address3,' ') AS addressex ");
      lSql.append(",'TEL：'||CASE b.otheraddrflg WHEN 1 THEN tel2 ELSE tel1 END AS tel ");
      lSql.append(",'FAX：'||CASE b.otheraddrflg WHEN 1 THEN fax2 ELSE fax1 END AS fax ");
      lSql.append("FROM branchtbl a,chargetbl b ");
      lSql.append("WHERE a.branchcode=b.belongingbranchcode ");
      lSql.append("AND b.chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
      Statement lStatement = null;
      ResultSet lResultSet = null;
      try {
        lStatement = conn.createStatement();
        lResultSet = lStatement.executeQuery(lSql.toString());
        
        if (lResultSet.next()) {
          hacchuPdf.writeCompanyName(lResultSet.getString("name"),amountFlg);
          hacchuPdf.writeCompanyPostCode(lResultSet.getString("postcode"),amountFlg);
          hacchuPdf.writeCompanyAddress(lResultSet.getString("address"),amountFlg);
          hacchuPdf.writeCompanyAddressEx(lResultSet.getString("addressex"),amountFlg);
          hacchuPdf.writeCompanyTel(lResultSet.getString("tel"),amountFlg);
          hacchuPdf.writeCompanyFax(lResultSet.getString("fax"),amountFlg);
          if (!amountFlg && "1".equals(hacchuEdit.getDirectFlg())) {
            hacchuPdf.writeDeliveryAddressee(lResultSet.getString("name"),chargeName);
            hacchuPdf.writeDeliveryPostCode(lResultSet.getString("postcode"));
            hacchuPdf.writeDeliveryAddress(lResultSet.getString("address"));
            hacchuPdf.writeDeliveryAddressEx(lResultSet.getString("addressex"));
            hacchuPdf.writeDeliveryTel(lResultSet.getString("tel"));
            hacchuPdf.writeDeliveryFax(lResultSet.getString("fax"));
          }
        }
      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        SIDBUtil.close(lResultSet, lStatement);
      }
    }
    
    int count = 1;
    int max = hacchuEdit.getItemColl().size();
    Iterator itemIte = hacchuEdit.getItemColl().iterator();
    
    if (amountFlg) {//店舗在庫商品
      while (itemIte.hasNext()) {
        SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
        int position = getPdfIndex1(count);
        hacchuPdf.writeIndex1(String.valueOf(count), position);
        hacchuPdf.writeIndividualCode1(lDetail.getIndividualCode(), position);
        String cmdtyName = lDetail.getCmdtyName();
        if (SIUtil.isNotNull(lDetail.getColorName())) cmdtyName = cmdtyName+"("+lDetail.getColorName()+")";
        hacchuPdf.writeCmdtyName1(cmdtyName, position);
        String amount = format.format(Integer.parseInt(lDetail.getAmount()));
        if (SIUtil.isNotNull(lDetail.getAmountAdd())&&!"0".equals(lDetail.getAmountAdd())) amount = amount+"("+lDetail.getAmountAdd()+")";
        hacchuPdf.writeAmountTotal1(amount, position);
        hacchuPdf.writePurchase1("￥"+format.format(Integer.parseInt(lDetail.getPurchasePrice())), position);
        String total = SIUtil.multi(lDetail.getPurchasePrice(), lDetail.getAmount());
        hacchuPdf.writeTotal1("￥"+format.format(Integer.parseInt(total)), position);
        String otherAmount = "0";
        String otherAmountAdd = "0";
        for (int i=0;i<lDetail.getBranchCodeList().length;i++) {
          if (SIUtil.isNotNull(lDetail.getBranchAmountListItem(i)) && !"0".equals(lDetail.getBranchAmountListItem(i))) {
            if (i>11 && i!=17 && i!=18) {//0〜11：東京〜千葉、17〜18：物流センター、楽天以外は合算して預け在庫と表記
              otherAmount = SIUtil.add(otherAmount, lDetail.getBranchAmountListItem(i));
              otherAmountAdd = SIUtil.add(otherAmountAdd, lDetail.getBranchAmountAddListItem(i));
            } else {
              int branchCount = i;
              if (branchCount==17) branchCount=12;
              if (branchCount==18) branchCount=13;
              hacchuPdf.writeAmount1(lDetail.getBranchAmountListItem(i), branchCount, position);
              if (SIUtil.isNotNull(lDetail.getBranchAmountAddListItem(i)) && !"0".equals(lDetail.getBranchAmountAddListItem(i))) {
                hacchuPdf.writeAmountAdd1(lDetail.getBranchAmountAddListItem(i), branchCount, position);
              }
            }
          }
        }
        if (SIUtil.isNotNull(otherAmount) && !"0".equals(otherAmount)) {
          hacchuPdf.writeAmount1(otherAmount, 14, position);
          if (SIUtil.isNotNull(otherAmountAdd) && !"0".equals(otherAmountAdd)) {
            hacchuPdf.writeAmountAdd1(otherAmountAdd, 14, position);
          }
        }
        hacchuPdf.writeComment1(lDetail.getComment(), position);
        if (count==8 && max<=17) hacchuPdf.addPage(template_a4);
        else if (count==8 && max!=count) hacchuPdf.addPage(template_a3);
        else if (count>=19 && ((count-8) % 11)==0 && count+9>=max) hacchuPdf.addPage(template_a4);
        else if (count>=19 && ((count-8) % 11)==0) hacchuPdf.addPage(template_a3);
        count++;
      }
      count--;
      if (count==7||(count-8)%11==10) hacchuPdf.addPage(template_a4);
    } else {
      while (itemIte.hasNext()) {
        SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
        int position = getPdfIndex2(count);
        hacchuPdf.writeIndividualCode2(lDetail.getIndividualCode(), position);
        String cmdtyName = lDetail.getCmdtyName();
        if (SIUtil.isNotNull(lDetail.getColorName())) cmdtyName = cmdtyName+"("+lDetail.getColorName()+")";
        hacchuPdf.writeCmdtyName2(cmdtyName, position);
        String amount = lDetail.getAmount();
        if (SIUtil.isNotNull(lDetail.getAmountAdd())&&!"0".equals(lDetail.getAmountAdd())) amount = amount+"("+lDetail.getAmountAdd()+")";
        hacchuPdf.writeAmountTotal2(format.format(Integer.parseInt(amount)), position);
        hacchuPdf.writePurchase2("￥"+format.format(Integer.parseInt(lDetail.getPurchasePrice())), position);
        String total = SIUtil.multi(lDetail.getPurchasePrice(), lDetail.getAmount());
        hacchuPdf.writeTotal2("￥"+format.format(Integer.parseInt(total)), position);
        hacchuPdf.writeComment2(lDetail.getComment(), position);
        if (count==22 && max<=40) hacchuPdf.addPage(template_b4);
        else if (count==22) hacchuPdf.addPage(template_b3);
        else if (count>=57 && ((count-22) % 35)==0 && count+29>=max) hacchuPdf.addPage(template_b3);
        else if (count>=57 && ((count-22) % 35)==0) hacchuPdf.addPage(template_b4);
        count++;
      }
      count--;
      if ((count>=16&&count<=22)||((count>57)&&(count-22)%35>=29)) hacchuPdf.addPage(template_b4);
    }
    
    String[] remarksList = remarks.split("\r\n");
    int remarksCount = 0;
    for (int i=0;i<remarksList.length;i++) {
      String temp1 = SIStringUtil.getLimitString(remarksList[i], 48);
      String temp2 = SIStringUtil.getLimitOverString(remarksList[i], 48);
      hacchuPdf.writeRemarks(temp1, remarksCount);
      remarksCount++;
      if (remarksCount>4) break;
      
      while (SIUtil.isNotNull(temp2)) {
        temp1 = SIStringUtil.getLimitString(temp2, 48);
        temp2 = SIStringUtil.getLimitOverString(temp2, 48);
        hacchuPdf.writeRemarks(temp1, remarksCount);
        remarksCount++;
        if (remarksCount>4) break;
      }
      if (remarksCount>4) break;
    }
    
    hacchuPdf.writeReFax("※受注確認のため、必ず担当者へ「Re:FAX」を入れてください。");
    
    hacchuPdf.close();
    
    SIPDFUtil lPdfUtil = new SIPDFUtil();
    String lOutputFileName = "order_" + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
    lPdfUtil.execute(response, lFileName, lOutputFileName);
    // 臨時ファイルの削除
    lOrderTempFile.delete();
  }
  
  private boolean producePDFMail(Connection conn, UIRegHacchu hacchuEdit, String remarks) throws IOException, DocumentException, SIDBAccessException, SIFatalException {
    boolean res = true;
    String value0 = "";//支社名
    String value1 = "";//担当者名
    String value2 = "";//メールアドレス
    String value3 = "";//電話番号
    String value4 = "";//FAX番号
    
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    File lOrderTempFile = new File("");
    boolean amountFlg = "1".equals(hacchuEdit.getCmdtyType());
    
    NumberFormat format = NumberFormat.getNumberInstance();
    
    //店舗在庫用テンプレート
    String template_a1 = (String) this.getServletContext().getRealPath("/docs/hacchu_a1.pdf");
    String template_a2 = (String) this.getServletContext().getRealPath("/docs/hacchu_a2.pdf");
    String template_a3 = (String) this.getServletContext().getRealPath("/docs/hacchu_a3.pdf");
    String template_a4 = (String) this.getServletContext().getRealPath("/docs/hacchu_a4.pdf");
    
    //受発注商品用テンプレート
    String template_b1 = (String) this.getServletContext().getRealPath("/docs/hacchu_b1.pdf");
    String template_b2 = (String) this.getServletContext().getRealPath("/docs/hacchu_b2.pdf");
    String template_b3 = (String) this.getServletContext().getRealPath("/docs/hacchu_b3.pdf");
    String template_b4 = (String) this.getServletContext().getRealPath("/docs/hacchu_b4.pdf");
    
    String template = "";
    
    if (amountFlg) {//店舗在庫商品
      if (hacchuEdit.getItemColl().size()<=6) template = template_a1;
      else template = template_a2;
    } else {//受注発注商品
      if (hacchuEdit.getItemColl().size()<=15) template = template_b1;
      else template = template_b2;
    }
    
    try {
      lOrderTempFile = File.createTempFile("order_" , ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    HacchuPdf hacchuPdf = new HacchuPdf();
    hacchuPdf.initialize(template, lOrderTempFile.getPath());
    hacchuPdf.fontSelect("Goth");
    
    hacchuPdf.writeStockName(hacchuEdit.getStockName());
    hacchuPdf.writeStockFax(SIDBUtil.getFirstData(conn, "SELECT fax FROM stockmtbl WHERE stockcode="+SIDBUtil.SQL2Str(hacchuEdit.getStockCode())));
    
    String arrival = hacchuEdit.getArrivalPlanDate();
    if (SIUtil.isNotNull(arrival)) {
      try{
        arrival = new SIDateTime(hacchuEdit.getArrivalPlanDate(),SIConfig.SIDATE_FORMAT0).getFullDateNen();
      }catch (Exception e) {}
      if ("1".equals(hacchuEdit.getArrivalPlanTime())) arrival = arrival + "　AM着";
      else if ("2".equals(hacchuEdit.getArrivalPlanTime())) arrival = arrival + "　PM着";
      hacchuPdf.writeArrivalPlan(arrival,amountFlg);
    }
    
    hacchuPdf.writeHacchuCode(hacchuEdit.getHacchuCode());
    hacchuPdf.writeHacchuDate(new SIDateTime().getFullDateNen());
    String chargeName = SIDBUtil.getFirstData(conn, "SELECT chargename FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    hacchuPdf.writeChargeName(chargeName);
    String totalOfPrice = SIDBUtil.getFirstData(conn, "SELECT sum(b.purchaseprice*b.amount) FROM hacchutbl a,hacchudetailtbl b WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 AND b.shipflg=0 AND a.hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()));
    
    hacchuPdf.wtireTotalOfPrice("￥"+format.format(Integer.parseInt(totalOfPrice)),amountFlg);
    
    if (!amountFlg) {
      hacchuPdf.writeDirectFlg(SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_HACCHU_DIRECT_FLG_INX, hacchuEdit.getDirectFlg()));
      if ("0".equals(hacchuEdit.getDirectFlg())) {
        hacchuPdf.writeDeliveryAddressee(hacchuEdit.getSalonName(),hacchuEdit.getCustName());
        hacchuPdf.writeDeliveryPostCode("〒"+hacchuEdit.getPostCode1()+"-"+hacchuEdit.getPostCode2());
        hacchuPdf.writeDeliveryAddress(hacchuEdit.getAddress1()+hacchuEdit.getAddress2());
        hacchuPdf.writeDeliveryAddressEx(hacchuEdit.getAddress3());
        hacchuPdf.writeDeliveryTel("TEL："+hacchuEdit.getTel());
        hacchuPdf.writeDeliveryFax("FAX："+hacchuEdit.getTel());
      }
      hacchuPdf.writeOrderCode(hacchuEdit.getOrderCode());
      hacchuPdf.writeCustName(hacchuEdit.getCustName());
    }
    
    if (SIUtil.isNotNull(hacchuEdit.getChargeCode())) {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT '株式会社ビューティガレージ　'||(CASE b.otheraddrflg WHEN 1 THEN a.branchname||'本社' ELSE a.branchname END) AS name ");
      lSql.append(",'〒'||a.postcode1||'-'||a.postcode2 AS postcode ");
      lSql.append(",a.address1||a.address2 AS address ");
      lSql.append(",coalesce(address3,' ') AS addressex ");
      lSql.append(",'TEL：'||CASE b.otheraddrflg WHEN 1 THEN tel2 ELSE tel1 END AS tel ");
      lSql.append(",'FAX：'||CASE b.otheraddrflg WHEN 1 THEN fax2 ELSE fax1 END AS fax ");
      lSql.append("FROM branchtbl a,chargetbl b ");
      lSql.append("WHERE a.branchcode=b.belongingbranchcode ");
      lSql.append("AND b.chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
      Statement lStatement = null;
      ResultSet lResultSet = null;
      try {
        lStatement = conn.createStatement();
        lResultSet = lStatement.executeQuery(lSql.toString());
        
        if (lResultSet.next()) {
          hacchuPdf.writeCompanyName(lResultSet.getString("name"),amountFlg);
          hacchuPdf.writeCompanyPostCode(lResultSet.getString("postcode"),amountFlg);
          hacchuPdf.writeCompanyAddress(lResultSet.getString("address"),amountFlg);
          hacchuPdf.writeCompanyAddressEx(lResultSet.getString("addressex"),amountFlg);
          hacchuPdf.writeCompanyTel(lResultSet.getString("tel"),amountFlg);
          hacchuPdf.writeCompanyFax(lResultSet.getString("fax"),amountFlg);
          if (!amountFlg && "1".equals(hacchuEdit.getDirectFlg())) {
            hacchuPdf.writeDeliveryAddressee(lResultSet.getString("name"),chargeName);
            hacchuPdf.writeDeliveryPostCode(lResultSet.getString("postcode"));
            hacchuPdf.writeDeliveryAddress(lResultSet.getString("address"));
            hacchuPdf.writeDeliveryAddressEx(lResultSet.getString("addressex"));
            hacchuPdf.writeDeliveryTel(lResultSet.getString("tel"));
            hacchuPdf.writeDeliveryFax(lResultSet.getString("fax"));
          }
          value0=lResultSet.getString("name");
          value1=chargeName;
          value3=lResultSet.getString("tel");
          value4=lResultSet.getString("fax");
        }
      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        SIDBUtil.close(lResultSet, lStatement);
      }
    }
    
    int count = 1;
    int max = hacchuEdit.getItemColl().size();
    Iterator itemIte = hacchuEdit.getItemColl().iterator();
    
    if (amountFlg) {//店舗在庫商品
      while (itemIte.hasNext()) {
        SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
        int position = getPdfIndex1(count);
        hacchuPdf.writeIndex1(String.valueOf(count), position);
        hacchuPdf.writeIndividualCode1(lDetail.getIndividualCode(), position);
        String cmdtyName = lDetail.getCmdtyName();
        if (SIUtil.isNotNull(lDetail.getColorName())) cmdtyName = cmdtyName+"("+lDetail.getColorName()+")";
        hacchuPdf.writeCmdtyName1(cmdtyName, position);
        String amount = format.format(Integer.parseInt(lDetail.getAmount()));
        if (SIUtil.isNotNull(lDetail.getAmountAdd())&&!"0".equals(lDetail.getAmountAdd())) amount = amount+"("+lDetail.getAmountAdd()+")";
        hacchuPdf.writeAmountTotal1(amount, position);
        hacchuPdf.writePurchase1("￥"+format.format(Integer.parseInt(lDetail.getPurchasePrice())), position);
        String total = SIUtil.multi(lDetail.getPurchasePrice(), lDetail.getAmount());
        hacchuPdf.writeTotal1("￥"+format.format(Integer.parseInt(total)), position);
        String otherAmount = "0";
        String otherAmountAdd = "0";
        for (int i=0;i<lDetail.getBranchCodeList().length;i++) {
          if (SIUtil.isNotNull(lDetail.getBranchAmountListItem(i)) && !"0".equals(lDetail.getBranchAmountListItem(i))) {
            if (i>11 && i!=17 && i!=18) {//0〜11：東京〜千葉、17〜18：物流センター、楽天以外は合算して預け在庫と表記
              otherAmount = SIUtil.add(otherAmount, lDetail.getBranchAmountListItem(i));
              otherAmountAdd = SIUtil.add(otherAmountAdd, lDetail.getBranchAmountAddListItem(i));
            } else {
              int branchCount = i;
              if (branchCount==17) branchCount=12;
              if (branchCount==18) branchCount=13;
              hacchuPdf.writeAmount1(lDetail.getBranchAmountListItem(i), branchCount, position);
              if (SIUtil.isNotNull(lDetail.getBranchAmountAddListItem(i)) && !"0".equals(lDetail.getBranchAmountAddListItem(i))) {
                hacchuPdf.writeAmountAdd1(lDetail.getBranchAmountAddListItem(i), branchCount, position);
              }
            }
          }
        }
        if (SIUtil.isNotNull(otherAmount) && !"0".equals(otherAmount)) {
          hacchuPdf.writeAmount1(otherAmount, 14, position);
          if (SIUtil.isNotNull(otherAmountAdd) && !"0".equals(otherAmountAdd)) {
            hacchuPdf.writeAmountAdd1(otherAmountAdd, 14, position);
          }
        }
        hacchuPdf.writeComment1(lDetail.getComment(), position);
        if (count==8 && max<=17) hacchuPdf.addPage(template_a4);
        else if (count==8 && max!=count) hacchuPdf.addPage(template_a3);
        else if (count>=19 && ((count-8) % 11)==0 && count+9>=max) hacchuPdf.addPage(template_a4);
        else if (count>=19 && ((count-8) % 11)==0) hacchuPdf.addPage(template_a3);
        count++;
      }
      count--;
      if (count==7||(count-8)%11==10) hacchuPdf.addPage(template_a4);
    } else {
      while (itemIte.hasNext()) {
        SIHacchuDetail lDetail = (SIHacchuDetail) itemIte.next();
        int position = getPdfIndex2(count);
        hacchuPdf.writeIndividualCode2(lDetail.getIndividualCode(), position);
        String cmdtyName = lDetail.getCmdtyName();
        if (SIUtil.isNotNull(lDetail.getColorName())) cmdtyName = cmdtyName+"("+lDetail.getColorName()+")";
        hacchuPdf.writeCmdtyName2(cmdtyName, position);
        String amount = lDetail.getAmount();
        if (SIUtil.isNotNull(lDetail.getAmountAdd())&&!"0".equals(lDetail.getAmountAdd())) amount = amount+"("+lDetail.getAmountAdd()+")";
        hacchuPdf.writeAmountTotal2(format.format(Integer.parseInt(amount)), position);
        hacchuPdf.writePurchase2("￥"+format.format(Integer.parseInt(lDetail.getPurchasePrice())), position);
        String total = SIUtil.multi(lDetail.getPurchasePrice(), lDetail.getAmount());
        hacchuPdf.writeTotal2("￥"+format.format(Integer.parseInt(total)), position);
        hacchuPdf.writeComment2(lDetail.getComment(), position);
        if (count==22 && max<=40) hacchuPdf.addPage(template_b4);
        else if (count==22) hacchuPdf.addPage(template_b3);
        else if (count>=57 && ((count-22) % 35)==0 && count+29>=max) hacchuPdf.addPage(template_b3);
        else if (count>=57 && ((count-22) % 35)==0) hacchuPdf.addPage(template_b4);
        count++;
      }
      count--;
      if ((count>=16&&count<=22)||((count>57)&&(count-22)%35>=29)) hacchuPdf.addPage(template_b4);
    }
    
    String[] remarksList = remarks.split("\r\n");
    int remarksCount = 0;
    for (int i=0;i<remarksList.length;i++) {
      String temp1 = SIStringUtil.getLimitString(remarksList[i], 48);
      String temp2 = SIStringUtil.getLimitOverString(remarksList[i], 48);
      hacchuPdf.writeRemarks(temp1, remarksCount);
      remarksCount++;
      if (remarksCount>4) break;
      
      while (SIUtil.isNotNull(temp2)) {
        temp1 = SIStringUtil.getLimitString(temp2, 48);
        temp2 = SIStringUtil.getLimitOverString(temp2, 48);
        hacchuPdf.writeRemarks(temp1, remarksCount);
        remarksCount++;
        if (remarksCount>4) break;
      }
      if (remarksCount>4) break;
    }
    
    hacchuPdf.close();
    
    SIMailTemp mailTemp=new SIMailTemp("0",SIConfig.SIMAIL_TEMPLATE_HACCHU_REGIST);
    try {
      if (!mailTemp.reset(conn)){
        throw new SIFatalException("can not find mail template for mallshopcode(0) and Type("+SIConfig.SIMAIL_TEMPLATE_HACCHU_REGIST+")");
      }
    } catch (SIDBAccessException e) {
      e.printStackTrace();
      throw new SIFatalException(e);
    }
    
    if (SIDBUtil.hasData(conn, "SELECT * FROM hacchutbl WHERE hacchumailFlg=1 AND hacchucode="+SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()))) {
      mailTemp.setTitle("【再送】" + mailTemp.getTitle() + "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    } else {
      mailTemp.setTitle(mailTemp.getTitle() + "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    }
    
    String to = SIDBUtil.getFirstData(conn, "SELECT coalesce(email,'')||','||coalesce(email2,'')||','||coalesce(email3,'') FROM stockmtbl WHERE stockcode="+SIDBUtil.SQL2Str(hacchuEdit.getStockCode()));
    String from = SIDBUtil.getFirstData(conn, "SELECT email FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    if (SIUtil.isNull(from)) from = mailTemp.getFromMail();
    value2=from;
    String subject = mailTemp.getTitle();
    StringBuffer lMailBuf = new StringBuffer("");
    
    if (SIUtil.isNotNull(mailTemp.getContent1())){
      lMailBuf.append("\n").append(mailTemp.getContent1());
    }
    if (SIUtil.isNotNull(mailTemp.getContent2())){
      lMailBuf.append("\n").append(mailTemp.getContent2());
    }
    if (SIUtil.isNotNull(mailTemp.getContent3())){
      lMailBuf.append("\n").append(mailTemp.getContent3());
    }
    
    //署名
    lMailBuf.append("\n-----------------------------------------");
    lMailBuf.append("\n").append(value0);
    lMailBuf.append("\n担当者：").append(value1);
    lMailBuf.append("\nE-MAIL：").append(value2);
    lMailBuf.append("\n").append(value3);
    lMailBuf.append("\n").append(value4);
    lMailBuf.append("\n-----------------------------------------");
    
    //if (SIUtil.isNotNull(mailTemp.getSignature())){
    //  lMailBuf.append("\n").append(mailTemp.getSignature());
    //}
    
    lMailBuf.append("\n");
    
    try{
      //SMTP
      Properties props = System.getProperties();
      props.put("mail.smtp.host",SIMailUtil.getMailHost());
      Session session = Session.getDefaultInstance(props,null);
      MimeMessage msg = new MimeMessage(session);
      log.debug("mail.smtp.host:"+SIMailUtil.getMailHost());
      
      //TO
      InternetAddress toList[] = InternetAddress.parse(to,false);
      msg.addRecipients(MimeMessage.RecipientType.TO,toList);
      
      //CC
      InternetAddress ccList[] = InternetAddress.parse(from,false);
      msg.addRecipients(MimeMessage.RecipientType.CC,ccList);
      
      //BCC
      InternetAddress bccList[] = InternetAddress.parse(mailTemp.getMallShopEmail(),false);
      msg.addRecipients(MimeMessage.RecipientType.BCC,bccList);
      
      //FROM
      InternetAddress fromAddress = new InternetAddress(from);
      msg.setFrom(fromAddress);
      msg.setSentDate(new Date());
      
      //SUBJECT
      msg.setSubject(subject,"ISO-2022-JP");
      
      //BODY
      MimeMultipart mm = new MimeMultipart();
      
      //MESSAGE
      MimeBodyPart mbText = new MimeBodyPart();
      mbText.setText(lMailBuf.toString(),"ISO-2022-JP");
      mm.addBodyPart(mbText);  
      
      //APPEND
      String file = lOrderTempFile.getPath();
      String fileName = "order_" + (new SIDateTime().getDateTimeString()) + ".pdf";
      
      MimeBodyPart mbFile1 = new MimeBodyPart();
      FileDataSource fds1 = new FileDataSource(file);
      DataHandler dh1 = new DataHandler(fds1);
      mbFile1.setDataHandler(dh1);
      mbFile1.setFileName(MimeUtility.encodeWord(fileName));
      mm.addBodyPart(mbFile1);
      
      msg.setContent(mm);
      
      //ENCODE
      msg.addHeader("Content-Transfer-Encoding", "7bit");
      
      //SEND
      Transport.send(msg);
      log.info("send mail ok!!");
      
    }catch(Exception e){
      e.printStackTrace();
      res = false;
    }
    
    // 臨時ファイルの削除
    lOrderTempFile.delete();
    return res;
  }
  
  private String setOutputTime(Connection lConnection, UIRegHacchu hacchuEdit,String userCode) {
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec1 = new SIModifyRec("hacchutbl");
    modifyRec1.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec1.addCondition("status", "1");
    modifyRec1.addCondition("enabledflg", "1");
    modifyRec1.addCondition("hacchuoutputdatetime", "");
    modifyRec1.add("hacchuoutputdatetime", lDate.getFullDateTime());
    modifyRec1.add("hacchuupdatedatetime", lDate.getFullDateTime());
    
    SIModifyRec modifyRec2 = new SIModifyRec("hacchutbl");
    modifyRec2.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec2.addCondition("status", "1");
    modifyRec2.addCondition("enabledflg", "1");
    modifyRec2.add("hacchuupdatedatetime", lDate.getFullDateTime());
    modifyRec2.add("hacchuoutputusercode", userCode);
    
    try {
      modifyRec1.execute(lConnection);
      modifyRec2.execute(lConnection);
    } catch (Exception e) {
      e.printStackTrace();
    }
    
    return lDate.getFullDateTime();
  }
  
  private void setStoreMailTime(Connection lConnection, UIRegHacchu hacchuEdit) {
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec = new SIModifyRec("hacchutbl");
    modifyRec.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec.addCondition("status", "1");
    modifyRec.addCondition("enabledflg", "1");
    modifyRec.add("storemaildatetime", lDate.getFullDateTime());
    
    hacchuEdit.setStoreMailDateTime(lDate.getFullDate());
    
    try {
      modifyRec.execute(lConnection);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  private void setHacchuMailTime(Connection lConnection, UIRegHacchu hacchuEdit,String userCode) {
    SIDateTime lDate = new SIDateTime();
    SIModifyRec modifyRec1 = new SIModifyRec("hacchutbl");
    modifyRec1.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec1.addCondition("status", "1");
    modifyRec1.addCondition("enabledflg", "1");
    modifyRec1.addCondition("hacchuoutputdatetime", "");
    modifyRec1.add("hacchuoutputdatetime", lDate.getFullDateTime());
    modifyRec1.add("hacchuupdatedatetime", lDate.getFullDateTime());
    
    SIModifyRec modifyRec2 = new SIModifyRec("hacchutbl");
    modifyRec2.addCondition("hacchuCode", hacchuEdit.getHacchuCode());
    modifyRec2.addCondition("status", "1");
    modifyRec2.addCondition("enabledflg", "1");
    modifyRec2.add("hacchuupdatedatetime", lDate.getFullDateTime());
    modifyRec2.add("hacchuoutputusercode", userCode);
    modifyRec2.add("hacchumailflg", "1");
    
    hacchuEdit.setHacchuMailFlg("1");
    hacchuEdit.setHacchuUpdateDateTime(lDate.getFullDate());
    
    try {
      modifyRec1.execute(lConnection);
      modifyRec2.execute(lConnection);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  
  private int getPdfIndex1(int index) {
    int returnValue = index;
    if (index<=8) return index+2;
    else if (index<=19) return index-9;
    else {
      returnValue-=9;
      return returnValue % 11;
    }
  }
  
  private int getPdfIndex2(int index) {
    int returnValue = index;
    if (index<=22) return index+12;
    else if (index<=57) return index-23;
    else {
      returnValue-=23;
      return returnValue % 35;
    }
  }
  
  public static void createHacchuData(Connection lConnection,String lOrderCode) throws SIDBAccessException,SIDuplicateKeyException,SQLException {
    StringBuffer lSql1 = new StringBuffer();
    boolean breakFlg = false;
    lSql1.append("SELECT a.individualcode,a.cmdtyname,b.stockcode,c.stockname,d.colorname,a.amount,a.purchaseprice ");
    lSql1.append(",CASE WHEN dt.deliverydate<=current_date THEN NULL ELSE dt.deliverydate END AS deliverydate ");
    lSql1.append("FROM orderdetaillatestvw a,cmdtyunittbl b,stockmtbl c,colortbl d ");
    lSql1.append(",(SELECT d.ordercode,i.stockcode,min(d.deliverydate) AS deliverydate ");
    lSql1.append("FROM orderdetaillatestvw d,individualtbl i ");
    lSql1.append("WHERE d.individualcode=i.individualcode AND i.amountflg=0 ");
    lSql1.append("GROUP BY d.ordercode,i.stockcode) dt ");
    lSql1.append("WHERE a.individualcode=b.individualcode AND b.stockcode=c.stockcode AND b.colorcode=d.colorcode AND b.amountflg=0 ");
    lSql1.append("AND b.stockcode=dt.stockcode AND a.ordercode=dt.ordercode ");
    lSql1.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
    
    if (!SIDBUtil.hasData(lConnection, lSql1.toString())) return;
    lSql1.append(" ORDER BY b.stockcode,a.individualcode");
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("SELECT a.branchcode,a.chargecode,b.tel,a.fax,a.initdatetime ");
    lSql2.append(",b.deliveryaddressee,b.postcode1,b.postcode2,b.address1,b.address2,b.address3,b.companyname ");
    lSql2.append("FROM orderlatestvw a,orderdeliverylatestvw b ");
    lSql2.append("WHERE a.ordercode=b.ordercode AND a.status=1 ");
    lSql2.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
    
    Statement lStatement1 = lConnection.createStatement();
    ResultSet lResultSet1 = lStatement1.executeQuery(lSql2.toString());
    
    String hacchuCode = "";
    SIInsertRec lRec1 = new SIInsertRec("hacchutbl");
    
    if (lResultSet1.next()) {
      lRec1.add("orderCode", lOrderCode);
      lRec1.add("orderFlg", "1");
      lRec1.add("branchCode", lResultSet1.getString("branchcode"));
      lRec1.add("chargeCode", lResultSet1.getString("chargecode"));
      lRec1.add("directFlg", "0");
      lRec1.add("custName", lResultSet1.getString("deliveryaddressee"));
      lRec1.add("salonName", lResultSet1.getString("companyname"));
      lRec1.add("postCode1", lResultSet1.getString("postcode1"));
      lRec1.add("postCode2", lResultSet1.getString("postcode2"));
      lRec1.add("address1", lResultSet1.getString("address1"));
      lRec1.add("address2", lResultSet1.getString("address2"));
      lRec1.add("address3", lResultSet1.getString("address3"));
      lRec1.add("tel", lResultSet1.getString("tel"));
      lRec1.add("fax", lResultSet1.getString("fax"));
      lRec1.add("arrivalPlanTime", "");
      lRec1.add("initDateTime", lResultSet1.getString("initdatetime"));
      lRec1.add("updateDateTime", lResultSet1.getString("initdatetime"));
      lRec1.add("hacchuOutputDateTime", "");
      lRec1.add("hacchuUpdateDateTime", "");
      lRec1.add("hacchuOutputUserCode", "");
      lRec1.add("hacchuMailFlg", "0");
      lRec1.add("storeMailDateTime", "");
      lRec1.add("status", "1");
      lRec1.add("layercode", "1");
      lRec1.add("enabledflg", "1");
      lRec1.add("taxRate", SITax.getTaxRate(lConnection));
    }else{
      breakFlg = true;
    }
    SIDBUtil.close(lStatement1, lResultSet1);
    if (breakFlg) return;
    
    Statement lStatement2 = lConnection.createStatement();
    ResultSet lResultSet2 = lStatement2.executeQuery(lSql1.toString());
    
    int stockCount = 0;
    int individualCount = 1;
    String stockCode = "";
    
    while (lResultSet2.next()) {
      if (!stockCode.equals(lResultSet2.getString("stockcode"))) {
        stockCount++;
        individualCount = 0;
        stockCode = lResultSet2.getString("stockcode");
        hacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
        lRec1.add("hacchuCode", hacchuCode);
        lRec1.add("stockCode", stockCode);
        lRec1.add("stockName", lResultSet2.getString("stockname"));
        lRec1.add("arrivalPlanDate", lResultSet2.getString("deliverydate"));
        lRec1.execute(lConnection);
      }
      individualCount++;
      
      SIInsertRec lRec2 = new SIInsertRec("hacchudetailtbl");
      lRec2.add("hacchuCode", hacchuCode);
      lRec2.add("hacchuIndividualCode", String.valueOf(individualCount));
      lRec2.add("hacchuBranchCode", "1");
      lRec2.add("individualCode", lResultSet2.getString("individualcode"));
      lRec2.add("cmdtyName", lResultSet2.getString("cmdtyname"));
      lRec2.add("colorName", lResultSet2.getString("colorname"));
      lRec2.add("branchCode", "0");
      lRec2.add("branchName", "直送");
      lRec2.add("shipFlg", "0");
      lRec2.add("storeNumber", "");
      lRec2.add("amount", lResultSet2.getString("amount"));
      lRec2.add("amountAdd", "0");
      lRec2.add("purchasePrice", lResultSet2.getString("purchaseprice"));
      lRec2.add("importCost", "0");
      lRec2.add("processingExpence", "0");
      lRec2.add("deliveryFee", "0");
      lRec2.add("comment", "");
      lRec2.add("shippmentDate", "");
      lRec2.add("layercode", "1");
      lRec2.execute(lConnection);
    }
    SIDBUtil.close(lStatement2, lResultSet2);
  }
  
  private void createHacchuData(Connection lConnection,UIRegHacchu hacchuEdit) throws SIDBAccessException,SIDuplicateKeyException,SQLException {
    boolean breakFlg = false;
    StringBuffer lSql1 = new StringBuffer();
    lSql1.append("SELECT a.individualcode,a.cmdtyname,b.stockcode,c.stockname,d.colorname,a.amount,a.purchaseprice ");
    lSql1.append("FROM orderdetaillatestvw a,cmdtyunittbl b,stockmtbl c,colortbl d ");
    lSql1.append("WHERE a.individualcode=b.individualcode AND b.stockcode=c.stockcode AND b.colorcode=d.colorcode AND b.amountflg=0 AND a.shippmentdate IS NULL ");
    lSql1.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(hacchuEdit.getOrderCode()," "));
    lSql1.append("AND b.stockcode=").append(SIDBUtil.SQL2Str(hacchuEdit.getStockCode()));
    
    if (!SIDBUtil.hasData(lConnection, lSql1.toString())) return;
    lSql1.append(" ORDER BY b.stockcode,a.individualcode");
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("SELECT (SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl) AS hacchucode ");
    lSql2.append(",a.branchcode,a.chargecode,b.tel,a.fax,a.initdatetime,c.stockname,a.taxrate ");
    lSql2.append(",b.deliveryaddressee,b.postcode1,b.postcode2,b.address1,b.address2,b.address3,b.companyname ");
    lSql2.append("FROM orderlatestvw a,orderdeliverylatestvw b,stockmtbl c ");
    lSql2.append("WHERE a.ordercode=b.ordercode AND a.status=1 ");
    lSql2.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(hacchuEdit.getOrderCode()," "));
    lSql2.append("AND c.stockcode=").append(SIDBUtil.SQL2Str(hacchuEdit.getStockCode()));
    
    Statement lStatement = lConnection.createStatement();
    ResultSet lResultSet = lStatement.executeQuery(lSql2.toString());
    if (lResultSet.next()) {
      SIInsertRec lRec1 = new SIInsertRec("hacchutbl");
      hacchuEdit.setHacchuCode(lResultSet.getString("hacchucode"));
      lRec1.add("hacchuCode", hacchuEdit.getHacchuCode());
      lRec1.add("stockCode", hacchuEdit.getStockCode());
      lRec1.add("stockName", lResultSet.getString("stockname"));
      lRec1.add("orderCode", hacchuEdit.getOrderCode());
      lRec1.add("orderFlg", "1");
      lRec1.add("branchCode", lResultSet.getString("branchcode"));
      lRec1.add("chargeCode", lResultSet.getString("chargecode"));
      lRec1.add("directFlg", "0");
      lRec1.add("custName", lResultSet.getString("deliveryaddressee"));
      lRec1.add("salonName", lResultSet.getString("companyname"));
      lRec1.add("postCode1", lResultSet.getString("postcode1"));
      lRec1.add("postCode2", lResultSet.getString("postcode2"));
      lRec1.add("address1", lResultSet.getString("address1"));
      lRec1.add("address2", lResultSet.getString("address2"));
      lRec1.add("address3", lResultSet.getString("address3"));
      lRec1.add("tel", lResultSet.getString("tel"));
      lRec1.add("fax", lResultSet.getString("fax"));
      lRec1.add("arrivalPlanDate", "");
      lRec1.add("arrivalPlanTime", "");
      lRec1.add("initDateTime", lResultSet.getString("initdatetime"));
      lRec1.add("updateDateTime", lResultSet.getString("initdatetime"));
      lRec1.add("hacchuOutputDateTime", "");
      lRec1.add("hacchuUpdateDateTime", "");
      lRec1.add("hacchuOutputUserCode", "");
      lRec1.add("hacchuMailFlg", "0");
      lRec1.add("storeMailDateTime", "");
      lRec1.add("status", "1");
      lRec1.add("taxRate", lResultSet.getString("taxrate"));
      lRec1.add("layercode", "1");
      lRec1.add("enabledflg", "1");
      lRec1.execute(lConnection);
    }else{
      breakFlg = true;
    }
    SIDBUtil.close(lStatement, lResultSet);
    if (breakFlg) return;
    
    Statement lStatement2 = lConnection.createStatement();
    ResultSet lResultSet2 = lStatement2.executeQuery(lSql1.toString());
    
    int individualCount = 1;
    
    while (lResultSet2.next()) {
      SIInsertRec lRec2 = new SIInsertRec("hacchudetailtbl");
      lRec2.add("hacchuCode", hacchuEdit.getHacchuCode());
      lRec2.add("hacchuIndividualCode", String.valueOf(individualCount));
      lRec2.add("hacchuBranchCode", "1");
      lRec2.add("individualCode", lResultSet2.getString("individualcode"));
      lRec2.add("cmdtyName", lResultSet2.getString("cmdtyname"));
      lRec2.add("colorName", lResultSet2.getString("colorname"));
      lRec2.add("branchCode", "0");
      lRec2.add("branchName", "直送");
      lRec2.add("shipFlg", "0");
      lRec2.add("storeNumber", "");
      lRec2.add("amount", lResultSet2.getString("amount"));
      lRec2.add("amountAdd", "0");
      lRec2.add("purchasePrice", lResultSet2.getString("purchaseprice"));
      lRec2.add("importCost", "0");
      lRec2.add("processingExpence", "0");
      lRec2.add("deliveryFee", "0");
      lRec2.add("comment", "");
      lRec2.add("shippmentDate", "");
      lRec2.add("layercode", "1");
      lRec2.execute(lConnection);
      individualCount++;
    }
    SIDBUtil.close(lStatement2, lResultSet2);
  }
  
  public static void updateHacchuDeliveryData(Connection lConnection,String lOrderCode) throws SIDBAccessException,SIDuplicateKeyException,SQLException {
    StringBuffer lSql1 = new StringBuffer();
    lSql1.append("SELECT a.branchcode,a.chargecode,b.tel,a.fax,a.updatedatetime ");
    lSql1.append(",b.deliveryaddressee,b.postcode1,b.postcode2,b.address1,b.address2,b.address3,b.companyname ");
    lSql1.append("FROM orderlatestvw a,orderdeliverylatestvw b ");
    lSql1.append("WHERE a.ordercode=b.ordercode AND a.status=1 ");
    lSql1.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("SELECT a.hacchucode ");
    lSql2.append("FROM hacchutbl a ");
    lSql2.append(",(SELECT hacchucode,layercode,sum(CASE WHEN shippmentdate IS NULL THEN 0 ELSE 1 END) AS ship ");
    lSql2.append(" FROM hacchudetailtbl GROUP BY hacchucode,layercode) b ");
    lSql2.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg=1 ");
    lSql2.append("AND a.status=1 AND b.ship=0 ");
    lSql2.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
    
    Statement lStatement1 = lConnection.createStatement();
    ResultSet lResultSet1 = lStatement1.executeQuery(lSql1.toString());
    
    SIModifyRec lRec = new SIModifyRec("hacchutbl");
    
    if (lResultSet1.next()) {
      Statement lStatement2 = lConnection.createStatement();
      ResultSet lResultSet2 = lStatement2.executeQuery(lSql2.toString());
      while (lResultSet2.next()){
        lRec = new SIModifyRec("hacchutbl");
        lRec.addCondition("hacchuCode", lResultSet2.getString("hacchuCode"));
        lRec.addCondition("orderCode", lOrderCode);
        lRec.add("custName", lResultSet1.getString("deliveryaddressee"));
        lRec.add("salonName", lResultSet1.getString("companyname"));
        lRec.add("postCode1", lResultSet1.getString("postcode1"));
        lRec.add("postCode2", lResultSet1.getString("postcode2"));
        lRec.add("address1", lResultSet1.getString("address1"));
        lRec.add("address2", lResultSet1.getString("address2"));
        lRec.add("address3", lResultSet1.getString("address3"));
        lRec.add("tel", lResultSet1.getString("tel"));
        lRec.add("fax", lResultSet1.getString("fax"));
        lRec.add("updateDateTime", lResultSet1.getString("updatedatetime"));
        lRec.execute(lConnection);
      }
      SIDBUtil.close(lResultSet2, lStatement2);
    }
    SIDBUtil.close(lResultSet1, lStatement1);
  }
  
  private boolean sendRequestMail(Connection lConnection,UIRegHacchu hacchuEdit,boolean isFirst) throws SIDBAccessException {
    boolean lRes = true;
    
    String[] sendList;
    String chargeName = "";
    
    SISendMail lSendMail = new SISendMail();
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_HACCHU_REQUEST);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    if (isFirst) {
      lSendMail.appendMailTitle("", "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    } else {
      lSendMail.appendMailTitle("【再送】", "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    }
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a.email ");
    lSqlBuf.append("FROM chargetbl a,mngusermtbl b,chargetbl c ");
    lSqlBuf.append("WHERE a.belongingbranchcode=c.belongingbranchcode ");
    lSqlBuf.append("AND b.usercode=a.chargecode ");
    lSqlBuf.append("AND b.mngpurchaselevel>1 ");
    lSqlBuf.append("AND c.chargecode = ").append(SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append("SELECT chargename,coalesce(email,'center@beautygarage.jp') AS fromMail ");
    lSqlBuf2.append("FROM chargetbl ");
    lSqlBuf2.append("WHERE chargecode=").append(SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()," "));
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      StringBuffer mail = new StringBuffer();
      while(lResultSet.next()) {
        mail.append(lResultSet.getString("email")).append(";");
      }
      sendList = mail.toString().split(";");
      SIDBUtil.close(lStatement, lResultSet);
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf2.toString());
      while(lResultSet.next()) {
        lSendMail.setFromMailAddress(lResultSet.getString("fromMail"));
        chargeName = lResultSet.getString("chargeName");
      }
      SIDBUtil.close(lStatement, lResultSet);
    }catch (SQLException e) {
      e.printStackTrace();
      SIDBUtil.close(lStatement, lResultSet);
      return false;
    }
    
    StringBuffer lMailBuf = new StringBuffer();
    lMailBuf.append("\n発注担当者:").append(chargeName);
    lMailBuf.append("\n発注番号:").append(hacchuEdit.getHacchuCode());
    lMailBuf.append("\n仕入先:").append(hacchuEdit.getStockName());
    lMailBuf.append("\n");
    
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setToMailAddress(sendList);
      lSendMail.execute();
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    }
    return lRes;
  }
  
  private boolean sendApproveMail(Connection lConnection,UIRegHacchu hacchuEdit,String userCode) throws SIDBAccessException {
    boolean lRes = true;
    String[] sendList = new String[2];
    SISendMail lSendMail = new SISendMail();
    String userName = "";
    String chargeName = "";
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_HACCHU_APPROVAL);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    lSendMail.appendMailTitle("", "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a.email AS toMail,b.username,coalesce(c.email,'center@beautygarage.jp') AS fromMail ");
    lSqlBuf.append("FROM chargetbl a,mngusermtbl b LEFT OUTER JOIN chargetbl c ON b.usercode=c.chargecode ");
    lSqlBuf.append("WHERE a.chargecode=").append(SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()," "));
    lSqlBuf.append("AND b.usercode=").append(SIDBUtil.SQL2Str(userCode));
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      if (lResultSet.next()) {
        lSendMail.setFromMailAddress(lResultSet.getString("fromMail"));
        sendList[0] = lResultSet.getString("toMail");
        sendList[1] = lResultSet.getString("fromMail");
        userName = lResultSet.getString("username");
      }
      SIDBUtil.close(lStatement, lResultSet);
      chargeName = SIDBUtil.getFirstData(lConnection, "SELECT chargename FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    }catch (SQLException e) {
      e.printStackTrace();
      SIDBUtil.close(lStatement, lResultSet);
      return false;
    }
    
    StringBuffer lMailBuf = new StringBuffer();
    lMailBuf.append("\n担当者:").append(chargeName);
    lMailBuf.append("\n決裁者:").append(userName);
    lMailBuf.append("\n\n発注番号:").append(hacchuEdit.getHacchuCode());
    lMailBuf.append("\n仕入先:").append(hacchuEdit.getStockName());
    lMailBuf.append("\n");
    
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setToMailAddress(sendList);
      lRes = lSendMail.execute();
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    }
    return lRes;
  }
  
  private boolean sendRejectionMail(Connection lConnection,UIRegHacchu hacchuEdit,String userCode) throws SIDBAccessException {
    boolean lRes = true;
    String[] sendList = new String[2];
    SISendMail lSendMail = new SISendMail();
    String userName = "";
    String chargeName = "";
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_HACCHU_REJECTION);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    lSendMail.appendMailTitle("", "【発注番号："+hacchuEdit.getHacchuCode()+"】");
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a.email AS toMail,b.username,coalesce(c.email,'center@beautygarage.jp') AS fromMail ");
    lSqlBuf.append("FROM chargetbl a,mngusermtbl b LEFT OUTER JOIN chargetbl c ON b.usercode=c.chargecode ");
    lSqlBuf.append("WHERE a.chargecode=").append(SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()," "));
    lSqlBuf.append("AND b.usercode=").append(SIDBUtil.SQL2Str(userCode));
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      if (lResultSet.next()) {
        lSendMail.setFromMailAddress(lResultSet.getString("fromMail"));
        sendList[0] = lResultSet.getString("toMail");
        sendList[1] = lResultSet.getString("fromMail");
        userName = lResultSet.getString("username");
      }
      SIDBUtil.close(lStatement, lResultSet);
      chargeName = SIDBUtil.getFirstData(lConnection, "SELECT chargename FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(hacchuEdit.getChargeCode()));
    }catch (SQLException e) {
      e.printStackTrace();
      SIDBUtil.close(lStatement, lResultSet);
      return false;
    }
    
    StringBuffer lMailBuf = new StringBuffer();
    lMailBuf.append("\n担当者:").append(chargeName);
    lMailBuf.append("\n決裁者:").append(userName);
    lMailBuf.append("\n\n発注番号:").append(hacchuEdit.getHacchuCode());
    lMailBuf.append("\n仕入先:").append(hacchuEdit.getStockName());
    lMailBuf.append("\n");
    
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setToMailAddress(sendList);
      lRes = lSendMail.execute();
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    }
    return lRes;
  }
  
  private boolean sendStoreMail(Connection lConnection,UIRegHacchu hacchuEdit,String userCode) throws SIDBAccessException {
    boolean lRes = true;
    String[] sendList = new String[1];
    String[] sendListCC = new String[1];
    String fromMail = "center@beautygarage.jp";
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT coalesce(b.email,'center@beautygarage.jp') AS fromMail ");
    lSqlBuf.append("FROM mngusermtbl a LEFT OUTER JOIN chargetbl b ON a.usercode=b.chargecode ");
    lSqlBuf.append("WHERE a.usercode=").append(SIDBUtil.SQL2Str(userCode));
    
    fromMail = SIDBUtil.getFirstData(lConnection, lSqlBuf.toString());
    
    Statement lStatement1 = null;
    ResultSet lResultSet1 = null;
    Statement lStatement2 = null;
    ResultSet lResultSet2 = null;
    try {
      
      StringBuffer lSqlBuf1 = new StringBuffer();
      lSqlBuf1.append("SELECT DISTINCT e.email ");
      lSqlBuf1.append(",d.branchname,c.chargename,b.branchname AS storebranch,b.branchcode");
      lSqlBuf1.append(",a.stockname,a.arrivalplandate,a.arrivalplantime ");
      lSqlBuf1.append("FROM hacchutbl a,hacchudetailtbl b,chargetbl c,branchtbl d,branchtbl e ");
      lSqlBuf1.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg='1' AND b.shipflg='0' ");
      lSqlBuf1.append("AND a.branchcode=d.branchcode AND a.chargecode=c.chargecode AND e.publicflg='1' AND b.branchcode=e.branchcode ");
      lSqlBuf1.append("AND b.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()," "));
      lSqlBuf1.append("ORDER BY b.branchcode ");
      
      lStatement1 = lConnection.createStatement();
      lResultSet1 = lStatement1.executeQuery(lSqlBuf1.toString());
      while (lResultSet1.next()) {
        SISendMail lSendMail = new SISendMail();
        String branchCode = "";
        try {
          lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_HACCHU_STORE);
        } catch (SIFatalException e2) {
          e2.printStackTrace();
        }
        lSendMail.appendMailTitle("", "【発注番号："+hacchuEdit.getHacchuCode()+"】");
        lSendMail.setFromMailAddress(fromMail);
        StringBuffer lMailBuf = new StringBuffer();
        lMailBuf.append("\n------------------------------");
        lMailBuf.append("\n発注担当者：").append(lResultSet1.getString("branchname")+" ").append(lResultSet1.getString("chargename"));
        lMailBuf.append("\n発注番号：").append(hacchuEdit.getHacchuCode());
        lMailBuf.append("\n仕入先：").append(lResultSet1.getString("stockname"));
        lMailBuf.append("\n納品先：").append(lResultSet1.getString("storebranch"));
        lMailBuf.append("\n納期予定：").append(SIDBUtil.getDate(lResultSet1.getTimestamp("arrivalplandate")));
        lMailBuf.append(SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_HACCHU_ARRIVALPLAN_FLG_INX, lResultSet1.getString("arrivalplantime")));
        lMailBuf.append("\n------------------------------");
        
        branchCode = lResultSet1.getString("branchcode");
        sendList[0] = lResultSet1.getString("email");
        sendListCC[0] = fromMail;
        
        StringBuffer lSqlBuf2 = new StringBuffer();
        lSqlBuf2.append("SELECT b.individualcode,b.cmdtyname,b.colorname,b.amount,b.amountadd,c.othername,d.setitem1,d.setitem2 ");
        lSqlBuf2.append("FROM hacchutbl a,hacchudetailtbl b,cmdtymtbl c,individualtbl d ");
        lSqlBuf2.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND a.enabledflg='1' AND b.shipflg='0' ");
        lSqlBuf2.append("AND b.hacchucode=").append(SIDBUtil.SQL2Str(hacchuEdit.getHacchuCode()," "));
        lSqlBuf2.append("AND b.branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
        lSqlBuf2.append("AND b.individualcode=d.individualcode AND d.cmdtycode=c.cmdtycode ");
        lSqlBuf2.append("ORDER BY b.individualcode ");
        
        lStatement2 = lConnection.createStatement();
        lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
        
        while (lResultSet2.next()){
          if ("18".equals(branchCode)) {
            lMailBuf.append("\n棚番:").append(SIBGUtil.getStockNumberAll(lConnection,lResultSet2.getString("individualcode")));
          }
          lMailBuf.append("\n在庫コード:").append(lResultSet2.getString("individualcode"));
          lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname"));
          if (SIUtil.isNotNull(lResultSet2.getString("colorname"))){
            lMailBuf.append("(").append(lResultSet2.getString("colorname")).append(")");
          }
          /*
          lMailBuf.append("\n型式:");
          if (SIUtil.isNotNull(lResultSet2.getString("othername"))) lMailBuf.append(lResultSet2.getString("othername"));
          lMailBuf.append("\n検品時注意事項:");
          if (SIUtil.isNotNull(lResultSet2.getString("setitem1"))) lMailBuf.append(lResultSet2.getString("setitem1"));
          lMailBuf.append("\n同梱品情報:");
          if (SIUtil.isNotNull(lResultSet2.getString("setitem2"))) lMailBuf.append(lResultSet2.getString("setitem2"));
          */
          lMailBuf.append("\n入荷数:").append(lResultSet2.getString("amount"));
          if (SIUtil.isNotNull(lResultSet2.getString("amountadd"))&&!"0".equals(lResultSet2.getString("amountadd"))){
            lMailBuf.append("(").append(lResultSet2.getString("amountadd")).append(")");
          }
          lMailBuf.append("\n--------------------");
        }
        SIDBUtil.close(lStatement2, lResultSet2);
        lSendMail.appendContent1(lMailBuf.toString());
        
        lSendMail.setToMailAddress(sendList);
        lSendMail.setCCMailAddress(sendListCC);
        lRes = lRes && lSendMail.execute();
      }
    }catch (SQLException e) {
      e.printStackTrace();
      lRes = false;
    } catch (SIFatalException e) {
      e.printStackTrace();
      lRes = false;
    } finally {
      SIDBUtil.close(lStatement1, lResultSet1);
    }
    return lRes;
  }
  
  private String createHacchuReturn(Connection lConnection,String oldHacchuCode) throws SIDBAccessException,SQLException,SIDuplicateKeyException{
    SIDateTime lDate = new SIDateTime();
    String newHacchuCode = "";
    
    newHacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
    UIRegHacchu hacchuEdit = new UIRegHacchu(oldHacchuCode,"");
    hacchuEdit.reset(lConnection);
    
    SIInsertRec lRec1 = new SIInsertRec("hacchutbl");
    lRec1.add("hacchucode", newHacchuCode);
    lRec1.add("ordercode", hacchuEdit.getOrderCode());
    lRec1.add("orderflg", hacchuEdit.getOrderFlg());
    lRec1.add("stockcode", hacchuEdit.getStockCode());
    lRec1.add("stockname", hacchuEdit.getStockName());
    lRec1.add("branchcode", hacchuEdit.getBranchCode());
    lRec1.add("chargecode", hacchuEdit.getChargeCode());
    lRec1.add("directflg", hacchuEdit.getDirectFlg());
    lRec1.add("custname", hacchuEdit.getCustName());
    lRec1.add("salonname", hacchuEdit.getSalonName());
    lRec1.add("postcode1", hacchuEdit.getPostCode1());
    lRec1.add("postcode2", hacchuEdit.getPostCode2());
    lRec1.add("address1", hacchuEdit.getAddress1());
    lRec1.add("address2", hacchuEdit.getAddress2());
    lRec1.add("address3", hacchuEdit.getAddress3());
    lRec1.add("tel", hacchuEdit.getTel());
    lRec1.add("fax", hacchuEdit.getFax());
    lRec1.add("initdatetime", lDate.getFullDateTime());
    lRec1.add("updatedatetime", lDate.getFullDateTime());
    lRec1.add("arrivalplandate", hacchuEdit.getArrivalPlanDate());
    lRec1.add("arrivalplantime", hacchuEdit.getArrivalPlanTime());
    lRec1.add("taxrate", hacchuEdit.getTaxRate());
    if (SIUtil.isNull(hacchuEdit.getOrderCode())&&SICheckUtil.isGreater(hacchuEdit.getTotalOfPrice(), threshold)) {
      lRec1.add("status", "3");
    } else {
      lRec1.add("status", "1");
    }
    lRec1.add("layercode", "1");
    lRec1.add("enabledflg", "1");
    lRec1.execute(lConnection);
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("SELECT b.* FROM hacchutbl a,hacchudetailtbl b ");
    lSql2.append("WHERE a.hacchucode=b.hacchucode AND a.layercode=b.layercode AND b.shipflg=0 AND b.amount>0 ");
    lSql2.append("AND a.enabledflg=1 AND a.hacchucode=").append(SIDBUtil.SQL2Str(oldHacchuCode));
    
    Statement lStatement = lConnection.createStatement();
    ResultSet lResultSet = lStatement.executeQuery(lSql2.toString());
    
    while (lResultSet.next()) {
      SIInsertRec lRec2 = new SIInsertRec("hacchudetailtbl");
      lRec2.add("hacchucode", newHacchuCode);
      lRec2.add("hacchuindividualcode", lResultSet.getString("hacchuindividualcode"));
      lRec2.add("hacchubranchcode", lResultSet.getString("hacchubranchcode"));
      lRec2.add("individualcode", lResultSet.getString("individualcode"));
      lRec2.add("cmdtyname", lResultSet.getString("cmdtyname"));
      lRec2.add("colorname", lResultSet.getString("colorname"));
      lRec2.add("branchcode", lResultSet.getString("branchcode"));
      lRec2.add("branchname", lResultSet.getString("branchname"));
      lRec2.add("shipflg", "0");
      lRec2.add("amount", lResultSet.getString("amount"));
      lRec2.add("amountadd", lResultSet.getString("amountadd"));
      lRec2.add("purchaseprice", lResultSet.getString("purchaseprice"));
      lRec2.add("processingexpence", lResultSet.getString("processingexpence"));
      lRec2.add("deliveryfee", lResultSet.getString("deliveryfee"));
      lRec2.add("importcost", lResultSet.getString("importcost"));
      lRec2.add("comment", lResultSet.getString("comment"));
      lRec2.add("layercode", "1");
      lRec2.execute(lConnection);
    }
    
    SIDBUtil.close(lResultSet, lStatement);
    
    return newHacchuCode;
  }
  
  public static void executeClose(Connection lConnection,String closeDate,String userCode) throws SIDuplicateKeyException,SIDBAccessException,SQLException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIDateTime lDate1 = new SIDateTime();
    SIDateTime lDate2 = new SIDateTime();
    try {
      lDate1 = new SIDateTime(closeDate,SIConfig.SIDATE_FORMAT);
      lDate1.addDay(-1);
      lDate2 = new SIDateTime(closeDate,SIConfig.SIDATE_FORMAT);
    } catch (Exception e) {
      throw new SIDBAccessException();
    }
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT a.stockcode,coalesce(to_char(a.closelimit ,'00'),'') AS closelimit,coalesce(to_char(a.closelimit+1 ,'00'),'') AS limitnext FROM stockmtbl a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(initdate) AS initdate,stockcode FROM hacchurecordtbl b GROUP BY stockcode) b ");
    lSql.append("ON a.stockcode=b.stockcode ");
    lSql.append("WHERE b.initdate IS NULL OR b.initdate<").append(SIDBUtil.SQL2Str(lDate1.getFullDate()));
    
    lStatement = lConnection.createStatement();
    lResultSet = lStatement.executeQuery(lSql.toString());
    
    while (lResultSet.next()) {
      String stockCode = lResultSet.getString("stockcode");
      String closeLimit = lResultSet.getString("closelimit").trim();
      
      StringBuffer lPriceSql = new StringBuffer();
      lPriceSql.append("SELECT sum(list.p+list.m) FROM ");
      lPriceSql.append("(SELECT a.p,a.m FROM hacchupaymentvw a ");
      lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
      lPriceSql.append(" WHERE a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
      lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.shippmentdate > b.lastClose END ");
      lPriceSql.append(" AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(lDate1.getFullDate()));
      lPriceSql.append(" UNION ALL ");
      lPriceSql.append(" SELECT CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS p,CASE WHEN a.paymentprice>0 THEN -1*a.paymentprice ELSE 0 END AS m ");
      lPriceSql.append(" FROM hacchupaymenttbl a ");
      lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode");
      lPriceSql.append(" WHERE a.enabledflg='1' AND a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
      lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.paymentdate > b.lastClose END ");
      lPriceSql.append(" AND a.paymentdate<=").append(SIDBUtil.SQL2Str(lDate1.getFullDate()));
      lPriceSql.append(") list ");
      
      String totalPrice = SIDBUtil.getFirstData(lConnection, lPriceSql.toString());
      if (SIUtil.isNull(totalPrice)) totalPrice="0";
      SIDateTime lDate = new SIDateTime();
      
      SIInsertRec lInsertRec = new SIInsertRec("hacchupaymenttbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("paymentPrice", totalPrice);
      lInsertRec.add("paymentDate", lDate1.getFullDate());
      lInsertRec.add("initDateTime", lDate.getFullDateTime());
      lInsertRec.add("initUserCode", userCode);
      lInsertRec.add("memo", "次月繰越分");
      lInsertRec.add("index", "9");
      lInsertRec.execute(lConnection);
      
      lDate.addSecond(1);
      
      lInsertRec = new SIInsertRec("hacchupaymenttbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("paymentPrice", SIUtil.multi("-1", totalPrice));
      lInsertRec.add("paymentDate", lDate2.getFullDate());
      lInsertRec.add("initDateTime", lDate.getFullDateTime());
      lInsertRec.add("initUserCode", userCode);
      lInsertRec.add("memo", "前月繰越分");
      lInsertRec.add("index", "0");
      lInsertRec.execute(lConnection);
      
      lInsertRec = new SIInsertRec("hacchurecordtbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("initDate", lDate1.getFullDate());
      lInsertRec.add("countflg", "1");
      lInsertRec.execute(lConnection);
      
      if (SIUtil.isNotNull(closeLimit)&&!"31".equals(closeLimit)) {//31日以外の日付が入っている場合
        String limitNext = lResultSet.getString("limitnext").trim();
        String limitDate = lDate1.getYearStr()+"/"+lDate1.getMonthStr()+"/"+closeLimit;
        String nextDate = lDate1.getYearStr()+"/"+lDate1.getMonthStr()+"/"+limitNext;
        
        lPriceSql = new StringBuffer();
        lPriceSql.append("SELECT sum(list.p+list.m) FROM ");
        lPriceSql.append("(SELECT a.p,a.m FROM hacchupaymentvw a ");
        lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose ");
        lPriceSql.append(" FROM hacchurecordtbl WHERE initdate<").append(SIDBUtil.SQL2Str(limitDate));
        lPriceSql.append(" GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
        lPriceSql.append(" WHERE a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
        lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.shippmentdate > b.lastClose END ");
        lPriceSql.append(" AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(limitDate));
        lPriceSql.append(" UNION ALL ");
        lPriceSql.append(" SELECT CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS p,CASE WHEN a.paymentprice>0 THEN -1*a.paymentprice ELSE 0 END AS m ");
        lPriceSql.append(" FROM hacchupaymenttbl a ");
        lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose ");
        lPriceSql.append(" FROM hacchurecordtbl WHERE initdate<").append(SIDBUtil.SQL2Str(limitDate));
        lPriceSql.append(" GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
        lPriceSql.append(" WHERE a.enabledflg='1' AND a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
        lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.paymentdate > b.lastClose END ");
        lPriceSql.append(" AND a.paymentdate<=").append(SIDBUtil.SQL2Str(limitDate));
        lPriceSql.append(") list ");
        
        totalPrice = SIDBUtil.getFirstData(lConnection, lPriceSql.toString());
        if (SIUtil.isNull(totalPrice)) totalPrice="0";
        
        lInsertRec = new SIInsertRec("hacchupaymenttbl");
        lInsertRec.add("stockCode", stockCode);
        lInsertRec.add("paymentPrice", totalPrice);
        lInsertRec.add("paymentDate", limitDate);
        lInsertRec.add("initDateTime", lDate.getFullDateTime());
        lInsertRec.add("initUserCode", userCode);
        lInsertRec.add("memo", "次回繰越分");
        lInsertRec.add("index", "9");
        lInsertRec.execute(lConnection);
        
        lDate.addSecond(1);
        
        lInsertRec = new SIInsertRec("hacchupaymenttbl");
        lInsertRec.add("stockCode", stockCode);
        lInsertRec.add("paymentPrice", SIUtil.multi("-1", totalPrice));
        lInsertRec.add("paymentDate", nextDate);
        lInsertRec.add("initDateTime", lDate.getFullDateTime());
        lInsertRec.add("initUserCode", userCode);
        lInsertRec.add("memo", "前回繰越分");
        lInsertRec.add("index", "0");
        lInsertRec.execute(lConnection);
        
        lInsertRec = new SIInsertRec("hacchurecordtbl");
        lInsertRec.add("stockCode", stockCode);
        lInsertRec.add("initDate", limitDate);
        lInsertRec.add("countflg", "0");
        lInsertRec.execute(lConnection);
      }
    }
    SIDBUtil.close(lStatement, lResultSet);
  }
  
  private void executeClose(Connection lConnection,UIHacchuPaymentListCond hacchuPaymentList,String userCode) throws SIDuplicateKeyException,SIDBAccessException,SQLException{
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT a.stockcode FROM stockmtbl a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(initdate) AS initdate,stockcode FROM hacchurecordtbl b GROUP BY stockcode) b ");
    lSql.append("ON a.stockcode=b.stockcode ");
    //lSql.append("WHERE a.closelimit='31' ");
    //lSql.append("AND (b.initdate IS NULL OR b.initdate<").append(SIDBUtil.SQL2Str(hacchuPaymentList.getCloseDate(),")"));
    lSql.append("WHERE b.initdate IS NULL OR b.initdate<").append(SIDBUtil.SQL2Str(hacchuPaymentList.getCloseDate()));
    
    lStatement = lConnection.createStatement();
    lResultSet = lStatement.executeQuery(lSql.toString());
    
    while (lResultSet.next()) {
      String stockCode = lResultSet.getString("stockCode");
      
      StringBuffer lPriceSql = new StringBuffer();
      lPriceSql.append("SELECT sum(list.p+list.m) FROM ");
      lPriceSql.append("(SELECT a.p,a.m FROM hacchupaymentvw a ");
      lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
      lPriceSql.append(" WHERE a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
      lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.shippmentdate > b.lastClose END ");
      lPriceSql.append(" AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(hacchuPaymentList.getCloseDate()));
      lPriceSql.append(" UNION ALL ");
      lPriceSql.append(" SELECT CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS p,CASE WHEN a.paymentprice>0 THEN -1*a.paymentprice ELSE 0 END AS m ");
      lPriceSql.append(" FROM hacchupaymenttbl a ");
      lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode");
      lPriceSql.append(" WHERE a.enabledflg='1' AND a.stockcode=").append(SIDBUtil.SQL2Str(stockCode," "));
      lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.paymentdate > b.lastClose END ");
      lPriceSql.append(" AND a.paymentdate<=").append(SIDBUtil.SQL2Str(hacchuPaymentList.getCloseDate()));
      lPriceSql.append(") list ");
      
      String totalPrice = SIDBUtil.getFirstData(lConnection, lPriceSql.toString());
      if (SIUtil.isNull(totalPrice)) totalPrice="0";
      SIDateTime lDate = new SIDateTime();
      
      SIInsertRec lInsertRec = new SIInsertRec("hacchupaymenttbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("paymentPrice", totalPrice);
      lInsertRec.add("paymentDate", hacchuPaymentList.getCloseDate());
      lInsertRec.add("initDateTime", lDate.getFullDateTime());
      lInsertRec.add("initUserCode", userCode);
      lInsertRec.add("memo", "次回繰越分");
      lInsertRec.add("index", "9");
      lInsertRec.execute(lConnection);
      
      lDate.addSecond(1);
      
      lInsertRec = new SIInsertRec("hacchupaymenttbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("paymentPrice", SIUtil.multi("-1", totalPrice));
      lInsertRec.add("paymentDate", hacchuPaymentList.getCloseNextDate());
      lInsertRec.add("initDateTime", lDate.getFullDateTime());
      lInsertRec.add("initUserCode", userCode);
      lInsertRec.add("memo", "前回繰越分");
      lInsertRec.add("index", "0");
      lInsertRec.execute(lConnection);
      
      lInsertRec = new SIInsertRec("hacchurecordtbl");
      lInsertRec.add("stockCode", stockCode);
      lInsertRec.add("initDate", hacchuPaymentList.getCloseDate());
      lInsertRec.add("countflg", "1");
      lInsertRec.execute(lConnection);
    }
    SIDBUtil.close(lStatement, lResultSet);
  }
  
  private void executeClose(Connection lConnection,UIRegHacchuPayment hacchuPayment,String userCode) throws SIDuplicateKeyException,SIDBAccessException,SQLException{
    StringBuffer lPriceSql = new StringBuffer();
    lPriceSql.append("SELECT sum(list.p+list.m) FROM ");
    lPriceSql.append("(SELECT a.p,a.m FROM hacchupaymentvw a ");
    lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lPriceSql.append(" WHERE a.stockcode=").append(SIDBUtil.SQL2Str(hacchuPayment.getStockCode()," "));
    lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.shippmentdate > b.lastClose END ");
    lPriceSql.append(" AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(hacchuPayment.getPaymentDate()));
    lPriceSql.append(" UNION ALL ");
    lPriceSql.append(" SELECT CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS p,CASE WHEN a.paymentprice>0 THEN -1*a.paymentprice ELSE 0 END AS m ");
    lPriceSql.append(" FROM hacchupaymenttbl a ");
    lPriceSql.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastClose FROM hacchurecordtbl GROUP BY stockcode) b ON a.stockcode=b.stockcode");
    lPriceSql.append(" WHERE a.enabledflg=1 AND a.stockcode=").append(SIDBUtil.SQL2Str(hacchuPayment.getStockCode()," "));
    lPriceSql.append(" AND CASE WHEN b.lastClose IS NULL THEN true ELSE a.paymentdate > b.lastClose END ");
    lPriceSql.append(" AND a.paymentdate<=").append(SIDBUtil.SQL2Str(hacchuPayment.getPaymentDate()));
    lPriceSql.append(") list ");
    
    String totalPrice = SIDBUtil.getFirstData(lConnection, lPriceSql.toString());
    SIDateTime lDate1 = new SIDateTime();
    SIDateTime lDate2 = new SIDateTime();
    try{
      lDate2 = new SIDateTime(hacchuPayment.getPaymentDate(),SIConfig.SIDATE_FORMAT);
      lDate2.addDay(1);
    }catch(Exception e){
      e.printStackTrace();
    }
    
    SIInsertRec lInsertRec = new SIInsertRec("hacchupaymenttbl");
    lInsertRec.add("stockCode", hacchuPayment.getStockCode());
    lInsertRec.add("paymentPrice", totalPrice);
    lInsertRec.add("paymentDate", hacchuPayment.getPaymentDate());
    lInsertRec.add("initDateTime", lDate1.getFullDateTime());
    lInsertRec.add("initUserCode", userCode);
    lInsertRec.add("memo", "次回繰越分");
    lInsertRec.add("index", "9");
    lInsertRec.execute(lConnection);
    
    lDate1.addSecond(1);
    
    lInsertRec = new SIInsertRec("hacchupaymenttbl");
    lInsertRec.add("stockCode", hacchuPayment.getStockCode());
    lInsertRec.add("paymentPrice", SIUtil.multi("-1", totalPrice));
    lInsertRec.add("paymentDate", lDate2.getFullDate());
    lInsertRec.add("initDateTime", lDate1.getFullDateTime());
    lInsertRec.add("initUserCode", userCode);
    lInsertRec.add("memo", "前回繰越分");
    lInsertRec.add("index", "0");
    lInsertRec.execute(lConnection);
    
    lInsertRec = new SIInsertRec("hacchurecordtbl");
    lInsertRec.add("stockCode", hacchuPayment.getStockCode());
    lInsertRec.add("initDate", hacchuPayment.getPaymentDate());
    lInsertRec.add("countflg", "0");
    lInsertRec.execute(lConnection);
  }
  
  private void insertPayment(Connection lConnection,UIRegHacchuPayment hacchuPayment,String userCode) throws SIDBAccessException,SIDuplicateKeyException{
    SIInsertRec lRec = new SIInsertRec("hacchupaymenttbl");
    lRec.add("stockcode", hacchuPayment.getStockCode());
    lRec.add("paymentprice", hacchuPayment.getPaymentPriceTxt());
    lRec.add("paymentdate", hacchuPayment.getPaymentDate());
    lRec.add("memo", hacchuPayment.getPaymentMemoTxt());
    lRec.add("initusercode", userCode);
    lRec.add("initdatetime", new SIDateTime().getFullDateTime());
    lRec.add("index", "5");
    lRec.execute(lConnection);
  }
  
  private void deletePayment(Connection lConnection,String paymentCode) throws SIDBAccessException,SIDuplicateKeyException{
    SIModifyRec lRec = new SIModifyRec("hacchupaymenttbl");
    lRec.addCondition("paymentcode", paymentCode);
    lRec.add("enabledflg", "0");
    lRec.execute(lConnection);
  }
  
  private void produceCSVFile(HttpServletResponse response, Connection lConnection, UIHacchuPaymentListCond lListCond) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HACCHU_PAYMENT_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT s.stockcode,s.stockname,c.chargename");
    lSqlBuf.append(",coalesce(p2.price1,0)+coalesce(p3.price2x,0)-coalesce(p3.price3x,0) AS PaymentTotal");
    lSqlBuf.append(",coalesce(p1.price2a,0)-coalesce(p1.price3a,0) AS PurchaseTotal");
    lSqlBuf.append(",coalesce(p1.price2b,0)-coalesce(p1.price3b,0) AS DeliveryTotal");
    lSqlBuf.append(",coalesce(p2.price1,0)+coalesce(p1.price2a,0)+coalesce(p1.price2b,0)+coalesce(p3.price2x,0)-coalesce(p1.price3a,0)-coalesce(p1.price3b,0)-coalesce(p3.price3x,0) AS TotalOfPrice ");
    
    lSqlBuf.append("FROM stockmtbl s ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) r ");
    lSqlBuf.append("ON s.stockcode=r.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT a.stockcode,sum(CASE a.branchcode WHEN -1 THEN 0 ELSE a.p END) AS price2a,sum(CASE a.branchcode WHEN -1 THEN a.p ELSE 0 END) AS price2b,sum(a.p) AS price2");
    lSqlBuf.append(",-1 * sum(CASE a.branchcode WHEN -1 THEN 0 ELSE a.m END) AS price3a,-1 * sum(CASE a.branchcode WHEN -1 THEN a.m ELSE 0 END) AS price3b,sum(a.m) AS price3 FROM hacchupaymentvw a ");
    lSqlBuf.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) b ");
    lSqlBuf.append(" ON a.stockcode=b.stockcode ");
    lSqlBuf.append(" WHERE CASE WHEN b.lastclose IS NULL THEN true ELSE a.shippmentdate > b.lastclose END GROUP BY a.stockcode) p1 ");
    lSqlBuf.append("ON s.stockcode=p1.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,-1 * paymentprice AS price1,paymentdate FROM hacchupaymenttbl WHERE index='0' AND enabledflg=1) p2 ");
    lSqlBuf.append("ON r.lastclose + '1 days'::interval = p2.paymentdate AND s.stockcode=p2.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (");
    lSqlBuf.append("SELECT pp.stockcode,sum(CASE WHEN pp.paymentprice < 0 THEN -1 * pp.paymentprice ELSE 0 END) AS price2x ");
    lSqlBuf.append(",sum(CASE WHEN pp.paymentprice > 0 THEN pp.paymentprice ELSE 0 END) AS price3x ");
    lSqlBuf.append("FROM hacchupaymenttbl pp LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) pr ");
    lSqlBuf.append("ON pp.stockcode=pr.stockcode ");
    lSqlBuf.append("WHERE index!='0' AND enabledflg='1' AND CASE WHEN pr.lastclose IS NULL THEN true ELSE pp.paymentdate > pr.lastclose END GROUP BY pp.stockcode) p3 ");
    lSqlBuf.append("ON s.stockcode=p3.stockcode ");
    lSqlBuf.append(",chargetbl c ");
    lSqlBuf.append("WHERE s.chargecode=c.chargecode ");
    lSqlBuf.append(lListCond.getCondtionSQL());
    lSqlBuf.append(SICSVConf.getOrderBySQL(SICSVConf.SICSV_HACCHU_PAYMENT_LIST_INX));
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HACCHU_PAYMENT_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceCSVFile2(HttpServletResponse response, Connection lConnection, UIHacchuPaymentListCond lListCond) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT list.* FROM ");
    
    lSqlBuf.append("(SELECT a.stockcode,s.stockname,a.hacchucode,a.p AS paymentprice1,-1*a.m AS paymentprice2");
    lSqlBuf.append(",a.shippmentdate AS paymentdate,a.branchname AS memo");
    lSqlBuf.append(",a.inx,a.individualcode,c.chargename,a.amount,cm.cmdtyname ");
    lSqlBuf.append("FROM hacchupaymentvw a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    lSqlBuf.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append(",stockmtbl s,cmdtyunittbl cm ");
    lSqlBuf.append("WHERE a.individualcode=cm.individualcode AND a.stockcode=s.stockcode ");
    lSqlBuf.append("AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.shippmentDate>b.closeDate END ");
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("SELECT a.stockcode,s.stockname,null::numeric,CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS paymentprice1");
    lSqlBuf.append(",CASE WHEN a.paymentprice>0 THEN a.paymentprice ELSE 0 END AS paymentprice2 ");
    lSqlBuf.append(",a.paymentdate,a.memo,a.index AS inx,'' AS individualcode,c.chargename,null::numeric AS amount,''::text AS cmdtyname ");
    lSqlBuf.append("FROM hacchupaymenttbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    lSqlBuf.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.initusercode=c.chargecode ");
    lSqlBuf.append(",stockmtbl s ");
    lSqlBuf.append("WHERE a.stockcode=s.stockcode AND a.enabledflg='1' AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.paymentDate>b.closeDate END) list ");
    
    lSqlBuf.append(",stockmtbl s ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl WHERE countflg='1' GROUP BY stockcode) r ");
    lSqlBuf.append("ON s.stockcode=r.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT a.stockcode,sum(CASE a.branchcode WHEN -1 THEN 0 ELSE a.p END) AS price2a,sum(CASE a.branchcode WHEN -1 THEN a.p ELSE 0 END) AS price2b,sum(a.p) AS price2");
    lSqlBuf.append(",-1 * sum(CASE a.branchcode WHEN -1 THEN 0 ELSE a.m END) AS price3a,-1 * sum(CASE a.branchcode WHEN -1 THEN a.m ELSE 0 END) AS price3b,sum(a.m) AS price3 FROM hacchupaymentvw a ");
    lSqlBuf.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl WHERE countflg='1' GROUP BY stockcode) b ");
    lSqlBuf.append(" ON a.stockcode=b.stockcode ");
    lSqlBuf.append(" WHERE CASE WHEN b.lastclose IS NULL THEN true ELSE a.shippmentdate > b.lastclose END GROUP BY a.stockcode) p1 ");
    lSqlBuf.append("ON s.stockcode=p1.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,-1 * paymentprice AS price1,paymentdate FROM hacchupaymenttbl WHERE index='0' AND enabledflg=1) p2 ");
    lSqlBuf.append("ON r.lastclose + '1 days'::interval = p2.paymentdate AND s.stockcode=p2.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (");
    lSqlBuf.append("SELECT pp.stockcode,sum(CASE WHEN pp.paymentprice < 0 THEN -1 * pp.paymentprice ELSE 0 END) AS price2x ");
    lSqlBuf.append(",sum(CASE WHEN pp.paymentprice > 0 THEN pp.paymentprice ELSE 0 END) AS price3x ");
    lSqlBuf.append("FROM hacchupaymenttbl pp LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl WHERE countflg=1 GROUP BY stockcode) pr ");
    lSqlBuf.append("ON pp.stockcode=pr.stockcode ");
    lSqlBuf.append("WHERE index!='0' AND enabledflg='1' AND CASE WHEN pr.lastclose IS NULL THEN true ELSE pp.paymentdate > pr.lastclose END GROUP BY pp.stockcode) p3 ");
    lSqlBuf.append("ON s.stockcode=p3.stockcode ");
    lSqlBuf.append(",chargetbl c ");
    lSqlBuf.append("WHERE s.chargecode=c.chargecode AND s.stockcode=list.stockcode ");
    
    lSqlBuf.append(lListCond.getCondtionSQL());
    lSqlBuf.append(SICSVConf.getOrderBySQL(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX));
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceCSVFile3(HttpServletResponse response, Connection lConnection, UIRegHacchuPayment hacchuPayment) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT a.stockcode,s.stockname,a.hacchucode,a.p AS paymentprice1,-1*a.m AS paymentprice2");
    lSqlBuf.append(",a.shippmentdate AS paymentdate,a.branchname AS memo");
    lSqlBuf.append(",a.inx,a.individualcode,c.chargename,a.amount,cm.cmdtyname ");
    lSqlBuf.append("FROM hacchupaymentvw a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    if(SIUtil.isNotNull(hacchuPayment.getLastClose())) {
      lSqlBuf.append("AND initdate<").append(SIDBUtil.SQL2Str(hacchuPayment.getLastClose()," "));
    }
    lSqlBuf.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append(",stockmtbl s,cmdtyunittbl cm ");
    lSqlBuf.append("WHERE a.individualcode=cm.individualcode AND a.stockcode=s.stockcode ");
    lSqlBuf.append("AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.shippmentDate>b.closeDate END ");
    lSqlBuf.append("AND a.stockcode=").append(SIDBUtil.SQL2Str(hacchuPayment.getStockCode()," "));
    if(SIUtil.isNotNull(hacchuPayment.getLastClose())) {
      lSqlBuf.append("AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(hacchuPayment.getLastClose()," "));
    }
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("SELECT a.stockcode,s.stockname,null::numeric,CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS paymentprice1");
    lSqlBuf.append(",CASE WHEN a.paymentprice>0 THEN a.paymentprice ELSE 0 END AS paymentprice2 ");
    lSqlBuf.append(",a.paymentdate,a.memo,a.index AS inx,'' AS individualcode,c.chargename,null::numeric AS amount,''::text AS cmdtyname ");
    lSqlBuf.append("FROM hacchupaymenttbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    if(SIUtil.isNotNull(hacchuPayment.getLastClose())) {
      lSqlBuf.append("AND initdate<").append(SIDBUtil.SQL2Str(hacchuPayment.getLastClose()," "));
    }
    lSqlBuf.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.initusercode=c.chargecode ");
    lSqlBuf.append(",stockmtbl s ");
    lSqlBuf.append("WHERE a.stockcode=s.stockcode AND a.enabledflg='1' AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.paymentDate>b.closeDate END ");
    lSqlBuf.append("AND a.stockcode=").append(SIDBUtil.SQL2Str(hacchuPayment.getStockCode()," "));
    if(SIUtil.isNotNull(hacchuPayment.getLastClose())) {
      lSqlBuf.append("AND a.paymentdate<=").append(SIDBUtil.SQL2Str(hacchuPayment.getLastClose()," "));
    }
    
    lSqlBuf.append(SICSVConf.getOrderBySQL(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX));
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HACCHU_PAYMENT_DETAIL_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  

  /**
   * <b>produceCSVFile</b> CSVファイルを作成します。
   * 
   * @param response クライアントまでのresponse
   * @param lConnection DBへのコネクション
   * @param lListCond CSVファイルに出力するデータセット
   * @return なし
   * @throws なし
   */
  private void produceCSVFile4(HttpServletResponse response, Connection lConnection, UIHacchuDetailListCond lListCond) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HACCHU_DETAIL_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT b.hacchucode,c.stockname,b.cmdtyname,b.colorname,f.makername, ");
    lSqlBuf.append("b.branchname,b.amount+b.amountadd AS amount,e.chargename AS charge,hacchuupdatedatetime, ");
    lSqlBuf.append("a.arrivalplandate,CASE a.arrivalplantime WHEN 1 THEN 'AM指定' WHEN 2 THEN 'PM指定' ELSE '指定なし' END AS arrivalplantime,b.individualcode ");
    lSqlBuf.append(",b.shippmentdate,CASE a.status WHEN 0 THEN 'キャンセル' WHEN 2 THEN '返品' WHEN 3 THEN '未承認' ELSE '通常' END AS status,b.comment ");
    lSqlBuf.append("FROM hacchutbl a ");
    lSqlBuf.append("LEFT OUTER JOIN branchtbl d ON a.branchcode=d.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl e ON a.chargecode=e.chargecode,");
    lSqlBuf.append("hacchudetailtbl b,stockmtbl c ");
    lSqlBuf.append(",(SELECT cm.individualcode,mk.makername FROM cmdtyunittbl cm,makertbl mk WHERE cm.makercode=mk.makercode) f ");
    lSqlBuf.append("WHERE a.hacchucode=b.hacchucode AND a.stockcode=c.stockcode AND a.layercode=b.layercode AND a.enabledflg='1' AND b.shipflg='0' ");
    lSqlBuf.append("AND b.individualcode=f.individualcode ");
    lSqlBuf.append(lListCond.getCondtionSQL());
    lSqlBuf.append("ORDER BY b.hacchucode DESC,b.hacchuindividualcode,b.hacchubranchcode");
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HACCHU_DETAIL_LIST_INX));
    if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {} else {
      lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_HACCHU_DETAIL_LIST_INX));
    }
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  public void destroy() {
  }
}