MobileBallotBoxGetPagedItems

USE [secInventory]
GO
/****** Object:  StoredProcedure [dbo].[Mobile_BallotBox_GetPagedItems]    Script Date: 7/22/2025 5:02:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec Mobile_BallotBox_GetPagedItems 1,20,'000','adminstate',1,'130800000000000','L',0
exec Mobile_BallotBox_GetPagedItems 1,20,'000','adminstate',1,'130800100000000','L',1
exec Mobile_BallotBox_GetPagedItems 1,20,'000','adminstate',1,'130800000000000','L'
exec Mobile_BallotBox_GetPagedItems 1,20,'001','dpomandi',1,'130800000000000','M'
exec Mobile_BallotBox_GetPagedItems 1,20,'002','bdobalh',1,'130800100000000','L'
exec Mobile_BallotBox_GetPagedItems 1,20,'000','adminstate',5,'130000000000000','L',0
*/

ALTER PROCEDURE [dbo].[Mobile_BallotBox_GetPagedItems]
    @PageNumber INT,
    @PageSize INT,
    @userlevelcode VARCHAR(5),
    @usercode CHAR(13),
    @action INT = 0,                  -- 0 for all, 1-5 for specific categories
    @zonecode CHAR(13) = NULL,       -- Optional filter
    @box_size CHAR(1) = NULL,        -- Optional filter ('S', 'M', 'L')
    @is_jurisdiction INT
AS
BEGIN
    SET NOCOUNT ON;

    SET @usercode = [secInventory].[sec].get_user_code(@usercode);

    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize < 1 SET @PageSize = 10;

    DECLARE @JurisdictionLeftLength INT;
    SET @JurisdictionLeftLength =
        CASE
            WHEN @userlevelcode IN ('000', '012', '013') THEN 2
            WHEN @userlevelcode = '001' THEN 4
            WHEN @userlevelcode IN ('002', '003') THEN 7
            WHEN @userlevelcode = '999' THEN 100
            ELSE 0
        END;

    DECLARE @zonecodeFilter CHAR(13) = ISNULL(@zonecode, '');
    DECLARE @boxsizeFilter CHAR(1) = ISNULL(@box_size, '');

    -- ACTION 1: Received in jurisdiction
    IF @action = 0 OR @action = 1
    BEGIN
        IF @is_jurisdiction = 1
        BEGIN
            SELECT
                S.qr_text,
                CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
                S.entry_date,
                ls.Name AS senderlocation,
                lr.Name AS receiverlocation,
                S.Problem_remarks,
                S.latitude,
                S.longitude
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
            LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
            LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
            WHERE
                S.is_received = 1
                AND S.to_user_code = @zonecode
                AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
            ORDER BY S.sr_no ASC
            OFFSET (@PageNumber - 1) * @PageSize ROWS
            FETCH NEXT @PageSize ROWS ONLY;
        END
        ELSE
        BEGIN
            DECLARE @tousercode CHAR(13) = @zonecode;
            DECLARE @ToUserCodeComparisonLength INT;

            IF @userlevelcode = '000'
            BEGIN
                SET @tousercode = LEFT(@tousercode, 4);
                SET @ToUserCodeComparisonLength = 4;
            END
            ELSE IF @userlevelcode = '001'
            BEGIN
                SET @tousercode = LEFT(@tousercode, 7);
                SET @ToUserCodeComparisonLength = 7;
            END
            ELSE IF @userlevelcode = '002'
            BEGIN
                SET @tousercode = LEFT(@tousercode, 10);
                SET @ToUserCodeComparisonLength = 10;
            END
            ELSE IF @userlevelcode = '999'
            BEGIN
                SET @tousercode = LEFT(@tousercode, 13);
                SET @ToUserCodeComparisonLength = 13;
            END

            SELECT
                S.qr_text,
                CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
                S.entry_date,
                ls.Name AS senderlocation,
                lr.Name AS receiverlocation,
                S.Problem_remarks,
                S.latitude,
                S.longitude
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
            LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
            LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
            WHERE
                (
                    (@JurisdictionLeftLength > 0 AND LEFT(S.to_user_code, @JurisdictionLeftLength) = LEFT(@usercode, @JurisdictionLeftLength)) OR
                    (@userlevelcode = '999' AND S.to_user_code = @usercode)
                )
                AND S.is_received = 1
                AND LEFT(S.to_user_code, @ToUserCodeComparisonLength) = @tousercode
                AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
            ORDER BY S.sr_no ASC
            OFFSET (@PageNumber - 1) * @PageSize ROWS
            FETCH NEXT @PageSize ROWS ONLY;
        END
    END

    -- ACTION 2: In Transit
    IF @action = 0 OR @action = 2
    BEGIN
        SELECT
            S.qr_text,
            CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
            S.entry_date,
            ls.Name AS senderlocation,
             CASE WHEN lr.Name IS NOT NULL THEN lr.Name ELSE st.State_Name END AS receiverlocation,
            S.Problem_remarks,
            S.latitude,
            S.longitude
        FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
        LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
        LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
        LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
        LEFT JOIN sec.sec.states AS st ON LEFT(S.to_user_code, 2) = st.State_Code 
        WHERE
            (
                (@JurisdictionLeftLength > 0 AND LEFT(S.from_user_code, @JurisdictionLeftLength) = LEFT(@usercode, @JurisdictionLeftLength)) OR
                (@userlevelcode = '999' AND S.from_user_code = @usercode)
            )
            AND S.is_received = 0
            AND (@zonecode IS NULL OR @zonecode = '' OR S.from_user_code = @zonecode)
            AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
        ORDER BY S.entry_date DESC
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH NEXT @PageSize ROWS ONLY;
    END

    -- ACTION 3: In Stock
    IF @action = 0 OR @action = 3
    BEGIN
        IF @is_jurisdiction = 1
        BEGIN
            SELECT
                S.qr_text,
                CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
                S.entry_date,
                ls.Name AS senderlocation,
                lr.Name AS receiverlocation,
                S.Problem_remarks,
                S.latitude,
                S.longitude
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
            LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
            LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
            WHERE
                S.to_user_code = @zonecode
                AND S.is_received = 1
                AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
            ORDER BY S.entry_date DESC
            OFFSET (@PageNumber - 1) * @PageSize ROWS
            FETCH NEXT @PageSize ROWS ONLY;
        END
        ELSE
        BEGIN
            SELECT
                S.qr_text,
                CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
                S.entry_date,
                ls.Name AS senderlocation,
                lr.Name AS receiverlocation,
                S.Problem_remarks,
                S.latitude,
                S.longitude
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
            LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
            LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
            WHERE
                S.to_user_code = @usercode
                AND S.is_received = 1
                AND (@zonecode IS NULL OR @zonecode = '' OR S.to_user_code = @zonecode)
                AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
            ORDER BY S.entry_date DESC
            OFFSET (@PageNumber - 1) * @PageSize ROWS
            FETCH NEXT @PageSize ROWS ONLY;
        END
    END

    -- ACTION 4: Inward
    IF @action = 0 OR @action = 4
    BEGIN
        SELECT
            S.qr_text,
            CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
            S.entry_date,
            ls.Name AS senderlocation,
            lr.Name AS receiverlocation,
            S.Problem_remarks,
            S.latitude,
            S.longitude
        FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
        LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
        LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
        LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
        WHERE
            S.to_user_code = @usercode
            AND S.is_received = 0
            AND (@zonecode IS NULL OR @zonecode = '' OR S.to_user_code = @zonecode)
            AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
        ORDER BY S.entry_date DESC
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH NEXT @PageSize ROWS ONLY;
    END

    -- ACTION 5: Outward
    IF @action = 0 OR @action = 5
    BEGIN
        SELECT
            S.qr_text,
            CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,
            S.entry_date,
            ls.Name AS senderlocation,
            --lr.Name AS receiverlocation,
            CASE WHEN lr.Name IS NOT NULL THEN lr.Name ELSE st.State_Name END AS receiverlocation,
            S.Problem_remarks,
            S.latitude,
            S.longitude
        FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
        LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
        LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
        LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code
        LEFT JOIN sec.sec.states AS st ON LEFT(S.to_user_code, 2) = st.State_Code 
        WHERE
            S.from_user_code = @usercode
            AND S.is_received = 0
            AND (@zonecode IS NULL OR @zonecode = '' OR S.from_user_code = @zonecode)
            AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
        ORDER BY S.entry_date DESC
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH NEXT @PageSize ROWS ONLY;
    END


