package com.*.*.thread.polling.domain;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.*.*.database.DBUtil;
import com.*.*.fwk.base.*BOImpl;
import com.*.*.util.PropertyReader;
import com.*.*.util.log.Logger;
import com.*.exception.DuplicateKeyException;
import com.*.exception.EncryptionException;
import com.*.exception.RecordNotFoundException;
import com.*.util.CharTurn;

 

public class PartenerSynPollingBO extends *BOImpl {
 
 //远程连接
 Connection remoteConn =null;

 private final static String LIST_REMOTE_PARTENER =
  " SELECT "
  +" SUP_ID,"
  +" SUP_CODE,"
  +" NAME,"
  +" REPRESENTER,"
  +" ADDRESS,"
  +" EMAIL,"
  +" FAX,"
  +" PHONE "
  +" FROM PMS.SUP_SUPPLIER_VIEW  ";
//  +" WHERE TYPE IN(1,2,3,4) AND SUP_LEVEL IN(1,2,3)" ;
 

    private static String s_par_db_UserName = "";
    private static String s_par_db_Password = "";
    private static String s_par_db_url = "";
    private static String s_par_db_driver= "";


    static {
        try {
            PropertyReader pr = new PropertyReader();
            s_par_db_UserName = pr.getProperty("PAR_DB_USERNAME");
            s_par_db_Password = CharTurn.paraDecrypt(pr.getProperty("PAR_DB_PASSWORD"));
            s_par_db_driver = pr.getProperty("DRIVER_PAR");
            s_par_db_url = pr.getProperty("PAR_DB_URL"); ;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (EncryptionException e) {
            e.printStackTrace();
        }
    }
 
 
 
 
 /**
  * @param args
  */
 public static void main(String[] args) throws IOException,
 SQLException, RecordNotFoundException {
  PartenerSynPollingBO partenerSynPollingBO = new PartenerSynPollingBO();
  try {
   partenerSynPollingBO.polling();
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
 }
 
   


    /**
     * 帐号生命周期轮询
     * @throws Exception
     * @throws RecordNotFoundException 记录无法找到
     * @throws DuplicateKeyException 重复主键
     */

    public void polling() throws Exception {
     System.out.println("开始同步厂商库 "+Calendar.getInstance().getTime());
     remoteConn = PartenerSynPollingBO.getRemoteConnection();
     PreparedStatement remotePstmt=null;
     ResultSet remoteRs =null;
     //本地连接
     Connection conn =null;
     conn = DBUtil.getConnection();
     
     remotePstmt = remoteConn.prepareStatement(LIST_REMOTE_PARTENER);
     remoteRs = remotePstmt.executeQuery();

     HashSet allParIdset= new HashSet();

     
     if(remoteConn!=null){
     
     
        try {
         int i=0;
         while(remoteRs.next()){
                //变量
          Integer sup_id = Integer.valueOf(String.valueOf(remoteRs.getInt("SUP_ID")));
          String sup_code = String.valueOf(remoteRs.getString("SUP_CODE")==null?"":remoteRs.getString("SUP_CODE"));
                String name = String.valueOf(remoteRs.getString("NAME")==null?"":remoteRs.getString("NAME"));
                String address = String.valueOf(remoteRs.getString("ADDRESS")==null?"":remoteRs.getString("ADDRESS"));
                String phone  = String.valueOf(remoteRs.getString("PHONE")==null?"":remoteRs.getString("PHONE"));
                String email =  String.valueOf(remoteRs.getString("EMAIL")==null?"":remoteRs.getString("EMAIL"));
                String representer = String.valueOf(remoteRs.getString("REPRESENTER")==null?"":remoteRs.getString("REPRESENTER"));
                String fax =  String.valueOf(remoteRs.getString("FAX")==null?"":remoteRs.getString("FAX"));
          //统计SUP_ID
                allParIdset.add(remoteRs.getObject("SUP_ID"));
               
                //取公司邮箱后缀
                String mailDomain ="";
                if(email!=null && !"".equals(email)){
                 int index = email.lastIndexOf("@");
                 if(index!=-1){
                  mailDomain = email.substring(index+1); 
                 }
                }
               
                PreparedStatement localPstmt=null;
             if(!PartenerSynPollingBO.isExist("t_ap_partener_syn", "SUP_ID", String.valueOf( sup_id ))){
                 System.out.println("---正在添加id为: "+ sup_id +" 的厂商---");
              /** 往厂商中间表插入数据 */

              localPstmt = conn.prepareStatement("insert into t_ap_partener_syn(SUP_ID,SUP_CODE,NAME,REPRESENTER,ADDRESS," +
                "EMAIL,FAX,PHONE,UPDATETIME, MAILDOMAIN) "
                + "values(?,?,?,?,?,?,?,?,SYSDATE,?)");
              
              localPstmt.setInt(1,Integer.parseInt(sup_id.toString()));  
              localPstmt.setString(2,sup_code); 
              localPstmt.setString(3,name); 
              localPstmt.setString(4,representer); 
              localPstmt.setString(5,address); 
              localPstmt.setString(6,email); 
              localPstmt.setString(7,fax);
              localPstmt.setString(8,phone);
              localPstmt.setString(9,mailDomain);
  
              localPstmt.executeUpdate();
              
             }else{
              //继续判断厂商记录是否被修改
              String sql = "select sup_id from t_ap_partener_syn where SUP_ID = "+sup_id ;
              
              //address进行特殊处理
              if(address!=null && !"".equals(address.trim())){ 
               address = address.replace("\'","\"" );
              }
              
              String condition = " and ( SUP_CODE!= '"+ sup_code + "' or name!= '"+ name+ "' or REPRESENTER!= '"+ representer
              + "' or ADDRESS!= '"+ address+ "' or EMAIL!= '"+ email+ "' or fax!= '"+ fax+ "' or mailDomain!= '"+ mailDomain
              + "' or phone!= '"+ phone+"' )";
              
              localPstmt = conn.prepareStatement(sql+condition);
              ResultSet rs_p = localPstmt.executeQuery();
              if(rs_p.next()){
               
               System.out.println("---正在修改id为: "+ sup_id +" 的厂商---");
               
               
               localPstmt = conn.prepareStatement("update T_AP_PARTENER_SYN set SUP_CODE=?,NAME=?,REPRESENTER=?,ADDRESS=?," +
                 " updatetime=sysdate,EMAIL=?, FAX=?,PHONE=?,mailDomain=?  where SUP_ID = ? ");
               
                  localPstmt.setString(1,sup_code); 
                  localPstmt.setString(2,name); 
                  localPstmt.setString(3,representer); 
                  localPstmt.setString(4,address); 
                  localPstmt.setString(5,email); 
                  localPstmt.setString(6,fax);
                  localPstmt.setString(7,phone);
                  localPstmt.setString(8,mailDomain);
                  localPstmt.setInt(9,Integer.parseInt(sup_id.toString()));  
                  localPstmt.executeUpdate();

              }
             }
               
                if(localPstmt!=null){
                 localPstmt.close();
                }
            

         i++;
         }
          
         //删除已不存在厂商记录
         deletePartenerRecord("T_AP_PARTENER_SYN", "SUP_ID" , allParIdset);
         
         
         System.out.print("结束同步厂商库 " +Calendar.getInstance().getTime());

        } catch (Exception ex) {
            throw ex;
        }finally{
         
         if(conn!=null){
          try {
           conn.close();
          } catch (SQLException e) {
           e.printStackTrace();
          }
         }
         
         if(remoteConn!=null){
          try {
           remoteConn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
         }
         
        }
       
       
       
     }else{
      System.out.println("连接数据库失败!");
     }
       
       
    }
   

    /**
     * 获得数据库连接
     * @return
     */
    public static Connection getRemoteConnection(){
        try {
            Connection conn = null;
            conn = DBUtil.getConnection(s_par_db_driver, s_par_db_url, s_par_db_UserName, s_par_db_Password);
            if(conn == null){
                throw new SQLException("Get partener db conncetion failed.");
            }
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }
   

   
    /** 通过关键字判断记录是否存在 */
    public static boolean isExist(String tableName, String colName, String value){
     Connection conn=null;
     PreparedStatement pstmt=null;
     try {
      conn = DBUtil.getConnection();
       pstmt = conn.prepareStatement("select "+colName +" from "+tableName + " where " + colName  +" = " +value );
       ResultSet rs= pstmt.executeQuery();
       if(rs.next()){
        return true;
       }
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }finally{
   if(pstmt!=null){
    try {
     pstmt.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
   if(conn!=null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
     return false;
    }

   
   
    /** 删除已不存在的厂商记录 */
    public static void deletePartenerRecord(String tableName, String colName, HashSet sub_ids){
     Connection conn=null;
     PreparedStatement pstmt=null;
     try {
      conn = DBUtil.getConnection();
      Statement sate = conn.createStatement();
      ResultSet rs =sate.executeQuery(" SELECT SUP_ID, SUP_CODE, NAME FROM T_AP_PARTENER_SYN ");
      
      String removeID="";
      while(rs.next()){
       if(!sub_ids.contains(rs.getObject("SUP_ID"))){
        removeID = removeID+","+ rs.getObject("SUP_ID").toString().trim();
        System.out.println("---将要删除: sub_id 为 "+rs.getObject("SUP_ID")+"的厂商---");
       }
      }
      
      //开始集体删除
      if(!"".equals(removeID) && removeID.length()>0){
       removeID = removeID.substring(0,1).equals(",")?removeID.substring(1,removeID.length()-1):removeID;
       pstmt = conn.prepareStatement("delete from  "+tableName +" where "+ colName + " not in ( "+removeID+" )" );
       System.out.println("delete from  "+tableName +" where "+ colName + " not in ( "+removeID+" )");
       pstmt.executeQuery();
             System.out.println("删除成功!");
      }
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }finally{
   if(pstmt!=null){
    try {
     pstmt.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
   if(conn!=null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
    }


}

 

//触发器

create or replace trigger tri_partener after insert or update or delete
on t_ap_partener_syn for each row

declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin


if inserting then
    insert into t_ap_partener(

  PARTENERID ,
  PARTENERNAME,
  ADDRESS,
  TELEPHONE,
  FLAG,
  MAILDOMAIN,
  VISUAL,
  SUP_ID,
  SUP_CODE,
  REPRESENTER,
  BUSI_TYPE,
  EMAIL,
  SITE_URL,
  FAX,
  UPDATETIME,
  syn_flag

    )
    values(SEQ_AP_PARTENER.Nextval,:new.name,:new.address,:new.phone,1,:new.mailDomain,
    0, :new.SUP_ID,:new.SUP_CODE,:new.REPRESENTER,:new.BUSI_TYPE,:new.EMAIL,:new.SITE_URL,:new.FAX,sysdate,0);


elsif updating then
    update t_ap_partener set PARTENERNAME=:new.name,ADDRESS=:new.ADDRESS,TELEPHONE=:new.phone,flag =1,MAILDOMAIN=:new.mailDomain,visual=0
    ,SUP_ID=:new.SUP_ID, SUP_CODE=:new.SUP_CODE,REPRESENTER=:new.REPRESENTER,BUSI_TYPE=:new.BUSI_TYPE,EMAIL=:new.EMAIL,SITE_URL=:new.SITE_URL,FAX=:new.FAX,UPDATETIME=sysdate,syn_flag=0
     where SUP_ID=:OLD.SUP_ID;


elsif deleting then
    delete from t_ap_partener where SUP_ID=:OLD.SUP_ID;
end if;
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;


//厂商表
-- Create table
create table T_AP_PARTENER
(
  PARTENERID   NUMBER not null,
  PARTENERNAME VARCHAR2(100),
  ADDRESS      VARCHAR2(100),
  TELEPHONE    VARCHAR2(50),
  FLAG         NUMBER(1) default 1,
  MAILDOMAIN   VARCHAR2(200),
  PARENTID     NUMBER default 0,
  PLEVEL       NUMBER(1) default 1,
  VISUAL       NUMBER default 0,
  SUP_ID       NUMBER,
  SUP_CODE     VARCHAR2(100),
  REPRESENTER  VARCHAR2(80),
  BUSI_TYPE    VARCHAR2(100),
  EMAIL        VARCHAR2(80),
  SITE_URL     VARCHAR2(120),
  FAX          VARCHAR2(40),
  UPDATETIME   TIMESTAMP(6),
  SYN_FLAG     NUMBER(1)
)

//中间表
create table T_AP_PARTENER_SYN
(
  SUP_ID      NUMBER,
  SUP_CODE    VARCHAR2(100),
  NAME        VARCHAR2(150),
  REPRESENTER VARCHAR2(80),
  BUSI_TYPE   VARCHAR2(100),
  ADDRESS     VARCHAR2(200),
  EMAIL       VARCHAR2(80),
  SITE_URL    VARCHAR2(120),
  FAX         VARCHAR2(40),
  PHONE       VARCHAR2(40),
  UPDATETIME  TIMESTAMP(6),
  MAILDOMAIN  VARCHAR2(200)
)
tablespace TBS_USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );