yl_gw_month_rpt.ec

在SCO UNIX制定界面程序 可根据文件配制菜单,而不必修改源程序,非常方便

/*=====================================
银联长城卡全国联行交易清算月报表
相关数据库：	cur_settle_ls, his_settle_ls
函数名：yl_gw_month_rpt()
程  序：Wubin 2001/09/15
=====================================*/

#include <stdio.h>
#include <math.h>

#include "all.h"
#include "macro_def.h"

$include "db_struct.h"
$include sqlca;

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


/* defined in cglfx.ec */
extern	struct {
	char	bankid[12];
	char	hostid[3];
	int		count;
	double	amount;
}	bankids[400];

typedef struct {
	double tran_amt;
	double settle_amt;
	double income_amt;
	double merch_amt;	/* 净计金额 给商户 */
	int tran_cnt;
} S_yl_gw_month;

extern double get_qs_tip(), get_bank_name(), type2buf();

int yl_gw_month_rpt(file, start_date, end_date)
char *file;
$parameter char *start_date, *end_date;
{
	FILE *fp;
	S_yl_gw_month total;
	int page_no, i, ret, flag;
	$S_tran_ls ls, ls1;
	$double card_discount_tmp, tran_amt, tran_amt_tmp;
	$char	sqlstr[1024], bankid[18], hostid[3], bankname[50], iss_bank_id[12];
	$int 	count, bank_num, total_tran_cnt;
	$short	indi1, indi2 ;
	double tip, income,total_tran_amt,total_income_amt;
	$S_sys_param sy;
	

	page_no = i = 1;
	bank_num = ret = total_tran_cnt = 0 ;
	total_tran_amt = total_income_amt = 0.0;
	if ( ret = select_sys_param(&sy) ) {
		errcall(ERROR, "查本地参数表错误[%d]!",ret);
		return ( -1 );
	}

/***************
* 统计发卡行数
***************/
	memset(&bankids, 0, sizeof(bankids));
		sprintf(sqlstr,
			"select distinct iss_bank_id, count(*), sum(tran_amt) \
			from cur_settle_ls \
			where   iss_bank_id != \'VOID\'  \
			and tran_type in (1,2,3,5,6,7,8,9,10,14) \
			and saf_flag = \'0\' \
			and tran_flag = \'0\' \
			and manual_flag = \'N\' \
			and resp_code like \'00\' \
			and snd_settle_date >= \'%s\' and  snd_settle_date <= \'%s\' \
			and acq_bank_id =\'%s\' \
			and card_type = \'01\' and bank_type = \'04\' \
			and card_no[1] in (4,5) group by iss_bank_id", \
			start_date, end_date, sy.bank_id);
		$prepare yl_iss from $sqlstr;
		$declare yl_iss_cur cursor for yl_iss;
		$open yl_iss_cur;
	
		if ( SQLCODE ) {
			errcall ( ERROR, "open yl_iss_cur err[%d]",SQLCODE );
			return ( -1 );
		}

		while ( bank_num < 400 ) {
			$fetch yl_iss_cur into $bankid,  $count:indi1, $tran_amt:indi2;
			if ( SQLCODE == SQLNOTFOUND )
				break;
			if ( SQLCODE ) {
				$close yl_iss_cur;
				errcall ( ERROR, "fetch yl_iss_cur err[%d]",SQLCODE );
				return ( -1 );
			}
			if ( indi1 != -1 && indi2 != -1 && count ) {
				DelSpace(bankid);
				strcpy ( bankids[bank_num].bankid, bankid );
				strcpy ( bankids[bank_num].hostid, "00" );
				bankids[bank_num].count = count;
				bankids[bank_num].amount = tran_amt;
				bank_num ++;
			}
		}
		$close yl_iss_cur;

		sprintf(sqlstr,
			"select distinct iss_bank_id, count(*), sum(tran_amt) \
			from his_settle_ls \
			where   iss_bank_id != \'VOID\'  \
			and tran_type in (1,2,3,5,6,7,8,9,10,14) \
			and saf_flag = \'0\' \
			and tran_flag = \'0\' \
			and manual_flag = \'N\' \
			and resp_code like \'00\' \
			and snd_settle_date >= \'%s\' and  snd_settle_date <= \'%s\' \
			and acq_bank_id =\'%s\' \
			and card_type = \'01\' and bank_type = \'04\' \
			and card_no[1] in (4,5) group by iss_bank_id", \
			start_date, end_date, sy.bank_id);
		$prepare yl_iss2 from $sqlstr;
		$declare yl_iss_cur2 cursor for yl_iss2;
		$open yl_iss_cur2;
		if ( SQLCODE ) {
			errcall ( ERROR, "open yl_iss_cur2 err[%d]",SQLCODE );
			return ( -1 );
		}
	
		while ( bank_num < 400 ) {
			$fetch yl_iss_cur2 into $bankid,  $count:indi1, $tran_amt:indi2;
			if ( SQLCODE == SQLNOTFOUND )
				break;
			if ( SQLCODE ) {
				$close yl_iss_cur2;
				errcall ( ERROR, "fetch yl_iss_cur2 err[%d]",SQLCODE );
				return ( -1 );
			}
			if ( indi1 != -1 && indi2 != -1 && count ) {
				DelSpace(bankid);
				for ( i=0; i<bank_num; i++ )
					if ( !strcmp ( bankids[i].bankid, bankid) )
						break;
				if ( i >= bank_num ) {
				strcpy ( bankids[bank_num].bankid, bankid );
				strcpy ( bankids[bank_num].hostid, "00" );
				bankids[bank_num].count = count;
				bankids[bank_num].amount = tran_amt;
				bank_num ++;
				} else {
					bankids[i].count += count;
					bankids[bank_num].amount = tran_amt;
				}
			}
		}
		$close yl_iss_cur2;

		
/****************/

	if (!(fp = fopen(file, "w"))) {
		daylog(ERROR, "Open %s error!", file);
		return(-1);	/* File operate error */
	}
	bank_num = 0;
	if (FileHead_yl_gw_month(fp, start_date, end_date)) {
		fclose(fp);
		return(-2);	/* Database operate error */
	}
	while (bankids[bank_num].bankid[0] != 0) {
		strcpy(iss_bank_id, bankids[bank_num].bankid);
		get_bank_name(iss_bank_id,"00",bankname);

		memset(&total, 0, sizeof(S_yl_gw_month));
		$declare yl_cur cursor for
		select * from cur_settle_ls
		where iss_bank_id = :iss_bank_id
		and tran_type in (1,2,3,5,6,7,8,9,10,14)
		and saf_flag = '0'
		and tran_flag = '0'
		and manual_flag = 'N'
		and resp_code like '00%'
		and snd_settle_date >= :start_date and  snd_settle_date <= :end_date
		and acq_bank_id = :sy.bank_id 
		and card_type = '01'  and bank_type = '04' and card_no[1] in (4,5) 
		order by card_no;
		$open yl_cur;
		if (SQLCODE) {
			daylog(ERROR, "Open yl_cur error![%d]", SQLCODE);
			fclose(fp);
			return(-2);
		}
		while (1) {
			$fetch yl_cur into :ls;
			if (SQLCODE == 100) break;
			if (SQLCODE) {
				daylog(ERROR, "Fetch yl_cur error![%d]", SQLCODE);
				$close yl_cur;
				fclose(fp);
				return(-2);
			}
			tip = 0.0;
			income = 0.0;
			card_discount_tmp = 0.0;
	
			/* 根据商户号获得商户回扣率 */
			EXEC SQL SELECT card_discount INTO :card_discount_tmp 
			FROM merchant_card  
			WHERE merchant_id=:ls.merchant_id and card_type='01' and bank_type='04';
	
			tip = ((card_discount_tmp / 100) * ls.tran_amt);	/* 手续费（交易金额×商户回扣率） */
			income = (tip - (ls.tran_amt * 0.005)); /* 我行收益 */
			del_st_space("tran_ls", &ls);
			
			flag = strcmp(ls.iss_bank_id, sy.bank_id);
			switch (ls.tran_type) {
			    case PURCHASE:
			    case WITHDRAWAL:
			    case CONFIRM:
			    case TRANS_OUT:
				if (flag) {
					if (ls.tran_amt != 0) {
						total.tran_cnt++;
						total.tran_amt += ls.tran_amt;	
						total.income_amt += income;
					}
				}
				break;
			    case REFUND:
			    case DEPOSIT:
				if (flag) {
					if (ls.tran_amt != 0) {
						total.tran_cnt++;
						total.tran_amt += ls.tran_amt;	
						total.income_amt += income;
					}
				}
				break;
				}
		}
		$close yl_cur;

		$declare yl_cur2 cursor for
		select * from his_settle_ls
		where iss_bank_id = :iss_bank_id
		and tran_type in (1,2,3,5,6,7,8,9,10,14)
		and saf_flag = '0'
		and tran_flag = '0'
		and manual_flag = 'N'
		and resp_code like '00%'
		and snd_settle_date >= :start_date and  snd_settle_date <= :end_date
		and acq_bank_id = :sy.bank_id 
		and card_type = '01'  and bank_type = '04' and card_no[1] in (4,5) 
		order by card_no;
		$open yl_cur2;
		if (SQLCODE) {
			daylog(ERROR, "Open yl_cur2 error![%d]", SQLCODE);
			fclose(fp);
			return(-2);
		}
		while (1) {
			$fetch yl_cur2 into :ls1;
			if (SQLCODE == 100) break;
			if (SQLCODE) {
				daylog(ERROR, "Fetch yl_cur2 error![%d]", SQLCODE);
				$close yl_cur;
				fclose(fp);
				return(-2);
			}
			tip = 0.0;
			income = 0.0;
			card_discount_tmp = 0.0;
	
			/* 根据商户号获得商户回扣率 */
			EXEC SQL SELECT card_discount INTO :card_discount_tmp 
			FROM merchant_card  
			WHERE merchant_id=:ls1.merchant_id and card_type='01' and bank_type='04';
	
			tip = ((card_discount_tmp / 100) * ls1.tran_amt);	/* 手续费（交易金额×商户回扣率） */
			income = (tip - (ls1.tran_amt * 0.005)); /* 我行收益 */
			del_st_space("tran_ls", &ls1);
			
			flag = strcmp(ls1.iss_bank_id, sy.bank_id);
			switch (ls1.tran_type) {
			    case PURCHASE:
			    case WITHDRAWAL:
			    case CONFIRM:
			    case TRANS_OUT:
				if (flag) {
					if (ls1.tran_amt != 0) {
						total.tran_cnt++;
						total.tran_amt += ls1.tran_amt;	
						total.income_amt += income;
					}
				}
				break;
			    case REFUND:
			    case DEPOSIT:
				if (flag) {
					if (ls1.tran_amt != 0) {
						total.tran_cnt++;
						total.tran_amt += ls1.tran_amt;	
						total.income_amt += income;
					}
				}
				break;
				}
		}
		$close yl_cur2;	


		fprintf(fp, "%-11.11s %-30s %8d %14.2lf %14.2lf\n", iss_bank_id, bankname,  total.tran_cnt, total.tran_amt, total.income_amt);
		total_tran_amt +=  total.tran_amt;
		total_income_amt += total.income_amt;
		total_tran_cnt += total.tran_cnt;
		bank_num++;
	}
	/* 报表结尾 */
	fprintf(fp, "-----------------------------------------------------------------------------------\n");
/*================== WuBin 2001-9-20 19:32 ======Start==============
	if (total.tran_cnt)
===========================2001-9-20 19:32=======End==============*/
	if (total_tran_cnt)
		fprintf(fp, "%s %s %-d %s %-14.2lf %s %-14.2lf\n\n\f", "小计：  ", "交易总笔数: ", total_tran_cnt, "交易总金额： ", total_tran_amt, "我行总收益： ", total_income_amt);
	fclose(fp);
	return(0);
}

int FileHead_yl_gw_month(FILE *fp, char *start_date, char *end_date)
{
	char date1[11],date2[11],bank_name[21];
	$S_sys_param sys;

	$select * into :sys from sys_param;
	if (SQLCODE) {
		daylog(ERROR, "Select sys_param error!");
		return(-1);
	}
	del_st_space("sys_param", &sys);
	str2date(date1, start_date, "/");
	str2date(date2, end_date, "/");
	get_bank_name(sys.bank_id,"00", bank_name);

	fprintf(fp, "%21s    中国银行 BTS 系统\n", " ");
	fprintf(fp, "%21s银联长城卡全国联行交易统计月报表\n\n", " ");
	fprintf(fp, "收单行号：%s         收单行名： %s\n",sys.bank_id,bank_name);
	fprintf(fp, "日期：%s 到   %s%30s单位：人民币元\n",date1,date2," ");
	fprintf(fp, "===================================================================================\n");
	fprintf(fp, "%-11s %-30s %8s %14s %14s \n", "发卡行联行号", "发卡行名", "交易笔数","交易金额", "我行收益");
	fprintf(fp, "-----------------------------------------------------------------------------------\n");

	return(0);
}