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
);