HRM-10/HRM.DA/DA/Users/SystemAuditHistoryDA.cs
2026-11-17 16:18:50 +06:00

122 lines
6.5 KiB
C#

using System;
using System.Data;
using Ease.Core.DataAccess;
using HRM.BO;
namespace HRM.DA
{
// Change history of the Authentication pages, read from their Z_ tables
// (SystemAuditHistory_DB_Changes.sql). Every query returns the same columns, so one
// MapObject reads them all: AuditType, AuditDate, LoginID, Name, Code, UserType, RoleName,
// RoleType, MenuKey, MenuName, AccessType, Status, Activities, ActionBy, ActionByLoginID,
// ActionByName. Date range: both days inclusive, newest first.
internal class SystemAuditHistoryDA
{
#region Constructor
private SystemAuditHistoryDA()
{
}
#endregion
#region Get Function
// Z_ROLE keeps a copy of the row. "Update (before)" rows are left out (the "after" row
// shows the change). Who did it: the approver on an approval, else the editor, else the creator.
internal static IDataReader GetRole(TransactionContext tc, DateTime dtFrom, DateTime dtTo)
{
string sql = SQLParser.MakeSQL(
@"SELECT CASE z.AUDITTYPE WHEN 'Inserted' THEN 'Role created'
WHEN 'Deleted' THEN 'Role deleted'
ELSE 'Role modified' END AuditType,
z.AUDITDATE AuditDate, NULL LoginID, z.NAME Name, z.CODE Code, NULL UserType,
NULL RoleName, z.ROLETYPE RoleType, NULL MenuKey, NULL MenuName, NULL AccessType,
z.STATUS Status, NULL Activities, a.ActionBy, u.LoginID ActionByLoginID, u.UserName ActionByName
FROM Z_ROLE z
CROSS APPLY (SELECT CASE WHEN z.AUDITTYPE = 'Deleted' THEN NULL
WHEN z.AUDITTYPE = 'Inserted' THEN z.CREATEDBY
WHEN z.STATUS = %n AND z.APPROVEBY IS NOT NULL THEN z.APPROVEBY
ELSE COALESCE(z.AUTHORIZEDBY, z.CREATEDBY) END ActionBy) a
LEFT JOIN Users u ON u.UserID = a.ActionBy
WHERE z.AUDITTYPE <> 'Update (before)'
AND z.AUDITDATE >= %d AND z.AUDITDATE < %d
ORDER BY z.AUDITDATE DESC",
(int)EnumAuthStatus.Approved, dtFrom.Date, dtTo.Date.AddDays(1));
return tc.ExecuteReader(sql);
}
// Z_ROLEPERMISSION (trigger trRolePermission). Menu name from the uploaded menu
// (ConfigurationAttribute), the same lookup RolePermissionDA.GetRolePerAudit uses.
internal static IDataReader GetRolePermission(TransactionContext tc, DateTime dtFrom, DateTime dtTo)
{
string sql = SQLParser.MakeSQL(
@"SELECT z.audittype AuditType, z.auditdate AuditDate, NULL LoginID, NULL Name, r.Code Code, NULL UserType,
r.Name RoleName, r.RoleType RoleType, z.menukey MenuKey,
(SELECT TOP 1 ca.Value FROM ConfigurationAttribute ca
WHERE ca.AttributeName = 'name'
AND ca.ParentID = (SELECT MAX(ck.ParentID) FROM ConfigurationAttribute ck
WHERE ck.Value = z.menukey AND ck.AttributeName = 'key')) MenuName,
NULL AccessType, z.status Status, z.activities Activities,
z.actionby ActionBy, u.LoginID ActionByLoginID, u.UserName ActionByName
FROM Z_ROLEPERMISSION z
LEFT JOIN Role r ON r.RoleID = z.roleid
LEFT JOIN Users u ON u.UserID = z.actionby
WHERE z.auditdate >= %d AND z.auditdate < %d
ORDER BY z.auditdate DESC",
dtFrom.Date, dtTo.Date.AddDays(1));
return tc.ExecuteReader(sql);
}
// Z_USERS (trigger trUsers).
internal static IDataReader GetUser(TransactionContext tc, DateTime dtFrom, DateTime dtTo)
{
string sql = SQLParser.MakeSQL(
@"SELECT z.audittype AuditType, z.auditdate AuditDate, z.loginid LoginID, z.name Name, NULL Code,
z.usertype UserType, NULL RoleName, NULL RoleType, NULL MenuKey, NULL MenuName, NULL AccessType,
NULL Status, z.activities Activities,
z.actionby ActionBy, u.LoginID ActionByLoginID, u.UserName ActionByName
FROM Z_USERS z
LEFT JOIN Users u ON u.UserID = z.actionby
WHERE z.auditdate >= %d AND z.auditdate < %d
ORDER BY z.auditdate DESC",
dtFrom.Date, dtTo.Date.AddDays(1));
return tc.ExecuteReader(sql);
}
// Z_USERROLE (trigger truserrole): Code / name are the role's.
internal static IDataReader GetUserRole(TransactionContext tc, DateTime dtFrom, DateTime dtTo)
{
string sql = SQLParser.MakeSQL(
@"SELECT z.audittype AuditType, z.auditdate AuditDate, NULL LoginID, NULL Name, z.Code Code, NULL UserType,
z.name RoleName, NULL RoleType, NULL MenuKey, NULL MenuName, NULL AccessType,
NULL Status, z.activities Activities,
z.actionby ActionBy, u.LoginID ActionByLoginID, u.UserName ActionByName
FROM Z_USERROLE z
LEFT JOIN Users u ON u.UserID = z.actionby
WHERE z.auditdate >= %d AND z.auditdate < %d
ORDER BY z.auditdate DESC",
dtFrom.Date, dtTo.Date.AddDays(1));
return tc.ExecuteReader(sql);
}
// Z_USERACCESSTYPE (trigger trUserAccessType).
internal static IDataReader GetUserAccessType(TransactionContext tc, DateTime dtFrom, DateTime dtTo)
{
string sql = SQLParser.MakeSQL(
@"SELECT z.audittype AuditType, z.auditdate AuditDate, z.loginid LoginID, z.name Name, NULL Code,
NULL UserType, NULL RoleName, NULL RoleType, NULL MenuKey, NULL MenuName, z.payrolltype AccessType,
NULL Status, z.activities Activities,
z.actionby ActionBy, u.LoginID ActionByLoginID, u.UserName ActionByName
FROM Z_USERACCESSTYPE z
LEFT JOIN Users u ON u.UserID = z.actionby
WHERE z.auditdate >= %d AND z.auditdate < %d
ORDER BY z.auditdate DESC",
dtFrom.Date, dtTo.Date.AddDays(1));
return tc.ExecuteReader(sql);
}
#endregion
}
}