From 9af6ae6be1019ea656a101dfe7b66709f82f68ca Mon Sep 17 00:00:00 2001
From: aurynn_cmd <aurynn_cmd@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Wed, 17 Oct 2007 23:16:35 +0000
Subject: Serious updates to the Admin controller; Creation of a User
 controller/object - I would like to use this to save/load roles, as well.
 Creation of Person stored procs, Entity stored procs. Serious updates to the
 employee stored procs.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1778 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/modules/Employee.sql | 252 ++++++++++++++++++++---------------------------
 sql/modules/Entity.sql   |  41 ++++++++
 sql/modules/Person.sql   |  52 ++++++++++
 sql/modules/admin.sql    | 123 ++++++++++-------------
 4 files changed, 251 insertions(+), 217 deletions(-)
 create mode 100644 sql/modules/Entity.sql
 create mode 100644 sql/modules/Person.sql

(limited to 'sql/modules')

diff --git a/sql/modules/Employee.sql b/sql/modules/Employee.sql
index a934a3e5..04f33e64 100644
--- a/sql/modules/Employee.sql
+++ b/sql/modules/Employee.sql
@@ -1,174 +1,119 @@
 -- VERSION 1.3.0
 BEGIN;
-CREATE OR REPLACE FUNCTION employee_save
-(in_id integer, in_employeenumber varchar(32), 
-	in_salutation int, in_first_name varchar(64), in_last_name varchar(64),
-	in_address1 varchar(32), in_address2 varchar(32),
-	in_city varchar(32), in_state varchar(32), in_zipcode varchar(10),
-	in_country int, in_workphone varchar(20), 
-	in_homephone varchar(20), in_startdate date, in_enddate date, 
-	in_notes text, in_role varchar(20), in_sales boolean, in_email text, 
-	in_ssn varchar(20), in_dob date, in_iban varchar(34), 
-	in_bic varchar(11), in_managerid integer) 
-returns int AS $$ 
-DECLARE
-    e_id int;
-    e entity;
-    loc location;
-    l_id int;
-    per person;
-    p_id int;
-BEGIN
 
-    select * into e from entity where id = in_id and entity_class = 3;
-    
-    if found then
+
+CREATE OR REPLACE FUNCTION employee_save(
+    in_person int, in_entity int, in_startdate date, in_enddate date,
+	in_role text, in_sales boolean, in_dob date, 
+    in_managerid integer, in_employeenumber text
+)
+returns int AS $$
+
+    DECLARE
+        e_ent entity_employee;
+        e entity;
+        p person;
+    BEGIN
+        select * into e from entity where id = in_entity and entity_class = 3;
         
-        select l.* into loc from location l 
-        left join person_to_location ptl on ptl.location_id = l.id
-        left join person p on p.id = ptl.person_id
-        where p.entity_id = in_id;
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'No entity found for ID %', in_id;
+        END IF;
         
-        select * into per from person p where p.entity_id = in_id;
+        select * into p from person where id = in_person;
         
-        update location 
-        set
-            line_one = in_address1,
-        	line_two = in_address2,
-        	city_province = in_city,
-        	mail_code = in_zipcode,
-        	country_id = in_country
-        where id = loc.id;
-	
-    	UPDATE employee
-    	SET 
-    		employeenumber = in_employeenumber,
-    		startdate = in_startdate,
-    		enddate = in_enddate,
-    		role = in_role,
-    		sales = in_sales,
-    		ssn = in_ssn,
-    		dob = in_dob, 
-    		managerid = in_managerid
-    	WHERE entity_id = in_id;
-    	
-    	update entity_note
-    	set 
-    	    note = in_note
-    	where entity_id = in_id;
-    	
-    	UPDATE entity_bank_account 
-    	SET
-    	    bic = in_bic,
-    	    iban = in_iban
-    	WHERE entity_id = in_id;
-    	
-    	UPDATE person
-        SET
-            salutation_id = in_salutation,
-            first_name = in_first_name,
-            last_name = in_last_name
-        WHERE entity_id = in_id;
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'No person found for ID %', in_id;
+        END IF;
         
-        UPDATE person_to_contact
-        set
-            contact = in_homephone
-        WHERE person_id = per.id
-          AND contact_class_id = 11;
-          
-        UPDATE person_to_contact
-        set
-          contact = in_workphone
-        WHERE person_id = per.id
-          AND contact_class_id = 1;
-          
-        UPDATE person_to_contact
-        set
-        contact = in_email
-        WHERE person_id = per.id
-        AND contact_class_id = 12;  
+        -- Okay, we're good. Check to see if we update or insert.
         
-        return in_id;
+        select * into e_ent from entity_employee where person_id = in_person 
+            and entity_id = in_entity;
+            
+        IF NOT FOUND THEN
+            -- insert.
+            
+            INSERT INTO entity_employee (person_id, entity_id, startdate, 
+                enddate, role, sales, manager_id, employeenumber, dob)
+            VALUES (in_person, in_entity, in_startdate, in_enddate, in_role, 
+                in_sales, in_managerid, in_employeenumber, in_dob);
+            
+            return in_entity;
+        ELSE
         
-	ELSIF NOT FOUND THEN	
-	    -- first, create a new entity
-    	-- Then, create an employee.
-	
-    	e_id := in_id; -- expect nextval entity_id to have been called.
-    	INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name);
-    	    
-    	INSERT INTO entity_bank_account (entity_id, iban, bic)
-    	VALUES (e_id, in_iban, in_bic);
-    	
-    	p_id := nextval('person_id_seq');
-    	insert into person (id, salutation_id, first_name, last_name, entity_id)
-    	VALUES
-    	(p_id, in_salutation, in_first_name, in_last_name, e_id);
-	    
-	    if in_notes is not null then
-	        insert into entity_note (note_class, note, ref_key, vector)
-	        values (1, in_notes, e_id, '');
-	    END IF;
-	    
-	    insert into person_to_contact (person_id, contact_class_id, contact)
-	    VALUES (p_id, 1, in_workphone); -- work phone #
-	    insert into person_to_contact (person_id, contact_class_id, contact)
-	    VALUES (p_id, 11, in_homephone); -- Home phone #
-	    insert into person_to_contact (person_id, contact_class_id, contact)
-	    VALUES (p_id, 12, in_email); -- email address.
-	    
-    	INSERT INTO employee
-    	(employeenumber, startdate, enddate, 
-    	    role, sales, ssn,
-    		dob, managerid, entity_id, entity_class_id)
-    	VALUES
-    	(in_employeenumber, in_startdate, in_enddate,
-    	    in_role, in_sales, in_ssn, 
-    	    in_dob,	in_managerid, e_id, 3);
-    		
-    	l_id := nextval('location_id_seq');
-    	insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code)
-    	VALUES (
-    	    l_id,
-    	    1,
-    	    in_address1,
-    	    in_address2,
-    	    in_city,
-    	    in_country, 
-    	    in_zipcode    	    
-    	);
-    	insert into person_to_location (person_id, location_id)
-    	VALUES (p_id, l_id);
-	
-    	return e_id;
-	END IF;
-END;
-$$ LANGUAGE 'plpgsql';
+            -- update
+            
+            UPDATE entity_employee
+            SET
+                startdate = in_startdate,
+                enddate = in_enddate,
+                role = in_role,
+                sales = in_sales,
+                manager_id = in_managerid
+                employeenumber = in_employeenumber,
+                dob = in_dob
+            WHERE
+                entity_id = in_entity
+            AND
+                person_id = in_person;
+                
+            return in_entity;
+        END IF;
+    END;
+
+$$ language 'plpgsql';
+
+create view employees as
+    select 
+        e.salutation,
+        e.first_name,
+        e.last_name,
+        ee.*
+    FROM entity e
+    JOIN entity_employees ee on e.id = ee.entity_id
+    where e.entity_class = 3;
+    
 
 -- why is this like this?
 CREATE OR REPLACE FUNCTION employee_get
 (in_id integer)
-returns employee as
+returns employees as
 $$
 DECLARE
-	emp employee%ROWTYPE;
+	emp employees%ROWTYPE;
 BEGIN
-	SELECT * INTO emp FROM employees WHERE id = in_id;
+	SELECT 
+	    e.salutation, 
+	    e.first_name,
+	    e.last_name,
+	    ee.* 
+	INTO emp 
+    FROM employees ee 
+    join entity e on ee.entity_id = e.id 
+    WHERE ee.entity_id = in_id;
+    
 	RETURN emp;
 END;
 $$ language plpgsql;
 
 CREATE OR REPLACE FUNCTION employee_list_managers
 (in_id integer)
