Pulling information from Commerce (Urgent Help).

[Dean_]

Guys I'm in need of some help.

We currently pull information from Nexus via a sql query.

This is to pull new members:

SELECT 
i.i_id as Invoice_ID,
m.member_id as Membership_Number,
m.name, 
c.cm_first_name,
c.cm_last_name, 
c.cm_address_1, 
c.cm_address_2, 
c.cm_city, 
c.cm_state, 
c.cm_zip, 
c.cm_country,
i.i_title, 
date_format(from_unixtime(p.PS_Start),'%d/%m/%Y') as Purchase_Start_Date,
date_format(from_unixtime(p.PS_Expire),'%d/%m/%Y')  as Purchase_End_Date,
i.i_status
FROM ibf_nexus_customers c
LEFT JOIN ibf_nexus_invoices i ON (i.i_member=c.member_id)
LEFT JOIN ibf_nexus_purchases p ON (p.ps_original_invoice=i.i_id)
LEFT JOIN ibf_nexus_ship_orders o ON (o.o_invoice=i.i_id)
LEFT JOIN ibf_members m ON (m.member_id = i.i_member)
where i.i_status = 'paid'
and i.i_title = 'Membership - 12 Months'
And i.i_id > 'XXX'

This is for people renewing:

SELECT i.i_id as Invoice_ID,
m.member_id as Membership_Number,
m.name, 
c.cm_first_name, 
c.cm_last_name, 
c.cm_address_1, 
c.cm_address_2, 
c.cm_city, 
c.cm_state, 
c.cm_zip, 
c.cm_country,
i.i_title, 
date_format(from_unixtime(t.t_date) ,'%d/%m/%Y') as Pay_Date,
date_format(from_unixtime(p2.PS_Expire),'%d/%m/%Y')  as Purchase_End_Date,
i.i_status
FROM ibf_nexus_customers c, 
ibf_nexus_invoices i,
ibf_nexus_purchases p2,
ibf_members m,
ibf_nexus_transactions t
where i.i_status = 'paid'
and i.i_id = t.t_invoice
and i.i_member=c.member_id
and p2.ps_member=m.member_id
and m.member_id = i.i_member
and i.i_title <> ('Membership - 12 Months')
and from_unixtime(t.t_date ) > 'YYYY-MM-DD'

This obviously doesn't work with Commerce now. As we normally take this info and special paste in to Excel as Macro and it then formats it correctly for us.

Do you guys have any idea how this can be achieved? Please try to keep it as simple as possible as I'm still currently learning and the person who made this a few years ago isn't around anymore.

 

Thanks!!!

[Dean_]

Just To add, we'd like to export purchases on a weekly/fortnightly basis so the address and member details could be mail merged. If anyone had an SQL script that exported Commerece purchase details and addresses.

[Dean_]

Would anyone be able to help or some guidance to see if this can be done?

[Dean_]

Bump!

Guys with knowledge I am willing to pay for this (if it can be done). I'm aware the separate fields are now combined in to a single field so I need a way to get this info out of the database!