cms_sql.cp

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

		      co_boacc_def.bid = :pSql2.bid;

	EXEC SQL Open e_cur;

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

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

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

		pub_trim( pSql2.boaid);
		pub_trim( pSql2.aid);
		pub_trim( pSql2.reg_teller);
		pub_trim( pSql2.reg_date);

		dp.sb[ dp.sn].buf_len = 0;
		sprintf( (char *)dp.sb[ dp.sn].buf, ";%s;%s;%s;%s;%d;%s;%d;%s;%s;%d;%s;%s;%d;\n",
		pSql2.boaid, pSql2.aid, pSql1.account, pSql1.name, pSql1.curr, 
		pSql1.bank_no, pSql1.acct_type,
		pSql2.reg_teller, pSql2.reg_date, pSql2.reg_brch,
		pSql1.reg_teller, pSql1.reg_date, pSql1.reg_brch);

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

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

int DB_search_co_boacc_def_on_aid( CO_BOACC_DEF *co_boacc_def)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_BOACC_DEF *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_BOACC_DEF *)co_boacc_def;
EXEC SQL SELECT * INTO :pSql FROM co_boacc_def WHERE cid = :pSql->cid 
        AND bid = :pSql->bid AND aid = :pSql->aid;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == 100 ) return( 100);
    pub_trim( pSql->boaid);
    pub_trim( pSql->aid);
    pub_trim( pSql->reg_teller);
    pub_trim( pSql->reg_date);
    pub_trim( pSql->modi_teller);
    pub_trim( pSql->modi_date);
    return( SUCCESS);
}

int DB_search_co_boacc_def_on_boaid( CO_BOACC_DEF *co_boacc_def)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_BOACC_DEF *pSql;
EXEC SQL END DECLARE SECTION;

    pSql = ( SQL_CO_BOACC_DEF *)co_boacc_def;
EXEC SQL SELECT * INTO :pSql FROM co_boacc_def WHERE cid = :pSql->cid 
        AND bid = :pSql->bid AND boaid = :pSql->boaid;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == 100 ) return( 100);
    pub_trim( pSql->boaid);
    pub_trim( pSql->aid);
    pub_trim( pSql->reg_teller);
    pub_trim( pSql->reg_date);
    pub_trim( pSql->modi_teller);
    pub_trim( pSql->modi_date);
    return( SUCCESS);
}

int DB_add_co_boacc_def( CO_BOACC_DEF *co_boacc_def)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_BOACC_DEF *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

    pSql = ( SQL_CO_BOACC_DEF *)co_boacc_def;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL INSERT INTO co_boacc_def VALUES( :pSql);
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 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 = :pSql->bid;
    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_co_boacc_def( CO_BOACC_DEF *co_boacc_def)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_CO_BOACC_DEF *pSql;
EXEC SQL END DECLARE SECTION;
    int errcode;

    pSql = ( SQL_CO_BOACC_DEF *)co_boacc_def;
EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM co_boacc_def WHERE cid = :pSql->cid 
    AND bid = :pSql->bid AND boaid = :pSql->boaid;
    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 = :pSql->bid;
    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_enableCID(char *cid, char *teller)
{
/*
	EXEC SQL BEGIN DECLARE SECTION;
	char C_id[11 + 1];
	char Oper[5 + 1];
	char Mod_Date[19 + 1];
	char sqlstmt[512];
	EXEC SQL END DECLARE SECTION;

	memset(C_id, 0, sizeof(C_id));
	memset(Oper, 0, sizeof(Oper));

	strcpy(C_id, cid);
	strcpy(Oper, teller);

	pub_get_year_second(Mod_Date);

	memset(sqlstmt, 0, sizeof(sqlstmt));

	sprintf(sqlstmt, "UPDATE person_info SET status = 1 WHERE cid = ? AND modi_teller = ? AND modi_date = ?");

	EXEC SQL EXECUTE IMMEDIATE "BEGIN TRANSACTION";

        EXEC SQL PREPARE ENABLE_CID FROM :sqlstmt; 

        if( sqlca.sqlcode != SUCCESS )
        {
                EXEC SQL EXECUTE IMMEDIATE "ROLLBACK TRANSACTION";
                return sqlca.sqlcode;
        }

        EXEC SQL EXECUTE ENABLE_CID USING :C_id, :Oper, :Mod_Date;

        if( sqlca.sqlcode != SUCCESS )
        {
                EXEC SQL EXECUTE IMMEDIATE "ROLLBACK TRANSACTION";
                return sqlca.sqlcode;
        }

	memset(sqlstmt, 0, sizeof(sqlstmt));

	sprintf(sqlstmt, "UPDATE ps_accinfo SET status = 1 WHERE cid = ? AND modi_teller = ? AND modi_date = ?");

        EXEC SQL PREPARE ENABLE_ACCT FROM :sqlstmt; 

        if( sqlca.sqlcode != SUCCESS )
        {
                EXEC SQL EXECUTE IMMEDIATE "ROLLBACK TRANSACTION";
                return sqlca.sqlcode;
        }

        EXEC SQL EXECUTE ENABLE_ACCT USING :C_id :Oper, :Mod_Date;

        if( sqlca.sqlcode != SUCCESS )
        {
                EXEC SQL EXECUTE IMMEDIATE "ROLLBACK TRANSACTION";
                return sqlca.sqlcode;
        }

	EXEC SQL EXECUTE IMMEDIATE "COMMIT TRANSACTION";
*/

	return SUCCESS;
}


int DB_CheckReplicateId(char *custid)
{
EXEC SQL BEGIN DECLARE SECTION;
    char Pid[20 + 1];
    char tmpCustId[20 + 1];
EXEC SQL END DECLARE SECTION;

	memset(tmpCustId, 0, sizeof(tmpCustId));
	strcpy(tmpCustId, custid);
	
	EXEC SQL SELECT pid INTO :Pid FROM person_info_id WHERE pid = :tmpCustId;

	if(sqlca.sqlcode < 0)
	{
		bms_trace("sqlcode = [%d]\n", sqlca.sqlcode);
		return FAILURE;
	}
	
	if(sqlca.sqlcode == 100)
	{
		bms_trace("sqlcode = [%d]\n", sqlca.sqlcode);
		return SUCCESS;
	}

	return FAILURE;
}

int SetAidToProxy(char *cid, char *aid, short proxyid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	char tmpCid[11 + 1];
	char tmpAid[30];
	short tmpProxyId;
	EXEC SQL END DECLARE SECTION;

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

	memset(tmpAid, 0, sizeof(tmpAid));
	strcpy(tmpAid, aid);

	tmpProxyId = proxyid;

	EXEC SQL UPDATE ps_proxyauth SET aid = :tmpAid WHERE cid = :tmpCid AND datumno = :tmpProxyId;

	if(sqlca.sqlcode < 0)
	{
		bms_trace("sqlcode = [%d]\n", sqlca.sqlcode);
		return FAILURE;
	}

	return SUCCESS;

}

int DB_check_dupID(char *id)
{
	EXEC SQL BEGIN DECLARE SECTION;
	char	tmpID[18 + 1];
	char	sID15[15 + 1];
	char	sID18[18 + 1];
	char	DBid[18 + 1];
	char    iid[20];
	EXEC SQL END DECLARE SECTION;

	memset(tmpID, 0, sizeof(tmpID));
	strcpy(tmpID, id);

	memset(sID15, 0, sizeof(sID15));
	memset(sID18, 0, sizeof(sID18));

	if( strlen(tmpID) == 15 )
	{
		id_OldToNew(id, sID18);
	}
	else if( strlen(tmpID) == 18 )
	{
		id_NewToOld(id, sID15);
	}

	bms_trace(sID15);		
	bms_trace(sID18);		

	memset(iid, 0, sizeof(iid));

	EXEC SQL SELECT ic_no INTO :iid
		 FROM person_info 
		 WHERE ic_type = 1 AND ic_no IN(:sID15, :sID18);

	if( sqlca.sqlcode ) return sqlca.sqlcode;

	return 0;
}

