General Linkpoint Question

[rnet]

I have found where linkpoint shows an order id ( a rather long number) and matched that to the linkpoint cc review screen in zen. I tried to create a report using a dump of linkpoint data, however I don't see a way to export the linkpoint payments from zen.

My plan was to do a pivot table to line up the orders and payments. There are too many orders to do this manually.

The problem is compounded since we take paypal, Visa, MC, Disc, Amex -- and only Visa, MC & Disc are listed.

I'm not sure how to get my arms around this data in a usable format.

[DrByte]

Studying the data and refining queries for an hour, I came up with this query, which you can run in phpMyAdmin and export the results as CSV or whatever you desire to import into a spreadsheet program so you can refine sorts as you desire, etc.

select lp.zen_order_id as order_num, 
  o.date_purchased, 
  concat(o.orders_status, ' - ',  os.orders_status_name) as orders_status,
  concat(lp.customer_id, ' - ', c.customers_lastname, ', ', c.customers_firstname) as customer, 
  o.cc_type, o.order_total, o.currency,
  lp.lp_trans_num as linkpoint_transaction_num, lp.transaction_result, 
  o.lp_avs as avs_response
from linkpoint_api lp, orders_status os,
  orders o left join customers c on c.customers_id = o.customers_id
where lp.zen_order_id = o.orders_id
  and lp.customer_id = o.customers_id
  and o.orders_status = os.orders_status_id
order by o.date_purchased, o.cc_type, o.orders_id;

It can probably be refined further, but this should get you most of what you need.

[rnet]

Great -- and thank you!!

I'll spend some time data mining and see if this will help us audit & verify the transactions.

I REALLY appreciate the support.