cms_sql.cp

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

    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL UPDATE ps_accinfo Set status = :pSql2->status, acct_num = 0, 
    detail_num = 0, ppasswd = :pSql2->ppasswd, spasswd = :pSql2->spasswd,
    modi_teller = :pSql2->modi_teller, modi_brch = :pSql2->modi_brch, 
    modi_date = :pSql2->modi_date WHERE cid = :pSql2->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

/***********************************************************
个人客户帐号登记
Input: ps_acc_reg 
Output: 无
Maybe return:
	SUCCESS, sqlcode<0
***********************************************************/
int DB_add_ps_acc_reg( PS_ACC_REG *ps_acc_reg,char *pwd)
{
BIG_STRC hBs;
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACC_REG *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

    pSql = ( SQL_PS_ACC_REG *)ps_acc_reg;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL INSERT INTO ps_acc_reg VALUES( :pSql);
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL UPDATE ps_accinfo Set acct_num=acct_num+1 WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

/*
    if ( pSql->auth_flg[ 2] == '1' ) {
EXEC SQL UPDATE person_info Set func_def[2,2]= "1" WHERE cid = :pSql->cid;
        if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
            errcode = sqlca.sqlcode;
EXEC SQL    ROLLBACK TRANSACTION;
            return( errcode);
        }
    }
*/
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

int DB_search_ps_acc_reg_on_aid( PS_ACC_REG *ps_acc_reg)
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_PS_ACC_REG *pSql;
	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() ;

    	pSql = ( SQL_PS_ACC_REG *)ps_acc_reg;
	EXEC SQL SELECT * INTO :pSql 
		 FROM ps_acc_reg 
		 WHERE cid = :pSql->cid AND aid = :pSql->aid;

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

	if ( sqlca.sqlcode == SQLNOTFOUND ) return( SQLNOTFOUND);

	pub_trim( pSql->account);

	/*fprintf(stdout, "account3 = [%s]\n", ps_acc_reg->account);*/

	pub_trim( ps_acc_reg->city_no);
	pub_trim( ps_acc_reg->name);
	return( SUCCESS);
}

int DB_search_ps_acc_reg_on_accout( PS_ACC_REG *ps_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACC_REG *pSql;
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() ;

    pSql = ( SQL_PS_ACC_REG *)ps_acc_reg;
EXEC SQL SELECT * INTO :pSql FROM ps_acc_reg WHERE account = :pSql->account;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == SQLNOTFOUND ) return( SQLNOTFOUND);
    pub_trim( pSql->account);
    pub_trim( ps_acc_reg->city_no);
    pub_trim( ps_acc_reg->name);
    return( SUCCESS);
}

int DB_search_ps_acc_reg_on_acct( PS_ACC_REG *ps_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACC_REG *pSql;
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() ;

pSql = ( SQL_PS_ACC_REG *)ps_acc_reg;
EXEC SQL SELECT * FROM ps_acc_reg 
    WHERE ( acct_type = :pSql->acct_type AND account = :pSql->account ) 
    Or ( zoneno = :pSql->zoneno AND account = :pSql->account );
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == SQLNOTFOUND ) return( SQLNOTFOUND);
    return( SUCCESS);
}

/***********************************************************
个人客户帐号查询
Input:	cid
Output: 
Maybe return:
	SUCCESS, FAILIURE, sqlcode<0
***********************************************************/
int DB_query_ps_acc_reg( char *cid)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACC_REG pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

        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( pSql));
    strcpy( pSql.cid, cid);

EXEC SQL DECLARE a_cur CURSOR FOR 
	  SELECT * FROM ps_acc_reg 
    	  WHERE cid = :pSql.cid 
	  ORDER BY aid;

EXEC SQL OPEN a_cur;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);

    dp.sn = 0;
    do {

        memset( (char *)&pSql, '\0', sizeof( pSql));
EXEC SQL FETCH a_cur INTO :pSql;
        if ( sqlca.sqlcode < 0 ) 
	{
            errcode = sqlca.sqlcode;
EXEC SQL    CLOSE a_cur;
            return( errcode);
        }
        if ( sqlca.sqlcode == SQLNOTFOUND ) break;

        pub_trim( pSql.aid);
        pub_trim( pSql.account);
        pub_trim( pSql.name);
        pub_trim( pSql.city_no);
        pub_trim( pSql.auth_flg);

        dp.sb[ dp.sn].buf_len = 0;
        sprintf( (char *)dp.sb[ dp.sn].buf, ";%s;%s;%s;%02d;%s;%d;%s;%.2lf;%d;%.2lf;%s;%s;%s;%d;\n",
        pSql.aid, pSql.account, pSql.name, pSql.acct_type, pSql.city_no,
        pSql.curr, pSql.auth_flg, pSql.limit_amt, pSql.limit_num, 
        pSql.max_limit_amt, pSql.reg_teller, pSql.reg_date, 
        pSql.reg_brch, pSql.status); 

        dp.sb[ dp.sn].buf_len = strlen( (char *)dp.sb[ dp.sn].buf);
        dp.sn ++;
    } while ( 1 ); 
EXEC SQL Close a_cur;

    if ( dp.sn == 0 ) return( FAILURE);
    return( SUCCESS);
}

