CEL_Payroll/Payroll.Service/Workflow/DA/WFMovementTranDA.cs

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
{
    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,WFSETUPID,"
              + " WFRULEID,OBJECTID,ISACTIVE,SENTTIME,STATUS,"
              + " UNIQUENUMBER,REMARKS,TIER,OBJECTDESCRIPTION)"
              + " VALUES(%n,%n,%n,%n,"
              + " %n,%n,%n,%d,%n,"
              + " %n,%s,%n,%s)",
              item.ID.Integer, item.FromEmployeeID.Integer, item.FromNodeID.Integer, item.SetupID.Integer,
              item.WFRuleID.Integer, item.ObjectID.Integer, item.IsActive, item.Senttime, 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.Integer, item.WFMovementTranID.Integer, item.NodeID.Integer,
              item.EmployeeID.Integer, 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)"
             + " VALUES(%n,%n,%n,"
             + " %n,%n,%s,%s,%n)",
             item.ID.Integer, item.WFMovementTranID.Integer, item.EmployeeID.Integer,
             item.NodeID.Integer, item.Status, item.Remarks, item.Description, item.ReceiveStatus);
        }

        #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.SetupID.Integer,
                               item.WFRuleID.Integer, item.ObjectID.Integer, item.IsActive, item.Senttime, item.Status,
                               item.Uniquenumber, item.Remarks, item.Tier, item.ObjectDescription, item.ID.Integer);
        }


        internal static void Update(TransactionContext tc, WFMovementTran item)
        {

            tc.ExecuteNonQuery("UPDATE WFMOVEMENTTRAN SET FROMEMPLOYEEID=%n, FROMNODEID=%n, WFSETUPID=%n, "
                              + " WFRULEID=%n, OBJECTID=%n, ISACTIVE=%b, SENTTIME=%d, STATUS=%n,"
                              + " UNIQUENUMBER=%n, REMARKS=%s, TIER=%n, OBJECTDESCRIPTION=%s "
                              + " WHERE WFMOVEMENTTRANID=%n ",
                               item.FromEmployeeID.Integer, item.FromNodeID.Integer, item.SetupID.Integer,
                               item.WFRuleID.Integer, item.ObjectID.Integer, item.IsActive, item.Senttime, item.Status,
                               item.Uniquenumber, item.Remarks, item.Tier, item.ObjectDescription, item.ID.Integer);
        }
        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" +
                " WHERE WFMovementNextID=%n", oItem.WFMovementTranID.Integer, oItem.EmployeeID.Integer, oItem.NodeID.Integer, oItem.Status, oItem.Remarks, oItem.Description, oItem.ReceiveStatus, oItem.ID.Integer);
        }
        public static void Update_WFMovementNext(TransactionContext tc, ID tranID, ID newEmployeeID, ID empID, ID NodeID, string remarks)
        {
            tc.ExecuteNonQuery("UPDATE WFMovementNext SET EmployeeID=%n, NodeID=%n, Remarks=%s" +
                " WHERE WFMovementTranID=%n AND EmployeeID=%n", newEmployeeID.Integer, NodeID.Integer, remarks, tranID.Integer, empID.Integer);
        }
        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.Integer, oItem.NodeID.Integer, oItem.EmployeeID.Integer,
            oItem.Status, oItem.TasksType, oItem.MailSendType, oItem.SentTime, oItem.Description, oItem.ReceiveStatus, oItem.ID.Integer);
        }

        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.Integer);
        }

        #endregion


        #region  Update function
        public static void Update_SysNotification(TransactionContext tc, ID movementTaskID, bool status)
        {
            tc.ExecuteNonQuery("UPDATE WFMovementTask SET  Status=%b " +
            " WHERE WFMOVEMENTTASKID=%n ", status, movementTaskID.Integer);
        }
        #endregion

        #region Delete

        internal static void Delete(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTTASK WFMOVEMENTTASKID=%n", id.Integer);
            tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTNEXT WFMOVEMENTTASKID=%n", id.Integer);
            tc.ExecuteNonQuery("DELETE FROM [WFMOVEMENTTRAN] Where WFMOVEMENTTRANID=%n", id.Integer);
        }

        internal static void DeleteWFMovementTask(TransactionContext tc, ID iD)
        {
            tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n", iD.Integer);
        }

        internal static void DeleteWFMovementNext(TransactionContext tc, ID iD)
        {
            tc.ExecuteNonQuery("DELETE FROM WFMOVEMENTNEXT WHERE WFMOVEMENTNEXTID=%n", iD.Integer);
        }

        #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 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;
            }

        }


        #region Get

        internal static IDataReader Get(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTRAN WHERE WFMOVEMENTTRANID=%n", id.Integer);
        }

        internal static IDataReader GetReceivedWFNext(TransactionContext tc, int wfsetupid, int objectID)
        {
            return tc.ExecuteReader(@"	 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);
        }
        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, ID employeeid)
        {
            return tc.ExecuteReader("SELECT WT.* FROM WFMovementTran WT, WFMovementNext WN  "
            + " WHERE WN.Status=%n AND WN.EmployeeId=%n   "
            + " AND WT.WFMovementTranID = WN.WFMovementTranID", enumwfStatus.Received, employeeid.Integer);
        }

        public static IDataReader PendingItems(TransactionContext tc, ID 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.Integer);
        }

        public static IDataReader NotificationItems(TransactionContext tc, ID employeeid)
        {
            return tc.ExecuteReader("SELECT * FROM  WFMOVEMENTTASK  "
             + " WHERE  EMPLOYEEID=%n AND Status=%b AND TasksType=%n  "
             + " ", employeeid.Integer, false, EnumWFNotifyType.SysNotification);
        }

        public static int ReceivedItemsCount(TransactionContext tc, ID employeeid)
        {
            object ncount = tc.ExecuteScalar("SELECT Count(*) FROM WFMovementTran WT, WFMovementNext WN  "
            + " WHERE WT.Status=%n AND WN.EmployeeId=%n"
            + " AND WT.WFMovementTranID = WN.WFMovementTranID", enumwfStatus.Received, employeeid.Integer);
            if (ncount == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(ncount);
            }
        }

        public static int NotificationItemsCount(TransactionContext tc, ID employeeid)
        {
            object ncount = tc.ExecuteScalar("SELECT Count(*) FROM  WFMOVEMENTTASK  "
            + " WHERE  EMPLOYEEID=%n AND Status=%b AND TasksType=%n "
            + " ", employeeid.Integer, false, EnumWFNotifyType.SysNotification);

            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 Get4WFMovementTask(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n ", id.Integer);
        }
        internal static IDataReader GetWFMovementTasks(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE Status=0 AND TasksType=1");
        }


        internal static IDataReader GetWFMovementTask(TransactionContext tc, ID movementtaskid)
        {
            return tc.ExecuteReader("SELECT * FROM WFMOVEMENTTASK WHERE WFMOVEMENTTASKID=%n", movementtaskid.Integer);
        }

        internal static IDataReader Get4WFMovementNext(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM WFMOVEMENTNEXT WHERE WFMovementTranID=%n", id.Integer);
        }

        internal static IDataReader Get4WFMovementNext(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, ID 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.Integer);
        }
        #endregion

        #endregion
    }
}