From e792a1a73506f2c09b60528418a36f2c3035f399 Mon Sep 17 00:00:00 2001 From: Lester Caine Date: Tue, 9 Jun 2026 16:48:25 +0100 Subject: stock: shortages CSV/order workflow; list header layout MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit - list_stock: CSV export and Create Order from shortages report; floaticon icons for print/CSV/order; filter form moved into header - add_order: new page — pre-populate draft ORDER movement from shortages list, editable qty/delete per line, supplier autocomplete - list_movements: print icon; filter form moved into floaticon header - list_assemblies_simple: print icon added to floaticon Co-Authored-By: Claude Sonnet 4.6 --- add_order.php | 249 +++++++++++++++++++++++++++++++++++ list_stock.php | 18 +++ templates/add_order.tpl | 163 +++++++++++++++++++++++ templates/list_assemblies_simple.tpl | 1 + templates/list_movements.tpl | 40 +++--- templates/list_stock.tpl | 26 ++-- 6 files changed, 466 insertions(+), 31 deletions(-) create mode 100644 add_order.php create mode 100644 templates/add_order.tpl diff --git a/add_order.php b/add_order.php new file mode 100644 index 0000000..60b4eb3 --- /dev/null +++ b/add_order.php @@ -0,0 +1,249 @@ +verifyPermission( 'p_stock_create' ); + +if( !empty( $_REQUEST['fCancel'] ) ) { + header( 'Location: '.STOCK_PKG_URL.'list_stock.php?shortages=1' ); + die; +} + +$errors = []; +$fromPost = false; + +if( !empty( $_POST['fCreate'] ) ) { + $fromPost = true; + $refKey = trim( $_POST['ref_key'] ?? '' ); + $supplierCid = isset( $_POST['supplier_contact_id'] ) && is_numeric( $_POST['supplier_contact_id'] ) + ? (int)$_POST['supplier_contact_id'] : 0; + $componentIds = array_map( 'intval', (array)( $_POST['component_id'] ?? [] ) ); + $qtyTypesPost = (array)( $_POST['qty_type'] ?? [] ); + $qtysPost = (array)( $_POST['qty'] ?? [] ); + $orderedDatePost = trim( $_POST['ordered_date'] ?? '' ); + + if( $refKey === '' ) { + $errors[] = KernelTools::tra( 'Please enter an order reference.' ); + } elseif( !array_filter( $componentIds ) ) { + $errors[] = KernelTools::tra( 'No lines to order.' ); + } else { + $movement = new StockMovement(); + $paramHash = [ + 'title' => $refKey, + 'content_type_guid' => STOCKMOVEMENT_CONTENT_TYPE_GUID, + ]; + if( $movement->store( $paramHash ) ) { + $refHash = [ + 'content_id' => $movement->mContentId, + 'item' => 'ORDER', + 'xkey' => $refKey, + 'fAddXref' => 1, + ]; + if( $supplierCid ) { + $refHash['xref'] = $supplierCid; + } + if( $orderedDatePost !== '' ) { + $parts = explode( '/', $orderedDatePost ); + if( count( $parts ) === 3 ) { + $year = (int)$parts[2] < 100 ? 2000 + (int)$parts[2] : (int)$parts[2]; + $ts = (int)mktime( 0, 0, 0, (int)$parts[1], (int)$parts[0], $year ); + if( $ts ) $refHash['start_date'] = $ts; + } + } + $movement->storeXref( $refHash ); + + $xorder = 1; + foreach( $componentIds as $i => $cid ) { + if( !$cid ) continue; + $qty = (float)( $qtysPost[$i] ?? 0 ); + if( $qty <= 0 ) continue; + $qtype = in_array( $qtyTypesPost[$i] ?? '', [ 'SGL', 'PCK', 'SHT', 'VOL' ] ) + ? $qtyTypesPost[$i] : 'SGL'; + $lineHash = [ + 'content_id' => $movement->mContentId, + 'item' => $qtype, + 'xref' => $cid, + 'xkey' => (string)$qty, + 'xorder' => $xorder++, + 'fAddXref' => 1, + ]; + $movement->storeXref( $lineHash ); + } + header( 'Location: '.STOCK_PKG_URL.'edit_movement.php?content_id='.$movement->mContentId ); + die; + } + $errors = array_merge( $errors, $movement->mErrors ); + } +} + +$lines = []; + +if( $fromPost ) { + $cids = array_map( 'intval', (array)( $_POST['component_id'] ?? [] ) ); + $types = (array)( $_POST['qty_type'] ?? [] ); + $qs = (array)( $_POST['qty'] ?? [] ); + $meta = []; + if( $cids ) { + $metaRows = $gBitDb->getAll( + "SELECT lc.`content_id`, lc.`title`, + (SELECT FIRST 1 sup.`xkey` FROM `".BIT_DB_PREFIX."liberty_xref` sup + WHERE sup.`content_id` = lc.`content_id` AND sup.`item` = '#SUP' + ORDER BY sup.`xorder`) AS part_number + FROM `".BIT_DB_PREFIX."liberty_content` lc + WHERE lc.`content_id` IN (".implode( ',', array_fill( 0, count( $cids ), '?' ) ).")", + $cids + ); + foreach( $metaRows as $r ) { + $meta[$r['content_id']] = [ 'title' => $r['title'], 'part_number' => $r['part_number'] ]; + } + } + foreach( $cids as $i => $cid ) { + if( !$cid ) continue; + $qtype = in_array( $types[$i] ?? '', [ 'SGL', 'PCK', 'SHT', 'VOL' ] ) ? $types[$i] : 'SGL'; + $lines[] = [ + 'component_id' => $cid, + 'title' => $meta[$cid]['title'] ?? '', + 'part_number' => $meta[$cid]['part_number'] ?? '', + 'qty_type' => $qtype, + 'qty' => (float)( $qs[$i] ?? 0 ), + ]; + } + $supplierVal = trim( $_POST['ref_from'] ?? '' ); + $supplierCidVal = (int)( $_POST['supplier_contact_id'] ?? 0 ); + $refKeyVal = trim( $_POST['ref_key'] ?? '' ); + $orderedDateVal = trim( $_POST['ordered_date'] ?? date( 'd/m/Y' ) ); +} else { + $assemblyContentId = isset( $_REQUEST['assembly_content_id'] ) && is_numeric( $_REQUEST['assembly_content_id'] ) + ? (int)$_REQUEST['assembly_content_id'] : null; + $kitCount = isset( $_REQUEST['kit_count'] ) && is_numeric( $_REQUEST['kit_count'] ) && (float)$_REQUEST['kit_count'] > 0 + ? (float)$_REQUEST['kit_count'] : 1; + $find = trim( $_REQUEST['find'] ?? '' ); + + $X = BIT_DB_PREFIX; + $bindVars = []; + + if( $assemblyContentId ) { + $findSql = $find !== '' ? " AND UPPER(lc.`title`) LIKE ?" : ''; + if( $find !== '' ) $bindVars[] = '%'.strtoupper( $find ).'%'; + + $query = "SELECT lc.`content_id`, lc.`title`, + bom.`item` AS qty_type, + CAST(bom.`xkey` AS DOUBLE PRECISION) AS bom_qty, + (SELECT FIRST 1 sup.`xkey` + 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') + ) THEN CAST(mx.`xkey` AS DOUBLE PRECISION) + ELSE -CAST(mx.`xkey` AS DOUBLE PRECISION) END ) + FROM `{$X}liberty_xref` mx + INNER JOIN `{$X}liberty_content` mc ON mc.`content_id` = mx.`content_id` + AND mc.`content_type_guid` = 'stockmovement' + WHERE mx.`xref` = lc.`content_id` + AND mx.`item` = bom.`item` + AND mx.`xkey` SIMILAR TO '[0-9]+(\.[0-9]+)?') AS stock_level + FROM `{$X}liberty_content` lc + INNER JOIN `{$X}liberty_xref` bom ON bom.`content_id` = ? + AND bom.`item` IN ('SGL','PCK','SHT','VOL') + AND bom.`xref` = lc.`content_id` + WHERE lc.`content_type_guid` = 'stockcomponent' + $findSql + ORDER BY bom.`xorder`"; + $bindVars[] = $assemblyContentId; + } else { + $whereSql = ''; + if( $find !== '' ) { + $whereSql = " AND UPPER(lc.`title`) LIKE ?"; + $bindVars[] = '%'.strtoupper( $find ).'%'; + } + $query = "SELECT lc.`content_id`, lc.`title`, + x.`item` AS qty_type, + CAST(NULL AS DOUBLE PRECISION) AS bom_qty, + (SELECT FIRST 1 sup.`xkey` + 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') + ) THEN CAST(x.`xkey` AS DOUBLE PRECISION) + ELSE -CAST(x.`xkey` AS DOUBLE PRECISION) END ) AS stock_level + FROM `{$X}liberty_content` lc + INNER JOIN `{$X}liberty_xref` x ON x.`xref` = lc.`content_id` + AND x.`item` IN ('SGL','PCK','SHT','VOL') + AND x.`xkey` SIMILAR TO '[0-9]+(\.[0-9]+)?' + INNER JOIN `{$X}liberty_content` mc ON mc.`content_id` = x.`content_id` + AND mc.`content_type_guid` = 'stockmovement' + WHERE lc.`content_type_guid` = 'stockcomponent' + $whereSql + GROUP BY lc.`content_id`, lc.`title`, x.`item` + ORDER BY lc.`title`, x.`item`"; + } + + $rows = $gBitDb->query( $query, $bindVars ); + $stockList = []; + foreach( $rows as $row ) { + $cid = $row['content_id']; + $level = $row['stock_level'] !== null ? (float)$row['stock_level'] : 0.0; + if( !isset( $stockList[$cid] ) ) { + $stockList[$cid] = [ + 'content_id' => $cid, + 'title' => $row['title'], + 'part_number' => $row['part_number'], + ]; + } + $stockList[$cid]['stock'][$row['qty_type']] = [ + 'level' => $level, + 'bom_qty' => $row['bom_qty'] !== null ? (float)$row['bom_qty'] : null, + ]; + } + + foreach( $stockList as $comp ) { + foreach( $comp['stock'] as $qtype => $row ) { + $shortage = $assemblyContentId && $row['bom_qty'] !== null + ? $row['bom_qty'] * $kitCount - $row['level'] + : -$row['level']; + if( $shortage <= 0 ) continue; + $lines[] = [ + 'component_id' => $comp['content_id'], + 'title' => $comp['title'], + 'part_number' => $comp['part_number'], + 'qty_type' => $qtype, + 'qty' => $shortage, + ]; + } + } + + $supplierVal = ''; + $supplierCidVal = 0; + $refKeyVal = ''; + $orderedDateVal = date( 'd/m/Y' ); +} + +$gBitSmarty->assign( 'lines', $lines ); +$gBitSmarty->assign( 'supplierVal', $supplierVal ); +$gBitSmarty->assign( 'supplierCidVal', $supplierCidVal ); +$gBitSmarty->assign( 'refKeyVal', $refKeyVal ); +$gBitSmarty->assign( 'orderedDateVal', $orderedDateVal ); +$gBitSmarty->assign( 'contactLookupUrl', CONTACT_PKG_URL.'includes/lookup_contact.php' ); +$gBitSmarty->assign( 'errors', $errors ); + +$gBitSystem->display( 'bitpackage:stock/add_order.tpl', KernelTools::tra( 'Create Order' ), [ 'display_mode' => 'edit' ] ); diff --git a/list_stock.php b/list_stock.php index 4589334..75be18e 100644 --- a/list_stock.php +++ b/list_stock.php @@ -161,6 +161,24 @@ $assemblyListJson = json_encode( array_values( array_map( $assemblyList ) ) ); +if( $showShortages && isset( $_REQUEST['format'] ) && $_REQUEST['format'] === 'csv' ) { + header( 'Content-Type: text/csv; charset=utf-8' ); + header( 'Content-Disposition: attachment; filename="shortages.csv"' ); + $out = fopen( 'php://output', 'w' ); + fputcsv( $out, [ 'Part No', 'Qty' ], ',', '"', '' ); + foreach( $stockList as $comp ) { + if( empty( $comp['part_number'] ) ) continue; + foreach( $comp['stock'] as $qtype => $row ) { + $qty = $qtype === 'PCK' && $row['pack_size'] > 0 + ? abs( $row['level'] ) / $row['pack_size'] + : abs( $row['level'] ); + fputcsv( $out, [ $comp['part_number'], $qty ], ',', '"', '' ); + } + } + fclose( $out ); + exit; +} + $gBitSmarty->assign( 'stockList', $stockList ); $gBitSmarty->assign( 'assemblyListJson', $assemblyListJson ); $gBitSmarty->assign( 'assemblyContentId', $assemblyContentId ); diff --git a/templates/add_order.tpl b/templates/add_order.tpl new file mode 100644 index 0000000..db5aab8 --- /dev/null +++ b/templates/add_order.tpl @@ -0,0 +1,163 @@ +{strip} +
+
+

