package com.palic.elis.tjs.ceis.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import tjs.plugins.jdbc.DBOperateFactory;
import com.beetle.framework.log.SysLogger;
public class SyncVipToLcchCeisCustomerContactTmp {
SysLogger logger = SysLogger.getInstance("JobRunLog");
// 中心库
private static final String ELIS_DATA_SOURCE = "ELIS_DB";
private static final String VIP_DATA_SOURCE = "VIP_DB";
// JOB名字
private static final String PRAISE_JOB_NAME = "SyncVipToLcchCeisCustomerContactTmpJob";// TJS同步数据到CEIS临时表
private static final String SQL_SWITCH_QUERY = "SELECT TO_CHAR(CONTROL_DATE,'yyyy-mm-dd') LAST_SYNC,to_char(sysdate,'yyyy-mm-dd') TODAY, CONTROL_CHAR SWITCH_FLAG FROM ceis_control WHERE control_id = 'SyncVipToLcchCeisCustomerContactTmpJob'";
private static final String SQL_SWITCH_UPDATE = "UPDATE ceis_control SET CONTROL_DATE =SYSDATE WHERE control_id = 'SyncVipToLcchCeisCustomerContactTmpJob'";
// VIP系统同步数据到CEIS临时表查询语句
private static final String SQL_QUERY_Userdefined_LIST = "select Contact_Type_Id,"
+ "Contact_Id,"
+ "Channel_Id,"
+ "Date_Start,"
+ "'02' motive_type,"
+ "SYSTEM_ID,"
+ "Motive_Result_Id,"
+ "Activity_Id,"
+ "Activity_Type,"
+ "Url_Content,"
+ "Match_Flag,"
+ "Mem_Name,"
+ "Sex,"
+ "to_char(Date_Birthday,'yyyy-mm-dd') Date_Birthday,"
+ "ID_Type,"
+ "ID_No," + "Row_id" + " from " + " order_info_to_lcch_temp";
// 根据五项信息查询客户号信息
private static final String SQL_QUERY_UserClientno_LIST = "SELECT CLIENTNO FROM ecif_client_info WHERE id_type = decode(ltrim(rtrim(?)),'***','1','护照','2','军人证','3','少儿证','4','异常***','5','港澳台证件','6','户口本','0','转换不详','9','出生证','A','9')"
+ " AND ((id_type = '1' AND elis_ecif_package.func_is_idno_equal(idno, ltrim(rtrim(?))) = 'Y') OR (id_type <> '1' AND upper(idno) = upper( ltrim(rtrim(?))))) "
+ " AND client_name = ltrim(rtrim(?)) "
+ "AND trunc(birth_date, 'DD') = trunc(to_date(?,'yyyy-mm-dd'), 'DD') "
+ "AND sex = decode(ltrim(rtrim(?)),'男','M','女','F','9') and rownum=1 ";
// 插入到CEIS的CEIS_CUSTOMER_CONTACT_TMP表
private static final String SQL_INSERT_KIT_INFO_LIST = "insert into ceis_customer_contact_new_tmp (module_code,contact_type_id,contact_id,channel_id,date_start,client_no ,motive_type ,system_id ,motive_result_id,activity_id ,activity_type ,url_content , match_flag) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
// 更新VIP系统同步标志
private static final String SQL_VIP_UPDATE = "update order_info_to_lcch_temp set is_synchronized=?, CREATED=SYSDATE, CREATED_BY=USER ,LAST_UPD=SYSDATE, LAST_UPD_BY=USER ";
// 维护ceis_activity_table基表
private static final String SQL_Query_PROD_INFO_TO_LCCH_TEMP = "select trim(row_id) row_id, trim(a.prod_code)prod_code ,trim(a.prod_name)prod_name from PROD_INFO_TO_LCCH_TEMP a where a.is_synchronized='01'";
// 查询基表是否有
private static final String SQL_Query_Ceis_Activity_Table_CNT = "select count(*)cnt from ceis_activity_table a where a.activity_type=?";
// 插入基表
private static final String SQL_Insert_Ceis_Activity_Table = "insert into ceis_activity_table( activity_type,module_code,description) values(?,?,?)";
// 更新VIP产品表的同步标志
private static final String SQL_UPDATE_PROD_INFO_TO_LCCH_TEMP = "update prod_info_to_lcch_temp a set a.is_synchronized=?,a.created=sysdate,a.created_by=user,a.last_upd=sysdate,a.last_upd_by=user";
// 记录日志
private static final String SQL_TJS_CEIS_TRACE_LOG = "insert into ceis_trace(ctrlno,trace_info,trace_type)values('SyncVipToLcchCeisCustomerContactTmpJob',?,'-1')";
private static final String SQL_TJS_MONITOR_INS = "insert into elis_tjs_jobs_monitor(job_name,execute_date,source,target,source_count,target_count) values(?,sysdate,?,?,?,?)";
private final Connection connElis;
private final Connection connVip;
private final int commitCnt = 10;
public SyncVipToLcchCeisCustomerContactTmp() {
connElis = DBOperateFactory.getConncetion(ELIS_DATA_SOURCE);
connVip = DBOperateFactory.getConncetion(VIP_DATA_SOURCE);
}
// 获取开关
public Map<String, Object> getJobSwitch() {
try {
return queryForMap(connElis, SQL_SWITCH_QUERY);
} catch (Exception ex) {
logger.info("getJobSwitch ", ex);
}
return new HashMap<String, Object>();
}
// 更新开关日期
public final int updateJobSwitch() {
try {
return updateSql(connElis, SQL_SWITCH_UPDATE);
} catch (SQLException ex) {
logger.info("updateJobSwitch", ex);
}
return 0;
}
// 运行
@SuppressWarnings({ "rawtypes" })
public void run() {
try {
// 查询开关日期,防止重复同步
Map switchMap = getJobSwitch();
String last = (String) switchMap.get("LAST_SYNC");
String today = (String) switchMap.get("TODAY");
if (last == null || last.compareTo(today) < 0) {
// VIP同步数据到CEIS临时表语句
syncDepraiseData(last);
// 更新开关日期
updateJobSwitch();
}
} catch (Exception e) {
e.printStackTrace();
}
}
// VIP同步数据到CEIS临时表语句
public void syncDepraiseData(String day) throws SQLException {
int cnt = 0;
int count = 0;
StringBuffer st = new StringBuffer();
ResultSet rtype = null;
ResultSet qcnt = null;
String pcode = null;
String Rowid = null;
PreparedStatement quertype = null;
PreparedStatement querycnt = null;
PreparedStatement insertable = null;
PreparedStatement update = null;
List<String> CommitProdList = new ArrayList<String>();
// 维护基表
logger.info("------维护基表开始-----");
try {
// 查询VIP系统没有同步的业务类型
quertype = connVip
.prepareStatement(SQL_Query_PROD_INFO_TO_LCCH_TEMP);
// 查询基表是否已经存在
querycnt = connElis
.prepareStatement(SQL_Query_Ceis_Activity_Table_CNT);
// 如果没有则插入到基表
insertable = connElis
.prepareStatement(SQL_Insert_Ceis_Activity_Table);
// 更新VIP产品表的同步标志
update = connVip
.prepareStatement(SQL_UPDATE_PROD_INFO_TO_LCCH_TEMP);
// 查詢產品表
rtype = quertype.executeQuery();
/*
* 基表维护 1、首先取出VIP系统的产品表同步标志为没有同步的数据。
* 2、根据不同的VIP增值服务类型给产品编码拼接前缀查询ceis_activity_table的业务类型如果没有需要插入进去。
* 3、每插入一条到ceis_activity_table表就需要更新VIP系统的产品表同步标志。 4、如果報錯記錄日誌。
* VIP增值服务申请/预约= VIPSV001(需要和产品组合) VIP增值服务订单取消= VIPSV002(需要和产品组合)
* VIP增值服务订单咨询= VIPSV003(不需要和产品组合) VIP增值服务投诉= VIPSV004(不需要和产品组合)
* VIP增值服务使用= VIPSV005(需要和产品组合)
*/
while (rtype.next()) {
logger.info("source Row_id " + rtype.getString("Row_id"));
String[] a = { "VIPSV001_", "VIPSV002_", "VIPSV005_" };
String[] b = { "增值服务申请/预约_", "VIP增值服务订单取消_", "VIP增值服务使用_" };
CommitProdList.add(rtype.getString("Row_id"));
for (int i = 0; i < a.length; i++) {
pcode = null;
String description = null;
pcode = a[i] + rtype.getString("prod_code");
description = b[i] + rtype.getString("prod_name");
querycnt.setString(1, pcode);
qcnt = querycnt.executeQuery();
while (qcnt.next()) {
if (qcnt.getString("cnt").equals("0")) {
insertable.setString(1, null);
insertable.setString(2, "30");
insertable.setString(3, description);
insertable.execute();
}
}
}
CommitProdList.clear();
}
} catch (Exception e) {
logger.info("VIP系统同步到CEIS基表数据报:" + e.getMessage());
for (String rowid : CommitProdList) {
insert_ceis_trace_Log("VIP系统同步到CEIS基表数据报错主键为:"
+ rowid+ "错误信息为:"
+ e.getMessage());
}
CommitProdList.clear();
} finally {
// 关闭连接
try {
if (quertype != null) {
quertype.executeBatch();
quertype.close();
}
if (querycnt != null) {
querycnt.executeBatch();
querycnt.close();
}
if (insertable != null) {
insertable.executeBatch();
insertable.close();
}
if (update != null) {
update.setString(1, "02");
update.execute();
update.close();
}
} catch (SQLException e) {
e.printStackTrace();
logger.info("关闭链接异常" + e.getMessage());
}
}
logger.info("------维护基表结束-----");
/*
* 同步VIP数据到CEIS临时表 1、首先是JOB开关打开、查询VIP系统是否有没有同步的数据。
* 2、逐条处理,根据VIP系统的五项信息查询查询出客户号。 3、查询出来的信息插入到CEIS临时表。 4、更新VIP系统的同步标志。
* 5、关闭连接、记录日志。
*/
try {
// 查询VIP未同步的数据
String sql = SQL_QUERY_Userdefined_LIST;
if (day != null && !"".equals(day)) {
sql = SQL_QUERY_Userdefined_LIST + " where created> to_date('"
+ day + "','yyyy-mm-dd')and Is_Synchronized='01'";
}
logger.info("VIP系统同步到CEIS临时表查询SQL语句:" + sql);
PreparedStatement psql = connVip.prepareStatement(sql);
// 插入到CEIS的CEIS_CUSTOMER_CONTACT_TMP表
PreparedStatement isql = connElis
.prepareStatement(SQL_INSERT_KIT_INFO_LIST);
// 根据五项信息查询客户号信息
PreparedStatement qclientno = connElis
.prepareStatement(SQL_QUERY_UserClientno_LIST);
// 记录错误日志
PreparedStatement ceislog = connElis
.prepareStatement(SQL_TJS_CEIS_TRACE_LOG);
// 更新VIP系统同步标志
PreparedStatement vipsign = connVip
.prepareStatement(SQL_VIP_UPDATE);
ResultSet rs = null;
ResultSet scup = null;
ResultSet rno = null;
String clientno = null;
List<String> commitDataList = new ArrayList<String>();
try {
psql.setFetchSize(1000);
isql.setFetchSize(1000);
// 查询出VIP系统的同步数据
rs = psql.executeQuery();
ResultSetMetaData rsmt = rs.getMetaData();
// 循环逐条处理
while (rs.next()) {
cnt++;
try {
clientno = null;
// 根据五项信息定位唯一客户
qclientno.setString(1, rs.getString("ID_TYPE"));
qclientno.setString(2, rs.getString("ID_NO"));
qclientno.setString(3, rs.getString("ID_NO"));
qclientno.setString(4, rs.getString("MEM_NAME"));
qclientno.setString(5, rs.getString("DATE_BIRTHDAY"));
qclientno.setString(6, rs.getString("SEX"));
System.out.println("source ID_TYPE "
+ rs.getString("ID_TYPE") + " IDNO "
+ rs.getString("ID_NO") + " CLIENT_NAME "
+ rs.getString("MEM_NAME") + " DATE_BIRTHDAY "
+ rs.getString("DATE_BIRTHDAY") + " SEX "
+ rs.getString("SEX"));
// 查询客户号
rno = qclientno.executeQuery();
rno.next();
if (rno.getRow() == 0) {
logger.info("以上五项信息没有取到客户号");
ceislog.setString(1,
"主键Row_id为" + rs.getString("Row_id")
+ "根据五项信息没有查到对应的客户号");
vipsign.setString(1, "2");
vipsign.executeUpdate();
ceislog.executeUpdate();
} else if (rno.getRow() == 1) {
count++;
// 取到一个客户号,同步数据
clientno = rno.getString("CLIENTNO");
logger.info("clientno取出来的客户号:" + clientno);
commitDataList.add(rs.getString("Row_id"));
isql.setString((1), "30");
isql.setString((2), rs.getString("contact_type_id"));
isql.setString((3), rs.getString("contact_id"));
isql.setString((4), rs.getString("channel_id"));
isql.setTimestamp((5),
rs.getTimestamp("date_start"));
isql.setString(6, clientno);
isql.setString((7), rs.getString("motive_type"));
isql.setString((8), rs.getString("system_id"));
isql.setString((9),
rs.getString("motive_result_id"));
isql.setString((10), rs.getString("activity_id"));
isql.setString((11), rs.getString("activity_type"));
isql.setString((12), rs.getString("url_content"));
isql.setString((13), rs.getString("match_flag"));
// 成功后更新VIP表的同步状态
vipsign.setString(1, "02");
isql.addBatch();
vipsign.addBatch();
}
if (cnt % commitCnt == 0) {// 每1000条提交
isql.executeBatch();
// vipsign.executeBatch();
if (ceislog != null)
ceislog.executeBatch();
commitDataList.clear();
}
} catch (Exception e) {
if (vipsign != null) {
vipsign.close();
}
connElis.rollback();
connVip.rollback();
vipsign = connVip.prepareStatement(SQL_VIP_UPDATE);
for (String rowid : commitDataList) {
insert_ceis_trace_Log("VIP系统同步到CEIS临时表数据报错主键为:"
+ rowid + "错误信息为" + e.getMessage());
count = count - 1;
}
commitDataList.clear();
}
}
// 执行最后一次批量
if (cnt % commitCnt != 0) {
try {
if (isql != null) {
isql.executeBatch();
}
if (vipsign != null) {
vipsign.executeBatch();
}
if (ceislog != null) {
ceislog.executeBatch();
}
commitDataList.clear();
} catch (Exception e) {
if (vipsign != null)
vipsign.close();
connElis.rollback();
vipsign = connVip.prepareStatement(SQL_VIP_UPDATE);
for (String rowid : commitDataList) {
logger.info("VIP系统同步到CEIS临时表数据报错主键为:" + rowid
+ "错误信息为" + e.getMessage());
insert_ceis_trace_Log("VIP系统同步到CEIS临时表数据报错主键为:"
+ rowid + "错误信息为" + e.getMessage());
count = count - 1;
}
commitDataList.clear();
}
}
} catch (Exception e) {
logger.info("VIP系统同步到CEIS临时表数据报错:" + e.getMessage());
} finally {
if (psql != null) {
psql.close();
}
if (isql != null) {
isql.close();
}
if (qclientno != null) {
qclientno.close();
}
if (vipsign != null) {
vipsign.close();
}
if (ceislog != null) {
ceislog.close();
}
}
} catch (Exception e) {
count = (cnt - commitCnt) < 0 ? 0 : (cnt - commitCnt);
logger.info("VIP系统同步到CEIS临时表数据报错" + e.getMessage());
insert_ceis_trace_Log(e.getMessage());
} finally {
insertTjsLog(PRAISE_JOB_NAME, String.valueOf(cnt),
String.valueOf(count));
}
}
// 插入到TJS对账日志表
public void insertTjsLog(String jobName, String sourceCnt, String targetCnt) {
PreparedStatement psql;
try {
psql = connElis.prepareStatement(SQL_TJS_MONITOR_INS);
psql.setString(1, jobName);
psql.setString(2, VIP_DATA_SOURCE);
psql.setString(3, ELIS_DATA_SOURCE);
psql.setString(4, sourceCnt);
psql.setString(5, targetCnt);
psql.executeUpdate();
} catch (SQLException e) {
logger.info("记录TJS日志报错" + e);
e.printStackTrace();
}
}
// 关闭数据库连接
public void close() {
try {
if (connElis != null) {
connElis.close();
}
if (connVip != null) {
connVip.close();
}
} catch (SQLException e) {
logger.error("关闭数据库连接失败", e);
}
}
// 插入到ceis_trace
public void insert_ceis_trace_Log(String trace_info) {
PreparedStatement psql;
try {
psql = connElis.prepareStatement(SQL_TJS_CEIS_TRACE_LOG);
psql.setString(1, trace_info);
psql.execute();
} catch (SQLException e) {
logger.info("记录ceis_trace日志报错" + e);
e.printStackTrace();
}
}
private Map<String, Object> queryForMap(final Connection conn,
final String sql) throws SQLException {
List<Map<String, Object>> list = queryForList(conn, sql);
return list.isEmpty() ? new HashMap<String, Object>() : list.get(0);
}
private int updateSql(final Connection conn, final String sql)
throws SQLException {
PreparedStatement psql = conn.prepareStatement(sql);
return psql.executeUpdate();
}
private List<Map<String, Object>> queryForList(final Connection conn,
final String sql) throws SQLException {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
PreparedStatement psql = conn.prepareStatement(sql);
Map<String, Object> dataRow = null;
ResultSet rs = null;
psql.setFetchSize(1000);
rs = psql.executeQuery();
ResultSetMetaData rsmt = rs.getMetaData();
while (rs.next()) {
dataRow = new HashMap<String, Object>();
for (int i = 1; i <= rsmt.getColumnCount(); i++) {
if ("java.lang.String".equals(rsmt.getColumnClassName(i))) {
dataRow.put(rsmt.getColumnLabel(i), rs.getString(i));
} else if ("java.sql.Timestamp".equals(rsmt
.getColumnClassName(i))) {
dataRow.put(rsmt.getColumnLabel(i), rs.getTimestamp(i));
} else if ("java.math.BigDecimal".equals(rsmt
.getColumnClassName(i))) {
dataRow.put(rsmt.getColumnLabel(i), rs.getBigDecimal(i));
} else {
throw new SQLException("Unsupport column type");
}
}
resultList.add(dataRow);
}
return resultList;
}
}