dbconnect.pc

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

#include <sqlda.h>
#include <sqlca.h>

static char DBLink[256] = "TestOraConn";
extern void DisplaySqlErrorMsg();

/*============判断数据库登录是否成功==============*/
int checkconnect(char *DBName,char *DBUser,char *DBPwd)
{
    if (DBName[0] == '\0')
    {
        return 0;
    }
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR dbuser[20];
        VARCHAR dbpwd[20];
        VARCHAR dbname[20];
    EXEC SQL END DECLARE SECTION;
    EXEC SQL INCLUDE SQLCA;

    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    strncpy(dbuser.arr, DBUser, 20);
    strncpy(dbpwd.arr, DBPwd, 20);
    strncpy(dbname.arr, DBName, 20);

    dbuser.arr[strlen(DBUser)] = '\0';
    dbpwd.arr[strlen(DBPwd)] = '\0';
    dbname.arr[strlen(DBName)] = '\0';
    dbuser.len=strlen(DBUser);
    dbpwd.len=strlen(DBPwd);
    dbname.len=strlen(DBName);
    DBLink[strlen(DBLink)] = '\0';

    EXEC SQL CONNECT :dbuser IDENTIFIED BY :dbpwd
        AT :DBLink USING :dbname;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 1;
CONN_FAILED: 
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;   
}

/*=================判断用户登录是否成功================
这里有返回2个值,一个是员工的姓名,一个是员工的类型
*/
int CheckUserLogin(char *id,char *pwd,char *ename,int *type)
{
    if (id[0] == '\0' || pwd[0] == '\0')
    {
	return 0;
    }
    EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR userid[20];
	VARCHAR userpwd[20];
	VARCHAR emp_name[20];
	int tem;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL INCLUDE SQLCA;

    memset(userid.arr,0,sizeof(userid.arr));
    userid.len=strlen(id);
    strncpy(userid.arr, id, strlen(id));

    memset(userpwd.arr,0,sizeof(userpwd.arr));
    userid.len=strlen(pwd);
    strncpy(userpwd.arr, pwd, strlen(pwd));

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

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

    EXEC SQL AT :DBLink 
		SELECT NAME INTO :emp_name FROM TB_EMPLOYEE WHERE ID=:id AND PASSWORD=:pwd; 
    EXEC SQL AT :DBLink 
		SELECT TYPE INTO :tem FROM TB_EMPLOYEE WHERE ID=:id AND PASSWORD=:pwd; 

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

    
    strncpy(ename,emp_name.arr,emp_name.len);
    *type=tem;
    return 1;

CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}
 
