265 lines
12 KiB
C#
265 lines
12 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
|
|||
|
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
internal class UserDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private UserDA() { }
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
item.CreatedDate = DateTime.Now;
|
|||
|
|
|
|||
|
|
//####
|
|||
|
|
string sql = SQLParser.MakeSQL(@"INSERT INTO Users(USERID, COMPUTERNAME, APPROVEDCOMPUTERNAME, USERTYPE, STATUS," +
|
|||
|
|
" LOCKEDWORKSTATION,PASSWORDHINT,LOGINID,USERNAME,PASSWORD, ApprovedBy," +
|
|||
|
|
" ApprovedDate, EMPLOYEEID,ChangePasswordAtNextLogon,NeverExpire,MacAddress,IPAddress," +
|
|||
|
|
" ExpireDate,CreatedDate,OwnerID, reason, authorizedBy, authorizedDate)" +
|
|||
|
|
" VALUES(%n, %s, %s, %n, %n, " +
|
|||
|
|
"%s, %s, %s, %s, %s, %n, " +
|
|||
|
|
" %d, %n, %n, %n, %s, %s, " +
|
|||
|
|
" %D, %D, %n, %s, %n, %D)" ,
|
|||
|
|
item.ID, item.ComputerName, item.ApprovedComputerName, item.UserType, item.UserStatus,
|
|||
|
|
item.LockedWorkStation, item.PasswordHints, item.LoginID, item.UserName, item.Password, item.ApprovedBy,
|
|||
|
|
item.ApprovedDate, item.EmployeeID, item.ChangePasswordAtNextLogon, item.NeverExpire, item.MacAddress, item.IPAddress,
|
|||
|
|
item.ExpireDate, item.CreatedDate,item.ParentID, item.Reason, item.AuthorizedBy, item.AuthorizedDate);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"UPDATE Users SET COMPUTERNAME=%s, APPROVEDCOMPUTERNAME=%s, USERTYPE=%n, LOCKEDWORKSTATION=%s, " +
|
|||
|
|
" LOGINID=%s, USERNAME=%s,PASSWORD=%s, PASSWORDHINT=%s, ApprovedBy=%n, ApprovedDate=%D, EMPLOYEEID=%n, " +
|
|||
|
|
" ChangePasswordAtNextLogon=%n, NeverExpire=%n, MacAddress = %s, IPAddress = %s , " +
|
|||
|
|
" ExpireDate = %D, Status = %n, reason = %s, authorizedBy = %n, authorizedDate = %D " +
|
|||
|
|
" WHERE UserID=%n", item.ComputerName, item.ApprovedComputerName, item.UserType, item.LockedWorkStation,
|
|||
|
|
item.LoginID, item.UserName, item.Password, item.PasswordHints, item.ApprovedBy, DataReader.GetNullValue(item.ApprovedDate), item.EmployeeID,
|
|||
|
|
item.ChangePasswordAtNextLogon, item.NeverExpire, item.MacAddress, item.IPAddress,
|
|||
|
|
DataReader.GetNullValue(item.ExpireDate), item.UserStatus, item.Reason, item.AuthorizedBy, DataReader.GetNullValue(item.AuthorizedDate), item.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
internal static void DoActiveAndIntacive(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"UPDATE Users SET " +
|
|||
|
|
" Status = %n, reason = %s, authorizedBy = %n, authorizedDate = %D " +
|
|||
|
|
" WHERE UserID=%n", item.UserStatus, item.Reason, item.AuthorizedBy, DataReader.GetNullValue(item.AuthorizedDate), item.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void passwordChange(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
item.CreatedDate = DateTime.Now;
|
|||
|
|
item.ApprovedDate = DateTime.Now;
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(@"UPDATE Users SET PASSWORD=%s, PASSWORDHINT=%s, " +
|
|||
|
|
" ChangePasswordAtNextLogon=%n " +
|
|||
|
|
" " +
|
|||
|
|
" WHERE UserID=%n",item.Password, item.PasswordHints,
|
|||
|
|
item.ChangePasswordAtNextLogon, item.ID);
|
|||
|
|
}
|
|||
|
|
internal static void passwordEss(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
item.CreatedDate = DateTime.Now;
|
|||
|
|
item.ApprovedDate = DateTime.Now;
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(@"UPDATE Users SET PASSWORD=%s, " +
|
|||
|
|
" ChangePasswordAtNextLogon=%n " +
|
|||
|
|
" " +
|
|||
|
|
" WHERE UserID=%n", item.Password,false, item.ID);
|
|||
|
|
}
|
|||
|
|
internal static void Update(TransactionContext tc, User item, EnumStatus status)
|
|||
|
|
{
|
|||
|
|
item.ApprovedDate = DateTime.Now;
|
|||
|
|
tc.ExecuteNonQuery("UPDATE Users SET ownerID=%n, Status = %n,ApprovedBy=%n,ApprovedDate=%D,ComputerName=%s,ApprovedComputerName=%s,ExpireDate=%d, AuthStatus=%n WHERE UserID = %n",
|
|||
|
|
item.ParentID, item.UserStatus, item.ApprovedBy, item.ApprovedDate, item.ComputerName,
|
|||
|
|
item.ApprovedComputerName, DataReader.GetNullValue(item.ExpireDate),item.UserStatus, item.ID);
|
|||
|
|
}
|
|||
|
|
internal static void Approve(TransactionContext tc, User item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE Users SET Status = %n, ApprovedBy=%n, ApprovedDate=%D,ComputerName=%s,ApprovedComputerName=%s," +
|
|||
|
|
" ExpireDate=%D WHERE UserID = %n",
|
|||
|
|
EnumAuthStatus.Approved, item.ApprovedBy, item.ApprovedDate, item.ComputerName,
|
|||
|
|
item.ApprovedComputerName, DataReader.GetNullValue(item.ExpireDate), item.ID);
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumSystemType type)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users where UserType = %n", type);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetByEmail(TransactionContext tc, string email)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"select u.* from users u, employee e where " +
|
|||
|
|
" e.employeeNo = u.loginid and u.usertype=%n and e.emailaddress=%s and e.STATUS=%n",
|
|||
|
|
EnumUserType.Employee, email,EnumEmployeeStatus.Live);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetUserByEmail(TransactionContext tc, string email)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"select * from users where usertype = %n and Email = %s", EnumUserType.User, email);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
//{
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Users where UserType = %n", (int)EnumSystemType.Desktop);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
internal static DateTime GetEndofContractDate(TransactionContext tc, string loginID)
|
|||
|
|
{
|
|||
|
|
DateTime endofContractDate = DateTime.MinValue;
|
|||
|
|
object a= tc.ExecuteScalar("SELECT endofcontractdate FROM Employee where employeeno = %s", loginID);
|
|||
|
|
if (a != DBNull.Value)
|
|||
|
|
{
|
|||
|
|
endofContractDate = Convert.ToDateTime(a);
|
|||
|
|
}
|
|||
|
|
return endofContractDate;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static int GetUserId(TransactionContext tc, int employeeid, EnumUserType type)
|
|||
|
|
{
|
|||
|
|
int userId = 0;
|
|||
|
|
object a = tc.ExecuteScalar("SELECT TOP 1 userId FROM Users where EmployeeID = %n and UserType=%n",
|
|||
|
|
employeeid, type);
|
|||
|
|
if (a != DBNull.Value)
|
|||
|
|
{
|
|||
|
|
userId = Convert.ToInt32(a);
|
|||
|
|
}
|
|||
|
|
return userId;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users WHERE UserID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, string loginID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users WHERE LOGINID=%s", loginID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByLogInID(TransactionContext tc, string sLogInID, EnumSystemType eSysType)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users WHERE lower(LOGINID)=%s AND UserType = %n", sLogInID.ToLower(), eSysType);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int employeeid, EnumUserType type)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT TOP 1 * FROM Users WHERE EmployeeID=%n AND UserType = %n ", employeeid, type);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByLoginIDAndPassword(TransactionContext tc, string sLogInID, string sPassword)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT * FROM Users WHERE lower(LOGINID)=%s AND Password=%s AND (status = 1 OR status = 5)", sLogInID.ToLower(), Ease.Core.Utility.Global.CipherFunctions.Encrypt("CeLiMiTeD.AdMIn",sPassword));
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByLoginIDByEmail(TransactionContext tc, string sEmail)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT * FROM Users WHERE lower(Email)=%s", sEmail.ToLower());
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static bool IsSuperUSer(TransactionContext tc, string loginID)
|
|||
|
|
{
|
|||
|
|
bool Exist = false;
|
|||
|
|
Object obj = tc.ExecuteScalar("Select Count(*) FROM Users WHERE LoginID=%s and UserType=%n", loginID, EnumUserType.SuperUser );
|
|||
|
|
Exist = Convert.ToInt32(obj) > 0 ? true : false;
|
|||
|
|
return Exist;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader ADLogIn(TransactionContext tc, string emailAddress, EnumSystemType eSysType)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT u.* FROM USERS u, EMPLOYEE emp WHERE emp.EMPLOYEENO = u.LOGINID AND lower(emp.EMAILADDRESS)=%s AND u.UserType=%n", emailAddress, eSysType);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetUsers(TransactionContext tc, EnumSystemType type, DateTime fromDate, DateTime ToDate)
|
|||
|
|
{
|
|||
|
|
DataSet oIDs = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL(@"Select zu.AuditDate,(SELECT LoginID FROM Users u2 WHERE userid=zu.ownerid) ActionBy,zu.UserName,zu.ComputerName,zu.Status, u2.EmployeeID
|
|||
|
|
from Z_Users zu
|
|||
|
|
INNER JOIN USERS u2 ON u2.USERID = zu.USERID
|
|||
|
|
where zu.AuditDate>= %d
|
|||
|
|
And zu.AuditDate<= %d
|
|||
|
|
AND zu.USERTYPE= %n
|
|||
|
|
AND zu.Status<>12
|
|||
|
|
AND zu.AuditType IN('Inserted','Update (after)')
|
|||
|
|
ORDER BY zu.AuditDate DESC", fromDate, ToDate, (int)type);
|
|||
|
|
|
|||
|
|
oIDs = tc.ExecuteDataSet(sSQL);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oIDs;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, string LoginID, string Name, EnumUserType type)
|
|||
|
|
{
|
|||
|
|
string sqlClause = "";
|
|||
|
|
//sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("UserType=%s", LoginID);
|
|||
|
|
|
|||
|
|
//if (LoginID != string.Empty)
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("LoginID=%s", LoginID);
|
|||
|
|
|
|||
|
|
//if (Name != string.Empty)
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("userName LIKE %s", ("%" + Name + "%")); ;
|
|||
|
|
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("UserType=%n", type);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users %q", sqlClause);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetAll(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
string sqlClause = "";
|
|||
|
|
//sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("UserType=%s", LoginID);
|
|||
|
|
|
|||
|
|
//if (LoginID != string.Empty)
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("LoginID=%s", LoginID);
|
|||
|
|
|
|||
|
|
//if (Name != string.Empty)
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("userName LIKE %s", ("%" + Name + "%")); ;
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Users");
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int UserID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("delete Users WHERE UserID=%n", UserID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|