/***********************************************************
个人客户帐号注销
Input:  ps_acc_reg	
Output: No
Maybe return:
	SUCCESS, sqlcode<0
***********************************************************/
int DB_del_ps_acc_reg( PS_ACC_REG *ps_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACC_REG *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

    pSql = ( SQL_PS_ACC_REG *)ps_acc_reg;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM ps_acc_reg WHERE cid = :pSql->cid AND aid = :pSql->aid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL UPDATE ps_accinfo Set acct_num=acct_num-1 WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

/*
原来此句话为注释的,不知道为什么
现打开,因为行里提出当删除一个帐号时相关的代理业务没删
宋强20040720
*/

EXEC SQL Delete FROM ps_proxyauth WHERE cid = :pSql->cid AND aid = :pSql->aid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

/*
EXEC SQL Delete FROM ps_relinfo WHERE cid = :pSql->cid 
    AND ( shaid=:pSql->aid Or szaid=:pSql->aid);
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
*/

EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

/***********************************************************
检查缴费种类
Input:  consign->consign_id
Output: consign
Maybe return:
	SUCCESS, FAILURE, sqlcode<0
***********************************************************/
int DB_search_consign( CONSIGN *consign)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CONSIGN *pSql;
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() ;

    pSql = ( SQL_CONSIGN *)consign;

EXEC SQL SELECT * INTO :pSql FROM consign 
    WHERE consign_id = :pSql->consign_id;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode); 
    if ( sqlca.sqlcode == SQLNOTFOUND ) return( FAILURE);

    pub_trim( pSql->consign);
    pub_trim( pSql->fisq);

    return( SUCCESS);
}

/***********************************************************
查询代理业务
Input: proxyfixauth 
Output: 
Maybe return:
	SUCCESS, sqlcode<0, SQLNOTFOUND
***********************************************************/
int DB_search_proxyfixauth(PROXYFIXAUTH *proxyfixauth)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PROXYFIXAUTH *pSql;
    char tmpAcc[30];
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() ;

    pSql = ( SQL_PROXYFIXAUTH *)proxyfixauth;
EXEC SQL SELECT * INTO :pSql FROM ps_proxyauth WHERE cid = :pSql->cid 
    AND datumno = :pSql->datumno;
    
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    
    if ( sqlca.sqlcode == SQLNOTFOUND) return( SQLNOTFOUND);

    pub_trim( pSql->aid);
    pub_trim( pSql->said);
    pub_trim( pSql->name);
    pub_trim( pSql->address);
 
    return( SUCCESS);
}

/***********************************************************
增加代理业务
Input: proxyfixauth 
Output: 
Maybe return:
	SUCCESS, sqlcode<0,
***********************************************************/
int DB_add_proxyfixauth( PROXYFIXAUTH *proxyfixauth)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PROXYFIXAUTH *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

    pSql = ( SQL_PROXYFIXAUTH *)proxyfixauth;

/*	fprintf(stdout, "%s\n", pSql->zoneno);
	fprintf(stdout, "%s\n", pSql->cid);
	fprintf(stdout, "%d\n", pSql->datumno);
	fprintf(stdout, "%d\n", pSql->unitno);
	fprintf(stdout, "%s\n", pSql->aid);
	fprintf(stdout, "%s\n", pSql->name);
	fprintf(stdout, "%d\n", pSql->status);
	fprintf(stdout, "%s\n", pSql->reg_teller);
	fprintf(stdout, "%lf\n", pSql->reg_brch);
	fprintf(stdout, "%s\n", pSql->modi_date);
	fprintf(stdout, "%s\n", pSql->modi_teller);
	fprintf(stdout, "%lf\n", pSql->modi_brch);
*/
EXEC SQL BEGIN TRANSACTION;
EXEC SQL INSERT INTO ps_proxyauth VALUES( :pSql);
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL UPDATE person_info Set func_def[4,4]= "1" WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

/***********************************************************
注销代理业务
Input:  proxyfixauth
Output: No
Maybe return:
	SUCCESS, sqlcode<0
***********************************************************/
int DB_del_proxyfixauth( PROXYFIXAUTH *proxyfixauth)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PROXYFIXAUTH *pSql;
    int num=0;
    short fid;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

    pSql = ( SQL_PROXYFIXAUTH *)proxyfixauth;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM ps_proxyauth WHERE cid = :pSql->cid 
    AND datumno = :pSql->datumno;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL SELECT count(*) INTO :num:fid FROM ps_proxyauth 
    WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
    if ( fid < 0 ) num = 0;

    if ( num == 0 ) {
EXEC SQL UPDATE person_info Set func_def[4,4]= "0" WHERE cid = :pSql->cid;
        if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) {
            errcode = sqlca.sqlcode;
EXEC SQL    ROLLBACK TRANSACTION;
            return( errcode);
        }
    }
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

/***********************************************************
查询代理业务信息
Input:	cid
Output: 
Maybe return:
	SUCCESS, FAILIURE, sqlcode<0
***********************************************************/
int DB_query_proxyfixauth( char *cid)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PROXYFIXAUTH pSql;
    char name[ 20 + 1];
    char account[ 30 + 1];
EXEC SQL END DECLARE SECTION;
    int errcode;

    memset( (char *)&pSql, '\0', sizeof( pSql));
    strcpy( pSql.cid, cid);

EXEC SQL Declare b_cur Cursor For SELECT * FROM ps_proxyauth
    WHERE cid = :pSql.cid Order By datumno;
EXEC SQL Open b_cur;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);

    dp.sn = 0;
    do {

        memset( (char *)&pSql, '\0', sizeof( pSql));
EXEC SQL Fetch b_cur INTO :pSql;
        if ( sqlca.sqlcode < 0 ) {
            errcode = sqlca.sqlcode;
EXEC SQL    Close b_cur;
            return( errcode);
        }