timecardlist.cpp

人力资源管理系统

				  and month(timecart_date) = %d \
				  and tb_employee.employee_name = '%s' and\
				  tb_employee.employee_id = tb_timecard.employee_id", year, month, name);
	ExportToWord(file, sql);
	return TRUE;
}


BOOL CTimeCardList::ExportToWord(CString &file, CString &sql)
{
	/*
		以下代码为数据库初始化操作部分
	*/
	_ConnectionPtr pConnection;
	_RecordsetPtr pRecordset;
	Fields* fields = NULL;
	_bstr_t bstrSQL = sql;
	long count = 0;			//表头元素个数
	try
	{
		pConnection.CreateInstance(_uuidof(Connection));
		pConnection->Open(_bstr_t(Provider), "", "", adModeUnknown);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		exit(0);
	}
	pRecordset.CreateInstance(_uuidof(Recordset));
	pRecordset->Open(bstrSQL, 
		pConnection.GetInterfacePtr(), 
		adOpenDynamic, adLockOptimistic, adCmdText);
	pRecordset->get_Fields(&fields);
	count = fields->Count;		//得到表头元素个数


	/*
		以下代码为Word应用对象的初始化过程
	*/
	_Application	application;		//创建一个Word应用对象
	_Document		document;
	Documents		documents;
	Tables			tables;
	Range			range;
	Selection		selection;
	COleVariant		colevariant;

	CComVariant Template(_T("")), NewTemplate(false), DocumentType(0), Visible(true);

	application.CreateDispatch("Word.Application");
	documents.AttachDispatch(application.GetDocuments());
	document.AttachDispatch(documents.Add(&Template,	//无模板
		&NewTemplate,									//不是新建模板
		&DocumentType,									//无类型
		&Visible));										//初始不可见
	range.AttachDispatch(document.GetContent());
	tables.AttachDispatch(document.GetTables());
	tables.Add(range, GetRecordCount(pRecordset),	//记录数
		count, colevariant, colevariant);						//添加表格
	selection.AttachDispatch(application.GetSelection());

	
	CString strText;
	BSTR bstr;

	_variant_t varField[20];
	for(long num = 0; num < count; num++)
	{
		strText.Format("%d", num);
		(fields->Item[(long)num])->get_Name(&bstr);
		varField[num] = bstr;

		selection.TypeText((char*)(_bstr_t)bstr);	//插入数据
		selection.MoveRight((COleVariant)"1", (COleVariant)"1", (COleVariant)"0");

	}

	CString content;
	if(!pRecordset->adoBOF)
		pRecordset->MoveFirst();
	while(!pRecordset->adoEOF)
	{
		for(long num = 0; num < (pRecordset->GetFields())->GetCount(); num++)
		{
			strText.Format("%d", num);
			if(num == 3)
			{
				content = (char *)(_bstr_t)pRecordset->GetCollect(varField[num]);
				content.Replace(" ", "");
				switch(atoi(content))
				{
				case 0:
					selection.TypeText("全勤");
					break;
				case 1:
					selection.TypeText("事假");
					break;
				case 2:
					selection.TypeText("公假");
					break;
				case 3:
					selection.TypeText("病假");
					break;
				default:
					{
						MessageBox("系统出现错误！");
						exit(0);
					}
				}
				
			}
			else if(num == 5 || num == 6)
			{
				content = (char *)(_bstr_t)pRecordset->GetCollect(varField[num]);
				content.Replace(" ", "");
				if(atoi(content) > 0)
					selection.TypeText("是");
				else
					selection.TypeText("否");
			}
			else
				selection.TypeText((char *)(_bstr_t)pRecordset->GetCollect(varField[num]));	//把对应表头的相关信息输入到Word的表中
			selection.MoveRight((COleVariant)"1", (COleVariant)"1", (COleVariant)"0");	//移动光标
		}
		pRecordset->MoveNext();
	}
	//断开连接
	if(pConnection != NULL)
		pConnection->Close();
	pConnection = NULL;

	//保存代码
	_Document saveDoc = application.GetActiveDocument();	//得到ActiveDocument

	CComVariant FileName("");		//保存的位置
	if(file.IsEmpty())
		FileName = "C:\\DataBaseTable.doc";
	else
		FileName = file;

	CComVariant FileFormat(0);						//这是重点看下面的说明
	CComVariant LockComments(FALSE), Password(_T(""));
	CComVariant AddToRecentFiles(true),WritePassword("");
	CComVariant ReadOnlyRecommended(false), EmbedTrueTypeFonts(false);
	CComVariant SaveNativePictureFormat(false), SaveFormsData(false);
	CComVariant SaveAsAOCELetter(false);
	saveDoc.SaveAs(&FileName, &FileFormat, &LockComments, &Password, 
		&AddToRecentFiles, &WritePassword, &ReadOnlyRecommended, 
		&EmbedTrueTypeFonts, &SaveNativePictureFormat, &SaveFormsData,&SaveAsAOCELetter);

	//设置Word窗口可见并释放应用程序的资源
	application.SetVisible(TRUE);
	tables.ReleaseDispatch();
	selection.ReleaseDispatch();
	documents.ReleaseDispatch();
	document.ReleaseDispatch();
	application.ReleaseDispatch();

	return TRUE;
}

DWORD CTimeCardList::GetRecordCount(_RecordsetPtr pRecordset)
{
	DWORD count = 1;
	if(!pRecordset->adoEOF)
		pRecordset->MoveFirst();
	else
		return 0;
	do{
		pRecordset->MoveNext();
		count++;

	} while(!pRecordset->adoEOF);

	return count;
}

