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

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

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

import jp.co.sint.basic.SILogin;
import jp.co.sint.beans.mallmgr.UIInquiryListCond;
import jp.co.sint.beans.mallmgr.UIRegInquiry;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.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.SIBGUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SICSVWrite;

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


public class SIRegInquirySrv 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への編集モード
      String edit = (String)urlParam.getParam("editMode");
      
      UIInquiryListCond inquiryList = new UIInquiryListCond();
      session.removeAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME);
      session.removeAttribute(SIConfig.SISESSION_MAN_INQUIRY_VIEW_NAME);
      
      if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){//一覧と検索などの画面
        //データの取得とデータのチェック
        inquiryList = new UIInquiryListCond(request,urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_LIST_NAME,inquiryList);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.list");
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)){
        forwardKey(request,response,"webshop.jsp.manager.inquiry.list");
      } else if (SIConfig.SIACTION_CANCEL.equalsIgnoreCase(actionName)){
        UIRegInquiry regInquiry = new UIRegInquiry();
        if (edit.equalsIgnoreCase(SIConfig.SIACTION_NEW)){
          regInquiry.initInsert2(request,urlParam);
          regInquiry.setEditMode(SIConfig.SIACTION_NEW);
        }else{
          regInquiry.initModify(request,urlParam);
          regInquiry.setEditMode(SIConfig.SIACTION_MODIFY);
        }
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
      } else if (SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)){//レコードの編集
        //データの取得とデータのチェック
        UIRegInquiry regInquiry = new UIRegInquiry();
        if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(edit)){
          regInquiry.initModify(request,urlParam);
          regInquiry.setEditMode(edit);
          if (!regInquiry.validateModify(request)){//エラーがあれば
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
          }else{//エラーがなければ
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_VIEW_NAME,regInquiry);
            forwardKey(request,response,"webshop.jsp.manager.inquiry.view");
          }
        }else if(SIConfig.SIACTION_NEW.equalsIgnoreCase(edit)){
          try{
              regInquiry.initInsert(request,databaseConnection.getConnection(),urlParam);
              regInquiry.setEditMode(edit);
          }catch(SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            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.inquiry.edit");
          }
          if (!regInquiry.validateInsert(request)){//エラーがあれば
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
          }else{//エラーがなければ
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_VIEW_NAME,regInquiry);
            forwardKey(request,response,"webshop.jsp.manager.inquiry.view");
          }
        }
      }else if (SIConfig.SIACTION_NEW.equalsIgnoreCase(actionName)){//レコードの新規作成
        UIRegInquiry regInquiry = new UIRegInquiry();
        regInquiry.setEditMode(SIConfig.SIACTION_NEW);
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
      }else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)){//データのアップデート
        //データの取得とデータのチェック
        UIRegInquiry regInquiry = new UIRegInquiry();
        if (SIConfig.SIACTION_NEW.equalsIgnoreCase(edit)){
        regInquiry.initInsert2(request,urlParam);
        regInquiry.setEditMode(edit);
          try {
            //データレコードの作製
            insertTableData(databaseConnection.getConnection(),regInquiry);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
                                  SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            updateNumberingTbl(databaseConnection.getConnection());
            try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
            forwardKey(request,response,"webshop.jsp.manager.inquiry.list");
          }catch (SIDuplicateKeyException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.insert.duplicate2","探してほしいマスタ"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
            forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            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.inquiry.edit");
          }
        }else if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(edit)){
          regInquiry.initModify(request,urlParam);
          regInquiry.setEditMode(edit);
          try {
            //レコードの修正
            updateTableData(databaseConnection.getConnection(),regInquiry);
            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.inquiry.list");
          }catch (SIDBAccessException e){
            try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
            session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
            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.inquiry.edit");
          }
        }
      }else if (SIConfig.SIACTION_ZIPDIC.equalsIgnoreCase(actionName)){//郵便番号から県名と市区町村名を検索
        UIRegInquiry regInquiry = new UIRegInquiry();
        regInquiry.initModify(request,urlParam);
        regInquiry.setEditMode(edit);
        if(SIConfig.SIZIP!=null){
          String lEncode=regInquiry.getEncode();//既存のコードの保存
          regInquiry.setEncode(SIConfig.SIENCODE_NONE);
          String lResult=SIConfig.SIZIP.getPrefectureName(regInquiry.getRegPostCode1(),regInquiry.getRegPostCode2());//県名の検索
          if (SIUtil.isNotNull(lResult))regInquiry.setRegAddress1(lResult);//県名の設定
          lResult=SIConfig.SIZIP.getAddressName(regInquiry.getRegPostCode1(),regInquiry.getRegPostCode2());//市区町村名の検索
          if (SIUtil.isNotNull(lResult))regInquiry.setRegAddress2(lResult);//市区町村名の設定
          regInquiry.setEncode(lEncode);//既存のコードの回復
        }
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
      }else if (SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)){//顧客コードから顧客データを検索
        UIRegInquiry regInquiry = new UIRegInquiry();
        regInquiry.initModify(request,urlParam);
        regInquiry.setEditMode(edit);
        if(regInquiry.validateSearch(request, databaseConnection.getConnection())){
          regInquiry.custSearch(databaseConnection.getConnection());
        }
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
      }else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//CSV出力
        inquiryList = (UIInquiryListCond)session.getAttribute(SIConfig.SISESSION_MAN_INQUIRY_LIST_NAME);
        if (inquiryList==null) inquiryList = new UIInquiryListCond();
        this.produceCSVFile(response,databaseConnection.getConnection(),inquiryList);
        if (!response.isCommitted()){
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("database.query.notexist","CSVデータ"));
          forwardKey(request,response,"webshop.jsp.manager.inquiry.list");
        }
      }else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)){//詳細画面
        UIRegInquiry regInquiry = new UIRegInquiry();
        if (edit.equalsIgnoreCase(SIConfig.SIACTION_NEW)) regInquiry.setEditMode(SIConfig.SIACTION_NEW);
        else regInquiry.setEditMode(SIConfig.SIACTION_MODIFY);
        try{
          regInquiry.init(request,urlParam);
          regInquiry.initDetail(urlParam,databaseConnection.getConnection());
        }catch(SIDBAccessException e){
          try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){}
          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.inquiry.list");
        }
        session.setAttribute(SIConfig.SISESSION_MAN_INQUIRY_EDIT_NAME,regInquiry);
        forwardKey(request,response,"webshop.jsp.manager.inquiry.edit");
      }
    }catch (SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }
  
  /**
   * <b>insertTableData</b>
   * データベースにレコードを作成します。
   * @param lConnection DBへのコネクション
   * @param regInquiry 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableData(Connection lConnection,UIRegInquiry regInquiry) throws SIDuplicateKeyException,SIDBAccessException{
    SIDateTime lDateTime = new SIDateTime();
    
    SIInsertRec lRec = new SIInsertRec("LookForTbl");
    lRec.add("ManagementNumber",regInquiry.getRegManagementNumber());
    lRec.add("HopeProduct",regInquiry.getRegHopeProduct());
    lRec.add("HopeCommodityName",regInquiry.getRegHopeCommodityName());
    lRec.add("HopeAmount",regInquiry.getRegHopeAmount());
    lRec.add("HopeDeliveryDate",regInquiry.getRegHopeDeliveryDate());
    lRec.add("MethodForHopeFlg",regInquiry.getRegMethodForHope());
    lRec.add("CustName",regInquiry.getRegCustName());
    lRec.add("CustPronName",regInquiry.getRegCustPronName());
    lRec.add("EMail",regInquiry.getRegEMail());
    lRec.add("PostCode1",regInquiry.getRegPostCode1());
    lRec.add("PostCode2",regInquiry.getRegPostCode2());
    lRec.add("Address1",regInquiry.getRegAddress1());
    lRec.add("Address2",regInquiry.getRegAddress2());
    lRec.add("Address3",regInquiry.getRegAddress3());
    lRec.add("StoreTel",regInquiry.getRegStoreTel());
    lRec.add("Tel",regInquiry.getRegTel());
    lRec.add("Fax",regInquiry.getRegFax());
    lRec.add("SalonName",regInquiry.getRegSalonName());
    lRec.add("InquiryFormFlg",regInquiry.getRegInquiryFormFlg());
    lRec.add("ClientComment",regInquiry.getRegClientComment());
    lRec.add("CorrespondingFlg",regInquiry.getRegCorrespondingFlg());
    lRec.add("BranchCode",regInquiry.getRegBranchCode());
    lRec.add("BranchName",SIBGUtil.getDBParameter(lConnection,"BranchTbl","BranchName","BranchCode",regInquiry.getRegBranchCode()));
    lRec.add("ChargeCode",regInquiry.getRegChargeCode());
    lRec.add("ChargeName",SIBGUtil.getDBParameter(lConnection,"ChargeTbl","ChargeName","ChargeCode",regInquiry.getRegChargeCode()));
    lRec.add("PresentationCommodity",regInquiry.getRegPresentationCommodity());
    lRec.add("PresentationCommodityAmount",regInquiry.getRegPresentationCommodityAmount());
    lRec.add("PresentationCommodityFlg",regInquiry.getRegPresentationCommodityFlg());
    lRec.add("CorrespondenceHistory",regInquiry.getRegCorrespondenceHistory());
    lRec.add("Remarks",regInquiry.getRegRemarks());
    lRec.add("InitDateTime",lDateTime.getFullDateTime());
    
    //データの更新
    lRec.execute(lConnection);
  }
  
  /**
   * <b>updateTableData</b>
   * データベースにレコードを修正します。
   * @param lConnection DBへのコネクション
   * @param regInquiry 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void updateTableData(Connection lConnection,UIRegInquiry regInquiry)throws SIDBAccessException{
    SIDateTime lDateTime = new SIDateTime();
    
    SIModifyRec lRec = new SIModifyRec("LookForTbl");
    lRec.addCondition("ManagementNumber",regInquiry.getRegManagementNumber());
    lRec.add("HopeProduct",regInquiry.getRegHopeProduct());
    lRec.add("HopeCommodityName",regInquiry.getRegHopeCommodityName());
    lRec.add("HopeAmount",regInquiry.getRegHopeAmount());
    lRec.add("HopeDeliveryDate",regInquiry.getRegHopeDeliveryDate());
    lRec.add("MethodForHopeFlg",regInquiry.getRegMethodForHope());
    lRec.add("CustName",regInquiry.getRegCustName());
    lRec.add("CustPronName",regInquiry.getRegCustPronName());
    lRec.add("EMail",regInquiry.getRegEMail());
    lRec.add("PostCode1",regInquiry.getRegPostCode1());
    lRec.add("PostCode2",regInquiry.getRegPostCode2());
    lRec.add("Address1",regInquiry.getRegAddress1());
    lRec.add("Address2",regInquiry.getRegAddress2());
    lRec.add("Address3",regInquiry.getRegAddress3());
    lRec.add("StoreTel",regInquiry.getRegStoreTel());
    lRec.add("Tel",regInquiry.getRegTel());
    lRec.add("Fax",regInquiry.getRegFax());
    lRec.add("SalonName",regInquiry.getRegSalonName());
    lRec.add("InquiryFormFlg",regInquiry.getRegInquiryFormFlg());
    lRec.add("ClientComment",regInquiry.getRegClientComment());
    lRec.add("CorrespondingFlg",regInquiry.getRegCorrespondingFlg());
    lRec.add("BranchCode",regInquiry.getRegBranchCode());
    lRec.add("BranchName",SIBGUtil.getDBParameter(lConnection,"BranchTbl","BranchName","BranchCode",regInquiry.getRegBranchCode()));
    lRec.add("ChargeCode",regInquiry.getRegChargeCode());
    lRec.add("ChargeName",SIBGUtil.getDBParameter(lConnection,"ChargeTbl","ChargeName","ChargeCode",regInquiry.getRegChargeCode()));
    lRec.add("PresentationCommodity",regInquiry.getRegPresentationCommodity());
    lRec.add("PresentationCommodityAmount",regInquiry.getRegPresentationCommodityAmount());
    lRec.add("PresentationCommodityFlg",regInquiry.getRegPresentationCommodityFlg());
    lRec.add("LatestDate",lDateTime.getFullDateTime());
    lRec.add("CorrespondenceHistory",regInquiry.getRegCorrespondenceHistory());
    lRec.add("Remarks",regInquiry.getRegRemarks());
    
    try {
      lRec.execute(lConnection);
    }catch(SIDuplicateKeyException e){
      e.printStackTrace();
      throw new SIDBAccessException();
    }
  }
  
  public static void updateNumberingTbl(Connection lConnection)throws SIDBAccessException{
    SIDateTime lDateTime = new SIDateTime();
    int lNowYear =lDateTime.getYear();
    String year = lDateTime.getYearStr();
    String lNow = lDateTime.getFullDate();
    
    StringBuffer lSqlBuf=new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
    lSqlBuf.append("WHERE Functional_Type='0' AND Year="+ lNowYear +"");
    
    try{
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf.toString());
      int sbno = 0;
      if (SIUtil.isNull(managementSbNo)) sbno = 2;
      else sbno = Integer.parseInt(managementSbNo) + 1;
    
      SIModifyRec lRec = new SIModifyRec("NumberingTbl");
      lRec.addCondition("Functional_type","0");
      lRec.addCondition("year",year);
      lRec.add("management_sbno",sbno);
      lRec.add("initdatetime",lNow);
    
      lRec.execute(lConnection);
    }catch(SIDuplicateKeyException e){
      e.printStackTrace();
      throw new SIDBAccessException();
    }
  }
  
  private void produceCSVFile(HttpServletResponse response,Connection lConnection,UIInquiryListCond inquiryList){
    //CSVファイル名の作成
    String lCsvFileName=SICSVConf.getCsvFileName(SICSVConf.SICSV_INQUIRY_LIST_INX);
    SICSVWrite lCsv=new SICSVWrite(lCsvFileName);
    
    //SQL文の作成
    StringBuffer lSqlBuf=new StringBuffer("SELECT ManagementNumber,InitDateTime");
    lSqlBuf.append(" ,CASE CorrespondingFlg WHEN 0 THEN '未対応' WHEN 1 THEN '初回対応'");
    lSqlBuf.append(" WHEN 2 THEN '対応継続中' WHEN 3 THEN '商品提案中'");
    lSqlBuf.append(" WHEN 4 THEN '商品検索中' ELSE '対応終了' END AS CorrespondingFlg");
    lSqlBuf.append(" ,BranchName,ChargeName,SalonName,CustName,CtgryName,HopeCommodityName");
    lSqlBuf.append(" ,HopeAmount,CASE WHEN substring(HopeDeliveryDate::text,9,11)='01' THEN lpad('上旬',10,HopeDeliveryDate::text)");
    lSqlBuf.append(" WHEN substring(HopeDeliveryDate::text,9,11)='10' THEN lpad('中旬',10,HopeDeliveryDate::text)");
    lSqlBuf.append(" WHEN substring(HopeDeliveryDate::text,9,11)='20' THEN lpad('下旬',10,HopeDeliveryDate::text) END AS HopeDeliveryDate");
    lSqlBuf.append(",StoreTel,Address1");
    lSqlBuf.append(" FROM LookForTbl AS a LEFT OUTER JOIN CtgryMTbl");
    lSqlBuf.append(" ON HopeProduct=CtgryCode");
    lSqlBuf.append(" WHERE 1=0 ");
    if (SIUtil.isNotNull(inquiryList.getConditionSQL())){
      lSqlBuf.append("OR( 1=1 ").append(inquiryList.getConditionSQL()).append(" )");
    }
    lSqlBuf.append(" ORDER BY ManagementNumber DESC");
    
    //SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    //タイトルとFieldの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_INQUIRY_LIST_INX));
    //CSVファイルの出力
    lCsv.execute(lConnection,response);
  }
  
  public void destroy() {}
}