-RETURNS SETOF employee as
+RETURNS SETOF employees as
 $$
 DECLARE
-	emp employee%ROWTYPE;
+	emp employees%ROWTYPE;
 BEGIN
 	FOR emp IN 
-		SELECT * FROM employee
-		WHERE sales = '1' AND role='manager'
-			AND entity_id <> coalesce(in_id, -1)
+		SELECT 
+		    e.salutation,
+		    e.first_name,
+		    e.last_name,
+		    ee.* 
+		FROM entity_employee ee
+		JOIN entity e on e.id = ee.entity_id
+		WHERE ee.sales = 't'::bool AND ee.role='manager'
+			AND ee.entity_id <> coalesce(in_id, -1)
 		ORDER BY name
 	LOOP
 		RETURN NEXT emp;
@@ -196,7 +141,7 @@ CREATE OR REPLACE VIEW employee_search AS
 SELECT e.*, em.name AS manager, emn.note, en.name as name
 FROM employee e 
 LEFT JOIN entity en on (e.entity_id = en.id)
-LEFT JOIN employee m ON (e.managerid = m.entity_id)
+LEFT JOIN entity_employee m ON (e.managerid = m.entity_id)
 LEFT JOIN entity em on (em.id = m.entity_id)
 LEFT JOIN entity_note emn on (emn.ref_key = em.id);
 
@@ -228,4 +173,17 @@ BEGIN
 	return;
 END;
 $$ language plpgsql;
-COMMIT;
\ No newline at end of file
+
+create or replace function employee_set_location 
+    (in_employee int, in_location int) 
+returns void as $$
+
+    INSERT INTO person_to_location (person_id,location_id) 
+        VALUES (in_employee, in_location);
+    
+    SELECT NULL;
+
+$$ language 'sql';
+
+COMMIT;
+
diff --git a/sql/modules/Entity.sql b/sql/modules/Entity.sql
new file mode 100644
index 00000000..2b5aed58
--- /dev/null
+++ b/sql/modules/Entity.sql
@@ -0,0 +1,41 @@
+--
+BEGIN;
+
+CREATE OR REPLACE FUNCTION entity_save(
+    in_entity_id int, in_name text, in_entity_class INT
+) RETURNS INT AS $$
+
+    DECLARE
+        e entity;
+        e_id int;
+        
+    BEGIN
+    
+        select * into e from entity where id = in_entity_id;
+        
+        IF NOT FOUND THEN
+            -- do the insert magic.
+            e_id = nextval('entity_id_seq');
+            insert into entity (id, name, entity_class) values 
+                (e_id,
+                in_name,
+                in_entity_class
+                );
+            return e_id;
+            
+        ELSIF FOUND THEN
+            
+            update 
+                entity 
+            SET
+                name = in_name
+                entity_class = in_entity_class
+            WHERE
+                id = in_entity_id;
+            return in_entity_id;
+        END IF;
+    END;
+
+$$ language 'plpgsql';
+
+commit;
\ No newline at end of file
diff --git a/sql/modules/Person.sql b/sql/modules/Person.sql
new file mode 100644
index 00000000..1ca1bb16
--- /dev/null
+++ b/sql/modules/Person.sql
@@ -0,0 +1,52 @@
+begin;
+
+CREATE OR REPLACE FUNCTION person_save
+
+(in_id integer, in_salutation int, 
+in_first_name text, in_last_name text    
+)
+RETURNS INT AS $$
+
+    DECLARE
+        e_id int;
+        e entity;
+        loc location;
+        l_id int;
+        per person;
+        p_id int;
+    BEGIN
+    
+    select * into e from entity where id = in_id and entity_class = 3;
+    
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'No entity found for ID %', in_id;
+    END IF;
+    
+    select * into per FROM person WHERE entity_id = in_id;
+    
+    IF FOUND THEN
+    
+        -- do an update
+        
+        UPDATE person SET
+            salutation = in_salutation,
+            first_name = in_first_name,
+            last_name = in_last_name
+        WHERE
+            entity_id = in_id
+        AND
+            id = per.id;
+    
+    ELSE
+    
+        -- Do an insert
+        
+        INSERT INTO person (salutation, first_name, last_name) VALUES 
+            (in_salutation, in_first_name, in_last_name);
+                
+    
+    END IF;
+
+$$ language plpgsql;
+
+commit;
\ No newline at end of file
diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
index 5cb6773a..84b65af3 100644
--- a/sql/modules/admin.sql
+++ b/sql/modules/admin.sql
@@ -1,3 +1,12 @@
+begin;
+
+create table lsmb_roles (
+    
+    user_id integer not null references users,
+    role text not null
+    
+);
+
 CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$
     
     declare
