Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

jello1

Order Tracker New Contrib....

Recommended Posts

This package is a stand alone app to report on alot of stuff that osc does not repot. some features include:

Get total dollars in orders, Get total shipping charges, Get total number of customers, Get total number new customers today, Customers online, How many orders today total,

How many orders yesterday, How many repeat customers today total, Daily Averages, Close rate (how many customers sign up and then buy), what your total profit is, ..

plus alot more stuff... this is my first contrip so be easy on me.

 

if you have any question please ask them here. and i'll see what i can do.

you can get the contrib at

 

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

enjoy!


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

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

Share this post


Link to post
Share on other sites
Guest

Seems to work... didn't check the math but looks pretty accurate for my store.

 

I am getting two warnings though "division by zero" on line 182 and 196

 

B)

Share this post


Link to post
Share on other sites

It's the month... it's set up for aug and sept at the moment... if you look at the line 182 it's the year. 182 line 185 is the months. just add the month you want to track there.

I don't have any errors on my site. but I've only been up since aug. this is why you see aug and sept only. let me know if you find the fix. but i'll do some work arounds and see if i can get the same error as you.

thanks


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

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

Share this post


Link to post
Share on other sites

196 is whitespace


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

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

Share this post


Link to post
Share on other sites
Guest
It's the month... it's set up for aug and sept at the moment... if you look at the line 182 it's the year. 182 line 185 is the months. just add the month you want to track there.

I don't have any errors on my site. but I've only been up since aug. this is why you see aug and sept only. let me know if you find the fix. but i'll do some work arounds and see if i can get the same error as you.

thanks

let me play around.... my store has been online since April....

Share this post


Link to post
Share on other sites
Guest

Looked at your code. Few comments:

 

-maybe you should consider adding dropdown for the months

 

-also noticed that you have the order status hardcoded instead of using whats in the database. I for instance have customer settings for the order status with different id #'s. also the paypal IPN contrib adds an order status.

 

I will continue playing with this contrib

Share this post


Link to post
Share on other sites

Cool... good idea. I'll look around today at the code.

thanks for the input


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

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

Share this post


Link to post
Share on other sites
Guest

I've fixed this up a bit for my site, to add the other months and such, but it's not really fixed well yet IMHO.

 

In my case, I hardcoded the years (so that I can get Dec from 2003, my first osC month) in the totals, but I have to add some logic to get around the "divide by zero" errors for the months that don't have data yet (like August and September, for example <grin> ). So far I just fudged it to say that if a $month == 0, then that $month = 1.

 

I'm interested in working on it some more, but since it's been a few months, I'm just curious if you have some later code before I spend much more time on the code that I have.

 

Thanks!

 

-jared

Share this post


Link to post
Share on other sites
Guest

Ok, so my mods to the code are still not the cleanest (hardcoded months, years), but the fudge factor has gone away. I now check to see if the month sales count is zero, and if so, don't do any of the divide operations.

 

I changed a couple of the functions to accept the year as a variable, not just the month, to make the hardcoding a bit easier.

 

Things to do:

- post my modified code here

- figure out how to change the code to use an array of months instead of a buncha hardcoded months (cleaner code - lower priority, since I believe in functionality first)

- figure out how to allow the user to input the year, rather than hardcode the year values

Share this post


Link to post
Share on other sites
Guest

here's my modified get_db_info.php:

 

<?php

 

function get_month($mo, $yr) {

$link = mysql_connect("localhost", "YOUR_DB_USER", "YOUR_DB_USER_PASSWORD")

or die("Could not connect : " . mysql_error());

mysql_select_db("YOUR_DB_NAME) or die("Could not select get_info database");

 

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$yr-$mo%\"";

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

$k=0;

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

$k++;

}

mysql_free_result($result);

$month=$k;

return $month;

}

 

function get_order_total($mo, $yr) {

$link = mysql_connect("localhost", "YOUR_DB_USER", "YOUR_DB_USER_PASSWORD")

or die("Could not connect : " . mysql_error());

mysql_select_db("YOUR_DB_NAME) or die("Could not select get_info2 database");

 

$query = "SELECT orders_id FROM orders WHERE date_purchased LIKE \"$yr-$mo%\" ORDER by orders_id";

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

$k=0;

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

foreach ($line as $col_value) {

if ( $k == 0 ) {

$first=$col_value;

$k++;

} else {

$last=$col_value;

}

}

}

mysql_free_result($result);

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$first\" and orders_id < \"$last\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$total=$col_value;

}

}

mysql_free_result($result);

return $total;

}

 

function get_status($type) {

$link = mysql_connect("localhost", "YOUR_DB_USER", "YOUR_DB_USER_PASSWORD")

or die("Could not connect : " . mysql_error());

mysql_select_db("YOUR_DB_NAME) or die("Could not select database");

 

$query = "SELECT orders_status FROM orders WHERE orders_status = \"$type\"";

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

$k=0;

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

foreach ($line as $col_value) {

$k++;

}

}

mysql_free_result($result);

return $k;

}

 

?>

Share this post


Link to post
Share on other sites
Guest

and here's my modified order_tracking.php:

 

<HTML>

<HEAD>

<TITLE>Site Overview</TITLE>

</HEAD>

<?php

require_once('get_db_info.php');

setlocale(LC_MONETARY, 'en_US');

 

# Get DB Connection and login

 

$link = mysql_connect("localhost", "YOUR_DB_USER", "YOUR_DB_USER_PASSWORD")

or die("Could not connect : " . mysql_error());

 

mysql_select_db("YOUR_DB_NAME) or die("Could not select database");

 

# Get total dollars in orders

 

$query = "SELECT sum(value) FROM orders_total WHERE class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$grand_total=$col_value;

}

}

mysql_free_result($result);

 

# Get total shipping charges

 

$query = "SELECT sum(value) FROM orders_total WHERE class like \"ot_shipping\"";

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

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

foreach ($line as $col_value) {

$shipping=$col_value;

}

}

mysql_free_result($result);

 

# Get total number of customers

 

$query = "SELECT * FROM customers";

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

$i=0;

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

$i++;

}

mysql_free_result($result);

 

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

 

# Whos online

$query = "SELECT * FROM whos_online";

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

$w=0;

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

$w++;

}

mysql_free_result($result);

$who = $w;

 

# Whos online again

$query = "SELECT * FROM whos_online WHERE customer_id != \"0\"";

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

$who_again=0;

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

$who_again++;

}

mysql_free_result($result);

 

# How many orders today total

 

$date = date('Y-m-d'); #2003-09-07%

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date%\"";

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

$j=0;

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

$j++;

}

mysql_free_result($result);

 

# How many orders yesterday

 

$mo = date('m');

$today = date('d');

$start = $today + 8;

$day = date('d');

if ( $day <= 10 ) {

$day = $day - 1;

$day = "0$day";

} elseif ( $day == "01" ) {

$day = "31";

} else {

$day = $day - 1;

}

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

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date-$day%\"";

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

$l=0;

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

$l++;

}

mysql_free_result($result);

$yesterday=$l;

 

# Get the total number of order_id's

 

$query = "SELECT orders_id FROM orders_total WHERE class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$k=$col_value;

}

}

mysql_free_result($result);

 

# Calculate the order_id number less the number of orders today

$k = $k - $j;

$t = $k - $l;

 

# Grab the sum of all orders greater than k

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$k\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$orders_today=$col_value;

}

}

mysql_free_result($result);

 

# Grab the sum of all orders greater than t and less than k

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$t\" and orders_id < \"$k\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$orders_yesterday=$col_value;

}

}

mysql_free_result($result);

 

# How many repeat orders today total

 

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

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date%\" AND customers_id < \"$k\"";

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

$repeat_orders=0;

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

$repeat_orders++;

}

mysql_free_result($result);

 

# Find all repeat orders

 

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

$query = "SELECT customers_id FROM orders ORDER by customers_id";

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

$repeats=0;

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

foreach ($line as $col_value) {

$cust_id=$col_value;

if ( $cust_id == $cust_id_last ) {

$repeats++;

}

$cust_id_last=$cust_id;

}

}

mysql_free_result($result);

 

mysql_close($link);

 

 

# How many per month

 

$year = date('Y'); #current year

$month = date('M'); #current month

 

$dec = get_month("12", "2003");

$nov = get_month("11", "2004");

$oct = get_month("10", "2004");

$sep = get_month("09", "2004");

$aug = get_month("08", "2004");

$jul = get_month("07", "2004");

$jun = get_month("06", "2004");

$may = get_month("05", "2004");

$apr = get_month("04", "2004");

$mar = get_month("03", "2004");

$feb = get_month("02", "2004");

$jan = get_month("01", "2004");

 

 

# Only Process Month Info if Month has info to process

# Always tally totals, even if zero

 

if ($jan != 0) $jan_avg = $jan_total / $jan;

$jan_total = get_order_total("01", "2004");

$order = $order + $jan_total;

 

if ($feb != 0) $feb_avg = $feb_total / $feb;

$feb_total = get_order_total("02", "2004");

$order = $order + $feb_total;

 

if ($mar != 0) $mar_avg = $mar_total / $mar;

$mar_total = get_order_total("03", "2004");

$order = $order + $mar_total;

 

if ($apr != 0) $apr_avg = $apr_total / $apr;

$apr_total = get_order_total("04", "2004");

$order = $order + $apr_total;

 

if ($may != 0) $may_avg = $may_total / $may;

$may_total = get_order_total("05", "2004");

$order = $order + $may_total;

 

if ($jun != 0) $jun_avg = $jun_total / $jun;

$jun_total = get_order_total("06", "2004");

$order = $order + $jun_total;

 

if ($jul != 0) $jul_avg = $jul_total / $jul;

$jul_total = get_order_total("07", "2004");

$order = $order + $jul_total;

 

if ($aug != 0) $aug_avg = $aug_total / $aug;

$aug_total = get_order_total("08", "2004");

$order = $order + $aug_total;

 

if ($sep != 0) $sep_avg = $sep_total / $sep;

$sep_total = get_order_total("09", "2004");

$order = $order + $sep_total;

 

if ($oct != 0) $oct_avg = $oct_total / $oct;

$oct_total = get_order_total("10", "2004");

$order = $order + $oct_total;

 

if ($nov != 0) $nov_avg = $nov_total / $nov;

$nov_total = get_order_total("11", "2004");

$order = $order + $nov_total;

 

if ($dec != 0) $dec_avg = $dec_total / $dec;

$dec_total = get_order_total("12", "2004");

$order = $order + $dec_total;

 

 

# Order Status

$pending = get_status("1");

$processing = get_status("2");

$delivered = get_status("3");

 

# Daily Averages

$today_avg = $orders_today / $j;

$yesterday_avg = $orders_yesterday / $yesterday;

 

 

$daily = $jan / $today;

$daily_total = $jan_total / $today;

$daily_avg = $daily_total / $daily;

 

$projected = $daily * 31;

$projected_total = $daily_total * 31;

 

$gross_profit = $grand_total * .3;

 

$close_ratio = $j / $newcust;

 

# format test into current

$total_orders = $jan + $feb + $mar + $apr + $may + $jun + $jul + $aug + $sep + $oct + $nov + $dec;

$total = $order / $total_orders;

$total = number_format($total,2,'.',',');

 

$order = number_format($order,2,'.',',');

$grand_total = number_format($grand_total,2,'.',',');

 

$gross_profit = number_format($gross_profit,2,'.',',');

 

$projected = number_format($projected,0,'.',',');

$projected_total = number_format($projected_total,2,'.',',');

 

$close_ratio = number_format($close_ratio,2,'.',',');

 

$yesterday_avg = number_format($yesterday_avg,2,'.',',');

 

$dec_total = number_format($dec_total,2,'.',',');

$nov_total = number_format($nov_total,2,'.',',');

$oct_total = number_format($oct_total,2,'.',',');

$sep_total = number_format($sep_total,2,'.',',');

$aug_total = number_format($aug_total,2,'.',',');

$jul_total = number_format($jul_total,2,'.',',');

$jun_total = number_format($jun_total,2,'.',',');

$may_total = number_format($may_total,2,'.',',');

$apr_total = number_format($apr_total,2,'.',',');

$mar_total = number_format($mar_total,2,'.',',');

$feb_total = number_format($feb_total,2,'.',',');

$jan_total = number_format($jan_total,2,'.',',');

 

 

$orders_today = number_format($orders_today,2,'.',',');

$orders_yesterday = number_format($orders_yesterday,2,'.',',');

 

$dec_avg = number_format($dec_avg,2,'.',',');

$nov_avg = number_format($nov_avg,2,'.',',');

$oct_avg = number_format($oct_avg,2,'.',',');

$sep_avg = number_format($sep_avg,2,'.',',');

$aug_avg = number_format($aug_avg,2,'.',',');

$jul_avg = number_format($jul_avg,2,'.',',');

$jun_avg = number_format($jun_avg,2,'.',',');

$may_avg = number_format($may_avg,2,'.',',');

$apr_avg = number_format($apr_avg,2,'.',',');

$mar_avg = number_format($mar_avg,2,'.',',');

$feb_avg = number_format($feb_avg,2,'.',',');

$jan_avg = number_format($jan_avg,2,'.',',');

 

$today_avg = number_format($today_avg,2,'.',',');

 

$shipping_avg = $shipping / $total_orders;

$shipping_avg = number_format($shipping_avg,2,'.',',');

$shipping = number_format($shipping,2,'.',',');

 

$daily = number_format($daily,2,'.',',');

$daily_total = number_format($daily_total,2,'.',',');

$daily_avg = number_format($daily_avg,2,'.',',');

?>

 

<CENTER>

<b><font size=4>My Store's Sales Stats</font></b>

<br><br>

<TABLE BORDER=1 CELLPADDING=5 CELLSPACING=1>

<TR bgcolor=silver><TH>Description<TH>Order Count<TH>Dollar<TH>Average</TR>

 

<TR>

<TD>Today <?php echo "$mo-$today"; ?></TD><TD><?php echo "$j ($repeat_orders)"; ?> *</TD>

<TD align=right>$<?php echo $orders_today ?></TD>

<TD align=right>$<?php echo $today_avg ?></TD>

</TR>

<TR>

<TD>Yesterday <?php echo "$mo-$day"; ?></TD>

<TD><?php echo $yesterday ?></TD

><TD align=right>$<?php echo $orders_yesterday ?></TD>

<TD align=right>$<?php echo $yesterday_avg ?></TD>

</TR>

<TR>

<TD>Daily Average for <?php echo $month ?></TD>

<TD><?php echo $daily ?></TD><TD align=right>$<?php echo $daily_total ?></TD>

<TD align=right>$<?php echo $daily_avg ?></TD>

</TR>

<TR>

<TD>Projected #'s for <?php echo $month ?></TD>

<TD><?php echo $projected ?></TD>

<TD align=right>$<?php echo $projected_total ?></TD>

<TD align=right> </TD>

</TR>

 

<TR><TD bgcolor=silver colspan=4> </TD></TR>

 

<TR>

<TR><TD>Jan 2004 Orders</TD><TD> <?php echo $jan ?></TD>

<TD align=right>$<?php echo $jan_total ?></TD>

<TD align=right>$<?php echo $jan_avg ?></TD></TR>

 

<TR>

<TR><TD>Feb 2004 Orders</TD><TD> <?php echo $feb ?></TD>

<TD align=right>$<?php echo $feb_total ?></TD>

<TD align=right>$<?php echo $feb_avg ?></TD></TR>

 

<TR>

<TR><TD>Mar 2004 Orders</TD><TD> <?php echo $mar ?></TD>

<TD align=right>$<?php echo $mar_total ?></TD>

<TD align=right>$<?php echo $mar_avg ?></TD></TR>

 

<TR>

<TR><TD>Apr 2004 Orders</TD><TD> <?php echo $apr ?></TD>

<TD align=right>$<?php echo $apr_total ?></TD>

<TD align=right>$<?php echo $apr_avg ?></TD></TR>

 

<TR>

<TR><TD>May 2004 Orders</TD><TD> <?php echo $may ?></TD>

<TD align=right>$<?php echo $may_total ?></TD>

<TD align=right>$<?php echo $may_avg ?></TD></TR>

 

<TR>

<TR><TD>Jun 2004 Orders</TD><TD> <?php echo $jun ?></TD>

<TD align=right>$<?php echo $jun_total ?></TD>

<TD align=right>$<?php echo $jun_avg ?></TD></TR>

 

<TR>

<TR><TD>Jul 2004 Orders</TD><TD> <?php echo $jul ?></TD>

<TD align=right>$<?php echo $jul_total ?></TD>

<TD align=right>$<?php echo $jul_avg ?></TD></TR>

 

<TR>

<TR><TD>Aug 2004 Orders</TD><TD> <?php echo $aug ?></TD>

<TD align=right>$<?php echo $aug_total ?></TD>

<TD align=right>$<?php echo $aug_avg ?></TD></TR>

 

<TR>

<TR><TD>Sep 2004 Orders</TD><TD> <?php echo $sep ?></TD>

<TD align=right>$<?php echo $sep_total ?></TD>

<TD align=right>$<?php echo $sep_avg ?></TD></TR>

 

<TR>

<TR><TD>Oct 2004 Orders</TD><TD> <?php echo $oct ?></TD>

<TD align=right>$<?php echo $oct_total ?></TD>

<TD align=right>$<?php echo $oct_avg ?></TD></TR>

 

<TR>

<TR><TD>Nov 2004 Orders</TD><TD> <?php echo $nov ?></TD>

<TD align=right>$<?php echo $nov_total ?></TD>

<TD align=right>$<?php echo $nov_avg ?></TD></TR>

 

<TR>

<TR><TD>Dec 2003 Orders</TD><TD> <?php echo $dec ?></TD>

<TD align=right>$<?php echo $dec_total ?></TD>

<TD align=right>$<?php echo $dec_avg ?></TD></TR>

 

<TR>

<TD NOWRAP><B>Total Orders in 12 Months</TD><TD><B><?php echo "$total_orders / $repeats"; ?> *</TD><TD align=right><B>$<?php echo $order ?></TD><TD align=right><B>$<?php echo $total ?></TD></TR>

<TR><TD><B>Grand Total**</TD><TD colspan=2 align=right><B>$<?php echo $grand_total ?></TD><TD align=right> </TD></TR>

<TR><TD><B>Gross Net @ 30%</TD><TD colspan=2 align=right><B>$<?php echo $gross_profit ?></TD><TD align=right> </TD></TR>

 

<TR bgcolor=silver><TD COLSPAN=4> </TD></TR>

<TR><TD>Total Customers</TD><TD><?php echo $i ?></TD><TD>Online Now</TD><TD><?php echo "$who / $who_again"; ?> *</TD></TR>

<TR><TD>New Customers Today</TD><TD><?php echo $newcust ?></TD><TD>Close ratio</TD><TD><?php echo $close_ratio ?>%</TR>

<TR><TD>Orders Pending</TD><TD><?php echo $pending ?></TD><TD colspan=2 align=right> </TD></TR>

<TR><TD>Orders Processing</TD><TD><?php echo $processing ?></TD><TD colspan=2 align=right> </TD></TR>

<TR><TD>Orders Delivered</TD><TD><?php echo $delivered ?></TD><TD colspan=2 align=right> </TD></TR>

 

 

<TR><TD>Shipping Charges</TD><TD colspan=2 align=right>$<?php echo $shipping ?></TD><TD align=right>$<?php echo $shipping_avg ?></TD></TR>

 

</TABLE>

 

<br><?php echo $jan ?>

<br><?php echo $dec ?>

<FONT SIZE=-1>

* - total / repeat customer

<BR>

** - Raw number pulled from database on select * from orders_total</FONT>

</BODY>

</HTML>

Share this post


Link to post
Share on other sites
Guest

I hope my changes don't bug anyone - - if they do, then either don't use them, or make them the way you'd like. <grin>

 

I have some other ideas that I'd like to implement - - Jello, if that's ok with you, then after I clean them up and post them here, I (or you) can re-release this.

 

Current Ideas / Things to do:

- figure out how to change the code to use an array of months instead of a buncha hardcoded months (cleaner code - lower priority, since I believe in functionality first)

- figure out how to allow the user to input the year, rather than hardcode the year values

- Add "Orders Shipped" to the report

- Either Create Admin module for this or add into existing module (need to think this one through)

 

-jared

Share this post


Link to post
Share on other sites

Great work Jared!

I like it. nice and smooth. by all means please re-release v2 that would be great. i will bring it down and add a few tids and tads that i've been working on.

thanks!


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

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

Share this post


Link to post
Share on other sites
Guest

I fixed a couple of more potential divide by zero errors that happen only when you don't yet have orders for the current day.

 

