public void DeleteCategory(long CategoryId)
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT * FROM CATEGORIES WHERE ID = " + CategoryId;
OleDbDataReader queryData = dataContext.Select(sqlQuery);
if (queryData.HasRows && queryData.Read())
{
string queryTemplateField = "SELECT TFC.TEMPLATE_FIELD_ID AS ID_TF " + Environment.NewLine
+ " FROM TEMPLATE_FIELD_CATEGORY AS TFC INNER JOIN TEMPLATE_FIELD AS TF ON TFC.ID_TEMPLATE_FIELD=TF.ID" + Environment.NewLine
+ " WHERE TFC.ID_CATEGORY = " + CategoryId;
List <TemplateFieldData> categoryTfDatas = new List <TemplateFieldData>();
OleDbDataReader tfFieldData = dataContext.Select(queryTemplateField);
if (tfFieldData.HasRows)
{
while (tfFieldData.Read())
{
int idxColumn = queryData.GetOrdinal("ID_TF");
long tfFieldId = queryData.GetInt32(idxColumn);
this.DeleteTemplateField(tfFieldId);
}
}
}
}
}
public Category[] GetCategories()
{
List <Category> result = new List <Category>();
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
// CHECK IF DATABASE IS OK
String sqlQuery = "SELECT ID, LABEL FROM CATEGORIES";
OleDbDataReader queryData = dataContext.Select(sqlQuery);
if (queryData.HasRows)
{
bool hasRecord = queryData.Read();
while (hasRecord)
{
Category newCatagory = new Category();
int idxColumn = queryData.GetOrdinal("ID");
newCatagory.Id = queryData.GetInt32(idxColumn);
idxColumn = queryData.GetOrdinal("LABEL");
newCatagory.Name = queryData.GetString(idxColumn);
string queryTemplateField = "SELECT TFC.ID AS ID_TFC, TF.LABEL AS LABEL_FIELD, TF.ID_FIELD_TYPE AS TYPE_FIELD " + Environment.NewLine
+ " FROM TEMPLATE_FIELD_CATEGORY AS TFC INNER JOIN TEMPLATE_FIELD AS TF ON TFC.ID_TEMPLATE_FIELD=TF.ID" + Environment.NewLine
+ " WHERE TFC.ID_CATEGORY = " + newCatagory.Id;
List <TemplateFieldData> categoryTfDatas = new List <TemplateFieldData>();
OleDbDataReader tfFieldData = dataContext.Select(queryTemplateField);
if (tfFieldData.HasRows)
{
bool hasTfData = true;
while (tfFieldData.Read())
{
TemplateFieldData dataTF = new TemplateFieldData();
idxColumn = tfFieldData.GetOrdinal("ID_TFC");
dataTF.Id = tfFieldData.GetInt32(idxColumn);
idxColumn = tfFieldData.GetOrdinal("LABEL_FIELD");
dataTF.FieldName = tfFieldData.GetString(idxColumn);
idxColumn = tfFieldData.GetOrdinal("TYPE_FIELD");
int typeId = tfFieldData.GetInt32(idxColumn);
dataTF.FieldType = (TemplateFieldType)typeId;
dataTF.IdFieldCategory = dataTF.Id;
//hasTfData = tfFieldData.Read();
categoryTfDatas.Add(dataTF);
}
}
tfFieldData.Close();
newCatagory.TemplateFields = categoryTfDatas.ToArray();
result.Add(newCatagory);
hasRecord = queryData.Read();
}
queryData.Close();
}
}
return(result.ToArray());
}
public bool TemplateFieldExists(string name, TemplateFieldType type)
{
bool result = false;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT COUNT(ID) + 1 AS ISEXIST FROM TEMPLATE_FIELD WHERE LABEL = '" + name + "' AND ID_FIELD_TYPE = " + type;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows && resultData.Read())
{
int idxColumn = resultData.GetOrdinal("ISEXIST");
object value = resultData.GetValue(idxColumn);
if (typeof(DBNull) == value.GetType())
{
result = false;
}
else
{
result = true;//resultData.GetInt32(idxColumn);
}
}
resultData.Close();
}
return(result);
}
private int _AddTemplateFieldToCategory(int IdTemplateField, int IdCategory)
{
int result = -1;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD_CATEGORY";
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows && resultData.Read())
{
int idxColumn = resultData.GetOrdinal("NEW_ID");
object value = resultData.GetValue(idxColumn);
if (typeof(DBNull) == value.GetType())
{
result = 1;
}
else
{
result = (int)value;//resultData.GetInt32(idxColumn);
}
}
else
{
result = 1;
}
sqlQuery = "INSERT INTO TEMPLATE_FIELD_CATEGORY(ID,ID_CATEGORY,ID_TEMPLATE_FIELD) VALUES(" + result + "," + IdCategory + "," + IdTemplateField + ")";
dataContext.Execute(sqlQuery);
resultData.Close();
}
return(result);
}
public Devices[] GetDeviceList()
{
List <Devices> deviceList = new List <Devices>();
try
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
OleDbDataReader result = dataContext.Select("SELECT DEVICE,DEVICE-TYPE FROM Devices");
if (result.HasRows)
{
while (result.Read())
{
Devices ADevice = new Devices();
int idxColumn = result.GetOrdinal("DEVICE");
ADevice.Device = result.GetString(idxColumn);
idxColumn = result.GetOrdinal("DEVICE-TYPE");
ADevice.DeviceType = (uint)result.GetInt32(idxColumn);
deviceList.Add(ADevice);
}
}
result.Close();
}
}
catch (Exception ex)
{ }
return(deviceList.ToArray());
}
private int _AddTemplateField(TemplateFieldData FieldData)
{
int result = -1;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD";
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows && resultData.Read())
{
int idxColumn = resultData.GetOrdinal("NEW_ID");
object value = resultData.GetValue(idxColumn);
if (typeof(DBNull) == value.GetType())
{
result = 1;
}
else
{
result = (int)value;//resultData.GetInt32(idxColumn);
}
}
else
{
result = 1;
}
resultData.Close();
sqlQuery = "INSERT INTO TEMPLATE_FIELD(ID,LABEL,ID_FIELD_TYPE) VALUES(" + result + ",'" + FieldData.FieldName + "'," + (FieldData.FieldType == TemplateFieldType.Text ? "0" : "1") + ")";
dataContext.Execute(sqlQuery);
}
return(result);
}
public Config GetConfig(string Version = "")
{
Config pikoConfig = null;
try
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT VERSION,SUPPORTS_ID,HASRUNS_ID FROM Config";
if (!String.IsNullOrEmpty(Version))
{
sqlQuery += " WHERE VERSION = '" + Version + "'";
}
OleDbDataReader result = dataContext.Select(sqlQuery);
if (result.HasRows)
{
pikoConfig = new Config();
int idxColumn = result.GetOrdinal("VERSION");
pikoConfig.Version = (ulong)result.GetInt64(idxColumn);
idxColumn = result.GetOrdinal("SUPPORTS_ID");
pikoConfig.SupportsId = result.GetString(idxColumn);
idxColumn = result.GetOrdinal("HASRUNS_ID");
pikoConfig.HasRunsId = result.GetString(idxColumn);
}
result.Close();
}
}
catch (Exception ex)
{ }
return(pikoConfig);
}
public Channel[] GetChannels()
{
List <Channel> channelList = new List <Channel>();
try
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
OleDbDataReader result = dataContext.Select("SELECT CHANNEL_NAME, LAST_HASRUN_DATE FROM Channels");
if (result.HasRows)
{
while (result.Read())
{
Channel AChannel = new Channel();
int idxColumn = result.GetOrdinal("CHANNEL_NAME");
AChannel.ChannelName = result.GetString(idxColumn);
idxColumn = result.GetOrdinal("LAST_HASRUN_DATE");
AChannel.LastHasRunDate = result.GetDateTime(idxColumn);
channelList.Add(AChannel);
}
}
result.Close();
}
}
catch (Exception ex)
{ }
return(channelList.ToArray());
}
public int CreateCategory(Category categoryToCreate)
{
int categoryId = -1;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
if (categoryToCreate.Id == -1)
{
String sqlQuery = "SELECT MAX(ID) FROM CATEGORIES";
OleDbDataReader queryData = dataContext.Select(sqlQuery);
if (queryData.HasRows && queryData.Read())
{
int idxColumn = queryData.GetOrdinal("ID");
categoryToCreate.Id = queryData.GetInt32(idxColumn) + 1;
queryData.Close();
sqlQuery = "INSERT INTO CATEGORIES(ID,LABEL) VALUES(" + categoryToCreate.Id + ",'" + categoryToCreate.Name + "')";
dataContext.Execute(sqlQuery);
}
}
else
{
//UPDATE
String sqlQuery = "UPDATE CATEGORIES SET " + Environment.NewLine;
sqlQuery += "LABEL='" + categoryToCreate.Name + "'" + Environment.NewLine;
sqlQuery += "WHERE ID=" + categoryToCreate.Id;
dataContext.Execute(sqlQuery);
}
if (categoryToCreate.TemplateFields != null && categoryToCreate.TemplateFields.Length > 0)
{
Category currentDbCategory = this.GetCategory(categoryToCreate.Id);
foreach (TemplateFieldData tfData in categoryToCreate.TemplateFields)
{
if (tfData.Id == -1)
{
if (!this.TemplateFieldExists(tfData.FieldName, tfData.FieldType))
{
this.AddTemplateField(tfData);
}
}
else
{
//Update template field
this.UpdateTemplateField(tfData.Id, tfData.FieldName, tfData.FieldType);
}
}
}
}
return(categoryId);
}
private void AddTemplateFieldValueForVideo(string IdVideo, int IdCategory, TemplateFieldValueData ValueData)
{
//Test if Field Exist for this category
int idFieldCategory = ValueData.FieldDefinition.IdFieldCategory;
if (idFieldCategory == -1)
{
int idTemplateField = ValueData.FieldDefinition.Id;
if (idTemplateField == -1)
{
idTemplateField = _AddTemplateField(ValueData.FieldDefinition);
}
if (idFieldCategory == -1)
{
idFieldCategory = _AddTemplateFieldToCategory(idTemplateField, IdCategory);
}
}
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "";
if (ValueData.IdValue == -1)
{
sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD_VIDEO_VALUES";
OleDbDataReader resultData = dataContext.Select(sqlQuery);
int newId = 1;
if (resultData.HasRows && resultData.Read() == true)
{
int idxColumn = resultData.GetOrdinal("NEW_ID");
object value = resultData.GetValue(idxColumn);
if (typeof(DBNull) == value.GetType())
{
newId = 1;
}
else
{
newId = (int)value;//resultData.GetInt32(idxColumn);
}
}
sqlQuery = "INSERT INTO TEMPLATE_FIELD_VIDEO_VALUES(ID,FIELD_VALUE,ID_VIDEO,ID_TEMPLATE_FIELD_CATEGORY) VALUES(" + newId + ",'" + ValueData.Value + "','" + IdVideo + "'," + idFieldCategory + ")";
}
else
{
sqlQuery = "UPDATE TEMPLATE_FIELD_VIDEO_VALUES SET " + Environment.NewLine;
sqlQuery += "FIELD_VALUE='" + ValueData.Value + "'" + Environment.NewLine;
sqlQuery += "WHERE ID_VIDEO ='" + IdVideo + "'" + Environment.NewLine;
}
dataContext.Execute(sqlQuery);
}
}
public bool TemplateFieldCategoryExists(int IdCategory, int IdTemplateField)
{
bool result = false;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT COUNT(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD_CATEGORY WHERE ID_CATEGORY = " + IdCategory + " AND ID_TEMPLATE_FIELD = " + IdTemplateField;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows)
{
int idxColumn = resultData.GetOrdinal("ISEXIST");
result = resultData.GetInt32(idxColumn) > 0;
}
}
return(result);
}
public TemplateFieldData GetTemplateField(int IdTemplateField)
{
TemplateFieldData result = null;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT ID, LABEL, ID_FIELD_TYPE FROM TEMPLATE_FIELD WHERE ID = " + IdTemplateField;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows)
{
result = new TemplateFieldData();
int idxColumn = resultData.GetOrdinal("ID");
result.Id = resultData.GetInt32(idxColumn);
idxColumn = resultData.GetOrdinal("LABEL");
result.FieldName = resultData.GetString(idxColumn);
idxColumn = resultData.GetOrdinal("ID");
result.FieldType = (TemplateFieldType)resultData.GetInt32(idxColumn);
}
}
return(result);
}
private void DeleteTemplateField(long TemplateFieldId)
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT ID FROM TEMPLATE_FIELD_CATEGORY WHERE ID_TEMPLATE_FIELD = " + TemplateFieldId;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows)
{
while (resultData.Read())
{
int idxColumn = resultData.GetOrdinal("ID");
long IdTFC = resultData.GetInt32(idxColumn);
sqlQuery = "DELETE FROM TEMPLATE_FIELD_VIDEO_VALUES WHERE ID_TEMPLATE_FIELD_CATEGORY = " + IdTFC;
dataContext.Execute(sqlQuery);
}
sqlQuery = "DELETE FROM TEMPLATE_FIELD_CATEGORY WHERE ID_TEMPLATE_FIELD = " + TemplateFieldId;
dataContext.Execute(sqlQuery);
sqlQuery = "DELETE FROM TEMPLATE_FIELD WHERE ID = " + TemplateFieldId;
dataContext.Execute(sqlQuery);
}
}
}
public SupportData GetVideoInfo(string IdVideo)
{
SupportData videoInfo = null;
try
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT ID, EXTENSION,TC_START,EOM,DURATION,WIDTH,HEIGHT,FRAMERATE,AUDIO_COUNT,FILE_SIZE,FILE_NAME,STATE,SUPPORT_ID,ID_CATEGORY,TITLE FROM Videos WHERE Id = '" + IdVideo + "'";
OleDbDataReader result = dataContext.Select(sqlQuery);
if (result.HasRows)
{
if (result.Read())
{
videoInfo = new SupportData();
int idxColumn = result.GetOrdinal("ID");
videoInfo.UIdSupport = result.GetString(idxColumn);
if (videoInfo.UIdSupport == null)
{
videoInfo.UIdSupport = IdVideo;
}
idxColumn = result.GetOrdinal("EXTENSION");
videoInfo.Extension = result.GetString(idxColumn);
if (videoInfo.Extension == null)
{
videoInfo.Extension = "";
}
idxColumn = result.GetOrdinal("TC_START");
videoInfo.TcStart = (ulong)result.GetInt32(idxColumn);
idxColumn = result.GetOrdinal("FILE_NAME");
videoInfo.FileName = result.GetString(idxColumn);
if (videoInfo.FileName == null)
{
videoInfo.FileName = "";
}
videoInfo.IsExist = true;
idxColumn = result.GetOrdinal("FILE_SIZE");
videoInfo.FileSize = long.Parse(result.GetString(idxColumn));
idxColumn = result.GetOrdinal("ID_CATEGORY");
videoInfo.IdCategory = result.GetInt32(idxColumn);
videoInfo.FullPath = videoInfo.FileName + videoInfo.Extension;
idxColumn = result.GetOrdinal("TITLE");
videoInfo.Title = result.GetString(idxColumn);
result.Close();
sqlQuery = "SELECT TFC.ID, TFC.ID_TEMPLATE_FIELD, TF.LABEL AS LABEL_TEMPLATE_FIELD,TF.ID_FIELD_TYPE " + Environment.NewLine;
sqlQuery += "FROM TEMPLATE_FIELD AS TF " + Environment.NewLine + "INNER JOIN TEMPLATE_FIELD_CATEGORY AS TFC ON TF.ID = TFC.ID_TEMPLATE_FIELD " + Environment.NewLine;
sqlQuery += "WHERE ID_CATEGORY = " + videoInfo.IdCategory.ToString();
//sqlQuery += "LEFT JOIN TEMPLATE_FIELD_VIDEO_VALUES AS TFVV ON TFC.ID = TFVV.ID_TEMPLATE_FIELD " + Environment.NewLine;
//sqlQuery += "WHERE TFVV.ID_VIDEO = '" + IdVideo + "' AND ID_CATEGORY = " + videoInfo.IdCategory.ToString();
result = dataContext.Select(sqlQuery);
if (result.HasRows)
{
List <TemplateFieldValueData> listTFData = new List <TemplateFieldValueData>();
while (result.Read())
{
idxColumn = result.GetOrdinal("ID");
int idTfc = result.GetInt32(idxColumn);
//TFVV.ID AS ID_VALUE,TFVV.VALUE,TFVV.ID_VIDEO
string sqlQuery2 = "SELECT ID,FIELD_VALUE,ID_VIDEO " + Environment.NewLine;
sqlQuery2 += "FROM TEMPLATE_FIELD_VIDEO_VALUES " + Environment.NewLine;
sqlQuery2 += "WHERE ID_VIDEO = '" + IdVideo + "' AND ID_TEMPLATE_FIELD_CATEGORY = " + idTfc.ToString();
OleDbDataReader result2 = dataContext.Select(sqlQuery2);
TemplateFieldValueData tfValueData = new TemplateFieldValueData();
//FIELD DEFINITION
tfValueData.FieldDefinition = new TemplateFieldData();
idxColumn = result.GetOrdinal("ID");
tfValueData.FieldDefinition.Id = result.GetInt32(idxColumn);
tfValueData.FieldDefinition.IdFieldCategory = idTfc;
idxColumn = result.GetOrdinal("LABEL_TEMPLATE_FIELD");
tfValueData.FieldDefinition.FieldName = result.GetString(idxColumn);
idxColumn = result.GetOrdinal("ID_FIELD_TYPE");
tfValueData.FieldDefinition.FieldType = (TemplateFieldType)result.GetInt32(idxColumn);
if (result2.HasRows && result2.Read())
{
//VALUE
idxColumn = result2.GetOrdinal("FIELD_VALUE");
tfValueData.Value = result2.GetString(idxColumn);
}
else
{
tfValueData.Value = "";
}
result2.Close();
listTFData.Add(tfValueData);
}
videoInfo.TemplateFields = listTFData.ToArray();
}
else
{
videoInfo.TemplateFields = new TemplateFieldValueData[0];
}
result.Close();
}
}
}
}
catch (Exception ex)
{
}
if (videoInfo == null)
{
videoInfo = new SupportData();
videoInfo.TemplateFields = new TemplateFieldValueData[0];
videoInfo.FullPath = "";
videoInfo.Title = "";
videoInfo.FileName = "";
videoInfo.Extension = "";
videoInfo.UIdSupport = "";
}
return(videoInfo);
}