billmanager.cpp

来自「本人工作中的一个软件开发实例。里面包含了数据库」· C++ 代码 · 共 522 行

// BillManager.cpp : implementation file
//

#include "stdafx.h"
#include "OIL.h"
#include "BillAdd.h"
#include "BillManager.h"
#include "excel9.h"

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

extern  COILApp theApp;
/////////////////////////////////////////////////////////////////////////////
// CBillManager dialog


CBillManager::CBillManager(CWnd* pParent /*=NULL*/)
	: CDialog(CBillManager::IDD, pParent)
{
	//{{AFX_DATA_INIT(CBillManager)
	m_strCustomer = _T("");
	m_nCondition = -1;
	//}}AFX_DATA_INIT
}


void CBillManager::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CBillManager)
	DDX_Control(pDX, IDC_VALID, m_ButtonCondition);
	DDX_Control(pDX, IDC_DATETIMEPICKER_START, m_dtcStart);
	DDX_Control(pDX, IDC_DATETIMEPICKER_END, m_dtcEnd);
	DDX_Control(pDX, IDC_LIST_BILL, m_listBill);
	DDX_CBString(pDX, IDC_COMBO_CUSTOMER, m_strCustomer);
	DDX_Radio(pDX, IDC_VALID, m_nCondition);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CBillManager, CDialog)
	//{{AFX_MSG_MAP(CBillManager)
	ON_BN_CLICKED(IDC_BILL_ADD, OnBillAdd)
	ON_BN_CLICKED(IDC_BILL_DEL, OnBillDel)
	ON_BN_CLICKED(IDC_BILL_EDIT, OnBillEdit)
	ON_BN_CLICKED(IDC_BILL_DELAY, OnBillDelay)
	ON_BN_CLICKED(IDC_BILL_UNUSE, OnBillUnuse)
	ON_BN_CLICKED(IDC_BILL_PRINT, OnBillPrint)
	ON_NOTIFY(NM_DBLCLK, IDC_LIST_BILL, OnDblclkListBill)
	ON_WM_CLOSE()
	ON_NOTIFY(DTN_DATETIMECHANGE, IDC_DATETIMEPICKER_START, OnDatetimechangeDatetimepickerStart)
	ON_NOTIFY(DTN_DATETIMECHANGE, IDC_DATETIMEPICKER_END, OnDatetimechangeDatetimepickerEnd)
	ON_CBN_EDITCHANGE(IDC_COMBO_CUSTOMER, OnEditchangeComboCustomer)
	ON_CBN_EDITUPDATE(IDC_COMBO_CUSTOMER, OnEditupdateComboCustomer)
	ON_CBN_SELCHANGE(IDC_COMBO_CUSTOMER, OnSelchangeComboCustomer)
	ON_CBN_SELENDOK(IDC_COMBO_CUSTOMER, OnSelendokComboCustomer)
	ON_CBN_DBLCLK(IDC_COMBO_CUSTOMER, OnDblclkComboCustomer)
	ON_BN_CLICKED(IDC_RADIO_VALID, OnRadio)
	ON_BN_CLICKED(IDC_VALID, OnRadio)
	ON_BN_CLICKED(IDC_UNVALID, OnRadio)
	ON_BN_CLICKED(IDC_ALL, OnRadio)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CBillManager message handlers

//新开提油单
void CBillManager::OnBillAdd() 
{
	// TODO: Add your control notification handler code here
	CBillAdd  aCBillAdd;
	aCBillAdd.m_nDoWhat=ADD;     //增加提油单

	if(aCBillAdd.DoModal()==IDOK)
	{
		//显示提油数据
	    OnRadio();

		//选中最后一行
		m_nItem=m_listBill.GetItemCount();
		DispListCtrlRow(&m_listBill,m_nItem);
	}
}

//删除提油单
void CBillManager::OnBillDel() 
{
	// TODO: Add your control notification handler code here
	CString sql;
	POSITION pos = m_listBill.GetFirstSelectedItemPosition();

	//如果选中一行，则生成动态的sql语句
	if(pos)
	{
		if(!(AfxMessageBox("你真的要删除此计录?",MB_YESNO)==IDYES))
			return;

		m_nItem = m_listBill.GetNextSelectedItem(pos);
        sql="delete tiyoudan where TydID ='"+m_listBill.GetItemText(m_nItem,0)+"'";

		//执行此sql语句
		_variant_t	RecordsAffected;
		theApp.m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);

		//更新数据
		OnRadio();

		//标识所选行
        if(m_nItem==0)
		{
			m_nItem=0;
		}
		else
		{
			m_nItem--;
		}	
		DispListCtrlRow(&m_listBill,m_nItem);
	}
	else
		AfxMessageBox("请选择一行数据！");
}

