864 lines
53 KiB
C#
864 lines
53 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Data;
|
|||
|
|
using Ease.Core.DataAccess.SQL;
|
|||
|
|
using Microsoft.Data.SqlClient;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
internal class WFMovementTranDA
|
|||
|
|
{
|
|||
|
|
#region WFMovementTranDA
|
|||
|
|
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private WFMovementTranDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, WFMovementTran item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO WFMOVEMENTTRAN(WFMOVEMENTTRANID,FROMEMPLOYEEID,FROMNODEID,wftypeid,"
|
|||
|
|
+ " WfRuleID,OBJECTID,ISACTIVE,SENTTIME,WFStatus,"
|
|||
|
|
+ " UNIQUENUMBER,REMARKS,TIER,OBJECTDESCRIPTION)"
|
|||
|
|
+ " VALUES(%n,%n,%n,%n,"
|
|||
|
|
+ " %n,%n,%n,%D,%n,"
|
|||
|
|
+ " %n,%s,%n,%s)",
|
|||
|
|
item.ID, item.FromEmployeeID, item.FromNodeID, item.WFTypeID,
|
|||
|
|
item.WFRuleID, item.ObjectID, item.IsActive, DateTime.Today, item.Status,
|
|||
|
|
item.UniqueNumber, item.Remarks, item.Tier, item.ObjectDescription);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, WFMovementTask item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO WFMOVEMENTTASK(WFMOVEMENTTASKID,WFMOVEMENTTRANID,NODEID,"
|
|||
|
|
+ " EMPLOYEEID,STATUS, REMARKS, TASKSTYPE, MAILSENDTYPE, SENTTIME, DESCRIPTION, RECEIVESTATUS)"
|
|||
|
|
+ " VALUES(%n,%n,%n,"
|
|||
|
|
+ " %n,%n,%s,%n,%n,%d,%s,%n)",
|
|||
|
|
item.ID, item.WFMovementTranID, DataReader.GetNullValue(item.NodeID, 0),
|
|||
|
|
item.EmployeeID, item.Status, item.Remarks, item.TasksType, item.MailSendType, item.SentTime,
|
|||
|
|
item.Description, item.ReceiveStatus);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, WFMovementNext item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO WFMOVEMENTNEXT(WFMOVEMENTNEXTID,WFMOVEMENTTRANID,EMPLOYEEID,"
|
|||
|
|
+ " NODEID,STATUS,REMARKS, DESCRIPTION, RECEIVESTATUS,SequeceNo, ReceiveDateTime, viewDateTime"
|
|||
|
|
+ " ,StatusUpdateTime,HaltReason,HaltReasonDatetime)"
|
|||
|
|
+ " VALUES(%n,%n,%n,"
|
|||
|
|
+ " %n,%n,%s,%s,%n, %n, %D, %D,%D,%s,%D )",
|
|||
|
|
item.ID, item.WFMovementTranID, item.EmployeeID,
|
|||
|
|
DataReader.GetNullValue(item.NodeID, 0), item.Status, item.Remarks, item.Description,
|
|||
|
|
item.ReceiveStatus, item.SequeceNo, DateTime.Today, null, null, null, null);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update
|
|||
|
|
|
|||
|
|
internal static void UpdateSourceStatus(TransactionContext tc, WFMovementTran item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE %q SET %q=%n, WHERE %q=%n "
|
|||
|
|
+ " "
|
|||
|
|
+ " "
|
|||
|
|
+ " ",
|
|||
|
|
item.WFSetup.WFType.StatusUpdateTable, item.WFSetup.WFType.StatusUpdateColumn, item.WFTypeID,
|
|||
|
|
item.WFRuleID, item.ObjectID, item.IsActive, item.Senttime, item.Status,
|
|||
|
|
item.UniqueNumber, item.Remarks, item.Tier, item.ObjectDescription, item.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void ForceComplete(TransactionContext tc, int wftranid, string remarks)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE WFMOVEMENTTRAN SET WFSTATUS=%n"
|
|||
|
|
+ " WHERE WFMOVEMENTTRANID=%n ",
|
|||
|
|
EnumwfStatus.End, wftranid);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE WFMovementNext SET Status=%n, Remarks=%s" +
|
|||
|
|
" WHERE WFMOVEMENTTRANID=%n", EnumwfStatus.End, remarks, wftranid);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static void Update(TransactionContext tc, WFMovementTran item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE WFMOVEMENTTRAN SET FROMEMPLOYEEID=%n, FROMNODEID=%n, WFTypeID=%n, "
|
|||
|
|
+ " WFRULEID=%n, OBJECTID=%n, ISACTIVE=%b, SENTTIME=%d, WFSTATUS=%n,"
|
|||
|
|
+ " UNIQUENUMBER=%n, REMARKS=%s, TIER=%n, OBJECTDESCRIPTION=%s "
|
|||
|
|
+ " WHERE WFMOVEMENTTRANID=%n ",
|
|||
|
|
item.FromEmployeeID, item.FromNodeID, item.WFTypeID,
|
|||
|
|
item.WFRuleID, item.ObjectID, item.IsActive, item.Senttime, item.Status,
|
|||
|
|
item.UniqueNumber, item.Remarks, item.Tier, item.ObjectDescription, item.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void Update_WFMovementNext(TransactionContext tc, WFMovementNext oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE WFMovementNext SET WFMovementTranID=%n,EmployeeID=%n, NodeID=%n,Status=%n, Remarks=%s, Description=%s, RECEIVESTATUS=%n, " +
|
|||
|
|
" StatusUpdateTime=%D WHERE WFMovementNextID=%n", oItem.WFMovementTranID, oItem.EmployeeID,
|
|||
|
|
DataReader.GetNullValue(oItem.NodeID, 0), oItem.Status, oItem.Remarks, oItem.Description,
|
|||
|
|
oItem.ReceiveStatus, oItem.StatusUpdateTime, oItem.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void Update_WFMovementNext(TransactionContext tc, int tranID, int newEmployeeID, int empID, string remarks)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE WFMovementNext SET EmployeeID=%n, Remarks=%s" +
|
|||
|
|
" WHERE WFMovementTranID=%n AND EmployeeID=%n", newEmployeeID,
|
|||
|
|
remarks, tranID, empID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void Update_WFMovementTask(TransactionContext tc, WFMovementTask oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE WFMovementTask SET WFMovementTranID=%n, NodeID=%n, EmployeeID=%n,Status=%n,Remarks=%s, TasksType=%n,MAILSENDTYPE=%n,SENTTIME=%d, DESCRIPTION=%s, RECEIVESTATUS=%n" +
|
|||
|
|
" WHERE WFMovementTaskID=%n", oItem.WFMovementTranID, DataReader.GetNullValue(oItem.NodeID, 0),
|
|||
|
|
oItem.EmployeeID,
|
|||
|
|
oItem.Status, oItem.TasksType, oItem.MailSendType, oItem.SentTime, oItem.Description,
|
|||
|
|
oItem.ReceiveStatus, oItem.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void UpdateObjectStatus(TransactionContext tc, WFMovementTran oTran)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE %q SET %q=%n " +
|
|||
|
|
" WHERE %q=%n", oTran.WFSetup.WFType.StatusUpdateTable,
|
|||
|
|
oTran.WFSetup.WFType.StatusUpdateColumn, oTran.Status,
|
|||
|
|
oTran.WFSetup.WFType.StatusUpdateTablePK, oTran.ObjectID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
public static void Update_SysNotification(TransactionContext tc, int movementTaskID, bool status)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("UPDATE WFMovementTask SET Status=%b " +
|
|||
|
|
" WHERE WFMOVEMENTTASKID=%n AND TasksType=%n ", status, movementTaskID, EnumWFNotifyType.SysNotification);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTTASK WFMOVEMENTTASKID=%n", id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTNEXT WFMOVEMENTTASKID=%n", id);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTTRAN Where WFMOVEMENTTRANID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteWFMovementTask(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n", iD);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteWFMovementNext(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTNEXT WHERE WFMOVEMENTNEXTID=%n", iD);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
public static int InitialTranID(TransactionContext tc, int nUniqueNumber)
|
|||
|
|
{
|
|||
|
|
object oRevertNode;
|
|||
|
|
oRevertNode =
|
|||
|
|
tc.ExecuteScalar("SELECT MIN(WFMovementTranID) FROM WFMovementTran WHERE Uniquenumber=%n " + "",
|
|||
|
|
nUniqueNumber);
|
|||
|
|
|
|||
|
|
if (oRevertNode == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(oRevertNode);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int? ListFieldActorID(TransactionContext tc, int wftypeid, int objectid, string listFieldName)
|
|||
|
|
{
|
|||
|
|
object oRevertNode;
|
|||
|
|
oRevertNode =
|
|||
|
|
tc.ExecuteScalar(@"declare @tablename varchar(255); set @tablename = 'tableA'; declare @pkName varchar(255); set @pkName = 'tableA';
|
|||
|
|
select @tablename = w.STATUSUPDATETABLE, @pkName = STATUSUPDATETABLEPK from wftype w, WFRuleStepActor a, WFRule r
|
|||
|
|
where r.wftypeid = w.wftypeid and a.WFRuleID = r.WFRuleID
|
|||
|
|
and w.WFTYPEID = %n and a.WFActorType = %n;
|
|||
|
|
|
|||
|
|
declare @sql nvarchar(max);
|
|||
|
|
set @sql = 'Select ' + %s +
|
|||
|
|
' from @tablename where @pkName =%n';
|
|||
|
|
|
|||
|
|
set @sql = replace(@sql, '@tablename', @tablename);
|
|||
|
|
set @sql = replace(@sql, '@pkName', @pkName);
|
|||
|
|
|
|||
|
|
exec sp_executesql @sql; ",
|
|||
|
|
wftypeid, EnumWFActorType.ListField, listFieldName, objectid);
|
|||
|
|
|
|||
|
|
if (oRevertNode == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(oRevertNode);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static int InitialEmpID(TransactionContext tc, int nUniqueNumber)
|
|||
|
|
{
|
|||
|
|
object empid;
|
|||
|
|
empid =
|
|||
|
|
tc.ExecuteScalar(@"Select wf.FromEmployeeID from wfmovementTran wf where wf.WFMovementTranID =(" +
|
|||
|
|
" SELECT MIN(WFMovementTranID) FROM WFMovementTran WHERE Uniquenumber=%n )" + "",
|
|||
|
|
nUniqueNumber);
|
|||
|
|
|
|||
|
|
if (empid == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(empid);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static int GetUniqueNumber(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
object nUniqueNumber = 0;
|
|||
|
|
nUniqueNumber = tc.ExecuteScalar("SELECT MAX(UniqueNumber) FROM WFMovementTran ");
|
|||
|
|
if (nUniqueNumber != DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(nUniqueNumber) + 1;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return 1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
internal static int pendingLoanAndWorkflowCount(TransactionContext tc, int empid)
|
|||
|
|
{
|
|||
|
|
var item = tc.ExecuteScalar(@"select a.total + b.total from (select ISNULL(count(*), 0 )" +
|
|||
|
|
" total from LOANSCHEDULE where EmployeeID =143 and PAYMENTDATE is null ) a," +
|
|||
|
|
"( select ISNULL(count(*), 0 ) total from WFMOVEMENTNEXT with (nolock) where" +
|
|||
|
|
" EmployeeID =%n and STATUS =1) b", empid);
|
|||
|
|
if (item == DBNull.Value) return 0;
|
|||
|
|
else return Convert.ToInt32(item);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#region Get
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTRAN WHERE WFMOVEMENTTRANID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetReceivedWFNext(TransactionContext tc, int wfsetupid, int objectID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
@"select wn.* from wfmovementtran wt, WFMOVEMENTNEXT wn where wt.WFMOVEMENTTRANID=wn.WFMOVEMENTTRANID
|
|||
|
|
and wt.WFMOVEMENTTRANID= (select max(WFMOVEMENTTRANID) from wfmovementtran wt where
|
|||
|
|
wt.wfsetupid=%n and objectid =%n) and wn.status=1", wfsetupid, objectID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader GetAllItem(TransactionContext tc, int nUniqueNumber)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM WFMovementTran WHERE Uniquenumber=%n ORDER BY WFMovementTranID DESC", nUniqueNumber);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader ReceivedItems(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT WT.* FROM WFMovementTran WT, WFMovementNext WN "
|
|||
|
|
+ " WHERE WN.Status=%n AND WN.EmployeeId=%n "
|
|||
|
|
+ " AND WT.WFMovementTranID = WN.WFMovementTranID order by SENTTIME desc",
|
|||
|
|
EnumwfStatus.Received, employeeid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataTable PendingJobsMobile(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
List<SqlParameter> p = new List<SqlParameter>
|
|||
|
|
{
|
|||
|
|
SqlHelperExtension.CreateInParam("@EmployeeId", SqlDbType.Int, employeeid),
|
|||
|
|
SqlHelperExtension.CreateInParam("@DailyAttnProcessWFStatus", SqlDbType.TinyInt, (int)EnumWFAttnStatus.EmpSubmitted),
|
|||
|
|
SqlHelperExtension.CreateInParam("@WFMovementNextStatus", SqlDbType.TinyInt, EnumwfStatus.Received),
|
|||
|
|
|
|||
|
|
};
|
|||
|
|
//object ncount = tc.ExecuteReader(CommandType.StoredProcedure, "sp_PendingJobNotificationCount", p.ToArray());
|
|||
|
|
//using (dT = (DataTable)tc.ExecuteDataTable(CommandType.StoredProcedure, "sp_PendingJobNotificationCount", p.ToArray()))
|
|||
|
|
//{
|
|||
|
|
// if (reader.Read())
|
|||
|
|
// {
|
|||
|
|
// int WFMovementTranID = reader.GetInt32(reader.GetOrdinal("WFMovementTranID"));
|
|||
|
|
// int wftypeid = reader.GetInt32(reader.GetOrdinal("wftypeid"));
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
return tc.ExecuteDataTable(CommandType.StoredProcedure, "sp_pendingJobs", p.ToArray());
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataTable GetReceivedItems(TransactionContext tc, int payrolltypeid, int? wftypeid, int? employeeid)
|
|||
|
|
{
|
|||
|
|
string sqlClause = string.Empty;
|
|||
|
|
sqlClause = SQLParser.MakeSQL(" AND E.PayrollTypeID = %n", payrolltypeid);
|
|||
|
|
|
|||
|
|
if (wftypeid != null)
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("WT.wftypeid = %s", wftypeid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (employeeid != null)
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("WN.EmployeeId=%n", employeeid);
|
|||
|
|
}
|
|||
|
|
return tc.ExecuteDataTable("SELECT WN.*, WT.objectDescription, WT.objectID objectID, WT.wftypeid, E.EmployeeID,E.EmployeeNo, E.Name EmpName, w.Name WFTypeName FROM WFtype w, WFMovementTran WT, WFMovementNext WN, Employee E "
|
|||
|
|
+ " WHERE w.wftypeid = WT.wftypeid AND WN.Status=%n AND E.EmployeeID =WN.EmployeeID "
|
|||
|
|
+ " AND WT.WFMovementTranID = WN.WFMovementTranID %q ORDER BY E.EmployeeNo",
|
|||
|
|
EnumwfStatus.Received, sqlClause);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader PendingItems(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT WN.* FROM WFMovementTran WT, WFMovementNext WN "
|
|||
|
|
+ " WHERE WN.Status=%n AND WN.EmployeeId=%n"
|
|||
|
|
+ " AND WT.WFMovementTranID = WN.WFMovementTranID", EnumwfStatus.Received,
|
|||
|
|
employeeid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader NotificationItems(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"
|
|||
|
|
SELECT * FROM WFMOVEMENTTASK
|
|||
|
|
WHERE EMPLOYEEID=%n AND Status=%b AND TasksType=%n
|
|||
|
|
Order by SENTTIME DESC", employeeid, false, EnumWFNotifyType.SysNotification);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataSet GetNotificationAndPendingJobCount(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT COUNT(*) TotalPendingJob From (
|
|||
|
|
SELECT E.NAME +'['+ E.EMPLOYEENO +'] has delegated some responsibility to you.' Description, d.DELEGATERESPONSIBILITYID
|
|||
|
|
TranID, '/work-flow/pending-delegation'+ Cast(d.DELEGATERESPONSIBILITYID as VarChar(500)) Link FROM DELEGATERESPONSIBILITY d
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = d.FROMEMPLOYEEID
|
|||
|
|
WHERE d.TOEMPLOYEEID = %n AND d.ISACCEPTED=0 AND d.Status=1
|
|||
|
|
union
|
|||
|
|
SELECT ObjectDescription Description,WT.WFMovementTranId TranId,'/work-flow/pending-job/'+
|
|||
|
|
Cast(WT.WFMovementTranId as VarChar(500)) Link FROM WFMovementTran WT, WFMovementNext WN
|
|||
|
|
WHERE WN.Status=1 AND WN.EmployeeId=%n
|
|||
|
|
AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
Union
|
|||
|
|
SELECT E.NAME +'['+ E.EMPLOYEENO +'] has reverted your objective' Description, OBJECTIVESET.employeeid TranID,
|
|||
|
|
'/work-flow/submitted-objective/'+ Cast(OBJECTIVESET.OBJECTIVESETID as VarChar(500)) Link FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and (OBLMComplete=3 AND OBEmpComplete=0)
|
|||
|
|
Union
|
|||
|
|
SELECT E.NAME +'['+ E.EMPLOYEENO +'] has submitted his objective' Description,
|
|||
|
|
E.EmployeeID TranID,'/work-flow/team-submitted-objective' Link FROM OBJECTIVESET OS,
|
|||
|
|
Employee E WHERE OS.EMPLOYEEID IN (
|
|||
|
|
Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
Select OrganogramID From Organogram Where ParentID=(
|
|||
|
|
Select NodeID from OrganEmployee Where EmployeeID=%n)))
|
|||
|
|
AND OS.EmployeeID=E.EmployeeID and (OS.OBLMComplete=2 AND OS.OBEmpComplete=1)
|
|||
|
|
Union
|
|||
|
|
SELECT '(Delegated) '+ ObjectDescription Description,WT.WFMovementTranId TranId,'/work-flow/pending-job/'+
|
|||
|
|
Cast(WT.WFMovementTranId as VarChar(500)) Link FROM WFMovementTran WT, WFMovementNext WN, WFSETUP WS
|
|||
|
|
WHERE WS.WFSETUPID=WT.WFSETUPID and WN.Status=1 AND WN.EmployeeId
|
|||
|
|
IN(%n)
|
|||
|
|
AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
) Tab1",
|
|||
|
|
employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid);
|
|||
|
|
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sql);
|
|||
|
|
tempdataset.Tables[0].TableName = "Job";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
@"SELECT COUNT (*) TotalNotification From (SELECT Description, WT.WFMovementTranId TranId,'/work-flow/notification/'+ Cast(
|
|||
|
|
WT.WFMOVEMENTTASKID as VarChar(500)) Link FROM WFMOVEMENTTASK WT
|
|||
|
|
WHERE WT.Status=0 AND WT.EmployeeId=%n AND WT.TasksType=%n
|
|||
|
|
Union
|
|||
|
|
SELECT E.NAME +'['+ E.EMPLOYEENO +'] has accepted your delegation request.' Description, d.DELEGATERESPONSIBILITYID
|
|||
|
|
TranID, '/ad-hoc/accepted-delegation' + Cast(d.DELEGATERESPONSIBILITYID as VarChar(500)) Link FROM DELEGATERESPONSIBILITY d
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = d.TOEMPLOYEEID
|
|||
|
|
WHERE d.FROMEMPLOYEEID = %n AND d.ISACCEPTED=1 AND d.ISREAD=0
|
|||
|
|
Union
|
|||
|
|
SELECT E.NAME +'['+ E.EMPLOYEENO +'] has agreed with your objective' Description, OBJECTIVESET.employeeid TranID,
|
|||
|
|
'/work-flow/submitted-objective/'+ Cast(OBJECTIVESET.OBJECTIVESETID as VarChar(500)) Link FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and NotificationStatus=%n and (OBLMComplete=1 AND OBEmpComplete=1) ) Tab2",
|
|||
|
|
employeeid, (int)EnumWFNotifyType.SysNotification, employeeid, employeeid,
|
|||
|
|
(int)EnumPMSNotificationStatus.OBJNotify);
|
|||
|
|
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sql);
|
|||
|
|
tempdataset.Tables[0].TableName = "Notification";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader GetPendingJob(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT WT.SENTTIME, '' Name , ObjectDescription ObjectDescription, WT.WFMovementTranId Id,
|
|||
|
|
CASE
|
|||
|
|
WHEN WT.wftypeid =1 THEN '/ess/leave-approve'
|
|||
|
|
WHEN WT.wftypeid =2 THEN '/ess/leave-approve'
|
|||
|
|
WHEN WT.wftypeid =23 THEN '/ess/recruitement/Approve-Offer-letter'
|
|||
|
|
WHEN WT.wftypeid =25 THEN '/ess/recruitment-ess/recruitment-requisition-entry/self'
|
|||
|
|
--WHEN WT.wftypeid =4 THEN '/ess/claim/claim-apply-by-line-manager'
|
|||
|
|
WHEN WT.wftypeid =5 THEN '/ess/resignation-emp-approval'
|
|||
|
|
WHEN WT.wftypeid =6 THEN '/ess/letter-request/letter-request-by-signatory-approval'
|
|||
|
|
WHEN WT.wftypeid =10 THEN '/ess/ess-prorated-approve'
|
|||
|
|
ELSE 'none' END AS link,0 count FROM WFMovementTran WT, WFMovementNext WN
|
|||
|
|
WHERE WN.Status = 1 AND WN.EmployeeId = %n AND WT.wftypeid <> 4
|
|||
|
|
AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, 'Your line manage has reverted your objective' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
'/talent-management/objective-setting-list-by-employee' Link,0 count FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and(OBLMComplete = 3 AND OBEmpComplete = 0)
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his objective') ObjectDescription,
|
|||
|
|
E.EmployeeID Id,'/talent-management/objective-setting-approve-by-line-manager' Link,0 count FROM OBJECTIVESET OS,
|
|||
|
|
Employee E WHERE OS.EmployeeID = E.EmployeeID AND E.LINEMANAGERID=%n and(OS.OBLMComplete NOT IN(1, 3) AND OS.OBEmpComplete = 1)
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, 'Your line manager has reverted your mid year review' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
'/talent-management/mid-year-employee-by-employee' Link,0 count FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and(MYlmComplete = 3 AND MYEmpComplete = 0)
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his mid year review') ObjectDescription,
|
|||
|
|
E.EmployeeID Id,'/talent-management/midyear-lm-setting-by-line-manager' Link,0 count FROM OBJECTIVESET OS,
|
|||
|
|
Employee E WHERE OS.EmployeeID = E.EmployeeID AND E.LINEMANAGERID=%n and(OS.MYlmComplete NOT IN(1, 3) AND OS.MYEmpComplete = 5)
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, 'Your Linemanager has reverted your year end review' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
'/talent-management/year-end-assessment-submit-by-employee' Link,0 count FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and(yelmComplete = 3 AND yeEmpComplete = 0)
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his year end review') ObjectDescription,
|
|||
|
|
E.EmployeeID Id,'/talent-management/year-end-assessment-submit-by-line-manager' Link,0 count FROM OBJECTIVESET OS,
|
|||
|
|
Employee E WHERE OS.EmployeeID = E.EmployeeID AND E.LINEMANAGERID=%n and(OS.yelmComplete NOT IN(1, 3) AND OS.yeEmpComplete = 5)
|
|||
|
|
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime,'' Name, CONCAT('You have ', count(*), ' attendance approval pending') ObjectDescription,
|
|||
|
|
0 Id,'/ess/attendance/attendance-regularize-approve' Link, count(*) count
|
|||
|
|
FROM DAILYATTNPROCESS OS
|
|||
|
|
JOIN EMPLOYEE e ON e.EMPLOYEEID = os.EMPLOYEEID
|
|||
|
|
WHERE e.LINEMANAGERID = %n and e.status =1
|
|||
|
|
and OS.WFStatus=1 having count(*)>0
|
|||
|
|
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime,'' Name, CONCAT('You have ', count(*), ' claim requisition approval pending') ObjectDescription,
|
|||
|
|
0 Id,'/ess/claim/claim-apply-by-line-manager' Link, count(*) count
|
|||
|
|
FROM ClaimRequisition OS
|
|||
|
|
INNER JOIN (SELECT wf.EmployeeID, w.ObjectID, wf.WFMovementTranID FROM
|
|||
|
|
WFMovementNext wf ,WFMovementTran w
|
|||
|
|
where w.WFMovementTranID = wf.WFMovementTranID
|
|||
|
|
and w.wftypeid in(4) and wf.Status =1 and wf.EmployeeID =%n) b on os.ClaimRequisitionID = b.ObjectID
|
|||
|
|
having count(*)>0
|
|||
|
|
|
|||
|
|
UNION
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime,NAME,CONCAT(Em.NAME, '[', Em.EMPLOYEENO, '] has submitted his final settlement,need clearance') ObjectDescription,
|
|||
|
|
em.EMPLOYEEID Id,'/ess/ess-final-settlement-process' Link,0 count
|
|||
|
|
FROM SettlementClearance AS sc
|
|||
|
|
INNER JOIN EMPLOYEE AS em ON em.EMPLOYEEID = sc.EmpCleranceID
|
|||
|
|
WHERE SettlementClearanceStatus=1 AND sc.EMPLOYEEID=%n
|
|||
|
|
|
|||
|
|
UNION
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime,us.USERNAME NAME,
|
|||
|
|
CASE WHEN p.PUREQUESTSTATUS = 2 THEN CONCAT(us.USERNAME, ' [',us.LOGINID, '] has Approved your Profile Update Request')
|
|||
|
|
WHEN p. PUREQUESTSTATUS = 3 THEN CONCAT(us.USERNAME, ' [',us.LOGINID, '] has Rejected your Profile Update Request') END as ObjectDescription,
|
|||
|
|
em.EMPLOYEEID Id,'' Link,0 count
|
|||
|
|
FROM PROFILEUPDATEREQUEST AS p
|
|||
|
|
INNER JOIN EMPLOYEE AS em ON em.EMPLOYEEID = p.REQUESTEMPID
|
|||
|
|
INNER JOIN USERS AS us ON us.USERID = p.APPROVEBY
|
|||
|
|
WHERE p.PUREQUESTSTATUS IN (2,3) AND p.REQUESTEMPID=%n
|
|||
|
|
AND REQUESTDATE BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
|
|||
|
|
|
|||
|
|
--ShortLeave
|
|||
|
|
Union
|
|||
|
|
|
|||
|
|
SELECT NULL as SentTime,'' Name, CONCAT('You have ', count(*), ' short-leave approval pending') ObjectDescription,
|
|||
|
|
0 Id,'/ess/leave/short-leave-approve' Link, count(*) count
|
|||
|
|
FROM ShortLeave SL
|
|||
|
|
JOIN EMPLOYEE e ON e.EMPLOYEEID = SL.EMPLOYEEID
|
|||
|
|
WHERE SL.LINEMANAGERID = %n and e.status =1
|
|||
|
|
and SL.GatePassStatus=1 having count(*)>0
|
|||
|
|
|
|||
|
|
Order by WT.SENTTIME desc"
|
|||
|
|
|
|||
|
|
, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid);
|
|||
|
|
|
|||
|
|
#region According to Organogram
|
|||
|
|
// sql = SQLParser.MakeSQL(@"SELECT WT.SENTTIME, '' Name , ObjectDescription ObjectDescription, WT.WFMovementTranId Id, '/ess/leave-approve' Link FROM WFMovementTran WT, WFMovementNext WN
|
|||
|
|
// WHERE WN.Status = 1 AND WN.EmployeeId = %n
|
|||
|
|
// AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
// Union
|
|||
|
|
// SELECT NULL as SentTime, E.Name NAME, 'Your line manage has reverted your objective' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
// '/talent-management/objective-setting-list-by-employee' Link FROM OBJECTIVESET
|
|||
|
|
// LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
// WHERE OBJECTIVESET.employeeid = %n and(OBLMComplete = 3 AND OBEmpComplete = 0)
|
|||
|
|
// Union
|
|||
|
|
// SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his objective') ObjectDescription,
|
|||
|
|
// E.EmployeeID Id,'/talent-management/objective-setting-approve-by-line-manager' Link FROM OBJECTIVESET OS,
|
|||
|
|
// Employee E WHERE OS.EMPLOYEEID IN(
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID in (
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID= %n)))
|
|||
|
|
// AND OS.EmployeeID = E.EmployeeID and(OS.OBLMComplete NOT IN(1, 3) AND OS.OBEmpComplete = 1)
|
|||
|
|
//Union
|
|||
|
|
//SELECT NULL as SentTime, E.Name NAME, 'Your line manager has reverted your mid year review' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
// '/talent-management/mid-year-employee-by-employee' Link FROM OBJECTIVESET
|
|||
|
|
// LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
// WHERE OBJECTIVESET.employeeid = %n and(MYlmComplete = 3 AND MYEmpComplete = 0)
|
|||
|
|
// Union
|
|||
|
|
// SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his mid year review') ObjectDescription,
|
|||
|
|
// E.EmployeeID Id,'/talent-management/midyear-lm-setting-by-line-manager' Link FROM OBJECTIVESET OS,
|
|||
|
|
// Employee E WHERE OS.EMPLOYEEID IN(
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID in (
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID= %n)))
|
|||
|
|
// AND OS.EmployeeID = E.EmployeeID and(OS.MYlmComplete NOT IN(1, 3) AND OS.MYEmpComplete = 5)
|
|||
|
|
//Union
|
|||
|
|
//SELECT NULL as SentTime, E.Name NAME, 'Your Linemanager has reverted your year end review' ObjectDescription, OBJECTIVESET.employeeid Id,
|
|||
|
|
// '/talent-management/year-end-assessment-submit-by-employee' Link FROM OBJECTIVESET
|
|||
|
|
// LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
// WHERE OBJECTIVESET.employeeid = %n and(yelmComplete = 3 AND yeEmpComplete = 0)
|
|||
|
|
// Union
|
|||
|
|
// SELECT NULL as SentTime, E.Name NAME, CONCAT(E.NAME, '[', E.EMPLOYEENO, '] has submitted his year end review') ObjectDescription,
|
|||
|
|
// E.EmployeeID Id,'/talent-management/year-end-assessment-submit-by-line-manager' Link FROM OBJECTIVESET OS,
|
|||
|
|
// Employee E WHERE OS.EMPLOYEEID IN(
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID in (
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID= %n)))
|
|||
|
|
// AND OS.EmployeeID = E.EmployeeID and(OS.yelmComplete NOT IN(1, 3) AND OS.yeEmpComplete = 5)
|
|||
|
|
//", employeeid, employeeid, employeeid, employeeid, employeeid, employeeid, employeeid);
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Old Code
|
|||
|
|
|
|||
|
|
// sql = SQLParser.MakeSQL(
|
|||
|
|
// @" SELECT NULL as SentTime, sg.get(E.Name) , sg.get(E.NAME) ||'['||sg.get( E.EMPLOYEENO) ||'] has delegated some responsibility to you.' Description, d.DELEGATERESPONSIBILITYID
|
|||
|
|
// TranID, '/work-flow/pending-delegation/'||d.DELEGATERESPONSIBILITYID Link FROM DELEGATERESPONSIBILITY d
|
|||
|
|
// LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = d.FROMEMPLOYEEID
|
|||
|
|
// WHERE d.TOEMPLOYEEID = %n AND d.ISACCEPTED=0 AND d.Status=1
|
|||
|
|
// union
|
|||
|
|
// SELECT WT.SENTTIME, sg.get(emp.Name) , ObjectDescription Description,WT.WFMovementTranId TranId,'/work-flow/pending-job/'||
|
|||
|
|
// WT.WFMovementTranId Link FROM WFMovementTran WT, WFMovementNext WN, Employee emp
|
|||
|
|
// WHERE WN.Status=1 AND WN.EmployeeId=%n AND Emp.EMPLOYEEID=WT.FROMEMPLOYEEID
|
|||
|
|
// AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
// Union
|
|||
|
|
//SELECT SentTime,Name, Description, TranID,Link FROM (
|
|||
|
|
//SELECT NULL as SentTime,'' Name, 'You have ' ||count(*) || ' OT preapproval pending' Description,
|
|||
|
|
// 0 TranID,'/overtime/lm-ot-preapproval' Link ,count(*) count
|
|||
|
|
// ,extract(day FROM SYSDATE) AS c
|
|||
|
|
// FROM OTPreApproval OS
|
|||
|
|
// WHERE OS.EMPLOYEEID IN (
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID=(
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID=%n)))
|
|||
|
|
// and OS.WFStatus=1 ) T WHERE count>0
|
|||
|
|
// Union
|
|||
|
|
//SELECT SentTime,Name, Description, TranID,Link FROM (
|
|||
|
|
//SELECT NULL as SentTime,'' Name, 'You have ' ||count(*) || ' OT approval pending' Description,
|
|||
|
|
// 0 TranID,'/overtime/lm-ot-approve' Link,count(*) count
|
|||
|
|
// ,extract(day FROM SYSDATE) AS c
|
|||
|
|
// FROM OTSubmit OS
|
|||
|
|
// WHERE OS.EMPLOYEEID IN (
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID=(
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID=%n)))
|
|||
|
|
// and OS.WFStatus=1 ) T WHERE count>0
|
|||
|
|
// Union
|
|||
|
|
//SELECT SentTime,Name, Description, TranID,Link FROM (
|
|||
|
|
//SELECT NULL as SentTime,'' Name, 'You have ' ||count(*) || ' attendance approval pending' Description,
|
|||
|
|
// 0 TranID,'/attendance/attendance-ot-lm-approve' Link ,count(*) count
|
|||
|
|
// ,extract(day FROM SYSDATE) AS c
|
|||
|
|
// FROM DAILYATTNPROCESS OS
|
|||
|
|
// WHERE OS.EMPLOYEEID IN (
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID=(
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID=%n)))
|
|||
|
|
// and OS.WFStatus=1 ) T WHERE count>0
|
|||
|
|
//union
|
|||
|
|
// SELECT NULL as SentTime, sg.get(E.Name) NAME, sg.get(E.NAME) NAME||'['|| sg.get(E.EMPLOYEENO) EMPLOYEENO||'] has reverted your objective' Description, OBJECTIVESET.employeeid TranID,
|
|||
|
|
// '/work-flow/submitted-objective' Link FROM OBJECTIVESET
|
|||
|
|
// LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
// WHERE OBJECTIVESET.employeeid = %n and (OBLMComplete=3 AND OBEmpComplete=0)
|
|||
|
|
// Union
|
|||
|
|
// SELECT NULL as SentTime, sg.get(E.Name) NAME, sg.get(E.NAME) NAME||'['|| sg.get(E.EMPLOYEENO) EMPLOYEENO||'] has submitted his objective' Description,
|
|||
|
|
// E.EmployeeID TranID,'/work-flow/team-submitted-objective' Link FROM OBJECTIVESET OS,
|
|||
|
|
// Employee E WHERE OS.EMPLOYEEID IN (
|
|||
|
|
// Select oe.EmployeeID from OrganEmployee oe Where oe.NodeID IN(
|
|||
|
|
// Select OrganogramID From Organogram Where ParentID=(
|
|||
|
|
// Select NodeID from OrganEmployee Where EmployeeID=%n)))
|
|||
|
|
// AND OS.EmployeeID=E.EmployeeID and (OS.OBLMComplete=2 AND OS.OBEmpComplete=1)
|
|||
|
|
// Union
|
|||
|
|
// SELECT WT.SENTTIME, sg.get(emp.NAME) NAME, '(Delegated) '|| ObjectDescription Description,WT.WFMovementTranId TranId,'/work-flow/pending-job/'||
|
|||
|
|
// WT.WFMovementTranId Link FROM WFMovementTran WT, WFMovementNext WN, WFSETUP WS , Employee emp
|
|||
|
|
// WHERE WS.WFSETUPID=WT.WFSETUPID and WN.Status=1 AND Emp.EMPLOYEEID=WT.FROMEMPLOYEEID AND WN.EmployeeId
|
|||
|
|
// IN(SELECT FROMEMPLOYEEID FROM DELEGATERESPONSIBILITY WHERE Status=1 and ((ISUnlimited=1) OR (FromDate<=TRUNC(SYSDATE) AND TODATE>=TRUNC(SYSDATE) )) AND ToEmployeeID=%n AND ISACCEPTED=1)
|
|||
|
|
// AND WS.WFTYPEID IN (
|
|||
|
|
// SELECT case ISLEAVE WHEN 1 THEN 1 ELSE 0 end FROM DELEGATERESPONSIBILITY WHERE Status=1 and ((ISUnlimited=1) OR (FromDate<=TRUNC(SYSDATE) AND TODATE>=TRUNC(SYSDATE) )) AND ToEmployeeID=%n AND ISACCEPTED=1
|
|||
|
|
// UNION
|
|||
|
|
// SELECT case ISAttendance WHEN 1 THEN 3 ELSE 0 end FROM DELEGATERESPONSIBILITY WHERE Status=1 and ((ISUnlimited=1) OR (FromDate<=TRUNC(SYSDATE) AND TODATE>=TRUNC(SYSDATE) )) AND ToEmployeeID=%n AND ISACCEPTED=1
|
|||
|
|
// UNION
|
|||
|
|
// SELECT case ISLOAN WHEN 1 THEN 2 ELSE 0 end FROM DELEGATERESPONSIBILITY WHERE Status=1 and ((ISUnlimited=1) OR (FromDate<=TRUNC(SYSDATE) AND TODATE>=TRUNC(SYSDATE) )) AND ToEmployeeID=%n AND ISACCEPTED=1
|
|||
|
|
// )
|
|||
|
|
// AND WT.WFMovementTranID = WN.WFMovementTranID
|
|||
|
|
// ", employeeid, employeeid, employeeid, employeeid, employeeid, employeeid,
|
|||
|
|
// employeeid, employeeid, employeeid, employeeid, employeeid);
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataSet GetNotification(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT Description, WT.WFMovementTranId TranId, '/work-flow/notification/'||
|
|||
|
|
WT.WFMOVEMENTTASKID Link FROM WFMOVEMENTTASK WT
|
|||
|
|
WHERE WT.Status=0 AND WT.EmployeeId=%n AND WT.TasksType=%n
|
|||
|
|
Union
|
|||
|
|
SELECT sg.get(E.NAME) NAME||'['|| sg.get(E.EMPLOYEENO) EMPLOYEENO||'] has accepted your delegation request.' Description, d.DELEGATERESPONSIBILITYID
|
|||
|
|
TranID, '/ad-hoc/accepted-delegation/'||d.DELEGATERESPONSIBILITYID Link FROM DELEGATERESPONSIBILITY d
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = d.TOEMPLOYEEID
|
|||
|
|
WHERE d.FROMEMPLOYEEID = %n AND d.ISACCEPTED=1 AND d.ISREAD=0
|
|||
|
|
Union
|
|||
|
|
SELECT sg.get(E.NAME) NAME||'['|| sg.get(E.EMPLOYEENO) EMPLOYEENO||'] has agreed with your objective' Description, OBJECTIVESET.employeeid TranID,
|
|||
|
|
'/work-flow/submitted-objective/'||OBJECTIVESET.OBJECTIVESETID Link FROM OBJECTIVESET
|
|||
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = OBJECTIVESET.LMID
|
|||
|
|
WHERE OBJECTIVESET.employeeid = %n and NotificationStatus=%n AND (OBLMComplete=1 AND OBEmpComplete=1)",
|
|||
|
|
employeeid, (int)EnumWFNotifyType.SysNotification, employeeid, employeeid,
|
|||
|
|
(int)EnumPMSNotificationStatus.OBJNotify);
|
|||
|
|
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sql);
|
|||
|
|
tempdataset.Tables[0].TableName = "Notification";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static string GetPendingJobNotificationCount(TransactionContext tc, int EmployeeID)
|
|||
|
|
{
|
|||
|
|
string countString = null;
|
|||
|
|
int AttnDataByWFStatusCount = 0, ReceivedItemsCount = 0, notificationCount = 0;
|
|||
|
|
|
|||
|
|
List<SqlParameter> p = new List<SqlParameter>
|
|||
|
|
{
|
|||
|
|
SqlHelperExtension.CreateInParam("@EmployeeId", SqlDbType.Int, EmployeeID),
|
|||
|
|
SqlHelperExtension.CreateInParam("@DailyAttnProcessWFStatus", SqlDbType.TinyInt, (int)EnumWFAttnStatus.EmpSubmitted),
|
|||
|
|
SqlHelperExtension.CreateInParam("@WFMOVEMENTTASKStatus", SqlDbType.Bit, false),
|
|||
|
|
SqlHelperExtension.CreateInParam("@TaskType", SqlDbType.TinyInt, EnumWFNotifyType.SysNotification),
|
|||
|
|
SqlHelperExtension.CreateInParam("@WFMovementNextStatus", SqlDbType.TinyInt, EnumwfStatus.Received),
|
|||
|
|
|
|||
|
|
};
|
|||
|
|
//object ncount = tc.ExecuteReader(CommandType.StoredProcedure, "sp_PendingJobNotificationCount", p.ToArray());
|
|||
|
|
|
|||
|
|
using (SqlDataReader reader = (SqlDataReader)tc.ExecuteReader(CommandType.StoredProcedure, "sp_PendingJobNotificationCount", p.ToArray()))
|
|||
|
|
{
|
|||
|
|
if (reader.Read())
|
|||
|
|
{
|
|||
|
|
AttnDataByWFStatusCount = reader.GetInt32(0);
|
|||
|
|
ReceivedItemsCount = reader.GetInt32(1);
|
|||
|
|
notificationCount = reader.GetInt32(2);
|
|||
|
|
//Console.WriteLine($"First Value: {firstValue}, Second Value: {secondValue}");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return countString = AttnDataByWFStatusCount.ToString() + "#" + ReceivedItemsCount.ToString() + "#" + notificationCount.ToString();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int ReceivedItemsCount(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
object ncount = tc.ExecuteScalar("SELECT Count(*) FROM WFMovementTran WT, WFMovementNext WN With(NoLock)"
|
|||
|
|
+ " WHERE WN.Status=%n AND WN.EmployeeId=%n"
|
|||
|
|
+ " AND WT.WFMovementTranID = WN.WFMovementTranID", EnumwfStatus.Received,
|
|||
|
|
employeeid);
|
|||
|
|
if (ncount == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(ncount);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int NotificationItemsCount(TransactionContext tc, int employeeid)
|
|||
|
|
{
|
|||
|
|
object ncount = tc.ExecuteScalar("SELECT Count(*) FROM WFMOVEMENTTASK With(NoLock)"
|
|||
|
|
+ " WHERE EMPLOYEEID=%n AND Status=%b AND TasksType=%n and SentTime>%d"
|
|||
|
|
+ " ", employeeid, false, EnumWFNotifyType.SysNotification, DateTime.Today.AddMonths(-1));
|
|||
|
|
|
|||
|
|
if (ncount == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return Convert.ToInt32(ncount);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTRAN");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetFlowHistory(TransactionContext tc, int? tranid, int? uniqueNumber)
|
|||
|
|
{
|
|||
|
|
if (tranid != null || tranid != -1)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT wn.UniqueNumber, wn.SENTTIME, next.*, e.EmployeeNo, e.Name FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next, Employee e where e.EmployeeID=next.EmployeeID
|
|||
|
|
and wn.WFMovementTranID=next.WFMovementTranID and wn.UniqueNumber =
|
|||
|
|
(select UniqueNumber from WFMovementTran wft where wft.WFMovementTranID=%n) order by next.WFMOVEMENTNEXTID desc, wn.SENTTIME desc", tranid);
|
|||
|
|
}
|
|||
|
|
else if (uniqueNumber != null || uniqueNumber != -1)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT wn.UniqueNumber , wn.TranDate, next.StatusUpdateTime wn.*, e.EmployeeNo, e.EmployeeName FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next, Employee e where e.EmployeeID=n.EmployeeID
|
|||
|
|
and wn.WFMovementTranID=next.WFMovementTranID and wn.UniqueNumber =%n order by wn.trandate", uniqueNumber);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
// if both null, which is not possible, however if anyhow the method is invoked with both of the value null....
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetFlowHistoryByObjectId(TransactionContext tc, int? objectId, int wfTypeId, int? employeeId)
|
|||
|
|
{
|
|||
|
|
if (objectId != null && objectId > 0)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT wn.UniqueNumber, wn.SENTTIME, next.*, e.EmployeeNo, e.Name FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next, Employee e where e.EmployeeID=next.EmployeeID
|
|||
|
|
and wn.WFMovementTranID=next.WFMovementTranID and wn.UniqueNumber =
|
|||
|
|
(select distinct wft.UniqueNumber from WFMovementTran wft where wft.objectID= %n AND wft.wftypeid=%n) order by next.WFMOVEMENTNEXTID desc, wn.SENTTIME desc", objectId, wfTypeId);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT wn.UniqueNumber, wn.SENTTIME, next.*, e.EmployeeNo, e.Name FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next, Employee e where e.EmployeeID=next.EmployeeID
|
|||
|
|
and wn.WFMovementTranID=next.WFMovementTranID and wn.UniqueNumber =
|
|||
|
|
(select distinct wft.UniqueNumber from WFMovementTran wft where wft.fromEmployeeID= %n AND wft.wftypeid=%n) order by next.WFMOVEMENTNEXTID desc, wn.SENTTIME desc", employeeId, wfTypeId);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4WFMovementTask(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n ", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetWFMovementTasks(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE Status=0 AND TasksType=1");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetWFMovementTask(TransactionContext tc, int movementtaskid)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n", movementtaskid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4WFMovementNext(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM WFMOVEMENTNEXT WHERE WFMovementTranID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get4WFMovementNextWithTranID(TransactionContext tc, int uniqueNumber)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM WFMOVEMENTNEXT WHERE WFMovementTranID In ( select WFMovementTranID from WFMOVEMENTTRAN where uniqueNumber=%n) order by WFMOVEMENTNEXTID DESC",
|
|||
|
|
uniqueNumber);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetWFMovementByWFType(TransactionContext tc, int wFTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM WFMOVEMENTTRAN WHERE WFSETUPID IN(SELECT WFSETUPID FROM WFSETUP WHERE WFTYPEID=%n) AND STATUS IN(0,1,2,3,5,8)",
|
|||
|
|
wFTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetClaimRequisitionTranItemsForApproval(TransactionContext tc, int employeId)
|
|||
|
|
{
|
|||
|
|
string strSQLQuery = SQLParser.MakeSQL(@"SELECT WT.*
|
|||
|
|
FROM WFMovementTran WT, WFMovementNext WN
|
|||
|
|
WHERE WN.Status = %n AND WN.EmployeeId =%n AND WT.wftypeid =4
|
|||
|
|
AND WT.WFMovementTranID = WN.WFMovementTranID", (int)enumwfStatus.Received, employeId);
|
|||
|
|
return tc.ExecuteReader(strSQLQuery);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetClaimRequisitionTranItemHistory(TransactionContext tc, int claimId, int employeId)
|
|||
|
|
{
|
|||
|
|
string strSQLQuery = SQLParser.MakeSQL(@"SELECT wn.UniqueNumber, wn.SENTTIME, next.*, e.EmployeeNo, e.Name FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next,
|
|||
|
|
Employee e where e.EmployeeID=next.EmployeeID
|
|||
|
|
and wn.WFMovementTranID=next.WFMovementTranID and wn.UniqueNumber =
|
|||
|
|
(select wft.WFMovementTranID FROM WFMovementTran wft
|
|||
|
|
WHERE wft.wftypeid =4 AND wft.objectID=%n) order by next.WFMOVEMENTNEXTID desc, wn.SENTTIME desc", claimId);
|
|||
|
|
return tc.ExecuteReader(strSQLQuery);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetWfTranByObjectId(TransactionContext tc, int objectId, int wftypeid, int wfruleId)
|
|||
|
|
{
|
|||
|
|
string strSQLQuery = "";
|
|||
|
|
|
|||
|
|
if (wfruleId == 0)
|
|||
|
|
{
|
|||
|
|
strSQLQuery = SQLParser.MakeSQL(@"SELECT * FROM WFMovementTran WHERE WFMovementTranID IN (
|
|||
|
|
SELECT MAX(WT.WFMovementTranID)
|
|||
|
|
FROM WFMovementTran WT
|
|||
|
|
WHERE WT.WfruleID =(SELECT wfruleID FROM WFRule AS w WHERE w.WFTypeID=%n) AND WT.wftypeid =%n AND WT.ObjectID=%n)
|
|||
|
|
", wftypeid, wftypeid, objectId);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
strSQLQuery = SQLParser.MakeSQL(@"SELECT * FROM WFMovementTran WHERE WFMovementTranID IN (
|
|||
|
|
SELECT MAX(WT.WFMovementTranID)
|
|||
|
|
FROM WFMovementTran WT
|
|||
|
|
WHERE WT.WfruleID =%n AND WT.wftypeid =%n AND WT.ObjectID=%n)
|
|||
|
|
", wfruleId, wftypeid, objectId);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(strSQLQuery);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
}
|