/*==============查询用户输入的条形码对应的商品是否存在==================*/
int SearchBarCode(char *bar_code,char *name,char *unit,char *spec,float *price,int *stock)
{
    if (bar_code[0] == '\0')
    {
	return 0;
    }
    EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR mer_bar_code[20];
	VARCHAR mer_name[20];
	VARCHAR mer_unit[20];
	VARCHAR mer_spec[20];
	float mer_price;
	int mer_stock;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL INCLUDE SQLCA;
   /*传入*/
    memset(mer_bar_code.arr,0,sizeof(mer_bar_code.arr));
    mer_bar_code.len=strlen(bar_code);
    strncpy(mer_bar_code.arr,bar_code, strlen(bar_code));
    mer_bar_code.arr[mer_bar_code.len] = '\0';
    /*传出*/
    memset(mer_name.arr,0,sizeof(mer_name.arr)); 
    mer_name.len=sizeof(mer_name.arr);
    mer_name.arr[mer_name.len] = '\0';

    memset(mer_unit.arr,0,sizeof(mer_unit.arr)); 
    mer_unit.len=sizeof(mer_unit.arr);
    mer_unit.arr[mer_unit.len] = '\0';

    memset(mer_spec.arr,0,sizeof(mer_spec.arr)); 
    mer_spec.len=sizeof(mer_spec.arr);
    mer_spec.arr[mer_spec.len] = '\0';

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

    EXEC SQL AT :DBLink 
        SELECT MER_NAME,UNIT,SPEC,SELL_PRICE,MER_STOCK
            INTO :mer_name,:mer_unit,:mer_spec,:mer_price,:mer_stock
		FROM TB_MERCHANDISE WHERE MER_BAR_CODE=:mer_bar_code;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    
    strncpy(name,mer_name.arr,mer_name.len);
    strncpy(unit,mer_unit.arr,mer_unit.len);
    strncpy(spec,mer_spec.arr,mer_spec.len);

    *price=mer_price;
    *stock=mer_stock;
    return 1;

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

/*============把销售信息保存到数据库===================*/

int SaveSellInfoToDb(char *sell_no,char *business_no,char *accountid,char *system_time,float user_pay,float fact_pay,float get_money,float zhaoling,int sell_state)
{
    if (sell_no[0] == '\0')
    {
	return 0;
    }		    

    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;
    
    EXEC SQL AT :DBLink
	INSERT INTO TB_SELL	    
		VALUES (:sell_no,:business_no,:accountid,:system_time,:user_pay,:fact_pay,:get_money,:zhaoling,:sell_state);
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL AT :DBLink COMMIT;
    return 1;	
    
CONN_FAILED:
    DisplaySqlErrorMsg();
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    return 0;
}

/*========查询最大交易号===============*/
void SearchMaxBusinessNo(char *business_no)
{
    EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR search_business_no[20];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL INCLUDE SQLCA;

    memset(search_business_no.arr,0,sizeof(search_business_no.arr)); 
    search_business_no.len=sizeof(search_business_no.arr);
    search_business_no.arr[search_business_no.len] = '\0';

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

    EXEC SQL AT :DBLink 
        SELECT MAX(BUSINESS_NO) INTO :search_business_no FROM TB_SELL;

    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    
    strncpy(business_no,search_business_no.arr,search_business_no.len);

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


}

/*=============修改商品的库存==========================*/
int Alter_Mer_Stock(char *bar_code,int amount)
{
    EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
        UPDATE TB_MERCHANDISE SET 
	    MER_STOCK = MER_STOCK - :amount
	        WHERE MER_BAR_CODE = :bar_code;

    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_Sell_Detail(char *system_time,char *sell_no,char *bar_code,int mer_amount,float sell_price,char *sell_detal_no)
{
    EXEC SQL BEGIN DECLARE SECTION;
		char save_sell_detail_no[100];
		char tmp[100];
    EXEC SQL END DECLARE SECTION;
  
    strncpy(tmp,system_time,100);
    strcat(tmp,sell_detal_no);
    strncpy(save_sell_detail_no,tmp,20);
    
    EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink       
	INSERT INTO TB_SELL_DETAIL	    
		VALUES (:save_sell_detail_no,:sell_no,:bar_code,:mer_amount,:sell_price);
    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 Delete_Mer_From_Db(char *bar_code)
{
	EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
        DELETE FROM TB_MERCHANDISE
			WHERE MER_BAR_CODE = :bar_code;
    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_Mer(char *bar_code,char *mer_name,char *unit,char *spec,float sell_price,float stock_price,int mer_amount,float mer_agio)
{
    
    EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink       
	INSERT INTO TB_MERCHANDISE
		(MER_BAR_CODE,MER_NAME,UNIT,SPEC,SELL_PRICE,STOCK_PRICE,MER_STOCK,AGIO)
			VALUES(:bar_code,:mer_name,:unit,:spec,:sell_price,:stock_price,:mer_amount,:mer_agio);
    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 Append_Mer_Stock(char *bar_code,int amount)
{
    EXEC SQL WHENEVER NOT FOUND GOTO CONN_FAILED;
    EXEC SQL WHENEVER SQLERROR GOTO CONN_FAILED;

    EXEC SQL AT :DBLink 
        UPDATE TB_MERCHANDISE SET 
	    MER_STOCK = MER_STOCK + :amount
	        WHERE MER_BAR_CODE = :bar_code;

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

    EXEC SQL AT :DBLink COMMIT;