Big Royal Mail 3.0

I have now completely rewritten Big royal mail version 3.2.0
I have included the insurance as standard in each application and added the parcel force postage methods to the pack.
I have also included a method of using attributes to exclude certain shipping methods. Table below show methods excluded for each attribute

New version submitted for price and international format changes.

Thanks Mark, you are a star.

I thought this came in tomorrow, judging by the farce at the Post Office today it would have been better coming in on April Fool's day!

Hello,

I've updated this mod but now I'm getting the following error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':aa43608fdf8a423584cc0ac9397f7045) AND po.products_options_name = "Minimum Deliv' at line 1
in:
[SELECT ifnull(max(pov.products_options_values_sort_order), 0) as postage_attribute FROM zen_products_options_values pov INNER JOIN zen_products_attributes pa ON pov.products_options_values_id = pa.options_values_id INNER JOIN zen_products_options po ON po.products_options_id = pa.options_id WHERE pa.products_id IN (1032:aa43608fdf8a423584cc0ac9397f7045) AND po.products_options_name = "Minimum Delivery Method:"]

Can anyone help please?

I just upgraded from 3.1.0 following the upgrade instructions but all I get when checking out or clicking "Estimate Shipping" is -

WARNING: An Error occurred, please refresh the page and try again.

[03-Apr-2014 12:51:25 Europe/London] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':93665ce0472894a1b520fc80489dc238) AND po.products_options_name = "Minimum Deliv' at line 1 :: SELECT ifnull(max(pov.products_options_values_sort_order), 0) as postage_attribute FROM products_options_values pov INNER JOIN products_attributes pa ON pov.products_options_values_id = pa.options_values_id INNER JOIN products_options po ON po.products_options_id = pa.options_id WHERE pa.products_id IN (1606:93665ce0472894a1b520fc80489dc238) AND po.products_options_name = "Minimum Delivery Method:" in /var/www/vhosts/devus.com/httpdocs/zen15/includes/classes/db/mysql/query_factory.php on line 120

