DROP TABLE IF EXISTS `cans_agency_personnel_profile`; CREATE TABLE `cans_agency_personnel_profile` ( `person_id` int(11) NOT NULL, `username` varchar(40) default NULL, `password_hint_question` varchar(100) default NULL, `password_hint_response` blob, `status_flg` char(1) NOT NULL default 'A', `reset_password` tinyint(4) NOT NULL default '1', `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) NOT NULL, PRIMARY KEY (`person_id`), KEY `IDX_FK_cans_agency_personnel_profile` (`person_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `cans_agency_personnel_profile` VALUES (6,'Sw','edia, except if you distribute the documentation in a manner similar to how M','LE_PRIVILEGES Table\n','A',81,'1993-06-20 06:11:45',0),(-1136332546,'ols','using csh or tcsh, you must issue commands somewhat differently:\n','ownload the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs. The MySQL software uses the GPL (GNU General Public License), http://www.fsf.org/licenses/, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL code into a commercial application, you can buy a commercially licensed version from us. See the MySQL Licensing Overview for more information (http://www.mysql.com/company/legal/licensing/).\n','e',-118,'1987-10-13 15:55:52',-1236491822),(0,' *\n','xternal Locking\n','racter Set Used for Data and Sorting\n','0',81,'2008-10-09 15:51:32',0),(-717462680,'ENTS Ta','onnector/NET Version 5.0.3 (05 Ja','tion Descriptions\n','0',-45,'2002-12-21 18:06:16',0),(-904346964,'ndard SQL\n',' December 2006)\n',' C.5.7. Changes in MySQL Connector/J 1.2.x and lower\n','0',85,'2037-06-29 02:12:36',-2076134414); DROP TABLE IF EXISTS `cans_app_permission`; CREATE TABLE `cans_app_permission` ( `permission_id` int(11) NOT NULL auto_increment, `permission_name` varchar(100) default NULL, `permission_description` text, PRIMARY KEY (`permission_id`) ) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1; INSERT INTO `cans_app_permission` VALUES (1,'CREATE_ELIGIBILITIES','Create Eligibilities'),(2,'EDIT_ELIGIBILITIES','Edit Eligibilities'),(3,'DELETE_ELIGIBILITIES','Delete Eligibilities'),(4,'CREATE_CARES_CALL','Create CARES Call'),(5,'EDIT_CARES_CALL','Edit CARES Call'),(6,'ADD_CARES_STAFF_USERS','Add CARES Staff Users'),(7,'DELETE_CARES_CALL','Delete CARES CALL'),(8,'CREATE_SASS_EVAL','Create SASS Eval'),(9,'EDIT_SASS_EVAL','Edit SASS Eval'),(10,'DELETE_SASS_EVAL','Delete SASS Eval'),(11,'ADD_SASS_STAFF_USER','Add SASS Staff User'),(12,'CREATE_HOSPITALIZATION','Create Hospitalization'),(13,'EDIT_HOSPITALIZATION','Edit Hospitalization'),(14,'DELETE_HOSPITALIZATION','Delete Hospitalization'),(15,'CREATE_TRANSFER','Create Transfer'),(16,'EDIT_TRANSFER','Edit Transfer'),(17,'DELETE_TRANSFER','Delete Transfer'),(18,'CREATE_CLINICAL_EXCEPTION','Create Clinical Exception'),(19,'EDIT_CLINICAL_EXCEPTION','Edit Clinical Exception'),(20,'DELETE_CLINICAL_EXCEPTION','Delete Clinical Exception'),(21,'APPROVE_CLINICAL_EXCEPTION','Approve Clinical Exception'),(22,'NEWS','News'),(23,'OPEN_CARES_CALLS','Open CARES Calls'),(24,'OPEN_CALLS_TO_SASS','Open CALLS to SASS'),(25,'OPEN_CLINICAL_OVERRIDES','Open Clinical Overrides'),(26,'OPEN_CLINICAL_REVIEWS','Open Clinical Reviews'),(27,'OPEN_SASS_EVALUATIONS','Open SASS Evaluations'),(28,'OPEN_HOSPITALIZATIONS','Open Hospitalizations'),(29,'OPEN_TRANSFERS','Open Transfers'),(30,'OPEN_DISCHARGES','Open Discharges'),(31,'OPEN_CLINICAL_EXCEPTIONS','Open Clinical Exceptions'),(32,'VIEW_ALL_AGENCIES','View All Agencies'); DROP TABLE IF EXISTS `cans_app_role`; CREATE TABLE `cans_app_role` ( `role_id` int(11) NOT NULL auto_increment, `role_name` varchar(100) default NULL, `role_descr` text, `app_name` varchar(40) NOT NULL, PRIMARY KEY (`role_id`), KEY `IDX_FK_cans_app_config_cans_app_user_role` (`app_name`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `cans_app_role` VALUES (1,'NUCANS_APP_USER','General role for all users (you have to be in this role to access any CANS app.)','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','Role for training users','NUCANSAPP'),(3,'IA_INTAKE_COORDINATOR','Intake coordinator role for IACANS app.','IACANS'),(4,'IA_SCREENER','Clinical Screener IACANS app','IACANS'),(5,'IA_SUPERVISOR','Supervisor role IACANS App','IACANS'),(6,'IA_READONLY','Readonly role IA CANS APP','IACANS'),(7,'SOC_USER','User role in SOC CANS app','SOCCANS'),(8,'CAYIT_USER','User role for CAYIT CANS app','CAYITCANS'),(9,'RTOS_DCFSPOS_SUPERVISOR','DCFS/POS Supervisor role, RTOS','RTOS'),(10,'RTOS_DCFSPOS_CASEWORKER','caseworker','RTOS'),(11,'RTOS_DCFSPOS_MANAGER','manager dcfs/pos','RTOS'),(12,'RTOS_DCFSPOS_SUPERUSER','superuser role','RTOS'),(13,'RTOS_DCFSPOS_UIRUSER','UIR user','RTOS'),(14,'RTOS_AGENCY_ADMINISTRATOR','agency administrator','RTOS'),(15,'RTOS_AGENCY_OUTCOMESMANAGER','Outcomes manager at agency','RTOS'),(16,'RTOS_AGENCY_UIRMANAGER','Uir manager at agency','RTOS'),(17,'RTOS_AGENCY_OPERATOR','Operator at agencies','RTOS'),(18,'CARES_STAFF','SASS Cares Staff','SASS'),(19,'CARES_SUPERVISOR','SASS Cares Supervisor','SASS'),(20,'STATE_EXECUTIVE_INTERN','state executive intern','SASS'),(21,'DCFSFOR_MONITOR','Monitor for FOR','FOR'),(22,'DCFSFOR_ADMINISTRATOR','Administrator','FOR'),(23,'DCFSFOR_AGENCY_USER','Agency user','FOR'),(24,'CARES_DATA_ENTRY','cares data entry','SASS'),(25,'CARES_CLINICIAN','cares clinician','SASS'),(26,'SASS_SUPERVISOR','sass supervisor','SASS'),(27,'SASS_CASE_WORKER','sass case worker','SASS'),(28,'SASS_DATA_ENTRY','sass data entry','SASS'),(29,'SUPER_USER','super user','SASS'); DROP TABLE IF EXISTS `cans_app_role_permission`; CREATE TABLE `cans_app_role_permission` ( `role_perm_id` int(11) NOT NULL auto_increment, `role_id` int(11) NOT NULL, `permission_id` int(11) NOT NULL, `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) default NULL, PRIMARY KEY (`role_perm_id`), KEY `IDX_FK_cans_app_role_permission` (`role_id`), KEY `IDX_FK_cans_role_app_permission` (`permission_id`) ) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=latin1; INSERT INTO `cans_app_role_permission` VALUES (1,29,1,'2006-07-20 13:13:16',0),(2,29,2,'2006-07-20 13:13:16',0),(3,29,3,'2006-07-20 13:13:16',0),(4,29,4,'2006-07-20 13:13:16',0),(5,29,5,'2006-07-20 13:13:16',0),(6,29,6,'2006-07-20 13:13:16',0),(7,29,7,'2006-07-20 13:13:16',0),(8,29,8,'2006-07-20 13:13:16',0),(9,29,9,'2006-07-20 13:13:16',0),(10,29,10,'2006-07-20 13:13:16',0),(11,29,11,'2006-07-20 13:13:16',0),(12,29,12,'2006-07-20 13:13:16',0),(13,29,13,'2006-07-20 13:13:16',0),(14,29,14,'2006-07-20 13:13:16',0),(15,29,15,'2006-07-20 13:13:16',0),(16,29,16,'2006-07-20 13:13:16',0),(17,29,17,'2006-07-20 13:13:16',0),(18,29,18,'2006-07-20 13:13:16',0),(19,29,19,'2006-07-20 13:13:16',0),(20,29,20,'2006-07-20 13:13:16',0),(21,29,21,'2006-07-20 13:13:16',0),(22,29,22,'2006-07-20 13:13:16',0),(23,29,23,'2006-07-20 13:13:16',0),(24,29,24,'2006-07-20 13:13:16',0),(25,29,25,'2006-07-20 13:13:16',0),(26,29,26,'2006-07-20 13:13:16',0),(27,29,27,'2006-07-20 13:13:16',0),(28,29,28,'2006-07-20 13:13:16',0),(29,29,29,'2006-07-20 13:13:16',0),(30,29,30,'2006-07-20 13:13:16',0),(31,29,31,'2006-07-20 13:13:16',0),(32,20,1,'2006-07-20 13:13:16',0),(33,20,19,'2006-07-20 13:13:16',0),(34,20,21,'2006-07-20 13:13:16',0),(35,20,22,'2006-07-20 13:13:16',0),(36,19,4,'2006-07-20 13:13:16',0),(37,19,5,'2006-07-20 13:13:16',0),(38,19,6,'2006-07-20 13:13:16',0),(39,19,22,'2006-07-20 13:13:16',0),(40,19,23,'2006-07-20 13:13:16',0),(41,19,24,'2006-07-20 13:13:16',0),(42,19,25,'2006-07-20 13:13:16',0),(43,19,26,'2006-07-20 13:13:16',0),(44,18,4,'2006-07-20 13:13:16',0),(45,18,5,'2006-07-20 13:13:16',0),(46,18,22,'2006-07-20 13:13:16',0),(47,18,23,'2006-07-20 13:13:16',0),(48,18,24,'2006-07-20 13:13:16',0),(49,18,25,'2006-07-20 13:13:16',0),(50,18,26,'2006-07-20 13:13:16',0),(51,24,4,'2006-07-20 13:13:16',0),(52,24,5,'2006-07-20 13:13:16',0),(53,24,22,'2006-07-20 13:13:16',0),(54,25,25,'2006-07-20 13:13:16',0),(55,25,26,'2006-07-20 13:13:16',0),(56,26,8,'2006-07-20 13:13:16',0),(57,26,9,'2006-07-20 13:13:16',0),(58,26,11,'2006-07-20 13:13:16',0),(59,26,12,'2006-07-20 13:13:16',0),(60,26,13,'2006-07-20 13:13:16',0),(61,26,15,'2006-07-20 13:13:16',0),(62,26,16,'2006-07-20 13:13:16',0),(63,26,18,'2006-07-20 13:13:16',0),(64,26,19,'2006-07-20 13:13:16',0),(65,26,22,'2006-07-20 13:13:16',0),(66,26,27,'2006-07-20 13:13:16',0),(67,26,28,'2006-07-20 13:13:16',0),(68,26,29,'2006-07-20 13:13:16',0),(69,26,30,'2006-07-20 13:13:16',0),(70,26,31,'2006-07-20 13:13:16',0),(71,27,8,'2006-07-20 13:13:16',0),(72,27,9,'2006-07-20 13:13:16',0),(73,27,12,'2006-07-20 13:13:16',0),(74,27,13,'2006-07-20 13:13:16',0),(75,27,15,'2006-07-20 13:13:16',0),(76,27,16,'2006-07-20 13:13:16',0),(77,27,22,'2006-07-20 13:13:16',0),(78,27,27,'2006-07-20 13:13:16',0),(79,27,28,'2006-07-20 13:13:16',0),(80,27,29,'2006-07-20 13:13:16',0),(81,27,30,'2006-07-20 13:13:16',0),(82,27,31,'2006-07-20 13:13:16',0),(83,28,8,'2006-07-20 13:13:16',0),(84,28,9,'2006-07-20 13:13:16',0),(85,28,12,'2006-07-20 13:13:16',0),(86,28,13,'2006-07-20 13:13:16',0),(87,28,15,'2006-07-20 13:13:16',0),(88,28,16,'2006-07-20 13:13:16',0),(89,28,22,'2006-07-20 13:13:16',0),(90,28,27,'2006-07-20 13:13:16',0),(91,28,28,'2006-07-20 13:13:16',0),(92,28,29,'2006-07-20 13:13:16',0),(93,28,30,'2006-07-20 13:13:16',0),(94,28,31,'2006-07-20 13:13:16',0),(95,20,32,'2006-07-20 13:13:16',0),(96,26,32,'2006-07-20 13:13:16',0),(97,29,32,'2006-07-20 13:13:16',0); DROP TABLE IF EXISTS `cans_app_user_role`; CREATE TABLE `cans_app_user_role` ( `person_role_id` int(11) NOT NULL auto_increment, `role_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `updated_by` int(11) NOT NULL, PRIMARY KEY (`person_role_id`), KEY `IDX_FK_cans_agency_personnel_app_user_role` (`person_id`), KEY `IDX_FK_cans_app_role_app_user_role` (`role_id`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `cans_app_user_role` VALUES (1,3,6,'2007-04-10 13:18:04',0),(2,4,7,'2007-04-10 13:18:04',0),(3,5,8,'2007-04-10 13:18:04',0),(4,6,9,'2007-04-10 13:18:04',0),(5,1,6,'2007-04-10 13:18:04',0),(6,1,7,'2007-04-10 13:18:04',0),(7,1,8,'2007-04-10 13:18:04',0),(8,1,9,'2007-04-10 13:18:04',0),(9,1,10,'2007-04-10 13:18:04',0),(10,1,11,'2007-04-10 13:18:04',0),(11,1,12,'2007-04-10 13:18:04',0),(12,1,13,'2007-04-10 13:18:04',0),(13,1,14,'2007-04-10 13:18:04',0),(14,1,15,'2007-04-10 13:18:04',0),(15,1,16,'2007-04-10 13:18:04',0),(16,1,17,'2007-04-10 13:18:04',0),(17,1,18,'2007-04-10 13:18:04',0),(18,1,19,'2007-04-10 13:18:04',0),(19,18,11,'2007-04-10 13:18:04',0),(20,19,12,'2007-04-10 13:18:04',0),(21,20,13,'2007-04-10 13:18:04',0),(22,24,14,'2007-04-10 13:18:04',0),(23,25,15,'2007-04-10 13:18:04',0),(24,26,16,'2007-04-10 13:18:04',0),(25,27,17,'2007-04-10 13:18:04',0),(26,28,18,'2007-04-10 13:18:04',0),(27,29,19,'2007-04-10 13:18:04',0),(28,15,6,'2007-04-10 15:26:01',1),(29,14,9,'2007-04-13 12:07:09',1); DROP VIEW IF EXISTS `user_role_permission_view`; CREATE VIEW `user_role_permission_view` AS select (case when isnull(`perm`.`permission_id`) then concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`) else concat(concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`),_latin1'_',`perm`.`permission_id`) end) AS `id`,`profile`.`person_id` AS `person_id`,`profile`.`username` AS `username`,`role`.`role_id` AS `role_id`,`role`.`role_name` AS `role`,`perm`.`permission_id` AS `permission_id`,`perm`.`permission_name` AS `permission`,`role`.`app_name` AS `app` from ((((`cans_agency_personnel_profile` `profile` join `cans_app_user_role` `userrole` on((`userrole`.`person_id` = `profile`.`person_id`))) join `cans_app_role` `role` on((`role`.`role_id` = `userrole`.`role_id`))) left join `cans_app_role_permission` `rolperm` on((`rolperm`.`role_id` = `role`.`role_id`))) left join `cans_app_permission` `perm` on((`perm`.`permission_id` = `rolperm`.`permission_id`))) where (`profile`.`status_flg` = _latin1'A') order by `profile`.`person_id`,`role`.`app_name`,`role`.`role_name`,`perm`.`permission_name`; select userrolepe0_.person_id as person4_1_, userrolepe0_.id as id1_, userrolepe0_.id as id48_0_, userrolepe0_.permission as permission48_0_, userrolepe0_.role as role48_0_, userrolepe0_.person_id as person4_48_0_ from user_role_permission_view userrolepe0_ where userrolepe0_.person_id=6;