database.cpp

主要功能 商品销售管理系统属于小型的进销存管理系统

// Database.cpp: implementation of the CDatabase class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "merchandisesell.h"
#include "Database.h"

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

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

CDatabase::CDatabase()
{

}

CDatabase::~CDatabase()
{

}

int CDatabase::InitData()
{
	char m_szConnect[512];
	char m_szTmp[1024]="" ;
	char m_szHost[20], m_szUser[20], m_szPwd[20], m_szDef[20] ;
	GetPrivateProfileString("数据库", "主机名", NULL, m_szHost, sizeof(m_szHost), IniFile) ;
	GetPrivateProfileString("数据库", "用户名", NULL, m_szUser, sizeof(m_szUser), IniFile) ;
	GetPrivateProfileString("数据库", "密码", NULL, m_szPwd, sizeof(m_szPwd), IniFile) ;
	GetPrivateProfileString("数据库", "默认库", NULL, m_szDef, sizeof(m_szDef), IniFile) ;
	try
	{
		//连接XdData
		HRESULT hr = m_Connection.CreateInstance(__uuidof(Connection));
		sprintf(m_szConnect,"provider = sqloledb;server=%s;database=%s;", m_szHost, m_szDef);
		hr=m_Connection->Open(_bstr_t(m_szConnect),_bstr_t(m_szUser),_bstr_t(m_szPwd),-1);
		sprintf(m_szTmp, "数据库连接成功!");
		//连接XdData
	}
	catch(_com_error & e) 
	{
		sprintf(m_szTmp, "数据库打开失败,错误原因：%s\n",LPCTSTR(e.Description()));
		return 0 ;
	}
	return 1 ;
}

void CDatabase::UnInitData()
{
	m_Connection->Close() ;
	m_Connection.Release() ;
}

_RecordsetPtr CDatabase::Sql_Query(char *m_szSql)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}

	return m_Rsp ;
}

int CDatabase::IsVerifyUser(char *m_szUser, char *m_szPwd, char *m_szLevel)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_operator where name = '%s' and password = '%s'", m_szUser, m_szPwd) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("level") ;
			if(atoi(_bstr_t(v)) == 0)
			{//系统
				strcpy(m_szLevel, "系统管理员") ;
			}
			else
			{//普通
				strcpy(m_szLevel, "普通管理员") ;
			}
			return 1 ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
		return -1 ;
	}
	return 0 ;
}

