/**
 * Copyright (c) 2003-2004 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.basic.SIPointMan;
import jp.co.sint.beans.mallmgr.UIPointManListCond;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SICSVWrite;
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;//7.1.1 ST0236 追加
import jp.co.sint.tools.SICodeToName;//7.2.0 ST0540 追加

/**
 * @version $Id: SIRegPointManSrv.java,v 1.0 2003/12/03 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   2003/12/03 10:30:45  Original
 */
public class SIRegPointManSrv 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);//7.1.1 ST0236 追加
    
    try {
      String actionName=this.getActionName(urlParam);//画面からのアクション	//7.1.1 ST0236 修正
      String editMode = this.getEditMode(urlParam); //DBへの編集モード	//7.1.1 ST0236 修正
      
      SILogin lLogin=SIHTMLUtil.getLogin(request);
      if (SIUtil.isNull(actionName)){
        SIPointMan.clear(databaseConnection.getConnection());
        forwardKey(request,response,"webshop.jsp.manager.pointman.list");
      }else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//CSVファイルへの出力
        UIPointManListCond listCond=new UIPointManListCond();
        listCond=(UIPointManListCond)session.getAttribute(SIConfig.SISESSION_MAN_POINTMAN_LIST_NAME);
        if (listCond==null)listCond=new UIPointManListCond();
        this.produceCSVFile(lLogin,response,databaseConnection.getConnection(),listCond,getParameter(urlParam,"csvCodeTxt"));//7.1.1 ST0236 修正
        if (!response.isCommitted()){
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("database.query.notexist","CSVデータ"));
          forwardKey(request,response,"webshop.jsp.manager.pointman.list");
        }
      }else if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)){//一覧と検索などの画面のレコードの表示
        SIPointMan.clear(databaseConnection.getConnection());
        UIPointManListCond listCond=new UIPointManListCond();
        //データの取得
        //7.2.0 ST0540 追加・修正 ここから
        SICodeToName codeToName = new SICodeToName();
        codeToName.srchCustName(databaseConnection.getConnection(),(String)urlParam.getParam("custCodeTxt"));
        listCond=new UIPointManListCond(request,urlParam,codeToName.getCustName());//7.1.1 ST0236 修正
        //7.2.0 ST0540 追加・修正 ここまで
        
        //データのチェック
        listCond.validate(request,databaseConnection.getConnection()); //7.2.0 ST0540 修正
        session.setAttribute(SIConfig.SISESSION_MAN_POINTMAN_LIST_NAME,listCond);
        forwardKey(request,response,"webshop.jsp.manager.pointman.list");
      }
    }catch (SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }
  
  /**
   * <b>produceCSVFile</b>
   * CSVファイルを作成します。
   * @param response クライアントまでのresponse
   * @param lConnection DBへのコネクション
   * @param lListCond CSVファイルに出力するデータセット
   * @return なし
   * @throws なし
   */
  private void produceCSVFile(SILogin lLogin,HttpServletResponse response,Connection lConnection,UIPointManListCond lListCond,String lCsvCode){
    //CSVファイル名の作成
    String lCsvFileName=SICSVConf.getCsvFileName(SICSVConf.SICSV_ORDER_INX);
    SICSVWrite lCsv=new SICSVWrite(lCsvFileName);
    
    //SQL文の作成
    StringBuffer lSqlBuf=new StringBuffer();
    String lGroup=" ";
    String lOrder="";
    if (Integer.parseInt(lCsvCode)==SICSVConf.SICSV_POINTMAN_ALL_INX){
      lSqlBuf.append("SELECT aa.MallShopCode,cc.FrontShopName,");
      lSqlBuf.append("aa.CustCode,bb.CustName,");
      lSqlBuf.append("CASE WHEN aa.IssueFlg=0 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ISSUE_INX][0][0]));
      //lSqlBuf.append("     WHEN aa.IssueFlg=1 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ISSUE_INX][1][0]));//7.3.0 PI-NES0501 削除
      lSqlBuf.append("     WHEN aa.IssueFlg=2 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ISSUE_INX][1][0]));//7.3.0 PI-NES0501 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lSqlBuf.append("ELSE TEXT(aa.IssueFlg) END AS IssueFlg,");
      }else{
        lSqlBuf.append("ELSE TO_CHAR(aa.IssueFlg) END AS IssueFlg,");
      }
      lSqlBuf.append("aa.PointOfIssue,");
      lSqlBuf.append("CASE WHEN aa.EnableFlg=0 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ENABLE_INX][0][0]));
      lSqlBuf.append("     WHEN aa.EnableFlg=1 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ENABLE_INX][1][0]));
      lSqlBuf.append("     WHEN aa.EnableFlg=2 THEN "+SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_NAME[SIFlagConf.SIFLAG_ENABLE_INX][2][0]));
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lSqlBuf.append("ELSE TEXT(aa.EnableFlg) END AS EnableFlg,");
      }else{
        lSqlBuf.append("ELSE TO_CHAR(aa.EnableFlg) END AS EnableFlg,");
      }
      lSqlBuf.append("aa.OrderCode,dd.InitDateTime,dd.ReceiptDate,aa.ReviewCode,aa.IssueDateTime,ee.comment ");
      lSqlBuf.append("FROM PointManMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa ");
      lSqlBuf.append("LEFT OUTER JOIN ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("dd ON aa.OrderCode=dd.OrderCode ");
      lSqlBuf.append("LEFT OUTER JOIN InvestPointLogTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("ee ON aa.PointManCode=ee.PointManCode, ");
      lSqlBuf.append("CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb,");
      lSqlBuf.append("MallShopMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("cc ");
      lSqlBuf.append("WHERE aa.CustCode=bb.CustCode ");
      lSqlBuf.append("AND aa.MallShopCode=cc.MallShopCode ");
      
      lOrder=lListCond.getOrderBySQL();
      lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_POINTMAN_ALL_INX));//タイトルの設定
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      }else{
        lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_POINTMAN_ALL_INX));
      }
      lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_POINTMAN_ALL_INX));//ファイル名の設定
    }else if (Integer.parseInt(lCsvCode)==SICSVConf.SICSV_POINTMAN_SHOP_INX){
      lSqlBuf.append("SELECT aa.MallShopCode,bb.FrontShopName,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=0 THEN pointofissue ELSE 0 END) AS totalOfInvalid,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=2 THEN pointofissue ELSE 0 END) AS totalOfTemporary,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=1 THEN pointofissue ELSE 0 END) AS totalOfEffective ");
      lSqlBuf.append("FROM PointManMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
      lSqlBuf.append("MallShopMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
      lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
      lGroup="GROUP BY aa.MallShopCode,bb.FrontShopName ";
      lOrder="ORDER BY aa.MallShopCode ASC ";
      
      lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_POINTMAN_SHOP_INX));//タイトルの設定
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      }else{
        lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_POINTMAN_SHOP_INX));
      }
      lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_POINTMAN_SHOP_INX));//ファイル名の設定
    }else if (Integer.parseInt(lCsvCode)==SICSVConf.SICSV_INVEST_POINT_LOG_LIST_INX){
      lSqlBuf.append("SELECT bb.* ");
      lSqlBuf.append("FROM PointManMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, ");
      lSqlBuf.append("InvestPointLogTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
      lSqlBuf.append("WHERE aa.PointManCode=bb.PointManCode ");
      lOrder=lListCond.getOrderBySQL();
      lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_INVEST_POINT_LOG_LIST_INX));//タイトルの設定
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      }else{
        lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_INVEST_POINT_LOG_LIST_INX));
      }
      lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_INVEST_POINT_LOG_LIST_INX));//ファイル名の設定
    }else {
      lSqlBuf.append("SELECT aa.CustCode,bb.CustName,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=0 THEN pointofissue ELSE 0 END) AS totalOfInvalid,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=2 THEN pointofissue ELSE 0 END) AS totalOfTemporary,");
      lSqlBuf.append("SUM(CASE WHEN enableflg=1 THEN pointofissue ELSE 0 END) AS totalOfEffective ");
      lSqlBuf.append("FROM PointManMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
      lSqlBuf.append("CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
      lSqlBuf.append("WHERE aa.CustCode=bb.CustCode ");
      lGroup="GROUP BY aa.CustCode,bb.CustName ";
      lOrder="ORDER BY aa.CustCode ASC";
      
      lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_POINTMAN_CUST_INX));//タイトルの設定
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      }else{
        lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_POINTMAN_CUST_INX));
      }
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      }else{
        lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_POINTMAN_CUST_INX));
      }
      lCsv.setFileName(SICSVConf.getCsvFileName(SICSVConf.SICSV_POINTMAN_CUST_INX));//ファイル名の設定
    }
    
    if (lLogin.isShop()) lSqlBuf.append("AND aa.MallShopCode='").append(lLogin.getMallShopCode()+"' ");
    
    //条件
    lSqlBuf.append(lListCond.getConditionSQL());
    //合計
    lSqlBuf.append(lGroup);
    //ソート
    lSqlBuf.append(lOrder);
    log.debug("produceCSVFile:lSqlBuf="+lSqlBuf.toString());
    //SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    //CSVファイルの出力
    lCsv.execute(lConnection,response);
  }
}