dbfunc.cp

UNIX/LINUX平台下面SMS网管原代码

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <memory.h>
#include <sys/types.h>

#include "etc.h"
#include "bms_micro.h"
#include "bms_strcdef.h"

#include "sybtesql.h"
#include "sybhesql.h"

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE "bms_sql.h";

void error_handler();
void warning_handler();
void notfound_handler();

int  DB_connectDB(char *uid, char *pwd, char *svc);
void DB_closeDB();
void DB_Commit();
void DB_Rollback();

int  DB_search_pin(char *sname, short *ictype,char *icno, char *pin, char *cid);
int  DB_reset_pin(char *cid, short bid, char *pin);
int  DB_search_acct(char *acct, char *cid, char *aid, int type);
int  DB_check_bid_scope(char *cid, char *acct, short bid);
int  DB_check_broker( char *cid, short bid, int *st);
int  DB_check_co_pwd( char *cid, short bid, char *cpwd);
int  DB_add_broker_err(char *cid,int bid);
int  DB_reset_broker_err(char *cid,short bid);
int  DB_search_ps_acc_info( PS_ACCINFO *pS_aCcinfo);
int  DB_add_ps_err(char *cid);
int  DB_reset_ps_err(char *cid);
int  DB_search_ps_acct_by_tail(char *account, char *cid, char *tail);
int  DB_search_co_acct_by_tail(char *account, char *cid, char *tail);
int  DB_search_transfer_account(char *name, char *account, char *cid, char *inacc, char *tail);
int  DB_seek_acc_link(int *rowcount, char *cid, char *filename);
int  DB_seek_limit(double *aggr_amt, double *max_limit, char *cid, char *account);
int	 DB_search_person_info(int type, char *sonsign_no, char *cid) ;
int  DB_search_co_acct(char *acct, char *cid, char *aid);

extern char uid[32], pwd[32], svc[32];

void error_handler()
{
	bms_trace("SQLCODE = %d", sqlca.sqlcode);

	if (sqlca.sqlerrm.sqlerrml)
	{
		bms_trace("SQL Server Error:");
		bms_trace("%s\n\n", sqlca.sqlerrm.sqlerrmc);
	}

	if ( sqlca.sqlcode == -33620271 ) {
		DB_closeDB() ;
		sleep( 1) ;

		if ( DB_connectDB(uid, pwd, svc) < SUCCESS ) 
		{
			bms_trace( "Reconnected to Database Server FAILURE!\n") ;
			return ;
		}
		bms_trace("Reconnected to Database Server OK!\n") ;
	}

	return ;
}

void warning_handler()
{
	if (sqlca.sqlwarn[1] == 'W')
	{
		bms_trace("Data truncated.\n");
	}

	if (sqlca.sqlwarn[3] == 'W')
	{
		bms_trace("Insufficient host variables to store results.\n");
	}	

	return;
}

void notfound_handler()
{
	bms_trace("No data found for the query.");
	return;
}

int  DB_connectDB(char *uid, char *pwd, char *svc)
{
	EXEC SQL BEGIN DECLARE SECTION ; 
	char username[64] ;
	char password[64] ;
	char svcname[64];
	EXEC SQL END DECLARE SECTION ;

	EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

	memset( username, '\0', sizeof( username)) ;
	memset( username, '\0', sizeof( username)) ;
	memset( password, '\0', sizeof( password)) ;

	strcpy(svcname, svc);
	strcpy(username, uid) ;
	strcpy(password, pwd) ;

	EXEC SQL CONNECT :username IDENTIFIED BY :password USING :svcname;
	if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode) ;

	EXEC SQL EXECUTE IMMEDIATE "set chained off" ;
	if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode) ;

	EXEC SQL EXECUTE IMMEDIATE "set transaction isolation level 1" ;
	if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode) ;

	return( SUCCESS);
}

void DB_closeDB()
{
	EXEC SQL DISCONNECT ALL ;
	return;
}

void DB_Commit()
{
	EXEC SQL COMMIT TRANSACTION;
	return;
}

void DB_Rollback()
{
	EXEC SQL ROLLBACK TRANSACTION;
	return;
}

int  DB_search_pin(char *sname, short *ictype, char *icno, char *pin, char *cid)
{
	EXEC SQL BEGIN DECLARE SECTION ; 
	char sCid[17 + 1] ;
	char password[16 + 1] ;
	char name[30], id[30];
	int  type = -1;
	int  status = -1;
	EXEC SQL END DECLARE SECTION ;

	EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

	memset(sCid, 0, sizeof(sCid));
	strcpy(sCid, cid);
	bms_trace("cid=[%s]", sCid);

	memset(name, 0, sizeof(sname));
	memset(id, 0, sizeof(id));
	memset(password, 0, sizeof(password));

	EXEC SQL SELECT status INTO :status 
		 FROM person_info
		 WHERE cid = :sCid;

	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}

	if( status == 2 )
	{
		return 101;
	}

	EXEC SQL SELECT a.name, a.ic_type, a.ic_no, b.ppasswd 
	         INTO :name, :type, :id, :password 
	         FROM person_info a, ps_accinfo b
	         WHERE a.cid = b.cid AND a.cid = :sCid;

	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}

	AllTrim(password);
	AllTrim(name);
	AllTrim(id);

	strcpy(pin, password);
	strcpy(sname, name);
	strcpy(icno, id);
	*ictype = type;

	return SUCCESS;
}

int  DB_reset_pin(char *cid, short bid, char *pin)
{
	EXEC SQL BEGIN DECLARE SECTION ; 
	char sCid[17 + 1] ;
	char password[16 + 1] ;
	short Bid;
	EXEC SQL END DECLARE SECTION ;

	EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

	memset(sCid, 0, sizeof(sCid));
	strcpy(sCid, cid);

	memset(password, 0, sizeof(password));
	strcpy(password, pin);

	Bid = bid;
  bms_trace("cid[%s]", cid);
	if( cid[3] == '9' )
	{
		bms_trace("cid[%s],bid[%d]", cid, bid);
	EXEC SQL UPDATE co_broker_info
	         SET    ppasswd = :password,
			spasswd = :password
	         WHERE  cid = :sCid AND bid = :Bid;
	}
	else
	{
		bms_trace("cid1[%s],bid1[%d]", cid, bid);
	EXEC SQL UPDATE ps_accinfo
	         SET    ppasswd = :password,
			spasswd = :password
	         WHERE  cid = :sCid;
	}

	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}

	return SUCCESS;
}

int  DB_search_acct(char *acct, char *cid, char *aid, int type)
{
	EXEC SQL BEGIN DECLARE SECTION ; 
	char Aid[5] ;
	char AcctNo[30] ;
	char sCid[17 + 1] ;
	EXEC SQL END DECLARE SECTION ;


  EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;
	
	memset(sCid, 0, sizeof(sCid));
	strcpy(sCid, cid);
	
	bms_trace(sCid);
	bms_trace(Aid);
	if(strlen(acct) != 0)
	{
	memset(AcctNo, 0, sizeof(AcctNo));
	strcpy(AcctNo, acct);

	EXEC SQL SELECT account 
		 INTO   :AcctNo
		 FROM   ps_acc_reg 
		 WHERE  cid = :sCid AND account = :AcctNo;
	
	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}
	}
	
	//if(strlen(aid) != 0)
	else
	{
	
	memset(AcctNo, 0, sizeof(AcctNo));
	memset(Aid, 0, sizeof(Aid));
	sprintf(Aid, "%04d", atoi(aid));

	EXEC SQL SELECT account 
		 INTO   :AcctNo
		 FROM   ps_acc_reg 
		 WHERE  cid = :sCid AND aid = :Aid;
	
	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}
	}

	AllTrim(AcctNo);
	memcpy(acct, AcctNo, strlen(AcctNo));

	return SUCCESS;
}

int  DB_search_co_acct(char *acct, char *cid, char *aid)
{
	EXEC SQL BEGIN DECLARE SECTION ; 
	char Aid[5] ;
	char AcctNo[30] ;
	char sCid[17 + 1] ;
	EXEC SQL END DECLARE SECTION ;


  EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;
	
	memset(sCid, 0, sizeof(sCid));
	strcpy(sCid, cid);
	
	bms_trace(sCid);
	bms_trace(Aid);
	if(strlen(acct) != 0)
	{
	memset(AcctNo, 0, sizeof(AcctNo));
	strcpy(AcctNo, acct);

	EXEC SQL SELECT account 
		 INTO   :AcctNo
		 FROM   co_acc_reg 
		 WHERE  cid = :sCid AND account = :AcctNo;
	
	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}
	}
	
	if(strlen(aid) != 0)
	{
	
	memset(AcctNo, 0, sizeof(AcctNo));
	memset(Aid, 0, sizeof(Aid));
	sprintf(Aid, "%04d", atoi(aid));

	EXEC SQL SELECT account 
		 INTO   :AcctNo
		 FROM   co_acc_reg 
		 WHERE  cid = :sCid AND aid = :Aid;
	
	if( sqlca.sqlcode )
	{
		return sqlca.sqlcode;
	}
	}

	AllTrim(AcctNo);
	memcpy(acct, AcctNo, strlen(AcctNo));

	return SUCCESS;
}

int  DB_ps_acct_list( char *cid, char *aid, char *filename)
{
	EXEC Sql Begin Declare Section ;
    	SQL_PS_ACC_REG pSql ;
	char slctstmt[1024];
	Exec Sql End Declare Section ;
	FILE *vfp, *ffp, *msifp ;
	char vf[ 80], ff[ 80],mf[80];
	char accttype[ 20] ;
	char voxname[ 10] ;
	int  errcode ;
	short rn ;
	char  type[56];

	EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

	memset((char *)&pSql, 0, sizeof(SQL_PS_ACC_REG ));

	vfp=ffp=msifp=NULL;

	sprintf( vf, "%s/tmp/%s.ivr",getenv("HOME"),filename); 
	sprintf( ff, "%s/tmp/%s.fax",getenv("HOME"),filename); 
	sprintf( mf, "%s/tmp/%s",getenv("HOME"),filename); 

	memset( sCid, '\0', sizeof( sCid)) ;
	memset( sAid, '\0', sizeof( sAid)) ;

	strcpy( sCid, cid) ;
	strcpy( sAid, aid) ;
	sStatus = STATUS_ACTIVE ;

	if ( strlen( sAid) == 0 )
	sprintf( slctstmt, "SELECT * FROM ps_acc_reg \
			    WHERE cid = ? AND status = ? \
			    ORDER BY aid");
	else
	sprintf( slctstmt, "SELECT * FROM ps_acc_reg \
			    WHERE cid = ? AND aid = ? AND status = ?") ;

	EXEC SQL PREPARE sel_sid1 FROM :slctstmt ;

	if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode) ;

	EXEC SQL DECLARE sel_cur1 CURSOR FOR sel_sid1 ;

	if ( strlen( sAid) == 0 )
	EXEC SQL OPEN sel_cur1 USING :sCid, :sStatus ;
	else 
	EXEC SQL OPEN sel_cur1 USING :sCid, :sAid, :sStatus ;

	if( (vfp=fopen( vf, "wb+")) == NULL || 
	    (ffp=fopen( ff, "wb+")) == NULL ||
	    (msifp=fopen(mf,"wb+"))==NULL )
	{
		fclose( vfp) ;
		fclose( ffp) ;
		fclose( msifp) ;
		unlink( vf) ;
		unlink( ff) ;
		unlink( mf) ;
		return( FAILURE) ;
	}

	print_fax_head( ffp, "","签约帐号查询") ;
	fprintf( ffp, "|  客户号  :%s|\n", cid) ;
	fprintf( ffp, "|序号      帐号/卡号            帐户类型       |\n") ;

	rn = 0 ;
	while(1)
	{
	memset( (char *)&pSql, '\0', sizeof( pSql)) ;
	Exec Sql Fetch sel_cur1 Into :pSql ;
	if ( sqlca.sqlcode < 0 )
	{
		errcode = sqlca.sqlcode ;
		Exec Sql    Close sel_cur1 ;
		fclose( vfp) ;
		fclose( ffp) ;
		fclose( msifp) ;
		unlink( vf) ;
		unlink( ff) ;
		unlink( mf) ;
		return( errcode) ;
	}

	if ( sqlca.sqlcode == 100) break ;

	rn ++ ;

	pub_trim( pSql.account) ;

	memset(type, 0, sizeof(type));
	switch(pSql.acct_type)
	{
		case 101:
			strcpy(type, "普通存折");
			break;
		case 102:
			strcpy(type, "一本通");
			break;
		case 103:
			strcpy(type, "雄鹰卡");
			break;
		case 501:
			strcpy(type, "贷款户");
			break;
		case 601:
			strcpy(type, "支票户/企业户");
			break;
		default:
			sprintf(type, "未知类型[%d]", pSql.acct_type);
			break;
	}

	fprintf( ffp, "|帐号%d：|%20s|%s|\n", rn, pSql.account, type);
	fprintf(vfp, "帐号%d,%s,%s\n", rn, pSql.account, type);
	fprintf(msifp, "|%d：|%s|%s|\n", rn, pSql.account, type);
	}

	Exec Sql Close sel_cur1 ;

	fclose( vfp) ;
	fclose( ffp) ;
	fclose( msifp) ;

	return( SUCCESS) ;
}

