/**
 * 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 javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SILogin;
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.SIDateTimeType;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加

import org.apache.log4j.Category;

/**
 * @version $Id: UIPointManListCond.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 9:47:23  Original
 */
public class UIPointManListCond extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ショップコード
  private String mallShopCodeTxt="";
  
  //顧客コード
  private String custCodeTxt="";
  
  //顧客名
  private String custNameTxt="";//7.2.0 ST0540 修正
  
  //発行種別
  private String issueFlgRdo="";
  
  //有効フラグ
  private String enableFlgRdo="";
  
  //対象期間From
  private String issueDateTimeFrom="";
  
  //対象期間Fromの年
  private String issueDateTimeYearFromCbo="";
  
  //対象期間Fromの月
  private String issueDateTimeMonthFromCbo="";
  
  //対象期間Fromの日
  private String issueDateTimeDayFromCbo="";
  
  //対象期間To
  private String issueDateTimeTo="";
  
  //対象期間Toの年
  private String issueDateTimeYearToCbo="";
  
  //対象期間Toの月
  private String issueDateTimeMonthToCbo="";
  
  //対象期間Toの日
  private String issueDateTimeDayToCbo="";
  
  //並べ替え1
  private String firstOrderSel="mallShopCode";
  
  //並び順1
  private String firstOrderTxt="0";
  
  //並べ替え2
  private String secondOrderSel="custCode";
  
  //並び順2
  private String secondOrderTxt="0";
  
  //並べ替え3
  private String thirdOrderSel="issueDateTime";
  
  //並び順3
  private String thirdOrderTxt="0";
  
  //並び順
  private String orderBySQL=" ORDER BY aa.mallShopCode ASC ,aa.custCode ASC,aa.issueDateTime ASC ";
  
  //検索用のＳＱＬ文
  private String conditionSQL="";
  
  public UIPointManListCond(){}
  
  public UIPointManListCond(HttpServletRequest request,SIURLParameter lUrlParam,String lCustName){//7.1.1 ST0236 修正 //7.2.0 ST0540 修正
    this.init(request,lUrlParam,lCustName);//7.1.1 ST0236 修正 //7.2.0 ST0540 修正
  }
  
  //setter of ショップコード
  public void setMallShopCodeTxt(String lMallShopCodeTxt){
    if (SIUtil.isNull(lMallShopCodeTxt)) lMallShopCodeTxt="";
    this.mallShopCodeTxt=SIUtil.changeTo(lMallShopCodeTxt.trim(),this.encode);
  }
  
  //setter of 顧客コード
  public void setCustCodeTxt(String lCustCodeTxt){
    if (SIUtil.isNull(lCustCodeTxt)) lCustCodeTxt="";
    this.custCodeTxt=SIUtil.changeTo(lCustCodeTxt.trim(),this.encode);
  }
  
  //7.2.0 ST0540 修正 ここから
  //setter of 顧客名
  public void setCustNameTxt(String lCustNameTxt){
    if (SIUtil.isNull(lCustNameTxt)) lCustNameTxt="";
    this.custNameTxt=lCustNameTxt.trim();
  }
  //7.2.0 ST0540 修正 ここまで
  
  //setter of 発行種別
  public void setIssueFlgRdo(String lIssueFlgRdo){
    if (SIUtil.isNull(lIssueFlgRdo)) lIssueFlgRdo="";
    this.issueFlgRdo=SIUtil.changeTo(lIssueFlgRdo.trim(),this.encode);
  }
  
  //setter of 有効フラグ
  public void setEnableFlgRdo(String lEnableFlgRdo){
    if (SIUtil.isNull(lEnableFlgRdo)) lEnableFlgRdo="";
    this.enableFlgRdo=SIUtil.changeTo(lEnableFlgRdo.trim(),this.encode);
  }
  
  //setter of 対象期間From
