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 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);
}
