using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace FILib
{
    public class FISql
    {
        private string connStr = string.Empty;

        public FISql()
        {
            connStr = FIConfig.DBConnStr;
        }

        public FISql(string connStr)
        {
            this.connStr = connStr;
        }

        public DataTable ExecuteReader(string query)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                using (conn = new SqlConnection(this.connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        dt = new DataTable();
                        dt.Load(dr);
                        dr.Close();
                        cmd.Dispose();
                        return dt;
                    }
                }

            }
            catch (SqlException ex)
            {
                NLogger.LogError(typeof(FISql), "SqlException: {0}", ex.ToString());
            }
            catch (Exception ex)
            {
                NLogger.LogError(typeof(FISql), "Exception: {0}", ex.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return dt;
        }

        public int ExecuteNonQuery(string query)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(this.connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                NLogger.LogError(typeof(FISql), "SqlException: {0}", ex.ToString());
            }
            catch (Exception ex)
            {
                NLogger.LogError(typeof(FISql), "Exception: {0}", ex.ToString());
            }
            return 0;
        }

        public object ExecuteScalar(string query)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(this.connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        conn.Open();
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (SqlException ex)
            {
                NLogger.LogError(typeof(FISql), "SqlException: {0}", ex.ToString());
            }
            catch (Exception ex)
            {
                NLogger.LogError(typeof(FISql), "Exception: {0}", ex.ToString());
            }
            return null;
        }

        public DataTable Query_GTSP_Interface_SAP(DateTime businessdate)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                using (conn = new SqlConnection(this.connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(FIConfig.SP_Exec_SAP_Export, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = TimeSpan.FromHours(3).Seconds;

                        cmd.Parameters.Add("@GalaxyRule", SqlDbType.Int).Value = FIConfig.SP_Galaxy_Rule;
                        cmd.Parameters.Add("@BusinessDateTime", SqlDbType.DateTime).Value = businessdate.Date;
                        cmd.Parameters.Add("@OverrideExisting", SqlDbType.Int).Value = FIConfig.SP_Override;
                        cmd.Parameters.Add("@DebugFlag", SqlDbType.Int).Value = FIConfig.SP_Debug_Flag;

                        conn.Open();

                        SqlDataReader dr = cmd.ExecuteReader();
                        dt = new DataTable();
                        dt.Load(dr);
                        dr.Close();
                        cmd.Dispose();
                        return dt;
                    }
                }
            }
            catch (SqlException ex)
            {
                NLogger.LogError(typeof(FISql), "SqlException: {0}", ex.ToString());
            }
            catch (Exception ex)
            {
                NLogger.LogError(typeof(FISql), "Exception: {0}", ex.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return dt;
        }

        public async Task<DataTable> Query_GTSP_Interface_SAP_Async(DateTime businessdate)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            var asyncConnStr = new SqlConnectionStringBuilder(this.connStr) {
                AsynchronousProcessing = true
            }.ToString();

            try
            {
                using (conn = new SqlConnection(asyncConnStr))
                {
                    using (var cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = FIConfig.SP_Exec_SAP_Export;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 0;

                        cmd.Parameters.Add("@GalaxyRule", SqlDbType.Int).Value = FIConfig.SP_Galaxy_Rule;
                        cmd.Parameters.Add("@BusinessDateTime", SqlDbType.DateTime).Value = businessdate.Date;
                        cmd.Parameters.Add("@OverrideExisting", SqlDbType.Int).Value = FIConfig.SP_Override;
                        cmd.Parameters.Add("@DebugFlag", SqlDbType.Int).Value = FIConfig.SP_Debug_Flag;
                        //cmd.Parameters.Add("@Param2", SqlDbType.DateTime).Value = DateTime.Now;

                        conn.Open();

                        using (SqlDataReader dr = await cmd.ExecuteReaderAsync())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                            dr.Close();
                            cmd.Dispose();
                            return dt;
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                NLogger.LogError(typeof(FISql), "SqlException: {0}", ex.ToString());
            }
            catch (Exception ex)
            {
                NLogger.LogError(typeof(FISql), "Exception: {0}", ex.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return dt;
        }
    }
}