using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BLL;
using System.Data;
using DAL;
using System.Data.SqlClient;



    public class ThenThisaiDBAccess
    {
        public DataTable RetrunData(string query)
        {
            DataTable dt;
            dt = SqlDBHelper.ExecuteSelectCommand(query, CommandType.Text);
            return dt;
        }

        //Registered User
        public Guid AddNewRegisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@Password",thenthisai.Password), 
                new SqlParameter("@ProfilePhoto",thenthisai.ProfilePhoto.Length == 0 ? DBNull.Value : (object)thenthisai.ProfilePhoto)

             };
            return SqlDBHelper.ExecuteNonQuerywithID("AddNewRegisteredUser", CommandType.StoredProcedure, parameters);

        }
        public bool DeleteRegisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@RegisteredUserID",thenthisai.RegisteredUserID),
                  new SqlParameter("@ModifiedOn",thenthisai.ModifiedOn.ToShortDateString())
           
              };
            return SqlDBHelper.ExecuteNonQuery("DeleteRegisteredUser", CommandType.StoredProcedure, parameters);
        }
        public bool UpdateRegisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@RegisteredUserID",thenthisai.RegisteredUserID),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@Password",thenthisai.Password), 
                new SqlParameter("@ProfilePhoto",thenthisai.ProfilePhoto)
               
             };

            return SqlDBHelper.ExecuteNonQuery("UpdateRegisteredUser", CommandType.StoredProcedure, parameters);
        }
        public ThenThisaiClass GetRegisteredUserDetails(Guid RegisteredUserID)
        {
            ThenThisaiClass thenthisai = null;
            SqlParameter[] parameters = new SqlParameter[]
             {

                 new SqlParameter("@RegisteredUserID",RegisteredUserID)
             };

            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetRegisteredUserDetails", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    thenthisai = new ThenThisaiClass();
                    thenthisai.RegisteredUserID = (Guid)row["RegisteredUserID"];
                    thenthisai.Name = row["Name"].ToString();
                    thenthisai.Password = row["Password"].ToString();
                    thenthisai.MailID = row["UserID"].ToString();
                    if (row["ProfilePhoto"].ToString() != "" && row["ProfilePhoto"].ToString() != null)
                    {
                        thenthisai.ProfilePhoto = (byte[])(row["ProfilePhoto"]);
                    }
                    else
                    {
                        thenthisai.ProfilePhoto = null;
                    }
                    if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                    {
                        thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                    }
                    thenthisai.CreatedOn = Convert.ToDateTime(row["CreatedOn"]);

                }
            }
            return thenthisai;

        }
        public List<ThenThisaiClass> GetRegisteredUserList()
        {
            List<ThenThisaiClass> listuser = null;
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetRegisteredUserList", CommandType.StoredProcedure))
            {
                if (table.Rows.Count > 0)
                {
                    listuser = new List<ThenThisaiClass>();
                    foreach (DataRow row in table.Rows)
                    {
                        ThenThisaiClass thenthisai = new ThenThisaiClass();

                        thenthisai.RegisteredUserID = (Guid)row["RegisteredUserID"];
                        thenthisai.Name = row["Name"].ToString();
                        thenthisai.MailID = row["UserID"].ToString();
                        if (row["ProfilePhoto"].ToString() != "" && row["ProfilePhoto"].ToString() != null)
                        {
                            thenthisai.ProfilePhoto = (byte[])(row["ProfilePhoto"]);
                        }
                        else
                        {
                            thenthisai.ProfilePhoto = null;
                        }
                        if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                        {
                            thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                        }
                        thenthisai.CreatedOn = Convert.ToDateTime(row["CreatedOn"]);
                        listuser.Add(thenthisai);
                    }
                }
            }
            return listuser;
        }

        //Unregistered User
        public Guid AddnewUnregisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
            };
            return SqlDBHelper.ExecuteNonQuerywithID("AddnewUnregisteredUser", CommandType.StoredProcedure, parameters);
         }
        public bool DeleteUnregisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@UnregisteredUserID",thenthisai.UnregisteredUserID),
             };
            return SqlDBHelper.ExecuteNonQuery("DeleteUnregisteredUser", CommandType.StoredProcedure, parameters);
        }
        public bool UpdateUnregisteredUser(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@UnregisteredUserID",thenthisai.UnregisteredUserID),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID)              
             };

            return SqlDBHelper.ExecuteNonQuery("UpdateUnregisteredUser", CommandType.StoredProcedure, parameters);
        }
        public ThenThisaiClass GetUnregisteredUserDetails(Guid UnregisteredUserID)
        {
            ThenThisaiClass thenthisai = null;
            SqlParameter[] parameters = new SqlParameter[]
             {

                 new SqlParameter("@UnregisteredUserID",thenthisai.UnregisteredUserID)
             };

            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetUnregisteredUserDetails", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    thenthisai = new ThenThisaiClass();
                    thenthisai.UnregisteredUserID = (Guid)row["UnregisteredUserID"];
                    thenthisai.Name = row["Name"].ToString();
                    thenthisai.MailID = row["UserID"].ToString();
                    if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                    {
                        thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                    }
                    thenthisai.CreatedOn = Convert.ToDateTime(row["CreatedOn"]);

                }
            }
            return thenthisai;

        }
        public List<ThenThisaiClass> GetUnregisteredUserList()
        {
            List<ThenThisaiClass> listuser = null;
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetUnregisteredUserList", CommandType.StoredProcedure))
            {
                if (table.Rows.Count > 0)
                {
                    listuser = new List<ThenThisaiClass>();
                    foreach (DataRow row in table.Rows)
                    {
                        ThenThisaiClass thenthisai = new ThenThisaiClass();

                        thenthisai.UnregisteredUserID = (Guid)row["UnregisteredUserID"];
                        thenthisai.Name = row["Name"].ToString();
                        thenthisai.MailID = row["UserID"].ToString();
                        if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                        {
                            thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                        }
                        thenthisai.CreatedOn = Convert.ToDateTime(row["CreatedOn"]);
                        listuser.Add(thenthisai);
                    }
                }
            }
            return listuser;
        }

        //Comment
        public bool AddNewComment(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Comment",thenthisai.Comment),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@PageURL",thenthisai.PageURL), 
                new SqlParameter("@PostedIP",thenthisai.PostedIP), 
                new SqlParameter("@Flag",thenthisai.Flag)
               
                 
         };
            return SqlDBHelper.ExecuteNonQuery("AddNewComment", CommandType.StoredProcedure, parameters);
        }
        public bool DeleteComment(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@CommentID",thenthisai.CommentID),
             };
            return SqlDBHelper.ExecuteNonQuery("DeleteComment", CommandType.StoredProcedure, parameters);
        }
        public bool UpdateComment(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@CommentID",thenthisai.CommentID),
                new SqlParameter("@Comment",thenthisai.Comment),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@PageURL",thenthisai.PageURL), 
                new SqlParameter("@PostedIP",thenthisai.PostedIP), 
                new SqlParameter("@Flag",thenthisai.Flag)
                
             };

            return SqlDBHelper.ExecuteNonQuery("UpdateComment", CommandType.StoredProcedure, parameters);
        }
        public ThenThisaiClass GetCommentDetails(Guid CommentID)
        {
            ThenThisaiClass thenthisai = null;
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@CommentID",CommentID)
             };

            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCommentDetails", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    thenthisai = new ThenThisaiClass();
                    thenthisai.CommentID = (Guid)row["CommentID"];
                    thenthisai.Comment = row["Comment"].ToString();
                    thenthisai.Name = row["Name"].ToString();
                    thenthisai.MailID = row["UserID"].ToString();
                    thenthisai.PageURL = row["PageURL"].ToString();
                    thenthisai.PostedOn = Convert.ToDateTime(row["PostedOn"]);
                    thenthisai.PostedIP = row["PostedIP"].ToString();
                    thenthisai.Flag = Convert.ToInt32(row["Flag"]);
                    if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                    {
                        thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                    }
                }
            }
            return thenthisai;

        }
        public List<ThenThisaiClass> GetCommentList(string URL)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@URL",URL)
             };
            List<ThenThisaiClass> listuser = null;
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCommentList", CommandType.StoredProcedure,parameters))
            {
                if (table.Rows.Count > 0)
                {
                    listuser = new List<ThenThisaiClass>();
                    foreach (DataRow row in table.Rows)
                    {
                        ThenThisaiClass thenthisai = new ThenThisaiClass();

                        thenthisai.CommentID = (Guid)row["CommentID"];
                        thenthisai.Comment = row["Comment"].ToString();
                        thenthisai.Name = row["Name"].ToString();
                        thenthisai.MailID = row["UserID"].ToString();
                        thenthisai.PageURL = row["PageURL"].ToString();
                        thenthisai.PostedTime = (row["PostTime"]).ToString();
                        thenthisai.PostedIP = row["PostedIP"].ToString();
                        thenthisai.Flag = Convert.ToInt32(row["Flag"]);
                        if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                        {
                            thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                        }
                        
                        listuser.Add(thenthisai);
                    }
                }
            }
            return listuser;
        }
        public DataTable getCommentData(string URL)
        {
            string query = @"SELECT * FROM ThenThisaiComment where PageURL='" + URL+"' and StateCode=0 order by PostedOn desc";
            return RetrunData(query);
        }

        //Reply
        public bool AddNewReply(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@CommentID",thenthisai.CommentID),
                new SqlParameter("@Reply",thenthisai.Reply),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@PageURL",thenthisai.PageURL), 
                new SqlParameter("@PostedIP",thenthisai.PostedIP),
                new SqlParameter("@Flag",thenthisai.Flag)
            };
            return SqlDBHelper.ExecuteNonQuery("AddNewReply", CommandType.StoredProcedure, parameters);
        }
        public bool DeleteReply(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@ReplyID",thenthisai.ReplyID),
             };
            return SqlDBHelper.ExecuteNonQuery(" DeleteReply", CommandType.StoredProcedure, parameters);
        }
        public bool UpdateReply(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@ReplyID",thenthisai.ReplyID),
                new SqlParameter("@CommentID",thenthisai.CommentID),
                new SqlParameter("@Reply",thenthisai.Reply),
                new SqlParameter("@Name",thenthisai.Name),
                new SqlParameter("@UserID",thenthisai.MailID),
                new SqlParameter("@PageURL",thenthisai.PageURL), 
                new SqlParameter("@PostedIP",thenthisai.PostedIP)
                
               
               
             };

            return SqlDBHelper.ExecuteNonQuery("UpdateReply", CommandType.StoredProcedure, parameters);
        }
        public ThenThisaiClass GetReplyDetails(Guid ReplyID)
        {
            ThenThisaiClass thenthisai = null;
            SqlParameter[] parameters = new SqlParameter[]
             {

                 new SqlParameter("@ReplyID",ReplyID)
             };

            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetReplyDetails", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    thenthisai = new ThenThisaiClass();
                    thenthisai.ReplyID = (Guid)row["ReplyID"];
                    thenthisai.CommentID = (Guid)row["CommentID"];
                    thenthisai.Reply = row["Reply"].ToString();
                    thenthisai.Name = row["Name"].ToString();
                    thenthisai.MailID = row["UserID"].ToString();
                    thenthisai.PageURL = row["PageURL"].ToString();
                    thenthisai.PostedOn = Convert.ToDateTime(row["PostedOn"]);
                    thenthisai.PostedIP = row["PostedIP"].ToString();
                    if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                    {
                        thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                    }
                    
                }
            }
            return thenthisai;

        }
        public List<ThenThisaiClass> GetReplyList(Guid CommentID )
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@CommentID",CommentID)
             };
            List<ThenThisaiClass> listuser = null;
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetReplyList", CommandType.StoredProcedure,parameters))
            {
                if (table.Rows.Count > 0)
                {
                    listuser = new List<ThenThisaiClass>();
                    foreach (DataRow row in table.Rows)
                    {
                        ThenThisaiClass thenthisai = new ThenThisaiClass();

                        thenthisai.ReplyID = (Guid)row["ReplyID"];
                        thenthisai.CommentID = (Guid)row["CommentID"];
                        thenthisai.Reply = row["Reply"].ToString();
                        thenthisai.Name = row["Name"].ToString();
                        thenthisai.MailID = row["UserID"].ToString();
                        thenthisai.PageURL = row["PageURL"].ToString();
                        thenthisai.PostedTime = (row["PostTime"]).ToString();
                        thenthisai.PostedIP = row["PostedIP"].ToString();
                        if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                        {
                            thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                        }
                        
                        listuser.Add(thenthisai);
                    }
                }
            }
            return listuser;
        }

        //Thought Of the Day


        //Votting

        public bool AddNewVotting(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Questions",thenthisai.Questions),
                new SqlParameter("@Yes",thenthisai.yes),
                new SqlParameter("@No",thenthisai.No),
                new SqlParameter("@Consider",thenthisai.consider),
                new SqlParameter("@Category",thenthisai.Category) 
                
            };
            return SqlDBHelper.ExecuteNonQuery("votingInsert", CommandType.StoredProcedure, parameters);
        }
        public bool DeleteVotting(Guid VotingID)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                 new SqlParameter("@VotingID",VotingID),
             };
            return SqlDBHelper.ExecuteNonQuery(" VotingDelete", CommandType.StoredProcedure, parameters);
        }
        public bool UpdateVotting(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@VotingID",thenthisai.VotingID),
                new SqlParameter("@Questions",thenthisai.Questions),
                new SqlParameter("@Yes",thenthisai.yes),
                new SqlParameter("@No",thenthisai.No),
                new SqlParameter("@Consider",thenthisai.consider)        
               
             };

            return SqlDBHelper.ExecuteNonQuery("votingUpdate", CommandType.StoredProcedure, parameters);
        }
        public ThenThisaiClass GetVottingDetails(Guid VotingID)
        {
            ThenThisaiClass thenthisai = null;
            SqlParameter[] parameters = new SqlParameter[]
             {

                 new SqlParameter("@ReplyID",VotingID)
             };

            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("VotingGetdetails", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];
                    thenthisai = new ThenThisaiClass();
                    thenthisai.VotingID = (Guid)row["VotingID"];
                    thenthisai.Questions = row["Questions"].ToString();
                    thenthisai.Category = row["Category"].ToString();
                    thenthisai.yes = Convert.ToInt32(row["yes"].ToString());
                    thenthisai.No = Convert.ToInt32(row["No"].ToString());
                    thenthisai.consider = Convert.ToInt32(row["consider"].ToString());
                    thenthisai.CreatedOn = Convert.ToDateTime(row["PostedOn"]);
                    if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                    {
                        thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                    }

                }
            }
            return thenthisai;

        }
        public List<ThenThisaiClass> GetVottingList()
        {

            List<ThenThisaiClass> listuser = null;
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetReplyList", CommandType.StoredProcedure))
            {
                if (table.Rows.Count > 0)
                {
                    listuser = new List<ThenThisaiClass>();
                    foreach (DataRow row in table.Rows)
                    {
                        ThenThisaiClass thenthisai = new ThenThisaiClass();

                        thenthisai.VotingID = (Guid)row["VotingID"];
                        thenthisai.Questions = row["Questions"].ToString();
                        thenthisai.Category = row["Category"].ToString();
                        thenthisai.yes = Convert.ToInt32(row["yes"].ToString());
                        thenthisai.No = Convert.ToInt32(row["No"].ToString());
                        thenthisai.consider = Convert.ToInt32(row["consider"].ToString());
                        thenthisai.CreatedOn = Convert.ToDateTime(row["PostedOn"]);
                        if (row["ModifiedOn"].ToString() != null && row["ModifiedOn"].ToString() != "")
                        {
                            thenthisai.ModifiedOn = Convert.ToDateTime(row["ModifiedOn"]);
                        }

                        listuser.Add(thenthisai);
                    }
                }
            }
            return listuser;
        }

        //Votting Sheet
        public bool InsertNewVoteSheet(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID",thenthisai.UserID),
                new SqlParameter("@IPAddress",thenthisai.PostedIP),
                new SqlParameter("@Flag",thenthisai.Flag)                              
            };
            return SqlDBHelper.ExecuteNonQuery("VoteSheetInsert", CommandType.StoredProcedure, parameters);
        }
        public DataTable getVotter(Guid UserID)
        {
            string query = @"select * from VoteSheet where UserID='" + UserID + "' and cast(votedOn as date)=cast(getdate() as date)";
            return RetrunData(query);
        }
        //Time Sheet
        public bool AddTimeSheet(ThenThisaiClass thenthisai)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                
                
                new SqlParameter("@UserID",thenthisai.UserID),
                new SqlParameter("@IPAddress",thenthisai.PostedIP),
                new SqlParameter("@Flag",thenthisai.Flag)
                
            };
            return SqlDBHelper.ExecuteNonQuery("TimesheetInsert", CommandType.StoredProcedure, parameters);
        }

        //Login
        public DataTable getLogin(string MailID, string Password)
        {
            string query = @"select * from RegisteredUser where UserID='" + MailID + "' and Password='"+Password+"' and StateCode=0";
            return RetrunData(query);
        }
        public DataTable getGuestLogin(string MailID)
        {
            string query = @"select * from UnregisteredUser where UserID='" + MailID + "' and StateCode=0";
            return RetrunData(query);
        }

        //Check Registered MailId

        public DataTable getMailID(string MailID)
        {
            string query = @"select * from RegisteredUser where UserID='" + MailID + "' and StateCode=0";
            return RetrunData(query);
        }

        //Dynamic Update
        public DataTable getThought()
        {
            string query = @"select top 1 * from ThoughtNews where stateCode=0 and Type=0 order by CreatedOn desc";
            return RetrunData(query);
        }
        public DataTable getNews()
        {
            string query = @"select top 5 * from ThoughtNews where stateCode=0 and Type=1 order by CreatedOn desc";
            return RetrunData(query);
        }
        public DataTable getQuestions()
        {
            string query = @"select top 5 * from Voting where stateCode=0 order by CreatedOn desc";
            return RetrunData(query);
        }
        public DataTable getQuestionOpt(Guid VotingID)
        {
            string query = @"select * from Voting where VotingID='"+VotingID+"' and stateCode=0";
            return RetrunData(query);
        }

    }