Hello, on my senario, each invoice lines have some cost category. for this i add new table (domain) under app base, then i used it on purchase order line…
after i prepared some sql reports, its not ready yet but for me so complex. this is my not ready query.
WITH InvoiceRows AS (
SELECT
ppoll.id,
TO_CHAR(ai.origin_date, 'DD/MM/YYYY') AS invoice_date,
ROW_NUMBER() OVER (PARTITION BY ai.supplier_invoice_nb ORDER BY ppoll.id) AS satir_no,
ppoll.price AS price,
ppoll.in_tax_total,
CASE
WHEN ppoll.received_qty <> 0 THEN ROUND(ppoll.received_qty, 2)
ELSE NULL
END AS received_qty,
CASE
WHEN ppoll.received_qty > 0 THEN ssl.product_name
ELSE NULL
END AS gelenler,
ssl.product_name AS product_name,
CASE
WHEN ppoll.qty <> 0 THEN ROUND(ppoll.qty, 2)
ELSE NULL
END AS qty,
bu.name AS unit_name,
ai.supplier_invoice_nb,
ssm.supplier_shipment_ref,
TO_CHAR(ssm.supplier_shipment_date, 'DD/MM/YYYY') AS supplier_shipment_date,
bccr.name AS company_cost_category_ru
FROM
purchase_purchase_order_line ppoll
INNER JOIN
stock_stock_move_line ssl ON ppoll.id = ssl.purchase_order_line
LEFT JOIN
base_company_cost_category_ru bccr ON ppoll.company_cost_category_ru = bccr.id
LEFT JOIN
account_invoice ai ON ppoll.purchase_order = ai.purchase_order
LEFT JOIN
base_unit bu ON ssl.unit = bu.id
LEFT JOIN
stock_stock_move ssm ON ssl.stock_move = ssm.id
),
InvoiceSubtotals AS (
SELECT
supplier_invoice_nb,
SUM(in_tax_total) AS subtotal_in_tax
FROM
InvoiceRows
GROUP BY
supplier_invoice_nb
)
SELECT
company_cost_category_ru AS company_cost_category,
'BANKA' AS hesap,
satir_no AS no,
invoice_date AS invoice_date,
supplier_invoice_nb AS invoice_no,
product_name AS product_name,
qty AS quantity,
unit_name AS unit,
price AS price,
in_tax_total AS sum,
supplier_shipment_date AS supplier_shipping_date,
supplier_shipment_ref AS supplier_reference_no,
gelenler AS received_product,
received_qty AS received_quantity,
CASE WHEN gelenler IS NOT NULL THEN qty ELSE NULL END AS received_quantity_2,
CASE WHEN gelenler IS NOT NULL THEN unit_name ELSE NULL END AS received_unit,
CASE WHEN gelenler IS NOT NULL THEN price ELSE NULL END AS received_price,
CASE WHEN gelenler IS NOT NULL THEN in_tax_total ELSE NULL END AS received_sum
FROM
InvoiceRows
UNION ALL
SELECT
NULL AS company_cost_category,
NULL AS hesap,
NULL AS no,
NULL AS invoice_date,
supplier_invoice_nb AS invoice_no,
NULL AS product_name,
NULL AS quantity,
NULL AS unit,
NULL AS price,
CAST(subtotal_in_tax AS DECIMAL(12, 2)) AS sum,
NULL AS supplier_shipping_date,
NULL AS supplier_reference_no,
NULL AS received_product,
NULL AS received_quantity,
NULL AS received_quantity,
NULL AS received_unit,
NULL AS received_price,
NULL AS received_sum
FROM
InvoiceSubtotals
ORDER BY
invoice_no, no;
and this is my custom view
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<object-views xmlns="http://axelor.com/xml/ns/object-views"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://axelor.com/xml/ns/object-views http://axelor.com/xml/ns/object-views/object-views_5.4.xsd">
<custom name="report-company-list" title="Some Title" groups="" >
<field name="in_tax_total" type="decimal" x-scale="2" />
<field name="id" type="integer"/>
<field name="origin_date" type="date" x-translatable="true"/>
<field name="satir_no" type="integer"/>
<field name="price" type="decimal"/>
<field name="in_tax_total" type="decimal"/>
<field name="received_qty" type="decimal"/>
<field name="gelenler" type="string"/>
<field name="product_name" type="string"/>
<field name="qty" type="decimal"/>
<field name="unit_name" type="string"/>
<field name="supplier_invoice_nb" type="string"/>
<field name="supplier_shipment_ref" type="varchar"/>
<field name="supplier_shipment_date" type="date"/>
<field name="company_cost_category_ru" type="string"/>
<dataset type="sql" >
<![CDATA[
WITH InvoiceRows AS (
SELECT
ppoll.id,
TO_CHAR(ai.origin_date, 'DD/MM/YYYY') AS invoice_date,
ROW_NUMBER() OVER (PARTITION BY ai.supplier_invoice_nb ORDER BY ppoll.id) AS satir_no,
ppoll.price AS price,
ppoll.in_tax_total,
CASE
WHEN ppoll.received_qty <> 0 THEN ROUND(ppoll.received_qty, 2)
ELSE NULL
END AS received_qty,
CASE
WHEN ppoll.received_qty > 0 THEN ssl.product_name
ELSE NULL
END AS gelenler,
ssl.product_name AS product_name,
CASE
WHEN ppoll.qty <> 0 THEN ROUND(ppoll.qty, 2)
ELSE NULL
END AS qty,
bu.name AS unit_name,
ai.supplier_invoice_nb,
ssm.supplier_shipment_ref,
TO_CHAR(ssm.supplier_shipment_date, 'DD/MM/YYYY') AS supplier_shipment_date,
bccr.name AS company_cost_category_ru
FROM
purchase_purchase_order_line ppoll
INNER JOIN
stock_stock_move_line ssl ON ppoll.id = ssl.purchase_order_line
LEFT JOIN
base_company_cost_category_ru bccr ON ppoll.company_cost_category_ru = bccr.id
LEFT JOIN
account_invoice ai ON ppoll.purchase_order = ai.purchase_order
LEFT JOIN
base_unit bu ON ssl.unit = bu.id
LEFT JOIN
stock_stock_move ssm ON ssl.stock_move = ssm.id
),
InvoiceSubtotals AS (
SELECT
supplier_invoice_nb,
SUM(in_tax_total) AS subtotal_in_tax
FROM
InvoiceRows
GROUP BY
supplier_invoice_nb
)
SELECT
company_cost_category_ru AS company_cost_category,
'BANKA' AS hesap,
satir_no AS no,
invoice_date AS invoice_date,
supplier_invoice_nb AS invoice_no,
product_name AS product_name,
qty AS quantity,
unit_name AS unit,
price AS price,
in_tax_total AS sum,
supplier_shipment_date AS supplier_shipping_date,
supplier_shipment_ref AS supplier_reference_no,
gelenler AS received_product,
received_qty AS received_quantity,
CASE WHEN gelenler IS NOT NULL THEN qty ELSE NULL END AS received_quantity_2,
CASE WHEN gelenler IS NOT NULL THEN unit_name ELSE NULL END AS received_unit,
CASE WHEN gelenler IS NOT NULL THEN price ELSE NULL END AS received_price,
CASE WHEN gelenler IS NOT NULL THEN in_tax_total ELSE NULL END AS received_sum
FROM
InvoiceRows
UNION ALL
SELECT
NULL AS company_cost_category,
NULL AS hesap,
NULL AS no,
NULL AS invoice_date,
supplier_invoice_nb AS invoice_no,
NULL AS product_name,
NULL AS quantity,
NULL AS unit,
NULL AS price,
CAST(subtotal_in_tax AS DECIMAL(12, 2)) AS sum,
NULL AS supplier_shipping_date,
NULL AS supplier_reference_no,
NULL AS received_product,
NULL AS received_quantity,
NULL AS received_quantity,
NULL AS received_unit,
NULL AS received_price,
NULL AS received_sum
FROM
InvoiceSubtotals
ORDER BY
invoice_no, no;
]]>
</dataset>
<template>
<![CDATA[
<report-table data='data'columns='company_cost_category,hesap,no,invoice_date,invoice_no,product_name,quantity,unit,
price,sum,supplier_shipping_date,supplier_reference_no,received_product,received_quantity,received_unit,received_price,received_sum'></report-table>
]]>
</template>
</custom>
</object-views>
````Texte préformaté`
saisissez ou collez du code ici
i need to know how i can add some search and group and filter etc. and i also need some filter for this (i see on chart but its not table)
