I need a custom daily report of orders

**AATECH** · 21 Oct 2009, 12:41 AM

Hi everyone.
I was wondering if someone could show me where to start if I wanted to get the following done. I know it's just a matter of calling the info from the database, but how do I go about it.

Have a section in the backend of the site where:

You put in ONE specific date such as 10/15/2009 in a text field
and a report should generate a printable format like the one attached below with all the orders placed for that day.

[SCR]http://aatechdesign.com/private/DailyReport.jpg[/SCR]

Also if possible add the total of all the invoices for that day together somewhere at the bottom.

Thanks in advance.

**DrByte** · 21 Oct 2009, 05:13 AM

Did you look at the "Sales Report" addon?

**AATECH** · 21 Oct 2009, 11:31 AM

Thank you so much for the reply. We actually went ahead with this add-on "Export invoice totals to .csv file for Excel use" in:
http://www.zen-cart.com/index.php?ma...roducts_id=170

We have made some changes and everything works, except there are two fields we need to add to this that is getting pulled from the "customers" table rather than the "orders" table in the MySql database.

The two fields we want is "customers_referral" and "customers_sales_person" (custom coded in there from before).

Here's the full code page below:

Code:

 
 
<?php
/**
 * /public_html/store/admin/export.php
 *
 * Export invoice data from Zend to Microsoft Excel CSV
 *
 * @author    Ahmed Chafik <ajchafik######################>
 * @copyright Cyberscraps 2006
 * @version   Define("Id: export.php,v 1 2006/06/21 21:24:53 Ahmed")
 *
 * Released under the terms and conditions of the
 * GNU General Public License (Version 2)
 *
 */
 require('includes/application_top.php');
 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();
 if( isset($_POST['download_csv']) ) {
    Header('Content-type: application/csv');
    Header("Content-disposition: attachment; filename=\"Payments".date('mdy-Hi').".csv\"");
     $order = $db->execute("SELECT orders_id, date_purchased, payment_method,
                                   customers_name, customers_company, order_tax, order_total
                              FROM ". TABLE_ORDERS ."
                            WHERE downloaded='no'
                            ORDER BY orders_id ASC");
     echo "Invoice No.,Date of Order,Payment Method,Customer Name,Customer Company,Tax,Total (excluding tax),Total (including tax),Sales Person,Shipped Date,Date Received\n";
     while( !$order->EOF ) {
       list( $invoice, $date, $method, $name, $customers_company, $tax, $total ) = array_values($order->fields);

       $get_tax = ((($total-5)/($total-5-$tax))-1)*100;
       preg_match('/^[0-9]{2}([0-9]{2})\-([0-9]{2})\-([0-9]{2}) .+$/', $date, $args);
       list( , $year, $month, $day ) = $args;
       echo "$invoice,".
            "$month/$day/$year,\"$method\",\"$name\",\"$customers_company\",\"".
            zen_display_tax_value($get_tax, true).'%","'.
            $currencies->format($total-$tax).'","'.
            $currencies->format($total). '","'. "Bob Smith" ."\"\n";
        $order->MoveNext();
     }
/** Changed This By AA TECH to be able to keep downloading all files instead of only once. 
 **     $db->execute('UPDATE '. TABLE_ORDERS .' SET downloaded="yes" WHERE downloaded="no"');
 **/
     $db->execute('UPDATE '. TABLE_ORDERS .' SET downloaded="no" WHERE downloaded="no"');
/** END OF IT HERE **/

   exit;
 }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?= HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?= CHARSET; ?>">
<title><?= TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
    <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">Order History</td>
            <td class="pageHeading" align="right"><?php echo zen_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">Invoice No.</td>
                <td class="dataTableHeadingContent">Date of Order</td>
                <td class="dataTableHeadingContent">Payment Method</td>
                <td class="dataTableHeadingContent">Customer Name</td>
                <td class="dataTableHeadingContent">Customer Company</td>
                <td class="dataTableHeadingContent">Tax</td>
                <td class="dataTableHeadingContent">Total (excluding tax)</td>
                <td class="dataTableHeadingContent" align="right">Total (including tax)&nbsp;</td>
    <!-- ADDED BY AA TECH -->
    <td class="dataTableHeadingContent" align="right">Sales Person &nbsp;</td>
    <td class="dataTableHeadingContent" align="right">Shipped Date &nbsp;</td>
    <td class="dataTableHeadingContent" align="right">Date Received &nbsp;</td>
    <!-- END OF - ADDED BY AA TECH -->
              </tr>
<?php
 $query = "SELECT orders_id, date_purchased, payment_method,
                  customers_name, customers_company, order_tax, order_total
           FROM ". TABLE_ORDERS ."
           ORDER BY orders_id ASC";
 $query = strtolower($query);
 $order_pages = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $query, $rows);
 $order = $db->execute($query);
 while( !$order->EOF ) {
   list( $invoice, $date, $method, $name, $customers_company, $tax, $total ) = array_values($order->fields);

   $get_tax = ((($total-5)/($total-5-$tax))-1)*100;
?>
              <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="window.open('<?= zen_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $invoice, 'NONSSL'); ?>')">
                <td class="dataTableContent" align="center"><?= $invoice; ?>&nbsp;&nbsp;</td>
                <td class="dataTableContent"><?= zen_date_long($date) ?></td>
                <td class="dataTableContent"><?= $method ?></td>
                <td class="dataTableContent"><?= $name ?></td>
    <td class="dataTableContent"><?= $customers_company ?></td>
                <td class="dataTableContent"><?= zen_display_tax_value($get_tax, true) ?>%</td>
                <td class="dataTableContent"><?= $currencies->format($total-$tax); ?></td>
                <td class="dataTableContent" align="right"><?= $currencies->format($total); ?>&nbsp;</td>
    <!-- ADDED BY AA TECH -->
<td class="dataTableContent" align="center">Bob Smith</td>
<td class="dataTableContent" align="center">&nbsp;&nbsp;</td>
<td class="dataTableContent" align="center">&nbsp;&nbsp;</td>
    <!-- END OF - ADDED BY AA TECH -->
              </tr>
<?php
    $order->MoveNext();
  }
  if( !isset($invoice) ) {
  ?>
              <tr class="dataTableRow">
                <td class="dataTableContent" align="center" colspan="7"><b>No orders were found!</b></td>
              </tr>
  <?
  }
