Is there a plugin for Magmi that deletes the products which are not in the CSV file?

Question

I have to add this feature and I want to know if such a plugin exists before writing one.

Answer 1

I've created a plugin that disables files not in the CSV. I prefer disabling the items, instead of actually deleting them in case something goes wrong (it won't wipe my database).

1. Create the plugin file magmi/plugins/extra/general/itemdisabler/magmi_itemdisabler_plugin.php

2. In the file, paste in the following and save:

Plugin Code:

<?php
class Magmi_ItemdisablerPlugin extends Magmi_ItemProcessor
{
    protected $datasource_skus = array();    

    public function getPluginInfo()
    {
        return array("name"=>"Magmi Magento Item Disabler",
                             "author"=>"Axel Norvell (axelnorvell.com)",
                             "version"=>"1.0.6");
    }      

    public function afterImport()
    {
        $this->log("Running Item Disabler Plugin","info");
        $this->disableItems();
        return true;
    }

    public function getPluginParams($params)
    {
        return array();
    }

    public function isRunnable()
    {
        return array(true,"");
    }

    public function initialize($params)
    {
    }

    public function processItemAfterId(&$item,$params=null)
    {
        if(isset($item['sku']))
        {
            $this->datasource_skus[] = $item['sku'];
        }
    }

    public function disableItems()
    {
        if(count($this->datasource_skus) <= 0)
        {
            $this->log('No items were found in datasource.  Item Disabler will not run.', "info");
            return false; /* Nothing to disable */  
        }

        //Setup tables
        $ea     = $prefix!=""?$prefix."eav_attribute":"eav_attribute";
        $eet     = $prefix!=""?$prefix."eav_entity_type":"eav_entity_type";
        $cpe     = $prefix!=""?$prefix."catalog_product_entity":"catalog_product_entity";
        $cpei     = $prefix!=""?$prefix."catalog_product_entity_int":"catalog_product_entity_int";

        //Get "status" attribute_id
        $status_attr_id = "     
            SELECT ea.attribute_id FROM $ea ea
            LEFT JOIN $eet eet ON ea.entity_type_id = eet.entity_type_id
            WHERE ea.attribute_code = 'status'
            AND eet.entity_type_code = 'catalog_product'";               
        $result = $this->selectAll($status_attr_id);  
        if (count($result) == 1) {
            $attribute_id = $result[0]['attribute_id'];
        }
        unset($result);

        //Get all active items
        $sql = "SELECT e.sku, e.entity_id FROM $cpei i
                          INNER JOIN $cpe e ON
                          e.entity_id = i.entity_id
                          WHERE attribute_id=?
                          AND i.value = 1";
        $all_magento_items = $this->selectAll($sql, array($attribute_id));

        //Setup the magento_skus array for easy processing.
        $magento_skus = array();
        foreach($all_magento_items as $item)
        {
            $this->log("{$item['sku']} found in Mage", "info");

            $magento_skus[$item['sku']] = $item['entity_id'];
        }


        //process the array, move anything thats in the datasource.
        foreach($this->datasource_skus as $sku)
        {
            if(isset($magento_skus[$sku]))
            {
                unset($magento_skus[$sku]);
            }
        }

        if(!empty($magento_skus))
        {               
            foreach($magento_skus as $sku => $id)
            {

                $this->log("Disabling Item Id $id with SKU: $sku", "info"); 
                $this->update("
                    UPDATE $cpei i
                    INNER JOIN $cpe e ON
                    e.entity_id = i.entity_id
                    SET VALUE = '2'
                    WHERE attribute_id = ?
                    AND i.value = 1
                    AND e.sku=?", array($attribute_id, $sku));
            }
        }
        else
        {
            //If the Datasource contains all Magento's items.
            $this->log('All items present in datasource.  No items to disable.', "info");       
        }

    }
}

Then login to Magmi, enable the plugin and run the import. This plugin will execute after the import has completed. It opens the datasource, logs all of the SKUs, then compares them against the Magento database. Any skus that aren't found in the datasource are disabled. This plugin could be optimized a bit better but it works as it is right now.

Answer 2

I would think it much better to affirmatively delete rather than delete by omission. It should be trivial to adapt the convert adapter to parse a column (e.g. "deleted") and set the _isDeleted property to true. This will cause the product to be deleted on save.

Ref Mage_Core_Model_Abstract and Varien_Object.

Answer 3

Here's a Magmi plugin which does exactly what you are looking for imo: http://www.emvee-solutions.com/blog/magmi-delete-disable-products-missing-csv-source/

Answer 4

I have tried it with the built in product deleter but it didn't work quite as expected. It deletes the old products but when it adds them again it increases the article number, so after a few imports you have article number 1xxxxx.

If I have only products imported from a CSV and no products which are "always" there I actually use a php script before magmi to delete all the products:

