eq_statistic.cpp

自己编的设备管理系统

// Eq_STATISTIC.cpp : implementation file
//

#include "stdafx.h"
#include "eq_manager_sys.h"
#include "Eq_STATISTIC.h"

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

/////////////////////////////////////////////////////////////////////////////
// CEq_STATISTIC dialog


CEq_STATISTIC::CEq_STATISTIC(CWnd* pParent /*=NULL*/)
	: CDialog(CEq_STATISTIC::IDD, pParent)
{
	//{{AFX_DATA_INIT(CEq_STATISTIC)
	m_com2 = _T("");
	m_edit1 = _T("");
	m_com4 = _T("");
	m_edit_3 = _T("");
	m_edit_2 = _T("");
	//}}AFX_DATA_INIT
}


void CEq_STATISTIC::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CEq_STATISTIC)
	DDX_Control(pDX, IDC_EDIT2, m_edit2);
	DDX_Control(pDX, IDC_CHECK2, m_check2);
	DDX_Control(pDX, IDC_EDIT3, m_edit3);
	DDX_Control(pDX, IDC_CHECK1, m_check1);
	DDX_Control(pDX, IDC_COMBO4, m_combo4);
	DDX_Control(pDX, IDC_LIST1, m_list1);
	DDX_Control(pDX, IDC_RADIO2, m_radio2);
	DDX_Control(pDX, IDC_RADIO1, m_radio1);
	DDX_Control(pDX, IDC_COMBO2, m_combo2);
	DDX_Control(pDX, IDC_COMBO1, m_combo1);
	DDX_CBString(pDX, IDC_COMBO2, m_com2);
	DDX_Text(pDX, IDC_EDIT1, m_edit1);
	DDX_CBString(pDX, IDC_COMBO4, m_com4);
	DDX_Text(pDX, IDC_EDIT3, m_edit_3);
	DDX_Text(pDX, IDC_EDIT2, m_edit_2);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CEq_STATISTIC, CDialog)
	//{{AFX_MSG_MAP(CEq_STATISTIC)
	ON_BN_CLICKED(IDC_RADIO1, OnRadio1)
	ON_BN_CLICKED(IDC_RADIO2, OnRadio2)
	ON_CBN_SELCHANGE(IDC_COMBO1, OnSelchangeCombo1)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	ON_BN_CLICKED(IDC_CHECK1, OnCheck1)
	ON_BN_CLICKED(IDC_CHECK2, OnCheck2)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CEq_STATISTIC message handlers

BOOL CEq_STATISTIC::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	InitControl();//初始化control列
	ConnectDB();
	m_radio1.SetCheck (1);
	m_combo1.SetCurSel (0);
	m_check1.SetCheck (0);
	m_edit3.SetWindowText ("D:\\xzc.xls");
	m_edit2.SetWindowText ("D:\\xzc.txt");
	SYSTEMTIME st;
	CString date;
	int month;
	::GetSystemTime(&st);
	month=st.wMonth ;
	while(month>1)
	{
		month--;
		if(month<10)
			date.Format("0%d月",month);
		else
			date.Format("%d月",month);
		m_combo4.AddString (date);
	}
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CEq_STATISTIC::InitControl()//添加控件显示列
{
	DWORD dwExstyle=LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES|LVS_EX_HEADERDRAGDROP|
		LVS_EX_ONECLICKACTIVATE|LVS_EX_UNDERLINEHOT;
	m_list1.SetExtendedStyle(dwExstyle);
	
	//初试化设备信息表列表框控件
	m_list1.InsertColumn(0,"部门编号",LVCFMT_CENTER,80);
	m_list1.InsertColumn(1,"设备编号",LVCFMT_CENTER,60);
	m_list1.InsertColumn(2,"设备名称",LVCFMT_CENTER,80);
	m_list1.InsertColumn(3,"类别编号",LVCFMT_CENTER,80);
	m_list1.InsertColumn(4,"规格型号",LVCFMT_CENTER,200);
	m_list1.InsertColumn(5,"设备数量",LVCFMT_CENTER,60);
	m_list1.InsertColumn(6,"设备状态",LVCFMT_CENTER,60);
	m_list1.InsertColumn(7,"购入日期",LVCFMT_CENTER,80);
	m_list1.InsertColumn(8,"安装日期",LVCFMT_CENTER,80);
	m_list1.InsertColumn(9,"原值",LVCFMT_CENTER,70);
	m_list1.InsertColumn(10,"安装地点",LVCFMT_CENTER,120);
	m_list1.InsertColumn(11,"生产厂家",LVCFMT_CENTER,150);
	m_list1.InsertColumn(12,"使用日期",LVCFMT_CENTER,80);
	m_list1.InsertColumn(13,"使用年限",LVCFMT_CENTER,60);
	m_list1.InsertColumn(14,"残值",LVCFMT_CENTER,80);
	m_list1.InsertColumn(15,"月折旧金额",LVCFMT_CENTER,80);
	m_list1.InsertColumn(16,"备注",LVCFMT_CENTER,200);

	m_list1.SetBkColor(RGB(255,255,200));
	m_list1.SetTextBkColor(RGB(255,221,221));

	UpdateData(FALSE);
}

