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 digioz.PictureModule;

namespace digioz.PictureModule
{
    public class PictureInfoManagement
    {
        private static PortalSettings coPortalSettings;

        public static int GetPictureIDFromName(string psPictureName)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            int liPictureID = 0;

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

                lsSQL = "SELECT picture_id FROM tblPictures WHERE picture_serverFileName = '" + psPictureName + "'";

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

        public static PictureInfo GetPictureInfo(int piPictureID)
        {
            PictureInfo loPictureInfo = new PictureInfo();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

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

                string lsSQL = "SELECT * FROM tblPictures WHERE picture_id=" + piPictureID + ";";

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

                DataRow loDR = loPictureTable.Rows[0];

                loPictureInfo = new PictureInfo();
                loPictureInfo.ID = Convert.ToInt32(loDR["picture_id"]);
                loPictureInfo.FileName = (string)loDR["picture_filename"];
                loPictureInfo.Description = (string)loDR["picture_description"];
                loPictureInfo.Category = (string)loDR["category_name"];
                loPictureInfo.Uploadtime = (DateTime)loDR["picture_upload_time"];
                loPictureInfo.Uploader = (string)loDR["picture_uploader"];
                loPictureInfo.Serverfilename = (string)loDR["picture_serverfilename"];
            }

            return loPictureInfo;
        }

        public static int FlipPicture(int piPictureID, string psDirection, string psCategory)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            int liNewPictureID = 0;

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

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

                string lsSQL = "EXEC spFlipPicture " + piPictureID + ",'" + psDirection + "','" + psCategory + "';";

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

