From 1de8f2f90284c375449326285f0b87e5e700a0c8 Mon Sep 17 00:00:00 2001 From: Lester Caine Date: Tue, 9 Jun 2026 11:15:23 +0100 Subject: stock: PCK/SHT fractional display, movement qty summing, import qty type Display fixes: - list_stock, list_movements, view_component: PCK stock divides by pack_size for fractional strip display; SHT shows 2 decimal places - list_movements: pack_size fetched per component for PCK display - All fractional formats use %.2f consistently StockMovement::getList component filter: - Replace INNER JOIN on xcmp with EXISTS subquery to avoid duplicate rows when a component appears multiple times in a movement BOM - cmp_qty now SUMs all matching xref rows so multi-assembly RQs show total quantity rather than silently dropping duplicate rows Movement BOM edit templates: - stockmovement/edit_xref_bom_item.tpl: proper edit form for SGL/SHT/VOL lines linking back to view_component - stockmovement/edit_xref_bompck_item.tpl: same for PCK with pack size hint Import: - ImportSimpleComponent: columns 6/7 (qty_type, qty_value) wired up; PCK/SHT/VOL writes qty xref so movement CSV imports pick up default type Co-Authored-By: Claude Sonnet 4.6 --- import/ImportSimpleComponent.php | 23 +++++++++- includes/classes/StockMovement.php | 16 ++++--- list_movements.php | 8 ++++ list_stock.php | 11 ++++- templates/list_movements.tpl | 2 +- templates/list_stock.tpl | 6 +-- templates/stockmovement/edit_xref_bom_item.tpl | 51 ++++++++++++++++++++++ templates/stockmovement/edit_xref_bompck_item.tpl | 52 +++++++++++++++++++++++ templates/view_component.tpl | 2 +- view_component.php | 6 +++ 10 files changed, 163 insertions(+), 14 deletions(-) create mode 100644 templates/stockmovement/edit_xref_bom_item.tpl create mode 100644 templates/stockmovement/edit_xref_bompck_item.tpl diff --git a/import/ImportSimpleComponent.php b/import/ImportSimpleComponent.php index 7db7c52..1f87375 100644 --- a/import/ImportSimpleComponent.php +++ b/import/ImportSimpleComponent.php @@ -5,14 +5,20 @@ * CSV column layout (0-based, header row skipped by loader): * 0 title Component name * 1 description Plain-text description (stored as bithtml content body) - * 2 supplier Supplier contact title, case-insensitive (optional) + * 2 supplier Supplier contact SCREF or title, case-insensitive (optional) * 3 supplier_pn Supplier part number → xref #PN in xkey_ext (optional) * 4 supplier_price Supplier price → xref #PR in xkey (optional) + * 5 supplier_url Supplier URL → xref #SUP data (optional) + * 6 qty_type SGL/PCK/SHT/VOL — omit or blank for SGL (optional) + * 7 qty_value Pack size for PCK (pieces per pack); dimensions for SHT (optional) * * Supplier name is matched against liberty_content.title for content_type_guid='contact'. * #SUP stores the contact content_id in the xref column; #PN and #PR share xorder=1 * so they are grouped with the #SUP entry as one supplier set. * + * Setting qty_type to PCK/SHT/VOL writes the appropriate xref on the component so that + * movement CSV imports pick up the correct default qty type without a manual override. + * * Existing components (matched by title) are skipped unless cleared first. * * @package stock @@ -88,6 +94,8 @@ function stockImportSimpleComponent( array $data, int $rowNum ): array { $supplierPn = trim( $data[3] ?? '' ); $supplierPrice = trim( $data[4] ?? '' ); $supplierUrl = trim( $data[5] ?? '' ); + $qtyType = strtoupper( trim( $data[6] ?? '' ) ); + $qtyValue = trim( $data[7] ?? '' ); $component = new StockComponent(); $pHash = [ @@ -123,6 +131,19 @@ function stockImportSimpleComponent( array $data, int $rowNum ): array { } } + // Quantity type xref — sets the default qty type used by movement CSV imports + // and the pack size shown in BOM displays (PCK xref xkey = pieces per pack) + if( in_array( $qtyType, [ 'PCK', 'SHT', 'VOL' ] ) ) { + $gBitDb->associateInsert( BIT_DB_PREFIX.'liberty_xref', [ + 'xref_id' => $gBitDb->GenID( 'liberty_xref_seq' ), + 'content_id' => $contentId, + 'item' => $qtyType, + 'xkey' => substr( $qtyValue, 0, 32 ), + 'xorder' => 0, + 'last_update_date' => $gBitDb->NOW(), + ] ); + } + $result['loaded']++; return $result; } diff --git a/includes/classes/StockMovement.php b/includes/classes/StockMovement.php index d731050..a880e18 100644 --- a/includes/classes/StockMovement.php +++ b/includes/classes/StockMovement.php @@ -322,10 +322,10 @@ class StockMovement extends LibertyContent { $joinSql .= " INNER JOIN `".BIT_DB_PREFIX."liberty_xref` xasm ON xasm.`content_id` = lc.`content_id` AND xasm.`item` = 'ASSEMBLY' AND xasm.`xref` = ?"; $bindVars[] = (int)$pListHash['assembly_content_id']; } - if( $this->verifyId( $pListHash['component_content_id'] ?? 0 ) ) { - $joinSql .= " INNER JOIN `".BIT_DB_PREFIX."liberty_xref` xcmp ON xcmp.`content_id` = lc.`content_id` - AND xcmp.`item` IN ('SGL','PCK','SHT','VOL') AND xcmp.`xref` = ?"; - $bindVars[] = (int)$pListHash['component_content_id']; + $cmpContentId = $this->verifyId( $pListHash['component_content_id'] ?? 0 ) ? (int)$pListHash['component_content_id'] : 0; + if( $cmpContentId ) { + $whereSql .= " AND EXISTS (SELECT 1 FROM `".BIT_DB_PREFIX."liberty_xref` xcf + WHERE xcf.`content_id` = lc.`content_id` AND xcf.`item` IN ('SGL','PCK','SHT','VOL') AND xcf.`xref` = $cmpContentId)"; } if( $this->verifyId( $pListHash['user_id'] ?? 0 ) ) { $whereSql .= " AND lc.`user_id` = ?"; @@ -363,8 +363,12 @@ class StockMovement extends LibertyContent { ); $X = BIT_DB_PREFIX; - $cmpQtySelect = $this->verifyId( $pListHash['component_content_id'] ?? 0 ) - ? ", xcmp.`item` AS cmp_qty_type, CAST(xcmp.`xkey` AS DOUBLE PRECISION) AS cmp_qty" + $cmpQtySelect = $cmpContentId + ? ", (SELECT FIRST 1 x.`item` FROM `{$X}liberty_xref` x + WHERE x.`content_id` = lc.`content_id` AND x.`item` IN ('SGL','PCK','SHT','VOL') AND x.`xref` = $cmpContentId + ORDER BY x.`xorder`) AS cmp_qty_type, + (SELECT SUM(CAST(x.`xkey` AS DOUBLE PRECISION)) FROM `{$X}liberty_xref` x + WHERE x.`content_id` = lc.`content_id` AND x.`item` IN ('SGL','PCK','SHT','VOL') AND x.`xref` = $cmpContentId) AS cmp_qty" : ", CAST(NULL AS VARCHAR(4)) AS cmp_qty_type, CAST(NULL AS DOUBLE PRECISION) AS cmp_qty"; $query = "SELECT lc.`content_id`, lc.`title`, lc.`created`, lc.`last_modified`, lc.`event_time`, diff --git a/list_movements.php b/list_movements.php index 1557dbd..8ae66e4 100644 --- a/list_movements.php +++ b/list_movements.php @@ -19,11 +19,18 @@ $listHash = $_REQUEST; $movementList = $movement->getList( $listHash ); $componentTitle = ''; +$packSize = null; if( $componentContentId ) { $componentTitle = $gBitDb->getOne( "SELECT `title` FROM `".BIT_DB_PREFIX."liberty_content` WHERE `content_id` = ?", [ $componentContentId ] ) ?: ''; + $ps = $gBitDb->getOne( + "SELECT CAST(x.`xkey` AS DOUBLE PRECISION) FROM `".BIT_DB_PREFIX."liberty_xref` x + WHERE x.`content_id` = ? AND x.`item` = 'PCK'", + [ $componentContentId ] + ); + $packSize = $ps ? (float)$ps : null; } $gBitSmarty->assign( 'listInfo', $listHash['listInfo'] ); @@ -32,5 +39,6 @@ $gBitSmarty->assign( 'filterType', $_REQUEST['ref_type'] ?? '' ); $gBitSmarty->assign( 'assemblyContentId', isset( $_REQUEST['assembly_content_id'] ) && is_numeric( $_REQUEST['assembly_content_id'] ) ? (int)$_REQUEST['assembly_content_id'] : null ); $gBitSmarty->assign( 'componentContentId', $componentContentId ); $gBitSmarty->assign( 'componentTitle', $componentTitle ); +$gBitSmarty->assign( 'packSize', $packSize ); $gBitSystem->display( 'bitpackage:stock/list_movements.tpl', 'Movements', [ 'display_mode' => 'list' ] ); diff --git a/list_stock.php b/list_stock.php index 141b187..9771d4e 100644 --- a/list_stock.php +++ b/list_stock.php @@ -36,6 +36,9 @@ if( $assemblyContentId ) { FROM `{$X}liberty_xref` sup WHERE sup.`content_id` = lc.`content_id` AND sup.`item` = '#SUP' ORDER BY sup.`xorder`) AS part_number, + (SELECT FIRST 1 CAST(pk.`xkey` AS DOUBLE PRECISION) + FROM `{$X}liberty_xref` pk + WHERE pk.`content_id` = lc.`content_id` AND pk.`item` = 'PCK') AS pack_size, (SELECT SUM( CASE WHEN EXISTS ( SELECT 1 FROM `{$X}liberty_xref` r WHERE r.`content_id` = mx.`content_id` AND r.`item` IN ('TRANS','ORDER') @@ -71,6 +74,9 @@ if( $assemblyContentId ) { FROM `{$X}liberty_xref` sup WHERE sup.`content_id` = lc.`content_id` AND sup.`item` = '#SUP' ORDER BY sup.`xorder`) AS part_number, + (SELECT FIRST 1 CAST(pk.`xkey` AS DOUBLE PRECISION) + FROM `{$X}liberty_xref` pk + WHERE pk.`content_id` = lc.`content_id` AND pk.`item` = 'PCK') AS pack_size, SUM( CASE WHEN EXISTS ( SELECT 1 FROM `{$X}liberty_xref` r WHERE r.`content_id` = x.`content_id` AND r.`item` IN ('TRANS','ORDER') @@ -112,8 +118,9 @@ foreach( $rows as $row ) { // In BOM view show all components; in general list respect hide-zero filter if( $assemblyContentId || !$hideZero || $level != 0 ) { $stockList[$cid]['stock'][$row['qty_type']] = [ - 'level' => $level, - 'bom_qty' => $row['bom_qty'] !== null ? (float)$row['bom_qty'] : null, + 'level' => $level, + 'bom_qty' => $row['bom_qty'] !== null ? (float)$row['bom_qty'] : null, + 'pack_size' => $row['pack_size'] !== null ? (float)$row['pack_size'] : null, ]; } } diff --git a/templates/list_movements.tpl b/templates/list_movements.tpl index aef033b..e50979e 100644 --- a/templates/list_movements.tpl +++ b/templates/list_movements.tpl @@ -55,7 +55,7 @@ {$mov.title|escape} {$mov.ref_type|escape|default:'—'} {if $componentContentId} - {$mov.cmp_qty|string_format:"%.0f"} {$mov.cmp_qty_type|escape} + {if $mov.cmp_qty_type eq 'PCK' && $packSize > 0}{math equation="q/p" q=$mov.cmp_qty p=$packSize format="%.2f"}{elseif $mov.cmp_qty_type eq 'SHT'}{$mov.cmp_qty|string_format:"%.2f"}{else}{$mov.cmp_qty|string_format:"%.0f"}{/if} {$mov.cmp_qty_type|escape} {/if} {if $mov.ref_start_date}{$mov.ref_start_date|bit_short_date}{else}—{/if} {if $mov.event_time}{$mov.event_time|bit_short_date}{else}—{/if} diff --git a/templates/list_stock.tpl b/templates/list_stock.tpl index edb6afe..6e9ede7 100644 --- a/templates/list_stock.tpl +++ b/templates/list_stock.tpl @@ -80,11 +80,11 @@ {$comp.data|escape} {$comp.part_number|escape} {/if} - {if $showBom}{math equation="b*k" b=$row.bom_qty k=$kitCount format="%.0f"}{/if} + {if $showBom}{if $qtype eq 'PCK' && $row.pack_size > 0}{math equation="b*k/p" b=$row.bom_qty k=$kitCount p=$row.pack_size format="%.2f"}{elseif $qtype eq 'SHT'}{math equation="b*k" b=$row.bom_qty k=$kitCount format="%.2f"}{else}{math equation="b*k" b=$row.bom_qty k=$kitCount format="%.0f"}{/if}{/if} {$qtype|escape} - {$row.level|string_format:"%.0f"} + {if $qtype eq 'PCK' && $row.pack_size > 0}{math equation="l/p" l=$row.level p=$row.pack_size format="%.2f"}{elseif $qtype eq 'SHT'}{$row.level|string_format:"%.2f"}{else}{$row.level|string_format:"%.0f"}{/if} {if $showBom} - {$remaining|string_format:"%.0f"} + {if $qtype eq 'PCK' && $row.pack_size > 0}{math equation="r/p" r=$remaining p=$row.pack_size format="%.2f"}{elseif $qtype eq 'SHT'}{$remaining|string_format:"%.2f"}{else}{$remaining|string_format:"%.0f"}{/if} {/if} {/foreach} diff --git a/templates/stockmovement/edit_xref_bom_item.tpl b/templates/stockmovement/edit_xref_bom_item.tpl new file mode 100644 index 0000000..29c1aa8 --- /dev/null +++ b/templates/stockmovement/edit_xref_bom_item.tpl @@ -0,0 +1,51 @@ +{strip} +
+
+

