== Descripción ==
Actualizador de precios de un presupuesto indicado. Toma toda la información existente y la actualiza de acuerdo a la lista de precios vigente
== Composición ==
*[[TABLA:vidquoten3]]
*[[TABLA:vidquoten2]]
*[[TABLA:precios]]
*[[TABLA:productos]]
*[[TABLA:composicion]]
== Código ==
<small><pre>
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`sas_admin`@`%` PROCEDURE `ActualizaPreciosPresupuesto`(IN pVidQuoteID INT(5), IN pVidQuoteLista INT(1), IN pCoeficienteAC DECIMAL(10,5))
BEGIN
-- ----------------------------------------------------
-- Update from tables with ProductoID=ElementoID
-- ----------------------------------------------------
UPDATE
    composicion AS T2,
    vidquoten3 AS T1,
    precios AS T3,
    productos AS T4,
    vidquoten2 AS T5
SET 
    T1.VidQuotePrecio = CalculoPrecio(
                        T5.VidQuoteAncho, 
                        T5.VidQuoteAlto,
                        (CASE (pVidQuoteLista)
                          WHEN 0 THEN T3.PrecioProd00
                          WHEN 1 THEN T3.PrecioProd01
                          WHEN 2 THEN T3.PrecioProd02
                          WHEN 3 THEN T3.PrecioProd03
                        END),
                        T4.Magnitud,
                        T2.OffsetVanoAlto,
                        T2.OffsetVanoAncho,
                        T2.CoefVanoAlto,
                        T2.CoefVanoAncho,
                        T2.ValorElemento) * pCoeficienteAC,
    T1.VidQuotePrecioP= CalculoPrecio(
                        T5.VidQuoteAncho, 
                        T5.VidQuoteAlto,
                        (CASE (pVidQuoteLista)
                          WHEN 0 THEN T3.PrecioProd00
                          WHEN 1 THEN T3.PrecioProd01
                          WHEN 2 THEN T3.PrecioProd02
                          WHEN 3 THEN T3.PrecioProd03
                        END),
                        T4.Magnitud,
                        T2.OffsetVanoAlto,
                        T2.OffsetVanoAncho,
                        T2.CoefVanoAlto,
                        T2.CoefVanoAncho,
                        T2.ValorElemento) * pCoeficienteAC * T5.VidQuoteProdCantidad 
WHERE
    T1.VidQuoteID = pVidQuoteID AND
    T2.ProductoID = T1.VidQuoteProdID AND 
    T2.ElementoID = T1.VidQuoteElementoID AND 
    T2.ComposicID = T1.VidQuoteComposicID AND 
    T4.ProductoID = T3.ProductoID AND 
    T5.VidQuoteID = T1.VidQuoteID AND 
    T5.VidQuoteItem = T1.VidQuoteItem AND 
    T3.ProductoID = T2.ElementoID AND 
    T1.VidQuoteProdID = T1.VidQuoteElementoID;
-- ----------------------------------------------------
-- Update from tables with ProductoID<>ElementoID
-- ----------------------------------------------------
UPDATE
    vidquoten3 AS W1,
    (SELECT 
        V1.VidQuoteID,
        V1.VidQuoteItem,
        V1.VidQuoteProdID,
        V1.VidQuoteElementoID,
        V1.VidQuoteComposicID,
        SUM(CalculoPrecio(
            V6.VidQuoteAncho, 
            V6.VidQuoteAlto,
            (CASE (pVidQuoteLista)
              WHEN 0 THEN V4.PrecioProd00
              WHEN 1 THEN V4.PrecioProd01
              WHEN 2 THEN V4.PrecioProd02
              WHEN 3 THEN V4.PrecioProd03
            END),
            V5.Magnitud,
            V2.OffsetVanoAlto + V3.OffsetVanoAlto,
            V2.OffsetVanoAncho + V3.OffsetVanoAncho,
            V2.CoefVanoAlto * V3.CoefVanoAlto,
            V2.CoefVanoAncho * V3.CoefVanoAncho,
            V2.ValorElemento * V3.ValorElemento)) * pCoeficienteAC AS VidQuotePrecio,
        SUM(CalculoPrecio(
            V6.VidQuoteAncho, 
            V6.VidQuoteAlto,
            (CASE (pVidQuoteLista)
              WHEN 0 THEN V4.PrecioProd00
              WHEN 1 THEN V4.PrecioProd01
              WHEN 2 THEN V4.PrecioProd02
              WHEN 3 THEN V4.PrecioProd03
            END),
            V5.Magnitud,
            V2.OffsetVanoAlto + V3.OffsetVanoAlto,
            V2.OffsetVanoAncho + V3.OffsetVanoAncho,
            V2.CoefVanoAlto * V3.CoefVanoAlto,
            V2.CoefVanoAncho * V3.CoefVanoAncho,
            V2.ValorElemento * V3.ValorElemento)) * pCoeficienteAC * V6.VidQuoteProdCantidad AS VidQuotePrecioP
    FROM
        fzggestion.composicion AS V3,
        fzggestion.composicion AS V2,
        fzggestion.vidquoten3 AS V1,
        fzggestion.precios AS V4,
        fzggestion.productos AS V5,
        fzggestion.vidquoten2 AS V6
    WHERE
        V1.VidQuoteID = pVidQuoteID AND
        V6.VidQuoteID = V1.VidQuoteID AND 
        V6.VidQuoteItem = V1.VidQuoteItem AND
        V3.ProductoID = V2.ElementoID AND 
        V2.ProductoID = V1.VidQuoteProdID AND 
        V2.ElementoID = V1.VidQuoteElementoID AND 
        V2.ComposicID = V1.VidQuoteComposicID AND 
        V4.ProductoID = V3.ElementoID AND 
        V5.ProductoID = V4.ProductoID AND 
        V1.VidQuoteProdID <> V1.VidQuoteElementoID AND 
        V3.ElementoPM = 0
    GROUP BY
        V1.VidQuoteID,
        V1.VidQuoteItem,
        V1.VidQuoteElementoID,
        V1.VidQuoteComposicID) AS W2
SET
    W1.VidQuotePrecio = W2.VidQuotePrecio,
    W1.VidQuotePrecioP = W2.VidQuotePrecioP
WHERE
    -- ----------------------------------------------------------
    -- Join between result of calculating prices and vidquoten3
    -- ----------------------------------------------------------
    W2.VidQuoteID = W1.VidQuoteID AND 
    W2.VidQuoteItem = W1.VidQuoteItem AND 
    W2.VidQuoteProdID = W1.VidQuoteProdID AND 
    W2.VidQuoteElementoID = W1.VidQuoteElementoID AND 
    W2.VidQuoteComposicID = W1.VidQuoteComposicID AND
    -- ----------------------------------------------------------
    -- Select what Qoute will be affected
    -- ----------------------------------------------------------
    W1.VidQuoteID = pVidQuoteID AND
    -- ----------------------------------------------------------
    -- Select records not PPD
    -- ----------------------------------------------------------
    W1.VidQuoteProdID <> W1.VidQuoteElementoID;
-- ----------------------------------------------------------
-- Update Precios Resume on vidquoten2
-- ----------------------------------------------------------
UPDATE
    vidquoten2 AS T1,
    (SELECT 
        VidQuoteID,
        VidQuoteItem,
        VidQuoteProdCantidad,
        VidQuoteProdID,
        SUM(VidQuotePrecioP) AS VidQuotePrecioP
    FROM
        vidquoten3
    GROUP BY
        VidQuoteID,
        VidQuoteItem) AS T2
SET
    T1.VidQuotePrecio=T2.VidQuotePrecioP
WHERE
    T1.VidQuoteID = pVidQuoteID AND
    T1.VidQuoteID = T2.VidQuoteID AND
    T1.VidQuoteItem = T2.VidQuoteItem;
