projekt kivitendo

CREATE OR REPLACE FUNCTION public.glinetotal(in_qty real, in_sellprice numeric, in_price_factor numeric, in_discount real, in_exchangerate numeric, in_taxincluded boolean, in_taxrate numeric, OUT linetotal_net numeric, OUT linetotal_gross numeric, OUT linetotal_tax_amount numeric, OUT linetotal_discount_amount numeric, OUT sellprice_net numeric)

 RETURNS record

 LANGUAGE plpgsql

AS $function$

DECLARE

  num_dec   int;

  discount  numeric(15,5);

  sellprice numeric(15,5);

  linetotal numeric(15,5);

  tax       numeric(15,5);

  qty       numeric(15,5);

  linetotal_no_discount numeric(15,5);

BEGIN

  -- some assumptions: qty = base_qty

  -- linetotals must always be rounded to two digits

  num_dec := 2;

  sellprice := in_sellprice::numeric;

  qty       := in_qty::numeric;

  linetotal_no_discount := ROUND(in_sellprice                            * qty / in_price_factor, num_dec)::numeric(15,5);

  linetotal             := ROUND(in_sellprice * (1-in_discount)::numeric * qty / in_price_factor, num_dec)::numeric(15,5);

  -- RAISE NOTICE 'linetotal_no_discount = %, linetotal = %', linetotal_no_discount, linetotal;

  linetotal_discount_amount := linetotal_no_discount - linetotal;

  IF in_taxincluded THEN

    -- sellprice is gross

    linetotal_gross := linetotal;

    linetotal_net   := ROUND( linetotal_gross / (1+in_taxrate) , num_dec);

  ELSE

    -- sellprice is net

    linetotal_net   := linetotal;

    tax             := ROUND(linetotal_net * in_taxrate,2);

    linetotal_gross := linetotal_net + tax;

  END IF;

  linetotal_tax_amount := linetotal_gross - linetotal_net;

  sellprice_net := (linetotal_net / qty)::numeric(15,5);

  RETURN;

END;

$function$;

select * from glinetotal(1000, 4.5::numeric, 1, 0.05, 1, 'f', 0.19);

--           linetotal_net             | 4275.00000

--           linetotal_gross           | 5087.25000

--           linetotal_tax_amount      | 812.25000

--           linetotal_discount_amount | 225.00000

--           sellprice_net             | 4.27500

select * from glinetotal(1000, (4.5*1.19)::numeric, 1, 0.05, 1, 't', 0.19);

--           linetotal_net             | 4275.00

--           linetotal_gross           | 5087.25000

--           linetotal_tax_amount      | 812.25000

--           linetotal_discount_amount | 267.75000

--           sellprice_net             | 4.27500