122 lines
6.5 KiB
C#
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
|
|
}
|
|
}
|