insert_data.pc

LINUX下POS收银系统,可以对货物及工作人员进行管理.近本实现增删改查的功能

#include "control_db.h"

EXEC SQL BEGIN DECLARE SECTION;
varchar username[20];
varchar password[20];
EXEC SQL END DECLARE SECTION;
EXEC  SQL INCLUDE SQLCA;

EXEC SQL INCLUDE ORACA;
EXEC ORACLE OPTION (ORACA=YES);
void ErrorDB(char *msg, char *file, int line);
void connect_db(char *name,char *pass)
{
/*
	strcpy(username.arr,"cj0804");
	username.len=strlen(username.arr);
	strcpy(password.arr,"cj0804");
	password.len=strlen(password.arr);*/
	EXEC SQL WHENEVER SQLERROR DO sql_error();
	EXEC SQL CONNECT :name IDENTIFIED BY :pass;
	printf("connect scuess!!!\n");
}
void del_table_sq_constraint()
{
	EXEC SQL alter table table_sell
	 drop constraint FK_SALE_STAF;
	EXEC SQL alter table table_sell_list
	 drop constraint FK_SALE_PROD;

	EXEC SQL alter table table_sell_list
	 drop constraint FK_SALDET_SALE;

	EXEC SQL drop table table_commodity cascade constraints;

	EXEC SQL drop table table_employee cascade constraints;

	EXEC SQL drop table table_sell cascade constraints;

	EXEC SQL drop table table_sell_list cascade constraints;
	EXEC SQL drop sequence SEQ_BUSINESS ;
	EXEC SQL drop sequence SEQ_GOODS_CODE ;
	EXEC SQL drop sequence SEQ_PUTONG_ID ;
	EXEC SQL drop sequence SEQ_SELL_ID ;
	EXEC SQL drop sequence SEQ_SELL_LIST_ID ;
	EXEC SQL drop sequence SEQ_TEST ;
	EXEC SQL drop sequence SEQ_TUIHUO_ID ;
	EXEC SQL COMMIT;
}
void insert_db(EMP emp)/*向数据库中插入一条数据*/
{
	char sqlstr[100]="insert into table_employee values(:v1,:v2,:v3,:v4,:v5,:v6)";
	EXEC SQL PREPARE S FROM :sqlstr;
	EXEC SQL EXECUTE S USING :emp;
	EXEC SQL COMMIT;

}
/*char *id,char *password*/
int login(char *id,char *password,EMP *emp)
{
	EMP e={"\0","\0","\0",-1,-1,"\0"};
	EMP *ep=&e;
	EXEC SQL WHENEVER NOT FOUND continue;
	EXEC SQL SELECT * INTO :ep FROM TABLE_EMPLOYEE
	WHERE EMPLOYEE_ID = :id;
	del_blank(e.emp_id);
	del_blank(e.emp_name);
	del_blank(e.emp_passwd);
	del_blank(e.remark);
	
	if(strlen(e.emp_passwd) == 0)
	{
		return 0;
	}
	else
	{
		if(strcmp(e.emp_passwd,password) == 0)
		{
			if(e.delete_sign == 1)
			{
				return 1;
			}
			else
			{
				memcpy(emp,ep,sizeof(EMP));
				switch(e.emp_style)
				{
				case 0:
					return 4;
					break;
				case 1:
					return 3;
					break;
				case 2:
					return 5;
					break;
				}
			}
		}
		else
		{
			return 2;
		}
	}

}
void updata_goods_from_link(LINK *link,char *str,int count)/*更新数据库中table_commodity表stock_number的值*/
{
	int number=0;
	int leave=0;
	EXEC SQL WHENEVER NOT FOUND continue;/*没找到做continue*/
	EXEC SQL SELECT STOCK_NUMBER INTO :number FROM TABLE_COMMODITY WHERE BAR_CODE = :str;
	leave=number+count;
	EXEC SQL UPDATE TABLE_COMMODITY SET STOCK_NUMBER =:leave WHERE BAR_CODE = :str;
	EXEC SQL COMMIT;
}

void update_user_table(EMP *e)
{
	EXEC SQL UPDATE TABLE_EMPLOYEE SET EMPLOYEE_NAME =:e->emp_name,EMPLOYEE_PASSWD =:e->emp_passwd,
	EMPLOYEE_STYLE =:e->emp_style,REMARK =:e->remark WHERE EMPLOYEE_ID = :e->emp_id;
	EXEC SQL COMMIT;
}
void update_goods_table(GOODS *g)
{
	EXEC SQL UPDATE TABLE_COMMODITY SET COMMODITY_NAME =:g->commodity_name,UNTI =:g->unti,
	SPEC =:g->spec,SELL_PRICE =:g->sell_price,STOCK_PRICE =:g->stock_price ,STOCK_NUMBER =:g->stock_number
	,AGIO =:g->agio,DELETE_SIGN =:g->delete_sign WHERE BAR_CODE = :g->bar_code;
	EXEC SQL COMMIT;
}

void del_user(EMP *e)
{
	int i=1;
	int *p=&i;
	EXEC SQL UPDATE TABLE_EMPLOYEE SET DELETE_SIGN =:i WHERE EMPLOYEE_ID = :e->emp_id;
	EXEC SQL COMMIT;
}
int insert_sell_list(SELL_LIST *s)
{
	char sqlstr[300]="insert into table_sell values(:v1,:v2,:v3,to_date(:v4,'yyyy-mm-dd hh24:mi:ss'),:v5,:v6,:v7,:v8,:v9)";
	s->list_num[4]=0;
	EXEC SQL WHENEVER SQLERROR DO ErrorDB("error",__FILE__,__LINE__);
	EXEC SQL PREPARE S FROM :sqlstr;
	EXEC SQL EXECUTE S USING :s->sell_ID,:s->employee_ID,:s->list_num,:s->sell_date,:s->payment_money,:s->must_pay_money,:s->fact_money,:s->give_change,:s->sell_fettle;
	
	EXEC SQL COMMIT;
}

void insert_sell_goods_list(SELL_GOODS_LIST *sgl)
{
	char sqlstr[300]="insert into table_sell_list values(:v1,:v2,:v3,:v4,:v5)";
	EXEC SQL WHENEVER SQLERROR DO ErrorDB("error",__FILE__,__LINE__);
	EXEC SQL PREPARE S FROM :sqlstr;
	EXEC SQL EXECUTE S USING :sgl;
	
	EXEC SQL COMMIT;
}
void get_business_number(char *number)/*生成交易号*/
{
	char temp[30]={"\0"};
	EXEC SQL SELECT SEQ_BUSINESS.NEXTVAL INTO:temp FROM DUAL;
	strcpy(number,temp);
	*number=*temp;

}
int get_sell_id()/*生成单号*/
{
	int sell_id=0;
	EXEC SQL SELECT SEQ_SELL_ID.NEXTVAL INTO:sell_id FROM DUAL;
	return sell_id;
}
int get_tuihuo_id()/*生成退货用户ID*/
{
	int sell_id=0;
	EXEC SQL SELECT SEQ_TUIHUO_ID.NEXTVAL INTO:sell_id FROM DUAL;
	return sell_id;
}
int get_putong_id()/*生成普通用户ID*/
{
	int sell_id=0;
	EXEC SQL SELECT SEQ_PUTONG_ID.NEXTVAL INTO:sell_id FROM DUAL;
	return sell_id;
}
int get_goods_code()/*生成条形码*/
{
	int sell_id=0;
	EXEC SQL SELECT SEQ_GOODS_CODE.NEXTVAL INTO:sell_id FROM DUAL;
	return sell_id;
}
int get_sell_list_id()/*生成商品明细表ID*/
{
	int sell_id=0;
	EXEC SQL SELECT SEQ_SELL_LIST_ID.NEXTVAL INTO:sell_id FROM DUAL;
	return sell_id;
}
int select_user(EMP *e,char *str)/*查询用户存在*/
{
	EMP emp={"\0","\0","\0",-1,-1,"\0"};
	EMP *ep=&emp;
	EXEC SQL WHENEVER NOT FOUND continue;
	EXEC SQL SELECT * INTO:ep FROM TABLE_EMPLOYEE	WHERE EMPLOYEE_ID = :str;

	del_blank(emp.emp_id);
	del_blank(emp.emp_name);
	del_blank(emp.emp_passwd);
	del_blank(emp.remark);

	if(strlen(emp.emp_name) == 0)
	{
		return 0;
	}
	else
	{
		memcpy(e,ep,sizeof(EMP));
		if(emp.delete_sign == 1)
		{
			return 1;
		}
		else
		{
			switch(emp.emp_style)
			{
			case 0:
				return 4;
				break;
			case 1:
				return 3;
				break;
			case 2:
				return 5;
				break;
			}
		}
	}
}
int select_db_goods(char *str)/*查询货物是否存在*/
{
	GOODS good;
	memset(&good,0,sizeof(GOODS));
	EXEC SQL DECLARE a_emp_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_COMMODITY
	WHERE BAR_CODE = :str;/*创建一个游标*/
	EXEC SQL OPEN a_emp_cursor ;/*打开游标*/
	EXEC SQL WHENEVER NOT FOUND continue;/*没找到做break*/
	EXEC SQL FETCH a_emp_cursor INTO :good;/*提取数据*/
	del_blank(good.bar_code);
	if(strlen(good.bar_code)==0)
	{
		/*没有那东西*/
		EXEC SQL CLOSE a_emp_cursor;/*关闭游标*/
		return -1;
	}
	else
	{
		EXEC SQL CLOSE a_emp_cursor;/*关闭游标*/
		return good.stock_number;/*返回那东西的个数*/
	}
	
}

int select_exist_goods(char *str,GOODS *g)/*查询货物是否存在,若存在拷贝该货物信息*/
{
	GOODS good;
	int sign=0;
	memset(&good,0,sizeof(GOODS));
	EXEC SQL DECLARE goods_emp_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_COMMODITY
	WHERE COMMODITY_NAME = :str;/*创建一个游标*/
	EXEC SQL OPEN goods_emp_cursor ;/*打开游标*/
	EXEC SQL WHENEVER NOT FOUND continue;/*没找到做break*/
	EXEC SQL FETCH goods_emp_cursor INTO :good;/*提取数据*/
	del_blank(good.commodity_name);
	del_blank(good.bar_code);
	del_blank(good.unti);
	del_blank(good.spec);

	if(strlen(good.commodity_name)==0)
	{
		/*没有那东西*/
		EXEC SQL CLOSE goods_emp_cursor;/*关闭游标*/
		return sign;
	}
	else
	{
		if(good.delete_sign == 1)
		{
			sign=2;
		}
		else
		{
			sign=1;
		}
		good.unti[16]=0;
		good.spec[16]=0;
		memcpy(g,&good,sizeof(GOODS));
		
		EXEC SQL CLOSE goods_emp_cursor;/*关闭游标*/
		return sign;/*返回那东西的个数*/
	}
	
}

int cmp_tow_uns(NODE *node,char *key)
{
	if (strcmp(((UNS *)(node->data))->bar_code,key)==0)
	{
		return 1;
	} 
	else
	{
		return 0;
	}
}
int (*fp1)(NODE *node,char * key)=cmp_tow_uns;
NODE * select_all_goods(LINK *link,char *str,int count)/*查找满足条件的所有数据*/
{
	GOODS good;
	UNS sell={12,"CN00000","KKKKK","KKK","KKK",12,33,23};
	NODE *temp=NULL;
	memset(&good,0,sizeof(GOODS));
	memset(&sell,0,sizeof(UNS));
	EXEC SQL DECLARE emp_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_COMMODITY
	WHERE BAR_CODE = :str;
	EXEC SQL OPEN emp_cursor ;
	EXEC SQL WHENEVER NOT FOUND do break;
	while(1)
	{
		EXEC SQL FETCH emp_cursor INTO :good;
		del_blank(good.bar_code);
		del_blank(good.commodity_name);
		del_blank(good.unti);
		del_blank(good.spec);
		if(good.stock_number - count >= 0)
		{
			sell.num=good.num;
			strcpy(sell.bar_code,good.bar_code);
			strcpy(sell.commodity_name,good.commodity_name);
			strcpy(sell.unti,good.unti);
			strcpy(sell.spec,good.spec);
			sell.sell_price=good.sell_price*good.agio;
			sell.total_count=count;
			sell.total_pay=sell.total_count*sell.sell_price;
			temp=link->get_node_by_key(link,sell.bar_code,(int (*)(void *,void *))fp1);
			if(temp == NULL)
			{
				link->insert_front(link, &sell, sizeof(UNS));
			}
			else
			{
				((UNS *)(temp->data))->total_count+=sell.total_count;
				((UNS *)(temp->data))->total_pay+=sell.total_pay;
			}
			updata_goods_from_link(link,str,-count);
		}
		else
		{
			temp=(NODE *)-1;
		}

	}
	EXEC SQL CLOSE emp_cursor;/*关闭游标*/
	return temp;
}
/*
typedef struct sell_list
{
	int sell_ID;
    char employee_ID[7];
    char list_num[19];
    char sell_date[15];
    float payment_money;
    float must_pay_money;
    float fact_money;
    float give_change;
	int sell_fettle;
}SELL_LIST;
*/
int select_note_by_date(char *date_s,char *date_e,LINK *note)
{
	int sign=0;
	SELL_LIST sl;
	memset(&sl,0,sizeof(SELL_LIST));

	EXEC SQL DECLARE sl_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_SELL
	WHERE SELL_DATE >= to_date(:date_s,'yyyymmdd ') and SELL_DATE <= to_date(:date_e,'yyyymmdd ') ;
	EXEC SQL OPEN sl_cursor ;
	EXEC SQL WHENEVER NOT FOUND do break;
	while(1)
	{
		EXEC SQL FETCH sl_cursor INTO :sl;

		del_blank(sl.employee_ID);
		del_blank(sl.list_num);
		del_blank(sl.sell_date);

		if(sl.sell_ID > 0)
		{
			note->insert_front(note, &sl, sizeof(SELL_LIST));
			sign=1;
		}	

	}
	EXEC SQL CLOSE sl_cursor;/*关闭游标*/
	return sign;
}

