EchoTex_Payroll/HRM.DA/DA/Users/RolePermissionDA.cs

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

namespace HRM.DA
{
    internal class RolePermissionDA
    {
        #region Constructor

        private RolePermissionDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, Role.RolePermission item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO RolePermission(RolePermissionID, RoleID, PermissionCode, Name, Link, AuthStatus,ApproveBy, ApproveDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)" +
                " VALUES(%n, %s, %s, %s, %n,%n,%D,%n,%D,%n,%D)", item.ID, item.RoleID, item.PermissionCode, item.Name,
                item.Link, (int)item.Status, item.ApproveBy, item.ApproveDate, item.CreatedBy, item.CreatedDate,
                item.ModifiedBy, item.ModifiedDate);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, Role.RolePermission item)
        {
            //item.ModifiedBy = 1;
            //item.ModifiedDate = DateTime.Today;
            tc.ExecuteNonQuery(
                "UPDATE RolePermission SET RoleID=%d,PermissionCode=%s,Name=%s,Link=%s, AuthStatus=%n,ApproveBy=%n,ApproveDate=%D ,ModifiedBy=%n ,ModifiedDate=%D" +
                " WHERE RolePermissionID=%n", item.RoleID, item.PermissionCode, item.Name, item.Link, (int)item.Status,
                item.ApproveBy, item.ApproveDate, item.ModifiedBy, item.ModifiedDate, item.ID);
        }

        #endregion

        #region Get Function

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

        internal static IDataReader GetByRoleID(TransactionContext tc, int roleID)
        {
            return tc.ExecuteReader("SELECT * FROM RolePermission WHERE RoleID=%n AND AuthStatus=%n", roleID,
                EnumAuthStatus.Approved);
        }

        internal static IDataReader GetAll(TransactionContext tc, int roleID)
        {
            string Ssql = SQLParser.MakeSQL("SELECT * FROM RolePermission WHERE RoleID=%n", roleID);
            return tc.ExecuteReader(Ssql);
        }

        internal static IDataReader Get(TransactionContext tc, int roleID, EnumAuthStatus eStatus)
        {
            return tc.ExecuteReader("SELECT * FROM RolePermission WHERE RoleID=%n AND AuthStatus=%n", roleID,
                (int)eStatus);
        }

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

        //internal static IDataReader Get(TransactionContext tc, string sKey, int nRoleID)
        //{
        //    return tc.ExecuteReader("SELECT * FROM RolePermission WHERE menuKey=%s AND RoleID=%n and Isapproved=%n", sKey, nRoleID,(int)EnumStatus.Active);
        //}

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int id)
        {
//            tc.ExecuteNonQuery("Update RolePermission set CreatedBy=%n WHERE RolePermissionID=%n", User.CurrentUser.ID, id);
            tc.ExecuteNonQuery("DELETE FROM RolePermission WHERE RolePermissionID=%n", id);
        }

        internal static void DeleteByRoleID(TransactionContext tc, int id)
        {
//            tc.ExecuteNonQuery("Update RolePermission set CreatedBy=%n  WHERE RoleID=%n", User.CurrentUser.ID, id);
            tc.ExecuteNonQuery("DELETE FROM RolePermission WHERE RoleID=%n", id);
        }

        #endregion

        internal static IDataReader GetAllItem(TransactionContext tc, int roleID)
        {
            string sIsapproved = Convert.ToString((int)EnumAuthStatus.NewNotYetApprove + "," +
                                                  (int)EnumAuthStatus.EditedNotYetApprove);
            string Ssql = SQLParser.MakeSQL("SELECT * FROM RolePermission WHERE RoleID=%n and IsApproved in(%q)",
                roleID, sIsapproved);
            return tc.ExecuteReader(Ssql);
        }

        internal static DataSet GetRolePerAudit(TransactionContext tc, int type, DateTime fromDate, DateTime ToDate)
        {
            DataSet oIDs = new DataSet();
            try
            {
                string sSQL = SQLParser.MakeSQL(
                    @"SELECT (SELECT  zca.Value FROM ConfigurationAttribute zca WHERE ParentID in(
                SELECT  max(ParentID) FROM ConfigurationAttribute zca WHERE VALUE = zp.menuKey AND zca.AttributeName='key') 
                AND zca.AttributeName='name') MenuName,zp.AuditDate,zp.ComputerName,zp.IsApproved,e.LoginID ActionBy, r.name roleName
                FROM Z_RolePermission zp, Users e, role r  WHERE r.roleid=zp.roleid AND r.Roletype=%n  
                AND e.UserID=zp.CreatedBy AND zp.AuditDate>=%d And zp.AuditDate<=%d AND zp.AuditType IN('Inserted','Update (after)')  
                AND zp.IsApproved<>12 ORDER BY zp.AuditDate DESC", type, fromDate, ToDate);

                oIDs = tc.ExecuteDataSet(sSQL);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return oIDs;
        }
    }
}