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

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

import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SIUtil;

public class SIMaker extends SIBasic {
  
  // ブランドコード
  private String makerCode = "";
  
  // ブランド名
  private String makerName = "";
  
  // メモ
  private String description = "";
  
  // 電話番号
  private String tel = "";
  
  // URL
  private String url = "";
  
  // 登録日時
  private String initDateTime = "";
  
  // 更新日時
  private String updateDateTime = "";
  
  // 紐付け商品フラグ
  private String cmdtyCount = "";
  
  // フロント表示区分
  private String frontDispFlg = "";
  
  // ソート順
  private String sortOrder = "";
  
  // 検索用ブランドコード
  private String makerCodeReference = "";
  
  // 要契約フラグ
  private String contractFlg = "";
  
  // ブランド名別名１
  private String otherName1 = "";
  
  // ブランド名別名２
  private String otherName2 = "";
  
  // 問い合わせ先部署名
  private String referencePost = "";
  
  // 問い合わせ先電話番号表記
  private String referenceNumber = "";
  
  // 問い合わせ先メールアドレス
  private String referenceMail = "";
  
  // コンストラクタ
  public SIMaker() {}
  
  // getter of ブランドコード
  public String getMakerCode() {
    return this.makerCode;
  }
  
  // getter of ブランド名
  public String getMakerName() {
    return this.makerName;
  }
  
  // getter of メモ
  public String getDescription() {
    return this.description;
  }
  
  // getter of 電話番号
  public String getTel() {
    return this.tel;
  }
  
  // getter of URL
  public String getUrl() {
    return this.url;
  }
  
  // getter of 登録日時
  public String getInitDateTime() {
    return this.initDateTime;
  }
  
  // getter of 更新日時
  public String getUpdateDateTime() {
    return this.updateDateTime;
  }
  
  // getter of 紐付け商品フラグ
  public String getCmdtyCount() {
    return this.cmdtyCount;
  }
  
  // getter of 検索用ブランドコード
  public String getMakerCodeReference() {
    return this.makerCodeReference;
  }
  
  // getter of フロント表示フラグ
  public String getFrontDispFlg() {
    return this.frontDispFlg;
  }
  
  // getter of ソート順
  public String getSortOrder() {
    return this.sortOrder;
  }
  
  // getter of 要契約フラグ
  public String getContractFlg() {
    return this.contractFlg;
  }
  
  // getter of ブランド名別名１
  public String getOtherName1() {
    return this.otherName1;
  }
  
  // getter of ブランド名別名２
  public String getOtherName2() {
    return this.otherName2;
  }
  
  // setter of ブランドコード
  public void setMakerCode(String lMakerCode) {
    if (SIUtil.isNull(lMakerCode)) lMakerCode = "";
    this.makerCode = SIUtil.changeTo(lMakerCode.trim(), this.encode);
  }
  
  // setter of ブランド名
  public void setMakerName(String lMakerName) {
    if (SIUtil.isNull(lMakerName)) lMakerName = "";
    this.makerName = SIUtil.changeTo(lMakerName.trim(), this.encode);
  }
  
  // setter of メモ
  public void setDescription(String lDescription) {
    if (SIUtil.isNull(lDescription)) lDescription = "";
    this.description = SIUtil.changeTo(lDescription.trim(), this.encode);
  }
  
  // setter of 電話番号
  public void setTel(String lTel) {
    if (SIUtil.isNull(lTel)) lTel = "";
    this.tel = SIUtil.changeTo(lTel.trim(), this.encode);
  }
  
  // setter of URL
  public void setUrl(String lUrl) {
    if (SIUtil.isNull(lUrl)) lUrl = "";
    this.url = SIUtil.changeTo(lUrl.trim(), this.encode);
  }
  
  // setter of 登録日時
  public void setInitDateTime(String lInitDateTime) {
    if (SIUtil.isNull(lInitDateTime)) lInitDateTime = "";
    this.initDateTime = SIUtil.changeTo(lInitDateTime.trim(), this.encode);
  }
  
  // setter of 更新日時
  public void setUpdateDateTime(String lUpdateDateTime) {
    if (SIUtil.isNull(lUpdateDateTime)) lUpdateDateTime = "";
    this.updateDateTime = SIUtil.changeTo(lUpdateDateTime.trim(), this.encode);
  }
  
  // setter of 紐付け商品フラグ
  public void setCmdtyCount(String lCmdtyCount) {
    if (SIUtil.isNull(lCmdtyCount)) lCmdtyCount = "";
    this.cmdtyCount = SIUtil.changeTo(lCmdtyCount.trim(), this.encode);
  }
  
  // setter of 検索用ブランドコード
  public void setMakerCodeReference(String lMakerCodeReference) {
    if (SIUtil.isNull(lMakerCodeReference)) lMakerCodeReference = "";
    this.makerCodeReference = SIUtil.changeTo(lMakerCodeReference.trim(), this.encode);
  }
  
