database.cpp

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

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

void CDatabase::UpdateSprkData(char *ID,char *provider,char *ooperator,char *rebate,char *sumtotal,char *paymoney,char *factmoney,char *intime,char *merchandiseID,char *unitPrice,char *numbers,char *stockname)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_instore_main where id = '%s'", ID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_instore_main set provider = '%s', \
				operator = '%s', rebate = '%s', sumtotal = %s, paymoney = %s, \
				factmoney = %s, intime = '%s' where ID = '%s'", 
				provider,ooperator,rebate,sumtotal,paymoney,factmoney, intime, ID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "update tb_instock_sub set merchandiseID = '%s', \
				unitPrice = %s, numbers = '%s', rebate = '%s', paymoney = %s, \
				stockname = '%s' where instockid = '%s'", 
				merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, ID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_instore_main (ID,provider,operator,rebate,sumtotal,paymoney,factmoney, intime) \
				values ('%s','%s', '%s', %s, %s, %s, %s, '%s')" ,ID,provider,ooperator,rebate,sumtotal,paymoney,factmoney, intime) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "insert into tb_instock_sub (instockid,merchandiseID,unitPrice,numbers,rebate,paymoney,stockname) \
				values ('%s', '%s', %s, %s, %s, %s, '%s')" , ID,merchandiseID,unitPrice,numbers,rebate,paymoney,stockname) ;
			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::EditSprkToCtrl(char *ID, CEdit *m_hEditZk, CEdit *m_hEditYfje, CEdit *m_hEditSpbh, CEdit *m_hEditSl, CEdit *m_hEditSfje, CEdit *m_hEditRkdh, CEdit *m_hEditHj, CEdit *m_hEditGys, CEdit *m_hEditDj, CEdit *m_hEditCzy, CEdit *m_hEditCkmc)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp, m_Rsp1 ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_instore_main 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_hEditRkdh->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("provider") ;
			m_hEditGys->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("operator") ;
			m_hEditCzy->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("rebate") ;
			m_hEditZk->SetWindowText(_bstr_t(v)) ;
			
			v = m_Rsp->GetCollect("sumtotal") ;
			m_hEditHj->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("paymoney") ;
			m_hEditYfje->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("factmoney") ;
			m_hEditSfje->SetWindowText(_bstr_t(v)) ;

			sprintf(m_szSql, "select * from tb_instock_sub where instockid = '%s'", ID) ;
			m_Rsp1 = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			if(!m_Rsp1->GetadoEOF())
			{
				v = m_Rsp1->GetCollect("merchandiseID") ;
				m_hEditSpbh->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("unitPrice") ;
				m_hEditDj->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("numbers") ;
				m_hEditSl->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("stockname") ;
				m_hEditCkmc->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::ListCancelSellToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

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

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

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

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

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

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

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

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

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

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

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

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

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

			v = m_Rsp->GetCollect("intime") ;
			m_hListCtrl->SetItemText(0, 12, _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::UpdateCancelSellData(char *CancelID,char *Customer,char *ooperator,char *rebate,char *sumtotal,char *paymoney,char *factmoney,char *intime,char *merchandiseID,char *unitPrice,char *numbers,char *stockname)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_cancelsell_main where CancelID = '%s'", CancelID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_cancelsell_main set Customer = '%s', \
				operator = '%s', rebate = '%s', sumtotal = %s, paymoney = %s, \
				factmoney = %s, intime = '%s' where CancelID = '%s'", 
				Customer,ooperator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "update tb_cancelsell_sub set merchandiseID = '%s', \
				unitPrice = %s, numbers = '%s', rebate = '%s', paymoney = %s, \
				stockname = '%s' where CancelID = '%s'", 
				merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_cancelsell_main (Customer,operator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) \
				values ('%s', '%s', '%s', %s, %s, %s, '%s','%s')" ,Customer,ooperator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "insert into tb_cancelsell_sub (merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, CancelID) \
				values ('%s', %s, '%s', '%s', %s, '%s', '%s')" ,merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, CancelID) ;
			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::EditCancelSellToCtrl( CDateTimeCtrl* date,char *CancelID, CEdit *m_hEditCancelID, CEdit *m_hEditUnitPrice, CEdit *m_hEditSumTotal, CEdit *m_hEditStockName, CEdit *m_hEditRebate, CEdit *m_hEditPayMoney, CEdit *m_hEditOperator, CEdit *m_hEditNumbers, CEdit *m_hEditMerchandiseID, CEdit *m_hEditFactMoney, CEdit *m_hEditCustomer)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp, m_Rsp1 ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_cancelsell_main where CancelID = '%s'", CancelID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("CancelID") ;
			m_hEditCancelID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Customer") ;
			m_hEditCustomer->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("operator") ;
			m_hEditOperator->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("rebate") ;
			m_hEditRebate->SetWindowText(_bstr_t(v)) ;
			
			v = m_Rsp->GetCollect("sumtotal") ;
			m_hEditSumTotal->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("paymoney") ;
			m_hEditPayMoney->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("factmoney") ;
			m_hEditFactMoney->SetWindowText(_bstr_t(v)) ;

			sprintf(m_szSql, "select * from tb_cancelsell_sub where CancelID = '%s'", CancelID) ;
			m_Rsp1 = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			if(!m_Rsp1->GetadoEOF())
			{
				v = m_Rsp1->GetCollect("merchandiseID") ;
				m_hEditMerchandiseID->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("unitPrice") ;
				m_hEditUnitPrice->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("numbers") ;
				m_hEditNumbers->SetWindowText(_bstr_t(v)) ;
				
				v = m_Rsp1->GetCollect("stockname") ;
				m_hEditStockName->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::ListSellToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_sell_main a, tb_sell_sub b where a.CancelID = b.SellID") ;
	try{
		
			
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

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

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

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

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

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

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

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

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

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

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

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

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

			v = m_Rsp->GetCollect("intime") ;
			m_hListCtrl->SetItemText(0, 12, _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::UpdateSellData(char *CancelID,char *Customer,char *ooperator,char *rebate,char *sumtotal,char *paymoney,char *factmoney,char *intime,char *merchandiseID,char *unitPrice,char *numbers,char *stockname)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_sell_main where CancelID = '%s'", CancelID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_sell_main set Customer = '%s', \
				operator = '%s', rebate = '%s', sumtotal = %s, paymoney = %s, \
				factmoney = %s, intime = '%s' where CancelID = '%s'", 
				Customer,ooperator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "update tb_sell_sub set merchandiseID = '%s', \
				unitPrice = %s, numbers = '%s', rebate = '%s', paymoney = %s, \
				stockname = '%s' where SellID = '%s'", 
				merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_sell_main (Customer,operator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) \
				values ('%s', '%s', '%s', %s, %s, %s, '%s','%s')" ,Customer,ooperator,rebate,sumtotal,paymoney,factmoney, intime, CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;

			sprintf(m_szSql, "insert into tb_sell_sub (merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, SellID) \
				values ('%s', %s, '%s', '%s', %s, '%s', '%s')" ,merchandiseID,unitPrice,numbers,rebate,paymoney,stockname, CancelID) ;
			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::EditSellToCtrl(char *CancelID, CEdit *m_hEditCancelID, CEdit *m_hEditUnitPrice, CEdit *m_hEditSumTotal, CEdit *m_hEditStockName, CEdit *m_hEditRebate, CEdit *m_hEditPayMoney, CEdit *m_hEditOperator, CEdit *m_hEditNumbers, CEdit *m_hEditMerchandiseID, CEdit *m_hEditFactMoney, CEdit *m_hEditCustomer)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp, m_Rsp1 ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_sell_main where CancelID = '%s'", CancelID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("CancelID") ;
			m_hEditCancelID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Customer") ;
			m_hEditCustomer->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("operator") ;
			m_hEditOperator->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("rebate") ;
			m_hEditRebate->SetWindowText(_bstr_t(v)) ;
			
			v = m_Rsp->GetCollect("sumtotal") ;
			m_hEditSumTotal->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("paymoney") ;
			m_hEditPayMoney->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("factmoney") ;
			m_hEditFactMoney->SetWindowText(_bstr_t(v)) ;