using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Collections.Generic;
using System.Data;


namespace HRM.DA
{
    #region SetupDetailDA

    internal class SetupDetailDA
    {
        #region Constructor

        private SetupDetailDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, SetupDetail item, EnumParameterSetup setup)
        {
            tc.ExecuteNonQuery("INSERT INTO " + SetupDetailDA.getSetupTableName(setup) +
                               "(DetailID, SetupID, tranID, tranType)" +
                               " VALUES(%n, %n, %n, %n)", item.ID, item.SetupID, item.TranID,
                item.TranType);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, SetupDetail item, EnumParameterSetup setup)
        {
            tc.ExecuteNonQuery("UPDATE " + SetupDetailDA.getSetupTableName(setup) +
                               " SET SetupID=%n, tranID=%n, tranType=%n" +
                               " WHERE SetupID=%n", item.SetupID, item.TranID, item.TranType, item.ID);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, EnumParameterSetup setup)
        {
            return tc.ExecuteReader("SELECT * FROM %q", SetupDetailDA.getSetupTableName(setup));
        }

        internal static IDataReader Get(TransactionContext tc, int nID, EnumParameterSetup setup)
        {
            return tc.ExecuteReader("SELECT * FROM %q WHERE SetupID=%n", getSetupTableName(setup), nID);
        }

        public static IDataReader GetParameters(TransactionContext tc, int nSetupID, EnumParameterSetup setup)
        {
            return tc.ExecuteReader("SELECT * FROM %q Where SetupID = %n", getSetupTableName(setup), nSetupID);
        }

        public static DataSet GetParameters(TransactionContext tc, EnumParameterSetup setup, List<string> sqlrelations,
            string INEmpID)
        {
            string sql = "";
            string InSQL = "";
            if (INEmpID != "")
            {
                InSQL = SQLParser.MakeSQL(" AND E.EmployeeID IN (%q)", INEmpID);
            }

            foreach (string subsql in sqlrelations)
            {
                sql = sql + SQLParser.MakeSQL("select EmployeeID, SetupID from Employee E, %q SD	where "
                                              + " %q %q", getSetupTableName(setup), subsql, InSQL) + " UNION ";
            }

            sql = sql.Substring(0, sql.Length - 7);
            // sql = sql.Substring(0, sql.Length - 6);
            sql = " select DISTINCT EmployeeID, SetupID from ( " + sql + " ) A Order by EmployeeId, SetupID";
            return tc.ExecuteDataSet(sql);
        }

        public static IDataReader GetUsedParameters(TransactionContext tc, EnumParameterSetup setup, int nSetupID,
            int objectID)
        {
            return tc.ExecuteReader("Select * from %q Where SetupID IN " +
                                    "(SELECT %q FROM %q WHERE %q =%n AND %q = %n)",
                getSetupTableName(setup), getParameterPrimaryKey(setup),
                getParameterTableName(setup), getParameterPrimaryKey(setup),
                nSetupID, getParameterObjectKey(setup), objectID);
        }

        public static IDataReader GetUsedParameters(TransactionContext tc, EnumParameterSetup setup, string nSetupIDs,
            int objectID)
        {
            return tc.ExecuteReader("Select * from %q Where SetupID IN " +
                                    "(SELECT %q FROM %q WHERE %q in(%q) AND %q = %n)",
                getSetupTableName(setup), getParameterPrimaryKey(setup),
                getParameterTableName(setup), getParameterPrimaryKey(setup),
                nSetupIDs, getParameterObjectKey(setup), objectID);
        }

        public static IDataReader GetUsedParameters(TransactionContext tc, EnumParameterSetup setup, string objectIDs)
        {
            string sql = SQLParser.MakeSQL("Select * from %q Where SetupID IN " +
                                           "(SELECT %q FROM %q WHERE %q in(%s))",
                getSetupTableName(setup), getParameterPrimaryKey(setup),
                getParameterTableName(setup),
                getParameterObjectKey(setup), objectIDs);

            return tc.ExecuteReader(sql);
        }

        public static IDataReader GetUsedParameters(TransactionContext tc, EnumParameterSetup setup, int objectID)
        {
            string sql = SQLParser.MakeSQL("Select * from %q Where SetupID IN " +
                                           "(SELECT %q FROM %q WHERE %q = %n)",
                getSetupTableName(setup), getParameterPrimaryKey(setup),
                getParameterTableName(setup),
                getParameterObjectKey(setup), objectID);

            return tc.ExecuteReader(sql);
        }

