Skip to content

Instantly share code, notes, and snippets.

@josemoralesp
Created December 21, 2018 16:05
Show Gist options
  • Save josemoralesp/8b0dc9c2060fabe743f10b216abc9bda to your computer and use it in GitHub Desktop.
Save josemoralesp/8b0dc9c2060fabe743f10b216abc9bda to your computer and use it in GitHub Desktop.
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment