Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

Connect to multiple databases at the same time to list all orders and export? (Code included)

chrish123

By chrish123
in General Support

Recommended Posts

chrish123

chrish123

Posted
Posted

Hi, I took the "export orders to csv" contribution and modified it to work with quickbooks UK, the SPF sections are hardcoded so you would need to modify it yourself to make it work if you wanted to use this. I know its messy and theres unused code, but just simply place this file in your admin folder, and simply make a link in the admin/includes/boxes/reports.php

 

There is a problem though which you may see that maybe one in every 200 or so orders, an order total will be out by 0.1p , we just correct this within excel, as i havent figured out why this happens yet but its somewhere in the splitline loop, the math seems correct though.

 

Anyway to the point ;) I want to be able to connect to multiple shops/databases, using the same username/password at the same time to list all orders from all shops, is this possible? how could i do this as im tearing my hair out!

 

Ive been playing with methods described in comments here: http://uk3.php.net/mysql_select_db but they do not work for the above purpose!

 

Any help very, very much appreciated!

 

Chris

 

 

EDIT: add the below code to your admin/includes/functions/general.php

 

function greaterDate($start_date1,$end_date1)
{
 $start1 = strtotime($start_date1);
 $end1 = strtotime($end_date1);
 if ($start1-$end1 > 0)
return 1;
 else
  return 0;
}

 

 

Make an empty file called exportordersQB.php and paste the code below into it and save in your admin folder:

 

<?php
/*
 $Id: exportorders.php,v 1.1 April 21, 2006 Harris Ahmed $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2004 Oscommerce

*/


define('HEADING_TITLE', 'Export orders to a IIF file for direct import into Quickbooks');
define('INPUT_START', 'From Order No');
define('INPUT_END', 'To Order No');
define('INPUT_VALID', 'Export to Quickbooks IIF');
define('INPUT_DESC', 'Select the order numbers you want to export. Leave both fields empty if you want to export all orders or leave one of the fields empty to export all orders from X or all orders to X.');

require('includes/application_top.php'); 



// Check if the form is submitted
if (!$_GET['submitted'])
{
?>
<!-- header_eof //-->
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
	<!-- left_navigation //-->
	<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
	<!-- left_navigation_eof //-->
  </table></td>
<!-- body_text //-->
<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"><?php echo HEADING_TITLE; ?></td>
			<td class="pageHeading" align="right"></td>
		  </tr>
		</table></td>
	</tr>
	<!-- first ends // -->
	<tr>
	  <td><table border="0" style="font-family:tahoma;font-size:11px;" width="100%" cellspacing="2" cellpadding="2">
		  <tr>
			<td><form method="GET" action="<?php echo $PHP_SELF; ?>">
				<table border="0" style="font-family:tahoma;font-size:11px;" cellpadding="3">
				<?php echo '<center><b>This Export Module is designed for Quickbooks UK Software</center></b>'; ?>
				  <tr>
					<td><?php echo INPUT_START; ?></td>
					<td><!-- input name="start" size="5" value="<?php echo $start; ?>"> -->
					  <?php
						$orders_list_query = mysql_query("SELECT orders_id, date_purchased FROM orders ORDER BY orders_id DESC");
  							$orders_list_array = array();
						$orders_list_array[] = array('id' => '', 'text' => '---');
  							while ($orders_list = mysql_fetch_array($orders_list_query)) {
  							$orders_list_array[] = array('id' => $orders_list['orders_id'],
								   'text' => $orders_list['orders_id']." - ".tep_date_short($orders_list['date_purchased']));
						}  

						echo '  ' . tep_draw_pull_down_menu('start', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '   ';

					?></td>
				  </tr>
				  <tr>
					<td><?php echo INPUT_END; ?></td>
					<td><!-- <input name="end" size="5" value="<?php echo $end; ?>"> -->
					  <?php 
					echo '  ' . tep_draw_pull_down_menu('end', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '   ';
					?></td>
				  </tr>
				  <tr>
					<td> </td>
					<td><input type="submit" value="<?php echo INPUT_VALID; ?>"></td><?php echo tep_hide_session_id(); ?>
				  </tr>
				</table>
				<input type="hidden" name="submitted" value="1">
			  </form></td>
		  </tr>
		  <tr>
			<td><?php echo INPUT_DESC; ?></td>
		  </tr>
		  <tr>
			<td> </td>
		  </tr>
		  <tr>
			<td> </td>
		  </tr>
		</table></td>
	</tr>
  </table></td>
 </tr>
</table>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
<?php
}
// submitted so generate csv if the form is submitted
else
{
generatecsv($_GET['start'], $_GET['end']);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end)
{




// Patch dlan
// if both fields are empty we select all orders
if ($start=="" && $end=="") {
$orders = mysql_query("SELECT orders_id, date_purchased, customers_name, cc_owner, customers_company, customers_email_address, billing_street_address, billing_city, billing_state, billing_postcode, billing_country, customers_telephone, delivery_name, delivery_company, delivery_street_address, delivery_city, delivery_state, delivery_postcode, delivery_country, cc_type, cc_number, cc_expires 
FROM orders ORDER BY orders_id"); 
// if $start is empty we select all orders up to $end
} else if($start=="" && $end!="") {
$orders = mysql_query("SELECT orders_id, date_purchased, customers_name, cc_owner, customers_company, customers_email_address, billing_street_address, billing_city, billing_state, billing_postcode, billing_country, customers_telephone, delivery_name, delivery_company, delivery_street_address, delivery_city, delivery_state, delivery_postcode, delivery_country, cc_type, cc_number, cc_expires 
FROM orders WHERE orders_id <= $end ORDER BY orders_id"); 
// if $end is empty we select all orders from $start
} else if($start!="" && $end=="") {
$orders = mysql_query("SELECT orders_id, date_purchased, customers_name, cc_owner, customers_company, customers_email_address, billing_street_address, billing_city, billing_state, billing_postcode, billing_country, customers_telephone, delivery_name, delivery_company, delivery_street_address, delivery_city, delivery_state, delivery_postcode, delivery_country, cc_type, cc_number, cc_expires 
FROM orders WHERE orders_id >= $start ORDER BY orders_id");
// if both fields are filed in we select orders betwenn $start and $end
} else {
$orders = mysql_query("SELECT orders_id, date_purchased, customers_name, cc_owner, customers_company, customers_email_address, billing_street_address, billing_city, billing_state, billing_postcode, billing_country, customers_telephone, delivery_name, delivery_company, delivery_street_address, delivery_city, delivery_state, delivery_postcode, delivery_country, cc_type, cc_number, cc_expires 
FROM orders WHERE orders_id >= $start AND orders_id <= $end ORDER BY orders_id");
}
//patch

//$csv_output ="\n";
while ($row_orders = mysql_fetch_array($orders)) { //start one loop



$Orders_id = $row_orders["orders_id"];
//$oID = $row_orders["orders_id"];
$Date1 = $row_orders["date_purchased"];
//list($Date, $Time) = explode (' ',$Date1);
$Date = date('m/d/Y', strtotime($Date1));
$Time= date('H:i:s', strtotime($Date1));
$Name_On_Card1 = $row_orders["customers_name"]; 
$Name_On_Card = filter_text($Name_On_Card1);// order changed
list($First_Name,$Last_Name) = explode(', ',$Name_On_Card1); // order changed
$Company = filter_text($row_orders["customers_company"]);
$email = filter_text($row_orders["customers_email_address"]);
$Billing_Address_1 = filter_text($row_orders["billing_street_address"]);
$Billing_Address_2 = "";
$Billing_City = filter_text($row_orders["billing_city"]);
$Billing_State = filter_text($row_orders["billing_state"]);
$Billing_Zip = filter_text($row_orders["billing_postcode"]);
$Billing_Country = str_replace("(48 Contiguous Sta", "", $row_orders["billing_country"]);
$Billing_Phone = filter_text($row_orders["customers_telephone"]);
$ShipTo_Name1 = $row_orders["delivery_name"];
$ShipTo_Name = filter_text($ShipTo_Name1); // order changed
list($ShipTo_First_Name,$ShipTo_Last_Name) = explode(', ',$ShipTo_Name1); // order changed
$ShipTo_Company = filter_text($row_orders["delivery_company"]);
$ShipTo_Address_1 = filter_text($row_orders["delivery_street_address"]);
$ShipTo_Address_2 = "";
$ShipTo_City = filter_text($row_orders["delivery_city"]);
$ShipTo_State = filter_text($row_orders["delivery_state"]);
$ShipTo_Zip = filter_text($row_orders["delivery_postcode"]);
$ShipTo_Country = str_replace("(48 Contiguous Sta", "", $row_orders["delivery_country"]);
$ShipTo_Phone = "";
$Card_Type = $row_orders["cc_type"];
$Card_Number = $row_orders["cc_number"];
$Exp_Date = $row_orders["cc_expires"];
$Bank_Name = "";
$Gateway  = "";
$AVS_Code = "";
$Transaction_ID = "";
$Order_Special_Notes = "";
// --------------------	QUERIES 1  ------------------------------------//
//Orders_status_history for comments
$orders_status_history = mysql_query("select comments from orders_status_history
where orders_id = " . $Orders_id);
//$row_orders_status_history = mysql_fetch_array($comments);
while($row_orders_status_history = mysql_fetch_array($orders_status_history)) {
// end //

$Comments = filter_text($row_orders_status_history["comments"]);

}
// --------------------	QUERIES 2  ------------------------------------//
//Orders_subtotal
$orders_subtotal = mysql_query("select value from orders_total
where class = 'ot_subtotal' and orders_id = " . $Orders_id);
//$row_orders_subtotal = mysql_fetch_array($orders_subtotal);
while($row_orders_subtotal = mysql_fetch_array($orders_subtotal)) {
// end //
$Order_Subtotal = filter_text($row_orders_subtotal["value"]);
}
// --------------------	QUERIES 3  ------------------------------------//
//Orders_tax
$orders_tax = mysql_query("select value from orders_total
where class = 'ot_tax' and orders_id = " . $Orders_id);
//$row_orders_tax = mysql_fetch_array($orders_tax);
while($row_orders_tax = mysql_fetch_array($orders_tax)) {
// end //
$Order_Tax = filter_text($row_orders_tax["value"]);
}
// --------------------	QUERIES 4  ------------------------------------//
//Orders_Insurance
$orders_insurance = mysql_query("select value from orders_total
where class = 'ot_insurance' and orders_id = " . $Orders_id);
//$row_orders_insurance = mysql_fetch_array($orders_insurance);
while($row_orders_insurance = mysql_fetch_array($orders_insurance)) {
// end //
$Order_Insurance = filter_text($row_orders_insurance["value"]);
}
$Tax_Exempt_Message = "";
// --------------------	QUERIES 5  ------------------------------------//
//Orders_Shipping
$orders_shipping = mysql_query("select title, value from orders_total
where class = 'ot_shipping' and orders_id = " . $Orders_id);
//$row_orders_shipping = mysql_fetch_array($orders_shipping);
while($row_orders_shipping = mysql_fetch_array($orders_shipping)) {
// end //
$Order_Shipping_Total = ($row_orders_shipping["value"]);
$Shipping_Method = filter_text($row_orders_shipping["title"]); // Shipping method from query 5
}
// --------------------	QUERIES 6  ------------------------------------//
//Orders_Residential Del Fee (Giftwrap)
$orders_residential_fee = mysql_query("select value from orders_total
where class = 'ot_giftwrap' and orders_id = " . $Orders_id);
//$row_orders_residential_fee = mysql_fetch_array($orders_residential_fee);
while($row_orders_residential_fee = mysql_fetch_array($orders_residential_fee)) {
// end //
$Small_Order_Fee = $row_orders_residential_fee["value"];
}
////////////////////////////////////
$Discount_Rate = "";
$Discount_Message  = "";
$CODAmount  = "";
// --------------------	QUERIES 7  ------------------------------------//
//Orders_Total
$orders_total = mysql_query("select value from orders_total
where class = 'ot_total' and orders_id = " . $Orders_id);
//$row_orders_total = mysql_fetch_array($orders_total);
while($row_orders_total = mysql_fetch_array($orders_total)) {
// end //
$Order_Grand_Total = $row_orders_total["value"];
}
// --------------------	QUERIES 8  ------------------------------------//
//Products COunt
$orders_count = mysql_query("select count(products_quantity) as o_count from orders_products
where orders_id = " . $Orders_id);
//$row_orders_total = mysql_fetch_array($orders_total);
while($row_orders_count = mysql_fetch_array($orders_count)) {
// end //
$Number_of_Items = $row_orders_count[0]; // used array to show the number of items ordered
}
//--------------------------------------------------------------------------------
//

///TAX 

$date_tax_change= $Date;
//$date_tax_change='2008-11-30 00-00-00';
$date_tax_change1='11/30/2008';

//test function below
$orders_products_costp2 = mysql_query("SELECT sum(products_cost * products_quantity) AS total_sum2
FROM orders_products WHERE orders_id = " . $Orders_id);
$add_result2 = mysql_fetch_array($orders_products_costp2);
$totalcost = $add_result2['total_sum2'];

if(greaterDate($date_tax_change,$date_tax_change1)) {
$orders_products_costp = mysql_query("SELECT sum(products_cost * products_quantity * 1.150) AS total_sum
FROM orders_products WHERE orders_id = " . $Orders_id);
$add_result = mysql_fetch_array($orders_products_costp);
$mytotalsum = $add_result['total_sum'];
} else {
$orders_products_costp = mysql_query("SELECT sum(products_cost * products_quantity * 1.175) AS total_sum
FROM orders_products WHERE orders_id = " . $Orders_id);
$add_result = mysql_fetch_array($orders_products_costp);
$mytotalsum = $add_result['total_sum'];
}

if(greaterDate($date_tax_change,$date_tax_change1)) {
//correct individual item vat!! now used in indprodvat
$orders_indv_cost = mysql_query("SELECT products_cost * products_quantity * 1.150 - products_cost * products_quantity AS total_cost
FROM orders_products WHERE orders_id = " . $Orders_id);
//correct tax total! 
} else {
$orders_indv_cost = mysql_query("SELECT products_cost * products_quantity * 1.175 - products_cost * products_quantity AS total_cost
FROM orders_products WHERE orders_id = " . $Orders_id);
} 


if(greaterDate($date_tax_change,$date_tax_change1)) {
$orders_tax_fixed = mysql_query("SELECT sum(products_cost * products_quantity * 1.150) - (products_cost * products_quantity) AS total_tax
FROM orders_products WHERE orders_id = " . $Orders_id);
$tax_result = mysql_fetch_array($orders_tax_fixed);
$total_tax = $tax_result['total_tax'];
} else {
$orders_tax_fixed = mysql_query("SELECT sum(products_cost * products_quantity * 1.175) - (products_cost * products_quantity) AS total_tax
FROM orders_products WHERE orders_id = " . $Orders_id);
$tax_result = mysql_fetch_array($orders_tax_fixed);
$total_tax = $tax_result['total_tax'];
}

if(greaterDate($date_tax_change,$date_tax_change1)) {
$Order_Shipping_Total_with_tax = $Order_Shipping_Total * 1.150;
} else {
$Order_Shipping_Total_with_tax = $Order_Shipping_Total * 1.175;
}
if(greaterDate($date_tax_change,$date_tax_change1)) {
$Order_Shipping_Total_tax_only = $Order_Shipping_Total * 1.150 - $Order_Shipping_Total;
} else {
$Order_Shipping_Total_tax_only = $Order_Shipping_Total * 1.175 - $Order_Shipping_Total;
}


//costprice + shipping just tax
$mytax = $add_result['total_sum'] + ($Order_Shipping_Total);
$orders_products = mysql_query("select products_cost,'', products_model, products_quantity, products_cost from orders_products
where orders_id = " . $Orders_id);

///spare function to test
for ($i=0; $i<sizeof($order->products); $i++) {
$qty2 = $order->products[$i]['qty'];
}
//////////////

//$test_sql = ($totalcost + ($total_tax) + $Order_Shipping_Total_with_tax);
$test_sql33 = $totalcost;

if(greaterDate($date_tax_change,$date_tax_change1)) {
$test_sql = number_format($mytotalsum,2) - number_format(($mytotalsum / 1.150),2) + $Order_Shipping_Total_tax_only + $totalcost + $Order_Shipping_Total;
} else {
$test_sql = number_format($mytotalsum,2) - number_format(($mytotalsum / 1.175),2) + $Order_Shipping_Total_tax_only + $totalcost + $Order_Shipping_Total;
}
///flipped negative number
$test_sql2 = -abs($test_sql);







// csv settings
$CSV_SEPARATOR = ","; // delimiter
$CSV_NEWLINE = "\r\n"; //new line
$site_name = STORE_NAME;

$csv_output_once .= "\n"; //new line for SPL CODE
$iifformat = "!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,NAME,CLASS,AMOUNT,DOCNUM,MEMO,CLEAR,TOPRINT,
NAMEISTAXABLE,DUEDATE,TERMS,PAYMETH,SHIPVIA,SHIPDATE,REP,FOB,PONUM,INVMEMO,ADDR1
,
ADDR2,ADDR3,ADDR4,ADDR5,SADDR1,SADDR2,SADDR3,SADDR4,SADDR5,TOSEND,ISAJE
!SPL,SPLID,TRNSTYPE,DATE,ACCNT,NAME,CLASS,AMOUNT,DOCNUM,MEMO,CLEAR,QNTY,PRICE
,INVITEM,PAYMETH,TAXABLE,EXTRA,VATCODE,VATRATE,VATAMOUNT,VALADJ,SERVICEDATE,TAXC
O
DE,TAXRATE,TAXAMOUNT,OTHER2,OTHER3,REIMBEXP,,,,,,
!ENDTRNS,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,";

/// .IIF OUTPUT in this order

$csv_output .= "\n" . "TRNS" . ","; //TRNS
$csv_output .= "" . ","; //TRNSID
$csv_output .= "PURCHORD" . ","; //TRNSTYPE
$csv_output .= $Date . ","; //DATE
$csv_output .= "Purchase Orders" . ","; //ACCNT
$csv_output .= "supplier company name" . ","; //NAME
$csv_output .= "" . ","; //CLASS
$csv_output .= round($test_sql2,2) . ","; //?????
$csv_output .= $Orders_id . ","; //DOCNUM
$csv_output .= "paid £" . number_format($Order_Grand_Total ,2) . ","; //MEMO //ok as is but 2 decimal places	   ********************
$csv_output .= "" . ","; //CLEAR
$csv_output .= "N" . ","; //TOPRINT
$csv_output .= "N" . ","; //NAMEISTAXABLE
$csv_output .= $Date . ","; //DUEDATE
$csv_output .= "" . ","; //TERMS
$csv_output .= "" . ","; //PAYMETH
$csv_output .= "" . ","; //SHIPVIA
$csv_output .= $Date . ","; //SHIPDATE
$csv_output .= "" . ","; //REP
$csv_output .= "" . ","; //FOB
$csv_output .= $Orders_id . ","; //PONUM
$csv_output .= "" . ","; //INVMEMO
$csv_output .= "supplier address 1" . ","; //ADDR1
$csv_output .= "suppier address 2 " . ","; //ADDR2
$csv_output .= "supplier town" . ","; //ADDR3
$csv_output .= "supplier city" . ","; //ADDR4
$csv_output .= "supplier address postcode" . ","; //ADDR5
$csv_output .= $First_Name . $Last_Name . ","; //SADDR1
$csv_output .= $Billing_Address_1 . ","; //SADDR2
$csv_output .= $Billing_Address_2 . ","; //SADDR3
$csv_output .= $Billing_City . ","; // SADDR4
$csv_output .= $Billing_Zip . ","; //SADDR5
$csv_output .= "N" . ","; //ISAJE
$csv_output .= "\n"; //new line for SPL CODE


// While loop to list the item
while($row_orders_products = mysql_fetch_array($orders_products)) {
// for the UK tax change if date greater than 30/11/08 then tax code changes from S to TS and VAT RATE changes from 17.5% to 15%  
if(greaterDate($date_tax_change,$date_tax_change1)) {
$indprodvat = ($row_orders_products[0]) * $row_orders_products['products_quantity'] * 1.150 - ($row_orders_products[0]) * $row_orders_products['products_quantity']; //individual product VAT YES!!!!!!!!!
} else {
$indprodvat = ($row_orders_products[0]) * $row_orders_products['products_quantity'] * 1.175 - ($row_orders_products[0]) * $row_orders_products['products_quantity']; //individual product VAT YES!!!!!!!!!
}
//eof
$multitemscost = ($row_orders_products[0]) * ($row_orders_products['products_quantity']);
$csv_output .= "SPL" . "," . "" . "," . "PURCHORD" . "," . $Date . "," . 'Sales:' . $site_name . "," . $First_Name . $Last_Name . "," . "" . "," . number_format($multitemscost,2) . ","; //products_cost needs to be multiplied by quantity*
$csv_output .= $row_orders_products[1] . ","; //model numb
$csv_output .= $row_orders_products[2] . "," . ","; //quantity
$csv_output .= $row_orders_products[3] . ","; //cost individual

// for the UK tax change if date greater than 30/11/08 then tax code changes from S to TS and VAT RATE changes from 17.5% to 15%  
if(greaterDate($date_tax_change,$date_tax_change1)) {
$csv_output .= number_format(filter_text($row_orders_products[4]),2) . "," . 'Web Sales:' . $site_name  . "," . "" . "," . "Y" . "," . "" . "," . "TS" . "," . "15.00%" . "," . round($indprodvat,2) . ",,,,,,,,,,,,,,"  . "\n"; //products_cost individual OK! + individual vat ok!   ??
} else {
$csv_output .= number_format(filter_text($row_orders_products[4]),2) . "," . 'Web Sales:' . $site_name  . "," . "" . "," . "Y" . "," . "" . "," . "S" . "," . "17.50%" . "," . round($indprodvat,2) . ",,,,,,,,,,,,,,"  . "\n"; //products_cost individual OK! + individual vat ok!   ??
}
} // end while loop for products

//delivery costs Splitline here -  ONLY shown if delivery is above 0.01p
 if ($Order_Shipping_Total>=0.01){	 
$csv_output .= "SPL" . ","; //!SPL START!
$csv_output .= "" . ","; //
$csv_output .= "PURCHORD" . ","; //
$csv_output .= $Date . ","; //
$csv_output .= 'Sales:' . $site_name . ","; //
$csv_output .= $First_Name . $Last_Name . ","; //SADDR1
$csv_output .= "" . ","; //
$csv_output .= number_format($Order_Shipping_Total,2) . ","; //
$csv_output .= "" . ","; //
$csv_output .= "delivery costs" . ","; //
$csv_output .= "" . ","; //
$csv_output .= "1" . ","; //QNTY
$csv_output .= number_format($Order_Shipping_Total,2) . ","; //
$csv_output .= 'Web Sales:'. $site_name . ","; //
$csv_output .= "" . ","; //
$csv_output .= "Y" . ","; //
$csv_output .= "" . ","; //
// for the UK tax change if date greater than 30/11/08 then tax code changes from S to TS and VAT RATE changes from 17.5% to 15%  
if(greaterDate($date_tax_change,$date_tax_change1)) {
$csv_output .= "TS" . ","; //
} else {
$csv_output .= "S" . ","; //
}

// for the UK tax change if date greater than 30/11/08 then tax code changes from S to TS and VAT RATE changes from 17.5% to 15%  
if(greaterDate($date_tax_change,$date_tax_change1)) {
$csv_output .= "15.00%" . ","; //
} else {
$csv_output .= "17.50%" . ","; //
}
$csv_output .= number_format($Order_Shipping_Total_with_tax - $Order_Shipping_Total,2) . ",,,,,,,,,,,,,,,,,"; // shipping vat calculation goes here!!!!!!!
//end spl3 part
$csv_output .= "\n"; //new line for SPL CODE
 }else{}
//end of Delivery cost Splitline
//start spl4 part - documented??????

$csv_output .= "SPL" . ","; //!SPL START!
$csv_output .= "" . ","; //
$csv_output .= "PURCHORD" . ","; //
$csv_output .= $Date . ","; //
$csv_output .= "VAT Liability" . ","; //
$csv_output .= "HMRC VAT" . ","; //
$csv_output .= "" . ","; //
if(greaterDate($date_tax_change,$date_tax_change1)) {
$csv_output .= round($mytotalsum = number_format($mytotalsum,2) - number_format(($mytotalsum / 1.150),2) + $Order_Shipping_Total_tax_only,2) . ","; //Total !!cost price!! Order TAX !!!  * 100 + 0.5 / 100
//$csv_output .= round($total_tax,2) + round($Order_Shipping_Total_with_tax,2) - round($Order_Shipping_Total,2) . ",";
} else {
$csv_output .= round($mytotalsum = number_format($mytotalsum,2) - number_format(($mytotalsum / 1.175),2) + $Order_Shipping_Total_tax_only,2) . ","; //Total !!cost price!! Order TAX !!!  * 100 + 0.5 / 100
//$csv_output .= round($total_tax,2) + round($Order_Shipping_Total_with_tax,2) - round($Order_Shipping_Total,2) . ",";
}
$csv_output .= "" . ","; //
$csv_output .= "Standard Purchases" . ","; //
$csv_output .= "N" . ","; //
$csv_output .= "" . ","; //
// for the UK tax change if date greater than 30/11/08 then tax code changes from S to TS and VAT RATE changes from 17.5% to 15%  
if(greaterDate($date_tax_change,$date_tax_change1)) {
$csv_output .= "15.00%" . ","; //
} else {
$csv_output .= "17.50%" . ","; //
}
//eof tax change
$csv_output .= "Standard Purchases" . ","; //
$csv_output .= "" . ","; //
$csv_output .= "N" . ","; //
$csv_output .= "AUTOSTAX" . ",,,,,,,,,,,,,,,,,"; //
$csv_output .= "\n"; //new line for ENDTRANS CODE
$csv_output .= "ENDTRNS" . ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"; //
//eof spl4


} // while loop main first

//print
header("Content-Type: application/force-download\n");
header("Cache-Control: cache, must-revalidate");   
header("Pragma: public");
//header("Content-Disposition: attachment; filename=quickbooks_export_" . date("Ymd") . ".iif");
header("Content-Disposition: attachment; filename=" . $site_name . "_QuickBooks_Export_" . date("d-m-Y") . ".csv");
print $iifformat . $csv_output;
exit;
}//function main

function filter_text($text) {
$filter_array = array(",","\r","\n","\t","-");
return str_replace($filter_array,"",$text);
} // function for the filter

?>

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...