MobileGetBallotBoxCountBySize

USE [secInventory]
GO
/****** Object:  StoredProcedure [sec].[Mobile_GetBallotBoxCountBySize]    Script Date: 7/22/2025 5:00:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*

EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '000',
    @usercode = 'AdminState',
    @zonecode = '1308000000000',
    @action=1,
    @is_jurisdiction =0;

    EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '000',
    @usercode = 'AdminState',
    @zonecode = '1308001000000',
    @action=1,
    @is_jurisdiction =1;

    EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '001',
    @usercode = 'dpomandi',
    @zonecode = '1308001000000',
    @action=1;


EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '000',
    @usercode = 'AdminState',
    @zonecode = '',
    @action=3;

    EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '000',
    @usercode = 'AdminState',
    @zonecode = '1300000000000',
    @action=2;

    EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '001',
    @usercode = 'dpomandi',
    @zonecode = '1308000000000';

    EXEC sec.Mobile_GetBallotBoxCountBySize 
    @userlevelcode = '002',
    @usercode = 'bdobalh',
    @zonecode = '1308001000000';
*/

ALTER PROCEDURE [sec].[Mobile_GetBallotBoxCountBySize]
    @userlevelcode CHAR(3),
    @usercode CHAR(13),
    @zonecode CHAR(13) = NULL,
    @action INT = 1,  -- 1=Received, 2=In Transit, 3=In Stock, 4=Inward, 5=Outward
    @is_jurisdiction INT=0
AS
BEGIN
    SET NOCOUNT ON;

    -- Resolve actual user code
    SET @usercode = [secInventory].[sec].get_user_code(@usercode);

    -- Determine jurisdiction scope
    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;

    -- Derive effective zone from @zonecode or fallback to @usercode
    DECLARE @zone CHAR(13);
    SET @zone = 
        CASE 
            WHEN @zonecode IS NULL OR LTRIM(RTRIM(@zonecode)) = '' THEN @usercode 
            ELSE @zonecode 
        END;

    -- ACTION 1: Received in jurisdiction
    IF @action = 1
BEGIN
    IF @is_jurisdiction = 1
    BEGIN
        -- Use @zone directly for to_user_code filter
        SELECT  
            @zone AS zonecode,
            CASE S.box_size
                WHEN 'S' THEN 'Small'
                WHEN 'M' THEN 'Medium'
                WHEN 'L' THEN 'Large'
                ELSE 'Un-known'
            END AS box_size,
            COUNT(*) AS total_boxes, @is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        WHERE 
            S.is_received = 1
            AND S.to_user_code = @zone
        GROUP BY S.box_size
        ORDER BY box_size;
    END
    ELSE
    BEGIN
        IF @zonecode IS NOT NULL AND LTRIM(RTRIM(@zonecode)) <> ''
        BEGIN
            DECLARE @tousercode CHAR(13) = @zone;
            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
        END

        SELECT  
            @zone AS zonecode,
            CASE S.box_size
                WHEN 'S' THEN 'Small'
                WHEN 'M' THEN 'Medium'
                WHEN 'L' THEN 'Large'
                ELSE 'Un-known'
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        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
        GROUP BY S.box_size
        ORDER BY box_size;
    END
END




    -- ACTION 2: In Transit
    ELSE IF @action = 2
    BEGIN
        SELECT  
            @zone AS zonecode,
            CASE S.box_size 
                WHEN 'S' THEN 'Small' 
                WHEN 'M' THEN 'Medium' 
                WHEN 'L' THEN 'Large' 
                ELSE 'Un-known' 
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        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 S.from_user_code = @zone
        GROUP BY S.box_size
        ORDER BY box_size;
    END

    -- ACTION 3: In Stock
    ELSE IF @action = 3
BEGIN
    IF @is_jurisdiction = 1
    BEGIN
        SELECT  
            @zone AS zonecode,
            CASE S.box_size
                WHEN 'S' THEN 'Small'
                WHEN 'M' THEN 'Medium'
                WHEN 'L' THEN 'Large'
                ELSE 'Un-known'
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        WHERE 
            S.to_user_code = @zone
            AND S.is_received = 1
        GROUP BY S.box_size
        ORDER BY box_size;
    END
    ELSE
    BEGIN
        SELECT  
            @zone AS zonecode,
            CASE S.box_size
                WHEN 'S' THEN 'Small'
                WHEN 'M' THEN 'Medium'
                WHEN 'L' THEN 'Large'
                ELSE 'Un-known'
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        WHERE 
            S.to_user_code = @usercode
            AND S.is_received = 1
            AND S.to_user_code = @zone
        GROUP BY S.box_size
        ORDER BY box_size;
    END
END


    -- ACTION 4: Inward
    ELSE IF @action = 4
    BEGIN
        SELECT  
            @zone AS zonecode,
            CASE S.box_size 
                WHEN 'S' THEN 'Small' 
                WHEN 'M' THEN 'Medium' 
                WHEN 'L' THEN 'Large' 
                ELSE 'Un-known' 
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        WHERE S.to_user_code = @usercode
            AND S.is_received = 0
            AND S.to_user_code = @zone
        GROUP BY S.box_size
        ORDER BY box_size;
    END

    -- ACTION 5: Outward
    ELSE IF @action = 5
    BEGIN
        SELECT  
            @zone AS zonecode,
            CASE S.box_size 
                WHEN 'S' THEN 'Small' 
                WHEN 'M' THEN 'Medium' 
                WHEN 'L' THEN 'Large' 
                ELSE 'Un-known' 
            END AS box_size,
            COUNT(*) AS total_boxes,@is_jurisdiction as is_jurisdiction
        FROM sec.Ballot_Box_Master_SrNo S WITH (NOLOCK)
        WHERE S.from_user_code = @usercode
            AND S.is_received = 0
            AND S.from_user_code = @zone
        GROUP BY S.box_size
        ORDER BY box_size;
    END
END;