Sales reports exporting

oli.cartw.. Team Cartweaver

Re: Sales reports exporting

1 year ago

Hi ,

You would need to build your own queries in order to aggregate the information you need.

Basically, you will need to query tables tbl_orders and tbl_orderskus , making sure you only include processed order.

A good starting point would be for you to see how the admin filters the orders .

Oli
Cartweaver Support Team

stevenwol..

Reporting

4 months ago

do you have any basic queries you can share for getting customer list for a product?
I don't want to spend all my time reverse engineering the admin pages if you can give me some direction on what tables to join. The problem I have is that our database (left by my predecessor) is full of garbage tables making it difficult to navigate.
Thanks,
Steven

oli.cartw.. Team Cartweaver

Re: Sales reports exporting

4 months ago

Hi,

The involved tables are tables tbl_orders and tbl_orderskus .

We don't have any stock reports to offer.

Oli

Cartweaver Support Team

ontsdtx

Re: Sales reports exporting

4 months ago

Here is something to get you started on this modification. Insert the code below into a file named TotalRevenue.php in the admin folder of your site. Then there is a code to insert into the CWIncNav.php file that will help you run the simple report from the admin nav area.

<?php
require_once("application.php");
/*
================================================================
Application Info:
Cartweaver© 2002 - 2007, All Rights Reserved.
Developer Info:
Application Dynamics Inc.
1560 NE 10th
East Wenatchee, WA 98802
Support Info: http://www.cartweaver.com/go/phphelp

Cartweaver Version: 3.0.0 - Date: 4/21/2007

================================================================
Name: Orders.php
Description: Displays a list of orders filtered by the selected "Order Status"
================================================================
*/

/* Set location for highlighting in Nav Menu */
$strSelectNav = "Reports";

/* Set default values for order status and search dates */
if(!isset($_GET["searchBy"])) {$_GET["searchBy"] = 0;}
if(!isset($_POST["StartDate"])) {
$_POST["StartDate"] = cwDateFormat(date("Y/m/d",mktime(0,0,0,date("m"),date("d") - 7, date("y"))),true);
}else{

$theDate = cwGetDateFormat($_POST["StartDate"]);
$month = strftime("%m",$theDate);
$year = strftime("%Y",$theDate);
$day = strftime("%d",$theDate);
$_POST["StartDate"] = cwDateFormat("$year/$month/$day", true);
}

//default is last week
if(!isset($_POST["EndDate"])) {
$_POST["EndDate"] = cwDateFormat(date('Y/m/d'),true);
} else{
//$_POST["EndDate"] = cwDateFormat(cwGetDateFormat($_POST["EndDate"]), true);
$theDate = cwGetDateFormat($_POST["EndDate"]);
$month = strftime("%m",$theDate);
$year = strftime("%Y",$theDate);
$day = strftime("%d",$theDate);
$_POST["EndDate"] = cwDateFormat("$year/$month/$day", true);
}

if(!isset($_POST["Status"])) {$_POST["Status"] = '0';}

if($_GET["searchBy"] != 0) {
$_POST["Status"] = $_GET["searchBy"];
}

$query_rsCWRevenue = sprintf("SELECT O.order_ID,
O.order_Date,
O.order_Status,
O.order_Tax,
O.order_Shipping,
O.order_Total,
O.order_State,
OS.orderSKU_OrderID,
OS.orderSKU_SKUTotal,
OS.orderSKU_TaxRate,
OS.orderSKU_DiscountAmount
FROM tbl_orders O
INNER JOIN tbl_orderskus OS
ON O.order_ID = OS.orderSKU_OrderID
WHERE (O.order_Status = 2 || O.order_Status = 3 || O.order_Status = 9) AND (O.order_Date >= '%s'
AND O.order_Date <= '%s')
%s
ORDER BY order_Date DESC"
,mySQLDate($_POST["StartDate"])
,mySQLDate($_POST["EndDate"])
,($_POST["Status"] != 0) ? " AND order_Status = " .$_POST["Status"] : '');
$rsCWRevenue = $cartweaver->db->executeQuery($query_rsCWRevenue, "rsCWRevenue");
$rsCWRevenue_recordCount = $cartweaver->db->recordCount;
$row_rsCWRevenue = $cartweaver->db->db_fetch_assoc($rsCWRevenue);

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Admin: Report Revenue</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="assets/admin.css" rel="stylesheet" type="text/css">
<script type="text/javascript" language="JavaScript">

</script>
</head>
<body>

<?php include("CWIncNav.php");?>
<div id="divMainContent">

<form name="DateForm" method="post" action="<?php echo($cartweaver->thisPage);?>">
      From
        <input name="StartDate" type="text" required="yes" message="Must be a date - mm/dd/yyyy format" validate="date" value="<?php echo($_POST["StartDate"]);?>" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'StartDate')"><img src="assets/images/calendar.gif" alt="Click to Select Date" width="16" height="16"></a> To
        <input name="EndDate" type="text" required="yes" validate="date" message="Must be a date - mm/dd/yyyy format" value="<?php echo($_POST["EndDate"]);?>" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'EndDate')"><img src="assets/images/calendar.gif" width="16" height="16" style="margin-bottom:0px;" alt="Click to Select Date"></a>


      <input name="Submit" type="submit" class="formButton" value="Get Report">
</form>
<br />

<h1>Report: Revenue

</h1>
<?php /* Results Output table */
if($rsCWRevenue_recordCount !=0) { ?>
    <table>
      <tr>
        <th width="165">Order ID</th>
        <th width="170">Order Date</th>
  <th width="95">Sku Revenue</th>
  <th width="95">Sku Tax Revenue</th>
        <th width="95">Shipping Revenue</th>
  <th width="95">Shipping Tax Revenue</th>
  <th width="95">Discount Credit</th>
  <th width="150">Total Revenue</th>
      </tr>
      <?php /* loop through to show all records found */
      if($rsCWRevenue_recordCount == 0) {
        echo("<p>No results found.</p>");
      }
    $recCounter = 0;
do {
  ?>
        <tr style="text-align:center" class="<?php cwAltRow($recCounter++);?>">
          <td><?php echo($row_rsCWRevenue["order_ID"]);?></td>
          <td><?php echo(cwDateFormat($row_rsCWRevenue["order_Date"],false));?></td>
    <td><?php echo($row_rsCWRevenue["orderSKU_SKUTotal"]);?></td>
    <td><?php echo($row_rsCWRevenue["orderSKU_TaxRate"]);?></td>
    <td><?php echo($row_rsCWRevenue["order_Shipping"]);?></td>
          <td><?php echo($row_rsCWRevenue["order_Tax"]);?></td>
    <td><?php echo($row_rsCWRevenue["orderSKU_DiscountAmount"]);?></td>
    <td><?php echo($row_rsCWRevenue["order_Total"]);?></td>
        </tr>
      <?php } while ($row_rsCWRevenue = $cartweaver->db->db_fetch_assoc($rsCWRevenue)); ?>
    </table>
    <?php
} else {
echo("<p><strong>Sorry, no matching records found.");
/* If the user has tried a form search, and there are no results, prompt them to choose different dates */
if (isset ($_POST["StartDate"])) {
  echo("Try different dates.");
}
echo("</strong></p>");
}?>
</div>
</body>
</html>
<?php
cwDebugger($cartweaver);
?>

PLACE THIS CODE IN CWIncNav.php I placed it above the Categories tab around line 53

<a href="javascript:;" class="leftNav" id="lnReports" onclick="dwfaq_ToggleOMaticClass(this,'lnReports','leftNavOpen');dwfaq_ToggleOMaticDisplay(this,'lnSubReports');return document.MM_returnValue">Reports</a>
  <div id="lnSubReports" class="lnSubMenu" style="display: none;">
  <a href="TotalRevenue.php">–Revenue</a>

</div>

Hope this gives you a starting point to complete what you are looking for.

oli.cartw.. Team Cartweaver

Re: Sales reports exporting

4 months ago

Hi Stacey! Thanks for sharing !;-)

ontsdtx

Re: Sales reports exporting

4 months ago

After a short Christmas break from the computer I have been able to get something together that will generate a report like that above but with totals! Here is the code;

<?php
require_once("application.php");
/*
================================================================
Application Info:
Cartweaver© 2002 - 2007, All Rights Reserved.
Developer Info:
Application Dynamics Inc.
1560 NE 10th
East Wenatchee, WA 98802
Support Info: http://www.cartweaver.com/go/phphelp

Cartweaver Version: 3.0.0 - Date: 4/21/2007

================================================================
Name: Orders.php
Description: Displays a list of orders filtered by the selected "Order Status"
================================================================
*/

/* Set location for highlighting in Nav Menu */
$strSelectNav = "Reports";

/* Set default values for order status and search dates */
if(!isset($_GET["searchBy"])) {$_GET["searchBy"] = 0;}
if(!isset($_POST["StartDate"])) {
$_POST["StartDate"] = cwDateFormat(date("Y/m/d",mktime(0,0,0,date("m"),date("d") - 7, date("y"))),true);
}else{

$theDate = cwGetDateFormat($_POST["StartDate"]);
$month = strftime("%m",$theDate);
$year = strftime("%Y",$theDate);
$day = strftime("%d",$theDate);
$_POST["StartDate"] = cwDateFormat("$year/$month/$day", true);
}

//default is last week
if(!isset($_POST["EndDate"])) {
$_POST["EndDate"] = cwDateFormat(date('Y/m/d'),true);
} else{
//$_POST["EndDate"] = cwDateFormat(cwGetDateFormat($_POST["EndDate"]), true);
$theDate = cwGetDateFormat($_POST["EndDate"]);
$month = strftime("%m",$theDate);
$year = strftime("%Y",$theDate);
$day = strftime("%d",$theDate);
$_POST["EndDate"] = cwDateFormat("$year/$month/$day", true);
}

if(!isset($_POST["Status"])) {$_POST["Status"] = '0';}

if($_GET["searchBy"] != 0) {
$_POST["Status"] = $_GET["searchBy"];
}

$query_rsCWRevenue = sprintf("SELECT DISTINCT O.order_ID,
O.order_Date,
O.order_Status,
O.order_Tax,
O.order_Shipping,
O.order_ShippingTax,
O.order_Total,
O.order_State,
OS.orderSKU_OrderID,
OS.orderSKU_SKUTotal,
OS.orderSKU_TaxRate,
OS.orderSKU_DiscountAmount
FROM tbl_orders O
INNER JOIN tbl_orderskus OS
ON O.order_ID = OS.orderSKU_OrderID
WHERE (O.order_Status = 2 || O.order_Status = 3 || O.order_Status = 9) AND (O.order_Date >= '%s'
AND O.order_Date <= '%s')
%s
GROUP BY O.order_ID
ORDER BY order_Date DESC"
,mySQLDate($_POST["StartDate"])
,mySQLDate($_POST["EndDate"])
,($_POST["Status"] != 0) ? " AND order_Status = " .$_POST["Status"] : '');
$rsCWRevenue = $cartweaver->db->executeQuery($query_rsCWRevenue, "rsCWRevenue");
$rsCWRevenue_recordCount = $cartweaver->db->recordCount;
$row_rsCWRevenue = $cartweaver->db->db_fetch_assoc($rsCWRevenue);

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Admin: Report Revenue</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="assets/admin.css" rel="stylesheet" type="text/css">
<script type="text/javascript" language="JavaScript">

</script>
</head>
<body>

<?php include("CWIncNav.php");?>
<div id="divMainContent">

<form name="DateForm" method="post" action="<?php echo($cartweaver->thisPage);?>">
      From
        <input name="StartDate" type="text" required="yes" message="Must be a date - mm/dd/yyyy format" validate="date" value="<?php echo($_POST["StartDate"]);?>" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'StartDate')"><img src="assets/images/calendar.gif" alt="Click to Select Date" width="16" height="16"></a> To
        <input name="EndDate" type="text" required="yes" validate="date" message="Must be a date - mm/dd/yyyy format" value="<?php echo($_POST["EndDate"]);?>" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'EndDate')"><img src="assets/images/calendar.gif" width="16" height="16" style="margin-bottom:0px;" alt="Click to Select Date"></a>


      <input name="Submit" type="submit" class="formButton" value="Get Report">
</form>
<br />

<h1>Report: Revenue

</h1>
<?php /* Results Output table */
if($rsCWRevenue_recordCount !=0) { ?>
    <table>
      <tr>
        <th width="165">Order ID</th>
        <th width="170">Order Date</th>
  <th width="95">Sku Revenue</th>
  <th width="95">Sku Tax Revenue</th>
        <th width="95">Shipping Revenue</th>
  <th width="95">Shipping Tax Revenue</th>
  <th width="95">Discount Credit</th>
  <th width="150">Total Revenue</th>
      </tr>
      <?php /* loop through to show all records found */
      if($rsCWRevenue_recordCount == 0) {
        echo("<p>No results found.</p>");
      }
    $recCounter = 0;
$SKU = "";
$TLSku = "";
$Tax = "";
$TLTax = "";
$Ship = "";
$TLShip = "";
$ShipTax = "";
$TLShipTax = "";
$Credit = "";
$TLCredit = "";
$GDTL = "";
$TLGDTL = "";

do {
  ?>
        <tr style="text-align:center" class="<?php cwAltRow($recCounter++);?>">
          <td><?php echo($row_rsCWRevenue["order_ID"]);?></td>
          <td><?php echo(cwDateFormat($row_rsCWRevenue["order_Date"],false));?></td>
    <td><?php echo $SKU = ($row_rsCWRevenue["order_Total"]- ($row_rsCWRevenue["order_Tax"] + $row_rsCWRevenue["order_Shipping"] + $row_rsCWRevenue["order_ShippingTax"]));?></td>
    <td><?php echo $Tax = ($row_rsCWRevenue["order_Tax"])?></td>
    <td><?php echo $Ship = ($row_rsCWRevenue["order_Shipping"])?></td>
          <td><?php echo $ShipTax = ($row_rsCWRevenue["order_ShippingTax"]);?></td>
    <td><?php echo $Credit = ($row_rsCWRevenue["orderSKU_DiscountAmount"]);?></td>
    <td><?php echo $GDTL = ($row_rsCWRevenue["order_Total"]);?></td>
        </tr>

      <?php
   $TLSku = $SKU + $TLSku;
   $TLTax = $TLTax + $Tax;
   $TLShip = $Ship + $TLShip;
   $TLShipTax = $ShipTax + $TLShipTax;
   $TLCredit = $Credit + $TLCredit;
   $TLGDTL = $GDTL + $TLGDTL;

    } while ($row_rsCWRevenue = $cartweaver->db->db_fetch_assoc($rsCWRevenue))?>
   <tr>
   <td colspan="2">Total</td>
  <td align="center"><?php echo $TLSku;?></td>
  <td align="center"><?php echo $TLTax;?></td>
  <td align="center"><?php echo $TLShip;?></td>
  <td align="center"><?php echo $TLShipTax;?></td>
  <td align="center"><?php echo $TLCredit;?></td>
  <td align="center"><?php echo $TLGDTL;?></td>
   </tr>
    </table>
    <?php
} else {
echo("<p><strong>Sorry, no data to report. ");
/* If the user has tried a form search, and there are no results, prompt them to choose different dates */
if (isset ($_POST["StartDate"])) {
  echo("Try different dates.");
}
echo("</strong></p>");
}?>
</div>
</body>
</html>
<?php
cwDebugger($cartweaver);
?>

Hope this starts your New Year off on the right foot.

cpsinclair

Re: Sales reports exporting

4 months ago

This is GREAT! Works like a charm. Thanks for sharing.

ontsdtx

Re: Sales reports exporting

4 months ago

I was in retail a long time and I used lots of reports to ensure that the customers would always find what they were looking for and to ensure that I found what I was lossing for inventory. I know that it is important to know what the sales are but are there any other reports or details that would be needed for an online store. I have a lot of fun trying to figure things out. So if you can think of a report you may need let me know and I will try to figure it out. Sorry I do not know CF only PHP.

stevenwol..

Re: Sales reports exporting

3 months ago

<cfparam name="enddate" default="#dateformat(now(),"yyyy-mm-dd")#" />
<cfparam name="startdate" default="2012-01-01" />
<cfscript>
theDate = startdate;
month = month(theDate);
year = year(theDate);
day = day(theDate);

</cfscript>
<cfquery name="rsCWRevenue" datasource="#request.dsn#">

SELECT DISTINCT O.order_ID,
O.order_Date,
O.order_Status,
O.order_Tax,
O.order_Shipping,
O.order_ShippingTax,
O.order_Total,
O.order_State,
OS.orderSKU_OrderID,
OS.orderSKU_SKUTotal,
OS.orderSKU_TaxRate,
OS.orderSKU_DiscountAmount
FROM tbl_orders O
INNER JOIN tbl_orderskus OS
ON O.order_ID = OS.orderSKU_OrderID
WHERE (O.order_Status = 2 || O.order_Status = 3 || O.order_Status = 9) AND (O.order_Date >= '#startdate#'
AND O.order_Date <= '#enddate#')

GROUP BY O.order_ID
ORDER BY order_Date DESC
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Admin: Report Revenue</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="assets/admin.css" rel="stylesheet" type="text/css">
<script type="text/javascript" language="JavaScript">

</script>
</head>
<body>

<div id="divMainContent">
<cfoutput>
<form name="DateForm" method="post" action="">
      From
        <input name="StartDate" type="text" required="yes" message="Must be a date - mm/dd/yyyy format" validate="date" value="#StartDate#" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'StartDate')"><img src="assets/images/calendar.gif" alt="Click to Select Date" width="16" height="16"></a> To
        <input name="EndDate" type="text" required="yes" validate="date" message="Must be a date - mm/dd/yyyy format" value="#EndDate#" size="10" passthrough="onFocus=""cw_cleardefault(this);""">
      <a href="javascript:ShowCalendar('DateForm', 'EndDate')"><img src="assets/images/calendar.gif" width="16" height="16" style="margin-bottom:0px;" alt="Click to Select Date"></a>


      <input name="Submit" type="submit" class="formButton" value="Get Report">
</form></cfoutput>
<br />

<h1>Report: Revenue

</h1>

<cfif rsCWRevenue.recordcount neq 0>
    <table>
      <tr>
        <th width="165">Order ID</th>
        <th width="170">Order Date</th>
<th width="95">Sku Revenue</th>
<th width="95">Sku Tax Revenue</th>
        <th width="95">Shipping Revenue</th>
<th width="95">Shipping Tax Revenue</th>
<th width="95">Discount Credit</th>
<th width="150">Total Revenue</th>
      </tr>

<cfscript> recCounter = 0;
SKU = 0;
TLSku = 0;
Tax = 0;
TLTax = 0;
Ship = 0;
TLShip = 0;
ShipTax = 0;
TLShipTax = 0;
Credit = 0;
TLCredit = 0;
GDTL = 0;
TLGDTL = 0;
</cfscript>

<cfoutput query="rsCWRevenue">
<cfscript>
SKU = evaluate(order_Total- order_Tax + order_Shipping + order_ShippingTax);
TAX = order_Tax;
SHIP = order_shipping;
SHIPTAX = order_shippingTax;
CREDIT = orderSKU_DiscountAmount;
GDTL = order_Total;
</cfscript>
        <tr style="text-align:center" class="">
          <td>#order_ID#</td>
          <td>#dateformat(order_date,"mm/dd/yyyy")#</td>
    <td>#SKU#</td>
    <td>#TAX#</td>
    <td>#SHIP#</td>
          <td>#SHIPTAX#</td>
    <td>#CREDIT#</td>
    <td>#GDTL#</td>
        </tr>
    <cfscript>

    TLSku = SKU + TLSku;
   TLTax = TLTax + Tax;
   TLShip = Ship + TLShip;
   TLShipTax = ShipTax + TLShipTax;
   TLCredit = Credit + TLCredit;
   TLGDTL = GDTL + TLGDTL;
    </cfscript>

</cfoutput>
<cfoutput> <tr>
    <td colspan="2">Total</td>
<td align="center">#TLSku#</td>
<td align="center">#TLTax#</td>
<td align="center">#TLShip#</td>
<td align="center">#TLShipTax#</td>
<td align="center">#TLCredit#</td>
<td align="center">#TLGDTL#</td>
   </tr>
    </table></cfoutput>

<cfelse><p><strong>Sorry, no data to report.</strong></p>

</cfif>
</div>
</body>
</html>