Order Tracker New Contrib....

jello1 · September 14, 2003

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!

September 14, 2003

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)

jello1 · September 14, 2003

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

jello1 · September 14, 2003

196 is whitespace

September 14, 2003

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

September 15, 2003

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

jello1 · September 15, 2003

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

thanks for the input

January 23, 2004

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

January 23, 2004

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

January 23, 2004

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;

}

?>

January 23, 2004

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

?>

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

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

</TR>

<TR>

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

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

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

</TR>

<TR>

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

</TR>

<TR>

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

</TR>

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

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

* - total / repeat customer

<BR>

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

</BODY>

</HTML>

January 23, 2004

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

jello1 · January 31, 2004

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!

February 1, 2004

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

February 5, 2004

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

February 5, 2004

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

<head>

</head>

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

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

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

</table></td>

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

<tr>

<?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,'.',',');

?>

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

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

</TR>

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

</TR>

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

</TR>

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

</TR>

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

<TR>

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

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

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

</tr>

</table>

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

</body>

</html>

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

jello1 · February 5, 2004

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!

February 6, 2004

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

February 11, 2004

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

cgchris99 · February 13, 2004

Is this compatible with ms1 and ms2 ?

Thanks

February 13, 2004

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

-jared

cgchris99 · February 16, 2004

Is there a contribution for this yet?

cgchris99 · February 16, 2004

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

cgchris99 · February 16, 2004

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

jello1 · February 16, 2004

Hey All...

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

Order Tracker New Contrib....

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation