Results 1 to 4 of 4
  1. 9 Sep 2007, 06:27 AM #1
    MB1
    MB1 is offline Totally Zenned
    Join Date
    Jun 2006
    Posts
    566
    Plugin Contributions
    0

    Idea or Suggestion Exporting invoice details

    i'm trying to modify the export invoice totals mod in the downloads section having a hard time as i don't know anything about php and mysql

    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,  customers_state,
                                   customers_name, order_tax, order_total
                              FROM ". TABLE_ORDERS ."
                            WHERE downloaded='no'
                            ORDER BY orders_id ASC");

     echo "Invoice No.,Date,State,Customer Name,Tax,Total (excluding tax),Total (including tax)\n";

     while( !$order->EOF ) {
       list( $invoice, $date, $state, $name, $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 ) = $args;

       echo "$invoice,".
            "$month/$year,\"$state\",\"$name\",\"".
            zen_display_tax_value($get_tax, true).'&#37;","'.
            $currencies->format($total-$tax).'","'.
            $currencies->format($total)."\"\n";

        $order->MoveNext();

     }

     $db->execute('UPDATE '. TABLE_ORDERS .' SET downloaded="yes" WHERE downloaded="no"');

   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</td>
                <td class="dataTableHeadingContent">State</td>
                <td class="dataTableHeadingContent">Customer Name</td>
                <td class="dataTableHeadingContent">Tax</td>
                <td class="dataTableHeadingContent">Total (excluding tax)</td>
                <td class="dataTableHeadingContent" align="right">Total (including tax)&nbsp;</td>
              </tr>
<?php

 $query = "SELECT orders_id, date_purchased, customers_state,
                  customers_name, 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, $state, $name, $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="right"><?= $invoice; ?>&nbsp;&nbsp;</td>
                <td class="dataTableContent"><?= zen_date_long($date) ?></td>
                <td class="dataTableContent"><?= $state ?></td>
                <td class="dataTableContent"><?= $name ?></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>
              </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'); ?>
    all i'm trying to do is get the invoice number, date, province, tax exported then i can do the rest in excell

    pretty much just got to the state i was repalcing all the $name with $tax but it didn't work
  2. 10 Sep 2007, 04:50 AM #2
    MB1
    MB1 is offline Totally Zenned
    Join Date
    Jun 2006
    Posts
    566
    Plugin Contributions
    0

    Default Re: Configuring Taxes for Canadian Sites/Zones

    ok i think i got it figured out kinda

    took this module http://www.zen-cart.com/index.php?ma...roducts_id=170

    edit export.php to this point
    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,  customers_state,
                                   order_tax
                              FROM ". TABLE_ORDERS ."
                            WHERE downloaded='no'
                            ORDER BY orders_id ASC");

     echo "Invoice No.,Date,State,Tax \n";

     while( !$order->EOF ) {
       list( $invoice, $date, $state, $tax ) = array_values($order->fields);


       preg_match('/^[0-9]{2}([0-9]{2})\-([0-9]{2})\-([0-9]{2}) .+$/', $date, $args);
       list( $year, $month ) = $args;

       echo "$invoice,".
            "$month/$year,\"$state\",\"$tax\",\"".
            "\"\n";

        $order->MoveNext();

     }

     $db->execute('UPDATE '. TABLE_ORDERS .' SET downloaded="yes" WHERE downloaded="no"');

   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 Tax 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</td>
                <td class="dataTableHeadingContent">State</td>
                <td class="dataTableHeadingContent">Tax</td>
                </tr>
<?php

 $query = "SELECT orders_id, date_purchased, customers_state,
                  order_tax 
           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, $state, $tax) = array_values($order->fields);
  
?>
              <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="right"><?= $invoice; ?>&nbsp;&nbsp;</td>
                <td class="dataTableContent"><?= zen_date_long($date) ?></td>
                <td class="dataTableContent"><?= $state ?></td>
                <td class="dataTableContent"><?= $tax ?></td>
                        </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'); ?>
    now i can download my tax totals to my excel and just add up all the taxes however i want

    i'm just curious if anyone would know how to modify this file so i can pull the data on a month by month baises as of right now the data is pulled once and that is it as it becomes marked on the database and can't be pulled again unless you do some ediing in the database.
  3. 10 Sep 2007, 06:19 AM #3
    MB1
    MB1 is offline Totally Zenned
    Join Date
    Jun 2006
    Posts
    566
    Plugin Contributions
    0

    Default Re: Configuring Taxes for Canadian Sites/Zones

    i just found a problem i'm exporting the tax before it's being recalculated after discounts i'm not sure where in the database i could find that or how to change the code to make that work. any help would be totally appreciated as this lil mod hack will safe me at least 30mins every month when i process my pst
  4. 22 Sep 2007, 03:43 PM #4
    MB1
    MB1 is offline Totally Zenned
    Join Date
    Jun 2006
    Posts
    566
    Plugin Contributions
    0

    Default Re: Exporting invoice details

    any help?
 

 
« Previous Thread | Next Thread »

Similar Threads

  1. v1.2.x Exporting All Customer and Order Details
    By mahanteshpa in forum Managing Customers and Orders
    Replies: 3
    Last Post: 12 May 2015, 02:52 AM
  2. v1.2.x Exporting All Customer Details
    By mahanteshpa in forum Managing Customers and Orders
    Replies: 1
    Last Post: 27 Mar 2015, 09:58 AM
  3. exporting customers details
    By alpertheidiot in forum General Questions
    Replies: 2
    Last Post: 21 Dec 2009, 11:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR
Content and Graphics Copyright (c) 2003 - 2024 Zen Ventures, LLC - all rights reserved
Zen Cart® is a Registered Trademark of Zen Ventures, LLC