posdb.pc

本人做的一个基于LINUX平台的小程序

#include <sqlca.h>
#include <stdio.h>
#include <string.h>
#include "posdb.h"



void del_sale_temp(char *sale_id)
{
	EXEC SQL delete from sale_tmp where sale_id = :sale_id;
	EXEC SQL delete from sale where sale_id = :sale_id;
	EXEC SQL delete from sale_detail where sale_id = :sale_id;
	EXEC SQL commit;
}


int del_product(char *bar_code)
{
	EXEC SQL delete from product where bar_code = :bar_code;
	EXEC SQL commit;
}

int del_staff(char *str)
{
	EXEC SQL delete from staff where staff_id = :str;
	EXEC SQL commit;
}

void insert_sale_temp(char *sale_id,char *staff_id)
{
	EXEC SQL insert into sale_tmp
	values (:sale_id,:staff_id);
	EXEC SQL commit;
}

int insert_new_staff(struct staff *temp)
{
	char name[16] = {0};
	strcpy(name,temp->name);
	short ind_name;
	char remark[31] = {0};
	strcpy(remark,temp->remark);
	short ind_remark;
	EXEC SQL insert into staff values(:temp->id,:name:ind_name,:temp->passwd,
	:temp->type,:remark:ind_remark);
	EXEC SQL commit;
	return 1;
}

int insert_new_product(product_info *temp)
{
	EXEC SQL insert into product values(:temp);
	EXEC SQL commit;
	return 1;
}

void update_product(char *bar_code,int count)
{
	EXEC SQL update product set count = :count
	where bar_code = :bar_code;
	EXEC SQL commit;
}

int update_staff(struct staff *temp)
{
	char name[16] = {0};
	strcpy(name,temp->name);
	short ind_name;
	char remark[31] = {0};
	strcpy(remark,temp->remark);
	short ind_remark;
	EXEC SQL update staff set staff_name = :name:ind_name,
	staff_pwd=:temp->passwd,staff_type=:temp->type,
	remark = :remark:ind_remark where staff_id = :temp->id;
	EXEC SQL commit;
	return 1;

}

int update_product_info(product_info *temp)
{
	EXEC SQL update product set product_name = :temp->name,
	unit = :temp->unit,spec = :temp->spec,sale_price = :temp->sale_price,
	purchase_price = :temp->purchase_price,count = :temp->count,discount = :temp->discount
	where bar_code = :temp->bar_code;
	EXEC SQL commit;
	return 1;
}


int get_detail_id()
{
	int temp = 0;
	EXEC SQL SELECT SEQ_DETAIL_ID.nextval into :temp from dual;
	return temp;
	return 1;
}

int get_trans_id()
{
	int temp = 0;
	EXEC SQL SELECT SEQ_TRANS_ID.nextval into :temp from dual;
	return temp;
	return 1;
}

int is_sale_id(char *str)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL SELECT sale_id from sale where sale_id = :str;
	return 1;
	NO_FIND_DATA:
		return 0;
	return 1;
}

int is_staff_id(char *str)
{
	int type = 0;
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL SELECT  staff_type into :type  from staff where staff_id = :str;
	return type;
	NO_FIND_DATA:
		return -1;
}

int is_product_code(char *bar_code)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL SELECT *from product where  bar_code = :bar_code;
	return 1;
	NO_FIND_DATA:
		return 0;
	return 1;
}

int get_detail_bar_code(LINKLIST *linklist,char *str)
{
	
	char bar_code[9] = {0};

	product_info *check;
	check =(product_info *)malloc(sizeof(product_info));
	memset(check,0,sizeof(product_info));

	int count_temp=0;

	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL DECLARE check_cursor CURSOR FOR
	select bar_code,count from sale_detail
	where sale_id = :str;
	EXEC SQL open check_cursor;
	
	for(;;)
	{
		EXEC SQL FETCH check_cursor into :bar_code,:count_temp;

		get_product(bar_code,check);

		for_sale *temp;
		temp = (for_sale *)malloc(sizeof(for_sale));
		memset(temp,0,sizeof(for_sale));
		
		strcpy(temp->bar_code,bar_code);
		strcpy(temp->name,check->name);
		strcpy(temp->unit,check->unit);
		strcpy(temp->spec,check->spec);
		temp->sale_price = check->sale_price;
		temp->discount = check->discount;
		temp->storage = check->count;

		temp->count = count_temp;
		temp->sum = (temp->sale_price)*(temp->discount)*(temp->count);
		insert_rear_link(linklist,temp);
		memset(check,0,sizeof(product_info));
		memset(bar_code,0,9);
	}
	return 1;
	NO_FIND_DATA:
		free(check);
		EXEC SQL CLOSE check_cursor;
		return 0;
	return 1;
}

