reset_stock_quants

"""
Simple shell script to reset stock quant quantities.
Cut and paste and run this from the Odoo shell.
"""

from odoo.exceptions import UserError
from odoo.tools import float_round, float_compare


def reset_stock_quants(env, location_ids=None):
    """Reset stock quant quantities based on stock moves and create missing quants"""
    # Get locations to process
    if location_ids:
        locations = env['stock.location'].browse(location_ids).filtered(lambda l: l.usage == 'internal')
    else:
        domain = [('usage', '=', 'internal')]
        locations = env['stock.location'].search(domain)
    if not locations:
        raise UserError('No internal locations found to reset')
    reset_count = 0
    created_count = 0
    error_count = 0
    summary_lines = []
    print(f"Starting stock quant reset for {len(locations)} locations...")
    for location in locations:
        try:
            # Reset existing quants
            location_reset_count = _reset_location_quants(env, location)
            reset_count += location_reset_count
            # Create missing quants
            location_created_count = _create_missing_quants(env, location)
            created_count += location_created_count
            summary_lines.append(f"Location {location.complete_name}: {location_reset_count} quants reset, {location_created_count} quants created")
            print(f"✓ Location {location.complete_name}: {location_reset_count} quants reset, {location_created_count} quants created")
        except Exception as e:
            error_count += 1
            error_msg = f"Location {location.complete_name}: ERROR - {str(e)}"
            summary_lines.append(error_msg)
            print(f"✗ {error_msg}")
    # Update summary
    summary_lines.insert(0, f"Total quants reset: {reset_count}")
    summary_lines.insert(1, f"Total quants created: {created_count}")
    if error_count > 0:
        summary_lines.append(f"Errors encountered: {error_count}")
    summary = '\n'.join(summary_lines)
    print(f"\nReset completed!")
    print(f"Total quants reset: {reset_count}")
    print(f"Total quants created: {created_count}")
    if error_count > 0:
        print(f"Errors encountered: {error_count}")
    return {
        'total_reset': reset_count,
        'total_created': created_count,
        'errors': error_count,
        'summary': summary
    }


def _reset_location_quants(env, location):
    """Reset quants for a specific location"""
    reset_count = 0
    # Get all quants for this location
    domain = [('location_id', '=', location.id)]
    quants = env['stock.quant'].search(domain)
    for quant in quants:
        try:
            new_quantity = _calculate_quant_quantity(env, quant, location)
            if float_compare(quant.quantity, new_quantity, precision_digits=3):
                quant.quantity = new_quantity
                reset_count += 1
        except Exception as e:
            print(f"  Warning: Error resetting quant {quant.id}: {str(e)}")
    return reset_count


def _create_missing_quants(env, location):
    """Create missing quants for a specific location based on stock move lines"""
    created_count = 0
    # Find all unique product-lot combinations that should have quants but don't
    # Use SQL query to find missing combinations
    query = """
        SELECT DISTINCT
            sml.product_id,
            sml.lot_id,
            COALESCE(sml.lot_id, 0) as lot_key
        FROM stock_move_line sml
        JOIN product_product pp ON sml.product_id = pp.id
        JOIN product_template pt ON pp.product_tmpl_id = pt.id
        WHERE sml.state = 'done'
        AND sml.quantity > 0
        AND pt.is_storable = true
        AND (
            (sml.location_dest_id = %s AND sml.quantity > 0) OR
            (sml.location_id = %s AND sml.quantity > 0)
        )
        AND NOT EXISTS (
            SELECT 1 FROM stock_quant sq
            WHERE sq.location_id = %s
            AND sq.product_id = sml.product_id
            AND COALESCE(sq.lot_id, 0) = COALESCE(sml.lot_id, 0)
        )
        ORDER BY sml.product_id, lot_key
    """
    env.cr.execute(query, (location.id, location.id, location.id))
    missing_combinations = env.cr.fetchall()
    for product_id, lot_id, lot_key in missing_combinations:
        try:
            # Calculate the quantity for this product-lot combination
            quantity = _calculate_quant_quantity_for_combination(env, product_id, lot_id, location)
            if quantity > 0:
                # Create the missing quant
                env['stock.quant'].create({
                    'location_id': location.id,
                    'product_id': product_id,
                    'lot_id': lot_id if lot_id else False,
                    'quantity': quantity,
                })
                created_count += 1
                print(f"  Created quant for product {product_id}, lot {lot_id or 'None'} at location {location.complete_name} with quantity {quantity}")
        except Exception as e:
            print(f"  Warning: Error creating quant for product {product_id}, lot {lot_id or 'None'} at location {location.complete_name}: {str(e)}")
    return created_count


def _calculate_quant_quantity_for_combination(env, product_id, lot_id, location):
    """Calculate quantity for a specific product-lot combination at a location"""
    # Base domain for stock move lines to this location
    to_location_domain = [
        ('location_dest_id', '=', location.id),
        ('state', '=', 'done'),
        ('product_id', '=', product_id)
    ]
    # Base domain for stock move lines from this location
    from_location_domain = [
        ('location_id', '=', location.id),
        ('state', '=', 'done'),
        ('product_id', '=', product_id)
    ]
    # Add lot filter
    if lot_id:
        to_location_domain.append(('lot_id', '=', lot_id))
        from_location_domain.append(('lot_id', '=', lot_id))
    else:
        to_location_domain.append(('lot_id', '=', False))
        from_location_domain.append(('lot_id', '=', False))
    # Calculate quantities from stock move lines
    move_lines_to = env['stock.move.line'].search(to_location_domain)
    move_lines_from = env['stock.move.line'].search(from_location_domain)
    # Sum quantities using the quantity field
    quantity_to = float_round(sum(move_lines_to.mapped('quantity')), precision_digits=3)
    quantity_from = float_round(sum(move_lines_from.mapped('quantity')), precision_digits=3)
    # Final quantity = moves to - moves from
    final_quantity = quantity_to - quantity_from
    return max(0, final_quantity)  # Ensure non-negative


def _calculate_quant_quantity(env, quant, location):
    """Calculate the correct quantity for a quant based on stock move lines"""
    # Base domain for stock move lines to this location
    to_location_domain = [
        ('location_dest_id', '=', location.id),
        ('state', '=', 'done')
    ]
    # Base domain for stock move lines from this location
    from_location_domain = [
        ('location_id', '=', location.id),
        ('state', '=', 'done')
    ]
    # Add product filter
    if quant.product_id:
        to_location_domain.append(('product_id', '=', quant.product_id.id))
        from_location_domain.append(('product_id', '=', quant.product_id.id))
    # Add lot filter based on quant lot
    if quant.lot_id:
        to_location_domain.append(('lot_id', '=', quant.lot_id.id))
        from_location_domain.append(('lot_id', '=', quant.lot_id.id))
    else:
        to_location_domain.append(('lot_id', '=', False))
        from_location_domain.append(('lot_id', '=', False))
    # Calculate quantities from stock move lines
    move_lines_to = env['stock.move.line'].search(to_location_domain)
    move_lines_from = env['stock.move.line'].search(from_location_domain)
    # Sum quantities using the quantity field
    quantity_to = float_round(sum(move_lines_to.mapped('quantity')), precision_digits=3)
    quantity_from = float_round(sum(move_lines_from.mapped('quantity')), precision_digits=3)
    # Final quantity = moves to - moves from
    final_quantity = quantity_to - quantity_from
    return max(0, final_quantity)  # Ensure non-negative

reset_stock_quants(env)