Export completed Pronamic Pay payments with Gravity Forms entries.

pronamic-pay-export-payments-gravityforms-entries.sql

SELECT
	post.ID,
	post.post_title,
	post.post_date,
	post.post_status,
	MAX( IF( meta.meta_key = "_pronamic_payment_description", meta.meta_value, NULL ) ) AS payment_description,
	MAX( IF( meta.meta_key = "_pronamic_payment_entrance_code", meta.meta_value, NULL ) ) AS payment_entrance_code,
	MAX( IF( meta.meta_key = "_pronamic_payment_language", meta.meta_value, NULL ) ) AS payment_language , 
	MAX( IF( meta.meta_key = "_pronamic_payment_amount", meta.meta_value, NULL ) ) AS payment_amount, 
	MAX( IF( meta.meta_key = '_pronamic_payment_currency', meta.meta_value, NULL ) ) AS payment_currency,
	MAX( IF( meta.meta_key = '_pronamic_payment_config_id', meta.meta_value, NULL ) ) AS payment_config_id,
	MAX( IF( meta.meta_key = '_pronamic_payment_status', meta.meta_value, NULL ) ) AS payment_status,
	MAX( IF( meta.meta_key = "_pronamic_payment_source", meta.meta_value, NULL ) ) AS payment_source,
	MAX( IF( meta.meta_key = "_pronamic_payment_source_id", meta.meta_value, NULL ) ) AS payment_source_id,
	MAX( IF( meta.meta_key = "_pronamic_payment_transaction_id", meta.meta_value, NULL ) ) AS payment_transaction_id, 
	MAX( IF( meta.meta_key = "_pronamic_payment_action_url", meta.meta_value, NULL ) ) AS payment_action_url,
	form.id AS form_id,
	form.title AS form_title,
	lead.id AS lead_id,
	lead.date_created AS lead_date_created,
	lead.ip AS lead_ip,
	lead.source_url AS lead_source_url,
	lead.user_agent AS lead_user_agent,
	lead.currency AS lead_currency,
	lead.payment_status AS lead_payment_status,
	lead.payment_date AS lead_payment_date,
	lead.payment_amount AS lead_payment_amount,
	lead.transaction_id AS lead_transaction_id,
	lead.status AS lead_status
FROM
	wp_posts AS post
		LEFT JOIN
	wp_postmeta AS meta
			ON post.ID = meta.post_id
		LEFT JOIN
	wp_postmeta AS meta_source_id
			ON meta_source_id.post_id = post.ID AND meta_source_id.meta_key = "_pronamic_payment_source_id"
		LEFT JOIN
	wp_rg_lead AS lead
			ON lead.id = meta_source_id.meta_value
		LEFT JOIN
	wp_rg_form AS form
			ON form.id = lead.form_id
WHERE
	post_type = "pronamic_payment"
		AND
	post_status = "payment_completed"
GROUP BY
	post.ID
;