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