MobileGetBallotBoxwhdisblklist
USE [secInventory]
GO
/****** Object: StoredProcedure [sec].[Mobile_GetBallotBox_wh_dis_blk_list] Script Date: 7/22/2025 4:57:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '000',
@usercode = 'AdminState',
@actioncode=1;
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '001',
@usercode = 'dpomandi',
@actioncode=1;
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '002',
@usercode = 'bdobalh',
@actioncode=1;
--intransit
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '000',
@usercode = 'AdminState',
@actioncode=2;
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '001',
@usercode = 'dpomandi',
@actioncode=2;
EXEC sec.Mobile_GetBallotBox_wh_dis_blk_list
@userlevelcode = '002',
@usercode = 'bdobalh',
@actioncode=2;
*/
ALTER PROCEDURE [sec].[Mobile_GetBallotBox_wh_dis_blk_list]
@userlevelcode CHAR(5),
@usercode CHAR(13),
@actioncode INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @JurisdictionLeftLength INT;
SET @usercode = [secInventory].[sec].get_user_code(@usercode);
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 @actioncode = 1
BEGIN
IF @userlevelcode IN ('000', '012', '013') -- State-level
BEGIN
SELECT
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN 'Warehouse'
ELSE d.District_Name
END AS ddname,
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN S.to_user_code
ELSE CAST(LEFT(S.to_user_code, 4) + '0000000000' AS CHAR(13))
END AS code,
COUNT(*) AS total_boxes,
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN 0 -- Warehouse
ELSE 1 -- District
END AS is_jurisdiction
FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
LEFT JOIN sec.sec.Districts d ON d.District_Code = LEFT(S.to_user_code, 4)
WHERE
LEFT(S.to_user_code, 2) = LEFT(@usercode, 2)
AND S.is_received = 1
GROUP BY
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN 'Warehouse'
ELSE d.District_Name
END,
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN S.to_user_code
ELSE CAST(LEFT(S.to_user_code, 4) + '0000000000' AS CHAR(13))
END,
CASE
WHEN RIGHT(S.to_user_code, 11) = '00000000000' THEN 0
ELSE 1
END
ORDER BY ddname;
END
ELSE IF @userlevelcode = '001' -- District-level
BEGIN
SELECT
CASE
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE b.Block_Name
END AS ddname,
CAST(LEFT(S.to_user_code, 7) + '0000000' AS CHAR(13)) AS code,
COUNT(*) AS total_boxes,
CASE
-- Corrected condition: Check if the 'to_user_code' segment matches the warehouse pattern
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) AND RIGHT(S.to_user_code, 7) = '0000000' THEN 0 -- Warehouse for district level
ELSE 1 -- block
END AS 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
LEFT(S.to_user_code, 4) = LEFT(@usercode, 4)
AND S.is_received = 1
GROUP BY
CASE
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE b.Block_Name
END,
CAST(LEFT(S.to_user_code, 7) + '0000000' AS CHAR(13)),
CASE -- Include in GROUP BY as well
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) AND RIGHT(S.to_user_code, 7) = '0000000' THEN 0
ELSE 1
END
ORDER BY ddname;
END
ELSE IF @userlevelcode IN ('002', '003') -- Block-level
BEGIN
SELECT
CASE
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE p.Panchayat_Name
END AS ddname,
CAST(LEFT(S.to_user_code, 10) + '000' AS CHAR(13)) AS code,
COUNT(*) AS total_boxes,
CASE
-- Corrected condition: Check if the 'to_user_code' segment matches the warehouse pattern
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) AND RIGHT(S.to_user_code, 6) = '000000' THEN 0 -- Warehouse for block level (Panchayat starts from 7th char)
ELSE 1 -- panchayat
END AS 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
LEFT(S.to_user_code, 7) = LEFT(@usercode, 7)
AND S.is_received = 1
GROUP BY
CASE
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE p.Panchayat_Name
END,
CAST(LEFT(S.to_user_code, 10) + '000' AS CHAR(13)),
CASE -- Include in GROUP BY as well
WHEN LEFT(S.to_user_code, 7) = LEFT(@usercode, 7) AND RIGHT(S.to_user_code, 6) = '000000' THEN 0
ELSE 1
END
ORDER BY ddname;
END
END
-- ACTION 2: IN TRANSIT
ELSE IF @actioncode = 2
BEGIN
IF @userlevelcode IN ('000', '012', '013') -- State-level
BEGIN
SELECT
CASE
WHEN RIGHT(S.from_user_code, 11) = '00000000000' THEN 'Warehouse'
ELSE d.District_Name
END AS ddname,
CASE
WHEN RIGHT(S.from_user_code, 11) = '00000000000' THEN S.from_user_code
ELSE CAST(LEFT(S.from_user_code, 4) + '0000000000' AS CHAR(13))
END AS code,
COUNT(*) AS total_boxes,
0 AS is_jurisdiction -- Always 0 for actioncode 2
FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
LEFT JOIN sec.sec.Districts d ON d.District_Code = LEFT(S.from_user_code, 4)
WHERE
LEFT(S.from_user_code, 2) = LEFT(@usercode, 2)
AND S.is_received = 0
GROUP BY
CASE
WHEN RIGHT(S.from_user_code, 11) = '00000000000' THEN 'Warehouse'
ELSE d.District_Name
END,
CASE
WHEN RIGHT(S.from_user_code, 11) = '00000000000' THEN S.from_user_code
ELSE CAST(LEFT(S.from_user_code, 4) + '0000000000' AS CHAR(13))
END
ORDER BY ddname;
END
ELSE IF @userlevelcode = '001' -- District-level
BEGIN
SELECT
CASE
WHEN LEFT(S.from_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE b.Block_Name
END AS ddname,
CAST(LEFT(S.from_user_code, 7) + '0000000' AS CHAR(13)) AS code,
COUNT(*) AS total_boxes,
0 AS is_jurisdiction -- Always 0 for actioncode 2
FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
LEFT JOIN sec.sec.Blocks b ON b.Block_Code = LEFT(S.from_user_code, 7)
WHERE
LEFT(S.from_user_code, 4) = LEFT(@usercode, 4)
AND S.is_received = 0
GROUP BY
CASE
WHEN LEFT(S.from_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE b.Block_Name
END,
CAST(LEFT(S.from_user_code, 7) + '0000000' AS CHAR(13))
ORDER BY ddname;
END
ELSE IF @userlevelcode IN ('002', '003') -- Block-level
BEGIN
SELECT
CASE
WHEN LEFT(S.from_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE p.Panchayat_Name
END AS ddname,
CAST(LEFT(S.from_user_code, 10) + '000' AS CHAR(13)) AS code,
COUNT(*) AS total_boxes,
0 AS is_jurisdiction -- Always 0 for actioncode 2
FROM sec.Ballot_Box_Master_SrNo AS S WITH (NOLOCK)
LEFT JOIN sec.sec.Panchayats p ON p.Panchayat_Code = LEFT(S.from_user_code, 10)
WHERE
LEFT(S.from_user_code, 7) = LEFT(@usercode, 7)
AND S.is_received = 0
GROUP BY
CASE
WHEN LEFT(S.from_user_code, 7) = LEFT(@usercode, 7) THEN 'Warehouse'
ELSE p.Panchayat_Name
END,
CAST(LEFT(S.from_user_code, 10) + '000' AS CHAR(13))
ORDER BY ddname;
END
END
END;