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

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
	public DataAccess()
	{
        conn.Open();
    }
    public void Closeconn()
    {
        conn.Close();
    }

            public DataTable CheckUser(string username, string password)
        {
            SqlDataAdapter sda = new SqlDataAdapter("CheckUser", conn);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.Parameters.AddWithValue("@username", username);
            sda.SelectCommand.Parameters.AddWithValue("@password", password);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

            public DataTable GetAllNews(string NewsID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllNews", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@NewsID", NewsID);

                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            public DataTable GetTotalLinks(string LinkID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetTotalLinks", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@LinkID", LinkID);

                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            public void useronlineforum(string page)
            {
                //SqlDataAdapter sda = new SqlDataAdapter("useronlineforum", conn);
                //sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                //sda.SelectCommand.Parameters.AddWithValue("@ip_address", Request.ServerVariables("remote_host"));
                //sda.SelectCommand.Parameters.AddWithValue("@page", Strings.Trim(page));
                //conn.Open();
                //sda.ExecuteNonQuery();
                //conn.Close();
            }


            public DataTable GetAllDialuges(string DialogueID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllDialuges", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@DialogueID", DialogueID);

                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            //public User CheckUser(string UserID)
            //{
            //    com = new SqlCommand(SP_CHECKUSER, con);
            //    com.CommandType = CommandType.StoredProcedure;
            //    com.Parameters.Add("@UserId", DbType.String).Value = UserID;
            //    dr = com.ExecuteReader();
            //    User _user = null;
            //    while (dr.Read())
            //    {
            //        _user = new User();
            //        _user.Password = dr["Password"].ToString();
            //        _user.Role = dr["Role"].ToString();
            //    }
            //    return _user;
            //}

            public DataTable GetAllMembers(string Membership_ID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllMembers", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@Membership_ID", Membership_ID);

                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }


            public int AddEditNews(string newsid,string newstitle, string newssum, string newsdetails, string photoname, string photopath)
            {
                SqlCommand cmd=new SqlCommand("AddEditNews",conn);
                cmd.CommandType=CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@NewsID",newsid);
                cmd.Parameters.AddWithValue("@NewsTitle",newstitle);
                cmd.Parameters.AddWithValue("@NewsSummary",newssum);
                cmd.Parameters.AddWithValue("@NewsDetails",newsdetails);
                cmd.Parameters.AddWithValue("@PhotoName",photoname);
                cmd.Parameters.AddWithValue("@PhotoPath",photopath);

                return cmd.ExecuteNonQuery();
            }

            public int DeleteNews(string newsid)
            {
                SqlCommand cmd = new SqlCommand("DeleteNews", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@NewsID", newsid);
                return cmd.ExecuteNonQuery();

            }

            public int AddBookCategory(string CategoryNameAR)
            {
                SqlCommand cmd = new SqlCommand("AddBookCategory", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CategoryNameAR", CategoryNameAR);
                return cmd.ExecuteNonQuery();
            }
            
            public int AddBook(string BookName,string BookSum,string BookPath,string CategoryID)
            {
                SqlCommand cmd = new SqlCommand("AddBook", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BookName",BookName);
                cmd.Parameters.AddWithValue("@BookSum",BookSum);
                cmd.Parameters.AddWithValue("@BookPath",BookPath);
                cmd.Parameters.AddWithValue("@CategoryID",CategoryID);
                return cmd.ExecuteNonQuery();
            }

            public DataTable GetAllBookCategory(string CategoryNameAR)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllBookCategory", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@CategoryNameAR", CategoryNameAR);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            public int AddEditDialouge(string DialogueID, string DialogueTitle, string DialogueSummary, string DialogueDetails, string photoname, string photopath)
            {
                SqlCommand cmd = new SqlCommand("AddEditDialouge", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@DialogueTitle", DialogueTitle);
                cmd.Parameters.AddWithValue("@DialogueSummary", DialogueSummary);
                cmd.Parameters.AddWithValue("@DialogueDetails", DialogueDetails);
                cmd.Parameters.AddWithValue("@PhotoName", photoname);
                cmd.Parameters.AddWithValue("@PhotoPath", photopath);
                cmd.Parameters.AddWithValue("@DialogueID", DialogueID);

                return cmd.ExecuteNonQuery();
            }

            public int AddEditVulenteers(string VulenteerID, string VulenteerName, string DateOfBirth, string Nationality, string IqamaNo, string Address, string Mobile, string Email, string Occupation, string EmergincyName, string RelationshipType, string HomePhone, string IPAddress)
            {
                SqlCommand cmd = new SqlCommand("AddEditVulenteers", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@VulenteerID",VulenteerID);
                cmd.Parameters.AddWithValue("@VulenteerName",VulenteerName);
                cmd.Parameters.AddWithValue("@DateOfBirth",DateOfBirth);
                cmd.Parameters.AddWithValue("@Nationality",Nationality);
                cmd.Parameters.AddWithValue("@IqamaNo",IqamaNo);
                cmd.Parameters.AddWithValue("@Address",Address);
                cmd.Parameters.AddWithValue("@Mobile",Mobile);
                cmd.Parameters.AddWithValue("@Email",Email);
                cmd.Parameters.AddWithValue("@Occupation",Occupation);
                cmd.Parameters.AddWithValue("@EmergincyName",EmergincyName);
                cmd.Parameters.AddWithValue("@RelationshipType",RelationshipType);
                cmd.Parameters.AddWithValue("@HomePhone",HomePhone);
                cmd.Parameters.AddWithValue("@IPAddress",IPAddress);

                return cmd.ExecuteNonQuery();
            }

            public DataTable GetAllVulenteers(string VulenteerID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllVulenteers", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@VulenteerID",VulenteerID );
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            public DataTable GetTop3News(string NewsID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetTop3News", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@NewsID", NewsID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

            public int AddEditUsers(string UserID,string UserName,string Password,string UserEmail,string FullName,string MobileNo)
            {
                SqlCommand cmd = new SqlCommand("AddEditUsers", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserID",UserID);
                cmd.Parameters.AddWithValue("@UserName",UserName);
                cmd.Parameters.AddWithValue("@Password",Password);
                cmd.Parameters.AddWithValue("@UserEmail",UserEmail);
                cmd.Parameters.AddWithValue("@FullName",FullName);
                cmd.Parameters.AddWithValue("@MobileNo",MobileNo);

                return cmd.ExecuteNonQuery();
            }

            public DataTable GetAllUsers(string UserID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllUsers", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@UserID", UserID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

    //--------------------------------------------------------------------------------//
            public int AddEditStaff(string StaffUserID,string RoleID,string Title,string StaffFullName,string Email,string Phone,string Extension,string Mobile)
            {
                SqlCommand cmd = new SqlCommand("AddEditStaff", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StaffUserID", StaffUserID);
                cmd.Parameters.AddWithValue("@RoleID",RoleID);
                cmd.Parameters.AddWithValue("@Title",Title);
                cmd.Parameters.AddWithValue("@StaffFullName",StaffFullName);
                cmd.Parameters.AddWithValue("@Email",Email);
                cmd.Parameters.AddWithValue("@Phone",Phone);
                cmd.Parameters.AddWithValue("@Extension",Extension);
                cmd.Parameters.AddWithValue("@Mobile",Mobile);
                return cmd.ExecuteNonQuery();
            }

            public DataTable GetAllStaff(string StaffUserID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllStaff", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@StaffUserID", StaffUserID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }


            public int AddEditClient(string ClientID, string OrganizationName, string ContactFullName, string Email, string CountryID, string CityID, string State_Province, string StreetAddress, string Zip_PostalCode, string ClientWebsite, string ClientDescription, string Extrafields, string Title ,string Phone, string Extension, string Mobile)
            {
                SqlCommand cmd = new SqlCommand("AddEditClient", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ClientID", ClientID);
                cmd.Parameters.AddWithValue("@OrganizationName",OrganizationName);
                cmd.Parameters.AddWithValue("@ContactFullName",ContactFullName);
                cmd.Parameters.AddWithValue("@Email",Email);
                cmd.Parameters.AddWithValue("@CountryID",CountryID);
                cmd.Parameters.AddWithValue("@CityID",CityID);
                cmd.Parameters.AddWithValue("@State_Province",State_Province);
                cmd.Parameters.AddWithValue("@StreetAddress",StreetAddress);
                cmd.Parameters.AddWithValue("@Zip_PostalCode",Zip_PostalCode);
                cmd.Parameters.AddWithValue("@ClientWebsite",ClientWebsite);
                cmd.Parameters.AddWithValue("@ClientDescription",ClientDescription);
                cmd.Parameters.AddWithValue("@Extrafields",Extrafields);
                cmd.Parameters.AddWithValue("@Title",Title);
                cmd.Parameters.AddWithValue("@Phone",Phone);
                cmd.Parameters.AddWithValue("@Extension",Extension);
                cmd.Parameters.AddWithValue("@Mobile",Mobile);

                return cmd.ExecuteNonQuery();
            }

            public DataTable GetAllClintes(string ClientID)
                {
                    SqlDataAdapter sda = new SqlDataAdapter("GetAllClintes", conn);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@ClientID", ClientID);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }

            public int AddEditProject(string ProjectID, string ClientID, string ProjectName, string ProjectNO, string Description, string InternalNote, string StartDate, string EndDate, string BillingMethod, string HourlyRate, string BudgetType, string BudgetTotal)
            {
                SqlCommand cmd = new SqlCommand("AddEditProject", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ProjectID",ProjectID);
                cmd.Parameters.AddWithValue("@ClientID",ClientID);
                cmd.Parameters.AddWithValue("@ProjectName",ProjectName);
                cmd.Parameters.AddWithValue("@ProjectNO",ProjectNO);
                cmd.Parameters.AddWithValue("@Description",Description);
                cmd.Parameters.AddWithValue("@InternalNote",InternalNote);
                cmd.Parameters.AddWithValue("@StartDate",StartDate);
                cmd.Parameters.AddWithValue("@EndDate",EndDate);
                cmd.Parameters.AddWithValue("@BillingMethod",BillingMethod);
                cmd.Parameters.AddWithValue("@HourlyRate",HourlyRate);
                cmd.Parameters.AddWithValue("@BudgetType",BudgetType);
                cmd.Parameters.AddWithValue("@BudgetTotal",BudgetTotal);
                return cmd.ExecuteNonQuery();
            }


            public DataTable GetAllProjects(string ProjectID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllProjects", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@ProjectID", ProjectID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }




            public int AddEditOrder(string OrderID, string ClientID, string OrderName, string OrderDetailes, string OrderDate, string StartWork, string EndWork, string TypeID, string StatesID, string ResponsibleID, string DesignerID, string PhotoPath, string EmailMassage, string Notes)
            {
                SqlCommand cmd = new SqlCommand("AddEditOrder", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@OrderID", OrderID);
                cmd.Parameters.AddWithValue("@ClientID", ClientID);
                cmd.Parameters.AddWithValue("@OrderName", OrderName);
                cmd.Parameters.AddWithValue("@OrderDetailes", OrderDetailes);
                cmd.Parameters.AddWithValue("@OrderDate", OrderDate);
                cmd.Parameters.AddWithValue("@StartWork", StartWork);
                cmd.Parameters.AddWithValue("@EndWork", EndWork);
                cmd.Parameters.AddWithValue("@TypeID", TypeID);
                cmd.Parameters.AddWithValue("@StatesID", StatesID);
                cmd.Parameters.AddWithValue("@ResponsibleID", ResponsibleID);
                cmd.Parameters.AddWithValue("@DesignerID", DesignerID);
                cmd.Parameters.AddWithValue("@PhotoPath", PhotoPath);
                cmd.Parameters.AddWithValue("@EmailMassage", EmailMassage);
                cmd.Parameters.AddWithValue("@Notes", Notes);

                return cmd.ExecuteNonQuery();
            }


            public DataTable GetAllOrders(string OrderID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllOrders", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@OrderID", OrderID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }


            public int AddEditComment(string CommentID, string Comment, string ProjectID)
            {
                SqlCommand cmd = new SqlCommand("AddEditComment", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CommentID",CommentID );
                //cmd.Parameters.AddWithValue("@UesrID",UesrID );
                //cmd.Parameters.AddWithValue("@UesrName",UesrName );
                //cmd.Parameters.AddWithValue("@Email", Email);
                cmd.Parameters.AddWithValue("@Comment",Comment );
                cmd.Parameters.AddWithValue("@ProjectID",ProjectID );
                //cmd.Parameters.AddWithValue("@CommentDate", CommentDate);
                //cmd.Parameters.AddWithValue("@UserPhotoPhathID",UserPhotoPhathID );
                return cmd.ExecuteNonQuery();
            }


            public DataTable GetAllComments(string CommentID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllComments", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@CommentID", CommentID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }


            public int AddEditHosting(string HostID, string ClientID, string DomainName, string Created, string Expires, string Updated, string NameServer, string DiskSpaceUsage, string FtpAccount, string UserName, string Password, string FullNameContact, string Phone, string Extention, string Mobile, string Email, string URL, string Notes)
            {
                SqlCommand cmd = new SqlCommand("AddEditHosting", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HostID",HostID);
                cmd.Parameters.AddWithValue("@ClientID",ClientID);
                cmd.Parameters.AddWithValue("@DomainName",DomainName);
                cmd.Parameters.AddWithValue("@Created",Created);
                cmd.Parameters.AddWithValue("@Expires",Expires);
                cmd.Parameters.AddWithValue("@Updated",Updated);
                cmd.Parameters.AddWithValue("@NameServer",NameServer);
                cmd.Parameters.AddWithValue("@DiskSpaceUsage",DiskSpaceUsage);
                cmd.Parameters.AddWithValue("@FtpAccount",FtpAccount);
                cmd.Parameters.AddWithValue("@UserName",UserName);
                cmd.Parameters.AddWithValue("@Password",Password);
                cmd.Parameters.AddWithValue("@FullNameContact",FullNameContact);
                cmd.Parameters.AddWithValue("@Phone",Phone);
                cmd.Parameters.AddWithValue("@Extention",Extention);
                cmd.Parameters.AddWithValue("@Mobile",Mobile);
                cmd.Parameters.AddWithValue("@Email",Email);
                cmd.Parameters.AddWithValue("@URL",URL);
                cmd.Parameters.AddWithValue("@Notes",Notes);

                return cmd.ExecuteNonQuery();
            }


            public DataTable GetAllHosting(string HostID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllHosting", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@HostID", HostID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }



            public int AddEditStaffMassege(string ID, string DevisionID, string UserName, string MassageTitle, string MassageDetaiels, string Email)
            {
                SqlCommand cmd = new SqlCommand("AddEditStaffMassege", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID",ID );
                cmd.Parameters.AddWithValue("@DevisionID", DevisionID);
                cmd.Parameters.AddWithValue("@UserName", UserName);
                cmd.Parameters.AddWithValue("@MassageTitle", MassageTitle);
                cmd.Parameters.AddWithValue("@MassageDetaiels", MassageDetaiels);
                cmd.Parameters.AddWithValue("@Email", Email);

                return cmd.ExecuteNonQuery();
            }


            public DataTable GetAllStaffMassege(string ID)
            {
                SqlDataAdapter sda = new SqlDataAdapter("GetAllStaffMassege", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddWithValue("@ID", ID);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
}