USE [WebWISH]
GO
/****** Object: StoredProcedure [dbo].[PR_TotalQtyCount] Script Date: 7/14/22 7:26:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[PR_TotalQtyCount]
(
@yrbegin as DATE,
@yrend AS DATE,
@periodbegin AS DATE,
@periodend AS DATE,
@source AS INT,
@orguuid AS VARCHAR(36),
@counter AS VARCHAR(100),
@type AS VARCHAR(100),
@field AS VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
DROP TABLE IF EXISTS #progstats
SELECT PROG as Code, (COUNT(PROG)) as counted
INTO #progstats
FROM CLIPROGS
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = PROG THEN 1
ELSE 0
END
END
GROUP BY PROG
UNION ALL
SELECT PROG as Code, (COUNT(PROG)) as counted
FROM ORPROGS
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = PROG THEN 1
ELSE 0
END
END
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
GROUP BY PROG
UNION ALL
SELECT A.[CODE] as Code,
(SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'S'
AND [TEXT] LIKE CONCAT('%;',TRIM(A.[CODE]),'^%')
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C') --'E' FOR CHILDREN
as counted
FROM PROGS AS A
WHERE ORGUUID = @orguuid
AND
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = A.CODE THEN 1
ELSE 0
END
END
AND (SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'S'
AND [TEXT] LIKE CONCAT('%;',TRIM(A.[CODE]),'^%')
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C' ) --'E' FOR CHILDREN
>0
GROUP BY A.[CODE]
UNION ALL
SELECT A.[CODE] as Code,
(SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'M'
--AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
AND LEFT([TEXT],6) = A.[CODE]
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C') --'E' FOR CHILDREN
as counted
FROM PROGS AS A
WHERE ORGUUID = @orguuid
AND
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = A.CODE THEN 1
ELSE 0
END
END
AND (SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'M'
--AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
AND LEFT([TEXT],6) = A.[CODE]
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C' ) --'E' FOR CHILDREN
>0
GROUP BY A.[CODE]
;
SELECT Code, SUM(counted) FROM #progstats
WHERE
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = [Code] THEN 1
ELSE 0
END
END
GROUP BY Code
ORDER BY 1
;
END
GO