        public static IDataReader GetParameters(TransactionContext tc, EnumParameterSetup setup)
        {
            return tc.ExecuteReader("SELECT * FROM %q", getSetupTableName(setup));
        }

        public static IDataReader GetParametersById(TransactionContext tc, EnumParameterSetup setup, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM %q Where SetupID = %n", getSetupTableName(setup), ID);
        }
        public static DataSet GetTypes(TransactionContext tc, EnumParameterSetup setup)
        {
            return tc.ExecuteDataSet("SELECT DISTINCT TranType FROM %q ", getSetupTableName(setup));
        }

        public static DataSet GetParameters(TransactionContext tc,
            EnumParameterSetup setup,
            EnmSetupManagerTranType type, int TranID)
        {
            return tc.ExecuteDataSet("SELECT DISTINCT TranType FROM %q WHERE TranType=%n AND TranID=%n ",
                getSetupTableName(setup), (int)type, TranID);
        }

        #endregion

        public static int GenerateID(TransactionContext tc, EnumParameterSetup setup)
        {
            int ID = tc.GenerateID(getSetupTableName(setup), "DetailID");
            return ID;
        }

        private static string getSetupTableName(EnumParameterSetup setup)
        {
            string tablename = "";
            switch (setup)
            {
                case EnumParameterSetup.OT:
                    tablename = "OTSetupDetail";
                    break;
                case EnumParameterSetup.Bonus:
                    tablename = "BonusSetupDetail";
                    break;
                case EnumParameterSetup.SalaryDeduct:
                    tablename = "UaLeaveSetupDetail";
                    break;
                case EnumParameterSetup.Gratuity:
                    break;
                case EnumParameterSetup.Leave:
                    tablename = "LeaveSetupDetail";
                    break;
                default:
                    break;
            }

            return tablename;
        }

        private static string getParameterTableName(EnumParameterSetup setup)
        {
            string tablename = "";
            switch (setup)
            {
                case EnumParameterSetup.OT:
                    tablename = "TERMPARAMETER";
                    break;
                case EnumParameterSetup.Bonus:
                    tablename = "BONUSPARAMETER";
                    break;
                case EnumParameterSetup.SalaryDeduct:
                    tablename = "UALeaveParam";
                    break;
                case EnumParameterSetup.Gratuity:
                    break;
                case EnumParameterSetup.Leave:
                    tablename = "LEAVEPARAMETER";
                    break;
                default:
                    break;
            }

            return tablename;
        }

        private static string getParameterPrimaryKey(EnumParameterSetup setup)
        {
            string tablename = "";
            switch (setup)
            {
                case EnumParameterSetup.OT:
                    tablename = "TermParameterID";
                    break;
                case EnumParameterSetup.Bonus:
                    tablename = "BonusParameterID";
                    break;
                case EnumParameterSetup.SalaryDeduct:
                    tablename = "UaLeaveParamID";
                    break;
                case EnumParameterSetup.Gratuity:
                    break;
                case EnumParameterSetup.Leave:
                    tablename = "LEAVEPARAMID";
                    break;
                default:
                    break;
            }

            return tablename;
        }

        private static string getParameterObjectKey(EnumParameterSetup setup)
        {
            string tablename = "";
            switch (setup)
            {
                case EnumParameterSetup.OT:
                    tablename = "TERMID";
                    break;
                case EnumParameterSetup.Bonus:
                    tablename = "BonusID";
                    break;
                case EnumParameterSetup.SalaryDeduct:
                    tablename = "UaLeaveID";
                    break;
                case EnumParameterSetup.Gratuity:
                    break;
                case EnumParameterSetup.Leave:
                    tablename = "LEAVEID";
                    break;
                default:
                    break;
            }

            return tablename;
        }

        #region Delete function

        internal static void Delete(TransactionContext tc, EnumParameterSetup setup, int SetupID)
        {
            tc.ExecuteNonQuery("DELETE FROM %q WHERE SetupID=%n", getSetupTableName(setup), SetupID);
        }

        #endregion


        internal static IDataReader GetByID(EnumParameterSetup oSetup, int parmID, TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"SELECT * From  %q Where SetupID = %n", getSetupTableName(oSetup), parmID);
            return tc.ExecuteReader(sql);
        }
    }

    #endregion
}