Testing 'Custom XML Page URL' gets error about shipstation-connect HOT 6 CLOSED

Hey @johnnynotsolucky apologies for the mega late reply here! I've added in the matrix field & selected it in the plugin settings matrix field handle, however the same responses are still occurring as before.

from shipstation-connect.

@hannahwoodward this is more than likely related to #11. I'll reopen if #11 doesn't resolve this issue.

from shipstation-connect.

Chatted with @hannahwoodward and we tried adding more query parameters: store=calyx&SS-UserName=******&SS-Password=******&action=export&start_date=08/29/2019+09:37&end_date=12/12/2019+13:30&page=1 but the error is the same as noted above.

The site was originally built with Craft 3.0.0-RC5 and has been updated to 3.3.x since. My hunch is that there was a failed migration along the way, probably to 3.1.x, that messed up the db schema.

Hannah is going to send a database over so we can review.

from shipstation-connect.

Scratch that. The first error:

{"error":"DOMDocument::createCDATASection() expects parameter 1 to be string, array given"}

is showing when there are orders to return. After diving into the code with Hannah to see specifically where it breaks, Hannah found the offending section. She'll post here with those details.

from shipstation-connect.

Had another call with Stephen and had a look into the plugin code.

There seem to be 2 issues:

1. Commenting out this line fixes the SQL error (repeated below) -

   shipstation-connect/src/controllers/OrdersController.php

   Line 118 in 67e0b6c

   $query->andWhere(["field_${storeFieldHandle}" => $store]);

As a reference, the relevant variables & their values:

$storeFieldHandle = '';
$store = 'default';

Manually setting $storeFieldHandle to default still causes the error.

Error:

{
    "error": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_' in 'where clause'\nThe SQL being executed was: SELECT COUNT(*)\nFROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`\nFROM `elements` `elements`\nINNER JOIN `commerce_orders` `commerce_orders` ON `commerce_orders`.`id` = `elements`.`id`\nINNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`\nINNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)\nWHERE (`commerce_orders`.`isCompleted`=TRUE) AND (`field_`='default') AND (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)) `subquery`\nINNER JOIN `commerce_orders` `commerce_orders` ON `commerce_orders`.`id` = `subquery`.`elementsId`\nINNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`\nINNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`\nINNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`"
}

2. As Stephen mentioned - we managed to narrow down the CDATA error to

   shipstation-connect/src/services/Xml.php

   Line 299 in 67e0b6c

   $this->addChildWithCDATA($option_xml, 'Value', $value);

Dumping $options from the options() function parameter gives:

[
    'uniqid' => '_qwt4o15gr'
    'customFields' => []
]

Which makes sense as that's what the error is reporting. I had a look at the site's code to find the origin of 'customFields' in the line item options, and it looks like we set this in a custom Craft module. Having a glance in the DB, some of the line items have this populated, e.g.

{"customFields":{"boxQuantity":"50","unitPrice":2}}

Is this something you'd be able to support in the plugin?

from shipstation-connect.

@hannahwoodward thank you for digging into this. I've published v1.2.5 which includes an update to serialize any array or object values in the line item options field to JSON.

There's also extra logic to limit the number of options per item to 10 which is set by ShipStation.

For example, for the following line item options:

{
  "Option A": "Option A Value",
  "Option B": "Option B Value",
  "Object A": {
    "Prop A": "A Value",
    "Prop B": "B Value"
  },
  "A": "Included",
  "B": "Included",
  "C": "Included",
  "D": "Included",
  "E": "Included",
  "F": "Included",
  "G": "Included",
  "H": "Excluded",
  "I": "Excluded",
  "J": "Excluded",
  "K": "Excluded",
  "L": "Excluded",
  "M": "Excluded",
  "N": "Excluded"
}

The export to ShipStation would be

<Options>
    <Option>
	<Name>Option A</Name>
	<Value>
	    <![CDATA["Option A Value"]]>
	</Value>
    </Option>
    <Option>
	<Name>Option B</Name>
	<Value>
	    <![CDATA["Option B Value"]]>
	</Value>
    </Option>
    <Option>
	<Name>Object A</Name>
	<Value>
	    <![CDATA[{"Prop A":"A Value","Prop B":"B Value"}]]>
	</Value>
    </Option>
    <Option>
	<Name>A</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>B</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>C</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>D</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>E</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>F</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
    <Option>
	<Name>G</Name>
	<Value>
	    <![CDATA["Included"]]>
	</Value>
    </Option>
</Options>

from shipstation-connect.