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

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

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SICmdty;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: UIConnCmdtyListCond.java,v 1.0 2003/09/29 Exp $
 * @author asakura <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>
 * asakura 2003/12/26 Original
 */
public class UIOtherCmdty extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 商品取得件数
  private static final int count = 30;
  
  // 商品取得件数
  private static final int mobileCount = 2;
  
  // ショップコード
  private String shopCode = "";
  
  // 商品コード
  private String cmdtyCode = "";
  
  public UIOtherCmdty(String shopCode, String cmdtyCode) {
    this.setShopCode(shopCode);
    this.setCmdtyCode(cmdtyCode);
  }
  
  // setter of ショップコード
  public void setShopCode(String lShopCode) {
    if (SIUtil.isNull(lShopCode)) lShopCode = "";
    this.shopCode = SIUtil.changeTo(lShopCode.trim(), this.encode);
  }
  
  // setter of 商品コード
  public void setCmdtyCode(String lCmdtyCode) {
    if (SIUtil.isNull(lCmdtyCode)) lCmdtyCode = "";
    this.cmdtyCode = SIUtil.changeTo(lCmdtyCode.trim(), this.encode);
  }
  
  // getter of ショップコード
  public String getShopCode() {
    return this.shopCode;
  }
  
  // getter of 商品コード
  public String getCmdtyCode() {
    return this.cmdtyCode;
  }
  
  // getter of ページ
  public String getPageNumberTxt() {
    return this.pageNumberTxt;
  }
  
  /**
   * init 対象のショップコードと商品コードを取得し、入力した検索データを取得し、 対象の商品名を設定します。
   * 
   * @param lRequest リクエスト
   * @param lConnection DBへのコネクション
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, Connection lConnection) {

  }
  
  public Collection getCollectionPreview(Connection lConnection) throws SIDBAccessException {
    return getCollection(lConnection,false,true,null);
  }
  
  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection, boolean isMobile, HttpServletRequest request) throws SIDBAccessException {
    return getCollection(lConnection,isMobile,false,request);
  }
  public Collection getCollection(Connection lConnection, boolean isMobile, boolean isPreview, HttpServletRequest request) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lConnCmdty = new SICmdty();
    Collection lConnCmdtys = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    String cmdtyName = "";
    String colorName = "";
    int usedNewFlg = 0;
    String usedNewName = "";
    StringBuffer makerString = new StringBuffer();
    if (lUserInfo.getMaker().length > 0) {
      makerString = new StringBuffer("(");
      for (int i = 0; i < lUserInfo.getMaker().length; i++) {
        makerString.append(SIDBUtil.SQL2Str(lUserInfo.getMaker()[i]));
        makerString.append(i < lUserInfo.getMaker().length - 1 ? "," : ")");
      }
    }
    
    lSqlBuf.append("SELECT c.cmdtycode,c.cmdtyname,c.individualcode,c.bgpricedispflg,c.unitprice,c.deliverytypecode,c.cmdtysize,c.memberdiscountflg");
    lSqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lSqlBuf.append("      WHEN c.deliveryTypeCode='0' AND c.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別A/Gサイズ）
    lSqlBuf.append("      WHEN c.deliveryTypeCode='1' AND c.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    lSqlBuf.append(" ELSE 0 END AS freedeliveryflg ");
    lSqlBuf.append(",c.usednewflg,c.taxflg,c.description,c.consigngoodsflg,g.colorname,c.unitpricewithouttax");
    lSqlBuf.append(",ab.sellcount ,ab.lastsell ,COALESCE((SELECT taxrate FROM taxvw) ,0) AS taxrate ");
    /* ログイン必須フラグ
    lSqlBuf.append(",CASE WHEN c.cmdtycode");
    lSqlBuf.append(" IN(SELECT cc.cmdtycode FROM cmdtyctgrymtbl cc,ctgryvw ct WHERE cc.ctgrycode = ct.ctgrycode");
    lSqlBuf.append(" AND (");
    for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
      if (i>0) lSqlBuf.append(" OR ");
      lSqlBuf.append(" (ct.branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
      lSqlBuf.append(" OR ct.ctgrycode = ").append(SIDBUtil.SQL2Str(SIConfig.LOGINCTGRY[i], ")"));
    }
    lSqlBuf.append(")) THEN '1' ELSE '0' END AS loginflg ");
    */
    lSqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lSqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lSqlBuf.append(" ELSE 1 END AS contractflg ");
    // EDBTG003-00 nagayoshi add start
    lSqlBuf.append(" ,c.cmdtyCompositionFlg ");
    // EDBTG003-00 nagayoshi add end
    lSqlBuf.append("FROM (SELECT a.cmdtycode,SUM(a.sellcount) AS sellcount,MAX(a.lastsell) AS lastsell ");
    lSqlBuf.append(" FROM othercmdtymtbl AS a,othercmdtymtbl AS b");
    lSqlBuf.append(" WHERE a.custcode=b.custcode AND b.cmdtycode=").append(SIDBUtil.SQL2Str(this.getCmdtyCode()));
    lSqlBuf.append(" GROUP BY a.cmdtycode) AS ab");
    // EDBTG003-00 nagayoshi mod start
