memodbsdlg.cpp

SQL Server2000 in the database management system, using VC + + development tools to develop a memora

	}
	//获取第nItem列中的信息.
	int id = atoi(m_listMember.GetItemText(nItem,0));
	CString name = m_listMember.GetItemText(nItem,1);
	CString position = m_listMember.GetItemText(nItem,2);
	CString strDate = m_listMember.GetItemText(nItem,3);
	CString gender = m_listMember.GetItemText(nItem,4);
	CString hometown = m_listMember.GetItemText(nItem,5);
	//创建时间变量.
	COleDateTime dtTime(atoi(strDate.Left(4)),atoi(strDate.Mid(5,2)),atoi(strDate.Mid(8,2)),0,0,0);
	//创建成员对话框实例.
	CMemberDlg dlg;
	//给成员对话框的实例的变量赋值.
	dlg.m_dtBirth = dtTime;
	dlg.m_strName = name;
	dlg.m_strPosition = position;
	dlg.m_strGender = gender;
	dlg.m_strHometown = hometown;
	//打开成员对话框,进行修改.
	if(dlg.DoModal() == IDOK){
		//获取修改后的参数.
		
		CString strName = dlg.m_strName;
		CString strPosition = dlg.m_strPosition;
		CString strBirth = dlg.m_dtBirth.Format("%Y-%m-%d");
		CString strGender = dlg.m_strGender;
		CString strHometown = dlg.m_strHometown;
		TRY{
     		CString sql;
			//修改数据库相应的纪录.
			sql.Format("update company_member_tab set member_name = '%s', "
				" member_position = '%s', "
				" birth_date = %s, "
				" member_gender = '%s', "
				" member_hometown = '%s' "
		    	"where member_id = %d",strName,strPosition,strBirth,strGender,strHometown,id);
			TRACE(sql);
		
			m_db.ExecuteSQL(sql);
			//修改列表控件的值.
			m_listMember.SetItemText(nItem,1,strName);
			m_listMember.SetItemText(nItem,2,strPosition);
			m_listMember.SetItemText(nItem,3,strBirth);
			m_listMember.SetItemText(nItem,4,strGender);
			m_listMember.SetItemText(nItem,5,strHometown);
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}	

	
}

void CMemoDBSDlg::OnExit() 
{
	// TODO: Add your control notification handler code here
	CDialog::OnOK();
}

void CMemoDBSDlg::OnBtnDelMember() 
{
	// TODO: Add your control notification handler code here
	//获取要删除的成员记录所在的行.
	int nItem = m_listMember.GetNextItem(-1, LVNI_SELECTED);
	if(nItem == -1){
		AfxMessageBox("没有选择要删除的成员");
		return;
	}
	//获取成员ID.
	int id = atoi(m_listMember.GetItemText(nItem,0));	
	TRY{
		CString sql;
		//从数据库中删除记录.
		sql.Format("DELETE company_member_tab "
			"where member_id = %d",id);
		TRACE(sql);
		m_db.ExecuteSQL(sql);
		//从列表控件中删除该记录.
		m_listMember.DeleteItem(nItem);
	}
	CATCH(CDBException,ex)
	{
		AfxMessageBox (ex->m_strError);
		AfxMessageBox (ex->m_strStateNativeOrigin);		
	}
	AND_CATCH(CException,e)
	{
		TCHAR szError[100];
		e->GetErrorMessage(szError,100);
		AfxMessageBox (szError);
	}
	END_CATCH
}


void CMemoDBSDlg::OnBtnAddType() 
{
	// TODO: Add your control notification handler code here
	//添加备注类型.
	//初始化备注类型对话框实例.
	CMemoTypeDlg dlg;
	if(dlg.DoModal() == IDOK){
		//从对话框中获取新添加的备注类型.
		CString strName = dlg.m_strTypeName;
		TRY{
			CRecordset rs(&m_db);
			//获取最大的类型ID值.
			rs.Open(CRecordset::dynaset, 
				"Select max(TYPE_ID) from MEMO_TYPE_TAB");
			//初始化新类型ID值为1.
			int newTypeID = 1;
			//如果数据库已经有类型记录,则新的类型ID为最大类型ID+1
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					newTypeID = var.m_iVal + 1;
			}
			CString sql;
			//向数据库中添加新的备忘录类型记录.
			sql.Format("Insert into MEMO_TYPE_TAB(TYPE_ID,"
				"TYPE_NAME) "
				"VALUES("
				"%d,'%s')",newTypeID,strName);
			TRACE(sql);
			m_db.ExecuteSQL(sql);			
			//向界面中添加记录行.
			InsertMemoTypeItem(newTypeID,strName);
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}
	
}


void CMemoDBSDlg::OnBtnModType() 
{
	// TODO: Add your control notification handler code here
	//获取要修改的类型记录
	int nItem = m_listType.GetNextItem(-1, LVNI_SELECTED);
	//如果没有选择要修改的纪录,返回.
	if(nItem == -1){
		AfxMessageBox("没有选择要修改的类型");
		return;
	}
    //获取要修改记录的ID和名称.
	int id = atoi(m_listType.GetItemText(nItem,0));
	CString name = m_listType.GetItemText(nItem,1);
	//初始化备注类型对话框.
	CMemoTypeDlg dlg;
	//给对话框的变量赋值.
	dlg.m_strTypeName = name;
	if(dlg.DoModal() == IDOK){
        //获取修改后的数据.
		CString strName = dlg.m_strTypeName;
		TRY{
			CString sql;
			//更新记录.
			sql.Format("UPDATE MEMO_TYPE_TAB SET TYPE_NAME = '%s' "
				" WHERE TYPE_ID = %d",strName,id);
			TRACE(sql);
			m_db.ExecuteSQL(sql);			
			//修改界面的值.
			m_listType.SetItemText(nItem,1,strName);
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}	
}

void CMemoDBSDlg::OnBtnDelType() 
{
	// TODO: Add your control notification handler code here
	//获取要删除的记录所在的行.
	int nItem = m_listType.GetNextItem(-1, LVNI_SELECTED);
	//如果没有选择,返回.
	if(nItem == -1){
		AfxMessageBox("没有选择要删除的类型");
		return;
	}
	//获取要删除的类型ID
	int id = atoi(m_listType.GetItemText(nItem,0));
	TRY{
		CString sql;
		sql.Format("DELETE MEMO_TYPE_TAB "
			"where TYPE_ID = %d",id);
		TRACE(sql);
		//删除类型记录
		m_db.ExecuteSQL(sql);
		//删除界面中的记录.
		m_listType.DeleteItem(nItem);
	}
	CATCH(CDBException,ex)
	{
		AfxMessageBox (ex->m_strError);
		AfxMessageBox (ex->m_strStateNativeOrigin);		
	}
	AND_CATCH(CException,e)
	{
		TCHAR szError[100];
		e->GetErrorMessage(szError,100);
		AfxMessageBox (szError);
	}
	END_CATCH	
}

void CMemoDBSDlg::OnBtnAddMemo() 
{
	// TODO: Add your control notification handler code here
	//初始化别忘录信息对话框.
	CMemoInfoDlg dlg;
	//获取所有的家庭成员的名称.
	for(int i = 0 ; i < m_listMember.GetItemCount() ; i++ )
		dlg.m_strMemberArray.Add(m_listMember.GetItemText(i,1)) ;
	//获取所有的类型信息.
	for(i = 0 ; i < m_listType.GetItemCount() ; i++ )
		dlg.m_strTypeArray.Add(m_listType.GetItemText(i,1)) ;
	//打开对话框,添加新的记录.
	if(dlg.DoModal() == IDOK){
		//从对话框中获取记录值.
		CString strName = dlg.m_strMember;
		CString strType = dlg.m_strMemoType;		    
		CString strHappenDate = dlg.m_oleDate.Format("%Y-%m-%d") 
			               + " " + dlg.m_oleTime.Format("%H:%M:%S");
		CString strOperDate = COleDateTime::GetCurrentTime().Format("%Y-%m-%d %H:%M:%S");
		int isEvent = dlg.m_isEvent;
		CString strCost = dlg.m_strCost;
		CString strMemo = dlg.m_strMemoInfo;
		TRY{	
			CRecordset rs(&m_db);
			CString sql;
			//根据成员姓名获取成员ID值.
			sql.Format("Select MEMBER_ID from COMPANY_MEMBER_TAB where "
				   "MEMBER_NAME = '%s'",strName);
			rs.Open(CRecordset::dynaset,sql);
			int memberID = 1;
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					memberID = var.m_iVal;
			}
	        rs.Close();
			//根据备忘录类型获取备忘录类型ID.
			sql.Format("Select TYPE_ID from MEMO_TYPE_TAB where "
				"TYPE_NAME = '%s'",strType);
			rs.Open(CRecordset::dynaset,sql);
			int typeID = 1;
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					typeID = var.m_iVal;
			}						
			rs.Close();
            //从SEQ_MEMO_ID序列中获取下一个值.这个值就是新的备忘录ID.
		
			rs.Open(CRecordset::dynaset, 
				"Select max(MEMO_ID) from MEMO_INFO_TAB");
			
			int memoID = 1;
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					memoID = var.m_iVal+1;
			}
     
			sql.Format("Insert into MEMO_INFO_TAB(MEMO_ID,"
				"MEMBER_ID,OPER_DATE,HAPPEN_DATE,"
				"IS_BIGEVENT,EVENT_COST,TYPE_ID,MEMO_TEXT) "
				"VALUES("
				"%d,%d,'%s'"
				",'%s',%d,'%s'"
				",%d,'%s')",memoID,memberID,
				strOperDate,strHappenDate,isEvent,strCost,typeID,strMemo);
			TRACE(sql);
			
			m_db.ExecuteSQL(sql);
			//向界面中插入新的纪录.
			InsertMemoInfoItem(memoID,strName,strOperDate,strHappenDate,isEvent,strCost,strType,strMemo) ;
		}
		CATCH(CDBException,ex)
		{
			
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}		
}

void CMemoDBSDlg::OnBtnModMemo() 
{
	// TODO: Add your control notification handler code here
	//获取要修改的备注信息所在的行数.
	int nItem = m_listMemoInfo.GetNextItem(-1, LVNI_SELECTED);
	if(nItem == -1){
		AfxMessageBox("没有选择要修改的成员");
		return;
	}
	//获取备注类型ID.
	int memoID = atoi(m_listMemoInfo.GetItemText(nItem,0));
	//创建对话框实例
	CMemoInfoDlg dlg;
	
	//给对话框赋参数.
	dlg.m_strMember = m_listMemoInfo.GetItemText(nItem,1);
	dlg.m_strCost = m_listMemoInfo.GetItemText(nItem,5);
	dlg.m_strMemoType = m_listMemoInfo.GetItemText(nItem,6);
	dlg.m_oleDate.ParseDateTime(m_listMemoInfo.GetItemText(nItem,2));
	dlg.m_oleTime.ParseDateTime(m_listMemoInfo.GetItemText(nItem,3));
	
	if(m_listMemoInfo.GetItemText(nItem,4).CompareNoCase("是") == 0)
		dlg.m_isEvent = 1;
	else 
		dlg.m_isEvent = 0;
	
	dlg.m_strMemoInfo = m_listMemoInfo.GetItemText(nItem,7);
	for(int i = 0 ; i < m_listMember.GetItemCount() ; i++ )
		dlg.m_strMemberArray.Add(m_listMember.GetItemText(i,1)) ;
	for(i = 0 ; i < m_listType.GetItemCount() ; i++ )
		dlg.m_strTypeArray.Add(m_listType.GetItemText(i,1)) ;
	
	//打开修改的对话框.
	if(dlg.DoModal() == IDOK){
		//获取修改后的记录
		CString strName = dlg.m_strMember;
		CString strType = dlg.m_strMemoType;		    
		CString strHappenDate = dlg.m_oleDate.Format("%Y-%m-%d") 
			+ " " + dlg.m_oleTime.Format("%H:%M:%S");
		CString strOperDate = COleDateTime::GetCurrentTime().Format("%Y-%m-%d %H:%M:%S");
		int isEvent = dlg.m_isEvent;
		CString strCost = dlg.m_strCost;
		CString strMemo = dlg.m_strMemoInfo;
		TRY{
			//为了修改的方便,先删除这条记录,然后再添加记录,备忘录ID值不变.
			CString deletedSql;
			deletedSql.Format("delete from MEMO_INFO_TAB where MEMO_ID = %d",memoID);
			m_db.ExecuteSQL(deletedSql);
			CRecordset rs(&m_db);
			CString sql;
			//根据成员姓名获取成员ID值.
			sql.Format("Select MEMBER_ID from COMPANY_MEMBER_TAB where "
				"MEMBER_NAME = '%s'",strName);
			rs.Open(CRecordset::dynaset,sql);
			int memberID = 1;
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					memberID = var.m_iVal;
			}
			rs.Close();
			//根据备忘录类型获取备忘录类型ID.
			sql.Format("Select TYPE_ID from MEMO_TYPE_TAB where "
				"TYPE_NAME = '%s'",strType);
			rs.Open(CRecordset::dynaset,sql);
			int typeID = 1;
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					typeID = var.m_iVal;
			}						
			rs.Close();	
			//插入记录.备忘录ID不变.
			sql.Format("UPDATE MEMO_INFO_TAB SET "
				"OPER_DATE = '%s',"
				"HAPPEN_DATE = '%s',"
				"IS_BIGEVENT = %d,"
				"EVENT_COST = '%s',"
				"TYPE_ID = %d,"
				"MEMO_TEXT = '%s'"
				"where memo_id = %d",
				strOperDate,strHappenDate,
				isEvent,strCost,typeID,strMemo,memoID);

			TRACE(sql);
			//AfxMessageBox(sql);
			m_db.ExecuteSQL(sql);
			m_listMemoInfo.DeleteItem(nItem);
			//修改界面的值.
			InsertMemoInfoItem(memoID,strName,strOperDate,strHappenDate,isEvent,strCost,strType,strMemo) ;
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}	
}

void CMemoDBSDlg::OnBtnDelMemo() 
{
	// TODO: Add your control notification handler code here
	//获取要删除的备忘录所在的行数.
	int nItem = m_listMemoInfo.GetNextItem(-1, LVNI_SELECTED);
	if(nItem == -1){
		AfxMessageBox("没有选择要删除的备注信息");
		return;
	}
	//获取要删除的备忘录ID.
	int memoID = atoi(m_listMemoInfo.GetItemText(nItem,0));	
	TRY{
		CString sql;
		sql.Format("DELETE MEMO_INFO_TAB "
			"where MEMO_ID = %d",memoID);
		TRACE(sql);
		//删除备忘录ID
		m_db.ExecuteSQL(sql);
		//从界面中删除记录信息.
		m_listMemoInfo.DeleteItem(nItem);
	}
	CATCH(CDBException,ex)
	{
		AfxMessageBox (ex->m_strError);
		AfxMessageBox (ex->m_strStateNativeOrigin);		
	}
	AND_CATCH(CException,e)
	{
		TCHAR szError[100];
		e->GetErrorMessage(szError,100);
		AfxMessageBox (szError);
	}
	END_CATCH	
}

void CMemoDBSDlg::OnBtnQueryBirth() 
{
	// TODO: Add your control notification handler code here
	//创建生日提示对话框实列.
	CBirthQueryDlg dlg;
	//把数据库的指针传入对话框中.
	dlg.m_pDB = &m_db;
	//打开生日提示对话框.
	dlg.DoModal();	
}

void CMemoDBSDlg::OnBtnMemoInfo() 
{
	// TODO: Add your control notification handler code here
    //创建备忘录提示对话框实列.
	CMemoQueryDlg dlg;
	//把数据库的指针传入对话框中.
	dlg.m_pDB = &m_db;
	//打开备忘录提示对话框.
	dlg.DoModal();		
}

void CMemoDBSDlg::OnBtnMemoEvent() 
{
	// TODO: Add your control notification handler code here
	//创建备注信息提示对话框实列.
	CEventDlg dlg;
	//把数据库的指针传入对话框中.
	dlg.m_pDB = &m_db;
	//打开大事记提示对话框.
	dlg.DoModal();		
}
void CMemoDBSDlg::OnCancel() 
{
	// TODO: Add extra cleanup here
//	CDialog::OnCancel();
}