CEL_Payroll/Payroll.Service/Notefication/DA/NotificationDA.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Ease.CoreV35.DataAccess;
using Ease.CoreV35.Model;
using Payroll.BO;

namespace Payroll.Service
{
    internal class NotificationDA
    {
        #region Get
        
        internal static IDataReader Get(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM Notification where NotificationID=%n",id.Integer);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM Notification");
        }

        internal static IDataReader GetByDateRange(TransactionContext tc, DateTime startDate, DateTime endDate)
        {
            String sql = SQLParser.MakeSQL(@"SELECT * FROM [Notification] n
                                    WHERE n.NotificationID IN 
                                    (
	                                    SELECT dt.NotificationID FROM
	                                    (SELECT MAX(nr.SendDate) SendDate,nr.NotificationID FROM NotificationRule nr
	                                    GROUP BY nr.NotificationID) dt
	                                    WHERE dt.SendDate BETWEEN %d AND %d
	                                    UNION
	                                    SELECT dt.NotificationID FROM
	                                    (SELECT MIN(nr.SendDate) SendDate,nr.NotificationID FROM NotificationRule nr
	                                    GROUP BY nr.NotificationID) dt
	                                    WHERE dt.SendDate BETWEEN %d AND %d	
                                    )", startDate, endDate, startDate, endDate);
            return tc.ExecuteReader(sql);
        }

        public static IDataReader Get(TransactionContext tc, EnumNotificationStatus status)
        {
            return tc.ExecuteReader("SELECT * FROM Notification WHERE Status=%n",(int)status);
        }
        internal static IDataReader GetParticipants(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM NotificationParticipant where NotificationID=%n",id.Integer);
        }

        internal static IDataReader GetNotificationRules(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM NotificationRule where NotificationID=%n ORDER BY BatchNo", id.Integer);
        }

        internal static DateTime GetMinDateByID(TransactionContext tc, ID iD)
        {
            string sql =
                SQLParser.MakeSQL("SELECT MIN(nr.SendDate) FROM NotificationRule nr WHERE nr.NotificationID = %n", iD.Integer);
            DateTime sendDate = (DateTime)tc.ExecuteScalar(sql);
            return sendDate;
        }

        #endregion

        #region Delete
        
        internal static void Delete(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM Notification WHERE NotificationID=%n", id.Integer);

        }

        internal static void DeleteParticipants(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM NotificationParticipant WHERE NotificationID=%n", id.Integer);
        }

        internal static void DeleteNotificationRules(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM NotificationRule WHERE NotificationID=%n", id.Integer);
        }

        #endregion

        #region Insert
        internal static void Insert(TransactionContext tc, Notification item)
        { 
            string sql = SQLParser.MakeSQL("INSERT INTO Notification(NotificationID, TagOutputID,ObjectID,OptionalObjectID, Subject, Body, Attachment, Footer,Type,IsMailNf,IsSMSNf,IsWebNf,Status)" +
            " VALUES(%n,%n,%n,%n,%s, %s, %s, %s,%n,%b,%b,%b,%n)", item.ID.Integer, DataReader.GetNullValue(item.TagOutputID, IDType.Integer), DataReader.GetNullValue(item.ObjectID, IDType.Integer), DataReader.GetNullValue(item.OptionalObjectID, IDType.Integer), item.Subject, item.Body, item.Attachment, item.Footer, (int)item.Type, item.IsMailNf, item.IsSMSNf, item.IsWebNf, (int)item.Status);
            tc.ExecuteNonQuery(sql);

        }
        internal static void InsertNotificationRule(TransactionContext tc, NotificationRule item)
        {
            string sql =
                SQLParser.MakeSQL(
                    "INSERT INTO NotificationRule(NotificationRuleID,BatchNo,NotificationID,PerticipantID,PerticipantType,SendDate, ExtendedBody, Remarks, AllowWrite,SMSSendStatus,WebSendStatus,EmailSendStatus)" +
                    " VALUES(%n,%n,%n,%n,%n,%d, %s, %s, %b,%n,%n,%n)", item.ID.Integer, item.BatchNo, item.NotificationID.Integer,
                     item.PerticipantID.Integer, (int)item.PerticipantType, item.SendDate, item.ExtendedBody, item.Remarks, item.AllowWrite, (int)item.SMSSendStatus, (int)item.WebSendStatus, (int)item.EmailSendStatus);
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Update
        internal static void Update(TransactionContext tc, Notification item)
        {
            string sql =
                SQLParser.MakeSQL(
                    "UPDATE Notification SET TagOutputID=%n,ObjectID=%n,OptionalObjectID=%n, Subject=%s, Body=%s, Attachment=%s, Footer=%s,Type=%n,IsMailNf=%b,IsSMSNf=%b,IsWebNf=%b,Status=%n" +
                    " WHERE NotificationID=%n", DataReader.GetNullValue(item.TagOutputID.Integer), DataReader.GetNullValue(item.ObjectID.Integer), DataReader.GetNullValue(item.OptionalObjectID.Integer), item.Subject,
                    item.Body, item.Attachment, item.Footer, (int)item.Type, item.IsMailNf, item.IsSMSNf, item.IsWebNf,
                    (int)item.Status, item.ID.Integer);
            tc.ExecuteNonQuery(sql);
        }

        public static void UpdateNotficationRule(TransactionContext tc, NotificationRule item)
        {
            string sql =
         SQLParser.MakeSQL(
             "UPDATE NotificationRule SET BatchNo=%n,NotificationID=%n,PerticipantID=%n,PerticipantType=%n,SendDate=%d, ExtendedBody=%s, Remarks=%s, AllowWrite=%b,SMSSendStatus=%n,WebSendStatus=%n,EmailSendStatus=%n " +
             "Where NotificationRuleID=%n", item.BatchNo, item.NotificationID.Integer,
              item.PerticipantID.Integer, (int)item.PerticipantType, item.SendDate, item.ExtendedBody, item.Remarks, item.AllowWrite, (int)item.SMSSendStatus, (int)item.WebSendStatus, (int)item.EmailSendStatus, item.ID.Integer);
            tc.ExecuteNonQuery(sql);

        }
        #endregion  
    
    
        internal static void InsertReply(TransactionContext tc, NotificationParticipant oParticipant)
        {
            string sql = SQLParser.MakeSQL("INSERT INTO NotificationParticipant(NotificationParticipantID,NotificationID,EmployeeID,Comments, SentTime, Authorized,Locked, ProcessButton,UndoButton,ApproveButton)" +
            " VALUES(%n,%n,%n,%s,%D,%b,%b,%b,%b,%b)",oParticipant.ID.Integer,oParticipant.NotificationID.Integer,oParticipant.EmployeeID.Integer,oParticipant.Comments,oParticipant.SentTime,true, true,false,false,false);
            tc.ExecuteNonQuery(sql);

        }
    }
}