The calendar view sends filters as a list of [doctype, field, op, value]
conditions (filter_area.get()); get_filter_conditions_qb accepts dict or list.
create_prospect_against_crm_deal and create_customer read `contacts`
from form_dict (json.loads(doc.contacts) / customer_data.get("contacts")),
which arrives as a native list under JSON body mode. Use frappe.parse_json.
make_payment_request(**args) is whitelisted and the client passes
`schedules` as a list, so json.loads(args.get("schedules")) raised
TypeError under JSON body mode. Use frappe.parse_json.
Opt ERPNext into native application/json request bodies (frappe#40237).
Non-GET requests to erpnext.* endpoints now send args as a JSON body
instead of form-encoded, per-key JSON-stringified values. Safe after the
preceding commits hardened every whitelisted endpoint with frappe.parse_json.
Replace json.loads(object_hook=...) with frappe.parse_json and wrap each
row in frappe._dict, fixing attribute access when args arrive as native
JSON (list of dicts) instead of a JSON string.
Adds a `pcv_job_timeout` Int field (default 3600s) to Accounts Settings
so admins can tune the enqueue timeout for PCV background jobs without
a code change. All three `frappe.enqueue` calls in
`process_period_closing_voucher.py` now read this value at runtime.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
get_item_price orders Item Price rows by valid_from DESC and takes LIMIT 1 to
pick the most-recent applicable price. NULL-valid_from rows are kept (the
transaction-date guard uses IfNull(valid_from, '2000-01-01')), and MariaDB
sorts NULL last for DESC while PostgreSQL defaults to NULLS FIRST — so when an
item/price_list/uom has both a dated price and a NULL-valid_from price,
PostgreSQL returns the NULL one and MariaDB the most-recent dated one, a silent
price divergence.
Wrap the sort key in IfNull(valid_from, '1900-01-01') so the NULL row sorts
last on both engines. MariaDB already placed it last for DESC, so its pick is
unchanged. Same NULL-ordering class fixed in point_of_sale.get_items (#56378).
Financial Report Template calculation_formula filters are user-authored and
only validated for field existence + operator membership, not that a
like/ilike operator targets a text field. A filter such as
["is_group", "like", "1"] builds `is_group ILIKE '%1%'`; PostgreSQL has no
LIKE/ILIKE operator for a smallint/int/numeric column
(`operator does not exist: smallint ~~* unknown`) and aborts the report, while
MariaDB implicitly casts the numeric column to text and matches.
For like-family operators, cast a numeric/Check Account field to varchar
(`Cast_(field, "varchar")`), reproducing MariaDB's implicit numeric->text
coercion on both engines. Text-field filters (the normal account_name/
account_number case) are left untouched, so MariaDB output is unchanged.
get_materials divides stock_entry_detail.qty by a CASE that returns
fg_completed_qty when it is > 0 and otherwise the injected sabb_data.qty. The
code explicitly anticipates fg_completed_qty <= 0 (the else branch), and
neither fg_completed_qty nor sabb_data.qty is constrained non-zero, so the
divisor can be 0. MariaDB returns NULL for x/0; PostgreSQL raises
`division by zero` and aborts the report. Wrapping the CASE in NullIf(..., 0)
makes the divisor NULL instead of 0 — unchanged on MariaDB, valid on Postgres.
POS get_items keeps Item Price rows with a NULL valid_from (open-ended base
price) alongside dated rows, orders by valid_from DESC, then picks the first
matching UOM positionally via next()/[0]. MariaDB sorts NULL last for DESC, so
a dated override wins; PostgreSQL defaults to NULLS FIRST for DESC, so the
NULL-valid_from base price wins instead — the POS shows a different
price_list_rate/currency on the two engines for an item that has both an
undated standing price and a dated price.
Coalesce(valid_from, "1900-01-01") in the ORDER BY forces the NULL row to sort
last on both engines. MariaDB already placed it last for DESC, so its output is
unchanged; PostgreSQL now picks the same dated override.
The Quality Inspection item link search builds a distinct, paginated
get_query with order_by="items.item_code". frappe's db_query silently drops
the ORDER BY for a distinct query on Postgres, so with offset/limit the
results come back in a different order AND a different page slice than MariaDB.
Append the ordering to the built query instead of passing order_by: item_code
is already in the DISTINCT select list, so ORDER BY on it is valid under
DISTINCT on Postgres, and it now applies before LIMIT on both engines. MariaDB
output is unchanged (it was already ordered by item_code). The items child
field is guarded for None so a doctype without it degrades gracefully rather
than raising AttributeError.
* fix(accounts): wrap loose finance_book in max() in Trial Balance (Simple) (Postgres)
The Trial Balance (Simple) Query Report selects `finance_book` but groups only
by `fiscal_year, company, posting_date, account`. PostgreSQL rejects the
non-grouped, non-aggregated column:
column "tabGL Entry.finance_book" must appear in the GROUP BY clause or be
used in an aggregate function
MariaDB tolerates it and returns an arbitrary finance_book per group.
Wrapping it in `max(finance_book)` keeps the row count identical (the GROUP BY
is unchanged) and makes PostgreSQL valid. Adding finance_book to GROUP BY would
split each group into N rows and change the MariaDB row count, so it is not an
option. This replaces MariaDB's previously arbitrary finance_book value with a
deterministic one (the only sanctioned MariaDB-output change); the row count is
preserved.
* fix(accounts): make Sales Partners Commission valid on Postgres (ORDER BY + div-by-zero)
The Sales Partners Commission Query Report had two PostgreSQL problems:
1. It ended with `ORDER BY "Total Commission:Currency:120"`, but the alias the
query produces is `"Total Commission:Currency:170"` (width 170, not 120), so
the ORDER BY never referenced a real output column. On MariaDB a double-quoted
token is a string literal — a no-op sort that never errored. On PostgreSQL a
double-quoted token is an identifier, so it errors with
`column "Total Commission:Currency:120" does not exist` (and single-quoting it
instead trips `non-integer constant in ORDER BY`). Since the clause was always
a no-op on MariaDB, it is removed — MariaDB's group-order output is unchanged
and the report runs on PostgreSQL.
2. `sum(total_commission)*100 / sum(amount_eligible_for_commission)` has an
unguarded divisor: the inner query filters `total_commission`/`base_net_total`
but not `amount_eligible_for_commission`, so a partner whose rows sum to 0
there makes MariaDB return NULL but PostgreSQL raise `division by zero`. Wrap
it in `NULLIF(sum(amount_eligible_for_commission), 0)` — NULL on both engines.
Both verified live on MariaDB and PostgreSQL.
The non-stock-item valuation rate divides Sum(base_net_amount) by
Sum(qty * conversion_factor) over Purchase Invoice Items. A line with qty 0
zeroes the divisor. MariaDB returns NULL for x/0 (the caller maps it via
`or 0.0`); PostgreSQL raises `division by zero` and aborts. Wrap the divisor in
NullIf(Sum(qty * conversion_factor), 0): unchanged on MariaDB, valid on Postgres.
deactivate_sales_person looks up `frappe.db.get_value("Sales Person",
{"Employee": employee})`. The Sales Person field is `employee` (lower case);
the lookup runs with ignore_permissions, so the capital-cased key reaches the
query as the column `"Employee"`. PostgreSQL matches quoted identifiers
case-sensitively and errors:
column "Employee" does not exist
MariaDB resolves `Employee` to the `employee` column regardless of case, so
using the stored `{"employee": employee}` selects the same row on MariaDB and
is valid on PostgreSQL.
Customer-wise Item Price builds `ip.selling.eq(True)`, which renders
`WHERE "selling" = true`. `selling` is a Check field (smallint on Postgres),
and PostgreSQL has no `smallint = boolean` operator:
operator does not exist: smallint = boolean
MariaDB accepts it because `true` aliases to `1`. Comparing with the integer
`1` (`ip.selling.eq(1)`) selects identical rows on MariaDB and is valid on
PostgreSQL.
update_received_qty_if_from_pp divides received_qty by (qty / fg_item_qty) over
Purchase Order Items. Both qty and fg_item_qty are Float with no non-zero
constraint, so a zero qty (or fg_item_qty) drives the divisor to 0.
MariaDB returns NULL for x/0 (dropped by the surrounding Sum); PostgreSQL
raises `division by zero` and aborts the Purchase Receipt submit/cancel.
Wrapping both divisors in NullIf(..., 0) makes the zero row contribute NULL on
both engines, leaving MariaDB output unchanged.
The Incorrect Serial No Valuation report computes
stock_value_difference / actual_qty for every matching Stock Ledger Entry. A
valuation-only Stock Reconciliation of serialized/batched stock writes an SLE
with actual_qty = 0 and a non-zero stock_value_difference, and the or_filters
(serial_no / serial_and_batch_bundle set) do not exclude it.
MariaDB returns NULL for x/0; PostgreSQL raises `division by zero` and aborts
the report. Using the get_all nested NULLIF form
{"DIV": ["stock_value_difference", {"NULLIF": ["actual_qty", 0]}]} yields NULL
on both engines, leaving MariaDB output unchanged.
The Lost Quotations report's lost-value ratio divides Sum(base_net_total) by
total_value, a scalar Sum(base_net_total) subquery over the same lost
quotations. If every lost quotation in the period is zero-amount, total_value
is 0 while the grouped query still returns rows.
MariaDB returns NULL for x/0; PostgreSQL raises `division by zero` and aborts
the report. Wrapping the divisor in NullIf(total_value, 0) yields the same NULL
column on MariaDB and no error on PostgreSQL. (The sibling count ratio divides
by Count >= 1 in any returned row and is unaffected.)
_get_avg_valuation_rate_from_bins divides Sum(stock_value) by Sum(actual_qty).
The `Count(name) > 0` guard only proves a Bin row exists; Sum(actual_qty) can
still be 0 (stock depleted, or per-warehouse quantities cancelling out), and
the outer IfNull catches only NULL, not a 0 divisor.
MariaDB returns NULL for x/0 (then IfNull -> 0.0); PostgreSQL raises
`division by zero` and aborts BOM costing. Wrapping the divisor in
NullIf(Sum(actual_qty), 0) keeps the identical 0.0 result on MariaDB and
avoids the error on PostgreSQL.
calculate_exchange_rate_using_last_gle divides (debit - credit) by
(debit_in_account_currency - credit_in_account_currency). The GL row is
re-selected by (voucher_type, voucher_no, account) ordered by posting_date
WITHOUT the "(debit_in_account_currency > 0) | (credit_in_account_currency > 0)"
filter the first query used, so the chosen row can have equal/zero account-
currency amounts, making the divisor 0.
MariaDB returns NULL for x/0 (the caller maps it via `or 0.0`); PostgreSQL
raises `division by zero` and aborts. Wrapping the divisor in NullIf(divisor, 0)
yields NULL on both engines, so MariaDB output is unchanged and PostgreSQL no
longer errors.
The Asset Depreciations and Balances report tested disposal status with
IfNull(asset.disposal_date, 0) != 0 / == 0 — coalescing the DATE column
disposal_date with the integer 0. frappe.qb renders this as
COALESCE("disposal_date", 0); PostgreSQL requires COALESCE arguments to
share a type and raises:
psycopg2.errors.DatatypeMismatch: COALESCE types date and integer
cannot be matched
The predicate is in the WHERE/CASE of every query the report runs (both
group_by=Asset Category and group_by=Asset), so the whole report errored
on PostgreSQL. MariaDB's IFNULL(date, 0) is permissive and worked.
Replace each comparison with the null-test form already used elsewhere in
this same file: IfNull(disposal_date, 0) != 0 -> disposal_date.isnotnull(),
== 0 -> disposal_date.isnull(). Semantically identical (a stored date is
never 0), valid on both engines, MariaDB output unchanged.
Adds the division-by-zero divergence class to the PG-compat review tooling:
on a divisor that the data can drive to 0 (e.g. Sum(a)/Sum(b)), MariaDB
returns NULL for division by zero while PostgreSQL raises `division by zero`
and aborts the query. The portable fix is to wrap the divisor in
NullIf(divisor, 0), which yields NULL on both engines (matching MariaDB).
- .greptile/config.json: add it to the "would ERROR on PostgreSQL" list.
- .github/POSTGRES_COMPATIBILITY.md: document it under §1 (hard breaks).
- .github/helper/postgres_compat.py: note it in the docstring as a
deliberately-not-statically-checked semantic divergence (data-dependent,
like integer-division intent), so it stays a reviewer/Greptile concern.
Tooling-only; no source query changes. The instance fix shipped in #56361.
get_project_list builds a single-table query (no joins) with fields="*",
which always selects the unique PK `name`, so distinct=True can never
deduplicate any rows. It is a no-op on the result set for both engines.
It is not a no-op on ordering, though: frappe.db drops the ORDER BY clause
for distinct queries on Postgres (Postgres requires every ORDER BY term to
appear in the select list under DISTINCT), so the website project list came
back unordered on Postgres while MariaDB returned it ordered by `order_by`.
Removing the redundant flag leaves the MariaDB result and order untouched
and restores the same ordering on Postgres.
fix(stock): guard batchwise valuation-rate division against a zero divisor
get_valuation_rate's batchwise fallback selects
Sum(stock_value_difference) / Sum(actual_qty). When a batch's non-current
Stock Ledger Entries net to zero quantity (equal received and issued) the
divisor Sum(actual_qty) is 0. On MariaDB x/0 yields NULL and the caller's
`if last_valuation_rate and last_valuation_rate[0][0] is not None` check
falls through to the next strategy; on PostgreSQL float division by zero
raises `division by zero`, aborting the query (and the transaction).
Wrap the divisor in NullIf(Sum(actual_qty), 0) so a zero divisor yields
NULL on both engines, matching MariaDB and preserving the caller's
is-not-None fall-through. (stock_value_difference is Currency and actual_qty
is Float, so the division was already float — no integer-truncation change.)
The item_code field in the Job Card Item child table was optional,
allowing job cards to be saved without a raw material item linked.
Set reqd=1 in the JSON and update the Python type annotation accordingly.
ci(postgres): teach the guard about COALESCE(date, int) type mismatch
New class found by the whole-repo audit (the Asset Depreciations report fix in this PR): IfNull/Coalesce of a typed column with a different-typed literal -- e.g. IfNull(date_col, 0) -> COALESCE(date, integer), which PostgreSQL rejects (DatatypeMismatch). Added to the Greptile config and POSTGRES_COMPATIBILITY.md (not statically checkable without column types).
The [^)]* span stopped at the first inner ')', so CAST(ABS(col) AS CHAR) slipped through. Use a non-greedy .+? with re.S; still zero production false positives (verified). Addresses review feedback.
The whole-repo MariaDB<->PostgreSQL audits surfaced classes the checker and
review guide did not yet cover. Add them:
Static checker (.github/helper/postgres_compat.py) - new mechanical breaks:
- .rlike() / raw RLIKE: frappe rewrites REGEXP->~* on Postgres but NOT RLIKE.
- Cast(x, "char") / raw CAST AS CHAR: bare CHAR is character(1) on Postgres
and truncates multi-digit values; use "varchar".
(Both flag zero production code; the only repo hit is in patches/, which the
hook already excludes.)
Greptile config + POSTGRES_COMPATIBILITY.md - new semantic/hard classes:
- aggregate (Sum/Count) selected next to bare columns with no GROUP BY at all.
- .like()/LIKE on a non-text column (bigint ILIKE) -> Cast_ to varchar.
- get_all(fields=["CapitalCase"]) identifier-case (extends the get_value case).
- bool into a Check column via qb.update().set() (extends set_value/db_set).
- int/int division: float a literal (col/1440 -> col/1440.0).
- Concat over a nullable column leaking a bare prefix on Postgres.
- clarify REGEXP/.regexp() is translated but RLIKE/.rlike() is not.
get_item_lead_time in Master Production Schedule computes
manufacturing_time_in_mins / 1440 + purchase_time + buffer_time. As in the
MRP report, manufacturing_time_in_mins is an Int column and 1440 an int
literal, so the division truncates on PostgreSQL (720/1440 -> 0) while
MariaDB yields 0.5. The value is summed over the BOM tree, ceil'd, and
drives the planned order-release date, so it diverged by engine.
Use a float numerator (1440.0). MariaDB output is unchanged; PostgreSQL
now matches it.
get_item_lead_time computed the manufacturing lead time as
1440 / manufacturing_time_in_mins + buffer_time. Both columns are Int, so
integer/integer division truncates on PostgreSQL (1440/7 -> 205) while
MariaDB yields a decimal (205.71). The value feeds math.ceil() and then
release_date = add_days(delivery_date, -lead_time), so a user could see a
release date that differs by a day between engines.
Make the numerator a float literal (1440.0) so both engines do decimal
division. MariaDB already returned a decimal, so its output is unchanged;
PostgreSQL now matches it.
get_tax_accounts fetched fields=['Account'] but the UAE VAT Account fieldname is lowercase account. PostgreSQL treats the double-quoted identifier case-sensitively ('column "Account" does not exist'); MariaDB identifiers are case-insensitive so it worked there. Use the real fieldname account; output unchanged on MariaDB.
get_opening_balance_for_inv_dimension selected item_code and warehouse alongside Sum() aggregates with no GROUP BY, which PostgreSQL rejects ('column ...item_code must appear in the GROUP BY clause'). Add GROUP BY item_code, warehouse. The query already returns early unless a single item and warehouse is selected, so this stays one row with identical values on MariaDB while becoming valid on Postgres.
get_filtered_child_rows searched child rows by row number with table.idx.like(...). idx is an integer column; frappe maps .like() to ILIKE on Postgres, which has no bigint ILIKE operator ('operator does not exist: bigint ~~* unknown'). Cast idx to string via frappe's Cast_ with 'varchar': a bare CAST(idx AS CHAR) is character(1) on Postgres and silently truncates a two-digit idx (11 -> '1'), dropping the row; CAST(idx AS VARCHAR) keeps the full value, and on MariaDB Cast_ rewrites to CONCAT(idx, '') matching the previous implicit coercion. MariaDB output unchanged. The test builds an order with >10 rows and searches row 11 (fails on Postgres with a char(1) cast).
get_docnames_for issued SELECT DISTINCT on Dynamic Link.link_name while ordering by Dynamic Link.idx, a column absent from the select list. This is a raw frappe.qb query (run via .run(), not get_all/get_list), so the ORDER BY is emitted verbatim and PostgreSQL rejects it: 'for SELECT DISTINCT, ORDER BY expressions must appear in select list'. Order by link_name (the selected, distinct column) instead; same docnames on both engines, now deterministically ordered.
The batch-number link picker (get_batch_no) had two Postgres-only defects in
both of its query builders (get_batches_from_stock_ledger_entries and
get_batches_from_serial_and_batch_bundle):
1. GROUP BY. They group by Stock Ledger Entry / Serial-and-Batch-Entry columns
while selecting un-aggregated Batch-master columns (manufacturing_date,
expiry_date, search fields). PostgreSQL only accepts that when the Batch
primary key is in the GROUP BY, so the picker raised GroupingError. Adding
batch_table.name (equal to the grouped batch_no via the join) keeps the
group count - and the MariaDB result - unchanged while making it valid.
2. CONCAT over nullable dates. "MFG-"/"EXP-" labels were built with
Concat("MFG-", manufacturing_date). When the date is NULL, MariaDB CONCAT
returns NULL but Postgres CONCAT drops the NULL and yields a bare "MFG-"/
"EXP-". Guard each with Case().when(date.isnotnull(), ...) so a missing date
is NULL on both engines (matching MariaDB, fixing Postgres).
Both leave MariaDB output unchanged. test_get_batch_no_search_returns_batches
exercises both builders directly and asserts no bare "MFG-"/"EXP-" leaks;
reverting either fix makes it fail on Postgres.
The Lead Details report concatenated address_line1 and address_line2 with
CONCAT_WS. An unfilled optional Data field is stored as '' on MariaDB but as
NULL on PostgreSQL; CONCAT_WS keeps the empty string (leaving a trailing
", ") on MariaDB while Postgres drops the NULL, so the same lead rendered a
different address on each engine.
Wrap both parts in NULLIF(part, '') so empty values are treated as NULL on
both engines: the report now produces the same clean address (no trailing
separator) everywhere.
get_picked_batches summed Serial-and-Batch-Entry qty while selecting bare
batch_no and warehouse with no GROUP BY. PostgreSQL rejects this outright:
column "tabSerial and Batch Entry.batch_no" must appear in the GROUP BY clause
MariaDB does not error but collapses every picked row into a single result -
the grand-total qty pinned to one arbitrary batch - so the caller, which keys
the result by (batch_no, warehouse) to subtract already-picked stock, under-counts
whenever more than one batch is picked.
Add GROUP BY batch_no, warehouse so the query returns one correct row per batch
on both engines (this corrects the MariaDB result, not just Postgres validity).
get_material_requests_based_on_supplier deduplicated requests with
SELECT DISTINCT (name, transaction_date, company) while ordering by
mr_item.item_code, which is not in the select list. MariaDB allows this;
PostgreSQL rejects it:
psycopg2.errors.InvalidColumnReference: for SELECT DISTINCT,
ORDER BY expressions must appear in select list
so the picker errored out there.
Group by the three selected columns (equivalent to the DISTINCT, so the
same set of requests is returned) and order by Min(item_code). The order
key stays item_code but is now a well-defined aggregate, making the query
valid - and the ordering deterministic and identical - on both engines.
frappe's db_query SILENTLY drops ORDER BY for distinct queries on Postgres (the ORDER BY
column must appear in the SELECT-DISTINCT list), so `get_all/get_list(distinct=True,
order_by="<col>")` is a no-op there and the result comes back unordered — the root cause of
the Sales Register, Purchase Register and Sales Analytics ordering fixes. Add an AST rule to
.github/helper/postgres_compat.py that flags this (literal order_by only; an empty order_by=""
suppression and a dynamic/variable order_by are not flagged). `# pg-ok` escape hatch as usual.
Grandfather the three pre-existing low-impact sites the rule surfaces (paging/iteration order
only, not data): job_card operation autocomplete, inventory_dimension config list, and a
work_order test loop.
get_payer_address_html picks one company address with ORDER BY (Postal DESC, Billing DESC)
LIMIT 1 and no column tie-break. When a company has two addresses of the same address_type
the two CASE keys tie, so the LIMIT-1 row is implementation-defined and MariaDB and PostgreSQL
can return a different address.name — i.e. a different payer address on the rendered IRS-1099
form for identical data.
Add a final .orderby(address.name), mirroring the sibling get_street_address_html in the same
file (which already carries the "deterministic LIMIT-1 tie-break across engines" order). The
pick is now the lexicographically-smallest name on both engines.
get_teams fetched distinct order_types with get_all(distinct=True, order_by="order_type").
frappe drops ORDER BY for distinct queries on postgres (db_query), so the order_by is a
no-op there and the report's order-type leaf rows are not guaranteed any order on PG
(PostgreSQL only sorts them incidentally via its DISTINCT plan). Sort in python with
key=str.casefold instead, matching MariaDB's case-insensitive collation and guaranteeing
an identical, stable order on both engines (same pattern as the Sales/Purchase Register
account-column fix). Add a test locking the sorted order-type row order.
employee_query, lead_query and bom() ranked autocomplete results with a bare
Locate(txt, col) in ORDER BY. frappe maps Locate -> strpos on Postgres, which is
case-sensitive, while MariaDB's LOCATE against a column uses the column's
case-insensitive collation. So the search-dropdown ordering diverged between engines for
mixed-case matches (row count/membership unchanged — the WHERE .like() is already ILIKE).
Wrap both Locate operands in Lower(), matching the sibling item_query/get_project_name
handlers in the same file: a no-op on MariaDB, and case-insensitive (MariaDB-faithful) on
Postgres. The existing test_queries suite stays green on both engines.
The Postgres-portability change moved the POS item-group filters to the query builder
(item.item_group.isin(...)) and frappe.get_all(["name","in",...]), which escape values
once. get_item_groups() still pre-escaped each name with frappe.db.escape(), so the
names were escaped TWICE -> `item_group IN ('''Products''')`, matching nothing. Any POS
Profile that restricts item groups returned ZERO items, on both MariaDB and Postgres.
Return raw names; the parameterized callers escape them correctly. (get_parent_item_group
also returned the quoted literal before this fix.) Add a regression test: a POS Profile
restricted to an item group must still surface that group's items — it returns 0 before
the fix and passes after, on both engines.
commission_rate is no_copy so it is not carried on Duplicate/amend, but the
mapper also skips no_copy fields, leaving the mapped Delivery Note / Sales
Invoice showing 0 commission until saved (it only re-fetched from the sales
partner on save). Map commission_rate explicitly in the SO->DN, SO->SI and
DN->SI mappers so it carries over immediately; Duplicate still does not copy
it.
set_lead_name fell through to email_id.split('@') when a lead had no name,
company or email but ignore_mandatory was set (e.g. data import), raising
AttributeError on a None email. Only derive from email when one exists; the
lead name is then left blank, as intended for that path.
* feat: capitalize full actual charge on stock items only for Purchase Invoice
Extends #56102 (Purchase Receipt) to the Purchase Invoice GL: an actual
valuation charge (e.g. Freight) flagged 'Allocate Full Amount to Stock Items'
is fully capitalized onto stock/asset items only; when unchecked, only the
stock items' share of a spread-across-all-items charge is capitalized.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
* test: aggregate GL rows per account in PI freight test
---------
Co-authored-by: Claude Opus 4.8 <noreply@anthropic.com>
Across Sales Order, Delivery Note, Sales Invoice and POS Invoice, the
Commission section's commission_rate, total_commission and
amount_eligible_for_commission are sales-partner commission fields:
- depends_on eval:doc.sales_partner so they only show when a Sales Partner
is set;
- no_copy so a duplicated/amended document does not carry a stale commission
rate or computed commission amount (the sales partner itself still copies).
depends_on is client-only, so the server-side commission calculation is
unchanged. Add a Sales Order test for the no_copy behaviour.
The previous string comparison (str(raw) != str(cleaned)) rewrote every
whole-number row ('20' vs '20.0'), turning a targeted cleanup into a
full-table rewrite on Sales Team. Skip rows already holding a plain numeric
string and only fix NULL / empty / non-numeric / percent-sign values.
commission_rate was a free-text Data field on the Sales Person master and the
Sales Team child, storing percentages as strings. Convert both to Percent.
A pre_model_sync patch sanitizes the existing values first (empty / NULL /
non-numeric -> 0, others normalised via flt) so the Data -> Percent column
change casts cleanly under strict SQL mode, where Percent is a NOT NULL
decimal column. The patch is idempotent and avoids db-specific SQL so it works
on both MariaDB and Postgres.
Drop the dead 'if coupon:' guard (get_doc would have thrown) and collapse the
duplicate increment branches into a single exhausted-check plus increment.
No behaviour change.
Use do_not_submit=1 for the service-item and reserve-warehouse validation
tests; they only exercise in-memory validation methods, so submitting the
Subcontracting Order is unnecessary.
The over-order check sums the same item across multiple order rows. Add a
test where one item is split into two Sales Order rows against the same
blanket order and together exceed its quantity.
Add tests for the previously-untested branches of validate_coupon_code
(not-yet-valid, expired, maximum-use exhausted) and update_coupon_code_count
(releasing a use on cancel, and rejecting use beyond the maximum). Both
functions are now fully covered.
The parent Commission section (Sales Partner commission) and the Sales Team
table (Sales Person contribution) drive separate logic in
SellingController.calculate_commission / calculate_contribution. Add
integration tests on Sales Order:
- sales partner commission: total_commission = eligible amount * rate / 100,
and the commission-rate 0..100 bound;
- sales-person allocated_amount tracks amount_eligible_for_commission
(grant_commission gated), not gross net_total, plus the incentive math;
- the allocated-percentage must-total-100 throw;
- rejection of a disabled sales person.
The method (cyclomatic complexity C/14) mixed packed-item separation, SRE
creation and packed-item reservation. Extract _extract_packed_item_details,
_packed_items_to_reserve and _reserve_packed_items (verbatim moves). Drops
C/14 -> A/3; no C-rank function remains in the module. No behaviour change
(stock-reservation, product-bundle and pick-list reservation suites green).
auto_close_opportunity fell back to 15 days in code when the CRM Setting was
blank (and its docstring still said 7). The field already defaults to 15, so
read the value straight from CRM Settings and add a patch to backfill 15 for
existing sites that left it blank, keeping the same auto-close schedule.
- assert cost-of-shipments against the PO base_amount instead of a
hardcoded total, so it holds when conversion_rate != 1
- guard the idempotency test's fixed scorecard name against leftovers
- clarify that the eval-statement zero/None substitution is a truthiness check
calculate_service_costs paired the service_items and items child tables
by list index, which breaks if the tables are not index-aligned (e.g.
populate_items_table skips a service item with zero available qty),
assigning the wrong service cost or raising IndexError. Match by
purchase_order_item instead, and guard against division by zero qty.
Adds a regression test asserting service costs follow purchase_order_item
regardless of table ordering.
Add tests for get_lead_details and the Lead <-> Prospect lifecycle: editing a
lead syncs into its Prospect Lead row, and deleting the only lead of a
prospect removes the prospect. Lead controller coverage 65% -> 74%.
Covers previously untested Subcontracting Order paths:
- a Subcontracting Order requires a subcontracting Purchase Order
- service items must be non-stock items
- a supplied item's reserve warehouse must differ from the supplier warehouse
- the Subcontracting Receipt mapper applies BOM process-loss to the received qty
Add tests for get_item_details, auto_close_opportunity (a stale Replied
opportunity is closed, a recent one is not) and the Opportunity -> Prospect
opportunity sync. Opportunity controller coverage 62% -> 80%.
set_expired_status passed filters= and fieldname= kwargs that
frappe.db.set_value does not accept, so the daily scheduled task threw
TypeError on every run and quotations were never marked Expired. Pass
the filter dict as the positional docname argument, and scope it to
submitted documents so draft quotations aren't wrongly expired (matching
the selling Quotation behaviour).
Adds coverage for valid-till validation, the expiry task, and the
RFQ quote-status round-trip on submit/cancel.
declare_enquiry_lost had almost no coverage. Add tests that marking an
Opportunity as lost records the lost reasons, competitors and detailed
reason and sets status to Lost, and that it is blocked when an active
(submitted) Quotation exists.
The Patch Test starts the full bench (incl. workers) and then runs migrate.
Migrate enqueues orphan-link cleanup jobs (delete_dynamic_links) that the
workers pick up and process while migrate is altering tables, which
intermittently fails with MySQL 1412 'Table definition has changed, please
retry transaction'.
Start every bench process except the workers during migrate, so nothing
consumes the queue mid-migrate. Redis and the other services stay up; the
queued jobs just wait.
Address Greptile review:
- customContext.files scope was **/*.py only, so Query Report SQL in .js/.sql/report .json
files didn't get the guide attached as context (the global instructions still applied).
Widen to .py/.js/.sql/report **/*.json.
- The guide's HAVING-alias rule said "with no GROUP BY"; PostgreSQL rejects a SELECT-alias in
HAVING regardless of GROUP BY. Reworded to match (repeat the expression, or move a
non-aggregate predicate to WHERE).
The PostgreSQL server-test job is label-gated, so until it is required the Greptile
PR-review bot is the always-on guard against cross-engine breaks. Extend
.greptile/config.json with `instructions` (and a `customContext` reference to a new
guide) so every review flags new/changed queries that would error on PostgreSQL or
silently diverge from MariaDB, under the prime rule that MariaDB output must not change.
- .github/POSTGRES_COMPATIBILITY.md — the catalogue the bot (and contributors) follow:
hard breaks (loose GROUP BY, MySQL-only funcs, UPDATE..JOIN, HAVING-on-alias,
DISTINCT+ORDER BY, single-quoted alias, varchar bitwise OR, capital identifiers,
set_value(Check,bool)), silent divergences (text case-sensitivity, name-lookup case,
empty-string↔NULL, NULL ordering, ORDER BY..LIMIT 1 tiebreakers, integer division,
distinct-drops-ORDER-BY-on-PG + casefold sorting, function-rewrite parity, UnixTimestamp
TZ), the GROUP BY row-count trap (Max()-wrap vs add-to-GROUP-BY; FD-by-source-table),
the InFailedSqlTransaction/savepoint rule, and the false positives NOT to flag
(.like→ILIKE, ifnull/backtick/LOCATE/REGEXP auto-translation, MariaDB-changing tiebreakers).
- Existing disabledLabels and frappe/frappe context are preserved.
The `skip_transfer` and transfer branches of `validate_manufacture` ran the
same per-item validation loop — look the row up or throw "not a part of",
check overconsumption, guard against duplicates, record — differing only in
the data source (SCIO Received Item vs Work Order Item), the available-qty
basis, the source-warehouse check (skip_transfer only) and the message text.
Split each branch into a small method that builds a normalised
`{item_code: {consumed_qty, available_qty}}` lookup, and share the loop via
`_validate_customer_provided_consumption`. Branch-specific throw messages are
passed as callbacks so the user-facing strings (and their translations) are
unchanged, and the order in which checks fire is preserved. Also drops the
unused `name` column from the skip_transfer query.
Adds a test for the non-skip-transfer manufacture flow (Material Transfer for
Manufacture -> Manufacture), which exercises the Work Order branch that the
existing suite — all of whose manufacture tests set skip_transfer=1 — never
covered. Full subcontracting-inward suite passes on MariaDB and PostgreSQL.
`get_fg_reference_names` built its LIKE filter with old-style
`"%%%s%%" % txt`. Use an f-string (`f"%{txt}%"`) for readability; the value
is still passed as a parameterised filter, so behaviour is unchanged.
Each new child row was given `idx=frappe.db.count(...) + 1`, issuing a count
query per inserted row across three insert loops (received items on receipt,
self-procured RM on manufacture, secondary items on manufacture). Compute the
starting index once before each loop and increment a local counter, producing
the same idx sequence with a single count query.
`update_inward_order_received_items_for_manufacture` unpacks
`zip(*item_code_wh.keys(), strict=True)`. When the manufacture entry has no
raw-material rows (all rows are finished/secondary/scrap), `item_code_wh` is
empty and the unpack raises `ValueError: not enough values to unpack`.
Return early when there are no such rows, mirroring the `if secondary_items:`
guard already present in `update_inward_order_secondary_items`.
In `validate_manufacture`, `customer_warehouse` is read only inside the
`skip_transfer` branch but was fetched unconditionally, wasting a lookup on
the non-skip-transfer path. Move it inside the branch that uses it.
`validate_material_transfer` ran the `Work Order Item` query and rebuilt
`wo_item_dict` inside the per-item loop, even though both depend only on
`self.work_order`. For an entry with N customer-provided rows that meant N
identical queries. Build the lookup once before the loop.
`validate_manufacture` already builds the analogous dict once up front, so
this also aligns the two methods.
In `update_inward_order_item`, the walrus assignment `scio_item_name :=` is
already part of the truthy `if` condition, so the nested `if scio_item_name:`
is always true. Remove it and dedent the body.
`validate_delivery_on_save` imported `pypika.terms.ValueWrapper` inside its
per-item loop, re-running the import on every iteration. Move it to the
module-level imports.
The "exceeds quantity available" throw in `validate_manufacture` passes a
third positional arg (`item.transfer_qty`), but the message only has `{0}`
and `{1}` placeholders, so `str.format` silently discards it. Remove the
dead argument; no behaviour change.
`validate_manufacture` builds its "Target Warehouse for Finished Good must
be same as Finished Good Warehouse ..." message with placeholders `{1}` and
`{2}`, but only passes two positional args (indices 0 and 1). `str.format`
raises `IndexError: Replacement index 2 out of range` instead of rendering
the message, so a user who sets the wrong FG target warehouse gets an opaque
traceback rather than the intended validation error.
Renumber the placeholders to `{0}` and `{1}` to match the args.
get_account_columns fetched the dynamic expense / unrealized-P&L account lists with
frappe.get_all(distinct=True, order_by=...). frappe silently drops ORDER BY for
distinct queries on postgres (db_query), so the generated account columns came back
in arbitrary order on Postgres while MariaDB kept them ordered — a cross-engine
parity gap (the sibling Sales Register had already moved to a python sort).
Sort the lists in python with key=str.casefold (dropping the ignored order_by) so the
column order is deterministic, case-insensitive (matching MariaDB's collation), and
identical on both engines. Add a regression test with two case-colliding expense
account names asserting the casefold column order on both engines.
get_account_columns sorts the dynamic income / unrealized-P&L account columns with
python sorted() (the original raw SQL used ORDER BY, which frappe drops for distinct
queries on postgres). Plain sorted() is case-sensitive (ASCII), so it reordered the
columns versus the pre-effort MariaDB output, whose ORDER BY ran under the
case-insensitive utf8mb4 collation.
Sort with key=str.casefold so the column order matches MariaDB's collation and is
identical on MariaDB and Postgres. Add a regression test with two case-colliding
account names ("aaa ..." / "ZZZ ...") that fails on case-sensitive sort and passes
after, on both engines.
get_customer_name's Postgres branch extracted the PURE TRAILING digits of the
name (regexp '^.*?(\d*)$'), while the MariaDB branch uses
CAST(SUBSTRING_INDEX(name, ' ', -1) AS UNSIGNED) — the LEADING digits of the last
whitespace token. For a scanned name like "<base> - 3a" MariaDB yields 3 but
Postgres yielded NULL→0, so the next de-duplicated number (and thus the generated
Customer name) diverged between engines.
Make the Postgres branch take the last whitespace token then its leading digits,
mirroring MariaDB exactly ("X - 3a"->3, "X - 1.5"->1, "X - Foo"->0). Add a
regression test with a "<base> - 3a" name asserting the next name is "<base> - 4"
on both engines (it produced "<base> - 1" on the old Postgres regex).
The Postgres-portability change added the Purchase Order Item PK (child.name) to
get_po_entries' GROUP BY. material_request_item is blank for PO lines not sourced
from a Material Request, so a multi-line PO previously collapsed to ONE row per
(PO, blank) on MariaDB but now produced one row PER LINE — changing the MariaDB
row count (and the add_total_row totals).
Group only by (PO, material_request_item) — the pre-effort key — and Max()-
aggregate the other selected columns so the query stays valid on Postgres while
restoring the prior one-row-per-group MariaDB output (per-column arbitrary→
deterministic, row count preserved). Add a regression test with a two-line PO
that fails on the multi-column GROUP BY (2 rows) and passes after (1 row), on
both MariaDB and Postgres.
Add a regression test for the one-row-per-item invariant: a BOM that lists the
same raw item on two lines at different qty must still be counted once in the
report ("# Req'd Items" == 1). The test fails on the pre-fix multi-column GROUP
BY (which split the item into one row per distinct stock_qty -> 2) and passes
after the fix, on both MariaDB and Postgres.
The earlier parity fix aggregated the non-key descriptive columns for the Item
and Customer based-on paths but left Supplier grouping by all three selected
columns (supplier, supplier_name, supplier_group). supplier_name is a stored
per-transaction field, so historical purchase docs holding a divergent value for
the same supplier would split one supplier into multiple rows — diverging from
the original MariaDB output, which grouped by t1.supplier only.
Aggregate supplier_name with Max() and keep only supplier + the FD master column
supplier_group in GROUP BY, restoring one row per supplier on both engines.
Add regression tests for the Supplier (purchase) and Customer (sales) paths that
assert a single row per key even when stored descriptive fields diverge; both
fail on the pre-fix multi-column GROUP BY and pass after the fix, on MariaDB and
Postgres.
frappe.local is request-scoped, not test-scoped — it survives
across unit-test methods. Two tests calling get_standard_rated_
expenses_total({"company": "_Test Company UAE VAT"}) hit the
same cache key, so the second test (foreign-currency PI, expected
917.5) was seeing 250 carried over from the first.
Short-circuit @_cached on frappe.flags.in_test so each test method
queries fresh. Production callers run one execute() per request and
have the cache cleared at the top of that call, so the optimisation
still applies there.
#56192 made the trends queries Postgres-strict-GROUP-BY-valid by widening based_on_group_by
to include the selected descriptive columns. For Item it added t2.item_name, for Customer
t1.territory (and customer_name) — but item_name is an editable per-line field and territory an
editable per-document field, not functionally dependent on the item_code/customer key. On MariaDB
(ONLY_FULL_GROUP_BY off) this SPLITS the single row per key into one row per distinct
(key, item_name)/(key, territory), so a customer transacting across two territories (or an item
with an edited item_name) now shows duplicate rows with fractured per-period subtotals.
Group by the KEY only and aggregate the non-key descriptive columns with Max(): one row per
based-on key (identical to the pre-#56192 MariaDB output) and still Postgres-valid. Supplier
columns are master-joined / fetch-locked (functionally dependent) so they stay unchanged.
The JS button only renders the Generate/Retry action for Draft and
Error; the REST endpoint, however, still let an authenticated caller
silently overwrite the attached CSV on a Generated FAF. Tighten the
server-side guard to match the UI lifecycle so the destructive
action has to be explicit (delete and create a new doc to regenerate).
- Gate generate_faf() and mark_as_submitted() on write permission so
REST callers without write access can no longer trigger state
changes via the whitelisted endpoints.
- Drop test_generate_faf_excise_not_yet_implemented; the Excise file
type is no longer a valid Select option, so doc.insert() now fails
before generate_faf() is reached.
- Stream GL Entry rows in pages of GL_PAGE_SIZE to bound memory on
multi-year exports against large companies; running balance,
account-name cache, and totals carry across batches so output is
byte-identical to the single-fetch implementation.
- Move the VAT 201 helper cache from a module-level dict to
frappe.local so concurrent requests on threaded workers no longer
race or leak data across users.
ERPNextTestSuite already calls frappe.db.rollback() in its base
tearDown; overriding (even with the same call) trips the
semgrep "Dont-override-teardown" rule.
- Updated the UAE VAT 201 report HTML to improve layout and styling for better readability.
- Modified the JavaScript for the UAE VAT 201 report to include additional formatting for VAT legends.
- Enhanced the Python logic in the UAE VAT 201 report to include caching for performance improvements and added calculations for net VAT due.
- Introduced a new UAE VAT Register report with filters for company, date range, document type, and item-wise details.
- Implemented SQL queries in the UAE VAT Register to fetch sales and purchase invoice data based on selected filters.
- Added a new field for "Company Name in Arabic" in the Company doctype for compliance with local regulations.
#56196's Postgres GROUP BY fix added bom.quantity, bom_item.stock_qty and
bin.actual_qty to the GROUP BY. bom.quantity and bin.actual_qty are pinned to a
single value by the WHERE/join, but a BOM may list the same item_code on multiple
lines with different stock_qty (validate_materials does not dedupe), so grouping by
stock_qty SPLITS the row and changes req_items/instock on MariaDB for such BOMs.
Aggregate build_qty with Max() and group by item_code only: one row per item_code
(identical to the pre-#56196 single-line result; deterministic for duplicate lines),
and Postgres-valid. MariaDB output is unchanged for the common single-line case and
its row count is restored for the duplicate-line case.
- semgrep: annotate the source-reading open() with # nosemgrep for the
frappe-security-file-traversal rule (dev-only lint tool; path comes from pre-commit,
not user input).
- bool-scan: only inspect the field *value* arg (db_set args[1]/dict args[0];
set_value args[3]/dict args[2]) so a positional update_modified=False
(e.g. db_set('f', 0, False)) no longer false-positives.
- # pg-ok: also honour the annotation on a multi-line call's closing paren line
(scan one line past the node's end).
Remove erpnext/tests/test_postgres_compat.py (and its pre-commit exclude); a unit
test for the dev-tooling lint helper isn't needed in the app test suite.
The Postgres test job is label-gated, so it does not run on every PR. This adds an
always-on pre-commit hook that statically flags the *mechanical* breaks: MySQL-only
functions (timestamp(date,time), timediff, str_to_date, date_format/add/sub,
group_concat, period_diff, SQL IF()), SHOW INDEX/TABLES/COLUMNS, single-quoted
aliases, UPDATE..JOIN, interpolated/f-string SQL carrying MySQL-isms,
set_value/db_set(<Check>, bool), and MySQL SHOW INDEX result keys.
It deliberately does NOT flag the framework auto-translations (ifnull->coalesce,
backtick/locate/REGEXP, .like()->ILIKE) nor the *semantic* divergences (loose GROUP
BY, case-sensitive ==/IN, NULL ordering, tiebreakers) — those need the test suite,
which remains the backstop. AST + structure-gated regex keep false positives near
zero (docstrings and prose skipped); '# pg-ok' exempts intentional MariaDB-only
branches. Scoped to erpnext/ excluding patches/. Includes a unit test of the checker.
The Postgres CI site only listed erpnext in install_apps, so the payments app
(fetched and built by install.sh via 'bench get-app payments') was never
installed on the site — leaving 'tabPayment Gateway' absent. test_payment_request
(and other payment-gateway-dependent tests) then errored on Postgres with
'relation "tabPayment Gateway" does not exist', while MariaDB passed because its
site_config already lists ["payments", "erpnext"]. Match that ordering for parity.
Postgres fsyncs on every commit by default, which dominates a commit-heavy test suite.
Turn off synchronous_commit/fsync/full_page_writes on the throwaway CI database (reload-
time settings, no restart). MariaDB CI is unaffected (DB != postgres).
The MariaDB job is named 'Python Unit Tests', and 'Python Unit Tests (1..4)' are the
required status checks on develop. Naming the Postgres matrix job the same made its
checks report under those required contexts, effectively gating every (labelled) PR on
Postgres. Rename it to 'Postgres Unit Tests' so its contexts are distinct and the
workflow stays non-required until we deliberately add it to branch protection.
Bring the Server (Postgres) workflow in line with Server (MariaDB) internals while
keeping it opt-in for now: pull_request runs still require the 'postgres' label, but the
job now uses the full 4-container matrix (was 1), adds the nightly schedule /
workflow_dispatch / repository_dispatch triggers (which always run), and uploads
coverage. Builds ERPNext against frappe `develop` (PostgreSQL query-builder/ORM support
is merged there), so no fork override is needed.
The ERPNext server suite now passes on PostgreSQL and MariaDB from a single codebase;
flipping this to run on every PR / become a required check is a later, separate step.
get_timeline_data uses UnixTimestamp(posting_date); on Postgres that is the date's
midnight epoch in the DB session timezone, which can sit up to a day ahead of the
Python time.time() instant when the app timezone is ahead of UTC. The strict
'<= now' upper bound is therefore flaky on Postgres. Allow a day of slack on the
upper bound; MariaDB's UNIX_TIMESTAMP stays <= now so its pass/fail is unchanged.
create_bank_account() inserts a bank Account and swallows DuplicateEntryError
('bank account same as a CoA entry'). On Postgres the failed insert aborts the
transaction, so the rest of company setup ran against a poisoned transaction.
Take a savepoint and roll back to it in the handler. No-op on MariaDB.
add_bank_accounts() inserts a Bank Account per Plaid account in a loop. On a
duplicate the bare insert raises UniqueValidationError, which on Postgres aborts
the whole transaction; the handler only msgprint'd and continued, so the next
iteration's insert died with InFailedSqlTransaction. Wrap each iteration in a
savepoint and roll back to it in the handlers (the pattern frappe#40075 prescribes
after dropping the blanket per-insert savepoint). No-op on MariaDB.
SHOW INDEX is MySQL-only and errored on Postgres. Add a db-aware helper that reads
the leading index column from pg_index on Postgres and keeps SHOW INDEX on
MariaDB; both assert the field is the first column of some index.
The deliberate UniqueValidationError when re-adding a barcode aborts the
transaction on Postgres, so the next frappe.get_doc() failed with
InFailedSqlTransaction. Wrap the expected-failure save in a savepoint and roll
back to it. No-op on MariaDB.
The deliberate UniqueValidationError from the second Bin insert aborts the
transaction on Postgres, so the following _create_bin() (which takes its own
savepoint) failed with InFailedSqlTransaction. Wrap the expected-failure insert in
a savepoint and roll back to it, mirroring _create_bin's 'preserve transaction in
postgres' pattern. No-op on MariaDB.
frappe.db.set_value(..., "reconciled", True) renders SET reconciled=true; the
column is smallint, which Postgres rejects (DatatypeMismatch). MariaDB coerces the
boolean to 1. Pass 1 so both engines store the same value.
_bom_contains_item() lowercased the item name and then reused that lowercased
value as a doc name in frappe.db.get_value("Item", item, "variant_of"). Doc
names are case-sensitive on Postgres, so the lowercased name matched no row,
variant_of came back NULL, and a Work Order for a variant item built from the
template's BOM was wrongly rejected with 'BOM ... does not belong to Item ...'.
Keep the original case for the Item lookup; the comparisons stay case-insensitive.
MariaDB is unchanged (its name lookup was case-insensitive either way).
It never references `self`. The deterministic-serial-value test added in #56249
called it as `get_incoming_value_for_serial_nos(None, sle, serial_nos)` — passing
None for self, which is fragile: a future `self.*` access would fail with an opaque
AttributeError. Declaring it @staticmethod makes the call honest
(`get_incoming_value_for_serial_nos(sle, serial_nos)`) and is backward compatible —
the method has no in-repo callers besides that test, and any `self.`-style call still
binds correctly to a staticmethod.
Addresses Greptile review feedback on #56249.
The repo-wide query audit fixed runtime/source queries, but test files carry their
own raw SQL helpers that were never swept and only fail when the suite runs on
Postgres. Port the staging branch's already-green fixes for them:
- timestamp(posting_date, posting_time) (raw + qb Timestamp) -> posting_datetime /
CombineDatetime (test_stock_ledger_entry, test_stock_balance, test_utils)
- HAVING <select-alias> -> qb .having(<expr>) (test_asset_capitalization, test_purchase_order)
- capital-cased identifiers ("Status", "Name") -> lowercase (test_delivery_note,
test_purchase_order, test_employee)
- raw GL/SLE select helpers -> frappe.get_all / qb, with order-independent
comparisons where account ordering is collation-dependent across engines
(test_purchase_invoice, test_sales_invoice, test_payment_entry, test_asset,
test_purchase_receipt, test_payment_request, test_repost_accounting_ledger,
test_journal_entry)
All changes are test-only and behaviour-identical on MariaDB (lowercase column names
resolve the same; posting_datetime == timestamp(posting_date, posting_time); HAVING on
the expression is the same computation). Verified: the heavy modules pass on both
MariaDB and Postgres, and MariaDB output is unchanged.
Bugs surfaced while writing coverage for the scorecard engine:
- update_standing treated every band as [min, max), leaving the global
ceiling open, so a perfect score (100) - including the no-period
fallback - mapped to no standing. Make the top band inclusive of its
upper bound.
- get_on_time_shipments counted PR lines where qty exactly matched the PO
line, so on-time deliveries split across partial receipts were never
counted while still inflating late shipments (and could push
get_late_shipments negative). Count fully-on-time PO lines instead,
keeping units consistent with get_total_shipments.
- validate_standings now rejects inverted bands (min >= max) and checks
band continuity directly instead of relying on fragile float-equality
accumulation.
- Remove dead 'crit.score = 0' after frappe.throw in calculate_criteria.
The report's batch_no filter used an exact `==`, which is case-sensitive on Postgres -- a
differently-cased batch_no missed Job Cards that MariaDB (case-insensitive collation)
matches. Add a dedicated batch_no branch wrapping both sides in Lower() (keeping the exact
match, not a substring like serial_no): MariaDB result is unchanged, Postgres now matches.
The serial-no filter used serial_batch_entry.serial_no.isin(serial_nos), which is
case-sensitive on Postgres -- a differently-cased serial no missed Serial and Batch
Entry rows that MariaDB (case-insensitive collation) matches (the OR'd regexp branch
only covers the legacy Stock Ledger Entry.serial_no text, empty for bundle-tracked
serials). Lower() both sides: MariaDB result unchanged, Postgres now matches too.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_item_codes_by_attributes compared Item Variant Attribute `attribute`/`attribute_value`
with raw equality/IN, which is case-sensitive on Postgres -- a differently-cased website
filter value missed variants that MariaDB (case-insensitive collation) matches. Lower()
both sides: MariaDB result is unchanged (already case-insensitive), Postgres now matches too.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
test_stock_entry.get_sle ordered by `timestamp(posting_date, posting_time)`, a
MySQL-only two-arg function that errors on Postgres ("function timestamp(date, time)
does not exist"), so every test using get_sle (test_fifo, test_stock_entry_qty, ...)
failed to run on Postgres. Order by the precomputed `posting_datetime` column instead
(identical value on MariaDB, valid on both engines).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
batch.get_batches(item_code, warehouse, ...) was added by #55647 and has no callers
anywhere in erpnext, frappe, or payments (not whitelisted, not referenced from JS/hooks).
It is also obsolete: it joins Stock Ledger Entry on `batch_no`, which the Serial and
Batch Bundle system no longer populates, so it returns nothing even on MariaDB. Its
query was additionally Postgres-invalid (GROUP BY batch_id with ORDER BY expiry_date/
creation -> GroupingError, since batch_id is not the primary key).
Remove the dead function (and its now-unused CurDate/Sum import) rather than fix a query
that nothing can reach. Live batch-quantity lookups go through get_batch_qty() /
get_auto_batch_nos(), which use the bundle model.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_timesheets_list selected `timesheet.sales_invoice | detail.sales_invoice`,
intending COALESCE (pick the parent timesheet's invoice, else the detail's) -- the
original raw SQL was COALESCE(ts.sales_invoice, tsd.sales_invoice). pypika's `|` is a
bitwise OR, not a coalesce:
- Postgres: `varchar | varchar` -> "operator does not exist" (hard error).
- MariaDB: bitwise OR coerces the operands to integers; with a NULL detail invoice the
result is NULL, so the portal showed no invoice even when the timesheet was billed.
Replace with Coalesce(table.sales_invoice, child_table.sales_invoice).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
test_index_creation used `frappe.db.sql("show index from tabItem")` and the MySQL-only
result key "Column_name". "SHOW INDEX" errors on Postgres, so the test could not run
there. Use the db-agnostic frappe.db.get_column_index("tabItem", column) (checking both
unique and non-unique single-column indexes) instead.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
test_index_exists used `frappe.db.sql("show index from tabBin ...")`. "SHOW INDEX"
is MySQL-only syntax and errors on Postgres (syntax error at "from"), so the test
could not run there. Use the db-agnostic frappe.db.has_index("tabBin",
"unique_item_warehouse") instead.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
rfq_transaction_list had two defects introduced when it was converted to the query
builder:
1. `party.supplier == party[0]` compared supplier to a column literally named "0"
(a stray index on the DocType, not the intended `parties[0]` value). This renders
as `supplier = \`0\`` / `supplier = "0"` and errors on BOTH engines
(MariaDB: Unknown column '0'; Postgres: column "0" does not exist), so the
supplier portal RFQ list was completely broken.
2. SELECT DISTINCT ordered by `creation`, which is not in the select list. Postgres
rejects this ("for SELECT DISTINCT, ORDER BY expressions must appear in select list").
Compare against `parties[0]` and add `creation` to the select list.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The "Lost Quotations %" column computed Count(distinct) / total_quotations * 100,
where both operands are integers. Postgres does integer division on int/int, so any
group that is a strict minority of the total truncated to 0 (e.g. 1 of 4 -> 0%);
MariaDB always divides as decimal. Multiply by 100.0 before dividing so the division
is done in floating point on both engines.
The "Lost Value %" column already divided Sum(Currency)/Sum(Currency) (numeric), so it
was unaffected; left unchanged.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
When Accounts Settings -> general_ledger_remarks_length is set, the GL report
adds `substr(remarks, 1, n) as 'remarks'` to its raw SQL. Postgres treats a
single-quoted column alias as a string literal and raises a syntax error, so
the General Ledger report is broken on Postgres whenever that setting is on.
Use a bare alias (`as remarks`). substr() itself is portable.
Adds a test that sets general_ledger_remarks_length and runs the report,
asserting it executes (and returns rows) on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_vendor_invoice_query filtered unclaimed invoices with
.having(unclaimed_amount > 0), but the query has no GROUP BY/aggregate and
unclaimed_amount is a SELECT alias. Postgres rejects HAVING on a SELECT alias
(and HAVING without GROUP BY on a non-aggregated column); MariaDB allowed it.
Move the threshold into WHERE on the underlying expression.
Behaviour is identical on MariaDB (same rows); fixes a hard error on Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
update_variant_attribute_values (propagates renamed Item Attribute Values to
variant items) used a qb UPDATE ... JOIN. Postgres has no UPDATE..JOIN syntax,
so renaming an Item Attribute Value errored on Postgres.
Rewrite as a correlated UPDATE that restricts to variant items via a subquery
on the parent (item_variant_table.parent.isin(variant Items)) instead of
joining the Item table. MariaDB behaviour is unchanged.
Covered by the existing test_item.test_rename_attribute_value_updates_variants
and test_swapped_attribute_value_renames_update_variants, which errored on
Postgres before and now pass on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_timeline_data grouped Timesheet Detail by Date(from_time) but selected
UnixTimestamp(from_time) (the full timestamp, ungrouped). MariaDB
arbitrary-picks a row's timestamp; Postgres rejects it ("must appear in the
GROUP BY clause"), so the Project timeline (calendar heatmap) is broken on PG.
Select UnixTimestamp(Date(from_time)) — the day's epoch — which is the
timeline key and matches the GROUP BY. CurDate() - Interval(years=1) is
portable and kept as-is.
Adds a test (no coverage existed) that records a timesheet against a project
and asserts get_timeline_data returns day-bucketed counts, on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_purchase_details grouped Purchase Order Item by (item_code, warehouse)
while selecting `qty` ungrouped/unaggregated. MariaDB arbitrary-picks one
row's qty; Postgres rejects the query ("must appear in the GROUP BY clause"),
so the report is broken on Postgres.
Sum the qty per item+warehouse ({"SUM": "qty"}). The column is the "Arrival
Qty" (quantity on order arriving) display figure; summing the open PO lines is
the meaningful planning number, and is deterministic vs MariaDB's arbitrary
single-line pick (which only differed when an item+warehouse had multiple open
PO lines).
Adds a test (no test file existed) that creates a Work Order plus two PO lines
for a BOM raw material and asserts the report runs and reports arrival_qty = 7
(3 + 4), on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_used_alternative_items built its WHERE with f-string interpolation of
subcontract_order / subcontract_order_field / work_order (a SQL-injection risk)
and used a raw implicit comma cross-join. Convert to frappe.qb with an
inner_join on sted.parent == ste.name and parameterised conditions. The raw
SELECT listed sted.conversion_factor twice; the qb version selects it once.
Engine-portable and MariaDB-identical.
Surgical re-apply: the rest of stock_entry.py (the services/ package layout and
other develop-only logic) is untouched.
Adds a test that substitutes an alternative item in a work order's transfer
entry and asserts get_used_alternative_items returns the mapping, on MariaDB
and Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the raw frappe.db.sql statements in Item to frappe.qb / ORM:
validate_barcode duplicate check (-> frappe.get_all), stock_ledger_created
(-> frappe.db.exists), update_item_price + the BOM/BOM Item/BOM Explosion
description updates + check_stock_uom_with_bin's Bin UOM update (-> frappe.qb
.update), on_trash Bin/Item Price deletes (-> frappe.db.delete),
check_stock_uom_with_bin's bin lookup (-> frappe.get_all with or_filters), and
get_uom_conv_factor's self-join (-> frappe.qb).
The one genuine Postgres break is validate_duplicate_item_in_stock_reconciliation:
its raw query used `HAVING records > 1`, referencing the SELECT alias, which
Postgres rejects. The qb version uses `HAVING Count("*") > 1`.
Surgical re-apply (not a whole-file port): develop's opening-stock-reconciliation
flow (set_opening_stock / create_opening_stock_reconciliation /
make_opening_stock_entry) is preserved, and get_timeline_data keeps develop's
CurDate()-Interval form (valid on both engines), so the Interval/CurDate/
SerialBatchCreation imports are retained.
Verified: test_item 38/38 on MariaDB. Added a merge-rename test exercising the
HAVING query (validate_duplicate_item_in_stock_reconciliation) which passes on
MariaDB and Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert four raw frappe.db.sql statements to frappe.qb:
- set_as_cancel (UPDATE -> frappe.qb.update)
- the invalid-serial-no incoming_rate lookup
- get_valuation_rate's last-valuation lookup
- get_future_sle_with_negative_qty
The serial-no comparisons (invalid-serial lookup and the get_stock_ledger_entries
condition builder, which stays raw) are wrapped in lower()/Lower() so serial
matching is case-insensitive on Postgres too -- MariaDB's collation already is,
so this is a no-op there. Deterministic creation/name tiebreakers are added to
the "ORDER BY posting_date DESC LIMIT 1" lookups so Postgres picks the same row
MariaDB did.
Surgical re-apply (not a whole-file port): develop's reposting valuation-recalc
clause (`recalculate_valuation_rate`) in update_entries_after and the
already-shipped Min()-wrapped get_items_to_be_repost GROUP BY are preserved. The
dynamic-condition / row-locking raw queries (get_previous_sle,
get_stock_ledger_entries builder, get_future_sle_with_negative_batch_qty, the
qty_shift UPDATE) are intentionally left raw.
Verified: full test_stock_ledger_entry suite 22/22 on MariaDB; added focused
tests for set_as_cancel / get_valuation_rate / get_future_sle_with_negative_qty
that pass on MariaDB and Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
deduplicate_similar_repost used a raw UPDATE with the MySQL-only two-arg
TIMESTAMP(posting_date, posting_time) constructor, which is invalid on Postgres.
Convert the UPDATE to frappe.qb and replace TIMESTAMP() with CombineDatetime
on the column (portable, and preserves the original NULL semantics so rows with
a NULL posting_time stay excluded); the right-hand side is this document's own
always-set posting datetime, computed in Python via get_combine_datetime to
avoid wrapping literals in a SQL datetime function.
Surgical re-apply: develop's recalculate_valuation_rate field /
_recalculate_valuation_rate method / repost() branch are left intact.
The existing test_repost_item_valuation.test_deduplication directly exercises
this UPDATE; it errors on develop's Postgres and now passes on MariaDB and
Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The *_trends reports (Sales/Purchase Order/Invoice, Delivery Note, etc.) built
raw SQL that is invalid on Postgres:
- `SUM(IF(...))` -> `SUM(CASE WHEN ... ELSE NULL END)` (IF is MySQL-only).
- Loose GROUP BY: each based_on `group by` listed only the key column while the
SELECT also returned name/territory/group/currency columns. Widen the GROUP BY
to include every selected non-aggregated column so the query is valid on
Postgres.
- Add a based_on_key (the first group-by column) for the group-by detail
subqueries, which equate against a single column (a multi-column group_by
spliced into an equality produced malformed SQL on both engines).
Behaviour note: widening the GROUP BY can split one based-on group into multiple
report rows when the snapshot columns (territory, renamed customer/item) differ
across transactions, vs MariaDB's previous one-arbitrary-row-per-group. Grand
totals are unchanged (calculate_total_row); per-group subtotals become
deterministic partial sums. This is the accepted widen-vs-arbitrary-pick
tradeoff.
Adds a test (no test file existed) running Sales Order Trends with a group_by,
exercising the widened GROUP BY / based_on_key / SUM(CASE) on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
make_variant_item_code used a raw frappe.db.sql left join over Item Attribute /
Item Attribute Value. Convert to frappe.qb. The attribute_value comparison
casts the param with cstr() so Postgres does not error on `varchar = numeric`
for numeric attributes (where that side is irrelevant, since numeric_values == 1
already satisfies the OR). MariaDB-identical.
Surgical re-apply: develop's get_attribute_value_renames /
update_variant_attribute_values helpers and the Case import are preserved.
Covered by test_item_variant on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- Asset Movement deletion: raw implicit-join select -> frappe.get_all on
Asset Movement Item (pluck="parent").
- validate_item_type: raw `name in (...)` select -> frappe.get_all with an
`in` filter (pluck="item_code").
Both are engine-portable, MariaDB-identical. Surgical re-apply: develop's
actual-tax distribution rewrite (distribute_actual_tax_amount / get_tax_details)
is preserved (the staging branch predated it).
validate_item_type runs on every Purchase Receipt validation (covered by
test_asset.test_purchase_asset on both engines); the Asset Movement deletion
is covered by the asset cancellation flow.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert eight raw frappe.db.sql search handlers to frappe.qb / frappe.qb.get_query
(which applies user-permission match conditions): employee_query, lead_query,
tax_account_query, bom, warehouse_query, get_batch_numbers, get_purchase_receipts
and get_purchase_invoices. Removes the MySQL-only get_match_cond/get_filters_cond
string building and ifnull usage.
The genuine Postgres break is get_project_name: it used CustomFunction("IF")
which emits a literal IF() (invalid on Postgres). Switch it to Case().
Surgical re-apply (not a whole-file port): develop's case-insensitive
Lower() ordering in item_query and get_project_name is preserved (the staging
branch reverted it), item_query is otherwise left untouched, and the Lower
import is retained.
Existing test_queries tests cover the converted handlers and now pass on
Postgres (test_project_query errors on develop). Adds smoke tests for the
three previously-untested handlers (batch numbers / purchase receipts /
purchase invoices).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- make_gl_entries_on_cancel: raw GL Entry existence select -> frappe.db.exists.
- future_sle_exists: raw GROUP BY count -> frappe.qb Count with Criterion.any,
and get_conditions_to_validate_future_sle builds qb Criterion objects
(warehouse == x & item_code.isin(...)) instead of escaped SQL strings.
Parity-preserving and valid on Postgres. Surgical re-apply: develop's
check_item_quality_inspection fix (`return items if doctype == "Stock Entry"
else []`) is preserved (the staging branch predated and would have reverted
it).
Adds a test asserting future_sle_exists detects a later SLE for the same
item/warehouse on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
validate_returned_items used a raw frappe.db.sql with a string-built column
list (and a separate Packed Item select); get_already_returned_items used a
raw GROUP BY sum. Convert both to frappe.get_all / frappe.qb (Sum(Abs(...))
with an explicit groupby). The qb GROUP BY mirrors the original
`group by item_code, <field>`, so it is parity-preserving (not a behaviour
change) and valid on Postgres.
Surgical re-apply: develop's `is_debit_note = 0` credit-note fix in
make_return_doc is preserved (the staging branch predated and would have
reverted it).
Adds a test (Delivery Note -> sales return) exercising validate_returned_items
and get_already_returned_items on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
daily_reminder/email_sending used raw frappe.db.sql with two portability
and correctness problems:
- The update query selected `progress` and `progress_details` from
`tabProject Update`, but those columns do not exist on the Project
Update doctype, so the query raised on BOTH MariaDB and Postgres
(the function is whitelisted-only, so the bug was latent). Drop the
non-existent columns and the corresponding "Project Status"/"Notes"
cells from the summary table.
- `DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY)` (MySQL-only) and a
`CURRENT_DATE` Holiday lookup are not valid on Postgres.
Convert to ORM: frappe.get_all for Project/Project Update/Project User,
frappe.db.count for drafts, frappe.db.exists for the holiday check, and
add_days(today(), -1) for the date filter. Also str() the frequency in the
message so a NULL/empty frequency (Postgres returns None) does not raise.
Adds a test (the file was an empty stub) that creates a project + an update
dated yesterday and asserts the reminder finds it and runs end to end on
both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The max-allowed-qty Case used `... | ValueWrapper(allow_delivery_of_overproduced_qty)`
where the flag is an int (0/1). Postgres rejects `OR <integer>` ("argument of
OR must be type boolean"). Wrap it in bool() so the literal renders as
true/false. MariaDB behaviour is unchanged.
Surgical: only the bool() wrap is applied; develop's weighted-average rate
logic and the internal/whitelisted status-helper split are left intact (the
staging branch predated both).
Covered by test_subcontracting_inward_order.test_over_production_delivery,
which now passes on Postgres and is unchanged on MariaDB.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The Material Request requested-amount query selects
`Sum(stock_qty - ordered_qty) * mri.rate` -- an implicit aggregate with no
GROUP BY, where mri.rate is neither grouped nor aggregated. MariaDB
arbitrary-picks the rate; Postgres rejects it ("must appear in the GROUP BY
clause"). Wrap the rate in Max(mri.rate) so the SELECT is a pure aggregate.
Behaviour note: for matched MR items with differing rates, Max() picks the
highest (vs MariaDB's arbitrary single rate). The underlying Sum(qty) * rate
is a pre-existing single-rate aggregation; this preserves it under the
accepted arbitrary-pick convention.
Covered by erpnext.accounts.doctype.budget.test_budget
.test_monthly_budget_crossed_for_mr, which now passes on Postgres (it errors
on develop) and is unchanged on MariaDB.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_list_context built the enabled-currency symbol map with a raw
frappe.db.sql select. Convert to frappe.get_all (as_list). MariaDB-identical.
Adds a test asserting the currency-symbol map is built and contains a known
enabled currency, on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_customer_name's Postgres branch used `Substring(Customer.name, r"\d+$")`,
but pypika's Substring is a start/length function, not a regex extractor, so
it raised `TypeError: Substring.__init__() missing 1 required positional
argument: 'stop'` at query-build time. Creating a second Customer with an
existing name therefore failed outright on Postgres.
Extract the trailing digits with regexp_replace + NULLIF + CAST instead. A
non-numeric trailing token strips to an empty string, which NULLIF turns into
NULL so MAX() skips it and COALESCE floors to 0 -- matching MariaDB's
CAST(... AS UNSIGNED) -> 0. MariaDB behaviour is unchanged (its branch is
untouched). Drops the now-unused Substring import.
Adds a test that creates "<name>" and "<name> - 3" and asserts the next
de-duplicated name is "<name> - 4" on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- Replace MySQL-only `ifnull(...)` with `coalesce(...)` in the two
source/second-source percentage subqueries that remain raw (they
interpolate dynamic table/field names).
- zero_amount_refdocs: raw `sql_list` → `frappe.get_all(pluck="name")`.
- update_billing_status: two raw `ifnull(sum(qty), 0)` selects → frappe.qb
`Sum`; an empty result yields None and flt(None) == 0, matching the old
ifnull behaviour.
Behaviour is unchanged on MariaDB. The percentage path (coalesce subquery)
is exercised by test_selling_controller's Sales Order -> Delivery Note
per_delivered test on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_already_delivered_qty used two raw frappe.db.sql sums (Delivery Note
Item, and Sales Invoice Item joined to Sales Invoice) and
get_so_qty_and_warehouse used a raw select. Convert to frappe.qb (Sum) and
frappe.db.get_value. Engine-portable and MariaDB-identical.
Adds a test (Sales Order -> partial Delivery Note) that asserts
per_delivered, exercising get_already_delivered_qty / get_so_qty_and_warehouse
(and the StatusUpdater percentage path) on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
boot_session used raw `frappe.db.sql`, including a MySQL-only
`ifnull(account_type, '')` over Party Type that is invalid on Postgres.
- customer_count: `SELECT count(*)` → `frappe.db.count`
- setup_complete: `SELECT name ... LIMIT 1` → `frappe.db.get_all(limit=1)`
- companies: raw select → `frappe.get_all`, preserving the `:Company`
virtual-doc marker
- party_account_types: `ifnull(account_type,'')` → `frappe.get_all` with a
Python `account_type or ""`, which collapses NULL→'' and ''→''
identically on both engines (handles Postgres storing '' as NULL)
Adds a test (no test file existed) that runs boot_session and asserts the
company list and party_account_types are populated, on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
validate_exising_items() used a raw frappe.db.sql implicit-join to find
variant items using the attribute. Convert it to a frappe.qb inner join
(engine-portable, MariaDB-identical) so it no longer relies on raw SQL.
Only this query is converted; develop's update_variant_attribute_values
on_update hook and its imports are left intact (the staging branch's
whole-file version predated and would have reverted them).
Adds a focused test that creates a variant and asserts validate_exising_items
finds it (the validation only raises if the converted query returned the
variant row). Passes on MariaDB and Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Both get_batchwise_data_from_stock_ledger and
get_batchwise_data_from_serial_batch_bundle select Batch columns
(expiry_date, and item_name when show_item_name is set) while grouping
only by Stock Ledger Entry columns. MariaDB arbitrary-picks the Batch
columns; Postgres rejects the query with "column ... must appear in the
GROUP BY clause".
Add the Batch PK (batch.name) to both GROUP BYs. batch.name is 1:1 with
the grouped batch_no (the join condition), so groups are unchanged and the
result is identical on MariaDB.
The serial-batch-bundle query additionally grouped by ch_table.warehouse
while selecting table.warehouse; group by the selected (SLE) warehouse so
the grouped and selected columns match (also required by Postgres).
Adds a test (no test file existed) that receives batch stock and asserts
the report lists it with the correct balance, exercising the GROUP BY on
both engines (with show_item_name set to force the extra Batch column).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
authorization_control.py used MySQL-only `ifnull()` in its raw rule
lookups (invalid on Postgres) and several raw `frappe.db.sql` selects.
- Replace every `ifnull(...)` with the portable `coalesce(...)` in the
rule-lookup statements that remain raw (they interpolate dynamic
conditions and rely on Frappe's Postgres backtick translation).
- Convert the user/role based_on lookups in validate_approving_authority
and the four value-based lookups in get_value_based_rule to frappe.qb
(Coalesce, isin, and a fresh Employee-designation subquery per use).
Behaviour is unchanged on MariaDB; the queries now run on Postgres.
Adds a test (no test file existed): a not-authorized case that exercises
the based_on + coalesce rule lookups (run as a non-admin user, since
Administrator implicitly holds every role), and a get_value_based_rule
call that exercises all four query-builder lookups.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_item_list() computes build_qty as IfNull(bin.actual_qty * bom.quantity
/ bom_item.stock_qty, 0) while grouping only by bom_item.item_code. The
three operand columns are neither grouped nor aggregated, so MariaDB
arbitrary-picks them but Postgres rejects the query with "column ... must
appear in the GROUP BY clause".
Add bom.quantity, bom_item.stock_qty and bin.actual_qty to the GROUP BY.
The WHERE pins bom/item and the join pins warehouse to single rows (Bin is
unique per item+warehouse), so the result stays one row per item and
MariaDB behaviour is unchanged.
Adds a test (no test file existed) that runs the report against a Work
Order and asserts it is listed, exercising the query on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_data() grouped only by Purchase Order Item while selecting Purchase
Order parent columns. MariaDB allows this loose GROUP BY; Postgres rejects
it with "column ... must appear in the GROUP BY clause".
Add the Purchase Order PK (po.name) to the GROUP BY. po.name is 1:1 with
the already-grouped po_item.name, so groups are unchanged and the result
is identical on MariaDB.
Adds a test (no test file existed) that runs the report and asserts the PO
is listed, exercising the GROUP BY on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_po_entries() grouped only by (Purchase Order, material_request_item)
while selecting other Purchase Order Item columns. MariaDB allows this
loose GROUP BY (arbitrary-picking the extra columns); Postgres rejects it
with "column ... must appear in the GROUP BY clause".
Add the Purchase Order Item PK (child.name) to the GROUP BY so the
selected child columns are functionally determined by a grouped key.
Behaviour note: this is not a MariaDB no-op. When one PO has multiple
items sharing the same/blank material_request_item, MariaDB collapsed
them into one arbitrary row; now there is one row per PO line. The
downstream report already keys rows by purchase_order, so totals are
unaffected and the per-line breakdown is more correct.
Adds a test that runs the report and asserts the PO is listed, exercising
the GROUP BY on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
_query_bom_items / _build_base_bom_items_query / _add_*_item_columns selected
non-grouped columns (idx, item_name, image, project, item-default fields, BOM
Item attributes) alongside `group by item_code` -> arbitrary pick on MariaDB,
GroupingError on Postgres. Wrap them in Max() (Min() for idx, preserving the
original ordering). Every wrapped column is functionally dependent on the
grouped item_code (item attributes / the single BOM's project / one Item
Default per item+company), so Max()/Min() returns exactly the value MySQL
picked arbitrarily -> MariaDB output unchanged.
This was previously shipped in #56008 and reverted with that batch; re-applied
in isolation here. Verified: test_work_order 85/85 on BOTH MariaDB (no change)
and Postgres (was 85/85 failing on this query).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_items_from_manufacture_stock_entry aggregated Stock Entry Detail rows by
item_code while selecting item_name/description/warehouses/etc. (and an orderby
on the non-grouped idx) -> arbitrary pick on MariaDB, GroupingError on Postgres.
Wrap the non-grouped columns in Max() (Min(idx) for the orderby), preserving the
one-row-per-item shape the disassembly expects; an item plays one role with one
uom/warehouse across the WO's manufacture entries, so MariaDB output is unchanged.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_secondary_items_from_job_card selected item_name/description/stock_uom/
bom_secondary_item alongside `group by item_code, secondary_item_type` (and an
orderby on the non-grouped idx) -> arbitrary pick on MariaDB, GroupingError on
Postgres. Wrap the non-grouped columns in Max() (Min(idx) for the orderby);
they are item attributes / the secondary-item BOM link, constant per group, so
MariaDB output is unchanged.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
validate_expense_account SLE existence check -> frappe.db.get_all(limit=1);
get_items_for_stock_reco's two comma-join SELECTs -> frappe.qb inner_joins, with
the correlated Warehouse-subtree EXISTS replaced by a precomputed
warehouses_in_tree subquery + isin and ifnull(disabled,0)=0 -> disabled==0|isnull.
The Item-Default query's `group by i.name` is dropped (sound: validate_item_defaults
enforces one Item Default per (item, company), so the company-filtered query already
returns one row per item; the downstream (item_code, warehouse) de-dup is unchanged).
Same result on MariaDB; valid under Postgres.
Tests: get_items_for_stock_reco Bin branch (stocked item) and Item-Default branch
(default_warehouse, no stock).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_items_to_be_repost selected posting_date/posting_time/creation/posting_datetime
alongside `group_by item_code, warehouse` with no aggregation -> arbitrary pick on
MariaDB, GroupingError on Postgres. Wrap the four columns in `Min()` (earliest row
per item+warehouse, the correct repost-start point; a single voucher's SLEs share
posting_date/time per group -> MariaDB-identical). This is reached by every stock
transaction submit/cancel via repost_future_sle_and_gle, so it unblocks the whole
transaction-heavy stock suite on Postgres (e.g. test_purchase_receipt 105/105).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the `update tabSerial No set purchase_rate ... where name in (...)` to
frappe.qb.update(isin). Also fix the #39 Postgres bug in
set_landed_cost_voucher_amount: `.select(Sum(applicable_charges), cost_center)`
selected a non-grouped column with no GROUP BY (GroupingError on PG) -> wrap it
in `Max(cost_center)` (deterministic representative; per (receipt_document,
receipt_item) the matching LCV items share a cost_center -> MariaDB-identical).
Covered by the existing landed-cost tests.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw `select pr_detail, qty from Purchase Invoice Item` (summed in
Python) with a frappe.qb GROUP BY Sum(qty) per pr_detail, matching the sibling
get_returned_qty_map. Same result on MariaDB; valid under Postgres. Covered by
the existing make_purchase_invoice tests.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_already_received_qty (sum over Purchase Receipt Item, parent != self.name)
and the two Purchase-Invoice-against-receipt existence checks (implicit
comma-joins -> child-table get_all on Purchase Invoice Item, docstatus=1).
Also fixes a pre-existing `self.submit_rv` -> `submit_rv` typo in the (dead)
check_next_docstatus that staging carried forward. Same result on MariaDB;
valid under Postgres.
Tests: get_already_received_qty (parent-exclusion sum) and check_next_docstatus
(blocks on a submitted Purchase Invoice; also locks the typo fix).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The authorized-user backdated-entry guard used MariaDB-only
`MAX(timestamp(posting_date, posting_time))`, invalid on Postgres. Convert to
`Max(posting_datetime)` (the precomputed column) via frappe.qb. Same result on
MariaDB; now valid under Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw nearest-ancestor warehouse-account SELECT with frappe.get_all;
`account is not null and ifnull(account,'')!=''` -> filter ["account","is","set"]
(IS NOT NULL AND != ''), order_by lft desc, limit 1, pluck. Same result on
MariaDB; valid under Postgres. Covered by the existing get_warehouse_account tests.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert get_stock_value_from_bin (comma-join + internal ifnull/warehouse-subtree
fragments -> inner_join + qb subquery), get_latest_stock_qty, get_latest_stock_balance,
get_avg_purchase_rate and get_incoming_outgoing_rate_for_cancel (Case/Abs) to
frappe.qb / get_all. Same result on MariaDB; valid under Postgres.
Tests: get_latest_stock_qty and get_stock_value_from_bin against received stock.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the repost item/warehouse UNION, get_balance_qty_from_sle,
get_reserved_qty (UNION of correlated subqueries -> two qb Sum branches with an
inner_join to Sales Order Item, added in Python; qty!=0 guards the divide and
mirrors the original `qty>=delivered_qty` which on MariaDB excluded x/0 NULL
rows), get_indented_qty, get_planned_qty and set_stock_balance_as_per_serial_no
to frappe.qb / get_all / db.count. Same result on MariaDB; valid under Postgres.
Tests (new test_stock_balance.py): get_reserved_qty SO-item + packed-bundle
branches and get_indented_qty, all without delivery so they avoid the unrelated
#39 SLE-repost path and pass on Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
validate_qty_against_so: the already-indented (Material Request Item) and
Sales-Order-qty (Sales Order Item) sum lookups -> frappe.get_all({SUM}).
check_modified_date: raw `select modified` + MariaDB-only `TIMEDIFF` ->
frappe.db.get_value + a get_datetime() comparison. The TIMEDIFF removal also
fixes a real Postgres bug: update_status() (Stop/Reopen/Cancel) ran TIMEDIFF,
which errors on PG (`function timediff does not exist`); this greens 7
previously-failing status-change tests on Postgres.
Same result on MariaDB. Tests: concurrent-modification guard (pass + throw
branches) and the over-request-against-SO throw (both converted SUM queries +
the boundary). mapper.py is intentionally left untouched (no raw SQL; its
staging copy predates develop's RFQ cost_center field-map).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_default_contact / get_default_address: raw correlated-subquery SELECTs over
Dynamic Link -> frappe.qb with a LEFT join (preserving the original
correlated-subquery semantics: a Dynamic Link whose parent Contact/Address is
missing still returns, with a NULL flag). Same result on MariaDB; valid under
Postgres.
Tests: pin the converted query output (real linked Contact/Address) and lock
the LEFT-join choice with an orphaned-Dynamic-Link case (fails under an inner
join).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
update_billed_amount_based_on_so: raw "select sum(amount) ... where
dn_detail=%s and docstatus=1" -> frappe.get_all(fields=[{SUM: amount}]); the
bare aggregate needs no GROUP BY and the NULL-sum still resolves to 0. Same
result on MariaDB; valid under Postgres. Covered by the existing billing tests
in test_delivery_note.py.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
cancel_packing_slips: raw "SELECT name FROM `tabPacking Slip` WHERE
delivery_note=%s AND docstatus=1" -> frappe.get_all(pluck="name") with
pluck-aware iteration. Same result on MariaDB; valid under Postgres.
Covered by test_cancel_packing_slips_cancels_submitted_slips in
test_delivery_note.py.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
set_actual_qty Bin lookup -> frappe.db.get_value; validate_proj_cust raw
"customer=%s OR ifnull(customer,'')=''" -> get_all or_filters with
[customer, is, not set] (correct PG empty-string/NULL handling); the two
check_next_docstatus implicit comma-joins -> get_all on the child table
(Sales Invoice Item / Installation Note Item, docstatus=1). Same result on
MariaDB; valid under Postgres.
Tests: validate_proj_cust mismatch + no-customer (the or_filters branch), and
check_next_docstatus blocking cancel when a submitted Sales Invoice draws from
the DN.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw SELECT with get_match_cond in item_details with
frappe.qb.get_query(ignore_permissions=False) plus a Delivery Note Item
subquery; get_query applies the permission match conditions. Same result on
MariaDB; valid under Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the get_all(limit=1, pluck) existence checks with frappe.db.exists and
the no-own-valuation-method Stock Ledger Entry EXISTS with a frappe.qb
subquery (null-or-empty valuation_method preserved). Same result on MariaDB;
valid under Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the on_trash Stock Ledger Entry serial-match SELECT and the
update_maintenance_status expiry SELECT with frappe.get_all (or_filters for
the amc/warranty expiry OR). Same result on MariaDB; valid under Postgres.
Tests: maintenance-status expiry transition, the not-in exclusion (with a
negative-control candidate), and NULL-status handling.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw f-string UPDATE (name + production_item match) that links a
Quality Inspection back to its Job Card with frappe.qb.update. Same result on
MariaDB; valid under Postgres.
Tests: the Job Card reference update and its production_item scoping.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw UPDATE ... modified=NOW() in update_item_price with
frappe.qb.update (now() for modified). Same result on MariaDB; valid under
Postgres.
Tests: currency/buying/selling/modified propagation and price-list scoping.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw UNION of forward/two-way alternative-item matches with two
frappe.get_all calls, order-preserving dedup (dict.fromkeys) and Python
pagination. Each leg is bounded to start+page_len rows so the per-keystroke
search round trip stays small (the original bounded with LIMIT/OFFSET);
ItemAlternative forbids duplicate (item_code, alternative_item_code) pairs, so
each leg is internally distinct and that bound is exact. Same result on
MariaDB; valid under Postgres.
Tests: both-direction dedup, txt filtering, pagination, bounded-and-exact
page-walk reconstruction, and case-insensitive (ILIKE-on-Postgres) matching.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw lft/rgt SELECT with frappe.get_all(pluck="name"). Same result
on MariaDB; valid under Postgres.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Replace the raw EXISTS subquery (items within an item-group subtree) with
frappe.qb (item_group.isin(subquery)). Same result on MariaDB; valid under
Postgres' stricter SQL.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
make_timesheet(simulate=True) logs at now_datetime(); when the suite runs late
in the day under the site timezone the 2h log crosses midnight, so its to_time
falls outside the report's `to_time <= end-of-day` bound and the submitted
timesheet is (correctly) excluded — making test_submitted_timesheet_in_summary
fail in an evening window (observed at 22:51 IST in CI). Pin the log to a fixed
mid-day window on today so the assertion is time-of-day independent.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
validate_fixed_asset (C/11) is flattened with a guard clause and the per-item
checks move into _validate_fixed_asset_item. validate_inter_company_party
(C/12) splits into _get_inter_company_party_config plus _validate_against_reference
and _validate_internal_party_company (conditions preserved verbatim). No C-rank
function remains in either module.
Characterize the previously-untested asset-sale throws (missing asset, update
stock, return without return-against, selling a sold/scrapped asset) and the
asset-restore note text before the move; behaviour is unchanged (asset and
inter-company suites green).
- Replace the JournalEntry controller class and cur_frm.cscript free
functions with frappe.ui.form.on event blocks plus a namespaced
erpnext.journal_entry helper object
- Drop deprecated APIs: cur_frm/script_manager, add_fetch, $.each and var
- Move the bank_account -> account fetch to fetch_from on the
Journal Entry Account "account" field
- Keep totals/difference and company-currency conversion on the client
(cheap, race-free); call the server only to fetch exchange rates
- get_balance now computes its own difference instead of trusting the
client-sent value, with a regression test
The function was 199 lines, dominated by a 102-line update_details closure.
Extract the two party-mapping branches into module helpers
_apply_purchase_party_details / _apply_sales_party_details and the address
lookup into _get_linked_address; update_details is now a 6-line dispatcher.
make_inter_company_transaction drops to ~104 lines. No behaviour change
(inter-company SI->PI and PO->SO suites green).
Rename three private helpers to follow the verb-prefixed convention used
across the services:
- _amount_in_account_currency -> _get_amount_in_account_currency
- _return_aware_against_voucher -> _resolve_against_voucher
- _sdbnb_booking_for_item -> _get_sdbnb_booking_for_item
Pure rename of private methods, no behaviour change.
set_status was a single status-resolution cascade (cyclomatic complexity
C/19). Extract the submitted-invoice resolution into _submitted_status (with
the invoice-discounting suffix) and _payment_status (guard clauses), leaving
the cancelled/draft quirks inline. set_status drops C/19 -> B/7; no C-rank
function remains in the module.
Add a characterization test for set_indicator, which was 93% untested -
the portal indicator colour/title for credit-note / unpaid / overdue /
return / paid states. Behaviour is unchanged (status and invoice-discounting
suites green).
The link-decision was a single four-way boolean OR (cyclomatic complexity
C/16) where every branch repeated 'args.timesheet_detail == data.name'.
Factor that match out as a loop guard and extract the remaining decision into
_should_set_sales_invoice as ordered guard clauses. Behaviour is unchanged
(project, link-on-submit, unlink-on-cancel and return paths preserved);
characterization tests and the full timesheet suite are green.
Pin TimesheetBillingService behaviour before refactor: billing a timesheet
into a Sales Invoice links the timesheet detail and marks it Billed on submit,
and clears the link / reverts status on cancel; an unsubmitted timesheet
cannot be invoiced.
Convert raw `frappe.db.sql` across the Projects module to `frappe.qb` / the ORM
so the same code runs on MariaDB and Postgres. Behaviour is preserved on
MariaDB; the conversions also make these paths valid under Postgres' stricter
SQL (GROUP BY, case-sensitivity, empty-string handling).
Conversions of note (behaviour kept identical to the MariaDB original):
- project.get_users_for_project: search selects the stored full_name instead of
concat_ws(first, middle, last) (concat_ws diverges on Postgres, where empty
Data fields are NULL) and wraps Locate in LOWER() to keep MariaDB's
case-insensitive result ordering.
- project costing: percent-complete and sales/billed-amount aggregates rebuilt
as Sum() query-builder selects.
- task.reschedule_dependent_tasks: the correlated subquery is split into a
`Task Depends On` parent-pluck + a Task lookup (same rows, no nested SQL).
- timesheet.get_events: user-permission match conditions move to the query-builder
form via get_event_conditions_qb; calendar columns rebuilt with Concat/Round.
- report/project_wise_stock_tracking & report/daily_timesheet_summary: GROUP BY
cost aggregates and the timesheet date window (timestamp(to_date,'24:00:00') ->
end-of-day via get_combine_datetime) rebuilt to satisfy Postgres.
- search helpers (query_task, get_project, get_timesheet) use frappe.qb.get_query
with ignore_permissions=False in place of build_match_conditions/get_match_cond.
Tests (run on both MariaDB and Postgres, --lightmode):
- Existing project/task/timesheet/activity_cost suites kept green (27 tests).
- New project_wise_stock_tracking test drives all three cost aggregates with
positive data (purchased / issued / delivered GROUP BY) plus get_project_details.
- New daily_timesheet_summary test covers the date-window join.
Not included: project_update.py is deferred. Its daily_reminder()/email_sending()
select `progress`/`progress_details`, columns that do not exist on the Project
Update doctype, so the function errors when invoked regardless of backend - a
pre-existing bug that needs an email-rework, not just a query port.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(coa_importer): allow importing COA only for `Accounts Manager`
Co-authored-by: Pratheep S <pratheeps2024@gmail.com>
* fix(coa_importer): check permissions in `unset_existing_data`
---------
Co-authored-by: Pratheep S <pratheeps2024@gmail.com>
Convert raw `frappe.db.sql` in the Setup, Utilities, Templates and Regional
areas to `frappe.qb` / the ORM so the same code runs on MariaDB and Postgres.
Behaviour is preserved on MariaDB; the conversions also make these paths valid
under Postgres' stricter SQL (GROUP BY, case-sensitivity, reserved words).
Conversions of note (behaviour kept identical to the MariaDB original):
- email_digest: ToDo ordering replicated with a CASE that mirrors MySQL
`field(priority,'High','Medium','Low')` (unknown/NULL -> 0, sorts first),
NULL-date-first and a `name` tie-break for a deterministic LIMIT.
- company.get_all_transactions_annual_history: the cross-DocType UNION + GROUP BY
is replaced by one grouped query per DocType merged with a Counter, so two
different DocTypes sharing a transaction_date still collapse into one bucket.
- templates/utils.send_message: contact lookup wraps both sides in LOWER() to
keep MariaDB's case-insensitive email match on case-sensitive Postgres.
- regional/irs_1099 & uae_vat_201: address ranking and emirate aggregation
rebuilt with CASE/aggregate selects that satisfy Postgres GROUP BY, with a
deterministic tie-break on the LIMIT-1 address lookups.
- utilities/product.get_item_codes_by_attributes: numeric attribute values are
cast with cstr() so Postgres doesn't reject `varchar = numeric`.
Tests (run on both MariaDB and Postgres, --lightmode):
- New: company merge test, authorization_rule duplicate-check, youtube report,
templates/utils, and utilities/templates page reports (partners, rfq,
material_request_info, product, utilities __init__).
- Existing suites kept green: company, email_digest, transaction_deletion_record,
irs_1099, uae_vat_201.
Deferred (tracked separately):
- setup/doctype/authorization_control.py still has raw `.format()` SELECTs;
left for its own PR.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- Filter the parent Maintenance Visit's docstatus (mv.docstatus != 2) via a qb join, as
the original SQL did, instead of the child Maintenance Visit Purpose row's docstatus.
Synced in normal flows, but exactly faithful to the original intent.
- Add a limit(500) to bound the read on a cancellation path.
Adds two both-engine tests calling on_cancel directly: an active (non-cancelled) visit
blocks the claim cancel; with no referencing visit the claim is marked Cancelled.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the remaining raw frappe.db.sql in the Support module to frappe.qb / ORM so the
queries run on PostgreSQL as well as MariaDB. Faithful conversions -- no MariaDB
behaviour change:
- issue.py, warranty_claim.py (Maintenance Visit lookup / make_maintenance_visit)
- reports: first_response_time_for_issues and issue_summary (GROUP BY on the grouped
Date(creation)/based-on field + Avg/Count -- Postgres-valid), support_hour_distribution
Tests: existing issue suite (35) passes on both engines; adds both-engine tests for the
previously-untested warranty_claim mapper (3) and the three reports. All green on
MariaDB and PostgreSQL.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
BootStrapTestData.make_workstation() created _Test Workstation 1 referencing the
warehouse "_Test warehouse - _TC" (lowercase 'w'), but make_warehouse() never
creates that name -- it makes "_Test Warehouse - _TC" (capital W) and others.
On MariaDB the lowercase reference resolves to the capital warehouse via the default
case-insensitive collation, so it silently works. On PostgreSQL (case-sensitive) the
link cannot be found, so on a freshly-bootstrapped site make_workstation() raises
LinkValidationError: Could not find Warehouse: _Test warehouse - _TC, which aborts the
module-level BootStrapTestData() import and blocks every test extending ERPNextTestSuite.
It was masked only on sites where the workstation was already bootstrapped (make_records
skips existing) or where the lowercase name happened to exist from legacy data.
Point the fixture at the existing "_Test Warehouse - _TC". Verified on a freshly-reset
site: the buggy fixture raises the LinkValidationError on Postgres and passes after the
fix; MariaDB passes either way.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The second Project in test_sales_order_link_is_not_overwritten_by_second_project
was inserted without a company, which only succeeds when a default company is
configured. On a fresh CI site this raised MandatoryError. Set company from the
sales order explicitly.
set_pos_fields was a 97-line method (cyclomatic complexity E/36). Split
it into a small orchestrator plus focused helpers, each preserving the
exact for_validate semantics (A/5 after).
Collapse the three near-identical mode-of-payment query builders onto a
shared _enabled_mode_of_payment_query, and add type hints and docstrings.
Public signatures and return shapes are unchanged.
Pin the behaviour of POSService.set_pos_fields (POS-profile default
resolution and the for_validate guard) and the mode-of-payment query
helpers before refactoring them.
- Fix N+1: the per-lead conversion issued 4 queries per lead (Opportunity, Quotation,
Issue, Communication). Collect the reference documents for all leads in 3 bulk
queries, then one Communication query per lead -> ~N+3 round-trips instead of 4N,
matching the original single-query-per-lead cost.
- Constrain reference_doctype in the Communication lookup (names are unique only within
a doctype), closing a latent cross-doctype name-collision gap the original also had.
Both-engine test still green.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the remaining raw frappe.db.sql in the CRM module to frappe.qb / ORM so the
queries run on PostgreSQL as well as MariaDB. Faithful conversions -- no MariaDB
behaviour change:
- opportunity.py, doctype/utils.py (get_last_interaction)
- reports: campaign_efficiency, first_response_time_for_opportunity (GROUP BY on the
grouped Date(creation) + Avg -- Postgres-valid), lead_conversion_time,
prospects_engaged_but_not_converted
lead_conversion_time also keeps the IS NOT NULL communication-date guard (forward-port
of the fix already on develop). Also drops invalid backtick notation from two get_all
order_by clauses in doctype/utils.py (order_by="`creation` DESC"), which frappe's
query engine rejects -- a latent failure on both engines, surfaced by the new test.
Tests: existing opportunity suite plus new both-engine tests for the four previously
untested reports/utils. All green on MariaDB and PostgreSQL.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- cancel_movement_entries: filter the parent Asset Movement's docstatus via a qb join
(as the original SQL did), instead of the child Asset Movement Item.docstatus. Behaviour
is identical in normal flows (child docstatus is synced) but this is exactly faithful.
- get_maintenance_log: add a both-engine test for this previously-untested whitelisted
endpoint. Confirms the frappe v16 dict aggregate field spec ({"COUNT": ...}) runs and
returns correct per-status counts (no runtime crash).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Convert the remaining raw frappe.db.sql in the Assets module to frappe.qb / ORM so
the queries run on PostgreSQL as well as MariaDB. Faithful 1:1 conversions -- no
MariaDB behaviour change:
- asset.py (gl-entry / bom-cost fetches), asset_maintenance.py (team members),
asset_movement.py (latest location/custodian), location.py (get_children)
- fixed_asset_register.py: the depreciation-amount aggregate groups by asset.name
(the primary key) selecting only asset.name + Sum(gle.debit), which is valid under
Postgres strict GROUP BY (PK functional dependency)
Tests: existing asset (61), asset_maintenance, asset_movement and location suites
pass on both engines; adds a test for the previously-untested Fixed Asset Register
report (covers the GROUP BY aggregate on both engines).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review (#56113): add a customer filter to the is_pos test so the
customer-subquery fix is covered (a.customer was unselected before and errored).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review / semgrep (#56112): convert the last raw f-string query
(budget_records, with a dynamic dimension column + tree EXISTS condition) to
frappe.qb, clearing the sql-format-injection finding. Also switch the two
remaining implicit comma-joins (get_requested_amount / get_ordered_amount) to
explicit .join().on() for consistency. test_budget 23/23 on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review (#56111):
- pos_closing get_payments grouped by mode_of_payment but selected a bare account ->
Postgres GroupingError. Wrap in Max() (deterministic, both engines agree; account is
consumed downstream for the change-amount adjustment). test_pos_closing_entry 9/9 both engines.
- get_voucherwise_gl_entries: add limit=0 to make the unbounded fetch explicit.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review (#56105): when there's no matching communication, first_contact is
None and date_diff(invoice_date, None) treats None as today, giving a wrong
(negative) duration. Skip the entry instead, mirroring the communication_count guard.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Converts the report's raw f-string SQL (incl. 3-way UNIONs) to frappe.qb. Split out of #56082.
The non-POS path is MariaDB-identical. get_pos_invoice_data had a loose GROUP BY that
errors on Postgres; line-level warehouse/cost_center/mode_of_payment are now Max()
(deterministic, both engines agree), the unused item_code dropped, and customer added to
the invoice subquery so the customer filter works (it referenced a column the subquery
never selected). + a test covering the previously-untested is_pos path.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Converts budget.py raw SQL to frappe.qb for Postgres compatibility. Split out of #56082.
Mostly MariaDB-identical, with one behaviour change: get_requested_amount summed
Sum(qty) * <arbitrary rate> (a bare rate column, invalid on Postgres and wrong when an
item was requested at different rates). Now Sum(qty * rate) per line -- correct, and
identical on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Pure MariaDB-identical conversion for Postgres compatibility. Split out of #56082.
bank_clearance, pos_closing_entry, process_statement_of_accounts, party, utils, and
sales_invoice/services/pos converted to frappe.qb; bundles erpnext/utilities/query.py
(the get_match_conditions_qb helper, frappe#40075 follow-up) which
process_statement_of_accounts depends on. No behaviour change on MariaDB.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Pure MariaDB-identical conversion (raw frappe.db.sql -> frappe.qb / portable
functions) for Postgres compatibility. Split out of #56082.
general_ledger, gl_entry, gl_validator, period_closing_voucher, deferred_revenue,
process_payment_reconciliation + their tests. No behaviour change on MariaDB;
verified equivalent and the suites pass on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review (#56105): the IS NOT NULL guard can return no rows (the count above
filters on sender, this query on recipients), so [0][0] would raise IndexError.
Fall back to None when empty, matching the prior behaviour for that case.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Address review (#56101): with 3+ orders the proportional shares could drift by a
sub-cent and not sum back to the grand total, leaving the last payment term
"Partly Paid". The last order (sorted, so MariaDB and Postgres agree) now absorbs
the residual: grand_total - sum(prior shares). Extended the test to three orders.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Parity sweep findings in queries already merged in develop:
- pos_invoice.py: serial_no return-match matched case-sensitively on Postgres (case-insensitive
on MariaDB). Replaced the get_all or_filters lookup with a qb query that case-folds both sides
via Lower() (no-op on MariaDB).
- controllers/queries.py + pick_list.py: the Locate()-based relevance ranking in link-query
ORDER BY is case-sensitive on Postgres (Strpos) vs case-insensitive on MariaDB (Locate), so
autocomplete order differed. Lower() both arguments so the ranking matches on both engines.
- crm/lead_conversion_time.py: "first contact" used ORDER BY communication_date LIMIT 1 read by
index; a NULL date sorts first on MariaDB but last on Postgres, changing the result. Added
`communication_date IS NOT NULL` so both engines return the earliest real contact date.
Verified on MariaDB and Postgres: test_pick_list 40/40, test_pos_invoice 26/26 on both engines.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_pick_list_query selects Sales Order.customer (a joined table's column) while
grouping only by Pick List.name. Postgres' functional-dependency relaxation applies
to a table's own primary key, not to a joined table's columns, so the query raises
GroupingError on Postgres. MariaDB arbitrary-picks and runs.
customer is already pinned to a single value by `WHERE Sales Order.customer = filter`,
so adding it to the GROUP BY is identical on MariaDB and valid on Postgres.
Test (errors with GroupingError on the old code on Postgres, passes on both engines):
- test_get_pick_list_query_postgres_valid: a submitted pick list for a customer is
returned by the link query.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
payment_terms_status_for_sales_order grouped invoice rows by `sii.parent` and took
`Max(sii.sales_order)`, so an invoice that bills several Sales Orders was credited
in full to one arbitrary order and the rest were starved of that payment.
get_so_with_invoices now returns one row per (invoice, sales_order) and splits the
invoice's grand total across the orders in proportion to each order's net line
amount on that invoice. A single-order invoice keeps the full grand total (ratio 1),
so the common case is unchanged; the split is pure Python over deterministic SQL,
so MariaDB and Postgres produce identical results (100% parity).
Test (fails on the old code, passes on both engines):
- test_invoice_billing_multiple_orders_splits_proportionally: one invoice billing two
SOs 600/400 -> each order credited its share, summing to the grand total. Old
Max(sales_order) collapsed the invoice onto one order.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Same class as the sub_assembly_queries / bom_stock_analysis fixes in this PR: two
more merged queries wrapped a non-functionally-dependent column in Max(), so the
engines can disagree and the value is wrong for the case the column drives.
bom_explosion._subitems_query — Max(is_phantom_item):
Rows are grouped by item_code and get_subitems() drops any grouped row whose
is_phantom_item is truthy. When one item_code is listed in a BOM both as a
phantom sub-assembly and as a plain raw material, Max() returns 1 and the real
raw material is silently dropped from the plan. Use Min(): an item is phantom
only when EVERY line for it is phantom, so a real material is never lost.
required_items._material_transfer_qty_by_item — Max(original_item):
original_item is the output dict key. The same item B can be transferred both
for itself (original_item NULL) and as a substitute for required item A
(original_item=A). Grouping by item_code alone with Max() merged the two and
credited B's whole transfer to A, leaving B at 0. Group by
(item_code, original_item) and accumulate into the keyed dict so each transfer
is credited to the right required item (two rows can resolve to one key, e.g.
A's own transfer and B-for-A, hence += not plain assignment).
Both were previously undefined SQL (loose GROUP BY); the fix makes MariaDB and
Postgres agree on the correct, deterministic value. Other Max()-wrapped columns
in these queries are functionally dependent on the grouped item and unchanged.
Tests (fail on the old code, pass on both engines):
- test_subitems_query_keeps_real_rm_listed_alongside_phantom
- test_transferred_qty_not_misattributed_between_item_and_its_substitute
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The test was mutating an already-submitted RFQ, which raised
UpdateAfterSubmitError because cost_center lacks allow_on_submit.
Use do_not_submit=True, set cost_center on the draft, save, then submit.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Address review on #56090: get_bom_data groups components by item_code, so it
picks one representative BOM Item line for the (bom_no, is_phantom_item) pair.
Taking the first line by idx dropped the phantom flag when a non-phantom line
was listed before the phantom one, so explode_phantom_boms skipped the sub-BOM.
Keep one row per item_code (preserving the qty_per_unit total per component
rather than widening the GROUP BY), but make the representative phantom-
preferring: the first line, upgraded to the first phantom line if any exists.
A phantom sub-BOM is therefore never dropped due to line order, on either engine.
Adds test_phantom_explosion_when_phantom_line_is_not_first (phantom line at
idx 2) alongside the existing idx-1 case; both pass on MariaDB and Postgres and
the new one fails on the naive first-line representative.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
When a component is listed more than once in a BOM pointing at different
sub-BOMs (e.g. one phantom, one not), two queries grouped the duplicate
lines into a single row and aggregated bom_no and is_phantom_item with
*independent* Max(). That could pair the phantom flag of one line with the
bom_no of another, so the consumer recursed into the wrong sub-BOM:
- sub_assembly_queries._sub_assembly_rm_query keys on (item_code, bom_no)
and recurses on is_phantom_item. An incoherent pair sent raw-material
resolution down the wrong sub-assembly BOM.
- bom_stock_analysis.get_bom_data: explode_phantom_boms recurses into
bom_no only when is_phantom_item is set; an incoherent pair exploded a
non-phantom sub-BOM as if it were phantom (or vice-versa).
Fix:
- sub_assembly_queries: group also by (bom_no, is_phantom_item) so each
distinct sub-BOM is its own coherent row.
- bom_stock_analysis: drop the two independent Max()es and attach a single
representative line (lowest idx) per item_code before exploding.
This was previously undefined SQL (loose GROUP BY); the fix makes MariaDB
and Postgres agree on a deterministic, coherent pairing. Other Max()-wrapped
columns are functionally dependent on the grouped item and keep their value
on both engines.
Tests (fail on the old code, pass on both engines):
- test_phantom_explosion_picks_coherent_sub_bom: duplicate-component BOM
explodes the phantom sub-BOM, not the lexically-larger non-phantom one.
- test_sub_assembly_rm_query_keeps_bom_no_phantom_pair_coherent: the query
returns one coherent row per distinct sub-BOM with the right phantom flag.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
get_future_payments_from_journal_entry has no GROUP BY: the Sum() makes it an
implicit single-group aggregate, so every future-dated JE payment company-wide
collapses into ONE row keyed by an arbitrary (invoice, party). The Accounts
Receivable report then allocates the entire future sum against that one invoice and
shows zero future payment for every other invoice. This predates the postgres work
(MariaDB returned an arbitrary single row); the qb conversion only made the arbitrary
pick deterministic via Max().
Add an explicit GROUP BY (je.name, jea.reference_name, jea.party, jea.party_type,
je.posting_date, je.cheque_no) and drop the Max() wrappers, so each (JE, invoice,
party) is its own future-payment row -- matching the Payment Entry path and the
(invoice_no, party) keying the report's allocator already expects. Identical on
MariaDB and PostgreSQL.
Ships a JE-path future-payment test (one future JE paying two invoices -> each
invoice keeps its own future amount; fails on the old code with 0 != 50).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
stock_delivered_but_not_billed_gl_entries (C/15) is split into a thin loop
plus _sdbnb_booking_for_item (eligibility + valuation), _is_sdbnb_reversal
(the account predicate) and _append_sdbnb_gl_entries (the two GL rows) —
guard clauses replace the nested continues. get_gle_for_change_amount now
uses the shared _amount_in_account_currency helper. No C-rank functions
remain in the file. Behaviour unchanged; characterization tests and the
full Sales Invoice suite (133) green.
Three Query Reports embedded double-quoted string literals and an unquoted table
identifier that error on PostgreSQL. Portability-only, no behaviour change on either engine:
- material_requests_for_which_supplier_quotations_are_not_created,
requested_items_to_be_transferred: double-quoted string literals ("Stopped",
"Material Transfer") -> single quotes (double quotes are identifiers on postgres, not strings).
- items_to_be_requested: quote the `tabBin` table identifier so postgres doesn't lower-case it.
(received_items_to_be_billed was dropped: it is a Script Report, so its `query` field is dead
code and the fix never reaches the DB.)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Pin the two least-covered SalesInvoiceGLComposer methods before refactor:
- stock_delivered_but_not_billed_gl_entries: billing a perpetual Delivery
Note via Sales Invoice reverses the SDBNB account into COGS for an equal
amount.
- get_gle_for_change_amount: empty without change, mandatory-account error,
and the debit-to / change-account entry pair.
Extract two cross-cutting helpers used across the GL methods:
- _amount_in_account_currency: the repeated 'base if account is in company
currency else transaction amount' ternary (customer, tax, item, POS and
write-off entries).
- _return_aware_against_voucher: the return/self-outstanding against_voucher
rule duplicated in the customer and POS entries.
Pull the per-item income and discount rows into their own builders and
flatten make_item_gl_entries with a guard clause. Complexity drops:
make_customer C11->B7, make_pos C12->B7, make_item C14->B10,
make_discount C13->B10, make_tax->A3. No behaviour change; entry dicts and
amounts are identical (full Sales Invoice suite green).
stock_delivered_but_not_billed_gl_entries is left for a coverage-first pass
(it is the least-tested GL branch).
get_warehouse could never run: it filtered POS Profile on a non-existent
'user' column (users live in the applicable_for_users child table), and
embedded a Python bool (frappe.session["user"] == "") inside a query
builder predicate, which raises before reaching the database. It also
has no callers. Remove it and the now-unused msgprint import.
- Add `cost_center` field and `accounting_dimensions_section` / `dimension_col_break`
to Request for Quotation Item DocType so custom accounting dimensions propagate automatically
- Register `Request for Quotation Item` in `accounting_dimension_doctypes` hook
- Map `cost_center` from Material Request → RFQ in `make_request_for_quotation`
- Map `cost_center` from RFQ → Supplier Quotation in `make_supplier_quotation_from_rfq`
and `create_rfq_items` (portal flow)
- Map `cost_center` in `get_item_from_material_requests_based_on_supplier` (MR-based RFQ flow)
- Add test cases to verify cost_center propagation through the MR → RFQ → SQ chain
Fixes#55855
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Changing the transaction/posting date re-triggers the `currency` handler,
which fetches a fresh exchange rate and, whenever it differs from the
current one, divided every Amount-type item margin and Actual tax charge
by the new rate. `margin_rate_or_amount` is an amount in the transaction
currency, so dividing it is only meaningful when the document actually
switches currency; on a mere rate refresh it silently shrinks the margin
every time.
Track the currency the rendered document is denominated in and convert
margins/actual charges only on a real currency change, while still
updating `conversion_rate` so base amounts recalculate correctly.
Also remove the duplicated `currency()` override in quotation.js: it
re-ran the same fetch-and-convert block after `super.currency()` (double
converting margins) and lacked the `load_after_mapping` guard. The base
handler already covers Quotation via `transaction_date`.
Fixes#45210
`StockClosing.get_sle_entries` filtered `Stock Closing Balance` by a
`closing_stock_balance` column that does not exist; the link field back to
the closing entry is `stock_closing_entry`. As a result every Stock
Closing Entry created after the first one failed with
`OperationalError (1054, "Unknown column 'closing_stock_balance'")`, since
the previous-balance branch only runs once an earlier closing exists.
Fixes#54819
Creating a Project with a `sales_order` set used `frappe.db.set_value`
to unconditionally write the Sales Order's `project` field. When several
projects were created for the same Sales Order, each new project silently
overwrote the previous link, leaving earlier projects orphaned.
Only back-link the Sales Order when it is not already tied to another
project, and write the value through the document's `db_set` so the
modified timestamp and realtime update are handled. A warning is shown
when an existing link is left untouched.
Fixes#52179
self._flag(node,f"{name}(distinct=True, order_by=...) -> frappe drops ORDER BY for distinct queries on Postgres; sort in python instead, e.g. sorted(..., key=str.casefold)")
# query-builder .rlike(...): pypika emits the MySQL-only RLIKE operator, which frappe does
# NOT translate for Postgres (it rewrites only REGEXP -> ~*).
ifname=="rlike":
self._flag(node,".rlike() emits MySQL-only RLIKE (not translated on Postgres) -> use .regexp() (rewritten to ~*) or .like()")
# Cast(col, "char") / Cast_(col, "char"): on Postgres a bare CHAR is character(1) and truncates
# (e.g. CAST(12 AS CHAR) -> '1'); use "varchar" for a full-length string cast.
self._flag(node,f'"{key.value}" is a MySQL SHOW INDEX result key -> use frappe.db.has_index()/get_column_index()')
self.generic_visit(node)
defcheck_file(path:str)->list[str]:
try:
# nosemgrep: frappe-semgrep-rules.rules.security.frappe-security-file-traversal -- dev-only lint tool; `path` is a source file supplied by pre-commit, not user input
src=open(path,encoding="utf-8").read()
except(OSError,UnicodeDecodeError):
return[]
try:
tree=ast.parse(src,filename=path)
exceptSyntaxError:
return[]# check-ast hook reports real syntax errors
"depends_on":"eval:doc.charge_type=='Actual' && ['Valuation', 'Valuation and Total'].includes(doc.category)",
"description":"If checked, the entire amount (e.g. Freight) is allocated to the valuation of stock & asset items only. If unchecked, the amount is distributed across all items and the portion belonging to non-stock items is not added to valuation.",
"query":"SELECT\n sales_partner as \"Sales Partner:Link / Sales Partner:220\",\n sum(base_net_total) as \"Invoiced Amount (Excl. Tax):Currency:220\",\n sum(amount_eligible_for_commission) as \"Amount Eligible for Commission:Currency:220\",\n sum(total_commission) as \"Total Commission:Currency:170\",\n sum(total_commission)*100 / sum(amount_eligible_for_commission) as \"Average Commission Rate:Percent:220\"\nFROM\n (\n SELECT\n sales_partner,\n base_net_total,\n total_commission,\n amount_eligible_for_commission\n FROM\n `tabSales Invoice` \n WHERE\n docstatus = 1\n AND IFNULL(base_net_total, 0) > 0\n AND IFNULL(total_commission, 0) > 0\n\n UNION ALL\n\n SELECT\n sales_partner,\n base_net_total,\n total_commission,\n amount_eligible_for_commission\n FROM\n `tabPOS Invoice`\n WHERE\n docstatus = 1\n AND IFNULL(base_net_total, 0) > 0\n AND IFNULL(total_commission, 0) > 0\n ) AS sub\nGROUP BY\n sales_partner\nORDER BY\n \"Total Commission:Currency:120\"",
"query":"SELECT\n sales_partner as \"Sales Partner:Link / Sales Partner:220\",\n sum(base_net_total) as \"Invoiced Amount (Excl. Tax):Currency:220\",\n sum(amount_eligible_for_commission) as \"Amount Eligible for Commission:Currency:220\",\n sum(total_commission) as \"Total Commission:Currency:170\",\n sum(total_commission)*100 / NULLIF(sum(amount_eligible_for_commission), 0) as \"Average Commission Rate:Percent:220\"\nFROM\n (\n SELECT\n sales_partner,\n base_net_total,\n total_commission,\n amount_eligible_for_commission\n FROM\n `tabSales Invoice` \n WHERE\n docstatus = 1\n AND IFNULL(base_net_total, 0) > 0\n AND IFNULL(total_commission, 0) > 0\n\n UNION ALL\n\n SELECT\n sales_partner,\n base_net_total,\n total_commission,\n amount_eligible_for_commission\n FROM\n `tabPOS Invoice`\n WHERE\n docstatus = 1\n AND IFNULL(base_net_total, 0) > 0\n AND IFNULL(total_commission, 0) > 0\n ) AS sub\nGROUP BY\n sales_partner",
"ref_doctype":"Sales Invoice",
"report_name":"Sales Partners Commission",
"report_type":"Query Report",
Some files were not shown because too many files have changed in this diff
Show More
Reference in New Issue
Block a user
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
