CEL_Payroll/Payroll.Service/Users/DA/RoleDA.cs

using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;
using System.Net;

namespace Payroll.Service
{
    #region  RoleDA

    internal class RoleDA
    {
        #region  Constructor

        private RoleDA() { }

        #endregion


        #region  Insert function

        internal static void Insert(TransactionContext tc, Role item)
        {
            //item.Status = EnumStatus.Inactive;

            item.CreatedDate = DateTime.Now;
            tc.ExecuteNonQuery("INSERT INTO Role(RoleID,Code, name, CreatedBy, CreatedDate, Sequence, Status,Roletype,isDefault,Description,AuthorizedBy,ComputerName,ApprovedComputerName)" +
            " VALUES(%n,%s, %s, %n, %D, %n, %n, %n,%n,%s,%n,%s,%s)", item.ID.Integer, item.Code, item.Name, item.CreatedBy.Integer, item.CreatedDate, item.Sequence, (int)item.Status, Convert.ToInt32(item.RoleType), Convert.ToInt32(item.IsDefault), item.Description, DataReader.GetNullValue(item.AuthorizedBy, IDType.Integer), item.ComputerName, item.ApprovedComputerName);
        }

        #endregion


        #region  Update function

        internal static void Update(TransactionContext tc, Role item)
        {
            item.CreatedDate = DateTime.Now;
            item.AuthorizedDate = DateTime.Now;
            string sSql = SQLParser.MakeSQL("UPDATE Role SET Code=%s, name = %s,Description=%s,Sequence = %n, Status = %n, isDefault = %n, RoleType = %n,CreatedBy=%n,CreatedDate=%D,AuthorizedBy=%n,AuthorizedDate=%D" +
            " WHERE RoleID = %n and RoleType = %n", item.Code, item.Name, item.Description, item.Sequence, (int)item.Status, Convert.ToInt32(item.IsDefault), Convert.ToInt32(item.RoleType), item.CreatedBy.Integer, item.CreatedDate, DataReader.GetNullValue(item.AuthorizedBy, IDType.Integer), item.AuthorizedDate, item.ID.Integer, Convert.ToInt32(item.RoleType));
            tc.ExecuteNonQuery("UPDATE Role SET Code=%s, name = %s,Description=%s,Sequence = %n, Status = %n, isDefault = %n, RoleType = %n,CreatedBy=%n,CreatedDate=%D,AuthorizedBy=%n,AuthorizedDate=%D" +
            " WHERE RoleID = %n and RoleType = %n", item.Code, item.Name, item.Description, item.Sequence, (int)item.Status, Convert.ToInt32(item.IsDefault), Convert.ToInt32(item.RoleType), item.CreatedBy.Integer, item.CreatedDate, DataReader.GetNullValue(item.AuthorizedBy, IDType.Integer), item.AuthorizedDate, item.ID.Integer, Convert.ToInt32(item.RoleType));
        }

        #endregion


        #region Get Function

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, EnumSystemType type)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Role where Status = %n  and RoleType = %n Order By Sequence", status, type);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Role Where RoleType = %n Order By Sequence", type);
            }
        }

        internal static IDataReader GetDesktopRole(TransactionContext tc, EnumStatus status)
        {

            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Role where Status = %n  and RoleType = %n Order By Sequence", status, EnumSystemType.Desktop);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Role Where RoleType = %n Order By Sequence", EnumSystemType.Desktop);
            }
        }

        internal static IDataReader GetWebRole(TransactionContext tc, EnumStatus status)
        {

            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Role where Status = %n  and RoleType = %n Order By Sequence", status, EnumSystemType.Web);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Role Where RoleType = %n Order By Sequence", EnumSystemType.Desktop);
            }
        }

        internal static IDataReader GetRole(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM Role WHERE RoleID  NOT IN(SELECT RoleID FROM RoleAccessType)");

        }

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM Role WHERE RoleID=%n", nID.Integer);
        }

        internal static DataSet GetRoleAudit(TransactionContext tc, int type, DateTime fromDate, DateTime ToDate)
        {
            DataSet oIDs = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT zr.*,e.[LoginID] ActionBy FROM z_Role zr,Users e 
                                         WHERE e.UserID=zr.CreatedBy AND Roletype=%n AND AuditDate>=%d 
                                         And AuditDate<=%d AND zr.AuditType IN('Inserted','Update (after)') 
                                        AND zr.[status]<>12
                                         ORDER BY zr.AuditDate DESC", type, fromDate, ToDate);
                oIDs = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oIDs;

        }
        internal static DataSet GetRoleRootPermissionName(TransactionContext tc, int nRoleID)
        {
            DataSet oIDs = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(@"SELECT [value] FROM ConfigurationAttribute WHERE ParentID IN(
                                                SELECT distinct ParentID FROM ConfigurationAttribute ca WHERE ca.[Value]='')
                                                AND AttributeName='name'
                                                AND parentid in(select parentid from ConfigurationAttribute where VALUE IN(
                                                SELECT DISTINCT ca.[Value] FROM ConfigurationAttribute ca,rolepermission rp
                                                WHERE [value]=rp.menuKey
                                                AND roleid=%n AND [Isapproved]<>12))", nRoleID);
                oIDs = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oIDs;

        }

        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("UPDATE [Role] SET CreatedBy=%n WHERE RoleID = %n", Payroll.BO.User.CurrentUser.ID.Integer, nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM Role WHERE RoleID = %n", nID.Integer);
        }

        #endregion

        internal static IDataReader Get(TransactionContext tc, EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Role where Status = %n   Order By Sequence", status);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Role  Order By Sequence");
            }
        }

        internal static Object Check(TransactionContext tc)
        {
            Object myObject = tc.ExecuteScalar("select * from role where isdefault = 1");
            return myObject;
        }

        internal static Object Check(TransactionContext tc, EnumSystemType type)
        {
            Object myObject;
            if (type == EnumSystemType.Web)
            {
                myObject = tc.ExecuteScalar("select * from role where isdefault = 1 and RoleType = 2");
            }
            else
            {
                myObject = tc.ExecuteScalar("select * from role where isdefault = 1 and RoleType = 1");
            }
            return myObject;
        }

        internal static IDataReader GetAllRole(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM Role");
        }
    }

    #endregion
}