304 lines
9.0 KiB
SQL
304 lines
9.0 KiB
SQL
-- phpMyAdmin SQL Dump
|
|
-- version 5.0.4
|
|
-- https://www.phpmyadmin.net/
|
|
--
|
|
-- Host: 127.0.0.1
|
|
-- Waktu pembuatan: 19 Nov 2022 pada 03.30
|
|
-- Versi server: 10.4.17-MariaDB-log
|
|
-- Versi PHP: 7.3.27
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
START TRANSACTION;
|
|
SET time_zone = "+00:00";
|
|
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
|
|
--
|
|
-- Database: `pcm_database`
|
|
--
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `absensi`
|
|
--
|
|
|
|
CREATE TABLE `absensi` (
|
|
`ID_ABSENSI` varchar(20) NOT NULL,
|
|
`IA` int(11) NOT NULL,
|
|
`DETAIL_SHIFT_ID` varchar(20) NOT NULL,
|
|
`TGL_ABSENSI` date DEFAULT NULL,
|
|
`JAM_MASUK` time DEFAULT NULL,
|
|
`JAM_KELUAR` time DEFAULT NULL,
|
|
`TELAT` varchar(5) DEFAULT NULL,
|
|
`STATUS_ABSENSI` varchar(15) DEFAULT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `absensi`
|
|
--
|
|
|
|
INSERT INTO `absensi` (`ID_ABSENSI`, `IA`, `DETAIL_SHIFT_ID`, `TGL_ABSENSI`, `JAM_MASUK`, `JAM_KELUAR`, `TELAT`, `STATUS_ABSENSI`, `SYNC`) VALUES
|
|
('BDLAB1', 1, 'BDLDS2', '2022-11-02', '10:43:56', '10:48:33', 'YA', NULL, 1),
|
|
('BDLAB2', 2, 'BDLDS1', '2022-11-02', NULL, NULL, 'CUTI', NULL, 1),
|
|
('MGDAB1', 1, 'MGDDS1', '2022-11-02', '13:36:20', NULL, 'YA', NULL, 1);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `cabang`
|
|
--
|
|
|
|
CREATE TABLE `cabang` (
|
|
`KODE_CABANG` varchar(20) NOT NULL,
|
|
`NAMA_CABANG` varchar(50) NOT NULL,
|
|
`ALAMAT` varchar(250) NOT NULL,
|
|
`WAKTU` datetime NOT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `cabang`
|
|
--
|
|
|
|
INSERT INTO `cabang` (`KODE_CABANG`, `NAMA_CABANG`, `ALAMAT`, `WAKTU`, `SYNC`) VALUES
|
|
('BDL', 'Bendul', 'jl. Bendul', '2022-10-27 16:21:27', 1),
|
|
('MGD', 'Mangga Dua Surabaya', 'Jl. Jagir Wonokromo', '2022-10-27 16:17:44', 1);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `detail_shift`
|
|
--
|
|
|
|
CREATE TABLE `detail_shift` (
|
|
`ID_DETAIL_SHIFT` varchar(20) NOT NULL,
|
|
`IDS` int(11) NOT NULL,
|
|
`SHIFT_ID` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL,
|
|
`KARYAWAN_ID` varchar(20) DEFAULT NULL,
|
|
`WAKTU_DETAIL_SHIFT` datetime NOT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `detail_shift`
|
|
--
|
|
|
|
INSERT INTO `detail_shift` (`ID_DETAIL_SHIFT`, `IDS`, `SHIFT_ID`, `KARYAWAN_ID`, `WAKTU_DETAIL_SHIFT`, `SYNC`) VALUES
|
|
('BDLDS1', 1, 'BDLSH1', 'BDLKY2', '2022-11-02 09:52:58', 1),
|
|
('BDLDS2', 2, 'BDLSH1', 'BDLKY3', '2022-11-02 09:52:58', 1),
|
|
('MGDDS1', 1, 'MGDSH1', 'MGDKY2', '2022-11-02 13:38:35', 1);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `ip_server`
|
|
--
|
|
|
|
CREATE TABLE `ip_server` (
|
|
`ID_IP` int(11) NOT NULL,
|
|
`IP` varchar(15) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `ip_server`
|
|
--
|
|
|
|
INSERT INTO `ip_server` (`ID_IP`, `IP`) VALUES
|
|
(1, '200.1.1.92');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `jabatan`
|
|
--
|
|
|
|
CREATE TABLE `jabatan` (
|
|
`ID_JABATAN` varchar(20) NOT NULL,
|
|
`IJ` int(11) NOT NULL,
|
|
`CABANG_KODE` varchar(20) CHARACTER SET utf8mb4 NOT NULL,
|
|
`NAMA_JABATAN` varchar(25) DEFAULT NULL,
|
|
`WAKTU_JABATAN` datetime NOT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `jabatan`
|
|
--
|
|
|
|
INSERT INTO `jabatan` (`ID_JABATAN`, `IJ`, `CABANG_KODE`, `NAMA_JABATAN`, `WAKTU_JABATAN`, `SYNC`) VALUES
|
|
('BDLJB1', 1, 'BDL', 'aaaaaa', '2022-10-28 15:18:40', 1),
|
|
('BDLJB2', 2, 'BDL', 'Admin', '2022-10-29 10:39:53', 1),
|
|
('BDLJB3', 3, 'BDL', 'eeee', '2022-11-04 10:49:00', 1),
|
|
('MGDJB113', 113, 'MGD', 'nguetes', '2022-10-28 15:13:14', 1),
|
|
('MGDJB114', 114, 'MGD', 'Admin', '2022-10-29 10:39:39', 1),
|
|
('MGDJB115', 115, 'MGD', 'tes 1243', '2022-10-31 09:11:22', 1),
|
|
('MGDJB21', 21, 'MGD', 'dksd.jasop10s', '2022-10-28 09:51:58', 1);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `karyawan`
|
|
--
|
|
|
|
CREATE TABLE `karyawan` (
|
|
`ID_KARYAWAN` varchar(20) NOT NULL,
|
|
`IK` int(11) DEFAULT NULL,
|
|
`JABATAN_ID` varchar(20) DEFAULT NULL,
|
|
`NAMA_KARYAWAN` varchar(50) NOT NULL,
|
|
`NAMA_PANGGILAN_KARYAWAN` varchar(25) DEFAULT NULL,
|
|
`STATUS_KARYAWAN` varchar(10) DEFAULT NULL,
|
|
`USERNAME` varchar(16) NOT NULL,
|
|
`PASSWORD` varchar(16) NOT NULL,
|
|
`TGLLHR_KARYAWAN` date DEFAULT NULL,
|
|
`KOTA_KARYAWAN` varchar(25) DEFAULT NULL,
|
|
`JK_KARYAWAN` varchar(10) DEFAULT NULL,
|
|
`WAKTU_KARYAWAN` datetime DEFAULT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `karyawan`
|
|
--
|
|
|
|
INSERT INTO `karyawan` (`ID_KARYAWAN`, `IK`, `JABATAN_ID`, `NAMA_KARYAWAN`, `NAMA_PANGGILAN_KARYAWAN`, `STATUS_KARYAWAN`, `USERNAME`, `PASSWORD`, `TGLLHR_KARYAWAN`, `KOTA_KARYAWAN`, `JK_KARYAWAN`, `WAKTU_KARYAWAN`, `SYNC`) VALUES
|
|
('1', NULL, NULL, 'Administrator', 'Administrator', '1', 'administrator', '1234qweR', NULL, NULL, NULL, NULL, 1),
|
|
('BDLKY1', 1, 'BDLJB2', 'Admin Bendul', 'Admin', '1', 'admin_bendul', '12', NULL, NULL, NULL, '2022-10-29 11:14:05', 1),
|
|
('BDLKY2', 2, 'BDLJB1', 'sdf;jdnfaos', 'sasasa', '1', 'sasas', '12', NULL, NULL, NULL, '2022-10-31 15:20:54', 1),
|
|
('BDLKY3', 3, 'BDLJB1', 'asdas', 'ss', '1', 'ss', '12', NULL, NULL, NULL, '2022-10-31 15:56:17', 1),
|
|
('MGDKY1', 1, 'MGDJB114', 'Admin Mangga Dua', 'Admin', '1', 'admin_mangga_dua', '12', NULL, NULL, NULL, '2022-10-29 11:13:11', 1),
|
|
('MGDKY2', 2, 'MGDJB21', 'aaaa', 'aaaa', '1', 'aaaa', '12', NULL, NULL, NULL, '2022-10-31 09:57:59', 1);
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Struktur dari tabel `shift`
|
|
--
|
|
|
|
CREATE TABLE `shift` (
|
|
`ID_SHIFT` varchar(20) NOT NULL,
|
|
`IS` int(11) NOT NULL,
|
|
`CABANG_KODE` varchar(20) NOT NULL,
|
|
`NAMA_SHIFT` varchar(50) NOT NULL,
|
|
`JAM_MASUK` time NOT NULL,
|
|
`JAM_KELUAR` time NOT NULL,
|
|
`TANGGAL_SHIFT` date NOT NULL,
|
|
`WAKTU_SHIFT` datetime NOT NULL,
|
|
`SYNC` tinyint(1) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--
|
|
-- Dumping data untuk tabel `shift`
|
|
--
|
|
|
|
INSERT INTO `shift` (`ID_SHIFT`, `IS`, `CABANG_KODE`, `NAMA_SHIFT`, `JAM_MASUK`, `JAM_KELUAR`, `TANGGAL_SHIFT`, `WAKTU_SHIFT`, `SYNC`) VALUES
|
|
('BDLSH1', 1, 'BDL', 'Pagi', '08:00:00', '14:00:00', '2022-11-02', '2022-10-31 14:36:13', 1),
|
|
('MGDSH1', 1, 'MGD', 'PAGI', '08:30:00', '16:00:00', '2022-11-02', '2022-11-02 13:38:29', 1);
|
|
|
|
--
|
|
-- Indexes for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Indeks untuk tabel `absensi`
|
|
--
|
|
ALTER TABLE `absensi`
|
|
ADD PRIMARY KEY (`ID_ABSENSI`),
|
|
ADD KEY `FK_DETAIL_SHIFT_ABSENSI` (`DETAIL_SHIFT_ID`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `cabang`
|
|
--
|
|
ALTER TABLE `cabang`
|
|
ADD PRIMARY KEY (`KODE_CABANG`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `detail_shift`
|
|
--
|
|
ALTER TABLE `detail_shift`
|
|
ADD PRIMARY KEY (`ID_DETAIL_SHIFT`),
|
|
ADD KEY `FK_KARYAWAN_ID_DETAIL` (`KARYAWAN_ID`),
|
|
ADD KEY `FK_SHIFT_DETAIL_SHIFT` (`SHIFT_ID`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `ip_server`
|
|
--
|
|
ALTER TABLE `ip_server`
|
|
ADD PRIMARY KEY (`ID_IP`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `jabatan`
|
|
--
|
|
ALTER TABLE `jabatan`
|
|
ADD PRIMARY KEY (`ID_JABATAN`),
|
|
ADD KEY `FK_CABANGKODE_JABATAN` (`CABANG_KODE`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `karyawan`
|
|
--
|
|
ALTER TABLE `karyawan`
|
|
ADD PRIMARY KEY (`ID_KARYAWAN`),
|
|
ADD KEY `FK_JABATAN_ID_KARYAWAN` (`JABATAN_ID`);
|
|
|
|
--
|
|
-- Indeks untuk tabel `shift`
|
|
--
|
|
ALTER TABLE `shift`
|
|
ADD PRIMARY KEY (`ID_SHIFT`),
|
|
ADD KEY `FK_KODECABANG_SHIFT` (`CABANG_KODE`);
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel yang dibuang
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT untuk tabel `ip_server`
|
|
--
|
|
ALTER TABLE `ip_server`
|
|
MODIFY `ID_IP` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel pelimpahan (Dumped Tables)
|
|
--
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `absensi`
|
|
--
|
|
ALTER TABLE `absensi`
|
|
ADD CONSTRAINT `FK_DETAIL_SHIFT_ABSENSI` FOREIGN KEY (`DETAIL_SHIFT_ID`) REFERENCES `detail_shift` (`ID_DETAIL_SHIFT`) ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `detail_shift`
|
|
--
|
|
ALTER TABLE `detail_shift`
|
|
ADD CONSTRAINT `FK_KARYAWAN_ID_DETAIL` FOREIGN KEY (`KARYAWAN_ID`) REFERENCES `karyawan` (`ID_KARYAWAN`) ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `FK_SHIFT_DETAIL_SHIFT` FOREIGN KEY (`SHIFT_ID`) REFERENCES `shift` (`ID_SHIFT`) ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `jabatan`
|
|
--
|
|
ALTER TABLE `jabatan`
|
|
ADD CONSTRAINT `FK_CABANGKODE_JABATAN` FOREIGN KEY (`CABANG_KODE`) REFERENCES `cabang` (`KODE_CABANG`) ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `karyawan`
|
|
--
|
|
ALTER TABLE `karyawan`
|
|
ADD CONSTRAINT `FK_JABATAN_ID_KARYAWAN` FOREIGN KEY (`JABATAN_ID`) REFERENCES `jabatan` (`ID_JABATAN`) ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Ketidakleluasaan untuk tabel `shift`
|
|
--
|
|
ALTER TABLE `shift`
|
|
ADD CONSTRAINT `FK_KODECABANG_SHIFT` FOREIGN KEY (`CABANG_KODE`) REFERENCES `cabang` (`KODE_CABANG`) ON UPDATE CASCADE;
|
|
COMMIT;
|
|
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|