            return liNewPictureID;
        }

        public static PictureInfoList GetPictureInfoUserList(string psUsername)
        {
            PictureInfoList loPictureInfoList = new PictureInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                PictureInfo loPictureInfo;
                DataTable loPictureTable;

                string lsSQL = "SELECT * FROM tblPictures WHERE picture_uploader='" + psUsername + "' ORDER by picture_id DESC";

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

                foreach (DataRow loDR in loPictureTable.Rows)
                {
                    loPictureInfo = new PictureInfo();

                    loPictureInfo.ID = Convert.ToInt32(loDR["picture_id"]);
                    loPictureInfo.FileName = (string)loDR["picture_filename"];
                    loPictureInfo.Description = (string)loDR["picture_description"];
                    loPictureInfo.Category = (string)loDR["category_name"];
                    loPictureInfo.Uploadtime = (DateTime)loDR["picture_upload_time"];
                    loPictureInfo.Uploader = (string)loDR["picture_uploader"];
                    loPictureInfo.Serverfilename = (string)loDR["picture_serverfilename"];

                    loPictureInfoList.Add(loPictureInfo);
                }
            }
            return loPictureInfoList;
        }

        public static PictureInfoList GetPictureInfoTop6List()
        {
            PictureInfoList loPictureInfoList = new PictureInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                PictureInfo loPictureInfo;
                DataTable loPictureTable;

                string lsSQL = "SELECT TOP 6* FROM tblPictures ORDER BY picture_id DESC;";

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

                foreach (DataRow loDR in loPictureTable.Rows)
                {
                    loPictureInfo = new PictureInfo();

                    loPictureInfo.ID = Convert.ToInt32(loDR["picture_id"]);
                    loPictureInfo.FileName = (string)loDR["picture_filename"];
                    loPictureInfo.Description = (string)loDR["picture_description"];
                    loPictureInfo.Category = (string)loDR["category_name"];
                    loPictureInfo.Uploadtime = (DateTime)loDR["picture_upload_time"];
                    loPictureInfo.Uploader = (string)loDR["picture_uploader"];
                    loPictureInfo.Serverfilename = (string)loDR["picture_serverfilename"];

                    loPictureInfoList.Add(loPictureInfo);
                }
            }
            return loPictureInfoList;
        }

        /// <summary>
        /// Gets the picture info list.
        /// If empty string is passed returns the full picture info list.
        /// </summary>
        /// <param name="categoryName">Name of the category.</param>
        /// <returns></returns>
        public static PictureInfoList GetPictureInfoList(string categoryName)
        {
            PictureInfoList loPictureInfoList = new PictureInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                PictureInfo loPictureInfo;
                DataTable loPictureTable;

                string lsSQL;

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

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

                foreach (DataRow loDR in loPictureTable.Rows)
                {
                    loPictureInfo = new PictureInfo();

                    loPictureInfo.ID = Convert.ToInt32(loDR["picture_id"]);
                    loPictureInfo.FileName = (string)loDR["picture_filename"];
                    loPictureInfo.Description = (string)loDR["picture_description"];
                    loPictureInfo.Category = (string)loDR["category_name"];
                    loPictureInfo.Uploadtime = (DateTime)loDR["picture_upload_time"];
                    loPictureInfo.Uploader = (string)loDR["picture_uploader"];
                    loPictureInfo.Serverfilename = (string)loDR["picture_serverfilename"];

                    loPictureInfoList.Add(loPictureInfo);
                }
            }
            return loPictureInfoList;
        }

        public static bool SavePictureInfo(PictureInfo poPictureInfo, string psMode)
        {
            bool lbRetValue = false;
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

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

                if (psMode == "New")
                {
                    lsSQL = "spInsertPictures";
                    loParamaters = new DbParameter[8];

                    loParamaters[0] = new SqlParameter();
                    loParamaters[0].ParameterName = "@pic_name";
                    loParamaters[0].DbType = DbType.String;
                    loParamaters[0].Value = poPictureInfo.FileName;

                    loParamaters[1] = new SqlParameter();
                    loParamaters[1].ParameterName = "@pic_descript";
                    loParamaters[1].DbType = DbType.String;
                    loParamaters[1].Value = poPictureInfo.Description;

                    loParamaters[2] = new SqlParameter();
                    loParamaters[2].ParameterName = "@pic_cate_name";
                    loParamaters[2].DbType = DbType.String;
                    loParamaters[2].Value = poPictureInfo.Category;

                    loParamaters[3] = new SqlParameter();
                    loParamaters[3].ParameterName = "@pic_uploadtime";
                    loParamaters[3].DbType = DbType.DateTime;
                    loParamaters[3].Value = poPictureInfo.Uploadtime;

                    loParamaters[4] = new SqlParameter();
                    loParamaters[4].ParameterName = "@pic_uploader";
                    loParamaters[4].DbType = DbType.String;
                    loParamaters[4].Value = poPictureInfo.Uploader;

                    loParamaters[5] = new SqlParameter();
                    loParamaters[5].ParameterName = "@pic_serverfilename";
                    loParamaters[5].DbType = DbType.String;
                    loParamaters[5].Value = poPictureInfo.Serverfilename;

                    loParamaters[6] = new SqlParameter();
                    loParamaters[6].ParameterName = "@pic_mimeType";
                    loParamaters[6].DbType = DbType.String;
                    loParamaters[6].Value = poPictureInfo.Mimetype;

                    loParamaters[7] = new SqlParameter();
                    loParamaters[7].ParameterName = "@pic_id";
                    loParamaters[7].DbType = DbType.Int32;
                    loParamaters[7].Direction = ParameterDirection.Output;
            
                    loDB.openConnection();
                    loDB.ExecDBProc(lsSQL, out liCount, loParamaters);
                    loDB.closeConnection();

                    poPictureInfo.ID = (int)loParamaters[7].Value;
                }
                else if (psMode == "Update")
                {
                    lsSQL = "spUpdatePicture";
                    loParamaters = new DbParameter[7];
                    loParamaters[0] = new SqlParameter();
                    loParamaters[0].ParameterName = "@id";
                    loParamaters[0].DbType = DbType.Int32;
                    loParamaters[0].Value = poPictureInfo.ID;

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

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

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

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

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

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

                    loDB.openConnection();
                    loDB.ExecDBProc(lsSQL, out liCount, loParamaters);
                    loDB.closeConnection();
                }
                lbRetValue = string.IsNullOrEmpty(loDB.sErr);
            }
            return lbRetValue;
        }

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

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

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

                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
            }
        }
        /// <summary>
        /// SaveToFavorites
        /// </summary>
        /// <param name="referrer"></param>
        public void SaveToFavorites(string lsImageUrl)
        {
            //Code for storing url in favorites database table
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

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

                lsSQL = "EXEC spAddToFavorites '" + lsImageUrl + "';";

                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)
        {          
            
            //Code for storing url in favorites database table
            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>
        /// GetFavoritePictureInfoUserList
        /// </summary>
        /// <param name="psUsername"></param>           
        public static PictureInfoList GetFavoritePictureInfoUserList(string psUsername)
        {
            PictureInfoList loPictureInfoList = new PictureInfoList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                PictureInfo loPictureInfo;
                DataTable loPictureTable;

                string lsSQL = "SELECT * FROM tblPictures a,tblFavorites b  WHERE a.picture_id = b.fav_table_id and b.fav_type='picture' and a.picture_uploader='" + psUsername + "' ORDER by picture_id DESC";

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

                foreach (DataRow loDR in loPictureTable.Rows)
                {
                    loPictureInfo = new PictureInfo();

                    loPictureInfo.ID = Convert.ToInt32(loDR["picture_id"]);
                    loPictureInfo.FileName = (string)loDR["picture_filename"];
                    loPictureInfo.Description = (string)loDR["picture_description"];
                    loPictureInfo.Category = (string)loDR["category_name"];
                    loPictureInfo.Uploadtime = (DateTime)loDR["picture_upload_time"];
                    loPictureInfo.Uploader = (string)loDR["picture_uploader"];
                    loPictureInfo.Serverfilename = (string)loDR["picture_serverfilename"];

                    loPictureInfoList.Add(loPictureInfo);
                }
            }
            return loPictureInfoList;
        }
    }
}



