using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Aram_CRM_Project.BLL.Entity;

namespace Aram_CRM_Project.BLL
{
    class Public : Aram_CRM_Project.DAL.DA
    {

        public User IdentifyUser(string Username, string Password)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                User UserVar = new User();
                base.SQL = "SP_IdentifyUser";
                base.InitializeCommand();
                base.AddParameter("@Username", Username);
                base.AddParameter("@Password", Password);
                base.FillDataTable(dtTemp);
                if (dtTemp.Rows.Count > 0)
                {
                    UserVar.Id = dtTemp.Rows[0]["Id"].ToString();
                    UserVar.Name = dtTemp.Rows[0]["Name"].ToString();
                    UserVar.Username = dtTemp.Rows[0]["Username"].ToString();
                    UserVar.Password = dtTemp.Rows[0]["Password"].ToString();
                    UserVar.MenuItem = dtTemp.Rows[0]["MenuItem"].ToString();
                    UserVar.MenuItemCount = dtTemp.Rows[0]["MenuItemCount"].ToString();
                    UserVar.UserStatusId = dtTemp.Rows[0]["UserStatusId"].ToString();
                    return UserVar;
                }
                else
                {
                    return null;
                }
                
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                return null;
            }
        }

        public string ReminderCount(string UserId)
        {
            try
            {
                base.SQL = "SP_ReminderCount";
                base.InitializeCommand();
                base.AddParameter("@UserId", UserId);
                return base.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                return "0";
            }
        }

        public void BackupDatabase(string Path)
        {
            Aram_CRM_Project.UIL.frmMessage fMessage = new Aram_CRM_Project.UIL.frmMessage();
            try
            {
                base.SQL = "Backup Database DbCrm1 To Disk='" + Path + "'";
                base.InitializeCommand();
                base.ExecuteStoredProcedure();
                fMessage.BackupSuccess();
            }
            catch (Exception exp)
            {
                fMessage.BackupUnSuccess();
            }
        }

        public void RestoreDatabase(string Path)
        {
            Aram_CRM_Project.UIL.frmMessage fMessage = new Aram_CRM_Project.UIL.frmMessage();
            try
            {
                base.SQL = "ALTER DATABASE DbCrm1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE Use master;Restore Database DbCrm1 From Disk = '" + Path + "' WITH REPLACE; ALTER DATABASE DbCrm1 SET MULTI_USER";
                base.InitializeCommand();
                base.ExecuteStoredProcedure();
                System.Threading.Thread.Sleep(8000);
                fMessage.RestoreSuccess();
            }
            catch (Exception exp)
            {
                fMessage.RestoreUnSuccess();
            }
        }

        public int CheckConnectionToDataBase()
        {
            
            try
            {
                if (System.IO.File.Exists(Application.StartupPath + @"\NC.dat"))
                {
                    ReadFile();
                }
                else
                {
                    Aram_CRM_Project.UIL.frmConnection fConnection = new Aram_CRM_Project.UIL.frmConnection();
                    fConnection.ShowDialog();
                    ReadFile();
                }


                return 1;
            }
            catch(Exception exp)
            {
                MessageBox.Show(exp.Message);
                return 0;
            }
        }

        private void ReadFile()
        {
            string TextFile;
            string[] NetworkConnection;
            string ConnectionString = string.Empty; ;
            string ConnectionStringMaster = string.Empty;
            Utility utility = new Utility();

            System.IO.StreamReader Reader = new System.IO.StreamReader(Application.StartupPath + @"\NC.dat");
            TextFile = Reader.ReadToEnd();
            Reader.Close();
            NetworkConnection = TextFile.Split('&');
            //If Not Exist NC.dat,show frmConnection
            if (TextFile == "")
            {
                Aram_CRM_Project.UIL.frmConnection fConnection = new Aram_CRM_Project.UIL.frmConnection();
                fConnection.ShowDialog();
                System.IO.StreamReader Read = new System.IO.StreamReader(Application.StartupPath + @"\NC.dat");
                TextFile = Read.ReadToEnd();
                Read.Close();
                NetworkConnection = TextFile.Split('&');
            }

            if (NetworkConnection[0] == "Server")
            {
                Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource = "Data Source=.;Initial Catalog=DbCrm1;Integrated Security=true;";
                Aram_CRM_Project.BLL.Entity.StaticVariable.DataSourceMaster = "Data Source=.;Initial Catalog=master;Integrated Security=true;";
                CreateDataBase();
            }
            else if (NetworkConnection[0] == "Client")
            {
                Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource = "Data Source=" + NetworkConnection[1] + ";Initial Catalog=DbCrm1;User Id=sa;Password=" + utility.Decode(NetworkConnection[2]) + ";";
            }
        }

        public void CreateDataBase()
        {
            int Result;
            try
            {
                Result = base.CheckOpenConnection();
                if (Result == 0)
                {
                    //Create
                    SqlConnection cnn = new SqlConnection(Aram_CRM_Project.BLL.Entity.StaticVariable.DataSourceMaster);
                    String Query = "Use master;CREATE DATABASE DbCrm1";
                    SqlCommand cmd = new SqlCommand(Query, cnn);
                    if (cnn.State != ConnectionState.Open)
                        cnn.Open();
                    cmd.ExecuteNonQuery();
                    cnn.Close();

                    System.Threading.Thread.Sleep(6000);

                    //Restore
                    string Path = Application.StartupPath + @"\DbCrm1_Empty.bak";
                    SqlConnection CnnRestore = new SqlConnection(Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource);
                    string RestoreQuery = "Use DbCrm1;ALTER DATABASE DbCrm1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE Use master;Restore Database DbCrm1 From Disk = '" + Path + "' WITH REPLACE; ALTER DATABASE DbCrm1 SET MULTI_USER";
                    SqlCommand CmdRestore = new SqlCommand(RestoreQuery, CnnRestore);
                    if (CnnRestore.State != ConnectionState.Open)
                        CnnRestore.Open();
                    CmdRestore.ExecuteNonQuery();
                    CnnRestore.Close();
                }
            }
            catch(Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }
    }
}
