cms_sql.cp

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

    pSql = (SQL_CO_INFO *)co_info;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL INSERT INTO co_info VALUES( :pSql);
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

int DB_del_co_info( char *cid, CO_INFO *co_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_INFO *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

    pSql = ( SQL_CO_INFO *)co_info;
    strcpy( pSql->cid, cid);
    pSql->status = STATUS_DISA;

    /* 修改档案状态, 删除所有帐号, 业务代表信息, 业务代表可操作信息*/
EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM co_acc_reg WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

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

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

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

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

EXEC SQL UPDATE co_info Set status = :pSql->status, 
    modi_teller = :pSql->modi_teller, modi_brch = :pSql->modi_brch, 
    modi_date = :pSql->modi_date WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

int DB_modi_co_info( char *cid, CO_INFO *co_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_INFO *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_INFO *)co_info;

    strcpy( pSql->cid, cid);
EXEC SQL UPDATE co_info Set unitname=:pSql->unitname,
        country_tax=:pSql->country_tax, local_tax=:pSql->local_tax,
        basaccd=:pSql->basaccd, reg_bank=:pSql->reg_bank,
        agent=:pSql->agent, ic_type=:pSql->ic_type,
        ic_no=:pSql->ic_no, finacemas=:pSql->finacemas,
        mas_type=:pSql->mas_type, mas_no=:pSql->mas_no,
        address=:pSql->address, postcode=:pSql->postcode,
        phone1=:pSql->phone1, phone2=:pSql->phone2,
        fax=:pSql->fax, email=:pSql->email, type=:pSql->type,
        remark=:pSql->remark, modi_teller=:pSql->modi_teller, 
        modi_brch=:pSql->modi_brch, modi_date=:pSql->modi_date 
        WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) return( sqlca.sqlcode);
    return( SUCCESS); 
}

int DB_readd_co_info( char *cid, CO_INFO *co_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_INFO *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_INFO *)co_info;

    strcpy( pSql->cid, cid);
EXEC SQL UPDATE co_info Set unitname=:pSql->unitname,
        country_tax=:pSql->country_tax, local_tax=:pSql->local_tax,
        basaccd=:pSql->basaccd, reg_bank=:pSql->reg_bank,
        agent=:pSql->agent, ic_type=:pSql->ic_type,
        ic_no=:pSql->ic_no, finacemas=:pSql->finacemas,
        mas_type=:pSql->mas_type, mas_no=:pSql->mas_no,
        address=:pSql->address, postcode=:pSql->postcode,
        phone1=:pSql->phone1, phone2=:pSql->phone2,
        fax=:pSql->fax, email=:pSql->email, type=:pSql->type,
        remark=:pSql->remark, modi_teller=:pSql->modi_teller, 
        modi_brch=:pSql->modi_brch, modi_date=:pSql->modi_date,
        status=:pSql->status 
        WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) return( sqlca.sqlcode);
    return( SUCCESS); 
}

int DB_search_co_acc_reg_on_aid( CO_ACC_REG *co_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_ACC_REG *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_ACC_REG *)co_acc_reg;
EXEC SQL SELECT * INTO :pSql FROM co_acc_reg WHERE cid = :pSql->cid AND aid = :pSql->aid;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == 100 ) return( 100);
    pub_trim( pSql->account);
    pub_trim( pSql->bank_no);
    pub_trim( pSql->name);
    return( SUCCESS);
}

int DB_search_co_acc_reg_on_acct( CO_ACC_REG *co_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_ACC_REG *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_ACC_REG *)co_acc_reg;
EXEC SQL SELECT * INTO :pSql FROM co_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 == 100 ) return( 100);
    pub_trim( pSql->account);
    pub_trim( pSql->bank_no);
    pub_trim( pSql->name);
    return( SUCCESS);
}

