Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What are you using to view sales and calculate profit / tax?


NodsDorf

Recommended Posts

Posted

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

Posted

Just checking in.

 

No suggestions yet, but if somebody knows of something I am still watching for help.

 

Thanks gang

  • 2 weeks later...
Posted

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.

Posted

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

Posted

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

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

Posted
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?

Posted

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...