{tr}Edit Item{/tr}: {$gContent->getTitle()|escape}

+
+
+ {formfeedback error=$errors} + {form id="editXrefForm"} + + + + + +
+ {formlabel label="Component"} + {forminput} +

+ {$xrefInfo.xref_title|default:$xrefInfo.xref|escape} +

+ {/forminput} +
+ +
+ {formlabel label="Quantity" for="xkey"} + {forminput} + + {/forminput} +
+ +
+ {formlabel label="Ref designators" for="xkey_ext"} + {forminput} + + {/forminput} +
+ +
+ {formlabel label="Note" for="edit"} + {forminput} + + {/forminput} +
+ +
+ + +
+ {/form} +
+
+{/strip} diff --git a/templates/stockmovement/edit_xref_bompck_item.tpl b/templates/stockmovement/edit_xref_bompck_item.tpl new file mode 100644 index 0000000..bd69cff --- /dev/null +++ b/templates/stockmovement/edit_xref_bompck_item.tpl @@ -0,0 +1,52 @@ +{strip} +
+
+

{tr}Edit Item{/tr}: {$gContent->getTitle()|escape}

+
+
+ {formfeedback error=$errors} + {form id="editXrefForm"} + + + + + +
+ {formlabel label="Component"} + {forminput} +

+ {$xrefInfo.xref_title|default:$xrefInfo.xref|escape} +

