admin.cpp

通过Access数据库实现对个人开支的管理

// Admin.cpp : implementation file
//
#include "Register.h"
#include "stdafx.h"
#include "TotalPay.h"
#include "Admin.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CAdmin dialog

extern CTotalPayApp theApp;
CAdmin::CAdmin(CWnd* pParent /*=NULL*/)
	: CDialog(CAdmin::IDD, pParent)
{
	//{{AFX_DATA_INIT(CAdmin)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
}


void CAdmin::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAdmin)
	DDX_Control(pDX, IDC_LINE3, m_line3);
	DDX_Control(pDX, IDC_LINE2, m_line2);
	DDX_Control(pDX, IDC_LINE1, m_line1);
	DDX_Control(pDX, IDC_PHOTO, m_photo);
	DDX_Control(pDX, IDC_LIST4, m_list4);
	DDX_Control(pDX, IDC_SEE, m_see);
	DDX_Control(pDX, IDC_DEL, m_del);
	DDX_Control(pDX, IDC_LIST1, m_list1);
	DDX_Control(pDX, IDC_LIST3, m_list3);
	DDX_Control(pDX, IDC_LIST2, m_list2);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CAdmin, CDialog)
	//{{AFX_MSG_MAP(CAdmin)
	ON_BN_CLICKED(IDC_DEL, OnDel)
	ON_BN_CLICKED(IDC_SEE, OnSee)
	ON_NOTIFY(NM_CLICK, IDC_LIST1, OnClickList1)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAdmin message handlers

BOOL CAdmin::OnInitDialog() 
{
	CDialog::OnInitDialog();
	m_list1.SetBkColor(RGB(220,230,233));
	m_list1.SetTextBkColor(RGB(240,247,233));
	
	m_list2.ShowWindow(SW_HIDE);
	m_list3.ShowWindow(SW_HIDE);
	m_list4.ShowWindow(SW_HIDE);
	m_line1.ShowWindow(SW_HIDE);
	m_line2.ShowWindow(SW_HIDE);
	m_line3.ShowWindow(SW_HIDE);
	

	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_list1.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_list1.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_list1.SetExtendedStyle(dwStyle);
	m_list1.GetClientRect(&rect);
	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH |LVCF_FMT;
	lvc.fmt=LVCFMT_LEFT;
	lvc.iSubItem = 0;
	lvc.pszText = _T("      姓 　名");
	lvc.cx = 130;
	m_list1.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T("             密       码");
	lvc.cx = 200;
	m_list1.InsertColumn(2,&lvc);
	lvc.iSubItem = 2;
	CString sql;
	sql = "select * from alluser";
	ReadtoList(sql);

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


void CAdmin::ReadtoList(CString sql)
{
	//删除所有list中的数据。
	m_list1.DeleteAllItems();

	int numline=0;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open((_variant_t)sql,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		if(m_pRecordset->adoEOF)
		{
		}
		else
		{
			LV_ITEM lvitem;
			lvitem.pszText="";
			lvitem.mask=LVIF_TEXT;
			lvitem.iSubItem=0;
			while(!m_pRecordset->adoEOF)
			{
				lvitem.iItem=numline;
				m_list1.InsertItem(&lvitem);
				//读出数据写入到list中
				m_list1.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("name"));
				m_list1.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("pwd"));
	
				numline++;
				m_pRecordset->MoveNext();
			}
		}
		m_pRecordset->Close();
	}
	catch(_com_error e)///捕捉异常
	{
		CString temp;
		temp.Format("连接数据库错误信息:%s",e.ErrorMessage());
		AfxMessageBox(temp);
		return;
	}
	//设置标题
	
}

void CAdmin::ReadtoList2(CString sql)
{
//删除所有list中的数据
	m_list2.SetBkColor(RGB(240,247,233));
	m_list2.SetTextBkColor(RGB(240,247,233));
	

	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
	m_list2.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_list2.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_list2.SetExtendedStyle(dwStyle);
	m_list2.GetClientRect(&rect);
	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH |LVCF_FMT;
	lvc.fmt=LVCFMT_LEFT;
	lvc.iSubItem = 0;
	lvc.pszText = _T("姓名");
	lvc.cx = 60;
	m_list2.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T("开支类型");
	lvc.cx = 90;
	m_list2.InsertColumn(2,&lvc);
	lvc.iSubItem = 2;
	lvc.pszText = _T("开支金额");
	lvc.cx = 90;
	m_list2.InsertColumn(3,&lvc);
	lvc.iSubItem = 3;
	lvc.pszText = _T("时间");
	lvc.cx = 90;
	m_list2.InsertColumn(4,&lvc);
	lvc.iSubItem = 4;
	lvc.pszText = _T("开支用途");
	lvc.cx = 120;
	m_list2.InsertColumn(5,&lvc);
	lvc.iSubItem = 5;
	lvc.pszText = _T("备 注");
	lvc.cx = 120;
	m_list2.InsertColumn(6,&lvc);
//	lvc.iSubItem = 6;。
	m_list2.DeleteAllItems();

	int numline=0;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open((_variant_t)sql,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		if(m_pRecordset->adoEOF)
		{
			//AfxMessageBox("你的数据库中还没有出差记录信息！");
		}
		else
		{
			LV_ITEM lvitem;
			lvitem.pszText="";
			lvitem.mask=LVIF_TEXT;
			lvitem.iSubItem=0;
			while(!m_pRecordset->adoEOF)
			{
				lvitem.iItem=numline;
				m_list2.InsertItem(&lvitem);
				//读出数据写入到list中
				m_list2.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("name"));
				m_list2.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("paytype"));
				m_list2.SetItemText(numline,2,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("money"));
				m_list2.SetItemText(numline,3,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("time"));
				m_list2.SetItemText(numline,4,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("reason"));
				m_list2.SetItemText(numline,5,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("explain"));	
				numline++;
				m_pRecordset->MoveNext();
			}
		}
		m_pRecordset->Close();
	}
	catch(_com_error e)///捕捉异常
	{
		CString temp;
		temp.Format("连接数据库错误信息:%s",e.ErrorMessage());
		AfxMessageBox(temp);
		return;
	}

}
void CAdmin::ReadtoList3(CString sql)
{
	m_list3.SetBkColor(RGB(240,247,233));
	m_list3.SetTextBkColor(RGB(240,247,233));
	

	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_list3.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_list3.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_list3.SetExtendedStyle(dwStyle);
	m_list3.GetClientRect(&rect);
	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH |LVCF_FMT;
	lvc.fmt=LVCFMT_LEFT;
	lvc.iSubItem = 0;
	lvc.pszText = _T("  姓　  名");
	lvc.cx = 100;
	m_list3.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T(" 开支类型");
	lvc.cx = 120;
	m_list3.InsertColumn(2,&lvc);
	lvc.iSubItem = 2;
	lvc.pszText = _T(" 开支金额");
	lvc.cx = 100;
	m_list3.InsertColumn(3,&lvc);

	m_list3.DeleteAllItems();

	int numline=0;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open((_variant_t)sql,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		if(m_pRecordset->adoEOF)
		{
		//	AfxMessageBox("还没有任何开支!");
		}
		else
		{
			LV_ITEM lvitem;
			lvitem.pszText="";
			lvitem.mask=LVIF_TEXT;
			lvitem.iSubItem=0;
			while(!m_pRecordset->adoEOF)
			{
				lvitem.iItem=numline;
				m_list3.InsertItem(&lvitem);
				//读出数据写入到list中
				m_list3.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("a"));
				m_list3.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("b"));
				m_list3.SetItemText(numline,2,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("c"));
				numline++;
				m_pRecordset->MoveNext();
			}
		}
		m_pRecordset->Close();
	}
	catch(_com_error e)///捕捉异常
	{
		CString temp;
		temp.Format("连接数据库错误信息:%s",e.ErrorMessage());
		AfxMessageBox(temp);
		return;
	}	
}
void CAdmin::ReadtoList4(CString sql)
{
	m_list4.SetBkColor(RGB(240,247,233));
	m_list4.SetTextBkColor(RGB(240,247,233));
	

	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_list4.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_list4.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_list4.SetExtendedStyle(dwStyle);
	m_list4.GetClientRect(&rect);
	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH |LVCF_FMT;
	lvc.fmt=LVCFMT_LEFT;
	lvc.iSubItem = 0;
	lvc.pszText = _T("  姓　  名");
	lvc.cx = 200;
	m_list4.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T(" 总开支");
	lvc.cx = 400;
	m_list4.InsertColumn(2,&lvc);

	
	

	m_list4.DeleteAllItems();

	int numline=0;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open((_variant_t)sql,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		if(m_pRecordset->adoEOF)
		{
			AfxMessageBox("还没有任何开支!");
		}
		else
		{
			LV_ITEM lvitem;
			lvitem.pszText="";
			lvitem.mask=LVIF_TEXT;
			lvitem.iSubItem=0;
			while(!m_pRecordset->adoEOF)
			{
				lvitem.iItem=numline;
				m_list4.InsertItem(&lvitem);
				//读出数据写入到list中
				m_list4.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("a"));
				m_list4.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("b"));
				numline++;
				m_pRecordset->MoveNext();
			}
		}
		m_pRecordset->Close();
	}
	catch(_com_error e)///捕捉异常
	{
		CString temp;
		temp.Format("连接数据库错误信息:%s",e.ErrorMessage());
		AfxMessageBox(temp);
		return;
	}		
}
void CAdmin::OnDel() 
{
	// TODO: Add your control notification handler code here
		// TODO: Add your control notification handler code here
	CString sql;
	POSITION pos = m_list1.GetFirstSelectedItemPosition();
	//如果选中一行，则生成动态的sql语句
	if(pos)
	{
		if(!(AfxMessageBox("真的要删除此用户吗？",MB_YESNO)==IDYES))
			return;

		int nItem = m_list1.GetNextSelectedItem(pos);
		CAdmin dlg;	
		dlg.m_name	=m_list1.GetItemText(nItem,0);;
		sql="delete from alluser where name='"+dlg.m_name+"'";
		_variant_t RecordsAffected;
		//执行此sql语句
		theApp.m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);
		AfxMessageBox("成功删除此用户！");
		//显示用户数据
		CString sql1="SELECT * FROM alluser";
		ReadtoList(sql1);
	}
	else
		AfxMessageBox("请选择一行数据！");
}



