operationdb.pc

linux环境下结合ncurse库

#include "DuLink.h"
#include "OperationDB.h"
EXEC SQL INCLUDE sqlca;


/*********************************************************
*功能:初始化数据库即创建数据库
*参数: 无
*返回：0
*********************************************************/
int CreatDbaseUser()
{
	EXEC SQL CONNECT :"system" IDENTIFIED BY :"manager";
	EXEC SQL drop user pos cascade;
	EXEC SQL CREATE USER pos identified by pos;
	EXEC SQL grant resource,create table,connect to pos;
	return 0;
}


/*******************************************************************
Function: 读取配置文件
Param: 1.配置文件名 2.配置信息结构体指针
Return: bool 读取配置文件是否成功
Data: 2008-10-29
********************************************************************/
int ReadConfig(char * Filename,ConfigInfo *pConfig)
{
	char * note="#";
	FILE *pfile;
	char line[512];
	char * token1;
	char * token2;
	memset(line,0,512);	
	if((pfile=fopen(Filename,"r"))==NULL)
	{
		return 0;
	}
	else
	{
	}
	
	while(fgets(line,512,pfile)!=NULL)
	{
		if(!strlen(line) || !strncmp(line,note,1) || !strcmp(line,"\n"))
		{
			continue;
		}
		
		token1=strtok(line," ");
		token2=strtok(NULL,"\n");
		if(strcmp(token1,"DataBaseUserName")==0)
		{
			strcpy(pConfig->DataBaseUserName,token2);
		}else if(strcmp(token1,"DataBasePassword")==0)
		{
			
			strcpy(pConfig->DataBasePassword,token2);
		}else if(strcmp(token1,"datasid")==0)
		{
			
			strcpy(pConfig->Datasid,token2);
		}	
	}

	int len=strlen(pConfig->DataBaseUserName);
	if(pConfig->DataBaseUserName[len-1]==13)
	{
		pConfig->DataBaseUserName[len-1]='\0';
	}
	
	len=strlen(pConfig->DataBasePassword);
	if(pConfig->DataBasePassword[len-1]==13)
	{
		pConfig->DataBasePassword[len-1]='\0';
	}
	len=strlen(pConfig->Datasid);
	if(pConfig->Datasid[len-1]==13)
	{
		pConfig->Datasid[len-1]='\0';
	}
	fclose(pfile);
	return 1;
}

/*******************************************************************
Function: 连接数据库函数
Param:  用户信息结构指针
Return: 返回  连接数据库是否成功
Data: 2008-10-29
********************************************************************/

int ConnectDB(ConfigInfo * pConfig)
{
	
	EXEC SQL WHENEVER SQLERROR GOTO CONNECT_ERROR;
	EXEC SQL CONNECT: pConfig->DataBaseUserName IDENTIFIED BY: pConfig->DataBasePassword;
	return 1;
	CONNECT_ERROR:
	return 0;
}


/*******************************************************************
Function: 数据库查找帐号信息
Param:  用户信息结构指针
Return: 返回 1.找到  0没有找到 -1 错误
Data: 2008-10-30
********************************************************************/
int ConnectAccout(StaffInfo* staff,UserInfo* userinfo)
{

	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT * INTO: staff
					FROM STAFF
					WHERE STAFF_ID=:userinfo->username and STAFF_PWD=:userinfo->password;
	return 1;

	NOT_FOUND:
	return 0;

	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
}

/*************************************************************
Function: 得到交易号
Param: char number[5];
Return: 返回 1.找到  0没有找到 -1 错误
*************************************************************/

int GetTradeID(char * strid)
{
	
	char tempid[6];
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT seq_trans_id.nextval into :tempid
			FROM dual;
			strcpy(strid,tempid);
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0;
}

/*************************************************************
Function: 取得指定条形码的商品信息
Param: 商品信息结构体  条形码字符指针
Return: 返回 1.找到  0没有找到 -1 错误
*************************************************************/
int GetGoodsByBarCode(Goods * goods,char * barcode)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL SELECT * INTO:goods FROM PRODUCT WHERE BAR_CODE=:barcode;
	return 1;
	
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0;
}