  // setter of フロント表示フラグ
  public void setFrontDispFlg(String lFrontDispFlg) {
    if (SIUtil.isNull(lFrontDispFlg)) lFrontDispFlg = "";
    this.frontDispFlg = SIUtil.changeTo(lFrontDispFlg.trim(), this.encode);
  }
  
  // setter of ソート順
  public void setSortOrder(String lSortOrder) {
    if (SIUtil.isNull(lSortOrder)) lSortOrder = "";
    this.sortOrder = SIUtil.changeTo(lSortOrder.trim(), this.encode);
  }
  
  // setter of 要契約フラグ
  public void setContractFlg(String lContractFlg) {
    if (SIUtil.isNull(lContractFlg)) lContractFlg = "";
    this.contractFlg = SIUtil.changeTo(lContractFlg.trim(), this.encode);
  }
  
  // setter of ブランド名別名１
  public void setOtherName1(String lOtherName1) {
    if (SIUtil.isNull(lOtherName1)) lOtherName1 = "";
    this.otherName1 = SIUtil.changeTo(lOtherName1.trim(), this.encode);
  }
  
  // setter of ブランド名別名２
  public void setOtherName2(String lOtherName2) {
    if (SIUtil.isNull(lOtherName2)) lOtherName2 = "";
    this.otherName2 = SIUtil.changeTo(lOtherName2.trim(), this.encode);
  }
  
  public String getReferencePost() {
    return referencePost;
  }
  
  public String getReferenceNumber() {
    return referenceNumber;
  }
  
  public String getReferenceMail() {
    return referenceMail;
  }
  
  public void setReferencePost(String referencePost) {
    if (SIUtil.isNull(referencePost)) referencePost = "";
    this.referencePost = referencePost;
  }
  
  public void setReferenceNumber(String referenceNumber) {
    if (SIUtil.isNull(referenceNumber)) referenceNumber = "";
    this.referenceNumber = referenceNumber;
  }
  
  public void setReferenceMail(String referenceMail) {
    if (SIUtil.isNull(referenceMail)) referenceMail = "";
    this.referenceMail = referenceMail;
  }
  