//  public void setIssueDateTimeFromCbo(String lIssueDateTimeFromCbo){
//    if (SIUtil.isNull(lIssueDateTimeFromCbo)) lIssueDateTimeFromCbo="";
//    this.issueDateTimeFromCbo=SIUtil.changeTo(lIssueDateTimeFromCbo.trim(),this.encode);
//  }
  
  //setter of 対象期間Fromの年
  public void setIssueDateTimeYearFromCbo(String lIssueDateTimeYearFromCbo){
    if (SIUtil.isNull(lIssueDateTimeYearFromCbo)) lIssueDateTimeYearFromCbo="";
    this.issueDateTimeYearFromCbo=SIUtil.changeTo(lIssueDateTimeYearFromCbo.trim(),this.encode);
  }
  
  //setter of 対象期間Fromの月
  public void setIssueDateTimeMonthFromCbo(String lIssueDateTimeMonthFromCbo){
    if (SIUtil.isNull(lIssueDateTimeMonthFromCbo)) lIssueDateTimeMonthFromCbo="";
    this.issueDateTimeMonthFromCbo=SIUtil.changeTo(lIssueDateTimeMonthFromCbo.trim(),this.encode);
  }
  
  //setter of 対象期間Fromの日
  public void setIssueDateTimeDayFromCbo(String lIssueDateTimeDayFromCbo){
    if (SIUtil.isNull(lIssueDateTimeDayFromCbo)) lIssueDateTimeDayFromCbo="";
    this.issueDateTimeDayFromCbo=SIUtil.changeTo(lIssueDateTimeDayFromCbo.trim(),this.encode);
  }
  
  //setter of 対象期間To
