/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.5.60-MariaDB : Database - smsku_anyar
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`smsku` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `smsku`;

/*Table structure for table `akta_cerai` */

DROP TABLE IF EXISTS `akta_cerai`;

CREATE TABLE `akta_cerai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `perkara_id` int(10) NOT NULL,
  `nomor_perkara` varchar(50) NOT NULL,
  `tgl_ac` date DEFAULT NULL,
  `nomor_ac` varchar(100) DEFAULT NULL,
  `nama_id` int(10) DEFAULT NULL,
  `nama` varchar(150) DEFAULT NULL,
  `nomor_hp` varchar(30) DEFAULT NULL,
  `pesan` varchar(255) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`perkara_id`,`nomor_perkara`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `akta_cerai` */

/*Table structure for table `antrian` */

DROP TABLE IF EXISTS `antrian`;

CREATE TABLE `antrian` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nomor_antrian` int(11) NOT NULL,
  `tanggal_sidang` date NOT NULL,
  `ruang_id` tinyint(11) DEFAULT NULL,
  `perkara_id` bigint(11) NOT NULL,
  `nomor_perkara` varchar(30) NOT NULL,
  `status` tinyint(11) DEFAULT NULL,
  `urutan` tinyint(4) DEFAULT NULL,
  `nomor_hp` varchar(30) DEFAULT NULL,
  `pesan` varchar(255) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  `jam_mulai` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `antrian` */

/*Table structure for table `brodcast` */

DROP TABLE IF EXISTS `brodcast`;

CREATE TABLE `brodcast` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) DEFAULT NULL,
  `nomor` varchar(25) DEFAULT NULL,
  `pesan` varchar(100) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `brodcast` */

/*Table structure for table `daftar_kontak` */

DROP TABLE IF EXISTS `daftar_kontak`;

