Store all data in JSON

[remcotolsma]

Currently not all payment and subscription data is stored in JSON: https://docs.google.com/spreadsheets/d/1CJHnHcr9s8lkh4Fq9HQmlAThCK69eXiTC3UktyeQKYw/edit?usp=sharing

I think the payment and subscription JSON object should always contain all data, post meta is purely for query purposes.

To Do

• [x] _pronamic_payment_action_url
• [x] _pronamic_payment_adyen_details_result
• [x] #226
• [x] _pronamic_payment_adyen_sdk_version
• [x] #231
• [x] _pronamic_payment_description
• [x] _pronamic_payment_entrance_code
• [x] #229
• [x] #237
• [x] #230
• [x] _pronamic_payment_mollie_change_payment_state_url
• [x] _pronamic_payment_mollie_customer_id
• [x] _pronamic_payment_omnikassa_2_merchant_order_id
• [x] #244
• [x] _pronamic_payment_recurring
• [x] _pronamic_payment_recurring_type
• [x] #232
• [x] _pronamic_payment_source_id
• [x] _pronamic_payment_version
• [x] _pronamic_payment_woocommerce_payment_method
• [x] _pronamic_payment_woocommerce_payment_method_title
• [x] _pronamic_subscription_config_id
• [x] _pronamic_subscription_description
• [x] _pronamic_subscription_key
• [x] _pronamic_subscription_mollie_customer_id
• [x] _pronamic_subscription_mollie_mandate_id
• [x] _pronamic_subscription_payment_method
• [x] _pronamic_subscription_source
• [x] _pronamic_subscription_source_id

Pronamic Pay meta

Payments

Key	In JSON	In post meta
_pronamic_payment_action_url	:x:	:white_check_mark:
_pronamic_payment_address	:white_check_mark:	:white_check_mark:
_pronamic_payment_adyen_details_result	:x:	:white_check_mark:
_pronamic_payment_adyen_payment_response	:x:	:white_check_mark:
_pronamic_payment_adyen_sdk_version	:x:	:white_check_mark:
_pronamic_payment_amount	:white_check_mark:	:white_check_mark:
_pronamic_payment_city	:white_check_mark:	:white_check_mark:
_pronamic_payment_config_id	:x:	:white_check_mark:
_pronamic_payment_consumer_account_number	:white_check_mark:	:white_check_mark:
_pronamic_payment_consumer_bic	:white_check_mark:	:white_check_mark:
_pronamic_payment_consumer_iban	:white_check_mark:	:white_check_mark:
_pronamic_payment_consumer_name	:white_check_mark:	:white_check_mark:
_pronamic_payment_country	:white_check_mark:	:white_check_mark:
_pronamic_payment_currency	:white_check_mark:	:white_check_mark:
_pronamic_payment_customer_name	:white_check_mark:	:white_check_mark:
_pronamic_payment_description	:x:	:white_check_mark:
_pronamic_payment_email	:white_check_mark:	:white_check_mark:
_pronamic_payment_end_date	:white_check_mark: (also through periods)	:white_check_mark:
_pronamic_payment_entrance_code	:x:	:white_check_mark:
_pronamic_payment_first_name	:white_check_mark:	:white_check_mark:
_pronamic_payment_issuer	:x:	:white_check_mark:
_pronamic_payment_key	:x:	:white_check_mark:
_pronamic_payment_language	:white_check_mark:	:white_check_mark:
_pronamic_payment_last_name	:white_check_mark:	:white_check_mark:
_pronamic_payment_locale	:white_check_mark:	:white_check_mark:
_pronamic_payment_method	:x:	:white_check_mark:
_pronamic_payment_mollie_change_payment_state_url	:x:	:white_check_mark:
_pronamic_payment_mollie_customer_id	:x:	:white_check_mark:
_pronamic_payment_omnikassa_2_merchant_order_id	:x:	:white_check_mark:
_pronamic_payment_order_id	:x:	:white_check_mark:
_pronamic_payment_recurring	:x:	:white_check_mark:
_pronamic_payment_recurring_type	:x:	:white_check_mark:
_pronamic_payment_source	:x:	:white_check_mark:
_pronamic_payment_source_id	:x:	:white_check_mark:
_pronamic_payment_start_date	:white_check_mark: (also through periods)	:white_check_mark:
_pronamic_payment_status	:white_check_mark:	:white_check_mark:
_pronamic_payment_subscription_id	:white_check_mark: through periods	:white_check_mark:
_pronamic_payment_telephone_number	:white_check_mark:	:white_check_mark:
_pronamic_payment_transaction_id	:white_check_mark:	:white_check_mark:
_pronamic_payment_user_agent	:white_check_mark:	:white_check_mark:
_pronamic_payment_user_ip	:white_check_mark:	:white_check_mark:
_pronamic_payment_version	:x:	:white_check_mark:
_pronamic_payment_woocommerce_payment_method	:x:	:white_check_mark:
_pronamic_payment_woocommerce_payment_method_title	:x:	:white_check_mark:
_pronamic_payment_zip	:white_check_mark:	:white_check_mark:

Subscriptions

Key	In JSON	In post meta
_pronamic_subscription_amount	:white_check_mark: through fases	:white_check_mark:
_pronamic_subscription_config_id	:x:	:white_check_mark:
_pronamic_subscription_currency	:white_check_mark: through fases	:white_check_mark:
_pronamic_subscription_customer_name	:white_check_mark:	:white_check_mark:
_pronamic_subscription_description	:x:	:white_check_mark:
_pronamic_subscription_email	:white_check_mark:	:white_check_mark:
_pronamic_subscription_end_date	:white_check_mark:	:white_check_mark:
_pronamic_subscription_expiry_date	:white_check_mark:	:white_check_mark:
_pronamic_subscription_key	:x:	:white_check_mark:
_pronamic_subscription_mollie_customer_id	:x:	:white_check_mark:
_pronamic_subscription_mollie_mandate_id	:x:	:white_check_mark:
_pronamic_subscription_next_payment	:white_check_mark:	:white_check_mark:
_pronamic_subscription_next_payment_delivery_date	:white_check_mark:	:white_check_mark:
_pronamic_subscription_payment_method	:x:	:white_check_mark:
_pronamic_subscription_source	:x:	:white_check_mark:
_pronamic_subscription_source_id	:x:	:white_check_mark:
_pronamic_subscription_start_date	:white_check_mark: through fases	:white_check_mark:
_pronamic_subscription_status	:white_check_mark:	:white_check_mark:

https://github.com/pronamic/wp-pronamic-pay/issues/212#issuecomment-931258268

[remcotolsma]

Nice to know:

SELECT
    ID,
    post_content->"$.customer.name",
    post_content
FROM
    wp_2_posts
WHERE
    post_type = 'pronamic_payment'
        AND
    post_mime_type = 'application/json'
        AND
    post_content->"$.total_amount.value" > 200
;