If (filterFR = "Unsubmitted") Then
            strFR = " AND FRP.Status='Saved' "
        ElseIf (filterFR = "Submitted") Then
            strFR = " AND FRP.Status='Submitted' "
        ElseIf (filterFR = "TS") Then
            strFR = " AND FRP.Status='TS' "
        ElseIf (filterFR = "NotPrinted") Then
            strFR = " AND WO.PrintStatus IN(0,1) "
        ElseIf (filterFR = "Printed") Then
            strFR = " AND WO.PrintStatus=2 "
        ElseIf (filterProject <> "") Then
            strFR = " AND Pr.WBS1 Like '" & filterProject & "%'"
        Else
            strFR = ""
        End If

        strFR = strFR & " AND EXISTS (SELECT 1 FROM Pr PR2 WHERE Pr.WBS1=PR2.WBS1 COLLATE sql_latin1_general_cp1_ci_as " & _
                        " AND Pr.WBS2=PR2.WBS2 COLLATE sql_latin1_general_cp1_ci_as AND Pr.WBS3=PR2.WBS3 COLLATE sql_latin1_general_cp1_ci_as AND UnitTable IS NOT NULL AND (Org " & _
                        " LIKE '%" & filterOffice & "%' OR '" & filterOffice & "'='All'))"

        Try
            dtblLEMList.Dispose()
            dtblLEMList = New DataTable()

            cmdFR.Connection = connSqlFr

            cmdFR.CommandText = " SELECT null PRID, TransDate RepDate, WBS1 + CASE WHEN WBS2 ='' THEN '' ELSE ' - ' + WBS2 END + CASE WHEN WBS3 ='' THEN '' ELSE ' - ' + WBS3 END Project, 'Office Work' Employee, 'Office Work' Name, CONVERT(DECIMAL(5,2), SUM(RegHrs)) Hours, null Uname, null StartTime, CASE WHEN LineItemApprovalStatus='A' THEN 'Approved' ELSE 'Unapproved' END Status " & _
                                " FROM tkdetail tk WHERE TransDate >= @startDate AND TransDate <= @stopDate AND TransDate<=GETDATE()" & _
                                " AND EXISTS (SELECT 1 FROM PR WHERE PR.WBS1=tk.WBS1 AND PR.WBS2=tk.WBS2 AND PR.WBS3=tk.WBS3 AND UnitTable IS NOT NULL AND (Org LIKE '%" & filterOffice & "%' OR '" & filterOffice & "'='All')) AND tk.WBS1 like '%" & filterProject & "%' " & _
                                " GROUP BY TransDate, WBS1 + CASE WHEN WBS2 ='' THEN '' ELSE ' - ' + WBS2 END + CASE WHEN WBS3 ='' THEN '' ELSE ' - ' + WBS3 END, LineItemApprovalStatus ORDER BY Repdate DESC, 3"

            cmdFR.Parameters.Add("@startDate", SqlDbType.DateTime).Value = startDate
            cmdFR.Parameters.Add("@stopDate", SqlDbType.DateTime).Value = stopDate
            cmdFR.Parameters.Add("@PCCode", SqlDbType.VarChar).Value = filterPC

            da = New SqlDataAdapter(cmdFR)
            da.Fill(dtblLEMList)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using AppDb;
using SspMain;

namespace AppLogic
{
    public class MessageTaskIndex
    {
        public int? ID;

        public string ProjectStr;

        public string Subject;

        public MessageMember Sender;

        public MessageMember Recipient;

        public DateTime? ActivityDate;

        public DateTime? PlanCompleteDate;

        public MessageState CurrentState;

        public int CommentCount;

        public bool CanChangeMessage;

        public bool MustReadMessage;

        public bool IsTaskNotReaded;

        public bool IsCommentNotReaded;

        public bool IsPlanCompleteDateExpired;
    }

    public class MessageTaskIndexFilter : PaginalFilter
    {
        public int FilterType; // 0 - Текущие задачи, 1 - Избранное, 2 - Поиск по коду задачи, 3 - Расширенный фильтр

        #region Текущие задачи

        public int? F0_Direction;

        public int? F0_Category;

        public int? F0_ProjectID;

        #endregion

        #region Избранное

        public int? F1_FavID;

        #endregion

        #region Поиск по коду задачи

