totalpaydlg.cpp

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

		if(dlglogon.DoModal()==IDOK)
		{
			m_in.ShowWindow(SW_SHOW);
			CString sql="SELECT * FROM total where name='"+theApp.name+"'";											 			
			//使其它控件有效
			EnableRadio(true);
			EnableButton(true);
			GetDlgItem(IDC_LIST1)->EnableWindow();
			//使登录按钮变成注销用户按钮
			SetDlgItemText(IDC_BUTTON_LOGON,"注销用户");
			//设置标题
			SetWindowText("出差记录 V1.0 by lqp      登录者："+theApp.name);
			//设显示所有记录的按钮为选中状态。
			m_radio1.SetCheck(1);
			//显示此用户的数据
			ReadtoList(sql);
			//GetDlgItem(ID_YEAR)->EnableWindow(true);
		}
	} 
	else if(title=="注销用户")
	{
		if(!(AfxMessageBox("真的要注销吗？",MB_YESNO)==IDYES))
			return;
		m_in.ShowWindow(SW_HIDE);
		//GetDlgItem(IDC_PHOTO)->EnableWindow(true);
		m_photo.ShowWindow(SW_SHOW);
		GetDlgItem(IDC_IN)->EnableWindow(false);
		GetDlgItem(IDC_LIST1)->EnableWindow(false);
		EnableRadio(false);
		EnableButton(false);
		theApp.name="";
		theApp.pwd="";
		m_listmsg.DeleteAllItems();
		SetDlgItemText(IDC_BUTTON_LOGON,"登  陆");
		SetWindowText("出差记录 V1.0 by lqp      未登录...");
	}
}
void CTotalPayDlg::EnableRadio(BOOL set)
{
	GetDlgItem(IDC_RADIO1)->EnableWindow(set);
	GetDlgItem(IDC_RADIO2)->EnableWindow(set);
	GetDlgItem(IDC_RADIO3)->EnableWindow(set);
	GetDlgItem(IDC_RADIO4)->EnableWindow(set);
	
}


void CTotalPayDlg::ReadtoList(CString sql)
{
	//删除所有list中的数据。
	m_listmsg.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_listmsg.InsertItem(&lvitem);
				//读出数据写入到list中
				m_listmsg.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("name"));
				m_listmsg.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("paytype"));
				m_listmsg.SetItemText(numline,2,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("money"));
				m_listmsg.SetItemText(numline,3,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("time"));
				m_listmsg.SetItemText(numline,4,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("reason"));
				m_listmsg.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;
	}
	//设置标题
	CString temp;
	temp.Format("出差记录 V1.0 by lqp      登录者：%s [共有%d条记录]",theApp.name,numline);
	SetWindowText(temp);
}

void CTotalPayDlg::OnRadio1() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	//OnInitDialog();
	SetList();
	CString sql;
	switch(m_radio)
	{
	case 0:
		sql="SELECT * FROM total where name='"+theApp.name+"'";
		break;
	case 1:
		sql="SELECT * FROM total where name='"+theApp.name+"' and paytype='学习开支'";
		break;
	case 2:
		sql="SELECT * FROM total where name='"+theApp.name+"' and paytype='生活开支'";
		break;
	case 3:
		sql="SELECT * FROM total where name='"+theApp.name+"' and paytype='其它开支'";
		break;
	default:
		AfxMessageBox("error!");
	}
	//根据上面的动态生成的sql语句列出数据
	ReadtoList(sql);
	
}

void CTotalPayDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	char szURL[80];
	strcpy(szURL,"http://l747474.blog.163.com");
	ShellExecute(NULL, 
		"open", 
		szURL, 
		NULL, 
		NULL, 
		SW_SHOWNORMAL);
}

void CTotalPayDlg::OnButtonAdd() 
{
	
	// TODO: Add your control notification handler code here
	CAdd dlgadd;
	//转递用户名到此对话框中
	dlgadd.m_name=theApp.name;
	dlgadd.dowhat = 0;
	dlgadd.DoModal();	
}

void CTotalPayDlg::OnButtonEdit() 
{
	// TODO: Add your control notification handler code here
	POSITION pos = m_listmsg.GetFirstSelectedItemPosition();
	
	if(pos)
	{
		int nItem = m_listmsg.GetNextSelectedItem(pos);
		CAdd dlgadd;
		dlgadd.dowhat= 1;		
		dlgadd.m_name	=theApp.name;
		dlgadd.m_type1	=m_listmsg.GetItemText(nItem,1);
		dlgadd.m_money	=m_listmsg.GetItemText(nItem,2);
		
		dlgadd.m_reason	=m_listmsg.GetItemText(nItem,4);
		dlgadd.m_beizhu	=m_listmsg.GetItemText(nItem,5);
		dlgadd.DoModal();
		
	}
	else
		AfxMessageBox("请选择一行数据！");
	
}