?>
            </table></td>
          </tr>
          <tr>
            <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                <td class="smallText" valign="top"><?php echo $order_pages->display_count($rows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], 'Displaying <b>%d</b> to <b>%d</b> (of <b>%d</b> orders)'); ?></td>
                <td class="smallText" align="right"><?php echo $order_pages->display_links($rows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page']); ?></td>
              </tr>
            </td>
            <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr>
                <td class="smallText" align="right">
                    <form name="download_csv" method="post">
                        <input style="font-weight: bold" name="download_csv" type="submit" value="Export to Excel Spreadsheet" />
                    </form>
                </td>
              </tr>
            </table></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'); ?>

"Bob Smith" is where the sales person's name from the table "customers" is going to go.

I'm not really a MySQL savvy, but is there any way to include the other fields in this code here so we can call it.

Code:

 
 
     $order = $db->execute("SELECT orders_id, date_purchased, payment_method,
                                   customers_name, customers_company, order_tax, order_total
                              FROM ". TABLE_ORDERS ."
                            WHERE downloaded='no'
                            ORDER BY orders_id ASC");

We have added the following info into the super orders invoice page module, and it works fine, but can't get it to to work for here.

This is what we have in the super_invoice.php Page:

Code:

 
 
// Added Customer Number By AA TECH
  $sql = "SELECT o.customers_id, c.customers_referral from " . TABLE_ORDERS . " o, " . TABLE_CUSTOMERS . " c WHERE c.customers_id= o.customers_id and o.orders_id = '" . $oID . "'"; 
  $chk_customers_referral = $db->Execute($sql); 
  $customers_referral2 = $chk_customers_referral->fields['customers_referral'];  
// Added Sales Person By AA TECH
  $sql = "SELECT o.customers_id, c.customers_sales_person from " . TABLE_ORDERS . " o, " . TABLE_CUSTOMERS . " c WHERE c.customers_id= o.customers_id and o.orders_id = '" . $oID . "'"; 
  $chk_customers_sales_person = $db->Execute($sql); 
  $customers_sales_person2 = $chk_customers_sales_person->fields['customers_sales_person'];

**AATECH** · 22 Oct 2009, 09:52 PM

Can someone please let me know if this is an easy fix?

can we add something like this:

$order = $db->execute("SELECT orders_id, date_purchased, payment_method,
customers_name, customers_company, order_tax, order_total
FROM ". TABLE_ORDERS ." <-- [and perhaps add also From TABLE_CUSTOMERS so we can get some fields from there as well?]
WHERE downloaded='no' <-- [Never mind this section]

ORDER BY orders_id ASC");

Thank you.