datasplit.pc

一个LINUX下面

/************************************************************************************
DataSplit.pc
说明：
1 本程序主要是处理历史数据分离，主要包括A,B,C,D,E,F,G类表(目前常德局只有A,B,C类)。
2 要处理的表可在配置表中配置，建议每类表分别处理，特别是A类表，这样的话，如果有异常好灰愎。
3 建议每月做一次，如果隔月再做，则在配置表中对要倒换的表配两条不同账务月的记录（这只是针对C类
表,A,B类不是按账务月倒换的）。
4 如果删或建索引失败，程序不退出，以后可以手动建索引。
开发记录:时间：20050719  编码：xyf      修改记录：
************************************************************************************/
#include <sqlca.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <time.h>

#include "DataSplit.h"

/************************************************************************************
//功能:主函数，启动带两个连库的参数，
//如：nohup DataSplit bill/mllz89816 hisbill/hisbill@jfhis cdweb/cdweb& 。
//参数:int argc,char **argv
//返回值:>0 成功   <0 失败
************************************************************************************/
int main(int argc,char **argv)
{
	int iMaxSeq = 0;
	int iRtn  = 0;			/*获得函数调用的返回值*/
	int sCnts = 0;			/*记录表处理成功的数量*/
	int fCnts = 0;			/*记录表处理失败的数量*/
	int doFlag;				/*表示动作成功失败的标志*/
	long lSRows = 0;		/*记录表处理之前的记录数*/
	long lDRows = 0;		/*记录表处理之后的记录数*/
	long lRows1 = 0;		/*中间变量*/
	long lRows2 = 0;		/*中间变量*/
	char szDateTime[15];	/*保存时间值，以YYYYMMDDHIMMSS表示*/
	char szANMonth[3];		/*本次帐务月的N个月前的帐务月的月，即szCBMonth-N，对应acct_item表
	                          	的account_month字段，以MM表示*/
	struct Tab_Parameter pTableSet[900];
	int i, j, i_Nums;
	char achSql[1000];
	char chTemp1[500];
	char chTemp2[400];
	char chTemp3[400];
	char *pTokens=NULL;
	
	sprintf( g_strLogLine, "************************Progress Begin***********************\n");
	WriteLog( g_strLogLine );
	
	memset( szDateTime, '\0', sizeof(szDateTime) );
	memset( g_strYearMonth, '\0', sizeof(g_strYearMonth) );
	memset( szANMonth,  '\0', sizeof(szANMonth ) );

	if (argc<4)
	{
		printf("Please 运行./run or input parameter:\n 如：nohup DataSplit bill/mllz89816 hisbill/hisbill@jfhis cdweb/cdweb& \n" );
		sprintf( g_strLogLine, "Please 运行./run or input parameter:\n 如：nohup DataSplit bill/mllz89816 hisbill/hisbill@jfhis cdweb/cdweb& \n" );
		WriteLog( g_strLogLine );
		return -1;
	}
	strcpy(g_strSrcDB,argv[1]);
	strcpy(g_strDestDB,argv[2]);
	strcpy(g_strTempDB,argv[3]);
	strcpy(g_strTempUser,argv[3]);
	pTokens=strtok(g_strTempUser,"/");
	if (argc==5)/*第4个参数等于"pub"*/
		strcpy(g_strPublic,argv[4]);
	else memset(g_strPublic,'\0',sizeof(g_strPublic));
	
	sleep(5);
	
	/*先测试3个用户连库操作*/
	if ( ConnectDB(g_strSrcDB)!=1 )/*连接在线数据库失败*/
	{
		sprintf( g_strLogLine, "user [%s] connect online database failed!!\n\n",g_strSrcDB);
		WriteLog( g_strLogLine );
		return -1;
	}
	iRtn = ReleaseDB();
	if ( ConnectDB(g_strDestDB)!=1 )/*连接历史数据库失败*/
	{
		sprintf( g_strLogLine, "user [%s] connect history database failed!!\n\n",g_strDestDB);
		WriteLog( g_strLogLine );
		return -1;
	}
	iRtn = ReleaseDB();
	if ( ConnectDB(g_strTempDB)!=1 )/*连接在线数据库失败*/
	{
		sprintf( g_strLogLine, "user [%s] connect online database failed!!\n\n",g_strTempDB);
		WriteLog( g_strLogLine );
		return -1;
	}
	iRtn = ReleaseDB();
	
	sprintf( g_strLogLine, "users [%s][%s][%s] connect database succeed\n",g_strSrcDB,g_strDestDB,g_strTempDB);
	WriteLog( g_strLogLine );
	
	/*从billing_cycle表中取到当前帐务月*/
	if ( ConnectDB(g_strSrcDB)!=1 )
	{
		printf("connect db failed!!\n\n");
		return 0;
	}
	/*取得当前帐务月*/
	iRtn = getBYMonth();
	if ( iRtn == 0 )
	{
		printf("get account_date failed!\n\n");
		iRtn = ReleaseDB();
		return 0;
	}
	strcpy(g_strPreYMonth,g_strYearMonth);
	/*断开数据库的连接*/
	iRtn = ReleaseDB();

	/*连接历史数据库*/
	if ( ConnectDB(g_strDestDB)!=1 )
	{
		printf("connect hisDB failed!!\n\n");
		return 0;
	}
	
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/*取得当前帐务月倒换日志中的最大序列*/
	sprintf(achSql,"SELECT nvl(max(seq),0) FROM his_expimp_log WHERE accounte_date = '%s' ",g_strYearMonth);
	EXEC SQL PREPARE stmt09 FROM :achSql;
	EXEC SQL DECLARE cur09 CURSOR FOR stmt09;
	EXEC SQL OPEN cur09;
	if (sqlca.sqlcode)
	{
	    sprintf( g_strLogLine, " main()! cur09 error code: %d --- %s\n",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
		WriteLog( g_strLogLine );
	    return -900;
	}
	EXEC SQL FETCH cur09 INTO :iMaxSeq;
	EXEC SQL CLOSE cur09;
		
	/*给取得的最大序列加1成当前序列*/
	iMaxSeq += 1;
	/*如果是本帐务月的第一次则删除以前所有的DMP文件*/
	if ( iMaxSeq == 1 )
	{
		system("rm ./dmp/*.dmp");
		system("rm ./log/*.log");
		system("rm ./pfile/*.pfile");
	}

	/**/
	sprintf(achSql,"INSERT INTO his_expimp_log(accounte_date,seq,begin_time) VALUES('%s',%d,sysdate)",g_strYearMonth,iMaxSeq);
	EXEC SQL PREPARE select_stmt FROM :achSql;
	EXEC SQL EXECUTE select_stmt;
	/*后一个条件(table_name NOT IN) 保证当前账务月只成功做一次,on_system='zj'这是做自缴表*/
	sprintf(chTemp1,"INSERT INTO his_expimp_tab_log(accounte_date,seq,table_name,on_system,do_class,succ_flag) ");
	sprintf(chTemp2,"SELECT '%s', %d,upper(table_name),lower(on_system),do_class,'0' FROM his_expimp_tab_cfg ",g_strYearMonth,iMaxSeq);
	sprintf(chTemp3,"WHERE lower(on_system)='zj' and state=1 and table_name NOT IN (SELECT table_name FROM his_expimp_tab_log WHERE accounte_date='%s' AND succ_flag='T') ",g_strYearMonth);
	sprintf( achSql, "%s%s%s",chTemp1,chTemp2,chTemp3);
	EXEC SQL PREPARE select_stmt FROM :achSql;
	EXEC SQL EXECUTE select_stmt;
	
	/*取要处理的表信息*/
	/*后一个条件(table_name NOT IN) 保证当前账务月只成功做一次,on_system='zj'这是做自缴表*/
	sprintf(chTemp1,"SELECT upper(table_name),lower(on_system),do_class,nvl(reserve_months,0),nvl(Account_Date,'0') FROM his_expimp_tab_cfg ");
	sprintf(chTemp2,"WHERE lower(on_system)='zj' and state=1 and table_name NOT IN (SELECT table_name FROM his_expimp_tab_log WHERE accounte_date='%s' AND succ_flag='T') order by do_class,table_name,Account_Date ",g_strYearMonth);
	sprintf( achSql, "%s%s",chTemp1,chTemp2);
	EXEC SQL PREPARE stmt10 FROM :achSql;
	EXEC SQL DECLARE cur10 CURSOR FOR stmt10;
	EXEC SQL OPEN cur10;
	if (sqlca.sqlcode)
	{
	    sprintf( g_strLogLine, " Cann't open cursor(cur10)! error code: %d --- %s",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
		WriteLog( g_strLogLine );
	    return -900;
	}
	i=0;
	while(1)
	{
	    EXEC SQL WHENEVER NOT FOUND CONTINUE;
	    EXEC SQL WHENEVER SQLERROR GOTO goto_err01;
	    EXEC SQL FETCH cur10 INTO :pTableSet[i].Table_Name,:pTableSet[i].On_System,:pTableSet[i].Do_Class,:pTableSet[i].Reserve_Months,:pTableSet[i].Account_Date;
	    if(sqlca.sqlcode) break;
	    i=i+1;
	}/*end while*/
	EXEC SQL CLOSE cur10;
	
	/*总处理表个数*/
	sprintf(chTemp1,"SELECT COUNT(table_name) FROM his_expimp_tab_cfg ");
	sprintf(chTemp2,"WHERE lower(on_system)='zj' and state=1 and table_name NOT IN (SELECT table_name FROM his_expimp_tab_log WHERE accounte_date='%s' AND succ_flag='T')  ",g_strYearMonth);
	sprintf( achSql, "%s%s",chTemp1,chTemp2);
	EXEC SQL PREPARE stmt11 FROM :achSql;
	EXEC SQL DECLARE cur11 CURSOR FOR stmt11;
	EXEC SQL OPEN cur11;
	if (sqlca.sqlcode)
	{
	    sprintf( g_strLogLine, " main()! cur11 error code: %d --- %s\n",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
		WriteLog( g_strLogLine );
	    return -900;
	}
	EXEC SQL FETCH cur11 INTO :i_Nums;
	EXEC SQL close cur11;
	/*总处理表个数is 0,则返回*/
	if(i_Nums==0)
	{
		sprintf( g_strLogLine, "all tables to be dealed is 0! progress is over\n" );
		WriteLog( g_strLogLine );
		sprintf( g_strLogLine, "1 检查his_expimp_tab_log当前账务月是否己成功(succ_flag='T')做过一次；\n\t\t2 检查his_expimp_tab_cfg中要处理表的状态（state='1'）\n" );
		WriteLog( g_strLogLine );
		ReleaseDB();
		sprintf( g_strLogLine, "************************Progress   End***********************\n\n");
		WriteLog( g_strLogLine );
		return 0;	
	}
	
	iRtn = CommitWork();
	
	/**************************临时表创建：开始****************************************/
	/*进程启动创建一次,该临时表只用于A类表（总账，payment,bill）的处理,没有A类则不创建*/
	sprintf( g_strLogLine, "Now create temp table his_billtohisdb\n" );
	WriteLog( g_strLogLine );
	if( ConnectDB(g_strSrcDB)!=1 )
	{
		sprintf( g_strLogLine, " Connect to source db failed! \n\n" );
		WriteLog( g_strLogLine );
		return 0;
	}
	/*建临时表以及临时表的索引*/ 
	sprintf( g_strLogLine, "Now create temp table and index\n" );
	WriteLog( g_strLogLine );
	if(pTableSet[0].Do_Class=='A' && g_strPublic[0]=='\0')/*A类表创建 但公免的不创建*/
	{
		iRtn = crtZjTmpTab();
		if ( iRtn == 0 )
		{
			sprintf( g_strLogLine, " create temp table his_billtohisdb failed! \n\n" );
			WriteLog( g_strLogLine );
			return 0;
		}
		/*从临时表中删除发票流水号在总账表中对应的状态='22T'的记录(送托记录)*/
		/*sprintf( g_strLogLine, " delete his_billtohisdb where bill_serial_nbr=送托.\n" );
		WriteLog( g_strLogLine );
		DelTempTabRecord();
		*/
		/*从临时表中删除流水号为后台销账（后台划账，网上银行扣款）记录*/
		sprintf( g_strLogLine, " delete his_billtohisdb where pay_serial_nbr=后台销账.\n" );
		WriteLog( g_strLogLine );
		DelTempTabBackPay();
	
		/*payment中预存款记录补充到临时表*/
		sprintf( g_strLogLine, " insert his_billtohisdb values(预存款记录).\n" );
		WriteLog( g_strLogLine );
		IntTempTabPrepay();
	}
	else
	{
		sprintf( g_strLogLine, " There is not A class table,or is deal public phone.must not create temp table! \n" );
		WriteLog( g_strLogLine );
	}
	
	iRtn = ReleaseDB();
	/******************************临时表创建：结束*************************************/

	/*循环处理将要倒换的表，表的信息放在结构体pTableSet[i]中*/
	for ( i = 0; i < i_Nums; i++ )
	{
		iRtn = 0;
		/*去空格*/
		for ( j = 0; j < strlen( pTableSet[i].Table_Name ); j++ )
		{
			if ( isspace( pTableSet[i].Table_Name[j] ) != 0 )
			{
				pTableSet[i].Table_Name[j] = '\0';
				break;
			}
		}
		for ( j = 0; j < strlen( pTableSet[i].Account_Date ); j++ )
		{
			if ( isspace( pTableSet[i].Account_Date[j] ) != 0 )
			{
				pTableSet[i].Account_Date[j] = '\0';
				break;
			}
		}
		/**/
		if ( ConnectDB(g_strDestDB) != 1 )
		{
			/**/
			printf("connect hisDB failed!!\n\n");
			return 0;
		}
		if ( pTableSet[i].Table_Name[0] != '\0' )
		{
			sprintf(chTemp1,"UPDATE his_expimp_tab_log SET begin_time=sysdate ");
			sprintf(chTemp2,"WHERE accounte_date='%s' AND seq=%d AND table_name=upper('%s') ",g_strPreYMonth,iMaxSeq,pTableSet[i].Table_Name);
			sprintf( achSql, "%s%s",chTemp1,chTemp2);
			EXEC SQL PREPARE select_stmt FROM :achSql;
			EXEC SQL EXECUTE select_stmt;
		}
		iRtn = CommitWork();

		/*根据结构体中表内容中表分类信息调用相应的处理过程*/
		switch ( pTableSet[i].Do_Class )
		{
			/*按照数据倒换的特性分为N类表*/
			case 'A':
				/*A类表，处理各总账表、payment和bill三个表*/
				iRtn = DoAClass( pTableSet[i] );
				break;
			case 'B':
				/*B类表，余额表，停开机表*/
				iRtn = DoBClass( pTableSet[i] );
				break;
			case 'C':
				/*C类表，是计费详账，详帐类表*/
				iRtn = DoCClass( pTableSet[i] );
				break;
			case 'D':
				/*D类表，是计费统计类表*/
				iRtn = DoDClass( pTableSet[i] );
				break;
			case 'E':
				/*E类表，是参数表或资料表，不保留老的只倒新的，如item_area表等*/
				iRtn = DoEClass( pTableSet[i] );
				break;
			case 'F':
				/*F类表，是参数表或资料表，按照年月保留以前帐务月，如serv表等*/
				iRtn = DoFClass( pTableSet[i] );
				break;
			case 'G':
				/*G类表，是自缴BILL_REPORT_STAT表，和D类相似，这个表以pay_year和pay_month这两个字段相当月account_date*/
				iRtn = DoGClass( pTableSet[i] );
				break;
			default:
				/*未知的类型，不做处理*/
				sprintf( g_strLogLine, " 未知的表处理类型，不做处理! \n" );
				WriteLog( g_strLogLine );
				iRtn = -1;
				break;
		}/*end switch*/
		if ( iRtn == 1 )
			sCnts += 1;
		else
			fCnts += 1;
		if ( ConnectDB(g_strDestDB) != 1 )
		{
			/**/
			printf("connect hisDB failed!!\n\n");
			return 0;
		}
		if ( iRtn == 1 )
		{
			sprintf(chTemp1,"UPDATE his_expimp_tab_log SET end_time=sysdate,succ_flag='T' ");