commidityplanexecute.cpp

基于VC+SQL SERVER环境开发的企业商品销售系统.完整的数据库和原代码

// CommidityPlanExecute.cpp : implementation file
//

#include "stdafx.h"
#include "Sale.h"
#include "CommidityPlanExecute.h"

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

/////////////////////////////////////////////////////////////////////////////
// CCommidityPlanExecute dialog


CCommidityPlanExecute::CCommidityPlanExecute(CWnd* pParent /*=NULL*/)
	: CDialog(CCommidityPlanExecute::IDD, pParent)
{
	//{{AFX_DATA_INIT(CCommidityPlanExecute)
	m_year = _T("");
	m_monthBegin = _T("");
	m_monthEnd = _T("");
	m_commidityID = _T("");
	//}}AFX_DATA_INIT
}


void CCommidityPlanExecute::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CCommidityPlanExecute)
	DDX_Control(pDX, IDC_COMBO4, m_commidityCombo);
	DDX_Control(pDX, IDC_LIST1, m_commidityPlanExecuteList);
	DDX_CBString(pDX, IDC_COMBO1, m_year);
	DDX_CBString(pDX, IDC_COMBO2, m_monthBegin);
	DDX_CBString(pDX, IDC_COMBO3, m_monthEnd);
	DDX_CBString(pDX, IDC_COMBO4, m_commidityID);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CCommidityPlanExecute, CDialog)
	//{{AFX_MSG_MAP(CCommidityPlanExecute)
	ON_BN_CLICKED(IDC_BUTTON1, OnSearch)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CCommidityPlanExecute message handlers

void CCommidityPlanExecute::OnSearch() 
{
	UpdateData(true);
	CString strSQL;
	HRESULT hTRes;
	COleDateTime dtBegin;
	COleDateTime dtEnd;
	if(m_year==""||m_monthBegin==""||m_monthEnd==""||m_commidityID=="")
	{
		AfxMessageBox("必须填写年度、月份、产品编号!");
		return;
	}
	dtBegin.SetDate(atoi(m_year),atoi(m_monthBegin),1);
	dtEnd.SetDate(atoi(m_year),atoi(m_monthEnd),1);
	strSQL="select * from commiditySalePlanView where commidityID='";
	strSQL=strSQL+m_commidityID+"'";
	strSQL=strSQL+" and planYear='";
	strSQL=strSQL+m_year+"'";
	_RecordsetPtr m_pCommiditySalePlanRecordset;
	float totalPlan=0;
	float totalRatio=0;
	totalPlan=0;
	totalRatio=0;
	hTRes = m_pCommiditySalePlanRecordset.CreateInstance(_T("ADODB.Recordset"));
	if (SUCCEEDED(hTRes))
	{
		hTRes = m_pCommiditySalePlanRecordset->Open((LPTSTR)strSQL.GetBuffer(130),
		((CSaleApp*)AfxGetApp())->m_pConn.GetInterfacePtr(),
				adOpenDynamic,adLockPessimistic,adCmdText);
		if(SUCCEEDED(hTRes))
		{
			TRACE(_T("连接成功!\n"));
			
			int monthBegin,monthEnd;
			m_commidityPlanExecuteList.DeleteAllItems();
			
			monthBegin=atoi(m_monthBegin);
			monthEnd=atoi(m_monthEnd);
			int j=0;
			if(!(m_pCommiditySalePlanRecordset->adoEOF))
			{
				m_commidityPlanExecuteList.InsertItem(j,(LPCTSTR)(_bstr_t)(m_pCommiditySalePlanRecordset->GetCollect("commidityID")));
				m_commidityPlanExecuteList.SetItemText(j,1,(LPCTSTR)(_bstr_t)(m_pCommiditySalePlanRecordset->GetCollect("commodityName")));
			
				while(!(m_pCommiditySalePlanRecordset->adoEOF))
				{			
					_variant_t index;
					for(int i=monthBegin;i<monthEnd;i++)
					{					
						index=(long)(i*2+4-2);
						totalPlan=totalPlan+atof((_bstr_t)m_pCommiditySalePlanRecordset->GetCollect(index));
						index=(long)(i*2+4-1);
						totalRatio=totalRatio+atof((_bstr_t)m_pCommiditySalePlanRecordset->GetCollect(index));
					}
					m_pCommiditySalePlanRecordset->MoveNext();	
				}
				CString str;
				str.Format("%f",totalPlan);
				m_commidityPlanExecuteList.SetItemText(j,2,(LPCTSTR)str);
				str.Format("%f",totalRatio);
				m_commidityPlanExecuteList.SetItemText(j,3,(LPCTSTR)str);
			}
		}
	}
	m_pCommiditySalePlanRecordset->Close();

	strSQL="select * from saleAllView where commidityID='";
	strSQL=strSQL+m_commidityID+"'";
	strSQL=strSQL+" and formDate>='";
	strSQL=strSQL+m_year+"-";
	strSQL=strSQL+m_monthBegin+"-1' and formDate<='";
	strSQL=strSQL+m_year+"-";
	strSQL=strSQL+m_monthEnd+"-1'";
	_RecordsetPtr m_pConsignmentFormCommidityRecordset;
	float total;
	total=0;
	hTRes = m_pConsignmentFormCommidityRecordset.CreateInstance(_T("ADODB.Recordset"));
	if (SUCCEEDED(hTRes))
	{
		hTRes = m_pConsignmentFormCommidityRecordset->Open((LPTSTR)strSQL.GetBuffer(130),
		((CSaleApp*)AfxGetApp())->m_pConn.GetInterfacePtr(),
				adOpenDynamic,adLockPessimistic,adCmdText);
		if(SUCCEEDED(hTRes))
		{
			TRACE(_T("连接成功!\n"));
			if(!(m_pConsignmentFormCommidityRecordset->adoEOF))
			{
				while(!(m_pConsignmentFormCommidityRecordset->adoEOF))
				{
					total=total+(atoi(_bstr_t(m_pConsignmentFormCommidityRecordset->GetCollect("num"))))*
						(atof((_bstr_t)(m_pConsignmentFormCommidityRecordset->GetCollect("price"))));
					m_pConsignmentFormCommidityRecordset->MoveNext();					
				}
			}
				CString str;
				str.Format("%f",total);
				m_commidityPlanExecuteList.SetItemText(0,4,(LPCTSTR)str);
				str.Format("%f",(total-totalPlan));
				m_commidityPlanExecuteList.SetItemText(0,5,(LPCTSTR)str);
				str.Format("%f",(total-totalRatio));
				m_commidityPlanExecuteList.SetItemText(0,6,(LPCTSTR)str);
				str.Format("%f",(total-totalPlan)/totalPlan);
				m_commidityPlanExecuteList.SetItemText(0,7,(LPCTSTR)str);
				str.Format("%f",(total-totalRatio)/totalRatio);
				m_commidityPlanExecuteList.SetItemText(0,8,(LPCTSTR)str);

			
		}
	}
	UpdateData(false);	
}

