dlgstat.cpp

一个员工考勤管理的程序

// DlgStat.cpp : implementation file
//

#include "stdafx.h"
#include "AttendMIS.h"
#include "DlgStat.h"

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

/////////////////////////////////////////////////////////////////////////////
// CDlgStat dialog


CDlgStat::CDlgStat(CWnd* pParent /*=NULL*/)
	: CDialog(CDlgStat::IDD, pParent)
{
	//{{AFX_DATA_INIT(CDlgStat)
	m_strPerID = _T("");
	m_strYear = _T("2004");
	m_strMonth = _T("12");
	m_strPerName = _T("");
	//}}AFX_DATA_INIT
}


void CDlgStat::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDlgStat)
	DDX_Control(pDX, IDC_LIST_STAT, m_listStat);
	DDX_Text(pDX, IDC_EDIT_PERID, m_strPerID);
	DDX_Text(pDX, IDC_EDIT_YEAR, m_strYear);
	DDX_Text(pDX, IDC_EDIT_MONTH, m_strMonth);
	DDX_Text(pDX, IDC_EDIT_PERNAME, m_strPerName);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CDlgStat, CDialog)
	//{{AFX_MSG_MAP(CDlgStat)
	ON_BN_CLICKED(IDC_BTN_STAT, OnBtnStat)
	ON_BN_CLICKED(IDC_BTN_QUERY_BYID, OnBtnQueryByid)
	ON_BN_CLICKED(IDC_BTN_QUERY_ALL, OnBtnQueryAll)
	ON_EN_CHANGE(IDC_EDIT_PERID, OnChangeEditPerid)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDlgStat message handlers

