MobileGetBlocksfordistrictparked

USE [secInventory]
GO
/****** Object:  StoredProcedure [sec].[Mobile_GetBlocks_fordistrict_parked]    Script Date: 7/22/2025 4:58:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
    exec [sec].[Mobile_GetBlocks_fordistrict_parked] '000','1308000000000', 1
    exec [sec].[Mobile_GetBlocks_fordistrict_parked] '001','1308001000000', 1
    Select * from sec.Ballot_Box_Master_SrNo
*/

ALTER PROCEDURE [sec].[Mobile_GetBlocks_fordistrict_parked]
    @userlevelcode CHAR(5),
    @usercode CHAR(13),
    @action INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @JurisdictionLeftLength INT;
    SET @usercode = [secInventory].[sec].get_user_code(@usercode); -- Ensure @usercode is processed as needed

    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;

    -- ACTION 1: RECEIVED
    IF @action = 1
    BEGIN
        IF @userlevelcode IN ('000', '012', '013') -- State-level (showing Blocks, filtered by District for the user)
        BEGIN
            SELECT
                CASE
                    -- If S.to_user_code exactly matches @usercode, then it's the Warehouse
                    WHEN S.to_user_code = @usercode THEN 'Warehouse'
                    ELSE b.Block_Name
                END AS ddname,
                -- For 'Warehouse', the 'code' will be @usercode. For blocks, it's their 7-char prefix + '0000000'.
                CAST(LEFT(S.to_user_code, 7) + '0000000' AS CHAR(13)) AS code,
                COUNT(*) AS total_boxes,
                '1' is_jurisdiction
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.sec.Blocks b ON b.Block_Code = LEFT(S.to_user_code, 7)
            WHERE
                S.is_received = 1
                AND (
                    -- Condition to include regular blocks: within the user's district AND not the exact user code
                    -- AND ensures it's not an aggregate code like '...0000000' unless it's @usercode itself.
                    (LEFT(S.to_user_code, 4) = LEFT(@usercode, 4) AND S.to_user_code <> @usercode AND RIGHT(S.to_user_code, 7) <> '0000000')
                    OR
                    -- Condition to explicitly include the 'Warehouse' entry, where S.to_user_code exactly matches @usercode
                    (S.to_user_code = @usercode)
                )
            GROUP BY
                CASE
                    WHEN S.to_user_code = @usercode THEN 'Warehouse'
                    ELSE b.Block_Name
                END,
                CAST(LEFT(S.to_user_code, 7) + '0000000' AS CHAR(13))
            ORDER BY ddname;
        END
        ELSE IF @userlevelcode = '001' -- District-level (showing Panchayats, filtered by Block for the user)
        BEGIN
            SELECT
                CASE
                    -- If S.to_user_code exactly matches @usercode, then it's the Warehouse
                    WHEN S.to_user_code = @usercode THEN 'Warehouse'
                    ELSE p.Panchayat_Name
                END AS ddname,
                -- For 'Warehouse', the 'code' will be @usercode. For panchayats, it's their 10-char prefix + '000'.
                CAST(LEFT(S.to_user_code, 10) + '000' AS CHAR(13)) AS code,
                COUNT(*) AS total_boxes,
                '1' is_jurisdiction
            FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
            LEFT JOIN sec.sec.Panchayats p ON p.Panchayat_Code = LEFT(S.to_user_code, 10)
            WHERE
                S.is_received = 1
                AND (
                    -- Condition to include regular panchayats: within the user's block AND not the exact user code
                    -- AND ensures it's not an aggregate code like '...000000' unless it's @usercode itself.
                    (LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) AND S.to_user_code <> @usercode AND RIGHT(S.to_user_code, 6) <> '000000')
                    OR
                    -- Condition to explicitly include the 'Warehouse' entry, where S.to_user_code exactly matches @usercode
                    (S.to_user_code = @usercode)
                )
            GROUP BY
                CASE
                    WHEN S.to_user_code = @usercode THEN 'Warehouse'
                    ELSE p.Panchayat_Name
                END,
                CAST(LEFT(S.to_user_code, 10) + '000' AS CHAR(13))
            ORDER BY ddname;
        END
    END
END;