using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace digioz
{
    /// <summary>
    /// Summary description for CategoryInfoManagement
    /// </summary>
    public class CategoryInfoManagement
    {
        private static PortalSettings coPortalSettings;

	    public CategoryInfoManagement()
	    {
	    }

        public static CategoryInfoList GetCategoriesList()
        {
            return GetCategoriesList(null);
        }

        /// <summary>
        /// Get list a list of video or picture categories 
        /// (depending on Entity parameter)
        /// </summary>
        /// <param name="psEntity"></param>
        /// <returns></returns>
        public static CategoryInfoList GetCategoriesList(string psEntity)
        {
            CategoryInfoList loList = new CategoryInfoList();
            CategoryInfo loCategory = new CategoryInfo();

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql;

                if (null == psEntity)
                {
                    lsSql = "Exec spGetCategoriesList";
                }
                else
                {
                    lsSql = string.Format("Exec spGetCategoriesList @category_entity = {0}",
                                               psEntity);
                }

                try
                {
                    loDB.openConnection();
                    DataTable ta = loDB.QueryDBDataset(lsSql);
                    foreach (DataRow dr in ta.Rows)
                    {
                        loCategory = new CategoryInfo();
                        loCategory.ID = (int)dr["category_id"];
                        loCategory.CategoryName = Convert.ToString(dr["category_name"]);
                        loCategory.CategoryEntity = Convert.ToString(dr["category_entity"]);
                        loList.Add(loCategory);
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return loList;
        }

        /// <summary>
        /// Get a category based on id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static CategoryInfo GetCategory(int piID)
        {
            CategoryInfo loCategory = null;

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql = string.Format("Exec spGetCategory @category_id = {0}",
                                           piID);
                try
                {
                    loDB.openConnection();
                    DataTable ta = loDB.QueryDBDataset(lsSql);
                    if (ta.Rows.Count > 0)
                    {
                        loCategory = new CategoryInfo();
                        DataRow dr = ta.Rows[0];
                        loCategory.ID = (int)dr["category_id"];
                        loCategory.CategoryName = Convert.ToString(dr["category_name"]);
                        loCategory.CategoryEntity = Convert.ToString(dr["category_entity"]);
                    }
                    
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return loCategory;
        }

        /// <summary>
        /// Get a category based on name
        /// </summary>
        /// <param name="psCategoryName"></param>
        /// <returns></returns>
        public static CategoryInfo GetCategoryBasedOnName(string psCategoryName)
        {
            CategoryInfo loCategory = new CategoryInfo();

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql = string.Format("Exec spGetCategoryBasedOnName @category_name = '{0}'", psCategoryName);
                try
                {
                    loDB.openConnection();
                    DataTable ta = loDB.QueryDBDataset(lsSql);
                    if (ta.Rows.Count > 0)
                    {
                        DataRow dr = ta.Rows[0];
                        loCategory.ID = (int)dr["category_id"];
                        loCategory.CategoryName = Convert.ToString(dr["category_name"]);
                        loCategory.CategoryEntity = Convert.ToString(dr["category_entity"]);
                    }
                    else
                    {
                        loCategory.ID = -1;
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return loCategory;
        }

        /// <summary>
        /// Add/Update category
        /// </summary>
        /// <param name="poCategory"></param>
        /// <param name="psError"></param>
        /// <returns></returns>
        public static bool Save(CategoryInfo poCategory, out string psError)
        {
            if (poCategory.ID == -1)
            {
                return Insert(poCategory, out psError);
            }
            else
            {
                return Update(poCategory, out psError);
            }
        }

        /// <summary>
        /// Insert a new record
        /// </summary>
        /// <param name="poCategory"></param>
        /// <param name="psError"></param>
        /// <returns></returns>
        public static bool Insert(CategoryInfo poCategory, out string psError)
        {
            bool lbRetValue = false;
            psError = string.Empty;

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql;

                lsSql = "spInsertCategory";
                SqlParameter[] loParameters = new SqlParameter[2];
                loParameters[0] = new SqlParameter("@category_name", 
                                                   poCategory.CategoryName);
                loParameters[1] = new SqlParameter("@category_entity", 
                                                   poCategory.CategoryEntity);

                try
                {
                    int liCount = 0;
                    loDB.openConnection();
                    loDB.ExecDBProc(lsSql, out liCount, loParameters);
                    lbRetValue = (String.IsNullOrEmpty(loDB.sErr));
                    psError = loDB.sErr;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return lbRetValue;
        }

        /// <summary>
        /// Update a record
        /// </summary>
        /// <param name="poCategory"></param>
        /// <param name="psError"></param>
        /// <returns></returns>
        public static bool Update(CategoryInfo poCategory, out string psError)
        {
            bool lbRetValue = false;
            psError = string.Empty;

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql;

                lsSql = "spUpdateCategory";
                SqlParameter[] loParameters = new SqlParameter[3];
                loParameters[0] = new SqlParameter("@category_id",
                                                   poCategory.ID);
                loParameters[1] = new SqlParameter("@category_name",
                                                   poCategory.CategoryName);
                loParameters[2] = new SqlParameter("@category_entity",
                                                   poCategory.CategoryEntity);

                try
                {
                    int liCount = 0;
                    loDB.openConnection();
                    loDB.ExecDBProc(lsSql, out liCount, loParameters);
                    lbRetValue = (String.IsNullOrEmpty(loDB.sErr));
                    psError = loDB.sErr;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return lbRetValue;
        }

        /// <summary>
        /// Delete a category based on id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool Delete(int piID, out string psError)
        {
            bool lbRetValue = false;
            psError = string.Empty;

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSql = string.Format("Exec spDeleteCategory @category_id = {0}",
                                           piID);
                try
                {
                    loDB.openConnection();
                    loDB.ExecDB(lsSql);
                    lbRetValue = (String.IsNullOrEmpty(loDB.sErr));
                    psError = loDB.sErr;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return lbRetValue;
        }
    }
}