dbinterface.cpp

视频播放控制器程序

}
NameListShowItem *CDBInterface::GetScheName(int n)
{
	CString strSql; 
	strSql.Format("select sche_id,name_type,name_id from tbl_sche_namelist where sche_id=%d", n);
	if ( !m_pQuery->execute((LPTSTR)(LPCTSTR)strSql) ){
		return FALSE;
	}
	sql_result_c *pProfSet = NULL;
	pProfSet = m_pQuery->store();
	if ( pProfSet == NULL ) return FALSE;

	NameListShowItem *pRt = NULL, *pItem,*pBack = NULL;
	sql_row_c row_prof;
	int nProfCount = (int)(pProfSet->n_rows());
	for ( int i=0; i<nProfCount; i++ )
	{
		row_prof = pProfSet->fetch_row();
		pItem = new NameListShowItem;
		pItem->sche_id = (row_prof[0]);
		pItem->nametype = (row_prof[1]);
		pItem->nameid = (LPCTSTR)(row_prof[2]);
		if ( pRt == NULL ) {
			pRt = pItem;
		}
		pItem->pnext = NULL;
		
		if ( pBack ) {
			pBack->pnext = pItem;
		}
		pBack = pItem;
	}
	m_pQuery->free_result( pProfSet );
	return pRt;

}

VideoListItem* CDBInterface::GetVideoList()
{
	CString strSql = "select vidsrc_id,vidsrc_type,vidsrc_name,\
		vidsrc_path,vidsrc_formatfile from tbl_vidsrc";
	if ( !m_pQuery->execute((LPTSTR)(LPCTSTR)strSql) ){
		return FALSE;
	}
	sql_result_c *pVidSet = NULL;
	pVidSet = m_pQuery->store();
	if ( pVidSet == NULL ) return FALSE;

	VideoListItem *pRt = NULL, *pItem,*pBack = NULL;
	sql_row_c row_vid;
	int nVidCount = (int)(pVidSet->n_rows());
	for ( int i=0; i<nVidCount; i++ )
	{
		row_vid = pVidSet->fetch_row();
		pItem = new VideoListItem;
		pItem->VideoID = (row_vid[0]);
		pItem->VideoType = (row_vid[1]);
		pItem->VideoName = (LPCTSTR)(row_vid[2]);
		pItem->VideoPath = (LPCTSTR)(row_vid[3]);
		pItem->VideoFormatFlie = (LPCTSTR)(row_vid[4]);
		if ( pRt == NULL ) {
			pRt = pItem;
		}
		pItem->pNext = NULL;
		
		if ( pBack ) {
			pBack->pNext = pItem;
		}
		pBack = pItem;
	}
	m_pQuery->free_result( pVidSet );
	return pRt;
}

BOOL CDBInterface::AddSchedule( CSchedule *pSche )
{
	CString strSt = pSche->m_nStartime.Format("%Y-%m-%d %H:%M:%S");
	CString strEt = pSche->m_nStoptime.Format("%Y-%m-%d %H:%M:%S");
	CString strSql;
	// First, insert to the tbl_sche
	strSql.Format( "values(0,'%d','%s','%s','%s','%s','%s','%d','%d')",
		pSche->m_nScheType, pSche->m_strName, pSche->m_strDesc, 
		strSt, strEt, pSche->m_strMCastIP, pSche->m_nMCastPort,
		pSche->m_nLastUpdate );
	strSql = "insert into tbl_sche(sche_id,sche_type,sche_name,sche_desc,\
		sche_starttime,sche_stoptime,sche_mip,sche_mport,sche_lastupdate) " + strSql;
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
		return FALSE;
	}

	// Then get the sche_id by auto increasement
	strSql.Format( "select sche_id from tbl_sche where sche_name='%s' \
		and sche_starttime='%s' and sche_stoptime='%s'",
		pSche->m_strName, strSt, strEt );
	if ( !m_pQuery->execute((LPTSTR)(LPCTSTR)strSql) ){
		AfxMessageBox( "Error CDBInterface::AddSchedule" );
		return FALSE;
	}
	sql_result_c *pSet = NULL;
	pSet = m_pQuery->store();
	if ( pSet == NULL )
	{
		AfxMessageBox( "Error CDBInterface::AddSchedule" );
		return FALSE;
	}
	sql_row_c row_c;
	int nCount = (int)(pSet->n_rows());
	if ( nCount > 0 )
	{
		row_c = pSet->fetch_row();
		pSche->m_nScheID = atoi(row_c[0]);
	}
	m_pQuery->free_result( pSet );

	// After that, put the playist into DB
	CPlayItem *pItem = pSche->m_pSeqPlayList_H;
	while ( pItem )
	{
		strSql.Format( "values(0,'%d','%d','%d','%d','%d','%d')",
			pItem->m_nPlayItemID, pSche->m_nScheID, pItem->m_nVidSrcID,
			pItem->m_nPlayOpt, pItem->m_nPlayValue, pItem->m_nRepeatCount );
		strSql = "insert into tbl_scheplay(playlist_id,sche_id,vidsrc_id,play_opt,\
			play_value,play_repeatcount) " + strSql;
		if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
			return FALSE;
		}
		pItem = pItem->m_pNext;
	}
	// 将词汇表的内容加进数据库
	CWordManage *pIn = pSche->m_pSetWordList_H;
	while (pIn)
	{
		int type;
		type = atoi((char*)(LPCTSTR)pIn->type);
		strSql.Format( "values('%d','%d','%s')",pSche->m_nScheID,type,pIn->word);
		strSql = "insert into tbl_sche_wordlist(sche_id,word_type,word_content)" + strSql;
		if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
			return FALSE;
		}
		pIn = pIn->pNext;
	}
	// 将设置好的名单加入数据库中
	CNameManage *pInName = pSche->m_pSetNameList_H;
	while (pInName)
	{
		int type;
		type = atoi((char*)(LPCTSTR)pInName->type);
		strSql.Format( "values('%d','%d','%s')",pSche->m_nScheID,type,pInName->name);
		strSql = "insert into tbl_sche_namelist(sche_id,name_type,name_id)" + strSql;
		if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
			return FALSE;
		}
		pInName = pInName->pNext;
	}
	return TRUE;
}

BOOL CDBInterface::AddVidsrc(VideoListItem *pItem)
{
	// insert
	CString strSql;
	strSql.Format( "values(0,'%d','%s','%s','%s')",
	pItem->VideoType,pItem->VideoName,pItem->VideoPath,pItem->VideoFormatFlie);
	strSql = "insert into tbl_vidsrc(vidsrc_id,vidsrc_type,vidsrc_name,vidsrc_path,\
			  vidsrc_formatfile) " + strSql;
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
		AfxMessageBox( "Error CDBInterface::AddVideoSource" );
		return FALSE;
	}
	return TRUE;
	// get
	strSql.Format( "select vidsrc_id from tbl_vidsrc where vidsrc_name='%s' \
		and vidsrc_path='%s'",
		pItem->VideoName, pItem->VideoPath );
	if ( !m_pQuery->execute((LPTSTR)(LPCTSTR)strSql) ){
		AfxMessageBox( "Error CDBInterface::AddVideoSource" );
		return FALSE;
	}
	sql_result_c *pSet = NULL;
	pSet = m_pQuery->store();
	if ( pSet == NULL )
	{
		AfxMessageBox( "Error CDBInterface::AddVideoSource" );
		return FALSE;
	}
	sql_row_c row_c;
	int nCount = (int)(pSet->n_rows());
	if ( nCount > 0 )
	{
		row_c = pSet->fetch_row();
		pItem->VideoID = atoi(row_c[0]);
	}
	m_pQuery->free_result( pSet );

	
	return TRUE;
}


