diff options
Diffstat (limited to 'public/app/models')
| -rw-r--r-- | public/app/models/Access_model.php | 51 | ||||
| -rw-r--r-- | public/app/models/Content_model.php | 101 | ||||
| -rw-r--r-- | public/app/models/Office_model.php | 314 | ||||
| -rw-r--r-- | public/app/models/_info.md | 240 |
4 files changed, 289 insertions, 417 deletions
diff --git a/public/app/models/Access_model.php b/public/app/models/Access_model.php index 023d361..844ff6b 100644 --- a/public/app/models/Access_model.php +++ b/public/app/models/Access_model.php @@ -33,6 +33,19 @@ class Access_model } + /** getUserByInvitation + * + * @param string $invitation + */ + public static function getUserByInvitation($invitation) + { + return Registry::use('database')->query( + "SELECT * FROM user WHERE invitation = :invitation", + ['invitation' => $invitation] + )->getFirst(); + } + + /** create user * * creates user record; @@ -156,24 +169,6 @@ class Access_model ## Set permission methods ## ------------------------------------------------------------------------- - - /** set_user_privileges - * - * @param $privileges (array) - */ - public static function set_user_privileges($user, $privileges) - { - $db = Registry::use('database'); // database connection - foreach($privileges as $pri) { // pri = privilege id - $db->runQuery( - "INSERT INTO user_privilege (user_id, privilege_id) VALUES (:user, :pri)", - [ ':user' => $user, ":pri" => $pri ] - ); - } - return true; - } - - /** set_user_role * * user, role are (int) IDs @@ -190,24 +185,4 @@ class Access_model } - ## List methods - ## ------------------------------------------------------------------------- - - - /** privileges - * --- - * get all privileges (as a list) - * used by cacher; speeds up seveal things - * - * @param void - * @return privileges (array) - */ - public static function privileges() - { - return Registry::use('database')->runQuery( - "SELECT * FROM privilege",[] - ); - } - - } diff --git a/public/app/models/Content_model.php b/public/app/models/Content_model.php new file mode 100644 index 0000000..ea19165 --- /dev/null +++ b/public/app/models/Content_model.php @@ -0,0 +1,101 @@ +<?php +namespace app\models; + +use \Registry; + + +class Content_model { + + ## P E T I T I O N S + ## ------------------------------------------------------------------------- + + /** add petition + * + * add petition record to the database + * + * @param array $data + * @return int new petition-ID + */ + public static function add_petition($data) + { + return Registry::use('database')->query( + "INSERT INTO petition + (user_id, `type_id`, `subject`, `signature`, `form_structure`) + VALUES (:user_id, :type_id, :subject, :signature, :form_structure)", + [ + ':user_id' => $data['user_id'], + ':type_id' => $data['type_id'], + ':subject' => $data['subject'], + ':signature' => $data['signature'], + ':form_structure' => $data['form_structure'] + ] + )->lastInsertID(); + } + + + /** set protocol + * + * @param int $pid: petition ID + * @param string $protocol: official pritocol number (full string) + */ + public static function set_protocol($pid, $protocol) + { + Registry::use('database')->runQuery( + "UPDATE petition SET protocol = :protocol WHERE id = :pid", + [ + ':protocol' => $protocol, + ':pid' => $pid + ] + ); + return true; + } + + + /** user petitions + * + * get all petitions of a user + * + * @param int $uid: user id + * @return array petition records + */ + public static function user_petitions($uid) + { + return Registry::use('database')->runQuery( + "SELECT * FROM petition WHERE user_id = :uid", + [ ':uid' => $uid ] + ); + } + + + /** all petitions + * + * get all petitions of a user + * + * @param void + * @return array petition records + */ + public static function all_petitions() + { + return Registry::use('database')->runQuery( + "SELECT * FROM petition", + [] + ); + } + + + + ## F I L E S + ## ------------------------------------------------------------------------- + + /** files + * return all files + * @param void + * @return array + */ + public static function files() + { + return Registry::use('database')->runQuery("SELECT * from media", []); + } + + +} diff --git a/public/app/models/Office_model.php b/public/app/models/Office_model.php deleted file mode 100644 index fad11b8..0000000 --- a/public/app/models/Office_model.php +++ /dev/null @@ -1,314 +0,0 @@ -<?php -namespace app\models; - -use \Registry; - - -class Cms_model { - - /** COURSES - * ------------------------------------------------------------------------- - */ - - /** get all categories - * - * raw table data (simplest SELECT) - * - */ - public static function get_categories() - { - return Registry::use('database')->runQuery( - "SELECT * FROM course ORDER BY label", - [] - ); - } - - /** courses struct - * - * a super array with almost any info needed about courses - * - * @return (array) ['tree' => ..., 'breadcrumbs' => ... ] - */ - public static function courses_struct() - { - $tree = self::category_tree(); - return [ - 'tree' => $tree, - 'breadcrumbs' => self::breadcrumbs($tree) - ]; - } - - - /** constuct a category_tree - * - * returns a tree representation of the categories - * - * NOTE: - * category_tree() is an expensive method; - * it calls 2 other methods implementing recursive algorithms - * thus it uses many sources to run (particularly RAM). - * Caching the result is strogly recommended. - * - */ - public static function category_tree() - { - $categories = self::get_categories(); // get all categories - - $tree = self::to_tree($categories); // format to a tree - - $tree_wParents = self::tree_parents($tree); // add parents section for each tree-node - - return $tree_wParents; - } - - - /** to_tree - * - * constructs a tree from raw-table data; - * this is a private method and uses a recursive algorithm - * - * @param $dataset (array): flar array of records with id/parent-id pairs - * @return $root (array): id of root category - * - * (**) each node has 2 parts: - * .... .. rec : all record attributes/data as passed into $dataset - * .... .. childs : array of (children) nodes - */ - private static function to_tree($dataset, $root = 0) - { - $return = []; - - // loop data ; search for direct children of root - foreach($dataset as $key => $rec) { - - $child = $rec['id']; - $parent = $rec['parent_id']; - - if ($parent == $root) { // a direct child is found - - unset($dataset[$key]); // remove item (no need to traverse again) - - // Append the child into result array ; parse its children - $return[] = [ - 'rec' => [ - 'id' => $rec['id'], - 'label' => $rec['label'], - 'order' => $rec['order'], - 'parent' => $rec['parent_id'] - ], - 'childs' => self::to_tree($dataset, $child) // recursively - ]; - } - } - return empty($return) ? [] : $return; - } - - - /** tree_parents - * - * adds a section to each tree node with all parents of each node - * - * @param $tree (array) : nodes array (each node has `rec` and `childs` sections ) - * @param $parents (array); DO NOT SET IT (takes values automaticaly) - * @return array of nodes with an extra node[parents] section - * - */ - private static function tree_parents($tree, $parents = []) - { - $tree_with_parents = []; - - foreach($tree as $key => $node) { - // parents to be pushed for node's children - $push_parents = $parents; // parents so far - $push_parents[] = $node['rec']; // this record will be a new parent - - $tree_with_parents[$key] = [ - 'rec' => $node['rec'], - 'parents' => $parents, - 'childs' => ($node['childs'] == []) - ? [] - : self::tree_parents($node['childs'], $push_parents) - ]; - } - - return $tree_with_parents; - } - - - /** all_breadcrumbs - * ------------------------------------------------------------------------- - * - * returns an array of all breadcrumbs - * where array-key of each record is category[id] - * - * NOTE: - * --- - * Cms_model::all_breadcrumbs returns an indexed super-array; - * each array item includes a banch of information: [ - * breadcrumb, - * rec: [ id , title ], - * parents: [ [id, title] , ... ] - * childs: [ [id, title] , ... ], - * level - * ] - * - * Use Cases: - * --- - * as a super-array, the output can be used in many cases - * for example... - * into <select-option> form elements - * .. while selecting category for a post - * .. or editing a category - * or directry referring to category's parents/childs - * - * Arguments: - * --- - * @param $tree (array) : category tree (with childs and parents parts) - * @param $detimiter (string, optional) : string to split breadcrumb's path-nodes - * @param $exception (int, optional) : id of category to exclude (subcategories shall be excluded too) - * @param $l (int, not-pass) : depth level of the node; DO NOT SET (takes values automaticaly) - * @return array of breadcrumbs - * ------------------------------------------------------------------------- - */ - static public function breadcrumbs($tree, $delimiter = " / ", $exception = 0, $l = 0) - { - $all = []; // results array - - foreach($tree as $node) { // loop through all nodes - - if (intval($node['rec']['id']) != $exception) { // if node is not exception - - // construct breadcrumb html of node - // --- -- -- - - - - $breadcrumb = ""; - foreach($node['parents'] as $par) { // first: join path titles - $breadcrumb .= $par['label'] . $delimiter; - } - $breadcrumb .= $node['rec']['label']; // last: append title - - // make a new super record - // --- -- -- - - - - $all[$node['rec']['id']] = [ // set record is as key - 'breadcrumb' => $breadcrumb, // add breadcrump to results - 'rec' => $node['rec'], // + node info - 'parents' => $node['parents'], // + parents array - 'childs' => self::first_level_childs($node), // + direct childs - 'level' => $l // + level - ]; - - // recursively traverse children nodes - // --- -- -- - - - - if (isset($node['childs']) && $node['childs'] != []) { - $child_breadcrumbs = self::breadcrumbs( - $node['childs'], - $delimiter, - $exception, - $l+1 - ); - - $all = $all + $child_breadcrumbs; // concatenate arrays (keep array-keys) - } - } - - } - return $all; - - } - - /** first_level_childs - * --- -- -- - - - - * used by all_breadcrumbs() - */ - static private function first_level_childs($node) - { - $childs = []; - if ($node['childs'] == []) { - return []; - } - foreach($node['childs'] as $key => $kid) { - $childs[] = [ - 'id' => $kid['rec']['id'], - 'label' => $kid['rec']['label'] - ]; - } - return $childs; - } - - - - - /** LESSONS - * ------------------------------------------------------------------------- - */ - - - /** lessons of course - * - * @param $id (int) : course_id - */ - public static function lessons_of_course($id) - { - // TODO: order results in some way - - return Registry::use('database')->runQuery( - "SELECT lesson.*, lesson_privilege.privilege_id FROM lesson - LEFT JOIN lesson_privilege ON lesson_privilege.lesson_id = lesson.id - WHERE lesson.status = 1 AND lesson.course_id = :id - ORDER BY lesson_privilege.privilege_id ASC", - [':id' => $id] - ); - } - - /** lesson - * - * @param $id (int) : lesson id - */ - public static function lesson($id) - { - // TODO: order results in some way - - return Registry::use('database')->query( - "SELECT lesson.*, lesson_privilege.privilege_id FROM lesson - LEFT JOIN lesson_privilege ON lesson_privilege.lesson_id = lesson.id - WHERE lesson.status = 1 AND lesson.id = :id", - [':id' => $id] - )->getFirst(); - } - - - - /** files - * return all files - * @param void - * @return array - */ - public static function files() - { - return Registry::use('database')->runQuery("SELECT * from media", []); - } - - - /** pages - * - * return all pages as array; - * array key of each item shall be the page-id - * - * @param void - * @return array - */ - public static function pages() - { - $result = []; - $pages = Registry::use('database')->runQuery("SELECT * FROM page", []); - foreach($pages as $key => $page) { - $result[ $page['id'] ] = [ - 'title' => $page['title'], - 'body' => $page['body'], - 'status' => $page['status'] - ]; - } - // print_r($result); die(); - return $result; - } - -} diff --git a/public/app/models/_info.md b/public/app/models/_info.md index ac6d334..9f02061 100644 --- a/public/app/models/_info.md +++ b/public/app/models/_info.md @@ -3,68 +3,72 @@ ## user fields; insert / update sql queries -INSERT INTO user -( id, - prefix, - first_name, - last_name, - email, - father_name, - registration_number, - sector_id, - specialty, - belonging_school, - working_shcool, - position_type_id, - phone, - password, - expiration, - otp, - otp_expiration, - activation, - creation, -active) VALUES (:id, - :prefix, - :first_name, - :last_name, - :email, - :father_name, - :registration_number, - :sector_id, - :specialty, - :belonging_school, - :working_shcool, - :position_type_id, - :phone, - :password, - :expiration, - :otp, - :otp_expiration, - :activation, - :creation, -:active ) - - -UPDATE user -SET prefix = :prefix, -first_name = :first_name, -last_name = :last_name, -email = :email, -father_name = :father_name, -registration_number = :registration_number, -sector_id = :sector_id, -specialty = :specialty, -belonging_school = :belonging_school, -working_shcool = :working_shcool, -position_type_id = :position_type_id, -phone = :phone, -password = :password, -expiration = :expiration, -otp = :otp, -otp_expiration = :otp_expiration, -activation = :activation, -creation = :creation, -active = :active, +Insert user: + + INSERT INTO user + ( id, + prefix, + first_name, + last_name, + email, + father_name, + registration_number, + sector_id, + specialty, + belonging_school, + working_shcool, + position_type_id, + phone, + password, + expiration, + otp, + otp_expiration, + activation, + creation, + active) VALUES (:id, + :prefix, + :first_name, + :last_name, + :email, + :father_name, + :registration_number, + :sector_id, + :specialty, + :belonging_school, + :working_shcool, + :position_type_id, + :phone, + :password, + :expiration, + :otp, + :otp_expiration, + :activation, + :creation, + :active ) + + +Update user + + UPDATE user + SET prefix = :prefix, + first_name = :first_name, + last_name = :last_name, + email = :email, + father_name = :father_name, + registration_number = :registration_number, + sector_id = :sector_id, + specialty = :specialty, + belonging_school = :belonging_school, + working_shcool = :working_shcool, + position_type_id = :position_type_id, + phone = :phone, + password = :password, + expiration = :expiration, + otp = :otp, + otp_expiration = :otp_expiration, + activation = :activation, + creation = :creation, + active = :active, ## record_types @@ -158,16 +162,122 @@ call_user_func(array($myobject, 'say_hello')); \ controllers [ ] App - [ ] Auth + [*] Auth \ extends [ ] Cache_service [*] App_manager [*] App_user [ ] SendMail_service - [ ] Form_builder + [*] Form_builder \ models [ ] Access_model - [ ] History_model
\ No newline at end of file + [ ] History_model + + + +# DataBase Structure + + + + SET NAMES utf8; + SET time_zone = '+00:00'; + SET foreign_key_checks = 0; + SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; + + SET NAMES utf8mb4; + + DROP TABLE IF EXISTS `history`; + CREATE TABLE `history` ( + `id` bigint(20) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `type` smallint(6) NOT NULL, + `message` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL, + `note` text COLLATE utf8mb4_unicode_ci, + `ip` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + CONSTRAINT `history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + + DROP TABLE IF EXISTS `media`; + CREATE TABLE `media` ( + `id` bigint(20) NOT NULL AUTO_INCREMENT, + `title` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL, + `user_id` int(11) NOT NULL, + `path` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL, + `type` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'media-type', + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + CONSTRAINT `media_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + + DROP TABLE IF EXISTS `petition`; + CREATE TABLE `petition` ( + `id` bigint(20) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `type_id` smallint(6) NOT NULL, + `form_structure` text COLLATE utf8mb4_unicode_ci NOT NULL, + `protocol` int(11) DEFAULT NULL, + `signature` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, + `record_date` date DEFAULT NULL, + `creation_date` datetime DEFAULT CURRENT_TIMESTAMP, + `update_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `signature` (`signature`), + KEY `user_id` (`user_id`), + CONSTRAINT `petition_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + + DROP TABLE IF EXISTS `petition_media`; + CREATE TABLE `petition_media` ( + `petition_id` bigint(20) NOT NULL, + `media_id` bigint(20) NOT NULL, + KEY `petition_id` (`petition_id`), + KEY `media_id` (`media_id`), + CONSTRAINT `petition_media_ibfk_1` FOREIGN KEY (`petition_id`) REFERENCES `petition` (`id`), + CONSTRAINT `petition_media_ibfk_2` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + + DROP TABLE IF EXISTS `role`; + CREATE TABLE `role` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `alias` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, + `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + + DROP TABLE IF EXISTS `user`; + CREATE TABLE `user` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `prefix` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `first_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, + `last_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, + `email` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL, + `father_name` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `registration_number` bigint(20) DEFAULT NULL COMMENT 'AM', + `sector` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'sector_id + speciality', + `belonging_school` varchar(96) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `position` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'chief, permanent, deputy etc', + `phone` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `password` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `role_id` int(11) DEFAULT NULL, + `expiration` datetime DEFAULT NULL COMMENT 'account expiration datetime', + `otp` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'one time password for reset password', + `otp_expiration` datetime DEFAULT NULL, + `invitation` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'invitation code', + `creation_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation datetime', + `update_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `active` smallint(6) NOT NULL DEFAULT '0' COMMENT 'account status flag; 1=active 0=inactive', + PRIMARY KEY (`id`), + KEY `role_id` (`role_id`), + CONSTRAINT `user_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE NO ACTION + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + |