253 lines
9.4 KiB
C#
253 lines
9.4 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using Ease.Core.Model;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
#region SearchEmployeeDA
|
|||
|
|
|
|||
|
|
internal class SearchEmployeeDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
public const string fndSQL = @"
|
|||
|
|
IF OBJECT_ID('tempdb..#tmpEmpSrc') IS NOT NULL
|
|||
|
|
DROP TABLE #tmpEmpSrc %q
|
|||
|
|
|
|||
|
|
UPDATE
|
|||
|
|
#tmpEmpSrc
|
|||
|
|
SET
|
|||
|
|
#tmpEmpSrc.GradeName = RAN.DESCRIPTION
|
|||
|
|
FROM
|
|||
|
|
#tmpEmpSrc SI
|
|||
|
|
INNER JOIN
|
|||
|
|
grades RAN
|
|||
|
|
ON
|
|||
|
|
SI.gradeid = RAN.GRADEID;
|
|||
|
|
|
|||
|
|
|
|||
|
|
UPDATE
|
|||
|
|
#tmpEmpSrc
|
|||
|
|
SET
|
|||
|
|
#tmpEmpSrc.DepartmentName = RAN.DESCRIPTION
|
|||
|
|
FROM
|
|||
|
|
#tmpEmpSrc SI
|
|||
|
|
INNER JOIN
|
|||
|
|
DEPARTMENT RAN
|
|||
|
|
ON
|
|||
|
|
SI.departmentid = RAN.DEPARTMENTID;
|
|||
|
|
;
|
|||
|
|
|
|||
|
|
UPDATE
|
|||
|
|
#tmpEmpSrc
|
|||
|
|
SET
|
|||
|
|
#tmpEmpSrc.DesignationName = RAN.NAME
|
|||
|
|
FROM
|
|||
|
|
#tmpEmpSrc SI
|
|||
|
|
INNER JOIN
|
|||
|
|
DESIGNATION RAN
|
|||
|
|
ON
|
|||
|
|
SI.DESIGNATIONID = RAN.DESIGNATIONID;
|
|||
|
|
|
|||
|
|
|
|||
|
|
";
|
|||
|
|
public const string fnddataPermissionSQL = @"
|
|||
|
|
IF EXISTS
|
|||
|
|
(
|
|||
|
|
SELECT*
|
|||
|
|
FROM DataPermission
|
|||
|
|
WHERE userid = %n and PayrollTypeID = %n and PermissionType=1
|
|||
|
|
)
|
|||
|
|
BEGIN
|
|||
|
|
delete from #tmpEmpSrc where categoryid not in ( select referenceid from DataPermission where UserID =%n and PayrollTypeID =%n and PermissionType=1);
|
|||
|
|
END;
|
|||
|
|
IF EXISTS
|
|||
|
|
(
|
|||
|
|
SELECT*
|
|||
|
|
FROM DataPermission
|
|||
|
|
WHERE userid = %n and PayrollTypeID = %n and PermissionType=2
|
|||
|
|
)
|
|||
|
|
BEGIN
|
|||
|
|
delete from #tmpEmpSrc where GRADEID not in ( select referenceid from DataPermission where UserID =%n and PayrollTypeID =%n and PermissionType=2);
|
|||
|
|
END;
|
|||
|
|
|
|||
|
|
IF EXISTS
|
|||
|
|
(
|
|||
|
|
SELECT*
|
|||
|
|
FROM DataPermission
|
|||
|
|
WHERE userid = %n and PayrollTypeID = %n and PermissionType=3
|
|||
|
|
)
|
|||
|
|
BEGIN
|
|||
|
|
IF OBJECT_ID('tempdb..#TEMPDOC') IS NOT NULL DROP TABLE #TEMPDOC
|
|||
|
|
;WITH
|
|||
|
|
cteReports(departmentid)
|
|||
|
|
|
|||
|
|
AS
|
|||
|
|
(
|
|||
|
|
SELECT departmentid FROM DEPARTMENT
|
|||
|
|
|
|||
|
|
WHERE DEPARTMENTID in (select referenceid from DataPermission where UserID = %n and PayrollTypeID = %n and PermissionType = 3)
|
|||
|
|
|
|||
|
|
UNION ALL
|
|||
|
|
|
|||
|
|
SELECT e.departmentid FROM DEPARTMENT e
|
|||
|
|
INNER JOIN cteReports r ON e.PARENTID = r.departmentid
|
|||
|
|
) SELECT* INTO #TEMPDOC FROM cteReports
|
|||
|
|
|
|||
|
|
|
|||
|
|
delete from #tmpEmpSrc where Departmentid NOT in (SELECT * FROM #TEMPDOC);
|
|||
|
|
END;
|
|||
|
|
|
|||
|
|
|
|||
|
|
IF EXISTS
|
|||
|
|
(
|
|||
|
|
SELECT*
|
|||
|
|
FROM DataPermission
|
|||
|
|
WHERE userid = %n and PayrollTypeID = %n and PermissionType=4
|
|||
|
|
)
|
|||
|
|
BEGIN
|
|||
|
|
|
|||
|
|
IF OBJECT_ID('tempdb..#tmpLOC') IS NOT NULL
|
|||
|
|
DROP TABLE #tmpLOC
|
|||
|
|
|
|||
|
|
-- delete from #tmpEmpSrc where locationid NOT in (
|
|||
|
|
; WITH
|
|||
|
|
cteReports(locationid)
|
|||
|
|
|
|||
|
|
AS
|
|||
|
|
(
|
|||
|
|
SELECT locationid FROM location
|
|||
|
|
|
|||
|
|
WHERE LOCATIONid in (select referenceid from DataPermission where UserID = %n and PayrollTypeID = %n and PermissionType = 4)
|
|||
|
|
|
|||
|
|
UNION ALL
|
|||
|
|
|
|||
|
|
SELECT e.locationid FROM location e
|
|||
|
|
INNER JOIN cteReports r ON e.PARENTID = r.locationid
|
|||
|
|
) SELECT* INTO #tmpLOC FROM cteReports
|
|||
|
|
|
|||
|
|
|
|||
|
|
delete from #tmpEmpSrc where locationid NOT in ( SELECT *FROM #tmpLOC);
|
|||
|
|
DROP TABLE #tmpLOC;
|
|||
|
|
END;";
|
|||
|
|
|
|||
|
|
public const string fnddropTempSQL = @"
|
|||
|
|
|
|||
|
|
select* from #tmpEmpSrc order by employeeNo;
|
|||
|
|
DROP TABLE #tmpEmpSrc;
|
|||
|
|
|
|||
|
|
";
|
|||
|
|
private SearchEmployeeDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader Search(TransactionContext tc, string sql, bool withName)
|
|||
|
|
{
|
|||
|
|
if (withName == true)
|
|||
|
|
{
|
|||
|
|
string qry = SQLParser.MakeSQL(SearchEmployeeDA.fndSQL + SearchEmployeeDA.fnddropTempSQL, sql);
|
|||
|
|
return tc.ExecuteReader(qry);
|
|||
|
|
}
|
|||
|
|
else return tc.ExecuteReader(@"%q" , sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Search(TransactionContext tc, string sql, int userid, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
string qry = SQLParser.MakeSQL(SearchEmployeeDA.fndSQL + SearchEmployeeDA.fnddataPermissionSQL + SearchEmployeeDA.fnddropTempSQL, sql,
|
|||
|
|
userid, payrolltypeid,userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid,
|
|||
|
|
userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid);
|
|||
|
|
//return tc.ExecuteReader(@"%q" +
|
|||
|
|
// "", sql, userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid, userid, payrolltypeid);
|
|||
|
|
return tc.ExecuteReader(qry);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader get(TransactionContext tc, int empid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"Select EmployeeID, EmployeeNo, Name, categoryID, GradeID,
|
|||
|
|
LocationID, DepartmentID From Employee where employeeid=%n", empid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader FindCordinator(TransactionContext tc, int? id, int? payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT E.EmployeeID, E.LocationID, E.CategoryID, E.DesignationID, E.DepartmentID, D.DESCRIPTION AS DepartmentName, E.GradeID, G.DESCRIPTION AS GradeName , E.EmployeeNo, E.Name,
|
|||
|
|
E.Emailaddress, DS.NAME AS DesignationName
|
|||
|
|
FROM Employee E JOIN employeecordinator EC
|
|||
|
|
ON E.EMPLOYEEID = EC.EmployeeID
|
|||
|
|
JOIN DEPARTMENT D ON D.DEPARTMENTID = E.DepartmentID
|
|||
|
|
JOIN GRADES G ON G.GRADEID = E.GRADEID
|
|||
|
|
JOIN DESIGNATION DS ON DS.DESIGNATIONID = E.DESIGNATIONID
|
|||
|
|
WHERE EC.CordinatorID = %n AND E.PAYROLLTYPEID = 1
|
|||
|
|
", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetEmployeeNotYetUser(TransactionContext tc, int payrollTypeid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"Select EmployeeID, EmployeeNo, Name, categoryID, GradeID,
|
|||
|
|
LocationID, DepartmentID From Employee e
|
|||
|
|
where e.EmployeeNo not IN (Select LoginID from Users where Usertype=%n) and e.payrolltypeid=%n",
|
|||
|
|
EnumUserType.Employee, payrollTypeid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetTeam(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"Select EmployeeID, EmployeeNo, Name, categoryID, GradeID,
|
|||
|
|
LocationID, DepartmentID From Employee
|
|||
|
|
where linemanagerid =%n", employeeid);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetByEmpIDIn(TransactionContext tc, string empid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"Select e.EmployeeID, e.EmployeeNo, e.Name, e.categoryID, e.GradeID,
|
|||
|
|
e.LocationID, e.DepartmentID, e.DesignationID, dep.Description as DepartmentName, des.Name as DesignationName From Employee e, Department dep, Designation des
|
|||
|
|
where e.EmployeeID IN (%q) and dep.DepartmentID = e.DepartmentID and des.DesignationID = e.DesignationID ", empid);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetTeam(TransactionContext tc, int employeeid, EnumStatus enumStatus)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"Select EmployeeID, EmployeeNo, e.Name, e.categoryID, e.GradeID,
|
|||
|
|
LocationID, d.name as DesignationName, g.DESCRIPTION GradeName, dept.DESCRIPTION DepartmentName From Employee e
|
|||
|
|
left join designation d on d.DESIGNATIONID = e.DESIGNATIONID
|
|||
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID = e.DEPARTMENTID
|
|||
|
|
LEFT JOIN GRADES g ON g.GRADEID = e.GRADEID
|
|||
|
|
where e.linemanagerid = %n and e.status = %n", employeeid, (int)enumStatus);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetTeamForAttenReguApprove(TransactionContext tc, int employeeid, EnumStatus enumStatus)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT DISTINCT e.EmployeeID, EmployeeNo, e.Name, e.categoryID, e.GradeID,
|
|||
|
|
LocationID, d.name as DesignationName, g.DESCRIPTION GradeName, dept.DESCRIPTION DepartmentName From Employee e
|
|||
|
|
left join designation d on d.DESIGNATIONID = e.DESIGNATIONID
|
|||
|
|
LEFT JOIN DEPARTMENT dept ON dept.DEPARTMENTID = e.DEPARTMENTID
|
|||
|
|
LEFT JOIN GRADES g ON g.GRADEID = e.GRADEID
|
|||
|
|
LEFT JOIN DAILYATTNPROCESS dap ON dap.EMPLOYEEID = e.EMPLOYEEID
|
|||
|
|
WHERE dap.WFStatus = 1 AND e.linemanagerid =%n and e.status = %n", employeeid, (int)enumStatus);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Search(TransactionContext tc, int payrollTypeID, string code, string name)
|
|||
|
|
{
|
|||
|
|
string orderby = "name";
|
|||
|
|
string sqlClause = "";
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID =%n AND Status = %n", payrollTypeID, EnumStatus.Active);
|
|||
|
|
if (code != string.Empty)
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("EmployeeNo LIKE %s", ("%" + code + "%"));
|
|||
|
|
orderby = "EmployeeNo";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (name != string.Empty)
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"Select EmployeeID, EmployeeNo, Name, categoryID, GradeID, LocationID, designationid, DepartmentID From Employee %q Order by %s",
|
|||
|
|
sqlClause, orderby);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|