private async Task <IEnumerable <Channel> > GetAsync(string command)
{
List <Channel> channels = new List <Channel>();
await DBFactory.SQL_ExecuteReaderAsync(WebApiConfig.ConnectionString, command, System.Data.CommandType.Text, null, dr =>
{
var clCtrl = new ChannelLogoController();
while (dr.Read())
{
var chan = new Channel();
chan.ServiceID = int.Parse(dr.GetString(0));
chan.ChannelNumber = dr.GetInt32(1);
chan.CallSign = dr.GetString(2);
chan.StationName = dr.GetString(3);
chan.Logo = clCtrl.getLogoInfo(dr.GetInt32(4));
chan.Version = dr.IsDBNull(5) ? null : dr.GetString(5);
chan.VHO = dr.IsDBNull(6) ? null : dr.GetString(6);
chan.LastUpdate = dr.IsDBNull(7) ? (DateTime?)null : dr.GetDateTime(7);
chan.RegionID = int.Parse(dr.GetString(8));
chan.RegionName = dr.IsDBNull(9) ? null : dr.GetString(9);
chan.StationDescription = dr.IsDBNull(10) ? null : dr.GetString(10);
chan.UniqueString = dr.IsDBNull(11) ? null : dr.GetString(11);
chan.IsVirtual = dr.GetBoolean(12);
channels.Add(chan);
}
});
return(channels);
}
private async Task <IEnumerable <Channel> > getChannelWithInactiveLogo(int?vhoId = null, int?regionId = null, string regionName = null, string version = "1.9")
{
StringBuilder command = new StringBuilder();
StringBuilder conditions = new StringBuilder();
command.AppendFormat("SELECT intBitmapId FROM vChannelMap WHERE strFIOSVersionAliasId = '{0}'", version);
if (null != vhoId)
{
conditions.AppendFormat(" AND strVHOId = 'VHO{0}'", vhoId);
}
if (null != regionId)
{
conditions.AppendFormat(" AND strFIOSRegionId = '{0}'", regionId);
}
if (!string.IsNullOrEmpty(regionName))
{
conditions.AppendFormat(" AND strFIOSRegionName LIKE '{0}'", regionName);
}
command.Append(conditions.ToString());
var clCtrl = new ChannelLogoController();
var missingIdsFromDir = await clCtrl.getMissingBitmapIds();
List <int> existingIdsFromDB = new List <int>();
existingIdsFromDB.AddRange((await GetAsync(command.ToString())).Select(x => x.Logo.BitmapId));
//await DBFactory.SQL_ExecuteReaderAsync(WebApiConfig.ConnectionString, command.ToString(), System.Data.CommandType.Text, null, dr =>
//{
// while (dr.Read())
// {
// existingIdsFromDB.Add(dr.GetInt32(0));
// }
//});
List <int> assignedMissingIds = existingIdsFromDB.Intersect(missingIdsFromDir).ToList();
var strJoined = String.Join(",", assignedMissingIds);
command.Clear();
command.AppendFormat("SELECT * FROM vChannelMap WHERE strFIOSVersionAliasId = '{0}' AND intBitmapId IN ({1})", version, strJoined);
command.Append(conditions.ToString());
return(await GetAsync(command.ToString()));
}
