1_debug_update_openmage_weight.sql

-- DEBUG FOR ONE ORDER AND ONE SHIPMENT
-- RUN THIS BEFORE AND AFTER AND COMPARE

SELECT increment_id, weight
FROM sales_flat_order;

SELECT sfoi.product_type, sfoi.item_id, sfoi.parent_item_id, sfoi.product_id, CAST(sfoi.qty_ordered AS FLOAT) AS qty,
 CAST(sfoi.row_weight AS FLOAT) AS order_row_weight, CAST(sfoi.weight AS FLOAT) AS order_weight,
 sfsi.entity_id AS ship_item_id, CAST(sfsi.weight AS FLOAT) AS shipment_weight,
 CAST(cped.value AS FLOAT) AS orig_weight
FROM sales_flat_order_item sfoi
LEFT JOIN catalog_product_entity_decimal cped ON cped.entity_id = sfoi.product_id
 AND cped.attribute_id = 80 AND cped.store_id = 0
LEFT JOIN sales_flat_shipment_item sfsi ON sfsi.order_item_id = sfoi.item_id
ORDER BY sfoi.item_id DESC;

-- GLOBAL DEBUG

SELECT product_id, weight
FROM sales_flat_shipment_item
GROUP BY product_id, weight
ORDER BY product_id DESC;

SELECT product_id, product_type, weight
FROM sales_flat_order_item
GROUP BY product_id, weight
ORDER BY product_id DESC;

2_update_openmage_weight.php

<?php

// https://gist.github.com/luigifab/8acd4ef47e9defc976077f5dcf8569fd
$this->startSetup();
$this->run('

	-- this SQL will recalculate orders and shipments weight
	-- 80 is the global weight attribute only for simple and bundle products
	-- 126 is weight_type attribute

	-- update weight_type
	-- because we are using only dynamic weight
	UPDATE '.$this->getTable('catalog_product_entity_int').' SET value = 0 WHERE attribute_id = 126;

	-- delete weight for products without weight
	-- simple+bundle if not only dynamtic weight
	DELETE FROM '.$this->getTable('catalog_product_entity_decimal').'
	WHERE
	 attribute_id = 80
	 AND entity_id NOT IN (
	  SELECT entity_id
	  FROM '.$this->getTable('catalog_product_entity').'
	  WHERE type_id IN ("simple")
	 );

	-- reset weight in sales_flat_*
	UPDATE '.$this->getTable('sales_flat_order_item').' SET weight = NULL, row_weight = NULL WHERE weight IS NOT NULL;
	UPDATE '.$this->getTable('sales_flat_order').' SET weight = NULL WHERE weight IS NOT NULL;
	UPDATE '.$this->getTable('sales_flat_shipment_item').' SET weight = NULL WHERE weight IS NOT NULL;

	-- copy/calc/sum weight from catalog_product_entity_decimal to sales_flat_order_item
	-- for simple not associated
	UPDATE '.$this->getTable('sales_flat_order_item').' sfoi
	LEFT JOIN '.$this->getTable('catalog_product_entity_decimal').' cped
	 ON  cped.entity_id = sfoi.product_id
	 AND cped.attribute_id = 80 AND cped.store_id = 0
	SET
	 sfoi.weight = cped.value,
	 sfoi.row_weight = cped.value * sfoi.qty_ordered
	WHERE sfoi.product_type = "simple" AND sfoi.parent_item_id IS NULL;

	-- copy/calc/sum weight from catalog_product_entity_decimal to sales_flat_order_item
	-- for simple associated
	UPDATE '.$this->getTable('sales_flat_order_item').' sfoi
	LEFT JOIN '.$this->getTable('catalog_product_entity_decimal').' cped
	 ON  cped.entity_id = sfoi.product_id
	 AND cped.attribute_id = 80 AND cped.store_id = 0
	SET
	 sfoi.weight = cped.value,
	 sfoi.row_weight = 0
	WHERE sfoi.product_type = "simple" AND sfoi.parent_item_id IS NOT NULL;

	-- copy/calc/sum weight from sales_flat_order_item to sales_flat_order_item
	-- for bundle and configurable
	UPDATE '.$this->getTable('sales_flat_order_item').' sfoi
	LEFT JOIN (
	 SELECT parent_item_id, SUM(weight * qty_ordered) AS sum_row_weight
	 FROM '.$this->getTable('sales_flat_order_item').'
	 GROUP BY parent_item_id
	) s ON s.parent_item_id = sfoi.item_id
	SET
	 sfoi.weight = s.sum_row_weight / sfoi.qty_ordered,
	 sfoi.row_weight = s.sum_row_weight
	WHERE sfoi.product_type IN ("bundle", "configurable");

	-- sum weight from sales_flat_order_item to sales_flat_order
	UPDATE '.$this->getTable('sales_flat_order').' sfo
	LEFT JOIN (
	 SELECT order_id, SUM(row_weight) AS sum_row_weight
	 FROM '.$this->getTable('sales_flat_order_item').'
	 GROUP BY order_id
	) s ON s.order_id = sfo.entity_id
	SET sfo.weight = s.sum_row_weight;

	-- copy weight from sales_flat_order_item to sales_flat_shipment_item
	UPDATE '.$this->getTable('sales_flat_shipment_item').' sfsi
	LEFT JOIN '.$this->getTable('sales_flat_order_item').' sfoi
	 ON sfoi.item_id = sfsi.order_item_id
	SET sfsi.weight = sfoi.weight
	WHERE sfsi.weight != sfoi.weight;

');
$this->endSetup();

3_clear_openmage_eav_values.php

<?php

// https://gist.github.com/luigifab/8acd4ef47e9defc976077f5dcf8569fd
$this->startSetup();
$this->run('
	-- remove categories attributes values in wrong entity tables

	DELETE FROM '.$this->getTable('catalog_category_entity_datetime').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 3 AND backend_type != "datetime"
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_decimal').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 3 AND backend_type != "decimal"
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_int').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 3 AND backend_type != "int"
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_text').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 3 AND backend_type != "text"
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_varchar').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 3 AND backend_type != "varchar"
	);

	-- remove products attributes values in wrong entity tables

	DELETE FROM '.$this->getTable('catalog_product_entity_datetime').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 4 AND backend_type != "datetime"
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_decimal').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 4 AND backend_type != "decimal"
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_int').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 4 AND backend_type != "int"
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_text').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 4 AND backend_type != "text"
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_varchar').'
	WHERE attribute_id IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').' WHERE entity_type_id = 4 AND backend_type != "varchar"
	);


	-- remove categories attributes values for attributes that does not exists

	DELETE FROM '.$this->getTable('catalog_category_entity_datetime').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_decimal').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_int').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_text').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_category_entity_varchar').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	-- remove products attributes values for attributes that does not exists

	DELETE FROM '.$this->getTable('catalog_product_entity_datetime').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_decimal').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_int').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_text').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);

	DELETE FROM '.$this->getTable('catalog_product_entity_varchar').'
	WHERE attribute_id NOT IN (
	 SELECT attribute_id FROM '.$this->getTable('eav_attribute').'
	);
');
$this->endSetup();