I note that the instructions say to upload BigRoyalMail_V3.2.1/images/* but there are no images in the zip file.

install3.2 sql file seems to contain a syntax error and therefore cannot be installed until it's corrected?

Just speculating here but the error mentions a product option called 'minimum deliv'

The README.htm for a fresh installation talks about setting the minimum delivery method via Catalog>Categories/Products> but having followed the UPGRADE.htm instructions I don't see anything about minimum delivery on the product options.

I've searched via developers toolkit for some of the code fragments in the error above and they only occur in /includes/modules/shipping/BigRoyalMail/quote.php so I tend to think the error is in this file.

clearly this latest update has not been thoroughly tested.

I tried to call Mark via his website but no answer so I left a message asking him to have a look here when he had time.

I compared the quote.php with previous versions and they are the same, so it may not be that.

Can't wait any longer so I am having our site restored from backup.

globalmemory:

Hello,

I've updated this mod but now I'm getting the following error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':aa43608fdf8a423584cc0ac9397f7045) AND po.products_options_name = "Minimum Deliv' at line 1
in:
[SELECT ifnull(max(pov.products_options_values_sort_order), 0) as postage_attribute FROM zen_products_options_values pov INNER JOIN zen_products_attributes pa ON pov.products_options_values_id = pa.options_values_id INNER JOIN zen_products_options po ON po.products_options_id = pa.options_id WHERE pa.products_id IN (1032:aa43608fdf8a423584cc0ac9397f7045) AND po.products_options_name = "Minimum Delivery Method:"]

Can anyone help please?

Sorry I have been away. Just got back. It looks like you have not run the SQL Script install3.2.sql this is necessary for the latest version. I'll check the install instructions to see if that is ok. seems to be there.

globalmemory:

install3.2 sql file seems to contain a syntax error and therefore cannot be installed until it's corrected?

I have check the SQL on my system and it is fine. Just incase here is the script.

--
-- Set up the Attributes for Big royal Mail delivery options.
--
-- Create or find the Delivery option
--
SELECT @options_id:=ifnull(max(products_options_id)+1,1) FROM products_options;

SELECT @options_id := ifnull(products_options_id, @options_id) FROM products_options WHERE products_options_name="Minimum Delivery Method:" ;

REPLACE INTO products_options (products_options_id, language_id, products_options_name, products_options_sort_order, products_options_type, products_options_length, products_options_comment, products_options_size, products_options_images_per_row, products_options_images_style, products_options_rows) VALUES
(@options_id, 1, 'Minimum Delivery Method:', 0, 5, 32, '', 32, 0, 0, 0);

SELECT @options_id := products_options_id FROM products_options WHERE products_options_name="Minimum Delivery Method:" ;

--
-- Set up the option values
--
-- Large Letter
--
SELECT @LargeLetter_id := ifnull(max(products_options_values_id)+1,1) FROM products_options_values;

SELECT @LargeLetter_id := ifnull(products_options_values_id, @LargeLetter_id) FROM products_options_values WHERE products_options_values_name="Large Letter" ;

REPLACE INTO products_options_values (products_options_values_id, language_id, products_options_values_name, products_options_values_sort_order) VALUES
(@LargeLetter_id, 1, 'Large Letter', 10);

SELECT @LargeLetter_id := products_options_values_id FROM products_options_values WHERE products_options_values_name="Large Letter" ;

--
-- Small Parcel
--
SELECT @SmallParcel_id := ifnull(max(products_options_values_id)+1,1) FROM products_options_values;

SELECT @SmallParcel_id := ifnull(products_options_values_id, @SmallParcel_id) FROM products_options_values WHERE products_options_values_name="Small Parcel" ;

REPLACE INTO products_options_values (products_options_values_id, language_id, products_options_values_name, products_options_values_sort_order) VALUES
(@SmallParcel_id, 1, 'Small Parcel', 20);


SELECT @SmallParcel_id := products_options_values_id FROM products_options_values WHERE products_options_values_name="Small Parcel" ;

--
-- Medium Parcel
--
SELECT @MediumParcel_id := ifnull(max(products_options_values_id)+1,1) FROM products_options_values;

SELECT @MediumParcel_id := ifnull(products_options_values_id, @MediumParcel_id) FROM products_options_values WHERE products_options_values_name="Medium Parcel" ;

REPLACE INTO products_options_values (products_options_values_id, language_id, products_options_values_name, products_options_values_sort_order) VALUES
(@MediumParcel_id, 1, 'Medium Parcel', 30);


SELECT @MediumParcel_id := products_options_values_id FROM products_options_values WHERE products_options_values_name="Medium Parcel" ;

--
-- Parcel Force
--
SELECT @ParcelForce_id := ifnull(max(products_options_values_id)+1,1) FROM products_options_values;

SELECT @ParcelForce_id := ifnull(products_options_values_id, @ParcelForce_id) FROM products_options_values WHERE products_options_values_name="Special Delivary or Parcel Force" ;

REPLACE INTO products_options_values (products_options_values_id, language_id, products_options_values_name, products_options_values_sort_order) VALUES
(@ParcelForce_id, 1, 'Special Delivary or Parcel Force', 40);


SELECT @ParcelForce_id := products_options_values_id FROM products_options_values WHERE products_options_values_name="Special Delivary or Parcel Force" ;

--
-- Link the option and option values.
--
SELECT @Pov2po_id := ifnull(max(products_options_values_to_products_options_id)+1,1) FROM products_options_values_to_products_options;

SELECT @Pov2po_id := ifnull(products_options_values_to_products_options_id, @Pov2po_id) FROM products_options_values_to_products_options 
WHERE products_options_id= @options_id AND products_options_values_id = @LargeLetter_id;
REPLACE INTO products_options_values_to_products_options (products_options_values_to_products_options_id, products_options_id, products_options_values_id) VALUES
(@Pov2po_id, @options_id, @LargeLetter_id);

SELECT @Pov2po_id := ifnull(max(products_options_values_to_products_options_id)+1,1) FROM products_options_values_to_products_options;

SELECT @Pov2po_id := ifnull(products_options_values_to_products_options_id, @Pov2po_id) FROM products_options_values_to_products_options 
WHERE products_options_id= @options_id AND products_options_values_id = @SmallParcel_id;
REPLACE INTO products_options_values_to_products_options (products_options_values_to_products_options_id, products_options_id, products_options_values_id) VALUES
(@Pov2po_id, @options_id, @SmallParcel_id);

SELECT @Pov2po_id := ifnull(max(products_options_values_to_products_options_id)+1,1) FROM products_options_values_to_products_options;

SELECT @Pov2po_id := ifnull(products_options_values_to_products_options_id, @Pov2po_id) FROM products_options_values_to_products_options 
WHERE products_options_id= @options_id AND products_options_values_id = @MediumParcel_id;
REPLACE INTO products_options_values_to_products_options (products_options_values_to_products_options_id, products_options_id, products_options_values_id) VALUES
(@Pov2po_id, @options_id, @MediumParcel_id);

SELECT @Pov2po_id := ifnull(max(products_options_values_to_products_options_id)+1,1) FROM products_options_values_to_products_options;

SELECT @Pov2po_id := ifnull(products_options_values_to_products_options_id, @Pov2po_id) FROM products_options_values_to_products_options 
WHERE products_options_id= @options_id AND products_options_values_id = @ParcelForce_id;
REPLACE INTO products_options_values_to_products_options (products_options_values_to_products_options_id, products_options_id, products_options_values_id) VALUES
(@Pov2po_id, @options_id, @ParcelForce_id);

-- 
-- Add the insurance rates for Special delivery if installed
--
INSERT ignore INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added)
SELECT 'Insurance rates ', 'MODULE_SHIPPING_RMSPECIALDELIVERY_ZONES_INSURE', '500:0,1000:1,2500:3', 'example: 200:1.2 means values less than or equal to £200 would cost £1.20. to insure. 100+:4.5 means that each additional £100 costs £4.50 to insure.', '6', '0', 'zen_cfg_textarea(', now()
FROM configuration WHERE configuration_key  = "MODULE_SHIPPING_RMSPECIALDELIVERY_STATUS";
  
INSERT ignore INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added)
SELECT 'Insurance rates ', 'MODULE_SHIPPING_RMSPECIALDELIVERY9AM_ZONES_INSURE', '50:0,2.2:1000,5.7:2500', 'example: 200:1.2 means values less than or equal to £200 would cost £1.20. to insure. 100+:4.5 means that each additional £100 costs £4.50 to insure.', '6', '0', 'zen_cfg_textarea(', now()
FROM configuration WHERE configuration_key  = "MODULE_SHIPPING_RMSPECIALDELIVERY9AM_STATUS";

-- 
-- Update max order value for special to 2500
-- 
UPDATE configuration SET configuration_value = 2500 WHERE configuration_key  = "MODULE_SHIPPING_RMSPECIALDELIVERY_MAX_ORDERVALUE";

UPDATE configuration SET configuration_value = 2500 WHERE configuration_key  = "MODULE_SHIPPING_RMSPECIALDELIVERY9AM_MAX_ORDERVALUE";

devus:

I note that the instructions say to upload BigRoyalMail_V3.2.1/images/* but there are no images in the zip file.

Whoops. I was a bit rushed. I will rerelease the Images. They have not changed.

devus:

Just speculating here but the error mentions a product option called 'minimum deliv'

The README.htm for a fresh installation talks about setting the minimum delivery method via Catalog>Categories/Products> but having followed the UPGRADE.htm instructions I don't see anything about minimum delivery on the product options.

I've searched via developers toolkit for some of the code fragments in the error above and they only occur in /includes/modules/shipping/BigRoyalMail/quote.php so I tend to think the error is in this file.
Please see http://www.zen-cart.com/showthread.php?198005-Big-Royal-Mail-3-0&p=1227764#post1227764 to see about the new method I introduced at version 3.1. I will look at putting in some code to produce a meaningful error.

Found a bug connected with attributes.

Change the quote.php module in

YOUR_SHOPPING_CART_DIRECTORY/includes/modules/shipping/BigRoyalMail/quote.php

As a temporary fix replace```
$attribute_postage_select = 'SELECT ifnull(max(pov.products_options_values_sort_order), 0) as postage_attribute FROM '.TABLE_PRODUCTS_OPTIONS_VALUES.' pov INNER JOIN '.
TABLE_PRODUCTS_ATTRIBUTES.' pa ON pov.products_options_values_id = pa.options_values_id INNER JOIN '.
TABLE_PRODUCTS_OPTIONS.' po ON po.products_options_id = pa.options_id WHERE pa.products_id IN ('.$products_list.
') AND po.products_options_name = "Minimum Delivery Method:"';

with ```
$attribute_postage_select = 'SELECT 0';

This should work with 1.5 versions of Zen Cart I am not sure if it works with 1.3 versions but suspect it will.

I have found the issue. it was connected with attributes.
You need to change the quote.php which can be found in YOUR_SHOPPING_CART_DIRECTORY/includes/modules/shipping/BigRoyalMail
you need to replace line 57

$products_list = $_SESSION['cart']->get_product_id_list();

with

	$products_list = '';
	if (is_array($_SESSION['cart']->contents)) {
		reset($_SESSION['cart']->contents);
		while (list($products_id, ) = each($_SESSION['cart']->contents)) {
			$products_list .= ', ' . zen_get_prid(zen_db_input($products_id));
		}
	}
	$products_list = substr($products_list, 2);

Hi Mark,

I just installed your latest update v3.2.1 in a test shop. (It seems to work fine in Zen Cart v1.3.9 by the way.)

Unfortunately, there seems to be one service missing in my installation, "International Signed" (which used to be International "signed for").

Royal Mail have really complicated things with this new shuffle, they seem to have made three overlapping services out of one!
Now we have "International Signed", "International Tracked & Signed" and "International Tracked". Strangely, all at the same price unless you send to Europe using the latter one (with no signature provided), when they add extra 20% vat.

Thanks for the contribution. You must have incredible patience.

Sorry, my mistake, I just found it. There was a line with no description, just "ver. 3.2.1" in red letters that I'd overlooked. Clicked on it and that was the one I'd been looking for.

That suggest that you may be missing a language file or the file is the corrupt. The file should be in YOUR_SHOPPING_CART_DIRECTORY/includes/languages/english/modules/shipping/ and is called rmamparcelsf.php.

And Yes you are right it is crazy. I also only had a weeks notice of the change because parcelforce did not release their rates until the Monday before the change. You are right with 3 methods with the same price. The International Tracked and Signed is supposed to be faster than the other two signed and tracked.

If you use attributes at all please ensure that you apply the fix above http://www.zen-cart.com/showthread.php?198005-Big-Royal-Mail-3-0&p=1243102#post1243102. I am just setting up a 1.3.8a to try to test it. Glad to here that it works on 1.3.9.
Finally if you have updated from 3.0.1 you will need to apply RemoveSpecial.sql to remove the extra special delivery method from the configuration files.

Hi Mark,

I checked that file and it's not really a problem, just that the text hasn't been fully filled in - obviously a "holding" dot. First line reads:

define('MODULE_SHIPPING_RMAMPARCELSF_TEXT_TITLE', '.');

Looks like the same for rmspecialdelivery file also.