@@ -19,7 +28,7 @@ CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) re
             RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
         END IF;
         
-        stmt := 'GRANT '|| in_role ||' to '|| in_user;
+        stmt := 'GRANT '|| quote_ident(in_role) ||' to '|| quote_ident(in_user);
         
         EXECUTE stmt;
         
@@ -49,7 +58,7 @@ CREATE OR REPLACE FUNCTION admin_remove_user_from_role(in_user TEXT, in_role TEX
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.';
         END IF;
         
-        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        stmt := 'REVOKE '|| quote_ident(in_role) ||' FROM '|| quote_ident(in_user);
         
         EXECUTE stmt;
         
@@ -79,7 +88,7 @@ CREATE OR REPLACE FUNCTION admin_add_function_to_group(in_func TEXT, in_role TEX
             RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
         END IF;
         
-        stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role;
+        stmt := 'GRANT EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' to '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -109,7 +118,7 @@ CREATE OR REPLACE FUNCTION admin_remove_function_from_group(in_func TEXT, in_rol
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.';
         END IF;
         
-        stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role;
+        stmt := 'REVOKE EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' FROM '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -147,7 +156,7 @@ CREATE OR REPLACE FUNCTION admin_add_table_to_group(in_table TEXT, in_role TEXT,
             raise exception 'Cannot add unknown permission';
         END IF;
         
-        stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role;
+        stmt := 'GRANT '|| quote_ident(in_perm) || 'ON TABLE '|| quote_ident(in_table) ||' to '|| quote_ident(in_role);
         
         EXECUTE stmt;
         
@@ -171,8 +180,6 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
             RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
         END IF;
         
-        SELECT table_schema, table_name from 
-        
         select table_name into a_table from information_schema.tables 
         where table_schema NOT IN ('information_schema','pg_catalog','pg_toast') 
         and table_type='BASE TABLE' 
@@ -182,7 +189,7 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
             RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.';
         END IF;
         
-        stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+        stmt := 'REVOKE '|| quote_literal(in_role) ||' FROM '|| quote_literal(in_user);
         
         EXECUTE stmt;
         
@@ -191,18 +198,19 @@ CREATE OR REPLACE FUNCTION admin_remove_table_from_group(in_table TEXT, in_role
         
 $$ language 'plpgsql';
 
-create or replace function admin_get_user(in_user TEXT) returns setof user as $$
+create or replace function admin_get_user(in_user TEXT) returns setof users as $$
     
     DECLARE
-        a_user user;
+        a_user users;
     BEGIN
         
-        select * into a_user from user where username = in_user;
+        select * into a_user from users where username = in_user;
         IF NOT FOUND THEN
             RAISE EXCEPTION 'cannot find user %', in_user;
         END IF;
         
-        return a_user;
+        return next a_user;
+        return;
     
     END;    
 $$ language plpgsql;
@@ -211,13 +219,13 @@ create or replace function admin_get_roles_for_user(in_user TEXT) returns setof
     
     declare
         u_role lsmb_roles;
-        a_user user;
+        a_user users;
     begin
         select * into a_user from admin_get_user(in_user);
         
-        FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP
+        FOR u_role IN select * from lsmb_roles lr WHERE lr.user_id = a_user.id LOOP
         
-            RETURN NEXT a_role;
+            RETURN NEXT u_role;
         
         END LOOP;
         RETURN;
@@ -227,82 +235,55 @@ $$ language 'plpgsql';
 
 CREATE OR REPLACE FUNCTION admin_save_user(
     in_id int, 
+    in_entity_id INT,
     in_username text, 
-    in_password TEXT, 
-    in_dbname TEXT, 
-    in_host TEXT, 
-    in_port TEXT
+    in_password TEXT
 ) returns int AS $$
     DECLARE
     
-        a_user user;
-        v_entity_id int;
+        a_user users;
+        v_user_id int;
         p_id int;
         l_id int;
         stmt text;
     BEGIN
     
-        select * into a_user from user where id = in_id;
+        select * into a_user from users lu where lu.id = in_id;
         
         IF NOT FOUND THEN 
             -- Insert cycle
             
-            --- First, create an entity.
+            --- The entity is expected to already BE created. See admin.pm.
             
             if admin_is_user(in_username) then
                 
                 -- uhm, this is bad.
-                RAISE EXCEPTION 
-                    "Fatal exception: Username already exists in Postgres; not
-                    a valid lsmb user.";
-            end if;
-            
-            v_entity_id := nextval('entity_id_seq');
-                
-            INSERT INTO entity (id, name, entity_class) VALUES (
-                v_entity_id,
-                in_first_name || ' ' || in_last_name,
-                3
-            );
-            
+                RAISE EXCEPTION 'Fatal exception: Username already exists in Postgres; not
+                    a valid lsmb user.';
+            end if;         
             -- create an actual user
-            insert into users (name, entity_id) VALUES (
+            
+            v_user_id := nextval('users_id_seq');
+            insert into users (id, name, entity_id) VALUES (
+                v_user_id,
                 in_username,
-                v_entity_id
+                in_entity_id
             );
-            
-            insert into user_connection (entity_id, database, host, port) 
-                VALUES (
-                    v_entity_id,
-                    in_database,
-                    in_host,
-                    in_port                    
-                );
-            
+
             -- Finally, issue the create user statement
             
-            stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$;
+            stmt := 'CREATE USER ' || quote_ident( in_username ) || ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password);
             execute stmt;
             
-            return v_entity_id;
+            return v_user_id ;
 
         ELSIF FOUND THEN
             
             -- update cycle
             
-            -- Only update if it's changed. Wewt.
-            UPDATE entity SET name = in_first_name || ' ' || in_last_name 
-            WHERE entity_id = a_user.entity_id and 
-            name <> in_first_name || ' ' || in_last_name;
-            
-            stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$;
+            stmt := ' alter user '|| quote_ident(in_username) || ' with encrypted password ' || quote_literal(in_password);
             execute stmt;
-            
-            update user_connection set database = in_database, host = in_host, port = in_port
-            where database <> in_database
-            OR host <> in_host
-            OR port <> in_port;
-            
+                      
             return a_user.id;
         
         END IF;
@@ -341,7 +322,7 @@ CREATE OR REPLACE FUNCTION admin_create_group(in_group_name TEXT, in_dbname TEXT
         stmt text;
         
     BEGIN
-        stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;';
+        stmt := 'create role '|| quote_ident(quote_ident(in_dbname) || '_lsmb_' || quote_ident(in_group_name) );
         execute stmt;
         return 1;
     END;
@@ -352,17 +333,17 @@ CREATE OR REPLACE FUNCTION admin_delete_user(in_username TEXT) returns INT as $$
     
     DECLARE
         stmt text;
-        a_user user;
+        a_user users;
     BEGIN
     
         select * into a_user from users where username = in_username;
         
         IF NOT FOUND THEN
         
-            raise exception "User not found.";
+            raise exception 'User not found.';
         ELSIF FOUND THEN
     
-            stmt := $$ drop user $$ || a_user.username ||;
+            stmt := ' drop user ' || quote_ident(a_user.username);
             execute stmt;
             
             -- also gets user_connection
@@ -378,7 +359,7 @@ comment on function admin_delete_user(text) is $$
     Drops the provided user, as well as deletes the entity and user configuration data.
 $$;
 
-CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$
+CREATE OR REPLACE FUNCTION admin_delete_group (in_dbname TEXT, in_group_name TEXT) returns bool as $$
     
     DECLARE
         stmt text;
@@ -390,14 +371,14 @@ CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool
         if not found then
             return 'f'::bool;
         else
-            stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;';
+            stmt := 'drop role ' || quote_ident(in_dbname || '_lsmb_' || in_group_name);
             execute stmt;
             return 't'::bool;
         end if;
     END;
 $$ language 'plpgsql';
 
-comment on function admin_delete_group(text) IS $$ 
+comment on function admin_delete_group(text,text) IS $$ 
     Deletes the input group from the database. Not designed to be used to 
     remove a login-capable user.
 $$;
@@ -425,8 +406,10 @@ $$ LANGUAGE PLPGSQL;
 -- TODO:  Add admin user
 
 
-CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
+--CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
     
     
     
-$$ language plpgsql;
\ No newline at end of file
+--$$ language plpgsql;
+
+commit;
\ No newline at end of file
-- 
cgit v1.2.3