PHP/Query For Export To CSV

**[email protected]** · 8 Mar 2013, 09:48 PM

for those that would like to see the full php file, please see below heavily modified from the sage intergration plugin, just need this last bit to work and its complete....

<?php
/**
* sage.php
*
* @package sage
* @copyright Portions Copyright 2003-2006 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @copyright Portions Copyright 2003 Andrew Berezin
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: sage.php, v 1.0 15.8.2007 5:54 James Fitch [email protected]$
*/
/*

TODO List
add in the function to export all stock from system to this program aswell??
*/
require('includes/application_top.php');

@define('SAGE_DIRECTORY', 'sage/');
@define('SAGE_OUTPUT_BUFFER_MAXSIZE', 1024*1024);
@define('SAGE_USE_CPATH', 'false');

define('NL', " \n");

$count=0;

require(zen_get_file_directory(DIR_WS_LANGUAGES . $_SESSION['language'] .'/', 'sage.php', 'false'));

echo TEXT_SAGE_STARTED . NL;
echo TEXT_SAGE_FILE_LOCATION . DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2 . NL;
echo TEXT_SAGE_FILE_LOCATION . DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME . NL;

if (isset($_GET['feed']) && $_GET['feed'] == "yes") {
if (is_dir(DIR_FS_CATALOG . SAGE_DIRECTORY)) {
if (!is_writeable(DIR_FS_CATALOG . SAGE_DIRECTORY)) {
echo ERROR_SAGE_DIRECTORY_NOT_WRITEABLE . NL;
die;
}
} else {
echo ERROR_SAGE_DIRECTORY_DOES_NOT_EXIST . NL;
die;
}

$stimer_feed = microtime_float();
@set_time_limit(0);

$output_buffer = "";
$output_buffer2 = "";

if(!zen_sages_fwrite('', 'open')) {
echo ERROR_SAGE_OPEN_FILE . NL;
die;
}

if(!zen_sages_fwrite2('', 'open')) {
echo ERROR_SAGE_OPEN_FILE . NL;
die;
}

$sage_update_query = "SELECT *
FROM " .SAGE_UPDATE. "";
//$ordereds = $db->Execute($sage_update_query);

$datestart = SAGE_STARTSDATE;

$dateend = SAGE_ENDSDATE;

if ($datestart == "null"){

$datestart = $ordereds ->fields['last_update'];
}else{$datestart=$datestart." 00:00:01";
}

if ($dateend == "null"){

$yesterday = mktime(0,0,0,date("m"),date("d")-1,date("Y"));
$dateend = date("Y-m-d", $yesterday);
}

$datestart='\''.$datestart.'\'';

$dateend=$dateend." 23:59:59";
$dateend='\''.$dateend.'\'';

$statustosages =SAGE_STATUS;

$orders_query = "SELECT *
FROM " . TABLE_ORDERS . " WHERE orders_status <= $statustosages AND date_purchased >= $datestart AND date_purchased <= $dateend
ORDER BY date_purchased ASC";

// $refund_query = "SELECT * FROM " . TABLE_SO_REFUNDS . "

//ORDER BY orders_id ASC";

// $refund = $db->Execute($refund_query);

$stimer = microtime_float();

$orders = $db->Execute($orders_query);

$timer['$orders->mySQL'] += microtime_float()-$stimer;

while (!$orders->EOF) { //1

$orderid=$orders->fields['orders_id'];

##################################################################### below checks for any postage charges - these will be added across number of products
$ship=200;
$ship=3;

$post_query="SELECT *
FROM " . TABLE_ORDERS_TOTAL .

" WHERE

orders_id = $orderid and

class LIKE '%ot_shipping%' "

;

$Postagecosts = $db->Execute($post_query);

$postageprice = $Postagecosts->fields['value'];

###################################################################### below checks to see if total = total of full price...

$count=0; //used for other things further down
$proid_query="SELECT *
FROM " . TABLE_ORDERS_PRODUCTS . " as t1 WHERE t1.orders_id = $orderid";

$Orderedproduct = $db->Execute($proid_query);
// get all products from that order

while (!$Orderedproduct->EOF) { //5

$prodprice=$Orderedproduct->fields['final_price'];

if ($Orderedproduct->fields[products_tax] > 0) {

$prodprice=($Orderedproduct->fields['final_price'] * ($Orderedproduct->fields[products_tax]/100)) +$Orderedproduct->fields['final_price'];

}
$prodprice = ($Orderedproduct->fields['products_quantity'])*$prodprice;
$totalprice=$prodprice+$totalprice;
$count=$count+1; // count how many products in order, handy to use as reference for other calcs....
$Orderedproduct->MoveNext();

} //5

################################################################################ ####
//get total price from file and compare that all items add up to this. Doing it this way ensures no issues with tax.

$discount=1.0;

If ($totalprice > $orders->fields['order_total']) {

$change=($totalprice -$orders->fields['order_total'] ) /$totalprice;

$discount=$discount-$change;
}

$totalprice=0;

#######################################################//end of price adjustment

$products_cost =
"select p.products_id, pd.products_name, p.products_quantity, ".
"p.products_cost, (p.products_quantity*p.products_cost) AS products_total_cost, ".
"p.products_type ".
"from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd ".
"where p.products_id = pd.products_id and pd.language_id='" . $_SESSION['languages_id'] . "' ".
"order by (p.products_cost) DESC, p.products_cost desc, pd.products_name";

$products_stock = $db->Execute($products_cost);

//Start Of Output for Stock File

while(!$products_sock->EOF) {
$output = array();
$output["tx_type"]='ST';
$output["Stock_Code"]= $products_stock->fields['products_id'];
$output["Qty"]= $products_stock->fields['products_quantity'];
$output["Description"]= $products_stock->fields['products_name'];
$output["CostPriceEx"]= $products_stock->fields['products_cost'];
$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);
$products_stock->MoveNext();
}
$stimer = microtime_float();

##################################################### switch statement to take account of exports. i.e if not UK/ireland then add VAT using T1 russ

$taxid=$orders->fields['delivery_country'];

switch ($taxid) {

case "United Kingdom":
$taxcode = "T1";
$netprice= $totprice;
$taxam=($orders->fields['order_total']/1.200) ;

break;

case "Ireland":
$taxcode = "T1";
$netprice= $totprice;
$taxam=($orders->fields['order_total']/1.200) ;
break;

default:
$taxcode = "T0";
$netprice= $totprice;
$taxam=($orders->fields['order_total']);
}

############################################### identify payment type

$paytype=$orders->fields['payment_method'];

switch ($paytype) {
case "Paypal IPN":
$purchacc = SAGE_PAYPAL;
break;
case "Paypal":
$purchacc = SAGE_EBAY;
break;
case "Credit Card":
$purchacc = 1233;
break;
case "Credit Card / Paypal":
$purchacc = SAGE_EBAY;
break;
case "Credit Card/ Paypal":
$purchacc = SAGE_EBAY;
break;
case "Cheque/Money Order":
$purchacc = SAGE_CHEQ;
break;
case "Ebay Paypal":
$purchacc = 1230;
break;
case "Credit/Debit Card (Secured by Sage Pay)":
$purchacc = 1232;
break;
case "Amazon":
$purchacc = 1231;
break;
case "Amazon FBA":
$purchacc = 1235;
break;
case "Cash":
$purchacc = 1260;
break;
case "Cheque":
$purchacc = 1200;
break;
case "Group Payment - Crownlea":
$purchacc = 2500;
break;
case "Group Payment - BNM UK":
$purchacc = 2501;
break;
case "Group Payment - BNM LTD":
$purchacc = 2502;
break;
case "Group Payment - BNM Ireland":
$purchacc = 2503;
break;
case "Group Payment - Protecta Screen":
$purchacc = 2504;
break;
case "Group Payment - OSSSC":
$purchacc = 2506;
break;
case "Group Payment - Package-IN":
$purchacc = 2507;
break;
case "Group Payment - TIC":
$purchacc = 2508;
break;
case "Group Payment - Billaman":
$purchacc = 2509;
break;

default:
$purchacc = 0000;

}

$dateprod=$orders->fields['date_purchased'];

$arr=split(" ",$dateprod); // splitting the array
$dateprod=$arr[0]; // first element of the array is month
$arr=split("-",$dateprod); // splitting the array
$yyyy=$arr[0];
$mm=$arr[1];
$dd=$arr[2];

$output2 = array();
$output2["tx_type"]=SAGE_TX2;
$output2["Purchase Acc"]=$purchacc;
$output2["nominal_code"]='4000';
$output2["dept_num"]='1';
$output2["Transaction_Date"] = $dd.'/'.$mm.'/'.$yyyy;
$output2["Reference"] = $orders->fields['orders_id'];
$output2["Details"]='Paid By -'.$orders->fields['payment_method'].' ** Customer- '.$orders->fields['customers_name'];
$output2["Tax Code"]=$taxcode;
$output2["TaxAmmount"]=number_format ($taxam, 2, '.', '');
$output2["postageprice"] = $Postagecosts->fields['value'];

$output2["NetAmmount"]= $orders->fields['order_total'] - number_format ($taxam, 2, '.', '');
$output2["order_total"] = $orders->fields['order_total'];

zen_sages_fwrite2($output2);

$totprice=0;

$orders->MoveNext();

$timer['$orders->mySQL'] += microtime_float()-$stimer;

} //1
zen_sages_fwrite('', 'close');
zen_sages_fwrite2('', 'close');
$date_updated = date('Y-m-d');
$date_updated=$date_updated." 00:00:01"; //Current date in US format $yyyy.'/'.$mm.'/'.$dd;

//$sage_updated_query = "UPDATE
//" . sage_update . " SET last_update = '$date_updated'";
//$ordered = $db->Execute($sage_updated_query);

// @chmod(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME, 0644);

$timer_feed = microtime_float()-$stimer_feed;
$percent_other = 0;
$time_other = $timer_feed;
if(SAGE_STAT === true) {
echo 'Execution time: ';
echo '<table cellspace="2" border="1px">';
echo '<tr><td>Function</td><td>Execution time</td><td>Percent</td></tr>';
foreach($timer as $func => $time) {
$percent_total += ($time*100)/$timer_feed;
$time_other -= $time;
echo '<tr><td>' . $func . '</td><td align="right">' . number_format($time, 6) . '</td><td align="right">' . round(($time*100)/$timer_feed, 2) . '%</td></tr>';
}
echo '<tr><td>Other</td><td align="right">' . number_format($time_other, 6) . '</td><td align="right">' . round(($time_other*100)/$timer_feed, 2) . '%</td></tr>';
//echo '<tr><td>Total</td><td align="right">' . number_format($timer_feed, 6) . '</td><td align="right">' . round(($timer_feed*100)/$timer_feed, 2) . '%</td></tr>';
echo '</table>';
}
//$orders->RecordCount()
echo ' ' . TEXT_SAGE_FEED_COMPLETE . ' ' . SAGE_TIME_TAKEN . ' ' . sprintf("%f " . TEXT_SAGE_FEED_SECONDS, number_format($timer_feed, 6) ) . ' ' . $count . TEXT_SAGE_FEED_RECORDS . NL . ' ';
echo $datestart.' to '.$dateend.NL;

echo ' ' . 'To View SAGE 1000 Transaction File <a title="Click Here" href="' . DIR_WS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2 . '" target="_new"> Click Here</a> ';

echo' '. 'To View SAGE 1000 Stock File <a title="Click Here" href="' . DIR_WS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME . '" target="_new"> Click Here</a> ';

//echo zen_image(DIR_WS_IMAGES . 'sage1000.jpg', '', 300, 300, ' ');

echo zen_image(DIR_WS_IMAGES . 'sagelogo.jpg', 300, 300);

}

function zen_sages_fwrite($output, $op='fwrite') {
global $timer;$stimer = microtime_float();
static $fp;
static $output_buffer = "";
static $title_row = false;
switch ($op) {
case 'open':
$retval = $fp = fopen(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME, "wb");
break;
case 'fwrite':
if(!$title_row) {
$title_row = $output;
}
$buf = array();
foreach($title_row as $key=>$val) {
$buf[] = (isset($output[$key]) ? $output[$key] : '');
// var_dump($key, $output[$key]);echo ' ';
}
$output = implode(",", $buf);
if(strlen($output_buffer) > SAGE_OUTPUT_BUFFER_MAXSIZE) {
$retval = fwrite($fp, $output_buffer, strlen($output_buffer));
$output_buffer = "";
}
$output = rtrim($output) . "\n";
if(strtolower(CHARSET) != 'utf-8')
$output_buffer .= utf8_encode($output);
else
$output_buffer .= $output;
break;
case 'close':
if(strlen($output_buffer) > 0) {
$retval = fwrite($fp, $output_buffer, strlen($output_buffer));
$output_buffer = "";
}
fclose($fp);
break;
}
$timer[__FUNCTION__] += microtime_float()-$stimer;
return $retval;
}

function zen_sages_fwrite2($output2, $op='fwrite') {
static $fp2;
static $output_buffer2 = "";
static $title_row2 = false;
switch ($op) {
case 'open':
$retval = $fp2 = fopen(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2, "wb");
break;
case 'fwrite':
if(!$title_row2) {
$title_row2 = $output2;
}
$buf = array();
foreach($title_row2 as $key=>$val) {
$buf[] = (isset($output2[$key]) ? $output2[$key] : '');
// var_dump($key, $output2[$key]);echo ' ';
}
$output2 = implode(",", $buf);
if(strlen($output_buffer2) > SAGE_OUTPUT_BUFFER_MAXSIZE) {
$retval = fwrite($fp2, $output_buffer2, strlen($output_buffer2));
$output_buffer2 = "";
}
$output2 = rtrim($output2) . "\n";
if(strtolower(CHARSET) != 'utf-8')
$output_buffer2 .= utf8_encode($output2);
else
$output_buffer2 .= $output2;
break;

case 'close':
if(strlen($output_buffer2) > 0) {
$retval = fwrite($fp2, $output_buffer2, strlen($output_buffer2));
$output_buffer2 = "";
}
fclose($fp2);
break;
}
return $retval;
}

function zen_sages_sanita($str, $rt=false) {
global $timer;$stimer = microtime_float();
$str = strip_tags($str);
$str = str_replace(array("\t" , "\n", "\r"), ' ', $str);
$str = preg_replace('/\s\s+/', ' ', $str);
// $str = str_replace(array("®", "®", "©", "©", "™", "™"), ' ', $str);
$str = htmlentities(html_entity_decode($str));
$in = $out = array();
$in[] = "®"; $out[] = '(r)';
$in[] = "©"; $out[] = '(c)';
$in[] = "™"; $out[] = '(tm)';
// $str = str_replace($in, $out, $str);
if($rt) $str = str_replace(array('>'), ' ', $str);
$str = trim($str);
$str = "\"$str\"";

$timer[__FUNCTION__] += microtime_float()-$stimer;
return $str;
}

function ftp_get_error_from_ob() {
$out = ob_get_contents();
ob_end_clean();
$out = str_replace(array('\\', '', ' ', ' ', "\n", 'in '),array('/', '', '', '', '', ''),$out);
if(strpos($out, DIR_FS_CATALOG) !== false){
$out = substr($out, 0, strpos($out, DIR_FS_CATALOG));
}
return $out;
}

function microtime_float() {
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
?>

Thread: PHP/Query For Export To CSV

Thread Tools

Search Thread

Display

Threaded View

Re: PHP Query For Export To CSV

Similar Threads

Export in csv for ceneje.si

Export invoice totals to .csv file for Excel use

Export invoice totals to .csv for use in Excel-QUESTION

Export invoice totals to .csv for use in Excel

Posting Permissions