oracledb.pc

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

	char tmp[10];
	error_login(sale_id,"errlog.txt",0);
	error_login(bar_code,"errlog.txt",0);
*/
	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=1 
	GROUP BY bar_code;

	return 0;
NOT_GET:
	EXEC SQL WHENEVER NOT FOUND CONTINUE;
	return 1;
GET_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}


/*查询挂单编号*/
int Get_Hang_Bill_List(LINKLIST *linklist,char * staff_id)
{
	int sale_state=0;
	short hand_int;
	char temp_sale_id[19]={'\0'};
	EXEC SQL WHENEVER SQLERROR GOTO GET_LIST_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND_REC;
	EXEC SQL DECLARE cur_data CURSOR FOR SELECT sale_id from sale where sale_state=:sale_state AND staff_id=:staff_id;
	EXEC SQL OPEN cur_data;
	char * hand_id;
	while(1)
	{
		hand_id=(char *)malloc(sizeof(temp_sale_id));
		EXEC SQL FETCH cur_data INTO :temp_sale_id;
		memset(hand_id,'\0',20);
		if(sqlca.sqlcode==0)  /*sqlca是通讯区，里边保存的是一些错误信息，其本身是一个结构体*/
		{
			strcpy(hand_id,temp_sale_id);
			error_login(hand_id,"errlog.txt",0);
			Trim(hand_id);
			insert_rear_link(linklist,hand_id);
		}else
		{
			break;
		}
	}
	EXEC SQL CLOSE cur_data;
	return 0;
NOT_FOUND_REC:
	return 1;
GET_LIST_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return -1;
}



/*通过销售单号查询商品信息*/
int get_product_by_hang_id(char* sale_id,LINKLIST* linklist)
{
/*
//出售产品
typedef struct saleProduct
{
	char bar_code[9];//商品ID
	char product_name[31];//产品名称
	char unit[17];//规格
	char spec[17];//单位
	float sale_price;//单价
	int amount;//卖出商品数量
	float money;//金额
} saleProduct;
*/
	saleProduct tmp;
	EXEC SQL WHENEVER SQLERROR GOTO CUR_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL DECLARE cur_sale CURSOR FOR SELECT a.bar_code,a.product_name,a.unit,a.spec,a.sale_price,b.count FROM product a,sale_detail b 
		WHERE a.bar_code=b.bar_code and b.sale_id=:sale_id;
	EXEC SQL OPEN cur_sale;
	while(1)
	{
		EXEC SQL FETCH cur_sale INTO :tmp.bar_code,:tmp.product_name,:tmp.unit,:tmp.spec,:tmp.sale_price,:tmp.amount;
		if(sqlca.sqlcode==0)
		{
			saleProduct *pro=(saleProduct*)malloc(sizeof(saleProduct));
			Trim(tmp.bar_code);/*去掉字符串的左右空格，由于数据库读取出来时会跟库的字段一样长*/
			Trim(tmp.product_name);
			Trim(tmp.unit);
			Trim(tmp.spec);
			tmp.money=tmp.sale_price*tmp.amount;
			*pro=tmp;
			insert_rear_link(linklist,pro);
		}
		else
			break;
	}
	EXEC SQL CLOSE cur_sale;
	return 0;
NOT_FOUND:
	return 1;
CUR_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
}

/*获取商品信息总页数 返回总页码 recourdNum 代表的是每页显示多少记录*/
int Get_ProductInfo_TotalPageNumber(int recordNum)
{
	int recordCount=0; /* 存放记录总数 */
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT count(*) into :recordCount FROM product;
	int more=(recordCount%recordNum==0)?0:1;
	int page=(recordCount/recordNum)+more;
	return page;
NOT_FOUND:
	/*error_login("dwa","3",0);*/
	return -1;
} 



/*获取帐户信息总页数 返回总页码 recordNum代表的是一页有多少条记录*/
int Get_Staff_TotalPageNumber(int recordNum)
{
	int recordCount=0; /* 存放记录总数 */
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT count(*) into :recordCount FROM staff;
	int more=(recordCount%recordNum==0)?0:1;
	int page=(recordCount/recordNum)+more;
	return page;
NOT_FOUND:
	/*error_login("dwa","3",0);*/
	return -1;
}