/*************************************************************
Function: 根据条形码删除商品信息
Param: 商品信息结构体  条形码字符指针
Return: 返回 1.找到  0没有找到 -1 错误
*************************************************************/
int DelGoodsByBarCode(char * barcode)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

	EXEC SQL DELETE FROM PRODUCT WHERE BAR_CODE=:barcode;
	
	EXEC SQL COMMIT;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0;
}


/*************************************************************
Function: 根据帐户信息删除帐户
Param: 帐户结果体指针
Return: 返回 1.找到  0没有找到 -1 错误
*************************************************************/
int DelStaff(StaffInfo * pstaff)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

	EXEC SQL DELETE FROM STAFF WHERE STAFF_ID=:pstaff->staff_id;
	EXEC SQL COMMIT;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0;
}

/*********************************************
  FunCtion: 信息插入销售表
  Param: 销售表结构体指针
  Return :返回 1.成功  0失败 -1 错误
*********************************************/
int InsertSaleTable(SaleTableInfo * pp)
{
	
	 SaleTableInfo p;	
	memcpy(&p,pp,sizeof(SaleTableInfo));
	
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	
	
	EXEC SQL INSERT INTO SALE VALUES(:p.sale_id,:p.trans_id,:p.staff_id,
	to_date(:p.sale_date,'yymmdd'),:p.give_sum,:p.real_sum,:p.money,:p.change,:p.sale_state);

	EXEC SQL COMMIT;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0;
}

/*****************************************
  Function: 信息插入销售明细表
  Param : 销售明细表信息结构体指针
  Return；返回 1.成功  0失败 -1 错误
*******************************************/
int InsertSaleDetailTable(SaleDetailInfo saledetail)
{
	
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL insert into SALE_DETAIL values(
	:saledetail.detail_id,
	:saledetail.sale_id,
	:saledetail.bar_code,
	:saledetail.count,
	:saledetail.sale_price,
	:saledetail.sale_state);
	EXEC SQL COMMIT;	
	return 1;
_ERROR:
	EXEC SQL ROLLBACK RELEASE;
	return -1;
NOT_FOUND:
	return 0;
   return 1;
}


/*****************************************************************************
FUNCTION:读取商品信息查询
param: 存在放一条数据链里面
return:1 成功，0 失败 ,-1 错误
******************************************************************************/
int SelectGoods(C_CLASS LINKLIST * link)
{
	Goods goods;
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL DECLARE  goods_cursor CURSOR FOR  
		SELECT * from product;
	EXEC SQL OPEN  goods_cursor;
	int i;
	for(i=0;i<200;i++)
	{
		EXEC SQL FETCH goods_cursor INTO:goods;
		link_insert_rear(link,&goods,sizeof(Goods));
		if(sqlca.sqlcode!=0)
		{
			break;
		}
	}
	
	EXEC SQL CLOSE goods_cursor;


	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0; 		
}
/*******************************************
	FUNCTION:插入商品信息
	param: 商品信息结构体
	return:1 成功，0 失败 ,-1 错误
**********************************************/
int InsertProduct(Goods * pgoods)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

	EXEC SQL INSERT INTO PRODUCT VALUES(:pgoods->bar_code,:pgoods->product_name,
	:pgoods->unit,:pgoods->spec,:pgoods->sale_price,:pgoods->purchase_price,
	:pgoods->count,:pgoods->discount);
	EXEC SQL COMMIT;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return 0;
	NOT_FOUND:
	return -1;
}

/*****************************************************************************
FUNCTION:插入新的帐户
param: 新的帐户的结构体指针
return:1 成功，0 失败 ,-1 错误
******************************************************************************/
int InsertStaff(StaffInfo * newstaff)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;

	EXEC SQL INSERT INTO staff VALUES(:newstaff->staff_id,:
	newstaff->staff_name,:newstaff->staff_pwd,:newstaff->staff_type,:newstaff->staff_remark);
	EXEC SQL COMMIT;

	return 1;

	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return 0;
	NOT_FOUND:
	return -1;
}

/*****************************************************************************
FUNCTION:查询用户信息
param: 帐户的结构体指针
return:1 成功，0 失败 ,-1 错误
******************************************************************************/
int GetStaffByID(StaffInfo * staffinfo,char * strid)
{
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	
	EXEC SQL select *  INTO:staffinfo from staff where STAFF_ID=:strid;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return 0;
	NOT_FOUND:
	return -1;
}

