/**
 * 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.SIDeleteRec;//7.2.0 ST1030
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: SIPointMan.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 10:56:43  Original
 */
public class SIPointMan 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 SIPointMan(){
  }
  
  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;
  }
  
  /**
   * <b>reset</b>
   * SQLからポイント情報を取得しセッターに格納
   * @param Connection
   * @return boolean 結果が存在するかどうか
   * @throws なし
   */
  public boolean reset(Connection lConnection){
    boolean lResult=false;
    if (SIUtil.isNull(getPointManCode())) return lResult;
    
    Statement lStatement=null;
    ResultSet lResultSet=null;
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append("SELECT * FROM PointManMTbl ");
    lSqlBuf.append("WHERE PointManCode="+SIDBUtil.SQL2Str(getPointManCode()));
    
    log.debug("reset:lSqlBuf="+lSqlBuf.toString());
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      if (lResultSet.next()){
        this.setEncode(SIConfig.SIENCODE_NONE);
        this.setPointManCode(lResultSet.getString("pointManCode"));//ポイント管理番号
        this.setCustCode(lResultSet.getString("custCode"));//顧客コード
        this.setMallShopCode(lResultSet.getString("mallShopCode"));//管理コード
        this.setIssueFlg(lResultSet.getString("issueFlg"));//発行種別フラグ
        this.setPointOfIssue(lResultSet.getString("pointOfIssue"));//発行／使用ポイント数
        this.setEnableFlg(lResultSet.getString("enableFlg"));//有効フラグ
        this.setOrderCode(lResultSet.getString("orderCode"));//受注番号
        this.setReviewCode(lResultSet.getString("reviewCode"));//レビューコード
        this.setIssueDateTime(SIDBUtil.getDate(lResultSet.getTimestamp("issueDateTime")));//日時
        lResult=true;
      }else {
        log.error("not find record for pointManCode="+getPointManCode());
      }
    }catch(SQLException sqle){
      sqle.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lResult;
   }
  
   /**
    * <b>clear</b>
    *
    * @param Connection
    * @param lCustCode 顧客コード
    * @throws なし
    */
  public static void clear(Connection lConnection,String lCustCode){
    StringBuffer lSqlBuf=new StringBuffer();
    SIDateTime lDateTime = new SIDateTime();
    String lNow=lDateTime.getFullDateTime();
    
    lSqlBuf.append("SELECT aa.CustCode,MAX(aa.IssueDateTime) AS MaxIssueDateTime,");
    lSqlBuf.append("COALESCE((SELECT Period FROM PointShopMTbl WHERE MallShopCode='0'),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 ");
    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();
        lSqlBuf.append("SELECT ").append(SIDBUtil.SQL2Str(lResultSet.getString("MaxIssueDateTime"),"::TIMESTAMP "));
        lSqlBuf.append("+ ").append(SIDBUtil.SQL2Str(lResultSet.getString("Period") + " month","::INTERVAL "));
        lSqlBuf.append("> ").append(SIDBUtil.SQL2Str(lNow)).append("::TIMESTAMP");
        
        log.debug("clear:lSqlBuf(2)="+lSqlBuf);
        try {
          if (SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()).startsWith("f")){
            SIModifyRec lModRec=new SIModifyRec("PointManMTbl");
            lModRec.addCondition("CustCode",lResultSet.getString("CustCode"));
            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();}
  }
  
  /**
   * <b>clear</b>
   * 全ての顧客に対して、ポイント有効期限の判定を行います
   * @param Connection
   * @throws なし
   */
  public static void clear(Connection lConnection) {
    SIModifyRec  lModRec = new SIModifyRec();
    StringBuffer lSqlBuf = new StringBuffer();
    
    try {
      //ポイント使用期限（月数）取得
      String limitMonths = "0";
      lSqlBuf.append("SELECT COALESCE(Period, 0) FROM PointShopMTbl WHERE MallShopCode='0'");
      limitMonths = SIDBUtil.getFirstData(lConnection, lSqlBuf.toString());
      
      lSqlBuf = new StringBuffer();
      lSqlBuf.append("UPDATE PointManMTbl SET EnableFlg = '0'");
      lSqlBuf.append(" WHERE EnableFlg = '1' AND CustCode IN (");
      lSqlBuf.append("  SELECT aa.CustCode FROM PointManMTbl aa");
      lSqlBuf.append("  GROUP BY aa.CustCode");
      lSqlBuf.append("  HAVING MAX(aa.IssueDateTime) + '").append(limitMonths).append(" months' < NOW()");
      lSqlBuf.append(" )");
      
      log.debug("updatePointEnableFlg:lSqlBuf="+lSqlBuf.toString());
      lModRec.execute(lConnection, lSqlBuf.toString());
    } catch (SIDBAccessException e1) {
      e1.printStackTrace();
    } catch (SIDuplicateKeyException e2) {
      e2.printStackTrace();
    }
    try{lConnection.commit();}catch(Exception e){e.printStackTrace();}
  }
  
  /**
   * <b>getMaxPoint</b>
   * ある顧客に対するポイント残高の計算
   * @param lConnection DBへのコネンクション
   * @param lCustCode 顧客コード
   * @lMallShopCode モールショップコード
   * @return ポイント残高
   * @throws なし
   */
  public static String getMaxPoint(Connection lConnection,String lCustCode,String lMallShopCode){
    String lRes="0";
    
    //ポイント残高の計算のために
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append("SELECT CASE WHEN SUM(PointOfIssue)<0 THEN 0 ELSE SUM(PointOfIssue) END AS Point ");
    lSqlBuf.append("FROM PointManMTbl ");
    lSqlBuf.append("WHERE EnableFlg='1' AND CustCode=").append(SIDBUtil.SQL2Str(lCustCode," "));
    
    log.debug("getMaxPoint:lSqlBuf="+lSqlBuf.toString());
    
    try {
      lRes=SIDBUtil.getFirstData(lConnection,lSqlBuf.toString());//最大のポイント使用額
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    
    return lRes;
  }
  
  /**
   * 入金、出荷時のポイント管理
   * @param lConnection
   * @param OrderCode
   * @throws SQLException
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void changeEnableFlg(Connection lConnection,String OrderCode) throws SQLException, SIDuplicateKeyException, SIDBAccessException{
    StringBuffer lSqlBuf1 = new StringBuffer();
    lSqlBuf1.append("SELECT * FROM OrderLatestVW ");
    lSqlBuf1.append("WHERE receiptdate IS NULL ");
    lSqlBuf1.append("AND ordercode=").append(SIDBUtil.SQL2Str(OrderCode));
    
    StringBuffer shippCntSql = new StringBuffer();
    shippCntSql.append("SELECT count(*) FROM OrderDetailLatestVW ");
    shippCntSql.append("WHERE shippmentdate IS NULL ");
    shippCntSql.append("AND ordercode=").append(SIDBUtil.SQL2Str(OrderCode));
    String shippCnt = SIDBUtil.getFirstData(lConnection,shippCntSql.toString());
    
    log.debug("changeEnableFlg:lSqlBuf1="+lSqlBuf1.toString());
    Statement lStatement1=lConnection.createStatement();
    ResultSet lResultSet1=lStatement1.executeQuery(lSqlBuf1.toString());
    
    SIModifyRec lModRec=new SIModifyRec();
    StringBuffer lSqlBuf =new StringBuffer();
    
    //PointOfIssue<0は使用したポイント。
    
    if(lResultSet1.next() || Integer.parseInt(shippCnt)>0){//未入金又は未出荷がある状態
      lSqlBuf.append("UPDATE PointManMTbl SET EnableFlg = '2' ");
      lSqlBuf.append("WHERE EnableFlg = '1' ");
      lSqlBuf.append("AND PointOfIssue >= 0 ");
      lSqlBuf.append("AND OrderCode=").append(SIDBUtil.SQL2Str(OrderCode));
    }else{//未入金、未出荷がなくなった時点でポイント使用可能
      lSqlBuf.append("UPDATE PointManMTbl SET EnableFlg='1' ,IssueDateTime=current_timestamp ");
      lSqlBuf.append("WHERE EnableFlg = '2' ");
      lSqlBuf.append("AND PointOfIssue >= 0 ");
      lSqlBuf.append("AND OrderCode=").append(SIDBUtil.SQL2Str(OrderCode));
    }
    log.debug("changeEnableFlg:lSqlBuf="+lSqlBuf.toString());
    lModRec.execute(lConnection,lSqlBuf.toString());
    SIDBUtil.close( lStatement1,lResultSet1);
  }
  
  /**
   * ポイントを無効にする。
   * @param lConnection
   * @param OrderCode
   * @throws SQLException
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void pointInvalid(Connection lConnection,String OrderCode) throws SQLException, SIDuplicateKeyException, SIDBAccessException{
    SIModifyRec lModRec=new SIModifyRec("PointManMTbl");
    lModRec.addCondition("OrderCode",OrderCode);
    lModRec.add("EnableFlg","0");
    lModRec.execute(lConnection);
  }
  
  /**
   * ポイントを削除する。
   * @param lConnection
   * @param OrderCode
   * @throws SQLException
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void pointDelete(Connection lConnection,String OrderCode) throws SQLException, SIDuplicateKeyException, SIDBAccessException {
    SIDeleteRec lRec = new SIDeleteRec();
    lRec=new SIDeleteRec("PointManMTbl");
    lRec.addCondition("OrderCode", OrderCode);//受注番号
    lRec.execute(lConnection);
  }
}