Jump to content

Pulling information from Commerce (Urgent Help).


Dean_

Recommended Posts

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!!!

Link to comment
Share on other sites

  • 2 months later...

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...