END
</pre></small>
== Bitácora ==
*20120921
**Actualización de acuerdo al cambio de diseño de [[FORM:Presupuesto]]
<small><pre>
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`sas_admin`@`%` PROCEDURE `ActualizaPreciosPresupuesto_OLD`(IN pVidQuoteID INT(5))
BEGIN
	UPDATE vidquoten3 AS T5,
    (
    SELECT
		T4.VidQuoteProdID,
      T4.VidQuoteElementoID,
		T4.VidQuoteProdCantidad,
		T4.VidQuoteAncho,
		T4.VidQuoteAlto,
      T4.PrecioProd,
		SUM(CalculoPrecio(
			T4.VidQuoteAncho, 
			T4.VidQuoteAlto,
			T4.PrecioProd,
			T4.Magnitud,
			T4.OffsetVanoAlto,
			T4.OffsetVanoAncho,
			T4.CoefVanoAlto,
			T4.CoefVanoAncho,
			T4.ValorElemento)) AS VidQuotePrecio,
		SUM(CalculoPrecio(
			T4.VidQuoteAncho, 
			T4.VidQuoteAlto,
			T4.PrecioProd,
			T4.Magnitud,
			T4.OffsetVanoAlto,
			T4.OffsetVanoAncho,
			T4.CoefVanoAlto,
			T4.CoefVanoAncho,
			T4.ValorElemento) * T4.VidQuoteProdCantidad) AS VidQuotePrecioP
	FROM
	((SELECT 
		 T1.VidQuoteLPre AS VidQuoteLPre,
		 T1.VidQuoteProdID AS VidQuoteProdID,
		 T1.VidQuoteElementoID AS VidQuoteElementoID,
		 T2.ElementoID AS ElementoID,
		 T1.VidQuoteProdCantidad AS VidQuoteProdCantidad,
		 T1.VidQuoteAncho AS VidQuoteAncho,
		 T1.VidQuoteAlto AS VidQuoteAlto,
		 T2.Magnitud,
		 T2.ValorElemento AS ValorElemento,
		 T2.OffsetVanoAncho AS OffsetVanoAncho,
		 T2.OffsetVanoAlto AS OffsetVanoAlto,
		 T2.CoefVanoAncho AS CoefVanoAncho,
		 T2.CoefVanoAlto AS CoefVanoAlto,
		(CASE (T1.VidQuoteLPre)
		     WHEN 0 THEN T2.`PrecioProd00`
			WHEN 1 THEN T2.`PrecioProd01`
			WHEN 2 THEN T2.`PrecioProd02`
			WHEN 3 THEN T2.`PrecioProd03`
			END) AS PrecioProd
	FROM
		 { OJ fzggestion.visprodadic AS T2
		     RIGHT OUTER JOIN
		 fzggestion.vidquoten3 AS T1 ON T2.ProductoID = T1.VidQuoteProdID AND T2.ElementoID = T1.VidQuoteElementoID }
	WHERE
		 T1.VidQuoteProdID=VidQuoteElementoID AND T1.VidQuoteID = pVidQuoteID)
	UNION
	(SELECT 
		 T1.VidQuoteLPre AS VidQuoteLPre,
		 T1.VidQuoteProdID AS VidQuoteProdID,
		 T1.VidQuoteElementoID AS VidQuoteElementoID,
		 T2.ElementoID AS ElementoID,
		 T1.VidQuoteProdCantidad AS VidQuoteProdCantidad,
		 T1.VidQuoteAncho AS VidQuoteAncho,
		 T1.VidQuoteAlto AS VidQuoteAlto,
		 T2.Magnitud,
		 T3.ValorElemento * T2.ValorElemento AS ValorElemento,
		 T3.OffsetVanoAncho + T2.OffsetVanoAncho AS OffsetVanoAncho,
		 T3.OffsetVanoAlto + T2.OffsetVanoAlto AS OffsetVanoAlto,
		 T3.CoefVanoAncho * T2.CoefVanoAncho AS CoefVanoAncho,
		 T3.CoefVanoAlto * T2.CoefVanoAlto AS CoefVanoAlto,
		(CASE (T1.VidQuoteLPre)
		     WHEN 0 THEN T2.`PrecioProd00`
			WHEN 1 THEN T2.`PrecioProd01`
			WHEN 2 THEN T2.`PrecioProd02`
			WHEN 3 THEN T2.`PrecioProd03`
			END) AS PrecioProd
	FROM
		 { OJ fzggestion.visprodadic AS T2
		     RIGHT OUTER JOIN
		 fzggestion.vidquoten3 AS T1 ON T2.ProductoID = T1.VidQuoteElementoID },
		 fzggestion.visprodadic AS T3
	WHERE
		 T3.ProductoID = T1.VidQuoteProdID AND 
		 T3.ElementoID = T1.VidQuoteElementoID AND 
		 T1.VidQuoteProdID <> T1.VidQuoteElementoID AND T1.VidQuoteID = pVidQuoteID)
	ORDER BY VidQuoteProdID,VidQuoteElementoID,ElementoID) AS T4
	GROUP BY VidQuoteProdID,VidQuoteElementoID) AS T6
	SET T5.VidQuotePrecio = T6.VidQuotePrecio, T5.VidQuotePrecioP = T6.VidQuotePrecioP
	WHERE
		 T5.VidQuoteID = pVidQuoteID AND 
		 T5.VidQuoteProdID = T6.VidQuoteProdID AND 
		 T5.VidQuoteElementoID = T6.VidQuoteElementoID;
END
</pre></small>
*20120925
**Reestructura actualización del atributo de precio debido a que se incorpora el [[TRIG:Actualiza_Item_Presupuesto]] y produce un dead-lock al gestionar las mismas tablas. Se modifica el [[TRIG:Actualiza_Item_Presupuesto]] y se hace un UPDATE falso para forzar el Trigger de actualización a hacer el recálculo.
<small><pre>
UPDATE
    vidquoten2 AS T1,
    (SELECT 
        VidQuoteID,
        VidQuoteItem,
        VidQuoteProdCantidad,
        VidQuoteProdID,
        SUM(VidQuotePrecioP) AS VidQuotePrecioP
    FROM
        vidquoten3
    GROUP BY
        VidQuoteID,
        VidQuoteItem) AS T2
SET
    T1.VidQuotePrecio=T2.VidQuotePrecioP
WHERE
    T1.VidQuoteID = pVidQuoteID AND
    T1.VidQuoteID = T2.VidQuoteID AND
    T1.VidQuoteItem = T2.VidQuoteItem;
END
</pre></small>

== Uso ==
*[[FORM:Presupuesto]]
*[[MACRO:BotonActualizarPresupuesto]]