private void InitializeXpMsVer()
{
// permit disabling of these checks
if( xp_msverInitialized || GentleSettings.AnalyzerLevel == AnalyzerLevel.None )
{
return;
}
try
{
SqlCommand cmd = provider.GetCommand() as SqlCommand;
cmd.CommandType = CommandType.StoredProcedure;
// retrieve name
cmd.Parameters.AddWithValue( "@optname", NameProperty );
SqlStatement stmt = new SqlStatement( StatementType.Unknown, cmd, xp_msverProcedureName );
SqlResult sr = provider.ExecuteStatement( stmt );
name = sr.GetString( 0, 3 );
// retrieve version
cmd.Parameters[ "@optname" ].Value = VersionProperty;
stmt = new SqlStatement( StatementType.Unknown, cmd, xp_msverProcedureName );
sr = provider.ExecuteStatement( stmt );
version = new Version( sr.GetString( 0, 3 ) );
}
finally
{
xp_msverInitialized = true;
}
}
/// <summary>
/// Constructor for ExecuteScalar results.
/// </summary>
/// <param name="broker">The PersistenceBroker instance to use for database access.</param>
/// <param name="retval">The object returned by the .NET database provider.</param>
/// <param name="stmt">The SqlStatement leading to this SqlResult</param>
public SqlResult( PersistenceBroker broker, object retval, SqlStatement stmt ) :
base( broker )
{
this.stmt = stmt;
if( retval != null )
{
try
{
// some rdbms' return 64-bit row identities
long lastId = Convert.ToInt64( retval );
returnValue = Convert.ToInt32( lastId );
if( stmt.StatementType != StatementType.Count )
{
rowsAffected = 1;
}
}
catch( Exception e )
{
throw new GentleException( Common.Error.NoIdentityReturned,
stmt.Sql, e );
}
}
}
public IList<Program> GetProgramsByTitle(DateTime startTime, DateTime endTime, string title)
{
IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
string StartTimeString = startTime.ToString(GetDateTimeString(), mmddFormat);
string EndTimeString = endTime.ToString(GetDateTimeString(), mmddFormat);
StringBuilder SqlSelectCommand = new StringBuilder();
SqlSelectCommand.Append("select p.* from Program p inner join Channel c on c.idChannel = p.idChannel ");
SqlSelectCommand.AppendFormat(
"where ((EndTime > '{0}' and EndTime < '{1}') or (StartTime >= '{0}' and StartTime <= '{1}') or (StartTime <= '{0}' and EndTime >= '{1}')) and title = '{2}' and c.visibleInGuide = 1 order by startTime",
StartTimeString, EndTimeString, EscapeSQLString(title)
);
SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof (Program)).GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(),
typeof (Program));
return ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute());
}
private void getTvServerChannels()
{
CBChannelGroup chGroup = (CBChannelGroup)GroupComboBox.SelectedItem;
IList<Channel> Channels;
if (chGroup != null && chGroup.idGroup != -1)
{
SqlBuilder sb1 = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof (Channel));
SqlStatement stmt1 = sb1.GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(stmt1.StatementType, stmt1.Command,
String.Format(
"select c.* from Channel c join {0}GroupMap g on c.idChannel=g.idChannel where c.{1} = 1 and g.idGroup = '{2}' order by g.sortOrder",
IsTvMapping ? "" : "Radio", IsTvMapping ? "isTv" : "isRadio",
chGroup.idGroup), typeof (Channel));
Channels = ObjectFactory.GetCollection<Channel>(ManualJoinSQL.Execute());
}
else
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
sb.AddOrderByField(true, "sortOrder");
if (IsTvMapping)
{
sb.AddConstraint("isTv = 1");
}
else
{
sb.AddConstraint("isRadio = 1");
}
SqlStatement stmt = sb.GetStatement(true);
Channels = ObjectFactory.GetCollection<Channel>(stmt.Execute());
}
foreach (Channel chan in Channels)
{
if (!_channelMapping.ContainsKey(chan.DisplayName))
{
ChannelMap channel = new ChannelMap();
channel.displayName = chan.DisplayName;
_channelMapping.Add(chan.DisplayName, channel);
}
}
}
public IList<Channel> GetChannelsInGroup(ChannelGroup group)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
SqlStatement origStmt = sb.GetStatement(true);
string sql = "SELECT c.* FROM Channel c INNER JOIN GroupMap gm ON (c.idChannel = gm.idChannel AND gm.idGroup =" +
group.IdGroup + ") ORDER BY gm.SortOrder ASC";
SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql,
typeof (Channel));
return ObjectFactory.GetCollection<Channel>(statement.Execute());
}
private void buttonRefresh_Click(object sender, EventArgs e)
{
String name = null;
try
{
textBoxAction.Text = "Loading";
this.Refresh();
Log.Debug("Loading all channels from the tvguide[s]");
// used for partial matches
TstDictionary guideChannels = new TstDictionary();
Dictionary<string, Channel> guideChannelsExternald = new Dictionary<string, Channel>();
List<Channel> lstTvGuideChannels = readChannelsFromTVGuide();
if (lstTvGuideChannels == null)
return;
// convert to Dictionary
foreach (Channel ch in lstTvGuideChannels)
{
string tName = ch.DisplayName.Replace(" ", "").ToLowerInvariant();
if (!guideChannels.ContainsKey(tName))
guideChannels.Add(tName, ch);
// used to make sure that the available mapping is used by default
if (ch.ExternalId != null && !ch.ExternalId.Trim().Equals(""))
{
// need to check this because we can have channels with multiple display-names
// and they're currently handles as one channel/display-name.
// only in the mapping procedure of course
if (!guideChannelsExternald.ContainsKey(ch.ExternalId))
guideChannelsExternald.Add(ch.ExternalId, ch);
}
}
Log.Debug("Loading all channels from the database");
CBChannelGroup chGroup = (CBChannelGroup)comboBoxGroup.SelectedItem;
IList<Channel> channels;
bool loadRadio = checkBoxLoadRadio.Checked;
if (chGroup != null && chGroup.idGroup != -1)
{
SqlBuilder sb1 = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof (Channel));
SqlStatement stmt1 = sb1.GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(stmt1.StatementType, stmt1.Command,
String.Format(
"select c.* from Channel c join GroupMap g on c.idChannel=g.idChannel where " +
(loadRadio ? "" : " c.isTv = 1 and ") +
" g.idGroup = '{0}' order by g.sortOrder", chGroup.idGroup),
typeof (Channel));
channels = ObjectFactory.GetCollection<Channel>(ManualJoinSQL.Execute());
}
else
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
sb.AddOrderByField(true, "sortOrder");
if (!loadRadio)
sb.AddConstraint(" isTv = 1");
SqlStatement stmt = sb.GetStatement(true);
channels = ObjectFactory.GetCollection<Channel>(stmt.Execute());
}
progressBar1.Minimum = 0;
progressBar1.Maximum = channels.Count;
progressBar1.Value = 0;
dataGridChannelMappings.Rows.Clear();
int row = 0;
if (channels.Count == 0)
{
MessageBox.Show("No tv-channels available to map");
return;
}
// add as many rows in the datagrid as there are channels
dataGridChannelMappings.Rows.Add(channels.Count);
DataGridViewRowCollection rows = dataGridChannelMappings.Rows;
// go through each channel and try to find a matching channel
// 1: matching display-name (non case-sensitive)
// 2: partial search on the first word. The first match will be selected in the dropdown
foreach (Channel ch in channels)
{
Boolean alreadyMapped = false;
DataGridViewRow gridRow = rows[row++];
DataGridViewTextBoxCell idCell = (DataGridViewTextBoxCell)gridRow.Cells["Id"];
DataGridViewTextBoxCell channelCell = (DataGridViewTextBoxCell)gridRow.Cells["tuningChannel"];
DataGridViewTextBoxCell providerCell = (DataGridViewTextBoxCell)gridRow.Cells["tuningChannel"];
DataGridViewCheckBoxCell showInGuideCell = (DataGridViewCheckBoxCell)gridRow.Cells["ShowInGuide"];
channelCell.Value = ch.DisplayName;
idCell.Value = ch.IdChannel;
showInGuideCell.Value = ch.VisibleInGuide;
DataGridViewComboBoxCell guideChannelComboBox = (DataGridViewComboBoxCell)gridRow.Cells["guideChannel"];
// always add a empty item as the first option
// these channels will not be updated when saving
guideChannelComboBox.Items.Add("");
// Start by checking if there's an available mapping for this channel
Channel matchingGuideChannel = null;
if (guideChannelsExternald.ContainsKey(ch.ExternalId))
{
matchingGuideChannel = guideChannelsExternald[ch.ExternalId];
alreadyMapped = true;
}
// no externalid mapping available, try using the name
if (matchingGuideChannel == null)
{
string tName = ch.DisplayName.Replace(" ", "").ToLowerInvariant();
if (guideChannels.ContainsKey(tName))
matchingGuideChannel = (Channel)guideChannels[tName];
}
Boolean exactMatch = false;
Boolean partialMatch = false;
if (!alreadyMapped)
{
if (matchingGuideChannel != null)
{
exactMatch = true;
}
else
{
// No name mapping found
// do a partial search, default off
if (checkBoxPartialMatch.Checked)
{
// do a search using the first word(s) (skipping the last) of the channelname
name = ch.DisplayName.Trim();
int spaceIdx = name.LastIndexOf(" ");
if (spaceIdx > 0)
{
name = name.Substring(0, spaceIdx).Trim();
}
else
{
// only one word so we'll do a partial match on the first 3 letters
if (name.Length > 3)
name = name.Substring(0, 3);
}
try
{
// Note: the partial match code doesn't work as described by the author
// so we'll use PrefixMatch method (created by a codeproject user)
ICollection partialMatches = guideChannels.PrefixMatch(name.Replace(" ", "").ToLowerInvariant());
if (partialMatches != null && partialMatches.Count > 0)
{
IEnumerator pmE = partialMatches.GetEnumerator();
pmE.MoveNext();
matchingGuideChannel = (Channel)guideChannels[(string)pmE.Current];
partialMatch = true;
}
}
catch (Exception ex)
{
Log.Error("Error while searching for matching guide channel :" + ex.Message);
}
}
}
}
// add the channels
// set the first matching channel in the search above as the selected
Boolean gotMatch = false;
string ALREADY_MAPPED = "Already mapped (got external id)";
string EXACT_MATCH = "Exact match";
string PARTIAL_MATCH = "Partial match";
string NO_MATCH = "No match";
DataGridViewCell cell = gridRow.Cells["matchType"];
foreach (DictionaryEntry de in guideChannels)
{
Channel guideChannel = (Channel)de.Value;
String itemText = guideChannel.DisplayName + " (" + guideChannel.ExternalId + ")";
guideChannelComboBox.Items.Add(itemText);
if (!gotMatch && matchingGuideChannel != null)
{
if (guideChannel.DisplayName.ToLowerInvariant().Equals(matchingGuideChannel.DisplayName.ToLowerInvariant()))
{
// set the matchtype row color according to the type of match(already mapped,exact, partial, none)
if (alreadyMapped)
{
cell.Style.BackColor = Color.White;
cell.ToolTipText = ALREADY_MAPPED;
// hack so that we can order the grid by mappingtype
cell.Value = "";
}
else if (exactMatch)
{
cell.Style.BackColor = Color.Green;
cell.ToolTipText = EXACT_MATCH;
cell.Value = " ";
}
else if (partialMatch)
{
cell.Style.BackColor = Color.Yellow;
cell.ToolTipText = PARTIAL_MATCH;
cell.Value = " ";
}
guideChannelComboBox.Value = itemText;
guideChannelComboBox.Tag = ch.ExternalId;
gotMatch = true;
}
}
}
if (!gotMatch)
{
cell.Style.BackColor = Color.Red;
cell.ToolTipText = NO_MATCH;
cell.Value = " ";
}
progressBar1.Value++;
}
textBoxAction.Text = "Finished";
}
catch (Exception ex)
{
Log.Error("Failed loading channels/mappings : channel {0} erro {1} ", name, ex.Message);
Log.Error(ex.StackTrace);
textBoxAction.Text = "Error";
}
}
/// <summary>
/// Constructor for ExecuteNonQuery results.
/// </summary>
/// <param name="broker">The PersistenceBroker instance to use for database access.</param>
/// <param name="rowsAffected">The number of rows affected by the query</param>
/// <param name="stmt">The SqlStatement leading to this SqlResult</param>
public SqlResult( PersistenceBroker broker, int rowsAffected, SqlStatement stmt ) :
base( broker )
{
this.stmt = stmt;
this.rowsAffected = rowsAffected;
}
public IList<Channel> GetChannelsInGroup(ChannelGroup group)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
SqlStatement origStmt = sb.GetStatement(true);
string sql = "select c.* from channel c inner join groupmap gm on (c.idChannel = gm.idChannel and gm.idGroup =" +
group.IdGroup + ") order by gm.SortOrder asc";
SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql,
typeof (Channel));
return ObjectFactory.GetCollection<Channel>(statement.Execute());
}
//-------------------------------------------------------------------------------------------------------------
// Search epg data and find all matching programs
//-------------------------------------------------------------------------------------------------------------
// matching programs are searched based on available information for program name, start time, endtime, displayname or groupname
// if dispalyname is defined, groupnames will be ignored
// partial name = true will handle partial name matches from program name
// the routine will return an IList<Program> matchingprograms which contains all EPG programs of Type Program
// the routine will not do any checking for conflicts of the list
//-------------------------------------------------------------------------------------------------------------
public bool SqlQueryRecordings(TvWish mywish, int counter)
{
LogDebug("TvWishList: Autocomplete search for matching recordings", (int)LogSetting.DEBUG);
if (mywish.b_active == false)
{
Log.Debug("Tvwish is inactive - returning");
return true;
}
string SearchIn = mywish.s_SearchIn.ToLower();
String Expression = String.Empty;
IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
IList<Recording> myRecordingList = null;
/* search names are case invariant (title SOKO equal to title soko)
*
* LIKE
* NOT LIKE
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] or [!charlist] Any single character not in charlist
*
*
*
* SELECT column_name(s)
FROM table_name
WHERE column_name operator value
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
*
*
* Combine with brackets ()
* and with AND or OR
Note: In some versions of SQL the <> operator may be written as !=
*
*
* Recording columns(use lower case in sql quesry):
public string Description { get; set; }
public DateTime EndTime { get; set; }
public string EpisodeName { get; set; }
public string EpisodeNum { get; set; }
public string EpisodeNumber { get; }
public string EpisodePart { get; set; }
public string FileName { get; set; }
public string Genre { get; set; }
public int IdChannel { get; set; }
public int IdRecording { get; }
public int Idschedule { get; set; }
public int IdServer { get; set; }
public bool IsChanged { get; }
public bool IsManual { get; }
public bool IsRecording { get; set; }
public int KeepUntil { get; set; }
public DateTime KeepUntilDate { get; set; }
public string SeriesNum { get; set; }
public bool ShouldBeDeleted { get; }
public DateTime StartTime { get; set; }
public int StopTime { get; set; }
public int TimesWatched { get; set; }
public string Title { get; set; }
*/
string EscapeName = EscapeSQLString(mywish.searchfor);
if (SearchIn == "title") //no translation needed due to separation of original string
{
//Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
if (mywish.b_partialname == true) //partial title
{
Expression += String.Format("( title like '%{0}%' ) ", EscapeName);
}
else //exact title
{
Expression += String.Format("( title = '{0}' ) ", EscapeName);
}
}//end title
else if (SearchIn == "text") //search in description //no translation needed due to separation of original string
{
//Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
Expression += String.Format("( description like '%{0}%' ) ", EscapeName);
}//end description
else if (SearchIn == "both") //no translation needed due to separation of original string
{
//Expression += String.Format("(EndTime >= '{0}') AND (", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
if (mywish.b_partialname == true) //partial title
{
Expression += String.Format("( title like '%{0}%' ) OR ", EscapeName);
}
else //exact title
{
Expression += String.Format("( title = '{0}' ) OR ", EscapeName);
}
Expression += String.Format("( description like '%{0}%' )", EscapeName);
}//end both
if (SearchIn == "expression") //no translation needed due to separation of original string
{
Expression = mywish.searchfor;
//split in schedule and recording expression
Expression = Expression.Replace("<BR>", "\n");
Expression = Expression.Replace("<br>", "\n");
Expression = Expression.Replace(@"\n", "\n");
Expression = Expression.Replace(@"\'", "'"); //needed for expressions
string[] expArray = Expression.Split('\n');
if (expArray.Length != 2)
{
Log.Debug("No expression defined for searching recordings on tvwish "+mywish.name);
return true;
}
else
{
Expression = expArray[1];
}
Log.Debug("Expression=" + Expression);
}
//SQL Query
LogDebug("SQL query for: " + Expression, (int)LogSetting.DEBUG);
try
{
#if (MPTV2)
string command = "select * from recordings where " + Expression;
Log.Debug("command=" + command);
myRecordingList = Recording.GeneralSqlQuery(command);
#else
StringBuilder SqlSelectCommand = new StringBuilder();
SqlSelectCommand.Append("select * from Recording ");
SqlSelectCommand.AppendFormat(string.Format("where {0}", Expression));
SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof(Recording)).GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(), typeof(Recording));
myRecordingList = ObjectFactory.GetCollection<Recording>(ManualJoinSQL.Execute());
#endif
Log.Debug("SQL Result:myRecordingList.Count" + myRecordingList.Count.ToString());
}
catch (Exception exc)
{
LogDebug("Error in SQL query for searching " + Expression, (int)LogSetting.ERROR);
LogDebug("Exception message was " + exc.Message, (int)LogSetting.ERROR);
string languagetext = lng.TranslateString("Error in SQL query - check the expression {0}", 25, Expression);
labelmessage(languagetext, PipeCommands.StartEpg); //do not mark as an error as other commands are still coming - just delay message
Thread.Sleep(ErrorWaitTime);
return false;
}
// End of SQL Query
Log.Debug(myRecordingList.Count.ToString() + " recordings found after SQL query");
foreach (Recording myrecording in myRecordingList)
{
LogDebug("****Next program in autocompletion:", (int)LogSetting.DEBUG);
//outputrecordingresponse(myrecording, (int)LogSetting.DEBUG); //Debug only
/* // process groupname BUG: not needed in recordings
if (mywish.group != lng.TranslateString("All Channels", 4104))
{
if ((IsChannelInGroup(myrecording.IdChannel, mywish.group) == false) && (IsRadioChannelInGroup(myrecording.IdChannel, mywish.group) == false))
{
LogDebug("!!!!!!!Groupname " + mywish.group + " not matching for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}*/
//process exclude
if (mywish.exclude != string.Empty)
{
string Exclude = mywish.exclude.ToUpper();
String Exclude1 = "";
String Exclude2 = "";
Exclude = Exclude.Replace("||", "\n");//bug fix: was && before
String[] Tokens = Exclude.Split('\n');
if (Tokens.Length >= 2)
{
Exclude2 = Tokens[1];
}
if (Tokens.Length >= 1)
{
Exclude1 = Tokens[0];
}
LogDebug("TvWishList: Exclude1=" + Exclude1, (int)LogSetting.DEBUG);
LogDebug("TvWishList: Exclude2=" + Exclude2, (int)LogSetting.DEBUG);
Boolean foundflag = false;
if ((Exclude1.StartsWith("*") == true) && (Exclude1.StartsWith("**") == false)) //exclude in description
{
string Exclude_mod = Exclude1.Substring(1, Exclude.Length - 1);
//LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG);
if ((myrecording.Description.ToUpper().Contains(Exclude_mod) == false) || (Exclude_mod == ""))
{
if ((myrecording.Description.ToUpper().Contains(Exclude2) == false) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
}
else if (Exclude1.StartsWith("**") == true) //exclude in title or description
{
string Exclude_mod = Exclude1.Substring(2, Exclude.Length - 2);
//LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG);
if (((myrecording.Title.ToUpper().Contains(Exclude_mod) == false) && (myrecording.Description.ToUpper().Contains(Exclude_mod) == false)) || (Exclude_mod == ""))
{
if (((myrecording.Title.ToUpper().Contains(Exclude2) == false) && (myrecording.Description.ToUpper().Contains(Exclude2) == false)) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
}
else if ((myrecording.Title.ToUpper().Contains(Exclude1) == false) || (Exclude1 == "")) //exclude only in title
{
if ((myrecording.Title.ToUpper().Contains(Exclude2) == false) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
if (foundflag == false)
{
LogDebug("!!!!!!!exclude condition " + Exclude + " found for for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}//end exclude
// process valid word check
if (mywish.b_wordmatch == true)
{
if (SearchIn == "title") //no translation needed due to separation of original string
{
if (mywish.b_partialname == true) //partial title
{
if (validwordcheck(myrecording.Title, mywish.searchfor) == false)
{
LogDebug("!!!!!!!wordcheck on title failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}
}
else if (SearchIn == "text") //search in description //no translation needed due to separation of original string
{
if (validwordcheck(myrecording.Description, mywish.searchfor) == false)
{
LogDebug("!!!!!!!wordcheck on description failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}
else if (SearchIn == "both") //search in either title or description //no translation needed due to separation of original string
{
if ((validwordcheck(myrecording.Title, mywish.searchfor) == false) && (validwordcheck(myrecording.Description, mywish.searchfor) == false))
{
LogDebug("!!!!!!!wordcheck on title and description failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}
} //end processing valid wordcheck
//process episode name, part, number and series number
#if(TV100 || TV101)
if (1==1) //always true for 1.0.1
#elif(TV11 || TV12)
if (((myrecording.EpisodeName.ToLower() == mywish.episodename.ToLower() == true) || (mywish.episodename == "")) && ((myrecording.EpisodePart.ToLower() == mywish.episodepart.ToLower() == true) || (mywish.episodepart == "")) && ((myrecording.SeriesNum.ToLower() == mywish.seriesnumber.ToLower()) || (mywish.seriesnumber == "")) && ((myrecording.EpisodeNum.ToLower() == mywish.episodenumber.ToLower()) || (mywish.episodenumber == "")))
//bug to fix with > number
#endif
{
if ((myrecording.StartTime < myrecording.EndTime)&&(File.Exists(myrecording.FileName)))
{//during recording start time will be equal to end time and text all in capitol letters - in that case ignore and wait till recording is finished - schedule does exist in parallel during recording
//create new recording message
Log.Debug("myrecording.Title=" + myrecording.Title);
Log.Debug("myrecording.Description=" + myrecording.Description);
FileInfo myfileinfo = new FileInfo(myrecording.FileName);
double filesize = Convert.ToDouble(myfileinfo.Length)/(1024*1024*1024);
string message = lng.TranslateString("{0} with {1} GB",59,myrecording.FileName,filesize.ToString("F2"));
Log.Debug("recording message with filesize="+message);
//add new recorded message
mymessage.addmessage(myrecording, message, MessageType.Recorded, mywish.searchfor, (int)XmlMessages.MessageEvents.RECORDING_FOUND, mywish.tvwishid, myrecording.FileName);
//remove deleted messages with same title/description/episode name/number
try
{
for (int i = mymessage.ListAllTvMessages().Count-1; i >=0 ; i--)
{
xmlmessage onemessage = mymessage.GetTvMessageAtIndex(i);
if ((onemessage.title == myrecording.Title) && (onemessage.description == myrecording.Description) && (onemessage.type == MessageType.Deleted.ToString()))
{
#if(TV100 || TV101)
if (1==1) //always true for 1.0.1
#elif(TV11 || TV12)
if (((myrecording.EpisodeName.ToLower() == onemessage.EpisodeName.ToLower() == true) || (onemessage.EpisodeName == "")) && ((myrecording.EpisodePart.ToLower() == onemessage.EpisodePart.ToLower() == true) || (onemessage.EpisodePart == "")) && ((myrecording.SeriesNum.ToLower() == onemessage.SeriesNum.ToLower()) || (onemessage.SeriesNum == "")) && ((myrecording.EpisodeNum.ToLower() == onemessage.EpisodeNum.ToLower()) || (onemessage.EpisodeNum == "")))
#endif
{
//delete message
Log.Debug("Deleting message " + onemessage.title+" at "+onemessage.start+" due to new recording");
mymessage.DeleteTvMessageAt(i);
}
}
}
}
catch (Exception exc)
{
LogDebug("Error: searching deleted messages failed with exception " + exc.Message, (int)LogSetting.ERROR);
string languagetext2 = lng.TranslateString("Fatal error - check the log file", 24);
labelmessage(languagetext2, PipeCommands.StartEpg); //do not stop - do not flag as error
Thread.Sleep(ErrorWaitTime);
return false;
}
}
}//end episode filter
string languagetext = lng.TranslateString("Processing Recording {0} at {1}", 22, myrecording.Title, myrecording.StartTime.ToString());
labelmessage(languagetext, PipeCommands.StartEpg);
}//end all recordings
LogDebug("TvWishList: Autocomplete finished", (int)LogSetting.DEBUG);
return true;
}
//-------------------------------------------------------------------------------------------------------------
// Search epg data and find all matching programs
//-------------------------------------------------------------------------------------------------------------
// matching programs are searched based on available information for program name, start time, endtime, displayname or groupname
// if dispalyname is defined, groupnames will be ignored
// partial name = true will handle partial name matches from program name
// the routine will return an IList<Program> matchingprograms which contains all EPG programs of Type Program
// the routine will not do any checking for conflicts of the list
//-------------------------------------------------------------------------------------------------------------
public bool SqlQueryPrograms(ref TvWish mywish, int counter)
{
LogDebug("TvWishList: Autocomplete search for matching programs", (int)LogSetting.DEBUG);
if (mywish.b_active == false)
{
Log.Debug("Tvwish is inactive - returning");
return true;
}
string SearchIn = mywish.s_SearchIn.ToLower();
String Expression = String.Empty;
IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
IList<Program> myprogramlist = null;
/* search names are case invariant (title SOKO equal to title soko)
*
* LIKE
* NOT LIKE
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] or [!charlist] Any single character not in charlist
*
*
*
* SELECT column_name(s)
FROM table_name
WHERE column_name operator value
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
*
*
* Combine with brackets ()
* and with AND or OR
Note: In some versions of SQL the <> operator may be written as !=
*
*
* Program columns:
* title
* description
* classification
*
string Title
string Classification
string Description
DateTime StartTime
DateTime EndTime
DateTime OriginalAirDate
string EpisodeName
string EpisodeNum
string EpisodeNumber
string EpisodePart
string Genre
int ParentalRating
string SeriesNum
int StarRating
int IdChannel
int IdProgram
*/
string EscapeName = EscapeSQLString(mywish.searchfor);
if (SearchIn == "title") //no translation needed due to separation of original string
{
Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
if (mywish.b_partialname == true) //partial title
{
Expression += String.Format("( title like '%{0}%' ) ", EscapeName);
}
else //exact title
{
Expression += String.Format("( title = '{0}' ) ", EscapeName);
}
}//end title
else if (SearchIn == "text") //search in description //no translation needed due to separation of original string
{
Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
Expression += String.Format("( description like '%{0}%' ) ", EscapeName);
}//end description
else if (SearchIn == "both") //no translation needed due to separation of original string
{
Expression += String.Format("(EndTime >= '{0}') AND (", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now
if (mywish.b_partialname == true) //partial title
{
Expression += String.Format("( title like '%{0}%' ) OR ", EscapeName);
}
else //exact title
{
Expression += String.Format("( title = '{0}' ) OR ", EscapeName);
}
Expression += String.Format("( description like '%{0}%' ) )", EscapeName);
}//end both
if (SearchIn == "expression") //no translation needed due to separation of original string
{
Expression = mywish.searchfor;
//split in schedule and recording expression
Expression = Expression.Replace("<BR>", "\n");
Expression = Expression.Replace("<br>", "\n");
Expression = Expression.Replace(@"\n", "\n");
Expression = Expression.Replace(@"\'", "'"); //needed for expressions
string[] expArray = Expression.Split('\n');
if (expArray.Length < 1)
{
Log.Debug("No expression defined for searching schedules on tvwish " + mywish.name);
return true;
}
else
{
Expression = expArray[0];
Log.Debug("Expression=" + Expression);
}
}
//SQL Query
LogDebug("SQL query for: " + Expression, (int)LogSetting.DEBUG);
try
{
#if (MPTV2)
string command = "select * from programs where "+Expression;
Log.Debug("command=" + command);
myprogramlist = Program.GeneralSqlQuery(command);
#else
StringBuilder SqlSelectCommand = new StringBuilder();
SqlSelectCommand.Append("select * from Program ");
SqlSelectCommand.AppendFormat("where {0}", Expression); //!!!!!!!!!!!!!!!!!!!!!!EscapeSQLString cannot be checked for expression
SqlSelectCommand.Append(" order by StartTime");
SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof(Program)).GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(),typeof(Program));
myprogramlist = ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute());
#endif
}
catch (Exception exc)
{
LogDebug("Error in SQL query for searching " + Expression, (int)LogSetting.ERROR);
LogDebug("Exception message was " + exc.Message, (int)LogSetting.ERROR);
string languagetext = lng.TranslateString("Error in SQL query - check the expression {0}", 25, Expression);
labelmessage(languagetext, PipeCommands.StartEpg); //do not mark as an error as other commands are still coming - just delay message
Thread.Sleep(ErrorWaitTime);
return false;
}
// End of SQL Query
Log.Debug(myprogramlist.Count.ToString()+" programs found");
foreach (Program myprogram in myprogramlist)
{
Program oneprogram = myprogram;
LogDebug("\n************************************************************", (int)LogSetting.DEBUG);
LogDebug("****Next program in autocompletion:", (int)LogSetting.DEBUG);
outputprogramresponse(myprogram, (int)LogSetting.DEBUG); //Debug only
// process groupname
if (mywish.group != lng.TranslateString("All Channels",4104))
{
if ((IsChannelInGroup(oneprogram.IdChannel, mywish.group) == false) && (IsRadioChannelInGroup(oneprogram.IdChannel, mywish.group) == false))
{
LogDebug("!!!!!!!Groupname " + mywish.group + " not matching for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG);
continue;
}
}
//process exclude
if (mywish.exclude != string.Empty)
{
string Exclude = mywish.exclude.ToUpper();
String Exclude1 = "";
String Exclude2 = "";
Exclude = Exclude.Replace("||", "\n");//bug fix: was && before
String[] Tokens = Exclude.Split('\n');
if (Tokens.Length >= 2)
{
Exclude2 = Tokens[1];
}
if (Tokens.Length >= 1)
{
Exclude1 = Tokens[0];
}
LogDebug("TvWishList: Exclude1=" + Exclude1, (int)LogSetting.DEBUG);
LogDebug("TvWishList: Exclude2=" + Exclude2, (int)LogSetting.DEBUG);
Boolean foundflag = false;
if ((Exclude1.StartsWith("*") == true) && (Exclude1.StartsWith("**") == false)) //exclude in description
{
string Exclude_mod = Exclude1.Substring(1, Exclude.Length - 1);
//LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG);
if ((oneprogram.Description.ToUpper().Contains(Exclude_mod) == false) || (Exclude_mod == ""))
{
if ((oneprogram.Description.ToUpper().Contains(Exclude2) == false) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
}
else if (Exclude1.StartsWith("**") == true) //exclude in title or description
{
string Exclude_mod = Exclude1.Substring(2, Exclude.Length - 2);
//LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG);
if (((oneprogram.Title.ToUpper().Contains(Exclude_mod) == false) && (oneprogram.Description.ToUpper().Contains(Exclude_mod) == false)) || (Exclude_mod == ""))
{
if (((oneprogram.Title.ToUpper().Contains(Exclude2) == false) && (oneprogram.Description.ToUpper().Contains(Exclude2) == false)) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
}
else if ((oneprogram.Title.ToUpper().Contains(Exclude1) == false) || (Exclude1 == "")) //exclude only in title
{
if ((oneprogram.Title.ToUpper().Contains(Exclude2) == false) || (Exclude2 == ""))
{
foundflag = true;
LogDebug("No Exclude of program for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG);
}
}
if (foundflag == false)
{
LogDebug("", (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
message = lng.TranslateString("Excluding program {0} due to exclude filter condition {1}",55,oneprogram.Title,mywish.exclude);
LogDebug(message, (int)LogSetting.INFO);
LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty);
continue;
}
}//end exclude
// process valid word check
if (mywish.b_wordmatch == true)
{
if (SearchIn == "title") //no translation needed due to separation of original string
{
if (mywish.b_partialname == true) //partial title
{
if (validwordcheck(oneprogram.Title,mywish.searchfor) == false)
{
LogDebug("", (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition",56,oneprogram.Title);
LogDebug(message, (int)LogSetting.INFO);
LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty);
continue;
}
}
}
else if (SearchIn == "text") //search in description //no translation needed due to separation of original string
{
if (validwordcheck(oneprogram.Description, mywish.searchfor) == false)
{
LogDebug("", (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition ", 57, oneprogram.Title);
LogDebug(message, (int)LogSetting.INFO);
LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty);
continue;
}
}
else if (SearchIn == "both") //search in either title or description //no translation needed due to separation of original string
{
if ((validwordcheck(oneprogram.Title, mywish.searchfor) == false) && (validwordcheck(oneprogram.Description, mywish.searchfor) == false))
{
LogDebug("", (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition ", 57, oneprogram.Title);
LogDebug(message, (int)LogSetting.INFO);
LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty);
continue;
}
}
} //end processing valid wordcheck
string channelname = "Error_with_ID_" + oneprogram.IdChannel.ToString();
try
{
channelname = Channel.Retrieve(oneprogram.IdChannel).DisplayName;
}
catch//do nothing
{
}
string languagetext = lng.TranslateString("Processing EPG data {0} on {1} at {2}", 21, oneprogram.Title, channelname, oneprogram.StartTime.ToString());
labelmessage(languagetext, PipeCommands.StartEpg);
if (mywish.i_scheduled >= MAXFOUND)
{
LogDebug("", (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
message = lng.TranslateString("Maximum number ({0}) of programs found - will ignore additional matches",58, MAXFOUND.ToString());
LogDebug(message, (int)LogSetting.INFO);
LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO);
mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.MAXFOUND_EXCEEDED, mywish.tvwishid, string.Empty);
return true;
}
DateTime start = DateTime.Now; //DEBUG PERFORMANCE
bool ok = addsingleschedule(oneprogram, ref mywish, counter);
DateTime end = DateTime.Now; //DEBUG PERFORMANCE
Log.Debug("addsingleschedule time=" + end.Subtract(start).TotalSeconds.ToString()); //DEBUG PERFORMANCE
//post processing and updating of Tv wish data by locking to existing found schedule
if (ok == true)
{
try //update lock filters into tv server
{
if (mywish.recordtype == lng.TranslateString("Only Once",2650)) // stop after first successful program
{
LogDebug("Program " + mywish.name + " deactivated", (int)LogSetting.DEBUG);
mywish.b_active = false;
mywish.active = "False";
break; // stop after first successful program
}
//include all language strings with recordtype Channel
if (((mywish.recordtype == lng.Get(2652)) || (mywish.recordtype== lng.Get(2653)) || (mywish.recordtype== lng.Get(2654))|| (mywish.recordtype== lng.Get(2655)))&& (mywish.channel == lng.TranslateString("Any",4100)))
{
mywish.channel = channelname;
Log.Debug("Locked to channelfilter=" + channelname);
}
//include all language strings with recordtype Time
if (((mywish.recordtype == lng.Get(2654)) || (mywish.recordtype == lng.Get(2655)))&& (mywish.i_aftertime == -1) && (mywish.i_beforetime == -1))
{
mywish.i_aftertime = oneprogram.StartTime.Hour * 60 + oneprogram.StartTime.Minute;
mywish.aftertime = oneprogram.StartTime.Hour.ToString("D2") + ":" + oneprogram.StartTime.Minute.ToString("D2");
Log.Debug("Locked to i_aftertime=" + mywish.i_aftertime.ToString());
mywish.i_beforetime = oneprogram.EndTime.Hour * 60 + oneprogram.EndTime.Minute;
mywish.beforetime = oneprogram.EndTime.Hour.ToString("D2") + ":" + oneprogram.EndTime.Minute.ToString("D2");
Log.Debug("Locked to i_beforetime=" + mywish.i_beforetime.ToString());
}
//include all language strings with recordtype Day
if (((mywish.recordtype == lng.Get(2653)) || (mywish.recordtype == lng.Get(2655))) && (mywish.i_afterdays == -1) && (mywish.i_beforedays == -1))
{
mywish.i_afterdays = (int)oneprogram.StartTime.DayOfWeek;
if (mywish.i_afterdays == (int)DayOfWeek.Sunday)
mywish.afterdays = "Sunday";
else if (mywish.i_afterdays == (int)DayOfWeek.Monday)
mywish.afterdays = "Monday";
else if (mywish.i_afterdays == (int)DayOfWeek.Tuesday)
mywish.afterdays = "Tuesday";
else if (mywish.i_afterdays == (int)DayOfWeek.Wednesday)
mywish.afterdays = "Wednesday";
else if (mywish.i_afterdays == (int)DayOfWeek.Thursday)
mywish.afterdays = "Thursday";
else if (mywish.i_afterdays == (int)DayOfWeek.Friday)
mywish.afterdays = "Friday";
else if (mywish.i_afterdays == (int)DayOfWeek.Saturday)
mywish.afterdays = "Saturday";
mywish.i_beforedays = (int)oneprogram.StartTime.DayOfWeek;
if (mywish.i_beforedays == (int)DayOfWeek.Sunday)
mywish.beforedays = "Sunday";
else if (mywish.i_beforedays == (int)DayOfWeek.Monday)
mywish.beforedays = "Monday";
else if (mywish.i_beforedays == (int)DayOfWeek.Tuesday)
mywish.beforedays = "Tuesday";
else if (mywish.i_beforedays == (int)DayOfWeek.Wednesday)
mywish.beforedays = "Wednesday";
else if (mywish.i_beforedays == (int)DayOfWeek.Thursday)
mywish.beforedays = "Thursday";
else if (mywish.i_beforedays == (int)DayOfWeek.Friday)
mywish.beforedays = "Friday";
else if (mywish.i_beforedays == (int)DayOfWeek.Saturday)
mywish.beforedays = "Saturday";
Log.Debug("Locked to day=" + mywish.i_beforedays.ToString());
}
}
catch (Exception exc)
{
Log.Error("Error in updating lock filter - ex ception:" + exc.Message);
languagetext = lng.TranslateString("Fatal error - check the log file", 24);
labelmessage(languagetext, PipeCommands.StartEpg); //do not stop - do not flag as error
Thread.Sleep(ErrorWaitTime);
}
} //end locking condition
}//end search episode n
//}
LogDebug("TvWishList: Autocomplete finished", (int)LogSetting.DEBUG);
return true;
}
public static void ResetAllStates()
{
string sql = "Update Program set state=0 where state<>0;";
SqlStatement stmt = new SqlStatement(StatementType.Update, Broker.Provider.GetCommand(), sql);
stmt.Execute();
Gentle.Common.CacheManager.ClearQueryResultsByType(typeof(Program));
}
/// <summary>
/// Constructor for ExecuteNonQuery results.
/// </summary>
/// <param name="broker">The PersistenceBroker instance to use for database access.</param>
/// <param name="rowsAffected">The number of rows affected by the query</param>
/// <param name="stmt">The SqlStatement leading to this SqlResult</param>
public SqlResult(PersistenceBroker broker, int rowsAffected, SqlStatement stmt) :
base(broker)
{
this.stmt = stmt;
this.rowsAffected = rowsAffected;
}
/// <summary>
/// Constructor that reads and encapsulates results from the data reader. The maximum
/// number of rows to retrieve is obtained from the SqlStatement parameter.
/// </summary>
/// <param name="broker">The PersistenceBroker instance to use for database access.</param>
/// <param name="dr">The DataReader instance to read from</param>
/// <param name="stmt">The SqlStatement leading to this SqlResult</param>
public SqlResult(PersistenceBroker broker, IDataReader dr, SqlStatement stmt) : base(broker)
{
this.stmt = stmt;
try
{
rows = new ArrayList();
// store column names/order
columnNames = new string[dr.FieldCount];
// also cache fieldmaps for object retrievals (used later for NullValue handling)
ObjectMap map = stmt != null && stmt.Type != null?ObjectFactory.GetMap(broker, stmt.Type) : null;
fieldMaps = map != null ? new FieldMap[dr.FieldCount] : null;
// process all columns
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames[i] = dr.GetName(i);
if (map != null)
{
fieldMaps[i] = map.GetFieldMapFromColumn(columnNames[i]);
}
}
// skip past offset number of rows if SQL Server and paged statement
int skipCount = 0;
if (stmt.RowOffset > 0 && broker.ProviderName == "SQLServer")
{
while (dr != null && skipCount++ < stmt.RowOffset && dr.Read())
{
;
}
}
// read and store rows in result set
while (dr != null && dr.Read() && (stmt.RowLimit <= 0 || rows.Count < stmt.RowLimit))
{
// store values
object[] row = new object[dr.FieldCount];
dr.GetValues(row);
// convert DBNulls to system nulls (or the appropriate null translation value)
for (int i = 0; i < row.Length; i++)
{
if (row[i] == DBNull.Value)
{
row[i] = (map != null && fieldMaps[i] != null) ? fieldMaps[i].NullValue : null;
}
}
rows.Add(row);
}
rowsAffected = rows.Count;
}
//catch( Exception e )
//{
// string msg = e.Message;
// throw;
//}
finally
{
// close the data reader
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
}
public IList<Program> SearchProgramsByDescription(string searchCriteria, ChannelType channelType)
{
IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
StringBuilder SqlSelectCommand = new StringBuilder();
SqlSelectCommand.Append("select p.* from Program p inner join Channel c on c.idChannel = p.idChannel ");
SqlSelectCommand.AppendFormat("where endTime > '{0}'", DateTime.Now.ToString(GetDateTimeString(), mmddFormat));
if (searchCriteria.Length > 0)
{
SqlSelectCommand.AppendFormat("and description like '{0}%' ", EscapeSQLString(searchCriteria));
}
switch (channelType)
{
case ChannelType.Radio:
SqlSelectCommand.Append("and c.isTv=0 ");
break;
case ChannelType.Tv:
SqlSelectCommand.Append("and c.isTv=1 ");
break;
}
SqlSelectCommand.Append("and c.visibleInGuide = 1 order by description, startTime");
SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof (Program)).GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(),
typeof (Program));
return ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute());
}
/// <summary>
/// Execute the <see cref="SqlStatement"/> by providing it with a connection
/// to the SQL engine.
/// </summary>
/// <param name="stmt">The SqlStatement instance</param>
/// <returns>The result of the statement</returns>
public virtual SqlResult ExecuteStatement( SqlStatement stmt )
{
Check.VerifyNotNull( stmt, Error.NullParameter, "stmt" );
return stmt.Execute( GetConnection(), null );
}
public bool IsChannelMappedToCard(Channel dbChannel, Card card, bool forEpg)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (ChannelMap));
SqlStatement origStmt = sb.GetStatement(true);
string sql = "select cm.* from ChannelMap cm where cm.idChannel =" +
dbChannel.IdChannel + " and cm.idCard=" + card.IdCard + (forEpg ? "" : " and cm.epgOnly=0");
SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql,
typeof (Channel));
IList<ChannelMap> maps = ObjectFactory.GetCollection<ChannelMap>(statement.Execute());
return maps != null && maps.Count > 0;
}
/// <summary>
/// Add the specified statement to the statement cache.
/// </summary>
/// <param name="type">The business object with which to associate the statement</param>
/// <param name="stmtType">The type of the SQL statement</param>
/// <param name="stmt">The statement instance to cache</param>
public void CacheStatement( Type type, StatementType stmtType, SqlStatement stmt )
{
if( GentleSettings.CacheStatements )
{
Initialize(); // ensure thread local variables have been initialized
Hashtable stmts = (Hashtable) stmtByType[ type ];
if( stmts == null ) // create a new hashtable for this class
{
stmts = new Hashtable();
stmtByType[ type ] = stmts;
}
stmts[ stmtType ] = stmt;
}
}
/// <summary>
/// Gets a list of tv channels sorted by their group
/// </summary>
/// <returns>a list of TVDatabase Channels</returns>
public List<Channel> GetTVGuideChannelsForGroup(int groupID)
{
SqlBuilder sb1 = new SqlBuilder(StatementType.Select, typeof (Channel));
SqlStatement stmt1 = sb1.GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(stmt1.StatementType, stmt1.Command,
String.Format(
"select c.* from Channel c inner join GroupMap g on (c.idChannel=g.idChannel and g.idGroup = '{0}') where visibleInGuide = 1 and isTv = 1 order by g.sortOrder",
groupID), typeof (Channel));
return ObjectFactory.GetCollection<Channel>(ManualJoinSQL.Execute()) as List<Channel>;
}
/// <summary>
/// Constructor that reads and encapsulates results from the data reader. The maximum
/// number of rows to retrieve is obtained from the SqlStatement parameter.
/// </summary>
/// <param name="broker">The PersistenceBroker instance to use for database access.</param>
/// <param name="dr">The DataReader instance to read from</param>
/// <param name="stmt">The SqlStatement leading to this SqlResult</param>
public SqlResult( PersistenceBroker broker, IDataReader dr, SqlStatement stmt ) : base( broker )
{
this.stmt = stmt;
try
{
rows = new ArrayList();
// store column names/order
columnNames = new string[dr.FieldCount];
// also cache fieldmaps for object retrievals (used later for NullValue handling)
ObjectMap map = stmt != null && stmt.Type != null ? ObjectFactory.GetMap( broker, stmt.Type ) : null;
fieldMaps = map != null ? new FieldMap[dr.FieldCount] : null;
// process all columns
for( int i = 0; i < dr.FieldCount; i++ )
{
columnNames[ i ] = dr.GetName( i );
if( map != null )
{
fieldMaps[ i ] = map.GetFieldMapFromColumn( columnNames[ i ] );
}
}
// skip past offset number of rows if SQL Server and paged statement
int skipCount = 0;
if( stmt.RowOffset > 0 && broker.ProviderName == "SQLServer" )
{
while( dr != null && skipCount++ < stmt.RowOffset && dr.Read() )
{
;
}
}
// read and store rows in result set
while( dr != null && dr.Read() && (stmt.RowLimit <= 0 || rows.Count < stmt.RowLimit) )
{
// store values
object[] row = new object[dr.FieldCount];
dr.GetValues( row );
// convert DBNulls to system nulls (or the appropriate null translation value)
for( int i = 0; i < row.Length; i++ )
{
if( row[ i ] == DBNull.Value )
{
row[ i ] = (map != null && fieldMaps[ i ] != null) ? fieldMaps[ i ].NullValue : null;
}
}
rows.Add( row );
}
rowsAffected = rows.Count;
}
//catch( Exception e )
//{
// string msg = e.Message;
// throw;
//}
finally
{
// close the data reader
if( dr != null && ! dr.IsClosed )
{
dr.Close();
}
}
}
