package com.*.*.modules.webservice.service;
import gmcc.investmentBuget.service.InvestmentBudgetImplStub;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import com.*.*.database.DBUtil;
import com..fwk.base.APMSBOImpl;
import com.*.*.modules.system.system.dao.ParameterDAO;
import com.*.*.util.*CharTurn;
import com.*.exception.RecordNotFoundException;
public class ProjectSynPollingBO extends *BOImpl{
/**
* @param args
*/
public static void main(String[] args) throws IOException,
SQLException, RecordNotFoundException {
InvestmentBudgetImplStub stub =
new InvestmentBudgetImplStub
("http://192.*.*.*:8080/nmcsoa/services/InvestmentBudgetService");
stub._getServiceClient().getOptions().setTimeOutInMilliSeconds(1000*60*1500);
new ProjectSynPollingBO().polling(stub);
// List proList = getProjectList();
// List conList = getContractList();
// restoreRelation(proList,conList);
}
public void polling(InvestmentBudgetImplStub stub){
String ids_pro="";
HashSet ids_con= new HashSet();
Connection conn=null;
try{
//写库
conn = DBUtil.getConnection();
System.out.println("开始同步项目合同库 "+Calendar.getInstance().getTime());
InvestmentBudgetImplStub.GetAllProjectResponse res =
stub.getAllProject();
InvestmentBudgetImplStub.InvestmentBudgetProject[] a = res.get_return();
System.out.println("项目数: "+a.length);
if(a!=null && a.length>0){
//往t_parameter插入同步时间
java.util.Date date = new java.util.Date();
ParameterDAO parameterDAO = new ParameterDAO(conn);
parameterDAO.storeParameterValueByName("Ltim_PROJECT_lastSynTime",APMSCharTurn.getLongDate(date),conn);
for(int i=0; i<a.length; i++){
if(i==0){
ids_pro =ids_pro+ a[i].getId();
}else{
ids_pro = ids_pro+","+ a[i].getId();
}
PreparedStatement pstmt=null;
InvestmentBudgetImplStub.GetAllContractByProjectId req = new InvestmentBudgetImplStub.GetAllContractByProjectId();
req.setProjectId(a[i].getId());
InvestmentBudgetImplStub.GetAllContractByProjectIdResponse res_c =
stub.getAllContractByProjectId(req);
InvestmentBudgetImplStub.InvestmentBudgetContract[] c = res_c.get_return();
int p_proID = Integer.parseInt(String.valueOf(a[i].getId()));
String p_proCode = a[i].getCode()==null?"": a[i].getCode();
String p_proName = a[i].getName() ==null?"":a[i].getName();
int p_proType = Integer.parseInt(String.valueOf(a[i].getProjectType()));
int p_proStatus = Integer.parseInt(String.valueOf(a[i].getStatus()));
String p_content = a[i].getContent()==null?"":a[i].getContent();
String p_networkDef = a[i].getNetworkDef()==null?"":a[i].getNetworkDef();
String p_details = a[i].getDetails()==null?"":a[i].getDetails();
String p_deadline = a[i].getDeadline()==null?"":a[i].getDeadline();
String p_approvedCode = a[i].getApprovedCode()==null?"":a[i].getApprovedCode();
String p_investedType = a[i].getInvestedType()==null?"":a[i].getInvestedType();
Date p_startDate = a[i].getStartDate()==null?null:(Date) a[i].getStartDate().getTime();
String p_startCode = a[i].getStartCode()==null?"":a[i].getStartCode();
Date p_endDate = a[i].getEndDate()==null?null:(Date) a[i].getEndDate().getTime();
String p_endCode = a[i].getEndCode()==null?"":a[i].getEndCode();
if(!ProjectSynPollingBO.isExist("t_project_syn", "id", String.valueOf( a[i].getId()))){
System.out.println("---正在添加id为: "+ a[i].getId() +" 的项目---");
/** 往项目表插入数据 */
pstmt = conn.prepareStatement("insert into t_project_syn(id, code,name,protype,prostatus,updatetime," +
"Content, NetworkDef,Details,Deadline,ApprovedCode,InvestedType,StartDate,StartCode,EndDate,EndCode) "
+ "values(?,?,?,?,?,sysdate, ?,?,?,?,?,?,?,?,?,?)");
pstmt.setInt(1,p_proID);
pstmt.setString(2,p_proCode);
pstmt.setString(3,p_proName);
pstmt.setInt(4,p_proType);
pstmt.setInt(5,p_proStatus);
pstmt.setString(6,p_content);
pstmt.setString(7,p_networkDef);
pstmt.setString(8,p_details);
pstmt.setString(9,p_deadline);
pstmt.setString(10,p_approvedCode);
pstmt.setString(11,p_investedType);
pstmt.setDate(12,p_startDate);
pstmt.setString(13,p_startCode);
pstmt.setDate(14,p_endDate);
pstmt.setString(15,p_endCode);
pstmt.executeUpdate();
}else{
//继续判断项目记录是否被修改
String sql = "select id from t_project_syn where id = "+p_proID;
String condition = " and ( code!= '"+ p_proCode + "' or name!= '"+ p_proName+ "' or protype!= "+ p_proType
+ " or prostatus!= "+ p_proStatus+ " or Content!= '"+ p_content+ "' or Details!= '"+ p_details
+ "' or InvestedType!= '"+ p_investedType+"' )";
//System.out.println(sql+condition);
pstmt = conn.prepareStatement(sql+condition);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
System.out.println("---正在修改id为: "+ a[i].getId() +" 的项目---");
pstmt = conn.prepareStatement("update t_project_syn set code=?,name=?,protype=?,prostatus=?," +
" updatetime=sysdate,Content=?, NetworkDef=?,Details=?,Deadline=?,ApprovedCode=?,InvestedType=?, " +
" StartDate=?,StartCode=?,EndDate=?,EndCode=? where id = ? ");
pstmt.setInt(15,p_proID);
pstmt.setString(1,p_proCode);
pstmt.setString(2,p_proName);
pstmt.setInt(3,p_proType);
pstmt.setInt(4,p_proStatus);
pstmt.setString(5,p_content);
pstmt.setString(6,p_networkDef);
pstmt.setString(7,p_details);
pstmt.setString(8,p_deadline);
pstmt.setString(9,p_approvedCode);
pstmt.setString(10,p_investedType);
pstmt.setDate(11,p_startDate);
pstmt.setString(12,p_startCode);
pstmt.setDate(13,p_endDate);
pstmt.setString(14,p_endCode);
pstmt.executeUpdate();
}
}
for(int j=0; j<c.length; j++){
String str_id =String.valueOf(c[j].getId());
if(!ids_con.contains(str_id)){
ids_con.add(str_id);
}
Long v_ID = Long.valueOf((String.valueOf(c[j].getId())));
Long v_projectID = Long.valueOf((String.valueOf(c[j].getProjectId())));
String v_concode=c[j].getCode()==null?"":c[j].getCode();
String v_conname=c[j].getName()==null?"":c[j].getName();
String v_con_topics =c[j].getTopics()==null?"":c[j].getTopics();
String v_orgName=c[j].getOrgName()==null?"":c[j].getOrgName();
long v_orgID=c[j].getOrgId();
//v_userName=c[j].getUserName();
long v_userID=c[j].getUserId();
String v_planDate = c[j].getPlanDate()==null?"":c[j].getPlanDate();
java.sql.Date v_FactDate = null;
java.sql.Date v_SigningDate = null;
InvestmentBudgetImplStub.GetUserById req_user = new InvestmentBudgetImplStub.GetUserById();
req_user.setOrgId(v_orgID);
req_user.setUserId(v_userID);
InvestmentBudgetImplStub.GetUserByIdResponse res_user =stub.getUserById(req_user);
InvestmentBudgetImplStub.UserInfo uInfo = res_user.get_return();
String v_userName = uInfo.getUser_Account()==null?"":uInfo.getUser_Account();
String v_fullName = uInfo.getUser_Name();
String v_orgFullName = uInfo.getUser_FullName();
Long v_con_status = Long.valueOf((String.valueOf(c[j].getStatus())));
String v_deadline = c[j].getDeadline()==null?"":c[j].getDeadline();
String v_otherParty = c[j].getOtherParty()==null?"":c[j].getOtherParty();
if(c[j].getFactDate()!=null){
Calendar cal_FactDate = c[j].getFactDate();
String day=null;
String month=null;
String hour=null;
String minu=null;
//日
if(cal_FactDate.get(Calendar.DATE)<10){
day = "0"+String.valueOf(cal_FactDate.get(Calendar.DATE));
}else{
day = String.valueOf(cal_FactDate.get(Calendar.DATE));
}
//月
if((cal_FactDate.get(Calendar.MONTH) + 1)<10){
month = "0"+String.valueOf(cal_FactDate.get(Calendar.MONTH) + 1);
}else{
month =String.valueOf(cal_FactDate.get(Calendar.MONTH) + 1);
}
//年
String year = String.valueOf(cal_FactDate.get(Calendar.YEAR)) ;
//时
if(cal_FactDate.get(Calendar.HOUR_OF_DAY)<10){
hour = "0"+String.valueOf(cal_FactDate.get(Calendar.HOUR_OF_DAY));//不能用Calendar.HOUR，它是12进制的
}else{
hour = String.valueOf(cal_FactDate.get(Calendar.HOUR_OF_DAY));
}
//分
if(cal_FactDate.get(Calendar.MINUTE)<10){
minu = "0"+String.valueOf(cal_FactDate.get(Calendar.MINUTE));
}else{
minu = String.valueOf(cal_FactDate.get(Calendar.MINUTE));
}
String str_FactDate = year+"-"+month+"-"+day+" "+hour+":"+minu+":00";
SimpleDateFormat bartDateFormat =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.ENGLISH);
v_FactDate = new java.sql.Date(bartDateFormat.parse(str_FactDate).getTime());
}
if(c[j].getSigningDate()!=null){
Calendar cal_SigningDate = c[j].getSigningDate();
String day=null;
String month=null;
String hour=null;
String minu=null;
//日
if(cal_SigningDate.get(Calendar.DATE)<10){
day = "0"+String.valueOf(cal_SigningDate.get(Calendar.DATE));
}else{
day = String.valueOf(cal_SigningDate.get(Calendar.DATE));
}
//月
if((cal_SigningDate.get(Calendar.MONTH) + 1)<10){
month = "0"+String.valueOf(cal_SigningDate.get(Calendar.MONTH) + 1);
}else{
month =String.valueOf(cal_SigningDate.get(Calendar.MONTH) + 1);
}
//年
String year = String.valueOf(cal_SigningDate.get(Calendar.YEAR)) ;
//时
if(cal_SigningDate.get(Calendar.HOUR_OF_DAY)<10){
hour = "0"+String.valueOf(cal_SigningDate.get(Calendar.HOUR_OF_DAY));//不能用Calendar.HOUR，它是12进制的
}else{
hour = String.valueOf(cal_SigningDate.get(Calendar.HOUR_OF_DAY));
}
//分
if(cal_SigningDate.get(Calendar.MINUTE)<10){
minu = "0"+String.valueOf(cal_SigningDate.get(Calendar.MINUTE));
}else{
minu = String.valueOf(cal_SigningDate.get(Calendar.MINUTE));
}
String str_SigningDate = year+"-"+month+"-"+day+" "+hour+":"+minu+":00";
SimpleDateFormat bartDateFormat =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.ENGLISH);
v_SigningDate = new java.sql.Date(bartDateFormat.parse(str_SigningDate).getTime());
}
if(!ProjectSynPollingBO.isExist("t_contract_syn", "id", String.valueOf(v_ID))){
System.out.println("---正在添加id为: " + c[j].getId() +" 的合同---");
/** 往合同表插入数据 */
pstmt = conn.prepareStatement("insert into t_contract_syn(ID, concode,conname,contopics,orgname,username,constatus,updatetime,PROJECTID," +
"PlanDate,FactDate,OtherParty,SigningDate,Deadline,fullname,orgfullname) values(?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?)");
pstmt.setInt(1,Integer.parseInt(String.valueOf(v_ID)));
pstmt.setString(2,v_concode);
pstmt.setString(3,v_conname);
pstmt.setString(4,v_con_topics);
pstmt.setString(5,v_orgName);
pstmt.setString(6,v_userName);
pstmt.setInt(7,Integer.parseInt(String.valueOf(v_con_status)));
pstmt.setInt(8,Integer.parseInt(String.valueOf(v_projectID)));
pstmt.setString(9,v_planDate);
pstmt.setDate(10,v_FactDate==null?null:v_FactDate);
pstmt.setString(11,v_otherParty);
pstmt.setDate(12,v_SigningDate==null?null:v_SigningDate);
pstmt.setString(13,v_deadline);
pstmt.setString(14,v_fullName);
pstmt.setString(15,v_orgFullName);
pstmt.executeUpdate();
}else{
//继续判断合同记录是否被修改
String sql_c = "select id from t_contract_syn where id = "+v_ID;
String condition_c = " and ( concode!= '"+ v_concode + "' or conname!= '"+ v_conname+ "' or contopics!= '"+ v_con_topics
+ "' or orgname!= '"+ v_orgName+ "' or orgfullname!= '"+ v_orgFullName+ "' or username!= '"+ v_userName
+ "' or fullname!= '"+ v_fullName+ "' or constatus!= "+ v_con_status+" or projectid!= "+ v_projectID+
" or plandate!= '"+ v_planDate+"' or otherparty!= '"+ v_otherParty+
"' or deadline!= '"+ v_deadline+"')";
//System.out.println(sql_c+condition_c);
pstmt = conn.prepareStatement(sql_c+condition_c);
ResultSet rs_c = pstmt.executeQuery();
if(rs_c.next()){
System.out.println("---正在修改id为: "+c[j].getId() +" 的合同---");
pstmt = conn.prepareStatement("update t_contract_syn set concode=?,conname=?,contopics=?,orgname=?,username=?" +
",constatus=?,updatetime=sysdate,PROJECTID=?,PlanDate=?,FactDate=?,OtherParty=?,SigningDate=?,Deadline=?"+
",fullname=?,orgfullname=? where id= ? ");
pstmt.setInt(15,Integer.parseInt(String.valueOf(v_ID)));
pstmt.setString(1,v_concode);
pstmt.setString(2,v_conname);
pstmt.setString(3,v_con_topics);
pstmt.setString(4,v_orgName);
pstmt.setString(5,v_userName);
pstmt.setInt(6,Integer.parseInt(String.valueOf(v_con_status)));
pstmt.setInt(7,Integer.parseInt(String.valueOf(v_projectID)));
pstmt.setString(8,v_planDate);
pstmt.setDate(9,c[j].getFactDate()==null?null:v_FactDate);
pstmt.setString(10,v_otherParty);
pstmt.setDate(11,c[j].getSigningDate()==null?null:v_SigningDate);
pstmt.setString(12,v_deadline);
pstmt.setString(13,v_fullName);
pstmt.setString(14,v_orgFullName);
pstmt.executeUpdate();
}
}
}
if(pstmt!=null){
pstmt.close();
}
}
//删除已不存在项目记录
System.out.println("正在删除已不存在项目记录: "+ids_pro);
deleteProjectRecord("t_project_syn", "id" , ids_pro);
//删除已不存在合同记录
System.out.println("正在删除已不存在合同记录: "+ids_con);
deleteContractRecord("t_contract_syn", "id" , ids_con);
//恢复项目与合同的关系
System.out.println("正在恢复项目与合同关系");
List proList = getProjectList();
List conList = getContractList();
restoreRelation(proList,conList);
}
System.out.print("结束同步项目合同库 "+Calendar.getInstance().getTime());
if(conn!=null){
conn.close();
}
} catch(Exception e){
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/** 通过关键字判断记录是否存在 */
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 deleteProjectRecord(String tableName, String colName, String value){
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn = DBUtil.getConnection();
Statement sate = conn.createStatement();
System.out.println("目前远程合同集合("+value.split(",").length+"): " +value);
ResultSet rs =sate.executeQuery("select id, code,name from t_project_syn where id not in ("+ value +") ");
while(rs.next()){
System.out.println("正在删除已经不存在项目记录! id: "+ rs.getInt("ID")+", code: "+rs.getString("CODE")+", name: "+rs.getString("NAME"));
}
//开始集体删除
pstmt = conn.prepareStatement("delete from "+tableName +" where "+ colName + " not in ( "+value+" )" );
pstmt.executeQuery();
} 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();
}
}
}
}
/** 删除已不存在的合同记录 */
public static void deleteContractRecord(String tableName, String colName, HashSet value){
String ids="";
if(value!=null && value.size()>0){
Iterator it = value.iterator();
int i=0;
while(it.hasNext()){
String id=String.valueOf(it.next());
if(i==0){
ids = ids + id;
}else{
ids = ids+ ","+ id;
}
i++;
}
}
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn = DBUtil.getConnection();
Statement sate = conn.createStatement();
System.out.println("目前远程合同集合("+ids.split(",").length+"): " +ids);
ResultSet rs =sate.executeQuery("select id, concode,conname from t_contract_syn where id not in ("+ ids +") ");
while(rs.next()){
System.out.println("正在删除已经不存在合同记录! id: "+ rs.getInt("ID")+", conCode: "+rs.getString("CONCODE")+", conName: "+rs.getString("CONNAME"));
}
//开始集体删除
pstmt = conn.prepareStatement("delete from "+tableName +" where "+ colName + " not in ( "+ids+" )" );
pstmt.executeQuery();
} 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();
}
}
}
}
/**
* 项目集合
*/
public static List getProjectList(){
Connection conn=null;
Statement proState =null;
ResultSet proRs=null;
List proList = new ArrayList();
try {
conn = DBUtil.getConnection();
proState = conn.createStatement();
proRs = proState.executeQuery(" select x.projectid, x.projectname, x.projectid_syn from t_project x where x.flag_syn=0 ");
while(proRs.next()){
//项目信息
Map proMap = new HashMap();
proMap.put(proRs.getObject("PROJECTID"), proRs.getObject("PROJECTID_SYN"));
//System.out.println(proRs.getObject("PROJECTID")+","+ proRs.getObject("PROJECTID_SYN"));
proList.add(proMap);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
if(proState!=null){
try {
proState.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return proList;
}
/**
* 合同集合
*/
public static List getContractList(){
Connection conn=null;
Statement conState =null;
ResultSet conRs=null;
List proList = new ArrayList();
try {
conn = DBUtil.getConnection();
conState = conn.createStatement();
conRs = conState.executeQuery(" select y.id,y.conname, y.projectid, y.contractid_syn from t_contract y where y.flag_syn=0 ");
while(conRs.next()){
//项目信息
Map proMap = new HashMap();
proMap.put(conRs.getObject("ID"), conRs.getObject("PROJECTID"));
//System.out.println(conRs.getObject("ID")+","+ conRs.getObject("PROJECTID"));
proList.add(proMap);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
if(conState!=null){
try {
conState.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return proList;
}
/**
* 恢复关系
*/
public static void restoreRelation(List projectList, List contractList){
Connection conn=null;
PreparedStatement pstmt=null;
if(projectList!=null && projectList.size()>0 && contractList!=null && contractList.size()>0){
//项目
for(int i=0; i< projectList.size(); i++){
Map proMap = (HashMap)projectList.get(i);
for(Iterator itPro = proMap.entrySet().iterator(); itPro.hasNext(); ){
Map.Entry ep = (Map.Entry)itPro.next();
int newProjectID = Integer.parseInt(ep.getKey().toString());
int projectid_syn =Integer.parseInt(ep.getValue().toString());
//合同
for(int j=0; j< contractList.size(); j++){
Map conMap = (HashMap)contractList.get(j);
for(Iterator itCon = conMap.entrySet().iterator(); itCon.hasNext(); ){
Map.Entry ec = (Map.Entry)itCon.next();
int contractID = Integer.parseInt(ec.getKey().toString());
int projectid =Integer.parseInt(ec.getValue().toString());
if(projectid==projectid_syn){
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(" update t_contract set projectid = ? where id = ? ");
pstmt.setInt(1, newProjectID);
pstmt.setInt(2, contractID);
pstmt.executeQuery();
} 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();
}
}
}
System.out.println("合同contract: "+contractID+"由原来的所属项目"+projectid_syn+"恢复为: "+newProjectID);
}
}
}
}
}
}
}
}
//-------触发器-----------
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_PROJECT
(
PROJECTID NUMBER,
PROJECTCODE VARCHAR2(50),
PROJECTNAME VARCHAR2(100),
PROTYPE NUMBER(2),
PROSTATUS NUMBER(2),
CONCODE VARCHAR2(50),
CONNAME VARCHAR2(100),
CONTOPICS VARCHAR2(200),
ORGNAME VARCHAR2(30),
USERNAME VARCHAR2(30),
CONSTATUS NUMBER(2),
UPDATETIME DATE,
TOPPROJECTID NUMBER(25),
PROJECTLEVEL NUMBER(2),
PRORESPERSONID NUMBER(15),
PRORESPERSONNAME VARCHAR2(15),
ACCEPT NUMBER(1),
FLAG NUMBER(1),
DESCRIPTION VARCHAR2(100),
PRORESPERSONDEPTID NUMBER(15),
PRORESPERSONDEPTNAME VARCHAR2(40),
CONTENT VARCHAR2(1024),
NETWORKDEF VARCHAR2(64),
DETAILS VARCHAR2(128),
DEADLINE VARCHAR2(255),
APPROVEDCODE VARCHAR2(255),
INVESTEDTYPE VARCHAR2(255),
STARTDATE DATE,
STARTCODE VARCHAR2(255),
ENDDATE DATE,
ENDCODE VARCHAR2(255),
FLAG_SYN NUMBER(1),
PROJECTID_SYN NUMBER
)
//合同
create table T_CONTRACT
(
ID NUMBER,
CONCODE VARCHAR2(50),
CONNAME VARCHAR2(150),
CONTOPICS VARCHAR2(300),
ORGNAME VARCHAR2(30),
ORGFULLNAME VARCHAR2(100),
USERNAME VARCHAR2(30),
FULLNAME VARCHAR2(10),
CONSTATUS NUMBER(2),
UPDATETIME DATE,
PROJECTID NUMBER,
PLANDATE VARCHAR2(10),
FACTDATE DATE,
OTHERPARTY VARCHAR2(255),
SIGNINGDATE DATE,
DEADLINE VARCHAR2(255),
FLAG_SYN NUMBER(1),
FLAG NUMBER(1),
CONTRACTID_SYN NUMBER
)
tablespace TBS_USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
//中间项目表
-- Create table
create table T_PROJECT_SYN
(
ID NUMBER,
CODE VARCHAR2(50),
NAME VARCHAR2(100),
PROTYPE NUMBER(2),
PROSTATUS NUMBER(2),
UPDATETIME DATE,
CONTENT VARCHAR2(1024),
NETWORKDEF VARCHAR2(64),
DETAILS VARCHAR2(128),
DEADLINE VARCHAR2(255),
APPROVEDCODE VARCHAR2(255),
INVESTEDTYPE VARCHAR2(255),
STARTDATE DATE,
STARTCODE VARCHAR2(255),
ENDDATE DATE,
ENDCODE VARCHAR2(255)
)
//中间合同表
-- Create table
create table T_CONTRACT_SYN
(
ID NUMBER,
CONCODE VARCHAR2(50),
CONNAME VARCHAR2(150),
CONTOPICS VARCHAR2(300),
ORGNAME VARCHAR2(30),
ORGFULLNAME VARCHAR2(100),
USERNAME VARCHAR2(20),
FULLNAME VARCHAR2(10),
CONSTATUS NUMBER(2),
UPDATETIME DATE,
PROJECTID NUMBER,
PLANDATE VARCHAR2(10),
FACTDATE DATE,
OTHERPARTY VARCHAR2(255),
SIGNINGDATE DATE,
DEADLINE VARCHAR2(255)
)