int get_product(char *bar_code,product_info * temp)
{
	varchar code[8]={0,"\0"};
	varchar name[30]={0,"\0"};
	varchar unit[16]={0,"\0"};
	varchar spec[16]={0,"\0"};
	float sale_price = 0;
	float purchase_price = 0;
	int count = 0;
	float discount = 0;

	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL SELECT bar_code,product_name,unit,spec,sale_price,purchase_price,count,discount
	into :code,:name,:unit,:spec,:sale_price,:purchase_price,:count,:discount from product
	where bar_code = :bar_code;
	strcpy(temp->bar_code,code.arr);
	strcpy(temp->name,name.arr);
	strcpy(temp->unit,unit.arr);
	strcpy(temp->spec,spec.arr);
	temp->sale_price = sale_price;
	temp->purchase_price = purchase_price;
	temp->count = count;
	temp->discount = discount;
	return 1;
	NO_FIND_DATA:
		return 0;
	return 1;
}

int user_confirm(char *user,struct staff *temp)
{
	varchar id[7]={0,"\0"};
	varchar name[16]={0,"\0"};
	varchar passwd[17]={0,"\0"};
	varchar remark[31]={0,"\0"};
	int type=0;
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL SELECT staff_id,staff_name,staff_pwd,staff_type,remark into :id,:name,:passwd,:type,:remark from staff where staff_id = :user;
	strcpy(temp->id,id.arr);
	strcpy(temp->name,name.arr);
	strcpy(temp->passwd,passwd.arr);
	temp->type=type;
	strcpy(temp->remark,remark.arr);
	return temp->type;
	NO_FIND_DATA:
		return -1;
	return 1;
}

int sale_list_by_date(LINKLIST *linklist,char *first,char *last)
{
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL DECLARE sale_date CURSOR FOR
	select  sale_id,trans_id,staff_id,to_char(sale_date,'yyyy-mm-dd'),
	given_sum,real_sum,sale_money,change,sale_state from sale where sale_date >= to_date(:first,'yyyymmdd')
	and  sale_date<= to_date(:last,'yyyymmdd');
	EXEC SQL open sale_date;
	for(;;)
	{
		sale_record *temp;
		temp = (sale_record *)malloc(sizeof(sale_record));
		memset(temp,0,sizeof(sale_record));
		EXEC SQL FETCH sale_date into :temp;
		insert_rear_link(linklist,temp);
	}
	return 1;

	NO_FIND_DATA:
		EXEC SQL CLOSE sale_date;
		return -1;
	conner:
		EXEC SQL CLOSE sale_date;
		return 0;
}


int db_connect()
{
	char user[30],passwd[20];
	memset(user,0,30);
	memset(passwd,0,20);
	getconf("connect.conf",user,passwd);
	/*EXEC SQL BEGIN DECLARE SECTION;*/
	/*EXEC SQL END DECLARE SECTION;*/
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL CONNECT :user IDENTIFIED BY :passwd;
	printf("connect oracle!\n");
	return 0;	
 conner:
	printf("connect error!\n");
	EXEC SQL ROLLBACK RELEASE;
	return -1;
}

int insert_sale_detail(char *bar_code,int count,float sale_price,char *detail_id,sale_record *temp)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL insert into sale_detail 
	values (:detail_id,:temp->sale_id,:bar_code,:count,:sale_price,:temp->sale_state);
	EXEC SQL commit;
	return 1;
	NO_FIND_DATA:
		return -1;
	conner:
		return 0;
	return 1;

}

int update_sale(float sale_money,float change,int state,char *str)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL update sale set sale_money=:sale_money,
	change=:change,sale_state=:state where sale_id = :str;
	EXEC SQL COMMIT;
	return 1;

	NO_FIND_DATA:
		return -1;
	conner:
		return 0;
	return 1;
}

