Trying to determine profit levels. Please look.

blueline · March 7, 2003

Hey all. I am trying to show a client there profit per order, when looking at the "Customer Orders-Total" link under the Reports tab in the Admin.

I have written all of the code so that the user can input the cost of the products when inputting all other data. I am actually all done writing the code.

The problem is that when the user clicks on "Customers Orders-Total", everything shows up properly....but the amounts are not right. When pulling up invoices the amounts are perfect. I have included the code below for /Catalog/Admin/Stats_Customers.php.

Please take a look at let me know what you are thinking.

Thanks,

-Chris

<?php

/*

 $Id: stats_customers.php,v 1.29 2002/05/16 15:32:22 hpdl Exp $



 osCommerce, Open Source E-Commerce Solutions

 http://www.oscommerce.com



 Copyright (c) 2002 osCommerce



 Released under the GNU General Public License

*/



 require('includes/application_top.php');



 require(DIR_WS_CLASSES . 'currencies.php');

 $currencies = new currencies();

?>

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

</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="2">

     <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="2">

         <tr>

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

             <tr class="dataTableHeadingRow">

               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td>

               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>

               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PROFIT; ?></td>

               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td>

             </tr>

<?php

 if ($HTTP_GET_VARS['page'] > 1) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;

 $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum, sum(op.products_quantity * p.products_dealer_cost) as profit from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

 $customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);

// fix counted customers

 $customers_query_numrows = tep_db_query("select customers_id from " . TABLE_ORDERS . " group by customers_id");

 $customers_query_numrows = tep_db_num_rows($customers_query_numrows);



 $customers_query = tep_db_query($customers_query_raw);

 while ($customers = tep_db_fetch_array($customers_query)) {

   $rows++;



   if (strlen($rows) < 2) {

     $rows = '0' . $rows;

   }

?>

             <tr class="dataTableRow" onmouseover="this.className='dataTableRowOver';this.style.cursor='hand'" onmouseout="this.className='dataTableRow'" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL'); ?>'">

               <td class="dataTableContent"><?php echo $rows; ?>.</td>

               <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL') . '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>

   <td class="dataTableContent" align="right"><?php echo $currencies->format($customers['profit']); ?> </td>                

   <td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>

             </tr>

<?php

 }

?>

           </table></td>

         </tr>

         <tr>

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

             <tr>

               <td class="smallText" valign="top"><?php echo $customers_split->display_count($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_CUSTOMERS); ?></td>

               <td class="smallText" align="right"><?php echo $customers_split->display_links($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?> </td>

             </tr>

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

Notice that I changed a couple of things.....the only one that could impact the prices though would be the SQL query.

Thanks again.

blueline · March 7, 2003

To give you an idea....under William Trotter I purchased 1 dvd, priced at 49.99 i think (USD). I put cost at somewhere around 30-35 (USD). This is the end result.

See what's wrong here?

Thanks,

-Chris

blueline · March 8, 2003

Quick note.....

Linda pointed out an obvious mistake that I made. Profit != profit.

Meaning....'profit' should actually be 'cost' to keep the code accurate.

I fixed this, but that does not solve the problem.

Ajeh · March 8, 2003

Your image link will not load.

March 8, 2003

can you post the code where you enter the item cost initially?

blueline · March 8, 2003

Yes. I will put it up tomorrow morning.

Linda, the image loads just fine for me.....can anyone else see it?

-Thanks,

-Chris

March 8, 2003

the first time i read your msg the image link was showing as broken, this time the image is appearing fine..... :?:

blueline · March 8, 2003

Ok....I can feel it....almost there. Here's the problem:

I got the code to work just fine, but whenever I define TABLE_PRODUCTS . " p, " such as I do with every other table I use....that's when the math gets messed up. This makes absolutely no sense.

I rewrote all the code, tested every step, and the above situation is THE ONLY TIME, that the math doesn't come out correctly. Any ideas anyone?

Code that works:

<?php

$ord2_query = "select o.orders_id, op.orders_id, sum(op.products_quantity * op.final_price)as ordtotal2 from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op where o.orders_id = op.orders_id group by o.orders_id order by o.orders_id ASC";

$orders2_query = tep_db_query($ord2_query);

while ($or2 = tep_db_fetch_array($orders2_query)) {

  $rows++;



   if (strlen($rows) < 2) {

     $rows = '0' . $rows;

   }

?>

<table><tr><td>

<?php echo $or2['orders_id']; ?></td><br>

<td>

<?php echo $or2['ordtotal2']; ?></td>

</tr></table>

<?php

}



?>

The results:

Order No.     Order Total:

1                 64.9500 

2                 121.9800 

3                 49.9900 

4                 49.9900 

5                 69.9900

Same code with slight variation...define the products table, define the cost calculation, echo cost:

<?php

$ord2_query = "select o.orders_id, op.orders_id, sum(op.products_quantity * op.final_price)as ordtotal2, sum(op.products_quantity * p.products_dealer_cost) as cost from " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS_PRODUCTS . " op where o.orders_id = op.orders_id group by o.orders_id order by o.orders_id ASC";

$orders2_query = tep_db_query($ord2_query);

while ($or2 = tep_db_fetch_array($orders2_query)) {

  $rows++;



   if (strlen($rows) < 2) {

     $rows = '0' . $rows;

   }

?>

<table><tr><td>

<?php echo $or2['orders_id']; ?></td><br>

<td>

<?php echo $or2['ordtotal2']; ?></td><br>

<td>

<?php echo $or2['cost']; ?>

</td>

</tr></table>

<?php

}



?>

And these are the results I get:

Order No.     Order Total:     Cost Total:

1                 1753.6500        60.39   

2                 3293.4600        181.17   

3                 1349.7300        60.39   

4                 1349.7300        60.39   

5                 1889.7300        60.39

Thanks everyone,

-Chris

Trying to determine profit levels. Please look.

Recommended Posts

blueline

Link to comment

Share on other sites

blueline

Link to comment

Share on other sites

blueline

Link to comment

Share on other sites

Ajeh

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

blueline

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

blueline

Link to comment

Share on other sites

Archived

Browse

Activity