internal string BuildWhereClauseString(OysterRecordingSessionSearchType RecordingSessionSearchType, string SearchCriteria)
{
string NewWhereString = "";
switch(RecordingSessionSearchType)
{
case OysterRecordingSessionSearchType.All:
throw new Exception("OysterRecordingSessionSearchType.All is not yet Implemented");
case OysterRecordingSessionSearchType.Date:
string[] sDates = SearchCriteria.Split("@".ToCharArray());
NewWhereString = "WHERE tblSession.Id in " +
"(SELECT DISTINCT tblSession.Id FROM tblSession " +
"WHERE tblSession.CreatedDate BETWEEN '" + sDates[0] + "' AND '" + sDates[1] + "' " +
"AND tblSession.IsDefault = 0) ";
break;
case OysterRecordingSessionSearchType.Groups:
NewWhereString = "LEFT OUTER JOIN tblGroup ON tblGroup.Id = tblGroupTokens.GroupId " +
"WHERE tblGroup.Description Like '%" + SearchCriteria + "%' ";
break;
case OysterRecordingSessionSearchType.Notes:
// NewWhereString = " LEFT OUTER JOIN tblNotes ON tblNotes.SessionId = tblSession.Id " +
// "WHERE tblNotes.Description LIKE '%" + SearchCriteria + "%' ";
NewWhereString = " WHERE tblSession.Id in " +
"(SELECT DISTINCT tblSession.Id FROM tblSession LEFT OUTER JOIN tblNotes ON tblNotes.SessionId = tblSession.Id " +
"WHERE tblNotes.Description LIKE '%" + SearchCriteria + "%') ";
break;
case OysterRecordingSessionSearchType.NumberOfRecordings:
throw new Exception("OysterRecordingSessionSearchType.NumberOfRecordings not yet Implemented");
case OysterRecordingSessionSearchType.People:
NewWhereString = " Left Outer Join tblUser ON tblUser.Id = " +
"tblSession.OwnerId WHERE tblUser.Name LIKE '%" + SearchCriteria + "%' ";
break;
case OysterRecordingSessionSearchType.Title:
NewWhereString = "WHERE tblSession.Description Like '%" + SearchCriteria + "%' ";
break;
case OysterRecordingSessionSearchType.TitleAndNotes:
NewWhereString = " WHERE (Id IN (SELECT DISTINCT tblSession.Id FROM tblSession " +
" LEFT OUTER JOIN tblNotes ON tblNotes.SessionId = tblSession.Id " +
" WHERE tblNotes.Description LIKE '%" + SearchCriteria + "%') OR " +
"Id IN (SELECT DISTINCT tblSession.Id " +
" FROM tblSession WHERE tblSession.Description LIKE '%"+ SearchCriteria +"%'))";
break;
default:
throw new Exception("New OysterRecordingSessionSearchType has not been implemented in BuildWhereClauseString");
}
return NewWhereString;
}
/// <summary>
/// Returns all recordings that are visible to the AccessingUser
/// </summary>
/// <param name="AccessingUser"></param>
/// <param name="TargetUser"></param>
/// <returns>OCL.Recordings</returns>
internal RecordingSessions GetAllVisibleRecordingSessions(OCL.User AccessingUser,OysterRecordingSessionSearchType RecordingSessionSearchType,string SearchCriteria)
{
string sSQL = "";
bool GetOwned = false;
string WhereClause = BuildWhereClauseString(RecordingSessionSearchType,SearchCriteria);
if(((RecordingSessionSearchType != OysterRecordingSessionSearchType.TitleAndNotes)||(RecordingSessionSearchType != OysterRecordingSessionSearchType.Groups))&&(AccessingUser.mvarIsSuperUser))
{
// sSQL = "SELECT DISTINCT tblSession.Id, tblSession.Description FROM tblSession " + WhereClause + " AND tblSession.IsDefault <> 1 ORDER BY tblSession.Description";
sSQL = "SELECT tblSession.Id, tblSession.Description FROM tblSession " + WhereClause + " AND tblSession.IsDefault = 0 AND tblSession.IsOysterSession <> 0 ORDER BY tblSession.Description";
}
else if((RecordingSessionSearchType == OysterRecordingSessionSearchType.TitleAndNotes)&&(AccessingUser.mvarIsSuperUser))
{
sSQL = "SELECT DISTINCT tblSession.Id, tblSession.Description FROM tblSession " + WhereClause + " AND tblSession.IsDefault <> 1 AND tblSession.IsOysterSession <> 0 ORDER BY tblSession.Description";
}
else
{
sSQL = "SELECT DISTINCT tblGroupTokens.ObjectId, tblSession.Description FROM tblGroupTokens LEFT OUTER JOIN tblSession ON tblGroupTokens.ObjectId = tblSession.Id " + WhereClause+ " AND UserId = " + AccessingUser.ID +
" AND tblGroupTokens.ObjectTypeId = 7" +
" AND tblGroupTokens.IsVisible <> 0" +
" AND tblSession.IsDefault = 0" +
" AND tblSession.IsOysterSession <> 0 " +
" ORDER BY tblSession.Description";
GetOwned = true;
}
System.Data.DataSet DS = null;
try
{
DS = RF.GetDataSet(sSQL);
}
catch(SqlException Err)
{
throw new Exception(Err.Message,Err.InnerException);
}
OCL.RecordingSessions AR = new OCL.RecordingSessions();
foreach(DataRow r in DS.Tables[0].Rows)
{
AR.Add(GetSession(Convert.ToInt32(r[0])));
}
if(GetOwned == true)
{
sSQL = "SELECT tblSession.ID FROM tblSession "+ WhereClause + " AND OwnerId = " + AccessingUser.ID +
" AND tblSession.IsDefault = 0 AND tblSession.IsOysterSession <> 0 ORDER BY Description";
DS = RF.GetDataSet(sSQL);
foreach(DataRow r in DS.Tables[0].Rows)
{
foreach(OCL.RecordingSession OR in AR)
{
if(OR.ID == Convert.ToInt32(r[0]))
goto skipAdd;
}
AR.Add(GetSession(Convert.ToInt32(r[0])));
skipAdd:{}
}
OCL.User PAccess = GetPublicAccessUser();
if(AccessingUser.ID != PAccess.ID)
{
sSQL = "SELECT tblSession.ID FROM tblSession "+ WhereClause + " AND OwnerId = " + PAccess.ID +
" AND tblSession.IsDefault = 0 AND tblSession.IsOysterSession <> 0 ORDER BY Description";
DS = RF.GetDataSet(sSQL);
foreach(DataRow r in DS.Tables[0].Rows)
{
// foreach(OCL.RecordingSession OR in AR)
// {
// if(OR.ID == Convert.ToInt32(r[0]))
// goto skipAdd;
// }
AR.Add(GetSession(Convert.ToInt32(r[0])));
// skipAdd:{}
}
}
}
return AR;
}
/// <summary>
/// Get all visible Oyster Recording Sessions that are visible by the accessing user
/// and meets the supplied search criteria
/// </summary>
/// <param name="AccessingUser"></param>
/// <param name="RecordingSessionSearchType"></param>
/// <param name="SearchCriteria"></param>
/// <returns></returns>
public RecordingSessions AllVisibleRecordingSessions(OCL.User AccessingUser,
OysterRecordingSessionSearchType RecordingSessionSearchType,string SearchCriteria)
{
Functions F = new Functions();
RecordingSessions ARS = new RecordingSessions();
ARS = F.GetAllVisibleRecordingSessions(AccessingUser,RecordingSessionSearchType,SearchCriteria);
return ARS;
}
