Testing 'Custom XML Page URL' gets error

[hannahwoodward]

Unsure if this is related to https://github.com/FosterCommerce/shipstation-connect/issues/11, but the client had reported a 500 error when trying to import the orders from Craft. On testing this endpoint - https://website.com/actions/shipstationconnect/orders/process?action=export

I get the following response: {"error":"DOMDocument::createCDATASection() expects parameter 1 to be string, array given"}

I then wondered if I needed to manually set the store name in the url, but then testing https://website.com/actions/shipstationconnect/orders/process?store=default&action=export

gets the following response: {"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`"}

[johnnynotsolucky]

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

[hannahwoodward]

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.

[sjcallender]

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.

[sjcallender]

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.

[hannahwoodward]

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) - https://github.com/FosterCommerce/shipstation-connect/blob/67e0b6c1cf2612a88439fcf651a4adb5467c110d/src/controllers/OrdersController.php#L118

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 https://github.com/FosterCommerce/shipstation-connect/blob/67e0b6c1cf2612a88439fcf651a4adb5467c110d/src/services/Xml.php#L299

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?

[johnnynotsolucky]

@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>