Hi,
I have created a custom Barcode Wise Stock Report (Excel) using a wizard in Odoo.
The report is working correctly, but I need to filter the data based on selected branch (location).
🔹 Requirement:
- Add a field in wizard to select branch (location)
- When exporting Excel, it should show data only for selected branch
<?xml version="1.0" encoding="utf-8"?>
<odoo>
<!-- ================= FORM VIEW ================= -->
<record id="view_barcode_stock_report_form" model="ir.ui.view">
<field name="name">barcode.stock.report.form</field>
<field name="model">barcode.stock.report.wizard</field>
<field name="arch" type="xml">
<form string="Barcode Wise Stock Report">
<group>
<group>
<field name="from_date" required="1"/>
<field name="to_date" required="1"/>
<field name="categ_ids" widget="many2many_tags"/>
<field name="company_ids" widget="many2many_tags"/>
</group>
</group>
<footer>
<button name="action_export_excel"
type="object"
string="Export Excel"
class="btn-primary"/>
<button string="Cancel"
special="cancel"
class="btn-secondary"/>
</footer>
</form>
</field>
</record>
<!-- ================= ACTION ================= -->
<record id="action_barcode_stock_report" model="ir.actions.act_window">
<field name="name">Barcode Wise Stock Report</field>
<field name="res_model">barcode.stock.report.wizard</field>
<field name="view_mode">form</field>
<field name="target">new</field>
</record>
<!-- ================= MENU ================= -->
<!-- Appears inside Inventory → Reporting -->
<menuitem id="menu_barcode_stock_report"
name="Barcode Wise Stock Report"
parent="stock.menu_warehouse_report"
action="action_barcode_stock_report"
sequence="52"/>
</odoo>
from odoo import models, fields, _
from odoo.exceptions import ValidationError
import io
import base64
import xlsxwriter
from datetime import datetime, time
class BarcodeStockReportWizard(models.TransientModel):
_name = 'barcode.stock.report.wizard'
_description = 'Barcode Wise Stock Report'
from_date = fields.Date(required=True)
to_date = fields.Date(required=True)
categ_ids = fields.Many2many('product.category', string="Categories")
# NEW FIELD (BRANCH FILTER)
location_ids = fields.Many2many(
'stock.location',
string="Branch",
domain=[('usage', '=', 'internal')]
)
file = fields.Binary()
file_name = fields.Char()
# -------------------------------------------------------
# VARIANT FETCH
# -------------------------------------------------------
def _get_variant_map(self, products):
variant_map = {}
for product in products:
size = ""
color = ""
batch = ""
for val in product.product_template_attribute_value_ids:
attr = val.attribute_id.name.lower()
value = val.product_attribute_value_id.name
if 'size' in attr:
size = value
elif 'color' in attr or 'colour' in attr:
color = value
elif 'batch' in attr:
batch = value
variant_map[product.id] = (size, color, batch)
return variant_map
# -------------------------------------------------------
# MAIN EXPORT
# -------------------------------------------------------
def action_export_excel(self):
if self.from_date > self.to_date:
raise ValidationError("From Date must be less than To Date.")
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'constant_memory': True})
sheet = workbook.add_worksheet("Barcode Stock")
bold = workbook.add_format({'bold': True})
headers = [
"BARCODE","ITEM","CODE","BATCH","MFG. DATE","UNIT",
"PURCHASE RATE","MRP","SELLING RATE","WHOLESALE RATE",
"OPENING STOCK","PURCHASE","SALE","SALES RETURN",
"CURRENT BALANCE","DAYS","SUPPLIER","BRAND","CATEGORY",
"ARTICLE NO","RANGE","DEPARTMENT","MATERIAL","TYPE",
"COLOUR","SIZE"
]
for col, header in enumerate(headers):
sheet.write(0, col, header, bold)
sheet.set_column(col, col, 22)
from_dt = datetime.combine(self.from_date, time.min)
to_dt = datetime.combine(self.to_date, time.max)
# ---------------------------------------------------
# PRODUCTS FILTER ( COMPANY ADDED)
# ---------------------------------------------------
domain = [
('barcode', '!=', False),
('product_tmpl_id.type', '=', 'consu'),
('can_be_expensed', '=', False),
]
if self.categ_ids:
domain.append(('categ_id', 'child_of', self.categ_ids.ids))
products = self.env['product.product']\
.with_context(prefetch_fields=False)\
.search(domain)
if not products:
raise ValidationError(_("No products found."))
product_ids = tuple(products.ids)
tmpl_ids = tuple(products.mapped('product_tmpl_id').ids)
variant_map = self._get_variant_map(products)
# ---------------------------------------------------
# WHOLESALE PRICE
# ---------------------------------------------------
wholesale_map = {}
if tmpl_ids:
self.env.cr.execute("""
SELECT DISTINCT ON (product_tmpl_id)
product_tmpl_id,
wholesale_price
FROM prepurchase_line
WHERE product_tmpl_id IN %s
ORDER BY product_tmpl_id, id DESC
""", [tmpl_ids])
for tmpl_id, price in self.env.cr.fetchall():
wholesale_map[tmpl_id] = price or 0.0
# ---------------------------------------------------
# STOCK QUERY ( COMPANY FILTER ADDED)
# ---------------------------------------------------
stock_map = {}
if product_ids:
company_ids = tuple(self.company_ids.ids)
self.env.cr.execute("""
SELECT
sm.product_id,
SUM(CASE
WHEN sm.date < %s
AND dest.usage='internal'
THEN sm.product_uom_qty ELSE 0 END)
-
SUM(CASE
WHEN sm.date < %s
AND src.usage='internal'
THEN sm.product_uom_qty ELSE 0 END)
AS opening,
SUM(CASE
WHEN sm.date >= %s
AND sm.date <= %s
AND dest.usage='internal'
THEN sm.product_uom_qty ELSE 0 END)
AS purchase,
SUM(CASE
WHEN sm.date >= %s
AND sm.date <= %s
AND src.usage='internal'
THEN sm.product_uom_qty ELSE 0 END)
AS sale,
SUM(CASE
WHEN sm.origin_returned_move_id IS NOT NULL
THEN sm.product_uom_qty ELSE 0 END)
AS return_qty
FROM stock_move sm
JOIN stock_location src ON sm.location_id = src.id
JOIN stock_location dest ON sm.location_dest_id = dest.id
WHERE sm.state='done'
AND sm.product_id IN %s
AND sm.company_id IN %s -- IMPORTANT FIX
GROUP BY sm.product_id
""", (
from_dt, from_dt,
from_dt, to_dt,
from_dt, to_dt,
product_ids,
company_ids
))
for pid, opening, purchase, sale, ret in self.env.cr.fetchall():
stock_map[pid] = {
'opening': opening or 0,
'purchase': purchase or 0,
'sale': sale or 0,
'return': ret or 0,
}
# ---------------------------------------------------
# EXCEL WRITE (UNCHANGED)
# ---------------------------------------------------
row = 1
today = fields.Date.today()
for product in products:
tmpl = product.product_tmpl_id
stock = stock_map.get(product.id, {})
opening = stock.get('opening', 0)
purchase = stock.get('purchase', 0)
sale = stock.get('sale', 0)
ret = stock.get('return', 0)
closing = opening + purchase + ret - sale
size, color, batch = variant_map.get(product.id, ("","",""))
supplier = tmpl.seller_ids[0].partner_id.name if tmpl.seller_ids else ""
wholesale = wholesale_map.get(tmpl.id, 0.0)
brand = product.brand_id.name if hasattr(product,'brand_id') and product.brand_id else ""
department = product.department_id.name if hasattr(product,'department_id') and product.department_id else ""
material = product.material_id.name if hasattr(product,'material_id') and product.material_id else ""
sheet.write_row(row, 0, [
product.barcode,
product.name,
"",
batch,
"",
product.uom_id.name,
product.standard_price,
tmpl.list_price,
tmpl.list_price,
wholesale,
opening,
purchase,
sale,
ret,
closing,
"",
supplier,
brand,
tmpl.categ_id.name,
getattr(tmpl,'article_no',""),
getattr(tmpl,'range',""),
department,
material,
getattr(tmpl,'type',""),
color,
size,
])
row += 1
workbook.close()
output.seek(0)
self.file = base64.b64encode(output.read())
self.file_name = "Barcode_Stock_Report.xlsx"
return {
'type': 'ir.actions.act_url',
'url': f'/web/content/?model=barcode.stock.report.wizard&id={self.id}&field=file&download=true&filename={self.file_name}',
'target': 'self',
}
🔹 Issue:
- Report is showing data from all branches
- Selected location is not affecting the result
How can I correctly filter stock data using selected location_ids/company_id?