I've also added it into the admin/reports section, but while installing another similar contrib, I found that the sales numbers returned by the two contribs are not the same. I need to find out how each one does the numbers, and try to figure out the difference before I'm comfortable with it.

 

Adding "Orders Shipped" shouldn't be a problem.

 

I'll try to figure out the dynamic year thing within the next few days, then post the new version!

 

-jared

Share this post


Link to post
Share on other sites
Guest

Next post will be what I have so far, including my worklist and installation instructions (no get_db_info.php required anymore!) for making this work as part of Admin.

 

Jello, will you please verify my installation instructions?

 

Items worth mentioning:

 

1) Stats are now for every month in current year. I haven't yet added ability to switch years (though it'd be easy for someone to hard-code if they really wanted to).

2) Projected Sales (and also how many sales there were Yesterday) are no longer always calculated for 31 day months, but rather for the number of days in each month. Leap years (like 2004) are also figured in.

3) Order of some stats has been changed a bit for clarity.

4) System stylesheet incorporated for consistency in look/feel

5) "Online Now" is now a link to the "Who's Online"

 

Enjoy! I'll keep working on it.

 

-jared

Share this post


Link to post
Share on other sites
Guest

<?php

require('includes/application_top.php');

 

// WORKLIST

// allow year selection for other than current year

// finish loop code for month data?

// orders shipped this month/year -- way to do this without adding date field to orders_total?

// orders shipped this year -- way to do this without adding date field to orders_total?

// look_feel should look like other admin modules

// check math for Today's average (and dollar amount) vs. Daily Average (and dollar amount)

// clean up variable names. $i==total_customers? $j==orders_today? $w==customers_online? $day=$yesterday?

// add calculation for gift vouchers?

 

// FIXED? TEST at beginning of month. yesterday should never be the 0th (zero-th) day

 

 

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

?>

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

}

*/

 

 

 

 

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<title><?php echo TITLE; ?></title>

<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">

<script language="javascript" src="includes/general.js"></script>

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- 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 //-->

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

 

<?php

setlocale(LC_MONETARY, 'en_US');

 

function get_month($mo, $yr) {

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$yr-$mo%\"";

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

$k=0;

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

$k++;

}

mysql_free_result($result);

$month=$k;

return $month;

}

 

function get_order_total($mo, $yr) {

$query = "SELECT orders_id FROM orders WHERE date_purchased LIKE \"$yr-$mo%\" ORDER by orders_id";

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

$k=0;

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

foreach ($line as $col_value) {

if ( $k == 0 ) {

$first=$col_value;

$k++;

} else {

$last=$col_value;

}

}

}

mysql_free_result($result);

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$first\" and orders_id < \"$last\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$total=$col_value;

}

}

mysql_free_result($result);

return $total;

}

 

function get_status($type) {

$query = "SELECT orders_status FROM orders WHERE orders_status = \"$type\"";

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

$k=0;

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

foreach ($line as $col_value) {

$k++;

}

}

mysql_free_result($result);

return $k;

}

 

# Get total dollars in orders

 

$query = "SELECT sum(value) FROM orders_total WHERE class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$grand_total=$col_value;

}

}

mysql_free_result($result);

 

# Get total shipping charges

 

$query = "SELECT sum(value) FROM orders_total WHERE class like \"ot_shipping\"";

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

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

foreach ($line as $col_value) {

$shipping=$col_value;

}

}

mysql_free_result($result);

 

 

# Get total number of customers

 

$query = "SELECT * FROM customers";

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

$i=0;

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

$i++;

}

mysql_free_result($result);

 

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

 

# Whos online

$query = "SELECT * FROM whos_online";

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

$w=0;

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

$w++;

}

mysql_free_result($result);

$who = $w;

 

# Whos online again

$query = "SELECT * FROM whos_online WHERE customer_id != \"0\"";

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

$who_again=0;

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

$who_again++;

}

mysql_free_result($result);

 

# How many orders today total

 

$date = date('Y-m-d'); #2003-09-07%

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date%\"";

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

$j=0;

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

$j++;

}

mysql_free_result($result);

 

# How many orders yesterday

 

$mo = date('m');

$today = date('d');

$year = date('Y');

 

/*

// what's this line for? $start = $today + 8;

$day = date('d');

if ( $day <= 10 ) {

$day = $day - 1;

// next line to normalize $day format to 2 digits

$day = "0$day";

} elseif ( $day == "01" ) {

$day = "31";

} else {

$day = $day - 1;

}

*/

 

$last_month = $mo-1;

$day = date('d') - 1;

if ($day == "0") //today is the first day of the month, now "Thirty days hath November . . ." for the prev month

{ if ( ($last_month == 2) OR ($last_month == 4) OR ($last_month == 6) OR ($last_month == 8) OR ($last_month == 9) OR ($last_month == 11) OR ($last_month == 1) )

$day = "31";

elseif ( ($last_month == 5) OR ($last_month == 7) OR ($last_month == 10) OR ($last_month == 12) )

$day = "30";

//calculate Feb end day, including leap year calculation from http://www.mitre.org/tech/cots/LEAPCALC.html

else {

if ( ($year % 4) != 0) $day = "28";

elseif ( ($year % 400) == 0) $day = "29";

elseif ( ($year % 100) == 0) $day = "28";

else $day = "29";

}

}

 

// next line to normalize $day format to 2 digits

$day = "0$day";

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

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date-$day%\"";

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

$l=0;

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

$l++;

}

mysql_free_result($result);

$yesterday=$l;

// $yesterday = $day;

 

# Get the total number of order_id's

 

$query = "SELECT orders_id FROM orders_total WHERE class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$k=$col_value;

}

}

mysql_free_result($result);

 

# Calculate the order_id number less the number of orders today

$k = $k - $j;

$t = $k - $l;

 

# Grab the sum of all orders greater than k

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$k\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$orders_today=$col_value;

}

}

mysql_free_result($result);

 

# Grab the sum of all orders greater than t and less than k

 

$query = "SELECT sum(value) FROM orders_total WHERE orders_id > \"$t\" and orders_id < \"$k\" and class = \"ot_total\"";

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

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

foreach ($line as $col_value) {

$orders_yesterday=$col_value;

}

}

mysql_free_result($result);

 

# How many repeat orders today total

 

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

$query = "SELECT * FROM orders WHERE date_purchased LIKE \"$date%\" AND customers_id < \"$k\"";

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

$repeat_orders=0;

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

$repeat_orders++;

}

mysql_free_result($result);

 

# Find all repeat orders

 

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

$query = "SELECT customers_id FROM orders ORDER by customers_id";

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

$repeats=0;

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

foreach ($line as $col_value) {

$cust_id=$col_value;

if ( $cust_id == $cust_id_last ) {

$repeats++;

}

$cust_id_last=$cust_id;

}

}

mysql_free_result($result);

 

 

# How many per month

 

$year = date('Y'); #current year

$month = date('M'); #current month

 

$dec = get_month("12", $year);

$nov = get_month("11", $year);

$oct = get_month("10", $year);

$sep = get_month("09", $year);

$aug = get_month("08", $year);

$jul = get_month("07", $year);

$jun = get_month("06", $year);

$may = get_month("05", $year);

$apr = get_month("04", $year);

$mar = get_month("03", $year);

$feb = get_month("02", $year);

$jan = get_month("01", $year);

$current_month = get_month($mo, $year);

 

 

# Only Process Month Info if Month has info to process

# Always tally totals, even if zero

 

//while ($i < 13)

// (

// $month_avg = $month_total / $current_month;

// $current_month_total = get_order_total($i, $year);

// $order = $order + $current_month_total;

// )

// $i++;

 

if ($jan != 0) $jan_avg = $jan_total / $jan;

$jan_total = get_order_total("01", $year);

$order = $order + $jan_total;

 

if ($feb != 0) $feb_avg = $feb_total / $feb;

$feb_total = get_order_total("02", $year);

$order = $order + $feb_total;

 

if ($mar != 0) $mar_avg = $mar_total / $mar;

$mar_total = get_order_total("03", $year);

$order = $order + $mar_total;

 

if ($apr != 0) $apr_avg = $apr_total / $apr;

$apr_total = get_order_total("04", $year);

$order = $order + $apr_total;

 

if ($may != 0) $may_avg = $may_total / $may;

$may_total = get_order_total("05", $year);

$order = $order + $may_total;

 

if ($jun != 0) $jun_avg = $jun_total / $jun;

$jun_total = get_order_total("06", $year);

$order = $order + $jun_total;

 

if ($jul != 0) $jul_avg = $jul_total / $jul;

$jul_total = get_order_total("07", $year);

$order = $order + $jul_total;

 

if ($aug != 0) $aug_avg = $aug_total / $aug;

$aug_total = get_order_total("08", $year);

$order = $order + $aug_total;

 

if ($sep != 0) $sep_avg = $sep_total / $sep;

$sep_total = get_order_total("09", $year);

$order = $order + $sep_total;

 

if ($oct != 0) $oct_avg = $oct_total / $oct;

$oct_total = get_order_total("10", $year);

$order = $order + $oct_total;

 

if ($nov != 0) $nov_avg = $nov_total / $nov;

$nov_total = get_order_total("11", $year);

$order = $order + $nov_total;

 

if ($dec != 0) $dec_avg = $dec_total / $dec;

$dec_total = get_order_total("12", $year);

$order = $order + $dec_total;

 

if ($current_month != 0) $current_month_avg = $current_month_total / $current_month;

$current_month_total = get_order_total($mo, $year);

 

 

# Order Status

$pending = get_status("1");

$processing = get_status("2");

$delivered = get_status("3");

$shipped = get_status("4");

 

# Daily Averages

if ($j !=0 ) $today_avg = $orders_today / $j;

else $today_avg = 0;

if ($yesterday != 0) $yesterday_avg = $orders_yesterday / $yesterday;

else ($yesterday_avg = 0);

 

$daily = $current_month / $today;

$daily_total = $current_month_total / $today;

 

if ($daily) $daily_avg = $daily_total / $daily;

else ($daily_avg = 0);

 

# Calculate days in this month for accurate sales projection

 

if ( ($mo == 1) OR ($mo == 3) OR ($mo == 5) OR ($mo == 7) OR ($mo == 8) OR ($mo == 10) OR ($mo == 12) )

$days_this_month = "31";

elseif ( ($mo == 4) OR ($mo == 6) OR ($mo == 9) OR ($mo == 11) )

$days_this_month = "30";

//calculate Feb end day, including leap year calculation from http://www.mitre.org/tech/cots/LEAPCALC.html

else {

if ( ($year % 4) != 0) $days_this_month = "28";

elseif ( ($year % 400) == 0) $days_this_month = "29";

elseif ( ($year % 100) == 0) $days_this_month = "28";

else $days_this_month = "29";

}

 

# Projected Profits this month

$projected = $daily * $days_this_month;

$projected_total = $daily_total * $days_this_month;

 

$gross_profit = $grand_total * .3;

 

$year_profit = $order * .3;

 

If ($newcust != 0) $close_ratio = $j / $newcust;

else $close_ratio = 0;

 

# format test into current

$total_orders = $jan + $feb + $mar + $apr + $may + $jun + $jul + $aug + $sep + $oct + $nov + $dec;

$total = $order / $total_orders;

$total = number_format($total,2,'.',',');

 

$order = number_format($order,2,'.',',');

$grand_total = number_format($grand_total,2,'.',',');

 

$gross_profit = number_format($gross_profit,2,'.',',');

$year_profit = number_format($year_profit,2,'.',',');

 

$projected = number_format($projected,0,'.',',');

$projected_total = number_format($projected_total,2,'.',',');

 

$close_ratio = number_format($close_ratio,2,'.',',');

 

$yesterday_avg = number_format($yesterday_avg,2,'.',',');

 

$dec_total = number_format($dec_total,2,'.',',');

$nov_total = number_format($nov_total,2,'.',',');

$oct_total = number_format($oct_total,2,'.',',');

$sep_total = number_format($sep_total,2,'.',',');

$aug_total = number_format($aug_total,2,'.',',');

$jul_total = number_format($jul_total,2,'.',',');

$jun_total = number_format($jun_total,2,'.',',');

$may_total = number_format($may_total,2,'.',',');

$apr_total = number_format($apr_total,2,'.',',');

$mar_total = number_format($mar_total,2,'.',',');

$feb_total = number_format($feb_total,2,'.',',');

$jan_total = number_format($jan_total,2,'.',',');

 

 

$orders_today = number_format($orders_today,2,'.',',');

$orders_yesterday = number_format($orders_yesterday,2,'.',',');

 

$dec_avg = number_format($dec_avg,2,'.',',');

$nov_avg = number_format($nov_avg,2,'.',',');

$oct_avg = number_format($oct_avg,2,'.',',');

$sep_avg = number_format($sep_avg,2,'.',',');

$aug_avg = number_format($aug_avg,2,'.',',');

$jul_avg = number_format($jul_avg,2,'.',',');

$jun_avg = number_format($jun_avg,2,'.',',');

$may_avg = number_format($may_avg,2,'.',',');

$apr_avg = number_format($apr_avg,2,'.',',');

$mar_avg = number_format($mar_avg,2,'.',',');

$feb_avg = number_format($feb_avg,2,'.',',');

$jan_avg = number_format($jan_avg,2,'.',',');

 

$today_avg = number_format($today_avg,2,'.',',');

 

if ($total_orders !=0) $shipping_avg = $shipping / $total_orders;

else $shipping_avg = 0;

 

$shipping_avg = number_format($shipping_avg,2,'.',',');

$shipping = number_format($shipping,2,'.',',');

 

$daily = number_format($daily,2,'.',',');

$daily_total = number_format($daily_total,2,'.',',');

$daily_avg = number_format($daily_avg,2,'.',',');

?>

 

<CENTER>

<TABLE BORDER=1 CELLPADDING=5 CELLSPACING=1>

<TR class="dataTableHeadingRow" bgcolor=silver><th class="dataTableHeadingContent">Description<th class="dataTableHeadingContent">Order Count<th class="dataTableHeadingContent">Dollar<th class="dataTableHeadingContent">Average</TR>

<!-- <TR bgcolor=silver><TH>Description<TH>Order Count<TH>Dollar<TH>Average</TR> -->

 

<TR class="dataTableRow">

<td class="dataTableContent" align="left">Today <?php echo "$mo-$today"; ?></TD><td class="dataTableContent" align="left"><?php echo "$j ($repeat_orders)"; ?> *</TD>

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

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

</TR>

<TR class="dataTableRow">

<td class="dataTableContent">Yesterday <?php echo "$mo-$day"; ?></TD>

<td class="dataTableContent"><?php echo $yesterday ?></TD>

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

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

</TR>

<TR class="dataTableRow">

<td class="dataTableContent">Daily Average for <?php echo $month ?></TD>

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

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

</TR>

<TR class="dataTableRow">

<td class="dataTableContent">Projected #'s for <?php echo $month ?></TD>

<td class="dataTableContent"><?php echo $projected ?></TD>

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

<td class="dataTableContent" align=right> </TD>

</TR>

 

<TR valign="bottom"><TD bgcolor=silver colspan=4></TD></TR>

 

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Jan 2004 Orders</TD><td class="dataTableContent" > <?php echo $jan ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Feb 2004 Orders</TD><td class="dataTableContent" > <?php echo $feb ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Mar 2004 Orders</TD><td class="dataTableContent" > <?php echo $mar ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Apr 2004 Orders</TD><td class="dataTableContent" > <?php echo $apr ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >May 2004 Orders</TD><td class="dataTableContent" > <?php echo $may ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Jun 2004 Orders</TD><td class="dataTableContent" > <?php echo $jun ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Jul 2004 Orders</TD><td class="dataTableContent" > <?php echo $jul ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Aug 2004 Orders</TD><td class="dataTableContent" > <?php echo $aug ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Sep 2004 Orders</TD><td class="dataTableContent" > <?php echo $sep ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Oct 2004 Orders</TD><td class="dataTableContent" > <?php echo $oct ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Nov 2004 Orders</TD><td class="dataTableContent" > <?php echo $nov ?></TD>

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

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

 

<TR>

<TR class="dataTableRow"><td class="dataTableContent" >Dec 2004 Orders</TD><td class="dataTableContent" > <?php echo $dec ?></TD>

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

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

 

<TR class="dataTableRow" >

<td class="dataTableContent" NOWRAP><B>Total <?php echo $year ?> Orders</TD><td class="dataTableContent"><B><?php echo "$total_orders / $repeats"; ?> *</TD><td class="dataTableContent" align=right><B>$<?php echo $order ?></TD><td class="dataTableContent" align=right><B>$<?php echo $total ?></TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent"><B><?php echo $year ?> Profit @ 30%</TD><td class="dataTableContent" colspan=2 align=right><B>$<?php echo $year_profit ?></TD><td class="dataTableContent" align=right> </TD></TR>

 

<TR valign="bottom"><TD bgcolor=silver colspan=4></TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Total Customers</TD><td class="dataTableContent"><?php echo $i ?></TD><td class="dataTableContent"><A href="whos_online.php">Online Now</a></TD><td class="dataTableContent"><?php echo "$who / $who_again"; ?> *</TD></TR>

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

 

<TR class="dataTableHeadingRow" bgcolor=silver><td class="dataTableContent" class="dataTableHeadingContent" COLSPAN=4><b><center><br>**Grand Totals</b></center></TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent"><B>Grand Total**</TD><td class="dataTableContent" colspan=2 align=right><B>$<?php echo $grand_total ?></TD><td class="dataTableContent" align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent"><B>Gross Profit @ 30%</TD><td class="dataTableContent" colspan=2 align=right><B>$<?php echo $gross_profit ?></TD><td class="dataTableContent" align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Orders Pending</TD><td class="dataTableContent"><?php echo $pending ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Orders Processing</TD><td class="dataTableContent"><?php echo $processing ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Orders Shipped</TD><td class="dataTableContent"><?php echo $shipped ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Orders Delivered</TD><td class="dataTableContent"><?php echo $delivered ?></TD><td class="dataTableContent" colspan=2 align=right> </TD></TR>

<TR class="dataTableRow" ><td class="dataTableContent">Shipping Charges</TD><td class="dataTableContent" colspan=2 align=right>$<?php echo $shipping ?></TD><td class="dataTableContent" align=right>$<?php echo $shipping_avg ?></TD></TR>

 

 

 

</TABLE>

 

<FONT SIZE=-1>

<br>

* - total / repeat customer

<BR>

** - Grand Totals since database inception. Raw numbers pulled from database on select * from orders_total</FONT>

 

 

</td>

</tr>

</table></td>

</tr>

</table></td>

<!-- body_text_eof //-->

</tr>

</table>

 

<!-- body_eof //-->

 

<!-- footer //-->

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<!-- footer_eof //-->

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Share this post


Link to post
Share on other sites

looks good.... great job...

are you going to package this up?... Linda McGrath sent me some mods on this as well but my email crashed and need to contact her again.

but, it runs smooth.

load it up and great work Jared!


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

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

Share this post


Link to post
Share on other sites
Guest

I'm happy to package it up - - Would you like me/you to include Linda's mods on this first?

 

I changed it again a bit today - - made the "Today" stats clickable links to orders.php, and the "Online Now" clickable link to the "who's online" page. Minor stuff, but convenient.

 

-jared

Share this post


Link to post
Share on other sites
Guest

found a problem. I forgot to re-include the day formatting normalization when $day < 10.

 

Find:

// next line to normalize $day format to 2 digits

$day = "0$day";

 

and change it to:

// next line to normalize $day format to 2 digits

if ($day <10) {$day = "0$day";}

 

-jared

Share this post


Link to post
Share on other sites
Guest

I use it with MS2 - - don't know for sure with MS1.

 

-jared

Share this post


Link to post
Share on other sites

It only took a little minor tweaking to get it working with MS1.

 

I also noticed a but where the Daily average from Jan/Feb/March...etc was not calulating.

 

I changed this

 

if ($jan != 0) $jan_avg = $jan_total / $jan;

$jan_total = get_order_total("01", $year);

$order = $order + $jan_total;

 

to this...

$jan_total = get_order_total("01", $year);

if ($jan != 0) $jan_avg = $jan_total / $jan;

$order = $order + $jan_total;

 

repeat for all other months.

 

Chris

Share this post


Link to post
Share on other sites

my message should have said, I also noticed a bug not but....

 

Has anyone modified this to allow for a selectable Year?

 

I would like to see what last year looked like.

 

thanks for any info

 

Chris

Share this post


Link to post
Share on other sites

Hey All...

no drop down box is in yet.... I'm working on some adjustments.... Jared are you ready to package it yet?...


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

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

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

×