int select_note_by_user_id(char *userID,LINK *note)
{
	int sign=0;
	SELL_LIST sl;
	memset(&sl,0,sizeof(SELL_LIST));

	EXEC SQL DECLARE u_sl_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_SELL
	WHERE EMPLOYEE_ID = :userID;
	EXEC SQL OPEN u_sl_cursor ;
	EXEC SQL WHENEVER NOT FOUND do break;
	while(1)
	{
		EXEC SQL FETCH u_sl_cursor INTO :sl;

		del_blank(sl.employee_ID);
		del_blank(sl.list_num);
		del_blank(sl.sell_date);

		if(sl.sell_ID > 0)
		{
			note->insert_front(note, &sl, sizeof(SELL_LIST));
			sign=1;
		}	

	}
	EXEC SQL CLOSE u_sl_cursor;/*关闭游标*/
	return sign;
}
int select_sell_list_by_ID(SELL_GOODS_LIST *s,char *str,LINK *note)
{
	int sign=0;
	SELL_GOODS_LIST sl;
	memset(&sl,0,sizeof(SELL_GOODS_LIST));

	EXEC SQL DECLARE sid_sl_cursor CURSOR FOR
	SELECT * 
	FROM TABLE_SELL_LIST
	WHERE SELL_ID = :str;
	EXEC SQL OPEN sid_sl_cursor ;
	EXEC SQL WHENEVER NOT FOUND do break;
	while(1)
	{
		EXEC SQL FETCH sid_sl_cursor INTO :sl;

		del_blank(sl.bar_code);
		if(sl.sell_list_id > 0)
		{
			note->insert_front(note, &sl, sizeof(SELL_GOODS_LIST));
			sign=1;
		}	

	}
	EXEC SQL CLOSE sid_sl_cursor;/*关闭游标*/
	return sign;
}
void del_sell_list_by_sell_id(int sell_id)
{
	EXEC SQL WHENEVER NOT FOUND continue;
	EXEC SQL DELETE FROM TABLE_SELL_LIST WHERE SELL_ID=:sell_id;
	EXEC SQL COMMIT;
}
void del_sell_list_by_bar_code(char *bar_code)
{
	EXEC SQL WHENEVER NOT FOUND continue;
	EXEC SQL DELETE FROM TABLE_SELL_LIST WHERE BAR_CODE=:bar_code;
	EXEC SQL COMMIT;
}
char *find_first_not_of(char *str,char ch)/*找出字符串中第一个不是ch字符的位置*/
{
	int len=strlen(str);
	int i=0;
	for(i=0;i<len;i++)
	{
		if(*(str+i) == ch)
		{
			continue;
		}
		else
		{
			return str+i;
		}
	}
	return NULL;
}
char *find_last_not_of(char *str,char ch)/*找出字符串中最后一个不是ch字符的位置*/
{
	int len=strlen(str);
	int i=0;
	for(i=len-1;i>=0;i--)
	{
		if(*(str+i) == ch)
		{
			continue;
		}
		else
		{
			return str+i;
		}
	}
	return NULL;
}
/*将字符串数组source_str从偏移量为start_space开始拷贝num个字符去aim_str字符数组*/
int copy_str(char *source_str,char *aim_str,int num,int start_space)
{
	int i=0;
	if(source_str == aim_str)
	{
		return 0;
	}
	else
	{
		for(i=0;i<num;i++)
		{
			*(aim_str+i) = *(source_str+start_space+i);
			if(*(source_str+start_space+i) == '\0')
			{
				return i;
			}
			
		}
		*(aim_str+i) = '\0';
		return  i;
	}
	
}

void del_blank(char *str)
{
	int len=strlen(str);
	int i=0;
	char *temp=(char *)malloc(len+1);
	char *first=NULL,*last=NULL;
	memset(temp,0,len+1);
	first=find_first_not_of(str,' ');
	last=find_last_not_of(str,' ');	
	if(first==0&&last==0)
	{
		memset(str,'\0',len+1);
	}
	else
	{
		i=copy_str(str,temp,last-first+1,first-str);
		strcpy(str,temp);
	}
	free(temp);

}

void sql_error()/*数据库错误提示*/
{
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	printf("\nORACLE error detected:\n");
	printf("\n%.70s \n",sqlca.sqlerrm.sqlerrmc);
	EXEC SQL ROLLBACK RELEASE;
	exit(1);
}

void quit_db()
{
	EXEC SQL COMMIT WORK RELEASE;
}


void ErrorDB(char *msg, char *file, int line)
{
	printf("msg:%s-%d:%s",file,line,sqlca.sqlerrm.sqlerrmc);
	exit(0);
	printf("in \"%.*s...\"\n",
        oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
	printf("on line %d of %.*s.\n\n",
        oraca.oraslnr, oraca.orasfnm.orasfnml,
        oraca.orasfnm.orasfnmc);
	exit(0);
}