using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using servicesDAO;
using System.Data;

namespace server
{
    public class ServicesDAO
    {
        protected readonly DataAccess data;
        protected String error = String.Empty;

        public ServicesDAO(DataAccess dataAccess)
        {
            data = dataAccess;
        }

        #region funciones
        /// <summary>
        /// Método que convierte una tabla DataTable en una cadena  de texto formato JSON
        /// </summary>
        /// <param name="dataTable">Tabla de datos a convertir</param>
        /// <returns>Cadena de texto en formato JSON</returns>
        public string DataTableToJson(DataTable dataTable)
        {
            string JSONString = string.Empty;
            JSONString = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);
            return JSONString;
        }

        public DataTable JsonToDataTable(string json)
        {
            DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
            return dt;
        }

        private List<String> GetParametersfromSP(string nameSP)
        {
            List<String> listParameters = new List<String>();
            listParameters = GetParametersfromSP(nameSP);
            return listParameters;
        }

        private void ImprimirLog(String error)
        {
            Console.WriteLine("Error al obtener información desde base de datos");
            Console.WriteLine(error);
        }

        /// <summary>
        /// Método dinámico que consulta en BD en base a parámetros tabla y cláusula condicional
        /// </summary>
        /// <param name="tab_name"> nombre de la tabla (DESPUES DEL FROM) o se puede complementar con JOINS completos</param>
        /// <param name="wclause"> sentencia condicional después de la palabra reservada WHERE</param>
        /// <returns>retorna JSON con resultados de la consulta</returns>
        public String getDataTablefromClause(string tab_name, string wclause, string wsent)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@tab_name", tab_name);
                _param[1] = new MySqlParameter("@wcol_name", wclause);
                _param[2] = new MySqlParameter("@w_sent", wsent);