int DB_GenPCid(char *cid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	int  		SerialNo;
	EXEC SQL END DECLARE SECTION;

	char  cSerialNo[10 + 1];
	char  ch;

        /* Genenrate cid */
        EXEC SQL EXEC :SerialNo = proc_get_pcid;
        if( SerialNo < 0 )
        {
                return (SerialNo);
        }

        memset(cSerialNo, 0, sizeof(cSerialNo));
        sprintf(cSerialNo, "100%07ld", SerialNo);

	ch = PUB_ProCifCusNoVer(cSerialNo);
	sprintf(cid, "%s%c", cSerialNo, ch);
	cid[11] = 0;

	return 0;
}

int DB_GenCCid(char *cid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	int  		SerialNo;
	EXEC SQL END DECLARE SECTION;

	char  cSerialNo[11 + 1];
	char  ch;

        /* Genenrate cid */
        EXEC SQL EXEC :SerialNo = proc_get_ccid;
        if( SerialNo < 0 )
        {
                return (SerialNo);
        }

        memset(cSerialNo, 0, sizeof(cSerialNo));
        sprintf(cSerialNo, "1009%06ld", SerialNo);

	ch = PUB_ProCifCusNoVer(cSerialNo);
	sprintf(cid, "%s%c", cSerialNo, ch);
	cid[11] = 0;

	return 0;
}

int DB_Query_Link_Acct(char *cid, char *acct, char *link)
{
	EXEC SQL BEGIN DECLARE SECTION;
	char Cid[11 + 1];
	char Acct[30];
	char Link[30];
	char tmp[11 + 1];
	EXEC SQL END DECLARE SECTION;

	memset(Cid, 0, sizeof(Cid));
	strcpy(Cid, cid);
	cid[11] = 0;

	memset(Acct, 0, sizeof(Acct));
	strcpy(Acct, acct);

	memset(Link, 0, sizeof(Link));
	strcpy(Link, link);

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

	memset(tmp, 0, sizeof(tmp));
	EXEC SQL SELECT cid INTO :tmp
		 FROM ps_acc_link
		 WHERE cid = :Cid 
		 AND account = :Acct
		 AND link = :Link;

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

	return 	SUCCESS;
}

int DB_Add_Link_Acct(PS_ACC_LINK *link)
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_PS_ACC_LINK *pSql;
	EXEC SQL END DECLARE SECTION;

	pSql = (SQL_PS_ACC_LINK *)link;

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

	EXEC SQL BEGIN TRANSACTION;

	EXEC SQL INSERT INTO ps_acc_link VALUES( :pSql );

	if( sqlca.sqlcode )
	{
		EXEC SQL ROLLBACK TRANSACTION;
		return FAILURE;
	}

	EXEC SQL COMMIT TRANSACTION;

	return 	SUCCESS;
}

int DB_check_auth(char *cid, char *aid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	char tmpCid[11 + 1];
	char tmpAid[4 + 1];
	char tmpAuthFlg[1 + 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(tmpCid, 0, sizeof(tmpCid));
	strcpy(tmpCid, cid);

	memset(tmpAid, 0, sizeof(tmpAid));
	strcpy(tmpAid, aid);

	memset(tmpAuthFlg, 0, sizeof(tmpAuthFlg));

	EXEC SQL SELECT substring(auth_flg, 3, 1)
		 INTO   :tmpAuthFlg
		 FROM   ps_acc_reg
		 WHERE  cid = :tmpCid AND aid = :tmpAid;

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

	bms_trace("flag = [%c]", tmpAuthFlg[0]);

	if( tmpAuthFlg[0] == '0' )
	{
		return 9999;
	}

	return 0;
}