Created
December 21, 2018 16:05
-
-
Save josemoralesp/8b0dc9c2060fabe743f10b216abc9bda to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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