Bank Statement Lines Moves.py

def fix_account_bank_statement_line_link_moves(src_cr, dst_cr):
    ## SET CURRENCY_ID

    # "UPDATE account_bank_statement_line AS l
    SET_CURRENCY_QUERY = """
    UPDATE account_bank_statement_line AS l
    SET currency_id = 
        (CASE
            WHEN l.currency_id IS NULL THEN COALESCE(j.currency_id, c.currency_id)
            ELSE l.currency_id
        END)
    FROM account_bank_statement AS s
    LEFT JOIN account_journal AS j ON j.id = s.journal_id
    LEFT JOIN res_company AS c ON c.id = j.company_id
    WHERE s.id = l.statement_id;
    """

    dst_cr.execute(SET_CURRENCY_QUERY)

    dst_cr.execute("select id from account_bank_statement_line where move_id is null")
    # dst_bank_statment_lines = [row['id'] for row in dst_cr.fetchall()]

    for row in dst_cr.fetchall():
        statement_line_id = row[0]
        account_move_query = f"""
            INSERT INTO account_move ( state, move_type, auto_post,date, currency_id, journal_id, company_id, name, ref)
            SELECT 'draft', 'entry', 'no',
                    '2024-12-12' as date,  
                   sl.currency_id,
                   abs.journal_id,
                   j.company_id,
                   sl.internal_index, 
                   'Your Ref Name'
            FROM account_bank_statement_line sl
            INNER JOIN account_bank_statement abs ON sl.statement_id = abs.id
            INNER JOIN account_journal j ON abs.journal_id = j.id
            WHERE sl.id = {statement_line_id}
            RETURNING id;
            """
        dst_cr.execute(account_move_query)


        # Fetch the generated move_id
        move_id = dst_cr.fetchone()[0]
        dst_cr.execute(f"UPDATE account_bank_statement_line SET move_id = {move_id} WHERE id = {statement_line_id};")
        # Now, use the move_id in the second query
        QUERY_MOVE_LINES = f"""
            INSERT INTO account_move_line (name, move_id, partner_id, account_id, currency_id, amount_currency, debit, credit, display_type)
            WITH statement_line AS (
                SELECT *
                FROM account_bank_statement_line
                WHERE id = {statement_line_id} -- Replace with the actual statement_line ID
            ),
            journal AS (
                SELECT j.*
                FROM account_journal j
                JOIN account_bank_statement abs ON j.id = abs.journal_id
                JOIN statement_line sl ON abs.id = sl.statement_id
            ),
            company_currency AS (
                -- Query to get the company's currency using the journal's company_id
                SELECT c.currency_id
                FROM res_company c
                JOIN journal j ON c.id = j.company_id
            ),
            partner AS (
                -- Query to get the partner information
                SELECT p.id AS partner_id
                FROM res_partner p
                JOIN statement_line sl ON p.id = sl.partner_id
            ),
            amounts AS (
                -- Query to calculate the company_amount, journal_amount, transaction_amount, etc.
                SELECT
                    sl.amount AS company_amount,
                    sl.amount_currency AS journal_amount,
                    sl.amount AS transaction_amount -- Assuming this field is used for transaction amount
                FROM statement_line sl
            ),
            liquidity_line_vals AS (
                SELECT
                    sl.payment_ref AS name,
                    {move_id} AS move_id, -- Use the generated move_id here
                    'product' AS display_type,
                    p.partner_id AS partner_id,
                    j.default_account_id AS account_id,
                    cc.currency_id AS currency_id,
                    journal_amount AS amount_currency,
                    CASE WHEN company_amount > 0 THEN company_amount ELSE 0.0 END AS debit,
                    CASE WHEN company_amount < 0 THEN -company_amount ELSE 0.0 END AS credit
                FROM statement_line sl
                CROSS JOIN journal j
                CROSS JOIN company_currency cc
                CROSS JOIN partner p
                CROSS JOIN amounts
            ),
            counterpart_line_vals AS (
                SELECT
                    sl.payment_ref AS name,
                    {move_id} AS move_id, -- Use the generated move_id here
                    'product' AS display_type,
                    p.partner_id AS partner_id,
                    j.suspense_account_id AS account_id, -- Replace with the appropriate counterpart account ID
                    sl.currency_id AS currency_id,
                    -transaction_amount AS amount_currency,
                    CASE WHEN company_amount < 0.0 THEN -company_amount ELSE 0.0 END AS debit,
                    CASE WHEN company_amount > 0.0 THEN company_amount ELSE 0.0 END AS credit
                FROM statement_line sl
                CROSS JOIN journal j
                CROSS JOIN company_currency cc
                CROSS JOIN partner p
                CROSS JOIN amounts
            )
            SELECT name, move_id, partner_id, account_id, currency_id, amount_currency, debit, credit, display_type
            FROM liquidity_line_vals
            UNION ALL
            SELECT name, move_id, partner_id, account_id, currency_id, amount_currency, debit, credit, display_type
            FROM counterpart_line_vals;
        """
        dst_cr.execute(QUERY_MOVE_LINES)
    ## Set Date and ref from old database
    src_cr.execute("SELECT id, ref, date FROM account_bank_statement_line")
    for row in src_cr.fetchall():
        statement_line_id = row[0]
        ref = row[1]
        date = row[2]
        ref = row[1] and row[1].replace("'", "''") or ''  # Properly escape single quotes

        # Execute the query to fetch move_id from dst_cr
        dst_cr.execute(f"SELECT move_id FROM account_bank_statement_line WHERE id = {statement_line_id}")
        move_id_row = dst_cr.fetchone()  # Fetch from dst_cr, not src_cr

        if move_id_row:
            print("Updating move line.....", move_id_row)
            move_id = move_id_row[0]

            # Ensure proper string formatting and variable interpolation
            dst_cr.execute(f"UPDATE account_move SET date = '{date}', ref = '{ref}' WHERE id = {move_id}")
    return True