Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Products Purchased Report with Date Range


yakseller

Recommended Posts

I tried it out, and saw a couple of issues:

 

1) Just cosmetic - The model constant TABLE_HEADING_MODEL is not found. It is defined in the includes/languages files, but not on the admin side. So it would just need to be added to the appropriate include file on the admin side.

 

2) I did a date search, and it says "viewing 1-20 of 164 products", but there are only 4 products displayed. That is because there are only 4 active products, so you're apparently not including inactive products in your query. But i don't even HAVE 164 products in my catalog. I only have about 10. Maybe that includes deleted ones? But I doubt I had that many even when it was installed for the first time with the defaults...

 

HTH

Link to comment
Share on other sites

  • 3 weeks later...
I tried it out, and saw a couple of issues:

 

1) Just cosmetic - The model constant TABLE_HEADING_MODEL is not found.  It is defined in the includes/languages files, but not on the admin side. So it would just need to be added to the appropriate include file on the admin side.

 

2) I did a date search, and it says "viewing 1-20 of 164 products", but there are only 4 products displayed.  That is because there are only 4 active products, so you're apparently not including inactive products in your query.  But i don't even HAVE 164 products in my catalog. I only have about 10.  Maybe that includes deleted ones?  But I doubt I had that many even when it was installed for the first time with the defaults...

 

HTH

 

 

To fix the table heading add the following line to this file

 

admin/includes/languages/english/stats_products_purchased.php

 

after this line

define('TABLE_HEADING_NUMBER', 'No.');

 

Add this line

define('TABLE_HEADING_MODEL', 'Model');

 

worked for me!

 

As for the search issue I did not have this problem. I am going to check it against my inventory to be sure but it looks right on!

 

Many thanks to whomever made this!

Link to comment
Share on other sites

I updated this contrib to fix some problems.

A couple of suggestions.

 

First, I edited my file to automatically display the first day of the current month as the start date so that month-to-date is the default report you see when you click on the link in the Reports box (changed "d" to "01").

 

Second, I eliminated the links to the products because I was getting an error message (or two) when I clicked on them and didn't have the expertise to debug it. If anyone else is having the same issue, maybe someone can take a look at the code to see if there is a fix.

 

Third, it would be nice if there were alternating shaded lines so it is easier to follow a line over to the number purchased.

 

Fourth, it would be nice if there was a calendar dropdown in the start/end date boxes like the ones in the Sales Statistics contribution.

 

Hopefully someone in the group (more talented than I) can code these things. This contribution is a very useful improvement over the stock file, and hopefully someone can make it even better.

 

Dave

Edited by baddog
Link to comment
Share on other sites

A couple of suggestions.

 

First, I edited my file to automatically display the first day of the current month as the start date so that month-to-date is the default report you see when you click on the link in the Reports box (changed "d" to "01").

 

Second, I eliminated the links to the products because I was getting an error message (or two) when I clicked on them and didn't have the expertise to debug it.  If anyone else is having the same issue, maybe someone can take a look at the code to see if there is a fix.

 

Third, it would be nice if there were alternating shaded lines so it is easier to follow a line over to the number purchased.

 

Fourth, it would be nice if there was a calendar dropdown in the start/end date boxes like the ones in the Sales Statistics contribution.

 

Hopefully someone in the group (more talented than I) can code these things.  This contribution is a very useful improvement over the stock file, and hopefully someone can make it even better.

 

Dave

 

First suggestion: Seems like you figured this one out:

 

Change:

$start_date = date('Y-m-d');

 

To:

$start_date = date('Y-m-01');

 

 

Second:

I removed the product_id from the SQL statement when I made the last batch of changes, and forgot that I was linking to the products. To fix the linking error, change the SQL query to:

 

$products_query_raw = "select op.products_id, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . " 23:59:59' AND o.orders_id = op.orders_id GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";

 

Third Suggestion (Alternated Shaded Lines):

 

Find:

if(strlen($rows) < 2) {

$rows = '0' . $rows;

}