//    lSqlBuf.append(",(SELECT cmdtycode,cmdtyname,individualcode,bgpricedispflg,unitprice,usednewflg,taxflg,description,colorcode,consigngoodsflg,makercode,deliverytypecode,cmdtysize,memberdiscountflg");
    lSqlBuf.append(",(SELECT cmdtycode,cmdtyname,individualcode,bgpricedispflg,unitprice,usednewflg,taxflg,description,colorcode,consigngoodsflg,makercode,deliverytypecode,cmdtysize,memberdiscountflg,cmdtyCompositionFlg,unitpricewithouttax");
    // EDBTG003-00 nagayoshi mod end
    lSqlBuf.append(" FROM cmdtyunittbl WHERE frontdispflg='1' ");
    lSqlBuf.append("AND NOT(usednewflg!=1 AND usednewflg!='7' AND amountflg='1' AND (amount-bgpamount)='0' ");
    lSqlBuf.append(" AND (rsrvenableflg='0' OR (rsrvamount IS NOT NULL AND rsrvamount='0')) ");
    lSqlBuf.append(" AND (soldoutdate IS NULL OR soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE))) AS c ");
    lSqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lSqlBuf.append("ON c.cmdtycode=comp.cmdtycode AND c.individualcode=comp.individualcode ");
    lSqlBuf.append(",colortbl AS g ,makertbl maker,feerulemtbl feerule ");
    lSqlBuf.append("WHERE ab.cmdtycode=c.cmdtycode AND c.colorcode = g.colorcode AND c.makercode = maker.makercode ");
    lSqlBuf.append("AND c.cmdtycode<>").append(SIDBUtil.SQL2Str(this.getCmdtyCode(), " "));
    lSqlBuf.append("AND c.cmdtycode IN(SELECT cmdtycode FROM cmdtyctgrymtbl) AND c.deliverytypecode = feerule.deliverytypecode ");
    
    lSqlBuf.append("AND c.individualcode IN (SELECT i.individualcode ");
    lSqlBuf.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
    lSqlBuf.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
    lSqlBuf.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
    lSqlBuf.append(" AND g.ctgrygroup<>'eyelashgarage') ");
    
    lSqlBuf.append("ORDER BY ab.lastsell DESC,ab.sellcount DESC LIMIT ");
    if (isMobile) {
      lSqlBuf.append(mobileCount);
    } else {
      lSqlBuf.append(count);
    }
    
    log.debug("lSqlStatementBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      // 商品レコードのセットの作成
      while (lResultSet.next()) {
        lConnCmdty = new SICmdty();
        lConnCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lConnCmdty.setShopCode("0");
        lConnCmdty.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lConnCmdty.setIndividualCode(lResultSet.getString("individualcode"));// 在庫コード PI-NES0501 追加
        lConnCmdty.setDescription(lResultSet.getString("description"));// 商品詳細
        lConnCmdty.setUnitPrice(lResultSet.getString("unitprice"));// 価格
        lConnCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitpricewithouttax"));// 価格
        lConnCmdty.setCalcUnitPrice(lResultSet.getString("unitprice"));// 勘定価格
        lConnCmdty.setTaxFlg(lResultSet.getString("taxflg"));// 税区分
        lConnCmdty.setTaxRate(lResultSet.getString("taxrate"));// 税率
        lConnCmdty.setBGPriceDispFlg(lResultSet.getString("bgpricedispflg"));// BG卸価表示フラグ 7.3.0 PI-NES0501 追加
        lConnCmdty.setUsedNewFlg(lResultSet.getInt("usednewflg"));// 中古・新品フラグ 7.3.0 PI-NES0501 追加
        // lConnCmdty.setLoginFlg(lResultSet.getString("loginflg").equals("1")); // ログイン必須フラグ
        lConnCmdty.setContractFlg(lResultSet.getString("contractflg").equals("1"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".equals(lResultSet.getString("IndividualCode").substring(0,1))||"L".equals(lResultSet.getString("IndividualCode").substring(0,1))){
          if ("1".equals(lUserInfo.getHairCosmeFlg())) {
            lConnCmdty.setHairCosmeFlg(false);
          }else{
            lConnCmdty.setHairCosmeFlg(true);
            lConnCmdty.setContractFlg(true);
          }
        }else{
          lConnCmdty.setHairCosmeFlg(false);
        }
        lConnCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lConnCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        
        cmdtyName = lResultSet.getString("cmdtyname");
        colorName = lResultSet.getString("colorname");
        usedNewFlg = lConnCmdty.getUsedNewFlg();
        if (usedNewFlg == 1 || usedNewFlg == 6 || usedNewFlg == 7) {// 新品
          usedNewName = "新品";
        } else if (usedNewFlg == 2 || usedNewFlg == 4) {// 新古OUTLET
          usedNewName = "新古OUTLET";
        } else if (usedNewFlg == 3) {// BG認定中古
          usedNewName = "BG認定中古";
        } else {// 中古
          usedNewName = "中古";
        }
        //if (lResultSet.getInt("consigngoodsflg") == 1) usedNewName = usedNewName + "[委託]";
        
        if (SIUtil.isNotNull(colorName) && SIUtil.isNotNull(usedNewName)) {
          cmdtyName = cmdtyName + "(" + colorName + "・" + usedNewName + ")";
        } else if (SIUtil.isNotNull(colorName) || SIUtil.isNotNull(usedNewName)) {
          cmdtyName = cmdtyName + "(" + colorName + usedNewName + ")";
        }
        
        lConnCmdty.setCmdtyName(cmdtyName);
        lConnCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        // EDBTG003-00 nagayoshi add start
        lConnCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lConnCmdtys.add(lConnCmdty);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lConnCmdtys;
  }
}