BOOL CDBInterface::RemoveSchedule( int nScheID )
{
	CString strSql;
	// First, insert to the tbl_sche
	strSql.Format( "delete from tbl_sche where sche_id=%d", nScheID );
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
		return FALSE;
	}
	return TRUE;
}

BOOL CDBInterface::AddExpert( ExpertListItem *pItem )
{
	CString strSql;
	strSql.Format( "values('%s','%s','%s','%d','%d')",
	pItem->ExpertID,pItem->ExpertName,pItem->ExpertPW,pItem->ExpertAuthority,pItem->ExpertRank);
	strSql = "insert into tbl_prof(prof_id,prof_name,prof_pswd,\
			  prof_auth,prof_rank) " + strSql;
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
		AfxMessageBox( "Error CDBInterface::AddExpert" );
		return FALSE;
	}
	
	return TRUE;
}

BOOL CDBInterface::RemoveExpertItem(CString nID)
{
	CString strsql;
	strsql.Format( "delete from tbl_prof where prof_id='%s'", nID );
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strsql) ) {
		return FALSE;
	}
	return TRUE;
}

BOOL CDBInterface::AddChannel(CChannel *pChannel)
{
	CString strSql;
	strSql.Format( "values('%d','%s','%d')",
	pChannel->m_nChannelIndex,pChannel->m_strChannelIP,pChannel->m_nChannelPort);
	strSql = "insert into tbl_channels(channel_id,channel_ip,channel_port) " + strSql;
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strSql) ) {
		AfxMessageBox( "Error CDBInterface::AddExpert" );
		return FALSE;
	}
	
	return TRUE;
}
BOOL CDBInterface::RemoveChannelItem(int cID)
{
	CString strsql;
	strsql.Format( "delete from tbl_channels where channel_id='%d'", cID );
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strsql) ) {
		return FALSE;
	}
	return TRUE;

}
BOOL CDBInterface::RemoveVidsrc(int vID)
{
	CString strsql;
	strsql.Format( "delete from tbl_vidsrc where vidsrc_id='%d'", vID );
	if ( !m_pQuery->try_execute((LPTSTR)(LPCTSTR)strsql) ) {
		return FALSE;
	}
	return TRUE;
}

//////////////////////////////////////////////////////////////////////////
int CDBInterface::Str2Int( LPCTSTR szStr )
{
	int r = 0;
	int n = strlen( szStr );
	for ( int i=0; i<n; i++ )
	{
		if ( (szStr[i]<'0') || (szStr[i]>'9') )
		{
			return -1;
		}
		r = r*10 + szStr[i] - '0';
	}
	return r;
}


CTime CDBInterface::GetTimeFromString( LPCTSTR szTime )
{
	CTime nowtime = CTime::GetCurrentTime();
	CTime tReturn = 0;
	CString strTime = szTime, strTemp;
	if ( strTime.GetLength() < 19 ) return FALSE;
	strTemp = strTime.Mid( 0, 4 );
	int nYear = Str2Int(strTemp);
	if ( nYear < 0 ) return FALSE;

	strTemp = strTime.Mid( 5, 2 );
	int nMont = Str2Int(strTemp);
	if ( nMont < 0 ) return FALSE;
	
	strTemp = strTime.Mid( 8, 2 );
	int nDay = Str2Int(strTemp);
	if ( nDay < 0 ) return FALSE;

	strTemp = strTime.Mid( 11, 2 );
	int nHour = Str2Int(strTemp);
	if ( nHour < 0 ) return FALSE;

	strTemp = strTime.Mid( 14, 2 );
	int nMint = Str2Int(strTemp);
	if ( nMint < 0 ) return FALSE;

	strTemp = strTime.Mid( 17, 2 );
	int nSeco = Str2Int(strTemp);
	if ( nSeco < 0 ) return FALSE;
	tReturn = CTime(nYear,nMont,nDay,nHour,nMint,nSeco);
	return tReturn;
}