-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `gaji`
--

-- --------------------------------------------------------

--
-- Table structure for table `akses`
--

CREATE TABLE `akses` (
  `akses_id` int(1) NOT NULL,
  `akses_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `akses`
--

INSERT INTO `akses` (`akses_id`, `akses_name`) VALUES
(1, 'ADMINISTRATOR'),
(2, 'BENDAHARA GAJI'),
(3, 'UMUM');

-- --------------------------------------------------------

--
-- Table structure for table `faktur`
--

CREATE TABLE `faktur` (
  `tahun` int(4) NOT NULL,
  `bulan` int(2) NOT NULL,
  `user_id` char(5) NOT NULL,
  `sekolah_id` int(3) NOT NULL,
  `norek` char(20) NOT NULL,
  `total` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `faktur`
--

INSERT INTO `faktur` (`tahun`, `bulan`, `user_id`, `sekolah_id`, `norek`, `total`) VALUES
(2021, 1, '2', 2, '500404040044004', 3708800),
(2021, 1, '26524', 2, '20303030303030', 3000000),
(2021, 1, '36060', 2, '10060210047276', 4522300),
(2021, 1, '40201', 2, '10060210034233', 10000000),
(2021, 1, '61683', 2, '33030330030330', 4000000),
(2021, 1, '72406', 2, '30303002020200', 4300000),
(2021, 2, '2', 2, '500404040044004', 3708800),
(2021, 2, '36060', 2, '10060210047276', 4522300),
(2021, 2, '40201', 2, '10060210034233', 10000000),
(2021, 9, '36060', 2, '10060210047276', 4522300),
(2021, 10, '2', 2, '500404040044004', 3708800),
(2022, 2, '36060', 2, '10060210047276', 4522300),
(2022, 9, '2', 2, '500404040044004', 3708800),
(2022, 11, '2', 2, '500404040044004', 3708800),
(2022, 11, '36060', 2, '10060210047276', 4522300),
(2022, 11, '40201', 2, '10060210034233', 10000000),
(2022, 12, '2', 2, '50040404004400', 3708800),
(2022, 12, '36060', 2, '10060210047276', 4522300),
(2022, 12, '40201', 2, '10060210034233', 10000000);

--
-- Triggers `faktur`
--
DELIMITER $$
CREATE TRIGGER `Delete faktur` AFTER DELETE ON `faktur` FOR EACH ROW DELETE FROM item WHERE tahun=OLD.tahun AND bulan=OLD.bulan AND user_id=OLD.user_id
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `item`
--

CREATE TABLE `item` (
  `tahun` int(4) NOT NULL,
  `bulan` int(2) NOT NULL,
  `user_id` char(5) NOT NULL,
  `potongan_id` int(1) NOT NULL,
  `jumlah` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `item`
--

INSERT INTO `item` (`tahun`, `bulan`, `user_id`, `potongan_id`, `jumlah`) VALUES
(2021, 1, '2', 1, 800),
(2021, 1, '2', 2, 0),
(2021, 1, '2', 3, 0),
(2021, 1, '2', 4, 0),
(2021, 1, '2', 5, 0),
(2021, 1, '2', 6, 0),
(2021, 1, '2', 7, 0),
(2021, 1, '2', 8, 0),
(2021, 1, '26524', 1, 899999),
(2021, 1, '26524', 2, 300000),
(2021, 1, '26524', 3, 1200),
(2021, 1, '26524', 4, 300000),
(2021, 1, '26524', 5, 300),
(2021, 1, '26524', 6, 100),
(2021, 1, '26524', 7, 0),
(2021, 1, '26524', 8, 0),
(2021, 1, '36060', 1, 700000),
(2021, 1, '36060', 2, 0),
(2021, 1, '36060', 3, 0),
(2021, 1, '36060', 4, 0),
(2021, 1, '36060', 5, 0),
(2021, 1, '36060', 6, 0),
(2021, 1, '36060', 7, 0),
(2021, 1, '36060', 8, 0),
(2021, 1, '40201', 1, 100),
(2021, 1, '40201', 2, 200),
(2021, 1, '40201', 3, 300),
(2021, 1, '40201', 4, 400),
(2021, 1, '40201', 5, 500),
(2021, 1, '40201', 6, 6000),
(2021, 1, '40201', 7, 7000),
(2021, 1, '40201', 8, 8000000),
(2021, 1, '61683', 1, 9000),
(2021, 1, '61683', 2, 70000),
(2021, 1, '61683', 3, 46666),
(2021, 1, '61683', 4, 34444),
(2021, 1, '61683', 5, 35555),
(2021, 1, '61683', 6, 19999),
(2021, 1, '61683', 7, 1000),
(2021, 1, '61683', 8, 0),
(2021, 1, '72406', 1, 0),
(2021, 1, '72406', 2, 0),
(2021, 1, '72406', 3, 0),
(2021, 1, '72406', 4, 0),
(2021, 1, '72406', 5, 0),
(2021, 1, '72406', 6, 0),
(2021, 1, '72406', 7, 0),
(2021, 1, '72406', 8, 0),
(2021, 2, '2', 1, 800),
(2021, 2, '2', 2, 0),
(2021, 2, '2', 3, 0),
(2021, 2, '2', 4, 0),
(2021, 2, '2', 5, 0),
(2021, 2, '2', 6, 0),
(2021, 2, '2', 7, 0),
(2021, 2, '2', 8, 0),
(2021, 2, '36060', 1, 700000),
(2021, 2, '36060', 2, 0),
(2021, 2, '36060', 3, 0),
(2021, 2, '36060', 4, 0),
(2021, 2, '36060', 5, 0),
(2021, 2, '36060', 6, 0),
(2021, 2, '36060', 7, 0),
(2021, 2, '36060', 8, 0),
(2021, 2, '40201', 1, 100),
(2021, 2, '40201', 2, 200),
(2021, 2, '40201', 3, 300),
(2021, 2, '40201', 4, 400),
(2021, 2, '40201', 5, 500),
(2021, 2, '40201', 6, 6000),
(2021, 2, '40201', 7, 7000),
(2021, 2, '40201', 8, 8000000),
(2021, 9, '36060', 1, 700000),
(2021, 9, '36060', 2, 0),
(2021, 9, '36060', 3, 0),
(2021, 9, '36060', 4, 0),
(2021, 9, '36060', 5, 0),
(2021, 9, '36060', 6, 0),
(2021, 9, '36060', 7, 0),
(2021, 9, '36060', 8, 0),
(2021, 10, '2', 1, 800),
(2021, 10, '2', 2, 0),
(2021, 10, '2', 3, 0),
(2021, 10, '2', 4, 0),
(2021, 10, '2', 5, 0),
(2021, 10, '2', 6, 0),
(2021, 10, '2', 7, 0),
(2021, 10, '2', 8, 0),
(2022, 2, '36060', 1, 700000),
(2022, 2, '36060', 2, 0),
(2022, 2, '36060', 3, 0),
(2022, 2, '36060', 4, 0),
(2022, 2, '36060', 5, 0),
(2022, 2, '36060', 6, 0),
(2022, 2, '36060', 7, 0),
(2022, 2, '36060', 8, 0),
(2022, 9, '2', 1, 800),
(2022, 9, '2', 2, 0),
(2022, 9, '2', 3, 0),
(2022, 9, '2', 4, 0),
(2022, 9, '2', 5, 0),
(2022, 9, '2', 6, 0),
(2022, 9, '2', 7, 0),
(2022, 9, '2', 8, 0),
(2022, 11, '2', 1, 800),
(2022, 11, '2', 2, 0),
(2022, 11, '2', 3, 0),
(2022, 11, '2', 4, 0),
(2022, 11, '2', 5, 0),
(2022, 11, '2', 6, 0),
(2022, 11, '2', 7, 0),
(2022, 11, '2', 8, 0),
(2022, 11, '36060', 1, 700000),
(2022, 11, '36060', 2, 0),
(2022, 11, '36060', 3, 0),
(2022, 11, '36060', 4, 0),
(2022, 11, '36060', 5, 0),
(2022, 11, '36060', 6, 0),
(2022, 11, '36060', 7, 0),
(2022, 11, '36060', 8, 0),
(2022, 11, '40201', 1, 100),
(2022, 11, '40201', 2, 200),
(2022, 11, '40201', 3, 300),
(2022, 11, '40201', 4, 400),
(2022, 11, '40201', 5, 500),
(2022, 11, '40201', 6, 6000),
(2022, 11, '40201', 7, 7000),
(2022, 11, '40201', 8, 8000000),
(2022, 12, '2', 1, 800),
(2022, 12, '2', 2, 0),
(2022, 12, '2', 3, 0),
(2022, 12, '2', 4, 0),
(2022, 12, '2', 5, 0),
(2022, 12, '2', 6, 0),
(2022, 12, '2', 7, 0),
(2022, 12, '2', 8, 0),
(2022, 12, '36060', 1, 700000),
(2022, 12, '36060', 2, 0),
(2022, 12, '36060', 3, 0),
(2022, 12, '36060', 4, 0),
(2022, 12, '36060', 5, 0),
(2022, 12, '36060', 6, 0),
(2022, 12, '36060', 7, 0),
(2022, 12, '36060', 8, 0),
(2022, 12, '40201', 1, 100),
(2022, 12, '40201', 2, 200),
(2022, 12, '40201', 3, 300),
(2022, 12, '40201', 4, 400),
(2022, 12, '40201', 5, 500),
(2022, 12, '40201', 6, 6000),
(2022, 12, '40201', 7, 7000),
(2022, 12, '40201', 8, 8000000);

-- --------------------------------------------------------

--
-- Table structure for table `master`
--

CREATE TABLE `master` (
  `potongan_id` int(3) NOT NULL,
  `user_id` char(5) NOT NULL,
  `jumlah` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `master`
--

INSERT INTO `master` (`potongan_id`, `user_id`, `jumlah`) VALUES
(1, '2', 800),
(1, '26524', 899999),
(1, '36060', 700000),
(1, '40201', 100),
(1, '61683', 9000),
(2, '2', 0),
(2, '26524', 300000),
(2, '36060', 0),
(2, '40201', 200),
(2, '61683', 70000),
(3, '2', 0),
(3, '26524', 1200),
(3, '36060', 0),
(3, '40201', 300),
(3, '61683', 46666),
(4, '2', 0),
(4, '26524', 300000),
(4, '36060', 0),
(4, '40201', 400),
(4, '61683', 34444),
(5, '2', 0),
(5, '26524', 300),
(5, '36060', 0),
(5, '40201', 500),
(5, '61683', 35555),
(6, '2', 0),
(6, '26524', 100),
(6, '36060', 0),
(6, '40201', 6000),
(6, '61683', 19999),
(7, '2', 0),
(7, '26524', 0),
(7, '36060', 0),
(7, '40201', 7000),
(7, '61683', 1000),
(8, '2', 0),
(8, '26524', 0),
(8, '36060', 0),
(8, '40201', 8000000),
(8, '61683', 0);

--
-- Triggers `master`
--
DELIMITER $$
CREATE TRIGGER `edit_master` AFTER UPDATE ON `master` FOR EACH ROW BEGIN

DECLARE TOTAL DOUBLE;


SET TOTAL=(SELECT SUM(jumlah) AS JML FROM master WHERE user_id=NEW.user_id);

END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `input_master` AFTER INSERT ON `master` FOR EACH ROW BEGIN

DECLARE TOTAL DOUBLE;


SET TOTAL=(SELECT SUM(jumlah) AS JML FROM master WHERE user_id=NEW.user_id);

END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `menu`
--

CREATE TABLE `menu` (
  `menu_id` int(2) NOT NULL,
  `menu_name` varchar(100) NOT NULL,
  `menu_link` varchar(100) NOT NULL,
  `akses_id` int(1) NOT NULL,
  `tunggal` int(1) NOT NULL,
  `top` int(1) NOT NULL,
  `sub` int(2) NOT NULL,
  `urut` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu`
--

INSERT INTO `menu` (`menu_id`, `menu_name`, `menu_link`, `akses_id`, `tunggal`, `top`, `sub`, `urut`) VALUES
(1, 'Tools', '#', 1, 0, 1, 0, 1),
(2, 'Manajemen User', 'user.asp', 1, 0, 0, 1, 1),
(3, 'Manajemen Sekolah', 'sekolah.asp', 1, 0, 0, 1, 2),
(4, 'Monitoring', 'monitoring.asp', 1, 1, 1, 0, 3),
(5, 'Tools', '#', 2, 0, 1, 0, 1),
(6, 'Manajemen Pegawai', 'pegawai.asp', 2, 0, 0, 5, 1),
(7, 'Proses', 'proses.asp', 2, 1, 1, 0, 2),
(8, 'Laporan', '#', 2, 0, 1, 0, 3),
(9, 'Tabel Gaji', 'print-tabel-gaji.asp', 2, 0, 0, 8, 1),
(10, 'Amprah Gaji', 'print-amprah-gaji.asp', 2, 0, 0, 8, 2);

-- --------------------------------------------------------

--
-- Table structure for table `potongan`
--

CREATE TABLE `potongan` (
  `potongan_id` int(3) NOT NULL,
  `potongan_name` varchar(100) NOT NULL,
  `formula` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `potongan`
--

INSERT INTO `potongan` (`potongan_id`, `potongan_name`, `formula`) VALUES
(1, 'Zakat', 0),
(2, 'Sosial', 0),
(3, 'Arisan DW', 0),
(4, 'Kop. Darmawanita', 0),
(5, 'Cicilan BPD', 0),
(6, 'Cicilan BTN', 0),
(7, 'Sip. Wajib Cicilan Kop.', 0),
(8, 'Arisan Rumah', 0);

-- --------------------------------------------------------

--
-- Table structure for table `sekolah`
--

CREATE TABLE `sekolah` (
  `sekolah_id` int(3) NOT NULL,
  `sekolah_name` varchar(100) NOT NULL,
  `IDBENDAHARA` char(5) DEFAULT NULL,
  `NAMABENDAHARA` varchar(100) DEFAULT NULL,
  `NIPBENDAHARA` char(18) DEFAULT NULL,
  `unit` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sekolah`
--

INSERT INTO `sekolah` (`sekolah_id`, `sekolah_name`, `IDBENDAHARA`, `NAMABENDAHARA`, `NIPBENDAHARA`, `unit`) VALUES
(1, 'ADMINISTRATOR', '', '', '', 0),
(2, 'SMA Negeri 9 Padang', '2', 'Mayyuni, S.Pd', '433344332', 1);

-- --------------------------------------------------------

--
-- Table structure for table `tahun`
--

CREATE TABLE `tahun` (
  `tahun_id` int(4) NOT NULL,
  `tahun_name` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tahun`
--

INSERT INTO `tahun` (`tahun_id`, `tahun_name`) VALUES
(2021, 2021),
(2022, 2022);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `akses`
--
ALTER TABLE `akses`
  ADD PRIMARY KEY (`akses_id`);

--
-- Indexes for table `faktur`
--
ALTER TABLE `faktur`
  ADD UNIQUE KEY `tahun` (`tahun`,`bulan`,`user_id`);

--
-- Indexes for table `item`
--
ALTER TABLE `item`
  ADD UNIQUE KEY `tahun` (`tahun`,`bulan`,`user_id`,`potongan_id`);

--
-- Indexes for table `master`
--
ALTER TABLE `master`
  ADD UNIQUE KEY `potongan_id` (`potongan_id`,`user_id`);

--
-- Indexes for table `menu`
--
ALTER TABLE `menu`
  ADD PRIMARY KEY (`menu_id`);

--
-- Indexes for table `potongan`
--
ALTER TABLE `potongan`
  ADD PRIMARY KEY (`potongan_id`);

--
-- Indexes for table `sekolah`
--
ALTER TABLE `sekolah`
  ADD PRIMARY KEY (`sekolah_id`);

--
-- Indexes for table `tahun`
--
ALTER TABLE `tahun`
  ADD PRIMARY KEY (`tahun_id`);
COMMIT;

