Order Tracker New Contrib....

[cgchris99]

Yes, I have real data and the totals do not match for the Sales/Tax Summary for November. Every other month is perfect.

[Guest]

have you tried to figure it out? I am not sure that I'm reproducing the same problem by creating bogus orders in a test database and then changing the order dates.

 

I'd really like to get this released, and IMO this is the only thing holding it up.

 

-jared

[cgchris99]

No, I haven't spent any real time on it. I thought by loading in bogus sales, it would have been easier to figure out than working with a whole months worth.

 

Chris

[Guest]

I understand. I thought the same thing (especially when I started testing with $100 gift vouchers - - it was easy to see if the total were right or not).

 

I also thought I'd duped it pretty quickly, but what I actually did was modify the dates on the *latest* orders to be from last November, rather than the *earliest* orders. That messed up some of the database chronological dependencies, I think, because it messed up the totals for not just November, but other months as well.

 

So, here is what I did:

I placed 4 orders, for $400, $300, $200, and $100, in that order. I then changed the dates in phpMyAdmin for the first 2 orders, so that the dates on those orders are now 2003-11-12 (I left the times alone).

 

My order totals in orders_tracking.php and stats_monthly_sales.php are the same. No differences, no errors.

 

Have you tried backing up your database and then doing a db repair? It just doesn't make sense to me how the same code would work for all other months. I could see it if it were a boundary month, like Jan or Dec, but it's not at either end.

 

Is *anyone* else having this November problem??

 

I'm tempted to release this as a contrib update, even though it may still have this problem. Any detractors?

 

-jared

[koraldon]

I use it and it is really nice,

I think it will make a very good contrib as is.

[Guest]

v2 has now been released at http://www.oscommerce.com/community/contributions,1522 .

 

I know that there is more that can be done for it (I never did write up what exactly Close Ratio is. Maybe that's an "exercise for the reader." <grin>

 

I'm not sure if anyone else is going to run into the November thing. If they do, we'll have to figure out what the deal is.

 

It was time to release it, methinks. Here is the release summary:

 

<summary>

- cleaned up formatting

- added ability to select year

- now fits in osC admin pages

- fixed divide by zero errors

- improved date calculations

- improved daily average calculations

- improved per-month calculcations

- made most of the reported stats links to corresponding areas in osC admin

 

Thanks to Jello, Chris, and everyone else on the forums for helping make this happen!

 

See http://www.oscommerce.com/forums/index.php?showtopic=58506 for the official thread.

</summary>

 

-jared

[spib]

Great Contribution!

 

Only problem is everything is in $. What would it take to read the correct currency out of the database for those of us who don't sell in dollars?

[spib]

Figured it out.

 

Add this to the top of the orders_tracking.php file

require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();

 

And then replace, for example,

<td class="dataTableContent" align=right>$<?php echo $orders_today ?></TD>

 

With

 

<td class="dataTableContent" align=right><?php echo $currencies->format($orders_today) ?></TD>

[Guest]

Thanks for the tip! Perhaps we should re-release with these changes within a few weeks, once we have opportunity to road test it a bit more.

 

-jared

[Guest]

will this work on ms1 if I edit application_top.php instead of filename.php? or is the db structure wrong?

 

Also If my prices change alot will this give amounts based on current price or based on the price at the time of a sale?

 

I am currently running sales report2 and would like something with a little more meat.

 

Thanks

Jeff

[cgchris99]

I am running it on ms1 and it only took a few minor changes

[Guest]

do you have the changes off hand or were they that minor?

[Guest]

Jeff - - the way that the stats work is based on the sale numbers in the database, not on what the current price is * how many units they ordered a month ago.

 

In other words, if you take 50% off everything, it won't change the reported values of the sales you've already made.

 

Does that help?

 

-jared

[Guest]

yes it does. thank you very much and it works like a charm. very nice report again thank you. :D

[airolg]

This is the best report!!! This should definitely be included as standard, for sure.

 

I can't wait to watch my stats.

 

Now, how could I add a line that pulls in the current month's new customers? I'd love to see how many new customers have signed up so far.

 

How much work would that be to do that?

 

Thanks for the report, it's a great one!

[cgchris99]

Not only new customers for this month, how about new customers for every month

[airolg]

I didn't want to ask for too much! :D

 

Wouldn't that be great!

[Guest]

probably wouldn't be too bad - - something like query the customers_info table for a customers_info_date_account_created value in the current month and current year.

 

It would then probably be trivial to add all of those together into a "new customers this year" stat for the bottom of the report

 

I'll look into it.

 

-jared

[Guest]

here is something quick:

 

Change this:

 

# Get total number new customers

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

$query = "SELECT customers_info_date_account_created FROM customers_info WHERE customers_info_date_account_created like \"$like%\"";

$result = mysql_query($query) or die("Query failed : " . mysql_error());

$newcust=0;

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

$newcust++;

}