int  DB_co_acct_list( char *cid, short bid, char *aid, char *filename)
{
	EXEC SQL BEGIN DECLARE SECTION ;
	SQL_CO_ACC_REG pSql ;
	char slctstmt[1024];
	EXEC SQL END DECLARE SECTION ;
	FILE *vfp, *ffp,*msifp ;
	char vf[ 80], ff[ 80],mf[80] ;
	char accttype[ 20] ;
	char voxname[ 10] ;
	int  errcode ;
	short rn ;

	EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
	EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
	EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

	vfp=ffp=msifp=NULL;
	sprintf( vf, "%s/tmp/%s.ivr",getenv("HOME"), filename) ;
	sprintf( ff, "%s/tmp/%s.fax",getenv("HOME"), filename) ;
	sprintf( mf, "%s/tmp/%s", getenv("HOME"), filename) ;

	memset( sCid, '\0', sizeof( sCid)) ;
	memset( sAid, '\0', sizeof( sAid)) ;

	strcpy( sCid, cid) ;
	strcpy( sAid, aid) ;
	sBid = bid ;
	sStatus = STATUS_ACTIVE ;

	bms_trace("cid[%s] bid[%d] aid[%s]", cid, bid, aid );
	sprintf( slctstmt, "Select co_acc_reg.* \
				From co_acc_reg \
				Where co_acc_reg.cid = ? \
				And co_acc_reg.status = ? \
				Order By aid") ;
	
	Exec Sql Prepare sel_sid2 From :slctstmt ;
	if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode) ;

	Exec Sql Declare sel_cur2 Cursor For sel_sid2 ;

	Exec Sql Open sel_cur2 Using :sCid, :sStatus ;

	if ( (vfp=fopen( vf, "wb+")) == NULL || 
	     (ffp=fopen( ff, "wb+")) == NULL ||
	     (msifp=fopen(mf,"wb+"))==NULL )
	{
		fclose( vfp) ;
		fclose( ffp) ;
		fclose( msifp) ;
		unlink( vf) ;
		unlink( ff) ;
		unlink( mf) ;
		return( FAILURE) ;
	}

	rn = 0 ;
	for ( ; ; )
	{
	memset( (char *)&pSql, '\0', sizeof( pSql)) ;
	Exec Sql Fetch sel_cur2 Into :pSql;
	if ( sqlca.sqlcode < 0 )
	{
	    errcode = sqlca.sqlcode ;
	Exec Sql    Close sel_cur2 ;
	    fclose( vfp) ;
	    fclose( ffp) ;
	    fclose( msifp) ;
	    unlink( vf) ;
	    unlink( ff) ;
	    unlink( mf) ;
	    return( errcode) ;
	}
	if ( sqlca.sqlcode == 100) break ;

	rn ++ ;
	bms_trace("have record[%d]", rn );

	pub_trim( pSql.account );
        fprintf( ffp, "|%s %20s|\n", pSql.aid, pSql.account) ;
        fprintf( vfp, "序号,%s,帐号,%s,,\n", pSql.aid, pSql.account) ;
        fprintf( msifp, "%s||%s|\n", pSql.aid, pSql.account) ;
	}
	Exec Sql Close sel_cur2 ;
	fclose( vfp) ;
	fclose( ffp) ;
	fclose( msifp) ;
	if ( rn == 0 )
	{
		unlink( vf) ;