📄 caccess.cpp
字号:
#include "stdafx.h"
#include "CAccess.h"
//缺省构造函数
//##ModelId=46B60939036B
CAccess::CAccess()
{
}
//构造函数
//参数：
//lpszAccessFilePathName，数据库文件的路径名
//##ModelId=46B60939037A
CAccess::CAccess(CHAR *lpszAccessFilePathName)
{
m_bConnectionOK = FALSE;
if( lpszAccessFilePathName )
{
m_strAccessFilePathName = lpszAccessFilePathName;
// Create DSN config.
string strDSNConfig;
strDSNConfig = "DSN=StDB;DBQ=";
strDSNConfig.append( m_strAccessFilePathName );
strDSNConfig.append( ";PWD=uestcgrsm;UID=admin;");
if ( SQLConfigDataSource(NULL, ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)\0", strDSNConfig.c_str() ) )
{
//创建数据源成功
CString strSql;
strSql.Format("DSN=%s;;","StDB" );
// 创建数据库
TRY
{
m_MfcDatabase.OpenEx( strSql , CDatabase::noOdbcDialog);
m_bConnectionOK = TRUE;
}
CATCH( CDBException, e )
{
CHAR szErrMsg[1024];
ZeroMemory( szErrMsg, 1024 );
e -> GetErrorMessage(szErrMsg,1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
}
else
{
MessageBox( NULL , "Config ODBC Database false!" , "错误" , NULL);
}
}
}
//析构函数
//##ModelId=46B60939038A
CAccess::~CAccess()
{}
//数据库的初始化函数。
//##ModelId=46B6093903B9
BOOL CAccess::Initialize()
{
//返回是否初始化成功
return m_bConnectionOK;
}
//数据库的结束函数。
//##ModelId=46B6093903C8
BOOL CAccess::Finalize()
{
if( m_MfcDatabase.IsOpen() )
{
m_MfcDatabase.Close();
}
return TRUE;
}
//得到销售信息
//##ModelId=46B6093903D8
BOOL CAccess::GetData(vector<LPORDERINFO>& vOut)
{
BOOL bResult = FALSE;
LPORDERINFO lpBuffer;
// Create SQL
CString strSQL;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
TRY
{
//得到整个表
recordset.m_strFilter ="";
strSQL.Format ("select T_Order.*,T_Product.*,T_Order.Memo as Memo from T_Order,T_Product where T_Order.ProductID=T_Product.ProductID order by OrderID desc");
recordset.Open( CRecordset::dynaset, strSQL);
if ( recordset.IsBOF() && recordset.IsEOF())
{
//空表
}
else
{
recordset.MoveFirst();
CString strCustomerName , strDate;
CString strMemo , strProductName , strType , strUnit , strUnitPrice , strDiscount;
while( !recordset.IsEOF() )
{
CDBVariant varValue[6];
recordset.GetFieldValue( "OrderID" , varValue[0] );
recordset.GetFieldValue( "CustomerName" , strCustomerName );
recordset.GetFieldValue( "ProductID" , varValue[1]);
recordset.GetFieldValue( "Quantity" , varValue[2]);
recordset.GetFieldValue( "UnitPrice" , strUnitPrice);
recordset.GetFieldValue( "OrderDate" , strDate);
recordset.GetFieldValue( "Discount" , strDiscount);
recordset.GetFieldValue( "Memo" , strMemo);
recordset.GetFieldValue( "ProductName" , strProductName);
recordset.GetFieldValue( "Unit" , strUnit);
recordset.GetFieldValue( "Type" , strType);
//把数据保存到结构体中
lpBuffer = new ORDERINFO;
if( lpBuffer )
{
lpBuffer -> dwOrderID = varValue[0].m_iVal;
lpBuffer -> strCustomerName = strCustomerName;
lpBuffer -> dwProductID = varValue[1].m_iVal;
lpBuffer -> dwQuantity = varValue[2].m_iVal;
lpBuffer -> fPrice = atof(strUnitPrice);
lpBuffer -> strDate = strDate;
lpBuffer -> fDiscount = atof(strDiscount);
lpBuffer -> strMemo = strMemo;
lpBuffer -> strProductName = strProductName;
lpBuffer -> strUnit = strUnit;
lpBuffer -> strType = strType;
vOut.push_back( lpBuffer );
}
recordset.MoveNext();
}
}
bResult = TRUE;
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
ZeroMemory( szErrMsg, 1024 );
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
if ( recordset.IsOpen () )
{
recordset.Close ();
}
return bResult;
}
//得到进货信息
//##ModelId=46B6093A0001
BOOL CAccess::GetData(vector<LPSTOCKINFO>& vOut)
{
BOOL bResult = FALSE;
LPSTOCKINFO lpBuffer;
// Create SQL
CString strSQL;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
TRY
{
//得到整个表
recordset.m_strFilter ="";
strSQL.Format ("select T_Stock.*,T_Product.*,T_Stock.Memo as Memo from T_Stock,T_Product where T_Stock.ProductID=T_Product.ProductID order by StockID desc");
recordset.Open( CRecordset::dynaset, strSQL);
if ( recordset.IsBOF() && recordset.IsEOF())
{
//空表
}
else
{
recordset.MoveFirst();
CString strDate;
CString strMemo , strProductName , strType , strUnit , strUnitPrice;
while( !recordset.IsEOF() )
{
CDBVariant varValue[4];
recordset.GetFieldValue( "StockID" , varValue[0] );
recordset.GetFieldValue( "ProductID" , varValue[1]);
recordset.GetFieldValue( "Quantity" , varValue[2]);
recordset.GetFieldValue( "UnitPrice" , strUnitPrice);
recordset.GetFieldValue( "StockDate" , strDate);
recordset.GetFieldValue( "Memo" , strMemo);
recordset.GetFieldValue( "ProductName" , strProductName);
recordset.GetFieldValue( "Unit" , strUnit);
recordset.GetFieldValue( "Type" , strType);
//把数据保存到结构体中
lpBuffer = new STOCKINFO;
if( lpBuffer )
{
lpBuffer -> dwStockID = varValue[0].m_iVal;
lpBuffer -> dwProductID = varValue[1].m_iVal;
lpBuffer -> dwQuantity = varValue[2].m_iVal;
lpBuffer -> fPrice = atof(strUnitPrice);
lpBuffer -> strDate = strDate;
lpBuffer -> strMemo = strMemo;
lpBuffer -> strProductName = strProductName;
lpBuffer -> strUnit = strUnit;
lpBuffer -> strType = strType;
vOut.push_back( lpBuffer );
}
recordset.MoveNext();
}
}
bResult = TRUE;
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
ZeroMemory( szErrMsg, 1024 );
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
if ( recordset.IsOpen () )
{
recordset.Close ();
}
return bResult;
}
//得到商品信息
//##ModelId=46B6093A0010
BOOL CAccess::GetData(vector<LPPRODUCTINFO>& vOut)
{
BOOL bResult = FALSE;
LPPRODUCTINFO lpBuffer;
// Create SQL
CString strSQL;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
TRY
{
//得到整个表
recordset.m_strFilter ="";
strSQL.Format ("select * from T_Product order by ProductID desc");
recordset.Open( CRecordset::dynaset, strSQL);
if ( recordset.IsBOF() && recordset.IsEOF())
{
//空表
}
else
{
recordset.MoveFirst();
CString strDate;
CString strMemo , strProductName , strType , strUnit;
while( !recordset.IsEOF() )
{
CDBVariant varValue[1];
recordset.GetFieldValue( "ProductID" , varValue[0]);
recordset.GetFieldValue( "ProductName" , strProductName);
recordset.GetFieldValue( "Unit" , strUnit);
recordset.GetFieldValue( "Type" , strType);
recordset.GetFieldValue( "Memo" , strMemo);
//把数据保存到结构体中
lpBuffer = new PRODUCTINFO;
if( lpBuffer )
{
lpBuffer -> dwProductID = varValue[0].m_iVal;
lpBuffer -> strMemo = strMemo;
lpBuffer -> strProductName = strProductName;
lpBuffer -> strUnit = strUnit;
lpBuffer -> strType = strType;
vOut.push_back( lpBuffer );
}
recordset.MoveNext();
}
}
bResult = TRUE;
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
ZeroMemory( szErrMsg, 1024 );
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
if ( recordset.IsOpen () )
{
recordset.Close ();
}
return bResult;
}
//插入销售信息
//##ModelId=46B6093A002E
DWORD CAccess::InsertData(LPORDERINFO lpData)
{
DWORD dwResult = 0;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
// Create SQL
CString strSQL;
if( lpData )
{
TRY
{
strSQL.Format( "Insert into T_Order (CustomerName,ProductID,Quantity,UnitPrice,OrderDate,Discount,Memo) VALUES ('%s',%d,%d,%f,'%s',%f,'%s') " ,
lpData -> strCustomerName.c_str(),
lpData -> dwProductID,
lpData -> dwQuantity,
lpData -> fPrice,
lpData -> strDate.c_str(),
lpData -> fDiscount,
lpData -> strMemo.c_str());
m_MfcDatabase.ExecuteSQL( strSQL );
// Read the ID of new insert data
recordset.m_strFilter ="";
strSQL.Format( "select top 1 * from T_Order order by OrderID desc" );
recordset.Open( CRecordset::dynaset, strSQL);
if ( !(recordset.IsEOF() && recordset.IsBOF()) )
{
recordset.MoveFirst();
CDBVariant varValue[1];
recordset.GetFieldValue( "OrderID", varValue[0] );//JourneyNotice ID
dwResult = varValue[0].m_iVal;
lpData -> dwOrderID = dwResult;
}
if(recordset.IsOpen())
recordset.Close();
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
}
return dwResult;
}
//插入进货信息
//##ModelId=46B6093A004E
DWORD CAccess::InsertData(LPSTOCKINFO lpData)
{
DWORD dwResult = 0;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
// Create SQL
CString strSQL;
if( lpData )
{
TRY
{
strSQL.Format( "Insert into T_Stock (ProductID,Quantity,UnitPrice,StockDate,Memo) VALUES (%d,%d,%f,'%s','%s') " ,
lpData -> dwProductID,
lpData -> dwQuantity,
lpData -> fPrice,
lpData -> strDate.c_str(),
lpData -> strMemo.c_str());
m_MfcDatabase.ExecuteSQL( strSQL );
// Read the ID of new insert data
recordset.m_strFilter ="";
strSQL.Format( "select top 1 * from T_Stock order by StockID desc" );
recordset.Open( CRecordset::dynaset, strSQL);
if ( !(recordset.IsEOF() && recordset.IsBOF()) )
{
recordset.MoveFirst();
CDBVariant varValue[1];
recordset.GetFieldValue( "StockID", varValue[0] );//JourneyNotice ID
dwResult = varValue[0].m_iVal;
lpData -> dwStockID = dwResult;
}
if(recordset.IsOpen())
recordset.Close();
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
}
return dwResult;
}
//插入商品信息
//##ModelId=46B6093A006D
DWORD CAccess::InsertData(LPPRODUCTINFO lpData)
{
DWORD dwResult = 0;
CRecordset recordset;
recordset.m_pDatabase = &m_MfcDatabase;
// Create SQL
CString strSQL;
if( lpData )
{
TRY
{
strSQL.Format( "Insert into T_Product (ProductName,Unit,Type,Memo) VALUES ('%s','%s','%s','%s') " ,
lpData -> strProductName.c_str(),
lpData -> strUnit.c_str(),
lpData -> strType.c_str(),
lpData -> strMemo.c_str());
m_MfcDatabase.ExecuteSQL( strSQL );
// Read the ID of new insert data
recordset.m_strFilter ="";
strSQL.Format( "select top 1 * from T_Product order by ProductID desc" );
recordset.Open( CRecordset::dynaset, strSQL);
if ( !(recordset.IsEOF() && recordset.IsBOF()) )
{
recordset.MoveFirst();
CDBVariant varValue[1];
recordset.GetFieldValue( "ProductID", varValue[0] );//JourneyNotice ID
dwResult = varValue[0].m_iVal;
lpData -> dwProductID = dwResult;
}
if(recordset.IsOpen())
recordset.Close();
}
CATCH(CDBException, e)
{
CHAR szErrMsg[1024];
e -> GetErrorMessage(szErrMsg , 1024);
MessageBox( NULL , szErrMsg , "错误" , NULL);
}
END_CATCH
}
return dwResult;
}
//修改销售信息
//##ModelId=46B6093A00BB
DWORD CAccess::UpdateData(LPORDERINFO lpData)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -