diff options
| -rwxr-xr-x | admin/schema_inc.php | 8 | ||||
| -rw-r--r-- | export_contacts.php | 153 |
2 files changed, 157 insertions, 4 deletions
diff --git a/admin/schema_inc.php b/admin/schema_inc.php index bc06b3b..50fc188 100755 --- a/admin/schema_inc.php +++ b/admin/schema_inc.php @@ -83,7 +83,7 @@ $gBitInstaller->registerSchemaDefault( CONTACT_PKG_NAME, [ "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_group` (`x_group`,`content_type_guid`,`title`,`sort_order`,`role_id`,`type_href`) VALUES ('type', 'contact','Contact Type List', 0,3,'')", "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_group` (`x_group`,`content_type_guid`,`title`,`sort_order`,`role_id`,`type_href`) VALUES ('contact','contact','General Contact Details', 1,3,'')", "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_group` (`x_group`,`content_type_guid`,`title`,`sort_order`,`role_id`,`type_href`) VALUES ('links', 'contact','Linked Contact Items', 2,3,'')", - "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_group` (`x_group`,`content_type_guid`,`title`,`sort_order`,`role_id`,`type_href`) VALUES ('account','contact','Account Details', 3,4,'')", + "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_group` (`x_group`,`content_type_guid`,`title`,`sort_order`,`role_id`,`type_href`) VALUES ('account','contact','Account Details', 3,3,'')", // --- liberty_xref_item (formerly contact_xref_source) --- // group: type @@ -108,9 +108,9 @@ $gBitInstaller->registerSchemaDefault( CONTACT_PKG_NAME, [ // group: links "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('SCREF','contact','links','Stock Source Reference',0,3,'../stock/?content_id=','text')", // group: account - "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('ACC_TO','contact','account','Account Turnover', 0,3,'../vat/?vat=', 'text')", - "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('SAGEID','contact','account','SAGE Account Reference', 0,3,'''sage''', 'text')", - "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('VAT_NO','contact','account','VAT Number', 0,3,'../vat/?vat=', 'text')", + "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('ACC_TO','contact','account','Account Turnover', 0,4,'../vat/?vat=', 'text')", + "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('ACCNO','contact','links','Account Number', 0,3,'', 'text')", + "INSERT INTO `" . BIT_DB_PREFIX . "liberty_xref_item` (`item`,`content_type_guid`,`x_group`,`cross_ref_title`,`multiple`,`role_id`,`cross_ref_href`,`template`) VALUES ('VAT_NO','contact','account','VAT Number', 0,4,'../vat/?vat=', 'text')", ] ); 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 ); |