        public int? F2_TaskID;

        #endregion

        #region Расширенный фильтр

        public DateTime? F3_T1;

        public DateTime? F3_T2;

        public int? F3_ProjectID;

        public int? F3_SenderPersID;

        public int? F3_RecipientPersID;

        public string F3_Subject;

        public int[] F3_StateID;

        #endregion
    }

    public class MessageTaskEdit : IMessageAttachmentChangesOwner
    {
        public int? ID;

        public byte[] TaskTimeS;

        public byte[] MessageTimeS;

        public int? ProjectID;

        public int? RecipientPersID;

        public DateTime? PlanCompleteDate;

        public string Subject;

        public string ContentText;

        public MessageAttachmentChanges Attachments { get; set; }
    }

    public class MessageTaskDetail
    {
        // Задача

        public int? ID;

        public string Subject;

        public string ProjectStr;

        public bool CanChangeMessage;

        // Постановка задачи        

        public MessageMember Sender;

        public MessageMember SenderShort;

        public DateTime? CreateDate;

        public DateTime? LastChangeDate;

        public MessageMember LastChangePers;

        // Выполнение

        public MessageMember Recipient;

        public MessageMember RecipientShort;

        public DateTime? RecipientReadDate;

        public bool IsNotReaded;

        public DateTime? PlanCompleteDate;

        public bool IsPlanCompleteDateExpired;

        public MessageState CurrentState;

        public DateTime? CurrentStateDate;

        public DateTime? ActivityDate;

        // Задание

        public string ContentText;
    }

    public class MessageTaskService : DbService,
        IIndexRepository<PaginalResult<MessageTaskIndex>, MessageTaskIndexFilter>,
        IExcelTableRepository<IEnumerable<MessageTaskIndex>, MessageTaskIndexFilter>,
        IEditRepository<MessageTaskEdit, int>
    {
        public CurrentUserService CurrentUserService { get; set; }

        public DbDateTimeService DbDateTimeService { get; set; }

        public MessageCoreService MessageCoreService { get; set; }

        public MessageAttachmentsService MessageAttachmentsService { get; set; }

        #region Index Core

        IQueryable<MessageTaskIndex> GetMessageTaskIndex(IQueryable<message_task> source)
        {
            var currentUserPersID = CurrentUserService.CurrentUserPersID;

            return
                from mt in source

                let m = mt.ID_Reference
                let sender = m.SenderPersID_Reference
                let recipient = m.RecipientPersID_Reference
                let currentStateID = MessageTaskCoreService.GetCurrentStateID.Invoke(mt)

                join currentState in Db.message_taskStates on currentStateID equals currentState.ID

                select new MessageTaskIndex
                {
                    ID = mt.ID,
                    ProjectStr = mt.ProjectID_Reference.Caption,
                    Subject = mt.Subject,
                    Sender = MessageCoreService.GetMessageMemberShort.Invoke(sender, currentUserPersID),
                    Recipient = MessageCoreService.GetMessageMemberShort.Invoke(recipient, currentUserPersID),
                    ActivityDate = MessageTaskCoreService.GetActivityDate.Invoke(mt),
                    PlanCompleteDate = mt.PlanCompleteDate,

                    CurrentState = new MessageState
                    {
                        Caption = currentState.Caption,
                        IsActive = MessageTaskCoreService.GetIsActive.Invoke(mt, currentState)
                    },

                    CommentCount = mt.message_comments_TaskID_Reference.Count(),
                    CanChangeMessage = MessageCoreService.GetCanChangeMessage.Invoke(m, currentUserPersID),
                    MustReadMessage = MessageCoreService.GetMustReadMessage.Invoke(m, currentUserPersID) ?? false,
                    IsTaskNotReaded = MessageCoreService.GetIsNotReadedMessage.Invoke(m, currentUserPersID) ?? false,

                    IsCommentNotReaded =
                        (
                            from mc in mt.message_comments_TaskID_Reference
                            let mcm = mc.ID_Reference
                            where MessageCoreService.GetIsNotReadedMessage.Invoke(mcm, currentUserPersID).Value
                            select mc
                        ).Any(),

                    IsPlanCompleteDateExpired =
                        MessageTaskCoreService.GetIsPlanCompleteDateExpired.Invoke(mt, currentState)
                };
        }

        IQueryable<message_task> GetCurrentUserTasks(Expression<Func<message, bool>> messageFilter)
        {
            var currentUserPersID = CurrentUserService.CurrentUserPersID;

            var currentUserMessages =
                Db.message.Where(m => m.RecipientPersID == currentUserPersID).Where(messageFilter);

            currentUserMessages = currentUserMessages.Concat(
                Db.message.Where(m => m.SenderPersID == currentUserPersID).Where(messageFilter)
            );

            // Мои задачи или задачи моих комментариев
            var currentUserTasksIds =
                (
                    from m in currentUserMessages
                    let c = m.message_comments_ID_Reference
                    select c.TaskID ?? m.ID
                ).Distinct();

            return
                from taskID in currentUserTasksIds
                join mt in Db.message_task on taskID equals mt.ID
                select mt;
        }

        IQueryable<MessageTaskIndex> GetMessageTaskIndexByEfficientFilter
        (
            int direction, // 0 - Все, 1 - Входящие, 2 - Исходящие
            int category, // 1 - Непрочитанные, 2 - Активные, 3 - Недавние
            int? projectID
        )
        {
            if (direction < 0 || direction > 2)
                throw new ArgumentException("direction");

            var currentUserPersID = CurrentUserService.CurrentUserPersID;
            var now = DbDateTimeService.Now;
            IQueryable<message_task> q;

            if (category == 1) // Непрочитанные
            {
                q = GetCurrentUserTasks(m => MessageCoreService.GetIsNotReadedMessage.Invoke(m, currentUserPersID).Value);
            }
            else if (category == 2) // Активные
            {
                var protectDateFrom = now.AddDays(-365);

                q =
                    from mt in GetCurrentUserTasks(m => m.CreateDate >= protectDateFrom && m.CreateDate <= now)
                    let m = mt.ID_Reference

                    // Исключая задачи от моих комментариев
                    where m.RecipientPersID == currentUserPersID || m.SenderPersID == currentUserPersID

                    let currentStateID = MessageTaskCoreService.GetCurrentStateID.Invoke(mt)
                    join currentState in Db.message_taskStates on currentStateID equals currentState.ID
                    where MessageTaskCoreService.GetIsActive.Invoke(mt, currentState)
                    select mt;
            }
            else if (category == 3) // Недавние
            {
                var recentDateFrom = now.AddDays(-30);
                q = GetCurrentUserTasks(m => m.CreateDate >= recentDateFrom && m.CreateDate <= now);
            }
            else
                throw new ArgumentException("category");

            if (direction == 1) // Входящие
                q = q.Where(mt => mt.ID_Reference.RecipientPersID == currentUserPersID);
            else if (direction == 2) // Исходящие
                q = q.Where(mt => mt.ID_Reference.SenderPersID == currentUserPersID);

            if (projectID.HasValue)
                q = q.Where(mt => mt.ProjectID == projectID);

            return GetMessageTaskIndex(q);
        }

        IQueryable<MessageTaskIndex> GetMessageTasIndexByFavID(int favID)
        {
            var q =
                from mt in Db.message_task
                from mtf in mt.message_taskToFavorite_TaskID_Reference
                where mtf.FavID == favID
                select mt;

            return GetMessageTaskIndex(q);
        }

        IQueryable<MessageTaskIndex> GetMessageTaskIndexByID(int id)
        {
            var q = Db.message_task.Where(mt => mt.ID == id);
            return GetMessageTaskIndex(q);
        }

        IQueryable<MessageTaskIndex> GetMessageTaskIndexByExtendedFilter
        (
            DateTime t1, DateTime t2, int? projectID,
            int? senderPersID, int? recipientPersID, string subject,
            int[] stateID
        )
        {
            t1 = t1.Date;
            t2 = t2.Date.AddDays(1);
            stateID = stateID ?? new int[0];

            var q =
                from mt in Db.message_task
                let m = mt.ID_Reference
                let currentStateID = MessageTaskCoreService.GetCurrentStateID.Invoke(mt)
                where m.CreateDate >= t1 && m.CreateDate < t2 && stateID.Contains(currentStateID.Value)
                select new { m, mt };

            if (senderPersID.HasValue)
                q = q.Where(s => s.m.SenderPersID == senderPersID);

            if (recipientPersID.HasValue)
                q = q.Where(s => s.m.RecipientPersID == recipientPersID);

            if (string.IsNullOrWhiteSpace(subject) == false)
                q = q.Where(s => s.mt.Subject.Contains(subject));

            if (projectID.HasValue)
                q = q.Where(s => s.mt.ProjectID == projectID.Value);

            return GetMessageTaskIndex(q.Select(s => s.mt));
        }

        IQueryable<MessageTaskIndex> GetIndexByFilterInternal(MessageTaskIndexFilter filter)
        {
            IQueryable<MessageTaskIndex> q = null;

            if (filter.FilterType == 0)
            {
                if (filter.F0_Direction.HasValue && filter.F0_Category.HasValue)
                    q = GetMessageTaskIndexByEfficientFilter(
                        filter.F0_Direction.Value,
                        filter.F0_Category.Value,
                        filter.F0_ProjectID
                    );
            }
            else if (filter.FilterType == 1)
            {
                if (filter.F1_FavID.HasValue)
                    q = GetMessageTasIndexByFavID(filter.F1_FavID.Value);
            }
            else if (filter.FilterType == 2)
            {
                if (filter.F2_TaskID.HasValue)
                    q = GetMessageTaskIndexByID(filter.F2_TaskID.Value);
            }
            else if (filter.FilterType == 3)
            {
                if (filter.F3_T1.HasValue && filter.F3_T2.HasValue)
                    q = GetMessageTaskIndexByExtendedFilter(
                        filter.F3_T1.Value, filter.F3_T2.Value, filter.F3_ProjectID,
                        filter.F3_SenderPersID, filter.F3_RecipientPersID, filter.F3_Subject,
                        filter.F3_StateID
                    );
            }
            else
                throw new ArgumentException("filterType");

            if (q == null)
                q = GetMessageTaskIndex(Db.message_task.Take(0));

            return q;
        }

        #endregion

        #region IIndexRepository<PaginalResult<MessageTaskIndex>,MessageTaskIndexFilter> Members

        public PaginalResult<MessageTaskIndex> GetIndexByFilter(MessageTaskIndexFilter filter)
        {
            var q = GetIndexByFilterInternal(filter);

            if (filter.FilterType == 3) // Расширенный фильтр
                return q.ToPaginal(filter);

            return q.ToArray().ToPaginal(filter);
        }

        #endregion

        #region IExcelTableRepository<IEnumerable<MessageTaskIndex>,MessageTaskIndexFilter> Members

        public IEnumerable<MessageTaskIndex> GetExcelDataByFilter(MessageTaskIndexFilter filter)
        {
            return GetIndexByFilterInternal(filter).OrderBy(filter);
        }

        #endregion

        #region IEditRepository<MessageTaskEdit,int> Members

        public MessageTaskEdit GetEditNew()
        {
            return new MessageTaskEdit();
        }

        public MessageTaskEdit GetEditByID(int id)
        {
            var q =
                from mt in Db.message_task
                where mt.ID == id
                let m = mt.ID_Reference
                select new MessageTaskEdit
                {
                    ID = mt.ID,
                    TaskTimeS = mt.TimeS,
                    MessageTimeS = m.TimeS,
                    ProjectID = mt.ProjectID,
                    RecipientPersID = m.RecipientPersID,
                    PlanCompleteDate = mt.PlanCompleteDate,
                    Subject = mt.Subject,
                    ContentText = m.ContentText
                };

            return q.First();
        }

        public int SaveEdit(MessageTaskEdit data, UpdateAction action)
        {
            message message;
            message_task message_task;

            if (action == UpdateAction.Insert)
            {
                message = MessageCoreService.CreateMessage();
                Db.AttachAsAdded(message);
                message.ID = -1;

                message_task = new message_task();
                Db.AttachAsAdded(message_task);
                message_task.ID = message.ID;
            }
            else
            {
                MessageCoreService.ValidateCanChangeMessageByID(data.ID.Value);

                message = Db.message.Where(m => m.ID == data.ID).First();
                message_task = Db.message_task.Where(mt => mt.ID == data.ID).First();

                var messageTimeS = Db.Entry(message).Property(v => v.TimeS);
                messageTimeS.OriginalValue = data.MessageTimeS;
                messageTimeS.CurrentValue = data.MessageTimeS;

                var taskTimeS = Db.Entry(message_task).Property(v => v.TimeS);
                taskTimeS.OriginalValue = data.TaskTimeS;
                taskTimeS.CurrentValue = data.TaskTimeS;

                MessageCoreService.SetMessageChanged(message);
            }

            message.RecipientPersID = data.RecipientPersID;
            message.ContentText = data.ContentText;
            message_task.ProjectID = data.ProjectID;
            message_task.PlanCompleteDate = data.PlanCompleteDate;
            message_task.Subject = data.Subject;

            if (data.Attachments != null)
            {
                data.Attachments.MsgID = message.ID;
                MessageAttachmentsService.Attach(data.Attachments);
            }

            Db.SaveChanges();
            return message.ID.Value;
        }

        public void DeleteByID(int id)
        {
            using (new EntityNoDetectChangesScope(Db))
            {
                MessageCoreService.ValidateCanChangeMessageByID(id);

                var qDeleteData =
                    from mt in Db.message_task
                    where mt.ID == id
                    select new
                    {
                        Task = mt,
                        TaskMessage = mt.ID_Reference,
                        Comments =
                            from comment in mt.message_comments_TaskID_Reference
                            select new
                            {
                                Comment = comment,
                                CommentMessage = comment.ID_Reference
                            },
                        Favorites = mt.message_taskToFavorite_TaskID_Reference
                    };

                var deleteData = qDeleteData.First();

                Db.AttachManyAsDeleted(deleteData.Comments.Select(d => d.Comment));
                Db.AttachManyAsDeleted(deleteData.Comments.Select(d => d.CommentMessage));
                Db.AttachManyAsDeleted(deleteData.Favorites);
                Db.AttachAsDeleted(deleteData.Task);
                Db.AttachAsDeleted(deleteData.TaskMessage);

                Db.SaveChanges();
            }
        }

        #endregion

        #region Detail

        public MessageTaskDetail GetDetailByID(int id)
        {
            var currentUserPersID = CurrentUserService.CurrentUserPersID;

            var q =
                from mt in Db.message_task
                where mt.ID == id

                let m = mt.ID_Reference
                let sender = m.SenderPersID_Reference
                let recipient = m.RecipientPersID_Reference
                let lastChangePers = m.LastChangePersID_Reference
                let currentStateID = MessageTaskCoreService.GetCurrentStateID.Invoke(mt)

                join currentState in Db.message_taskStates on currentStateID equals currentState.ID

                select new MessageTaskDetail
                {
                    ID = mt.ID,
                    Subject = mt.Subject,
                    ProjectStr = mt.ProjectID_Reference.Caption,
                    CanChangeMessage = MessageCoreService.GetCanChangeMessage.Invoke(m, currentUserPersID),

                    Sender = MessageCoreService.GetMessageMemberFull.Invoke(sender, currentUserPersID),
                    SenderShort = MessageCoreService.GetMessageMemberShort.Invoke(sender, currentUserPersID),
                    CreateDate = m.CreateDate,
                    LastChangeDate = m.LastChangeDate,
                    LastChangePers = MessageCoreService.GetMessageMemberFull.Invoke(lastChangePers, currentUserPersID),

                    Recipient = MessageCoreService.GetMessageMemberFull.Invoke(recipient, currentUserPersID),
                    RecipientShort = MessageCoreService.GetMessageMemberShort.Invoke(recipient, currentUserPersID),
                    RecipientReadDate = m.RecipientReadDate,
                    IsNotReaded = MessageCoreService.GetIsNotReadedMessage.Invoke(m, currentUserPersID) ?? false,
                    PlanCompleteDate = mt.PlanCompleteDate,
                    IsPlanCompleteDateExpired = MessageTaskCoreService.GetIsPlanCompleteDateExpired.Invoke(mt, currentState),

                    CurrentState = new MessageState
                    {
                        Caption = currentState.Caption,
                        IsActive = MessageTaskCoreService.GetIsActive.Invoke(mt, currentState)
                    },

                    CurrentStateDate = MessageTaskCoreService.GetCurrentStateDate.Invoke(mt),
                    ActivityDate = MessageTaskCoreService.GetActivityDate.Invoke(mt),

                    ContentText = m.ContentText
                };

            return q.First();
        }

        #endregion
    }
}