/**
 * Copyright (c) 2003-2006 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.servlet.mallmgr;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Collection;
import java.util.Iterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SITax;
import jp.co.sint.basic.SIOtherOrder;
import jp.co.sint.basic.SIPointRule;
import jp.co.sint.beans.mallmgr.UICmdtyCust;
import jp.co.sint.beans.mallmgr.UICmdtyParent;
import jp.co.sint.beans.mallmgr.UIOtherOrderListCond;
import jp.co.sint.beans.mallmgr.UIRegOtherOrder;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDeleteRec;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

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


public class SIRegOtherOrderSrv extends SIServlet {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);

  /**
   * <b>doUpdate</b>
   * HTTP リクエストの処理
   * @param  request　リクエスト
   * @param  response
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    if (!manLogin.isLogin()){
      forwardKey(request,response,"webshop.jsp.manager.login");
      return;
    }

    HttpSession session = request.getSession(true);//セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();//DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);

    try{
      String actionName = this.getActionName(urlParam);//画面からのアクション
      String editMode = this.getEditMode(urlParam);//DBへの編集モード
      Connection lConnection = databaseConnection.getConnection();
      
      UIOtherOrderListCond otherOrderList = new UIOtherOrderListCond();
      UIRegOtherOrder regOtherOrder = new UIRegOtherOrder();
      UICmdtyCust cmdtyCust = new UICmdtyCust();
      
      if(SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){//一覧と検索などの画面
        //データの取得とデータのチェック
        otherOrderList = new UIOtherOrderListCond(request,urlParam);
        otherOrderList.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_LIST_NAME,otherOrderList);
        forwardKey(request,response,"webshop.jsp.manager.otherorder.list");
      }else if(SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){//その他販売登録画面（商品）
        //データの取得とデータのチェック
        regOtherOrder = new UIRegOtherOrder(request,urlParam);
        regOtherOrder.reset(lConnection);
        regOtherOrder.setEditModeTxt(SIConfig.SIEDIT_MODE_UPDATE);
        session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME,regOtherOrder);
        forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
      }else if(SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)){//その他販売登録画面（新規）
        regOtherOrder.setEditModeTxt(SIConfig.SIEDIT_MODE_INSERT);
        session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME,regOtherOrder);
        forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
      }else if(SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){//一覧へ戻る
        forwardKey(request,response,"webshop.jsp.manager.otherorder.list");
      }else if(SIConfig.SIACTION_BACK2.equalsIgnoreCase(actionName)){//明細へ戻る
        forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
      }else if(SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)){
        cmdtyCust = new UICmdtyCust();
        cmdtyCust.init(request,urlParam);
        cmdtyCust.validate(request,databaseConnection.getConnection());
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_CUST,cmdtyCust);
        forwardKey(request, response, "webshop.jsp.manager.otherorder.cust");
      }else if(SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)){
        cmdtyCust.init(request,urlParam);
        cmdtyCust.decide2(request,(String)urlParam.getParam("custCodeTxt"),databaseConnection.getConnection(),manLogin);
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_CUST, cmdtyCust);
        forwardKey(request, response, "webshop.jsp.manager.otherorder.cust");
      }else if(SIConfig.SIACTION_DELIVERY.equalsIgnoreCase(actionName)){
        regOtherOrder.initInsert(request,urlParam);
        regOtherOrder.setDeliveryAddress(lConnection);
        session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME,regOtherOrder);
        forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
      }else if(SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)){
        regOtherOrder.initInsert(request,urlParam);
        if(regOtherOrder.validateAdd(request)){
          try{
            insertOtherDetail(lConnection,regOtherOrder);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try{lConnection.commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
          }catch(SIDBAccessException e){
            try{lConnection.rollback();}catch(SQLException sqle){}
            SICustomErrors errors=new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
            forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
          }
        }else{
          forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
        }
      }else if(SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)){
        regOtherOrder.initInsert(request,urlParam);
        if(regOtherOrder.validate(request,lConnection)){
          session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME,regOtherOrder);
          forwardKey(request,response,"webshop.jsp.manager.otherorder.confirm");
        }else{
          session.setAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME,regOtherOrder);
          forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
        }
      }else if(SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)){//その他販売ヘッダ作成/その他販売受注作成
        regOtherOrder=(UIRegOtherOrder)session.getAttribute(SIConfig.SISESSION_MAN_OTHERORDER_EDIT_NAME);
        if(SIConfig.SIEDIT_MODE_INSERT.equalsIgnoreCase(editMode)){
          try{
            insertOtherOrder(lConnection,regOtherOrder);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try{lConnection.commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.otherorder.list");
          }catch(SIDBAccessException e){
            try{lConnection.rollback();}catch(SQLException sqle){}
            SICustomErrors errors=new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
            forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
          }
        }else if(SIConfig.SIEDIT_MODE_UPDATE.equalsIgnoreCase(editMode)){
          try{
            insertOrder(lConnection,regOtherOrder);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try{lConnection.commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.otherorder.list");
          }catch(SIDBAccessException e){
            try{lConnection.rollback();}catch(SQLException sqle){}
            SICustomErrors errors=new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
            forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
          }
        }
      }else if(SIConfig.SIACTION_CLEAR.equalsIgnoreCase(actionName)){
        regOtherOrder.initDelete(request,urlParam);
        try{
          delete(lConnection,regOtherOrder);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          try{lConnection.commit();}catch(SQLException sqle){}
          forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
        }catch(SIDBAccessException e){
          try{lConnection.rollback();}catch(SQLException sqle){}
          SICustomErrors errors=new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
          forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
        }
      }else if(SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)){
        regOtherOrder.initDelete(request,urlParam);
        try{
          deleteAll(lConnection,regOtherOrder);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          try{lConnection.commit();}catch(SQLException sqle){}
          forwardKey(request,response,"webshop.jsp.manager.otherorder.list");
        }catch(SIDBAccessException e){
          try{lConnection.rollback();}catch(SQLException sqle){}
          SICustomErrors errors=new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
          forwardKey(request,response,"webshop.jsp.manager.otherorder.edit");
        }
      }
    }catch (Exception e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }

  public void insertOrder(Connection lConnection,UIRegOtherOrder regOtherOrder) throws SIDuplicateKeyException,SIDBAccessException{
    SIDateTime lDate = new SIDateTime();
    String lOrderCode = SIDBUtil.getFirstData(lConnection,SIDBUtil.getNextVal("ORDERTBL_ORDERCD_SEQ"));
    SIInsertRec lRec = new SIInsertRec("ordertbl");//受注ヘッダ
    
    lRec.add("ordercode",lOrderCode);
    lRec.add("custcode",regOtherOrder.getCustCode());
    lRec.add("custname",regOtherOrder.getCustName());
    lRec.add("custpronname",regOtherOrder.getCustPronName());
    lRec.add("email",regOtherOrder.getEMail());
    lRec.add("orderaddressee",regOtherOrder.getOrderAddressee());
    lRec.add("postcode1",regOtherOrder.getPostCode1());
    lRec.add("postcode2",regOtherOrder.getPostCode2());
    lRec.add("address1",regOtherOrder.getAddress1());
    lRec.add("address2",regOtherOrder.getAddress2());
    lRec.add("address3",regOtherOrder.getAddress3());
    lRec.add("companyname",regOtherOrder.getCompanyName());
    lRec.add("storetel",regOtherOrder.getTel1());
    lRec.add("tel",regOtherOrder.getTel2());
    lRec.add("fax",regOtherOrder.getFax());
    lRec.add("paymethodname",regOtherOrder.getPayMethodName());
    lRec.add("job",regOtherOrder.getBusinessForm());
    lRec.add("orderroute",regOtherOrder.getOrderRoute());
    lRec.add("contactmsg",regOtherOrder.getContactMsg());
    if(regOtherOrder.getBranchCode().equalsIgnoreCase("XX")) lRec.add("branchcode","");
    else lRec.add("branchcode",regOtherOrder.getBranchCode());
    if(regOtherOrder.getChargeCode().equalsIgnoreCase("XX")) lRec.add("chargecode","");
    else lRec.add("chargecode",regOtherOrder.getChargeCode());
    lRec.add("initdatetime",lDate.getFullDateTime());
    lRec.add("updatedatetime",lDate.getFullDateTime());
    lRec.add("taxrate",SITax.getTaxRate(lConnection));
    lRec.add("fee","0");
    lRec.add("status","1");
    lRec.add("feetaxflg","2");
    lRec.add("pointenableflg",SIDBUtil.getPointEnableFlg(SIConfig.SIMALL.getMallShopCode(),lConnection));
    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(lConnection);
    
    lRec = new SIInsertRec("orderdeliverytbl");//受注配送先
    lRec.add("ordercode",lOrderCode);
    lRec.add("deliverycode","1");
    lRec.add("deliveryname",SIDBUtil.getFirstData(lConnection,"SELECT deliveryname FROM custaddresstbl WHERE custcode='"+regOtherOrder.getCustCode()+"' AND addresscode='"+regOtherOrder.getAddressCode()+"'"));
    lRec.add("deliveryaddressee",regOtherOrder.getDeliveryAddressee());
    lRec.add("email",SIDBUtil.getFirstData(lConnection,"SELECT email FROM custaddresstbl WHERE custcode='"+regOtherOrder.getCustCode()+"' AND addresscode='"+regOtherOrder.getAddressCode()+"'"));
    lRec.add("postcode1",regOtherOrder.getDeliveryPostCode1());
    lRec.add("postcode2",regOtherOrder.getDeliveryPostCode2());
    lRec.add("address1",regOtherOrder.getDeliveryAddress1());
    lRec.add("address2",regOtherOrder.getDeliveryAddress2());
    lRec.add("address3",regOtherOrder.getDeliveryAddress3());
    lRec.add("companyname",regOtherOrder.getDeliveryCompanyName());
    lRec.add("tel",regOtherOrder.getDeliveryTel());
    lRec.add("fax",SIDBUtil.getFirstData(lConnection,"SELECT fax FROM custaddresstbl WHERE custcode='"+regOtherOrder.getCustCode()+"' AND addresscode='"+regOtherOrder.getAddressCode()+"'"));
    lRec.add("deliverytypename","個別");
    lRec.add("deliverytypecode","0");
    lRec.add("taxrate",SITax.getTaxRate(lConnection));
    lRec.add("deliveryfee","0");
    lRec.add("deliverytaxflg","2");
    lRec.add("custcode",regOtherOrder.getCustCode());
    lRec.add("addresscode",regOtherOrder.getAddressCode());
    
    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(lConnection);
    
    Collection detailColl = regOtherOrder.getCollection(lConnection);
    Iterator detailIte = detailColl.iterator();
    String individualHead ="";
    if(regOtherOrder.getCmdtySection().equals("0")) individualHead="HG";
    else if(regOtherOrder.getCmdtySection().equals("2")) individualHead="AF";
    else individualHead="OT";
    DecimalFormat exFormat = new DecimalFormat("0000");
    String individualCount = SIRegIndividualSrv.getIndividualNext(lConnection,individualHead);
    String totalPrice="0";
    String totalAmount="0";
    
    while(detailIte.hasNext()){
      SIOtherOrder lOtherOrder = (SIOtherOrder)detailIte.next();
      String lIndividualCode = individualHead+"-"+individualCount+"O";

      String lCmdtyCode = UICmdtyParent.getMaxCmdtyCode(lConnection);
      lRec = new SIInsertRec("cmdtymtbl");//商品マスタ（親）
      lRec.add("shopcode",SIConfig.SIMALL.getMallShopCode());
      lRec.add("cmdtycode",lCmdtyCode);
      lRec.add("cmdtyname",lOtherOrder.getCmdtyName());
      lRec.add("colorcode","1");
      lRec.add("makercode","1001");
      lRec.add("taxflg","2");
      lRec.add("deliverytypecode","0");
      lRec.add("cmdtysize","1");
      lRec.add("initdatetime",lDate.getFullDate());
      
      log.debug("insert sql=" + lRec.getSQL());
      lRec.execute(lConnection);
      
      lRec = new SIInsertRec("individualtbl");//商品マスタ（個別）
      lRec.add("shopcode",SIConfig.SIMALL.getMallShopCode());
      lRec.add("cmdtycode",lCmdtyCode);
      lRec.add("individualcode",lIndividualCode);
      lRec.add("usednewflg","1");
      lRec.add("arrivaldate",lDate.getFullDate());
      lRec.add("processingexpence","0");
      lRec.add("salessituationflg","0");
      lRec.add("tenureofuse","999");
      lRec.add("soldoutdispflg","0");
      lRec.add("campaignpriceflg","0");
      lRec.add("inquiryflg","0");
      lRec.add("frontdispflg","0");
      lRec.add("bgpricedispflg","0");
      lRec.add("consigngoodsflg","0");
      lRec.add("commodityexplanation",regOtherOrder.getMotherCmdtyName());
      lRec.add("purchaseprice",lOtherOrder.getPurchasePrice());
      lRec.add("purchaseprice2",lOtherOrder.getPurchasePrice());
      lRec.add("bgprice",lOtherOrder.getPrice());
      lRec.add("recommendationflg","0");
      lRec.add("memberdiscountflg","0");
      lRec.add("endsellflg","0");
      lRec.add("rsrvenableflg","0");
      lRec.add("nostockflg","0");
      lRec.add("amountflg","0");
      lRec.add("wrappingflg","0");
      lRec.add("discountflg","0");
      lRec.add("salesdiscountflg","0");
      lRec.add("spcampaignflg","0");
      lRec.add("remarks",lOtherOrder.getComment());
      lRec.add("initdatetime",lDate.getFullDate());
      
      log.debug("insert sql=" + lRec.getSQL());
      lRec.execute(lConnection);
      
      SIDBUtil.execSQL(lConnection,"INSERT INTO storestocktbl SELECT '0','"+lCmdtyCode+"','"+lIndividualCode+"',branchcode,1,0,'"+lDate.getFullDate()+"',null FROM branchtbl");
      
      lRec = new SIInsertRec("orderdetailtbl");//受注明細
      lRec.add("ordercode",lOrderCode);
      lRec.add("deliverycode","1");
      lRec.add("detailcode",lOtherOrder.getOtherDetailCode());
      lRec.add("shopcode",SIConfig.SIMALL.getMallShopCode());
      lRec.add("shopname",SIConfig.SIMALL.getMallShopName());
      lRec.add("cmdtycode",lCmdtyCode);
      lRec.add("individualcode",lIndividualCode);
      lRec.add("cmdtyname",lOtherOrder.getCmdtyName());
      lRec.add("amount",lOtherOrder.getAmount());
      lRec.add("price",lOtherOrder.getPrice());
      lRec.add("taxflg","2");
      lRec.add("taxrate",SITax.getTaxRate(lConnection));
      lRec.add("cmdtysize","1");
      lRec.add("orgprice",lOtherOrder.getPrice());
      lRec.add("purchaseprice",lOtherOrder.getPurchasePrice());
      lRec.add("processingexpence","0");
      lRec.add("deliveryfee","0");
      
      log.debug("insert sql=" + lRec.getSQL());
      lRec.execute(lConnection);
      
      individualCount=exFormat.format(Integer.parseInt(individualCount)+1);
      totalPrice=SIUtil.add(totalPrice,lOtherOrder.getTotalPrice());
      totalAmount=SIUtil.add(totalAmount,lOtherOrder.getAmount());
    }
    
    SIModifyRec lMod = new SIModifyRec("otherordertbl");//その他販売ヘッダ更新
    lMod.addCondition("otherordercode",regOtherOrder.getOtherOrderCode());
    lMod.add("custcode",regOtherOrder.getCustCode());
    lMod.add("custname",regOtherOrder.getCustName());
    lMod.add("custpronname",regOtherOrder.getCustPronName());
    lMod.add("email",regOtherOrder.getEMail());
    lMod.add("orderaddressee",regOtherOrder.getOrderAddressee());
    lMod.add("postcode1",regOtherOrder.getPostCode1());
    lMod.add("postcode2",regOtherOrder.getPostCode2());
    lMod.add("address1",regOtherOrder.getAddress1());
    lMod.add("address2",regOtherOrder.getAddress2());
    lMod.add("address3",regOtherOrder.getAddress3());
    lMod.add("companyname",regOtherOrder.getCompanyName());
    lMod.add("tel1",regOtherOrder.getTel1());
    lMod.add("tel2",regOtherOrder.getTel2());
    lMod.add("fax",regOtherOrder.getFax());
    lMod.add("paymethodname",regOtherOrder.getPayMethodName());
    lMod.add("businessform",regOtherOrder.getBusinessForm());
    lMod.add("orderroute",regOtherOrder.getOrderRoute());
    lMod.add("contactmsg",regOtherOrder.getContactMsg());
    if(regOtherOrder.getBranchCode().equalsIgnoreCase("XX")) lRec.add("branchcode","");
    else lRec.add("branchcode",regOtherOrder.getBranchCode());
    if(regOtherOrder.getChargeCode().equalsIgnoreCase("XX")) lRec.add("chargecode","");
    else lRec.add("chargecode",regOtherOrder.getChargeCode());
    lMod.add("addresscode",regOtherOrder.getAddressCode());
    lMod.add("deliveryaddressee",regOtherOrder.getDeliveryAddressee());
    lMod.add("deliverypostcode1",regOtherOrder.getDeliveryPostCode1());
    lMod.add("deliverypostcode2",regOtherOrder.getDeliveryPostCode2());
    lMod.add("deliveryaddress1",regOtherOrder.getDeliveryAddress1());
    lMod.add("deliveryaddress2",regOtherOrder.getDeliveryAddress2());
    lMod.add("deliveryaddress3",regOtherOrder.getDeliveryAddress3());
    lMod.add("deliverycompanyname",regOtherOrder.getDeliveryCompanyName());
    lMod.add("deliverytel",regOtherOrder.getDeliveryTel());
    lMod.add("status","1");
    lMod.add("ordercode",lOrderCode);
    lMod.add("mothercmdtyname",regOtherOrder.getMotherCmdtyName());
    lMod.add("cmdtysection",regOtherOrder.getCmdtySection());
    
    log.debug("update sql=" + lMod.getSQL());
    lMod.execute(lConnection);
  }
  
  public void insertOtherOrder(Connection lConnection,UIRegOtherOrder regOtherOrder) throws SIDuplicateKeyException,SIDBAccessException{
    SIInsertRec lRec = new SIInsertRec("otherordertbl");
    lRec.add("otherordercode",SIDBUtil.getFirstData(lConnection,SIDBUtil.getNextVal("OTHERORDERTBL_OTHERORDERCD_SEQ")));
    lRec.add("custcode",regOtherOrder.getCustCode());
    lRec.add("custname",regOtherOrder.getCustName());
    lRec.add("custpronname",regOtherOrder.getCustPronName());
    lRec.add("email",regOtherOrder.getEMail());
    lRec.add("orderaddressee",regOtherOrder.getOrderAddressee());
    lRec.add("postcode1",regOtherOrder.getPostCode1());
    lRec.add("postcode2",regOtherOrder.getPostCode2());
    lRec.add("address1",regOtherOrder.getAddress1());
    lRec.add("address2",regOtherOrder.getAddress2());
    lRec.add("address3",regOtherOrder.getAddress3());
    lRec.add("companyname",regOtherOrder.getCompanyName());
    lRec.add("tel1",regOtherOrder.getTel1());
    lRec.add("tel2",regOtherOrder.getTel2());
    lRec.add("fax",regOtherOrder.getFax());
    lRec.add("paymethodname",regOtherOrder.getPayMethodName());
    lRec.add("businessform",regOtherOrder.getBusinessForm());
    lRec.add("orderroute",regOtherOrder.getOrderRoute());
    lRec.add("contactmsg",regOtherOrder.getContactMsg());
    if(regOtherOrder.getBranchCode().equalsIgnoreCase("XX")) lRec.add("branchcode","");
    else lRec.add("branchcode",regOtherOrder.getBranchCode());
    if(regOtherOrder.getChargeCode().equalsIgnoreCase("XX")) lRec.add("chargecode","");
    else lRec.add("chargecode",regOtherOrder.getChargeCode());
    lRec.add("addresscode",regOtherOrder.getAddressCode());
    lRec.add("deliveryaddressee",regOtherOrder.getDeliveryAddressee());
    lRec.add("deliverypostcode1",regOtherOrder.getDeliveryPostCode1());
    lRec.add("deliverypostcode2",regOtherOrder.getDeliveryPostCode2());
    lRec.add("deliveryaddress1",regOtherOrder.getDeliveryAddress1());
    lRec.add("deliveryaddress2",regOtherOrder.getDeliveryAddress2());
    lRec.add("deliveryaddress3",regOtherOrder.getDeliveryAddress3());
    lRec.add("deliverycompanyname",regOtherOrder.getDeliveryCompanyName());
    lRec.add("deliverytel",regOtherOrder.getDeliveryTel());
    lRec.add("mothercmdtyname",regOtherOrder.getMotherCmdtyName());
    lRec.add("cmdtysection",regOtherOrder.getCmdtySection());
    
    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(lConnection);
  }
  
  public void insertOtherDetail(Connection lConnection,UIRegOtherOrder regOtherOrder) throws SIDuplicateKeyException,SIDBAccessException{
    SIInsertRec lRec = new SIInsertRec("otherdetailtbl");
    lRec.add("otherordercode",regOtherOrder.getOtherOrderCode());
    lRec.add("otherdetailcode",SIDBUtil.getFirstData(lConnection,"SELECT COALESCE(max(otherdetailcode),0)+1 FROM otherdetailtbl WHERE otherordercode='"+regOtherOrder.getOtherOrderCode()+"'"));
    lRec.add("cmdtyname",regOtherOrder.getCmdtyName());
    lRec.add("amount",regOtherOrder.getAmount());
    lRec.add("price",regOtherOrder.getPrice());
    lRec.add("purchaseprice",regOtherOrder.getPurchasePrice());
    lRec.add("comment",regOtherOrder.getComment());
    
    log.debug("insert sql=" + lRec.getSQL());
    lRec.execute(lConnection);
  }
  
  public void delete(Connection lConnection,UIRegOtherOrder regOtherOrder) throws SIDuplicateKeyException,SIDBAccessException{
    SIDeleteRec lRec = new SIDeleteRec("otherdetailtbl");
    lRec.addCondition("otherordercode",regOtherOrder.getOtherOrderCode());
    lRec.addCondition("otherdetailcode",regOtherOrder.getOtherDetailCode());
    
    log.debug("delete sql=" + lRec.getSQL());
    lRec.execute(lConnection);
    
    String renumStr = "UPDATE otherdetailtbl SET otherdetailcode=otherdetailcode-1 WHERE otherdetailcode > "+regOtherOrder.getOtherDetailCode()+" AND otherordercode = "+regOtherOrder.getOtherOrderCode();
    SIDBUtil.execSQL(lConnection,renumStr);
  }
  
  public void deleteAll(Connection lConnection,UIRegOtherOrder regOtherOrder) throws SIDuplicateKeyException,SIDBAccessException{
    SIDeleteRec lRec = new SIDeleteRec("otherordertbl");
    lRec.addCondition("otherordercode",regOtherOrder.getOtherOrderCode());
    
    log.debug("delete sql=" + lRec.getSQL());
    lRec.execute(lConnection);
    
    lRec = new SIDeleteRec("otherdetailtbl");
    lRec.addCondition("otherordercode",regOtherOrder.getOtherOrderCode());
    
    log.debug("delete sql=" + lRec.getSQL());
    lRec.execute(lConnection);
  }
  
  public void destroy() {
  }
}