useexceldlg.cpp

用VC写的日记笔小程序,采用的序列化存储.

	CRecordSet * pSet=m_cAdoConnSQL.CreateRecordSet(szSql);
	if(!pSet)
	{
		m_list.AddString("打开表失败");
		CString strInfo;
		strInfo="SQLServer 中 SM$ 表不存在";
		m_list.AddString(strInfo);
	}
	try
	{
		while(!pSet->IsEOF())
		{
//			pSet->GetColVar(szContent,0);
			pSet->GetColVar(&d1,1);
			pSet->GetColVar(&d2,2);
			COleDateTime lTime1(d1);
			COleDateTime lTime2(d2);
			CString szTime1;
			CString szTime2;
			szTime1.Format("%04d-%02d-%02d  %02d:%02d:%02d",lTime1.GetYear(),lTime1.GetMonth(),lTime1.GetDay(),lTime1.GetHour(),lTime1.GetMinute(),lTime1.GetSecond());
			szTime2.Format("%04d-%02d-%02d  %02d:%02d:%02d",lTime2.GetYear(),lTime2.GetMonth(),lTime2.GetDay(),lTime2.GetHour(),lTime2.GetMinute(),lTime2.GetSecond());
			m_list.AddString(szTime1);
			m_list.AddString(szTime2);
			pSet->MoveNext();
		}
	}
	catch(...)
	{
		m_cAdoConnSQL.DestroyRecordSet(pSet);
		m_list.AddString("从excel取值错误");
	}
}

void CUseExcelDlg::OnBtnOpenfile() 
{
/*	UpdateData(TRUE);
	BROWSEINFO bi;                        //BROWSEINFO结构体
	TCHAR Buffer[512]="";
	TCHAR FullPath[512]="";
	TCHAR szFullPath[1024];
	ZeroMemory(szFullPath,sizeof(szFullPath));
	bi.hwndOwner = m_hWnd;                    //m_hWnd你的程序主窗口
	bi.pidlRoot = NULL;
	bi.pszDisplayName = Buffer;          
	bi.lpszTitle = "请选择程序所在的文件夹";            //弹出的窗口的文字提示
	//只返回目录。其他标志看MSDN
	bi.ulFlags = BIF_RETURNONLYFSDIRS;
	
	bi.lpfn = NULL;                        //回调函数，有时很有用
	bi.lParam = 0;
	bi.iImage = 0;
	ITEMIDLIST* pidl = SHBrowseForFolder (&bi);        //显示弹出窗口，ITEMIDLIST很重要
	SHGetPathFromIDList (pidl,FullPath);        //在ITEMIDLIST中得到目录名的整个路径
	m_strFilePath=FullPath;
	UpdateData(FALSE);
	*/
	char szFilters[]="_T(MyType Files (*.xls)|*.xls|All Files (*.*)|*.*||)";

//    CFileDialog fileDlg (TRUE, _T("my"), _T("*.my"),
//    OFN_FILEMUSTEXIST| szFilters, this);
  
	CFileDialog dlg(TRUE, NULL, NULL, OFN_FILEMUSTEXIST,szFilters,this);//,"xls");
	if(dlg.DoModal()==IDOK){
		m_strPath=dlg.GetPathName();
		m_strFilePath = dlg.GetPathName();
		UpdateData(FALSE);
	}
}

BOOL CUseExcelDlg::OnMoveData_Star()
{
//	char szSql[2048];
	CString strInfo;
//	memset(szSql,0,sizeof(szSql));
	m_cAdoConnSQL.BeginTrans();
//	for(int i=0;i<2;i++){
//		sprintf(szSql,"select * from t_info");
	CRecordSet * pSet_Info=m_cAdoConnExcel.CreateRecordSet("select * from [t_info$]");
	if(!pSet_Info)
	{
		m_list.AddString("打开表失败");
		CString strInfo;
		strInfo.Format("Excel中 t_info 表不存在");
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
//		m_cAdoConnSQL.RollBackTrans();
//		m_cAdoConnExcel.CloseDb();
//		m_cAdoConnSQL.CloseDb();
	}
	CRecordSet * pSet_Topic=m_cAdoConnExcel.CreateRecordSet("select * from [t_topic$]");
	if(!pSet_Topic)
	{
		m_list.AddString("打开表失败");
		CString strInfo;
		strInfo.Format("Excel中 t_info 表不存在");
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
//		m_cAdoConnSQL.RollBackTrans();
//		m_cAdoConnExcel.CloseDb();
//		m_cAdoConnSQL.CloseDb();
	}
	try
	{
		int ret;
		char szSql[2048];	memset(szSql,0,sizeof(szSql));
		strInfo.Format("正在移动 [t_info$] —— t_info...");
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		sprintf(szSql,"delete from t_info where type!=8 and subtype!=8");
		ret=m_cAdoConnSQL.Execute(szSql);
//		删除同种类型的数据，在插入新的
		if(ret!=0){
			strInfo.Format("从表t_info删除数据失败,语句为：%s",szSql);
			m_lListIndex=m_list.AddString(strInfo);
			m_list.SetCurSel(m_lListIndex);
			return FALSE;
		}
		long iCount=0;
		while(!pSet_Info->IsEOF())
		{
			int iType,iSubType,iSubIndex;
			char szContext[2048];	memset(szContext,0,sizeof(szContext));
//			char szNote[50];		memset(szNote,0,sizeof(szNote));
			pSet_Info->GetColVar(&iType,1);
			pSet_Info->GetColVar(&iSubType,2);
			pSet_Info->GetColVar(&iSubIndex,3);
			pSet_Info->GetColVar(szContext,4);
			if(!strcmp(szContext,"????")){
				break;
			}

//			pSet_Info->GetColVar(szNote,5);
            OnFilter(szContext);
//			OnFilter(szNote);
/*			sprintf(szSql,"delete from t_info where type=%d and subtype=%d and subIndex=%d",
				iType,iSubType,iSubIndex);
			ret=m_cAdoConnSQL.Execute(szSql);
//			删除同种类型的数据，在插入新的
			if(ret!=0){
				strInfo.Format("从表t_info删除数据失败,语句为：%s",szSql);
				m_lListIndex=m_list.AddString(strInfo);
				m_list.SetCurSel(m_lListIndex);
				break;
			}
*/			sprintf(szSql,"insert into t_info(type,subType,subIndex,context) values(%d,%d,%d,'%s')",
				iType,iSubType,iSubIndex,szContext);
			ret=m_cAdoConnSQL.Execute(szSql);
			if(ret!=0){
				strInfo.Format("向表t_info中插入数据失败,语句为：%s",szSql);
				m_lListIndex=m_list.AddString(strInfo);
				m_list.SetCurSel(m_lListIndex);
				break;
			}
			if(iCount==0){
				sprintf(szSql,"if(not exists(select id from t_task where type=%d and subtype=%d)) \
					insert into t_task(type,subtype,subindex,bsend) values(%d,%d,0,1)\
					else Update t_task set bSend=1 where type = %d and subType = %d",
					iType,iSubType,iType,iSubType,iType,iSubType);
/*				sprintf(szSql,"if(not exists(select id from t_task where type=%d and subtype=%d)) \
					insert into t_task(type,subtype,subindex,bsend) values(%d,%d,0,1)",
					iType,iSubType,iType,iSubType);
*/
				ret=m_cAdoConnSQL.Execute(szSql);
				if(ret!=0){
					strInfo.Format("修改任务表t_task失败,语句为：%s",szSql);
					m_lListIndex=m_list.AddString(strInfo);
					m_list.SetCurSel(m_lListIndex);
					break;
				}
			}
			pSet_Info->MoveNext();
			iCount++;
		}
		strInfo.Format("共移动了 %d 条纪录.",iCount);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		m_cAdoConnExcel.DestroyRecordSet(pSet_Info);
		strInfo.Format("正在移动 [t_topic$] —— t_Topic...");
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		iCount=0;
		while(!pSet_Topic->IsEOF())
		{
			int iType,iSubType,iSubIndex;
			char szContext[2048];	memset(szContext,0,sizeof(szContext));
//			char szNote[50];		memset(szNote,0,sizeof(szNote));
			char szSql[2048];	memset(szSql,0,sizeof(szSql));
			pSet_Topic->GetColVar(&iType,1);
			pSet_Topic->GetColVar(&iSubType,2);
			pSet_Topic->GetColVar(szContext,3);
			if(!strcmp(szContext,"????")){
				break;
			}
//			pSet_Topic->GetColVar(szNote,4);
            OnFilter(szContext);
//			OnFilter(szNote);
			sprintf(szSql,"select * from t_Topic where type=%d and subtype=%d",iType,iSubType);
			CRecordSet * pSet_Star_SQL = m_cAdoConnSQL.CreateRecordSet(szSql);
			int ret;
			if(!pSet_Star_SQL->IsEOF())//库中已经有了此种类型的记录，更改记录内容
			{
				sprintf(szSql,"update t_Topic set context='%s' where type=%d and subType=%d",
					szContext,iType,iSubType);
//				ret=m_cAdoConnSQL.Execute(szSql);

			}
			else//如果库中没有就插入一条
			{
/*				CRecordSet * pSet_Channel=m_cAdoConnSQL.CreateRecordSet("select channelid,yybz from t_channel group by channelid,yybz");;
				while(!pSet_Channel){
					int iChannelid,iYybz;
					pSet_Channel->GetColVar(&iChannelid,0);
					pSet_Channel->GetColVar(&iYybz,1);
					sprintf(szSql,"insert into t_Topic(type,subType,subindex,icount,context,channelid,yybz) values(%d,%d,0,1,'%s',%d,%d)",
						iType,iSubType,szContext,iChannelid,iYybz);
					ret=m_cAdoConnSQL.Execute(szSql);
					pSet_Channel->MoveNext();
				}
				m_cAdoConnSQL.DestroyRecordSet(pSet_Channel);
*/				sprintf(szSql,"insert into t_Topic(type,subType,context) values(%d,%d,'%s')",
						iType,iSubType,szContext);
			}
			ret=m_cAdoConnSQL.Execute(szSql);
			if(ret!=0){
				strInfo.Format("修改表t_Topic中标题数据失败,语句为：%s",szSql);
				m_lListIndex=m_list.AddString(strInfo);
				m_list.SetCurSel(m_lListIndex);
				break;
			}
			pSet_Topic->MoveNext();
			iCount++;
		}
		strInfo.Format("共移动了 %d 条纪录.",iCount);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		m_cAdoConnExcel.DestroyRecordSet(pSet_Topic);
		m_cAdoConnSQL.Execute("update t_orderindex set pos=0");
	}
	catch(...)
	{
		m_cAdoConnSQL.RollBackTrans();
		m_cAdoConnExcel.DestroyRecordSet(pSet_Info);
		m_cAdoConnExcel.DestroyRecordSet(pSet_Topic);
//		m_cAdoConnExcel.CloseDb();
//		m_cAdoConnSQL.CloseDb();
		m_lListIndex=m_list.AddString("从excel取值错误");
		m_list.SetCurSel(m_lListIndex);
		return FALSE;
	}
	m_cAdoConnSQL.CommitTrans();
//	m_cAdoConnSQL.CloseDb();
//	m_cAdoConnExcel.CloseDb();
	strInfo="数据移动结束。";
    m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
	char szSql[512];
	memset(szSql,0,sizeof(szSql));
	sprintf(szSql,"update t_user set bsend= 0");
	int ret=m_cAdoConnSQL.Execute(szSql);
	if(ret!=0){
		strInfo.Format("修改用户表t_user失败，语句为：%s",szSql);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return FALSE;
	}
	strInfo.Format("修改了t_user,置发送状态成功");
	m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
/*	memset(szSql,0,sizeof(szSql));
	sprintf(szSql,"Update t_task set bSend = 0 where type=1 and subtype=1");
	ret=m_cAdoConnSQL.Execute(szSql);
	if(ret!=0){
		strInfo.Format("修改任务表t_task失败，语句为：%s",szSql);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return FALSE;
	}
	strInfo="更新数据后，修改要处理任务的状态为没有处理。";
	m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
*/	return TRUE;
}

void CUseExcelDlg::OnRadioSedimikey() 
{
	// TODO: Add your control notification handler code here
	m_iSelect=0;
}

void CUseExcelDlg::OnRadioStarfacetoface() 
{
	// TODO: Add your control notification handler code here
	m_iSelect=1;
}

BOOL CUseExcelDlg::OnBeginMove()
{
	((CButton*)GetDlgItem(IDOK))->EnableWindow(FALSE);
	CTime t=CTime::GetCurrentTime();
	int iDay=t.GetDay();
	int iDayOfWeek=t.GetDayOfWeek();
	int iHour=t.GetHour();
	CString strInfo;
	strInfo.Format("今天时间为：%04d-%02d-%02d  %02d:%02d   周 %d ,移动数据,每周一移动数据",t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute(),iDayOfWeek-1==0 ? 7 : iDayOfWeek-1);
	m_lListIndex=m_list.AddString(strInfo);
	m_lListIndex=m_list.AddString("开始移动数据，祝你成功！");
	m_list.SetCurSel(m_lListIndex);
	int iDBRet=OnOpenDB(m_iSelect);
	if(iDBRet==DBERR_EXCEL){
		m_lListIndex=m_list.AddString("打开excel库失败");
		m_list.SetCurSel(m_lListIndex);
		return FALSE;
	}
	else if(iDBRet==DBERR_SQLSERVER){
		m_lListIndex=m_list.AddString("打开SQL Server数据库失败");
		m_list.SetCurSel(m_lListIndex);
		return FALSE;
	}
	else{
		m_lListIndex=m_list.AddString("成功打开数据库！");
		m_list.SetCurSel(m_lListIndex);
	}
	BOOL bRet = TRUE;
	if(m_iSelect == 0)
		bRet=OnMoveData_Sedi();
	else bRet=OnMoveData_Star();
	if(bRet==FALSE){ 
		m_lListIndex=m_list.AddString("移动数据失败！");
		m_list.SetCurSel(m_lListIndex);
	}
	else{
		CString strInfo1;
		strInfo1.Format("时间为：%04d-%02d-%02d  %02d:%02d ",t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute());
		m_list.AddString(strInfo1);
		m_lListIndex=m_list.AddString("恭喜你，移动数据成功！");
		m_list.SetCurSel(m_lListIndex);
		m_iDay=iDay;
	}
    
	OnCloseDB();
	m_lListIndex=m_list.AddString("成功关闭数据库！");
	m_list.SetCurSel(m_lListIndex);
	((CButton*)GetDlgItem(IDOK))->EnableWindow(TRUE);

}

void CUseExcelDlg::OnQiangpiao() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	char szSql[512];
	char szQiangpiao[512];
	int ret=-1;
	CString strInfo;
	memset(szSql,0,sizeof(szSql));
	memset(szQiangpiao,0,sizeof(szQiangpiao));
	strcpy(szQiangpiao,m_strQiangpiao);
	char szOpenDb[512];
	memset(szOpenDb,0,sizeof(szOpenDb));
	HRESULT hr;
	OnFilter(szQiangpiao);
	sprintf(szOpenDb,"Provider=SQLOLEDB.1;Server=%s;User ID=%s;Password=%s;Initial Catalog=%s",m_szDbServer_Star,m_szDbUserName_Star,m_szDbPwd_Star,m_szDbName_Star);
	hr=m_cAdoConnSQL.OpenDb(szOpenDb);//连接SQL Server数据库
	if(!SUCCEEDED(hr))
	{
		strInfo.Format("下发抢票信息时数据库没有打开,语句为：%s",szOpenDb);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return ;
	}
//修改表t_Info中抢票信息
	sprintf(szSql,"update t_info set context= '%s' where type=8 and subtype=8",m_strQiangpiao);
	ret=m_cAdoConnSQL.Execute(szSql);
	if(ret!=0){
		strInfo.Format("修改表t_Info中抢票信息失败,语句为：%s",szSql);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return ;
	}
	memset(szSql,0,sizeof(szSql));
//创建下发抢票信息任务
	sprintf(szSql,"update t_user set bsend= 0");
	ret=m_cAdoConnSQL.Execute(szSql);
	if(ret!=0){
		strInfo.Format("修改用户表t_user失败，语句为：%s",szSql);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return ;
	}
	strInfo.Format("修改了t_user,置发送状态成功");
	m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
	memset(szSql,0,sizeof(szSql));
	sprintf(szSql,"update t_Task set bsend= 1 where type=8 and subtype=8");
	ret=m_cAdoConnSQL.Execute(szSql);
	if(ret!=0){
		strInfo.Format("修改任务表t_Task失败,创建下发抢票信息失败，语句为：%s",szSql);
		m_lListIndex=m_list.AddString(strInfo);
		m_list.SetCurSel(m_lListIndex);
		return ;
	}
	strInfo.Format("修改了t_info,t_task 创建下发抢票信息任务成功");
	m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
	m_cAdoConnSQL.CloseDb();
	return ;
}

void CUseExcelDlg::OnCancel() 
{
	// TODO: Add extra cleanup here
	
	CDialog::OnCancel();
}