SF.net SVN: ledger-smb: [1875] trunk/sql/modules/Employee.sql

[..hidden..]

Revision: 1875
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1875&view=rev
Author:   einhverfr
Date:     2007-11-17 17:19:37 -0800 (Sat, 17 Nov 2007)

Log Message:
-----------
Employee.sql QA first run commits

Modified Paths:
--------------
    trunk/sql/modules/Employee.sql

Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql	2007-11-18 01:06:11 UTC (rev 1874)
+++ trunk/sql/modules/Employee.sql	2007-11-18 01:19:37 UTC (rev 1875)
@@ -50,7 +50,7 @@
                 enddate = in_enddate,
                 role = in_role,
                 sales = in_sales,
-                manager_id = in_managerid
+                manager_id = in_managerid,
                 employeenumber = in_employeenumber,
                 dob = in_dob
             WHERE
@@ -66,13 +66,13 @@
 
 create view employees as
     select 
-        e.salutation,
-        e.first_name,
-        e.last_name,
+        s.salutation,
+        p.first_name,
+        p.last_name,
         ee.*
-    FROM entity e
-    JOIN entity_employees ee on e.id = ee.entity_id
-    where e.entity_class = 3;
+    FROM person p
+    JOIN entity_employee ee USING (entity_id)
+    JOIN salutation s ON (p.salutation_id = s.id);
     
 
 -- why is this like this?
@@ -84,13 +84,14 @@
 	emp employees%ROWTYPE;
 BEGIN
 	SELECT 
-	    e.salutation, 
-	    e.first_name,
-	    e.last_name,
+	    s.salutation, 
+	    p.first_name,
+	    p.last_name,
 	    ee.* 
 	INTO emp 
     FROM employees ee 
-    join entity e on ee.entity_id = e.id 
+    join person p USING (entity_id)
+    JOIN salutation s ON (p.salutation_id = s.id)
     WHERE ee.entity_id = in_id;
     
 	RETURN emp;
@@ -134,14 +135,13 @@
 --
 -- % type is pg_trgm comparison.
 
-CREATE INDEX name_idx ON employee USING gist(name gist_trgm_ops);
 CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops);
 
 CREATE OR REPLACE VIEW employee_search AS
 SELECT e.*, em.name AS manager, emn.note, en.name as name
-FROM employee e 
+FROM entity_employee e 
 LEFT JOIN entity en on (e.entity_id = en.id)
-LEFT JOIN entity_employee m ON (e.managerid = m.entity_id)
+LEFT JOIN entity_employee m ON (e.manager_id = m.entity_id)
 LEFT JOIN entity em on (em.id = m.entity_id)
 LEFT JOIN entity_note emn on (emn.ref_key = em.id);
 
@@ -179,10 +179,8 @@
 returns void as $$
 
     INSERT INTO person_to_location (person_id,location_id) 
-        VALUES (in_employee, in_location);
+        VALUES ($1, $2);
     
-    SELECT NULL;
-
 $$ language 'sql';
 
 COMMIT;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.