using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Data.Common;
using System.Data.SqlClient;
using digioz;
using System.Text;

namespace digioz.VideoModule
{
    public class VideoInfoManagement
    {
        private static PortalSettings coPortalSettings;

        public static int GetVideoIDFromName(string psVideoName)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            int liVideoID = 0;

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = String.Empty;

                lsSQL = "SELECT Video_id FROM tblVideos WHERE Video_serverFileName = '" + psVideoName + "'";

                loDB.openConnection();
                liVideoID = Convert.ToInt16(loDB.ExecDBScalar(lsSQL));
                loDB.closeConnection();
            }
            return liVideoID;
        }

        public static VideoInfoList GetVideoInfoTop6List()
        {
            VideoInfoList loVideoInfoList = new VideoInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                VideoInfo loVideoInfo;
                DataTable loVideoTable;

                string lsSQL = "SELECT TOP 6* FROM tblVideos ORDER BY video_id DESC;";

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loVideoTable.Rows)
                {
                    loVideoInfo = new VideoInfo();

                    loVideoInfo.ID = Convert.ToInt32(loDR["video_id"]);
                    loVideoInfo.FileName = (string)loDR["video_filename"];
                    loVideoInfo.Description = (string)loDR["video_description"];
                    loVideoInfo.Category = (string)loDR["category_name"];
                    loVideoInfo.Uploadtime = (DateTime)loDR["video_upload_time"];
                    loVideoInfo.Uploader = (string)loDR["video_uploader"];
                    loVideoInfo.Serverfilename = (string)loDR["video_serverfilename"];

                    loVideoInfoList.Add(loVideoInfo);
                }
            }
            return loVideoInfoList;
        }

        public static VideoInfo GetVideoInfo(int piVideoID)
        {
            VideoInfo loVideoInfo = new VideoInfo();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                DataTable loVideoTable;

                string lsSQL = "SELECT * FROM tblVideos WHERE video_id=" + piVideoID + ";";

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                DataRow loDR = loVideoTable.Rows[0];

                loVideoInfo = new VideoInfo();
                loVideoInfo.ID = Convert.ToInt32(loDR["video_id"]);
                loVideoInfo.FileName = (string)loDR["video_filename"];
                loVideoInfo.Description = (string)loDR["video_description"];
                loVideoInfo.Category = (string)loDR["category_name"];
                loVideoInfo.Uploadtime = (DateTime)loDR["video_upload_time"];
                loVideoInfo.Uploader = (string)loDR["video_uploader"];
                loVideoInfo.Serverfilename = (string)loDR["video_serverfilename"];
            }

            return loVideoInfo;
        }

        public static int FlipVideo(int piVideoID, string psDirection, string psCategory)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            int liNewVideoID = 0;

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                DataTable loVideoTable;

                if (psCategory == null || psCategory == String.Empty)
                {
                    psCategory = "ALL";
                }

                string lsSQL = "EXEC spFlipVideo " + piVideoID + ",'" + psDirection + "','" + psCategory + "';";

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                DataRow loDR = loVideoTable.Rows[0];
                liNewVideoID = Convert.ToInt32(loDR["ID"]);
                loDB.closeConnection();
            }

            return liNewVideoID;
        }

        /// <summary>
        /// Gets the video info list filtered by category.
        /// If empty string is passed returns the full video info list.
        /// </summary>
        /// <param name="categoryName">Name of the category.</param>
        /// <returns></returns>
        public static VideoInfoList GetVideoInfoList(string categoryName)
        {
            VideoInfoList loVideoInfoList = new VideoInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                VideoInfo loVideoInfo;
                DataTable loVideoTable;

                string lsSQL;

                if (String.IsNullOrEmpty(categoryName))
                {
                    lsSQL = "SELECT * FROM tblVideos ORDER BY Video_id DESC;";
                }
                else
                {
                    lsSQL = String.Format("SELECT * FROM tblVideos WHERE category_name='{0}' ORDER BY Video_id DESC;", categoryName);
                }

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loVideoTable.Rows)
                {
                    loVideoInfo = new VideoInfo();

                    loVideoInfo.ID = Convert.ToInt32(loDR["Video_id"]);
                    loVideoInfo.FileName = (string)loDR["Video_filename"];
                    loVideoInfo.Description = (string)loDR["Video_description"];
                    loVideoInfo.Category = (string)loDR["category_name"];
                    loVideoInfo.Uploadtime = (DateTime)loDR["Video_upload_time"];
                    loVideoInfo.Uploader = (string)loDR["Video_uploader"];
                    loVideoInfo.Serverfilename = (string)loDR["Video_serverfilename"];

                    loVideoInfoList.Add(loVideoInfo);
                }
            }
            return loVideoInfoList;
        }

        public static VideoInfoList GetVideoInfoUserList(string psUsername)
        {
            VideoInfoList loVideoInfoList = new VideoInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                VideoInfo loVideoInfo;
                DataTable loVideoTable;

                string lsSQL = "SELECT * FROM tblVideos WHERE video_uploader=" + psUsername + " ORDER BY Video_id DESC;";

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loVideoTable.Rows)
                {
                    loVideoInfo = new VideoInfo();

                    loVideoInfo.ID = Convert.ToInt32(loDR["Video_id"]);
                    loVideoInfo.FileName = (string)loDR["Video_filename"];
                    loVideoInfo.Description = (string)loDR["Video_description"];
                    loVideoInfo.Category = (string)loDR["category_name"];
                    loVideoInfo.Uploadtime = (DateTime)loDR["Video_upload_time"];
                    loVideoInfo.Uploader = (string)loDR["Video_uploader"];
                    loVideoInfo.Serverfilename = (string)loDR["Video_serverfilename"];

                    loVideoInfoList.Add(loVideoInfo);
                }
            }
            return loVideoInfoList;
        }

        public static bool SaveVideoInfo(VideoInfo poVideoInfo,
                                        string psMode)
        {
            bool lbRetValue = false;
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = String.Empty;
                int liCount = 0;
                DbParameter[] loParameters;

                if (psMode == "New")
                {
                    lsSQL = "spInsertVideos";
                    loParameters = new DbParameter[7];
                    loParameters[0] = new SqlParameter();
                    loParameters[0].ParameterName = "@vid_name";
                    loParameters[0].DbType = DbType.String;
                    loParameters[0].Value = poVideoInfo.FileName;

                    loParameters[1] = new SqlParameter();
                    loParameters[1].ParameterName = "@vid_descript";
                    loParameters[1].DbType = DbType.String;
                    loParameters[1].Value = poVideoInfo.Description;

                    loParameters[2] = new SqlParameter();
                    loParameters[2].ParameterName = "@vid_cate_name";
                    loParameters[2].DbType = DbType.String;
                    loParameters[2].Value = poVideoInfo.Category;

                    loParameters[3] = new SqlParameter();
                    loParameters[3].ParameterName = "@vid_uploader";
                    loParameters[3].DbType = DbType.String;
                    loParameters[3].Value = poVideoInfo.Uploader;

                    loParameters[4] = new SqlParameter();
                    loParameters[4].ParameterName = "@vid_serverfilename";
                    loParameters[4].DbType = DbType.String;
                    loParameters[4].Value = poVideoInfo.Serverfilename;

                    loParameters[5] = new SqlParameter();
                    loParameters[5].ParameterName = "@vid_mimetype";
                    loParameters[5].DbType = DbType.String;
                    loParameters[5].Value = poVideoInfo.Mimetype;

                    loParameters[6] = new SqlParameter();
                    loParameters[6].ParameterName = "@vid_id";
                    loParameters[6].DbType = DbType.Int32;
                    loParameters[6].Direction = ParameterDirection.Output;

                    loDB.openConnection();
                    loDB.ExecDBProc(lsSQL, out liCount, loParameters);
                    loDB.closeConnection();

                    poVideoInfo.ID = (int)loParameters[6].Value;
                }
                else if (psMode == "Update")
                {
                    lsSQL = "spUpdateVideo";

                    loParameters = new DbParameter[7];

                    loParameters[0] = new SqlParameter();
                    loParameters[0].ParameterName = "@id";
                    loParameters[0].DbType = DbType.Int32;
                    loParameters[0].Value = poVideoInfo.ID;

                    loParameters[1] = new SqlParameter();
                    loParameters[1].ParameterName = "@name";
                    loParameters[1].DbType = DbType.String;
                    loParameters[1].Value = poVideoInfo.FileName;

                    loParameters[2] = new SqlParameter();
                    loParameters[2].ParameterName = "@desc";
                    loParameters[2].DbType = DbType.String;
                    loParameters[2].Value = poVideoInfo.Description;

                    loParameters[3] = new SqlParameter();
                    loParameters[3].ParameterName = "@category";
                    loParameters[3].DbType = DbType.String;
                    loParameters[3].Value = poVideoInfo.Category;

                    loParameters[4] = new SqlParameter();
                    loParameters[4].ParameterName = "@dateTime";
                    loParameters[4].DbType = DbType.DateTime;
                    loParameters[4].Value = poVideoInfo.Uploadtime;

                    loParameters[5] = new SqlParameter();
                    loParameters[5].ParameterName = "@uploader";
                    loParameters[5].DbType = DbType.String;
                    loParameters[5].Value = poVideoInfo.Uploader;

                    loParameters[6] = new SqlParameter();
                    loParameters[6].ParameterName = "@serverFilename";
                    loParameters[6].DbType = DbType.String;
                    loParameters[6].Value = poVideoInfo.Serverfilename;

                    loDB.openConnection();
                    loDB.ExecDBProc(lsSQL, out liCount, loParameters);
                    loDB.closeConnection();
                }

                lbRetValue = string.IsNullOrEmpty(loDB.sErr);
            }
            return lbRetValue;
        }

        public static void DeleteVideoInfo(int piID)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = String.Empty;

                lsSQL = "EXEC spDeleteVideo '" + piID + "';";

                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
            }
        }

        /// <summary>
        /// SaveToFavorites
        /// </summary>
        /// <param name="lsType"></param>
        /// <param name="lsTableId"></param>
        /// <param name="lsName"></param>
        public void SaveToFavorites(string lsType, string lsTableId, string lsName)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = String.Empty;

                lsSQL = "EXEC spAddToFavorites '" + lsType + "','" + lsTableId + "','" + lsName + "';";

                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
            }
        }

        /// <summary>
        /// GetFavoritesInfoUserList
        /// </summary>
        /// <param name="psUsername"></param>
        public static VideoInfoList GetFavoriteVideoInfoUserList(string psUsername)
        {

            VideoInfoList loVideoInfoList = new VideoInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();


            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                VideoInfo loVideoInfo;
                DataTable loVideoTable;

                string lsSQL = "SELECT * FROM tblVideos a, tblFavorites b WHERE a.video_id = b.fav_table_id and b.fav_type='video' and a.video_uploader=" + psUsername + " ORDER BY Video_id DESC;";

                loDB.openConnection();
                loVideoTable = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loVideoTable.Rows)
                {
                    loVideoInfo = new VideoInfo();

                    loVideoInfo.ID = Convert.ToInt32(loDR["Video_id"]);
                    loVideoInfo.FileName = (string)loDR["Video_filename"];
                    loVideoInfo.Description = (string)loDR["Video_description"];
                    loVideoInfo.Category = (string)loDR["category_name"];
                    loVideoInfo.Uploadtime = (DateTime)loDR["Video_upload_time"];
                    loVideoInfo.Uploader = (string)loDR["Video_uploader"];
                    loVideoInfo.Serverfilename = (string)loDR["Video_serverfilename"];

                    loVideoInfoList.Add(loVideoInfo);
                }
            }
            return loVideoInfoList;
        }
    }
}



