I would like to know the answer too. Anyone here know what is the right answer to your question. I'll do some research in Google and get back to you if I find an useful answer. You should email the people at Verisign as they probably could help you..
I figured it would be best to just try to find the info with an SQL query. So far we are able to fetch the info with 2 queries. I am still trying to figure out how to join them. Seems when we use the SQL JOIN function it plays hell on the database..
To those who are interested here are the 2 queries..
Select orders.orders_id, orders.date_purchased, orders.billing_name, orders.delivery_state, orders.payment_method, orders_products.products_name, orders_products.products_quantity, orders_total.value.
From orders, orders_total, orders_products.
Where orders.orders_id = orders_total.orders_id AND orders_products.orders_id = orders.orders_id.
AND orders.orders_id >=('17734') AND orders_total.value >=(.01).
Order By orders.orders_id;.
If you are attempting to use this code to lets say find 2008 sales, you may want to change the "AND" line to this.
AND orders.date_purchased LIKE ('2008%') AND orders_total.value >=(.01).
The second query gets our order status id, which tells us if the item was refunded, canceled, denied, or delivered. The SQL database stores these as numbers. So you'll have to check your database to see what number is assigned to each status. Examples are 1=Processing, 2=PayPal Direct Payment, 3=Delivered, 4=Shipped... ect... In our database we are only worried about 5 statuses, Denied, Refunded, Canceled, Delivered, Shipped.
Here is the code:.
Select orders_id, orders_status_id.
Where orders_id >=('17334') AND orders_status_history.orders_status_id IN ('3','10001','100002','100004','100007').
Order By orders_id;.
Now if I can just figure out how to get the 2 queries as one. I could important the data to excel and find sales totals pretty easily...
I haven't tried this but it might help a little:.
Select o.orders_id, o.date_purchased, o.billing_name, o.delivery_state, o.payment_method, op.products_name, op.products_quantity, ot.value, osh.orders_status_id From.
Orders o left join.
Orders_total ot on o.orders_id = ot.orders_id left join.
Orders_products op on op.orders_id = o.orders_id,.
Where o.orders_id >= 17734 AND ot.value >= 0 and osh.orders_status_id IN ('3','10001','100002','100004','100007') Order By o.orders_id;.
Have you tried any of the report contributions..
There is also an open source finance package call osfinance...
Thanks for the input Jack. I had tried using the Join and Left Join functions previously and the database didn't like it. I ran this query and got a error #28 from the storage engine. I'm going through the code and see if I can add that to what I'm already working with to see if I can get a solution...
Thanks Geoffry for the post. I tried to find this osfinance to see what it does. I found this website: www.osfinance.org/ It does not have any package information nor a download link. Do you have a link that explains more about the package, and where to download it?..
Got that wrong.
Some of the report contributions are very usefull, but are not a full blown accounting package..
Excellent. I am going to have a look at this one..
Thank you both...
I'm looking for some type of ad on that will allow us to run a report so we can see what was sold and to where (in my case I need to know if the customer was in Ohio for taxes)..
I have tried QBI, paid good money for the software and install and then the guy who wrote it just won't help make it actually work. All kinds of errors and very slow responses, eventually leading to him not even returning emails..
I tried downloading the SQL files and playing with them to get some idea but this is very painstaking and really not doing what I need done..
Lastly I tried the Product Purchased in the contribution section. The install seemed to work fine, but when you go to product purchased in the admin panel it is unable to fetch any information. I'm not sure if that is because the install was wrong, it is outdated or what. Even if it were to work right it doesn't look like the info is going to be enough anyway, as it just shows the monthly sales totals, but doesn't give us the important information like if an order was refunded, or if it was sold to an Ohio Resident..
I know there are thousands of you out there who run oscommerce, who probably had to come up with some way to find your yearly profit or losses for tax purposes. So what are you using? How well does it work? Do you have any suggestions that may fit my needs?.
What I need sounds so simple to me. A simple ad on that will query the sales, fetch the Item, cost, and customer location for a given date range. Then let me know if the status of the order is delivered, refunded, or cancelled. But sounding simple and being simple don't seem to be the same thing..
Just checking in..
No suggestions yet, but if somebody knows of something I am still watching for help..