+ {/forminput} +
+ +
+ {formlabel label="Pieces required" for="xkey"} + {forminput} + + {if $xrefInfo.pack_size}{tr}of{/tr} {$xrefInfo.pack_size|escape} {tr}per pack{/tr}{/if} + {/forminput} +
+ +
+ {formlabel label="Ref designators" for="xkey_ext"} + {forminput} + + {/forminput} +
+ +
+ {formlabel label="Note" for="edit"} + {forminput} + + {/forminput} +
+ +
+ + +
+ {/form} +
+
+{/strip} diff --git a/templates/view_component.tpl b/templates/view_component.tpl index f0b3c8a..37b42ef 100755 --- a/templates/view_component.tpl +++ b/templates/view_component.tpl @@ -47,7 +47,7 @@ {foreach from=$componentStockLevels key=qtype item=level} {$qtype|escape} - {$level|string_format:"%.0f"} + {if $qtype eq 'PCK' && $packSize > 0}{math equation="l/p" l=$level p=$packSize format="%.2f"}{elseif $qtype eq 'SHT'}{$level|string_format:"%.2f"}{else}{$level|string_format:"%.0f"}{/if} {/foreach} {else} diff --git a/view_component.php b/view_component.php index 82f7c18..2940441 100755 --- a/view_component.php +++ b/view_component.php @@ -59,7 +59,13 @@ if( $gContent->isValid() ) { foreach( $rows as $row ) { $stockLevels[$row['qty_type']] = (float)$row['stock_level']; } + $ps = $gBitDb->getOne( + "SELECT CAST(x.`xkey` AS DOUBLE PRECISION) FROM `".BIT_DB_PREFIX."liberty_xref` x + WHERE x.`content_id` = ? AND x.`item` = 'PCK'", + [ $gContent->mContentId ] + ); $gBitSmarty->assign( 'componentStockLevels', $stockLevels ); + $gBitSmarty->assign( 'packSize', $ps ? (float)$ps : null ); } require_once STOCK_PKG_INCLUDE_PATH.'display_stock_component_inc.php'; -- cgit v1.3