//修改提油单
void CBillManager::OnBillEdit() 
{
	// TODO: Add your control notification handler code here
    POSITION pos = m_listBill.GetFirstSelectedItemPosition();
	//如果选中一行
	if(pos)
	{
		m_nItem = m_listBill.GetNextSelectedItem(pos);
		CBillAdd m_dlgBillAdd;
		
		//传送参数
		m_dlgBillAdd.m_nDoWhat=2;				//修改提油单资料
		m_dlgBillAdd.m_strTydID					=m_listBill.GetItemText(m_nItem,0);
        m_dlgBillAdd.m_strCustomerName          =m_listBill.GetItemText(m_nItem,2);
        m_dlgBillAdd.m_lInitQuantity            =atol((char *)(LPCTSTR)m_listBill.GetItemText(m_nItem,5));
        m_dlgBillAdd.m_strAgentBH               =m_listBill.GetItemText(m_nItem,1);
        m_dlgBillAdd.m_strOilName               =m_listBill.GetItemText(m_nItem,7);
        m_dlgBillAdd.m_lRemain					=atol((char *)(LPCTSTR)m_listBill.GetItemText(m_nItem,6));

        //设置开票日期
		int Year , Month , Day;
        _stscanf(m_listBill.GetItemText(m_nItem,3) , _T("%04d-%02d-%02d") , &Year , &Month , &Day);
        CTime time(Year , Month , Day , 0 , 0 , 0);
        m_dlgBillAdd.m_dtMakeOut=time;

		//设置失效日期
        _stscanf(m_listBill.GetItemText(m_nItem,4) , _T("%04d-%02d-%02d") , &Year , &Month , &Day);
        CTime time1(Year , Month , Day , 0 , 0 , 0);
        m_dlgBillAdd.m_dtDeadDate=time1;

        //显示对话框,并更新数据
		if(m_dlgBillAdd.DoModal()==IDOK)
		{   
			//更新数据
			OnRadio();

			//显示所选行
			DispListCtrlRow(&m_listBill,m_nItem);
		}
	}
	else
		AfxMessageBox("请选择一行数据！");
}

//提油单延期
void CBillManager::OnBillDelay() 
{
	// TODO: Add your control notification handler code here
    POSITION pos = m_listBill.GetFirstSelectedItemPosition();
	//如果选中一行
	if(pos)
	{
		m_nItem = m_listBill.GetNextSelectedItem(pos);
		CBillAdd m_dlgBillAdd;
		
		//传送参数
		m_dlgBillAdd.m_nDoWhat=3;				//提油单延期
		m_dlgBillAdd.m_strTydID					=m_listBill.GetItemText(m_nItem,0);
        m_dlgBillAdd.m_strCustomerName          =m_listBill.GetItemText(m_nItem,2);
        m_dlgBillAdd.m_lInitQuantity            =atol((char *)(LPCTSTR)m_listBill.GetItemText(m_nItem,5));
        m_dlgBillAdd.m_strAgentBH               =m_listBill.GetItemText(m_nItem,1);
        m_dlgBillAdd.m_strOilName               =m_listBill.GetItemText(m_nItem,7);
        m_dlgBillAdd.m_lRemain					=atol((char *)(LPCTSTR)m_listBill.GetItemText(m_nItem,6));

        //设置开票日期
		int Year , Month , Day;
        _stscanf(m_listBill.GetItemText(m_nItem,3) , _T("%04d-%02d-%02d") , &Year , &Month , &Day);
        CTime time(Year , Month , Day , 0 , 0 , 0);
        m_dlgBillAdd.m_dtMakeOut=time;

		//设置失效日期
        _stscanf(m_listBill.GetItemText(m_nItem,4) , _T("%04d-%02d-%02d") , &Year , &Month , &Day);
        CTime time1(Year , Month , Day , 0 , 0 , 0);
        m_dlgBillAdd.m_dtDeadDate=time1;

        //显示对话框并更新数据
		if(m_dlgBillAdd.DoModal()==IDOK)
		{ 
			//更新显示

			//显示所选行
			DispListCtrlRow(&m_listBill,m_nItem);
		}
	}
	else
		AfxMessageBox("请选择一行数据！");
}