/*取得指定页码的商品信息*/
int Get_ProductInfo_By_PageNumber(LINKLIST *linklist,int page,int recourdNum)
{
	int start;
	int end;
	int num;
	product tmp;
	product *pro;
	start=recourdNum * (page-1)+1;
	end=recourdNum*page;
	int pnum=0;
	EXEC SQL SELECT count(*) into :pnum FROM product;
	if(end>pnum)
	{
		end=pnum;
	}

	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL WHENEVER SQLERROR GOTO CUR_ERR;

	EXEC SQL DECLARE get_product_cursor CURSOR FOR 
		select B.bar_code,B.product_name,B.unit,B.spec,B.sale_price,B.purchase_price,B.count,B.discount,B.RN from 
		(select A.*,rownum RN from product A where rownum<=:end) B 
		where B.RN>=:start;
	EXEC SQL OPEN get_product_cursor;
	while(1)
	{
		EXEC SQL FETCH get_product_cursor INTO :tmp.bar_code,:tmp.product_name,:tmp.unit,:tmp.spec,:tmp.sale_price,:tmp.purchase_price,:tmp.count,:tmp.discount,:num;
		if(sqlca.sqlcode==0)
		{
/*
			error_login(tmp.bar_code,"errlog.txt",0);
			error_login(tmp.product_name,"errlog.txt",0);
			error_login(tmp.unit,"errlog.txt",0);
			error_login(tmp.spec,"errlog.txt",0);

			char sale_price[20];
			sprintf(sale_price,"%f",tmp.sale_price);
			error_login(sale_price,"errlog.txt",0);
			char purchase_price[20];
			sprintf(purchase_price,"%f",tmp.purchase_price);
			error_login(purchase_price,"errlog.txt",0);
			char count[20];
			sprintf(count,"%f",tmp.count);
			error_login(count,"errlog.txt",0);
			char discount[20];
			sprintf(discount,"%f",tmp.discount);
			error_login(discount,"errlog.txt",0);

			error_login("55555555555555555555555555555555","errlog.txt",0);
*/
			pro=(product*)malloc(sizeof(product));
			Trim(tmp.bar_code);
			Trim(tmp.product_name);
			Trim(tmp.unit);
			Trim(tmp.spec);
			*pro=tmp;

/*
			error_login(pro->bar_code,"errlog.txt",0);
			error_login(pro->product_name,"errlog.txt",0);
			error_login(pro->unit,"errlog.txt",0);
			error_login(pro->spec,"errlog.txt",0);

			char sale_price[20];
			sprintf(sale_price,"%f",pro->sale_price);
			error_login(sale_price,"errlog.txt",0);
			char purchase_price[20];
			sprintf(purchase_price,"%f",pro->purchase_price);
			error_login(purchase_price,"errlog.txt",0);
			char count[20];
			sprintf(count,"%f",pro->count);
			error_login(count,"errlog.txt",0);
			char discount[20];
			sprintf(discount,"%f",pro->discount);
			error_login(discount,"errlog.txt",0);

			error_login("1111111111111111111111","errlog.txt",0);
*/



			insert_rear_link(linklist,pro);
		}
		else
			break;
	}
	char count[12];
	sprintf(count,"%d",linklist->count);
	error_login(count,"errlog.txt",0);
	EXEC SQL CLOSE get_product_cursor;
	return 0;
NOT_FOUND:
	return 1;
CUR_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
} 

/*员工表结构*/
/*

staff_id
staff_name
staff_pwd
staff_type  int
remark



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

/*取得指定页码的帐户信息*/
int Get_Staff_By_PageNumber(LINKLIST *linklist,int page,int recourdNum)
{
	int start;
	int end;
	int num;
	staff tmp;
	staff *pstaff;
	start=recourdNum * (page-1)+1;
	end=recourdNum*page;
	int pnum=0;
	EXEC SQL SELECT count(*) into :pnum FROM staff;
	if(end>pnum)
	{
		end=pnum;
	}

	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL WHENEVER SQLERROR GOTO CUR_ERR;

	EXEC SQL DECLARE get_staff_cursor CURSOR FOR 
		select B.staff_id,B.staff_name,B.staff_pwd,B.staff_type,nvl(B.remark,' '),B.RN from 
		(select A.*,rownum RN from staff A where rownum<=:end) B 
		where B.RN>=:start;
/*在上边的SQL语句中对remark加了nvl进入对空判断*/

	EXEC SQL OPEN get_staff_cursor;
	while(1)
	{
		EXEC SQL FETCH get_staff_cursor INTO :tmp.id,:tmp.name,:tmp.pwd,:tmp.type,:tmp.remark,:num;
		if(sqlca.sqlcode==0)
		{

			pstaff=(staff*)malloc(sizeof(staff));
			Trim(tmp.id);
			Trim(tmp.name);
			Trim(tmp.pwd);
			Trim(tmp.remark);
			*pstaff=tmp;

			insert_rear_link(linklist,pstaff);
		}
		else
			break;
	}

	EXEC SQL CLOSE get_staff_cursor;
	return 0;
NOT_FOUND:
	return 1;
CUR_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	return -1;
} 



/*销售表结构*/
/*
typedef struct sale 
{
	char sale_id[19];
	char trans_id[5];
	char staff_id[7];
	char sale_date[15];
	float given_sum;
	float real_sum;
	float sale_money;
	float change;
	int sale_state;
}sale;
*/