void CTotalPayDlg::OnButtonDel() 
{
	// TODO: Add your control notification handler code here
	CString sql;
	POSITION pos = m_listmsg.GetFirstSelectedItemPosition();
	//如果选中一行，则生成动态的sql语句
	if(pos)
	{
		
		
		if(!(AfxMessageBox("真的要删除吗？",MB_YESNO)==IDYES))
			return;
		
		int nItem = m_listmsg.GetNextSelectedItem(pos);
		sql="delete from total where name='"+theApp.name+"' and paytype='"
			+m_listmsg.GetItemText(nItem,1)+"'and reason='"
			+m_listmsg.GetItemText(nItem,4)+"' and explain='"
			+m_listmsg.GetItemText(nItem,5)+"'";
		_variant_t RecordsAffected;
		//执行此sql语句
		theApp.m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);
		AfxMessageBox("成功删除此条数据！");
		//显示用户数据
		CString sql="SELECT * FROM total where name='"+theApp.name+"'";
		ReadtoList(sql);
		
	}
	else
		AfxMessageBox("请选择一行数据！");
	
}

void CTotalPayDlg::OnButtonSearch() 
{
	// TODO: Add your control notification handler code here
	CString sql;
	CSearch dlgsearch;
	dlgsearch.DoModal();
}

void CTotalPayDlg::OnButtonConnect() 
{
	// TODO: Add your control notification handler code here
	char szMailAddress[80];
	strcpy(szMailAddress,"mailto:[e-mail]");
	ShellExecute(NULL, 
		"open", 
		szMailAddress, 
		NULL, 
		NULL, 
		SW_SHOWNORMAL);	
}

void CTotalPayDlg::OnAbout() 
{
	// TODO: Add your command handler code here
	CAboutDlg about;
	about.DoModal();
}

void CTotalPayDlg::OnMonth() 
{
	// TODO: Add your command handler code here
	m_listmsg.SetBkColor(RGB(240,247,233));
	m_listmsg.SetTextBkColor(RGB(240,247,233));
	
	
	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_listmsg.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_listmsg.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_listmsg.SetExtendedStyle(dwStyle);
	m_listmsg.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_listmsg.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T("    年");
	lvc.cx = 130;
	m_listmsg.InsertColumn(2,&lvc);
	lvc.iSubItem = 2;
	lvc.pszText = _T("    月");
	lvc.cx = 100;
	m_listmsg.InsertColumn(3,&lvc);
	lvc.iSubItem = 3;
	lvc.pszText = _T("  开   支 ");
	lvc.cx = 200;
	m_listmsg.InsertColumn(4,&lvc);
	lvc.iSubItem = 4;
	
	
	CString sql;
	sql = "select name as a,year([time]) as b ,month([time]) as c,sum(money) as d "
		"from total"
		" where name ='" + theApp.name+"'"
		"group by year([time]),month([time]),name";
	
	m_listmsg.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_listmsg.InsertItem(&lvitem);
				//读出数据写入到list中
				m_listmsg.SetItemText(numline,0,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("a"));
				m_listmsg.SetItemText(numline,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("b"));
				m_listmsg.SetItemText(numline,2,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("c"));
				m_listmsg.SetItemText(numline,3,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("d"));
				numline++;
				m_pRecordset->MoveNext();
			}
		}
		m_pRecordset->Close();
	}
	catch(_com_error e)///捕捉异常
	{
		CString temp;
		temp.Format("连接数据库错误信息:%s",e.ErrorMessage());
		AfxMessageBox(temp);
		return;
	}
	
}

void CTotalPayDlg::OnYear() 
{
	// TODO: Add your command handler code here
	m_listmsg.SetBkColor(RGB(240,247,233));
	m_listmsg.SetTextBkColor(RGB(240,247,233));
	
	
	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_listmsg.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;
	dwStyle = m_listmsg.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES |LVS_EX_FULLROWSELECT|LVS_SHOWSELALWAYS ;
	m_listmsg.SetExtendedStyle(dwStyle);
	m_listmsg.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_listmsg.InsertColumn(1,&lvc);
	lvc.iSubItem = 1;
	lvc.pszText = _T("    年");
	lvc.cx = 130;
	m_listmsg.InsertColumn(2,&lvc);
	lvc.iSubItem = 2;
	lvc.pszText = _T("   开支");
	lvc.cx = 100;
	m_listmsg.InsertColumn(3,&lvc);
	lvc.iSubItem = 3;
	
	CString sql;
	sql = "select name as a,year([time]) as b ,sum(money) as c "
		" from total"
		"  where name ='" + theApp.name+"'"
		" group by year([time]),name";
	
	m_listmsg.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_listmsg.InsertItem(&lvitem);
				//读出数据写入到list中