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

osCommerce

The e-commerce.

New Demo
Our Partners

Can Someone please help with this code ?

valley

By valley
in General Support

Recommended Posts

valley

valley

Posted
Posted

I am trying to get the UK VAT summary contribution working.

 

 

The contribution assumes evry sale has three

entries, Sub Total , Shipping and VAT(Tax) and

with sort orders 3,2 and 1 (original 1,2 and 3)

 

A temporary table is created with the tree TEXT entries

for each order and then the three TEXTS are sorted in to

3 seperate arrays with order_id and ttext pair.

 

The problem is if an order doesn't have any tax or shipping

(manually modified) the second and third array gets mixed

up as data is picked sequentially.

 

I would like to ensure that the second and third array correspond to

the first one and ZERO values are entered if empty.

 

Below is an attempt which try to use a thirdtable but comes with

error abiguous order-id

 

tep_db_query("CREATE TABLE ". ORDERS_TEMP . " (
 orders_id int(11) NOT NULL default '0',
 date_purchased datetime default '0000-00-00 00:00:00',
 customers_name varchar(64) NOT NULL default '',
 customers_country varchar(32) NOT NULL default '',
 orders_status int(5) NOT NULL default '0',
 currency char(3) NOT NULL default '',
 currency_value decimal(14,6) NOT NULL default '0.000000',
 text varchar(255) default NULL,
 sort_order int(11) NOT NULL default '0'
 ) TYPE=MyISAM");

 tep_db_query("CREATE TABLE " . TAX_TABLE . " (
 orders_id int(11) NOT NULL default '0',
 purchase_date datetime NOT NULL default '0000-00-00 00:00:00',
 customer_name varchar(64) NOT NULL default '',
 customers_country varchar(32) NOT NULL default '0',
 orders_status int(5) NOT NULL default '0',
 currency char(3) NOT NULL default '',
 currency_value decimal(14,6) NOT NULL default '0.000000',
 orders_vat varchar(255) NOT NULL default '0',
 orders_shipping varchar(255) NOT NULL default '0',
 orders_total varchar(255) NOT NULL default '0',
 PRIMARY KEY  (orders_id)
 ) TYPE=MyISAM");

 tep_db_query("CREATE TABLE " . TEMP_SORT . " (
 orders_id int(11) NOT NULL default '0',
 PRIMARY KEY  (orders_id)
 ) TYPE=MyISAM");
 
 tep_db_query("INSERT INTO " . ORDERS_TEMP . " (orders_id, date_purchased, customers_name, customers_country, orders_status, currency, currency_value, text, sort_order) SELECT orders.orders_id, orders.date_purchased, orders.customers_name, orders.customers_country, orders.orders_status, orders.currency, orders.currency_value, orders_total.text, orders_total.sort_order FROM " . TABLE_ORDERS . ", orders_total WHERE orders.orders_id = orders_total.orders_id and orders.date_purchased > '" . $start_year. "-" . $start_date . " 00:00:00' and orders.date_purchased < '" . $end_year. "-" . $end_date . " 00:00:00'");

 $sel_tax_3 = tep_db_query("select orders_id, date_purchased, customers_name, customers_country, orders_status, currency, currency_value, text from " . ORDERS_TEMP . " where sort_order = 3 order by orders_id asc");
 tep_db_query("INSERT INTO " . TEMP_SORT . " (orders_id) SELECT orders_id from " . ORDERS_TEMP . " order by orders_id asc");

 $sel_tax_2 = tep_db_query("select orders_id, text from " . ORDERS_TEMP . ", vat_order_temp3 where orders_id = vat_order_temp3.orders_id and sort_order = 2 order by orders_id asc");
 $sel_tax_1 = tep_db_query("select orders_id, text from " . ORDERS_TEMP . ", vat_order_temp3 where orders_id = vat_order_temp3.orders_id and sort_order = 1 order by orders_id asc");

 

The full code is below

 

<?php

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();
 