/*按日期查找 查询销售*/
int Search_SaleInfo_By_Date(LINKLIST * linklist, char * startdate,char * enddate)
{
/*
	error_login(startdate,"errlog.txt",0);
	error_login(enddate,"errlog.txt",0);
*/
	sale tmp;
	sale * pSale;
	int num;
	EXEC SQL WHENEVER SQLERROR GOTO SEARCH_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

/*	
	EXEC SQL DECLARE search_sale_by_date_cursor CURSOR FOR
		select sale_id,trans_id,staff_id,sale_date,sale_money from sale
		where sale_date>to_date(:startdate,'yyyymmddhh24miss') 
		and sale_date<to_date(:enddate,'yyyymmddhh24miss') or 
		sale_date=to_date(:startdate,'yyyymmddhh24miss') or 
		sale_date=to_date(:enddate,'yyyymmddhh24miss');		
*/
	int sale_state=1;
	EXEC SQL DECLARE search_sale_by_date_cursor CURSOR FOR
	select sale_id,trans_id,staff_id,sale_date,sale_money from sale
	where sale_date between to_date(:startdate,'yyyymmddhh24miss') 
	and to_date(:enddate,'yyyymmddhh24miss') and sale_state=:sale_state;

	EXEC SQL OPEN search_sale_by_date_cursor;
	while(1)
	{
		EXEC SQL FETCH search_sale_by_date_cursor INTO :tmp.sale_id,:tmp.trans_id,:tmp.staff_id,:tmp.sale_date,:tmp.sale_money;
		if(sqlca.sqlcode==0)
		{
			pSale=(sale*)malloc(sizeof(sale));
			Trim(tmp.sale_id);
			Trim(tmp.trans_id);
			Trim(tmp.staff_id);
			Trim(tmp.sale_date);
			*pSale=tmp;
			insert_rear_link(linklist,pSale);
/*
			error_login(pSale->sale_id,"errlog.txt",0);
			error_login(pSale->trans_id,"errlog.txt",0);
			error_login(pSale->staff_id,"errlog.txt",0);
			error_login(pSale->sale_date,"errlog.txt",0);
*/		}
		else
			break;
	}
	EXEC SQL CLOSE search_sale_by_date_cursor;
	return 0;
NOT_FOUND:
	error_login("Not found","errlog.txt",0);
	return 1;
SEARCH_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return -1;
}



/*按员工编号查询销售*/
int Search_SaleInfo_By_StaffID(LINKLIST * linklist, char * staff_id)
{
/*
	error_login(startdate,"errlog.txt",0);
	error_login(enddate,"errlog.txt",0);
*/
	sale tmp;
	sale * pSale;
	int sale_state=1;/*商品状态 1为出售的商品*/
	int num;
	EXEC SQL WHENEVER SQLERROR GOTO SEARCH_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

	
	EXEC SQL DECLARE search_sale_by_id_cursor CURSOR FOR
		select sale_id,trans_id,staff_id,sale_date,sale_money from sale
		where staff_id=:staff_id and sale_state=:sale_state;		
	
	EXEC SQL OPEN search_sale_by_id_cursor;
	while(1)
	{
		EXEC SQL FETCH search_sale_by_id_cursor INTO :tmp.sale_id,:tmp.trans_id,:tmp.staff_id,:tmp.sale_date,:tmp.sale_money;
		if(sqlca.sqlcode==0)
		{
			pSale=(sale*)malloc(sizeof(sale));
			Trim(tmp.sale_id);
			Trim(tmp.trans_id);
			Trim(tmp.staff_id);
			Trim(tmp.sale_date);
			*pSale=tmp;
			insert_rear_link(linklist,pSale);
/*
			error_login(pSale->sale_id,"errlog.txt",0);
			error_login(pSale->trans_id,"errlog.txt",0);
			error_login(pSale->staff_id,"errlog.txt",0);
			error_login(pSale->sale_date,"errlog.txt",0);
*/		}
		else
			break;
	}
	EXEC SQL CLOSE search_sale_by_id_cursor;
	return 0;
NOT_FOUND:
	error_login("Not found","errlog.txt",0);
	return 1;
SEARCH_ERR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	error_login(sqlca.sqlerrm.sqlerrmc,"errlog.txt",0);
	return -1;
}




/*连接成功返回0 连接失败返回1*/
int db_connection(char * username,char * password)
{
	EXEC SQL WHENEVER SQLERROR GOTO CONNECT_ERR;
	EXEC SQL CONNECT :username IDENTIFIED BY :password;
		return 0;
	CONNECT_ERR:
		EXEC SQL ROLLBACK RELEASE; /*连接出错就回滚释放连接*/
		return -1;
}