cms_sql.cp

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

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <memory.h>
#include <sys/types.h>
#include <fcntl.h>
#include "apgloble.h"
#include "bms_strcdef.h"
#include "bms_micro.h"

#define MAX_PS_CID	8999999
#define MAX_CO_CID	9999999

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

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

EXEC SQL BEGIN DECLARE SECTION; 
	char sqlstmt[ 1024];
EXEC SQL END DECLARE SECTION; 

/***********************************************************
检查个人客户是否存在，并取得客户信息
Input:	cid, ic_type, ic_no
Output:	cid, ic_type, ic_no
Maybe return:
	SUCCESS, sqlcode<0 || sqlcode=100
***********************************************************/
int DB_search_ps_info( char *cid, short ic_type, char *ic_no, PERSON_INFO *ps_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PERSON_INFO *pSql;
EXEC SQL END DECLARE SECTION;
    int nCidLen, nNoLen;
    
    EXEC SQL WHENEVER SQLERROR CALL error_handler() ;
    EXEC SQL WHENEVER SQLWARNING CALL warning_handler() ;
    EXEC SQL WHENEVER NOT FOUND CALL notfound_handler() ;

    pSql = ( SQL_PERSON_INFO *)ps_info;
    strcpy( pSql->cid, cid);
    pSql->ic_type = ic_type;
    strcpy( pSql->ic_no, ic_no);

    nCidLen = strlen( pSql->cid);
    nNoLen = strlen( pSql->ic_no);

    if ( nCidLen != 0 && nNoLen != 0 )
    {
    	bms_trace("Cid and Pid:[%d],[%s],[%s]", pSql->ic_type, pSql->ic_no, pSql->cid);
    	EXEC SQL SELECT * INTO :pSql 
	 	FROM person_info 
	 	WHERE cid = :pSql->cid 
	 	AND   ic_type = :pSql->ic_type 
	 	AND   ic_no = :pSql->ic_no;
    }
    else if ( nCidLen != 0 )
    {	
    	bms_trace("Only Cid:[%s]", pSql->cid);
    	EXEC SQL SELECT * INTO :pSql 
		 FROM person_info 
		 WHERE cid = :pSql->cid;
    }
    else
    { 
    	bms_trace("Only Pid:[%d],[%s]", pSql->ic_type, pSql->ic_no);
	EXEC SQL SELECT * INTO :pSql 
		 FROM person_info 
        	 WHERE ic_type = :pSql->ic_type 
        	 AND   ic_no = :pSql->ic_no;
    }

    if ( sqlca.sqlcode != 0 ) return( sqlca.sqlcode);

    pub_trim( pSql->zoneno);
    pub_trim( pSql->cid);
    pub_trim( pSql->name);
    pub_trim( pSql->engname);
    pub_trim( pSql->engcard);
    pub_trim( pSql->ic_no);
    pub_trim( pSql->ratepayno);
    pub_trim( pSql->insrncno);
    pub_trim( pSql->ctrycode);
    pub_trim( pSql->mcall_id);
    pub_trim( pSql->ncall_id);
    pub_trim( pSql->birthday);
    pub_trim( pSql->consort);
    pub_trim( pSql->interest);
    pub_trim( pSql->append_info);
    pub_trim( pSql->address1);
    pub_trim( pSql->zip);
    pub_trim( pSql->address2);
    pub_trim( pSql->mobile);
    pub_trim( pSql->fax);
    pub_trim( pSql->phone1);
    pub_trim( pSql->email);
    pub_trim( pSql->empdate);
    pub_trim( pSql->job_stat);
    pub_trim( pSql->unitname);
    pub_trim( pSql->phone2);
    pub_trim( pSql->postcode);
    pub_trim( pSql->banka);
    pub_trim( pSql->widno);
    pub_trim( pSql->vip);
    pub_trim( pSql->remark);
    pub_trim( pSql->idexpire);
    pub_trim( pSql->reg_teller);
    pub_trim( pSql->reg_brch);
    pub_trim( pSql->reg_date);
    pub_trim( pSql->modi_teller);
    pub_trim( pSql->modi_brch);
    pub_trim( pSql->modi_date);

    return( SUCCESS);
}

/***********************************************************
登记个人档案信息
Input:	ps_info, ps_accinfo
Output:
Maybe return:
	SUCCESS, sqlcode<0 
***********************************************************/
int DB_add_ps_info(PERSON_INFO *ps_info,PS_ACCINFO *ps_accinfo,PS_PID *ps_pid)
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_PERSON_INFO *pSql1;
	SQL_PS_ACCINFO  *pSql2;
	int  		SerialNo;
	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() ;

	pSql1 = (SQL_PERSON_INFO *)ps_info;
	pSql2 = (SQL_PS_ACCINFO *)ps_accinfo;

	EXEC SQL BEGIN TRANSACTION;

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

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

	EXEC SQL COMMIT TRANSACTION;

	return( SUCCESS);
}

/***********************************************************
自动生成客户号
Input:	zone
Output: cid
Maybe return:
	SUCCESS, FAILURE, sqlcode<0, 
***********************************************************/
int DB_get_cid_ps( long zoneno, char *cid) 
{
	EXEC SQL BEGIN DECLARE SECTION;
	SQL_CISCFG pSql;
	EXEC SQL END DECLARE SECTION;
	long nCid;
	char tmp[ 11];
	char tmpCid[10 + 1], CheckBit = 49;

	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));

	EXEC SQL BEGIN TRANSACTION;

	pSql.zoneno = 39;

	EXEC SQL SELECT * INTO :pSql FROM ciscfg WHERE zoneno = :pSql.zoneno;

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

	if ( sqlca.sqlcode == 100 ) 
	{
		pSql.brach = 0;
		/*CheckBit = PUB_ProCifCusNoVer("1490000000");PENDING */
		sprintf( pSql.desedanno, "1490000000%c", CheckBit);

		/*CheckBit = PUB_ProCifCusNoVer("1400000000");PENDING */
		sprintf( pSql.deseperno, "1400000000%c", CheckBit);

		sprintf( pSql.desemgno, "%09ld", 0);
		sprintf( pSql.desegrtno, "%09ld", 0);

		EXEC SQL INSERT INTO ciscfg VALUES( :pSql);

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

	memset( tmp, '\0', sizeof( tmp));
	memcpy( tmp, pSql.deseperno + 2, 8);
	nCid = atol( tmp);

	memset( tmpCid, 0, sizeof(tmpCid));
	for (; nCid < MAX_PS_CID; ) 
	{
		/* 根据起始值生成客户号*/
		sprintf( tmpCid, "14%08ld", ++nCid);
		/* CheckBit = PUB_ProCifCusNoVer(tmpCid);*/
		sprintf( pSql.deseperno, "%s%c", tmpCid, CheckBit);

		/* 判断是否已被使用*/
		EXEC SQL SELECT * FROM person_info WHERE cid =:pSql.deseperno;
		if ( sqlca.sqlcode < 0 )  
		{
			EXEC SQL ROLLBACK TRANSACTION;
			return( sqlca.sqlcode);
		}

		if ( sqlca.sqlcode == SQLNOTFOUND ) 
		{
			strcpy( cid, pSql.deseperno);
			EXEC SQL UPDATE ciscfg 
				 Set deseperno = :pSql.deseperno 
				 WHERE zoneno = :pSql.zoneno;
			if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[2] == 0 )
			{
				EXEC SQL ROLLBACK TRANSACTION;
				return( sqlca.sqlcode);
			}
			EXEC SQL COMMIT TRANSACTION;
			return( SUCCESS);
		}
	}

	EXEC SQL ROLLBACK TRANSACTION;
	return( FAILURE);
}

/***********************************************************
检查个人客户基本帐户信息
Input:	cid
Output: ps_accinfo	
Maybe return:
	SUCCESS, FAILURE, sqlcode<0 
***********************************************************/
int DB_search_ps_accinfo( char *cid, PS_ACCINFO *ps_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACCINFO *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_ACCINFO *)ps_info;

    strcpy( pSql->cid, cid);
EXEC SQL SELECT * INTO :pSql FROM ps_accinfo WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) return( sqlca.sqlcode);
    if ( sqlca.sqlcode == SQLNOTFOUND ) return( FAILURE);
    return( SUCCESS);
}

/***********************************************************
修改个人客户档案信息
Input:	cid, ps_info
Output: 
Maybe return:
	SUCCESS, sqlcode<0 
***********************************************************/
int DB_modi_ps_info( char *cid, PERSON_INFO *ps_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PERSON_INFO *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_PERSON_INFO *)ps_info;

    strcpy( pSql->cid, cid);