void CAdmin::OnSee() 
{
	
	CString sql2,sql3,sql4;
	POSITION pos = m_list1.GetFirstSelectedItemPosition();
	//如果选中一行，则生成动态的sql语句
	if(pos)
	{
		m_line1.ShowWindow(SW_SHOW);
		m_line2.ShowWindow(SW_SHOW);
		m_line3.ShowWindow(SW_SHOW);
		m_list2.ShowWindow(SW_SHOW);
		m_list3.ShowWindow(SW_SHOW);
		m_list4.ShowWindow(SW_SHOW);
		m_photo.ShowWindow(SW_HIDE);
		int nItem = m_list1.GetNextSelectedItem(pos);
		CAdmin dlg;	
		dlg.m_name	=m_list1.GetItemText(nItem,0);;
		sql2="select *  from total where name='"+dlg.m_name+"'";
		ReadtoList2(sql2);

		sql3 = "select name as a,paytype as b,sum(money) as c "
		" from total "
		"  where  name ='" + dlg.m_name+"'"
		+"group by name, paytype";
		ReadtoList3(sql3);

		sql4 = "select name as a,sum(money) as b "
		" from total"
		"  where  name ='" + dlg.m_name+"'"
		+"group by name";
		ReadtoList4(sql4);


	}
	else
		AfxMessageBox("请选择一位用户!");
	// TODO: Add your control notification handler code here
	
}

void CAdmin::OnClickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	m_list2.ShowWindow(SW_HIDE);
	m_list3.ShowWindow(SW_HIDE);
	m_list4.ShowWindow(SW_HIDE);
	m_photo.ShowWindow(SW_SHOW);
	m_line1.ShowWindow(SW_HIDE);
	m_line2.ShowWindow(SW_HIDE);
	m_line3.ShowWindow(SW_HIDE);


	*pResult = 0;
}