ContactPaypalXref
Created by: Lester Caine, Last modification: 15 Oct 2010 (17:05 BST)
Contact XREF data is pulled from the paypal table using the following script. Additional XREF tags will be created for the transaction and other data as things are developed.
{code source=sql} INSERT INTO CONTACT_XREF SELECT NULL AS XREF_ID, s.CONTENT_ID, '#P' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, s.PHONE AS XKEY, '' AS XKEY, NULL AS DATA, CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE FROM PAYPAL s WHERE s.PHONE <> ''; INSERT INTO CONTACT_XREF SELECT NULL AS XREF_ID, s.CONTENT_ID, 'VAT_NO' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, s.VAT AS XKEY, '' AS XKEY_EXT, NULL AS DATA, CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE FROM PAYPAL s WHERE s.VAT <> ''; INSERT INTO CONTACT_XREF SELECT NULL AS XREF_ID, s.CONTENT_ID, '#E' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, s.FROM_EMAIL_ADDRESS AS XKEY_EXT, NULL AS DATA, CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE FROM paypal s WHERE s.TO_EMAIL_ADDRESS <> '' AND s.FROM_EMAIL_ADDRESS <> 'paypal@lsces.co.uk'; INSERT INTO CONTACT_XREF SELECT NULL AS XREF_ID, s.CONTENT_ID, '#E' AS SOURCE, 2 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, s.TO_EMAIL_ADDRESS AS XKEY_EXT, NULL AS DATA, CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE FROM paypal s WHERE s.TO_EMAIL_ADDRESS <> '' AND s.TO_EMAIL_ADDRESS <> 'paypal@lsces.co.uk'; INSERT INTO CONTACT_XREF SELECT NULL AS XREF_ID, s.CONTENT_ID, 'CUS_NO' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, CUST_NO AS XKEY_EXT, NULL AS DATA, CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE FROM PAYPAL s WHERE s.CUST_NO <> ''; {/code}