⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 reportdlg.cpp

📁 access dao 数据库
💻 CPP
字号:
🔍 
// reportdlg.cpp : 实现文件
//

#include "stdafx.h"
#include "kaoqing.h"
#include "reportdlg.h"
#include ".\reportdlg.h"


// reportdlg 对话框

IMPLEMENT_DYNAMIC(reportdlg, CDialog)
reportdlg::reportdlg(CWnd* pParent /*=NULL*/)
	: CDialog(reportdlg::IDD, pParent)
	, rptitlestr(_T(""))
	, sumtypestr(_T(""))
	, sumcatlogstr(_T(""))
	, yearstr(_T(""))
	, focus(0)
	, isok(false)
{
}

reportdlg::~reportdlg()
{
}

void reportdlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST1, reportlist);
	DDX_Control(pDX, IDC_BUTTON1, checkbymonthbt);
	DDX_Control(pDX, IDC_BUTTON11, savetoexcelbt);
	DDX_Control(pDX, IDCANCEL, closebt);
	DDX_Control(pDX, IDC_RPSTATIC, rptitlest);
	DDX_Control(pDX, IDC_BUTTON2, sumallbt);
	DDX_Text(pDX, IDC_RPSTATIC, rptitlestr);
	DDX_CBString(pDX, IDC_COMBO1, sumtypestr);
	DDX_CBString(pDX, IDC_COMBO5, sumcatlogstr);
	DDX_Control(pDX, IDC_COMBO1, sumtypecombo);
	DDX_Control(pDX, IDC_COMBO5, sumcatlogcombo);
	DDX_Control(pDX, IDC_CHOOSEST1, choosest);
}


BEGIN_MESSAGE_MAP(reportdlg, CDialog)
	ON_WM_PAINT()
	ON_WM_SIZE()
	ON_CBN_SELCHANGE(IDC_COMBO1, OnCbnSelchangeCombo1)
	ON_CBN_SELCHANGE(IDC_COMBO5, OnCbnSelchangeCombo5)
	ON_BN_CLICKED(IDC_BUTTON2, OnBnClickedButton2)
	ON_BN_CLICKED(IDC_BUTTON1, OnBnClickedButton1)
	ON_BN_CLICKED(IDC_BUTTON11, OnBnClickedButton11)
	ON_BN_CLICKED(IDCANCEL, OnBnClickedCancel)
	ON_BN_SETFOCUS(IDC_BUTTON2, OnBnSetfocusButton2)
	ON_BN_KILLFOCUS(IDC_BUTTON2, OnBnKillfocusButton2)
	ON_BN_SETFOCUS(IDC_BUTTON1, OnBnSetfocusButton1)
	ON_BN_KILLFOCUS(IDC_BUTTON1, OnBnKillfocusButton1)
END_MESSAGE_MAP()


// reportdlg 消息处理程序

BOOL reportdlg::OnInitDialog()
{CString tempstr;
	CDialog::OnInitDialog();
     reportlist.SetTextColor(RGB(0,0,0));
	 reportlist.SetTextBkColor(RGB(220,235,220));//文本背景色
	 reportlist.SetBkColor(RGB(200,220,220));//背景色
     reportlist.SetExtendedStyle (LVS_EX_GRIDLINES|LVS_REPORT|LVS_EX_FULLROWSELECT|LVS_EX_FLATSB);
     ShowWindow (SW_MAXIMIZE);
	// TODO:  在此添加额外的初始化


//reportlist.InsertColumn (0,"one",LVCFMT_CENTER,-1,-1);
//reportlist.InsertColumn (1,"two",LVCFMT_CENTER,-1,-1);
//this->rptitlestr=yearstr;
sumtypecombo.SetCurSel (0);
sumcatlogcombo.SetCurSel (0);
//this->UpdateData (false);
	return TRUE;  // return TRUE unless you set the focus to a control
	// 异常: OCX 属性页应返回 FALSE
}

