add sql

diff --git a/mysql/company_get_by_name.sql b/mysql/company_get_by_name.sql
new file mode 100644 (file)
index 0000000..dae31df
--- /dev/null
+++ b/mysql/company_get_by_name.sql
@@ -0,0 +1,12 @@
+
+ 
+DROP FUNCTION IF EXISTS company_get_by_name;
+DELIMITER $$
+CREATE FUNCTION company_get_by_name(in_name VARCHAR(128))  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        DECLARE v_ret INT(11);
+        SET v_ret= 0;
+        SELECT id    INTO v_ret  FROM Companies WHERE name = in_name LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ; 
\ No newline at end of file

diff --git a/mysql/company_get_name.sql b/mysql/company_get_name.sql
new file mode 100644 (file)
index 0000000..de0eb14
--- /dev/null
+++ b/mysql/company_get_name.sql
@@ -0,0 +1,12 @@
+
+ 
+DROP FUNCTION IF EXISTS company_get_name;
+DELIMITER $$
+CREATE FUNCTION company_get_name(in_id INT(11))  RETURNS VARCHAR(254) DETERMINISTIC
+    BEGIN
+        DECLARE v_ret VARCHAR(254);
+        SET v_ret= '';
+        SELECT name INTO v_ret  FROM Companies WHERE id = in_id LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ;
\ No newline at end of file

diff --git a/mysql/company_get_owner.sql b/mysql/company_get_owner.sql
new file mode 100644 (file)
index 0000000..29004bb
--- /dev/null
+++ b/mysql/company_get_owner.sql
@@ -0,0 +1,12 @@
+DROP FUNCTION IF EXISTS company_get_owner;
+
+DELIMITER $$
+CREATE FUNCTION company_get_owner()  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        DECLARE v_id INT(11);
+        SET v_id = 0;
+        SELECT id INTO v_id  FROM Companies WHERE isOwner = 1 LIMIT 1;
+        RETURN v_id;
+    END $$
+DELIMITER ;
+

diff --git a/mysql/company_get_owner_name.sql b/mysql/company_get_owner_name.sql
new file mode 100644 (file)
index 0000000..07a2149
--- /dev/null
+++ b/mysql/company_get_owner_name.sql
@@ -0,0 +1,12 @@
+
+DROP FUNCTION IF EXISTS company_get_owner_name;
+DELIMITER $$
+CREATE FUNCTION company_get_owner_name()  RETURNS VARCHAR(254) DETERMINISTIC
+    BEGIN
+        DECLARE v_ret VARCHAR(254);
+        SET v_ret= '';
+        SELECT name INTO v_ret  FROM Companies WHERE isOwner = 1 LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ;
+ 
\ No newline at end of file

diff --git a/mysql/person_get_action_type.sql b/mysql/person_get_action_type.sql
new file mode 100644 (file)
index 0000000..dcd3680
--- /dev/null
+++ b/mysql/person_get_action_type.sql
@@ -0,0 +1,13 @@
+
+DROP FUNCTION IF EXISTS person_get_action_type;
+
+DELIMITER $$
+CREATE FUNCTION person_get_action_type(in_id INT(11))  RETURNS VARCHAR(32) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_ret VARCHAR(32);
+        SET v_ret= '';
+        SELECT action_type INTO v_ret  FROM Person WHERE id = in_id  LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ;

diff --git a/mysql/person_get_by_email.sql b/mysql/person_get_by_email.sql
new file mode 100644 (file)
index 0000000..a098e61
--- /dev/null
+++ b/mysql/person_get_by_email.sql
@@ -0,0 +1,18 @@
+
+DROP FUNCTION IF EXISTS person_get_by_email;
+
+DELIMITER $$
+CREATE FUNCTION person_get_by_email(  
+        in_addr VARCHAR(254)
+        
+
+    )  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_id INT(11);
+        SET v_id = 0;
+        SELECT id INTO v_id FROM Person WHERE email = in_addr LIMIT 1;
+        RETURN v_id;
+        
+    END $$
+DELIMITER ;

diff --git a/mysql/person_get_company.sql b/mysql/person_get_company.sql
new file mode 100644 (file)
index 0000000..a5dc09e
--- /dev/null
+++ b/mysql/person_get_company.sql
@@ -0,0 +1,15 @@
+
+DROP FUNCTION IF EXISTS person_get_company;
+
+DELIMITER $$
+CREATE FUNCTION person_get_company(  
+       in_id INT(11)
+    )  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_id INT(11);
+        SET v_id = 0;
+        SELECT company_id INTO v_id FROM Person WHERE id  = in_id LIMIT 1;
+        RETURN v_id;
+    END $$
+DELIMITER ;

diff --git a/mysql/person_get_internal_id.sql b/mysql/person_get_internal_id.sql
new file mode 100644 (file)
index 0000000..ee673b8
--- /dev/null
+++ b/mysql/person_get_internal_id.sql
@@ -0,0 +1,32 @@
+
+DROP FUNCTION IF EXISTS person_get_internal_id;
+
+DELIMITER $$
+CREATE FUNCTION person_get_internal_id(  
+        in_addr VARCHAR(254)
+        
+
+    )  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_id INT(11);
+        DECLARE v_company_id INT(11);
+        SET v_id = 0;
+        SET v_company_id = 0;
+        
+        SELECT company_get_owner() INTO v_company_id;
+        IF v_company_id < 1 THEN
+            RETURN 0;
+        END IF;
+        
+        SELECT id INTO v_id FROM Person WHERE
+            company_id = v_company_id
+            AND email = in_addr LIMIT 1;
+        
+        RETURN v_id;
+        
+    
+        
+        
+    END $$
+DELIMITER ;

diff --git a/mysql/person_get_name.sql b/mysql/person_get_name.sql
new file mode 100644 (file)
index 0000000..f25d4bc
--- /dev/null
+++ b/mysql/person_get_name.sql
@@ -0,0 +1,14 @@
+
+
+DROP FUNCTION IF EXISTS person_get_name;
+
+DELIMITER $$
+CREATE FUNCTION person_get_name(in_id INT(11))  RETURNS VARCHAR(254) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_ret VARCHAR(254);
+        SET v_ret= '';
+        SELECT name INTO v_ret  FROM Person WHERE id = in_id  LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ;
\ No newline at end of file

diff --git a/mysql/person_get_project_id.sql b/mysql/person_get_project_id.sql
new file mode 100644 (file)
index 0000000..90fab7b
--- /dev/null
+++ b/mysql/person_get_project_id.sql
@@ -0,0 +1,18 @@
+
+
+DROP FUNCTION IF EXISTS person_get_project_id;
+
+DELIMITER $$
+CREATE FUNCTION person_get_project_id(  
+       in_id INT(11)
+    )  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_id INT(11);
+        SET v_id = 0;
+        SELECT project_id INTO v_id FROM Person WHERE id  = in_id LIMIT 1;
+        RETURN v_id;
+    END $$
+
+
+DELIMITER ;

diff --git a/mysql/person_has_perm.sql b/mysql/person_has_perm.sql
new file mode 100644 (file)
index 0000000..a051137
--- /dev/null
+++ b/mysql/person_has_perm.sql
@@ -0,0 +1,30 @@
+
+
+
+DROP FUNCTION IF EXISTS person_has_perm;
+
+DELIMITER $$
+CREATE FUNCTION person_has_perm(  
+       in_person_id INT(11),
+       in_perm_name VARCHAR(64),
+       in_perm_level VARCHAR(1)
+    )  RETURNS INT(11) DETERMINISTIC
+    BEGIN
+        
+        DECLARE v_id INT(11);
+        SET v_id = 0;
+        SELECT
+                group_members.id INTO v_id 
+            FROM
+                group_rights 
+            LEFT JOIN
+                group_members
+            ON 
+                group_rights.group_id = group_members.group_id  
+            WHERE 
+                group_rights.rightname= in_perm_name AND 
+                accessmask LIKE CONCAT('%', in_perm_level, '%') AND user_id = in_person_id LIMIT 1;
+        
+        RETURN v_id;
+    END $$
+DELIMITER ;

diff --git a/mysql/project_get_type.sql b/mysql/project_get_type.sql
new file mode 100644 (file)
index 0000000..f4a757e
--- /dev/null
+++ b/mysql/project_get_type.sql
@@ -0,0 +1,11 @@
+
+DROP FUNCTION IF EXISTS project_get_type;
+DELIMITER $$
+CREATE FUNCTION project_get_type(in_id INT(11))  RETURNS VARCHAR(2) DETERMINISTIC
+    BEGIN
+        DECLARE v_ret VARCHAR(2);
+        SET v_ret= '';
+        SELECT type INTO v_ret  FROM Projects WHERE id = in_id LIMIT 1;
+        RETURN v_ret;
+    END $$
+DELIMITER ;
\ No newline at end of file