Page 2 of 2

Re: SQL problem with adding the shipping value to vat and price

Posted: Wed May 28, 2008 3:06 pm
by ashebrian
i'm using this.....www.phpwebcommerce.com/php-mysql-shoppi ... torial.php. And am not using paypal

I have to go now and will be back with more info and be more clearer

Re: SQL problem with adding the shipping value to vat and price

Posted: Wed May 28, 2008 9:48 pm
by ashebrian
I am retrieving the vat value from my form:

Code: Select all

$vat = $_POST['hidVat'];


Would make a difference if the form i'm retrieving it from displays the information to the customer before they click the confirm button and bring them to the third party's website.
The only way I know to try to help you is to get back to the basics of what you are trying to do. You have an order form on which the user can enter several items, each for a different product and a qty. You want to store these records first as a shopping cart, then collect them and add shipping cost and perhaps VAT, depending on total order amount, then store the records, including an order record, to a different table, including the shipping cost and VAT. Is that correct?
The above is correct.

My tables are as follows:

Code: Select all

CREATE TABLE `tbl_order` (
  `od_id` int(10) unsigned NOT NULL auto_increment,
  `member_username` varchar(30),
  `od_date` datetime default NULL,
  `od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  `od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
  `od_memo` varchar(255) NOT NULL default '',
  `od_shipping_first_name` varchar(50) NOT NULL default '',
  `od_shipping_last_name` varchar(50) NOT NULL default '',
  `od_shipping_address1` varchar(100) NOT NULL default '',
  `od_shipping_address2` varchar(100) NOT NULL default '',
  `od_shipping_phone` varchar(32) NOT NULL default '',
  `od_shipping_city` varchar(100) NOT NULL default '',
  `od_shipping_state` varchar(32) NOT NULL default '',
  `od_shipping_postal_code` varchar(10) NOT NULL default '',
  `od_shipping_cost` decimal(5,2) default '0.00',
  `od_vat` int(2) NOT NULL default '21',
  `od_payment_first_name` varchar(50) NOT NULL default '',
  `od_payment_last_name` varchar(50) NOT NULL default '',
  `od_payment_address1` varchar(100) NOT NULL default '',
  `od_payment_address2` varchar(100) NOT NULL default '',
  `od_payment_phone` varchar(32) NOT NULL default '',
  `od_payment_city` varchar(100) NOT NULL default '',
  `od_payment_state` varchar(32) NOT NULL default '',
  `od_payment_postal_code` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`od_id`,`member_username`)
) TYPE=MyISAM AUTO_INCREMENT=1310 ;
 
CREATE TABLE `tbl_order_item` (
  `od_id` int(10) unsigned NOT NULL default '0',
  `pd_id` int(10) unsigned NOT NULL default '0',
  `od_qty` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`od_id`,`pd_id`)
) TYPE=MyISAM;
 
CREATE TABLE `tbl_cart` (
  `ct_id` int(10) unsigned NOT NULL auto_increment,
  `pd_id` int(10) unsigned NOT NULL default '0',
  `ct_qty` mediumint(8) unsigned NOT NULL default '1',
  `ct_session_id` char(32) NOT NULL default '',
  `ct_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ct_id`),
  KEY `pd_id` (`pd_id`),
  KEY `ct_session_id` (`ct_session_id`)
) TYPE=MyISAM AUTO_INCREMENT=58 ;
Now, if that is correct, the next thing to determine is where you want to calculate VAT and add shipping cost; presumably when you are ready to write the total order. So where is the VAT going to come from? Somehow it doesn't seem appropriate to be coming from your form--your customer shouldn't be involved in this step. Does it need to come from a table, such as relating VAT to levels of purchase? If not, why wouldn't you just code the percentage in your script? I understand that it needs to be stored in the final order, but I don't see any need to be passing it in the $_POST variable array, nor indeed why ANY of the data for the final order should be coming from a form. Shouldn't it just be taking the data that's already stored in the shopping cart and moving it into the final order records?? That's why I'm very confused about your project.
I've tried to just add $vat = 21; but the 3rd parties does not accept it for some reason. But if i used a $_POST it'll accept it. Quite weird after i tested it. Not sure how that is. The shpping cart is 3 php scripts away from where the vat and shipping cost needs to be sorted in checout-functions.php so i don't think you can take it from the shopping cart. i may be wrong.
Edit: Oh, maybe you are displaying the final order to your customer for verification! That would make sense. So you will need to display the VAT calculation at that time, in the form. Is that what you are doing?
thats correct, thats what i'm doing.

This is the shopping cart i'm using. I'm trying to not use Paypal but a different 3rd party.

http://www.phpwebcommerce.com/php-mysql ... torial.php

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 1:25 pm
by califdon
I am really sorry, and I don't like just blowing you off, but I'm just not able to analyze an entire system that is made up of subsystems of which I have no knowledge and snippets of code that depend on something that's not being shown. I hope that someone else who is able to understand what you are doing and is familiar with these external systems will come along and offer you some help. I'm sorry, but I just can't help you. Good luck.

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 7:54 pm
by ashebrian
Thats no problem. I understand. The thing is that i've now actually pin pointed the actual problem. My problem lies with the VAT price that ends up having a decimal point at the end of it. E.g 21/100 * €13.72 = €2.8812.........to get this into cents it'll be * 100 to give you 288.12.

What i now need is a simple code to get rid of the .12 decimal points. Something similar to %f%s or (float)(string).

I've now used hidden inputs in my form that displays everything to my customers for the total price, shipping cost and vat.

Code: Select all

 
$numItem  = count($cartContent);
$subTotal = 0;
$vat = 21;
$shipTotalHigh = 300;
for ($i = 0; $i < $numItem; $i++) {
    extract($cartContent[$i]);
    $subTotal += $pd_price * $ct_qty;
and:

Code: Select all

 
<input name="totalPriceItems" type="hidden" id="totalPriceItems" value="<?php echo displayAmount($subTotal * 100); ?>">
<input name="totalShipping" type="hidden" id="totalShipping" value="<?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount(0 * 100);
                        
                        } else {
                            echo displayAmount($shopConfig['shippingCost'] * 100);
                        }?>">
<input name="totalVat" type="hidden" id="totalVat" value="<?php echo displayAmount((($vat / 100) * $subTotal) * 100); ?>">
 
<input name="totalTotal" type="hidden" id="totalTotal" value="<?php if ($subTotal >= $shipTotalHigh){
                            echo displayAmount(($subTotal + 0 + (($vat / 100) * $subTotal)) * 100); 
                            } else {
                            echo displayAmount(($subTotal + $shopConfig['shippingCost'] + (($vat / 100) * $subTotal)) * 100);
                            }
                            ?>">
This will then POST the amounts to the next script that only adds the orders to the db and gets the totalAmount which is then sent on to the 3rd party. The totalAmount function code is as follows:

Code: Select all

 
function getOrderAmount($orderId)
{
    $orderAmount = 0;
    
    $totalPurchase = $_POST['totalPriceItems'];
    $vat = $_POST['totalVat'];
    $shipping = $_POST['totalShipping'];
    $total = $_POST['totalTotal'];
        
    echo "VAT= $vat";
    echo "ship= $shipping";
    echo "total= $totalPurchase";
    echo "totalAmont= $total";
    
    $orderAmount = $totalPurchase + $vat + $shipping;
 
    return $orderAmount;    
}
 
?>
 
I'm only echoing them to see the correct values. It will be taken out once my problem is solved. As seen above the totalAmount is just for show. I can just use that instead of the others and call it $orderAmount. Will need the decimal points sorted tho. I think to get this sorted it might have to be in the form where the inputs edited. Please advise on the best and easiest solution to get rid of decimal points.

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 9:37 pm
by califdon
ashebrian wrote:My problem lies with the VAT price that ends up having a decimal point at the end of it. \
E.g 21/100 * €13.72 = €2.8812.........to get this into cents it'll be * 100 to give you 288.12.

