1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
<?php
/**
* @package stock
* @subpackage functions
*/
namespace Bitweaver\Stock;
require_once '../kernel/includes/setup_inc.php';
global $gBitSystem, $gBitSmarty, $gBitDb;
$gBitSystem->verifyPermission( 'p_stock_view' );
$find = trim( $_REQUEST['find'] ?? '' );
$hideZero = empty( $_REQUEST['show_zero'] );
$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;
$X = BIT_DB_PREFIX;
$bindVars = [];
if( $assemblyContentId ) {
// BOM view: start from BOM items so components with no movements still appear
$findSql = $find !== '' ? " AND UPPER(lc.`title`) LIKE ?" : '';
if( $find !== '' ) $bindVars[] = '%'.strtoupper( $find ).'%';
$query = "SELECT lc.`content_id`, lc.`title`, lc.`data`,
bom.`item` AS qty_type,
CAST(bom.`xkey` AS DOUBLE PRECISION) AS bom_qty,
bom.`xorder` AS bom_xorder,
(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 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 {
// General list: only components with movement history
$whereSql = '';
if( $find !== '' ) {
$whereSql = " AND UPPER(lc.`title`) LIKE ?";
$bindVars[] = '%'.strtoupper( $find ).'%';
}
$query = "SELECT lc.`content_id`, lc.`title`, lc.`data`,
x.`item` AS qty_type,
CAST(NULL AS DOUBLE PRECISION) AS bom_qty,
CAST(NULL AS INTEGER) AS bom_xorder,
(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,
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`, lc.`data`, x.`item`
ORDER BY lc.`title`, x.`item`";
}
$rows = $gBitDb->query( $query, $bindVars );
// Group by component for display
$stockList = [];
foreach( $rows as $row ) {
$cid = $row['content_id'];
$level = $row['stock_level'] !== null ? (float)$row['stock_level'] : 0.0;
if( !isset( $stockList[$cid] ) ) {
$bomXorder = $row['bom_xorder'] !== null ? (int)$row['bom_xorder'] : null;
$stockList[$cid] = [
'content_id' => $cid,
'title' => $row['title'],
'data' => $row['data'],
'part_number' => $row['part_number'],
'display_url' => STOCK_PKG_URL.'view_component.php?content_id='.$cid,
'bom_group' => $bomXorder !== null ? (int)floor( $bomXorder / 1000 ) : null,
'stock' => [],
];
}
// 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,
];
}
}
// General list only: drop components with no stock rows after zero filter
if( !$assemblyContentId && $hideZero ) {
$stockList = array_filter( $stockList, fn($c) => !empty( $c['stock'] ) );
}
// Assembly selector list
$assembly = new StockAssembly();
$listHash = [ 'show_empty' => true, 'sort_mode' => 'title_asc', 'max_records' => 500 ];
$assemblyList = $assembly->getList( $listHash );
// Load selected assembly title for display
$assemblyTitle = '';
if( $assemblyContentId && isset( $assemblyList[$assemblyContentId] ) ) {
$assemblyTitle = $assemblyList[$assemblyContentId]['title'];
}
// KLID map for assembly autocomplete
$asmIds = array_keys( $assemblyList );
$asmKlidMap = [];
if( $asmIds ) {
$klidRows = $gBitDb->getAll(
"SELECT x.`content_id`, x.`xkey` FROM `".BIT_DB_PREFIX."liberty_xref` x
WHERE x.`item` = 'KLID' AND x.`content_id` IN (".implode( ',', array_fill( 0, count( $asmIds ), '?' ) ).")",
$asmIds
);
foreach( $klidRows as $r ) { $asmKlidMap[$r['content_id']] = $r['xkey']; }
}
$assemblyListJson = json_encode( array_values( array_map(
fn( $i ) => [ 'id' => (int)$i['content_id'], 'text' => $i['title'], 'klid' => $asmKlidMap[$i['content_id']] ?? '' ],
$assemblyList
) ) );
$gBitSmarty->assign( 'stockList', $stockList );
$gBitSmarty->assign( 'assemblyListJson', $assemblyListJson );
$gBitSmarty->assign( 'assemblyContentId', $assemblyContentId );
$gBitSmarty->assign( 'assemblyTitle', $assemblyTitle );
$gBitSmarty->assign( 'find', $find );
$gBitSmarty->assign( 'showZero', !$hideZero );
$gBitSmarty->assign( 'showBom', (bool)$assemblyContentId );
$gBitSmarty->assign( 'kitCount', $kitCount );
$gBitSystem->display( 'bitpackage:stock/list_stock.tpl', 'Stock Levels', [ 'display_mode' => 'list' ] );
|