void CDatabase::ListOpToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512], m_szName[30+1], m_szPwd[30+1], m_szLevel[30+1] ;
	sprintf(m_szSql, "select * from tb_operator") ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("name") ;
			strcpy(m_szName, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("password") ;
			strcpy(m_szPwd, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("level") ;
			if(atoi(_bstr_t(v)) == 0)
			{//系统
				strcpy(m_szLevel, "系统管理员") ;
			}
			else
			{//普通
				strcpy(m_szLevel, "普通管理员") ;
			}
			
			m_hListCtrl->InsertItem(0, m_szName) ;
			m_hListCtrl->SetItemText(0, 0, m_szName) ;
			m_hListCtrl->SetItemText(0, 1, m_szPwd) ;
			m_hListCtrl->SetItemText(0, 2, m_szLevel) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditOpToCtrl(char *m_szName, CEdit *m_hEditName, CEdit *m_hEditPwd, CComboBox *m_hCmbLevel)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512], m_szPwd[30+1], m_szLevel[30+1] ;
	sprintf(m_szSql, "select * from tb_operator where name = '%s'", m_szName) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("password") ;
			strcpy(m_szPwd, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("level") ;
			if(atoi(_bstr_t(v)) == 0)
			{//系统
				m_hCmbLevel->SetCurSel(0) ;
				strcpy(m_szLevel, "系统管理员") ;
			}
			else
			{//普通
				strcpy(m_szLevel, "普通管理员") ;
				m_hCmbLevel->SetCurSel(1) ;
			}
			
			m_hEditName->SetWindowText(m_szName) ;
			m_hEditPwd->SetWindowText(m_szPwd) ;
			m_hCmbLevel->SetWindowText(m_szLevel) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateOpData(char *m_szName, char *m_szPwd, int m_nLevel)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_operator where name = '%s'", m_szName) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_operator set password = '%s', level = %d where name = '%s'", m_szPwd, m_nLevel, m_szName) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_operator (password, level, name) values ('%s', %d, '%s')", m_szPwd, m_nLevel, m_szName) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::DeleteDataWhere(int m_nIndex, char *Fond)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	switch(m_nIndex)
	{
	case OPT://tb_operator
		{
			sprintf(m_szSql, "delete from tb_operator where name = '%s'", Fond) ;
			break ;
		}
	case SPXXT://tb_merchandiseinfo by ID
		{
			sprintf(m_szSql, "delete from tb_merchandiseinfo where id = '%s'", Fond) ;
			break ;
		}
	case GYSXX:
		{
			sprintf(m_szSql, "delete from tb_providerinfo where provider = '%s'", Fond) ;
			break ;
		}
	case KHXX:
		{
			sprintf(m_szSql, "delete from tb_customerinfo where name = '%s'", Fond) ;
			break ;
		}
	case KCGL:
		{
			sprintf(m_szSql, "delete from tb_merchandisestorage where merchandiseID = '%s'", Fond) ;
			break ;
		}
	case SPRK:
		{
			sprintf(m_szSql, "delete from tb_instore_main where ID = '%s'", Fond) ;
			break ;
		}
	case RKTH:
		{
			sprintf(m_szSql, "delete from tb_cancelinstock_main where CancelID = '%s'", Fond) ;
			break ;
		}
	case XSTH:
		{
			sprintf(m_szSql, "delete from tb_cancelsell_main where CancelID = '%s'", Fond) ;
			break ;
		}
	case SPXS:
		{
			sprintf(m_szSql, "delete from tb_sell_main where CancelID = '%s'", Fond) ;
			break ;
		}
	case GYSJK:
		{
			sprintf(m_szSql, "delete from tb_providerpay where PayID = '%s'", Fond) ;
			break ;
		}
	case KHJK:
		{
			sprintf(m_szSql, "delete from tb_customerpay where PayID = '%s'", Fond) ;
			break ;
		}
	}
	try{
		if(m_nIndex==SPRK)
		{
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			sprintf(m_szSql, "delete from tb_instock_sub where instockid = '%s'", Fond) ;
		}
		else
		if(m_nIndex==RKTH)
		{
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			sprintf(m_szSql, "delete from tb_cancelinstock_sub where CancelID = '%s'", Fond) ;
		}
		else
		if(m_nIndex==XSTH)
		{
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			sprintf(m_szSql, "delete from tb_cancelsell_sub where CancelID = '%s'", Fond) ;
		}
		else
		if(m_nIndex==SPXS)
		{
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			sprintf(m_szSql, "delete from tb_sell_sub where CancelID = '%s'", Fond) ;
		}
		m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListSpxxToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_merchandiseinfo") ;
	try{
		
			
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("ID") ;
			m_hListCtrl->SetItemText(0, 0, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("name") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("spec") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("shortmane") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("defaultprice") ;
			m_hListCtrl->SetItemText(0, 4, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("manufacturer") ;
			m_hListCtrl->SetItemText(0, 5, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hListCtrl->SetItemText(0, 6, _bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditSpxxToCtrl(char *ID, CEdit *m_hEditZjm, CEdit *m_hEditName,	CEdit *m_hEditMrjg, CEdit *m_hEditGg, CEdit *m_hEditCj,	CEdit *m_hEditBz, CEdit *m_hEditSpbh)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_merchandiseinfo where ID = '%s'", ID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("ID") ;
			m_hEditSpbh->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("name") ;
			m_hEditName->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("spec") ;
			m_hEditGg->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("shortmane") ;
			m_hEditZjm->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("defaultprice") ;
			m_hEditMrjg->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("manufacturer") ;
			m_hEditCj->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hEditBz->SetWindowText(_bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateSpxxData(char *m_szZjm, char *m_szName, char *m_szID, char *m_szGg, char *m_szMrjg, char *m_szCj, char *m_szBz)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_merchandiseinfo where id = '%s'", m_szID) ;