MySQL: Selhávání zápisu dat při hromadném zpracování

DROP PROCEDURE `SetPriceStock`;
CREATE DEFINER=`a55686_vonaj`@`%` PROCEDURE `SetPriceStock`(IN `p_reference` INT(11), IN `p_wholesaleprice` DECIMAL(20,6), IN `p_margin` DECIMAL(20,6), IN `p_quantity` INT(10), IN `p_idshop` INT(11), IN `p_idshopgroup` INT(11), IN `p_outofstock` INT(2)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
BEGIN
  DECLARE new_price DECIMAL(20,6) DEFAULT 0;
  DECLARE price_old DECIMAL(20,6) DEFAULT 0;
  DECLARE idproduct INT(11) DEFAULT 0;
  DECLARE idcombination INT(11) DEFAULT 0;   
  DECLARE total_quantity INT(10) DEFAULT 0;
  DECLARE Y_idproduct INT(11) DEFAULT 0;
  DECLARE Y_idcombination INT(11) DEFAULT 0;
  DECLARE Y_total_quantity INT(10) DEFAULT 0;  
  DECLARE change_percent INT(3) DEFAULT 0; -- změna ceny vyjádřená v procentech --
  DECLARE has_duplicate BOOLEAN DEFAULT FALSE;
  DECLARE quantity_exists BOOLEAN DEFAULT FALSE;
  DECLARE total_quantity_exists BOOLEAN DEFAULT FALSE;
  DECLARE activate TINYINT(1) DEFAULT 0;
  DECLARE success BOOLEAN DEFAULT FALSE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
      GET DIAGNOSTICS CONDITION 1 @msg= MESSAGE_TEXT;
      SELECT @msg AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
    END;                                
    
  SET new_price = ((p_wholesaleprice / 100) * p_margin) + p_wholesaleprice;  -- připočtení marže --
  
  IF p_quantity > 0 THEN
    SET activate = 1;
  END IF;
  
  SELECT id_product_attribute, id_product, price, COUNT(*) > 1 INTO idcombination, idproduct, price_old, has_duplicate FROM `ps_product_attribute` WHERE reference=p_reference;
  SELECT id_product_attribute, id_product INTO Y_idproduct, Y_idcombination FROM `ps_product_attribute` WHERE reference=CONCAT("Y", p_reference) LIMIT 1;
  
  -- výpočet procentuální změny ceny --
  IF p_wholesaleprice < 1 || price_old < 1 THEN
    SET change_percent = 0;
  ELSEIF p_wholesaleprice > 0 && price_old > 0 THEN
    SET change_percent = 100 - ROUND(new_price / price_old * 100);
  END IF;
  
  -- naskladnění a nacenění kombinace --
  IF has_duplicate THEN                                   
    SELECT CONCAT("Byla nalezena více než jedna kombinace s referencí `", p_reference, "`.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSEIF idcombination IS NULL THEN
    SELECT CONCAT("Kombinace s referencí `", p_reference, "` nebyla nalezena.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSEIF idproduct IS NULL THEN                                         
    SELECT CONCAT("ID produktu (id_product) u kombinace s referencí `", p_reference, "` nebyl nalezen.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSEIF LOWER(SUBSTR(p_reference, 1, 1)) = "X" || LOWER(SUBSTR(p_reference, 1, 1)) = "Y" THEN                                                         
    SELECT CONCAT("Byl přeskočen produkt, jehož dodavatelské ID (reference) `", p_reference, "` začíná písmenem X nebo Y.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSEIF p_wholesaleprice < 1 THEN
    SELECT CONCAT("Kombinace s referencí `", p_reference, "` nemá nastavenou cenu.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSEIF p_margin < 1 THEN
    SELECT CONCAT("Kombinace s referencí `", p_reference, "` nemá nastavenou marži.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
  ELSE
    SELECT COUNT(*) > 0 INTO quantity_exists FROM `ps_stock_available` WHERE id_product_attribute=idcombination;
    SELECT COUNT(*) > 0 INTO total_quantity_exists FROM `ps_stock_available` WHERE id_product=idproduct && id_product_attribute=0;
  
    START TRANSACTION;
      UPDATE `ps_product_attribute` SET wholesale_price=p_wholesaleprice, price=new_price, old_price=price_old, price_change_percent=change_percent, quantity=p_quantity WHERE reference=p_reference;
      UPDATE `ps_product_attribute_shop` SET wholesale_price=p_wholesaleprice, price=new_price WHERE id_product_attribute=idcombination;
      
      IF quantity_exists THEN
        UPDATE `ps_stock_available` SET quantity=p_quantity WHERE id_product_attribute=idcombination;
      ELSE
        INSERT INTO `ps_stock_available` (id_product, id_product_attribute, id_shop, id_shop_group, quantity, out_of_stock) VALUES (idproduct, idcombination, p_idshop, p_idshopgroup, p_quantity, p_outofstock);
      END IF;
                   
      -- vyskladnění Y produktu --
      IF Y_idproduct IS NOT NULL && Y_idcombination IS NOT NULL THEN
        UPDATE `ps_product_attribute` SET wholesale_price=quantity=p_quantity WHERE id_product_attribute=Y_idcombination;
        UPDATE `ps_product` SET active=0, indexed=0 WHERE id_product=Y_idproduct;
        UPDATE `ps_product_shop` SET active=0, indexed=0 WHERE id_product=Y_idproduct;
        UPDATE `ps_stock_available` SET quantity=p_quantity WHERE id_product_attribute=Y_idcombination;
      END IF;    
    COMMIT;  
             
    -- přepočet skladových zásob u Y produktu --
    IF Y_idproduct IS NOT NULL && Y_idcombination IS NOT NULL THEN
      SELECT SUM(quantity) INTO Y_total_quantity FROM `ps_stock_available` WHERE id_product=Y_idproduct && id_product_attribute>0;
      UPDATE `ps_stock_available` SET quantity=total_quantity WHERE id_product=Y_idproduct && id_product_attribute=0;
    END IF;
    
    -- přepočet skladových zásob --
    SELECT SUM(quantity) INTO total_quantity FROM `ps_stock_available` WHERE id_product=idproduct && id_product_attribute>0;
    SET activate = total_quantity > 0;
                                 
    START TRANSACTION;                   
      UPDATE `ps_product` SET active=activate, indexed=activate WHERE id_product=idproduct;
      UPDATE `ps_product_shop` SET active=activate, indexed=activate WHERE id_product=idproduct;
      
      IF total_quantity_exists THEN      
        UPDATE `ps_stock_available` SET quantity=total_quantity WHERE id_product=idproduct && id_product_attribute=0;
      ELSE
        INSERT INTO `ps_stock_available` (id_product, id_product_attribute, id_shop, id_shop_group, quantity, out_of_stock) VALUES (idproduct, 0, p_idshop, p_idshopgroup, total_quantity, p_outofstock);
      END IF;  
    COMMIT;
    
    -- ověření uložených hodnot --
    SELECT COUNT(*) > 0 INTO success FROM `ps_product_attribute` WHERE reference=p_reference && price=new_price && quantity=p_quantity;
    
    IF success THEN
      SELECT COUNT(*) > 0 INTO success FROM `ps_product_attribute_shop` WHERE id_product_attribute=idcombination && price=new_price;
    END IF;
    
    IF success THEN
      SELECT COUNT(*) > 0 INTO success FROM `ps_stock_available` WHERE id_product_attribute=idcombination && quantity=p_quantity;
    END IF;
    
    IF success THEN
      SELECT COUNT(*) > 0 INTO success FROM `ps_stock_available` WHERE id_product=idproduct && id_product_attribute=0 && quantity=total_quantity;
    END IF;        
    
    -- zobrazení výsledku --
    IF success THEN
      SELECT 1 AS success, p_reference AS reference, idcombination AS id_product_attribute, new_price AS price, p_quantity AS quantity, total_quantity, "SetPriceStock" AS procedure_name; 
    ELSE
      START TRANSACTION;
        UPDATE `ps_product` SET active=0, indexed=0 WHERE id_product=idproduct;
        UPDATE `ps_product_shop` SET active=0, indexed=0 WHERE id_product=idproduct;
      COMMIT;
      SELECT CONCAT("Naskladnění nebo nacenění kombinace (varianty) produktu s referencí `", p_reference, "` se nezdařilo.") AS error, p_reference AS reference, "SetPriceStock" AS procedure_name;
    END IF;      
  END IF;          
END