Inconsistent _addressbook State records

[Dirty Butter]

I've discovered that the state is sometimes recorded in the addressbook data table as the state name, and sometimes as the id number. I do know that Registration that requires the Description of the address, such as Home, Office, etc., records as the number. But a customer who adds personal information during the Checkout process and checks to use the billing as the default delivery address does not - their _description record shows as Default billing address and Default delivery address, and their state is recorded in _addressbook as the state name.

 

We have had a noticeable increase in abandoned carts, and I'm concerned that this might be the culprit.

[havenswift-hosting]

Hi DB

As you use the Vector skin, have you tried switching to one of the standard skins and then checking to see if this is a Vector skin problem rather than a general CubeCart problem. As you know the circumstances when it happens, it should be easy enough to check

This can then either be reported to the CubeCart team or to shopdev as an issue

Thanks

Ian

[bsmither]

It is a problem with CubeCart.

 

Specifically, a guest adds an item to the cart (necessary to start the checkout process - an empty cart goes nowhere). The guest arrives at the _a=confirm stage and fills in the billing address form, checks the "Delivery address is the same as billing" checkbox, checks the "I want to register" (or similar) checkbox, checks the T&C checkbox, answers the reCaptcha challenge, and clicks Continue/Checkout.

 

The form is POSTed with ['billing']['state'] holding the respective state's state_id number, and with ['delivery']['state'] holding 0 (which is expected and is expected to be ignored). So the checkout form is not at fault.

 

I'm just about to discover the real culprit. Stay tuned.

[bsmither]

Ok, here's the basics of the problem: User->saveAddress is given an array (of 19 elements) of $address data for $array, and the insert_id of the latest customer added to the CC_customer table for $new_user.
 
The $array contains (at least, and is correct):
 'state_id' => '13'
 'state' => 'Colorado'
 'state_abbrev' => 'CO'
 

Now, when $array is given to DB->update/insert, the respective column name in the CC_addressbook table is 'state'. A name-to-name association between the $array keys and the table column names is the database's way of handling data. So, the database will accept for the column 'state', the value in $array['state'] -- the state's name!

 

That is not what we want! Other functions require the state's id number be in this column so that the state's abbreviation can be fetched and used for shipping and payment gateways.

 

So, in /classes/user.class.php, near line 751, find:

$array['postcode'] = strtoupper($array['postcode']); // e.g. ab12 34cd to  AB12 34CD

Add above it:

$array['state'] = $array['state_id'];

A better solution (but hideously complicated in the consequences) would be to rename CC_addressbook 'state' to 'state_id'.

[bsmither]

Of course, full testing needs to be done so that this edit doesn't break anything elsewhere. Specifically, >this edit.

[Dirty Butter]

That worked!! I knew you would find the issue, Bsmither! (And yes, Ian, I do know to test with a stock skin. It was doing the exact same thing.)

 

This is definitely a CubeCart bug. Hopefully Al will see this, so it can be fixed in the next update.

[Dirty Butter]

Of course, full testing needs to be done so that this edit doesn't break anything elsewhere. Specifically, >this edit.

 

Did THIS edit make the edit referred to above unnecessary? Is there anything I can do to help to be sure the edit in this thread does not have unintended consequences elsewhere?

[bsmither]

"Did THIS edit make the edit referred to above unnecessary?"

 

No. I think that other edit is still necessary.

 

Is there anything I can do to help to be sure the edit in this thread does not have unintended consequences elsewhere?

 

Pay close attention. Pay very close attention.

[bsmither]

Also, having newly registered through checkout ("I want to register" checkbox), and having provided a billing address (and delivery address if entered), CubeCart is supposed to flag this billing address (and delivery address) as the default billing address ('billing' column) and default delivery address ('default' column).

 

Assuming you have not yet entered the customer account address book to update anything...

 

Verify: Check the values in the 'billing' and 'default' columns of the records (there will be two) for the newly created customer. Please report what you see in these two columns for these two records. (The 'description' should say "Default billing address" and "Default delivery address".)

[Dirty Butter]

It DOES say Default billing address and Default delivery address in the addressbook. If the customer adds an additional shipping address, they must provide a description of that alternate address, such as Home, and that's what ends up in the addressbook description for that alternate address.

 

I'll test by changing the default billing address as a customer and see what happens in the addressbook.

[bsmither]

That's not what I asked.

 