/*****************************************************************************
FUNCTION:读取帐户信息查询
param: 存在放一条数据链里面
return:1 成功，0 失败 ,-1 错误
******************************************************************************/
int SelectStaff(C_CLASS LINKLIST * link)
{
	StaffInfo staff;
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL DECLARE  staff_cursor CURSOR FOR  
		SELECT * from staff;
	EXEC SQL OPEN  staff_cursor;
	int i;
	for(i=0;i<200;i++)
	{
		EXEC SQL FETCH staff_cursor INTO:staff;
		link_insert_rear(link,&staff,sizeof(StaffInfo));
		if(sqlca.sqlcode!=0)
		{
			break;
		}
	}
	EXEC SQL CLOSE staff_cursor;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return -1;
	NOT_FOUND:
	return 0; 		
}


/*
FUNCTION:退货处理函数
param: saleid barcode 查询条件
return:1 成功，0 失败 ,-1 错误
*/

int SelectSaleDetail(char * saleid,char * barcode,float * howmuch)
{

	float a=0;
	int  thiscount=0;
	int b=0;
	EXEC SQL WHENEVER SQLERROR GOTO _ERROR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	
	EXEC SQL select sale_price,count INTO:a,:thiscount from SALE_DETAIL where sale_id=:saleid and bar_code=:barcode;
	*howmuch=a*thiscount;

	EXEC SQL update SALE_DETAIL set sale_state=:b where sale_id=:saleid and bar_code=:barcode and sale_state=1;
	EXEC SQL update product set count=count+:thiscount where bar_code=:barcode;
	return 1;
	_ERROR:
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK ;
	return 0;
	NOT_FOUND:
	return -1;
}

/*********************************************************
*功能: 根据时间查询
*参数: char * btime,char *etime,C_CLASS LINKLIST *list  结果链表
*返回：
*********************************************************/
int SelectSaleFromDate(C_CLASS LINKLIST *plink,char *begin,char *end)
{
	SaleTableInfo saleinfo;
	EXEC SQL WHENEVER SQLERROR GOTO ERROR;
	EXEC SQL DECLARE  sale_cursor CURSOR FOR	
	SELECT * FROM sale 
		WHERE  sale_date>=TO_DATE(:begin,'YYYYMMDDHH24MISS')  
		AND  sale_date<=TO_DATE(:end,'YYYYMMDDHH24MISS')
		ORDER BY staff_id; 
	EXEC SQL OPEN  sale_cursor;
	EXEC SQL WHENEVER NOT FOUND do break;
	while(1)
	{
		EXEC SQL FETCH sale_cursor into:saleinfo;
		link_insert_rear(plink,&saleinfo,sizeof(SaleTableInfo));
	}			
	EXEC SQL CLOSE sale_cursor;
	return 0;  
	ERROR:
	return -1;	
}
/****************************************************************
  功能:
  参数:
  返回：
******************************************************************/
int DepletionCount(char * barcode,int count)
{
	EXEC SQL WHENEVER SQLERROR GOTO UPDATE_ERR;
	EXEC SQL WHENEVER NOT FOUND GOTO NOT_FOUND;
	EXEC SQL UPDATE PRODUCT SET COUNT=count-:count WHERE BAR_CODE=:barcode;
	EXEC SQL COMMIT;
	return 1;
UPDATE_ERR:
	EXEC SQL ROLLBACK RELEASE;
	return -1;
NOT_FOUND:
	return 0;
}

/***************************************************************
*功能: 根据员工ID号查询
*参数: 
*返回：
***************************************************************/
int SelectSaleByStaff(C_CLASS LINKLIST *plink,char * staffid)
{

	SaleTableInfo  saleinfo;
	EXEC SQL WHENEVER SQLERROR GOTO ERROR;
	EXEC SQL DECLARE  sale_cursor2 CURSOR FOR	
	SELECT * FROM sale WHERE  staff_id=:staffid ORDER BY staff_id;

	EXEC SQL OPEN sale_cursor2;
	EXEC SQL WHENEVER NOT FOUND DO BREAK;
	while(1)
	{
		EXEC SQL FETCH sale_cursor2 into:saleinfo;
		link_insert_rear(plink,&saleinfo,sizeof(SaleTableInfo));
	}

	EXEC SQL CLOSE sale_cursor2;
	return 1;  
	ERROR:
	return -1;
}