--    -- ACTION 4: Inward
--    IF @action = 0 OR @action = 4
--    BEGIN
--  select
--         S.qr_text,            
--            CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,            

--            S.entry_date,
--            ls.Name AS senderlocation,
--            lr.Name AS receiverlocation,                  
--            S.Problem_remarks,
--            S.latitude,
--            S.longitude
--        FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
--        LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
--        LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
--        LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code

--        WHERE 
--            S.to_user_code = @usercode 
--            AND S.is_received = 0
--          AND (@zonecode IS NULL OR @zonecode = '' OR S.to_user_code = @zonecode)
--AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
--        ORDER BY S.entry_date DESC
--        OFFSET (@PageNumber - 1) * @PageSize ROWS
--        FETCH NEXT @PageSize ROWS ONLY;
--    END

--    -- ACTION 5: Outward
--    IF @action = 0 OR @action = 5
--    BEGIN
--        SELECT
--             S.qr_text,            
--            CASE M.box_size WHEN 'S' THEN 'Small' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Large' ELSE 'Un-known' END AS box_size,            

--            S.entry_date,
--            ls.Name AS senderlocation,
--            lr.Name AS receiverlocation,                  
--            S.Problem_remarks,
--            S.latitude,
--            S.longitude
--        FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
--        LEFT JOIN sec.Ballot_Box_Master AS M WITH (NOLOCK) ON M.qrtext = S.qr_text
--        LEFT JOIN [sec].view_EvmLocation AS ls ON S.from_user_code = ls.code
--        LEFT JOIN [sec].view_EvmLocation AS lr ON S.to_user_code = lr.code

--        WHERE 
--            S.from_user_code = @usercode 
--          AND S.is_received = 0
--          AND (@zonecode IS NULL OR @zonecode = '' OR S.from_user_code = @zonecode)
--          AND (@box_size IS NULL OR @box_size = '' OR M.box_size = @box_size)
--        ORDER BY S.entry_date DESC
--        OFFSET (@PageNumber - 1) * @PageSize ROWS
--        FETCH NEXT @PageSize ROWS ONLY;
--    END
END;