Help with data export

June 2, 2011

Hi Everyone,

I am a newbie to oscommerce. I need to export sales data to excel or csv. I have found a file in the add ons section and would like to get some thoughts from experienced users whether this file will work. I need the following fields exported.

Customer name

Customer Email

Date of order

Description of product

Order Ref

Product Category

Below is the script from what I have downloaded. Can any of you kind people let me know if this will work and if there is anything I need to change in the script.

<?php

/*

===========================================

Created by Dave Ferrise

For use with Oscommerce

Use with permission only

===========================================

9/22/05 - added international currency

support by using $currencies->format()

class instead or hard-coded "$"

===========================================

*/

require('includes/application_top.php');

if(isset($_COOKIE['ToDate']) && !empty($_COOKIE['ToDate']))

{

if($_POST['ToDate']!='')

{

setcookie("ToDate",$_POST['ToDate'],time()+3600*24*365);

}

else

{

setcookie("ToDate",date('Y-m-d'),time()+3600*24*365);

}

require(DIR_WS_CLASSES . 'currencies.php');

$currencies = new currencies();

$sales_products_query = tep_db_query("select sum(op.final_price*op.products_quantity) as daily_prod, sum(op.final_price*op.products_quantity*(1+op.products_tax/100)) as withtax, o.date_purchased, op.products_name, sum(op.products_quantity) as qty, op.products_model from orders as o, orders_products as op where o.orders_id = op.orders_id GROUP by year(o.date_purchased), month(o.date_purchased) ORDER BY year(o.date_purchased) DESC, month(o.date_purchased) DESC");

$orders_statuses = array();

$orders_status_array = array();

$orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . (int)$languages_id . "'");

$orders_statuses[] = array('id' => "",

'text' =>"--Select--");

while ($orders_status = tep_db_fetch_array($orders_status_query)) {

$orders_statuses[] = array('id' => $orders_status['orders_status_id'],

'text' => $orders_status['orders_status_name']);

}

function download_file($file)

{

if (!is_file($file))

{

die("<b>404 File not found!</b>");

}

//Gather relevent info about file

$len = filesize($file);

$filename = basename($file);

$file_extension = strtolower(substr(strrchr($filename,"."),1));

//This will set the Content-Type to the appropriate setting for the file

switch( $file_extension )

{

case "pdf": $ctype="application/pdf"; break;

case "exe": $ctype="application/octet-stream"; break;

case "zip": $ctype="application/zip"; break;

case "doc": $ctype="application/msword"; break;

case "xls": $ctype="application/vnd.ms-excel"; break;

case "ppt": $ctype="application/vnd.ms-powerpoint"; break;

case "gif": $ctype="image/gif"; break;

case "png": $ctype="image/png"; break;

case "jpeg":

case "jpg": $ctype="image/jpg"; break;

case "mp3": $ctype="audio/mpeg"; break;

case "wav": $ctype="audio/x-wav"; break;

case "mpeg":

case "mpg":

case "mpe": $ctype="video/mpeg"; break;

case "mov": $ctype="video/quicktime"; break;

case "avi": $ctype="video/x-msvideo"; break;

//The following are for extensions that shouldn't be downloaded (sensitive stuff, like php files)

case "php":

case "htm":

case "html":

case "txt":die("<b>Cannot be used for ". $file_extension ." files!</b>"); break;

default: $ctype="application/force-download";

}

ob_clean();

//Begin writing headers

header("Pragma: public");

header("Expires: 0");

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Cache-Control: public");

header("Content-Description: File Transfer");

//Use the switch-generated Content-Type

header("Content-Type: $ctype");

//Force the download

$header="Content-Disposition: attachment; filename=".$filename.";";

header($header );

header("Content-Transfer-Encoding: binary");

header("Content-Length: ".$len);

@readfile($file);

exit;

}

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<head>

</head>

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top">

<table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

<?php require(DIR_WS_INCLUDES . 'column_left.php');

?>

</table>

</td>

<tr>

<td>

<form action="<?php echo $PHP_SELF; ?>" method="post" name="reportit">

<tr>

</tr>

<tr>

</select>

</td>

</tr>

</table>

</form>

<br>

var dateAvailable = new ctlSpiffyCalendarBox("dateAvailable", "SalesReport", "FromDate","btnDate","<?=(isset($_COOKIE['ToDate']) && $_COOKIE['ToDate'] !="")?$_COOKIE['ToDate']:""?>",scBTNMODE_CUSTOMBLUE);

//var dateAvailable2 = new ctlSpiffyCalendarBox("dateAvailable2", "SalesReport", "ToDate","btnDate2","<?=(isset($_POST['ToDate']) && $_POST['ToDate'] !="")?$_POST['ToDate']:""?>",scBTNMODE_CUSTOMBLUE);

var dateAvailable2 = new ctlSpiffyCalendarBox("dateAvailable2", "SalesReport", "ToDate","btnDate2","<?=(isset($_POST['ToDate']) && $_POST['ToDate'] !="")?$_POST['ToDate']:date('Y-m-d')?>",scBTNMODE_CUSTOMBLUE);

//-->

</script>

<?php echo tep_draw_form('SalesReport', basename($PHP_SELF)); ?>

<tr>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' '; ?><script language="javascript">dateAvailable.writeControl(); dateAvailable.dateFormat="yyyy-MM-dd";</script></td>

<td class="main"><?php echo tep_draw_separator('pixel_trans.gif', '24', '15') . ' '; ?><script language="javascript">dateAvailable2.writeControl(); dateAvailable2.dateFormat="yyyy-MM-dd";</script></td>

<td class="main"> Payment Method

<option value="PayPal" <?=(isset($_POST['PaymentMethod']) && $_POST['PaymentMethod'] =="PayPal")?"selected":""?>>PayPal</option>

</select>

</td>

<td class="main">Status</b> <?php echo tep_draw_pull_down_menu('status', $orders_statuses, (isset($_POST['status']) && $_POST['status'] !="")?$_POST['status']:""); ?>

</td>

</tr>

</table>

</form>

<br>

</td>

</tr>

<?php

$orders_query_raw = "select o.*,o.customers_email_address,o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified,

o.currency, o.currency_value, s.orders_status_name, ot.text as order_total

from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s

where ";

$orders_query_raw .="o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total'";

$Message = "";

if(isset($_POST['status']) && $_POST['status'] !="")

{

$orders_query_raw .=" and s.orders_status_id = '" . (int)$_POST['status'] . "'";

}

if(isset($_POST['PaymentMethod']) && $_POST['PaymentMethod'] !="")

{

$orders_query_raw .=" and o.payment_method = '" . $_POST['PaymentMethod'] . "'";

}

if((isset($_POST['FromDate']) && $_POST['FromDate'] !="") && (isset($_POST['ToDate']) && $_POST['ToDate'] !=""))

{

$orders_query_raw .=" and o.date_purchased BETWEEN CAST('".$_POST['FromDate']."' AS DateTime) AND CAST('".$_POST['ToDate']."' AS DateTime)";

}

elseif ((isset($_POST['FromDate']) && $_POST['FromDate'] !=""))

{

$orders_query_raw .=" and o.date_purchased >= CAST('".$_POST['FromDate']."' AS DateTime)";

}

elseif ((isset($_POST['ToDate']) && $_POST['ToDate'] !=""))

{

$orders_query_raw .=" and o.date_purchased <= CAST('".$_POST['ToDate']."' AS DateTime)";

}

$orders_query_raw .=" order by o.orders_id DESC";

$orders_query = tep_db_query($orders_query_raw);

if(tep_db_num_rows($orders_query) && isset($_POST['SalesSubmit']))

{

$filename="../br/".date("Md-Y-his").".xls";

$fp = fopen($filename, "w");

fputs($fp,$Message."\n");

//download_file($filename);

?>

<tr>

<td class="dataTableHeadingContent">Customers</td>

<td class="dataTableHeadingContent" align="right">Order Total</td>

<td class="dataTableHeadingContent" align="center">Date purchased</td>

<td class="dataTableHeadingContent" align="right">Status</td>

<td class="dataTableHeadingContent" align="right">Action </td>

</tr>

<?

fputs($fp,"Date\t");

fputs($fp,"Time\t");

fputs($fp,"Order ID\t");

fputs($fp,"Customers\t");

fputs($fp,"Type\t");

fputs($fp,"Item(qty)\t");

fputs($fp,"Currency\t");

fputs($fp,"Gross\t");

fputs($fp,"Shipping\t");

fputs($fp,"Gift\t");

fputs($fp,"Order Total\t");

fputs($fp,"From Email Address\t");

fputs($fp,"Ship To\t");

fputs($fp,"Status\t");

fputs($fp,"\n");

while ($orders = tep_db_fetch_array($orders_query))

{

$shipping="";

$gift="";

$gross="";

$item="";

$shipto="";

$sqlsub = "select text,class from orders_total where orders_id=".$orders['orders_id']." and class='ot_subtotal'";

$sql_subtotal=tep_db_query($sqlsub);

$gross1=tep_db_fetch_array($sql_subtotal);

$gross=$gross1['text'];

$sqlsub = "select text,class from orders_total where orders_id=".$orders['orders_id']." and class='ot_shipping'";

$sql_subtotal=tep_db_query($sqlsub);

$shipping1=tep_db_fetch_array($sql_subtotal);

$shipping=$shipping1['text'];

$sqlsub = "select text,class from orders_total where orders_id=".$orders['orders_id']." and class='ot_giftwrap'";

$sql_subtotal=tep_db_query($sqlsub);

$gift1=tep_db_fetch_array($sql_subtotal);

$gift=$gift1['text'];

$sql_item=tep_db_query("select * from orders_products where orders_id=".$orders['orders_id']);

while ($orders_item = tep_db_fetch_array($sql_item))

{

$item.=$orders_item['products_name'].' ('.$orders_item['products_quantity'].')';

$item.=', ';

}

?>

<tr>

<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&action=edit') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif', ICON_PREVIEW) . '</a> ' . $orders['customers_name']; ?></td>

<td class="dataTableContent" align="right"><?php if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>

</tr>

<?

if($orders['delivery_name']!='')

{

$shipto.=$orders['delivery_name'];

}

if($orders['delivery_company']!='')

{

$shipto.=', '.$orders['delivery_company'];

}

if($orders['delivery_street_address']!='')

{

$shipto.=', '.$orders['delivery_street_address'];

}

if($orders['delivery_city']!='')

{

$shipto.=', '.$orders['delivery_city'];

}

if($orders['delivery_state']!='')

{

$shipto.=', '.$orders['delivery_state'];

}

if($orders['delivery_country']!='')

{

$shipto.=', '.$orders['delivery_country'];

}

fputs($fp,strip_tags(tep_date_short((substr($orders['date_purchased'],0,10))))."\t");

fputs($fp,strip_tags(substr($orders['date_purchased'],10))."\t");

fputs($fp,strip_tags($orders['orders_id'])."\t");

fputs($fp,strip_tags($orders['customers_name'])."\t");

fputs($fp,strip_tags($orders['payment_method'])."\t");

fputs($fp,strip_tags($item)."\t");

fputs($fp, $orders['currency']."\t");

fputs($fp,strip_tags($gross)."\t");

fputs($fp,strip_tags($shipping)."\t");

fputs($fp,strip_tags($gift)."\t");

fputs($fp,strip_tags($orders['order_total'])."\t");

fputs($fp,strip_tags($orders['customers_email_address'])."\t");

fputs($fp,strip_tags($shipto)."\t");

fputs($fp, $orders['orders_status_name']."\t");

fputs($fp,"\n");

}

?>

</table>

</td>

</tr>

<tr>

<td>

<a href="<?=$filename?>" target="_blank">To download this data in Excel sheet click here.</a></td></tr>

<?

fclose($fp);

}

/////////end reports

if ($multiplex !='') {

if (tep_db_num_rows($sales_products_query) > 0) {

?>

<tr>

<table>

<?php

// open file

$fp = fopen(DL_LOC,"w");

fputs($fp, CSV_MONTH . "," . CSV_SALES . "," . CSV_WITH_TAX . "\r");

while ($sales_products = tep_db_fetch_array($sales_products_query))

{

if (date("M-Y") == date("M-Y",strtotime($sales_products['date_purchased']))) {

echo "<tr><td colspan='3'><hr></td></tr>";

echo "<tr><td class=mainred width='70'>";

echo TEXT_PROJECTED.": </td>\n";

$this_day = date("j");

$tot_days = date("t");

$m_amt = round($sales_products ['daily_prod'],0);

$proj = round(($m_amt/$this_day)*$tot_days,0);

$pbar = $proj/$multiplex;

echo "<td class=mainred width='70' align='right'> ". $currencies->format($proj) . " </td>\n";

echo "<td class=main><img src='images/bar_red.jpg' height='10' width='".$pbar."' border='1'></td>\n</tr>\n";

echo "<tr><td colspan='3'><hr></td></tr>";

}

fputs($fp, date("M-Y",strtotime($sales_products['date_purchased'])) . "," .round($sales_products ['daily_prod'],2) . "," . round($sales_products ['withtax'],2)."\r");

echo "<tr><td class=main width='70'>";

echo date("M-Y",strtotime($sales_products['date_purchased']))."</td>\n";

$m_amt = round($sales_products ['daily_prod'],0);

$bar = $m_amt/$multiplex;

echo "<td class=main width='70' align='right'> ". $currencies->format($m_amt)." </td>\n";

echo "<td class=main><img src='images/bar.jpg' height='10' width='".$bar."' border='1'></td>\n</tr>\n";

}

?>

</table>

</td>

</tr>

<tr>

<?php echo "<a href='" . tep_href_link(DL_LOC, '', 'NONSSL'). "'>" . TEXT_DOWNLOAD . "</a></td>";?>

</tr>

<?php

} else {

?>

<tr>

</tr>

<?php

}

?>

</table>

</td>

</tr>

</table>

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Help with data export

Recommended Posts

Guest

Link to comment

Share on other sites

Archived

Browse

Activity