Jump to content
jello1

Order Tracker New Contrib....

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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://forums.oscommerce.com/index.php?showtopic=58506 for the official thread.

</summary>

 

-jared

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Guest

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Guest

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

great job.....just great


<span style='font-family:Courier'>If you can't fix it Perl it!!!...</span>

******************************

Share this post


Link to post
Share on other sites

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');

?>

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×