schema: tighten account group role_id to 3; add SAGEID; add export_contacts.php

account xref_group and items: role_id 4 → 3 (Registered, not Editors).
ACC_TO, VAT_NO: same role_id correction.
ACCNO replaced with SAGEID (SAGE Account Reference).
export_contacts.php: new CSV export (one row per contact, phones/emails/address).

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

1 files changed, 153 insertions, 0 deletions

diff --git a/export_contacts.php b/export_contacts.php
new file mode 100644
index 0000000..380d94c
--- /dev/null
+++ b/export_contacts.php
@@ -0,0 +1,153 @@
+<?php
+/**
+ * Export all contacts to CSV.
+ * One row per contact; up to 3 phones, 2 emails.
+ * Address: xkey_ext = full address text, xkey = postcode; postcode lookup adds add1-county where available.
+ */
+
+namespace Bitweaver\Liberty;
+
+use Bitweaver\KernelTools;
+
+ob_start();
+require_once '../kernel/includes/setup_inc.php';
+
+if( !$gBitSystem->isPackageActive( 'contact' ) ) {
+	$gBitSystem->fatalError( 'Contact package not active' );
+}
+if( !$gBitUser->hasPermission( 'p_contact_view' ) ) {
+	$gBitSystem->fatalError( KernelTools::tra( 'Permission denied' ) );
+}
+ob_clean();
+
+// --- Query 1: basic contact info ---
+$sql = "SELECT lc.`content_id`, lc.`title`,
+		x00.`xkey_ext`   AS person_name,
+		xsc.`xkey`       AS scref
+		FROM `" . BIT_DB_PREFIX . "liberty_content` lc
+		LEFT JOIN `" . BIT_DB_PREFIX . "liberty_xref` x00 ON x00.`content_id` = lc.`content_id` AND x00.`item` = '\$00'
+		LEFT JOIN `" . BIT_DB_PREFIX . "liberty_xref` xsc ON xsc.`content_id` = lc.`content_id` AND xsc.`item` = 'SCREF'
+		WHERE lc.`content_type_guid` = 'contact'
+		ORDER BY lc.`title`";
+
+$result = $gBitDb->query( $sql );
+$contacts = [];
+while( $row = $result->fetchRow() ) {
+	$contacts[$row['content_id']] = $row + [
+		'types'   => [],
+		'phones'  => [],
+		'fax'     => '',
+		'emails'  => [],
+		'website' => '',
+		'notes'   => [],
+		'con'     => '',
+		'address' => null,
+		'vat_no'  => '',
+		'sage_id' => '',
+	];
+}
+
+if( empty( $contacts ) ) {
+	die( "No contacts found.\n" );
+}
+
+// --- Query 2: all active xref items for these contacts ---
+$sql = "SELECT x.`content_id`, x.`item`, xi.`cross_ref_title`, xi.`template`,
+		x.`xkey`, x.`xkey_ext`, x.`data`, x.`xorder`,
+		ap.`add1`, ap.`add2`, ap.`add3`, ap.`add4`, ap.`town`, ap.`county`
+		FROM `" . BIT_DB_PREFIX . "liberty_xref` x
+		JOIN  `" . BIT_DB_PREFIX . "liberty_xref_item` xi ON xi.`item` = x.`item` AND xi.`content_type_guid` = 'contact'
+		LEFT JOIN `" . BIT_DB_PREFIX . "address_postcode` ap ON ap.`postcode` = x.`xkey`
+		WHERE ( x.`end_date` IS NULL OR x.`end_date` > CURRENT_TIMESTAMP )
+		AND x.`content_id` IN (" . implode( ',', array_keys( $contacts ) ) . ")
+		ORDER BY x.`content_id`, x.`item`, x.`xorder`";
+
+$result = $gBitDb->query( $sql );
+while( $row = $result->fetchRow() ) {
+	$cid  = $row['content_id'];
+	$item = $row['item'];
+	if( !isset( $contacts[$cid] ) ) continue;
+
+	if( $item[0] === '$' ) {
+		$contacts[$cid]['types'][] = $row['cross_ref_title'];
+	} elseif( $item === '#P' ) {
+		$contacts[$cid]['phones'][] = [ $row['xkey'] ?? '', $row['data'] ?? '' ];
+	} elseif( $item === '#F' ) {
+		$contacts[$cid]['fax'] = $row['xkey'] ?? '';
+	} elseif( $item === '#E' ) {
+		$contacts[$cid]['emails'][] = $row['xkey'] ?? '';
+	} elseif( $item === '#W' ) {
+		$contacts[$cid]['website'] = $row['xkey'] ?? '';
+	} elseif( $item === '0' ) {
+		if( !empty( $row['data'] ) ) $contacts[$cid]['notes'][] = $row['data'];
+	} elseif( $item === 'CON' ) {
+		$contacts[$cid]['con'] = $row['xkey'] ?? '';
+	} elseif( $item === 'VAT_NO' ) {
+		$contacts[$cid]['vat_no'] = $row['xkey'] ?? '';
+	} elseif( $item === 'SAGEID' ) {
+		$contacts[$cid]['sage_id'] = $row['xkey'] ?? '';
+	} elseif( $row['template'] === 'address' && $contacts[$cid]['address'] === null ) {
+		// take first active address only
+		$contacts[$cid]['address'] = [
+			'type'     => $row['cross_ref_title'],
+			'address'  => $row['xkey_ext'] ?? '',   // full address text or house name
+			'postcode' => $row['xkey']     ?? '',
+			'add1'     => $row['add1']     ?? '',    // from postcode lookup
+			'add2'     => $row['add2']     ?? '',
+			'add3'     => $row['add3']     ?? '',
+			'add4'     => $row['add4']     ?? '',
+			'town'     => $row['town']     ?? '',
+			'county'   => $row['county']   ?? '',
+			'notes'    => $row['data']     ?? '',
+		];
+	}
+}
+
+// --- Output CSV ---
+header( 'Content-Type: text/csv; charset=utf-8' );
+header( 'Content-Disposition: attachment; filename="contacts_' . date( 'Y-m-d' ) . '.csv"' );
+
+$out = fopen( 'php://output', 'w' );
+
+fputcsv( $out, [
+	'content_id', 'title', 'type', 'scref',
+	'name_prefix', 'forename', 'surname', 'name_suffix',
+	'phone1', 'phone1_notes', 'phone2', 'phone2_notes', 'phone3', 'phone3_notes',
+	'fax',
+	'email1', 'email2',
+	'website',
+	'notes', 'contact_person',
+	'address_type', 'address', 'postcode', 'add1', 'add2', 'add3', 'add4', 'town', 'county', 'address_notes',
+	'vat_no', 'sage_id',
+], ',', '"', '' );
+
+foreach( $contacts as $cid => $c ) {
+	$name  = $c['person_name'] ? explode( '|', $c['person_name'], 4 ) : [];
+	$addr  = $c['address'] ?? [];
+
+	fputcsv( $out, [
+		$cid,
+		$c['title'],
+		implode( ', ', $c['types'] ),
+		$c['scref'] ?? '',
+		$name[0] ?? '', $name[1] ?? '', $name[2] ?? '', $name[3] ?? '',
+		$c['phones'][0][0] ?? '', $c['phones'][0][1] ?? '',
+		$c['phones'][1][0] ?? '', $c['phones'][1][1] ?? '',
+		$c['phones'][2][0] ?? '', $c['phones'][2][1] ?? '',
+		$c['fax'],
+		$c['emails'][0] ?? '', $c['emails'][1] ?? '',
+		$c['website'],
+		implode( ' | ', $c['notes'] ),
+		$c['con'],
+		$addr['type']     ?? '', $addr['address']  ?? '',
+		$addr['postcode'] ?? '',
+		$addr['add1']     ?? '', $addr['add2']     ?? '',
+		$addr['add3']     ?? '', $addr['add4']     ?? '',
+		$addr['town']     ?? '', $addr['county']   ?? '',
+		$addr['notes']    ?? '',
+		$c['vat_no'],
+		$c['sage_id'],
+	], ',', '"', '' );
+}
+
+fclose( $out );