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

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?

philip_clarke:

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.

carlvt88:

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

philip_clarke:

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 :cool:

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

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.

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

• Batch Status Updating & Batch Printing throws errors when you search for orders using the following options:
  = (equals)
  < (less than)
  Error messages:
  *"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."

"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."*I KNOW you've come up with a fix for this.. Would you be so kind as to share it with me???

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.

carlvt88:

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

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).

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.

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

carlvt88:

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.
carlvt88:

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

carlvt88:

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

i finally got the guts to run the code. It date stamped all the 'Date Posted' to 11/30/1999 00:00:00. Is there a way to run a statement to change the dates to the actual date of which the payment was made?

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

mooomers:

i finally got the guts to run the code. It date stamped all the 'Date Posted' to 11/30/1999 00:00:00. Is there a way to run a statement to change the dates to the actual date of which the payment was made?
I did that with the code in http://www.zen-cart.com/forum/showpost.php?p=802502&postcount=1466

set the id in the first query to the status id that your orders get when they arrive in the shop.

Jeroen

DivaVocals:

help me fix a long standing issue with Super Orders..
Should be long standing indeed, as the = in the array is set as == which would never work in that statement.

The <= is probably not working, either because of browser issue or some cleaner stripping html tags.

To make it work you could do this, first rewrite the array in <admin folder>/super_batch_status.php:

$ot_sign = array();
  $ot_sign[] = array('id' => '1',
                     'text' => ' > ' . DROPDOWN_GREATER_THAN);
  $ot_sign[] = array('id' => '2',
                     'text' => ' < ' . DROPDOWN_LESS_THAN);
  $ot_sign[] = array('id' => '3',
                     'text' => ' = ' . DROPDOWN_EQUAL_TO);
```now adjust the SQL query build to match the id's:

if (isset($_GET['order_total']) && zen_not_null($_GET['order_total'])) {
if ($_GET['ot_sign'] == 3) { $sign_operator = '='; }
elseif ($_GET['ot_sign'] == 2) { $sign_operator = '<='; }
else { $sign_operator = '>='; }
$orders_query_raw .= " AND o.order_total " . $sign_operator . " '" . (int)$_GET['order_total'] . "'";
//$orders_query_raw .= " AND o.order_total " . $_GET['ot_sign'] . " '" . (int)$_GET['order_total'] . "'";
}

Jeroen

Wow!! Color me GRATEFUL!!!:clap: Gonna try and test this tonight..

nagelkruid:

Should be long standing indeed, as the = in the array is set as == which would never work in that statement.

The <= is probably not working, either because of browser issue or some cleaner stripping html tags.

To make it work you could do this, first rewrite the array in <admin folder>/super_batch_status.php:

$ot_sign = array();
$ot_sign[] = array('id' => '1',
'text' => ' > ' . DROPDOWN_GREATER_THAN);
$ot_sign[] = array('id' => '2',
'text' => ' < ' . DROPDOWN_LESS_THAN);
$ot_sign[] = array('id' => '3',
'text' => ' = ' . DROPDOWN_EQUAL_TO);

> ```
if (isset($_GET['order_total']) && zen_not_null($_GET['order_total'])) {
    if ($_GET['ot_sign'] == 3) { $sign_operator = '='; }
    elseif ($_GET['ot_sign'] == 2) { $sign_operator = '<='; }
      else { $sign_operator = '>='; }
      $orders_query_raw .= " AND o.order_total " . $sign_operator . " '" . (int)$_GET['order_total'] . "'";
    //$orders_query_raw .= " AND o.order_total " . $_GET['ot_sign'] . " '" . (int)$_GET['order_total'] . "'";
  }

nagelkruid:

finally, have a last look at these fields, as you will probably never use em while they're error free :cool:

JeroenI never used ANY of the batch functions because they were buggy and not FULLY functional.. Color me picky, but I like for things to work in full not in part.. :laugh: I'd disclose the bug to my clients so they could avoid the error.. Since most of my clients run small boutiqueish type stores, they all never felt like it was something they needed so they all chose not to use it.. But hot dog.. Boy am I happy to see you show up..

So if I post up some of my other pet peeves/errors you help with those too???:smile:

DivaVocals:

So if I post up some of my other pet peeves/errors you help with those too???:smile:
haha, at least you are not afraid to ask, lol.
well, as i am a father with 3 young children, just picked up a bachelor study, have a busy 40+ hour job, running a webstore next to that and trying to write something commaseparated that will flow right in our bookkeeping program, and fully aware this open source stuff is only open to mad street dogs, i guess i'ld say shoot any pet peeves/errors you may have just as long as you don't expect anything in the short term :lamo:

I just wonder, did you ever consider a basic php course, because if these errors are bugging you so much, they are fairly easy to solve with some determination and a little idea what php is about...

Cheers,
Jeroen

Nope.. Not afraid to ask at all..:laugh: It's part of what makes me a pretty good Business Analyst.. PERSERVERENCE!!! Okay.. I'm not unreasonable at all.. if you are willing to help, I'm willing to accept, and willing to be patient since you you are under no obligation to help ANYONE just 'cause they asked..:smile: I get how the world of tech forums work.. :smile: No one is obligated.. It's all a volunteer activity..

nagelkruid:

haha, at least you are not afraid to ask, lol.
well, as i am a father with 3 young children, just picked up a bachelor study, have a busy 40+ hour job, running a webstore next to that and trying to write something commaseparated that will flow right in our bookkeeping program, and fully aware this open source stuff is only open to mad street dogs, i guess i'ld say shoot any pet peeves/errors you may have just as long as you don't expect anything in the short term :lamo:

Given it some thought.. Been on the fence about this for a while.. The real issue for me is time.. Got a busy day job, busy side gig, drag racing, auntie duties, etc.. Despite that, I've gotten by so far.. with a little resourcefulness.. :smile:But truthfully the things that really are over my head I'll research to death to find answers, and anything after that I've outsourced to those much smarter than me as needed.. Been trying to find someone to make changes to Super Orders for me.. Besides the errors/malfunctioning issues, there are some functional changes I'd personally like to see in Super Orders.. (somewhere in this thread I outlined them..)

nagelkruid:

I just wonder, did you ever consider a basic php course, because if these errors are bugging you so much, they are fairly easy to solve with some determination and a little idea what php is about...

Cheers,
Jeroen

Okay.. so here goes... :smile: Two things:
Enter Payment function - If you check the "Notify the customer?" checkbox, the customer is notified. However, the "Status History" for the order indicates that the customer was not notified. Flag need to be fixed to accurately reflect the correct customer notification status.

and this

Batch Printing function - When no orders are selected, the error message should be a user friendly on-screen error message versus the very unfriendly message it shows now (which scares end-users): "Warning: Invalid argument supplied for foreach() in /home/content/o/v/e/USERNAME/html/FOLDERNAME/zentest1/admin/super_batch_forms.php on line 341
Error: No orders selected!"

nagelkruid:

i guess i'ld say shoot any pet peeves/errors you may have just as long as you don't expect anything in the short term :lamo:

DivaVocals:

Okay.. so here goes... :smile: Two things:
Enter Payment function - If you check the "Notify the customer?" checkbox, the customer is notified. However, the "Status History" for the order indicates that the customer was not notified. Flag need to be fixed to accurately reflect the correct customer notification status.

and this

Batch Printing function - When no orders are selected, the error message should be a user friendly on-screen error message versus the very unfriendly message it shows now (which scares end-users): "Warning: Invalid argument supplied for foreach() in /home/content/o/v/e/USERNAME/html/FOLDERNAME/zentest1/admin/super_batch_forms.php on line 341
Error: No orders selected!"

Those are pretty valid, thanks for letting me know as i didn't come accross these yet. I'll look at those.

Jeroen.

The notify the customer bug was fixed in version 47 of SO. If this isn't the issue or you can't upgrade, I could dig out the code...

carlvt88:

The notify the customer bug was fixed in version 47 of SO. If this isn't the issue or you can't upgrade, I could dig out the code...Let me test this again..:smile:

Okay.. So I just re-tested.. At first it didn't work.. Initially I had the exact same issue. (only opposite) When the the customer was not notified, the legend seemed to indicate that they had been..

I knew that the FEC add-on had code in it's super_orders.php file to correct the notification issue as well. So I was installing FEC anyway so I used Winmerge to merge the FEC code into the super_orders.php file. I grabbed the notification code from the FEC version of the super_orders.php file and replaced the rev 47 notify code, and it now works with the correct legend flags..

Not gonna do it now, but I'll have to go back and do a vanilla install of Zen and Super Orders to see what I did to break the Rev 47 super_orders.php file.