223 lines
9.0 KiB
SQL
223 lines
9.0 KiB
SQL
/*
|
|
SQLyog Enterprise - MySQL GUI v8.12
|
|
MySQL - 5.5.5-10.1.16-MariaDB : Database - surat_pnm
|
|
*********************************************************************
|
|
*/
|
|
|
|
|
|
/*!40101 SET NAMES utf8 */;
|
|
|
|
/*!40101 SET SQL_MODE=''*/;
|
|
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
|
|
CREATE DATABASE /*!32312 IF NOT EXISTS*/`surat_pnm` /*!40100 DEFAULT CHARACTER SET latin1 */;
|
|
|
|
USE `surat_pnm`;
|
|
|
|
/*Table structure for table `master_menu` */
|
|
|
|
DROP TABLE IF EXISTS `master_menu`;
|
|
|
|
CREATE TABLE `master_menu` (
|
|
`mm_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`mm_nama` varchar(50) DEFAULT NULL,
|
|
`mm_link` varchar(50) DEFAULT NULL,
|
|
`mm_icon` varchar(50) DEFAULT NULL,
|
|
`mm_parent` int(11) DEFAULT NULL,
|
|
`mm_level` int(11) DEFAULT NULL,
|
|
`mm_action` varchar(15) DEFAULT NULL,
|
|
`mm_aktif` int(11) DEFAULT NULL,
|
|
`mm_urutan` int(2) DEFAULT NULL,
|
|
PRIMARY KEY (`mm_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
|
|
|
|
/*Data for the table `master_menu` */
|
|
|
|
insert into `master_menu`(`mm_id`,`mm_nama`,`mm_link`,`mm_icon`,`mm_parent`,`mm_level`,`mm_action`,`mm_aktif`,`mm_urutan`) values (1,'Dashboard',NULL,'fa fa-dashboard',NULL,1,NULL,1,1),(2,'Setting','#','fa fa-gears',NULL,1,NULL,1,2),(3,'Hak Akses','ps_hakakses',NULL,2,2,'true',1,3),(4,'User Login','ps_userlogin',NULL,2,2,'true',1,4),(5,'Surat','#','fa fa-envelope',NULL,1,NULL,1,6),(6,'Surat Masuk','ps_suratmasuk',NULL,5,2,'true',1,7),(7,'PPTK','ps_pptk',NULL,5,2,'true',1,8),(8,'Disposisi','ps_disposisi',NULL,5,2,'true',1,9),(9,'SK Direktur','ps_skdirektur',NULL,5,2,'true',1,10),(10,'Surat Keluar','ps_suratkeluar',NULL,5,2,'true',1,11),(11,'Laporan','ps_laporan','fa fa-book',NULL,1,NULL,1,12),(12,'Unit Kerja','ps_unitkerja',NULL,2,2,'true',1,5);
|
|
|
|
/*Table structure for table `master_tipeuser` */
|
|
|
|
DROP TABLE IF EXISTS `master_tipeuser`;
|
|
|
|
CREATE TABLE `master_tipeuser` (
|
|
`mt_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`mt_nama` varchar(20) DEFAULT NULL,
|
|
PRIMARY KEY (`mt_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
|
|
|
|
/*Data for the table `master_tipeuser` */
|
|
|
|
insert into `master_tipeuser`(`mt_id`,`mt_nama`) values (1,'Super Admin'),(2,'Admin');
|
|
|
|
/*Table structure for table `master_unitkerja` */
|
|
|
|
DROP TABLE IF EXISTS `master_unitkerja`;
|
|
|
|
CREATE TABLE `master_unitkerja` (
|
|
`muk_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`muk_namalengkap` varchar(50) DEFAULT NULL,
|
|
`muk_singkatan` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`muk_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `master_unitkerja` */
|
|
|
|
insert into `master_unitkerja`(`muk_id`,`muk_namalengkap`,`muk_singkatan`) values (1,'Instalasi Penunjang Non Medik','PNM'),(2,'Instalasi Teknologi Informatika dan Komunikasi','ITIKOM'),(4,'Instalasi Pembangkit Setrum Non Medik','IPS NM');
|
|
|
|
/*Table structure for table `master_user` */
|
|
|
|
DROP TABLE IF EXISTS `master_user`;
|
|
|
|
CREATE TABLE `master_user` (
|
|
`mu_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`mu_nama_lengkap` varchar(50) DEFAULT NULL,
|
|
`mu_nama_user` varchar(50) DEFAULT NULL,
|
|
`mu_password` varchar(50) DEFAULT NULL,
|
|
`mu_tipeuser_id` int(11) DEFAULT NULL,
|
|
`mu_unitkerja_id` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`mu_id`),
|
|
KEY `user_tipeuser_FK` (`mu_tipeuser_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
|
|
|
|
/*Data for the table `master_user` */
|
|
|
|
insert into `master_user`(`mu_id`,`mu_nama_lengkap`,`mu_nama_user`,`mu_password`,`mu_tipeuser_id`,`mu_unitkerja_id`) values (1,'Ragil Bayu Nugroho','hamigaki','afa7f6fb4dd3bb49e6960b6f4c74ce3d',1,NULL),(2,'Reza Yuliani','reza','afa7f6fb4dd3bb49e6960b6f4c74ce3d',2,1);
|
|
|
|
/*Table structure for table `master_user_menu` */
|
|
|
|
DROP TABLE IF EXISTS `master_user_menu`;
|
|
|
|
CREATE TABLE `master_user_menu` (
|
|
`mum_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`mum_tambah` varchar(10) DEFAULT NULL,
|
|
`mum_ubah` varchar(10) DEFAULT NULL,
|
|
`mum_hapus` varchar(10) DEFAULT NULL,
|
|
`mum_lihat` varchar(10) DEFAULT NULL,
|
|
`mum_tipeuser_id` int(11) DEFAULT NULL,
|
|
`mum_menu_id` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`mum_id`),
|
|
KEY `usermenu_tipeuser_FK` (`mum_tipeuser_id`),
|
|
KEY `usermenu_menu_FK` (`mum_menu_id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
|
|
|
|
/*Data for the table `master_user_menu` */
|
|
|
|
insert into `master_user_menu`(`mum_id`,`mum_tambah`,`mum_ubah`,`mum_hapus`,`mum_lihat`,`mum_tipeuser_id`,`mum_menu_id`) values (4,NULL,NULL,NULL,NULL,1,1),(5,NULL,NULL,NULL,NULL,1,2),(6,'tambah','ubah','hapus','lihat',1,3),(7,'tambah','ubah','hapus','lihat',1,4),(8,'tambah','ubah','hapus','lihat',1,12),(9,NULL,NULL,NULL,'lihat',1,5),(10,'tambah','ubah','hapus','lihat',1,6),(11,'tambah','ubah','hapus','lihat',1,7),(12,'tambah','ubah','hapus','lihat',1,8),(13,'tambah','ubah','hapus','lihat',1,9),(14,'tambah','ubah','hapus','lihat',1,10),(15,NULL,NULL,NULL,'lihat',1,11),(16,NULL,NULL,NULL,NULL,2,1),(17,NULL,NULL,NULL,NULL,2,2),(18,'tambah','ubah',NULL,'lihat',2,3),(19,'tambah','ubah',NULL,'lihat',2,4),(20,'tambah','ubah','hapus','lihat',2,12),(21,NULL,NULL,NULL,'lihat',2,5),(22,'tambah','ubah','hapus','lihat',2,6),(23,'tambah','ubah','hapus','lihat',2,7),(24,'tambah','ubah','hapus','lihat',2,8),(25,'tambah','ubah','hapus','lihat',2,9),(26,'tambah','ubah','hapus','lihat',2,10),(27,NULL,NULL,NULL,'lihat',2,11);
|
|
|
|
/*Table structure for table `proses_disposisi` */
|
|
|
|
DROP TABLE IF EXISTS `proses_disposisi`;
|
|
|
|
CREATE TABLE `proses_disposisi` (
|
|
`pd_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`pd_nomor` varchar(5) DEFAULT NULL,
|
|
`pd_tgl_input` date DEFAULT NULL,
|
|
`pd_tgl_disposisi` date DEFAULT NULL,
|
|
`pd_nomor_surat` varchar(50) DEFAULT NULL,
|
|
`pd_nomor_agenda` varchar(50) DEFAULT NULL,
|
|
`pd_perihal` text,
|
|
`pd_asal_surat` varchar(20) DEFAULT NULL,
|
|
`pd_proses` int(1) DEFAULT NULL,
|
|
`pd_keterangan` text,
|
|
`pd_dokumen` varchar(20) DEFAULT NULL,
|
|
`pd_unitkerja_user_id` int(11) DEFAULT NULL,
|
|
`pd_aktif` int(1) DEFAULT NULL,
|
|
PRIMARY KEY (`pd_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `proses_disposisi` */
|
|
|
|
/*Table structure for table `proses_pptk` */
|
|
|
|
DROP TABLE IF EXISTS `proses_pptk`;
|
|
|
|
CREATE TABLE `proses_pptk` (
|
|
`pp_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`pp_nomor` varchar(5) DEFAULT NULL,
|
|
`pp_tgl_input` date DEFAULT NULL,
|
|
`pp_tgl_nodin` date DEFAULT NULL,
|
|
`pp_nomor_nodin` varchar(50) DEFAULT NULL,
|
|
`pp_perihal` text,
|
|
`pp_proses` int(1) DEFAULT NULL,
|
|
`pp_keterangan` text,
|
|
`pp_dokumen` varchar(20) DEFAULT NULL,
|
|
`pp_unitkerja_id` int(11) DEFAULT NULL,
|
|
`pp_surat_masuk_id` int(11) DEFAULT NULL,
|
|
`pp_unitkerja_user_id` int(11) DEFAULT NULL,
|
|
`pp_ket_surat_masuk` int(1) DEFAULT NULL,
|
|
`pp_aktif` int(1) DEFAULT NULL,
|
|
PRIMARY KEY (`pp_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `proses_pptk` */
|
|
|
|
/*Table structure for table `proses_sk_direktur` */
|
|
|
|
DROP TABLE IF EXISTS `proses_sk_direktur`;
|
|
|
|
CREATE TABLE `proses_sk_direktur` (
|
|
`psd_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`psd_nomor` varchar(5) DEFAULT NULL,
|
|
`psd_tgl_input` date DEFAULT NULL,
|
|
`psd_tgl_sk` date DEFAULT NULL,
|
|
`psd_nomor_sk` varchar(50) DEFAULT NULL,
|
|
`psd_perihal` text,
|
|
`psd_keterangan` text,
|
|
`psd_dokumen` varchar(20) DEFAULT NULL,
|
|
`psd_unitkerja_user_id` int(11) DEFAULT NULL,
|
|
`psd_aktif` int(1) DEFAULT NULL,
|
|
PRIMARY KEY (`psd_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `proses_sk_direktur` */
|
|
|
|
/*Table structure for table `proses_surat_keluar` */
|
|
|
|
DROP TABLE IF EXISTS `proses_surat_keluar`;
|
|
|
|
CREATE TABLE `proses_surat_keluar` (
|
|
`psk_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`psk_status` int(1) DEFAULT NULL,
|
|
`psk_nomor_dalam` varchar(5) DEFAULT NULL,
|
|
`psk_nomor_luar` varchar(20) DEFAULT NULL,
|
|
`psk_tgl_input` date DEFAULT NULL,
|
|
`psk_tgl_surat_keluar` date DEFAULT NULL,
|
|
`psk_perihal` text,
|
|
`psk_penerima` varchar(20) DEFAULT NULL,
|
|
`psk_keterangan` text,
|
|
`psk_dokumen` varchar(20) DEFAULT NULL,
|
|
`psk_unitkerja_user_id` int(11) DEFAULT NULL,
|
|
`psk_aktif` int(1) DEFAULT NULL,
|
|
`psk_nomor_surat_keluar` varchar(20) DEFAULT NULL,
|
|
PRIMARY KEY (`psk_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `proses_surat_keluar` */
|
|
|
|
/*Table structure for table `proses_surat_masuk` */
|
|
|
|
DROP TABLE IF EXISTS `proses_surat_masuk`;
|
|
|
|
CREATE TABLE `proses_surat_masuk` (
|
|
`psm_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`psm_nomor` varchar(5) DEFAULT NULL,
|
|
`psm_tgl_input` date DEFAULT NULL,
|
|
`psm_tgl_nodin` date DEFAULT NULL,
|
|
`psm_nomor_nodin` varchar(50) DEFAULT NULL,
|
|
`psm_perihal` text,
|
|
`psm_proses` int(1) DEFAULT NULL,
|
|
`psm_keterangan` text,
|
|
`psm_tindak_lanjut` int(1) DEFAULT NULL,
|
|
`psm_dokumen` varchar(20) DEFAULT NULL,
|
|
`psm_unitkerja_id` int(11) DEFAULT NULL,
|
|
`psm_unitkerja_user_id` int(11) DEFAULT NULL,
|
|
`psm_aktif` int(1) DEFAULT NULL,
|
|
PRIMARY KEY (`psm_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
/*Data for the table `proses_surat_masuk` */
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|