                DataTable dtData = data.GetDataTableFromStoreProcedure("spskDy_tablefromclause", _param);
                if (dtData.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dtData);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }
        }
        public String getTemporalContractedServiceID()
        {
            try
            {

                DataTable dtData = data.GetDataTableFromStoreProcedure("tmp_contracted_services_id", null);
                if (dtData.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dtData);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }
        }
        #endregion

        #region métodos de data

        #region data
        /// <summary>
        /// Método que obtiene las categorias de ofertas disponibles
        /// </summary>
        /// <param name="idProductType">IdCategoria, puede ir nulo</param>
        /// <returns>retorna una cadena de texto formato json</returns>
        public DataTable? getProductType(Int32 iduser= 0, Int32 idorg=0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@iduser", iduser > 0 ? iduser : DBNull.Value);
                _param[1] = new MySqlParameter("@idorganization", idorg > 0 ? idorg : DBNull.Value);
               
                DataTable dtCategories = data.GetDataTableFromStoreProcedure("spslGetProductType_rolesversion", _param);
                if (dtCategories.Rows.Count > 0)
                    return dtCategories;
                else return null;
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        /// <summary>
        ///Método que obtiene las categorias de ofertas disponibles
        /// </summary>
        /// <param name="idProductType">IdCategoria, puede ir nulo</param>
        /// <returns>Retorna el resultado en tipo DataTable</returns>
        public DataTable getProductTypeAsDataTable(Int32 idProductType = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idProductType", idProductType>0 ? idProductType: DBNull.Value) };
                DataTable dtCategories = data.GetDataTableFromStoreProcedure("spslGetProductType", parameters);
                if (dtCategories.Rows.Count > 0)
                {
                    return dtCategories;
                }
                else { return null; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        /// <summary>
        /// Método que obtiene el listado de servicios según categoría seleccionada
        /// </summary>
        /// <param name="idProductType">IdCategoria</param>
        /// <returns>Retorna el listado de servicios en una cadena de texto formato JSON</returns>
        public String getServicesCatalog(Int32 idProductType = 0, Int32 idUser = 0, Int32 idOrg = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@product_type_id", idProductType > 0 ? idProductType : DBNull.Value);
                _param[1] = new MySqlParameter("@iduser", idUser > 0  ? idUser :  DBNull.Value);
                _param[2] = new MySqlParameter("@org_id", idOrg > 0 ? idOrg : DBNull.Value);                
                    
                DataTable dataServicesCatalog = data.GetDataTableFromStoreProcedure("spslGetServicesByTypeId_rolesversion", _param);
                if (dataServicesCatalog.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataServicesCatalog);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }
        }

        public DataTable? getPlansService(Int32 idService = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idService", idService>0 ? idService : null)
                };
                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("spslGetPlansServicesById", parameters);
                if (dataPlansService.Rows.Count > 0)
                    return dataPlansService;
                else { return null; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }

        }

        public DataTable? getServicesAllContracted(Int32 idUser = 0, Int32 idPlan = 0, Int32 contracted_services_id = 0, Int32 vdc_vcd_id = 0)
        {
            try
            {

                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@user_id", idUser > 0 ? idUser : DBNull.Value);
                _param[1] = new MySqlParameter("@plan_id", idPlan > 0 ? idPlan : DBNull.Value);
                _param[2] = new MySqlParameter("@contracted_services_id", contracted_services_id > 0 ? contracted_services_id : DBNull.Value);
                _param[3] = new MySqlParameter("@vdc_vcd_id", vdc_vcd_id > 0 ? vdc_vcd_id : DBNull.Value);
                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("spslGetAllServicesContracted", _param);
                if (dataPlansService.Rows.Count > 0)
                    return dataPlansService;
                else { return null; }

            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }

        }

        /*
         * REVISAR METODO PARA NO VINCULAR LIBRERIA MYSQLCLIENT.
         */

        public string getIpServiceById(Int32 idFirewall = 0)
        {
            try
            {
                /*MySql.Data.MySqlClient.MySqlParameter[] parameters = {
                    new MySql.Data.MySqlClient.MySqlParameter("@idFirewall", idFirewall>0 ? idFirewall: null)
                };

                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("spslGetIpServiceById", parameters);
                if (dataPlansService.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataPlansService);
                    return jsonString;
                }
                else { return ""; }*/
                return "";
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }

        }

        public string setContractedServices(Int32 user_id = 0,
            Int32 backup_scheduled_id = 0, Int32 plan_id = 0,
            Int32 firewall_id = 0, Int32 vdc_vcd_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[5];
                _param[0] = new MySqlParameter("@user_id", user_id);
                _param[1] = new MySqlParameter("@backup_scheduled_id", backup_scheduled_id > 0 ? backup_scheduled_id : DBNull.Value);
                _param[2] = new MySqlParameter("@plan_id", plan_id);
                _param[3] = new MySqlParameter("@firewall_id", firewall_id > 0 ? firewall_id : DBNull.Value);
                _param[4] = new MySqlParameter("@vdc_vcd_id", vdc_vcd_id > 0 ? vdc_vcd_id : DBNull.Value);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spinIntoContractedServices", _param);
                if (dataService.Rows.Count > 0)
                {
                    Int32 value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                    {
                        throw new Exception("Couldn't get service information ID");
                        error = "Could't get information from spinIntoContractedServices";
                    }

                }
                else { throw new Exception("Couldn't save service information"); }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }

        }
        public string setFirewall(string ips = "", bool is_segment = false, Int32 firewall_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@ips", ips);
                _param[1] = new MySqlParameter("@is_segment", is_segment);
                _param[2] = new MySqlParameter("@firewall_id", firewall_id > 0 ? firewall_id : DBNull.Value);


                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("spinInsertIntoFirewall", _param);
                if (dataPlansService.Rows.Count > 0)
                {
                    string jsonString = DataTableToJson(dataPlansService);
                    return jsonString;
                }
                else { return ""; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }
        }

        public string setRequestQueue(Int32 contracted_services_id, Int32 request_queue_id = 0,
           String resource_specs = "", String request_datetime = "",
           String supply_datetime = "",
           String down_datetime = "", Int32 supply_status_id = 0, Int32 service_status_id = 0,
           String error_log = "", String identificator = "", String name_app = "",
           String status = "", Int32 port = 0, Int32 terms_conditions_id = 0, String accept_datetime_terms = "")
        {

            try
            {
                MySqlParameter[] _param = new MySqlParameter[15];
                _param[0] = new MySqlParameter("@contracted_services_id", contracted_services_id > 0 ? contracted_services_id : DBNull.Value);
                _param[1] = new MySqlParameter("@request_queue_id", request_queue_id > 0 ? request_queue_id : DBNull.Value);
                _param[2] = new MySqlParameter("@resource_specs", !String.IsNullOrEmpty(resource_specs) ? resource_specs : DBNull.Value);
                _param[3] = new MySqlParameter("@request_datetime", !String.IsNullOrEmpty(request_datetime) ? request_datetime : DBNull.Value);
                _param[4] = new MySqlParameter("@supply_datetime", !String.IsNullOrEmpty(supply_datetime) ? supply_datetime : DBNull.Value);
                _param[5] = new MySqlParameter("@down_datetime", !String.IsNullOrEmpty(down_datetime) ? down_datetime : DBNull.Value);
                _param[6] = new MySqlParameter("@supply_status_id", supply_status_id > 0 ? supply_status_id : DBNull.Value);
                _param[7] = new MySqlParameter("@service_status_id", service_status_id > 0 ? service_status_id : null);
                _param[8] = new MySqlParameter("@error_log", !String.IsNullOrEmpty(error_log) ? error_log : DBNull.Value);
                _param[9] = new MySqlParameter("@identificator", !String.IsNullOrEmpty(identificator) ? identificator : null);
                _param[10] = new MySqlParameter("@name_app", !String.IsNullOrEmpty(name_app) ? name_app : DBNull.Value);
                _param[11] = new MySqlParameter("@status_text", !String.IsNullOrEmpty(status) ? status : DBNull.Value);
                _param[12] = new MySqlParameter("@port_int", port > 0 ? port : DBNull.Value);
                _param[13] = new MySqlParameter("@terms_conditions_id", terms_conditions_id > 0 ? terms_conditions_id : DBNull.Value);
                _param[14] = new MySqlParameter("accept_datetime_terms", !String.IsNullOrEmpty(accept_datetime_terms) ? accept_datetime_terms : DBNull.Value);



                DataSet dataService = data.GetDataSetFromStoreProcedure("spupSetRequestQueue", _param);
                if (dataService.Tables.Count > 0)
                {
                    string jsonString = (dataService.Tables.Count > 1) ? DataTableToJson(dataService.Tables[1]) :
                        DataTableToJson(dataService.Tables[0]);
                    return jsonString;
                }
                else { throw new Exception("Couldn't update request queue"); }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }

        }



        public DataTable? getUserfromUserName(string userName = null, Int32 public_id = 0, Int32 user_id = 0,
            Int32 org_id = 0)
        {
            try
            {

                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@user_name", String.IsNullOrEmpty(userName) ?  DBNull.Value : userName);
                _param[1] = new MySqlParameter("@public_id", public_id > 0 ? public_id : DBNull.Value);
                _param[2] = new MySqlParameter("@user_id", user_id > 0 ? user_id : DBNull.Value);
                _param[3] = new MySqlParameter("@org_id", org_id > 0 ? org_id : 1);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetUserVCD", _param);

                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                    throw new Exception("Couldn't get user information");

            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public string setUserVcd(string username, string email, Int32 orgid)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@username", username);
                _param[1] = new MySqlParameter("@email", email);
                _param[2] = new MySqlParameter("@org_id", orgid);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spinSetUserVCD", _param);
                if (dataService.Rows.Count > 0)
                {
                    int value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't find organization information");
                }
                else
                    throw new Exception("Couldn't get user information");

            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return e.Message;
            }
        }

        public DataTable? getOrgfromName(string name = null, Int32 OrgId = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@org_name", String.IsNullOrEmpty(name) == true ? DBNull.Value : name);
                _param[1] = new MySqlParameter("@org_id", OrgId > 0 ? OrgId : DBNull.Value);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetOrgVCD", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                    throw new Exception("Couldn't get organization information");
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public string setOrgVcd(string orgname, string host, string apiversion)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@org_name", orgname);
                _param[1] = new MySqlParameter("@vhost", host);
                _param[2] = new MySqlParameter("@api_version", apiversion);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spinSetOrgVCD", _param);
                if (dataService.Rows.Count > 0)
                {
                    int value = 0;
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't save organization information");
                }
                else
                    throw new Exception("Couldn't save organization information");

            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String updateLicenses(Int32 idlicenses, String name, String specs, Int32 contracted_services_id)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@idlicenses", idlicenses);
                _param[1] = new MySqlParameter("@name", name);
                _param[2] = new MySqlParameter("@specs", specs);
                _param[3] = new MySqlParameter("@contracted_services_id", contracted_services_id);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spupLicenses", _param);
                if (dataService.Rows.Count > 0)
                    return DataTableToJson(dataService);

                else
                    throw new Exception("Couldn't set licenses information");
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }
        /// <summary>
        /// Metodo para consultar las versiones de container image por ServiceId
        /// </summary>
        /// <param name="idService"></param>
        /// <returns></returns>
        public DataTable? getVersionImages(Int32 idService = 0)
        {
            try
            {
                MySqlParameter[] parameters = {
                    new MySqlParameter("@idService", idService>0 ? idService : DBNull.Value)
                };
                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("spskGetVersionImagesById", parameters);
                if (dataPlansService.Rows.Count > 0)
                    return dataPlansService;
                else { return null; }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }

        }
        #endregion

        #region ciberseguridad
        /// <summary>
        /// Método para almacenar en tabla Licenses
        /// </summary>
        /// <param name="specs">especificaciones en formato json tipo string</param>
        /// <param name="contracted_services_id">id del servicio contratado tipo entero</param>
        /// <param name="name">nombre de la organización tipo string</param>
        /// <returns>retorna idlicenses en formato json</returns>
        public string saveLicenses(string specs, Int32 contracted_services_id, string name)
        {
            try
            {

                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@nameValue", name);
                _param[1] = new MySqlParameter("@specs", specs);
                _param[2] = new MySqlParameter("@contracted_services_id", contracted_services_id);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spinNewLicense", _param);
                Int32 value = 0;
                if (dataService.Rows.Count > 0)
                {
                    if ((Int32.TryParse(dataService.Rows[0][0].ToString(), out value)) && value > 0)
                    {
                        string jsonString = DataTableToJson(dataService);
                        return jsonString;
                    }
                    else
                        throw new Exception("Couldn't get license information");
                }
                else
                    throw new Exception("Couldn't save license information");

            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        private string getParametersReport(string user_id)
        {
            String result = String.Empty;
            try
            {
                MySqlParameter[] _param = new MySqlParameter[1];
                _param[0] = new MySqlParameter("@userid", user_id);

                DataTable dataPlansService = data.GetDataTableFromStoreProcedure("getInformationReport", _param);
                if (dataPlansService.Rows.Count > 0)
                {
                    result = DataTableToJson(dataPlansService);

                }
                else
                    throw new Exception("Couldn't find data history information");

            }

            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }

            return result;
        }

        public DataTable? getRequestQueue(Int32 user_id, Int32 services_id, Int32 contracted_services_id
          , Int32 service_status_id, String supply_datetime_start, String supply_datetime_end
            , String request_datetime_start, String request_datetime_end, Int32 request_queue_id = 0)
        {
            try
            {
                DateTime supplydDT_start, supplydDT_end, requestDT_start, requestDT_end = new DateTime();
                DateTime.TryParse(supply_datetime_start, out supplydDT_start);
                DateTime.TryParse(supply_datetime_end, out supplydDT_end);
                DateTime.TryParse(request_datetime_start, out requestDT_start);
                DateTime.TryParse(request_datetime_end, out requestDT_end);
                DateTime? supplydate_start = supplydDT_start;
                DateTime? supplydate_end = supplydDT_end;
                DateTime? requestdate_start = requestDT_start;
                DateTime? requestdate_end = requestDT_end;
                MySqlParameter[] _param = new MySqlParameter[9];
                _param[0] = new MySqlParameter("@user_id", user_id > 0 ? user_id : null);
                _param[1] = new MySqlParameter("@services_id", services_id > 0 ? services_id : null);
                _param[2] = new MySqlParameter("@contracted_services_id", contracted_services_id > 0 ? contracted_services_id : null);
                _param[3] = new MySqlParameter("@supply_datetime_start", !String.IsNullOrEmpty(supply_datetime_start) ? supplydate_start : null);
                _param[4] = new MySqlParameter("@supply_datetime_end", !String.IsNullOrEmpty(supply_datetime_end) ? supplydate_end : null);
                _param[5] = new MySqlParameter("@request_datetime_start", !String.IsNullOrEmpty(request_datetime_start) ? requestdate_start : null);
                _param[6] = new MySqlParameter("@request_datetime_end", !String.IsNullOrEmpty(request_datetime_end) ? requestdate_end : null);
                _param[7] = new MySqlParameter("@service_status_id", service_status_id > 0 ? service_status_id : null);
                _param[8] = new MySqlParameter("@request_queue_id", request_queue_id > 0 ? request_queue_id : null);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetRequestQueue", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get request queue information from stored procedured spskGetRequestQueue");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public DataTable? getfieldsvdc(Int32 vdc_id, Int32 org_id, string urn_id)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@vdc_id", vdc_id > 0 ? vdc_id : null);
                _param[1] = new MySqlParameter("@org_id", org_id > 0 ? org_id : null);
                _param[2] = new MySqlParameter("@urn_id", String.IsNullOrEmpty(urn_id) ? null : urn_id);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetvdc_VCD", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get vdc information from stored procedured spskGetvdc_VCD ");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String insertfieldsvdc(string vdc_name, string urn_id, Int32 subscription_id, Int32 org_id)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@vdc_name", vdc_name);
                _param[1] = new MySqlParameter("@urn_id", urn_id);
                _param[2] = new MySqlParameter("@subscription_id", subscription_id > 0 ? subscription_id : null);
                _param[3] = new MySqlParameter("@org_id", org_id > 0 ? org_id : null);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spinInsertVdc_VCD", _param);
                if (dataService.Rows.Count > 0)
                    return DataTableToJson(dataService);
                else
                {
                    throw new Exception("Couldn't get vdc information from stored procedured spinInsertVdc_VCD");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setfieldsvdc(Int32 vdc_id, string vdc_name, string urn_id, Int32 subscription_id, Int32 org_id, bool bactive = true)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[6];
                _param[0] = new MySqlParameter("@vdc_id", vdc_id);
                _param[1] = new MySqlParameter("@vdc_name", vdc_name);
                _param[2] = new MySqlParameter("@urn_id", urn_id);
                _param[3] = new MySqlParameter("@subscription_id", subscription_id > 0 ? subscription_id : null);
                _param[4] = new MySqlParameter("@org_id", org_id > 0 ? org_id : null);
                _param[5] = new MySqlParameter("@bactive", bactive);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spupUpdatevdc_VCD", _param);
                if (dataService.Rows.Count > 0)
                    return DataTableToJson(dataService);
                else
                {
                    throw new Exception("Couldn't update vdc information from stored procedured spupUpdatevdc_VCD");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setInsertConsumptionHistoryIdentificator(string start_date = "", string end_date = "", string identificator = "",
            string pod_identificator = "")
        {
            try
            {
                DateTime date_start = new DateTime(), date_end = new DateTime();
                if (!String.IsNullOrEmpty(start_date))
                    DateTime.TryParse(start_date, out date_start);
                if (!String.IsNullOrEmpty(end_date))
                    DateTime.TryParse(end_date, out date_end);
                string adate_start = date_start.ToString("yyyy-MM-dd HH:mm:ss");
                string adate_end = date_end.ToString("yyyy-MM-dd HH:mm:ss");

                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@start_date", !String.IsNullOrEmpty(start_date) ? adate_start : null);
                _param[1] = new MySqlParameter("@end_date", !String.IsNullOrEmpty(end_date) ? adate_end : null);
                _param[2] = new MySqlParameter("@identificator", identificator);
                _param[3] = new MySqlParameter("@pod_identificator", pod_identificator);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spup_setConsumptionHistoryIdentificator", _param);
                if (dataService.Rows.Count > 0)
                    return DataTableToJson(dataService);
                else
                {
                    throw new Exception("Couldn't update information from stored procedured spup_setConsumptionHistoryIdentificator");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public DataTable? getConsumptionHistory(Int32 consumption_history_id = 0, Int32 request_services_id = 0, string start_date = "",
            string end_date = "")
        {
            try
            {
                DateTime filterStart, filterEnd = new DateTime();
                DateTime.TryParse(start_date, out filterStart);
                DateTime.TryParse(end_date, out filterEnd);

                DateTime? date_start = filterStart;
                DateTime? date_end = filterEnd;
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@consumption_history_id", consumption_history_id > 0 ? consumption_history_id : null);
                _param[1] = new MySqlParameter("@request_services_id", request_services_id > 0 ? request_services_id : null);
                _param[2] = new MySqlParameter("@start_date", !String.IsNullOrEmpty(start_date) ? date_start : null);
                _param[3] = new MySqlParameter("@end_date", !String.IsNullOrEmpty(end_date) ? date_end : null);



                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetConsumptionHistory", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get data from stored procedured");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public DataTable? getTermsConditions(Int32 idTerms_conditions = 0, Int32 idservice = 0)
        {
            try
            {


                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@idTermsConditions", idTerms_conditions > 0 ? idTerms_conditions : null);
                _param[1] = new MySqlParameter("@idservice", idservice > 0 ? idservice : null);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetTermsAndConditions", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get data from stored procedured spskGetTermsAndConditions");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setTermsConditions(Int32 terms_conditions_id = 0, String terms_conditions = "", Int32 idservice = 0, bool? bactive = null)
        {
            try
            {


                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@idTermsConditions", terms_conditions_id > 0 ? terms_conditions_id : null);
                _param[1] = new MySqlParameter("@idservice", idservice > 0 ? idservice : null);
                _param[2] = new MySqlParameter("@terms_conditions", String.IsNullOrEmpty(terms_conditions) ? null : terms_conditions);
                _param[3] = new MySqlParameter("@bactive", bactive);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spinupSetTermsAndConditions", _param);
                if (dataService.Rows.Count > 0)
                    return DataTableToJson(dataService);
                else
                {
                    throw new Exception("Couldn't update vdc information from stored procedured spinupSetTermsAndConditions");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }


        public DataTable? getLicenses(Int32 idlicenses = 0, Int32 public_id = 0, Int32 org_id = 0,
     Int32 idService = 0, Int32 contracted_services_id = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[5];
                _param[0] = new MySqlParameter("@idLicense", idlicenses > 0 ? idlicenses : null);
                _param[1] = new MySqlParameter("@public_id", public_id > 0 ? public_id : null);
                _param[2] = new MySqlParameter("@org_id", org_id > 0 ? org_id : null);
                _param[3] = new MySqlParameter("@idService", idService > 0 ? idService : null);
                _param[4] = new MySqlParameter("@contracted_services_id",
                    contracted_services_id > 0 ? contracted_services_id : null);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetLicenses", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                    throw new Exception("Couldn't get licenses information");
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }


        public DataTable? getOrgVDCByService(int serviceId = 0)
        {
            try
            {

                MySqlParameter[] _param = new MySqlParameter[1];
                _param[1] = new MySqlParameter("@idservice", serviceId > 0 ? serviceId : null);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetOrgVdcbyService", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get data from stored procedured");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }


        #endregion

        #region Administracion
        public DataTable? getprofiles(Int32 idrole = 0, Int32 id_org = 0)
        {
            try
            {


                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@p_idrole", idrole > 0 ? idrole : DBNull.Value);
                _param[1] = new MySqlParameter("@p_idorganization", id_org > 0 ? id_org : DBNull.Value);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spsk_Role_GetRoleInfo", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get data from stored procedured spsk_Role_GetRoleInfo");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public DataTable? getServicesToDeploy(String status = "")
        {
            try
            {


                MySqlParameter[] _param = new MySqlParameter[1];
                _param[0] = new MySqlParameter("@str_status", String.IsNullOrEmpty(status) ? status : DBNull.Value);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetServicesToDeploy", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get data from stored procedured spskGetServicesToDeploy");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }
        public DataTable? getStatusCatalog(Int32 status_catalog_id = 0, Int32 id_type = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@status_catalog_id", status_catalog_id > 0 ? status_catalog_id : null);
                _param[1] = new MySqlParameter("@id_type", id_type > 0 ? id_type : null);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spskGetStatusCatalog", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get status catalog data from stored procedured spskGetStatusCatalog");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setRoleAccess(Int32 idrole, Int32 idorg, string rolename, string description,
        Int32 iduser, bool canview, bool canedit,
        bool candelete, String jsonServices = "", bool bactive = true, Int32 idmenu_component = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[11];
                _param[0] = new MySqlParameter("@p_idrole", idrole > 0 ? idrole : DBNull.Value);
                _param[1] = new MySqlParameter("@p_idorganization", idorg > 0 ? idorg : DBNull.Value);
                _param[2] = new MySqlParameter("@p_role_name", !String.IsNullOrEmpty(rolename) ? rolename : DBNull.Value);
                _param[3] = new MySqlParameter("@p_description", !String.IsNullOrEmpty(description) ? description : DBNull.Value);
                _param[4] = new MySqlParameter("@p_created_by", iduser);
                _param[5] = new MySqlParameter("@p_idmenu_component", idmenu_component > 0 ? idmenu_component : DBNull.Value);
                _param[6] = new MySqlParameter("@p_can_view", canview);
                _param[7] = new MySqlParameter("@p_can_edit", canedit);
                _param[8] = new MySqlParameter("@p_can_delete", candelete);
                _param[9] = new MySqlParameter("@p_json_services", !String.IsNullOrEmpty(jsonServices) ? jsonServices : DBNull.Value);
                _param[10] = new MySqlParameter("@p_active", bactive);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spInUp_Role_RoleAndAccess", _param);
                if (dataService.Rows.Count > 0)
                    return dataService.Rows[0][0].ToString();
                else { throw new Exception("Couldn't update role"); }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setMenuComponent(Int32 idmenu_component = 0, String component_name = "", String component_path = "",
    String description = "")
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@pidmenu_component", idmenu_component > 0 ? idmenu_component : DBNull.Value);
                _param[1] = new MySqlParameter("@pcomponent_name", !String.IsNullOrEmpty(component_name) ? component_name : DBNull.Value);
                _param[2] = new MySqlParameter("@pcomponent_path", !String.IsNullOrEmpty(component_path) ? component_path : DBNull.Value);
                _param[3] = new MySqlParameter("@pdescription", !String.IsNullOrEmpty(description) ? description : DBNull.Value);
                DataTable dataService = data.GetDataTableFromStoreProcedure("spInUp_Role_MenuComponent", _param);
                if (dataService.Rows.Count > 0)
                    return dataService.Rows[0][0].ToString();

                else { throw new Exception("Couldn't update request queue"); }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);               
                return null;
            }
        }

        public DataTable? getMenuComponent(Int32 idmenu_component_filter = 0, String component_name_filter = "")
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[2];
                _param[0] = new MySqlParameter("@idmenu_component_filter", idmenu_component_filter > 0 ? idmenu_component_filter : DBNull.Value);
                _param[1] = new MySqlParameter("@component_name_filter", String.IsNullOrEmpty(component_name_filter) ? DBNull.Value : component_name_filter);


                DataTable dataService = data.GetDataTableFromStoreProcedure("spsk_Role_GetAllMenuComponents", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get status catalog data from stored procedured spsk_Role_GetAllMenuComponents");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public DataTable? getUserAccessInfo(Int32 user_id = 0, Int32 org_id = 0, Int32 idrole=0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[3];
                _param[0] = new MySqlParameter("@p_user_id", user_id > 0 ? user_id : DBNull.Value);
                _param[1] = new MySqlParameter("@p_id_organization", org_id > 0 ? org_id : DBNull.Value);
                _param[2] = new MySqlParameter("@p_id_role", idrole > 0 ? idrole : DBNull.Value);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spsk_Role_GetUserAccessInfo", _param);
                if (dataService.Rows.Count > 0)
                    return dataService;
                else
                {
                    throw new Exception("Couldn't get status catalog data from stored procedured spsk_Role_GetAllMenuComponents");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }

        public String setUserRole(Int32 user_id = 0, Int32 org_id = 0, Int32 idrole = 0, Int32 assigned_by = 0)
        {
            try
            {
                MySqlParameter[] _param = new MySqlParameter[4];
                _param[0] = new MySqlParameter("@p_user_id", user_id > 0 ? user_id : DBNull.Value);
                _param[1] = new MySqlParameter("@p_idrole", idrole > 0 ? idrole : DBNull.Value);
                _param[2] = new MySqlParameter("@p_idorganization", org_id > 0 ? org_id : DBNull.Value);
                _param[3] = new MySqlParameter("@p_assigned_by", assigned_by > 0 ? assigned_by : DBNull.Value);

                DataTable dataService = data.GetDataTableFromStoreProcedure("spinup_Role_setUserRol", _param);
                if (dataService.Rows.Count > 0)
                    return dataService.Rows[0][0].ToString();
                else
                {
                    throw new Exception("Couldn't get status catalog data from stored procedured spsk_Role_GetAllMenuComponents");
                }
            }
            catch (Exception e)
            {
                ImprimirLog(e.Message);
                return null;
            }
        }
        #endregion

        #endregion
    }

}