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 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.SIPDFUtil;
import jp.co.sint.beans.mallmgr.UIPriceCardCmdtyListCond;
import jp.co.sint.beans.mallmgr.UIPriceCardListCond;
import jp.co.sint.beans.mallmgr.UIRegPriceCard;
import jp.co.sint.config.SIConfig;
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.SIDeleteRec;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIBGPdfTool;
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.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 SIPriceCardSrv 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への編集モード
      
      UIPriceCardListCond cardList1 = new UIPriceCardListCond();
      UIPriceCardCmdtyListCond cardList2 = new UIPriceCardCmdtyListCond();
      UIRegPriceCard cardEdit = new UIRegPriceCard();
      
      if(this.getServletPath(request).equals("/mallmgr/PriceCard")){
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){
          cardList1.init(request, urlParam);
          cardList1.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_PRICECARD_LIST1_NAME,cardList1);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.list");
        }else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
          session.removeAttribute(SIConfig.SISESSION_MAN_PRICECARD_LIST2_NAME);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.list");
        }else if (SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)){
          session.removeAttribute(SIConfig.SISESSION_MAN_PRICECARD_EDIT_NAME);
          session.removeAttribute(SIConfig.SISESSION_MAN_PRICECARD_LIST2_NAME);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.edit");
        }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){
          cardEdit = new UIRegPriceCard((String)urlParam.getParam("cardNumber"));
          session.setAttribute(SIConfig.SISESSION_MAN_PRICECARD_EDIT_NAME,cardEdit);
          session.removeAttribute(SIConfig.SISESSION_MAN_PRICECARD_LIST2_NAME);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.edit");
        }else if(SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)){
          cardList2.init(request, urlParam);
          cardList2.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_PRICECARD_LIST2_NAME,cardList2);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.edit");
        }else if(SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)){
          cardEdit.initAdd(request, urlParam);
          if (cardEdit.validateAdd(request, databaseConnection.getConnection())) {
            try {
              addItem(databaseConnection.getConnection(), cardEdit,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_PRICECARD_EDIT_NAME,cardEdit);
              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_PRICECARD_EDIT_NAME,cardEdit);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.edit");
        }else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)){
          cardEdit.initDelete(request, urlParam);
          try {
            deleteItem(databaseConnection.getConnection(),cardEdit);
            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_PRICECARD_EDIT_NAME,cardEdit);
            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_PRICECARD_EDIT_NAME,cardEdit);
          forwardKey(request,response,"webshop.jsp.manager.pricecard.edit");
        }else if(SIConfig.SIACTION_PDF.equalsIgnoreCase(actionName)){//PDF出力
          try {
            producePriceCardPDFFile(response, databaseConnection.getConnection(), (String)urlParam.getParam("cardNumber"));
          } catch (SIDBAccessException e1) {
            e1.printStackTrace();
          } catch (DocumentException e2) {
            e2.printStackTrace();
          }
          if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.pricecard.list"); 
        }else if(SIConfig.SIACTION_PDF2.equalsIgnoreCase(actionName)){//PDF出力
          try {
            producePriceCardPDFFile(response, databaseConnection.getConnection(), (String)urlParam.getParam("cardNumber"));
          } catch (SIDBAccessException e1) {
            e1.printStackTrace();
          } catch (DocumentException e2) {
            e2.printStackTrace();
          }
          if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.pricecard.edit"); 
        }
      }
    }catch (Exception e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }
  
  public void addItem(Connection lConnection,UIRegPriceCard regCard,String userCode) throws SIDBAccessException,SIDuplicateKeyException{
    if (SIUtil.isNull(regCard.getCardNumber())) {
      regCard.setNewCardNumber(lConnection);
      String charge = SIDBUtil.getFirstData(lConnection, "SELECT chargecode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(userCode));
      SIInsertRec lRec1 = new SIInsertRec("priceCardTbl");
      lRec1.add("cardNumber", regCard.getCardNumber());
      lRec1.add("initDate", new SIDateTime().getFullDate());
      if (SIUtil.isNotNull(charge)) {
        lRec1.add("chargeCode", charge);
      }
      lRec1.execute(lConnection);
    }
    
    String branchNumber = SIDBUtil.getFirstData(lConnection, "SELECT MAX(branchNumber)+1 FROM priceCardDetailTbl WHERE cardNumber="+SIDBUtil.SQL2Str(regCard.getCardNumber()));
    
    SIInsertRec lRec2 = new SIInsertRec("priceCardDetailTbl");
    lRec2.add("cardNumber", regCard.getCardNumber());
    lRec2.add("individualCode", regCard.getIndividualCode());
    if (SIUtil.isNotNull(branchNumber)) {
      lRec2.add("branchNumber", branchNumber);
    } else {
      lRec2.add("branchNumber", "1");
    }
    lRec2.execute(lConnection);
  }
  
  public void deleteItem(Connection lConnection,UIRegPriceCard regCard) throws SIDBAccessException,SIDuplicateKeyException{
    if (SIUtil.isNull(regCard.getCardNumber())) return;
    
    SIDeleteRec lRec = new SIDeleteRec("priceCardDetailTbl");
    lRec.addCondition("cardNumber", regCard.getCardNumber());
    lRec.addCondition("branchNumber", regCard.getBranchNumber());
    lRec.execute(lConnection);
    
    SIDBUtil.execSQL(lConnection, "CLUSTER priceCardDetailTbl_pkey ON priceCardDetailTbl");
    
    StringBuffer str = new StringBuffer();
    str.append("UPDATE priceCardDetailTbl ");
    str.append("SET branchNumber=branchNumber-1 ");
    str.append("WHERE cardNumber=").append(SIDBUtil.SQL2Str(regCard.getCardNumber()," "));
    str.append("AND branchNumber>").append(SIDBUtil.SQL2Str(regCard.getBranchNumber()," "));
    SIDBUtil.execSQL(lConnection, str.toString());
  }
  
  public void producePriceCardPDFFile(HttpServletResponse response, Connection conn, String cardNumber) throws IOException, DocumentException, SIDBAccessException {
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    File lOrderTempFile = new File("");
    
    //テンプレート
    String[] template = new String[4];
    template[0] = (String) this.getServletContext().getRealPath("/docs/pricecardUsed.pdf");
    template[1] = (String) this.getServletContext().getRealPath("/docs/pricecardBGUsed.pdf");
    template[2] = (String) this.getServletContext().getRealPath("/docs/pricecardNew.pdf");
    template[3] = (String) this.getServletContext().getRealPath("/docs/pricecardOutlet.pdf");
    
    //座標設定
    int[] individualVerticalAxis = {765,765,575,575,385,385,195,195};
    int[] individualHorizontalAxis =  {45,320,45,320,45,320,45,320};
    int[] makerVerticalAxis =  {780,780,590,590,400,400,210,210};
    int[] makerHorizontalAxis = {220,495,220,495,220,495,220,495};
    int[] cmdtynameVerticalAxis = {742,742,552,552,362,362,172,172};
    int[] cmdtynameHorizontalAxis = {45,320,45,320,45,320,45,320};
    int[] newfixedpriceVerticalAxis = {717,717,527,527,337,337,147,147};
    int[] newfixedpriceHorizontalAxis = {62,337,62,337,62,337,62,337};
    int[] bgpriceVerticalAxis = {677,677,487,487,297,297,107,107};
    int[] bgpriceHorizontalAxis = {84,359,84,359,84,359,84,359};
    int[] deliveryfeeVerticalAxis = {647,647,457,457,267,267,77,77};
    int[] deliveryfeeHorizontalAxis = {240,515,240,515,240,515,240,515};
    
    try {
      lOrderTempFile = File.createTempFile("pricecard_" , ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    String lFileName = lOrderTempFile.getAbsolutePath();
    SIBGPdfTool priceCardPdf = new SIBGPdfTool();
    
    //各項80件（10ページ）に制限する
    
    StringBuffer sql1 = new StringBuffer();
    sql1.append("SELECT u.individualcode,u.cmdtyname,m.makername,c.colorname,s.stockname");
    sql1.append(",CASE WHEN i.guaranteedterm IS NULL OR i.guaranteedterm = 0 THEN '' ");
    sql1.append("WHEN i.guaranteedterm < 12 THEN i.guaranteedterm % 12 ||'ヶ月' ");
    sql1.append("WHEN i.guaranteedterm % 12 = 0 THEN trunc(i.guaranteedterm / 12) ||'年' ");
    sql1.append("ELSE trunc(i.guaranteedterm / 12) ||'年'||i.guaranteedterm % 12 ||'ヶ月' END AS guaranteedterm ");
    sql1.append(",CASE WHEN u.deliverytypecode = '1' THEN '通常' ");
    sql1.append(" WHEN p.dispcarriagefree = '1' THEN '無料' ");
    sql1.append(" WHEN u.cmdtysize = '1' THEN '無料' WHEN u.cmdtysize = '7' THEN '無料' ");
    sql1.append(" WHEN u.cmdtysize = '2' THEN 'B' WHEN u.cmdtysize = '3' THEN 'C' ");
    sql1.append(" WHEN u.cmdtysize = '4' THEN 'D' WHEN u.cmdtysize = '5' THEN 'E' ELSE 'F' END AS cmdtysize ");
    sql1.append(",i.bgpricewithouttax,i.purchaseprice,i.processingexpence,i.purchaseprice2,u.newfixedprice");
    sql1.append(",CASE u.amountflg WHEN 0 THEN 'しない' ELSE 'する' END AS amountflg ");
    sql1.append(",CASE u.frontdispflg WHEN 0 THEN '非表示' ELSE '表示' END AS frontdispflg ");
    sql1.append(",CASE u.disableflg WHEN 0 THEN '通常' WHEN 1 THEN '廃盤予定' WHEN 2 THEN '販売保留' ELSE '廃盤' END AS disableflg ");
    sql1.append(",u.cmdtycompositionflg,p.dispcarriagefree ");
    sql1.append("FROM makertbl m,colortbl c,individualtbl i,pricecarddetailtbl d");
    sql1.append(",cmdtyunittbl u LEFT OUTER JOIN stockmtbl s ON u.stockcode=s.stockcode ");
    sql1.append("LEFT OUTER JOIN cmdtycompositionmtbl p ON u.individualcode=p.individualcode ");
    sql1.append("WHERE u.individualcode=i.individualcode AND u.colorcode=c.colorcode ");
    sql1.append("AND u.makercode=m.makercode AND d.individualcode=u.individualcode ");
    sql1.append("AND d.cardNumber=").append(SIDBUtil.SQL2Str(cardNumber," "));
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    try {
      lStatement = conn.createStatement();
      int index = 0;
      boolean firstFlg = true;
      StringBuffer sql2 = new StringBuffer();
      
      for (int i=0;i<4;i++) {
        if (firstFlg) {
          priceCardPdf.initialize(template[i], lOrderTempFile.getPath());
          priceCardPdf.fontSelect("Goth");
        }
        
        
        sql2 = new StringBuffer(sql1.toString());
        
        if (i==0) sql2.append("AND u.usednewflg='0' ");
        else if (i==1) sql2.append("AND u.usednewflg='3' ");
        else if (i==2) sql2.append("AND u.usednewflg IN ('1','6','7') ");
        else if (i==3) sql2.append("AND u.usednewflg IN ('2','4') ");
        sql2.append("ORDER BY d.branchnumber ");
        
        if (!firstFlg) index = 8;
        lResultSet = lStatement.executeQuery(sql2.toString());
        while(lResultSet.next()) {
          if (index==8) {
            priceCardPdf.addPage(template[i]);
            index=0;
          }
          
          priceCardPdf.setFontSize(9);//在庫コード、ブランド、保証、送料
          priceCardPdf.writeRtoL(lResultSet.getString("makerName"), makerHorizontalAxis[index], makerVerticalAxis[index]);
          priceCardPdf.write(lResultSet.getString("individualCode"), individualHorizontalAxis[index], individualVerticalAxis[index]);
          if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))&&("0".equals(lResultSet.getString("cmdtycompositionflg"))||"1".equalsIgnoreCase(lResultSet.getString("dispcarriagefree")))) {
            priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証 送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
          } else if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))){
            priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証", deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
          } else if ("0".equals(lResultSet.getString("cmdtycompositionflg"))||"1".equalsIgnoreCase(lResultSet.getString("dispcarriagefree"))){
            priceCardPdf.writeRtoL("送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
          }
          
          priceCardPdf.setFontSize(10);//M価
          if (SIUtil.isNotNull(lResultSet.getString("newfixedprice"))&&!"0".equals(lResultSet.getString("newfixedprice"))) {
            priceCardPdf.write("￥"+SIStringUtil.numberFormat(lResultSet.getString("newfixedprice")), newfixedpriceHorizontalAxis[index], newfixedpriceVerticalAxis[index]);
          }
          
          String cmdtyName = lResultSet.getString("cmdtyname");
          if (cmdtyName.getBytes().length > 40 || cmdtyName.length() >= 30) {
            priceCardPdf.setFontSize(8);//商品名
            priceCardPdf.write(cmdtyName, cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
            log.debug("PriceCardCmdty[8]:"+cmdtyName);
          } else if (cmdtyName.getBytes().length > 30 || cmdtyName.length() >= 24) {
            priceCardPdf.setFontSize(12);//商品名
            priceCardPdf.write(cmdtyName, cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
            log.debug("PriceCardCmdty[12]:"+cmdtyName);
          } else {
            priceCardPdf.setFontSize(15);//商品名
            priceCardPdf.write(cmdtyName, cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
            log.debug("PriceCardCmdty[15]:"+cmdtyName);
          }
          
          priceCardPdf.setFontSize(20);//BG卸価（￥）
          priceCardPdf.write("￥", bgpriceHorizontalAxis[index]-17, bgpriceVerticalAxis[index]);
          priceCardPdf.setFontSize(40);//BG卸価
          priceCardPdf.write(SIStringUtil.numberFormat(lResultSet.getString("bgpricewithouttax")), bgpriceHorizontalAxis[index], bgpriceVerticalAxis[index]);
          
          int pointlength = ((lResultSet.getString("bgpricewithouttax").length() - 1) / 3) * 8;
          
          priceCardPdf.setFontSize(15);//（税別）
          priceCardPdf.write("(税別)", bgpriceHorizontalAxis[index]+(lResultSet.getString("bgpricewithouttax")).length()*20+15+pointlength, bgpriceVerticalAxis[index]);
          
          index++;
          firstFlg=false;
        }
      }
    } catch (Exception e){
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    priceCardPdf.close();
    
    SIPDFUtil lPdfUtil = new SIPDFUtil();
    String lOutputFileName = "pricecard_" + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
    lPdfUtil.execute(response, lFileName, lOutputFileName);
    // 臨時ファイルの削除
    lOrderTempFile.delete();
  }
  
  public void destroy() {
  }
}