BOOL CCommidityPlanExecute::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	DWORD style;
	style=m_commidityPlanExecuteList.GetExStyle();
	style=(style|LVS_EX_GRIDLINES|LVS_EX_FULLROWSELECT)&(~LVS_EX_CHECKBOXES) ;
	m_commidityPlanExecuteList.SetExtendedStyle(style);

	m_commidityPlanExecuteList.InsertColumn(0,"产品编号",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(1,"产品名称",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(2,"计划金额",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(3,"销售定额",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(4,"销售额",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(5,"较计划金额",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(6,"较销售定额",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(7,"计划金额完成率",LVCFMT_LEFT,100);
	m_commidityPlanExecuteList.InsertColumn(8,"销售定额完成率",LVCFMT_LEFT,100);

	CString strSQL;
	HRESULT hTRes;	

	strSQL="select * from commodity";
	_RecordsetPtr m_pCommidityRecordset;
	hTRes = m_pCommidityRecordset.CreateInstance(_T("ADODB.Recordset"));
	if (SUCCEEDED(hTRes))
	{
		hTRes = m_pCommidityRecordset->Open((LPTSTR)strSQL.GetBuffer(130),
		((CSaleApp*)AfxGetApp())->m_pConn.GetInterfacePtr(),
				adOpenDynamic,adLockPessimistic,adCmdText);
		if(SUCCEEDED(hTRes))
		{
			TRACE(_T("连接成功!\n"));
			while(!(m_pCommidityRecordset->adoEOF))
			{		
				m_commidityCombo.AddString((LPCTSTR)(_bstr_t)(m_pCommidityRecordset->GetCollect("commodityID")));
				m_pCommidityRecordset->MoveNext();
			}
		}
	}
	m_pCommidityRecordset->Close();
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}