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 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);
}
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 bool SaveVideo(string IdVideo, SupportData Data)
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
if (Data.IsExist)
{
//Purge TEMPLATE_FIELD
String sqlQuery = "DELETE FROM TEMPLATE_FIELD_VIDEO_VALUES WHERE ID_VIDEO ='" + Data.UIdSupport + "'";
dataContext.Execute(sqlQuery);
//UPDATE
sqlQuery = "UPDATE Videos SET " + Environment.NewLine;
sqlQuery += "ID='" + Data.UIdSupport + "'," + Environment.NewLine;
sqlQuery += "EXTENSION='" + Data.Extension + "'," + Environment.NewLine;
sqlQuery += "TC_START=" + Data.TcStart.ToString() + "," + Environment.NewLine;
sqlQuery += "EOM=" + Data.Eom.ToString() + "," + Environment.NewLine;
sqlQuery += "DURATION=" + Data.Duration.ToString() + "," + Environment.NewLine;
sqlQuery += "WIDTH=" + Data.Width.ToString() + "," + Environment.NewLine;
sqlQuery += "HEIGHT=" + Data.Height.ToString() + "," + Environment.NewLine;
sqlQuery += "FRAMERATE=" + (int)Data.FrameRate + "," + Environment.NewLine;
//sqlQuery += "AUDIO_COUNT=" + Data.AudioCount.ToString() + Environment.NewLine;
sqlQuery += "FILE_SIZE=" + Data.FileSize.ToString() + "," + Environment.NewLine;
sqlQuery += "FILE_NAME='" + Data.FileName + "'," + Environment.NewLine;
//sqlQuery += "STATE=" + Data.State + Environment.NewLine;
//sqlQuery += "SUPPORT_ID=" + Environment.NewLine;
sqlQuery += "TITLE='" + Data.Title + "'," + Environment.NewLine;
sqlQuery += "ID_CATEGORY=" + Data.IdCategory.ToString() + Environment.NewLine;
sqlQuery += " WHERE ID='" + IdVideo + "'";
dataContext.Execute(sqlQuery);
}
else
{
//INSERT
CreateVideo(Data);
}
//INSERT NEW TEMPLATE FIELD VALUE
foreach (TemplateFieldValueData TFData in Data.TemplateFields)
{
AddTemplateFieldValueForVideo(Data.UIdSupport, Data.IdCategory, TFData);
}
}
return(true);
}
public void UpdateTemplateField(int IdTemplateField, string newName, TemplateFieldType newType)
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
//UPDATE
String sqlQuery = "UPDATE TEMPLATE_FIELD SET " + Environment.NewLine;
sqlQuery += "LABEL='" + newName + "'" + Environment.NewLine;
sqlQuery += "ID_FIELD_TYPE=" + newType + Environment.NewLine;
sqlQuery += "WHERE ID='" + IdTemplateField + "'";
dataContext.Execute(sqlQuery);
}
}
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 CreateVideo(SupportData Data)
{
SupportData result = Data;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "INSERT INTO Videos(ID,EXTENSION,TC_START,EOM,DURATION,WIDTH,HEIGHT,FRAMERATE,AUDIO_COUNT,FILE_SIZE,FILE_NAME,STATE,SUPPORT_ID,ID_CATEGORY,TITLE)" + Environment.NewLine;
sqlQuery += "VALUES('" + Data.UIdSupport + "','" + Data.Extension + "'," + Data.TcStart + "," + Data.Eom + "," + Data.Duration + "," + Data.Width + "," + Data.Height + "," + (int)Data.FrameRate + "," + 0 + ",'" + Data.FileSize + "','" + Data.FileName + "'," + 0 + ",'0'," + Data.IdCategory + ",'" + Data.Title + "')";
dataContext.Execute(sqlQuery);
result = GetVideoInfo(Data.UIdSupport);
}
return(result);
}
public bool DeleteVideo(string IdVideo, string Volume)
{
bool result = false;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
SupportData data = GetVideoInfo(IdVideo);
dataContext.Execute("DELETE FROM Videos WHERE ID ='" + IdVideo + "'");
System.IO.File.Delete(System.IO.Path.Combine(Volume, data.FileName + data.Extension));
result = true;
}
return(result);
}
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 void CheckSystem()
{
try
{
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
// CHECK IF DATABASE IS OK
String sqlQuery = "SELECT COUNT(Column_Name) as AS_CATEGORY FROM sys.columns WHERE name='Video' AND Column_Name = 'ID_CATEGORY'";
if (!dataContext.CheckSchema("ID_CATEGORY", "Videos"))
{
//CATEGORIES
sqlQuery = "CREATE TABLE CATEGORIES(ID Integer CONSTRAINT PK_CATEGORY PRIMARY KEY,LABEL Text )";
dataContext.Execute(sqlQuery);
sqlQuery = "INSERT INTO CATEGORIES(ID,LABEL) VALUES(0,'Default')";
dataContext.InsertData(sqlQuery);
sqlQuery = "ALTER TABLE Videos ADD COLUMN ID_CATEGORY Integer default 0";
dataContext.Execute(sqlQuery);
sqlQuery = "ALTER TABLE Videos ADD CONSTRAINT FK_CATEGORY FOREIGN KEY(ID_CATEGORY) REFERENCES CATEGORIES(ID)";
dataContext.Execute(sqlQuery);
//TEMPLATE FIELDS
sqlQuery = "CREATE TABLE TEMPLATE_FIELD(ID Integer CONSTRAINT PK_TEMPLATE_FIELD PRIMARY KEY, LABEL Text, ID_FIELD_TYPE Integer)";
dataContext.Execute(sqlQuery);
sqlQuery = "CREATE TABLE TEMPLATE_FIELD_TYPE(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_TYPE PRIMARY KEY, LABEL Text)";
dataContext.Execute(sqlQuery);
sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(0,'Texte')";
dataContext.InsertData(sqlQuery);
sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(1,'Picture')";
dataContext.InsertData(sqlQuery);
sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(2,'Video')";
dataContext.InsertData(sqlQuery);
sqlQuery = "ALTER TABLE TEMPLATE_FIELD ADD CONSTRAINT FK_TEMPLATE_FIELD_TYPE FOREIGN KEY(ID_FIELD_TYPE) REFERENCES TEMPLATE_FIELD_TYPE(ID)";
dataContext.Execute(sqlQuery);
sqlQuery = "CREATE TABLE TEMPLATE_FIELD_CATEGORY(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_CATEGORY PRIMARY KEY,ID_CATEGORY Integer,ID_TEMPLATE_FIELD Integer)";
dataContext.Execute(sqlQuery);
sqlQuery = "ALTER TABLE TEMPLATE_FIELD_CATEGORY ADD CONSTRAINT FK_TEMPLATE_FIELD_CATEGORY_CATEGORY FOREIGN KEY(ID_CATEGORY) REFERENCES CATEGORIES(ID)";
dataContext.Execute(sqlQuery);
sqlQuery = "ALTER TABLE TEMPLATE_FIELD_CATEGORY ADD CONSTRAINT FK_TEMPLATE_FIELD_TEMPLATE_FIELD FOREIGN KEY(ID_TEMPLATE_FIELD) REFERENCES TEMPLATE_FIELD(ID)";
dataContext.Execute(sqlQuery);
//TEMPLATE FIELD VALUES
sqlQuery = "CREATE TABLE TEMPLATE_FIELD_VIDEO_VALUES(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_VIDEO_VALUES PRIMARY KEY, FIELD_VALUE Text, ID_VIDEO Text, ID_TEMPLATE_FIELD_CATEGORY Integer)";
dataContext.Execute(sqlQuery);
sqlQuery = "ALTER TABLE TEMPLATE_FIELD_VIDEO_VALUES ADD CONSTRAINT FK_TEMPLATE_FIELD_VIDEO_VALUES_VIDEO FOREIGN KEY(ID_VIDEO) REFERENCES Videos(ID)";
dataContext.Execute(sqlQuery);
sqlQuery = "ALTER TABLE TEMPLATE_FIELD_VIDEO_VALUES ADD CONSTRAINT FK_TFVV_TFC FOREIGN KEY(ID_TEMPLATE_FIELD_CATEGORY) REFERENCES TEMPLATE_FIELD_CATEGORY(ID)";
dataContext.Execute(sqlQuery);
}
if (!dataContext.CheckSchema("TITLE", "Videos"))
{
sqlQuery = "ALTER TABLE Videos ADD COLUMN TITLE Text";
dataContext.Execute(sqlQuery);
}
}
}
catch (Exception ex)
{ }
}