What i now need is a simple code to get rid of the .12 decimal points. Something similar to %f%s or (float)(string).
Why divide by 100 and then multiply by 100?

Code: Select all

21 * 13.72 = 288.12
Then you can just take the integer of the result, or in PHP terms,

Code: Select all

$cents = floor($calcvat);

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 11:13 pm
by ashebrian
i changed it and had something very similar using floor. The amount is displayed correctly when i echo it out. but the 3rd party is saying Invalid characters.

when i use echo under neat my

Code: Select all

return orderTotal;
It does not display the amount. Is this supposed to happen. The code is as follows:

Code: Select all

function getOrderAmount($orderId)
{
    $orderTotal = 0;
    
    $orderTotal = $_POST['totalTotal'];
 
    echo "orderAmont= $orderTotal";
    return $orderTotal; 
echo "orderAmont= $orderTotal";
}
 
?>
Maybe my function is a mess and too empty

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 11:29 pm
by califdon
ashebrian wrote:i changed it and had something very similar using floor. The amount is displayed correctly when i echo it out. but the 3rd party is saying Invalid characters.
Are they expecting a numeric or text? the floor() function returns an integer, according to the manual.
when i use echo under neat my

Code: Select all

return orderTotal;
It does not display the amount. Is this supposed to happen. The code is as follows:

Code: Select all

function getOrderAmount($orderId)
{
    $orderTotal = 0;
    
    $orderTotal = $_POST['totalTotal'];
 
    echo "orderAmont= $orderTotal";
    return $orderTotal; 
echo "orderAmont= $orderTotal";
}
?>
Maybe my function is a mess and too empty
When you issue a "return ..." statement, the function is complete, so it won't execute anything that follows. I take it the first echo statement does echo to your screen?

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 11:40 pm
by ashebrian
it echos the correct amt in cents.

Total amount to authorise in the lowest unit of the currency –
i.e. 100 euro would be entered as 10000. If there is no decimal
in the currency then (e.g. JPY Yen) then contact xxxxxxxx. No decimal points are allowed.

Amount is Numeric (0-9).

May i ask about the $_post. the request file comes after this and show the echo amount. But as i used $_POST, will i have to use that for the request file too?

Re: SQL problem with adding the shipping value to vat and price

Posted: Thu May 29, 2008 11:42 pm
by califdon
ashebrian wrote:it echos the correct amt in cents.

Total amount to authorise in the lowest unit of the currency –
i.e. 100 euro would be entered as 10000. If there is no decimal
in the currency then (e.g. JPY Yen) then contact xxxxxxxx. No decimal points are allowed.

Amount is Numeric (0-9).
I don't see any reason that it doesn't accept it, then.
May i ask about the $_post. the request file comes after this and show the echo amount. But as i used $_POST, will i have to use that for the request file too?
I don't understand your question at all.

Re: SQL problem with adding the shipping value to vat and price

Posted: Fri May 30, 2008 4:29 am
by ashebrian
I was thinking that the maybe the

Code: Select all

$orderTotal = $_POST['totalTotal'];
Only posts the file once but not to request.php. But i'm wrong coz its an include file.

I've pin pointed the problem again. I have been getting my result as lets say 2,660. As you see the comma in that total, this means that its not in cents. So i've changed this.

My code now stands at:

Code: Select all

$numItem  = count($cartContent);
$subTotal = 0;
$vat = 21;
$shipTotalHigh = 300;
for ($i = 0; $i < $numItem; $i++) {
    extract($cartContent[$i]);
    $subTotal += $pd_price * $ct_qty;
    
$vattaxing = ((21/100) * $subTotal);
$vattax = floor(($vattaxing) * 100 + .5) * .01;
$ashtotal = $vattax + $subTotal;
$ashtotalShip = $vattax + $subTotal + $shopConfig['shippingCost'];

Code: Select all

<input name="totalTotal" type="hidden" id="totalTotal" value="<?php if ($subTotal >= $shipTotalHigh){
                            echo displayTotal($ashtotal); 
                            } else {
                            echo displayTotal($ashtotalShip);
                            }
                            ?>">
