EchoTex_Payroll/HRM.DA/DA/Common/SystemConfigurationDA.cs

using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;
using System.Data.SqlClient;


namespace HRM.DA
{
    #region ConfigarationDA

    internal class SystemConfigarationDA
    {
        #region Constructor

        private SystemConfigarationDA()
        {
        }

        #endregion

        internal static void Insert(TransactionContext tc, SystemConfigaration item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO Configuration(ConfigurationID, ParentID, Node, Value, Type, CreatedBy, CreatedDate)" +
                " VALUES(%n, %n, %s, %s, %n, %n, %d)", item.ID, DataReader.GetNullValue(item.ParentID), item.Node,
                item.Value, item.Type, item.CreatedBy, item.CreatedDate);
        }

        internal static void Insert(TransactionContext tc, SystemConfigaration.ConfigurationAttribute item)
        {
            tc.ExecuteNonQuery("INSERT INTO ConfigurationAttribute(AttributeID, ParentID, AttributeName,Value)" +
                               " VALUES(%n, %n, %s, %s)", item.ID, item.ConfigurationID, item.AttributeName,
                item.Value);
        }

        internal static void Update(TransactionContext tc, SystemConfigaration item)
        {
            tc.ExecuteNonQuery(
                "UPDATE Configuration SET ParentID=%n, Node=%s, Value=%s, Type=%n, ModifiedBy=%n, ModifiedDate=%d" +
                " WHERE ConfigurationID=%n", DataReader.GetNullValue(item.ParentID), item.Node, item.Value, item.Type,
                item.ModifiedBy, item.ModifiedDate, item.ID);
        }

