package jp.co.sint.servlet.mallmgr;

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

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.UIKeepDetailCond;
import jp.co.sint.beans.mallmgr.UIKeepListCond;
import jp.co.sint.beans.mallmgr.UIRegNewKeep;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
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.SIModifyRec;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.mail.SISendMail;
import jp.co.sint.tools.SICSVWrite;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIFatalException;

import org.apache.log4j.Category;

/**
 * @version $Id: SIRegReserveSrv.java,v 1.0 2004/01/29 Exp $
 * @author Jinwang Chen <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>
 * Jinwang Chen 2004/01/29 12:21:31 Original
 */
public class SIKeepListSrv 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);
    // SIRegMngAccesslogSrv siRegMngAccesslogSrv = new SIRegMngAccesslogSrv();
    if (!manLogin.isLogin()) {
      forwardKey(request, response, "webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session = request.getSession(true);// セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成
    
    try {
      SIURLParameter urlParam = new SIURLParameter(request);
      String actionName = this.getActionName(urlParam);// 画面からのアクション
      
      if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 予約リスト
        // 一覧と検索などの画面のレコードの表示
        UIKeepListCond listCond = new UIKeepListCond();
        // データの取得
        listCond.init(request, urlParam);
        // データのチェック
        listCond.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_KEEPORDER_LIST_NAME, listCond);
        forwardKey(request, response, "webshop.jsp.manager.keep.list");
      } else if (SIConfig.SIACTION_LIST2.equalsIgnoreCase(actionName)) {// 商品マスタから遷移
        // 一覧と検索などの画面のレコードの表示
        UIKeepListCond listCond = new UIKeepListCond();
        // データの取得
        listCond.setIndividualCodeTxt((String) urlParam.getParam("individualCode"));
        listCond.setKeepDate("3");
        // データのチェック
        listCond.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, "42");
        session.setAttribute(SIConfig.SISESSION_MAN_KEEPORDER_LIST_NAME, listCond);
        forwardKey(request, response, "webshop.jsp.manager.keep.list");
      } else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {// CSVファイルへの出力
        UIKeepListCond listCond = new UIKeepListCond();
        // 取得するCSVデータの取得
        listCond = (UIKeepListCond) session.getAttribute(SIConfig.SISESSION_MAN_KEEPORDER_LIST_NAME);
        if (listCond == null) listCond = new UIKeepListCond();
        this.produceCSVFile(response, databaseConnection.getConnection(), listCond);
        if (!response.isCommitted()) {
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        }
      } else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)) {// レコードの削除
        UIKeepListCond listCond = new UIKeepListCond();
        // 削除するPKデータの取得
        listCond = new UIKeepListCond();
        listCond.initDelete(request, urlParam);
        if (listCond.validateDelete(request)) {// 7.3.0 PI-NES0501
          try {
            boolean sendMailFlg = false;
            String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(manLogin.getUserCode()));
            for (int ii = 0; ii < (listCond.getCheck().length); ii++) {
              sendMailFlg = sendMailFlg | SIBGUtil.deleteKeep(databaseConnection.getConnection(), listCond.getCheck()[ii], fromMail);
            }
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException e) {}
            if (sendMailFlg) request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete.mail"));
            else request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete.mail2"));
            forwardKey(request, response, "webshop.jsp.manager.keep.list");
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            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.keep.list");
          }
        } else {
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        }
      } else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)) {// キープ詳細画面
        // 一覧と検索などの画面のレコードの表示
        UIKeepDetailCond listCond = new UIKeepDetailCond();
        // データの取得
        listCond = new UIKeepDetailCond(request, urlParam);
        // データのチェック
        listCond.init(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_KEEP_DETAIL_NAME, listCond);
        forwardKey(request, response, "webshop.jsp.manager.keep.detail");
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {
        forwardKey(request, response, "webshop.jsp.manager.keep.list");
      } else if (actionName.equals("mod_keep")) {
        UIRegNewKeep newkeep = new UIRegNewKeep();
        newkeep.setBackURL("webshop.jsp.manager.keep.detail");
        newkeep.setKeepNumber(request.getParameter("keepNumberTxt"));
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newkeep);
        forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep");
      } else if (SIConfig.SIACTION_MAIL.equalsIgnoreCase(actionName)) {
        try {
          sendMail(databaseConnection.getConnection());
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.mail"));
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        } catch (SIDBAccessException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException sqle) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.failure.mail"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        }
      } else if (actionName.equals("mail2")) {
        String keepnum = this.getParameter(urlParam, "keepnumberTxt");
        String mailComment = this.getParameter(urlParam, "mailComment");
        try {
          String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(manLogin.getUserCode()));
          SIBGUtil.sendKeepMail(databaseConnection.getConnection(), keepnum, mailComment, fromMail);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.mail"));
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        } catch (SIDBAccessException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException sqle) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.failure.mail"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        }
      } else if (SIConfig.SIACTION_CANCEL.equalsIgnoreCase(actionName)) {
        try {
          deleteKeep(databaseConnection.getConnection());
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        } catch (SIDBAccessException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException sqle) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.delete.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
          forwardKey(request, response, "webshop.jsp.manager.keep.list");
        }
      }else if (actionName.equals(SIConfig.SIACTION_PROTECT)){
        String keepnum = this.getParameter(urlParam,"keepnumberTxt");
        try{
          String sql = "SELECT CASE protectedFlg WHEN 0 THEN 1 ELSE 0 END FROM keepTbl WHERE keepNumber=" + SIDBUtil.SQL2Str(keepnum);
          String flg = SIDBUtil.getFirstData(databaseConnection.getConnection(), sql);
          
          SIModifyRec lRec = new SIModifyRec("keeptbl");
          lRec.addCondition("keepNumber", keepnum);
          lRec.add("protectedFlg", flg);
          if (SIUtil.isNotNull(flg)) {
            lRec.execute(databaseConnection.getConnection());
          }
          try{databaseConnection.getConnection().commit();}catch(SQLException sqle){}
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.modify"));
        }catch(Exception 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.keep.list");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * <b>deleteKeep</b> 3週間以前＋1週間見積なしのキープデータを削除します。
   * 
   * @param lConnection DBへのコネクション
   * @return なし
   * @throws SIDBAccessException
   */
  public static void deleteKeep(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer selectSQL = new StringBuffer();
    selectSQL.append("SELECT keepnumber FROM keeptbl WHERE status!='2' AND protectedflg='0' ");
    selectSQL.append("AND CASE WHEN keepdate < current_date + '-21 days'::interval THEN true WHEN status='0' AND keepdate < current_date + '-7 days'::interval THEN true ELSE false END ");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(selectSQL.toString());
      while (lResultSet.next()) {
        SIBGUtil.deleteEstimateFromKeep(lConnection, lResultSet.getString(1));
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new SIDBAccessException();
    }
  }
  
  /**
   * <b>sendMail</b> キープ警告メール送信
   * 
   * @param lConnection DBへのコネクション
   * @return なし
   * @throws SIDBAccessException
   */
  private boolean sendMail(Connection lConnection) throws SIDBAccessException, SQLException {
    boolean lRes = true;
    
    // 1.対象店舗リストを獲得
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT b.branchname,count(a.branchcode),b.email,a.branchcode FROM keeptbl a,branchtbl b, keepdetailtbl c ");
    lSqlBuf.append("WHERE a.status!='2' AND a.keepdate < (current_date + '-7 days'::interval) AND a.branchcode=b.branchcode AND a.keepnumber=c.keepnumber ");
    lSqlBuf.append("GROUP BY a.branchcode,b.branchname,b.email ORDER BY a.branchcode ");
    
    lStatement = lConnection.createStatement();
    lResultSet = lStatement.executeQuery(lSqlBuf.toString());
    
    while (lResultSet.next()) {
      sendMailexecute(lConnection, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(3), lResultSet.getString(4));
    }
    
    // メールはロールバックできないので１件コミット
    if (lRes) {
      try {
        lConnection.commit();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    } else {
      try {
        lConnection.rollback();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    }
    lStatement.close();
    lResultSet.close();
    return lRes;
  }
  
  private boolean sendMailexecute(Connection lConnection, String branchname, String count, String mail, String branchcode) throws SIDBAccessException, SQLException {
    boolean lRes = true;
    String[] sendList = new String[1];
    
    SISendMail lSendMail = new SISendMail();
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_WARNING);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    StringBuffer lMailBuf = new StringBuffer();
    
    lMailBuf.append("\n-------------");
    lMailBuf.append("\n対象店舗:").append(branchname).append("支店");
    lMailBuf.append("\n警告キープ件数:").append(count).append("件");
    sendList[0] = mail;
    
    Statement lStatement2 = null;
    ResultSet lResultSet2 = null;
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append("SELECT hc.chargename AS hcharge,kh.keepnumber,kh.keepdate,kh.chargecode, ");
    lSqlBuf2.append("kd.cmdtyname,kd.individualcode,kd.amount,db.branchname AS dbranch,kd.initdatetime ");
    lSqlBuf2.append("FROM keepdetailtbl kd ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT keepnumber,branchcode,chargecode,keepdate,status FROM keeptbl) kh ");
    lSqlBuf2.append("ON kh.keepnumber=kd.keepnumber ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT chargecode,chargename FROM chargetbl) hc ");
    lSqlBuf2.append("ON (kh.chargecode=hc.chargecode) ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) db ");
    lSqlBuf2.append("ON (kd.branchcode=db.branchcode) ");
    lSqlBuf2.append("WHERE kh.status!='2' AND kh.keepdate < (current_date + '-7 days'::interval) ");
    lSqlBuf2.append("AND kh.branchcode=").append(SIDBUtil.SQL2Str(branchcode," "));
    lSqlBuf2.append("ORDER BY kh.branchcode,kh.chargecode,kh.keepnumber,kd.individualcode");
    
    lStatement2 = lConnection.createStatement();
    lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
    
    String keepnumber = "";
    String allocate = "";
    String charge = "";
    
    while (lResultSet2.next()) {
      if (!keepnumber.equals(lResultSet2.getString("keepnumber"))) {
        keepnumber = lResultSet2.getString("keepnumber");
        lMailBuf.append("\n-------------");
        lMailBuf.append("\nキープ番号:").append(keepnumber).append(" 担当者:").append(lResultSet2.getString("hcharge"));
        lMailBuf.append("\n登録日時:").append(lResultSet2.getString("keepdate"));
        lMailBuf.append("\n-------------");
      }
      lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
      if (SIUtil.isNull(lResultSet2.getString("dbranch"))) allocate = "在庫管理なし";
      else allocate = lResultSet2.getString("dbranch");
      lMailBuf.append("\nキープ店舗:").append(allocate);
      lMailBuf.append("\nキープ数量:").append(lResultSet2.getString("amount"));
      lMailBuf.append("\nキープ日時:").append(lResultSet2.getString("initdatetime"));
      lMailBuf.append("\n");
      if (SIUtil.isNotNull(lResultSet2.getString("chargecode")) && !charge.equals(lResultSet2.getString("chargecode"))) {
        sendIndividualMailexecute(lConnection, branchcode, lResultSet2.getString("chargecode"));
      }
      charge = lResultSet2.getString("chargecode");
    }
    lMailBuf.append("\n-------------");
    lMailBuf.append("\n");
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setToMailAddress(sendList);
      lSendMail.execute();
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    }
    return lRes;
  }
  
  private boolean sendIndividualMailexecute(Connection lConnection, String branchcode, String chargecode) throws SIDBAccessException, SQLException {
    boolean lRes = true;
    String[] sendList = new String[1];
    
    SISendMail lSendMail = new SISendMail();
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_WARNING);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    StringBuffer lMailBuf = new StringBuffer();
    
    Statement lStatement2 = null;
    ResultSet lResultSet2 = null;
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append("SELECT hc.chargename AS hcharge,kh.keepnumber,kh.keepdate,hc.email, ");
    lSqlBuf2.append("kd.cmdtyname,kd.individualcode,kd.amount,db.branchname AS dbranch,kd.initdatetime ");
    lSqlBuf2.append("FROM keepdetailtbl kd ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT keepnumber,branchcode,chargecode,keepdate,status FROM keeptbl) kh ");
    lSqlBuf2.append("ON kh.keepnumber=kd.keepnumber ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) db ");
    lSqlBuf2.append("ON (kd.branchcode=db.branchcode) ");
    lSqlBuf2.append(",(SELECT chargecode,chargename,email FROM chargetbl) hc ");
    lSqlBuf2.append("WHERE kh.status!='2' AND kh.keepdate < (current_date + '-7 days'::interval) ");
    lSqlBuf2.append("AND kh.branchcode=").append(SIDBUtil.SQL2Str(branchcode," "));
    lSqlBuf2.append("AND kh.chargecode=hc.chargecode ");
    lSqlBuf2.append("AND kh.chargecode=").append(SIDBUtil.SQL2Str(chargecode," "));
    lSqlBuf2.append("ORDER BY kh.branchcode,kh.chargecode,kh.keepnumber,kd.individualcode");
    
    lStatement2 = lConnection.createStatement();
    lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
    
    String keepnumber = "";
    String allocate = "";
    
    while (lResultSet2.next()) {
      sendList[0] = lResultSet2.getString("email");
      if (!keepnumber.equals(lResultSet2.getString("keepnumber"))) {
        keepnumber = lResultSet2.getString("keepnumber");
        lMailBuf.append("\n-------------");
        lMailBuf.append("\nキープ番号:").append(keepnumber).append(" 担当者:").append(lResultSet2.getString("hcharge"));
        lMailBuf.append("\n登録日時:").append(lResultSet2.getString("keepdate"));
        lMailBuf.append("\n-------------");
      }
      lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
      if (SIUtil.isNull(lResultSet2.getString("dbranch"))) allocate = "在庫管理なし";
      else allocate = lResultSet2.getString("dbranch");
      lMailBuf.append("\nキープ店舗:").append(allocate);
      lMailBuf.append("\nキープ数量:").append(lResultSet2.getString("amount"));
      lMailBuf.append("\nキープ日時:").append(lResultSet2.getString("initdatetime"));
      lMailBuf.append("\n");
    }
    lMailBuf.append("\n-------------");
    lMailBuf.append("\n");
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setToMailAddress(sendList);
      lSendMail.execute();
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    }
    return lRes;
  }

  /**
   * <b>produceCSVFile</b> CSVファイルを作成します。
   * 
   * @param response クライアントまでのresponse
   * @param lConnection DBへのコネクション
   * @param lListCond CSVファイルに出力するデータセット
   * @return なし
   * @throws なし
   */
  private void produceCSVFile(HttpServletResponse response, Connection lConnection, UIKeepListCond lListCond) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_KEEP_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a1.keepnumber,a2.branchname,a3.chargename,b1.initdatetime,b1.cmdtycode,b1.individualcode,b1.cmdtyname ");
    lSqlBuf.append(",CASE WHEN b1.cmdtytype='0' THEN b2.branchname ELSE '在庫管理なし' END AS hikiate ");
    lSqlBuf.append(",b1.amount ");
    lSqlBuf.append(",CASE WHEN a1.status!='0' THEN '見積済' ELSE '' END AS mitumori ");
    lSqlBuf.append(",CASE WHEN a1.status='2' THEN '受注済' ELSE '' END AS jyutyuu ");
    lSqlBuf.append(",CASE WHEN a1.keepmailflg='1' THEN '送信済' ELSE '' END AS mail ");
    lSqlBuf.append(",CASE WHEN a1.protectedFlg='1' THEN 'する' ELSE 'しない' END AS protect ");
    lSqlBuf.append("FROM keeptbl a1  ");
    lSqlBuf.append("LEFT OUTER JOIN branchtbl a2 ON a1.branchcode=a2.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl a3 ON a1.chargecode=a3.chargecode ");
    lSqlBuf.append(",keepdetailtbl b1 ");
    lSqlBuf.append("LEFT OUTER JOIN branchtbl b2 ON b1.branchcode=b2.branchcode ");
    lSqlBuf.append("WHERE a1.keepnumber=b1.keepnumber ");
    // 検索条件
    lSqlBuf.append(lListCond.getCondtionCSVSQL());
    // ソート
    lSqlBuf.append(" ORDER BY a1.keepnumber ");
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_KEEP_LIST_INX));
    if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {} else {
      lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_KEEP_LIST_INX));
    }
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
}