cefyn:
Hi MC , fancy meeting you here . Zen Cart 1.5.4 ,yesterdays EP4 update . SBA 1.5.4 .When I import SBA-Stock-EP2015 whether edited in office libre or excel I get this error on every product.
MySQLi error 1062: Duplicate entry '' for key 'idx_customid'
When executing:
UPDATE products_with_attributes_stock SET
quantity = 1, customid = ''
WHERE (
stock_id = 122 )
Perhaps you could help an inaccurate and unreliable operator to get back on the right track. Sorry, had to laugh at your comment on the SBA thread , I probably have made a stupid mistake somewhere.
Liked that, huh? :)
So, the obvious solution is to provide a unique customid for each and every product variant... But if that were the only solution then how could the SBA plugin work without having a unique customid for each and every variant?
Couple of things going on here, I just had to figure out a good way to resolve it... See, the customid is designated as a unique key in the table definition for SBA, which means that every customid (that has data) is supposed to be different from every other customid. But unique keys (different than a primary key) are supposed to also be allowed the value NULL...
Okay, so it could be set to null... But then there is the issue that some servers treat '' as null or reject null expecting ''... So sure could omit the customid from the update if it is not provided, but then what if it were being reset to blank/NULL? Still needs to get updated to blank/NULL without losing the other properties of the entry...
Why does it seem that this issue has "snuck in"? When this feature was initially developed, I was advised that there would be a unique customid applied to each and every product (and was provided an example file dataset to match), so.. Naturally when I was testing it I followed suit and had a unique customid applied to each such product... No issues, right? Well, no one till now had pointed out the flaw which was that the case of populating the SBA database with stock quantities failed for product that did not have a customid (same thing will happen if it does have a customid but it is not unique compared to all other customid entries). Not sure I totally agree with the later requirement, but seems there was a need for it so I don't want to go mess that up...
So, I could upload a revision 4.0.31a to fix the issue above... It's not so significant to warrant a complete revision number change I think, just a bug fix... or I could provide the below code fix.. :) The problem is seen in both the stock and the detailed imports, so corrections are provided for both below.
Now, as for sorting by name.... :) If going to add that as a feature, then probably should consider all the methods of sorting, or provide a way/instruction on how to setup the export of the SBA file to be sorted by name... (to make it quicker)... Afterall, the stock export is all about doing an inventory in a store that uses SBA, whereas the detailed SBA export is all about the SBA only aspect of the data... For some both data dumps are relatively the same, but for others that use SBA to "highlight" some product and not on all product, then the detailed SBA data relatively speaking will have far fewer rows than the sba stock dump... (Yes, I know that it is possible to have many many variants for a single product causing this "number of rows" discussion to be way off...)
For the detailed import fix, find in admin/easypopulate_4_import.php approximately line 397:
$sql = $db->bindVars($sql, ':customid:', $items[$filelayout['v_customid']], 'string');
and replace with:
$sql = $db->bindVars($sql, ':customid:', (zen_not_null($items[$filelayout['v_customid']]) ? $items[$filelayout['v_customid']] : 'NULL'), (zen_not_null($items[$filelayout['v_customid']]) ? 'string' : 'passthru'));
For the stock import fix, find in admin/easypopulate_4_import.php approximately line 494:
} elseif ($items[(int)$filelayout['v_SBA_tracked']] == "X") {
Replace:
} elseif ($items[(int) $filelayout['v_SBA_tracked']] == "X") {
$sql = "UPDATE " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " SET
quantity = " . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? ", customid = '" . $items[(string) $filelayout['v_customid']] . "' " : "") . ") . "
WHERE (
stock_id = " . $items[$filelayout['v_table_tracker']] . " )";
if ($result = ep_4_query($sql)) {
zen_record_admin_activity('Updated products with attributes stock ' . (int) $items[$filelayout['v_table_tracker']] . ' via EP4.', 'info');
$display_output .= sprintf(EASYPOPULATE_4_DISPLAY_RESULT_UPDATE_PRODUCT, $items([int) $filelayout['v_products_model']]) . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? " " . $items[(string) $filelayout['v_customid']] : "");
$ep_update_count++;
if ($sync) {
$stock->update_parent_products_stock((int) $query[$items[(int) $filelayout['v_products_model']]][(int) $filelayout['v_table_tracker']]);
// $messageStack->add_session('Parent Product Quantity Updated', 'success');
unset($query[$items[(int) $filelayout['v_products_model']]]);
}
} else { // error Attribute entry not found - needs work!
$display_output .= sprintf('<br /><font color="red"><b>SKIPPED! - SBA Tracked Quantity ' . ($ep_4_SBAEnabled == '2' ? 'and CustomID ' : '') . 'on Model: </b>%s - Not Found!</font>', $items[(int) $filelayout['v_products_model']]);
$ep_error_count++;
} // if
} //end if Standard / SBA stock
With:
} elseif ($items[(int) $filelayout['v_SBA_tracked']] == "X") {
$sql = "UPDATE " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " SET
quantity = " . $items[(int) $filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? ", customid = :customid: " : "") . "
WHERE (
stock_id = " . $items[$filelayout['v_table_tracker']] . " )";
$sql = $db->bindVars($sql, ':customid:', (zen_not_null($items[$filelayout['v_customid']]) ? $items[ $filelayout['v_customid']] : 'NULL'), (zen_not_null($items[$filelayout['v_customid']]) ? 'string' : 'passthru'));
if ($result = ep_4_query($sql)) {
zen_record_admin_activity('Updated products with attributes stock ' . (int) $items[$filelayout['v_table_tracker']] . ' via EP4.', 'info');
$display_output .= sprintf(EASYPOPULATE_4_DISPLAY_RESULT_UPDATE_PRODUCT, $items[$filelayout['v_products_model']]) . $items[$filelayout['v_products_quantity']] . ($ep_4_SBAEnabled == '2' ? " " . $items[$filelayout['v_customid']] : "");
$ep_update_count++;
if ($sync) {
$stock->update_parent_products_stock((int) $query[$items[(int) $filelayout['v_products_model']]][(int) $filelayout['v_table_tracker']]);
// $messageStack->add_session('Parent Product Quantity Updated', 'success');
unset($query[$items[(int) $filelayout['v_products_model']]]);
}
} else { // error Attribute entry not found - needs work!
$display_output .= sprintf('<br /><font color="red"><b>SKIPPED! - SBA Tracked Quantity ' . ($ep_4_SBAEnabled == '2' ? 'and CustomID ' : '') . 'on Model: </b>%s - Not Found!</font>', $items[(int) $filelayout['v_products_model']]);
$ep_error_count++;
} // if
} //end if Standard / SBA stock
