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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SIReviewManager.java,v 1.0 2003/10/27 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/10/27 10:21:13  Original
 */
public class SIReviewInfoManager {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  private String email="";
  private String nickName="";
  private String sex="";
  
  /**
   * <b>SIReviewManager</b>
   * コンストラクタ
   * @param  なし
   * @return なし
   * @throws なし
   */
  public SIReviewInfoManager(){}
  
  /**
   * <b>SICartManager</b>
   * コンストラクタ
   * @param lNickName ニックネーム
   * @param lEmail メールアドレス
   * @param lSex 性別
   * @return なし
   * @throws なし
   */
  public SIReviewInfoManager(String lNickName,String lEmail,String lSex){
    this.nickName=lNickName;
    this.email=lEmail;
    this.sex=lSex;
  }
  
  public void setNickName(String lNickName){
    this.nickName=lNickName;
  }
  
  public void setEmail(String lEmail){
    this.email=lEmail;
  }
  
  public void setSex(String lSex){
    this.sex=lSex;
  }
  
  /**
   * <b>getCollection</b>
   * ニックネーム、Eメール、性別から、この人のレビューコレクションを取得します。
   * @param  Connection コネクション
   * @return collection
   * @throws なし
   */
  public Collection getCollection(Connection lConnection){
    Collection lReviewInfoColl=new ArrayList();
    SIReview lReview=new SIReview();
    Statement lStatement=null;
    ResultSet lResultSet=null;
    
    StringBuffer lSqlStatementBuf=new StringBuffer();
    lSqlStatementBuf.append("SELECT a.* ,CASE WHEN c.Status='1' ");
    if (SIDBMultiConf.SIDB_CURRENT_INX==SIDBMultiConf.SIDB_POSTGRESQL_INX){
      lSqlStatementBuf.append("AND ((b.SellFromDate <= CURRENT_DATE OR b.SellFromDate IS NULL) ");
      lSqlStatementBuf.append("OR (b.SellFromDate >= CURRENT_DATE AND b.SellFromDate IS NOT NULL AND b.rsrvenableflg=1)) ");
      lSqlStatementBuf.append("AND (b.SellToDate IS NULL OR b.SellToDate >= CURRENT_DATE) ");
    }else{
      lSqlStatementBuf.append("AND ((b.SellFromDate <= TO_DATE(SYSDATE) OR b.SellFromDate IS NULL) ");
      lSqlStatementBuf.append("OR (b.SellFromDate >= TO_DATE(SYSDATE) AND b.SellFromDate IS NOT NULL AND b.rsrvenableflg=1)) ");
      lSqlStatementBuf.append("AND (b.SellToDate IS NULL OR b.SellToDate >= TO_DATE(SYSDATE)) ");
    }
    lSqlStatementBuf.append("THEN 0 ELSE 1 END AS saleFlg ");
    lSqlStatementBuf.append("FROM ReviewTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("a,");
    lSqlStatementBuf.append("CmdtyMtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("b,");
    lSqlStatementBuf.append("MallShopMtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c ");
    lSqlStatementBuf.append("WHERE a.NickName = "+SIDBUtil.SQL2Str(nickName," "));
    lSqlStatementBuf.append("AND a.Email = "+SIDBUtil.SQL2Str(email," "));
    lSqlStatementBuf.append("AND a.Sex = "+SIDBUtil.SQL2Str(sex," "));
    lSqlStatementBuf.append("AND a.DispFlg = '1' ");
    lSqlStatementBuf.append("AND a.ShopCode = b.ShopCode ");
    lSqlStatementBuf.append("AND a.CmdtyCode = b.CmdtyCode ");
    lSqlStatementBuf.append("AND a.ShopCode = c.MallShopCode ");
    lSqlStatementBuf.append("ORDER BY a.InitDateTime DESC");
    log.debug("getCollection:lSqlStatementBuf="+lSqlStatementBuf.toString());
    
    //コネンクションの作成
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlStatementBuf.toString());
      while (lResultSet.next()){
        lReview=new SIReview();
        lReview.setEncode(SIConfig.SIENCODE_NONE);
        lReview.setTitle(lResultSet.getString("Title"));
        lReview.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lReview.setCmdtyName(lResultSet.getString("CmdtyName"));
        lReview.setShopCode(lResultSet.getString("ShopCode"));
        lReview.setContent(lResultSet.getString("Content"));
        lReview.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lReview.setAmountofStars(lResultSet.getString("AmountofStars"));
        lReview.setSex(lResultSet.getString("Sex"));
        lReview.setSaleFlg(lResultSet.getString("SaleFlg"));
        lReviewInfoColl.add(lReview);
      }
      return lReviewInfoColl;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
}