oracledb.pc

在linux平台下模拟超市的收银系统即POS机

#include <stdio.h>
#include <sqlcpr.h>
#include <sqlca.h>
#include <sqlda.h>
#include <string.h>
#include "OracleDB.h"



/*插入到product*/
int Insert_Into_Product(char * bar_code,char * product_name,char *unit,char *spec,float sale_price,float purchase_price,int count,float discount)
{
	EXEC SQL WHENEVER SQLERROR GOTO INSERT_ERR;
	EXEC SQL INSERT INTO product VALUES (:bar_code,:product_name,:unit,:spec,:sale_price,:purchase_price,:count,:discount);
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 1;/*正常插入*/
INSERT_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	
	return 0;/*出错*/
} 


/*添加员工到staff表中*/
int Insert_Into_Staff(char * staff_id,char * staff_name,char * staff_pwd,int staff_type, char * remark)
{
	EXEC SQL WHENEVER SQLERROR GOTO INSERT_ERR;
	EXEC SQL INSERT INTO staff VALUES (:staff_id,:staff_name,:staff_pwd,:staff_type,:remark);
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 1;/*正常插入*/
INSERT_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	
	return 0;/*出错*/
}



/*根据条码查找商品是否存在*/
int search_exist(char* barcode)
{
	int exist=0;
	EXEC SQL WHENEVER SQLERROR GOTO SEARCH_ERR;
	EXEC SQL SELECT count(*) INTO :exist FROM PRODUCT WHERE bar_code=:barcode;
	if(exist == 1)
	{
		return 0;/*商品已存在*/
	}
	else
	{
		return 1;/*商品不存在*/
	}
SEARCH_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}



/*根据帐户ID查找用户是否存在*/
int search_staff_id_exist(char* staff_id)
{
	int exist=0;
	EXEC SQL WHENEVER SQLERROR GOTO SEARCH_ERR;
	EXEC SQL SELECT count(*) INTO :exist FROM staff WHERE staff_id=:staff_id;
	if(exist == 1)
	{
		return 0;/*员工号已存在*/
	}
	else
	{
		return 1;/*员工号不存在*/
	}
SEARCH_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}



