313 lines
14 KiB
C#
313 lines
14 KiB
C#
|
using System;
|
|||
|
|
using Payroll.BO;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Linq;
|
|||
|
|
using Ease.CoreV35.Model;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using Ease.CoreV35.DataAccess;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using Ease.CoreV35.DataAccess.SQL;
|
|||
|
|
|
|||
|
|
namespace Payroll.Service
|
|||
|
|
{
|
|||
|
|
#region Organogram DA
|
|||
|
|
|
|||
|
|
internal class OrganogramDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private OrganogramDA() { }
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramBasic item)
|
|||
|
|
{
|
|||
|
|
string Ssql = SQLParser.MakeSQL("INSERT INTO Organogram(OrganogramID, PositionName, ParentID, Tier, UniqueId,"
|
|||
|
|
+ " PositionNo,CompanyID,DepartmentID,FunctionID,"
|
|||
|
|
+ " GradeID,DesignationID,LocationID,CreatedBy,"
|
|||
|
|
+ " CreationDate, SequenceNo, Status,JDNo,RevisionNo,RevisionDate)"
|
|||
|
|
+ " VALUES(%n, %s, %n, %n, %n,"
|
|||
|
|
+ " %s, %n, %n,%n,"
|
|||
|
|
+ " %n,%n,%n,%n,"
|
|||
|
|
+ " %d,%n,%n,%s,%s,%d)",
|
|||
|
|
item.ID.Integer, item.PositionName, DataReader.GetNullValue(item.ParentID, IDType.Integer), item.Tier, item.UniqueId,
|
|||
|
|
item.PositionNo, item.CompanyID.Integer, item.DepartmentID.Integer, item.FunctionID.Integer,
|
|||
|
|
item.GradeID.Integer, item.DesignationID.Integer, item.LocationID.Integer, item.CreatedBy.Integer,
|
|||
|
|
item.CreatedDate, item.Sequence, item.Status, item.JDNo, item.RevisionNo, DataReader.GetNullValue(item.RevisionDate));
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(Ssql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramAuthority item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganAuthority(OrganAuthorityID, NodeID, AuthorityType, ChildNodeID, EmployeeID)"
|
|||
|
|
+ " VALUES(%n, %n, %n, %n, %n)",
|
|||
|
|
item.ID.Integer,item.NodeID.Integer,item.AuthorityType,item.ChildNodeID.Integer,DataReader.GetNullValue(item.EmployeeID, IDType.Integer));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static int GetMaxTier(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
object minID = tc.ExecuteScalar("SELECT Max(Tier) FROM Organogram");
|
|||
|
|
if (minID == null || minID == DBNull.Value)
|
|||
|
|
return 0;
|
|||
|
|
else
|
|||
|
|
return Convert.ToInt32(minID);
|
|||
|
|
}
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramDelegation item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganDelegation(OrganDelegationID,NodeID,EmployeeID,"
|
|||
|
|
+ " FromDate,ToDate,DelegationType,IsActive,CreatedBy,CreationDate)"
|
|||
|
|
+ " VALUES(%n,%n,%n,"
|
|||
|
|
+ " %d,%d,%s,%n,%n,%d)",
|
|||
|
|
item.ID.Integer, item.NodeID.Integer, item.EmployeeID.Integer,
|
|||
|
|
item.FromDate, item.ToDate, item.IsActive, DataReader.GetNullValue(item.CreatedBy, IDType.Integer), item.CreatedDate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramJobDesc item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganJobDescription(OrganJobDescID,NodeID,Purpose,"
|
|||
|
|
+ " JobSummery,Experience,Education,CreatedBy,CreationDate)"
|
|||
|
|
+ " VALUES(%n,%n,%s,"
|
|||
|
|
+ " %s,%s,%s,%n,%d)",
|
|||
|
|
item.ID.Integer, item.NodeID.Integer, item.Purpose,
|
|||
|
|
item.JobSummery, item.Experience, item.Education,item.CreatedBy.Integer,item.CreatedDate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramResponsibility item)
|
|||
|
|
{
|
|||
|
|
//,CreatedBy,CreationDate ,%n,%d ,item.CreatedBy.Integer,item.CreatedDate
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganResponsibility(OrganResponsibilityID,NodeID,Responsibility,Isprimary)"
|
|||
|
|
+ " VALUES(%n,%n,%s,%n)",
|
|||
|
|
item.ID.Integer, item.NodeID.Integer, item.Responsibility, item.IsPrimary);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramSkillLevel item)
|
|||
|
|
{
|
|||
|
|
//,CreatedBy,CreationDate ,%n,%d ,item.CreatedBy.Integer,item.CreatedDate
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganSkillLevel(OrganSkillLevelID,NodeID,LevelID,SkillID)"
|
|||
|
|
+ " VALUES(%n,%n,%n,%n)",
|
|||
|
|
item.ID.Integer,item.NodeID.Integer,item.LevelID.Integer,item.SkillID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, OrganogramPosition item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO OrganPosition(OrganPositionID,NodeID,PositionTypeID)"
|
|||
|
|
+ " VALUES(%n,%n,%n)",
|
|||
|
|
item.ID.Integer, item.NodeID.Integer, item.PositionTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, OrganogramBasic item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE Organogram SET PositionName=%s, ParentID=%n, Tier=%n, UniqueId=%n,"
|
|||
|
|
+ " PositionNo=%s,CompanyID=%n,DepartmentID=%n,FunctionID=%n,"
|
|||
|
|
+ " GradeID=%n,DesignationID=%n,LocationID=%n, ModifiedBy=%n,"
|
|||
|
|
+ " ModifiedDate=%d, SequenceNo=%n, Status=%n,JDNo=%s,RevisionNo=%s,RevisionDate=%d"
|
|||
|
|
+ " WHERE OrganogramID=%n",
|
|||
|
|
item.PositionName, DataReader.GetNullValue(item.ParentID,IDType.Integer) , item.Tier, item.UniqueId,
|
|||
|
|
item.PositionNo, DataReader.GetNullValue(item.CompanyID, IDType.Integer), DataReader.GetNullValue(item.DepartmentID, IDType.Integer), DataReader.GetNullValue(item.FunctionID, IDType.Integer),
|
|||
|
|
DataReader.GetNullValue(item.GradeID, IDType.Integer), DataReader.GetNullValue(item.DesignationID, IDType.Integer),DataReader.GetNullValue(item.LocationID, IDType.Integer) , item.ModifiedBy.Integer,
|
|||
|
|
item.ModifiedDate, item.Sequence, item.Status,item.JDNo,item.RevisionNo,
|
|||
|
|
DataReader.GetNullValue(item.RevisionDate), item.ID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void UpdateParentAndTier(TransactionContext tc, OrganogramBasic oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE Organogram SET Tier=%n, ParentID=%n"
|
|||
|
|
+ " WHERE OrganogramID=%n", oItem.Tier,
|
|||
|
|
DataReader.GetNullValue(oItem.ParentID, IDType.Integer), oItem.ID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM [Organogram] WHERE OrganogramID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void DeleteOrganAuthority(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOrganDelegation(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOranJobDesc(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOrganResponsibility(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOrganSkilLevel(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOrganPosition(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE OrganogramID=%n", nID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetbyEmployeeID(TransactionContext tc, ID employeeId)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT O.* FROM Organogram O, OrganEmployee A WHERE A.NodeID=O.OrganogramID AND A.EmployeeID =%n", employeeId.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram where Status=%n order by OrganogramID", status);
|
|||
|
|
}
|
|||
|
|
else return tc.ExecuteReader("SELECT * FROM Organogram order by OrganogramID");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByParentID(TransactionContext tc, int parentID,EnumStatus status)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
if (parentID > 0)
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID=%n AND Status=%n", parentID,status);
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE Status=%n AND ParentID is Null",status);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if (parentID > 0)
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID=%n", parentID);
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID is Null");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static IDataReader GetTopParents(TransactionContext tc, EnumStatus status)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID IS NULL AND Status=%n", status);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID IS NULL");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganAuthority(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganDelegation(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganJobDesc(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganResponsibility(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganSkilLevel(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4OrganPosition(TransactionContext tc, ID nodeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetAllOrganPosition(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM OrganPosition");
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetMinNodeIdByDept(TransactionContext tc, ID nDeptID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("select * from Organogram where OrganogramID in (select Min(OrganogramID) from Organogram where DepartmentID =%n)", nDeptID.Integer);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetbyPositionType(TransactionContext tc, ID positionTypeid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("Select Organogram.* from Organogram, OrganPosition where Organogram.OrganogramID = OrganPosition.NodeID and PositionTypeID =%n", positionTypeid.Integer);
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetOrganogramNodes(TransactionContext tc, int nNodeID)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
ds = tc.ExecuteDataSet("WITH RecursiveCte AS " +
|
|||
|
|
"(" +
|
|||
|
|
" SELECT 1 as Tier, H1.OrganogramID, H1.ParentId, H1.PositionName FROM Organogram H1" +
|
|||
|
|
" WHERE OrganogramID = %n" +
|
|||
|
|
" UNION ALL" +
|
|||
|
|
" SELECT RCTE.Tier + 1 as Tier, H2.OrganogramID, H2.ParentId, H2.PositionName FROM Organogram H2" +
|
|||
|
|
" INNER JOIN RecursiveCte RCTE ON H2.ParentId = RCTE.OrganogramID" +
|
|||
|
|
" )" +
|
|||
|
|
" SELECT a.OrganogramID,a.parentid,'Position : '+a.PositionName as PositionName,c.employeeid,c.employeeno,c.employeeno+'('+c.name+')' as noname,'Name : '+c.name as name,'Designation : '+d.name as designation ,c.photopath,c.Gender" +
|
|||
|
|
" FROM RecursiveCte a" +
|
|||
|
|
" left outer join organemployee b on a.OrganogramID=b.nodeid " +
|
|||
|
|
" left outer join employee c on b.employeeid=c.employeeid " +
|
|||
|
|
" left outer join designation d on c.designationid=d.designationid", nNodeID);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetLMOrganogramNodes(TransactionContext tc, int nNodeID)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
ds = tc.ExecuteDataSet("SELECT a.OrganogramID,a.parentid,'Position : '+a.PositionName as PositionName,c.employeeid,c.employeeno,c.employeeno+'('+c.name+')' as noname,'Name : '+c.name as name,'Designation : '+d.name as designation ,c.photopath,c.Gender FROM Organogram a left outer join organemployee b on a.OrganogramID=b.nodeid left outer join employee c on b.employeeid=c.employeeid left outer join designation d on c.designationid=d.designationid where a.organogramid=%n Or parentid=%n", nNodeID, nNodeID);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
}
|