-- If you don't have PL/PGSQL activated uncomment the following lines

DROP FUNCTION plpgsql_call_handler();
DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

BEGIN TRANSACTION;

---------------------------------------------------------------------------------
-- Create internal procedures for samseOu objectClass
-- SAMSE: samseOu objectClass
---------------------------------------------------------------------------------

CREATE FUNCTION create_samse_ou () RETURNS int
AS '
	SELECT setval (''samse_ou_id_seq'', (select max(id) FROM samse_ou));
	INSERT INTO samse_ou (id,ou,uid) 
		VALUES (nextval(''samse_ou_id_seq''),'''','''');
	SELECT max(id) FROM samse_ou
'
LANGUAGE 'sql';

CREATE FUNCTION delete_samse_ou (int) RETURNS int
AS '
	DELETE FROM samse_ou WHERE id=CAST($1 AS INT);
	SELECT $1 AS RETURN
'
LANGUAGE 'sql';


---------------------------------------------------------------------------------
-- Create internal procedures for modifying samseOu attributes
-- SAMSE: ou=company,dc=samse,dc=fr
-- SAMSE: ou=region,dc=samse,dc=fr
-- SAMSE: ou=agency,dc=samse,dc=fr
---------------------------------------------------------------------------------

CREATE FUNCTION set_samse_ou_uid (varchar,int) RETURNS int
AS '
	UPDATE samse_ou SET uid=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_ou(varchar, int) RETURNS int
AS '
	UPDATE samse_ou SET ou=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_legalform(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET legalform=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_legalform(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET legalform=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_capital(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET capital=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_capital(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET capital=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET collectiveconvention=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_collectiveconv(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET collectiveconvention=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_description(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET description=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_description(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET description=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_responsable(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET responsable=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_responsable(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET responsable=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_quality(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET quality=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_quality(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET quality=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_internationalisdn(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET internationalisdnnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_internationalisdn(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET internationalisdnnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_businesscategory(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET businesscategory=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_businesscategory(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET businesscategory=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_telephonenumber(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET telephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_telephonenumber(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET telephonenumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_fax(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET facsimiletelephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_fax(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET facsimiletelephonenumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_street(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET street=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_street(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET street=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_postofficebox(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET postofficebox=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_postofficebox(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET postofficebox=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_postalcode(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET postalcode=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_postalcode(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET postalcode=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_l(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET l=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_l(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET l=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_region(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET region=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_region(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET region=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_rpvnumber(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET rpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_rpvnumber(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET rpvnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_companynumber(int, int) RETURNS int
AS '
        UPDATE samse_ou SET companynumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_companynumber(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET companynumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_agencyshield(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET agencyshield=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_agencyshield(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET agencyshield=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_maildomain(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET maildomain=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_maildomain(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET maildomain=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_codeape(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET codeape=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_codeape(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET codeape=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_realsite(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET realsite=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_realsite(varchar, int) RETURNS int
AS '
        UPDATE samse_ou SET realsite=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_ou_codelidis(int, int) RETURNS int
AS '
        UPDATE samse_ou SET codelidis=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_ou_codelidis(varchar, int)
RETURNS int4 AS '
        UPDATE samse_ou SET codelidis=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';


---------------------------------------------------------------------------------
-- Create internal procedures for posixGroup objectClass
-- SAMSE: posixGroup objectClass
---------------------------------------------------------------------------------

CREATE FUNCTION create_posix_group () RETURNS int
AS '
	SELECT setval (''posix_group_id_seq'', (select max(id) FROM posix_group));
	INSERT INTO posix_group (id,cn,gidnumber) 
		VALUES (nextval(''posix_group_id_seq''),'''',100000);
	SELECT max(id) FROM posix_group
'
LANGUAGE 'sql';

CREATE FUNCTION delete_posix_group (int) RETURNS int
AS '
	DELETE FROM group_member WHERE group_id=CAST($1 AS INT);
	DELETE FROM posix_group WHERE id=CAST($1 AS INT);
	SELECT $1 AS RETURN
'
LANGUAGE 'sql';

---------------------------------------------------------------------------------
-- Create internal procedures for modifying posixGroup attributes
-- SAMSE: ou=group,dc=samse,dc=fr
---------------------------------------------------------------------------------

CREATE FUNCTION set_posix_group_cn(varchar, int) RETURNS int
AS '
        UPDATE posix_group SET cn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
	SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_posix_group_gidnumber(int, int) RETURNS int
AS '
        UPDATE posix_group SET gidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
	SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_group_member_memberuid(varchar, int) RETURNS int
AS '
        INSERT INTO group_member VALUES ($2, $1);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_group_member_memberuid(varchar, int) RETURNS int
AS '
        DELETE FROM group_member WHERE group_id=CAST($2 AS INT) AND memberuid=CAST($1 AS VARCHAR);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';


---------------------------------------------------------------------------------
-- Create internal procedures for samsePerson objectClass
-- SAMSE: samsePerson objectClass
---------------------------------------------------------------------------------

CREATE FUNCTION create_samse_person () RETURNS int
AS '
	SELECT setval (''samse_person_id_seq'', (select max(id) FROM samse_person));
	INSERT INTO samse_person (id,ou,employeenumber,uid,cn) 
		VALUES (nextval(''samse_person_id_seq''),'''','''','''','''');
	SELECT max(id) FROM samse_person
'
LANGUAGE 'sql';

CREATE FUNCTION delete_samse_person (int) RETURNS int
AS '
        DELETE FROM samse_activite WHERE person_id=CAST($1 AS INT);
	DELETE FROM samse_person WHERE id=CAST($1 AS INT);
	SELECT $1 AS RETURN
'
LANGUAGE 'sql';

---------------------------------------------------------------------------------
-- Create internal procedures for modifying samsePerson attributes
-- SAMSE: ou=people,dc=samse,dc=fr
---------------------------------------------------------------------------------

CREATE FUNCTION set_samse_person_ou(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET ou=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_employeenumber(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET employeenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_uid(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET uid=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_uid(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET uid=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_cn(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET cn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_cn(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET cn=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_sn(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET sn=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_sn(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET sn=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_givenname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET givenname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_givenname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET givenname=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_displayname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET displayname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_displayname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET displayname=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_mail(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mail=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_mail(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mail=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_mailboxname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mailboxname=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_mailboxname(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mailboxname=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_title(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET title=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_title(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET title=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_employeetype(char(1), int) RETURNS int
AS '
        UPDATE samse_person SET employeetype=CAST($1 AS CHAR(1)) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_employeetype(char(1), int) RETURNS int
AS '
        UPDATE samse_person SET employeetype=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_departmentnum(int, int) RETURNS int
AS '
        UPDATE samse_person SET departmentnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_departmentnum(int, int) RETURNS int
AS '
        UPDATE samse_person SET departmentnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_arrivaldate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET arrivaldate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_arrivaldate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET arrivaldate=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_birthdate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET birthdate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_birthdate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET birthdate=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_leavingdate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET leavingdate=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_leavingdate(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET leavingdate=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_contribgroup(char(2), int) RETURNS int
AS '
        UPDATE samse_person SET contributiongroup=CAST($1 AS CHAR(2)) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_contribgroup(char(2), int) RETURNS int
AS '
        UPDATE samse_person SET contributiongroup=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_userpassword(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET userpassword=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_userpassword(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET userpassword=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_loginshell(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET loginshell=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_loginshell(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET loginshell=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_uidnumber(int, int) RETURNS int
AS '
        UPDATE samse_person SET uidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_uidnumber(int, int) RETURNS int
AS '
        UPDATE samse_person SET uidnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_gidnumber(int, int) RETURNS int
AS '
        UPDATE samse_person SET gidnumber=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_gidnumber(int, int) RETURNS int
AS '
        UPDATE samse_person SET gidnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_shadowlastchg(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowlastchange=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_shadowlastchg(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowlastchange=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_shadowmin(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowmin=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_shadowmin(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowmin=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_shadowmax(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowmax=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_shadowmax(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowmax=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_shadowwarning(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowwarning=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_shadowwarning(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowwarning=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_fixrpvnumber(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET fixrpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_fixrpvnumber(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET fixrpvnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_mobile(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mobile=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_mobile(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mobile=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_mobilerpvnum(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mobilerpvnumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_mobilerpvnum(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET mobilerpvnumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_assistant(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET assistant=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_assistant(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET assistant=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_phonenumber(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET telephonenumber=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_phonenumber(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET telephonenumber=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_sexe(char(1), int) RETURNS int
AS '
        UPDATE samse_person SET sexe=CAST($1 AS CHAR(1)) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_sexe(char(1), int) RETURNS int
AS '
        UPDATE samse_person SET sexe=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_homedirectory(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET homedirectory=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_homedirectory(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET homedirectory=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_codeemploi(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET codeemploi=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_codeemploi(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET codeemploi=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_codeagence(int, int) RETURNS int
AS '
        UPDATE samse_person SET codeagence=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_codeagence(int, int) RETURNS int
AS '
        UPDATE samse_person SET codeagence=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_fonction(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET fonction=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_fonction(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET fonction=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_activite_activite(varchar, int) RETURNS int
AS '
        INSERT INTO samse_activite VALUES ($2, $1);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_activite_activite(varchar, int) RETURNS int
AS '
        DELETE FROM samse_activite WHERE person_id=CAST($2 AS INT) AND activite=CAST($1 AS VARCHAR);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_shadowexpire(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowexpire=CAST($1 AS INT) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_shadowexpire(int, int) RETURNS int
AS '
        UPDATE samse_person SET shadowexpire=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_service(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET service=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_service(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET service=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION set_samse_person_typertt(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET typertt=CAST($1 AS VARCHAR) WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';

CREATE FUNCTION del_samse_person_typertt(varchar, int) RETURNS int
AS '
        UPDATE samse_person SET typertt=NULL WHERE id=CAST($2 AS INT);
        SELECT $2 AS RETURN
'
LANGUAGE 'sql';


END TRANSACTION;
