bak_workorder.pc

数据扫描程序

			
		EXEC SQL PREPARE delete_temp FROM :vcDeleteTxNo ;
		EXEC SQL EXECUTE delete_temp ;
		
		EXEC SQL WHENEVER NOT FOUND goto work_end ;
	
		if(opt_type == 'C')
			vcSelectTxNo.len = sprintf((char *)vcSelectTxNo.arr,"%s%s%d%s%d%s",
			"SELECT tx_no FROM stbs_tb_wo ",
			"WHERE to_date(to_char(complete_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
			iOnlineDur[0],
			" OR (to_date(to_char(in_cbs_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
			iOnlineDur[0],
			" AND proc_code like '%ERR')") ;
		else
			vcSelectTxNo.len = sprintf((char *)vcSelectTxNo.arr,"%s%s%d%s%d%s",
			"SELECT tx_no FROM stbs_tb_wo_h ",
			"WHERE to_date(to_char(complete_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
			iOfflineDur[0],
			" OR (to_date(to_char(in_cbs_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
			iOfflineDur[0],
			" AND proc_code like '%ERR')") ;
			
		vcSelectTxNo.arr[vcSelectTxNo.len] = 0 ;
printf("aaaaaavcSelectTxNo[%s]\n",vcSelectTxNo.arr) ;					
		EXEC SQL PREPARE select_txno FROM :vcSelectTxNo ;
		EXEC SQL DECLARE select_tx CURSOR for select_txno ;
		EXEC SQL OPEN select_tx ;
		while(1)
		{
			EXEC SQL FETCH select_tx INTO :vcTxNo ;
			vcTxNo.arr[vcTxNo.len] = 0 ;
if( bDebug )
{
	fprintf(fpDbg,"vcTxNo.arr is [%s] ProcessWorkOrder\n",vcTxNo.arr) ;
	fflush(stdout) ;
}
			
			iRet = ProcessEveryTabSeq((char *)vcTxNo.arr,opt_type) ;
			if(iRet == SQLERR)
			{
				EXEC SQL CLOSE 	select_tx ;
				EXEC SQL ROLLBACK ;
				return SQLERR ;
			}
			iRet = DeleteWorkOrder((char *)vcTxNo.arr,opt_type) ;
			if(iRet == SQLERR)
			{
				EXEC SQL CLOSE 	select_tx ;
				EXEC SQL ROLLBACK ;
				return SQLERR ;
			}
		}
		
	}
	
	return SQLOK ;
work_end:
	printf("bbbbvcSelectTxNo [%s]\n",vcSelectTxNo.arr) ;
	EXEC SQL CLOSE 	select_tx ;
	EXEC SQL COMMIT ;
	return SQLOK ;