?>  
<!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">
<div id="spiffycalendar" class="text"></div>
<!-- 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></tr>
   <!-- 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"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
   
 <?php
 
 function get_dates( $start_date,$start_year,$end_date,$end_year)
           {
           print $PHP_SELF;
           print "<form name=\"vat_report\" method=\"get\" action=\"vat_report.php\">
           <table width=\"47%\" border=\"2\" cellspacing=\"0\" cellpadding=\"1\">
               <tr> 
                     <td width=\"16%\">Start Month</td>
                     <td width=\"21%\">Start Year</td>
                     <td width=\"17%\"> </td>
                     <td width=\"20%\">End Month</td>
                     <td width=\"26%\">End Year</td>
               </tr>
               <tr> 
                     <td><select name=\"start_date\" id=\"select5\">
                     <option value=\"01-01\" selected>Jan 01</option>
                     <option value=\"04-01\">April 01</option>
                     <option value=\"07-01\">July 01</option>
                     <option value=\"10-01\">Nov 01</option>
                   </select></td>
                 <td><select name=\"start_year\" id=\"select6\">
                     <option value=\"2000\" selected>2000</option>
                     <option value=\"2001\">2001</option>
                     <option value=\"2002\">2002</option>
                     <option value=\"2003\">2003</option>
                     <option value=\"2004\">2004</option>
                     <option value=\"2005\">2005</option>
                   </select></td>
                 <td> </td>
                 <td><select name=\"end_date\" id=\"select7\">
                     <option value=\"01-01\" selected>Jan 01</option>
                     <option value=\"04-01\">April 01</option>
                     <option value=\"07-01\">July 01</option>
                     <option value=\"10-01\">Nov 01</option>
               </select></td>
                 <td><select name=\"end_year\" id=\"select8\">
                     <option value=\"2000\" selected>2000</option>
                     <option value=\"2001\">2001</option>
                     <option value=\"2002\">2002</option>
                     <option value=\"2003\">2003</option>
                     <option value=\"2004\">2004</option>
                     <option value=\"2005\">2005</option>
                   </select></td>
               </tr>
             </table>
           <input type=\"submit\" name=\"Submit\" value=\"Submit\">
           </form>";
           }
 get_dates($start_date,$start_year,$end_date,$end_year);
 
 tep_db_query("CREATE TABLE ". ORDERS_TEMP . " (
 orders_id int(11) NOT NULL default '0',
 date_purchased datetime default '0000-00-00 00:00:00',
 customers_name varchar(64) NOT NULL default '',
 customers_country varchar(32) NOT NULL default '',
 orders_status int(5) NOT NULL default '0',
 currency char(3) NOT NULL default '',
 currency_value decimal(14,6) NOT NULL default '0.000000',
 text varchar(255) default NULL,
 sort_order int(11) NOT NULL default '0'
 ) TYPE=MyISAM");

 tep_db_query("CREATE TABLE " . TAX_TABLE . " (
 orders_id int(11) NOT NULL default '0',
 purchase_date datetime NOT NULL default '0000-00-00 00:00:00',
 customer_name varchar(64) NOT NULL default '',
 customers_country varchar(32) NOT NULL default '0',
 orders_status int(5) NOT NULL default '0',
 currency char(3) NOT NULL default '',
 currency_value decimal(14,6) NOT NULL default '0.000000',
 orders_vat varchar(255) NOT NULL default '0',
 orders_shipping varchar(255) NOT NULL default '0',
 orders_total varchar(255) NOT NULL default '0',
 PRIMARY KEY  (orders_id)
 ) TYPE=MyISAM");

 tep_db_query("CREATE TABLE " . TEMP_SORT . " (
 orders_id int(11) NOT NULL default '0',
 PRIMARY KEY  (orders_id)
 ) TYPE=MyISAM");
 
 tep_db_query("INSERT INTO " . ORDERS_TEMP . " (orders_id, date_purchased, customers_name, customers_country, orders_status, currency, currency_value, text, sort_order) SELECT orders.orders_id, orders.date_purchased, orders.customers_name, orders.customers_country, orders.orders_status, orders.currency, orders.currency_value, orders_total.text, orders_total.sort_order FROM " . TABLE_ORDERS . ", orders_total WHERE orders.orders_id = orders_total.orders_id and orders.date_purchased > '" . $start_year. "-" . $start_date . " 00:00:00' and orders.date_purchased < '" . $end_year. "-" . $end_date . " 00:00:00'");

 $sel_tax_3 = tep_db_query("select orders_id, date_purchased, customers_name, customers_country, orders_status, currency, currency_value, text from " . ORDERS_TEMP . " where sort_order = 3 order by orders_id asc");
 tep_db_query("INSERT INTO " . TEMP_SORT . " (orders_id) SELECT orders_id from " . ORDERS_TEMP . " order by orders_id asc");

 $sel_tax_2 = tep_db_query("select orders_id, text from " . ORDERS_TEMP . ", vat_order_temp3 where orders_id = vat_order_temp3.orders_id and sort_order = 2 order by orders_id asc");
 $sel_tax_1 = tep_db_query("select orders_id, text from " . ORDERS_TEMP . ", vat_order_temp3 where orders_id = vat_order_temp3.orders_id and sort_order = 1 order by orders_id asc");

 function strip_no( $num, $exch )
 {
 $num = str_replace("?","",$num);
 $num = str_replace("EUR","",$num);
 $num = str_replace("$","",$num);
 $num = str_replace("USD","",$num);
 $num /= $exch;
 $num = number_format($num, 2, '.', '');
 return $num;
 }

 print "<table width=\"100%\" border=\"2\" cellspacing=\"1\" cellpadding=\"1\">";
 
 while (($sel_tax_1_array = tep_db_fetch_array($sel_tax_1)) && ($sel_tax_2_array = tep_db_fetch_array($sel_tax_2)) && ($sel_tax_3_array = tep_db_fetch_array($sel_tax_3)))
 {

 tep_db_query("insert into " . TAX_TABLE . " (orders_id, purchase_date, customer_name, customers_country, orders_status, currency, currency_value, orders_vat, orders_shipping, orders_total) values ('" . $sel_tax_3_array['orders_id'] . "','" . $sel_tax_3_array['date_purchased'] . "','" . str_replace("'","",$sel_tax_3_array['customers_name']) . "','" . $sel_tax_3_array['customers_country'] . "','" . $sel_tax_3_array['orders_status'] . "','" . $sel_tax_3_array['currency'] . "','" . $sel_tax_3_array['currency_value'] . "','" . $sel_tax_3_array['text'] . "','" . $sel_tax_2_array['text'] . "','" . $sel_tax_1_array['text'] . "')");
 $totals += str_replace("?","",$sel_tax_1_array['text']);
 $shipping_total += str_replace("?","",$sel_tax_2_array['text']);
 $vat_total += str_replace("?","",$sel_tax_3_array['text']);
 }              

 $uk_tax = tep_db_query("select * from " . TAX_TABLE . " where customers_country = 'United Kingdom' and orders_status = '3'");
 print "<table width=\"100%\" border=\"2\" cellspacing=\"0\" cellpadding=\"1\" bordercolor=\"#000000\" bgcolor=\"#F0F1F1\">";
 print "<tr><td colspan=8><div align=center><b>" . UK . "<b></td></tr>";
 print "<tr><td><div align=center><b>" . ORDER_NO . "</b></td><td><div align=center><b>" . PURCHASE_DATE . "</b></td><td><div align=center><b>" . CUSTOMER_NAME . "</b></td><td><div align=center><b>" . COUNTRY . "</b></td><td><div align=center><b>" . ORIG_CURRENCY . "</b></td><td><div align=center><b>" . VAT . "</b></td><td><div align=center><b>" . SHIPPING . "</b></td><td><div align=center><b>" . TOTAL . "</b></td></tr>";  
 while ($uk_tax_array = tep_db_fetch_array($uk_tax))
 {
 print "<tr>";                            
 print "<td>" . $uk_tax_array['orders_id'] . "</td>";
 print "<td>" . $uk_tax_array['purchase_date'] . "</td>";
 print "<td>" . $uk_tax_array['customer_name'] . "</td>";
 print "<td>" . $uk_tax_array['customers_country'] . "</td>";
 print "<td>" . $uk_tax_array['currency'] . "</td>";
 print "<td><div align=right>?" . strip_no( $uk_tax_array['orders_vat'], $uk_tax_array['currency_value']) . "</td>";
 print "<td><div align=right>?" . strip_no( $uk_tax_array['orders_shipping'], $uk_tax_array['currency_value']) . "</td>";
 print "<td><div align=right>?" . strip_no( $uk_tax_array['orders_total'], $uk_tax_array['currency_value']) . "</td>";
 print "</tr>";
 $totals_uk += strip_no( $uk_tax_array['orders_total'], $uk_tax_array['currency_value']);
 $shipping_total_uk += strip_no( $uk_tax_array['orders_shipping'], $uk_tax_array['currency_value']);
 $vat_total_uk += strip_no( $uk_tax_array['orders_vat'], $uk_tax_array['currency_value']);
 }
 print "<tr><td></td><td></td><td></td><td></td><td></td><td><div align=right>?" . $vat_total_uk . "</td><td><div align=right>?" . $shipping_total_uk . "</td><td><div align=right>?" . $totals_uk . "</td></tr></table>";
 
 
 $eu_tax = tep_db_query("select * from " . TAX_TABLE . " where customers_country " . IN_EU . " and orders_status = '3'");
 print "<br><br><table width=\"100%\" border=\"2\" cellspacing=\"0\" cellpadding=\"1\" bordercolor=\"#000000\" bgcolor=\"#F0F1F1\">";
 print "<tr><td colspan=8><div align=center>" . EU_COUNTRYS . "</td></tr>";
 print "<tr><td><div align=center><b>" . ORDER_NO . "</b></td><td><div align=center><b>" . PURCHASE_DATE . "</b></td><td><div align=center><b>" . CUSTOMER_NAME . "</b></td><td><div align=center><b>" . COUNTRY . "</b></td><td><div align=center><b>" . ORIG_CURRENCY . "</b></td><td><div align=center><b>" . VAT . "</b></td><td><div align=center><b>" . SHIPPING . "</b></td><td><div align=center><b>" . TOTAL . "</b></td></tr>";  
 while ($eu_tax_array = tep_db_fetch_array($eu_tax))
 {

 print "<tr>";                            
 print "<td>" . $eu_tax_array['orders_id'] . "</td>";
 print "<td>" . $eu_tax_array['purchase_date'] . "</td>";
 print "<td>" . $eu_tax_array['customer_name'] . "</td>";
 print "<td>" . $eu_tax_array['customers_country'] . "</td>";
 print "<td>" . $eu_tax_array['currency'] . "</td>";
 print "<td><div align=right>?" . strip_no( $eu_tax_array['orders_vat'], $eu_tax_array['currency_value']) . "</td>";
 print "<td><div align=right>?" . strip_no( $eu_tax_array['orders_shipping'], $eu_tax_array['currency_value'] ) . "</td>";
 print "<td><div align=right>?" . strip_no( $eu_tax_array['orders_total'], $eu_tax_array['currency_value'] ) . "</td>";
 print "</tr>";
 $vat_total_eu += strip_no( $eu_tax_array['orders_vat'], $eu_tax_array['currency_value']);
 $shipping_total_eu += strip_no( $eu_tax_array['orders_shipping'], $eu_tax_array['currency_value'] );
 $totals_eu += strip_no( $eu_tax_array['orders_total'], $eu_tax_array['currency_value'] );
 }
 print "<tr><td></td><td></td><td></td><td></td><td></td><td><div align=right>?" . $vat_total_eu . "</td><td><div align=right>?" . $shipping_total_eu . "</td><td><div align=right>?" . $totals_eu . "</td></tr></table>";
 print $vat;
  
 $non_eu_tax = tep_db_query("select * from " . TAX_TABLE . " where customers_country " . NOT_IN_EU . " and orders_status = '3'");
 print "<br><br><table width=\"100%\" border=\"2\" cellspacing=\"0\" cellpadding=\"1\" bordercolor=\"#000000\" bgcolor=\"#F0F1F1\">";
 print "<tr><td colspan=8><div align=center>" . NON_EU_COUNTRYS . "</td></tr>";
 print "<tr><td><div align=center><b>" . ORDER_NO . "</b></td><td><div align=center><b>" . PURCHASE_DATE . "</b></td><td><div align=center><b>" . CUSTOMER_NAME . "</b></td><td><div align=center><b>" . COUNTRY . "</b></td><td><div align=center><b>" . ORIG_CURRENCY . "</b></td><td><div align=center><b>" . VAT . "</b></td><td><div align=center><b>" . SHIPPING . "</b></td><td><div align=center><b>" . TOTAL . "</b></td></tr>";
 while ($non_eu_tax_array = tep_db_fetch_array($non_eu_tax))
 {
 print "<tr>";                            
 print "<td>" . $non_eu_tax_array['orders_id'] . "</td>";
 print "<td>" . $non_eu_tax_array['purchase_date'] . "</td>";
 print "<td>" . $non_eu_tax_array['customer_name'] . "</td>";
 print "<td>" . $non_eu_tax_array['customers_country'] . "</td>";
 print "<td>" . $non_eu_tax_array['currency'] . "</td>";
 print "<td><div align=right>?" . strip_no( $non_eu_tax_array['orders_vat'],$non_eu_tax_array['currency_value']) . "</td>";
 print "<td><div align=right>?" . strip_no( $non_eu_tax_array['orders_shipping'],$non_eu_tax_array['currency_value']) . "</td>";
 print "<td><div align=right>?" . strip_no( $non_eu_tax_array['orders_total'],$non_eu_tax_array['currency_value']) . "</td>";
 print "</tr>";
 $vat_total_non_eu += strip_no( $non_eu_tax_array['orders_vat'],$non_eu_tax_array['currency_value']);
 $shipping_total_non_eu += strip_no( $non_eu_tax_array['orders_shipping'],$non_eu_tax_array['currency_value']);
 $totals_non_eu += strip_no( $non_eu_tax_array['orders_total'],$non_eu_tax_array['currency_value']);
 }
 print "<tr><td></td><td></td><td></td><td></td><td></td><td><div align=right>?" . $vat_total_non_eu . "</td><td><div align=right>?" . $shipping_total_non_eu . "</td><td><div align=right>?" . $totals_non_eu . "</td></tr></table>";
 $vat_total = $vat_total_uk + $vat_total_eu;
 print("<br><br><table width=\"50%\" border=\"2\" align=\"center\" cellpadding=\"1\" cellspacing=\"1\" bgcolor=\"#CCCCCC\">
 <tr> 
   <td><div align=\"center\"><strong><font size=\"5\">UK VAT Payable</font></strong></div></td>
   <td><div align=\"center\"><strong><font size=\"5\">EU VAT Payable</font></strong></div></td>
   <td><div align=\"center\"><strong><font size=\"5\">Total VAT Payable</font></strong></div></td>
 </tr>
 <tr> 
   <td><div align=center><b>?" . $vat_total_uk . "<b></td>
   <td><div align=center><b>?" . $vat_total_eu . "<b></td>
   <td><div align=center><b>?" . $vat_total . "<b></td>
 </tr>
 </table>");
 
 tep_db_query("DROP TABLE `". ORDERS_TEMP . "`,`".  TEMP_SORT . "`,`" . TAX_TABLE . "`");
 
?>
  </table></td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Archived

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

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