酒店点餐系统开发详解（四）
|
酒店点餐系统开发详解（四） ——数据库模块设计 ? 在本系统中每个模块与数据库的一系列查询、插入、删除等操作是通过类CDatabaSEOperation进行的，所有的数据库操作都封装在这个类中。数据库功能的封装增加了模块的独立性和复用性，便于进行二次开发和软件的修改。 本系统采用ADO对象进行数据库操作，故应在stdafx.h中添加“#import?"c:/program?files/common?files/system/ado/msado15.dll"?no_namespace?rename("EOF","_EOF")”（不包括引号），每个模块刚启动并连接数据库的操作如下： BOOL?CCookTerminalApp::InitInstance() { …… AfxEnableControlContainer(); ::CoInitialize(NULL);//初始化com环境 //网络连接初始化 AfxSocketInit(NULL); //数据库连接 ConnectSQLServer(); …… } //连接数据库 void?CCookTerminalApp::ConnectSQLServer() { CString?IP,User,Passwd; int?Port; GetPrivateProfileString("SQLServer","IP","local",IP.GetBuffer(20),20,".//default.ini"); IP.ReleaseBuffer(); GetPrivateProfileString("SQLServer","User","sa",User.GetBuffer(20),".//default.ini"); User.ReleaseBuffer(); GetPrivateProfileString("SQLServer","Passwd","",Passwd.GetBuffer(20),".//default.ini"); Passwd.ReleaseBuffer(); Port?=?GetPrivateProfileInt("SQLServer","Port",1433,".//default.ini"); //数据库连接初始化 m_DbOp.CreateInstance(); if(IP?==?"local") m_DbOp.OpenLocalDatabase("DishesSystem"); else{ CString?str; str.Format("%s,%d",IP,Port); m_DbOp.OpenRemoteDatabase(str,"DishesSystem",Passwd); } } 类CDatabaSEOperation的具体接口及实现如下： 1、创建实例 void?CDatabaSEOperation::CreateInstance() { cnn.CreateInstance(__uuidof(Connection)); rst.CreateInstance(__uuidof(Recordset)); } 2、打开本地数据库 void?CDatabaSEOperation::OpenLocalDatabase(CString?basename) { CString?str; str.Format(_T("Provider=SQLOLEDB.1;Data?Source=(local);Initial?Catalog=%s;Integrated?Security=SSPI"),basename); cnn->ConnectionString?= (_bstr_t)str; try{ cnn->Open(L"",L"",adCmdUnspecified); }catch(_com_error?&e){ CatchError(e); } } 3、打开远程数据库 void?CDatabaSEOperation::OpenRemoteDatabase(CString?source,?CString?basename,?CString?user,?CString?pwd) { CString?str; str.Format(_T("Provider=SQLOLEDB.1;Data?Source=%s;Network?Library=DBMSSOCN;Initial?Catalog=%s;User?ID=%s;Password=%s"),source,basename,user,pwd); cnn->ConnectionString?= (_bstr_t)str; try{ cnn->Open(L"",adCmdUnspecified); }catch(_com_error?&e){ CatchError(e); } } 4、打开记录集 BOOL?CDatabaSEOperation::OpenRecordset(CString?sqlstatement) { CloseRecordset(); try{ rst->CursorLocation?=?adUseClient; rst->Open((_variant_t)sqlstatement,_variant_t((IDispatch?*)cnn,true),adOpenDynamic,adLockPessimistic,adCmdText); }catch(_com_error?&e){ // CatchError(e); return?FALSE; } return?TRUE; } 5、关闭记录集 void?CDatabaSEOperation::CloseRecordset() { if(rst->State) rst->Close(); } 6、返回记录集 _RecordsetPtr?CDatabaSEOperation::GetRecordset(CString?sqlstatement) { _RecordsetPtr?rst; rst.CreateInstance(__uuidof(Recordset)); try{ rst->CursorLocation?=?adUseClient; rst->Open((_variant_t)sqlstatement,adCmdText); }catch(_com_error?&e){ // CatchError(e); return?NULL; } return?rst; } 7、插入新数据 BOOL?CDatabaSEOperation::InsertItem(CString?sqlstatement) { try{ cnn->Execute((_bstr_t)sqlstatement,NULL,adCmdText); }catch(_com_error?&e){ CatchError(e); return?FALSE; } return?TRUE; } 8、修改数据 BOOL?CDatabaSEOperation::UpdateItem(CString?sqlstatement) { try{ cnn->Execute((_bstr_t)sqlstatement,adCmdText); }catch(_com_error?&e){ CatchError(e); return?FALSE; } return?TRUE; } 9、删除数据 BOOL?CDatabaSEOperation::DeleteItem(CString?sqlstatement) { try{ cnn->Execute((_bstr_t)sqlstatement,adCmdText); }catch(_com_error?&e){ CatchError(e); return?FALSE; } return?TRUE; } 10、返回属性值 CString?CDatabaSEOperation::GetAttrValues(CString?attr) { _variant_t?vt; CString?values; if(rst->_EOF?||?rst->BOF) return?""; try{ vt?=?rst->GetCollect((_variant_t)attr); }catch(_com_error?&e){ CatchError(e); return?""; } values?=?(LPCSTR)_bstr_t(vt); return?values; } 11、读取数据库图片数据 char?*CDatabaSEOperation::ReadPictureData(CString?sqlstatement,?CString?attrName) { _RecordsetPtr?temp_rst; char?*buf?=?NULL; temp_rst.CreateInstance(__uuidof(Recordset)); try{ temp_rst->Open((_variant_t)sqlstatement,adCmdText); }catch(_com_error?&e){ CatchError(e); return?NULL; } long?lDataSize; try{ lDataSize?=?temp_rst->GetFields()->GetItem((_variant_t)attrName)->ActualSize; }catch(_com_error?&e){ CatchError(e); return?NULL; } if(lDataSize?>?0) { _variant_t varBLOB; try{ varBLOB?=?temp_rst->GetFields()->GetItem((_variant_t)attrName)->GetChunk(lDataSize); }catch(_com_error?&e){ CatchError(e); return?NULL; } if(varBLOB.vt?==?(VT_ARRAY?|?VT_UI1)) { if(buf?=?new?char[lDataSize+1]) ///重新分配必要的存储空间 { char?*pBuf?=?NULL; SafeArrayAccessData(varBLOB.parray,(void?**)&pBuf); memcpy(buf,pBuf,lDataSize); ///复制数据到缓冲区 SafeArrayUnaccessData?(varBLOB.parray); } } } return?buf; } 12、保存图片到数据库 BOOL?CDatabaSEOperation::SavePicture(CString?sqlstatement,?CString?attrName,?char?*buf,?long?len) { if(buf?==?NULL) return?TRUE; char *pBuf?=?buf; VARIANT varBLOB; SAFEARRAY *psa; ????SAFEARRAYBOUND rgsabound[1]; _RecordsetPtr?temp_rst; temp_rst.CreateInstance(__uuidof(Recordset)); try{ temp_rst->Open((_variant_t)sqlstatement,adCmdText); }catch(_com_error?&e){ CatchError(e); return?FALSE; } if(temp_rst->_EOF) return?FALSE; if(pBuf) {???? rgsabound[0].lLbound?=?0; rgsabound[0].cElements?=?len; psa?=?SafeArrayCreate(VT_UI1,?1,?rgsabound); for?(long?i?=?0;?i?<?(long)len;?i++) SafeArrayPutElement?(psa,?&i,?pBuf++); varBLOB.vt?=?VT_ARRAY?|?VT_UI1; varBLOB.parray?=?psa; temp_rst->GetFields()->GetItem((_variant_t)attrName)->AppendChunk(varBLOB); } temp_rst->Update(); temp_rst->Close(); return?TRUE; } 在实际的操作中，我们将常用的数据库操作整理为存储过程编译到数据库中，以下就是本系统所涉及的具体存储过程： 从厨师表中删除厨师（DeleteCook?@cookid?） 1）本过程先查看点菜表中是否存在相关记录，若存在，则不允许删除厨师，因为顾客还没结账；若不存在，则先删除做菜表中记录，再删除厨师表中记录。 --删除厨师，需先将CookingTable和DishedTable中的相关项删除 CREATE?PROCEDURE?DeleteCook?@cookid?char(8) AS --如果点菜表中含有相关项，说明顾客未结账，则不允许删除 IF?EXISTS?(SELECT?*?FROM?DishedTable?WHERE?cookid=@cookid) ?????RETURN ELSE? IF?EXISTS?(SELECT?*?FROM?CookingTable?WHERE?cookid=@cookid) ?????DELETE?FROM?CookingTable?WHERE?cookid=@cookid DELETE?FROM?CookTable?WHERE?cookid=@cookid 2）从菜品表中删除菜品（DeleteDish?@dishid） 本过程同样先检查点菜表中是否有相关项，若有，则不允许删除菜品；若无，则先删除做菜表中记录，再删除菜品表中记录。 --删除菜品信息，先检查DishedTable和CookingTable中有无相关项 CREATE?PROCEDURE?DeleteDish?@dishid?char(8) AS --如果点菜表中含有相关项，说明顾客未结账，则不允许删除 IF?EXISTS?(SELECT?*?FROM?DishedTable?WHERE?dishid=@dishid) ??????RETURN ELSE? IF?EXISTS?(SELECT?*?FROM?CookingTable?WHERE?dishid=@dishid) ??????DELETE?FROM?CookingTable?WHERE?dishid=@dishid DELETE?FROM?DishesTable?WHERE?dishid=@dishid 3）销售统计（SalesStatistics?@date,@sales） 本过程首先检查表中是否存在日期相同的记录，若存在，则将原记录中的销售额加上新数据；若不存在，则直接插入新数据。 --销售统计函数 CREATE?PROCEDURE?SalesStatistics?@date?char(10),@sales?float AS --如果表中存在该项，则增加销售额 IF?EXISTS?(SELECT?*?FROM?SalesStatisticsTable?WHERE?date=@date) ????UPDATE?SalesStatisticsTable?SET?sales=sales+@sales?WHERE?date=@date ELSE --否则，插入新数据 ????INSERT?INTO?SalesStatisticsTable?(date,sales)?VALUES(@date,@sales) 4）搜索拿手菜表（SearchSpecialty?@cookname） 若cookname为'%%'，则显示所有表中数据，否则，显示某一厨师的拿手菜信息。所返回结果先按厨师名排序，然后再按喜爱度排序。 --搜索拿手菜表 CREATE?PROCEDURE?SearchSpecialty?@cookname?nchar(50) AS IF?@cookname='%%' ???SELECT?DT.dishid?as?菜品编号,LEFT(dishname,LEN(dishname))?as?菜品名称, ???????????CT.cookid?as?厨师编号,LEFT(cookname,LEN(cookname))?as?厨师姓名, ???????????average?as?喜爱度,dishprice?as?菜品单价 ???FROM?SpecialtyTable?ST?INNER?JOIN?DishesTable?DT?ON?ST.dishid=DT.dishid ?????????INNER?JOIN?CookTable?CT?ON?ST.cookid=CT.cookid ?????????ORDER?BY?cookname?ASC,average?DESC??--先按厨师名排序，再按平均分排序 ELSE ???SELECT?DT.dishid?as?菜品编号,dishprice?as?菜品单价 ???FROM?SpecialtyTable?ST?INNER?JOIN?DishesTable?DT?ON?ST.dishid=DT.dishid ?????????INNER?JOIN?CookTable?CT?ON?ST.cookid=CT.cookid ???WHERE?cookname?LIKE?@cookname ?????????ORDER?BY?cookname?ASC,average?DESC???--先按厨师名排序，再按平均分排序 5）点菜（DishedFuction?@dishedtime?,@deskid?@dishid?,@cookid） 本过程先查看表中是否存在相同记录，若存在，则将所点菜品份数amount加1，菜品总价cost自动加上相应菜品单价；若不存在，则直接插入新数据。 --点菜操作 CREATE?PROCEDURE?DishedFuction? ???????@dishedtime?char(19),@deskid?int,@dishid?char(8),@cookid?char(8) AS DECLARE?@price?float SELECT?@price=dishprice?FROM?DishesTable?WHERE?dishid=@dishid IF?EXISTS?(SELECT?*?FROM?DishedTable?WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid) ?????UPDATE?DishedTable?SET?amount=amount+1,cost=cost+@price ????????WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid ELSE ?????INSERT?INTO?DishedTable?(dishedtime,deskid,dishid,cookid,cost,amount)? ??????????????????VALUES(@dishedtime,@deskid,@dishid,@cookid,@price,1) 6）退菜（ReturnDishFuction?@dishedtime,@dishid） 本过程先查看菜品份数amount是否为1，若为1，则直接删除该记录；若不为1，则将菜品份数amount减1，然后菜品总价cost减去相应菜品单价。 --退菜操作 CREATE?PROCEDURE?ReturnDishFuction? ???????@dishedtime?char(19),@dishid?char(8) AS????DECLARE?@price?float SELECT?@price=dishprice?FROM?DishesTable?WHERE?dishid=@dishid --如果点菜表中存在该项则将点菜份数-1，菜品总价递减 IF?EXISTS?(SELECT?*?FROM?DishedTable?WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid) BEGIN??DECLARE?@amount?int ????????SELECT?@amount=amount?FROM?DishedTable?WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid IF?@amount=1 ???DELETE?FROM?DishedTable?WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid ELSE??? ????????UPDATE?DishedTable?SET?amount=amount-1,cost=cost-@price ????????WHERE?dishedtime=@dishedtime?AND?deskid=@deskid?AND?dishid=@dishid END 7）评分（ScoreFuction?@cookid,@dishid?,@score） 本过程先检查表中是否存在相同记录，若存在，则将评分次数freq加1，总分scores加上此次分数，并计算平均分；若不存在，则插入新数据。 --对厨师所做菜品进行评分 CREATE?PROCEDURE?ScoreFuction?@cookid?char(8),@score?int AS --如果表中存在该项，则将评次加1，总分增加，并求出平均分 IF?EXISTS?(SELECT?*?FROM?CookingTable?WHERE?cookid=@cookid?AND?dishid=@dishid) ???BEGIN ??????DECLARE?@f?int,@s?int,@a?int ??????SELECT?@f=freq,@s=scores?FROM?CookingTable?WHERE?cookid=@cookid?AND?dishid=@dishid ??????SET?@f=@f+1 ??????SET?@s=@s+@score ??????SET?@a=@s/@f ??????UPDATE?CookingTable?SET?freq=@f,scores=@s,average=@a ??????????????WHERE?cookid=@cookid?AND?dishid=@dishid ???END ELSE ???INSERT?INTO?CookingTable?(cookid,freq,scores,average)? ????????????????VALUES(@cookid,1,@score,@score) ? 8）搜索点菜表（SearchDished?@dishedtime?,@deskid） 本过程用于向顾客返回其所点菜品信息，并对厨师所做菜品进行评分。 --搜索点菜表 CREATE?PROCEDURE?SearchDished?@dishedtime?char(19),@deskid?int AS SELECT?DdT.dishid?as?菜品编号, DdT.cookid?as?厨师编号, amount?as?点菜份数,cost?as?菜品总价 FROM?DishedTable?DdT?INNER?JOIN?DishesTable?DsT?ON?DdT.dishid=DsT.dishid ??????????????INNER?JOIN?CookTable?CkT?ON?DdT.cookid=CkT.cookid WHERE?dishedtime=@dishedtime?AND?deskid=@deskid 9）获取账单信息（GetAccountInfo?@dishedtime,@deskid） 本过程用于结账时显示顾客所点菜品的详细信息。 --获取账单详细信息 CREATE?PROCEDURE?GetAccountInfo?@dishedtime?char(19),cost?as?总价 FROM?DishedTable?DdT?INNER?JOIN?DishesTable?DsT?ON?DdT.dishid=DsT.dishid ???????INNER?JOIN?CookTable?CkT?ON?DdT.cookid=CkT.cookid WHERE?dishedtime=@dishedtime?AND?deskid=@deskid 10）搜索菜品表（SearchDishes?@dishname） 本过程显示搜索菜品表的结果。 --搜索菜品表 CREATE?PROCEDURE?SearchDishes?@dishname?nchar(50) AS IF?@dishname='%%%%' SELECT?dishid?as?菜品编号,dishprice?as?菜品单价 ????FROM?DishesTable ELSE SELECT?dishid?as?菜品编号,dishprice?as?菜品单价 ???FROM?DishesTable?WHERE?dishname?LIKE?@dishname 源代码下载地址：http://download.csdn.net/source/2406335?标题有误，请见谅... （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |