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