void CEq_STATISTIC::ConnectDB()//连接数据库函数
{
	TRY
	{
		 m_db.OpenEx("DSN=ORADB;UID=eq_system;PWD=eq_system",CDatabase::noOdbcDialog);
	}
	CATCH(CDBException,ex)
	{
		AfxMessageBox("问题出在连接数据库");
		AfxMessageBox(ex->m_strError);
		AfxMessageBox(ex->m_strStateNativeOrigin);
	}
	AND_CATCH(CMemoryException,pEx)
	{
		pEx->ReportError();
		AfxMessageBox("memory exception");
	}
	AND_CATCH(CException,e)
	{
		TCHAR szError[100];
		e->GetErrorMessage(szError,100);
		AfxMessageBox(szError);
	}
	END_CATCH

}

void CEq_STATISTIC::InsertEq(CString id,CString name,CString eq_class_id,CString eq_type,int eq_amount,int eq_state,CString pur_date,CString install_date,CString dep_id,int eq_val,CString fix_place,CString manfac,CString use_date,int fix_year,int rem_val,int mon_val,CString remark)
{
	int index=m_list1.GetItemCount();
	CString temp,temp1,temp2,temp3,temp4,temp5,temp6;
	temp.Format("%d",eq_amount);
	temp1.Format("%d元",eq_val);
	temp2.Format("%d年",fix_year);
	temp3.Format("%d元",rem_val);
	temp4.Format("%d元",mon_val);
	if(eq_state==1)
	{temp5.Format("安装");}
	else if(eq_state==2)
	{temp5.Format("使用");}
	else if(eq_state==3)
	{temp5.Format("维修");}
	else if(eq_state==4)
	{temp5.Format("报废");}
	LV_ITEM lvItem;
	lvItem.mask=LVIF_TEXT;
	lvItem.iItem=index;// 行数
	lvItem.iSubItem=0;
	lvItem.pszText=(char*)(LPCTSTR)dep_id;//第一列
	//在最后一行插入记录直
	m_list1.InsertItem(&lvItem);
	//设置该行的其他列的直
	m_list1.SetItemText(index,1,id);
	m_list1.SetItemText(index,2,name);
	m_list1.SetItemText(index,3,eq_class_id);
	m_list1.SetItemText(index,4,eq_type);
	m_list1.SetItemText(index,5,temp);
	m_list1.SetItemText(index,6,temp5);
	m_list1.SetItemText(index,7,pur_date);
	m_list1.SetItemText(index,8,install_date);
	m_list1.SetItemText(index,9,temp1);
	m_list1.SetItemText(index,10,fix_place);
	m_list1.SetItemText(index,11,manfac);
	m_list1.SetItemText(index,12,use_date);
	m_list1.SetItemText(index,13,temp2);
	m_list1.SetItemText(index,14,temp3);
	m_list1.SetItemText(index,15,temp4);
	m_list1.SetItemText(index,16,remark);

	if(m_check1.GetCheck ())
	{
		CDatabase database;
        CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; 
        CString sExcelFile;  
		UpdateData(TRUE);
		for(int i=0;i<=m_edit_3.GetLength ();i++)
		{
			if(m_edit_3.Mid (i,1)=="\\")
			{
				sExcelFile=sExcelFile+m_edit_3.Mid (i,1)+"\\";
			}
			else
				sExcelFile=sExcelFile+m_edit_3.Mid (i,1);
		}
        CString sSql;sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);
        if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
		{
			sSql.Format ( "INSERT INTO statistic (部门编号,设备编号,设备名称,类别编号,规格型号,设备数量,设备状态号,购入日期,安装日期,原值,安装地点,生产厂家,使用日期,使用年限,残值,月折旧金额,备注) VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",dep_id,id,name,eq_class_id,eq_type,temp,temp5,pur_date,install_date,temp1,fix_place,manfac,use_date,temp2,temp3,temp4,remark);
            database.ExecuteSQL(sSql);
		}
		database.Close();
	}
}

void CEq_STATISTIC::OnRadio1() 
{
	// TODO: Add your control notification handler code here
	m_radio2.SetCheck (0);
	m_combo4.ResetContent ();
	SYSTEMTIME st;
	CString date;
	int month;
	::GetSystemTime(&st);
	month=st.wMonth ;
	while(month>1)
	{
		month--;
		if(month<10)
			date.Format("0%d月",month);
		else
			date.Format("%d月",month);
		m_combo4.AddString (date);
	}
	UpdateData(FALSE);
	m_combo4.SetCurSel (0);
	UpdateData(FALSE);
	if(!UpdateData())
	    return;
}

void CEq_STATISTIC::OnRadio2() 
{
	// TODO: Add your control notification handler code here
	m_radio1.SetCheck (0);
	m_combo4.ResetContent ();	
	SYSTEMTIME st;
	CString date;
	int year,i=5;
	::GetSystemTime(&st);
	year=st.wYear;
	for(;i>0;i--)
	{
		year--;
		date.Format("%d",year);
		m_combo4.AddString (date);
	}
	UpdateData(FALSE);
	m_combo4.SetCurSel (0);
	UpdateData(FALSE);
	if(!UpdateData())
	    return;
}

void CEq_STATISTIC::OnSelchangeCombo1() 
{
	// TODO: Add your control notification handler code here
	CRecordset rs(&m_db);
	rs.Open(CRecordset::dynaset,"select * from eq_info");
	if(m_combo1.GetCurSel ()==0)//企业级
	{
		m_combo2.ResetContent ();
	}
	else if(m_combo1.GetCurSel ()==1)//部门级
	{
		CString temp[100];
	    int count=0;
		m_combo2.ResetContent ();
		while(!rs.IsEOF())
		{
			CString dep_id;
			int j=0,flag=0;
			rs.GetFieldValue((short)8,dep_id);
			//向列表框中添加所有设备类别
			while(j<=count)
			{
				if(temp[j]==dep_id)
				{
					flag=1;
					break;
				}
				j++;
			}
			if(flag==0)
			{
				temp[count]=dep_id;
				count++;
				m_combo2.AddString(dep_id);
			}
			rs.MoveNext();
		}
	}
	rs.Close();
	//更新数据
	m_combo2.SetCurSel(0);
	if(!UpdateData())
	    return;
}

void CEq_STATISTIC::OnOK() 
{
	// TODO: Add extra validation here
	
	//CDialog::OnOK();
	UpdateData(TRUE);
	CRecordset rs(&m_db);
	CString id,name,eq_class_id,eq_type,pur_date,install_date,dep_id,fix_place,manfac,use_date,remark;
	int eq_amount,eq_state,fix_year,rem_val,mon_val,eq_val;
	CDBVariant var;
	int temp_eq_state_1=0,temp_eq_state_2=0,temp_eq_state_3=0,temp_eq_state_4=0;
	int temp_count_eq=0,temp_count_eq_num=1;
	int sum_temp_eq_state_1=0,sum_temp_eq_state_2=0,sum_temp_eq_state_3=0,sum_temp_eq_state_4=0;
	int sum_temp_count_eq=0;
	CString temp_count[100];
	CString edit_temp;
	CString temp_eq_state;
	rs.Open(CRecordset::dynaset,"select * from eq_info");

	SYSTEMTIME st;
	int syear;
	::GetSystemTime(&st);
	syear=st.wYear ;
	CString mmonth,myear;
	int pur_num=0,install_num=0,use_num=0;
	int sum_pur_num=0,sum_install_num=0,sum_use_num=0;
	CString temp_pur_num,temp_install_num,temp_use_num;
	if(m_radio1.GetCheck ())
	{
		mmonth=m_com4;
		mmonth=mmonth.Mid (0,2);
		myear.Format ("%d",syear);
		myear=myear.Mid (0,4);
	}
	else if(m_radio2.GetCheck ())
	{
		myear=m_com4;
		myear=myear.Mid (0,4);
	}

	
	if(m_check1.GetCheck ())
	{
		UpdateData(TRUE);
		CDatabase database;
        CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; 
        CString sExcelFile;// = "D:\\xzc.xls";               
        CString sSql;
		for(int i=0;i<=m_edit_3.GetLength ();i++)
		{
			if(m_edit_3.Mid (i,1)=="\\")
			{