?>

 

Add Directly After:

<tr bgcolor="<?php echo ((++$cnt)%2==0) ? '#e0e0e0' : '#ffffff' ?>">

 

I do not really see the need for the calander pull down, but I may work on it if I have some time. I am uploading the updated file to the contrib page.

 

http://www.oscommerce.com/community/contributions,2568

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 months later...

Great mod!

 

I have 2 questiosn.

 

1 : Would it be possible to let this contribution only show the products which status is (for instance) payed instead of purchased (because some customers order, but never pay, which makes a purchased list alone a bit unreal since it doesn't actually display the products which have really been payed for)

 

2: would it be possible to redesign it so, that you can display the products that HAVEN't been sold (which would give me an insight which ones that are so i can delete them OR make some more promotion for those)

 

 

Thanks

Link to comment
Share on other sites

  • 2 months later...
How do I add product images to this contrib?

 

// Fredrik

 

You would have to link the query to the products table. Currently, the products table is not part of the query. Would have to check if the product sold still exists in the products table, and then pull the picture info. This could be a problem if you have changed a product into a new product_id, and reused a product_id.

Link to comment
Share on other sites

  • 2 weeks later...

I had the same issue.

 

I used the May 6th, 2005 contribution (in place of the May 17th) and the May 6th worked with no issues!

 

I tired the new one 17 May 2005 - stat products purchased 3 and all it generated was a blank page?? any idas?

 

maddie

Link to comment
Share on other sites

  • 2 months later...

For my installation I had the following issues.

 

1. I had to change the sql query to use final_price rather than products_price to allow for orders that have been modified for discounting.

 

2. I had to add a 0 manufacturers_id to the manufacturers table so that the sql query would pick up products that had no manufacturer.

 

3. In order to match the actual sales for the period, total order discounts also have to be figured in on a separate line. This is an issue for those who us the Gift Voucher contribution. It entails summing the order_totals "other" class for the period.

Link to comment
Share on other sites

  • 4 months later...
  • 2 months later...

Is it possible to get an option to output a csv file? I know you can cut and paste the data and pop it into Excel using Paste Special.

PS using the latest version seems to work fine

Edited by blueSatsuma
Link to comment
Share on other sites

  • 11 months later...

Whenever I try to filter it, it gives me an error.

 

It doesn't seem to matter wether it's sorted by Manufacturer,Status, or date. I keep getting the same error. I'm not one for PHP, so I don't know what to do to fix it. I have attached a screenshot to help though.

 

errorbo8.jpg

 

EDIT: Fixed picture

Edited by SMFGGUY

What happens when a crazy man goes insane?

Link to comment
Share on other sites

  • 1 month later...
Whenever I try to filter it, it gives me an error.

 

It doesn't seem to matter wether it's sorted by Manufacturer,Status, or date. I keep getting the same error. I'm not one for PHP, so I don't know what to do to fix it. I have attached a screenshot to help though.

 

errorbo8.jpg

 

EDIT: Fixed picture

 

 

I got the same thing - I went back to the previous version and it worked without trouble.

 

It doesn't have the Manufacturer filter, unfortunately, but I have that from another report anyway.

 

Simon

Link to comment
Share on other sites

  • 5 months later...

I just deleted that part of the query and the error went away: Edit from line 158:

 

<?php
 if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
 if ($gross == 'on') {
    //$products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_other LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";
    $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";
  } else {
    //$products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_other LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";
    $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";

  }
} else {
if ($gross == 'on') {
    //$products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_other LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";
    $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" .  $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";

  } else {
    //$products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_other LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";
    $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";

  }
}

 

Also to get the left colum back add this:

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->

You can kill the King, but you can't kill the King Riddim...

Link to comment
Share on other sites

  • 2 weeks later...
  • 1 month later...

I just installed this and made the changes just as you specified and I love the report! The only thing I'd change is to make it so you can select the specific date ranges instead of just having the "month" option. Overall, VERY HAPPY!

 

Thanks and Great Work!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...