NodsDorf Posted January 7, 2009 Posted January 7, 2009 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. Thank you
NodsDorf Posted January 10, 2009 Author Posted January 10, 2009 Just checking in. No suggestions yet, but if somebody knows of something I am still watching for help. Thanks gang
NodsDorf Posted January 18, 2009 Author Posted January 18, 2009 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. Query #1 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. If it does not have one of those status it would mean that the order is still open and not yet figured into sales. Here is the code: Select orders_id, orders_status_id From orders_status_history 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.
Jack_mcs Posted January 18, 2009 Posted January 18, 2009 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, orders_status_history osh 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; Jack Support Links: For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc. All of My Addons Get the latest versions of my addons Recommended SEO Addons
♥geoffreywalton Posted January 18, 2009 Posted January 18, 2009 Have you tried any of the report contributions. There is also an open source finance package call osfinance. Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>.
NodsDorf Posted January 18, 2009 Author Posted January 18, 2009 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, orders_status_history osh 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; Jack 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.
NodsDorf Posted January 18, 2009 Author Posted January 18, 2009 Have you tried any of the report contributions. There is also an open source finance package call osfinance. 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?
♥geoffreywalton Posted January 18, 2009 Posted January 18, 2009 Got that wrong osfinancials http://sourceforge.net/projects/osfinancials/ Some of the report contributions are very usefull, but are not a full blown accounting package. G Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile Virus Threat Scanner My Contributions Basic install answers. Click here for Contributions / Add Ons. UK your site. Site Move. Basic design info. For links mentioned in old answers that are no longer here follow this link Useful Threads. If this post was useful, click the Like This button over there ======>>>>>.
NodsDorf Posted January 18, 2009 Author Posted January 18, 2009 Got that wrong osfinancials http://sourceforge.net/projects/osfinancials/ Some of the report contributions are very usefull, but are not a full blown accounting package. G Excellent. I am going to have a look at this one. Thank you both.
Guest Posted January 19, 2009 Posted January 19, 2009 Got that wrong osfinancials http://sourceforge.net/projects/osfinancials/ Some of the report contributions are very usefull, but are not a full blown accounting package. G Going to check it out now, thanks.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.