//  public void setIssueDateTimeToCbo(String lIssueDateTimeToCbo){
//    if (SIUtil.isNull(lIssueDateTimeToCbo)) lIssueDateTimeToCbo="";
//    this.issueDateTimeToCbo=SIUtil.changeTo(lIssueDateTimeToCbo.trim(),this.encode);
//  }
  
  //setter of 対象期間Toの年
  public void setIssueDateTimeYearToCbo(String lIssueDateTimeYearToCbo){
    if (SIUtil.isNull(lIssueDateTimeYearToCbo)) lIssueDateTimeYearToCbo="";
    this.issueDateTimeYearToCbo=SIUtil.changeTo(lIssueDateTimeYearToCbo.trim(),this.encode);
  }
  
  //setter of 対象期間Toの月
  public void setIssueDateTimeMonthToCbo(String lIssueDateTimeMonthToCbo){
    if (SIUtil.isNull(lIssueDateTimeMonthToCbo)) lIssueDateTimeMonthToCbo="";
    this.issueDateTimeMonthToCbo=SIUtil.changeTo(lIssueDateTimeMonthToCbo.trim(),this.encode);
  }
  
  //setter of 対象期間Toの日
  public void setIssueDateTimeDayToCbo(String lIssueDateTimeDayToCbo){
    if (SIUtil.isNull(lIssueDateTimeDayToCbo)) lIssueDateTimeDayToCbo="";
    this.issueDateTimeDayToCbo=SIUtil.changeTo(lIssueDateTimeDayToCbo.trim(),this.encode);
  }
  
  //setter of 並べ替え1
  public void setFirstOrderSel(String lFirstOrderSel){
    if (SIUtil.isNull(lFirstOrderSel)) lFirstOrderSel="mallshopCode";
    this.firstOrderSel=SIUtil.changeTo(lFirstOrderSel.trim(),this.encode);
  }
  
  //setter of 並び順1
  public void setFirstOrderTxt(String lFirstOrderTxt){
    if (SIUtil.isNull(lFirstOrderTxt)) lFirstOrderTxt="0";
    this.firstOrderTxt=SIUtil.changeTo(lFirstOrderTxt.trim(),this.encode);
  }
  
  //setter of 並べ替え2
  public void setSecondOrderSel(String lSecondOrderSel){
    if (SIUtil.isNull(lSecondOrderSel)) lSecondOrderSel="custCode";
    this.secondOrderSel=SIUtil.changeTo(lSecondOrderSel.trim(),this.encode);
  }
  
  //setter of 並び順2
  public void setSecondOrderTxt(String lSecondOrderTxt){
    if (SIUtil.isNull(lSecondOrderTxt)) lSecondOrderTxt="0";
    this.secondOrderTxt=SIUtil.changeTo(lSecondOrderTxt.trim(),this.encode);
  }
  
  //setter of 並べ替え3
  public void setThirdOrderSel(String lThirdOrderSel){
    if (SIUtil.isNull(lThirdOrderSel)) lThirdOrderSel="issueDateTime";
    this.thirdOrderSel=SIUtil.changeTo(lThirdOrderSel.trim(),this.encode);
  }
  
  //setter of 並び順3
  public void setThirdOrderTxt(String lThirdOrderTxt){
    if (SIUtil.isNull(lThirdOrderTxt)) lThirdOrderTxt="0";
    this.thirdOrderTxt=SIUtil.changeTo(lThirdOrderTxt.trim(),this.encode);
  }
  
  //setter of 並び順
  public void setOrderBySQL(String lOrderBySQL){
    if (SIUtil.isNull(lOrderBySQL)) lOrderBySQL=" ORDER BY aa.mallShopCode ASC ,aa.custCode ASC,aa.issueDateTime ASC ";
    this.orderBySQL=lOrderBySQL;
  }
  
  //setter of 検索用ＳＱＬ文
  public void setConditionSQL(String lConditionSQL){
    if (SIUtil.isNull(lConditionSQL)) lConditionSQL="";
    this.conditionSQL=SIUtil.changeTo(lConditionSQL.trim(),this.encode);
  }
  
  //getter of ショップコード
  public String getMallShopCodeTxt(){
    return this.mallShopCodeTxt;
  }
  
  //getter of 顧客コード
  public String getCustCodeTxt(){
    return this.custCodeTxt;
  }
  
  //7.2.0 ST0540 修正 ここから
  //getter of 顧客名
  public String getCustNameTxt(){
    return this.custNameTxt;
  }
  //7.2.0 ST0540 修正 ここまで
  
  //getter of 発行種別
  public String getIssueFlgRdo(){
    return this.issueFlgRdo;
  }
  
  //getter of 有効フラグ
  public String getEnableFlgRdo(){
    return this.enableFlgRdo;
  }
  
  //getter of 対象期間To
  public String getIssueDateTimeFrom(){
    return SIDateTime.getDate(getIssueDateTimeYearFromCbo(),getIssueDateTimeMonthFromCbo(),getIssueDateTimeDayFromCbo());
  }
  
  //getter of 対象期間Fromの年
  public String getIssueDateTimeYearFromCbo(){
    return this.issueDateTimeYearFromCbo;
  }
  
  //getter of 対象期間Fromの月
  public String getIssueDateTimeMonthFromCbo(){
    return this.issueDateTimeMonthFromCbo;
  }
  
  //getter of 対象期間Fromの日
  public String getIssueDateTimeDayFromCbo(){
    return this.issueDateTimeDayFromCbo;
  }
  
  //getter of 対象期間To
  public String getIssueDateTimeTo(){
    return SIDateTime.getDate(getIssueDateTimeYearToCbo(),getIssueDateTimeMonthToCbo(),getIssueDateTimeDayToCbo());
  }
  
  //getter of 対象期間Toの年
  public String getIssueDateTimeYearToCbo(){
    return this.issueDateTimeYearToCbo;
  }
  
  //getter of 対象期間Toの月
  public String getIssueDateTimeMonthToCbo(){
    return this.issueDateTimeMonthToCbo;
  }
  
  //getter of 対象期間Toの日
  public String getIssueDateTimeDayToCbo(){
    return this.issueDateTimeDayToCbo;
  }
  
  //getter of 並べ替え1
  public String getFirstOrderSel(){
    return this.firstOrderSel;
  }
  
  //getter of 並び順1
  public String getFirstOrderTxt(){
    return this.firstOrderTxt;
  }
  
  //getter of 並べ替え2
  public String getSecondOrderSel(){
    return this.secondOrderSel;
  }
  
  //getter of 並び順2
  public String getSecondOrderTxt(){
    return this.secondOrderTxt;
  }
  
  //getter of 並べ替え3
  public String getThirdOrderSel(){
    return this.thirdOrderSel;
  }
  
  //getter of 並び順3
  public String getThirdOrderTxt(){
    return this.thirdOrderTxt;
  }
  
  //getter of 並び順
  public String getOrderBySQL(){
    return this.orderBySQL;
  }
  
  //getter of 検索用ＳＱＬ文
  public String getConditionSQL(){
    return this.conditionSQL;
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  //7.1.1 ST0236 修正 ここから
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam,String lCustName){//7.2.0 ST0540 修正
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    if (lLogin.isShop()){
      this.setMallShopCodeTxt(lLogin.getMallShopCode());//ショップコード
    }else{
      this.setMallShopCodeTxt((String)lUrlParam.getParam("mallShopCodeTxt"));//ショップコード
    }
    this.setCustCodeTxt((String)lUrlParam.getParam("custCodeTxt"));//顧客コード
    this.setCustNameTxt(lCustName);//顧客名 //7.2.0 ST540 修正
    this.setIssueFlgRdo((String)lUrlParam.getParam("issueFlgRdo"));//発行種別
    this.setEnableFlgRdo((String)lUrlParam.getParam("enableFlgRdo"));//有効フラグ
    this.setIssueDateTimeYearFromCbo((String)lUrlParam.getParam("issueDateTimeYearFromCbo"));//対象期間Fromの年
    this.setIssueDateTimeMonthFromCbo((String)lUrlParam.getParam("issueDateTimeMonthFromCbo"));//対象期間Fromの月
    this.setIssueDateTimeDayFromCbo((String)lUrlParam.getParam("issueDateTimeDayFromCbo"));//対象期間Fromの日
    this.setIssueDateTimeYearToCbo((String)lUrlParam.getParam("issueDateTimeYearToCbo"));//対象期間Toの年
    this.setIssueDateTimeMonthToCbo((String)lUrlParam.getParam("issueDateTimeMonthToCbo"));//対象期間Toの月
    this.setIssueDateTimeDayToCbo((String)lUrlParam.getParam("issueDateTimeDayToCbo"));//対象期間Toの日
    this.setFirstOrderSel((String)lUrlParam.getParam("firstOrderSel"));//並べ替え1
    this.setFirstOrderTxt((String)lUrlParam.getParam("firstOrderTxt"));//並び順1
    this.setSecondOrderSel((String)lUrlParam.getParam("secondOrderSel"));//並べ替え2
    this.setSecondOrderTxt((String)lUrlParam.getParam("secondOrderTxt"));//並び順2
    this.setThirdOrderSel((String)lUrlParam.getParam("thirdOrderSel"));//並べ替え3
    this.setThirdOrderTxt((String)lUrlParam.getParam("thirdOrderTxt"));//並び順3
  }
  //7.1.1 ST0236 修正 ここまで
  
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest,Connection lConnection){//7.2.0 ST0540 修正
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //ショップコード
    if (SIUtil.isNotNull(getMallShopCodeTxt())&& SICheckValid.checkValid(errors,"ショップコード",getMallShopCodeTxt(),SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","MallShopCode",getMallShopCodeTxt(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //顧客コード
    if (SIUtil.isNotNull(getCustCodeTxt())&& SICheckValid.checkValid(errors,"顧客コード",getCustCodeTxt(),SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","custCode",getCustCodeTxt(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //7.2.0 ST0540 追加 ここから
    //顧客コードが存在するかどうかをチェック
    if(!this.getCustCodeTxt().equals("")){
      String lSql = "SELECT CustName FROM CustTbl WHERE CustCode = " + SIDBUtil.SQL2Str(this.getCustCodeTxt());
      SICheckValid.checkExist(errors,lConnection,"顧客コード",lSql);
    }
    //7.2.0 ST0540 追加 ここまで
    
    //発行種別
    if (SIUtil.isNotNull(getIssueFlgRdo())){
      lConditionMan.add(new SITableCondition("aa","IssueFlg",getIssueFlgRdo(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //有効フラグ
    if (SIUtil.isNotNull(getEnableFlgRdo())){
      lConditionMan.add(new SITableCondition("aa","EnableFlg",getEnableFlgRdo(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //対象期間From
    if (SIUtil.isNotNull(getIssueDateTimeFrom())&&SICheckValid.checkValid(errors,"ポイント発行期間From",getIssueDateTimeFrom(),SICheckDataConf.SICHECK_DATA_DATE_TYPE)){//7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lConditionMan.add(new SITableCondition("aa","issueDateTime",getIssueDateTimeFrom(),SIConfig.SICONDITION_TYPE_GREATER_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }else{
        lConditionMan.add(new SITableCondition("aa","issueDateTime",new SIDateTimeType(getIssueDateTimeFrom()),SIConfig.SICONDITION_TYPE_GREATER_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    //対象期間To
    if (SIUtil.isNotNull(getIssueDateTimeTo())&&SICheckValid.checkValid(errors,"ポイント発行期間To",getIssueDateTimeTo(),SICheckDataConf.SICHECK_DATA_DATE_TYPE)){//7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lConditionMan.add(new SITableCondition("aa","issueDateTime",getIssueDateTimeTo()+" 23:59:59",SIConfig.SICONDITION_TYPE_LESS_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }else{
        lConditionMan.add(new SITableCondition("aa","issueDateTime",new SIDateTimeType(getIssueDateTimeTo()+" 23:59:59"),SIConfig.SICONDITION_TYPE_LESS_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    //受注日大小
    try{
      if(!SICheckUtil.dateEqual(this.getIssueDateTimeFrom(),this.getIssueDateTimeTo())){
        SICheckValid.checkValid(errors,"ポイント発行期間From","ポイント発行期間To",this.getIssueDateTimeFrom(),this.getIssueDateTimeTo(),SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);//7.1.1 ST0162 修正
      }
    }catch(Exception e){}
    
    if (!errors.isEmpty()){
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
      lConditionMan.add(new SITableCondition(" AND 1=2 "));
    }else{
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    }
    //条件文の設定
    this.conditionSQL=lConditionMan.getCondtionSQL();
    //並び順文の作成
    this.makeOrderBySQL();
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public String getAll(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    
    //基本のSQL
    lSqlBuf.append("SELECT COUNT(aa.POINTMANCODE) ");
    lSqlBuf.append("FROM (PointManMTbl aa LEFT OUTER JOIN ");
    lSqlBuf.append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(" dd ON aa.OrderCode=dd.OrderCode),");
    lSqlBuf.append("CustTbl bb,");
    lSqlBuf.append("MallShopMTbl cc ");
    lSqlBuf.append("WHERE aa.CustCode=bb.CustCode ");
    lSqlBuf.append("AND aa.MallShopCode=cc.MallShopCode ");
    
    if (lLogin.isShop())lSqlBuf.append("AND aa.MallShopCode='").append(lLogin.getMallShopCode()+"' ");
    //検索の条件
    lSqlBuf.append(this.conditionSQL);
    log.debug("getAll:lSqlBuf="+lSqlBuf.toString());
    
    return exec(lConnection,lSqlBuf.toString());
  }
  
  public String getGroupByShop(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    //基本のSQL
    lSqlBuf.append("SELECT COUNT(DISTINCT aa.MallShopCode) ");
    lSqlBuf.append("FROM PointManMTbl aa,");
    lSqlBuf.append("MallShopMTbl bb ");
    lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
    if (lLogin.isShop())lSqlBuf.append("AND aa.MallShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
    //検索の条件
    lSqlBuf.append(this.conditionSQL);
    log.debug("getGroupByShop:lSqlBuf="+lSqlBuf.toString());
    
    return exec(lConnection,lSqlBuf.toString());
  }
  
  public String getGroupByCust(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    //基本のSQL
    lSqlBuf.append("SELECT COUNT(DISTINCT aa.CustCode) ");
    lSqlBuf.append("FROM PointManMTbl aa,");
    lSqlBuf.append("CustTbl bb ");
    lSqlBuf.append("WHERE aa.CustCode=bb.CustCode ");
    if (lLogin.isShop())lSqlBuf.append("AND aa.MallShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
    //検索の条件
    lSqlBuf.append(this.conditionSQL);
    log.debug("getGroupByShop:lSqlBuf="+lSqlBuf.toString());
    return exec(lConnection,lSqlBuf.toString());
  }
  
  public String getGroupByComment(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    //基本のSQL
    lSqlBuf.append("SELECT COUNT(*) ");
    lSqlBuf.append("FROM PointManMTbl aa,");
    lSqlBuf.append("InvestPointLogTbl bb ");
    lSqlBuf.append("WHERE aa.PointManCode=bb.PointManCode ");
    if (lLogin.isShop())lSqlBuf.append("AND aa.MallShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
    //検索の条件
    lSqlBuf.append(this.conditionSQL);
    log.debug("getGroupByShop:lSqlBuf="+lSqlBuf.toString());
    return exec(lConnection,lSqlBuf.toString());
  }
  
  private String exec(Connection lConnection,String lSql)throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSql);
      if (lResultSet.next())return lResultSet.getString(1);
      else return "0";
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
  }
  
  /**
   * <b>makeOrderBySQL</b>
   * 並び順文の作成
   * @param なし
   * @return なし
   * @throws なし
   */
  public void makeOrderBySQL(){
    StringBuffer lOrdBuf=new StringBuffer();
    int def=0;
    
    //第一の並び順
    try{def=Integer.parseInt(getFirstOrderTxt());}catch(Exception e){def=0;}
    this.setFirstOrderTxt(String.valueOf(def));
    lOrdBuf.append(" ORDER BY aa.").append(getFirstOrderSel()+" ").append(SIConfig.SIORDER_NAME[def]);
    //第二の並び順
    try{def=Integer.parseInt(getSecondOrderTxt());}catch(Exception e){def=0;}
    this.setSecondOrderTxt(String.valueOf(def));
    lOrdBuf.append(",aa.").append(getSecondOrderSel()+" ").append(SIConfig.SIORDER_NAME[def]);
    //第三の並び順
    try{def=Integer.parseInt(getThirdOrderTxt());}catch(Exception e){def=0;}
    this.setThirdOrderTxt(String.valueOf(def));
    lOrdBuf.append(",aa.").append(getThirdOrderSel()+" ").append(SIConfig.SIORDER_NAME[def]);
    
    log.debug("makeOrderBySQL:lOrdBuf="+lOrdBuf.toString());
    this.setOrderBySQL(lOrdBuf.toString());
  }
}