/*退货完，修改出售商品状态*/
int Modify_Sale_Detail_State(char * sale_id,char * bar_code)
{
	/*退货完出售商品状态为2*/
	EXEC SQL WHENEVER SQLERROR GOTO MOD_ERR;
	EXEC SQL UPDATE sale_detail SET sale_state=2 WHERE sale_id=:sale_id AND bar_code=:bar_code;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;
MOD_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*更新指定条形码的数据*/
int Update_ProductInfo_By_Barcode(char * bar_code,product * pro)
{
	EXEC SQL WHENEVER SQLERROR GOTO UPDATE_ERR;
	EXEC SQL UPDATE product SET bar_code=:pro->bar_code,product_name=:pro->product_name,unit=:pro->unit,spec=:pro->spec,sale_price=:pro->sale_price,purchase_price=:pro->purchase_price,count=:pro->count,discount=:pro->discount WHERE bar_code=:bar_code;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;
UPDATE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}

/*员工表结构*/
/*typedef struct staff
{
	char id[7];
	char name[16];
	char pwd[17];
	int type;
	char remark[31];
}staff;
*/

/*更新指定ID的帐户数据*/
int Update_Staff_By_ID(char * staff_id,staff * pStaff)
{
	EXEC SQL WHENEVER SQLERROR GOTO UPDATE_ERR;
	EXEC SQL UPDATE staff SET staff_id=:pStaff->id,staff_name=:pStaff->name,staff_pwd=:pStaff->pwd,staff_type=:pStaff->type,remark=:pStaff->remark WHERE staff_id=:staff_id;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;
UPDATE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}



/*删除指定条形码的数据*/
int Delete_ProductInfo_By_Barcode(char * bar_code)
{
	EXEC SQL WHENEVER SQLERROR GOTO DEL_ERR;
	EXEC SQL DELETE FROM product WHERE bar_code=:bar_code;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;
DEL_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*删除指定ID的用户*/
int Delete_Staff_By_ID(char * staff_id)
{
	EXEC SQL WHENEVER SQLERROR GOTO DEL_ERR;
	EXEC SQL DELETE FROM staff WHERE staff_id=:staff_id;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;
DEL_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*查询员工名*/
int Get_Staff_Name(char * staff_id,char * staff_name)
{
	/*short staff_in;*/
	EXEC SQL WHENEVER SQLERROR GOTO SELECT_ERR;
	/*EXEC SQL SELECT staff_name INTO :staff_name FROM staff WHERE staff_id=:staff_id :staff_in;*/
	EXEC SQL SELECT staff_name INTO :staff_name FROM staff WHERE staff_id=:staff_id;
	Trim(staff_name);
	/*if(staff_in==0)
	{
		error_login(staff_id,"log.txt",0);
	}
	*/
	return 0;
	SELECT_ERR:
		error_login(sqlca.sqlerrm.sqlerrmc,"log.txt",0);
		EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*插入时进行时间转换*/
/*
EXEC  SQL INSERT INTO sale  values(:sale_id,:tmp_trans_id,:staff_id,to_date(:sale_date,'yyyymmddHH24MISS'),
						:given_sum,:real_sum,:sale_money,:change,:sale_state);
*/

/*插入到销售表*/
void InsertIntoSale(char *sale_id,char* trans_id,char *staff_id,char * sale_date,float sale_money,int sale_state)
{
	EXEC SQL WHENEVER SQLERROR GOTO INSERT_ERR;
	EXEC SQL INSERT INTO sale (sale_id,trans_id,staff_id,sale_date,given_sum,real_sum,sale_money,sale_state) VALUES (:sale_id,:trans_id,:staff_id,to_date(:sale_date,'yyyymmddHH24MISS'),:sale_money,:sale_money,:sale_money,:sale_state);
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return;
INSERT_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return;
}


/*退货后修改销售商品总金额*/
void Modify_Sale_Money(char * sale_id,float money)
{
	EXEC SQL WHENEVER SQLERROR GOTO UPDATE_ERR;
	EXEC SQL UPDATE sale SET given_sum=given_sum-:money,real_sum=real_sum-:money,sale_money=sale_money-:money WHERE sale_id=:sale_id;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	/*
	char m[10];
	sprintf(m,"%d",money);
	error_login(sale_id,"errlog.txt",0);
	error_login(m,"errlog.txt",0);
	*/
	return;
UPDATE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/*error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);*/
	return;
}


/*修改商品库存量*/
void ReduceStock(char * barcode,int amount) /*barcode 条形码 amount 为要减少的数量*/
{
	EXEC SQL WHENEVER SQLERROR GOTO UPDATE_ERR;
	/*update product set count=count+13 where bar_code='CN000001'*/
	EXEC SQL UPDATE product SET count=count-:amount WHERE bar_code=:barcode;
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return;
UPDATE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return;
}

/*插入到销售明细表*/
/*
void InsertIntoSaleDetail(sale_detail * saleDetail)
{
	EXEC SQL WHENEVER SQLERROR GOTO INSERT_ERR;
	EXEC SQL INSERT INTO sale_detail (detail_id,dale_id,bar_code,count,sale_price,sale_state) VALUES (:saleDetail->detail_id,:saleDetail->sale_id,:saleDetail->bar_code,:saleDetail->count,:saleDetail->sale_price,:saleDetail->sale_state);
	EXEC SQL COMMIT;
	return;
INSERT_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"log.txt",0);
	return;
}
*/


/*去掉左右空格*/
void Trim(char * ch)
{
	int m;
	int len2=strlen(ch);
	for(m=0;m<len2;m++) /*去前面空格*/
	{
		 if(ch[m]!=' ')
			 break;
	}
	strcpy(ch,&ch[m]);
	int len3=strlen(ch);
	for(m=len3-1;m>0;m--) /*q去后面空格*/
	{
		if(ch[m]!=' ')
		{
			ch[m+1]='\0';
			break;
		}
	} 	
}



void select_staff_table(char * name)
{
	staff *s=(staff *) malloc(sizeof(staff));/*员工表*/
	staff_ind si; /*指示器   如果用到结构体的话，那也要用对应的指示器结构体*/

	EXEC SQL WHENEVER SQLERROR GOTO SELECT_ERR;
	EXEC SQL SELECT * INTO :s :si FROM staff WHERE STAFF_NAME=:name;
	if(si.name==-1)
	{
		printf("name is null");
	}
	printf("ID: %s\n",s->id);
	printf("name: %s\n",s->name);
	printf("pwd: %s\n",s->pwd);
	printf("type: %d\n",s->type);
	printf("remark: %s\n",s->remark);
	return;
	SELECT_ERR:
		EXEC SQL WHENEVER SQLERROR CONTINUE;
		printf("出错\n");
}








/*删除一个sale表中符合sale_id的记录*/
int Delete_Bill(char *sale_id)
{
	EXEC SQL WHENEVER SQLERROR GOTO DEL_ERR;
	EXEC SQL DELETE sale WHERE sale_id=:sale_id;
	EXEC SQL COMMIT;
	return 0;
DEL_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;	
}



void select_staff_all_data()
{
	staff s;
	EXEC SQL WHENEVER SQLERROR GOTO SELECT_ALL_ERR;
	EXEC SQL DECLARE cur_all_data CURSOR FOR select * from staff;
	EXEC SQL OPEN cur_all_data;
	while(1)
	{
		EXEC SQL FETCH cur_all_data INTO :s;
		if(sqlca.sqlcode==0)  /*sqlca是通讯区，里边保存的是一些错误信息，其本身是一个结构体*/
		{
			printf("ID: %s\n",s.id);
			printf("name: %s\n",s.name);
			printf("pwd: %s\n",s.pwd);
			printf("type: %d\n",s.type);
			printf("remark: %s\n",s.remark);
		}else
		{
			break;
		}
	}
	printf("%s",sqlca.sqlerrm.sqlerrmc);
	EXEC SQL CLOSE cur_all_data;
	return;
SELECT_ALL_ERR:

	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return;
}

/*取得明细序列*/
int Get_Detail_Seque()
{
	int seq_id;
	EXEC SQL WHENEVER SQLERROR GOTO GET_SEQUE_ERR;
	EXEC SQL SELECT seq_detail_id.nextval INTO :seq_id FROM dual;
	return seq_id;
GET_SEQUE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*取得交易序列*/
int Get_Trans_Seque()
{
	int seq_id;
	EXEC SQL WHENEVER SQLERROR GOTO GET_SEQUE_ERR;
	EXEC SQL SELECT seq_trans_id.nextval INTO :seq_id FROM dual;
	return seq_id;
GET_SEQUE_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}

/*插入到销售明细表*/
int Insert_To_Sale_Detail(char * detail_id,char * sale_id,char *bar_code,int count,float sale_price,int state)
{
	EXEC SQL WHENEVER SQLERROR GOTO INSERT_ERR;
	EXEC SQL INSERT INTO sale_detail VALUES (:detail_id,:sale_id,:bar_code,:count,:sale_price,:state);
	EXEC SQL COMMIT; /*当插入或删除的时候要提交事务*/
	return 0;/*正常插入*/
INSERT_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return -1;/*出错*/
}

/*员工表结构*/
/*typedef struct staff
{
	char id[7];
	char name[16];
	char pwd[17];
	int type;
	char remark[31];
}staff;
*/

/*取得指定ID的员工信息*/
int Get_Staff_By_ID(char * staff_id,staff * pStaff)
{
	/*error_login(staff_id,"errlog.txt",0);*/
	memset(pStaff->id,'\0',sizeof(pStaff->id));/*将id字段置成'\0'*/
	memset(pStaff->name,'\0',sizeof(pStaff->name));/*将name字段置成'\0'*/
	memset(pStaff->pwd,'\0',sizeof(pStaff->pwd));/*将pwd字段置成'\0'*/
	memset(pStaff->remark,'\0',sizeof(pStaff->remark));/*将remark字段置成'\0'*/

	EXEC SQL WHENEVER SQLERROR GOTO GET_INFO_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT * INTO :pStaff FROM staff WHERE staff_id=:staff_id;
	Trim(pStaff->id);/*将从数据库中取出的字符串，去掉左右空格，由于数据库会在插入字符串的时候如果达不到字段长度会自动在字符串后边以空格填充*/
	Trim(pStaff->name);
	Trim(pStaff->pwd);
	Trim(pStaff->remark);
	return 0; /*找到数据*/
NOT_FOUND:
	return 1;
GET_INFO_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;	
}


/*取得指定条形码的数据*/
int Get_ProductInfo_By_Barcode(char * bar_code,product * pro)
{
	memset(pro->bar_code,'\0',sizeof(pro->bar_code));
	memset(pro->product_name,'\0',sizeof(pro->product_name));
	memset(pro->unit,'\0',sizeof(pro->unit));
	memset(pro->spec,'\0',sizeof(pro->spec));

	EXEC SQL WHENEVER SQLERROR GOTO GET_INFO_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT * INTO :pro FROM product WHERE BAR_CODE=:bar_code;
	Trim(pro->bar_code);
	Trim(pro->product_name);
	Trim(pro->unit);
	Trim(pro->spec);
	return 0;
NOT_FOUND:
	return 1;
GET_INFO_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;	
}

/*登入验证*/
int check_login(char * userid,char * password ,int * type)
{
	EXEC SQL WHENEVER SQLERROR GOTO LOGIN_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NO_LOGIN;
	EXEC SQL SELECT staff_type INTO :type FROM staff WHERE staff_id=:userid and staff_pwd=:password;
		return 0;  /*查询成功返回0*/
	NO_LOGIN:
		return 1;  /*没有查询到数据返回1*/
	LOGIN_ERR:
		EXEC SQL WHENEVER SQLERROR CONTINUE;
		return -1; /*任意出错问题的情况下*/
}

/*根据销售编号和条形码得到销售数量*/
int Get_Sale_Detail(char * sale_id,char * bar_code,int * count,float * sale_price)
{
	/*
	int sale_state=1;  1代表结帐
	EXEC SQL WHENEVER SQLERROR GOTO GET_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_GET;
	EXEC SQL SELECT sum(count),max(sale_price) INTO :count,:sale_price FROM sale_detail WHERE sale_id=:sale_id AND bar_code=:bar_code AND sale_state=:sale_state GROUP BY bar_code;
	return 0;
NOT_GET:
	return 1;
GET_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
	*/
	/*