using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ClosedXML.Excel;
using DataEntryDAL;

namespace DataEntryDAL.DAO
{
    public class ReportDAO
    {
        static SqlConnection myConn = null;

        public static SqlConnection MyConn { get => myConn; set => myConn = value; }

        public static void OpenConnection()
        {
            try
            {
                string connString = $"Data Source={Global.dataSource};Initial Catalog={Global.dbName};User id={Global.sqlID};Password={Global.sqlPassword}";
                MyConn = new SqlConnection(connString);
                MyConn.Open();
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal static DataTable RunStoredProcedure(string storedProcedureName, string tableName, Dictionary<string, string> spParams)
        {
            DataTable ret = new DataTable(tableName);
            SqlCommand sc;
            OpenConnection();

            sc = new SqlCommand(storedProcedureName, MyConn)
            {
                CommandTimeout = 180,
                CommandType = CommandType.StoredProcedure
            };

            foreach (var item in spParams)
            {
                sc.Parameters.AddWithValue(item.Key, item.Value);
            }

            var returnParameter = sc.Parameters.Add("@ReturnVal", SqlDbType.Int);
            returnParameter.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter da = new SqlDataAdapter(sc);
            da.Fill(ret);
            CloseConnection();

            return ret;
        }

        public static void CloseConnection()
        {
            try
            {
                MyConn.Close();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Gọi dậy 1 câu StoredProcedure có 1 tham số với paramName và paramValue, trả về 1 DataTable có TableName là tableName
        /// </summary>
        /// <param name="storedProcedureName">tên của Stored Procedure</param>
        /// <param name="tableName">tên DataTable lấy về</param>
        /// <param name="paramName">tên tham số của Stored Procedure</param>
        /// <param name="paramValue">giá trị truyền vào Stored Procedure</param>
        /// <returns></returns>
        public static DataTable RunSP(string storedProcedureName, string tableName, string paramName = "", string paramValue = "")
        {
            DataTable ret = new DataTable(tableName);

            SqlCommand sc;

            OpenConnection();

            sc = new SqlCommand(storedProcedureName, MyConn)
            {
                CommandTimeout = 180,
                CommandType = CommandType.StoredProcedure
            };

            if (!String.IsNullOrEmpty(paramName))
            {
                sc.Parameters.AddWithValue(paramName, paramValue);
            }
            var returnParameter = sc.Parameters.Add("@ReturnVal", SqlDbType.Int);
            returnParameter.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter da = new SqlDataAdapter(sc);
            da.Fill(ret);

            CloseConnection();

            return ret;
        }

        public static DataTable RunStoredProcedure(string storeProcedureName
            , Dictionary<string, string> parameters , string tableName)
        {
            DataTable ret = new DataTable(tableName);
            SqlCommand sc;
            OpenConnection();

            sc = new SqlCommand(storeProcedureName, MyConn)
            {
                CommandTimeout = 180,
                CommandType = CommandType.StoredProcedure
            };

            foreach (KeyValuePair<string, string> entry in parameters)
            {
                sc.Parameters.AddWithValue(entry.Key, entry.Value);
            }

            var returnParameter = sc.Parameters.Add("@ReturnVal", SqlDbType.Int);
            returnParameter.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter da = new SqlDataAdapter(sc);
            da.Fill(ret);
            CloseConnection();
            return ret;
        }

        public static DataTable RunSelect(string sql, string tableName)
        {
            DataTable ret = new DataTable(tableName);

            OpenConnection();
            SqlDataAdapter da = new SqlDataAdapter(sql, myConn);
            da.Fill(ret);
            return ret;
        }
    }
}