//提油单作废
void CBillManager::OnBillUnuse() 
{
	// TODO: Add your control notification handler code here
	CString sql;
	POSITION pos = m_listBill.GetFirstSelectedItemPosition();

	//如果选中一行，则生成动态的sql语句
	if(pos)
	{
		if(!(AfxMessageBox("你真的要作废此提油单吗?",MB_YESNO)==IDYES))
			return;

		m_nItem = m_listBill.GetNextSelectedItem(pos);
        sql="update tiyoudan set state='F' where TydID ='"+m_listBill.GetItemText(m_nItem,0)+"'";
		_variant_t	RecordsAffected;

		//执行此sql语句
		theApp.m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);

		//显示用户数据
		OnRadio();

		//显示所选行
		DispListCtrlRow(&m_listBill,m_nItem);
	}
	else
		AfxMessageBox("请选择一行数据！");
}

//关闭窗口
void CBillManager::OnOK() 
{
	// TODO: Add extra validation here	
	CDialog::OnOK();
}

void CBillManager::OnCancel() 
{
	// TODO: Add extra validation here	
	CDialog::OnCancel();
}

//初始化窗口
BOOL CBillManager::OnInitDialog() 
{
	CDialog::OnInitDialog();	

	// TODO: Add extra initialization here	
	//默认显示有效提油单
    m_ButtonCondition.SetCheck(1);

	//设置List Control背景色及字体颜色
	m_listBill.SetBkColor(RGB(240,247,233));;
	m_listBill.SetTextColor(RGB(250,0,0));

	//清空List控件的数据
    for(int delcolumn=100;delcolumn>=0;delcolumn--)
	    m_listBill.DeleteColumn(delcolumn);

	//设置List控件的列
    DWORD		dwStyle;
	RECT		rect;
	LV_COLUMN	lvc;
	dwStyle = m_listBill.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;

	m_listBill.SetExtendedStyle(dwStyle);
	m_listBill.GetClientRect(&rect);
	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH |LVCF_FMT;
	lvc.fmt=LVCFMT_LEFT;

	lvc.iSubItem = 0;
	lvc.pszText = _T("提油单号");
	lvc.cx = 80;
	m_listBill.InsertColumn(1,&lvc);

	lvc.iSubItem = 1;
	lvc.pszText = _T("委托书号");
	lvc.cx = 80;
	m_listBill.InsertColumn(2,&lvc);

    lvc.iSubItem = 2;
	lvc.pszText = _T("客户名");
	lvc.cx = 120;
	m_listBill.InsertColumn(3,&lvc);

	lvc.iSubItem = 3;
	lvc.pszText = _T("开单日期");
	lvc.cx = 85;
	m_listBill.InsertColumn(4,&lvc);

	lvc.iSubItem = 4;
	lvc.pszText = _T("有效期限");
	lvc.cx = 85;
	m_listBill.InsertColumn(5,&lvc);

	lvc.iSubItem = 5;
	lvc.pszText = _T("油量");
	lvc.cx = 60;
	m_listBill.InsertColumn(6,&lvc);

	lvc.iSubItem = 6;
	lvc.pszText = _T("剩余量");
	lvc.cx = 80;
	m_listBill.InsertColumn(7,&lvc);

	lvc.iSubItem = 7;
	lvc.pszText = _T("油品名");
	lvc.cx = 60;
	m_listBill.InsertColumn(8,&lvc);

	lvc.iSubItem = 8;
	lvc.pszText = _T("状态");
	lvc.cx = 60;
	m_listBill.InsertColumn(9,&lvc);	
   
	//增加客户列表
	CString sql="select CustomerName from customer";
	ReadtoComBoBox((CComboBox *)GetDlgItem(IDC_COMBO_CUSTOMER),sql);
    ((CComboBox *)GetDlgItem(IDC_COMBO_CUSTOMER))->AddString("所有客户");
    ((CComboBox *)GetDlgItem(IDC_COMBO_CUSTOMER))->SelectString(0,"所有客户");

	//显示数据
    OnRadio();
    
	//选择第一行
	DispListCtrlRow(&m_listBill,0);

	return false;
	//return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

//打印提油单
void CBillManager::OnBillPrint() 
{
	// TODO: Add your control notification handler code here
	_Application	ExcelApp;      
	Workbooks		wbsMyBooks; 
	_Workbook		wbMyBook; 
	Worksheets		wssMysheets; 
	_Worksheet		wsMysheet; 
	Range			rgMyRge; 
	
	//创建Excel 2000服务器(启动Excel)  
	if (!ExcelApp.CreateDispatch("Excel.Application",NULL)) 
	{ 
		AfxMessageBox("创建Excel服务失败!"); 
		exit(1); 
	} 

	//利用模板文件建立新文档 
	CString strPath=theGlobal.TemplatePath+"book2.xlt";
	wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true); 
	wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t(strPath))); 
	wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true); 
	wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true); 
	rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);	//得到全部Cells，此时,rgMyRge是cells的集合 

	//设置1行1列的单元的值 
	rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("深圳妈")); 

	//得到所有的列 
	//rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true); 
	//得到第一列 
	//rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true); 
	//设置列宽 
	//rgMyRge.SetColumnWidth(_variant_t((long)200)); 

	//打印预览 
	wbMyBook.SetSaved(true); 
	ExcelApp.SetVisible(true); 
	wbMyBook.PrintPreview(_variant_t(false)); 

	//隐藏应用程序
    ExcelApp.SetVisible(false); 

	//释放对象 
	rgMyRge.ReleaseDispatch(); 
	wsMysheet.ReleaseDispatch(); 
	wssMysheets.ReleaseDispatch(); 
	wbMyBook.ReleaseDispatch(); 
	wbsMyBooks.ReleaseDispatch(); 
	ExcelApp.ReleaseDispatch(); 
}

