Bakery Reports Add-on

hillschmidt · June 11, 2014, 09:14:44 AM

Didn't work further since the last post ... so the above is still valid - this is the code to realize the rudimental I talked about:

Code Select

<?php

/*
  Module developed for the Open Source Content Management System WebsiteBaker (http://WebsiteBaker.org)
  Copyright (C) 2012, Christoph Marti

  LICENCE TERMS:
  This module is free software. You can redistribute it and/or modify it 
  under the terms of the GNU General Public License - version 2 or later, 
  as published by the Free Software Foundation: http://www.gnu.org/licenses/gpl.html.

  DISCLAIMER:
  This module is distributed in the hope that it will be useful, 
  but WITHOUT ANY WARRANTY; without even the implied warranty of 
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 
  GNU General Public License for more details.
*/


// include WB config.php file and admin class
require('../../config.php');
require_once(WB_PATH.'/framework/class.admin.php');

// Check if GET and SESSION vars are set
//if (!isset($_GET['page_id']) OR !isset($_GET['section_id']) OR !isset($_GET['order_id']) OR !is_numeric($_GET['page_id']) OR !is_numeric($_GET['section_id']) OR !is_numeric($_GET['order_id']) OR !isset($_SESSION['USER_ID']) OR !isset($_SESSION['GROUP_ID'])) {
//	die($MESSAGE['FRONTEND']['SORRY_NO_VIEWING_PERMISSIONS']);
	
if (!isset($_GET['page_id']) OR !isset($_GET['section_id']) OR !isset($_GET['YYYY']) OR !isset($_GET['MM']) OR !is_numeric($_GET['page_id']) OR !is_numeric($_GET['section_id']) OR !isset($_SESSION['USER_ID']) OR !isset($_SESSION['GROUP_ID'])) {
	die($MESSAGE['FRONTEND']['SORRY_NO_VIEWING_PERMISSIONS']);


} else {
	$page_id = $_GET['page_id'];
	$section_id = $_GET['section_id'];
	//$order_id = $_GET['order_id'];
	
	$order_YYYY = $_GET['YYYY'];
	$order_MM = $_GET['MM'];
    $start_date=$order_YYYY."-".$order_MM."-01";
	if ($order_MM=="01" OR $order_MM=="03" OR $order_MM=="05" OR $order_MM=="07" OR $order_MM=="08" OR $order_MM=="10" OR $order_MM=="12") 
	{
        $end_date=$order_YYYY."-".$order_MM."-31";
    }
    elseif ($order_MM=="02") 
    {
        $end_date=$order_YYYY."-".$order_MM."-28";
    }
    elseif ($order_MM=="04" OR $order_MM=="06" OR $order_MM=="09" OR $order_MM=="11") 
    {
        $end_date=$order_YYYY."-".$order_MM."-30";
    }
}

// Check if user is authenticated to view this page
$admin = new admin('', '', false, false);
if ($admin->get_page_permission($page_id, $action='admin') === false) {
	// User allowed to view this page
	die($MESSAGE['ADMIN']['INSUFFICIENT_PRIVELLIGES']);
}



//Look for language File
if (LANGUAGE_LOADED) {
    require_once(WB_PATH.'/modules/bakery/languages/EN.php');
    if (file_exists(WB_PATH.'/modules/bakery/languages/'.LANGUAGE.'.php')) {
        require_once(WB_PATH.'/modules/bakery/languages/'.LANGUAGE.'.php');
    }
}

// Header
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title><?php echo $MOD_BAKERY['TXT_ORDER']." ".$TEXT['VIEW_DETAILS']; ?></title>
<meta http-equiv="Content-Type" content="text/html; charset=<?php if (defined('DEFAULT_CHARSET')) { echo DEFAULT_CHARSET; } else { echo 'utf-8'; }?>" />
<link href="<?php echo WB_URL; ?>/modules/bakery/backend.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="order">
  <table width="540px" align="center" border="0" cellspacing="0" cellpadding="3">
    <tr>
  	  <td><span class="mod_bakery_order_b"><?php echo $MOD_BAKERY['TXT_ORDER_ID']."</span>: " ?></td>
	  <td><span class="mod_bakery_order_b"><?php echo $MOD_BAKERY['TXT_ORDER_DATE']."</span>: " ?></td>
	  <td><span class="mod_bakery_order_b"><?php echo $MOD_BAKERY['TXT_ADDRESS']."</span>: " ?></td>
	  <td><span class="mod_bakery_order_b"><?php echo $MOD_BAKERY['TXT_TOTAL']."</span>: " ?></td>
    </tr>

