create or replace function test() returns setof integer as
$$
BEGIN
	return next 1;
	return next 2;
	return;
end;
$$ language 'plpgsql';

--
-- getting self and the whole downline
--

create or replace function st(integer) returns setof integer as
$$
declare
  condition text;
  currentid RECORD;
  dist_id alias for $1;
  first boolean;
begin
	return next $1;
	condition:='sponsor=' || dist_id;
	while condition<>'' LOOP
		first:=true;
		for currentid in execute 'select id from distributor where ' || condition LOOP
		 	return next currentid.id;
			if first then
				condition:='sponsor=' || currentid.id;
				first:=false;
			else
				condition:=condition || ' OR sponsor=' || currentid.id;				
			end if;
		end loop;
		if first then
			condition:='';
		end if;
	end loop;
	return;
end;
$$ language 'plpgsql';

--
-- getting of direct distributors
--

create or replace function sdd(int) returns setof integer as
$$
declare
	currentid RECORD;

begin
	return next $1;
	for currentid in select id from distributor where sponsor=$1 and level_id>200  and active and not deleted loop
		return next currentid.id;
	end loop;	
	
	return;
end;
$$ language 'plpgsql';

--
-- getting the direct customers and self
--

create or replace function sdc(int) returns setof integer as $$
	select id from distributor where id=$1 or (sponsor=$1 and level_id<=200 and level_id>=100 and active and not deleted)
$$ language sql;

--
-- getting the direct customers
--

create or replace function dc(int) returns setof integer as
$$
   select id from distributor where sponsor=$1 and level_id<=200 and level_id>=100 and active and not deleted
$$ language sql;

--
-- getting the direct team
--
create or replace function dt(int) returns setof integer as 
$$
	select id from distributor where sponsor=$1 and active and not deleted
$$ language sql;


-- 
-- getting the direct team or self
--

create or replace function sdt(int) returns setof integer as
$$
	select id from distributor where id=$1 or id in (select * from dt($1))
$$ language sql;


---
--- getting ids of all distributors
---

create or replace function a(int) returns setof integer as
$$
	select id from distributor where id>1000000
$$ language sql;

---
--- getting self
---

create or replace function s(int) returns setof integer as
$$
  select $1
$$ language sql;

--
-- generating the next distributor id from the country code
--

create or replace function generate_next_dist_id(varchar(3)) returns integer as
$$
declare
	max_id int;
	new_id int;
	tel_cod int;
	curs1 refcursor;
begin
	select into tel_cod tel_code from country where country_code = $1;
	if not found then
		raise exception 'country post code $1 doesnt exist';
	end if;
	
	if tel_cod is null then
		raise exception 'there is no tel_code given in the country table, no code can be generated';
	end if;
	open curs1 for execute 'select max(id) from distributor where id like ' || quote_literal(tel_cod || '%');
	fetch curs1 into max_id;
	if (not found) or (max_id is null) or (max_id<(tel_cod * 1000000)+2000)  then
		new_id:=tel_cod*1000000+2000;
	else
		new_id:=max_id+1;
	end if;
	close curs1;
	return new_id;
	
		
end;
$$ language 'plpgsql';

	
create or replace function check_and_repair_order(bigint) returns boolean as
$$
declare
	sum_detail RECORD;
	sum_header RECORD;
	bad boolean;
begin
  select into sum_detail sum(netto*quantity) as tnetto, sum(bonus*quantity) as tbonus, sum(points*quantity) as tpoints, sum(brutto*quantity) as tbrutto from order_detail where order_id=$1;
  select into sum_header total_netto, total_bonus, total_points, total_brutto from order_header where id=$1;
  bad:=false;
 
  if abs(sum_detail.tnetto-sum_header.total_netto) > 0.7 then
  	update order_header set total_netto=sum_detail.tnetto where id=$1;
    bad:=true;
  end if;
 
  if abs(sum_detail.tbrutto-sum_header.total_brutto) > 0.7 then
  	update order_header set total_brutto=sum_detail.tbrutto where id=$1;
    bad:=true;
  end if;
  
  if abs(sum_detail.tbonus-sum_header.total_bonus) > 0.7 then
  	update order_header set total_bonus=sum_detail.tbonus where id=$1;
    bad:=true;
  end if;
  
  if abs(sum_detail.tpoints-sum_header.total_points) > 0.7 then
  	update order_header set total_points=sum_detail.tpoints where id=$1;
    bad:=true;
  end if;
 
  if bad then
    insert into repaired_orders values ($1);
  end if;
   
  return bad;	
end;
$$ language 'plpgsql'	
	

	
				