void CDlgStat::OnBtnStat() 
{
	// TODO: Add your control notification handler code here
	if(!UpdateData())
		return;
	//构造一天的间隔时间
	CTimeSpan tspan_1(1,0,0,0); 
	//获取所有的员工ID
	CDStrs perFields;
	g_odbcDB.ExecuteQuery( "select distinct personid from attend_info_tab",perFields);
	int personNum = 1;
	CStringArray personIDs;
	for(int index = 0 ; index < perFields.size(); index++){
		CStrs strs = perFields[index];
		personIDs.Add(strs[0]);
	}
	CString sql;
	//统计每个员工的统计信息
	for(int i = 0 ; i < personIDs.GetSize() ; i++)
	{
		CString strPersonID = personIDs[i];
		CString strMaxIOTime ="";
		int nLateTimes = 0;
		int nEarlyTimes = 0;
		float fHours = 0;
		float fOTHours = 0;
		float fLeaveHours = 0;
		float fErrandHours = 0;
		//构造月初时间和下月初时间
		int nMaxDay = 1;
		int nYear = atoi(m_strYear);
		int nMonth = atoi(m_strMonth);		
		CTime monthStart(nYear,nMonth,1,0,0,0);
		if(nMonth == 12){
			nMonth = 1;
			nYear +=1;
		}
		else
			nMonth++;			
		CTime monthEnd(nYear,nMonth,1,0,0,0);
		CString strMonthStart = monthStart.Format("%Y-%m-%d");
		CString strMonthEnd = monthEnd.Format("%Y-%m-%d");
		//获取加班统计信息
		sql.Format("select sum(ot_hours) from overtime_info_tab "
			"where personID = '%s' and  "
			"ot_date >= to_date('%s','yyyy-mm-dd') "
			" and ot_date < to_date('%s','yyyy-mm-dd')",
			strPersonID,strMonthStart,strMonthEnd);
		TRACE(sql);	
		CString strhours;
		g_odbcDB.ExecuteQueryValue(sql,strhours);
		fOTHours = atof(strhours);
		//获取请假统计信息
		sql.Format("select start_time,end_time from leave_info_tab "
			"where personID = '%s' and  "
			"start_time > to_date('%s','yyyy-mm-dd') "
			" and end_time < to_date('%s','yyyy-mm-dd')",
			strPersonID,strMonthStart,strMonthEnd);
		TRACE(sql);	
		CDStrs dtFields;
		//获取收入类型数据
		g_odbcDB.ExecuteQuery(sql,dtFields);
		for(int index2 = 0 ; index2 < dtFields.size() ; index2++)
		{
			CStrs strs = dtFields[index2];
			CTime tm1,tm2;
			CString strhours;
			//计算请假的小时数
			strhours = strs[0];
			tm1 = GetTMByString(strhours);
			strhours = strs[1];
			tm2 = GetTMByString(strhours);
			CTimeSpan span = tm2-tm1;
			fLeaveHours += span.GetTotalHours()- (tm2.GetDay()-tm1.GetDay())*16;	
		}

		CDStrs dtFields2;
		//获取出差工作时间
		sql.Format("select start_time,end_time from errand_info_tab "
			"where personID = '%s' and  start_time >to_date('%s','yyyy-mm-dd') "
			" and end_time < to_date('%s','yyyy-mm-dd')",
			strPersonID,strMonthStart,strMonthEnd);
		TRACE(sql);	
		g_odbcDB.ExecuteQuery(sql,dtFields2);
		for(int index3 = 0 ; index3 < dtFields2.size() ; index3++)
		{
			CStrs strs = dtFields2[index3];
			CTime tm1,tm2;
			CString strhours;
			//计算请假的小时数
			strhours = strs[0];
			tm1 = GetTMByString(strhours);
			strhours = strs[1];
			tm2 = GetTMByString(strhours);
			CTimeSpan span = tm2-tm1;
			fErrandHours += span.GetTotalHours() - (tm2.GetDay()-tm1.GetDay())*16;
		}
	
		//获取正常工作时间 迟到次数 早退次数
		//获取当月含有出勤信息的最大日期
		sql.Format("select max(io_time) from attend_info_tab "
			"where personID = '%s' and  "
			"io_time > to_date('%s','yyyy-mm-dd')"
			" and io_time <to_date('%s','yyyy-mm-dd')",
			strPersonID,strMonthStart,strMonthEnd);
		TRACE(sql);
		g_odbcDB.ExecuteQueryValue(sql,strMaxIOTime);
		//获取当月含有出勤信息的最大天数
		COleDateTime dtMax ;
		dtMax.ParseDateTime(strMaxIOTime); 
		nMaxDay= dtMax.GetDay();
		CString strStart,strEnd;
		CTime tm[4];
		//获取每天的工作时间
		for(int j = 1 ; j <= nMaxDay ; j++){
			CString sql;
			CTime tm1(atoi(m_strYear),atoi(m_strMonth),j,0,0,0);
			CTime tm2 = tm1 + tspan_1;
			strStart =tm1.Format("%Y-%m-%d");
			strEnd = tm2.Format("%Y-%m-%d");
			sql.Format("select io_time from attend_info_tab "
			"where personID = '%s' and  io_time > to_date('%s','yyyy-mm-dd') "
			" and io_time < to_date('%s','yyyy-mm-dd') order by io_time",
			strPersonID,strStart,strEnd);
			TRACE(sql);
			int k = 0;
			CDStrs ioFields;
			g_odbcDB.ExecuteQuery(sql,ioFields);
			//获取一天中的4个时间,分别为上午和下午的上下班时间
			for(int index4 = 0 ; index4 < ioFields.size() ; index4++) {
				CStrs strs = ioFields[index4];
				CString temp = strs[0];
				tm[k] = GetTMByString(temp);
				k++;
			}
			//如果当天没有,进入下一天
			if(k == 0)
				continue;
			//计算上午和下午的工作时间,并求和.
			CTimeSpan span =  tm[1]-tm[0];
			fHours = span.GetTotalHours();
			if(k == 4){
				span = tm[3]-tm[2];
				fHours += span.GetTotalHours();
			}
			if(tm[0]>m_dtAMStart)
				nLateTimes++;
			if(tm[1]<m_dtAMEnd)
				nEarlyTimes++;
			if(tm[2]>m_dtPMStart&&k==4)
				nLateTimes++;
			if(tm[3]>m_dtPMEnd&&k==4)
				nEarlyTimes++;
		}
		CString strID ;
		g_odbcDB.ExecuteQueryValue("select max(id) from attend_stat_info_tab",strID);
		int statID = atoi(strID)+1;
		//先删除该月的统计信息
		CString strMonthStat = m_strYear + "-" + m_strMonth; 
		sql.Format("delete from attend_stat_info_tab "
			"where personID = '%s' and year_month = '%s'",
			strPersonID,strMonthStat);
		TRACE(sql);
		g_odbcDB.ExecuteSQL(sql);
		sql.Format("insert into attend_stat_info_tab(id,year_month,"
			"personID,att_hour,ot_hour,leave_hour,"
			"errand_hour,late_times,early_times) values( "
			"%d,'%s','%s',%6.2f,%6.2f,%6.2f,%6.2f,%d,%d)",statID,
				strMonthStat,strPersonID,fHours,fOTHours,fLeaveHours,
				fErrandHours,nLateTimes,nEarlyTimes);
		TRACE(sql);
		//向数据库中添加该月的统计信息
		g_odbcDB.ExecuteSQL(sql);
	}
	OnBtnQueryAll();		
}

void CDlgStat::OnOK() 
{
	// TODO: Add extra validation here
	
	CDialog::OnOK();
}

void CDlgStat::OnBtnQueryByid() 
{
	if(!UpdateData())
		return;
	m_listStat.DeleteAllItems();
	CDStrs attFields;
	CString sql;
	//获取所有的统计信息
	sql.Format("select * from attend_stat_info_tab where personid = '%s'",m_strPerID);
	g_odbcDB.ExecuteQuery(sql,attFields);
	for(int i = 0 ; i < attFields.size() ; i++)
	{
		CStrs strs = attFields[i];
		InsertItemInfo(strs[0],strs[1],strs[2],m_strPerName,strs[3],strs[4],
			strs[5],strs[6],strs[7],strs[8]);	
	}
}

