public System.Data.DataTable listItems(ITEMFILTER filter, String param)
{
try
{
string query = "SELECT dataitems.* FROM dataitems";
if (filter == ITEMFILTER.CATEGORY)
{
query += " WHERE category='" + param + "';";
}
else if (filter == ITEMFILTER.DATAITEMID)
{
query += " WHERE iddataitems='" + param + "';";
}
else if (filter == ITEMFILTER.CANVASSLICE)
{
query = "SELECT * FROM canvasitem";
if (param != null)
{
query += " WHERE idcanvasslice='" + param + "' ORDER By clockposition";
}
}
else if (filter == ITEMFILTER.CANVASPERIOD)
{
query = "SELECT * FROM canvasslice, canvasperiod_items";
if (param != null)
{
query += " WHERE idcanvasslice=idslice AND idperiod = '" + param + "' ORDER by position asc";
}
}
//prepare adapter to run query
adapter = new MySqlDataAdapter(query, connection);
DataSet DS = new DataSet();
//get query results in dataset
adapter.Fill(DS);
return DS.Tables[0];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return null;
}
public void loadDataGrid(ITEMFILTER filter=ITEMFILTER.NONE, String param="")
{
items = new DataItems(this);
items.loadDataFromDB(filter, param);
if (filter == ITEMFILTER.CANVASPERIOD)
{
this.currentDisplay = ITEMFILTER.CANVASPERIOD;
this.grid.Columns.Clear();
this.grid.Columns.Add(newTextCol("id", "id"));
this.grid.Columns.Add(newTextCol("label", "label"));
this.UIData = items.getUIDataTableCanvasPeriod();
}
else if (filter == ITEMFILTER.CANVASSLICE)
{
this.currentDisplay = ITEMFILTER.CANVASSLICE;
this.grid.Columns.Clear();
this.grid.Columns.Add(newTextCol("id", "id"));
this.grid.Columns.Add(newTextCol("type", "type"));
this.grid.Columns.Add(newTextCol("sliceposition", "sliceposition"));
this.grid.Columns.Add(newTextCol("label", "label"));
this.grid.Columns.Add(newTextCol("param1", "param1"));
this.grid.Columns.Add(newTextCol("param2", "param2"));
this.grid.Columns.Add(newTextCol("param3", "param3"));
this.UIData = items.getUIDataTableCanvasSlice();
}
//CanvasPeriod
else
{
this.currentDisplay = ITEMFILTER.CATEGORY;
this.grid.Columns.Clear();
this.grid.Columns.Add(newTextCol("data.ID", "id"));
this.grid.Columns.Add(newTextCol("data.label", "label"));
this.grid.Columns.Add(newTextCol("data.runtime", "runtime"));
this.grid.Columns.Add(newTextCol("data.category", "category"));
this.UIData = items.getUIDataTable();
}
}
public void loadDataFromDB(ITEMFILTER filter = ITEMFILTER.NONE, String param=null)
{
try
{
dbengine = DBengineMySql.GetInstance();
//TODO: CHECK CHANGES???
dataitems.Clear();
dbengine.connect("127.0.0.1", "root", "1234", "ebuplayout-dev2");
_datatable = dbengine.listItems(filter, param);
if (filter == ITEMFILTER.CATEGORY)
{
for (int i = 0; i < _datatable.Rows.Count; i++)
{
if (_datatable.Rows[i]["type"].ToString() == "AUDIOFILE")
{
DataRow data = _datatable.Rows[i];
int id = (int)data["iddataitems"];
String artist = data["artist"].ToString();
String title = data["title"].ToString();
String filename = data["file"].ToString();
String radiovis1 = data["radiovis1"].ToString();
String radiovis2 = data["radiovis2"].ToString();
String radiovis3 = data["radiovis3"].ToString();
String radiovis4 = data["radiovis4"].ToString();
String radiovistxt = data["radiovistxt"].ToString();
String category = data["category"].ToString();
TimeSpan tmcue = TimeSpan.FromMilliseconds((int)data["tmcue"]);
TimeSpan tmnext = TimeSpan.FromMilliseconds((int)data["tmnext"]);
if (tmnext == TimeSpan.Zero)
{
tmnext = TimeSpan.FromMilliseconds((int)data["runtime"]);
}
DataSongItem song = new DataSongItem(id, artist, title, filename, new TimeMarker(tmcue, tmnext));
song.category = category;
song.setField("radiovis1", radiovis1);
song.setField("radiovis2", radiovis2);
song.setField("radiovis3", radiovis3);
song.setField("radiovis4", radiovis4);
song.setField("radiovistxt", radiovistxt);
dataitems.Add(id, song);
}
}
}
else if(filter == ITEMFILTER.CANVASPERIOD){
slicecanvas.Clear();
for (int i = 0; i < _datatable.Rows.Count; i++)
{
DataRow data = _datatable.Rows[i];
SliceCanvasItem c = new SliceCanvasItem(data["idcanvasperiod_items"].ToString(), (int)data["position"], data["idslice"].ToString(), data["name"].ToString(), TimeSpan.FromMilliseconds(0));
slicecanvas.Add(Int32.Parse(c.idperioditem), c);
}
}
else if (filter == ITEMFILTER.CANVASSLICE)
{
sliceitems.Clear();
for (int i = 0; i < _datatable.Rows.Count; i++)
{
DataRow data = _datatable.Rows[i];
SlotCanvas slot = new SlotCanvas(Int32.Parse(data["idcanvasitem"].ToString()), Int32.Parse(data["idcanvasslice"].ToString()), Int32.Parse(data["clockposition"].ToString()), getSlotCanvasType(data["type"]), data["param1"].ToString(), data["param2"].ToString(), data["param3"].ToString(), data["label"].ToString());
sliceitems.Add(slot.id, slot);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
