ziadoz · April 10, 2026 17:02
diff --git a/plannr-7016-duplicate-statement-numbers.sql b/plannr-7016-duplicate-statement-numbers.sql
 -- PLANNR-7016: Duplicate EDI Statement Numbers
 -- https://github.com/PlannrCrm/plannr/pull/4593
 --
 -- Both queries below apply to provider_statements and bank_statements.
 -- Substitute the table name as needed.


 -- ============================================================
 -- 1. Check whether any duplicate (firm_id, number) pairs exist
 --    Used in shouldRun() on the unique constraint migrations.
 --    Returns 1 if duplicates exist, 0 if not.
 -- ============================================================

 SELECT EXISTS(
    SELECT 1
    FROM provider_statements
    GROUP BY firm_id, number
    HAVING COUNT(*) > 1
 ) AS has_duplicates;


 -- ============================================================
 -- 2. List all duplicate groups with firm name
 --    Useful for auditing before running the fix command.
 --    GROUP_CONCAT shows the IDs and created_at of each duplicate
 --    in the same order so they can be paired by position.
 -- ============================================================

 SELECT
    f.name                                                                    AS firm_name,
    ps.firm_id,
    ps.number,
    COUNT(*)                                                                  AS count,
    GROUP_CONCAT(ps.id       ORDER BY ps.created_at SEPARATOR ', ')          AS ids,
    GROUP_CONCAT(ps.created_at ORDER BY ps.created_at SEPARATOR ', ')        AS created_at
 FROM provider_statements ps
 JOIN firms f ON f.id = ps.firm_id
 GROUP BY ps.firm_id, ps.number
 HAVING COUNT(*) > 1
 ORDER BY ps.firm_id, ps.number;


 -- ============================================================
 -- 3. Fix command: window function query (fix:duplicate-statement-numbers)
 --    Inner subquery ranks every row within each (firm_id, number)
 --    group (lowest ID = rank 1, kept as-is) and stamps the
 --    current max number for the firm onto every row.
 --    Outer query filters to rank > 1 (the duplicates) and
 --    assigns each a new number by counting up from the firm max.
 -- ============================================================

 SELECT
    id,
    firm_id,
    number        AS old_number,
    new_number
 FROM (
    SELECT
        id,
        firm_id,
        number,
        max_number + ROW_NUMBER() OVER (PARTITION BY firm_id ORDER BY id) AS new_number,
        rank_number
    FROM (
        SELECT
            id,
            firm_id,
            number,
            ROW_NUMBER() OVER (PARTITION BY firm_id, number ORDER BY id) AS rank_number,
            MAX(number)    OVER (PARTITION BY firm_id)                    AS max_number
        FROM provider_statements
        WHERE firm_id IN (
            -- firms that have at least one duplicate number
            SELECT DISTINCT firm_id
            FROM provider_statements
            GROUP BY firm_id, number
            HAVING COUNT(*) > 1
        )
    ) ranked
    WHERE rank_number > 1
 ) fixes
 ORDER BY firm_id, old_number;


 -- ============================================================
 -- 4. Number assignment lock (getLastStatementNumber)
 --    Called inside the Eloquent creating observer, wrapped in
 --    the existing DB transaction. FOR UPDATE blocks concurrent
 --    workers from reading the same max until the insert commits.
 -- ============================================================

 SELECT MAX(number)
 FROM provider_statements
 WHERE firm_id = :firm_id
 FOR UPDATE;
	-- PLANNR-7016: Duplicate EDI Statement Numbers
	-- https://github.com/PlannrCrm/plannr/pull/4593
	--
	-- Both queries below apply to provider_statements and bank_statements.
	-- Substitute the table name as needed.


	-- ============================================================
	-- 1. Check whether any duplicate (firm_id, number) pairs exist
	-- Used in shouldRun() on the unique constraint migrations.
	-- Returns 1 if duplicates exist, 0 if not.
	-- ============================================================

	SELECT EXISTS(
	SELECT 1
	FROM provider_statements
	GROUP BY firm_id, number
	HAVING COUNT(*) > 1
	) AS has_duplicates;


	-- ============================================================
	-- 2. List all duplicate groups with firm name
	-- Useful for auditing before running the fix command.
	-- GROUP_CONCAT shows the IDs and created_at of each duplicate
	-- in the same order so they can be paired by position.
	-- ============================================================

	SELECT
	f.name AS firm_name,
	ps.firm_id,
	ps.number,
	COUNT(*) AS count,
	GROUP_CONCAT(ps.id ORDER BY ps.created_at SEPARATOR ', ') AS ids,
	GROUP_CONCAT(ps.created_at ORDER BY ps.created_at SEPARATOR ', ') AS created_at
	FROM provider_statements ps
	JOIN firms f ON f.id = ps.firm_id
	GROUP BY ps.firm_id, ps.number
	HAVING COUNT(*) > 1
	ORDER BY ps.firm_id, ps.number;


	-- ============================================================
	-- 3. Fix command: window function query (fix:duplicate-statement-numbers)
	-- Inner subquery ranks every row within each (firm_id, number)
	-- group (lowest ID = rank 1, kept as-is) and stamps the
	-- current max number for the firm onto every row.
	-- Outer query filters to rank > 1 (the duplicates) and
	-- assigns each a new number by counting up from the firm max.
	-- ============================================================

	SELECT
	id,
	firm_id,
	number AS old_number,
	new_number
	FROM (
	SELECT
	id,
	firm_id,
	number,
	max_number + ROW_NUMBER() OVER (PARTITION BY firm_id ORDER BY id) AS new_number,
	rank_number
	FROM (
	SELECT
	id,
	firm_id,
	number,
	ROW_NUMBER() OVER (PARTITION BY firm_id, number ORDER BY id) AS rank_number,
	MAX(number) OVER (PARTITION BY firm_id) AS max_number
	FROM provider_statements
	WHERE firm_id IN (
	-- firms that have at least one duplicate number
	SELECT DISTINCT firm_id
	FROM provider_statements
	GROUP BY firm_id, number
	HAVING COUNT(*) > 1
	)
	) ranked
	WHERE rank_number > 1
	) fixes
	ORDER BY firm_id, old_number;


	-- ============================================================
	-- 4. Number assignment lock (getLastStatementNumber)
	-- Called inside the Eloquent creating observer, wrapped in
	-- the existing DB transaction. FOR UPDATE blocks concurrent
	-- workers from reading the same max until the insert commits.
	-- ============================================================

	SELECT MAX(number)
	FROM provider_statements
	WHERE firm_id = :firm_id
	FOR UPDATE;
No results found