void CDlgStat::OnBtnQueryAll() 
{
	if(!UpdateData())
		return;
	m_listStat.DeleteAllItems();
	CDStrs attFields;
	CString sql;
	//获取所有的统计信息
	sql = "select a.id,a.year_month,a.personid,b.name,"
		"a.att_hour,a.ot_hour,a.leave_hour,"
		"a.errand_hour,a.late_times,a.early_times "
		"from attend_stat_info_tab a,person_info_tab b where a.personid = b.personid";
	g_odbcDB.ExecuteQuery(sql,attFields);
	for(int i = 0 ; i < attFields.size() ; i++)
	{
		CStrs strs = attFields[i];
		InsertItemInfo(strs[0],strs[1],strs[2],strs[3],strs[4],
			strs[5],strs[6],strs[7],strs[8],strs[9]);	
	}				
}

BOOL CDlgStat::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	//设置列表框控件扩展风格
	DWORD dwExStyle = LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES |
		LVS_EX_HEADERDRAGDROP | LVS_EX_ONECLICKACTIVATE | LVS_EX_UNDERLINEHOT;
	m_listStat.SetExtendedStyle(dwExStyle);
	//初始化统计信息列表框控件
	m_listStat.InsertColumn(0,"ID",LVCFMT_CENTER,40);
	m_listStat.InsertColumn(1,"统计月份",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(2,"员工ID",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(3,"员工姓名",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(4,"正常工作小时",LVCFMT_CENTER,100);
	m_listStat.InsertColumn(5,"加班小时",LVCFMT_CENTER,100);	
	m_listStat.InsertColumn(6,"请假小时",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(7,"出差小时",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(8,"迟到次数",LVCFMT_CENTER,60);
	m_listStat.InsertColumn(9,"早退次数",LVCFMT_CENTER,60);		
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

CTime CDlgStat::GetTMByString(CString str)
{
	COleDateTime dt;
	dt.ParseDateTime(str); 
	CTime tm(dt.GetYear(),dt.GetMonth(),dt.GetDay(),dt.GetHour(),
		dt.GetMinute(),dt.GetSecond());
	return tm;
}

void CDlgStat::InsertStatInfo(int nID,CString monthStat,CString perID,CString perName, 
			float workhour,float othour,float leavehour, float errandhour,
			int latetimes,int earlytimes)
{
	//获取当前的纪录条数.
	int nIndex = m_listStat.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				//行数
	lvItem.iSubItem = 0;
	CString temp;
	temp.Format("%d",nID);
	lvItem.pszText = (char*)(LPCTSTR)temp;		//第一列
	//在最后一行插入记录值.
	m_listStat.InsertItem(&lvItem);	
	//设置该的其他列的值.
	m_listStat.SetItemText(nIndex,1,monthStat);
	m_listStat.SetItemText(nIndex,1,perID);
	m_listStat.SetItemText(nIndex,2,perName);	
	temp.Format("%6.2f",workhour);
	m_listStat.SetItemText(nIndex,3,temp);	
	temp.Format("%6.2f",othour);
	m_listStat.SetItemText(nIndex,4,temp);	
	temp.Format("%6.2f",leavehour);
	m_listStat.SetItemText(nIndex,5,temp);	
	temp.Format("%6.2f",errandhour);
	m_listStat.SetItemText(nIndex,6,temp);	
	temp.Format("%d",latetimes);
	m_listStat.SetItemText(nIndex,7,temp);	
	temp.Format("%d",earlytimes);
	m_listStat.SetItemText(nIndex,8,temp);
}

void CDlgStat::OnChangeEditPerid() 
{
	if(!UpdateData())
		return;
	m_strPerName = "";
	CString sql;
	//获取员工姓名
	sql.Format("select name from person_info_tab where personid = '%s'",m_strPerID);
	g_odbcDB.ExecuteQueryValue(sql,m_strPerName);
	UpdateData(FALSE);	
}

void CDlgStat::InsertItemInfo(CString item0,CString item1,CString item2, 
		CString item3,CString item4, CString item5, CString item6,
		CString item7,CString item8,CString item9)
{
	//获取当前的纪录条数.
	int nIndex = m_listStat.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				//行数
	lvItem.iSubItem = 0;
	lvItem.pszText = (char*)(LPCTSTR)item0;		//第一列
	//在最后一行插入记录值.
	m_listStat.InsertItem(&lvItem);	
	//设置该的其他列的值.
	m_listStat.SetItemText(nIndex,1,item1);
	m_listStat.SetItemText(nIndex,2,item2);	
	m_listStat.SetItemText(nIndex,3,item3);	
	m_listStat.SetItemText(nIndex,4,item4);	
	m_listStat.SetItemText(nIndex,5,item5);
	m_listStat.SetItemText(nIndex,6,item6);	
	m_listStat.SetItemText(nIndex,7,item7);	
	m_listStat.SetItemText(nIndex,8,item8);	
	m_listStat.SetItemText(nIndex,9,item9);	
}