payment_queries.py

queries = {
    # Query 1
    '1. 09-Sheet1': '''
        with main_table as (
        SELECT
        *
        FROM
        `fynd-db.Outstanding.09_Payable_File_table`
        where
        expected_payout_date <= '{selected_date}'
        ),
        calculated_table as (
        select
        *
        from
        (with Bag_count as
        (select 
        bag_id,
        count(bag_id) as bag_count
        from
        `fynd-db.Outstanding.09_Payable_File_table`
        where
        expected_payout_date <= '{selected_date}'
        group by
        1
        ),
        merged_count  as
        (select 
        concat(bag_id,transaction_type) as merged,
        count(concat(bag_id,transaction_type)) as merged_count
        from
        `fynd-db.Outstanding.09_Payable_File_table`
        where
        expected_payout_date <= '{selected_date}'
        group by
        1
        )
        select
        A.bag_id,
        settlement_type,
        transaction_type,
        A.merged,
        B.bag_count,
        C.merged_count,
        settled_merged,
        A.seller_net_collection,
        A.net_remitted,
        collection_payout_diff,
        Collection_amount_comment,
        paid_settled_merged_transaction,
        REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)')) as cc1,
        case 
        when A.bag_id in (select 
        bag_id,
        from
        `fynd-db.Outstanding.09_Payable_File_table`
        where
        expected_payout_date <= '{selected_date}'
        group by
        1
        having
        round(sum(seller_net_collection),0) in (0)) then 'collected'
        when Collection_amount_comment = 'Collection_Pending' and paid_settled_merged_transaction is null then 'Collection_Pending'
        when Collection_amount_comment = 'collection_diff' and paid_settled_merged_transaction is null then 'Collection_Pending'
        when bag_count = 3 and merged_count = 1 and collection_payout_diff in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'Match' then 'collected'
        when bag_count = 3 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'Collection_Pending' then 'collected'
        when bag_count = 3 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'collection_diff' then 'collected'
        when bag_count = 2 and merged_count = 1 and collection_payout_diff in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'Match' then 'collected'
        when bag_count = 2 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'Collection_Pending' then 'collected'
        when bag_count = 2 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'collection_diff' then 'collected'
        when bag_count = 1 and merged_count = 1 and collection_payout_diff in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false or paid_settled_merged_transaction is null) and  Collection_amount_comment = 'Match' then 'collected'
        when bag_count = 1 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false) and seller_net_collection < 0  and seller_net_collection < 0 and  Collection_amount_comment = 'Collection_Pending' then 'collected'
        when bag_count = 1 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and paid_settled_merged_transaction is null and  Collection_amount_comment = 'Collection_Pending' then 'Collection_Pending'
        when bag_count = 1 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)'))  = false) and  Collection_amount_comment = 'collection_diff' and seller_net_collection < 0 then 'collected' 
        when bag_count = 1 and merged_count = 1 and collection_payout_diff not in (0,1,-1) and paid_settled_merged_transaction is null and  Collection_amount_comment = 'collection_diff' and seller_net_collection < 0 then 'Collection_Pending' else 'Collection_Pending'
        end as Final_Comment
        from `fynd-db.Outstanding.09_Payable_File_table` as A
        left join
        Bag_count as B
        on
        A.bag_id = B.bag_id
        left join
        merged_count as C
        on
        A.merged = C.merged
        -- where
        --   A.bag_id not in (select 
        -- bag_id,
        -- from
        -- `fynd-db.Outstanding.09_Payable_File_table`
        -- group by
        --   1
        -- having
        -- round(sum(seller_net_collection),0) in (0))
        -- and A.bag_id = 21628339
        where
        expected_payout_date <= '{selected_date}'
        order by
        1 asc)
        -- where
        --   Final_Comment is null
        order by
        bag_id asc
        )

        select 
        *
        from
        main_table 
        left join
        calculated_table
        on 
        main_table.merged = calculated_table.merged
    ''',

    '2. 11-Sheet2': '''
        SELECT
        *
        FROM
        `fynd-db.Outstanding.11_fees_payable`
        where
        expected_payout_date <= '{selected_date}'
    ''',


    '3. 12-Sheet3': '''
        SELECT
        *
        FROM
        `fynd-db.Outstanding.12_claim_payable`
        where
        claim_settle_date <= '{selected_date}'
        ''',


    '4. MD-Sheet4': '''
        SELECT
        *
        FROM
        `fynd-db.Outstanding.Manual_Dispute`
        ''',


    '5. Not_match_09-Sheet5': '''
        WITH main_table AS (
            SELECT *
            FROM `fynd-db.Outstanding.09_Payable_File_table`
            WHERE expected_payout_date <= '{selected_date}'
        ),
        calculated_table AS (
            SELECT *
            FROM (
                WITH Bag_count AS (
                    SELECT 
                        bag_id,
                        COUNT(bag_id) AS bag_count
                    FROM `fynd-db.Outstanding.09_Payable_File_table`
                    WHERE expected_payout_date <= '{selected_date}'
                    GROUP BY 1
                ),
                merged_count AS (
                    SELECT 
                        CONCAT(bag_id, transaction_type) AS merged,
                        COUNT(CONCAT(bag_id, transaction_type)) AS merged_count
                    FROM `fynd-db.Outstanding.09_Payable_File_table`
                    WHERE expected_payout_date <= '{selected_date}'
                    GROUP BY 1
                )
                SELECT
                    A.bag_id,
                    settlement_type,
                    transaction_type,
                    A.merged,
                    B.bag_count,
                    C.merged_count,
                    settled_merged,
                    A.seller_net_collection,
                    A.net_remitted,
                    collection_payout_diff,
                    Collection_amount_comment,
                    paid_settled_merged_transaction,
                    REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)')) AS cc1,
                    CASE 
                        WHEN A.bag_id IN (
                            SELECT bag_id
                            FROM `fynd-db.Outstanding.09_Payable_File_table`
                            WHERE expected_payout_date <= '{selected_date}'
                            GROUP BY 1
                            HAVING ROUND(SUM(seller_net_collection), 0) IN (0)
                        ) THEN 'collected'
                        WHEN Collection_amount_comment = 'Collection_Pending' AND paid_settled_merged_transaction IS NULL THEN 'Collection_Pending'
                        WHEN Collection_amount_comment = 'collection_diff' AND paid_settled_merged_transaction IS NULL THEN 'Collection_Pending'
                        WHEN bag_count = 3 AND merged_count = 1 AND collection_payout_diff IN (0,1,-1) 
                            AND (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)')) = FALSE OR paid_settled_merged_transaction IS NULL) 
                            AND Collection_amount_comment = 'Match' THEN 'collected'
                        WHEN bag_count = 3 AND merged_count = 1 AND collection_payout_diff NOT IN (0,1,-1) 
                            AND (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)')) = FALSE OR paid_settled_merged_transaction IS NULL) 
                            AND Collection_amount_comment IN ('Collection_Pending', 'collection_diff') THEN 'collected'
                        WHEN bag_count = 1 AND merged_count = 1 AND collection_payout_diff NOT IN (0,1,-1) 
                            AND paid_settled_merged_transaction IS NULL 
                            AND Collection_amount_comment = 'Collection_Pending' THEN 'Collection_Pending'
                        WHEN bag_count = 1 AND merged_count = 1 AND collection_payout_diff NOT IN (0,1,-1) 
                            AND (REGEXP_CONTAINS(paid_settled_merged_transaction, CONCAT(r'(^|,)', A.merged, r'(,|$)')) = FALSE) 
                            AND Collection_amount_comment = 'collection_diff' 
                            AND seller_net_collection < 0 THEN 'collected'
                        ELSE 'Collection_Pending'
                    END AS Final_Comment
                FROM `fynd-db.Outstanding.09_Payable_File_table` AS A
                LEFT JOIN Bag_count AS B ON A.bag_id = B.bag_id
                LEFT JOIN merged_count AS C ON A.merged = C.merged
                WHERE expected_payout_date <= '{selected_date}'
                ORDER BY 1 ASC
            )
            ORDER BY bag_id ASC
        ),
        sub_query AS (
            SELECT *
            FROM main_table 
            LEFT JOIN calculated_table ON main_table.merged = calculated_table.merged
        )
        SELECT 
        sett_id, 
        bag_id, 
        settlement_type,
        '' as transaction_type,
        concat(bag_id, settlement_type) as merged,
        '' as New_sett_id
        FROM `finance_recon_tool_asia.09_seller_net_collection_daily` AS nine
        WHERE CONCAT(bag_id, settlement_type) NOT IN (
            SELECT merged_09
            FROM sub_query
        )
        AND nine.sett_id IN (
            SELECT sett_id
            FROM sub_query
        )
        ''',

    '6. Not_match_11-Sheet6': '''
        WITH main_table AS (
            SELECT *
            FROM   `fynd-db.Outstanding.11_fees_payable`
            WHERE expected_payout_date <= '{selected_date}'
        )
        SELECT 
        sett_id, 
        bag_id, 
        '' as settlement_type,
        transaction_type,
        concat(bag_id, transaction_type) as merged,
        '' as New_sett_id
        FROM `finance_recon_tool_asia.11_seller_fees_daily` AS nine
        WHERE CONCAT(bag_id, transaction_type) NOT IN (
            SELECT merged
            FROM main_table
        )
        AND nine.sett_id IN (
            SELECT sett_id
            FROM main_table
        )
        ''',

    '7. Not_match_12-Sheet7 ': '''
        WITH main_table AS (
            SELECT 
            *
            FROM  `fynd-db.Outstanding.12_claim_payable`
            WHERE claim_settle_date <= '{selected_date}'
        )
        SELECT 
        sett_id, 
        '' as bag_id, 
        '' as settlement_type,
        '' as transaction_type,
        concat(current_shipment_id, recon_status) as merged,
        '' as New_sett_id
        FROM `fynd-db.finance_recon_tool_asia.12_seller_claims_daily` AS nine
        WHERE concat(current_shipment_id, recon_status) NOT IN (
            SELECT 
            concat(current_shipment_id, recon_status) as merged 
            FROM main_table
        )
        AND nine.sett_id IN (
            SELECT sett_id
            FROM main_table
        )
        '''
}