bak_workorder.pc

数据扫描程序

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "common.h"

#define LOGFILE "./"
#define TABLE_NUM  200 
#define TABLE_LEN  31 

EXEC SQL INCLUDE SQLCA ;  
char	logfilename[128+1] ; 
char	arcTableName[TABLE_NUM][TABLE_LEN] ;
char	arcFlag[TABLE_NUM][2] ;
int	iOnlineDur[TABLE_NUM] ;
int	iOfflineDur[TABLE_NUM] ;
                  
FILE	*fpLog ;

void OracleErrorProcess(char *msg)
{
	
	printf("\n一个ORACLE error occured:");
	printf("%s.\n",sqlca.sqlerrm.sqlerrmc);
	
	fprintf(fpLog,"\nin [%s] ORACLE error occured :",msg);
	fprintf(fpLog,"%s.\n",sqlca.sqlerrm.sqlerrmc);
	
	fprintf(fpDbg,"\nin [%s] ORACLE error occured :",msg);
	fprintf(fpDbg,"%s.\n",sqlca.sqlerrm.sqlerrmc);
	fflush(stdout) ;
	EXEC SQL ROLLBACK WORK ;
	
}	

/*====================================*/
/*=====  release from database   =====*/
/*====================================*/
void DetachFromDB()
{
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK WORK RELEASE;
	printf("\nsuccessful to release from database!\n");
	fprintf(fpLog,"\nsuccessful to release from database!\n");
}
/*get table name which need to be oparated from resource manage table*/
int GetTableNameFromManage(int *TableNum,char *cType,char *TableName)
{
	int	i = 0 ;
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar		vcTableName[64] ;
	varchar		vcFlag[1+1] ;
	int		iOnlineTime = 0 ;
	int		iOfflineTime = 0 ;
	varchar		vcSelectSql[256] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND goto table_no_found ;
	EXEC SQL WHENEVER SQLERROR goto table_err ;
	
	for(i==0;i<TABLE_NUM;i++)
	{
		arcTableName[i][0] = '\0' ;
		arcFlag[i][0] = '\0' ;
		iOnlineDur[i] = 0 ;
		iOfflineDur[i] = 0 ;
	}
	
	if(cType[0] == '1')
	{
		vcSelectSql.len = sprintf((char *)vcSelectSql.arr,"%s%s",
		"SELECT table_name,online_time,offline_time,flag FROM ",
		"stbs_tb_table_manage_infor WHERE flag = '1' or flag = '3'") ;
	}
	if(cType[0] == '2')
	{
		vcSelectSql.len = sprintf((char *)vcSelectSql.arr,"%s%s%s%s%s%s",
		"SELECT table_name,online_time,offline_time,flag FROM ",
		"stbs_tb_table_manage_infor WHERE flag = '",
		cType,
		"' AND table_name = '",
		TableName,
		"'") ;
	}
if( bDebug )
{
	fprintf(fpDbg,"vcSelectSql.arr is [%s]\n",vcSelectSql.arr) ;	
	fflush(stdout) ;
}

	EXEC SQL PREPARE table_cur FROM :vcSelectSql ;
	EXEC SQL DECLARE select_table CURSOR for table_cur ;
	EXEC SQL OPEN select_table ;
	
	i = 0 ;
	while(1)
	{
		EXEC SQL FETCH select_table INTO :vcTableName,:iOnlineTime,:iOfflineTime,:vcFlag ;
		vcTableName.arr[vcTableName.len] = 0 ;
		vcFlag.arr[vcFlag.len] = 0 ;
		
		memcpy(arcTableName[i],(char *)vcTableName.arr,strlen((char *)vcTableName.arr)) ;
		arcTableName[i][vcTableName.len] = 0 ;
		
		memcpy(arcFlag[i],(char *)vcFlag.arr,vcFlag.len) ;
		arcFlag[i][vcFlag.len] = 0 ;
if( bDebug )
{
	fprintf(fpDbg,"vcTableName.arr is [%s]\n",vcTableName.arr) ;	
	fflush(stdout) ;
}
		iOnlineDur[i] = iOnlineTime ;
		iOfflineDur[i] = iOfflineTime ;
		i++ ;
	}
table_no_found:
	EXEC SQL CLOSE select_table ;	
	*TableNum = i ;
	return SQLOK ;
table_err:
	fprintf(fpLog,"\nError occured when calling GetTableNameFromManage function!\n");
	fflush(stdout) ;
	
	EXEC SQL CLOSE select_table ;
	return SQLERR ;
}

int GetColumnOfTable(char *cHisTableName,char *cColumnFrom,char *cColumnTo,char *cType)
{
	
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar	vcColumnName[64] ;
	varchar	vcSelectSql[1028] ;
	int	iColumnNum = 0 ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND goto column_no_found ;
	EXEC SQL WHENEVER SQLERROR goto column_one_error ;
	
	vcSelectSql.len = sprintf((char *)vcSelectSql.arr,"%s%s%s",
	"SELECT column_name FROM user_tab_columns WHERE table_name = '",
	cHisTableName,
	"'") ;

if( bDebug )
{
	fprintf(fpDbg,"user_tab_columns vcSelectSql.arr[%s]\n",vcSelectSql.arr) ;	
	fflush(stdout) ;
}
		
	EXEC SQL PREPARE column_cur FROM :vcSelectSql ;
	EXEC SQL DECLARE select_column CURSOR for column_cur ;
	EXEC SQL OPEN select_column ;
		
	while(1)
	{
		EXEC SQL FETCH select_column INTO :vcColumnName ;
		vcColumnName.arr[vcColumnName.len] = 0 ;
		
		/*remove the disable records to history table*/
		if(strcmp((char *)cType,(char *)"1")==0
		||strcmp((char *)cType,(char *)"3")==0)
		{
			if(strncmp((char *)vcColumnName.arr,(char *)"EXP_DATE",8) == 0
			&& (strncmp(cHisTableName,(char *)"STBS_TB_SERVICE_INFOR",21) != 0
			||strncmp(cHisTableName,(char *)"STBS_TB_SERVICE_LIST",20) != 0))
			{
				strcat(cColumnFrom,(char *)"END_DATE") ;
			}
			else
			{
				strcat(cColumnFrom,(char *)vcColumnName.arr) ;
			}
		}
		/*remove the disable records to history table*/
		else
		{
			strcat(cColumnFrom,(char *)vcColumnName.arr) ;
		}
		strcat(cColumnFrom,(char *)",") ;
		
		strcat(cColumnTo,(char *)vcColumnName.arr) ;
		
		strcat(cColumnTo,(char *)",") ;
	}
column_no_found:
	EXEC SQL CLOSE  select_column ;
	cColumnFrom[strlen(cColumnFrom)-1] = 0 ;
	cColumnTo[strlen(cColumnTo)-1] = 0 ;
	return SQLOK ;
column_one_error:
	fprintf(fpLog,"\nError occured when calling GetColumnOfTable function!\n");
	fflush(stdout) ;
	return SQLERR ;		
		
}
int DeleteFromTable(char *cTableName,int iOnLineTime,char *cFlag)
{
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar	vcDeleteSql[1028] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND continue ;
	EXEC SQL WHENEVER SQLERROR continue ;
	
	vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%d",
	"delete ",
	cTableName,
	" where end_date < sysdate - ",
	iOnLineTime) ;
	
	vcDeleteSql.arr[vcDeleteSql.len] = 0 ;
	if(strcmp(cFlag,"3")==0)
		strcat((char *)vcDeleteSql.arr,(char *)" and end_date is null") ;
		
	EXEC SQL PREPARE delete_sql FROM :vcDeleteSql ;
	EXEC SQL EXECUTE delete_sql ;
	
	if(sqlca.sqlcode < 0)
	{
		fprintf(fpLog,"\nError occured when calling DeleteFromTable function!\n");
		fflush(stdout) ;
		return SQLERR ;
	}	
	EXEC SQL COMMIT ;
	return SQLOK ;
	
}

int InsertRecordToHistory(char *cTableName,char *cHisTableName,char *cColumnFrom,char *cColumnTo,int iOnlineTime,char *cFlag)
{
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar	vcInsertSql[1028] ;
	EXEC SQL END DECLARE SECTION ;
	
	int	iRet = 0 ;
	
	EXEC SQL WHENEVER NOT FOUND continue ;
	EXEC SQL WHENEVER SQLERROR goto insert_error ;
	
	vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s%s%d",
	"INSERT INTO ",
	cHisTableName,
	"(",
	cColumnTo,
	") SELECT ",
	cColumnFrom,
	" FROM ",
	cTableName,
	" WHERE end_date < sysdate - ",
	iOnlineTime) ;
	vcInsertSql.arr[vcInsertSql.len] = 0 ;
	if(strcmp(cFlag,"3")==0)
		strcat((char *)vcInsertSql.arr,(char *)" and end_date is null") ;
printf("vcInsertSql.len[%d]\n",vcInsertSql.len) ;
printf("vcInsertSql.arr[%s]\n",vcInsertSql.arr) ;
if( bDebug )
{
	fprintf(fpDbg,"vcInsertSql.len[%d]\n",vcInsertSql.len) ;
	fprintf(fpDbg,"vcInsertSql.arr[%s]\n",vcInsertSql.arr) ;	
	fflush(stdout) ;
}

	EXEC SQL PREPARE insert_sql FROM :vcInsertSql ;
	EXEC SQL EXECUTE insert_sql ;
	
	if(sqlca.sqlerrd[2] == 0)
	{
		EXEC SQL COMMIT ;
		return SQLOK ;
	}
		
	/*delete records that have been insert into history table */	
	iRet = DeleteFromTable(cTableName,iOnlineTime,cFlag) ;
	if(iRet == SQLERR)
		return SQLERR ;
	EXEC SQL COMMIT ;	
	return 	SQLOK ;
		
insert_error:
	EXEC SQL ROLLBACK ;
	fprintf(fpLog,"\nError occured when calling InsertRecordToHistory function!\n");
	fflush(stdout) ;
	OracleErrorProcess("InsertRecordToHistory") ;
	return SQLERR ;	

}
int MoveRecordToHistory(char *cType)
{
	int	iRet = 0 ;
	int	i = 0 ;
	int	iOnlineTime = 0 ;
	int	iTableNum = 0 ;
	char	cColumnFrom[1028] ;
	char	cColumnTo[1028] ;
	char	cTableName[64] ;
	char	cHisTableName[64] ;
	char	cFlag[1+1] ;
	
	iRet = GetTableNameFromManage(&iTableNum,cType,NULL) ;
	if(iRet==SQLERR)
		return SQLERR ;

	for(i=0;i<iTableNum;i++)
	{
		strcpy(cTableName,arcTableName[i]) ;
		cTableName[strlen(cTableName)] = 0 ;
		
		strcpy(cFlag,arcFlag[i]) ;
		cFlag[strlen(cFlag)] = 0 ;
		
		iOnlineTime = iOnlineDur[i] ;
		
		if(memcmp(cTableName,"STBS_TB_FORMULA_PARA_VALUE_WO",29)!=0)
			sprintf(cHisTableName,"%s%s",cTableName,(char *)"_H") ;
		else
			sprintf(cHisTableName,"%s%s",cTableName,(char *)"H") ;
			
		cHisTableName[strlen(cHisTableName)] = 0 ;
		
		memset(cColumnFrom,0,sizeof(cColumnFrom)) ;
		memset(cColumnTo,0,sizeof(cColumnTo)) ;
		
		iRet = GetColumnOfTable(cHisTableName,cColumnFrom,cColumnTo,cFlag) ;
		if(iRet == SQLERR)
			return SQLERR ;
			
		iRet = InsertRecordToHistory(cTableName,cHisTableName,cColumnFrom,cColumnTo,iOnlineTime,cFlag) ;
		if(iRet == SQLERR)
			return SQLERR ;
	}
	return  SQLOK ;	
}
int ProcEveryWorkOrder(int sequence,char *tx_no,char opt_type)
{
	EXEC SQL BEGIN DECLARE SECTION ;
		varchar	vcInsertSql[2056] ;
		varchar	vcDeleteSql[2056] ;
	EXEC SQL END DECLARE SECTION ;
	
	char		cColumnFrom[1024] ;
	char		cColumnTo[1024] ;
	
	EXEC SQL WHENEVER NOT FOUND continue ;
	EXEC SQL WHENEVER SQLERROR goto every_error ;
	
	if(opt_type == 'C')
	{
		memset(cColumnFrom,0,sizeof(cColumnFrom)) ;
		memset(cColumnTo,0,sizeof(cColumnTo)) ;
	
		switch(sequence)
		{
			case 1:
				GetColumnOfTable("STBS_TB_ACCESS_NO_WO_H",cColumnFrom,cColumnTo,"2") ;
				
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_access_no_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_access_no_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_access_no_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 2:
				GetColumnOfTable("STBS_TB_ADDITIONAL_NO_WO_H",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_additional_no_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_additional_no_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_additional_no_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 3:
				GetColumnOfTable("STBS_TB_CHNG_WO_H",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_chng_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_chng_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_chng_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 4:
				GetColumnOfTable("STBS_TB_DIRECT_DEBIT_WO_H",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_direct_debit_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_direct_debit_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_direct_debit_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 5:
				GetColumnOfTable("STBS_TB_FORMULA_PARA_VALUE_WOH",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_formula_para_value_woh(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_formula_para_value_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_formula_para_value_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 6:
				GetColumnOfTable("STBS_TB_NEW_ACCT_WO_H",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_new_acct_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_new_acct_wo",
					" WHERE tx_no = '",
					tx_no,
					"')") ;
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s",
					"DELETE stbs_tb_new_acct_wo",
					" WHERE tx_no = '",
					tx_no,
					"'") ;
				break ;
			case 7:
				GetColumnOfTable("STBS_TB_NEW_ADDRESS_WO_H",cColumnFrom,cColumnTo,"2") ;
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s",
					"INSERT INTO stbs_tb_new_address_wo_h(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM stbs_tb_new_address_wo",
					" WHERE tx_no = '",