proinstore.cpp

用VC++和SQL做的仓库管理系统

// ProInStore.cpp: implementation of the CProInStore class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "Stock.h"
#include "ProInStore.h"
#include "ADOConn.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CProInStore::CProInStore()
{
	SpId = 0;
	Pid = 0;
	Pprice = 0;
	Pnum = 0;
	MakeDate = "";
	Sid = 0;
}

CProInStore::~CProInStore()
{

}
int CProInStore::GetSpId()
{
	return SpId;
}

void CProInStore::SetSpId(int iSpId)
{
	SpId = iSpId;
}

int CProInStore::GetPid()
{
	return Pid;
}

void CProInStore::SetPid(int iPid)
{
	Pid = iPid;
}

float CProInStore::GetPprice()
{
	return Pprice;
}

void CProInStore::SetPprice(float fPprice)
{
	Pprice = fPprice;
}

int CProInStore::GetPnum()
{
	return Pnum;
}

void CProInStore::SetPnum(int iPnum)
{
	Pnum = iPnum;
}

CString CProInStore::GetMakeDate()
{
	return MakeDate;
}

void CProInStore::SetMakeDate(CString cMakeDate)
{
	MakeDate = cMakeDate;
}

int CProInStore::GetSid()
{
	return Sid;
}

void CProInStore::SetSid(int iSid)
{
	Sid = iSid;
}

//数据库操作
int CProInStore::SumPro(CString cPid) //计算指定产品的库存总数量
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT Sum(Pnum) AS SumNum FROM ProInStore WHERE Pid=" + cPid;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		return 0;
	else
	{
		return atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("SumNum"));
	}
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

int CProInStore::HaveProduct(CString cPid)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ProInStore WHERE Pid=" + cPid;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

int CProInStore::HaveStore(CString cSid)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ProInStore WHERE Sid=" + cSid;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

int CProInStore::HaveData(CString cPid, CString cPprice, CString cMakeDate, CString cSid)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ProInStore WHERE Pid=" + cPid + " AND Pprice=" 
		+ cPprice + " AND MakeDate='" + cMakeDate + "' AND Sid=" + cSid;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}	 

void CProInStore::sql_insert()
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置INSERT语句
	CString strPid;
	strPid.Format("%d", Pid);
	CString strPprice;
	strPprice.Format("%f", Pprice);
	CString strPnum;
	strPnum.Format("%d", Pnum);
	CString strSid;
	strSid.Format("%d", Sid);

	_bstr_t vSQL;
	vSQL = "INSERT INTO ProInStore (Pid, Pprice, Pnum, MakeDate, Sid) VALUES(" 
		+ strPid + "," + strPprice + "," + strPnum + ",'" 
		+ MakeDate + "'," + strSid + ")";
	
	//执行INSERT语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

void CProInStore::sql_updateDiff(CString cPid, CString cPprice, CString cMakeDate, CString cSid)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置UPDATE语句
	CString strPnum;
	strPnum.Format("%d", Pnum);

	_bstr_t vSQL;
	vSQL = "UPDATE ProInStore SET Pnum = Pnum + " + strPnum + " WHERE Pid=" + cPid + " AND Pprice=" 
		+ cPprice + " AND MakeDate='" + cMakeDate + "' AND Sid=" + cSid;
	
	//执行UPDATE语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

void CProInStore::sql_updatePnum(CString cSpId)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置UPDATE语句
	CString strPnum;
	strPnum.Format("%d", Pnum);

	_bstr_t vSQL;
	vSQL = "UPDATE ProInStore SET Pnum=" + strPnum + " WHERE SpId=" + cSpId;
	
	//执行UPDATE语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}

void CProInStore::sql_delete(CString cSpId)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置DELETE语句
	_bstr_t vSQL;
	vSQL = "DELETE FROM ProInStore WHERE SpId=" + cSpId;
	
	//执行INSERT语句
	m_AdoConn.ExecuteSQL(vSQL);	
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}
	
	//读取所有字段值
	void GetData(CString cTid);

//读取所有字段值
void CProInStore::GetData(CString cSpId)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//设置SELECT语句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ProInStore WHERE SpId=" + cSpId;
	//执行SELETE语句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CProInStore();
	else
	{
		SpId = atoi(cSpId);
		Pid = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Pid"));
		Pprice = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Pprice"));
		Pnum = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Pnum"));
		MakeDate = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("MakeDate");
		Sid = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Sid"));
	}
	//断开与数据库的连接
	m_AdoConn.ExitConnect();
}