with the code from my checkout-fuctions.php multiplying the $orderTotal by 100 as shown below:

Code: Select all

function getOrderAmount($orderId)
{
    
    $orderTotal = $_POST['totalTotal'] * 100;
 
    echo "orderTotal= $orderTotal";
    return $orderTotal; 
}
 
?>
However the floor is not working properly as it doesn't give me the total of 2660 but it gives me 2700. I've been working on this and have got another result as 2600 but no 2660 as needed. I understand where those figures are coming from but i'm unsure if floor can be used to get the 2660 value.

Re: SQL problem with adding the shipping value to vat and price

Posted: Fri May 30, 2008 1:22 pm
by califdon
You should not guess at these things. Read the manual: http://us3.php.net/floor

Re: SQL problem with adding the shipping value to vat and price

Posted: Tue Jun 03, 2008 7:03 am
by ashebrian
Ok what i did was i'm able to get the amount. But it was displayed with a comma so i used

Code: Select all

str_replace
to get rid of this. However, the 3rd party still does not see that as cents and will not accept it. I think that they'll still read it as a comma.

Therefore, i think posting the prices over will not work. I'm going back to get the info from the db again. This time i'm trying to add the od_price_total to my tbl_order table. The only thing i've changed in my checkout-functions.php is from this code that used post and has the correct amount but wouldn't send due to the str_replace:

Code: Select all

$orderTotal = str_replace(',', '', $_POST['totalTotal']);
 
echo "orderTotal= $orderTotal";
return $orderTotal;
and replaced it with this:

Code: Select all

 
$orderTotal = 0;
$totalTotal    = $_POST['totalTotal'];
    
    $sql = "SELECT SUM('$totalTotal')
            FROM tbl_order
            WHERE od_id = $orderId";
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 1) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $orderTotal = $totalPurchase;
        echo "orderTotal= $orderTotal";
    }   
    return $orderTotal;


however, this code i replace it with only returns 2 from the db. ummmmmmm.....My table is as follows:

Code: Select all

CREATE TABLE `tbl_order` (
  `od_id` int(10) unsigned NOT NULL auto_increment,
  `member_username` varchar(30),
  `od_date` datetime default NULL,
  `od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  `od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
  `od_memo` varchar(255) NOT NULL default '',
  `od_shipping_first_name` varchar(50) NOT NULL default '',
  `od_shipping_last_name` varchar(50) NOT NULL default '',
  `od_shipping_address1` varchar(100) NOT NULL default '',
  `od_shipping_address2` varchar(100) NOT NULL default '',
  `od_shipping_phone` varchar(32) NOT NULL default '',
  `od_shipping_city` varchar(100) NOT NULL default '',
  `od_shipping_state` varchar(32) NOT NULL default '',
  `od_shipping_postal_code` varchar(10) NOT NULL default '',
  `od_shipping_cost` decimal(5,2) default '0.00',
  `od_vat` int(2) NOT NULL default '21',
  `od_price_total` bigint NOT NULL default '',
  `od_payment_first_name` varchar(50) NOT NULL default '',
  `od_payment_last_name` varchar(50) NOT NULL default '',
  `od_payment_address1` varchar(100) NOT NULL default '',
  `od_payment_address2` varchar(100) NOT NULL default '',
  `od_payment_phone` varchar(32) NOT NULL default '',
  `od_payment_city` varchar(100) NOT NULL default '',
  `od_payment_state` varchar(32) NOT NULL default '',
  `od_payment_postal_code` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`od_id`,`member_username`)
) TYPE=MyISAM AUTO_INCREMENT=1610 ;
I'm not sure if bigint is a problem for od_price_total.