work_error:
	EXEC SQL ROLLBACK ;
	fprintf(fpLog,"\nError occured when calling ProcessWorkOrder function!\n");
	fflush(stdout) ;
	OracleErrorProcess("ProcessWorkOrder") ;
	return SQLERR ;		
}
int ProcessRevenue_h(char *cTableName,int iOfflineTime)
{
	char	cHisTableName[31] ;
	
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar		vcPeriod[6+1] ;
	varchar		vcCycle[2+1] ;
	varchar		vcSelectPerCycle[512] ;
	varchar		vcDeleteSql[2056] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND goto work_end ;
	EXEC SQL WHENEVER SQLERROR goto work_error ;
	
	sprintf(cHisTableName,"%s%s",cTableName,"_H") ;
	cHisTableName[strlen(cHisTableName)] = 0 ;
	
	vcSelectPerCycle.len = sprintf((char *)vcSelectPerCycle.arr,"%s%s%d",
	"SELECT period,cycle FROM stbs_tb_para ",
	"WHERE to_date(to_char(end_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
	iOfflineTime) ;
	
	vcSelectPerCycle.arr[vcSelectPerCycle.len] = 0 ;
printf("vcSelectPerCycle[%s]\n",vcSelectPerCycle.arr) ;
fflush(stdout) ;	
	EXEC SQL PREPARE select_pcycle_h FROM :vcSelectPerCycle ;
	EXEC SQL DECLARE select_pc_h CURSOR for select_pcycle_h ;
	EXEC SQL OPEN select_pc_h ;
	while(1)
	{
		EXEC SQL FETCH select_pc_h INTO :vcPeriod, :vcCycle ;
		vcPeriod.arr[vcPeriod.len] = 0 ;
		vcCycle.arr[vcCycle.len] = 0 ;
printf("vcPeriod[%s]\n",vcPeriod.arr) ;	
printf("vcCycle[%s]\n",vcCycle.arr) ;	
if( bDebug )
{
	fprintf(fpDbg,"vcPeriod.arr is [%s] ProcessRevenue\n",vcPeriod.arr) ;
	fprintf(fpDbg,"vcCycle.arr is [%s] ProcessRevenue\n",vcCycle.arr) ;
	fflush(stdout) ;
}			
	vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s%s%s",
		"DELETE ",
		cTableName,
		" WHERE acct_period = '",
		vcPeriod.arr,
		"' AND acct_cycle = '",
		vcCycle.arr,
		"'") ;
	vcDeleteSql.arr[vcDeleteSql.len] = 0 ;
if( bDebug )
{
	fprintf(fpDbg,"vcDeleteSql.arr is [%s] ProcessRevenue\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}

		EXEC SQL PREPARE delete_sql_h FROM :vcDeleteSql ;
		EXEC SQL EXECUTE delete_sql_h ;
	}
	
work_end:
	printf("work_end: vcPeriod[%s]\n",vcPeriod.arr) ;	
	printf("work_end: vcCycle[%s]\n",vcCycle.arr) ;	
	EXEC SQL CLOSE 	select_pc_h ;
	EXEC SQL COMMIT ;
	return SQLOK ;
work_error:
	EXEC SQL CLOSE 	select_pc_h ;
	fprintf(fpLog,"\nError occured when calling ProcessRevenue_h function!\n");
	fflush(stdout) ;
	OracleErrorProcess("ProcessRevenue_h") ;
	return SQLERR ;		
}
int ProcessRevenue()
{
	int	TableNum = 0 ;
	int	iRet = 0 ;
	int	i = 0 ;
	int	RecNum = 0 ;
	char	cTableNameArray[4][31] ;
	char	cHisTableName[31] ;
	char	cColumnFrom[512] ;
	char	cColumnTo[512] ;
	
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar		vcPeriod[6+1] ;
	varchar		vcCycle[2+1] ;
	varchar		vcSelectPerCycle[256] ;
	varchar		vcInsertSql[2056] ;
	varchar		vcDeleteSql[2056] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER SQLERROR goto work_error ;
	
	iRet = GetTableNameFromManage(&TableNum,(char *)"2",(char *)"STBS_TB_REVENUE") ;
	if(iRet == SQLERR)
		return SQLERR ;
	
	if(TableNum != 0)
	{
		/*the following four tables should be processed*/
		strcpy(cTableNameArray[0],"STBS_TB_REVENUE") ;
		strcpy(cTableNameArray[1],"STBS_TB_REVENUE_CHARGE") ;
		strcpy(cTableNameArray[2],"STBS_TB_REVENUE_USAGE") ;
		strcpy(cTableNameArray[3],"STBS_TB_REVENUE_USAGE_DTL") ;
	
		vcSelectPerCycle.len = sprintf((char *)vcSelectPerCycle.arr,"%s%s%d",
			"SELECT period,cycle FROM stbs_tb_para ",
			"WHERE to_date(to_char(end_date,'yyyymmdd'),'yyyymmdd') < to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - ",
			iOnlineDur[0]) ;
		vcSelectPerCycle.arr[vcSelectPerCycle.len] = 0 ;
printf("vcSelectPerCycle[%s]\n",vcSelectPerCycle.arr) ;		
		EXEC SQL PREPARE select_pcycle FROM :vcSelectPerCycle ;
		EXEC SQL DECLARE select_pc CURSOR for select_pcycle ;
		EXEC SQL OPEN select_pc ;
		while(1)
		{
			
			EXEC SQL WHENEVER NOT FOUND goto work_end ;
			
			EXEC SQL FETCH select_pc INTO :vcPeriod, :vcCycle ;
			vcPeriod.arr[vcPeriod.len] = 0 ;
			vcCycle.arr[vcCycle.len] = 0 ;

if( bDebug )
{
	fprintf(fpDbg,"vcPeriod.arr is [%s] ProcessRevenue\n",vcPeriod.arr) ;
	fprintf(fpDbg,"vcCycle.arr is [%s] ProcessRevenue\n",vcCycle.arr) ;
	fflush(stdout) ;
}			
			for(i=0;i<4;i++)
			{
				memset(cColumnFrom,0,sizeof(cColumnFrom)) ;
				memset(cColumnTo,0,sizeof(cColumnTo)) ;
				
				memset(cHisTableName,0,sizeof(cHisTableName)) ;
				sprintf(cHisTableName,"%s%s",cTableNameArray[i],"_H") ;
				
				/*get fields of table*/
				iRet = GetColumnOfTable(cHisTableName,cColumnFrom,cColumnTo,"2") ;
				if(iRet == SQLERR)
					return SQLERR ;
				
				
				EXEC SQL WHENEVER NOT FOUND continue ;
				
				vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s%s%s%s%s%s",
					"INSERT INTO ",
					cHisTableName,
					"(",
					cColumnTo,
					") (SELECT ",
					cColumnFrom,
					" FROM ",
					cTableNameArray[i],
					" WHERE acct_period = '",
					vcPeriod.arr,
					"' AND acct_cycle = '",
					vcCycle.arr,
					"')") ;
				vcInsertSql.arr[vcInsertSql.len] = 0 ;
printf("vcInsertSql[%s]\n",vcInsertSql.arr) ;	
if( bDebug )
{
	fprintf(fpDbg,"vcInsertSql.arr is [%s] ProcessRevenue\n",vcInsertSql.arr) ;
	fflush(stdout) ;
}
				EXEC SQL PREPARE insert_sql FROM :vcInsertSql ;
				EXEC SQL EXECUTE insert_sql ;
				
				if(sqlca.sqlerrd[2] == 0)
					continue ;
					
				vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s%s%s",
					"DELETE ",
					cTableNameArray[i],
					" WHERE acct_period = '",
					vcPeriod.arr,
					"' AND acct_cycle = '",
					vcCycle.arr,
					"'") ;
				vcDeleteSql.arr[vcDeleteSql.len] = 0 ;
printf("vcDeleteSql[%s]\n",vcDeleteSql.arr) ;	
if( bDebug )
{
	fprintf(fpDbg,"vcDeleteSql.arr is [%s] ProcessRevenue\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}

				EXEC SQL PREPARE delete_sql FROM :vcDeleteSql ;
				EXEC SQL EXECUTE delete_sql ;
				
				iRet = ProcessRevenue_h(cTableNameArray[i],iOfflineDur[i]) ;
				if(iRet != SQLOK )
					goto work_error ;
					
				RecNum = RecNum + sqlca.sqlerrd[2] ;
				if(RecNum >=1000)
				{
					EXEC SQL COMMIT ;
					RecNum	= 0 ;
				}
			}
		}
	}
	return SQLOK ;
work_end:
	EXEC SQL CLOSE 	select_pc ;
	EXEC SQL COMMIT ;
	return SQLOK ;
work_error:
	EXEC SQL CLOSE 	select_pc ;
	fprintf(fpLog,"\nError occured when calling ProcessRevenue function!\n");
	fflush(stdout) ;
	OracleErrorProcess("ProcessRevenue") ;
	return SQLERR ;		
}
int deal_statement_dtl(char *cAcctId,char *cCycle,char *cPeriod,char cFlag)
{
	int	iRet = 0 ;
	char	cColumnFrom[512] ;
	char	cColumnTo[512] ;
	
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar		vcInsertSql[1028] ;
	varchar		vcDeleteSql[1028] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND goto deal_dtl_no ;
	EXEC SQL WHENEVER SQLERROR goto deal_dtl_error ;
	
	if(cFlag = 'C')
	{
		memset(cColumnFrom,0,sizeof(cColumnFrom)) ;
		memset(cColumnTo,0,sizeof(cColumnTo)) ;
        	
		iRet = GetColumnOfTable("STBS_TB_BILL_STATEMENT_DTL_H",cColumnFrom,cColumnTo,"2") ;
		if(iRet == SQLERR)
			return SQLERR ;
			
		vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s",
			"INSERT INTO ",
			"STBS_TB_BILL_STATEMENT_DTL_H",
			"(",
			cColumnTo,
			") SELECT ",
			cColumnFrom,
			" FROM ",
			"STBS_TB_BILL_STATEMENT_DTL",
			" WHERE ACCT_PERIOD = '",
			cPeriod,
			"' AND ACCT_CYCLE = '",
			cCycle,
			"' AND account_id = '",
			cAcctId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg," C vcInsertSql.arr is [%s] deal_statement_dtl\n",vcInsertSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE insert_sql1 FROM :vcInsertSql ;
		EXEC SQL EXECUTE insert_sql1 ;
		
		if(sqlca.sqlerrd[2] == 0 )
			return SQLOK ;
			
		vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s%s%s%s%s",
			"DELETE ",
			"STBS_TB_BILL_STATEMENT_DTL",
			" WHERE ACCT_PERIOD = '",
			cPeriod,
			"' AND ACCT_CYCLE = '",
			cCycle,
			"' AND account_id = '",
			cAcctId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg,"vcDeleteSql.arr is [%s] deal_statement_dtl\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE delete_sql1 FROM :vcDeleteSql ;
		EXEC SQL EXECUTE delete_sql1 ;
	}
	else if(cFlag = 'H')
	{
		vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s%s%s%s%s",
			"DELETE ",
			"STBS_TB_BILL_STATEMENT_DTL_H",
			" WHERE ACCT_PERIOD = '",
			cPeriod,
			"' AND ACCT_CYCLE = '",
			cCycle,
			"' AND account_id = '",
			cAcctId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg,"H vcDeleteSql.arr is [%s] deal_statement_dtl\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE delete_sql1 FROM :vcDeleteSql ;
		EXEC SQL EXECUTE delete_sql1 ;
	}

	return SQLOK ;
deal_dtl_no:
	return SQLOK ;
deal_dtl_error:
	fprintf(fpLog,"\nError occured when calling deal_statement_dtl function!\n");
	fflush(stdout) ;
	OracleErrorProcess("deal_statement_dtl") ;
	return SQLERR ;	
}
int deal_stmt_remain(char *cStatemId,char *cCycle,char *cPeriod,char cFlag)
{
	int	iRet = 0 ;
	char	cColumnFrom[1028] ;
	char	cColumnTo[1028] ;
	
	EXEC SQL BEGIN DECLARE SECTION ;
	varchar		vcInsertSql[512] ;
	varchar		vcDeleteSql[512] ;
	EXEC SQL END DECLARE SECTION ;
	
	EXEC SQL WHENEVER NOT FOUND goto deal_stmt_no ;
	EXEC SQL WHENEVER SQLERROR goto deal_stmt_error ;
	
	if(cFlag = 'C')
	{
		memset(cColumnFrom,0,sizeof(cColumnFrom)) ;
		memset(cColumnTo,0,sizeof(cColumnTo)) ;
					
		iRet = GetColumnOfTable("STBS_TB_BILL_STMT_REMAIN_H",cColumnFrom,cColumnTo,"2") ;
		if(iRet == SQLERR)
			return SQLERR ;
			
		vcInsertSql.len = sprintf((char *)vcInsertSql.arr,"%s%s%s%s%s%s%s%s%s%s%s",
			"INSERT INTO ",
			"STBS_TB_BILL_STMT_REMAIN_H",
			"(",
			cColumnTo,
			") SELECT ",
			cColumnFrom,
			" FROM ",
			"STBS_TB_BILL_STMT_REMAIN",
			" WHERE statement_id = '",
			cStatemId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg,"C vcInsertSql.arr is [%s] deal_stmt_remain\n",vcInsertSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE insert_sql2 FROM :vcInsertSql ;
		EXEC SQL EXECUTE insert_sql2 ;
		
		if(sqlca.sqlerrd[2] == 0)
			return SQLOK ;
			
		vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s",
			"DELETE ",
			"STBS_TB_BILL_STMT_REMAIN",
			" WHERE statement_id = '",
			cStatemId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg,"H vcDeleteSql.arr is [%s] deal_stmt_remain\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE delete_sql2 FROM :vcDeleteSql ;
		EXEC SQL EXECUTE delete_sql2 ; 
	}
	else if(cFlag = 'H')
	{
		vcDeleteSql.len = sprintf((char *)vcDeleteSql.arr,"%s%s%s%s%s",
			"DELETE ",
			"STBS_TB_BILL_STMT_REMAIN",
			" WHERE statement_id = '",
			cStatemId,
			"'") ;
if( bDebug )
{
	fprintf(fpDbg,"vcDeleteSql.arr is [%s] deal_stmt_remain\n",vcDeleteSql.arr) ;
	fflush(stdout) ;
}
		EXEC SQL PREPARE delete_sql2 FROM :vcDeleteSql ;
		EXEC SQL EXECUTE delete_sql2 ; 
	}
	return SQLOK ;
deal_stmt_no:
	return SQLOK ;
deal_stmt_error:
	fprintf(fpLog,"\nError occured when calling deal_stmt_remain function!\n");