Verify: Check the values in the 'billing' and 'default' columns of the records. Please report what you see only in these columns.

 

Assuming you have not yet entered the customer account address book to update anything...

 

Which you just now have.

[Dirty Butter]

I had checked the addressbook to see what was in those columns immediately after creating the account - BEFORE I looked in the Customer's Address Book. Everything was as it should be in the database.

 

BUT, I just added another address by logging in as the customer, going to the Customer's Address Book, and changing the new address to the billing address - and the database did NOT record anything in the state column.

[bsmither]

"Everything was as it should be in the database."

 

In my database:

 

* the "Default billing address" record shows 0 (I know it is a string) in the 'billing' column, and shows nothing (I know it is a zero-length string) in the 'default' column.

 

* the "Default delivery address" record shows nothing (I know it is a zero-length string) in the 'billing' column, and shows nothing (I know it is a zero-length string) in the 'default' column.

 

(This is probably because of something I did to (supposedly) fix a different bug. I will undo that edit and watch for evidence of why I made that edit.)

 

And, now, you say having the customer make edits to an Addressbook address results in nothing showing in the 'state' column?

[Dirty Butter]

I'm sorry, but I'm just now realizing what you're trying to find out from me. On the most recent customer I created, without going back to the Address Book to make any changes as a customer -

 

For the Default Billing Address.

The billing column in _addressbook has a 1.

The default column has a 0.

 

For the Default Delivery Address

Both billing and default columns have 0.

 

And, sadly, yes - when the customer edited the Address Book to change the Billing or Shipping Address - it did not record the state at all.

[bsmither]

Ok, that's a valid consequence. The form is returning the state's id number in the array element ['state']. That's what we need.

 

So, what did the checkout() function do that's different compared to the addressbook() function?

 

checkout():

CubeCart->_basket['billing_address']['state_id'] = $_POST['billing']['state']

$User->saveAddress($this->_basket['billing_address'])

 

addressbook():

$User->saveAddress($_POST)

 

Then, finally,

 

saveAddress(): (new line)

$array['state'] = $array['state_id'];

 

There is no ['state_id'] available from the addressbook form. (Maybe I can add one.) As such, ['state'] is being over-written by nothing.

 

Getting back to work on this. Stay Tuned.

 

 

As an aside... Dirty Butter, your assistance and willingness to experiment is very much appreciated.

[bsmither]

Well, a hack to fix a hack.

 

In /classes/cubecart.class.php, near line 374, find:

if (isset($_POST['save'])) {
  if(empty($_POST['description'])) {
    $_POST['description'] = $GLOBALS['language']->account['billing_address'];
  }
  // Update address data
  if($GLOBALS['user']->saveAddress($_POST)) {

Just before the line, // Update address data, add:

  $_POST['state_id'] = ( is_numeric($_POST['state']) ) ? $_POST['state'] : getStateFormat($_POST['state'],'name','id') ;

[Dirty Butter]

Created a new address, check marked it as being the new Billing Address. Database billing column changed to 1 on the new address in _addressbook, and old Default billing address now has 0 in billing. Added an additional Delivery Address. States are showing properly on all the available addresses.

 

Glad to help with testing! We've had 4 orders process correctly since your edit last night!

[Al Brookbanks]

I've been having a look over this and its all pretty complex. I think the problem comes down to lack of defaults and standards prior to developing v5 (and previous versions). I'm going to leave it as it is for now and overhaul it completely for v6. Numbers in the DB are a bad idea. What happens when a state or country gets deleted?

 

They need to be set as full state name and full country name and the upgrade script to v6 needs to update existing values. 

[Toucan Web Design]

Could always do both, have the number and a fallback string for if it can't find the reference.

 

I can think of a few reasons to potentiall ywant a foreign key for it. One would be if you were deepening the language support and wanted to be able to provide country/county selections tailored to the customers selected language, which wouldn't be too bad an idea, though certainly not in any way integral to anything

 

Also if you've linked to an order via the country, you might need to get a list of relevant states/counties, which you wouldn't be able to do with surety if the country was text (and had been changed since), also not exactly the most common of occurences really, short of editing orders via the admin

 

Finally, you'll be able to actually reference orders properly by country for the purposes of sales statistics and queries, which if they end up having potentially changed country names ever so often, wouldn't be plausible

[Al Brookbanks]

I see your views. Thanks for that.