/**
 * 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 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.SIDuplicateKeyException;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SISaleList,v 1.0 2003/12/15 Exp $
 * @author  yamauchi
* <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>
 * yamauchi   2003/12/15    Original
 */
public class SISaleList extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ポイント管理番号
  private String pointManCode="";
  
  //顧客コード
  private String custCode="";
  
  //管理コード
  private String mallShopCode="";
  
  //発行種別フラグ
  private String issueFlg="0";
  
  //発行／使用ポイント数
  private String pointOfIssue="0";
  
  //有効フラグ
  private String enableFlg="1";
  
  //受注番号
  private String orderCode="";
  
  //レビューコード
  private String reviewCode="";
  
  //日時
  private String issueDateTime="";
  
  public SISaleList(){}
  
//  public SIPointMan(String lPointManCode){
//    setPointManCode(lPointManCode);
//  }
  
  //setter of ポイント管理番号
  public void setPointManCode(String lPointManCode){
    if (SIUtil.isNull(lPointManCode)) lPointManCode="";
    this.pointManCode=SIUtil.changeTo(lPointManCode.trim(),this.encode);
  }
  
  //setter of 顧客コード
  public void setCustCode(String lCustCode){
    if (SIUtil.isNull(lCustCode)) lCustCode="";
    this.custCode=SIUtil.changeTo(lCustCode.trim(),this.encode);
  }
  
  //setter of 管理コード
  public void setMallShopCode(String lMallShopCode){
    if (SIUtil.isNull(lMallShopCode)) lMallShopCode="";
    this.mallShopCode=SIUtil.changeTo(lMallShopCode.trim(),this.encode);
  }
  
  //setter of 発行種別フラグ
  public void setIssueFlg(String lIssueFlg){
    if (SIUtil.isNull(lIssueFlg)) lIssueFlg="0";
    this.issueFlg=SIUtil.changeTo(lIssueFlg.trim(),this.encode);
  }
  
  //setter of 発行／使用ポイント数
  public void setPointOfIssue(String lPointOfIssue){
    if (SIUtil.isNull(lPointOfIssue)) lPointOfIssue="0";
    this.pointOfIssue=SIUtil.changeTo(lPointOfIssue.trim(),this.encode);
  }
  
  //setter of 有効フラグ
  public void setEnableFlg(String lEnableFlg){
    if (SIUtil.isNull(lEnableFlg)) lEnableFlg="1";
    this.enableFlg=SIUtil.changeTo(lEnableFlg.trim(),this.encode);
  }
  
  //setter of 受注番号
  public void setOrderCode(String lOrderCode){
    if (SIUtil.isNull(lOrderCode)) lOrderCode="";
    this.orderCode=SIUtil.changeTo(lOrderCode.trim(),this.encode);
  }
  
  //setter of レビューコード
  public void setReviewCode(String lReviewCode){
    if (SIUtil.isNull(lReviewCode)) lReviewCode="";
    this.reviewCode=SIUtil.changeTo(lReviewCode.trim(),this.encode);
  }
  
  //setter of 日時
  public void setIssueDateTime(String lIssueDateTime){
    if (SIUtil.isNull(lIssueDateTime)) lIssueDateTime="";
    this.issueDateTime=SIUtil.changeTo(lIssueDateTime.trim(),this.encode);
  }
  
  //getter of ポイント管理番号
  public String getPointManCode(){
    return this.pointManCode;
  }
  
  //getter of 顧客コード
  public String getCustCode(){
    return this.custCode;
  }
  
  //getter of 管理コード
  public String getMallShopCode(){
    return this.mallShopCode;
  }
  
  //getter of 発行種別フラグ
  public String getIssueFlg(){
    return this.issueFlg;
  }
  
  //getter of 発行／使用ポイント数
  public String getPointOfIssue(){
    return this.pointOfIssue;
  }
  
  //getter of 有効フラグ
  public String getEnableFlg(){
    return this.enableFlg;
  }
  
  //getter of 受注番号
  public String getOrderCode(){
    return this.orderCode;
  }
  
  //getter of レビューコード
  public String getReviewCode(){
    return this.reviewCode;
  }
  
  //getter of 日時
  public String getIssueDateTime(){
    return this.issueDateTime;
  }
  
  public static void clear(Connection lConnection,String lCustCode){
    SIModifyRec lModRec=new SIModifyRec("PointManMTbl");
    SIDateTime lDateTime = new SIDateTime();
    StringBuffer lSqlBuf=new StringBuffer();
    String lNow=lDateTime.getFullDateTime();
    
    lSqlBuf.append("SELECT aa.CustCode,aa.MallShopCode,MAX(aa.IssueDateTime) AS MaxIssueDateTime,");
    lSqlBuf.append("COALESCE((SELECT Period FROM PointShopMTbl WHERE MallShopCode=aa.MallShopCode),0) AS Period ");
    lSqlBuf.append("FROM PointManMTbl aa ");
    lSqlBuf.append("WHERE EnableFlg='1' ");
    if (SIUtil.isNotNull(lCustCode)){
      lSqlBuf.append("AND aa.CustCode=").append(SIDBUtil.SQL2Str(lCustCode," "));
    }
    
    lSqlBuf.append("GROUP BY aa.CustCode,aa.MallShopCode ");
    log.debug("clear:lSqlBuf(1)="+lSqlBuf);
    
    Statement lStatement=null;
    ResultSet lResultSet=null;
    
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      while (lResultSet.next()){
        lSqlBuf=new StringBuffer();
        if (SIDBMultiConf.SIDB_CURRENT_INX==SIDBMultiConf.SIDB_POSTGRESQL_INX){
          lSqlBuf.append("SELECT ('").append(lResultSet.getString("MaxIssueDateTime"));
          lSqlBuf.append("',INTERVAL '").append(lResultSet.getString("Period"));
          lSqlBuf.append(" month') OVERLAPS (DATE ").append(lDateTime.getFullDateTime()).append(",DATE ").append(lDateTime.getFullDateTime()).append(")");
        }else{
          String lMaxDateTime=SIDBUtil.getDateTime(lResultSet.getTimestamp("MaxIssueDateTime"));
          lSqlBuf.append("SELECT CASE WHEN ADD_MONTHS(").append(new SIDateTimeType(lMaxDateTime).getValue());
          lSqlBuf.append(",").append(lResultSet.getString("Period")).append(")<");
          lSqlBuf.append(new SIDateTimeType(lNow).getValue());
          lSqlBuf.append(" THEN 'true' ELSE 'false' END  FROM DUAL");
        }
        
        log.debug("clear:lSqlBuf(2)="+lSqlBuf);
        try {
          if (SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()).startsWith("f")){
            lModRec.addCondition("CustCode",lResultSet.getString("CustCode"));
            lModRec.addCondition("MallShopCode",lResultSet.getString("MallShopCode"));
            lModRec.addCondition("EnableFlg","1");
            lModRec.add("EnableFlg","0");
            lModRec.execute(lConnection);
          }
        } catch (SIDBAccessException e) {
          e.printStackTrace();
        }catch (SIDuplicateKeyException e1) {
          e1.printStackTrace();
        }
      }
    }catch(SQLException sqle){
      sqle.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    try{lConnection.commit();}catch(Exception e){e.printStackTrace();}
  }
  
  public static void clear(Connection lConnection){
    clear(lConnection,null);
  }
}