private static string BuildCommandTextMiniGuide(string aProvider, ICollection<Channel> aEpgChannelList)
{
string completeStatement;
// no channel = no EPG but we need a valid command text
if (aEpgChannelList.Count < 1)
{
completeStatement = "SELECT * FROM Program WHERE 0=1";
}
else
{
StringBuilder sbSelect = new StringBuilder();
if (aProvider == "mysql")
{
foreach (Channel ch in aEpgChannelList)
{
sbSelect.AppendFormat(
"(SELECT idChannel,idProgram,starttime,endtime,title,episodeName,seriesNum,episodeNum,episodePart FROM Program WHERE idChannel={0} AND (Program.endtime >= NOW()) order by starttime limit 2) UNION ",
ch.IdChannel);
}
completeStatement = sbSelect.ToString();
completeStatement = completeStatement.Remove(completeStatement.Length - 8); // Remove trailing UNION
}
else
{
//foreach (Channel ch in aEpgChannelList)
// sbSelect.AppendFormat("(SELECT TOP 2 idChannel,idProgram,starttime,endtime,title FROM Program WHERE idChannel={0} AND (Program.endtime >= getdate())) UNION ALL ", ch.IdChannel);
//completeStatement = sbSelect.ToString();
//completeStatement = completeStatement.Remove(completeStatement.Length - 12); // Remove trailing UNION ALL
//completeStatement = completeStatement + " ORDER BY idChannel, startTime"; // MSSQL does not support order by in single UNION selects
sbSelect.Append(
"SELECT idChannel,idProgram,starttime,endtime,title,episodeName,seriesNum,episodeNum,episodePart FROM Program ");
sbSelect.Append("WHERE (Program.endtime >= getdate() AND Program.endtime < DATEADD(day, 1, getdate()))");
StringBuilder whereChannel = new StringBuilder(" AND (");
foreach (Channel ch in aEpgChannelList)
{
whereChannel.AppendFormat("idChannel={0} OR ", ch.IdChannel);
}
string channelClause = whereChannel.ToString();
// remove trailing "OR "
channelClause = channelClause.Remove(channelClause.Length - 3);
sbSelect.Append(channelClause);
sbSelect.Append(")");
sbSelect.Append(" ORDER BY idchannel,starttime");
completeStatement = sbSelect.ToString();
}
}
// Log.Info("BusinessLayer: mini-guide command: {0}", completeStatement);
return completeStatement;
}