/**
 * Copyright (c) 2006-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.util.Collection;
import java.util.Iterator;

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

import org.apache.log4j.Category;

import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIProcess;
import jp.co.sint.basic.SITax;
import jp.co.sint.beans.mallmgr.UIProcessListCond;
import jp.co.sint.beans.mallmgr.UIProcessCmdtyListCond;
import jp.co.sint.beans.mallmgr.UIStockHistory;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIExcelConf;
import jp.co.sint.database.SIDBAccessException;
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.SIExcelRWrite;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIBGUtil;
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.SIURLParameter;
import jp.co.sint.tools.SIUtil;

/**
 * @version $Id: SIRegPaySrv.java,v 1.0 2006/04/03 Exp $
 * @author  加工管理を処理します。
 * <br>Description:
 * <p>History</p>
 * <p>Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason</p>
 *  ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Hong.M.J        2006/04/03 12:20:42  Original
 */
public class SIRegProcessSrv 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);
    
    UIProcessListCond processList = new UIProcessListCond();
    SIProcess process = new SIProcess();
    UIProcessCmdtyListCond processCmdty = new UIProcessCmdtyListCond();
    
    try {
      String actionName=this.getActionName(urlParam);//画面からのアクション
      String editMode=this.getEditMode(urlParam);
      
      if (SIConfig.SIACTION_LIST.equals(actionName)) {
        if(SIUtil.isNotNull(editMode)&&editMode.equals(SIConfig.SIEDIT_MODE_LIST)){
          forwardKey(request,response,"webshop.jsp.manager.process.list");
        }else{
          processList.init(request,urlParam);
          processList.validate(request,manLogin);
          session.setAttribute(SIConfig.SISESSION_MAN_PROCESS_LIST_NAME,processList);
          forwardKey(request,response,"webshop.jsp.manager.process.list");
        }
      }else if (SIConfig.SIACTION_LIST2.equals(actionName)){
        if(SIUtil.isNotNull(editMode)&&editMode.equals(SIConfig.SIEDIT_MODE_LIST)){
          forwardKey(request,response,"webshop.jsp.manager.process.cmdty.list");
        }else{
          processCmdty.init(request,urlParam);
          processCmdty.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_PROCESS_CMDTY_LIST_NAME,processCmdty);
          forwardKey(request,response,"webshop.jsp.manager.process.cmdty.list");
        }
      }else if(SIConfig.SIACTION_PROCESS.equalsIgnoreCase(actionName)){//商品別加工画面
        processList = new UIProcessListCond();
        processList.initCmdty(request,urlParam,databaseConnection.getConnection());
        processList.setBackUrl("webshop.jsp.manager.process.cmdty.list");
        if(processList.validateIndividual(request,databaseConnection.getConnection())){
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_PROCESS_NAME,processList);
          forwardKey(request,response,"webshop.jsp.manager.cmdty.process");
        }else{
          forwardKey(request,response,"webshop.jsp.manager.process.cmdty.list");
        }
      }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){//レコードの編集画面へ
        process.initDetail(request,urlParam);
        process.reset(databaseConnection.getConnection());
        session.setAttribute(SIConfig.SISESSION_MAN_PROCESS_EDIT_NAME,process);
        forwardKey(request,response,"webshop.jsp.manager.process.edit");
      }else if (SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)){//レコードの編集確認画面へ
        process.init(request,urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_PROCESS_EDIT_NAME,process);
        if(process.validate(request)){
          forwardKey(request,response,"webshop.jsp.manager.process.confirm");
        }else{
          forwardKey(request,response,"webshop.jsp.manager.process.edit");
        }
      }else if(SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){//完了画面から戻る
        process.init(request,urlParam);
        forwardKey(request,response,process.getBackUrl());
      }else if(SIConfig.SIACTION_RETURN.equalsIgnoreCase(actionName)){//編集画面から戻る
        forwardKey(request,response,"webshop.jsp.manager.process.edit");
      }else if (SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)){//修正を登録する
        process = (SIProcess) session.getAttribute(SIConfig.SISESSION_MAN_PROCESS_EDIT_NAME);
        try {
          if(process.getEditModeTxt().equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)){//新規登録時
            this.insertTable(request,databaseConnection.getConnection(),process);
            //SIBGUtil.costCalculationAdd(databaseConnection.getConnection(),process.getCmdtyCode(),process.getIndividualCode(),"0",process.getProcessPrice(),"0",true);
          }else{
            this.updateTable(request,databaseConnection.getConnection(),process);
          }
          try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          forwardKey(request,response,"webshop.jsp.manager.process.res");
        } catch (SIDuplicateKeyException e1) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          e1.printStackTrace();
          SICustomErrors errors=new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request,response,"webshop.jsp.manager.process.edit");
        } catch (SIDBAccessException e1) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          e1.printStackTrace();
          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.process.edit");
        }
      }else if(SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)){//加工追加（新規登録）
        process.initDetail(request,urlParam);
        if(process.validateNew(databaseConnection.getConnection())){
          process.setEditModeTxt(SIConfig.SIEDIT_MODE_INSERT);
          process.reset(databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_PROCESS_EDIT_NAME,process);
          forwardKey(request,response,"webshop.jsp.manager.process.edit");
        }else{
          SICustomErrors errors=new SICustomErrors();
          errors.addError(new SICustomError("manager.message.error.process.nostock"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request,response,"webshop.jsp.manager.cmdty.process");
        }
      }else if(SIConfig.SIACTION_PROCESS_BACK.equalsIgnoreCase(actionName)){//商品別加工画面から戻る
        processList=(UIProcessListCond)session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_PROCESS_NAME);
        if(SIUtil.isNotNull(processList.getBackUrl())){
          forwardKey(request,response,processList.getBackUrl());
        }else{
          forwardKey(request,response,"webshop.jsp.manager.cmdty.edit");
        }
      }else if(SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//加工伝票出力
        processList.init(request,urlParam);
        processList.printinit(request,urlParam);
        processList.validate(request,manLogin);
        this.printProcess(response,databaseConnection.getConnection(),processList,manLogin);
        if (!response.isCommitted()){//発行失敗
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("database.query.notexist","加工伝票データ"));
          forwardKey(request,response,"webshop.jsp.manager.process.list");
        }
      }
    }catch (SQLException e) {
      e.printStackTrace();
    }catch (NamingException e) {
      e.printStackTrace();
    }finally{
      databaseConnection.close();
    }
  }
  
  public void updateTable(HttpServletRequest lRequest, Connection lConnection,SIProcess process) throws SIDuplicateKeyException, SIDBAccessException{
    SIModifyRec lRec = new SIModifyRec(SIConfig.SITABLE_CMDTY_PROCESS_NAME);
    lRec.addCondition("CMDTYCODE",process.getCmdtyCode());//親コード
    lRec.addCondition("INDIVIDUALCODE",process.getIndividualCode()); //在庫コード
    lRec.addCondition("PROCESSNUMBER",process.getProcessNumber()); //加工番号
    lRec.add("PROCESSPRICE",process.getProcessPrice());//加工価格
    lRec.add("BRANCHCODE",process.getBranchCode());//加工支店
    lRec.add("CHARGECODE",process.getChargeCode());//加工担当者
    lRec.add("MAKERNAME",process.getMakerName());//加工先メーカー名
    lRec.add("PAYMENTMETHOD",process.getPaymentMethod());//決済方法
    lRec.add("REMARKS",process.getRemarks());//摘要
    lRec.execute(lConnection);
  }
  
  public void insertTable(HttpServletRequest lRequest, Connection lConnection,SIProcess process) throws SIDuplicateKeyException, SIDBAccessException{
    SIDateTime lDateTime=new SIDateTime();
    UIStockHistory lStock = new UIStockHistory(process.getCmdtyCode(),process.getIndividualCode());
    
    //入庫履歴
    String storeNum = lStock.getNewStoreNumber(lConnection);
    SIInsertRec lRec1=new SIInsertRec("storehistorytbl");
    lRec1.add("cmdtycode",process.getCmdtyCode());
    lRec1.add("individualcode",process.getIndividualCode());
    lRec1.add("storenumber",storeNum);
    lRec1.add("storebranch",process.getBranchCode());
    lRec1.add("storecharge",process.getChargeCode());
    lRec1.add("amount","0");
    lRec1.add("purchaseprice","0");
    lRec1.add("processingexpence",process.getProcessPrice());
    lRec1.add("storetype","2");
    lRec1.add("comment","販売前加工管理より作成");
    lRec1.add("delflg","4");
    lRec1.add("initdatetime",lDateTime.getFullDate());
    lRec1.add("calcdatetime",lDateTime.getFullDateTime());
    
    //出庫履歴
    String shipNum = lStock.getNewShipNumber(lConnection);
    SIInsertRec lRec2=new SIInsertRec("shiphistorytbl");
    lRec2.add("cmdtycode",process.getCmdtyCode());
    lRec2.add("individualcode",process.getIndividualCode());
    lRec2.add("shipnumber",shipNum);
    lRec2.add("shipbranch",process.getBranchCode());
    lRec2.add("shipcharge",process.getChargeCode());
    lRec2.add("amount","0");
    lRec2.add("purchaseprice","0");
    lRec2.add("processingexpence","0");
    lRec2.add("shiptype","2");
    lRec2.add("comment","販売前加工管理より作成");
    lRec2.add("ordercode","");
    lRec2.add("delflg","4");
    lRec2.add("initdatetime",lDateTime.getFullDate());
    lRec2.add("calcdatetime",lDateTime.getFullDateTime());
    
    SIInsertRec lRec3 = new SIInsertRec(SIConfig.SITABLE_CMDTY_PROCESS_NAME);
    lRec3.add("CMDTYCODE",process.getCmdtyCode());//親コード
    lRec3.add("INDIVIDUALCODE",process.getIndividualCode());//在庫コード
    lRec3.add("PROCESSNUMBER",process.getProcessNumber(lConnection));//加工番号
    lRec3.add("STORENUMBER",storeNum);//入庫番号
    lRec3.add("PROCESSPRICE",process.getProcessPrice());//加工価格
    lRec3.add("BRANCHCODE",process.getBranchCode());//加工支店
    lRec3.add("CHARGECODE",process.getChargeCode());//加工担当者
    lRec3.add("MAKERNAME",process.getMakerName());//加工先メーカー名
    lRec3.add("PAYMENTMETHOD",process.getPaymentMethod());//決済方法
    lRec3.add("REMARKS",process.getRemarks());//摘要
    lRec3.add("InitDateTime",lDateTime.getFullDate());//登録日時
    
    lRec1.execute(lConnection);
    lRec2.execute(lConnection);
    lRec3.execute(lConnection);
  }
  
  private void printProcess(HttpServletResponse response,Connection lConnection,UIProcessListCond processList,SILogin manLogin){
    String template = (String) this.getServletContext().getRealPath("/docs/process.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_PROCESS_INX,template);
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_PROCESS_INX),0,false);
    
    Collection searchColl = null;
    try {
      searchColl=processList.getPrintCollection(lConnection,manLogin);
    } catch (SIDBAccessException e1) {
      e1.printStackTrace();
    }
    SIProcess process = null;
    Iterator it ;
    int lineNo = 0;
    int pageNo = 1;
    int loopCount = 0;
    int headerCount = 12;//ヘッダー固定
    int detailCount = 20;//２０行毎
    String pageTotalSum = "0";
    long pageTotalSumLong = 0;
    String tax = "0";
    float taxRate = Float.parseFloat(SITax.getTaxRate(lConnection))/100;
    String totalSum = "0";
    if(searchColl==null||searchColl.size()==0) return;
    it = searchColl.iterator();
    lineNo = headerCount;
    // 特殊のフッターを指定
    lWrite.makeFooter(1,36);
    lWrite.makeFooter(2,38);
    lWrite.makeFooter(3,41);
    lWrite.makeFooter(4,43);
    
    if(processList.getProcessNumberChk() ==null||processList.getProcessNumberChk().length==0){
      while(it.hasNext()){
        process =(SIProcess)it.next();
        //２０行毎明細を出力する
        if(loopCount!=0 && loopCount%detailCount==0){
          lineNo = lineNo +1;
          //改ページ作成
          lWrite.makePageSet(lineNo, lineNo + (lWrite.getEndRowIndex()-headerCount),detailCount);
          pageNo = pageNo + 1;
        }
        lWrite.writeDetailItem(lineNo,0,process.getInitDateTime(),SIExcelConf.FIELD_STRING_TYPE,false);
        lWrite.writeDetailItem(lineNo,1,process.getMakerName(),SIExcelConf.FIELD_STRING_TYPE,false);
        lWrite.writeDetailItem(lineNo,2,process.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE,false);
        lWrite.writeDetailItem(lineNo,3,process.getCmdtyNameTxt(),SIExcelConf.FIELD_STRING_TYPE,false);
        lWrite.writeDetailItem(lineNo,4,process.getRemarks(),SIExcelConf.FIELD_STRING_TYPE,false);
        lWrite.writeDetailItem(lineNo,5,process.getProcessPrice(),SIExcelConf.FIELD_NUMERIC_TYPE,false);
        lineNo = lineNo + 1;
        loopCount = loopCount + 1;
        pageTotalSum = SIUtil.add(pageTotalSum,process.getProcessPrice());
        totalSum = SIUtil.add(totalSum,process.getProcessPrice());
        if(loopCount%detailCount==0){
          //フッターを出力する
          lWrite.writeFooterItem(lineNo+4,5,pageTotalSum,SIExcelConf.FIELD_NUMERIC_TYPE,1);//ページ合計
          if(!pageTotalSum.equals("0")&&taxRate!=0){
            pageTotalSumLong = Long.parseLong(pageTotalSum);
            tax = String.valueOf((Math.floor(pageTotalSumLong * taxRate/(1+taxRate))));
            lWrite.writeFooterItem(lineNo+6,5,tax,SIExcelConf.FIELD_NUMERIC_TYPE,2);//ページ合計消費税
          }else{
            lWrite.writeFooterItem(lineNo+6,5,"0",SIExcelConf.FIELD_NUMERIC_TYPE,2);
          }
          pageTotalSum = "0";
          lineNo = lineNo + 18;
        }
      }
    }else{//チェックをかけた場合
      while(it.hasNext()){
        process =(SIProcess)it.next();
        for(int i=0;i<processList.getProcessNumberChk().length;i++){
          if(process.getProcessNumber().equals(processList.getProcessNumberHid()[Integer.parseInt(processList.getProcessNumberChk()[i])])&&process.getIndividualCode().equals(processList.getIndividualCodeHid()[Integer.parseInt(processList.getProcessNumberChk()[i])])){ 
            //２０行毎明細を出力する
            if(loopCount!=0 && loopCount%detailCount==0){
              lineNo = lineNo +1;
              //改ページ作成
              lWrite.makePageSet(lineNo, lineNo +  (lWrite.getEndRowIndex()-headerCount),detailCount);
              pageNo = pageNo + 1;
            }
            lWrite.writeDetailItem(lineNo,0,process.getInitDateTime(),SIExcelConf.FIELD_STRING_TYPE,false);
            lWrite.writeDetailItem(lineNo,1,process.getMakerName(),SIExcelConf.FIELD_STRING_TYPE,false);
            lWrite.writeDetailItem(lineNo,2,process.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE,false);
            lWrite.writeDetailItem(lineNo,3,process.getCmdtyNameTxt(),SIExcelConf.FIELD_STRING_TYPE,false);
            lWrite.writeDetailItem(lineNo,4,process.getRemarks(),SIExcelConf.FIELD_STRING_TYPE,false);
            lWrite.writeDetailItem(lineNo,5,process.getProcessPrice(),SIExcelConf.FIELD_NUMERIC_TYPE,false);
            lineNo = lineNo + 1;
            loopCount = loopCount + 1;
            pageTotalSum = SIUtil.add(pageTotalSum,process.getProcessPrice());
            totalSum = SIUtil.add(totalSum,process.getProcessPrice());
            if(loopCount%detailCount==0){
              //フッターを出力する
              lWrite.writeFooterItem(lineNo+4,5,pageTotalSum,SIExcelConf.FIELD_NUMERIC_TYPE,1);
              if(!pageTotalSum.equals("0")&&taxRate!=0){
                pageTotalSumLong = Long.parseLong(pageTotalSum);
                tax = String.valueOf((Math.floor(pageTotalSumLong * taxRate/(1+taxRate))));
                lWrite.writeFooterItem(lineNo+6,5,tax,SIExcelConf.FIELD_NUMERIC_TYPE,2);
              }else{
                lWrite.writeFooterItem(lineNo+6,5,"0",SIExcelConf.FIELD_NUMERIC_TYPE,2);
              }
              pageTotalSum = "0";
              lineNo = lineNo + 18;
            }
            break;
          }
        }
      }
    }
    //最終フッターを出力する
    if(loopCount%detailCount!=0){
      lineNo = lineNo + (detailCount-loopCount%detailCount);
      lWrite.writeFooterItem(lineNo+4,5,pageTotalSum,SIExcelConf.FIELD_NUMERIC_TYPE,1);
      if(!pageTotalSum.equals("0")&&taxRate!=0){
        pageTotalSumLong = Long.parseLong(pageTotalSum);
        tax = String.valueOf((Math.floor(pageTotalSumLong * taxRate/(1+taxRate))));
        lWrite.writeFooterItem(lineNo+6,5,tax,SIExcelConf.FIELD_NUMERIC_TYPE,2);
      }else{
        lWrite.writeFooterItem(lineNo+6,5,"0",SIExcelConf.FIELD_NUMERIC_TYPE,2);
      }
      lineNo = lineNo + 18;
    }
    tax = "0";
    if(!totalSum.equals("0")&&taxRate!=0){
      pageTotalSumLong = Long.parseLong(totalSum);
      tax = String.valueOf((Math.floor(pageTotalSumLong * taxRate/(1+taxRate))));
    }
    int footerLine = 41;
    for(int ii=1;ii<pageNo+1;ii++){
      lWrite.writeFooterItem(footerLine,5,totalSum,SIExcelConf.FIELD_NUMERIC_TYPE,3);//総合計
      lWrite.writeFooterItem(footerLine+2,5,tax,SIExcelConf.FIELD_NUMERIC_TYPE,4);
      footerLine = footerLine + 39;
    }
    lWrite.makePage(lineNo);
    try {
      lWrite.close(response);
    } catch (IOException e) {
      //e.printStackTrace();
    }
  }

}