244 lines
11 KiB
C#
244 lines
11 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
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,ApproveBy,ApproveDate,ROLETYPE,Description)" +
|
|||
|
|
" VALUES(%n,%s, %s, %n, %D, %n,%n,%n,%D,%n,%s)", item.ID, item.Code, item.Name, item.CreatedBy,
|
|||
|
|
item.CreatedDate,
|
|||
|
|
item.Sequence, (int)item.RoleStatus,
|
|||
|
|
item.ApproveBy == 0 ? null : item.ApproveBy,
|
|||
|
|
item.ApproveDate == DateTime.MinValue ? null : item.ApproveDate, item.RoleType,
|
|||
|
|
item.Description); //item.ModifiedBy == 0 ? null : item.ModifiedBy, item.ModifiedDate == DateTime.MinValue ? null : item.ModifiedDate,
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, Role.RolePermission item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(@"INSERT INTO RolePermission(RolePermissionID, RoleID, PermissionCode, Name,
|
|||
|
|
Link, Status,ApprovedBy, ApprovedDate, CreatedBy, CreatedDate,ParentID)" +
|
|||
|
|
" VALUES(%n, %n, %s, %s, %s,%n,%n,%D,%n,%D,%n)",
|
|||
|
|
item.ID, item.RoleID, item.PermissionCode, item.Name, item.Link, (int)item.Status,
|
|||
|
|
item.ApproveBy == 0 ? null : item.ApproveBy,
|
|||
|
|
item.ApproveDate == DateTime.MinValue ? null : item.ApproveDate,
|
|||
|
|
item.CreatedBy, item.CreatedDate, item.parentID);
|
|||
|
|
//, item.ModifiedBy == 0 ? null : item.ModifiedBy, item.ModifiedDate == DateTime.MinValue ? null : item.ModifiedDate
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, Role.RoleActionPermission item)
|
|||
|
|
{
|
|||
|
|
//item.Status = EnumStatus.Inactive;
|
|||
|
|
|
|||
|
|
item.CreatedDate = DateTime.Now;
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO RoleActionPermission( RoleActionPermissionID ,RoleID, RolePermissionID, CreatedBy, CreatedDate, PermissionActionCode," +
|
|||
|
|
" Name,ApproveBy,ApproveDate,AuthStatus) VALUES(%n,%n, %n, %n, %D, %s,%s,%n,%D,%n)",
|
|||
|
|
item.ID, item.RoleID, item.RolePermissionID, item.CreatedBy, item.CreatedDate, item.ActionCode,
|
|||
|
|
item.Name,
|
|||
|
|
item.ApproveBy == 0 ? null : item.ApproveBy,
|
|||
|
|
item.ApproveDate == DateTime.MinValue ? null : item.ApproveDate, (int)(item.Status));
|
|||
|
|
//item.ModifiedBy == 0 ? null : item.ModifiedBy, item.ModifiedDate == DateTime.MinValue ? null : item.ModifiedDate,
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, Role item)
|
|||
|
|
{
|
|||
|
|
//string sSql = SQLParser.MakeSQL("UPDATE Role SET Code=%s, name = %s,ModifiedBy=%n,ModifiedDate = %n, Sequence = %n, Status = %n, ApproveBy = %n,ApproveDate=%D,AuthStatus=%n, Description=%s" +
|
|||
|
|
//" WHERE RoleID = %n", item.Code, item.Name, item.ModifiedBy, item.ModifiedDate, item.Sequence, (int)item.RoleStatus, item.ApproveBy, item.ApproveDate, (int)item.RoleStatus, item.Description, item.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE Role SET Code=%s, name = %s, Sequence = %n, ApproveBy = %n,ApproveDate=%D,Status=%n , AUTHORIZEDBY=%n, AUTHORIZEDDATE=%D" +
|
|||
|
|
" WHERE RoleID = %n", item.Code, item.Name, item.Sequence, item.ApproveBy, item.ApproveDate,
|
|||
|
|
(int)item.RoleStatus, item.ModifiedBy, item.ModifiedDate, item.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateRolePermission(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);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateRoleActionPermission(TransactionContext tc, Role.RoleActionPermission item)
|
|||
|
|
{
|
|||
|
|
string sSql = SQLParser.MakeSQL(
|
|||
|
|
"UPDATE RoleActionPermission SET RoleID=%n, RolePermissionID = %n,ModifiedBy=%n,ModifiedDate = %D, PermissionActionCode = %s, Name = %s, ApproveBy = %n,ApproveDate=%D,AuthStatus=%n" +
|
|||
|
|
" WHERE RoleActionPermissionID = %n", item.RoleID, item.RolePermissionID, item.ModifiedBy,
|
|||
|
|
item.ModifiedDate, item.ActionCode, item.Name, item.ApproveBy, item.ApproveDate, (int)item.Status,
|
|||
|
|
item.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE RoleActionPermission SET RoleID=%n, RolePermissionID = %n,ModifiedBy=%n,ModifiedDate = %D, PermissionActionCode = %s, Name = %s, ApproveBy = %n,ApproveDate=%D,AuthStatus=%n" +
|
|||
|
|
" WHERE RoleActionPermissionID = %n", item.RoleID, item.RolePermissionID, item.ModifiedBy,
|
|||
|
|
item.ModifiedDate, item.ActionCode, item.Name, item.ApproveBy, item.ApproveDate, (int)item.Status,
|
|||
|
|
item.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumAuthStatus status)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Role where AuthStatus = %n Order By Sequence", status);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRole(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Role");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Role WHERE RoleID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRolePermissionByRoleID(TransactionContext tc, int roleid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT r.* FROM RolePermission r" +
|
|||
|
|
" WHERE r.RoleID=%n ", roleid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRolePermissionByUserID(TransactionContext tc, int useridID)
|
|||
|
|
{
|
|||
|
|
//string str = SQLParser.MakeSQL(@"SELECT distinct r.* FROM RolePermission r, UserRole u" +
|
|||
|
|
//" WHERE r.RoleID= u.roleid and u.userid=%n order by PermissionCode",
|
|||
|
|
//useridID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(@"SELECT distinct r.*, CAST(REPLACE(r.PermissionCode, '.', '') AS int) pcode FROM RolePermission r, UserRole u
|
|||
|
|
WHERE r.RoleID = u.roleid and u.userid = %n order by pcode",useridID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRoleActionPermissionByRoleID(TransactionContext tc, int roleID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM RoleActionPermission WHERE RoleID=%n", roleID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRoleDesignationRoleID(TransactionContext tc, int roleID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM RoleDesignation WHERE RoleID=%n", roleID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
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, int id)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery("UPDATE Role SET CreatedBy=%n WHERE RoleID = %n", User.CurrentUser.ID, id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM Role WHERE RoleID = %n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteRoleActionPermission(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery("UPDATE Role SET CreatedBy=%n WHERE RoleID = %n", User.CurrentUser.ID, id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM RoleActionPermission WHERE RoleID = %n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteRoleDesignation(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery("UPDATE Role SET CreatedBy=%n WHERE RoleID = %n", User.CurrentUser.ID, id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM RoleDesignation WHERE RoleID = %n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteRolePermission(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery("UPDATE Role SET CreatedBy=%n WHERE RoleID = %n", User.CurrentUser.ID, id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM RolePermission WHERE RoleID = %n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void Delete(TransactionContext tc, String tableName, string columnName, int empID)
|
|||
|
|
{
|
|||
|
|
string query = "Delete from " + tableName + " Where " + columnName + " = %n ";
|
|||
|
|
tc.ExecuteNonQuery(query, empID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int GetNewID(TransactionContext tc, string tableName, string columnName)
|
|||
|
|
{
|
|||
|
|
return tc.GenerateID(tableName, columnName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAllRole(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Role");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetRoleActionPermissionByRolePermissionID(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM RoleActionPermission WHERE RolePermissionID=%n", id);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|