{tr}Create Order{/tr}

+
+ +
+ + {if $errors} +
+ {foreach $errors as $e}

{$e|escape}

{/foreach} +
+ {/if} + + {form ipackage="stock" ifile="add_order.php" method="post"} +
+ +
+ {formlabel label="Supplier" for="ref_from"} + {forminput} + +
+ + +
+ {/forminput} +
+ +
+ {formlabel label="Order Ref" for="ref_key"} + {forminput} + + {/forminput} +
+ +
+ {formlabel label="Order Date" for="ordered_date"} + {forminput} + + {/forminput} +
+ +
+ + {if $lines} + + + + + + + + + + + + {foreach $lines as $line} + + + + + + + + {/foreach} + +
{tr}Component{/tr}{tr}Part No{/tr}{tr}Type{/tr}{tr}Qty{/tr}
+ + + {$line.title|escape} + {$line.part_number|escape}{$line.qty_type|escape} + + + +
+ {else} +

{tr}No shortage lines found.{/tr}

+ {/if} + +
+ + +
+ + {/form} + +
+
+{/strip} + diff --git a/templates/list_assemblies_simple.tpl b/templates/list_assemblies_simple.tpl index 7d2eddf..4e91b8c 100644 --- a/templates/list_assemblies_simple.tpl +++ b/templates/list_assemblies_simple.tpl @@ -2,6 +2,7 @@
+ {minifind prompt="Assemblies" gallery_id=$smarty.request.gallery_id}

