/**
 * 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.Statement;
import java.util.ArrayList;
import java.util.Collection;

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

import org.apache.log4j.Category;

/**
 * @version $Id: SICust.java,v 1.0 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/12  Original
 */
public class SIDeliveryHist extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  /*---CustAddressTbl---*/
  //顧客コード
  private String custCode="";
  
  //アドレス帳番号
  private String addressCode="";
  
  //配送先呼称
  private String deliveryName="";
  
  //配送先正式名
  private String deliveryAddressee="";
  
  //住所1
  private String address1="";
  
  //住所2
  private String address2="";
  
  //住所3
  private String address3="";
  
  /*--OrderTbl---*/
  //受注番号
  private String orderCode="";
  
  //受注日
  private String initDateTime="";
  
  /*---OrderDeliveryTbl---*/
  //出荷日
  private String shippmentDate="";
  
  /*---OrderDetailTbl---*/
  //商品名
  private String cmdtyName="";
  
  //数量
  private String amount="";
  
  //購入価格
  private String orgPrice="";
  
  public SIDeliveryHist(){
  }
  
  /*---CustAddressTbl---*/
  //setter of 顧客コード
  public void setCustCode(String lCustCode){
    if (SIUtil.isNull(lCustCode)) lCustCode="";
    this.custCode=SIUtil.changeTo(lCustCode.trim(),this.encode);
  }
  
  //setter of アドレス帳番号
  public void setAddressCode(String lAddressCode){
    if (SIUtil.isNull(lAddressCode)) lAddressCode="";
    this.addressCode=SIUtil.changeTo(lAddressCode.trim(),this.encode);
  }
  
  //setter of 配送先呼称
  public void setDeliveryName(String lDeliveryName){
    if (SIUtil.isNull(lDeliveryName)) lDeliveryName="";
    this.deliveryName=SIUtil.changeTo(lDeliveryName.trim(),this.encode);
  }
  
  //setter of 配送先正式名
  public void setDeliveryAddressee(String lDeliveryAddressee){
    if (SIUtil.isNull(lDeliveryAddressee)) lDeliveryAddressee="";
    this.deliveryAddressee=SIUtil.changeTo(lDeliveryAddressee.trim(),this.encode);
  }
  
  //setter of 住所1
  public void setAddress1(String lAddress1){
    if (SIUtil.isNull(lAddress1)) lAddress1="";
    this.address1=SIUtil.changeTo(lAddress1.trim(),this.encode);
  }
  
  //setter of 住所2
  public void setAddress2(String lAddress2){
    if (SIUtil.isNull(lAddress2)) lAddress2="";
    this.address2=SIUtil.changeTo(lAddress2.trim(),this.encode);
  }
  
  //setter of 住所3
  public void setAddress3(String lAddress3){
    if (SIUtil.isNull(lAddress3)) lAddress3="";
    this.address3=SIUtil.changeTo(lAddress3.trim(),this.encode);
  }
  
  /*---OrderTbl---*/
  public void setOrderCode(String lOrderCode){
    if (SIUtil.isNull(lOrderCode)) lOrderCode="";
    this.orderCode=SIUtil.changeTo(lOrderCode.trim(),this.encode);
  }
  
  public void setInitDateTime(String lInitDateTime){
    if (SIUtil.isNull(lInitDateTime)) lInitDateTime="";
    this.initDateTime=SIUtil.changeTo(lInitDateTime.trim(),this.encode);
  }
  
  /*---OrderDeliveryTbl---*/
  //setter of 出荷日
  public void setShippmentDate(String lShippmentDate){
    if (SIUtil.isNull(lShippmentDate)) lShippmentDate="";
    this.shippmentDate=SIUtil.changeTo(lShippmentDate.trim(),this.encode);
  }
  
  /*---OrderDetailTbl---*/
  //setter of 商品名
  public void setCmdtyName(String lCmdtyName){
    if (SIUtil.isNull(lCmdtyName)) lCmdtyName="";
    this.cmdtyName=SIUtil.changeTo(lCmdtyName.trim(),this.encode);
  }
  
  //setter of 数量
  public void setAmount(String lAmount){
    if (SIUtil.isNull(lAmount)) lAmount="";
    this.amount=SIUtil.changeTo(lAmount.trim(),this.encode);
  }
  
  //setter of 購入価格
  public void setOrgPrice(String lOrgPrice){
    if (SIUtil.isNull(lOrgPrice)) lOrgPrice="";
    this.orgPrice=SIUtil.changeTo(lOrgPrice.trim(),this.encode);
  }
  
  //getter of 顧客コード
  public String getCustCode(){
    return this.custCode;
  }
  
  //getter of アドレス帳番号
  public String getAddressCode(){
    return this.addressCode;
  }
  
  //getter of 配送先正式名
  public String getDeliveryName(){
    return this.deliveryName;
  }
  
  //getter of 配送先正式名
  public String getDeliveryAddressee(){
    return this.deliveryAddressee;
  }
  
  //getter of 住所1
  public String getAddress1(){
    return this.address1;
  }
  
  //getter of 住所2
  public String getAddress2(){
    return this.address2;
  }
  
  //getter of 住所3
  public String getAddress3(){
    return this.address3;
  }
  
  /*---OrderCode---*/
  public String getOrderCode(){
    return this.orderCode;
  }
  
  public String getInitDateTime(){
    return this.initDateTime;
  }
  
  /*---OrderDeliveryTbl---*/
  //getter of 出荷日
  public String getShippmentDate(){
    return this.shippmentDate;
  }
  
  /*---OrderDetailTbl---*/
  //geter of 商品名
  public String getCmdtyName(){
    return this.cmdtyName;
  }
  
  //getter of 数量
  public String getAmount(){
    return this.amount;
  }
  
  //getter of 購入価格
  public String getOrgPrice(){
    return this.orgPrice;
  }
  
  public String getSum(){
    return String.valueOf(Integer.parseInt(this.amount) * Integer.parseInt(this.orgPrice) );
  }
  
  public String getAddress(){
    return getAddress1()+getAddress2()+getAddress3();
  }
  
  /**
   * <b>getCollection</b>
   * 配送先ごとの配送履歴をコレクションで返します
   * @param  Connection
   * @param  lCustCode     顧客コード
   * @param  lAddressCode  アドレス帳コード
   * @return Collection 配送先履歴
   * @throws なし
   */
  public static Collection getCollection(Connection lConnection,String lCustCode, String lAddressCode){
    Statement lStatement=null;
    ResultSet lResultSet=null;
    
    Collection lResultColl=new ArrayList();
    SIDeliveryHist lHist = new SIDeliveryHist();
    StringBuffer lSqlBuf=new StringBuffer();
    StringBuffer lSqlBuf0=new StringBuffer();
    
    lSqlBuf0.append(" SELECT DeliveryName, DeliveryAddressee, Address1, Address2, Address3 FROM CustAddressTbl");
    lSqlBuf0.append(" WHERE CustCode="+SIDBUtil.SQL2Str(lCustCode));
    lSqlBuf0.append(" AND AddressCode="+SIDBUtil.SQL2Str(lAddressCode));
    
    try {
      log.debug("getCollection:lSqlBuf="+lSqlBuf0.toString());
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf0.toString());
      while(lResultSet.next()){
        lHist=new SIDeliveryHist();
        lHist.setEncode(SIConfig.SIENCODE_NONE);
        lHist.setDeliveryName(lResultSet.getString("DeliveryName"));//配送先正式名
        lHist.setDeliveryAddressee(lResultSet.getString("DeliveryAddressee"));//配送先正式名
        lHist.setAddress1(lResultSet.getString("Address1"));//住所３
        lHist.setAddress2(lResultSet.getString("Address2"));//住所２
        lHist.setAddress3(lResultSet.getString("Address3"));//住所３
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    
    lSqlBuf.append("SELECT B.ShippmentDate AS ShippmentDate,O.OrderCode,O.InitDateTime,C.CmdtyName AS CmdtyName, C.Amount AS Amount, C.OrgPrice AS OrgPrice ");
    //7.2.0 ST1030 修正ここから
    lSqlBuf.append("FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("O,");
    lSqlBuf.append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("B,");
    lSqlBuf.append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("C ");
    //lSqlBuf.append("FROM OrderTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("O,");
    //lSqlBuf.append("OrderDeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("B,");
    //lSqlBuf.append("OrderDetailTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("C ");
    //7.2.0 ST1030 修正ここまで
    lSqlBuf.append("WHERE O.CustCode="+SIDBUtil.SQL2Str(lCustCode));
    //7.2.1 ST2006 修正ここから
    lSqlBuf.append(" AND B.AddressCode="+SIDBUtil.SQL2Str(lAddressCode));
    //lSqlBuf.append(" AND B.DeliveryAddressee="+SIDBUtil.SQL2Str(lHist.getDeliveryAddressee()));
    //7.2.1 ST2006 修正ここまで
    lSqlBuf.append(" AND B.OrderCode=O.OrderCode ");
    lSqlBuf.append(" AND C.OrderCode=O.OrderCode ");
    lSqlBuf.append(" AND C.OrderCode=B.OrderCode ");
    lSqlBuf.append(" AND C.DeliveryCode=B.DeliveryCode ");
    lSqlBuf.append(" ORDER BY B.ShippmentDate DESC ,O.OrderCode DESC ");
    
    try {
      log.debug("getCollection:lSqlBuf="+lSqlBuf.toString());
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      while(lResultSet.next()){
        lHist=new SIDeliveryHist();
        lHist.setEncode(SIConfig.SIENCODE_NONE);
        lHist.setShippmentDate(SIDBUtil.getDate(lResultSet.getTimestamp("ShippmentDate")));//出荷日
        lHist.setOrderCode(lResultSet.getString("OrderCode"));//受注番号
        lHist.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));//受注日
        lHist.setCmdtyName(lResultSet.getString("CmdtyName"));//商品名
        lHist.setAmount(lResultSet.getString("Amount"));//数量
        lHist.setOrgPrice(lResultSet.getString("OrgPrice"));//単価
        lResultColl.add(lHist);
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    
    return lResultColl;
  }
}