<?php
$ORDER_SUM=0;
// Get invoice data string from db customer table
//$query_customer = $database->query("SELECT invoice FROM ".TABLE_PREFIX."mod_bakery_customer WHERE order_id = '$order_id'");

$query_customer = $database->query("SELECT invoice FROM ".TABLE_PREFIX."mod_bakery_customer WHERE order_date >= UNIX_TIMESTAMP('$start_date') AND order_date <= UNIX_TIMESTAMP('$end_date') ORDER BY order_id");

//if ($query_customer->numRows() > 0) {

//while ($query_customer->numRows() > 0) {


$ANZ = $query_customer->numRows();
$i = 1;
while ($i <= $ANZ) {
	$customer = $query_customer->fetchRow();
	if ($customer['invoice'] != '') {
		// Convert string to array
		$invoice = stripslashes($customer['invoice']);
		$invoice_array = explode("&&&&&", $invoice);
		// Vars
		$order_id       = $invoice_array[0];
		#$shop_name     = $invoice_array[1];
		#$bank_account  = $invoice_array[2];
		#$cust_name     = $invoice_array[3];
		#$address       = $invoice_array[4];
		$cust_address   = $invoice_array[5];
		$ship_address   = $invoice_array[6];
		#$cust_email    = $invoice_array[7];
		$html_item_list = $invoice_array[8];
		$order_date     = $invoice_array[9];

		// If given get customer tax no
		$cust_tax_no    = isset($invoice_array[15]) ? $invoice_array[15] : '';
		$display_tax_no = $cust_tax_no == ''        ? 'none'             : '';

		// Change frontend classes (eg. mod_bakery_anything_f) to backend classes (eg. mod_bakery_anything_b)
		$html_item_list = str_replace("_f'", "_b'", $html_item_list);
		
		
		$ZW_SUM=explode($MOD_BAKERY['TXT_TOTAL'],$html_item_list);
		$CALC_ZW=explode("&nbsp;",$ZW_SUM[1]);
		$CALC_ZW[1]=str_replace(",", ".",$CALC_ZW[1]);
		$ORDER_SUM=$ORDER_SUM + strip_tags($CALC_ZW[1]);

// Show order
?>
  	<tr>
	  <td><?php echo strip_tags($order_id); ?></td>
	  <td><?php echo strip_tags($order_date); ?></td>
      <?php $cust=explode(" ",$cust_address); ?> 
      <td valign="top" width="30%"><?php echo strip_tags($cust[0])." ".strip_tags($cust[1]); ?></td>
	  <td><?php echo strip_tags($ZW_SUM[1]); ?></td> 
	</tr>

	<?php
	
	$i++;
	
	}
	//else {
	//echo "<p class='mod_bakery_error_b'>".$TEXT['NONE_FOUND']."!</p>";
	//echo "<p style='text-align: right;'><input type='button' value='{$TEXT['CLOSE']}' onclick='javascript: window.close();' style='width: 120px; margin-top: 5px;' /></p>";
	//}
}
?>
  </table>
</div>
	
	<table width="540px" align="center" border="0" cellspacing="0" cellpadding="3">
	<tr id="button" valign="top">
	  <td colspan="3" height="30" align="left" style="padding-left: 12px;">&nbsp;</td>
	  <td colspan="3" height="30" align="right" style="padding-right: 12px;"><?php printf("%01.2f",$ORDER_SUM); ?></td>
	</tr>
	<tr id="button" valign="top">
	  <td colspan="3" height="30" align="left" style="padding-left: 12px;">&nbsp;</td>
	  <td colspan="3" height="30" align="right" style="padding-right: 12px;">
	    <input type="button" value="<?php echo $TEXT['CLOSE']; ?>" onclick="javascript: window.close();" style="width: 120px; margin-top: 5px;" />
	  </td>
	</tr>
  </table>


</body>

avde · June 09, 2014, 11:46:08 PM

Hi,

can you please share the file for generating the report?

thx!

hillschmidt · November 22, 2013, 02:05:46 PM

so far, only the 'only' option has been realized

meaning selection of all orders within a given month.

