indicators

USE [D10Task]
GO
/****** Object:  UserDefinedFunction [dbo].[sub_Ticker_getNewTaskCount]    Script Date: 05/29/2015 17:10:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[sub_Ticker_getNewTaskCount]
(
@UserID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT DISTINCT TOP 1000
    t.Description AS 'Текст задачи'
    ,CONVERT(VARCHAR, t.CreatedTask, 104) AS 'Дата создания'
    ,t.TaskID
    ,t.IsOverdue
    ,t.IsEncrypted
    ,t.StateID
    ,t.PriorityID
    ,t.IsClosed
    ,tc.IntColor
    ,t.ParentTaskID
    ,s.FinishWork
    ,s.WorkNotStarted
FROM
    Tasks t WITH(NOLOCK)
    INNER JOIN TaskHelpers th WITH(NOLOCK) ON t.TaskID = th.TaskID AND th.UserID = @UserID AND th.WorkFinished = 0
    LEFT JOIN TaskColor tc WITH(NOLOCK) ON t.TaskID = tc.TaskId AND tc.UserId = @UserID
    INNER JOIN States s WITH(NOLOCK) ON t.StateID = s.StateID
    INNER JOIN Subcategories sc WITH(NOLOCK) ON t.SubcatID = sc.SubcatID
WHERE
    t.StateID = 1
    AND sc.IsDictionary = 0
ORDER BY t.TaskID DESC
)




GO

USE [D10Task]
GO
/****** Object:  UserDefinedFunction [dbo].[sub_Ticker_getTotalTaskCount]    Script Date: 05/29/2015 17:10:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[sub_Ticker_getTotalTaskCount]
(
@UserID INT
)
RETURNS TABLE
AS
RETURN
(

--все задачи
SELECT DISTINCT TOP 1000
    t.Description AS 'Текст задачи'
    ,CASE t.IsWaitingSign
        WHEN 1 THEN 'На подписи - '
        ELSE ''
    END + s.Description
     AS 'Статус'
    ,sc.Description AS 'Категория'
    ,t.OrderedTime AS 'Срок'
    ,t.TaskID
    ,t.IsOverdue
    ,t.IsEncrypted
    ,t.StateID
    ,t.PriorityID
    ,t.IsClosed
    ,tc.IntColor
    ,t.ParentTaskID
    ,s.FinishWork
    ,s.WorkNotStarted
FROM
    Tasks t WITH(NOLOCK)
    INNER JOIN TaskHelpers th WITH(NOLOCK) ON t.TaskID = th.TaskID AND th.UserID = @UserID AND th.WorkFinished = 0
    LEFT JOIN TaskColor tc WITH(NOLOCK) ON t.TaskID = tc.TaskId AND tc.UserId = @UserID
    INNER JOIN States s WITH(NOLOCK) ON t.StateID = s.StateID
    INNER JOIN Subcategories sc WITH(NOLOCK) ON t.SubcatID = sc.SubcatID
WHERE
    t.IsClosed = 0
    AND sc.IsDictionary = 0

ORDER BY t.OrderedTime DESC
)



GO


USE [D10Task]
GO
/****** Object:  UserDefinedFunction [dbo].[sub_Ticker_getTodayTaskCount]    Script Date: 05/29/2015 17:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[sub_Ticker_getTodayTaskCount]
(
@UserID INT
)
RETURNS TABLE
AS
RETURN
(

--задачи в статусе новая
SELECT DISTINCT TOP 100
    t.Description AS 'Текст задачи'
    ,CASE t.IsWaitingSign
        WHEN 1 THEN 'На подписи - '
        ELSE ''
    END + s.Description
     AS 'Статус'
    ,sc.Description AS 'Категория'
    ,t.TaskID
    ,t.IsOverdue
    ,t.IsEncrypted
    ,t.StateID
    ,t.PriorityID
    ,t.IsClosed
    ,tc.IntColor
    ,t.ParentTaskID
    ,s.FinishWork
    ,s.WorkNotStarted
FROM
    Tasks t WITH(NOLOCK)
    INNER JOIN TaskHelpers th WITH(NOLOCK) ON t.TaskID = th.TaskID AND th.UserID = @UserID AND th.WorkFinished = 0
    LEFT JOIN TaskColor tc WITH(NOLOCK) ON t.TaskID = tc.TaskId AND tc.UserId = @UserID
    INNER JOIN States s WITH(NOLOCK) ON t.StateID = s.StateID
    INNER JOIN Subcategories sc WITH(NOLOCK) ON t.SubcatID = sc.SubcatID
WHERE
    t.IsClosed = 0
    AND CAST(t.OrderedTime AS DATE) = CAST(GETDATE() AS DATE)
ORDER BY t.TaskID DESC
)