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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
|
<?php
/**
* @package liberty
* @subpackage classes
*/
namespace Bitweaver\Liberty;
use Bitweaver\BitBase;
use Bitweaver\BitDate;
/**
* Represents a single row in liberty_xref.
*
* liberty_xref is the live data table of the xref system. Each row attaches a
* typed key-value record to a content item:
*
* content_id — the liberty_content row this xref belongs to
* item — the slot key (matches liberty_xref_item.item), e.g. '#P', 'REQN', 'SGL'
* xorder — sequence within multiple-cardinality items; 0 for single items
* xref — optional FK to another content_id (e.g. linked contact or component)
* xkey — short key value (max 32 chars), e.g. SCREF code, postcode, quantity
* xkey_ext — longer extension of xkey (max 250 chars), e.g. pipe-separated name parts
* data — free-text blob, e.g. notes or a "from" label
* start_date — when this xref became active (NULL = open / not date-bounded)
* end_date — when this xref expired (NULL = still active)
* last_update_date — last write timestamp
*
* This class handles load/verify/store for a single row. For bulk loading of all
* xref rows belonging to a content item, use LibertyXrefGroup / LibertyXrefInfo.
*
* The stepXref() method implements an audit-trail pattern: instead of updating a row
* in place it closes the current row (sets end_date) and opens a new one, preserving
* history. Expired rows are swept into the synthetic 'history' group by LibertyXrefInfo.
*/
class LibertyXref extends LibertyBase {
/** x_group value of the loaded xref's item definition */
public $mType;
/** item key of the loaded row (matches liberty_xref_item.item) */
public $mItem;
/** primary key of the loaded liberty_xref row */
public $mXrefId;
/** content_id this xref belongs to */
public $mContentId;
/** BitDate instance used for start/end date conversions */
public $mDate;
/** when set, scopes liberty_xref_item lookups to this content type */
public $mContentTypeGuid = '';
public function __construct( $iXrefId = NULL ) {
$this->mXrefId = NULL;
$this->mItem = NULL;
parent::__construct();
if( $iXrefId ) {
$this->load( $iXrefId );
}
$this->mDate = new BitDate();
$this->mDate->get_display_offset();
}
/** @return bool true if a row has been loaded (mXrefId is a valid integer) */
public function isValid() {
return $this->verifyId( $this->mXrefId );
}
/**
* Load a single liberty_xref row by its primary key.
*
* Joins liberty_xref_item to resolve the group (mType), item display title,
* and template. Also derives source_title by appending xorder to the item
* title for multi-cardinality rows, and computes ignore_start/end_date flags
* so templates can treat NULL dates as "not set".
*
* On success populates mXrefId, mContentId, mType, mItem, and mInfo.
*
* @param int|null $pXrefId liberty_xref.xref_id to load
*/
public function load( $pXrefId = NULL ) {
if( BitBase::verifyId( $pXrefId ) ) {
$guidFilter = !empty( $this->mContentTypeGuid ) ? "AND s.`content_type_guid` = ?" : '';
$bindVars = !empty( $this->mContentTypeGuid ) ? [ $this->mContentTypeGuid, $pXrefId ] : [ $pXrefId ];
$sql = "SELECT x.*, CASE
WHEN x.`xorder` = 0 THEN s.`cross_ref_title`
ELSE s.`cross_ref_title` || '-' || x.`xorder` END
AS source_title, s.`item`, s.`x_group`,
CASE WHEN x.`start_date` IS NULL THEN 'y' ELSE 'n' END AS `ignore_start_date`,
CASE WHEN x.`end_date` IS NULL THEN 'y' ELSE 'n' END AS `ignore_end_date`,
s.`cross_ref_title` AS `template_title`, s.`template`
FROM `".BIT_DB_PREFIX."liberty_xref` x
JOIN `".BIT_DB_PREFIX."liberty_xref_item` s ON s.`item` = x.`item` $guidFilter
WHERE x.`xref_id` = ?
ORDER BY x.`xorder`";
$result = $this->mDb->getRow( $sql, $bindVars );
if( $result['content_id'] ) {
$this->mXrefId = $pXrefId;
$this->mContentId = $result['content_id'];
$this->mType = $result["x_group"];
$this->mItem = $result['item'];
$this->mInfo['title'] = $result['source_title'];
$this->mInfo['format_guid'] = 'text';
unset( $result['source_title'] );
$this->mInfo['data'] = $result;
}
}
}
/**
* Validate and normalise the param hash before store().
*
* Reads raw POST/form values from $pParamHash and builds a clean
* $pParamHash['xref_store'] array ready for associateInsert/Update.
*
* Special flags in the hash:
* fAddXref — new row for a multiple-cardinality item; auto-increments xorder
* fStepXref — audit-trail step: opens a new row as the continuation of this one
*
* Date fields (start_Month/Day/Year/Hour/Minute + ignore_start_date etc.) are
* converted from display timezone to UTC and stored as SQL TIMESTAMP strings.
* Setting ignore_start_date/ignore_end_date to 'on' stores NULL for that date.
*
* @param array &$pParamHash in/out; errors appended to $this->mErrors on failure
* @return bool true if no errors
*/
public function verify( &$pParamHash ) {
$pParamHash['xref_id'] = ( @$this->verifyId( $pParamHash['xref_id'] ) ) ? (int) $pParamHash['xref_id'] : null;
if( isset( $pParamHash['content_id'] ) ) {
$pParamHash['xref_store']['content_id'] = $pParamHash['content_id'];
}
if( isset( $pParamHash['item'] ) ) {
$pParamHash['xref_store']['item'] = $pParamHash['item'];
}
$pParamHash['xref_store']['xorder'] = 0;
if( isset( $pParamHash['fAddXref'] ) ) {
$pParamHash['xref_store']['item'] = isset( $pParamHash['Array_xref_type_list'] ) ? $pParamHash['Array_xref_type_list']['Array.item'] : $pParamHash['item'];
$pParamHash['xref_store']['content_id'] = $pParamHash['content_id'];
$guidWhere = !empty( $this->mContentTypeGuid ) ? "AND x.`content_type_guid` = ?" : '';
$guidBind = !empty( $this->mContentTypeGuid ) ? [ $pParamHash['xref_store']['item'], $this->mContentTypeGuid ] : [ $pParamHash['xref_store']['item'] ];
$sql = "SELECT x.`multiple` FROM `".BIT_DB_PREFIX."liberty_xref_item` x WHERE x.`item` = ? $guidWhere";
$next = $this->mDb->getOne( $sql, $guidBind );
if( $next > 0 ) {
$sql = "SELECT COALESCE( MAX(x.`xorder`) + 1, 1 ) FROM `".BIT_DB_PREFIX."liberty_xref` x WHERE x.`content_id` = ? AND x.`item` = ?";
$next = $this->mDb->getOne( $sql, [ $pParamHash['xref_store']['content_id'], $pParamHash['xref_store']['item'] ] );
}
$pParamHash['xref_store']['xorder'] = $next;
}
if( isset( $pParamHash['fStepXref'] ) ) {
$pParamHash['xref_store']['item'] = $this->mItem;
$pParamHash['xref_store']['xorder'] = $this->mInfo['data']['xorder'] + 1;
$pParamHash['xref_store']['content_id'] = $this->mContentId;
$pParamHash['start_date'] = time();
$pParamHash['ignore_end_date'] = 'on';
$pParamHash['xref_store']['xref'] = 0;
$pParamHash['xref_store']['xkey'] = '';
$pParamHash['xref_store']['xkey_ext'] = '';
$pParamHash['xref_store']['data'] = '';
}
if( isset( $pParamHash['xorder'] ) ) { $pParamHash['xref_store']['xorder'] = (int)$pParamHash['xorder']; }
if( isset( $pParamHash['xref'] ) ) { $pParamHash['xref_store']['xref'] = $pParamHash['xref']; }
if( isset( $pParamHash['xkey'] ) ) { $pParamHash['xref_store']['xkey'] = $pParamHash['xkey']; }
if( isset( $pParamHash['xkey_ext'] ) ) { $pParamHash['xref_store']['xkey_ext'] = $pParamHash['xkey_ext']; }
if( isset( $pParamHash['edit'] ) ) { $pParamHash['xref_store']['data'] = $pParamHash['edit']; }
$pParamHash['xref_store']['last_update_date'] = $this->mDb->NOW();
if( !empty( $pParamHash['start_Month'] ) ) {
$dateString = $this->mDate->gmmktime(
$pParamHash['start_Hour'],
$pParamHash['start_Minute'],
$pParamHash['start_Second'] ?? 0,
$pParamHash['start_Month'],
$pParamHash['start_Day'],
$pParamHash['start_Year'],
);
$timestamp = $this->mDate->getUTCFromDisplayDate( $dateString );
if( $timestamp !== -1 ) { $pParamHash['start_date'] = $timestamp; }
}
if( !empty( $pParamHash['start_date'] ) ) { $pParamHash['xref_store']['start_date'] = $this->mDate->date("Y-m-d H:i:s", $pParamHash['start_date'], true); }
if( isset( $pParamHash['ignore_start_date'] ) && $pParamHash['ignore_start_date'] == 'on' ) { $pParamHash['xref_store']['start_date'] = null; }
if( !empty( $pParamHash['end_Month'] ) ) {
$dateString = $this->mDate->gmmktime(
$pParamHash['end_Hour'],
$pParamHash['end_Minute'],
$pParamHash['end_Second'] ?? 0,
$pParamHash['end_Month'],
$pParamHash['end_Day'],
$pParamHash['end_Year'],
);
$timestamp = $this->mDate->getUTCFromDisplayDate( $dateString );
if( $timestamp !== -1 ) { $pParamHash['end_date'] = $timestamp; }
}
if( !empty( $pParamHash['end_date'] ) ) { $pParamHash['xref_store']['end_date'] = $this->mDate->date("Y-m-d H:i:s", $pParamHash['end_date'], true); }
if( isset( $pParamHash['ignore_end_date'] ) && $pParamHash['ignore_end_date'] == 'on' ) { $pParamHash['xref_store']['end_date'] = null; }
return count( $this->mErrors ) == 0;
}
/**
* Persist one liberty_xref row (insert or update).
*
* Calls verify() first. Inserts if $pParamHash['xref_id'] is absent;
* updates the matching row otherwise. On insert, allocates a new xref_id
* from liberty_xref_seq and writes it back into $pParamHash['xref_id'].
* Always reloads the row after writing so mInfo reflects the stored state.
*
* IMPORTANT: always pass a named variable — this method takes &$pParamHash
* by reference and will fatal if given a literal array.
*
* @param array &$pParamHash see verify() for expected keys
* @return bool true on success
*/
public function store( &$pParamHash = NULL ) {
if( $this->verify( $pParamHash ) ) {
$table = BIT_DB_PREFIX."liberty_xref";
$this->mDb->StartTrans();
if( isset( $pParamHash['xref_id'] ) ) {
$this->mDb->associateUpdate( $table, $pParamHash['xref_store'], [ "xref_id" => $pParamHash['xref_id'] ] );
} else {
$this->mXrefId = $this->mDb->GenID( 'liberty_xref_seq' );
$pParamHash['xref_id'] = $this->mXrefId;
$pParamHash['xref_store']['xref_id'] = $this->mXrefId;
$this->mDb->associateInsert( $table, $pParamHash['xref_store'] );
}
$this->load( $this->mXrefId );
$this->mDb->CompleteTrans();
return true;
}
return false;
}
/**
* Store an xref row using the audit-trail stepping pattern.
*
* The expunge value in $pParamHash controls the step behaviour:
* 2 — close the current row now (end_date = now) and immediately open a fresh
* continuation row (fStepXref), preserving the full history chain
* 1 — close the current row (end_date = now) with no continuation; the xref
* is retired and will appear in the history group on next load
* 0 — update in place (end_date cleared); standard non-stepping store
*
* @param array &$pParamHash must include 'expunge' and 'xref_id'
* @return bool always true
*/
public function stepXref( &$pParamHash = NULL ) {
if( isset( $pParamHash["expunge"] ) ) {
switch( $pParamHash["expunge"] ) {
case 2:
$pParamHash['end_date'] = time();
$this->store( $pParamHash );
unset( $pParamHash['xref_id'] );
$pParamHash['fStepXref'] = 1;
break;
case 1:
$pParamHash['end_date'] = time();
break;
default:
$pParamHash['ignore_end_date'] = 'on';
break;
}
}
$this->store( $pParamHash );
return true;
}
}
|