void reportdlg::OnPaint()
{int count=0;
	CPaintDC dc(this); // device context for painting
	CRect rect,rect2;
    GetClientRect (&rect);
  reportlist.MoveWindow (5,90,rect.Width ()-10,rect.Height ()-140);
  choosest.MoveWindow (5,0,rect.Width ()-10,45);
 
  rptitlest.GetClientRect (&rect2);
  rptitlest.MoveWindow (0.5*(rect.Width ()-rect2.Width ()),60,rect2.Width (),rect2.Height ());

//rect.Width ()-10-rect2.Width ()
  
  count=reportlist.GetHeaderCtrl ()->GetItemCount ();
  for( int i=0;i<count;i++)
reportlist.SetColumnWidth (i,(rect.Width ()-10)/count);


checkbymonthbt.GetClientRect (&rect2);
 checkbymonthbt.MoveWindow (30,rect.Height ()-40,rect2.Width (),rect2.Height ());
savetoexcelbt.GetClientRect (&rect2);
savetoexcelbt.MoveWindow (150,rect.Height ()-40,rect2.Width (),rect2.Height ());

closebt.GetClientRect (&rect2);
closebt.MoveWindow (270,rect.Height ()-40,rect2.Width (),rect2.Height ());
}

void reportdlg::OnSize(UINT nType, int cx, int cy)
{
	CDialog::OnSize(nType, cx, cy);

	Invalidate ();
    CenterWindow ();
}

void reportdlg::OnCbnSelchangeCombo1()
{
reportlist.DeleteAllItems ();
	// TODO: 在此添加控件通知处理程序代码
}

void reportdlg::OnCbnSelchangeCombo5()
{
	reportlist.DeleteAllItems ();

}

void reportdlg::OnBnClickedButton2()
{int count;
CRect rect;
GetClientRect (&rect);
CDaoFieldInfo fin;
UpdateData (true);
isok=false;
CString querystr="";
if(sumtypestr=="")  {sumtypecombo.SetFocus ();AfxMessageBox("请选择汇总类型、汇总类别");return;}
if(sumcatlogstr=="") {sumcatlogcombo.SetFocus ();AfxMessageBox("请选择汇总类型、汇总类别");return;}
//删除原来列表视图
reportlist.DeleteAllItems ();
count=reportlist.GetHeaderCtrl ()->GetItemCount ();
for(int i=0;i<count;i++) reportlist.DeleteColumn (0);


CDaoDatabase kaoqingbu;
CDaoRecordset fillrec;
kaoqingbu.Open (".\\kqbook.mdb",0,0,";PWD=caolinjie1226loveyou");//打开数据库
CDaoQueryDef conquery(&kaoqingbu);
CString conditionstr="";
conditionstr.Format ("WHERE 考勤明细查询.年份 = '%s'",yearstr);
if(sumtypestr==_T("按月份列出"))
                    {
	                 if(sumcatlogstr=="正班") querystr="TRANSFORM Sum(考勤明细查询.正班) AS 正班之Sum1 SELECT 考勤明细查询.人员编号, 考勤明细查询.姓名, Sum(考勤明细查询.正班) AS 正班总数 FROM 考勤明细查询 "+conditionstr+ " GROUP BY 考勤明细查询.人员编号, 考勤明细查询.姓名 ORDER BY 考勤明细查询.人员编号 PIVOT 考勤明细查询.月份;";
					                     else querystr="TRANSFORM Sum(考勤明细查询.副班) AS 副班之Sum1 SELECT 考勤明细查询.人员编号, 考勤明细查询.姓名, Sum(考勤明细查询.副班) AS 副班总数 FROM 考勤明细查询 "+conditionstr+ " GROUP BY 考勤明细查询.人员编号, 考勤明细查询.姓名   ORDER BY 考勤明细查询.人员编号 PIVOT 考勤明细查询.月份;";
                     
                    }

else{                if(sumcatlogstr=="正班") querystr="TRANSFORM Sum(考勤明细查询.正班) AS 正班之Sum1 SELECT 考勤明细查询.人员编号, 考勤明细查询.姓名, Sum(考勤明细查询.正班) AS 正班总数 FROM 考勤明细查询 "+conditionstr+ " GROUP BY 考勤明细查询.人员编号, 考勤明细查询.姓名 ORDER BY 考勤明细查询.人员编号  PIVOT 考勤明细查询.出勤单位;";
					                     else querystr="TRANSFORM Sum(考勤明细查询.副班) AS 副班之Sum1 SELECT 考勤明细查询.人员编号, 考勤明细查询.姓名, Sum(考勤明细查询.副班) AS 副班总数 FROM 考勤明细查询 "+conditionstr+ " GROUP BY 考勤明细查询.人员编号, 考勤明细查询.姓名 ORDER BY 考勤明细查询.人员编号  PIVOT 考勤明细查询.出勤单位;";
                     
                    }





conquery.Create (0,querystr);//创建临时查询

fillrec.Open (&conquery,dbOpenDynaset);
if(fillrec.IsBOF ()) 
{this->rptitlestr=""; 
this->UpdateData (false);
fillrec.Close ();conquery.Close ();kaoqingbu.Close ();
AfxMessageBox("无指定的考勤记录可以汇总");
return;

}//空则返回

//创建新列表视图
for( int i=0;i<fillrec.GetFieldCount ();i++)
{fillrec.GetFieldInfo(i,fin, AFX_DAO_PRIMARY_INFO );
reportlist.InsertColumn (i,fin.m_strName,LVCFMT_CENTER,-1,-1);
nametonum[i]=fin.m_strName;
//AfxMessageBox(nametonum[i]);
}
//设置列宽
count=reportlist.GetHeaderCtrl ()->GetItemCount ();
for( int i=0;i<count;i++)
reportlist.SetColumnWidth (i,(rect.Width ()-10)/count);
int j=0;
CString numstr;
while(!fillrec.IsEOF ())
{ numstr.Format ("%.2f",V_R8(&fillrec.GetFieldValue (2)));
  if(numstr=="0.00") {fillrec.MoveNext ();continue;}
 
 reportlist.InsertItem (j,CString(V_BSTRT(&fillrec.GetFieldValue (0))));
 reportlist.SetItemText (j,1,CString(V_BSTRT(&fillrec.GetFieldValue (1))));
 reportlist.SetItemText (j,2,numstr);


 for(int col=3;col<fillrec.GetFieldCount ();col++)
  {numstr.Format ("%.2f",V_R8(&fillrec.GetFieldValue (col)));
  if(numstr=="0.00") numstr="";
  reportlist.SetItemText (j,col,numstr);
  }
 
j++;
 fillrec.MoveNext ();
}

if(j==0)
{reportlist.DeleteAllItems ();
count=reportlist.GetHeaderCtrl ()->GetItemCount ();
for(int i=0;i<count;i++) reportlist.DeleteColumn (0);
checkbymonthbt.EnableWindow (false);
savetoexcelbt.EnableWindow (false);
fillrec.Close ();conquery.Close ();kaoqingbu.Close ();
rptitlestr=""; 
UpdateData (false);

AfxMessageBox("无指定的考勤记录可以汇总");
return;
}

if(sumtypestr=="按月份列出") 
{checkbymonthbt.EnableWindow (true);
savetoexcelbt.EnableWindow (false);
}
else 
{checkbymonthbt.EnableWindow (false);
 savetoexcelbt.EnableWindow (true);

}
fillrec.Close ();conquery.Close ();kaoqingbu.Close ();

rptitlestr="北京项目管理公司"+yearstr+"年考勤汇总表   (【汇总类型】 "+sumtypestr+"   "+sumcatlogstr+"汇总及明细)"; 
UpdateData (false);

//isok=true;
}
void reportdlg::OnBnClickedButton1()
{int row,col;
int ernum=0;

CString  spacestr="   ";
CString errostr="下列人员及月考勤天数不对,请在原始录入中核对：\n\n";
//nametonum[0]="人员编号";
//nametonum[1]="姓名";
//nametonum[2]="正班合计";

row=reportlist.GetItemCount ();
col=reportlist.GetHeaderCtrl ()->GetItemCount ();
for(int i=0;i<row;i++)
 for(int j=3;j<col;j++)
 {
    
	if(nametonum[j]==_T("01月")||nametonum[j]==_T("03月")||nametonum[j]=="05月"||nametonum[j]=="07月"||nametonum[j]=="08月"||nametonum[j]=="10月"||nametonum[j]=="12月")
	{if( atof(CString(reportlist.GetItemText (i,j)))>31) {errostr=errostr+spacestr+reportlist.GetItemText (i,0)+spacestr+reportlist.GetItemText (i,1)+spacestr+nametonum[j]+spacestr+CString(reportlist.GetItemText (i,j))+"\n";ernum++;continue;}
	}
     
   else
	   if((nametonum[j]=="04月"||nametonum[j]=="06月"||nametonum[j]=="09月"||nametonum[j]=="11月"))
	   {if(atof(CString(reportlist.GetItemText (i,j)))>30) {errostr=errostr+spacestr+reportlist.GetItemText (i,0)+spacestr+reportlist.GetItemText (i,1)+spacestr+nametonum[j]+spacestr+CString(reportlist.GetItemText (i,j))+"\n";ernum++;continue;}
	   }
         
	   else  if (atof(CString(reportlist.GetItemText (i,j)))>29)  {errostr=errostr+spacestr+reportlist.GetItemText (i,0)+spacestr+reportlist.GetItemText (i,1)+spacestr+nametonum[j]+spacestr+CString(reportlist.GetItemText (i,j))+"\n";ernum++;continue;}

 }   
	

 
 if(ernum==0) { savetoexcelbt.EnableWindow (true); AfxMessageBox("审核完成，没有发现错误！");}   
  
 else {	 savetoexcelbt.EnableWindow (false);AfxMessageBox(errostr) ; } 
	
	
	
	// TODO: 在此添加控件通知处理程序代码
}

