public static List<XmlDataRow> GetPlaylistItemData(PlaylistItem playlistItem)
{
List<XmlDataRow> xmlDataRows = new List<XmlDataRow>();
OracleConnection cnO = null;
OracleCommand cmdO = null;
OracleDataAdapter adpO = null;
OracleCommandBuilder bldrO = null;
OracleDataReader rdrO = null;
MySqlConnection cnM = null;
MySqlCommand cmdM = null;
MySqlDataReader rdrM = null;
DataTable tbl = null;
DataSet ds = null;
string teamLogo = " ";
string teamSwatch = " ";
string teamAbbrev = "";
try
{
tbl = new DataTable();
List<Int32> teamIds = new List<Int32>();
Int32 teamId = 0;
FileInfo queryFile = null;
if (playlistItem.Query != null && playlistItem.Query.ToString().Trim() != "")
{
queryFile = new FileInfo(ConfigurationManager.AppSettings["QueryDirectory"].ToString() + "\\" + playlistItem.Query);
}
string query = "";
if (queryFile != null)
{
if (queryFile.Exists)
{
query = File.ReadAllText(queryFile.FullName);
}
else
{
query = playlistItem.Query;
}
Object[] parms = null;
switch (playlistItem.Datasource.ToUpper())
{
case "SDR":
cnO = createConnectionSDR();
cmdO = new OracleCommand();
cmdO.BindByName = true;
//add the parms to the query/stored proc if there are any
if (playlistItem.QueryParameters.ToString() != "")
{
parms = playlistItem.QueryParameters.Split('|');
for (int i = 0; i < parms.Length; i++)
{
string[] parmval = parms[i].ToString().Split('=');
string parm = parmval[0].ToString();
string val = parmval[1].ToString();
if (val.Substring(0, 1) == "'" && val.Substring(val.Length - 1, 1) == "'")
{
string parmStr = val.Substring(1, val.Length - 2);
if (parmStr.Length > 0 && parmStr.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[parmStr.Substring(1)] != null) //pull the value from the app.config file
{
parmStr = ConfigurationManager.AppSettings[parmStr.Substring(1)].ToString();
}
}
if (parmStr.IndexOf(",") > -1 || (parm.IndexOf('(') > -1 && parm.IndexOf(')') > -1)) //this is an array
{
cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Varchar2));
string[] arrVals = parmStr.Split(',');
Int32[] arrSize = new Int32[arrVals.Length - 1];
for (int p = 0; p < arrVals.Length - 1; p++)
{
arrSize[p] = arrVals[p].Length;
}
cmdO.Parameters[parm].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
cmdO.Parameters[parm].Value = arrVals;
cmdO.Parameters[parm].Size = arrVals.Length;
cmdO.Parameters[parm].ArrayBindSize = arrSize;
OracleParameterStatus[] arrStatus = new OracleParameterStatus[1];
arrStatus[0] = 0;
cmdO.Parameters[parm].ArrayBindStatus = arrStatus;
cmdO.Parameters[parm].Direction = ParameterDirection.Input;
}
else
{
cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Varchar2, parmStr, ParameterDirection.Input));
}
}
else if (val.ToUpper() == "NULL")
{
cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.NVarchar2, DBNull.Value, ParameterDirection.Input));
}
else
{
if (val.ToString().ToUpper() != "OTC")
{
if (val.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[val.Substring(1)] != null)
{
val = ConfigurationManager.AppSettings[val.Substring(1)];
}
}
cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Int32, val, ParameterDirection.Input));
}
}
if (parm.ToUpper() == "INTEAMID")
{
//adding a special case for the On The Clock (connected) items, to add the team swatch to the dataset
if (val.ToString().ToUpper() == "OTC")
{
teamId = GlobalCollections.Instance.OnTheClock.Team.ID;
cmdO.Parameters.Add(new OracleParameter(parm, OracleDbType.Int32, teamId, ParameterDirection.Input));
}
else
{
if (val.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[val.Substring(1)] != null)
{
val = ConfigurationManager.AppSettings[val.Substring(1)];
}
}
Int32.TryParse(val, out teamId);
}
teamIds.Add(teamId);
}
}
}
if (playlistItem.QueryType.ToUpper() == "SP")
{
cmdO.Connection = cnO;
cmdO.CommandText = playlistItem.Query;
cmdO.CommandType = System.Data.CommandType.StoredProcedure;
cmdO.BindByName = true;
cmdO.Parameters.Add(new OracleParameter(playlistItem.OutputParameter, OracleDbType.RefCursor, ParameterDirection.Output));
adpO = new OracleDataAdapter(cmdO);
ds = new DataSet();
adpO.Fill(ds);
tbl = ds.Tables[0];
}
else
{
if (query != "")
{
cmdO.CommandText = query.ToString();
cmdO.Connection = cnO;
rdrO = cmdO.ExecuteReader();
tbl.Load(rdrO);
rdrO.Close();
rdrO.Dispose();
}
}
break;
case "MYSQL":
{
cnM = createConnectionMySql();
cmdM = new MySqlCommand(query.ToString(), cnM);
if (playlistItem.QueryType.ToUpper() == "SP")
{
cmdM.CommandType = System.Data.CommandType.StoredProcedure;
if (playlistItem.QueryParameters.ToString() != "")
{
parms = playlistItem.QueryParameters.Split('|');
for (int i = 0; i < parms.Length; i++)
{
string[] parmval = parms[i].ToString().Split('=');
string parm = parmval[0].ToString();
string val = parmval[1].ToString();
if (parm.ToUpper() == "INTEAMID")
{
//adding a special case for the On The Clock (connected) items, to add the team swatch to the dataset
if (val.ToString().ToUpper() == "OTC")
{
teamId = GlobalCollections.Instance.OnTheClock.Team.ID;
val = teamId.ToString();
}
else
{
if (val.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[val.Substring(1)] != null)
{
val = ConfigurationManager.AppSettings[val.Substring(1)];
}
}
Int32.TryParse(val, out teamId);
}
teamIds.Add(teamId);
}
cmdM.Parameters.AddWithValue(parm, val);
}
}
}
rdrM = cmdM.ExecuteReader();
tbl.Load(rdrM);
rdrM.Close();
rdrM.Dispose();
}
break;
case "WS":
parms = playlistItem.QueryParameters.Split('|');
Object[] wsParms = new Object[parms.Length];
for (int i = 0; i < parms.Length; i++)
{
string[] parmval = parms[i].ToString().Split('=');
string parm = parmval[0].ToString();
string val = parmval[1].ToString();
val = val.Replace("'", "");
if (val.Length > 0 && val.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[val.Substring(1)] != null)
{
val = ConfigurationManager.AppSettings[val.Substring(1)];
}
}
wsParms[i] = val;
}
DataSet dsTemp = null;
try
{
dsTemp = WebService.CallFunctionByName(playlistItem.Query, wsParms);
}
catch
{
Debug.Print("Web Service call failed");
dsTemp = null;
}
if (dsTemp != null)
{
tbl = dsTemp.Tables[0];
}
break;
}
}
if (playlistItem.AdditionalDataFields != null)
{
foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields)
{
if (pair.Key.ToString().ToUpper().IndexOf("INTEAMID") > -1)
{
string val = pair.Value;
if (val.Substring(0, 1) == "#")
{
if (ConfigurationManager.AppSettings[val.Substring(1)] != null)
{
val = ConfigurationManager.AppSettings[val.Substring(1)];
}
}
Int32.TryParse(val, out teamId);
teamIds.Add(teamId);
}
}
}
if (tbl.Rows.Count == 0 && playlistItem.Query != null)
{
Debug.Print("No data returned by " + playlistItem.Query);
}
XmlDataRow xmlDataRow;
switch (playlistItem.MaxRows)
{
case 0:
//just add a blank data row
xmlDataRow = new XmlDataRow();
xmlDataRows.Add(xmlDataRow);
break;
case 1:
foreach (DataRow row in tbl.Rows)
{
//DataRow row = tbl.Rows[playlistItem.RowIndex];
xmlDataRow = new XmlDataRow();
foreach (DataColumn col in tbl.Columns)
{
xmlDataRow.Add(col.ColumnName, row[col.ColumnName].ToString());
}
if (playlistItem.Description.ToUpper() != "PROMPTER")
{
xmlDataRow.Add("PANEL_TYPE", playlistItem.PanelType);
xmlDataRow.Add("PAGE_TYPE", playlistItem.PageType);
}
if (playlistItem.AdditionalDataFields != null)
{
foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields)
{
if (pair.Key.ToString().ToUpper().IndexOf("INTEAMID") == -1) //non-teamid items
{
//possibly put a db field to determine if additional fields should be loaded only on the first item
if (playlistItem.CurrentRow == 0)
{
xmlDataRow.Add(pair.Key, pair.Value);
}
}
}
}
xmlDataRows.Add(xmlDataRow);
}
break;
default:
xmlDataRow = new XmlDataRow();
int count = 1;
if (tbl.Rows.Count > 0)
{
foreach (DataRow row in tbl.Rows)
{
if (count > playlistItem.MaxRows)
{
xmlDataRows.Add(xmlDataRow);
xmlDataRow = new XmlDataRow();
count = 1;
}
foreach (DataColumn col in tbl.Columns)
{
xmlDataRow.Add(col.ColumnName + "_" + count.ToString(), row[col.ColumnName].ToString());
}
xmlDataRow.Add("PANEL_TYPE", playlistItem.PanelType);
xmlDataRow.Add("PAGE_TYPE", playlistItem.PageType);
if (playlistItem.AdditionalDataFields != null)
{
foreach (KeyValuePair<string, string> pair in playlistItem.AdditionalDataFields)
{
//possibly put a db field to determine if additional fields should be loaded only on the first item
if (playlistItem.CurrentRow == 0)
{
xmlDataRow.Add(pair.Key, pair.Value);
}
}
}
count++;
}
////blank out any extra fields that aren't filled...
for (int i = count; i <= playlistItem.MaxRows; i++)
{
foreach (DataColumn col in tbl.Columns)
{
xmlDataRow.Add(col.ColumnName + "_" + i.ToString(), "");
}
}
xmlDataRows.Add(xmlDataRow); //adds in the last row
}
break;
}
//add all the team data to each xmlDataRow (so the team info is included with each data row)
if (teamIds.Count > 0)
{
for (var i = 0; i < teamIds.Count; i++)
{
Team team = null;
if (GlobalCollections.Instance.Teams != null)
{
team = (Team)GlobalCollections.Instance.Teams.SingleOrDefault(t => t.ID == teamIds[i]);
if (team == null)
{
team = (Team)GlobalCollections.Instance.Schools.SingleOrDefault(t => t.ID == teamIds[i]);
}
}
else
{
team = (Team)GlobalCollections.Instance.Schools.SingleOrDefault(t => t.ID == teamIds[i]);
}
if (team != null)
{
teamLogo = team.LogoTgaNoKey.LocalPath;
FileInfo file = new FileInfo(teamLogo);
if (file.Exists == false)
{
teamLogo = " ";
}
teamSwatch = team.SwatchTga.LocalPath;
file = new FileInfo(teamSwatch);
if (file.Exists == false)
{
teamSwatch = " ";
}
teamAbbrev = team.Tricode;
foreach (XmlDataRow xmlRow in xmlDataRows)
{
xmlRow.Add("LOGO_" + (i + 1).ToString(), teamLogo);
xmlRow.Add("ABBREV_4_" + (i + 1).ToString(), teamAbbrev);
xmlRow.Add("SWATCH_" + (i + 1).ToString(), teamSwatch);
xmlRow.Add("VENT_SWATCH_" + (i + 1).ToString(), teamSwatch);
}
}
}
}
}
catch (Exception ex)
{
SetStatusBarMsg("Error getting playlist item data (item " + playlistItem.PlaylistOrder.ToString() + "): " + ex.Message, "Red");
xmlDataRows.Clear();
}
finally
{
if (cmdO != null) cmdO.Dispose();
if (cmdM != null) cmdM.Dispose();
if (tbl != null) tbl.Dispose();
if (cnO != null)
{
cnO.Close();
cnO.Dispose();
}
if (cnM != null)
{
cnM.Close();
cnM.Dispose();
}
}
return xmlDataRows;
}
public static ObservableCollection<PlaylistItem> GetPlaylistItems(int playlistId)
{
ObservableCollection<PlaylistItem> playlistItems = new ObservableCollection<PlaylistItem>();
MySqlConnection cn = null;
MySqlCommand cmd = null;
MySqlDataReader rdr = null;
DataTable tbl = null;
try
{
cn = createConnectionMySql();
String sql = "select * from playlistitems where playlistid = " + playlistId + " order by playlistorder asc";
cmd = new MySqlCommand(sql, cn);
rdr = cmd.ExecuteReader();
tbl = new DataTable();
tbl.Load(rdr);
rdr.Close();
rdr.Dispose();
PlaylistItem playlistItem;
foreach (DataRow row in tbl.Rows)
{
playlistItem = new PlaylistItem();
playlistItem.Template = row["template"].ToString();
playlistItem.PanelType = row["paneltype"].ToString();
playlistItem.PageType = row["pagetype"].ToString();
playlistItem.Description = row["desc"].ToString();
playlistItem.Datasource = row["datasource"].ToString();
playlistItem.QueryType = row["querytype"].ToString();
playlistItem.QueryParameters = row["queryparameters"].ToString();
playlistItem.OutputParameter = row["outputparameter"].ToString();
if (row["enabled"] != DBNull.Value)
{
playlistItem.Enabled = Convert.ToBoolean(row["enabled"]);
}
else
{
playlistItem.Enabled = false;
}
if (row["mergedatanotransitions"] != DBNull.Value)
{
playlistItem.MergeDataNoTransitions = Convert.ToBoolean(row["mergedatanotransitions"]);
}
else
{
playlistItem.MergeDataNoTransitions = false;
}
if (row["playlistorder"] != DBNull.Value)
{
playlistItem.PlaylistOrder = Convert.ToInt16(row["playlistorder"]);
}
else
{
playlistItem.PlaylistOrder = 9999;
}
if (row["duration"] != DBNull.Value)
{
playlistItem.Duration = Convert.ToDecimal(row["duration"]);
}
else
{
playlistItem.Duration = 5;
}
playlistItem.Query = row["query"].ToString();
if (row["maxrows"] != DBNull.Value)
{
playlistItem.MaxRows = Convert.ToInt16(row["maxrows"]);
}
else
{
playlistItem.MaxRows = 1;
}
//if (row["rowindex"] != DBNull.Value)
//{
// playlistItem.RowIndex = Convert.ToInt16(row["rowindex"]);
//}
//else
//{
// playlistItem.RowIndex = 0;
//}
if (row["additionaldatafields"].ToString().Trim() != "")
{
string[] additionalDataFields = row["additionaldatafields"].ToString().Split('|');
if (additionalDataFields.Length > 0)
{
playlistItem.AdditionalDataFields = new Dictionary<string, string>();
for (int i = 0; i < additionalDataFields.Length; i++)
{
string[] keyVal = additionalDataFields[i].ToString().Split('=');
playlistItem.AdditionalDataFields.Add(keyVal[0], keyVal[1].Trim());
}
}
}
playlistItems.Add(playlistItem);
}
}
finally
{
if (cmd != null) cmd.Dispose();
if (tbl != null) tbl.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
return playlistItems;
}