The Bakery Shop is not a customer based one but a order based one (see bakery-shop.ch:

Quote

No customer registration needed
For logged in customers Bakery tries to retrieve their address data of a previous order to prepopulat the address fields

) so it could become very hard to search all orders for a specific customer. The invoice is ONE field in the order so that you must scan this (and you will fail as soon as a different input or a typo makes a difference in the customer's section within this field ...)

jacobi22 · November 22, 2013, 01:10:27 PM

some question: is the report separatet by user btw customers or "only" for a special time like a mouth or a week?

hillschmidt · November 22, 2013, 09:35:16 AM

Quotehow are you telling Bakery to show you only the November report

That's one I will improve (if time remains): currently you could call the script only manual with page_id, section_id you need to know for a Bakery page, and also for the year YYYY and Month MM:
my_baker_domain/CMS/modules/bakery/test_order_summary.php?page_id=20&section_id=30&YYYY=2013&MM=11

But having a new selection form on the admin page for reports like this, you could select and change this - currently you must type the lengthy URL and change manually to see another month/year, e.g.
my_baker_domain/CMS/modules/bakery/test_order_summary.php?page_id=20&section_id=30&YYYY=2012&MM=07
for July 2012 e.g.

svsanchez · November 22, 2013, 12:52:55 AM

Hello Hillshmidt, that's looking awesome, I didn't understand what you said about the selection of YYYY and MM, how are you telling Bakery to show you only the November report?

hillschmidt · November 20, 2013, 02:51:31 PM

got kind of, using a quick and dirty call for the moment:

Code Select

http://my_baker_domain/CMS/modules/bakery/test_order_summary.php?page_id=20&section_id=30&YYYY=2013&MM=11
So far, I do not use a selection form for YYYY and MM ...
The script bases on view_order.php, so far quick&dirty code but the output (German Shop) looks like this:
where Adresse is only the name of the customer.
~~Also the total is not correct yet~~ ... some improvements needed of course ... but looks this O.K. for a starting point ?
Changed the script again - now the Sum is correct (problem with the German , as decimal separator and the needed .)

hillschmidt · November 14, 2013, 04:12:52 PM

No, I'm not the developer ...

... but looking into with SQL now ...
... the basis so far are two SELECTs:
select * from wb_mod_bakery_customer where order_date >= UNIX_TIMESTAMP('..') and ...< ...
so that in PHP a loop could follow for each entry:
select quantity, shipping, price from wb_mod_bakery_order where order_id=...
but very rudimental in the moment (no time) and probably not in sync with the real invoice ...

Will see what will happen ...

svsanchez · November 08, 2013, 07:03:17 PM

Hello hillschmidt, are you the developer of the Bakery module? What I meant by Sales and Stock reports is:

SALES REPORT
Being able to generate a report by date for example chose the month and see all orders from that month, that way we easily know how much we sold during that period. Example:

01/10/2013 Customer A Name Order #001 $55
02/10/2013 Customer B Name Order #002 $49
...
31/10/2013 Customer C Name Order #100 $89
--------------------------------------------------------------------------
TOTAL SALES DURING OCTOBER 2013: $5000

If it were possible to set the dates range for the report it would be so much great! And also possibility of exporting to Excel!

STOCK REPORT:
If it were possible to export the Stock to Excel it would be very useful!

hillschmidt · November 08, 2013, 03:02:37 PM

I checked my 1.60 version.
Stock is displayed (in my point of view) OK, see also http://www.bakery-shop.ch/#stock_administration

Order display is possible also in the admin function - but not a complete list of ALL orders in ONE view (really wanted ?) Here the view_order.php could give some idea - unfortunately I do not have time to investigate further. Possible should be to generate a link list per customer, showing all orders.
Cust A
- Order 12
- Order 27
- Order 50
Cust B
- Order 34
- order 46
and so on .... I will think about it.

svsanchez · November 04, 2013, 07:53:40 PM

Hello again, could someone please tell me if there are any add-ons for generating sales and stock reports?

svsanchez · October 30, 2013, 01:22:12 AM

Hello Hillschmidt, thank you for your reply. What I would like is to generate sales and stock reports.

hillschmidt · October 29, 2013, 10:22:50 AM

what kind of reports are you looking for?

svsanchez · October 29, 2013, 01:20:58 AM

Hello, I would like to know if there is an add-on module for generating Bakery reports?

Thank you!

News:

Bakery Reports Add-on

jacobi22