CEL_Payroll/Payroll.Service/Users/DA/RolePermissionDA.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;

namespace Payroll.Service
{
    #region  RolePermissionDA

    internal class RolePermissionDA
    {
        #region  Constructor

        private RolePermissionDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, RolePermission item)
        {

            tc.ExecuteNonQuery("INSERT INTO RolePermission(RolePermissionID, MenuKey, RoleID, CreatedBy, CreatedDate,AddButton, EditButton, DeleteButton, AuthorizeButton, LockButton,ProcessButton, UndoButton, ApproveButton,ActiveButton,UpButton,DownButton,SaveButton,IsApproved,ComputerName,ApprovedComputerName,MenuPermissionStatus)" +
            " VALUES(%n, %s, %n, %n, %d,%b,%b,%b,%b,%b,%b,%b,%b,%b,%b,%b,%b,%n,%s,%s,%n)", item.ID.Integer, item.MenuKey, item.RoleID.Integer, item.CreatedBy.Integer, item.CreatedDate, item.Add, item.Edit, item.DeleteButton, item.Authorize, item.Lock, item.Process, item.Undo, item.Approve, item.Active, item.Up, item.Down, item.SaveButton, (int)item.IsApproved, item.ComputerName, item.ApprovedComputerName, (int)item.MenuPermissionStatus);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, RolePermission item)
        {
            item.ModifiedBy = ID.FromInteger(1);
            item.ModifiedDate = DateTime.Today;
            tc.ExecuteNonQuery("UPDATE RolePermission SET MenuKey=%s,IsApproved=%n,ApprovedComputerName=%s,CreatedBy=%n, CreatedDate=%d,MenuPermissionStatus=%n" +
            " WHERE RolePermissionID=%n", item.MenuKey, (int)item.IsApproved, item.ApprovedComputerName, DataReader.GetNullValue(item.CreatedBy, IDType.Integer), DataReader.GetNullValue(item.CreatedDate), (int)item.MenuPermissionStatus, item.ID.Integer);
        }

        #endregion

        #region Get Function

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

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

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

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

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

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

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("Update [RolePermission] set CreatedBy=%n WHERE RolePermissionID=%n", Payroll.BO.User.CurrentUser.ID.Integer, nID.Integer);
            tc.ExecuteNonQuery("DELETE FROM [RolePermission] WHERE RolePermissionID=%n", nID.Integer);
        }

        internal static void DeleteByRoleID(TransactionContext tc, ID nRoleID)
        {
            tc.ExecuteNonQuery("Update [RolePermission] set CreatedBy=%n  WHERE RoleID=%n", Payroll.BO.User.CurrentUser.ID.Integer, nRoleID.Integer);
            tc.ExecuteNonQuery("DELETE FROM [RolePermission] WHERE RoleID=%n", nRoleID.Integer);
        }

        #endregion

        internal static IDataReader GetAllItem(TransactionContext tc, int roleID)
        {
            string sIsapproved = Convert.ToString((int)EnumStatus.Role_Menu_Assigned_And_Waiting_For_Approve + "," + (int)EnumStatus.Role_Menu_Modified_And_Waiting_For_Approve);
            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  top 1 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 as 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;
        }
    }

    #endregion
}