int get_sale(sale_record *str,char *sale_id)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;

	EXEC SQL select sale_id,trans_id,staff_id,to_char(sale_date,'yyyymmddHH24MISS'),given_sum,
	real_sum,sale_money,change,sale_state
	into :str->sale_id,:str->trans_id,:str->staff_id
	,:str->date,:str->give_sum,:str->real_sum,
	:str->sale_money,:str->change,:str->sale_state from sale  where sale_id = :sale_id;
	return 1;
	
	NO_FIND_DATA:
		return -1;
	conner:
		return 0;
	return 1;
}

int get_product_info_list(LINKLIST *linklist)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL DECLARE product_cursor CURSOR FOR
	select * from product;
	EXEC SQL open product_cursor;
	for(;;)
	{
		product_info *check;
		check =(product_info *)malloc(sizeof(product_info));
		memset(check,0,sizeof(product_info));
		EXEC SQL FETCH product_cursor into :check;
		insert_rear_link(linklist,check);
	}
	return 1;

	NO_FIND_DATA:
		EXEC SQL CLOSE product_cursor;
		return -1;

	conner:
		return 0;

	return 1;
}


int insert_sale(sale_record *temp)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;

	EXEC SQL insert into sale
	values (:temp->sale_id,:temp->trans_id,:temp->staff_id,to_date
	(:temp->date,'yyyymmddHH24MISS'),:temp->give_sum,:temp->real_sum,
	:temp->sale_money,:temp->change,:temp->sale_state);
	EXEC SQL commit;
	return 1;
	
	
	NO_FIND_DATA:
		return -1;
	conner:
		return 0;
	return 1;
}


int get_staff_list(LINKLIST *linklist)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL DECLARE user_cursor CURSOR FOR
	select staff_id,staff_name,staff_pwd,staff_type,remark from staff;
	EXEC SQL open user_cursor;
	
	for(;;)
	{
		staff_info *temp;
		temp = (staff_info *)malloc(sizeof(staff_info));
		memset(temp,0,sizeof(staff_info));
		EXEC SQL FETCH user_cursor into :temp;
		insert_rear_link(linklist,temp);
	}
	return 1;

	NO_FIND_DATA:
		EXEC SQL CLOSE user_cursor;
		return -1;

	conner:
		return 0;
	return 1;
}

int sale_list_by_user(LINKLIST *linklist,char *str)
{
	EXEC SQL WHENEVER NOT FOUND GOTO NO_FIND_DATA;
	EXEC SQL WHENEVER SQLERROR GOTO conner;
	EXEC SQL DECLARE user_sale CURSOR FOR
	select sale_id,trans_id,staff_id,to_char(sale_date,'yyyy-mm-dd'),
	given_sum,real_sum,sale_money,change,sale_state from sale where staff_id = :str;
	EXEC SQL open user_sale;

	for(;;)
	{
		sale_record *temp;
		temp = (sale_record *)malloc(sizeof(sale_record));
		memset(temp,0,sizeof(sale_record));
		EXEC SQL FETCH user_sale into :temp;
		insert_rear_link(linklist,temp);
	}

	NO_FIND_DATA:
		EXEC SQL CLOSE user_sale;
		return 1;

	conner:
		return 0;
}

void getconf(const char *filename,char *user,char *passwd)
{
	FILE *fp;
	char buf[128];
	int pos=0,i=0;

	if((fp=fopen(filename,"r")) == NULL)
	{
		printf("read %s failed!",filename);
		return;
	}
	
	while(fgets(buf,128,fp))
	{
		i=0;

		if(buf[0]=='#'||isspace(buf[0]))
			continue;

		for(i;i<strlen(buf);i++)
		{
			if(buf[i] == '=')
			{
				i++;
				for(i;i<strlen(buf);i++)
				{
					if(buf[i] != ' ')
					{
						pos++;
						if(pos == 1)
							memcpy(user,buf+i,strlen(buf)-i-2);
						if(pos == 2)
							memcpy(passwd,buf+i,strlen(buf)-i-2);
						break;
					}
				}

				break;
			}
		}
		memset(buf,0,128);
	}

	fclose(fp);
}