OLD Super Orders 2.0 (See v3.0 thread instead)

[carlvt88]

That is one of my primary uses - setting dozens of orders to 'delivered' in one shot. I've also tweaked it so that if we change the status to 'willcall', an email is generated using a template in catalog/email with instructions on picking up tickets at the willcall desk.

We also use the batch invoice functionality. It's a little odd when you have many (>5) invoices at once since the browser is jammed with many frames, but it works well. Again, I tweaked it to align the mailing addresses to the window envelopes we use, and to add other event information at the end of the invoice.

[mooomers]

Does this mean that it settles all the old orders, and for new orders we need to continue to manually settle and close the order?

I think carlvt88 is certainly along the correct lines, although I am not sure why the sort order is 999, but looking through some test data it does appear correct. I'd say that as a minimum the code should be

insert into so_payments (orders_id, payment_amount) 
select orders_id, value from zen_orders_total zot where zot.sort_order=999;

Which puts a space inbetween so_payments and the first bracket but leaves zen_orders_total as the default name, because I don't think the Install SQL patches box can add the correct table prefix to the second table in a sub-select query. Oh and one would need MySQL 4+ as the database backend in case anyone has joined this conversation late.

For the newbie the above code adds all the payments from the table orders_total (default name is zen_orders_total), to the super orders payments table so_payments which may on your system have zen_ stuck in front of the table name. If there are any DB_prefix errors that turn up using the above code, try adding or removing zen_ in front of the table name. if you get this error

ERROR: Cannot execute because table zen_so_payments(orders_id, does not exist.

it's because there is not a space in between the word payments and the first bracket in carlvt88's otherwise excellent code.

Philip.

[nagelkruid]

That is one of my primary uses - setting dozens of orders to 'delivered' in one shot.

Thanks carl, thats exactly what i need.

Is there any of you that knows or uses the data types.
I have played with it but i cant find the purpose. Only thing i can think of is that it may be used with the PO module and that you can select these data types for that module as the payment type once the order is paid?

As i will disable the PO option altogether, i think i can just make the payment type option, the cash report and the other report unavailable from the menu in the admin (as these will not be used/function anyway).

Just want to make sure that i wont break or miss out any cool options if i do so?

Thank you,
Jeroen

[nagelkruid]

I think carlvt88 is certainly along the correct lines, although I am not sure why the sort order is 999, but looking through some test data it does appear correct. I'd say that as a minimum the code should be

insert into so_payments (orders_id, payment_amount) 
select orders_id, value from zen_orders_total zot where zot.sort_order=999;

sort order = 999 is the default assigned to the ot_total class in the orders_total table. You can change the sort_order via admin in the menu under modules, but if you do, the above query will behave differently

[nagelkruid]

while we are on the subject, why not add the dates to this as well:

update so_payments sp, orders_status_history osh set sp.date_posted = osh.date_added where sp.orders_id = osh.orders_id AND osh.orders_status_id = 1;

and

update so_payments sp, orders_status_history osh set sp.last_modified = osh.date_added where sp.orders_id = osh.orders_id AND osh.orders_status_id = 3;

Note that the ordersatus id's represent 1= order accepted, and 3=order executed, which may different for others.

when i execute these, i add the prefix for my tables and apply them via dbadmin as the sql patch tool has given me errors too often. Not sure if these will work in that tool.

Cheers,
Jeroen

[DivaVocals]

That is one of my primary uses - setting dozens of orders to 'delivered' in one shot. I've also tweaked it so that if we change the status to 'willcall', an email is generated using a template in catalog/email with instructions on picking up tickets at the willcall desk.

We also use the batch invoice functionality. It's a little odd when you have many (>5) invoices at once since the browser is jammed with many frames, but it works well. Again, I tweaked it to align the mailing addresses to the window envelopes we use, and to add other event information at the end of the invoice.

Then you may be the man to help me fix a long standing issue with Super Orders..

• [FONT=Times New Roman]Batch Status Updating & Batch Printing throws errors when you search for orders using the following options:
  = (equals)
  < (less than)
  Error messages:
  [/FONT][FONT=Times New Roman]"1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''50' ORDER BY o.orders_id DESC' at line 4
  
  in:
  
  [SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_purchased, o.order_total, s.orders_status_name FROM zen_orders o LEFT JOIN zen_orders_status s ON o.orders_status = s.orders_status_id WHERE s.language_id = '1' AND o.order_total '50' ORDER BY o.orders_id DESC]
  
  If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."
  
  [/FONT][FONT=Times New Roman]"1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== '13' ORDER BY o.orders_id DESC' at line 4
  
  in:
  
  [SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_purchased, o.order_total, s.orders_status_name FROM zen_orders o LEFT JOIN zen_orders_status s ON o.orders_status = s.orders_status_id WHERE s.language_id = '1' AND o.order_total == '13' ORDER BY o.orders_id DESC]
  
  If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."[/FONT]

I KNOW you've come up with a fix for this.. Would you be so kind as to share it with me???

[carlvt88]

Philip wrote of this on page 125 of this thread - he's much more security conscous than I am but suggesed one fix. I'll try to look at that tonight, but I don't generally use those - I usually search by status code or shipment or payment method instead.

[nagelkruid]

Re paypal payments - Yes I did, another user posted an alternate approach also. I took the approach of intercepting the paypal transaction and adding it to the payments. The other approach was to look at paypal transactions when generating the report.

carl,
can you please set me on the right track here, i would like to add papal IPN stuff in the correct places and even more i will want to report on paypal totals and paypal costs.

thanks,
Jeroen

[carlvt88]

I guess the right approach depends on your needs. I'm doing the store for myself so I can be a little less complete than if I were providing a store to someone else. Also, I don't know where your comfort level is in playing with the database or coding with PHP and/or SQL.

For the SO reports, I actually think that the approach of making the reports check the paypal history for transactions may be a safer and cleaner way than what I did, but I was already down a certain path. My method of catching IPN transactions as they come in and posting to the SO tables keeps the database complete, but one could argue that the same data is being held in two places which usually is frowned upon.

Some other things that bother me with my implementation is that I don't get correct coloring of refunds, and the transactions are labeled 'parent' instead of something more usable like 'refund'. Maybe I'll fix these in the report generation functions. But, even with some complex payment reversals and reversal denials, it captured everything correctly and I've used this for two years now.

I'm happy to share code, but I don't think it's robust or documented enough to post for general sharing.

Same is true for the paypal fees. I added the fees capability to the cash report (I believe that's part of the sales-report package). It too works, but I haven't extensively tested it and simply hacked it together in an evening for a proof of concept. Dinki posted an sql query that would provide a result, but not in a report format (see post 395).

[nagelkruid]

I guess the right approach depends on your needs. I'm doing the store for myself so I can be a little less complete than if I were providing a store to someone else. Also, I don't know where your comfort level is in playing with the database or coding with PHP and/or SQL.

Same here, one shop, the lady doing the sales and me doing the coding

For the SO reports, I actually think that the approach of making the reports check the paypal history for transactions may be a safer and cleaner way than what I did, but I was already down a certain path. My method of catching IPN transactions as they come in and posting to the SO tables keeps the database complete, but one could argue that the same data is being held in two places which usually is frowned upon.

I didn't get that far yet, cash reports are not working and if i try to modify something in SO it pops me to the main page.
Something to be fixed there, but this is not primary concern right now.

Some other things that bother me with my implementation is that I don't get correct coloring of refunds, and the transactions are labeled 'parent' instead of something more usable like 'refund'. Maybe I'll fix these in the report generation functions. But, even with some complex payment reversals and reversal denials, it captured everything correctly and I've used this for two years now.

I'm happy to share code, but I don't think it's robust or documented enough to post for general sharing.

appreciate if you have anything useful i could use, but if it is in the cash reports i will probably have some fixing to do before i can put it to use in our shop...

Same is true for the paypal fees. I added the fees capability to the cash report (I believe that's part of the sales-report package). It too works, but I haven't extensively tested it and simply hacked it together in an evening for a proof of concept. Dinki posted an sql query that would provide a result, but not in a report format (see post 395).

Cheers, i am actually looking and working at/with sales reports, originally coded by blindside and ammended by Ceon.
That code comes close to what i need on the short term, which is an easy oversight of, say, sales over the past month with results for each order, orderid, total cost of order and paypal fee. then i want to export that in csv so i can easily import it in my bookkeeping program.

Jeroen