CREATE TABLE `daftar_kontak` (
  `nama` varchar(100) NOT NULL,
  `jabatan` varchar(25) NOT NULL,
  `idsipp` int(4) NOT NULL,
  `nomorhp` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`nama`,`jabatan`,`idsipp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `daftar_kontak` */

/*Table structure for table `epayment` */

DROP TABLE IF EXISTS `epayment`;

CREATE TABLE `epayment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dikirim` datetime DEFAULT NULL,
  `perkara_id` bigint(20) NOT NULL,
  `nomor_perkara` varchar(50) NOT NULL,
  `va` varchar(50) NOT NULL,
  `pesan` varchar(100) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `nohp` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`perkara_id`,`nomor_perkara`,`va`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `epayment` */

/*Table structure for table `gammu` */

DROP TABLE IF EXISTS `gammu`;

CREATE TABLE `gammu` (
  `Version` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Version`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `gammu` */

insert  into `gammu`(`Version`) values 
(16);

/*Table structure for table `inbox` */

DROP TABLE IF EXISTS `inbox`;

CREATE TABLE `inbox` (
  `UpdatedInDB` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ReceivingDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Text` text NOT NULL,
  `SenderNumber` varchar(20) NOT NULL DEFAULT '',
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
  `UDH` text NOT NULL,
  `SMSCNumber` varchar(20) NOT NULL DEFAULT '',
  `Class` int(11) NOT NULL DEFAULT '-1',
  `TextDecoded` text NOT NULL,
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `RecipientID` text NOT NULL,
  `Processed` enum('false','true') NOT NULL DEFAULT 'false',
  `Status` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `inbox` */

/*Table structure for table `login_pihak` */

DROP TABLE IF EXISTS `login_pihak`;

CREATE TABLE `login_pihak` (
  `pihak_id` int(11) NOT NULL,
  `nomor_hp` varchar(30) NOT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`pihak_id`,`nomor_hp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `login_pihak` */

/*Table structure for table `outbox` */

DROP TABLE IF EXISTS `outbox`;

CREATE TABLE `outbox` (
  `UpdatedInDB` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `InsertIntoDB` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SendingDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SendBefore` time NOT NULL DEFAULT '23:59:59',
  `SendAfter` time NOT NULL DEFAULT '00:00:00',
  `Text` text,
  `DestinationNumber` varchar(20) NOT NULL DEFAULT '',
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
  `UDH` text,
  `Class` int(11) DEFAULT '-1',
  `TextDecoded` text NOT NULL,
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MultiPart` enum('false','true') DEFAULT 'false',
  `RelativeValidity` int(11) DEFAULT '-1',
  `SenderID` varchar(255) DEFAULT NULL,
  `SendingTimeOut` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `DeliveryReport` enum('default','yes','no') DEFAULT 'default',
  `CreatorID` text NOT NULL,
  `Retries` int(3) DEFAULT '0',
  `Priority` int(11) DEFAULT '0',
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL DEFAULT 'Reserved',
  `StatusCode` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`ID`),
  KEY `outbox_date` (`SendingDateTime`,`SendingTimeOut`),
  KEY `outbox_sender` (`SenderID`(250))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `outbox` */

/*Table structure for table `outbox_multipart` */

DROP TABLE IF EXISTS `outbox_multipart`;

CREATE TABLE `outbox_multipart` (
  `Text` text,
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
  `UDH` text,
  `Class` int(11) DEFAULT '-1',
  `TextDecoded` text,
  `ID` int(10) unsigned NOT NULL DEFAULT '0',
  `SequencePosition` int(11) NOT NULL DEFAULT '1',
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL DEFAULT 'Reserved',
  `StatusCode` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`ID`,`SequencePosition`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `outbox_multipart` */

/*Table structure for table `pengaduan` */

DROP TABLE IF EXISTS `pengaduan`;

CREATE TABLE `pengaduan` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `nomorhp` varchar(25) NOT NULL,
  `pengaduan` varchar(255) DEFAULT NULL,
  `diterima` datetime DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `pengaduan` */

/*Table structure for table `perkara_daftar` */

DROP TABLE IF EXISTS `perkara_daftar`;

CREATE TABLE `perkara_daftar` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `perkara_id` int(11) NOT NULL,
  `nomor_perkara` varchar(255) NOT NULL,
  `tanggal_daftar` varchar(255) NOT NULL,
  `ecourt` varchar(1) DEFAULT NULL,
  `nama_pihak` varchar(255) NOT NULL,
  `nomor_hp` varchar(255) NOT NULL,
  `pesan` varchar(255) NOT NULL,
  `dikirim` datetime NOT NULL,
  PRIMARY KEY (`id`,`perkara_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `perkara_daftar` */

/*Table structure for table `phones` */

DROP TABLE IF EXISTS `phones`;

CREATE TABLE `phones` (
  `ID` text NOT NULL,
  `UpdatedInDB` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `InsertIntoDB` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TimeOut` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Send` enum('yes','no') NOT NULL DEFAULT 'no',
  `Receive` enum('yes','no') NOT NULL DEFAULT 'no',
  `IMEI` varchar(35) NOT NULL,
  `IMSI` varchar(35) NOT NULL,
  `NetCode` varchar(10) DEFAULT 'ERROR',
  `NetName` varchar(35) DEFAULT 'ERROR',
  `Client` text NOT NULL,
  `Battery` int(11) NOT NULL DEFAULT '-1',
  `Signal` int(11) NOT NULL DEFAULT '-1',
  `Sent` int(11) NOT NULL DEFAULT '0',
  `Received` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`IMEI`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `phones` */

insert  into `phones`(`ID`,`UpdatedInDB`,`InsertIntoDB`,`TimeOut`,`Send`,`Receive`,`IMEI`,`IMSI`,`NetCode`,`NetName`,`Client`,`Battery`,`Signal`,`Sent`,`Received`) values 
('','2019-08-16 15:39:27','2019-08-16 15:29:41','2019-08-16 15:39:37','yes','yes','012345678901234','510015950156720','510 01','','Gammu 1.38.1, Linux, kernel 4.14.79-v7+ (#1159 SMP Sun Nov 4 17:50:20 GMT 2018), GCC 6.2',0,57,0,0);

/*Table structure for table `reminder_kontak` */

DROP TABLE IF EXISTS `reminder_kontak`;

CREATE TABLE `reminder_kontak` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `jenis` varchar(50) DEFAULT NULL,
  `id_user_sipp` int(4) DEFAULT NULL,
  `nomorhp` varchar(25) DEFAULT NULL,
  `nama_user` varchar(100) DEFAULT NULL,
  `tupoksi` varchar(25) DEFAULT NULL,
  `pesan` varchar(100) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `reminder_kontak` */

/*Table structure for table `reminder_sipp` */

DROP TABLE IF EXISTS `reminder_sipp`;

CREATE TABLE `reminder_sipp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_sipp` varchar(50) NOT NULL,
  `nohp` varchar(30) NOT NULL,
  `validasi` varchar(10) DEFAULT NULL,
  `sms` varchar(250) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`user_sipp`,`nohp`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `reminder_sipp` */

/*Table structure for table `salinan_putusan` */

DROP TABLE IF EXISTS `salinan_putusan`;

CREATE TABLE `salinan_putusan` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `perkara_id` int(10) NOT NULL,
  `nomor_perkara` varchar(50) NOT NULL,
  `tgl_ac` date DEFAULT NULL,
  `nomor_ac` varchar(100) DEFAULT NULL,
  `nama` varchar(255) DEFAULT NULL,
  `nomor_hp` varchar(30) DEFAULT NULL,
  `pesan` varchar(255) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`perkara_id`,`nomor_perkara`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `salinan_putusan` */

/*Table structure for table `sentitems` */

DROP TABLE IF EXISTS `sentitems`;

CREATE TABLE `sentitems` (
  `UpdatedInDB` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `InsertIntoDB` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SendingDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `DeliveryDateTime` timestamp NULL DEFAULT NULL,
  `Text` text NOT NULL,
  `DestinationNumber` varchar(20) NOT NULL DEFAULT '',
  `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL DEFAULT 'Default_No_Compression',
  `UDH` text NOT NULL,
  `SMSCNumber` varchar(20) NOT NULL DEFAULT '',
  `Class` int(11) NOT NULL DEFAULT '-1',
  `TextDecoded` text NOT NULL,
  `ID` int(10) unsigned NOT NULL DEFAULT '0',
  `SenderID` varchar(255) NOT NULL,
  `SequencePosition` int(11) NOT NULL DEFAULT '1',
  `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL DEFAULT 'SendingOK',
  `StatusError` int(11) NOT NULL DEFAULT '-1',
  `TPMR` int(11) NOT NULL DEFAULT '-1',
  `RelativeValidity` int(11) NOT NULL DEFAULT '-1',
  `CreatorID` text NOT NULL,
  `StatusCode` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`ID`,`SequencePosition`),
  KEY `sentitems_date` (`DeliveryDateTime`),
  KEY `sentitems_tpmr` (`TPMR`),
  KEY `sentitems_dest` (`DestinationNumber`),
  KEY `sentitems_sender` (`SenderID`(250))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

/*Data for the table `sentitems` */

/*Table structure for table `sidang` */

DROP TABLE IF EXISTS `sidang`;

CREATE TABLE `sidang` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `perkara_id` bigint(20) NOT NULL,
  `nomor_perkara` varchar(50) NOT NULL,
  `tanggal_sidang` date NOT NULL,
  `ecourt` tinyint(1) DEFAULT NULL,
  `nomor_ecourt` varchar(50) DEFAULT NULL,
  `pihak` varchar(255) DEFAULT NULL,
  `nomorhp` varchar(25) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `pesan` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `sidang` */

/*Table structure for table `sisa_panjar` */

DROP TABLE IF EXISTS `sisa_panjar`;

CREATE TABLE `sisa_panjar` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `perkara_id` int(10) NOT NULL,
  `nomor_perkara` varchar(35) NOT NULL,
  `psp` decimal(10,0) DEFAULT NULL,
  `nama` varchar(255) DEFAULT NULL,
  `nomor_hp` varchar(30) DEFAULT NULL,
  `pesan` varchar(255) DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `sisa_panjar` */

/*Table structure for table `sms_kantor` */

DROP TABLE IF EXISTS `sms_kantor`;

CREATE TABLE `sms_kantor` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nomor_tujuan` varchar(25) DEFAULT NULL,
  `sms` varchar(150) DEFAULT NULL,
  `dikirim_pada` date DEFAULT NULL,
  `dikirim` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `sms_kantor` */

/*Table structure for table `template_pesan` */

DROP TABLE IF EXISTS `template_pesan`;

CREATE TABLE `template_pesan` (
  `no` tinyint(2) NOT NULL,
  `template` varchar(160) NOT NULL,
  `notifikasi` tinyint(1) DEFAULT '0',
  `keterangan` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `template_pesan` */

insert  into `template_pesan`(`no`,`template`,`notifikasi`,`keterangan`) values 
(1,'Akta Cerai perkara #noperk# telah terbit #tgl_ac# nomor #nomor_ac#',0,'Info akta cerai'),
(2,'Akta Cerai perkara #noperk# belum terbit, status terakhir #status_akhir#',0,'Info akta cerai belum terbit'),
(3,'Akta Cerai #noperk# telah terbit tgl #tgl_ac# no #nomor_ac dikirim otomatis oleh PA Ciamis',1,'Notifikasi otomatis akta cerai'),
(4,'Perkara #noperk# sidang ke #ke# #tgl_sidang#',0,'Info tanggal sidang'),
(5,'Tanggal sidang perkara #noperk# belum ada atau perkara sudah selesai, status terakhir #status_akhir# ',0,'Info tanggal sidang belum ada'),
(6,'Perkara #noperk# akan sidang ke #ke#  #tgl_sidang#,dikirim otomatis oleh PA Ciamis',1,'Notifikasi tanggal sidang'),
(7,'Keuangan perkara #noperk# : Total panjar=Rp.#total_panjar#, Total pengeluaran=Rp.#total_keluar#, Sisa panjar=Rp.#psp#',0,'Info Keuangan Perkara'),
(8,'Perkara #jenis_perkara# a.n #namap# terdaftar #tgl_daftar# nomor #noperk# dikirim otomatis oleh PA Ciamis',1,'Notifikasi perkara terdaftar'),
(9,'Perkara #noperk# putus #tgl_putus# status putusan #status_putusan#',0,'Info perkara putus'),
(10,'Perkara #noperk# belum putus, status terakhir #status_akhir#',0,'Info perkara belum putus'),
(11,'Sisa Panjar Pkr #noperk# sejumlah Rp.#saldo# silahkan ambil di kasir PA Ciamis, dikirim otomatis oleh PA Ciamis',1,'Notifikasi sisa panjar'),
(12,'perkara #noperk# tidak ada di register PA.Ciamis/salah format perkara, contoh 999/Pdt.G/2018 atau 999/Pdt.G/2018/#kodepa#',0,'format nomor salah');

/*Table structure for table `timer_notifikasi` */

DROP TABLE IF EXISTS `timer_notifikasi`;

CREATE TABLE `timer_notifikasi` (
  `id` bigint(4) NOT NULL AUTO_INCREMENT,
  `jenis` varchar(50) DEFAULT NULL,
  `jadwal_hari` tinyint(2) DEFAULT NULL,
  `terakhir_dikirim` date DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `timer_notifikasi` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
