dbconnect.pc

在LINUX下实现的一个超市的收银系统

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}


/*==============查询帐号是否存在===============*/
int Search_Account_Id(char *account_id)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
        SELECT ID FROM TB_EMPLOYEE 
			WHERE ID = :account_id;
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*===========把用户添加的帐户保存到数据库(员工表)======================*/
int Save_Account_To_Db(char *account_id,char *account_name,char *account_pwd,int grade,char *remark)
{	
    EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink       
		INSERT INTO TB_EMPLOYEE
			VALUES(:account_id,:account_name,:account_pwd,:grade,:remark);
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
		 
    EXEC SQL AT :DBLink COMMIT;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}


/*==============删除帐户====================*/
int Del_Account_From_Db(char *account_id)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		CALL delete_account(:account_id);
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL AT :DBLink COMMIT;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*================保存新密码到数据库,(员工表)=================*/

int Save_New_Pwd_To_Db(char *mod_id,char *new_pwd)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		CALL mod_password(:mod_id,:new_pwd);
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL AT :DBLink COMMIT;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*===========判断输入的日期正确吗===================*/
int Is_Date_Right(char *date)
{	
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT to_date(:date,'yyyymmdd') FROM DUAL;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*==================================================
通过条形码连接商品表和销售明细表
并返回商品表中的:商品名称,商品进货价格,商品销售价格.
====================================================*/

int Search_Mer_Info(char *bar_code,char *mer_name,float *sell_price,float *stock_price)
{	

	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR v_bar_code[20];
		VARCHAR v_mer_name[20];
	EXEC SQL End DECLARE SECTION;

	strncpy(v_bar_code.arr, bar_code, strlen(bar_code));
	v_bar_code.len = strlen(bar_code);
	v_bar_code.arr[v_bar_code.len]='\0';

	memset(v_mer_name.arr, 0, sizeof(v_mer_name.arr));
	v_mer_name.len=sizeof(v_mer_name.arr);


	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT MER_NAME,SELL_PRICE,STOCK_PRICE 
			INTO :v_mer_name,:sell_price,:stock_price
				FROM TB_MERCHANDISE 
					WHERE MER_BAR_CODE=:v_bar_code;

	strncpy(mer_name, v_mer_name.arr, v_mer_name.len);
	mer_name[v_mer_name.len] = '\0';

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;

}

/*==============================
	在销售明细表中
	查询商品销售的总数量
================================*/


int Stat_Mer_Sell_Amount(char *bar_code,int *total_sell,char *start_day,char *end_day)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT SUM(AMOUNT) 
			INTO :total_sell 
				FROM TB_SELL_DETAIL
					WHERE BAR_CODE = :bar_code AND
						SUBSTR(SELL_NO,1,8) BETWEEN :start_day AND :end_day;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*=========================================

查询当天的销售总金额

===========================================*/

int Day_Total_Sell_Money(char *sell_date,float *totol_sell_money)
{
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR v_sell_date[20];
	EXEC SQL End DECLARE SECTION;

	strncpy(v_sell_date.arr, sell_date, strlen(sell_date));
	v_sell_date.len = strlen(sell_date);
	v_sell_date.arr[v_sell_date.len]='\0';

	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT SUM(FACT_PAY) 
			INTO :totol_sell_money 
				FROM TB_SELL
					WHERE SUBSTR(SELL_DATE,1,8) = :v_sell_date;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*=====================================
		求出今天的总盈利
=======================================*/
int Today_Total_Payoff(char *sell_date,float *total_payoff)
{
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR v_sell_date[20];
	EXEC SQL End DECLARE SECTION;

	strncpy(v_sell_date.arr, sell_date, strlen(sell_date));
	v_sell_date.len = strlen(sell_date);
	v_sell_date.arr[v_sell_date.len]='\0';

	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT SUM(B.AMOUNT * (A.SELL_PRICE - A.STOCK_PRICE))
			INTO :total_payoff 
				FROM TB_MERCHANDISE A,TB_SELL_DETAIL B
					WHERE A.MER_BAR_CODE = B.BAR_CODE(+) AND
						SUBSTR(B.SELL_NO,1,8) = :v_sell_date;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*============================
判断销售单号是否存在
=============================*/
int Is_Sell_No_Exist(char *sell_no)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT SELL_NO 
			FROM TB_SELL
				WHERE SELL_NO = :sell_no;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;	
}

/*=======================================
判断输入的条形码在但前销售单号中存在不存在
=========================================*/
int Is_Mer_In_Sell_List(char *sell_no,char *bar_code,int *amount)
{
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR v_sell_no[20];
		VARCHAR v_bar_code[20];
	EXEC SQL End DECLARE SECTION;

	strncpy(v_sell_no.arr, sell_no, strlen(sell_no));
	v_sell_no.len = strlen(sell_no);
	v_sell_no.arr[v_sell_no.len]='\0';

	strncpy(v_bar_code.arr, bar_code, strlen(bar_code));
	v_bar_code.len = strlen(bar_code);
	v_bar_code.arr[v_bar_code.len]='\0';

	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
		SELECT AMOUNT INTO :amount
			FROM TB_SELL_DETAIL
				WHERE SELL_NO = :v_sell_no AND
					BAR_CODE = :bar_code;					

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}