void CTimeCardList::OnDeleteitem() 
{
	// TODO: Add your command handler code here
	if(m_CurSel < 0)
	{
		MessageBox("请选择一个所要删除的项！");
		return ;
	}
	CADOOperation ado;
	CString date = m_TimeCardList.GetItemText(m_CurSel, 0);
	CString name = m_TimeCardList.GetItemText(m_CurSel, 1);
	CString sql;
	sql.Format("\
				  delete from tb_timecard \
				  where tb_timecard.employee_id in \
				  (select tb_employee.employee_id from tb_employee, tb_timecard\
				  where tb_timecard.employee_id = tb_bargain.employee_id and employee_name = '%s')\
				  and timecart_date = '%s'", name, date);
	ado.OpenRecordset(sql);
	ado.CloseRecorset();
	m_CurSel = -1;
	UpdateTimeCardList();
}

void CTimeCardList::OnRclickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	m_CurSel = ((LPNMLISTVIEW)pNMHDR)->iItem;
/*	if(m_CurSel < 0)
		goto end;
	POINT pt;
	::GetCursorPos(&pt);
	CADOView::ShowItemMenu(G_IsLanded, pt.x, pt.y, this);*/

end:
	*pResult = 0;
}

void CTimeCardList::OnQuit() 
{
	// TODO: Add your command handler code here
	EndDialog(0);
}

void CTimeCardList::OnSearchRecord() 
{
	// TODO: Add your command handler code here
	MessageBox("检索");
}

BOOL CTimeCardList::CollectTimeCard(CString &name, int year, int month)
{
	int count = m_TimeCardList.GetItemCount();
	CString con;

	m_TimeCardList.InsertItem(count, "早退总数：");
	con.Format("%d", GetLeaveEarlyCount(name, year, month));
	m_TimeCardList.SetItemText(count, 1, con);
	count++;
	m_TimeCardList.InsertItem(count, "迟到总数：");
	con.Format("%d", GetLateCount(name, year, month));
	m_TimeCardList.SetItemText(count, 1, con);
	count++;
	m_TimeCardList.InsertItem(count, "缺勤总数：");
	con.Format("%d", GetAbsenceDuty(name, year, month));
	m_TimeCardList.SetItemText(count, 1, con);
	count++;
	m_TimeCardList.InsertItem(count, "总工作时间：");
	con.Format("%.1f", GetCollectWorkTime(name, year, month));
	m_TimeCardList.SetItemText(count, 1, con);

	return TRUE;
}

DWORD CTimeCardList::GetAbsenceDuty(CString &name, int year, int month)
{
	DWORD count = 0;
	CString sql;
	sql.Format("\
				  select count(*) from tb_timecard, tb_employee\
				  where tb_employee.employee_name = '%s' and tb_employee.employee_id = tb_timecard.employee_id and \
				  year(tb_timecard.timecart_date) = %d and month(tb_timecard.timecart_date) = %d\
				  and tb_timecard.timecart_reason > 0", name, year, month);
	CADOOperation ado;
	ado.OpenRecordset(sql);
	ado.GetItemContent(0, CADOOperation::ADO_TYPE_INT, &count);
	ado.CloseRecorset();
	return count;
}

double CTimeCardList::GetCollectWorkTime(CString &name, int year, int month)
{
	double collect = 0.0;
	DWORD count = 0;
	CString sql;
	CADOOperation ado;
	sql.Format("\
				  select sum(tb_timecard.timecart_worklong) as sum from tb_timecard, tb_employee\
				  where tb_employee.employee_name = '%s' and tb_employee.employee_id = tb_timecard.employee_id and \
				  year(tb_timecard.timecart_date) = %d and month(tb_timecard.timecart_date) = %d", name, year, month);
	PRINTRESULT(sql)
	ado.OpenRecordset(sql);
	ado.GetItemContent("sum", CADOOperation::ADO_TYPE_INT, &count);
	collect = (double)count/(double)60;
	ado.CloseRecorset();
	return collect;

}

DWORD CTimeCardList::GetLateCount(CString &name, int year, int month)
{
	DWORD count = 0;
	CADOOperation ado;
	CString sql;
	sql.Format("\
				  select count(*) from tb_timecard,tb_employee\
				  where timecart_late_leave_early & 1 >0\
				  and year(timecart_date) = %d \
				  and month(timecart_date) = %d\
				  and tb_employee.employee_name = '%s'\
				  and tb_employee.employee_id = tb_timecard.employee_id", year, month, name);
	ado.OpenRecordset(sql);
	ado.GetItemContent(0, CADOOperation::ADO_TYPE_INT, &count);
	ado.CloseRecorset();
	return count;
}

DWORD CTimeCardList::GetLeaveEarlyCount(CString &name, int year, int month)
{
	DWORD count = 0;
	CADOOperation ado;
	CString sql;
	sql.Format("select count(*) from tb_timecard,tb_employee\
				  where timecart_late_leave_early & 2 >0\
				  and year(timecart_date) = %d \
				  and month(timecart_date) = %d\
				  and tb_employee.employee_name = '%s'\
				  and tb_employee.employee_id = tb_timecard.employee_id", year, month, name);
	ado.OpenRecordset(sql);
	ado.GetItemContent(0, CADOOperation::ADO_TYPE_INT, &count);
	ado.CloseRecorset();
	return count;
}