//跟据条件显示不同数据
void CBillManager::OnRadio() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	CString sql;
    
	//得到时间变量
	CString m_strTimeStart,m_strTimeEnd;
	CTime   m_Timestart,   m_TimeEnd;
    m_dtcStart.GetTime(m_Timestart);
    m_dtcEnd.GetTime(m_TimeEnd);
    m_strTimeStart.Format("%04d-%02d-%02d",m_Timestart.GetYear(),m_Timestart.GetMonth(),m_Timestart.GetDay());
    m_strTimeEnd.Format("%04d-%02d-%02d",m_TimeEnd.GetYear(),m_TimeEnd.GetMonth(),m_TimeEnd.GetDay());

	switch(m_nCondition)
	{
	case 0:   //有效
		sql="select TydID,AgentBH,CustomerName,MakeOutDate,deadline,initQuantity,Remain,OilName,state \
			 from tiyoudan as a ,customer as b \
			 where a.CustomerID=b.CustomerID and State='T' and\
			 MakeOutDate>='"+ m_strTimeStart+"' and MakeOutDate<='"+ m_strTimeEnd+"'";		
		break;
	case 1:  //无效
		sql="select TydID,AgentBH,CustomerName,MakeOutDate,deadline,initQuantity,Remain,OilName,state \
			 from tiyoudan as a ,customer as b \
			 where a.CustomerID=b.CustomerID and State='F' and\
			 MakeOutDate>='"+m_strTimeStart +"' and MakeOutDate<='"+m_strTimeEnd +"'";		
		break;
	case 2:  //所有
		sql="select TydID,AgentBH,CustomerName,MakeOutDate,deadline,initQuantity,Remain,OilName,state \
			 from tiyoudan as a ,customer as b \
			 where a.CustomerID=b.CustomerID and \
			 MakeOutDate>='"+m_strTimeStart +"' and MakeOutDate<='"+m_strTimeEnd +"'";		
		break;
	default:
		AfxMessageBox("error!");
		return;
	}
     
	//显示指定用户数据
   CComboBox * m_pComboBox;
   m_pComboBox=(CComboBox *)GetDlgItem(IDC_COMBO_CUSTOMER);
   int index=((CComboBox *)m_pComboBox)->GetCurSel();
   m_pComboBox->GetLBText(index,m_strCustomer);//strContent中就是选中的内容

	//GetDlgItemText(IDC_COMBO_CUSTOMER,m_strCustomer);
	if(m_strCustomer!="所有客户")
	{
		sql=sql+" and CustomerName='"+m_strCustomer+"'";
	}

	//根据上面的动态生成的sql语句列出数据
	ReadtoListCtrl((CListCtrl *)GetDlgItem(IDC_LIST_BILL),sql);
}


void CBillManager::OnDblclkListBill(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnBillEdit();
}


void CBillManager::OnClose() 
{
	// TODO: Add your message handler code here and/or call default
	CDialog::OnOK();
}

void CBillManager::OnDatetimechangeDatetimepickerStart(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnRadio();
	*pResult = 0;
}

void CBillManager::OnDatetimechangeDatetimepickerEnd(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	OnRadio();
	*pResult = 0;
}

void CBillManager::OnEditchangeComboCustomer() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	OnRadio();
}

void CBillManager::OnEditupdateComboCustomer() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	OnRadio();
}

void CBillManager::OnSelchangeComboCustomer() 
{
	// TODO: Add your control notification handler code here
	//UpdateData();
	OnRadio();
}

void CBillManager::OnSelendokComboCustomer() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	OnRadio();
}

void CBillManager::OnDblclkComboCustomer() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	OnRadio();
}