    <?php

    $mysqli = new mysqli("localhost", "dbuser", "dbpassword", "db");

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }



    $query = "

    SET FOREIGN_KEY_CHECKS = 0;

    TRUNCATE TABLE `catalog_product_bundle_price_index`;
    TRUNCATE TABLE `catalog_product_bundle_selection`;
    TRUNCATE TABLE `catalog_product_bundle_selection_price`;
    TRUNCATE TABLE `catalog_product_bundle_option_value`;
    TRUNCATE TABLE `catalog_product_bundle_option`;
    TRUNCATE TABLE `catalog_product_entity_datetime`;
    TRUNCATE TABLE `catalog_product_entity_decimal`;
    TRUNCATE TABLE `catalog_product_entity_gallery`;
    TRUNCATE TABLE `catalog_product_entity_group_price`;
    TRUNCATE TABLE `catalog_product_entity_int`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
    TRUNCATE TABLE `catalog_product_entity_text`;
    TRUNCATE TABLE `catalog_product_entity_tier_price`;
    TRUNCATE TABLE `catalog_product_entity_varchar`;
    TRUNCATE TABLE `catalog_product_flat_1`;
    TRUNCATE TABLE `catalog_product_link`;
    TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
    TRUNCATE TABLE `catalog_product_link_attribute_int`;
    TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
    TRUNCATE TABLE `catalog_product_option`;
    TRUNCATE TABLE `catalog_product_option_price`;
    TRUNCATE TABLE `catalog_product_option_title`;
    TRUNCATE TABLE `catalog_product_option_type_price`;
    TRUNCATE TABLE `catalog_product_option_type_title`;
    TRUNCATE TABLE `catalog_product_option_type_value`;
    TRUNCATE TABLE `catalog_product_super_attribute_label`;
    TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
    TRUNCATE TABLE `catalog_product_super_attribute`;
    TRUNCATE TABLE `catalog_product_super_link`;
    TRUNCATE TABLE `catalog_product_enabled_index`;
    TRUNCATE TABLE `catalog_product_website`;
    TRUNCATE TABLE `catalog_category_product_index`;
    TRUNCATE TABLE `catalog_product_index_price`;
    TRUNCATE TABLE `catalog_product_index_eav`;
    TRUNCATE TABLE `catalog_category_product`;
    TRUNCATE TABLE `catalog_product_index_eav_idx`;
    TRUNCATE TABLE `catalog_product_entity`;
    TRUNCATE TABLE `catalog_product_relation`;
    TRUNCATE TABLE `catalog_product_index_price_idx`;
    TRUNCATE TABLE `catalog_product_index_website`;
    TRUNCATE TABLE `cataloginventory_stock_item`;
    TRUNCATE TABLE `cataloginventory_stock_status`;
    TRUNCATE TABLE  `core_url_rewrite`;

    SET FOREIGN_KEY_CHECKS = 1; 
    ";

   $results = mysqli_multi_query($mysqli,$query);

   $mysqli->close();


   ?>

Hope that helps.

Answer 5

I thought I would mention that people might run into issues if they have a really large list of products and they're trying to run this disabler. I'm not super familiar with magmi, but it seems more errors seem to show when using cli, so try testing with that if you're noticing the disbling isn't working. It wasn't working for me and I narrowed it down to php memory restrictions. I had to change the script to include ini_set('memory_limit','512M'); in the disableItems function.

So, mine now looks like this....

public function disableItems()
{
    ini_set('max_execution_time', 0);
    set_time_limit(0);
    ini_set('memory_limit','512M');
    umask(0); 

I hope that helps anyone if they're having issues with this at all. I also had to set mine to run as long as it takes and everything too. It might be best to set this in your configuration files, but this is a quick resolution, especially if you're on a shared host.