adb34:
OK silly question time:
I want to change the category that a lot of my products are in, so, if I use this plugin to download all my products, change the category of the products then upload. The question is will I duplicate the products in the different categories or will the category be over written by the new category?
Told you it is a silly question! :blush:
mc12345678:
Actually, not as silly as you may think. Normally this will create a linked product (duplicate but not so much a duplicate as it maintains the same products_id).
There currently is no "remove a product from a category" type feature... If a product being processed on import is in the category "assigned", then nothing is done, if it is not in the category that is assigned/determined in the process, then the product is added to the category (if the category didn't exist before, then it is created before this addition).
Removal from a category is somewhat of a two step process. The product's master_categories_id in the products table needs to reflect an existing category and the table products_to_categories needs to have the entry for the products_id removed for the category from which it is leaving and if not already associated with the new category an entry made... I did not find any code that did a delete on the products_to_categories table on import and there is nothing that specifically modifies an existing master_categories_id entry other than a product update with master_categories_id as a user defined field. The only other instance of affecting the master_categories_id is upon initial insert of a product as it must have/maintain a master_categories_id assigned.
So, removal of a product from one category (ie part of a process to move a product from one category to another) is not yet in the code... Perhaps can come up with something like another status switch that would "reassign" the master_categories_id to the chosen category and remove the linked product from the products_to_categories table in relation to the previous master category (status of 9 deletes a product, perhaps a different number could be used to do a move). It would not directly resolve issues associated with other linked product, but it would support a move action... Technically if the product were moved through each respective linked category, then there would remain but one category associated with the product (this could be accomplished in a single file upload where each line of the file had the product moved to each linked category and then a final move to the desired end category...)
Perhaps if considered further necessary (large issue with linked product) an additional option (dropdown, link, whatever) to address just bulk linked product changes would help...
I would suggest though in the interim not counting on the above to happen/be implemented quickly... Work on this is generally a free time dependent project, though now that a path is seen, it may not be as long as originally anticipated and there are other factors that could make it go faster. :)
Below is the code described above that I have prepared... I keep looking at it to ensure that each possibility is addressed and while I see that some conditions seem to be over described, I didn't want to eliminate the potential code needed to address the possibility of that condition. Would appreciate any operational testing feedback.. I've set up some tests that seem to have worked but could use some independent verification.
So beginning in EP4 version 4.0.29 the following code begins at line 1508. The code begins at other locations for earlier versions but until now has remained relatively consistent code.
The expectation of the below code is that if the status of a product is set to 7 (instead of the typical 0 to disable, 1 to enable, or 9 to delete), then the category of that row will become the master category for that item and the product will be removed from the previous master category. With the appropriate series of applicable add and move database entries a product's linked categories can be stripped, the master category revised to the desired category.
I chose the number 7 to be non-adjacent to the number 9, but I also am concerned that for those using a number pad without looking at the pad that they may incorrectly enter 9 instead of 7; however, also hope that the difference in shape (sharp corners (7) versus rounded (9) or what is likely to be a majority (1 or 0) will stand out a little..) Could use a two+ digit status indicator or maybe a "word", but I'm going with what I got at the moment.
Find:
//==================================================================================================================================
// Assign product to category if linked
// chadd - need to dig into further
if (isset($v_categories_id)) { // find out if this product is listed in the category given
$result_incategory = ep_4_query('SELECT
'.TABLE_PRODUCTS_TO_CATEGORIES.'.products_id,
'.TABLE_PRODUCTS_TO_CATEGORIES.'.categories_id
FROM
'.TABLE_PRODUCTS_TO_CATEGORIES.'
WHERE
'.TABLE_PRODUCTS_TO_CATEGORIES.'.products_id='.$v_products_id.' AND
'.TABLE_PRODUCTS_TO_CATEGORIES.'.categories_id='.$v_categories_id);
if (($ep_uses_mysqli ? mysqli_num_rows($result_incategory) : mysql_num_rows($result_incategory)) == 0) { // nope, this is a new category for this product
$res1 = ep_4_query('INSERT INTO '.TABLE_PRODUCTS_TO_CATEGORIES.' (products_id, categories_id)
VALUES ("'.$v_products_id.'", "'.$v_categories_id.'")');
if ($res1) {
zen_record_admin_activity('Product ' . (int)$v_products_id . ' copied as link to category ' . (int)$v_categories_id . ' via EP4.', 'info');
}
} else { // already in this category, nothing to do!
}
}
//==================================================================================================================================
And replace with:
//==================================================================================================================================
// Assign product to category if linked
// chadd - need to dig into further
if (isset($v_categories_id)) { // find out if this product is listed in the category given
$result_incategory = ep_4_query('SELECT
'.TABLE_PRODUCTS_TO_CATEGORIES.'.products_id,
'.TABLE_PRODUCTS_TO_CATEGORIES.'.categories_id,
'.TABLE_PRODUCTS.'.master_categories_id
FROM
'.TABLE_PRODUCTS.'
LEFT JOIN
'.TABLE_PRODUCTS_TO_CATEGORIES.' ON ('.TABLE_PRODUCTS.'.products_id = '.TABLE_PRODUCTS_TO_CATEGORIES.'.products_id AND '.TABLE_PRODUCTS_TO_CATEGORIES.'.categories_id='.$v_categories_id.')
WHERE
'.TABLE_PRODUCTS.'.products_id='.$v_products_id);
$result_incategory = ($ep_uses_mysqli ? mysqli_fetch_array($result_incategory) : mysql_fetch_array($result_incategory));
if (!zen_not_null($result_incategory['products_id']) || sizeof($result_incategory) <= 0 /* ($ep_uses_mysqli ? mysqli_num_rows($result_incategory) : mysql_num_rows($result_incategory)) == 0 */) { // nope, this is a new category for this product
if ($items[$filelayout['v_status']] == 7) {
/* $result_incategory = ep_4_query('SELECT
'.TABLE_PRODUCTS.'.master_categories_id
FROM
'.TABLE_PRODUCTS.'
WHERE
'.TABLE_PRODUCTS.'.products_id='.$v_products_id);
$result_incategory = ($ep_uses_mysqli ? mysqli_fetch_array($result_incategory) : mysql_fetch_array($result_incategory)); */
//do category move action.
// if the master_categories_id != categories_id, then for "safety" sake, should successfully insert the product to the category before deleting it from the previous category. Should also verify that the master_categories_id is set, because if it is not then there is a bigger issue. As part of the verification, if it is not set, then don't try to delete the previous, just add it and provide equivalent information.
if ((int) $result_incategory['master_categories_id'] != (int) $v_categories_id) {
//move is eminent
if ($result_incategory['master_categories_id'] > 0) {
//master_category is assigned to the product do the move.
// Ensure the product still is on the way to the move and available.
$res1 = ep_4_query('INSERT INTO ' . TABLE_PRODUCTS_TO_CATEGORIES . ' (products_id, categories_id)
VALUES (' . $v_products_id . ', ' . $v_categories_id . ')');
if ($res1) {
//Successfully inserted the product to the category, now need to remove the link from the previous category.
$res1 = ep_4_query('UPDATE ' . TABLE_PRODUCTS . ' set master_categories_id = ' . $v_categories_id . '
WHERE products_id = ' . $v_products_id);
$res2 = ep_4_query('DELETE FROM ' . TABLE_PRODUCTS_TO_CATEGORIES . ' WHERE products_id = ' . $v_products_id . ' AND categories_id =
' . $result_incategory['master_categories_id']);
if ($res1 && $res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' moved to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else if ($res1 && !$res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' master_categories_id changed to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else if (!$res1 && $res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' deleted from category ' . (int) $result_incategory['master_categories_id'] . ' and added to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else if (!$res1 && !$res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' copied as link to category ' . (int) $v_categories_id . ' via EP4.', 'info');
}
}
} /* EOF Master Category is set */ else {
//master_category is not assigned, assign the category to the master category and do the move.
$res1 = ep_4_query('INSERT INTO ' . TABLE_PRODUCTS_TO_CATEGORIES . ' (products_id, categories_id)
VALUES (' . $v_products_id . ', ' . $v_categories_id . ')');
if ($res1) {
//Successfully inserted the product to the category, now need to remove the link from the previous category.
$res1 = ep_4_query('UPDATE ' . TABLE_PRODUCTS . ' set master_categories_id = ' . $v_categories_id . '
WHERE products_id = ' . $v_products_id);
if ($res1) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' moved to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' copied as link to category ' . (int) $v_categories_id . ' via EP4.', 'info');
}
}
} // EOF Master category is not defined.
} // End if master and category different, nothing else to do as no where to go because both are the same...
} /* EOF status == Move */ else {
$res1 = ep_4_query('INSERT INTO ' . TABLE_PRODUCTS_TO_CATEGORIES . ' (products_id, categories_id)
VALUES (' . $v_products_id . ', ' . $v_categories_id . ')');
if ($res1) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' copied as link to category ' . (int) $v_categories_id . ' via EP4.', 'info');
}
} // Don't move the product
} else { // already in this category, nothing to do! // Though may need to do the move action so there is still possibly something to do...
if ($items[$filelayout['v_status']] == 7) {
// $result_incategory = ($ep_uses_mysqli ? mysqli_fetch_array($result_incategory) : mysql_fetch_array($result_incategory));
//do category move action.
// if the master_categories_id != categories_id, then for "safety" sake, should successfully insert the product to the category before deleting it from the previous category. Should also verify that the master_categories_id is set, because if it is not then there is a bigger issue. As part of the verification, if it is not set, then don't try to delete the previous, just add it and provide equivalent information.
if ($result_incategory['master_categories_id'] != $result_incategory['categories_id']) {
//move is eminent
if ($result_incategory['master_categories_id'] > 0) {
//master_category is assigned to the product complete the move.
// Ensure the product still is on the way to the move and available.
// $res1 = ep_4_query('INSERT INTO '.TABLE_PRODUCTS_TO_CATEGORIES.' (products_id, categories_id)
// VALUES ("'.$v_products_id.'", "'.$v_categories_id.'")');
$res1 = ep_4_query('UPDATE ' . TABLE_PRODUCTS . ' set master_categories_id = ' . $v_categories_id . '
WHERE products_id = ' . $v_products_id);
/* if ($res1) */
//Successfully updated the product to the category, now need to remove the link from the previous category.
$res2 = ep_4_query('DELETE FROM ' . TABLE_PRODUCTS_TO_CATEGORIES . ' WHERE products_id = ' . $v_products_id . ' AND categories_id =
' . $result_incategory['master_categories_id']);
if ($res1 && $res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' moved from category ' . (int)$result_incategory['master_categories_id'] . ' to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else if ($res1 && !$res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' master_categories_id changed from category ' . (int)$result_incategory['master_categories_id'] . ' to category ' . (int) $v_categories_id . ' via EP4.', 'info');
} else if (!$res1 && $res2) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' deleted from category ' . (int) $result_incategory['master_categories_id'] . ' and added to category ' . (int) $v_categories_id . ' via EP4.', 'info');
}
} else {
//master_category is not assigned, assign the category to the master category and do the move.
// $res1 = ep_4_query('INSERT INTO '.TABLE_PRODUCTS_TO_CATEGORIES.' (products_id, categories_id)
// VALUES ("'.$v_products_id.'", "'.$v_categories_id.'")');
//Successfully inserted the product to the category, now need to remove the link from the previous category.
$res1 = ep_4_query('UPDATE ' . TABLE_PRODUCTS . ' set master_categories_id = ' . $v_categories_id . '
WHERE products_id = ' . $v_products_id);
if ($res1) {
zen_record_admin_activity('Product ' . (int) $v_products_id . ' master_categories_id established as category ' . (int) $v_categories_id . ' via EP4.', 'info');
}
}
} // End if master and category different, nothing else to do as no where to go because both are the same...
//continue;
}
}
}
//==================================================================================================================================
This is expected to be included in the upcoming 4.0.31 code...
