EasyPopulate 4.0 Support Thread

[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.

[mc12345678]

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

[cefyn]

That's fantastic MC , the EP4 export/import SBA facility works perfectly with these edits. Thank you so much for the time and trouble you have taken to help me out.

[mc12345678]

That's fantastic MC , the EP4 export/import SBA facility works perfectly with these edits. Thank you so much for the time and trouble you have taken to help me out.

Absolutely no problem, it's a shame that anything had to be done at all as that feature has been available for quite some time now and no one has commented about the issues involved.. Doesn't have to be a big production, and as have seen I only discovered the issue when it was commented about on some other forum... :)

I do recall that at the time of development there was a bit of a clock ticking and only some of the specific functionality was requested (meaning anything extra was a "bonus" but not a development requirement). I do know that a basic import/export will eventually follow; however, I've been wanting to wait until SBA structure and building is settled/finalized... It's expected to be somewhat complicated (meaning lot's of code to put together/write) and I'd rather write it one time and be done not having to revise it significantly because of significant changes in the SBA code/processing/structure.

For anyone reading, hopefully can see how easy it can be to have an issue corrected with proper information, methods of duplicating the issue and posing the question(s)/issue(s) to the proper location...