  /**
   * <b>getNameCollection</b> ブランド名一覧を取得
   * 
   * @param Connection
   * @return Collection
   * @throws なし
   */
  public static Collection getNameCollection(Connection lConnection) {
    Collection lResultColl = new ArrayList();
    
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, "SELECT makername,makercode FROM makertbl ORDER BY makercode ASC", false, true);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    
    return lResultColl;
  }
  
  public static Collection getMakerColl(Connection lConnection, String lCtgryCode, String makerKey, String usedNewFlgTxt, String keyWord,boolean isLogin) {
    if (SIUtil.isNull(lCtgryCode)) return null;
    Collection lResultColl = new ArrayList();
    StringBuffer sqlStr = new StringBuffer();
    
    sqlStr.append("SELECT DISTINCT CASE WHEN m.makercode='1001' THEN 'その他' WHEN m.makercode='1230' THEN 'ポール･シェリーM-products' ELSE m.makername END AS makername");
    sqlStr.append(",m.makercode,m.sortorder ");
    if (SIUtil.isNotNull(makerKey)) {
      sqlStr.append("FROM makertbl m WHERE m.makercode = ").append(SIDBUtil.SQL2Str(makerKey));
    } else {
      sqlStr.append("FROM (SELECT * FROM makertbl WHERE makercode NOT IN ('1231','1232') ");
      if(!isLogin)sqlStr.append("AND contractflg='0' ");
      sqlStr.append(") m,cmdtymtbl cm,cmdtyctgrymtbl cc ");
      sqlStr.append("WHERE cm.makercode = m.makercode AND cc.cmdtycode = cm.cmdtycode AND m.enabledflg='1' ");
      sqlStr.append("AND cc.ctgrycode IN (SELECT ctgrycode FROM ctgryvw WHERE branch LIKE '%").append(SIDBUtil.SQL2Like("~" + lCtgryCode + "~")).append("%' ");
      sqlStr.append("OR branch like '%").append(SIDBUtil.SQL2Like("~" + lCtgryCode)).append("') ");
      sqlStr.append("AND cm.cmdtycode IN (SELECT cmdtycode FROM cmdtyunittbl WHERE frontdispflg='1' ");
      if (SIUtil.isNotNull(usedNewFlgTxt) && usedNewFlgTxt.equals("used")) {
        sqlStr.append("AND usednewflg IN ('0','3','4','5') ");
      } else if (SIUtil.isNotNull(usedNewFlgTxt) && usedNewFlgTxt.equals("new")) {
        sqlStr.append("AND usednewflg IN ('1','2','6') ");
      }
      if (SIUtil.isNotNull(keyWord)) {
        sqlStr.append("AND (cmdtyname LIKE '%").append(SIDBUtil.SQL2Like(keyWord)).append("%'");
        sqlStr.append(" OR description LIKE '%").append(SIDBUtil.SQL2Like(keyWord)).append("%'");
        sqlStr.append(" OR individualcode LIKE '%").append(SIDBUtil.SQL2Like(keyWord)).append("%') ");
      }
      sqlStr.append("AND NOT(usednewflg!='1' AND usednewflg!='7' AND rsrvenableflg='0' AND amountflg='1' AND amount='0' AND (soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE))) ");
      sqlStr.append("ORDER BY sortorder,makername");
    }
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, sqlStr.toString(), false, false);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    
    return lResultColl;
  }

  // 携帯の子カテ一覧表示用SQL
  public static String getChildListSql(String ctgry){
    StringBuffer str = new StringBuffer();
    str.append("SELECT DISTINCT CASE WHEN m.makercode='1001' THEN 'その他' WHEN m.makercode='1230' THEN 'ポール･シェリーM-products' ELSE m.makername END AS makername");
    str.append(",m.makercode,m.sortorder ");
    str.append("FROM (SELECT * FROM makertbl WHERE makercode NOT IN ('1231','1232')) m,cmdtymtbl cm,cmdtyctgrymtbl cc ");
    str.append("WHERE cm.makercode = m.makercode AND cc.cmdtycode = cm.cmdtycode AND m.enabledflg='1' ");
    str.append("AND cc.ctgrycode IN (SELECT ctgrycode FROM ctgryvw WHERE branch LIKE '%").append(SIDBUtil.SQL2Like("~" + ctgry + "~")).append("%' ");
    str.append("OR branch like '%").append(SIDBUtil.SQL2Like("~" + ctgry)).append("') ");
    str.append("AND cm.cmdtycode IN (SELECT cmdtycode FROM cmdtyunittbl WHERE frontdispflg=1 ");
    str.append("AND NOT(usednewflg!='1' AND usednewflg!='7' AND rsrvenableflg='0' AND amountflg='1' AND amount='0' AND (soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE))) ");
    str.append("ORDER BY sortorder,makername");
    return str.toString();
  }
  // 要契約ブランドを除く
  public static String getChildListSql2(String ctgry){
    StringBuffer str = new StringBuffer();
    str.append("SELECT DISTINCT CASE WHEN m.makercode='1001' THEN 'その他' WHEN m.makercode='1230' THEN 'ポール･シェリーM-products' ELSE m.makername END AS makername");
    str.append(",m.makercode,m.sortorder ");
    str.append("FROM (SELECT * FROM makertbl WHERE makercode NOT IN ('1231','1232') AND contractflg='0') m,cmdtymtbl cm,cmdtyctgrymtbl cc ");
    str.append("WHERE cm.makercode = m.makercode AND cc.cmdtycode = cm.cmdtycode AND m.enabledflg='1' ");
    str.append("AND cc.ctgrycode IN (SELECT ctgrycode FROM ctgryvw WHERE branch LIKE '%").append(SIDBUtil.SQL2Like("~" + ctgry + "~")).append("%' ");
    str.append("OR branch like '%").append(SIDBUtil.SQL2Like("~" + ctgry)).append("') ");
    str.append("AND cm.cmdtycode IN (SELECT cmdtycode FROM cmdtyunittbl WHERE frontdispflg='1' ");
    str.append("AND NOT(usednewflg!='1' AND usednewflg!='7' AND rsrvenableflg='0' AND amountflg='1' AND amount='0' AND (soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE))) ");
    str.append("ORDER BY sortorder,makername");
    return str.toString();
  }
  
  public void reset(Connection lConnection,String lMakerCode){
    Statement lStatement=null;
    ResultSet lResultSet=null;
    if (SIUtil.isNotNull(lMakerCode)&&SICheckUtil.isDigit(lMakerCode)) {
      try{
        lStatement = lConnection.createStatement();
        lResultSet = lStatement.executeQuery("SELECT * FROM makertbl WHERE makercode="+SIDBUtil.SQL2Str(lMakerCode));
        if (lResultSet.next()){
          this.setMakerCode(lMakerCode);
          this.setMakerName(lResultSet.getString("makerName"));
          this.setUrl(lResultSet.getString("url"));
          this.setTel(lResultSet.getString("tel"));
          this.setDescription(lResultSet.getString("memo"));
          this.setFrontDispFlg(lResultSet.getString("enabledflg"));
          this.setContractFlg(lResultSet.getString("contractflg"));
          this.setSortOrder(lResultSet.getString("sortorder"));
          this.setOtherName1(lResultSet.getString("othername1"));
          this.setOtherName2(lResultSet.getString("othername2"));
          this.setReferencePost(lResultSet.getString("referencepost"));
          this.setReferenceNumber(lResultSet.getString("referencenumber"));
          this.setReferenceMail(lResultSet.getString("referencemail"));
        }
      }catch(Exception e){
        e.printStackTrace();
      } finally {
        SIDBUtil.close(lStatement, lResultSet);
      }
    }
  }
}