get_qty_by_location.sql

DROP FUNCTION IF EXISTS float_round(amount float, rounding float, rounding_method varchar);
CREATE OR REPLACE FUNCTION float_round(amount float, rounding float, rounding_method varchar)
RETURNS float AS $$
    DECLARE
        normalized_value float;
        epsilon_magnitude float;
        epsilon float;
        rounded_value float;
        sign float;
    BEGIN
        IF amount = 0.0 THEN
            RETURN amount;
        END IF;
        IF rounding_method is null THEN
            rounding_method := 'UP';
        END IF;

        normalized_value := amount / rounding;
        epsilon_magnitude := log(abs(normalized_value)) /  log(2);
        epsilon := 2^(epsilon_magnitude-53);
        IF rounding_method = 'HALF-UP' THEN
            CASE WHEN normalized_value < 0 THEN
                sign := -1 * epsilon;
            WHEN normalized_value = 0 THEN
                sign := 0 * epsilon;
            WHEN normalized_value > 0 THEN
                sign := 1 * epsilon;
            END CASE;
            normalized_value := normalized_value + (sign*epsilon);
            rounded_value := round(normalized_value);
        ELSIF rounding_method = 'UP' THEN
            CASE WHEN normalized_value < 0 THEN
                sign := -1;
            WHEN normalized_value = 0 THEN
                sign := 0;
            WHEN normalized_value > 0 THEN
                sign := 1;
            END CASE;
            normalized_value := normalized_value - (sign*epsilon);
            rounded_value := ceil(abs(normalized_value)) * sign;
        END IF;
        amount := rounded_value * rounding;

        RETURN amount; END;
    $$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS compute_qty_obj(from_uom product_uom, qty float, to_unit product_uom, rounding_method varchar);
CREATE OR REPLACE FUNCTION compute_qty_obj(from_uom product_uom, qty float, to_unit product_uom, rounding_method varchar)
RETURNS float AS $$
    DECLARE
        amount float;
    BEGIN
        IF rounding_method is null THEN
            rounding_method := 'UP';
        END IF;

        IF from_uom.category_id != to_unit.category_id THEN
            RAISE EXCEPTION USING MESSAGE = 'The category of the unit of measure ' || from_uom.name || ' is different to the unit of measure '|| to_unit.name;
        END IF;
        amount := qty / from_uom.factor;
        IF to_unit is not null THEN
            amount := amount * to_unit.factor;
            SELECT float_round(amount, to_unit.rounding, rounding_method) INTO amount;
        END IF;

        RETURN amount; END;
    $$ LANGUAGE plpgsql;



DROP FUNCTION IF EXISTS get_child_locations(source_location integer);
CREATE OR REPLACE FUNCTION get_child_locations(source_location integer)
RETURNS integer[] AS $$
    DECLARE
        returned integer[];
    BEGIN
        SELECT array_agg(id) INTO returned
        FROM (WITH RECURSIVE tree AS (
              SELECT id, ARRAY[]::INTEGER[] AS ancestors
              FROM stock_location WHERE location_id IS NULL
              UNION ALL
              SELECT stock_location.id,
                     tree.ancestors || stock_location.location_id
              FROM stock_location, tree
              WHERE stock_location.location_id = tree.id
            )
            SELECT *
            FROM tree
            WHERE source_location = ANY(tree.ancestors) OR
                  id = source_location) AS a;
        RETURN returned; END;
    $$ LANGUAGE plpgsql;
/* RAISE NOTICE USING MESSAGE = 'move product: ' || move.product_id; */
DROP FUNCTION IF EXISTS get_qty_available_by_location(product integer, location integer);
CREATE OR REPLACE FUNCTION get_qty_available_by_location(product integer, location integer)
RETURNS float AS $$
    DECLARE
        total float;
        total_in float;
        total_out float;
        compute_qty float;
        template integer;
        move stock_move%rowtype;
        from_uom product_uom%rowtype;
        to_uom product_uom%rowtype;
    BEGIN
        total_in := 0;
        total_out := 0;
        total := 0;
        FOR move IN SELECT * FROM stock_move
                             WHERE product_id = product
                                   AND location_dest_id = location
                                   AND location_id != location
                                   AND state = 'done'
                                   AND product_uom_qty > 0
                             LOOP
            template := (SELECT product_tmpl_id FROM product_product WHERE id=move.product_id);
            SELECT * INTO from_uom FROM product_uom WHERE id=move.product_uom;
            SELECT * INTO to_uom FROM product_uom WHERE id IN (SELECT uom_id FROM product_template WHERE id = template);
            SELECT compute_qty_obj(from_uom, move.product_uom_qty, to_uom, 'HALF-UP') INTO compute_qty;
            /* IF from_uom.id != to_uom.id THEN */
            /*     RAISE NOTICE USING MESSAGE= ' FROM UOM '|| from_uom.name || ' TO UOM '|| to_uom.name || ' old qty '|| move.product_uom_qty || ' NEW QTY '|| compute_qty; */
            /* END IF; */
            total_in := total_in + compute_qty;
        END LOOP;
        FOR move IN SELECT * FROM stock_move
                             WHERE product_id = product
                                   AND location_id = location
                                   AND location_dest_id != location
                                   AND state = 'done'
                                   AND product_uom_qty > 0
                             LOOP
            template := (SELECT product_tmpl_id FROM product_product WHERE id=move.product_id);
            SELECT * INTO from_uom FROM product_uom WHERE id=move.product_uom;
            SELECT * INTO to_uom FROM product_uom WHERE id IN (SELECT uom_id FROM product_template WHERE id = template);
            SELECT compute_qty_obj(from_uom, move.product_uom_qty, to_uom, 'HALF-UP') INTO compute_qty;
            /* IF from_uom.id != to_uom.id THEN */
            /*     RAISE NOTICE USING MESSAGE= ' FROM UOM '|| from_uom.name || ' TO UOM '|| to_uom.name || ' old qty '|| move.product_uom_qty || ' NEW QTY '|| compute_qty; */
            /* END IF; */
            total_out := total_out + compute_qty;
        END LOOP;
        total := total_in - total_out;

        RETURN total; END;
    $$ LANGUAGE plpgsql;

SELECT
       loc.complete_name,
       move.product_id,
       move.location_dest_id,
       loc.usage,
       get_qty_available_by_location(move.product_id,
                                     move.location_dest_id) AS total
FROM (SELECT product_id, location_dest_id
      FROM stock_move WHERE state ='done'
      GROUP BY product_id, location_dest_id) AS move
INNER JOIN stock_location AS loc ON loc.id = move.location_dest_id
WHERE move.product_id=14608 AND loc.usage='internal';