{tr}Assemblies{/tr}{if $gQueryUserId} {tr}by{/tr} {displayname user_id=$gQueryUserId}{/if}

diff --git a/templates/list_movements.tpl b/templates/list_movements.tpl index e50979e..fe741ee 100644 --- a/templates/list_movements.tpl +++ b/templates/list_movements.tpl @@ -2,10 +2,30 @@
+ {if $gBitUser->hasPermission('p_stock_create')} {biticon ipackage="icons" iname="list-add" iexplain="Add Requisition"} {biticon ipackage="icons" iname="view-task-add" iexplain="Add Movement"} {/if} +
+ {if $componentContentId}{/if} +
+
+ +
+
+ +
+ +
+

{tr}Movements{/tr}{if $componentTitle} — {$componentTitle|escape}{/if}

@@ -14,28 +34,8 @@ {if $componentContentId}

← {tr}Back to component{/tr}

- {/if} - {form ipackage="stock" ifile="list_movements.php" method="get"} -
-
- -
-
- -
- -
- {/form} - diff --git a/templates/list_stock.tpl b/templates/list_stock.tpl index d70c606..f42f435 100644 --- a/templates/list_stock.tpl +++ b/templates/list_stock.tpl @@ -3,14 +3,16 @@
-
-

{tr}Stock Levels{/tr}{if $assemblyTitle} — {$assemblyTitle|escape}{/if}

-
- -
- -
-
+ {if $showShortages} + {biticon ipackage="icons" iname="text-csv" iexplain="Download CSV"} + {if $gBitUser->hasPermission('p_stock_create')} + {biticon ipackage="icons" iname="view-task-add" iexplain="Create Order"} + {/if} + {/if} + +
@@ -41,15 +43,17 @@
- {if $showShortages} - - {/if} {if $showBom && $gBitUser->hasPermission('p_stock_create')} {tr}Create Requisition{/tr} {/if}
+
+

{tr}Stock Levels{/tr}{if $assemblyTitle} — {$assemblyTitle|escape}{/if}

+ + +
{if $stockList}
-- cgit v1.3