int DB_add_co_acc_reg( CO_ACC_REG *co_acc_reg)
{
	EXEC SQL BEGIN DECLARE SECTION;
    	SQL_CO_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_CO_ACC_REG *)co_acc_reg;

	EXEC SQL BEGIN TRANSACTION;
	
	EXEC SQL INSERT INTO co_acc_reg VALUES( :pSql);

    	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_query_co_acc_reg( char *cid)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_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 c_cur Cursor For 
    	SELECT * 
    	FROM co_acc_reg 
    	WHERE cid = :pSql.cid 
    	Order By aid;
    
    EXEC SQL Open c_cur;
    
    if ( sqlca.sqlcode < 0 ) 
    {
    	bms_trace("Open cursor failed.");
    	return( sqlca.sqlcode);
    }

    dp.sn = 0;
    do {
        memset( (char *)&pSql, '\0', sizeof( pSql));
	EXEC SQL Fetch c_cur INTO :pSql;
        if ( sqlca.sqlcode < 0 ) 
        {
            errcode = sqlca.sqlcode;
	    EXEC SQL CLOSE c_cur;
            return( errcode);
        }
        if ( sqlca.sqlcode == 100 ) break;

        pub_trim( pSql.aid);
        pub_trim( pSql.account);
        pub_trim( pSql.name);
        pub_trim( pSql.bank_no);
        pub_trim( pSql.reg_teller);
        pub_trim( pSql.reg_date);

        dp.sb[ dp.sn].buf_len = 0;
        sprintf( (char *)dp.sb[ dp.sn].buf, ";%s;%s;%s;%02d;%s;%d;%s;%s;%ld;%d;\n",
        pSql.aid, pSql.account, pSql.name, pSql.acct_type, pSql.bank_no,
        pSql.curr, 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 c_cur;

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

int DB_del_co_acc_reg( CO_ACC_REG *co_acc_reg)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_ACC_REG *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

    pSql = ( SQL_CO_ACC_REG *)co_acc_reg;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM co_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 co_broker_info Set acct_num=acct_num-1 WHERE cid = :pSql->cid 
        AND bid In ( SELECT bid FROM co_boacc_def 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 co_boacc_def WHERE cid = :pSql->cid AND aid = :pSql->aid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }
EXEC SQL COMMIT TRANSACTION;
    return( SUCCESS);
}

int DB_query_broker_info( char *cid)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_BROKER_INFO pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

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

    dp.sn = 0;
    do {

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

	pub_trim( pSql.zoneno);
	pub_trim( pSql.ppasswd);
	pub_trim( pSql.spasswd);
	pub_trim( pSql.name);
	pub_trim( pSql.broker_right);
	pub_trim( pSql.ic_no);
	pub_trim( pSql.fax);
	pub_trim( pSql.rela_tel);
	pub_trim( pSql.bp);

        dp.sb[ dp.sn].buf_len = 0;
        sprintf( (char *)dp.sb[ dp.sn].buf, ";%d;%s;%d;%d;%s;%d;%s;%s;%s;%s;%s;%s;%ld;\n",
        pSql.bid, pSql.name, pSql.status, pSql.acct_num, pSql.broker_right,
        pSql.ic_type, pSql.ic_no, pSql.fax, pSql.bp, pSql.rela_tel,
	pSql.reg_teller, pSql.reg_date, pSql.reg_brch);

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

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

int DB_search_broker_info_on_bid( BROKER_INFO *broker_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_BROKER_INFO *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_BROKER_INFO *)broker_info;
EXEC SQL SELECT * INTO :pSql FROM co_broker_info WHERE cid = :pSql->cid AND bid = :pSql->bid;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == 100 ) return( 100);
    pub_trim( pSql->zoneno);
    pub_trim( pSql->ppasswd);
    pub_trim( pSql->spasswd);
    pub_trim( pSql->name);
    pub_trim( pSql->broker_right);
    pub_trim( pSql->ic_no);
    pub_trim( pSql->fax);
    pub_trim( pSql->rela_tel);
    pub_trim( pSql->bp);
    return( SUCCESS);
}

int DB_search_broker_info_on_ic_no( BROKER_INFO *broker_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_BROKER_INFO *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_BROKER_INFO *)broker_info;
EXEC SQL SELECT * INTO :pSql FROM co_broker_info WHERE cid = :pSql->cid 
    AND ic_type = :pSql->ic_type AND ic_no = :pSql->ic_no;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == 100 ) return( 100);
    pub_trim( pSql->zoneno);
    pub_trim( pSql->ppasswd);
    pub_trim( pSql->spasswd);
    pub_trim( pSql->name);
    pub_trim( pSql->broker_right);
    pub_trim( pSql->ic_no);
    pub_trim( pSql->fax);
    pub_trim( pSql->rela_tel);
    pub_trim( pSql->bp);
    return( SUCCESS);
}

int DB_add_broker_info( BROKER_INFO *broker_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_BROKER_INFO *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

    pSql = ( SQL_BROKER_INFO *)broker_info;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL INSERT INTO co_broker_info VALUES( :pSql);
    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_del_broker_info( BROKER_INFO *broker_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_BROKER_INFO *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

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

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

int DB_set_broker_passwd( char *cid, BROKER_INFO *broker_info)
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_BROKER_INFO *pSql;
	EXEC SQL END DECLARE SECTION;

	pSql = ( SQL_BROKER_INFO *)broker_info;
	strcpy( pSql->cid, cid);
	EXEC SQL UPDATE co_broker_info Set ppasswd=:pSql->ppasswd, spasswd=:pSql->spasswd,
	modi_date=:pSql->modi_date, modi_teller=:pSql->modi_teller,
	modi_brch=:pSql->modi_brch WHERE cid = :pSql->cid AND bid = :pSql->bid;
	if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) return( sqlca.sqlcode);
	return( SUCCESS);
}

int DB_query_co_boacc_def( char *cid, int bid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_CO_ACC_REG  pSql1;
	SQL_CO_BOACC_DEF pSql2;
	EXEC SQL END DECLARE SECTION;
	int errcode;

	memset( (char *)&pSql1, '\0', sizeof( pSql1));
	memset( (char *)&pSql2, '\0', sizeof( pSql2));
	strcpy( pSql1.cid, cid);
	strcpy( pSql2.cid, cid);
	pSql2.bid = bid;

	EXEC SQL Declare e_cur CURSOR FOR 
		SELECT co_acc_reg.*, co_boacc_def.* 
		FROM co_boacc_def, co_acc_reg 
		WHERE co_boacc_def.cid = co_acc_reg.cid AND 
		      co_boacc_def.aid = co_acc_reg.aid AND 
		      co_boacc_def.cid = :pSql2.cid AND