mysql_free_result($result);

 

To this:

 

# Get total number new customers

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

$this_month = date('Y-m');

$this_year = date ('Y');

$query = "SELECT customers_info_date_account_created FROM customers_info WHERE customers_info_date_account_created like \"$like%\"";

$result = mysql_query($query) or die("Query failed : " . mysql_error());

$newcust=0;

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

$newcust++;

}

$query = "SELECT customers_info_date_account_created FROM customers_info WHERE customers_info_date_account_created like \"$this_month%\"";

$result = mysql_query($query) or die("Query failed : " . mysql_error());

$newcust_this_month=0;

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

$newcust_this_month++;

}

$query = "SELECT customers_info_date_account_created FROM customers_info WHERE customers_info_date_account_created like \"$this_year%\"";

$result = mysql_query($query) or die("Query failed : " . mysql_error());

$newcust_this_year=0;

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

$newcust_this_year++;

}

mysql_free_result($result);

 

 

Near the end of the file, find this:

 

<TR class="dataTableRow" ><td class="dataTableContent">New Customers Today</TD><td class="dataTableContent"><?php echo $newcust ?></TD><td class="dataTableContent">Close ratio</TD><td class="dataTableContent"><?php echo $close_ratio ?>%</TR>

 

and add this below it:

 

<TR class="dataTableRow" ><td class="dataTableContent">New Customers This Month</TD><td class="dataTableContent"><?php echo $newcust_this_month ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">New Customers This Year</TD><td class="dataTableContent"><?php echo $newcust_this_year ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

 

Whaddya think?

 

We could change the HTML table structure a bit, and put these figures up for each month, but this was faster, at least for today.

 

-jared

[Guest]

10 points to the anyone who modifies the code to put monthly values by each month, and does it without making the HTML table look majorly messy.

 

hint: the year variable that gets set if you "recalculate" for a different year is about 1/2 of the way down the file.

[talon177]

Great mod, just got this installed and i'm loving it! Good work guys!

[jello1]

wow... i had no idea that my little contrib would be taken so far.....

great job.....just great

[airolg]

Thanks, Jared!

 

This is so cool!!! Now, what else can I ask for? ;)

[AceDog]

How do you install it. can not work out the instructions

i.e

---------------------------------------------

Installation instructions

 

 

1) Copy /admin/orders_tracking.php (this file)

 

2) Edit /admin/fileames.php. Add the following to the end of the file, just prior to the ?> line:

define('FILENAME_STATS_ORDERS_TRACKING', 'orders_tracking.php'); to /admin/filenames.php

 

3) Save the followig as /admin/includes/language/english/orders_tracking.php:

<?php

define('HEADING_TITLE', 'Daily Sales Summary');

define('HEADING_SELECT_YEAR', 'Enter Year');

?>

 

4) Edit /admin/includes/boxes/reports.php. It's a small file. Find the section with lines that look like the following one, and add the following line (this reads easier with word wrap off):

'<a href="' . tep_href_link(FILENAME_STATS_ORDERS_TRACKING, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TRACKING . '</a><br>'

You can add the line in any order you want, but make sure to follow the correct syntax. The finished section could look like this:

if ($selected_box == 'reports') {

$contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_STATS_ORDERS_TRACKING, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TRACKING . '</a>');

}

 

5) Edit /admin/includes/languages/english.php and add the following just before the closing ?>

// Orders_Tracking Contrib

define('BOX_REPORTS_ORDERS_TRACKING', 'Orders_Tracking');

 

-----------------------------------

 

I Think the instructions need to be simpler. not sure what files to edit

ie

what is this?

 

3) Save the followig as /admin/includes/language/english/orders_tracking.php:

<?php

define('HEADING_TITLE', 'Daily Sales Summary');

define('HEADING_SELECT_YEAR', 'Enter Year');

?>

[♥radders]

Has anyone got a version with the currency problem sorted? I made about 50 changes before I gave up after making a complete hash of the file :huh: