Resolved Export orders based on Order status

Nevermind. Filezilla was acting up on me.

Hello,

I am guessing the problem is because the product is no longer in VM.

The problem is not that the products are no longer there because the search only happens on the order item table. The issue is that the list is limited to 10 products and you have more than 10 products that start with the same 5 characters in your case. Usually you would continue typing to match the full SKU but your SKU ends at the first 5 characters. The search works on both the SKU or product name. You can filter the list on the name of the product to get that CJC1R product.

A work-around in your case would be to change the file administrator/components/com_csvi/addon/com_virtuemart/helper/com_virtuemart_json.php on line 106 and change the 10 to a higher number if you want more products to show up.

This is nothing new though, it has been there since a long time. It just happens that your database returns these 10 records as first items.

A downgrade is not necessary I think since you can filter the list on product name or that won't work either?

Roland,

A new problem has popped up with this after updating to the latest version, 6.5.4. If you recall, I have a site where I migrated old orders over to the new site. I did NOT migrate old items though and choose to create all new items from scratch. I have a number of old orders that I need to export that have item skus on them that are no longer in VM on the new site. When I try to filter in the template options for this item, I get no results to add to the filter. Example, the old product is CJC1R. If I type in CJC1R in the search box in options-filter, I get other products who's sku begins with CJC1R but not the old product. I have checked and I ABSOLUTELY have orders with CJC1R on them so that is not an issue. See the screen shot of what I am seeing:



I had conflicts with new VM products using the same VM Product ID, so a while back I did a sql query and changed the VM Product ID to 10000 in virtuemart_order_items table. Not sure if that makes any difference. Before the update, I was able to export these old orders just fine after applying the patches you provided.

I am guessing the problem is because the product is no longer in VM. I thought about just creating a new product in VM, then changing the productID in the database for that product to match the product ID used in the old orders but am concerned what that may do to old order balances, etc.

Any help you can provide on this would be greatly appreciated. I know you have probably already deleted the database I sent you but if you still have it or can recover it, please feel free to use it if you need to.

Alternatively, I can downgrade to the previous version and use it until I get past the old orders if that would be best. I take orders for products that have to be built. Once I get past them and am working with new orders placed on the new site, I could upgrade. Is there any trouble downgrading or anything special I would need to do if this is the easiest way to handle this?

Hello,

Glad to hear everything is working out

Woohoo! It appears to be working as expected with your latest patch and directions, Roland! I even used my version of the template rather than the one you provided above. I will put it through the paces to make sure there are no issues but as of now, it looks like problem solved!

THANK YOU SO MUCH for sticking with me on this and not just telling me it works and there is nothing more you can do. I actually had a developer recently do that to me. I hired a different dev who found a bug in his code that was causing me problems!

Your support, as always, is nothing but the best!

Hello,

I have dived into this some more. Please follow these steps and let me know how it goes.

1. Load this patch file via the maintenance menu
2. Go to Maintenance -> Tasks and click Reset and confirm the dialog.
3. Go to your testchamber template and edit it.
4. On the Options tab, remove all the products you have set in the product filter.
5. Add all the products you want to filter on and save the template
6. Go to export and run the export of the testchamber template.
7. Do you get anything in the export file?

Hello,

The error you see is because of a mix-up with the patch that has been applied.

Attached is a template version of the chambers that did export the data for me. You can load this template by going to Maintenance -> CSVI Pro -> Restore templates. After that select this template and it should show up on the export page.

Let me know if you get any output with this template.

Roland,

I am not sure what you are saying here. I am still not getting any results with the testchambers template. It comes up empty every time. The server cache has been cleared every time before I run the export. I just tried it again without making any changes to the template and got no data available. I then went into options in the template and deleted all the products in the filter. I added back CJC1r10 and tried to save the template. I got a blank screen and firebug showed no issues. Then went back to the main home page for the admin area and a warning message popped up. I refreshed the page and the error message was gone but when I went to CSVI and clicked on templates, then testchambers, the same error message showed back up. Here is the error message:



Now I have no idea what to do. I can't get the data I need and have no idea what is going on or how to get it. PLEASE HELP!

Hello,

So I have been checking out the issues you are seeing and I am going to run by them one by one here. My test setup is your database with CSVI 6.5.2 and the patch provided to you in the other thread. After applying the patch, one thing I had to do was clear my caches because the old JavaScript file was loaded.

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?

That is because these items have never been ordered, at least they are not stored with that SKU in the order items. So there is nothing to be found when searching for them.

If I go back and add CJC1r, I get the terribly long list again.

I added this product as an ordered product to the options but after reloading the template I only see 1 product. Not sure what has happened there in your case.

There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong.

The testchambers template I exported as well without any changes to it, this came up empty. The reason for that is, the selected products are stored with their IDs and not their SKUs. This is a change due to the patch that has been applied. So here you just need to re-add the products.

Let me know how it goes and if there is anything else you want me to check.

Hello,

I believe I have everything I need, so will check it out. Tomorrow I will have feedback for you. Thank you.

Thank you Roland. I will anxiously await your reply! Please let me know if you need anything else.

On the database tables...there are a few that I installed, tried to work with, and found them to be complete JUNK so I uninstalled them. I do understand a developer wanting to leave them in case the user decides to reinstall or upgrade and not lose data.

Hello,

Not many developers I would trust with that but I have "known" you for years now and trust that it will be fully deleted and destroyed once testing is done.

Thank you, I appreciate it. All data will be handled confidential. I have the file, so you can delete it.

Yes, but if it did not find the item on the list, it would just skip over it and not return any results. I believe one of your older version of CSVI allowed this but could be wrong.

That is something I will investigate with your data. The code in that respect hasn't changed as far as I can remember.

My issue is one of the products I make is made to order and I have a fairly long lead time. The export I do is for just orders that include those items and this is how I keep track of my list of orders that have to be made. I only run that export twice a month or so and it is really not a huge issue to need to go into the options and do a new search for any new items, as long as I remember to do it! I was thinking of cron for other exports I may want to set up in the future.

All clear, just thinking outside the box where possible

There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong.

Now that I have a copy of your data, I can check it as well.

can I safely delete the tables they sometimes leave behind?

Yes you can, if you no longer need them.

There is a good reason for extensions not deleting their tables on uninstallation. CSVI is one of them The reason behind it is, some people uninstall the extension and then install the new version. By doing that you would lose all your settings, not something many people are pleased with. That is the reason I implemented the option in Maintenance to delete all CSVI tables. A bit of a workaround but at least you can delete them if you want and so consciously.

I have sent the db export to you via pm. I know this is COMPLETELY unrelated to CSVI so if you don't answer, I will understand but since you are a Joomla! database guru, I thought I would ask! If I have installed some extensions and then removed them since I don't like them, can I safely delete the tables they sometimes leave behind? I have found that many extensions do not clean up and remove their database tables and like to keep things tidy!

My replies in red. (I hope that is a good format for you!)

Hello,

I don't want or care about the product status actually.

Aha, that got lost in translation.
[color=#ff0000]Sorry about that![/color]

If I can get the operation=orders to work, I will be golden!

We will and let's focus on that.

I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?

Yes, you can use PhpMyAdmin. What you do there is select the database in the tree on the left pane. After that click Export on the toolbar at the top. This will give you the export page. There you click Go. This will export the complete database in SQL and is probably the easiest way to go.
[color=#ff0000]I did not realize you wanted the entire database. I can certainly do that. Not many developers I would trust with that but I have "known" you for years now and trust that it will be fully deleted and destroyed once testing is done. I surely don't want my customer data, especially e-mails getting out on a spam list somewhere.[/color]

However, woudl it be possible to be have it so it will search all products in VM that match?

The search filter searches all the sold products because you can't export orders of products that have not been sold because they don't exist.
[color=#ff0000]Yes, but if it did not find the item on the list, it would just skip over it and not return any results. I believe one of your older version of CSVI allowed this but could be wrong.[/color]

This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export.

Thinking about a new approach here, the latest version of CSVI has the option to export everything since the last run. So you run your export on Monday morning where you set the filters. On Tuesday morning, you remove the filters and just export the orders since Monday morning by setting the order date to last run. On Wednesday morning, don't make any changes to the template but just run it and you get the orders from Tuesday till Wednesday. So you continue, it will always be the orders since last run. The reason the template needs to be changed on Tuesday morning is because you need to have a baseline for the Since last run. Perhaps this is something of use to you? This way you can also use it with cron.
[color=#ff0000]My issue is one of the products I make is made to order and I have a fairly long lead time. The export I do is for just orders that include those items and this is how I keep track of my list of orders that have to be made. I only run that export twice a month or so and it is really not a huge issue to need to go into the options and do a new search for any new items, as long as I remember to do it! I was thinking of cron for other exports I may want to set up in the future.[/color]

I looked at the debug log you sent, there is nothing wrong with the query, so if there is nothing in the export file all I can think of is that the query hasn't found any results.
[color=#ff0000]There are absolutely orders in VM that meet the criteria of the export. I double checked it numerous times. I will check again just to make sure I didn't do something wrong[/color].

I just did a brand new template. I triple checked that I had everything correct. Attached is a screen show of the optoins page. I chose a date range that I KNOW has orders that meet the filter criteria set. I actually double checked them in CM before setting the filters. I am still getting the no data results. I am also getting duplicate entries in the order product filter even though I only clicked on the product in the search box one time and it only showed one before I saved. After saving, if open options again, that is when I have duplicates.

Hello,

I don't want or care about the product status actually.

Aha, that got lost in translation.

If I can get the operation=orders to work, I will be golden!

We will and let's focus on that.

I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?

Yes, you can use PhpMyAdmin. What you do there is select the database in the tree on the left pane. After that click Export on the toolbar at the top. This will give you the export page. There you click Go. This will export the complete database in SQL and is probably the easiest way to go.

However, woudl it be possible to be have it so it will search all products in VM that match?

The search filter searches all the sold products because you can't export orders of products that have not been sold because they don't exist.

This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export.

Thinking about a new approach here, the latest version of CSVI has the option to export everything since the last run. So you run your export on Monday morning where you set the filters. On Tuesday morning, you remove the filters and just export the orders since Monday morning by setting the order date to last run. On Wednesday morning, don't make any changes to the template but just run it and you get the orders from Tuesday till Wednesday. So you continue, it will always be the orders since last run. The reason the template needs to be changed on Tuesday morning is because you need to have a baseline for the Since last run. Perhaps this is something of use to you? This way you can also use it with cron.

I looked at the debug log you sent, there is nothing wrong with the query, so if there is nothing in the export file all I can think of is that the query hasn't found any results.

My notes in Red

Hello,

You know there is an Edit button below the post if you want to add more information to it Going to combine all your posts in one here.
[color=#ff0000]Sorry about that! On another support forum I use, they get pissy if you go in and edit the post to combine things![/color]

I have been using CSVI since way back in VM1.1 days! I like having the code and the status name. Don't know why but I do!

Perfect !!

If I do a template using operation=order rather than order items, I should be able to get the information I need, correct?

You won't get the product status.
[color=#ff0000]I don't want or care about the product status actually. All I need and want is the order status. I am sorry I did not make that more clear in the original post. I had tried doing an export with operation=order and had the issue with no data so I assumed I had to use operation=order items which of course only gives me the product status. In the original post, I was looking for a way to change the product status to match the order status so I could then do an operation=order items export and get the orders I am looking for. My shipping software changes the order status in the database but does not change the product status. If I do an export based on product status, I get all orders from way back and have no idea what the order status is. If I can get the operation=orders to work, I will be golden![/color]

Of course if I try the export, I get a no data results.

If you can post the debug log of your export we can see what filters are being applied.
[color=#ff0000]Will do this evening.[/color]

This time I added the VM Order ID filed and placed it at the top in case that matters.

The location of the fields have no influence on the export.

If I go back and add CJC1r, I get the terribly long list again.

Would it be possible to send me a dump of your VirtueMart tables? I should be able to reproduce the long list of items then. This will make it easier to track down why that happens. You can send the dump in a private message.
[color=#ff0000]OK, you are getting a little over my experience level here! I can certainly work in phymyadmin and am not scared to do so. What tables do you need or is there a better way to do this? If I use phpmyadmin, do you want them in sql or csv?[/color]

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?

If they have never been ordered they won't show up. The search is only on ordered items. If they have been ordered, I would need to check closer as to see why that won't happen. With the dump, that will make it easier to investigate.

[color=#ff0000]I don't believe there is a problem and it is working as designed. However, woudl it be possible to be have it so it will search all products in VM that match? This is a something I export on a regular basis. As is, I will need to go into the template and do a new search to make sure new ones have not been ordered every time I want to run the export. I would also not be able to use cron (have not done so before but may decided to start). If it included all skus that meet the criteria rather than just what has been ordered, I woudl be able to use the export without having to remember to make the changes each time. The less I have to remember, the less chance I will mess something up![/color]

Hello,

You know there is an Edit button below the post if you want to add more information to it Going to combine all your posts in one here.

I have been using CSVI since way back in VM1.1 days! I like having the code and the status name. Don't know why but I do!

Perfect !!

If I do a template using operation=order rather than order items, I should be able to get the information I need, correct?

You won't get the product status.

Of course if I try the export, I get a no data results.

If you can post the debug log of your export we can see what filters are being applied.

This time I added the VM Order ID filed and placed it at the top in case that matters.

The location of the fields have no influence on the export.

If I go back and add CJC1r, I get the terribly long list again.

Would it be possible to send me a dump of your VirtueMart tables? I should be able to reproduce the long list of items then. This will make it easier to track down why that happens. You can send the dump in a private message.

I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?

If they have never been ordered they won't show up. The search is only on ordered items. If they have been ordered, I would need to check closer as to see why that won't happen. With the dump, that will make it easier to investigate.

Also, I have more CJC1r skus but they do not show up on the search list. I have CJC1r22 through CJC1r48. Even if I search on them specifically, they do not show up. Why is that?

And one more test! This time, I used the same operations as above and the same fields. In options, set the same order status' but this time, I choose CJC1r10, CJC1r12, CJC1r14, CJC1r16, CJC1r18, and CJC1r20. I clicked save and when I went back to options, this is what I see:

I did another test. I believe the really long list may be the result of old order migrations with VM Migrator from vm1.1.9 to VM3.0.12. The item CJC1r is an old sku I am not using on the new site but it is still on some of the older orders. If I choose CJC1r10 from the list and save, that is the only one that shows up. If I go back and add CJC1r, I get the terribly long list again. I even set the date range this time to only filter on orders since the new site went up. Not sure how to go about fixing this. I still have old orders from the old site that need to be filled and exported in this sheet.