        internal static void UpdateRolePermission(TransactionContext tc)
        {
            tc.ExecuteNonQuery(
                @"select  distinct a.value PCode, b.value pname, c.value Link INTO #TEMPMENU   from
                (select * from CONFIGURATIONATTRIBUTE where attributeName = 'key' and value != '') a,
                (select * from CONFIGURATIONATTRIBUTE where attributeName = 'name' and value != '') b,
                (select * from CONFIGURATIONATTRIBUTE where attributeName = 'url' ) c
                 where a.parentid = b.parentid and a.parentid = c.parentid");

            tc.ExecuteNonQuery(
                @"UPDATE  RP 
                SET
                RP.name = t.pname,
                RP.link = t.link
                FROM
                RolePermission RP
                INNER JOIN #TEMPMENU t
                ON RP.PermissionCode = t.PCode;
                ");
            tc.ExecuteNonQuery(
                "DROP TABLE #TEMPMENU");
        }

        internal static void DeleteChildren(TransactionContext tc, int parentID)
        {
            tc.ExecuteNonQuery(@"DELETE FROM ConfigurationAttribute WHERE ParentID=%n", parentID);
        }

        internal static void Delete(TransactionContext tc, int parentID)
        {
            tc.ExecuteNonQuery(@"DELETE FROM ConfigurationAttribute WHERE ParentID=%n;
            DELETE FROM Configuration WHERE ParentID=%n", parentID, parentID);
        }

        internal static IDataReader GetAllParent(TransactionContext tc)
        {
            //return tc.ExecuteReader("SELECT * FROM Configuration Order by ConfigurationID");
            return tc.ExecuteReader("SELECT * FROM Configuration Order by ConfigurationID");
        }

        internal static void DeleteAll(TransactionContext tc, EnumConfigurationType type)
        {
            string SQLAttribute =
                string.Format(
                    "Delete from configurationAttribute where ParentID in (Select ConfigurationID from Configuration where type={0})",
                    Convert.ToInt32(type));
            tc.ExecuteNonQuery(SQLAttribute);
            string SQLConfig = string.Format("Delete from configuration Where type = {0}", Convert.ToInt32(type));
            tc.ExecuteNonQuery(SQLConfig);
        }

        internal static IDataReader GetAllChildrens(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM ConfigurationAttribute Order by AttributeID");
        }

        internal static IDataReader GetAllParent(TransactionContext tc, EnumConfigurationType type)
        {
            //return tc.ExecuteReader("SELECT * FROM Configuration Order by ConfigurationID");
            return tc.ExecuteReader("SELECT * FROM Configuration  where type=%n Order by ConfigurationID", type);
        }

        internal static IDataReader GetAttribute(TransactionContext tc, int attributeID)
        {
            return tc.ExecuteReader("SELECT * FROM ConfigurationAttribute WHERE AttributeID=%n", attributeID);
        }

        internal static IDataReader GetChildren(TransactionContext tc, int ConfigurationID)
        {
            return tc.ExecuteReader("SELECT * FROM ConfigurationAttribute WHERE ParentID=%n Order by AttributeID",
                ConfigurationID);
        }
        internal static IDataReader GetallChildren(TransactionContext tc,  string parentid)
        {
            return tc.ExecuteReader(@"SELECT * FROM ConfigurationAttribute WHERE ParentID IN (  
                %q) Order by AttributeID",
                parentid);
        }

        internal static IDataReader GetChildrenbyType(TransactionContext tc, EnumConfigurationType type)
        {
            return tc.ExecuteReader(@"SELECT a.* FROM ConfigurationAttribute a, Configuration c 
                WHERE a.ParentID=c.ConfigurationID and c.type=%n", type);
        }

        internal static IDataReader GetChildrenByNode(TransactionContext tc, int sNode)
        {
            return tc.ExecuteReader("select * from dbo.ConfigurationAttribute where parentid=%n Order by AttributeID",
                sNode);
        }

        internal static IDataReader GetChildrenByValue(TransactionContext tc, string sMenuKey)
        {
            return tc.ExecuteReader(
                "select * from dbo.ConfigurationAttribute where parentid in(select parentid from dbo.ConfigurationAttribute where value=%s and attributename='key')  Order by AttributeID",
                sMenuKey);
        }

        internal static IDataReader GetAllChildren(TransactionContext tc)
        {
            return tc.ExecuteReader(
                "select * from dbo.ConfigurationAttribute where parentid in(select parentid from dbo.ConfigurationAttribute where  attributename='key')  Order by AttributeID");
        }

        internal static IDataReader Get(TransactionContext tc, int ConfigurationID)
        {
            return tc.ExecuteReader("SELECT * FROM Configuration WHERE ConfigurationID=%n", ConfigurationID);
        }

        internal static IDataReader Get(TransactionContext tc, EnumConfigurationType configType)
        {
            return tc.ExecuteReader("SELECT * FROM Configuration Where Type=%n Order by ConfigurationID", configType);
        }

        internal static IDataReader GetChildren(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM ConfigurationAttribute Order by AttributeID");
        }

        internal static IDataReader GetAllChildrenByParentNode(TransactionContext tc, EnumConfigurationType type, string parentNode)
        {
            return tc.ExecuteReader(@";WITH cte AS 
                                    (
                                        SELECT a.*
                                        FROM CONFIGURATION a
                                        WHERE Node=%s and a.type=%n
                                        UNION ALL
                                        SELECT a.*
                                        FROM CONFIGURATION a JOIN cte c ON a.parentId = c.CONFIGURATIONID
                                    )
                                    SELECT *
                                    FROM cte", parentNode, type);
        }


        internal static object getconfigValue(TransactionContext tc, EnumConfigurationType type, string firstParent, string secondParent)
        {
            object returnValue = null;
            Object obj = tc.ExecuteScalar(@"select value from configuration where PARENTID in (select CONFIGURATIONID from 
                        configuration where type = %n and node = %s
                        ) and node = %s", type, firstParent.ToLower(), secondParent.ToLower());
            if (obj != DBNull.Value) returnValue = obj;
            return returnValue;
        }

    }

    #endregion
}