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 p = new List { 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 p = new List { 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 } }