EXEC SQL UPDATE person_info Set name=:pSql->name, sex=:pSql->sex, 
        ratepayno=:pSql->ratepayno, insureno=:pSql->insrncno,
        birthday=:pSql->birthday, marriage=:pSql->marriage, study=:pSql->study,
	techlvl=:pSql->techlvl, consort=:pSql->consort, 
	address1=:pSql->address1, zip=:pSql->zip, 
	mobile=:pSql->mobile, phone1=:pSql->phone1,
        email=:pSql->email, empdate=:pSql->empdate, unitname=:pSql->unitname,
        utypeid=:pSql->utypeid, phone2=:pSql->phone2, postcode=:pSql->postcode,
        banka=:pSql->banka, widno=:pSql->widno, vip=:pSql->vip,
        remark=:pSql->remark, idexpire=:pSql->idexpire,  
        modi_teller=:pSql->modi_teller, modi_brch=:pSql->modi_brch,
        modi_date=:pSql->modi_date ,pacetype =:pSql->pacetype,ctrycode=:pSql->ctrycode,job=:pSql->job
        WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) return( sqlca.sqlcode);
    return( SUCCESS); 
}

/***********************************************************
注销个人档案信息
修改档案状态, 删除所有帐号, 代理信息，关联信息
Input:	cid, ps_info
Output: 无
Maybe return:
	SUCCESS, sqlcode<0
***********************************************************/
int DB_del_ps_info( char *cid, PERSON_INFO *ps_info)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PERSON_INFO *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_PERSON_INFO *)ps_info;
    strcpy( pSql->cid, cid);
    pSql->status = STATUS_DISA;

    /* 删除所有帐号, 代理信息，关联信息*/
EXEC SQL BEGIN TRANSACTION;
EXEC SQL Delete FROM ps_acc_reg WHERE cid = :pSql->cid;
    if ( sqlca.sqlcode < 0 ) {
        errcode = sqlca.sqlcode;
EXEC SQL ROLLBACK TRANSACTION;
        return( errcode);
    }

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

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

EXEC SQL UPDATE person_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 UPDATE ps_accinfo Set status = :pSql->status, acct_num = 0, 
    detail_num = 0, 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);
}

/***********************************************************
个人客户密码重置
Input: cid, ps_accinfo
Output: 无
Maybe return:
	SUCCESS, sqlcode<0
***********************************************************/
int DB_set_ps_passwd( char *cid, PS_ACCINFO *ps_accinfo)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PS_ACCINFO *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_ACCINFO *)ps_accinfo;
    strcpy( pSql->cid, cid);
EXEC SQL UPDATE ps_accinfo 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;
    if ( sqlca.sqlcode < 0 || sqlca.sqlerrd[ 2] == 0 ) return( sqlca.sqlcode);
    return( SUCCESS);
}

/***********************************************************
注销个人档案信息重新登记
Input:	ps_info, ps_accinfo 
Output: 
Maybe return:
	SUCCESS, sqlcode 
***********************************************************/
int DB_readd_ps_info( char *cid, PERSON_INFO *ps_info, PS_ACCINFO *ps_accinfo)
{
EXEC SQL BEGIN DECLARE SECTION;
    SQL_PERSON_INFO *pSql1;
    SQL_PS_ACCINFO *pSql2;
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() ;


    pSql1 = ( SQL_PERSON_INFO *)ps_info;
    pSql2 = ( SQL_PS_ACCINFO *)ps_accinfo;

    strcpy( pSql1->cid, cid);
    strcpy( pSql2->cid, cid);
    pSql1->status = STATUS_ACTIVE;
    pSql2->status = STATUS_ACTIVE;

EXEC SQL BEGIN TRANSACTION;
EXEC SQL UPDATE person_info Set name=:pSql1->name, sex=:pSql1->sex, 
        ratepayno=:pSql1->ratepayno, insureno=:pSql1->insrncno,
        birthday=:pSql1->birthday, marriage=:pSql1->marriage, 
        study=:pSql1->study, techlvl=:pSql1->techlvl, 
        consort=:pSql1->consort, address1=:pSql1->address1,
        zip=:pSql1->zip, mobile=:pSql1->mobile, phone1=:pSql1->phone1,
        email=:pSql1->email, empdate=:pSql1->empdate, unitname=:pSql1->unitname,
        utypeid=:pSql1->utypeid, phone2=:pSql1->phone2, 
        postcode=:pSql1->postcode, banka=:pSql1->banka, 
        widno=:pSql1->widno, vip=:pSql1->vip, remark=:pSql1->remark, 
        idexpire=:pSql1->idexpire, modi_teller=:pSql1->modi_teller, 
        modi_brch=:pSql1->modi_brch, modi_date=:pSql1->modi_date,
        status=:pSql1->status WHERE cid = :pSql1->cid;