void reportdlg::OnBnClickedButton11()
{
CDaoDatabase rtoexcelbase;
CDaoRecordset rtoexcelrec;
UpdateData (true);
if(reportlist.GetItemCount() ==0) {AfxMessageBox("列表为空,不能进行操作");return;}
CString xlsname;
xlsname=_T(".\\汇总\\")+rptitlestr +_T(".xls");

if(!CopyFile(".\\report.btl",xlsname,false))//拷贝模板
		              {AfxMessageBox("导出失败！");	// TODO: Add your control no7tification handler code here
		               return;

					  }

rtoexcelbase.Open(xlsname, FALSE,  FALSE, "Excel 4.0;");  
rtoexcelrec.m_pDatabase=(&rtoexcelbase);
rtoexcelrec.Open(dbOpenDynaset, "select * from [Sheet1$]");	

rtoexcelrec.AddNew ();
for(int j=0;j<reportlist.GetHeaderCtrl ()->GetItemCount ();j++)
{ rtoexcelrec.SetFieldValue(j,CString(nametonum[j]));
}
rtoexcelrec.Update ();



double days=0;

for(int i=0;i<reportlist.GetItemCount ();i++)
{rtoexcelrec.AddNew ();
 for(int j=0;j<reportlist.GetHeaderCtrl ()->GetItemCount ();j++)
if(j<2) rtoexcelrec.SetFieldValue(j,CString(reportlist.GetItemText (i,j)));
else rtoexcelrec.SetFieldValue(j,COleVariant(atof(reportlist.GetItemText (i,j))));

 
rtoexcelrec.Update ();

}


rtoexcelrec.Close ();
rtoexcelbase.Close ();
AfxMessageBox("导出成功！该报表文件存放在【汇总】文件夹里面");
}

void reportdlg::OnBnClickedCancel()
{
	// TODO: 在此添加控件通知处理程序代码
	OnCancel();
}

BOOL reportdlg::PreTranslateMessage(MSG* pMsg)
{
	if(pMsg->message ==WM_KEYDOWN)
		switch(pMsg->wParam)
              {case VK_RETURN: switch(focus)
                             
	                          {
							   case 3:OnBnClickedButton2();break;
							   case 4:OnBnClickedButton1(); break;
							   case 5:; break;
							  

							   default:{NextDlgCtrl();return TRUE;}
							  }
                 return TRUE;//对应case VK_RETURN:    	 
				case VK_LEFT:{PrevDlgCtrl();return TRUE;}
				case VK_RIGHT:{NextDlgCtrl();return TRUE;}
               
				case VK_ESCAPE:   return TRUE; 
		      }

	return CDialog::PreTranslateMessage(pMsg);
}

void reportdlg::OnBnSetfocusButton2()
{focus=3;
	// TODO: 在此添加控件通知处理程序代码
}

void reportdlg::OnBnKillfocusButton2()
{focus=0;
	// TODO: 在此添加控件通知处理程序代码
}

void reportdlg::OnBnSetfocusButton1()
{focus=4;
	// TODO: 在此添加控件通知处理程序代码
}

void reportdlg::OnBnKillfocusButton1()
{focus=0;
	// TODO: 在此添加控件通知处理程序代码
}

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -