text:
#
# SQL Export
# Created by Querious (962)
# Created: 2016年8月8日 GMT+8下午12:27:05
# Encoding: Unicode (UTF-8)
#

CREATE TABLE `history` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(45) DEFAULT NULL,
  `source` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=338 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent0` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=800906 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent1` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=799054 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent2` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=777980 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent3` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=776569 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent4` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=802410 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent5` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=773538 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent6` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=756437 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent7` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=751880 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent8` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=769953 DEFAULT CHARSET=utf8;

CREATE TABLE `torrent9` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=755656 DEFAULT CHARSET=utf8;

CREATE TABLE `torrenta` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=741695 DEFAULT CHARSET=utf8;

CREATE TABLE `torrentb` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=742012 DEFAULT CHARSET=utf8;

CREATE TABLE `torrentc` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=749031 DEFAULT CHARSET=utf8;

CREATE TABLE `torrentd` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=716835 DEFAULT CHARSET=utf8;

CREATE TABLE `torrente` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=718701 DEFAULT CHARSET=utf8;

CREATE TABLE `torrentf` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `data` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=689052 DEFAULT CHARSET=utf8;

CREATE TABLE `infohash` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `infohash` varchar(40) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `infohash` (`infohash`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3093 DEFAULT CHARSET=utf8;

CREATE TABLE `recommend` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


text:
-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 13, 2021 at 09:09 PM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 5.6.39

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `db_zidan`
--

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

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `id_admin` varchar(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  `nama_lengkap` varchar(50) NOT NULL,
  `no_hp` varchar(15) NOT NULL,
  `alamat` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`id_admin`, `email`, `nama_lengkap`, `no_hp`, `alamat`) VALUES
('ADMN.000001', 'admin@gmail.com', 'Admin', '089', 'Panguragan');

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

--
-- Table structure for table `kategori`
--

CREATE TABLE `kategori` (
  `id_kategori` varchar(11) NOT NULL,
  `nama_kategori` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kategori`
--

INSERT INTO `kategori` (`id_kategori`, `nama_kategori`) VALUES
('KTGR.000001', 'Tidak Berkategori'),
('KTGR.000002', 'plastik');

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

--
-- Table structure for table `keranjang`
--

CREATE TABLE `keranjang` (
  `id_keranjang` varchar(11) NOT NULL,
  `id_pembeli` varchar(11) NOT NULL,
  `id_produk` varchar(11) NOT NULL,
  `jumlah` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `keranjang`
--

INSERT INTO `keranjang` (`id_keranjang`, `id_pembeli`, `id_produk`, `jumlah`) VALUES
('CART.000001', 'PMBL.000001', 'PROD.000003', 1);

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

--
-- Table structure for table `komplain`
--

CREATE TABLE `komplain` (
  `id_komplain` varchar(11) NOT NULL,
  `no_invoice` varchar(30) NOT NULL,
  `jenis_komplain` varchar(100) NOT NULL,
  `keterangan` varchar(255) NOT NULL,
  `bukti_komplain` varchar(255) NOT NULL,
  `id_pembeli` varchar(11) NOT NULL,
  `status` varchar(100) NOT NULL,
  `solusi` varchar(100) DEFAULT NULL,
  `keterangan2` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `komplain`
--

INSERT INTO `komplain` (`id_komplain`, `no_invoice`, `jenis_komplain`, `keterangan`, `bukti_komplain`, `id_pembeli`, `status`, `solusi`, `keterangan2`) VALUES
('COMP.000001', 'INV20210118000000008', 'Produk tidak sesuai dengan deskripsi', 'p', '41Permadi Eka Permana-1.png', 'PMBL.000005', 'Selesai', 'Refund Dana', 'refund aja yah');

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

--
-- Table structure for table `konfirm_bayar`
--

CREATE TABLE `konfirm_bayar` (
  `no_invoice` varchar(30) NOT NULL,
  `tgl_bayar` date NOT NULL,
  `bank_asal` varchar(30) NOT NULL,
  `nama_pemilik` varchar(50) NOT NULL,
  `jumlah` int(11) NOT NULL,
  `bukti_transfer` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `konfirm_bayar`
--

INSERT INTO `konfirm_bayar` (`no_invoice`, `tgl_bayar`, `bank_asal`, `nama_pemilik`, `jumlah`, `bukti_transfer`) VALUES
('INV20201102000000001', '2020-11-02', 'BCA', 'Restu', 37000, '83ttt.jpg'),
('INV20201102000000002', '2020-11-05', 'BCA', 'Restu', 69000, '96p.jpg'),
('INV20201102000000003', '2020-11-05', 'BCA', 'Restu', 37000, '59p.jpg'),
('INV20201104000000004', '2020-11-04', 'BCA', 'abdul kholik', 45, '411.png'),
('INV20201108000000006', '2020-11-08', 'BRI', 'YANDI', 45000, '43akta 4.jpg'),
('INV20210118000000008', '2021-01-20', 'BCA', '1', 5, '99Permadi Eka Permana-1.png');

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

--
-- Table structure for table `kota`
--

CREATE TABLE `kota` (
  `id_kota` varchar(11) NOT NULL,
  `nama_kota` varchar(50) NOT NULL,
  `ongkir` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kota`
--

INSERT INTO `kota` (`id_kota`, `nama_kota`, `ongkir`) VALUES
('KOTA.000001', 'Kabupaten Cirebon', '12000'),
('KOTA.000002', 'Kota Cirebon', '12000'),
('KOTA.000003', 'Kabupaten Majalengka', '15000');

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

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `no_invoice` varchar(30) NOT NULL,
  `status_order` varchar(100) NOT NULL,
  `tgl_order` date NOT NULL,
  `total_tagihan` int(15) NOT NULL,
  `jam_order` time NOT NULL,
  `tgl_bayar` date DEFAULT NULL,
  `no_resi` varchar(100) DEFAULT NULL,
  `nama_penerima` varchar(100) NOT NULL,
  `no_hp` varchar(15) NOT NULL,
  `alamat` varchar(250) NOT NULL,
  `id_kota` varchar(11) NOT NULL,
  `pesan` varchar(250) NOT NULL,
  `id_pembeli` varchar(11) NOT NULL,
  `id_penjual` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`no_invoice`, `status_order`, `tgl_order`, `total_tagihan`, `jam_order`, `tgl_bayar`, `no_resi`, `nama_penerima`, `no_hp`, `alamat`, `id_kota`, `pesan`, `id_pembeli`, `id_penjual`) VALUES
('INV20201102000000001', 'Selesai', '2020-11-02', 25000, '15:44:55', '2020-11-02', '1', 'Pembeli', '08944477788', 'Plumbon 21 Kab. Cirebon', 'KOTA.000002', '', 'PMBL.000001', 'PNJL.000002'),
('INV20201102000000002', 'Pesanan Diterima', '2020-11-02', 82000, '16:12:12', '2020-11-05', '3', 'Pembeli', '08944477788', 'Plumbon', 'KOTA.000002', 'jangan pake lama', 'PMBL.000001', 'PNJL.000001'),
('INV20201102000000003', 'Ditolak', '2020-11-02', 82000, '16:12:12', '2020-11-05', NULL, 'Pembeli', '08944477788', 'Plumbon', 'KOTA.000002', 'jangan pake lama', 'PMBL.000001', 'PNJL.000002'),
('INV20201104000000004', 'Pesanan Dikirim', '2020-11-04', 45000, '14:32:17', '2020-11-04', '2', 'abdul kholik', '0897654320', 'ds.panguragan wetan blok v', 'KOTA.000001', 'benar', 'PMBL.000004', 'PNJL.000003'),
('INV20201104000000005', 'Menunggu Pembayaran', '2020-11-04', 45000, '14:33:57', NULL, NULL, 'abdul kholik', '0897654320', 'ds.panguragan wetan blok v', 'KOTA.000001', 'benar', 'PMBL.000004', 'PNJL.000003'),
('INV20201108000000006', 'Pesanan Diproses', '2020-11-08', 45000, '14:59:16', '2020-11-08', NULL, 'Pembeli', '0896445225', 'Plumbon', 'KOTA.000001', '', 'PMBL.000001', 'PNJL.000003'),
('INV20201108000000007', 'Menunggu Pembayaran', '2020-11-08', 45000, '15:08:58', NULL, NULL, 'Pembeli', '0895555343', 'Plumbon', 'KOTA.000003', '', 'PMBL.000001', 'PNJL.000003'),
('INV20210118000000008', 'Komplain Selesai', '2021-01-18', 25000, '07:58:39', '2021-01-20', '00998', 'Permadi Eka Permana', '6289660771166', 'plumbon 99', 'KOTA.000001', '', 'PMBL.000005', 'PNJL.000003'),
('INV20210514000000009', 'Menunggu Pembayaran', '2021-05-14', 400000, '01:48:45', NULL, NULL, 'Permadi Eka Permana', '6289660771166', 'plumbon 99', 'KOTA.000001', '', 'PMBL.000005', 'PNJL.000004'),
('INV20210514000000009', 'Menunggu Pembayaran', '2021-05-14', 400000, '01:48:45', NULL, NULL, 'Permadi Eka Permana', '6289660771166', 'plumbon 99', 'KOTA.000001', '', 'PMBL.000005', 'PNJL.000005');

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

--
-- Table structure for table `orders_detail`
--

CREATE TABLE `orders_detail` (
  `no_invoice` varchar(30) NOT NULL,
  `id_produk` varchar(11) NOT NULL,
  `jumlah` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `orders_detail`
--

INSERT INTO `orders_detail` (`no_invoice`, `id_produk`, `jumlah`) VALUES
('INV20201102000000001', 'PROD.000001', 1),
('INV20201102000000002', 'PROD.000002', 1),
('INV20201102000000002', 'PROD.000001', 1),
('INV20201102000000003', 'PROD.000002', 1),
('INV20201102000000003', 'PROD.000001', 1),
('INV20210118000000008', 'PROD.000003', 1),
('INV20210120000000009', 'PROD.000001', 1),
('INV20210514000000009', 'PROD.000004', 1),
('INV20210514000000009', 'PROD.000006', 1),
('INV20210514000000009', 'PROD.000004', 1),
('INV20210514000000009', 'PROD.000006', 1);

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

--
-- Table structure for table `pembeli`
--

CREATE TABLE `pembeli` (
  `id_pembeli` varchar(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  `id_kota` varchar(11) NOT NULL,
  `nama_lengkap` varchar(50) NOT NULL,
  `no_hp` varchar(15) NOT NULL,
  `alamat` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pembeli`
--

INSERT INTO `pembeli` (`id_pembeli`, `email`, `id_kota`, `nama_lengkap`, `no_hp`, `alamat`) VALUES
('PMBL.000001', 'pembeli@gmail.com', '', 'Pembeli', '089', 'Plumbon'),
('PMBL.000002', 'zidan@gmail.com', '', 'aufazidan', '089506173030', 'ds.panguragan wetan blok v'),
('PMBL.000003', 'aufazidan@gmail.com', '', 'aufazidan', '089506173030', 'ds.panguragan wetan blok v'),
('PMBL.000004', 'kholik@gmail.com', '', 'abdul kholik', '0897654320', 'ds.panguragan wetan blok v'),
('PMBL.000005', 'permadiekapermana@gmail.com', 'KOTA.000001', 'Permadi Eka Permana', '6289660771166', 'plumbon 99');

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

--
-- Table structure for table `penjual`
--

CREATE TABLE `penjual` (
  `id_penjual` varchar(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  `nama_lengkap` varchar(50) NOT NULL,
  `no_hp` varchar(15) NOT NULL,
  `alamat` varchar(250) NOT NULL,
  `nomor_rekening` varchar(30) NOT NULL,
  `nama_toko` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `penjual`
--

INSERT INTO `penjual` (`id_penjual`, `email`, `nama_lengkap`, `no_hp`, `alamat`, `nomor_rekening`, `nama_toko`) VALUES
('PNJL.000001', 'penjual@gmail.com', 'Penjual', '089', 'Kemlaka', '000', 'Muhidin Store'),
('PNJL.000002', 'Doni22@gmail.com', 'Doni', '087342567999', 'Panguragan Blok V Kec. Panguragan Kab. Cirebon', '123456789', 'Donies'),
('PNJL.000003', 'mufalikha@gmail.com', 'mufalikha', '08985139620', 'ds.panguragan wetan kecamatan panguragan', '1234', 'toko kelapa'),
('PNJL.000004', 'penjual1@gmail.com', 'Penjual 1', '089', 'p', '123', 'Penjual 1'),
('PNJL.000005', 'penjual2@gmail.com', 'Penjual 2', '089', 'p', '123', 'Penjual 2');

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

--
-- Table structure for table `produk`
--

CREATE TABLE `produk` (
  `id_produk` varchar(11) NOT NULL,
  `id_kategori` varchar(11) NOT NULL,
  `id_penjual` varchar(11) DEFAULT NULL,
  `nama_produk` varchar(100) DEFAULT NULL,
  `harga` int(10) DEFAULT NULL,
  `stok` int(10) DEFAULT NULL,
  `gambar` varchar(250) DEFAULT NULL,
  `keterangan` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `produk`
--

INSERT INTO `produk` (`id_produk`, `id_kategori`, `id_penjual`, `nama_produk`, `harga`, `stok`, `gambar`, `keterangan`) VALUES
('PROD.000001', 'KTGR.000002', 'PNJL.000002', 'Ember', 25000, 12, '42aaa.png', 'Ember anti pecah'),
('PROD.000002', 'KTGR.000001', 'PNJL.000001', 'Alumunium', 57000, 5, '36Untitled 1.png', 'Alumunium khusus untuk kanopi'),
('PROD.000003', 'KTGR.000001', 'PNJL.000003', 'Alumunium', 25000, 4, '36Screenshot (14).png', 'alumunium'),
('PROD.000004', 'KTGR.000001', 'PNJL.000004', 'Produk Test 1', 100000, 7, '13WhatsApp Image 2021-05-07 at 1.49.40 PM.jpeg', ''),
('PROD.000005', 'KTGR.000001', 'PNJL.000004', 'Produk Test 2', 200000, 8, '96screencapture-ptbsp-ddns-net-12892-2021-05-10-16_52_55.png', ''),
('PROD.000006', 'KTGR.000001', 'PNJL.000005', 'Produk Test 3', 300000, 7, '64Capture.PNG', '');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `email` varchar(50) NOT NULL,
  `password` varchar(35) NOT NULL,
  `hak_akses` varchar(10) NOT NULL,
  `aktif` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`email`, `password`, `hak_akses`, `aktif`) VALUES
('admin@gmail.com', '21232f297a57a5a743894a0e4a801fc3', 'Admin', '1'),
('aufazidan@gmail.com', 'fcea920f7412b5da7be0cf42b8c93759', 'Pembeli', '1'),
('Doni22@gmail.com', '827ccb0eea8a706c4c34a16891f84e7b', 'Penjual', '1'),
('kholik@gmail.com', '508df4cb2f4d8f80519256258cfb975f', 'Pembeli', '1'),
('mufalikha@gmail.com', '48c7be1154efa6bedf27e6e9d55d1104', 'Penjual', '1'),
('pembeli@gmail.com', 'a9f8bbb8cb84375f241ce3b9da6219a1', 'Pembeli', '1'),
('penjual1@gmail.com', '7fede5a7930d9ffafdf87bc536d39312', 'Penjual', '1'),
('penjual2@gmail.com', '95af899fc2dcb417586755297f7e5950', 'Penjual', '1'),
('penjual@gmail.com', '08634230004f9098ef63bfabef63a407', 'Penjual', '1'),
('permadiekapermana@gmail.com', 'b69d343badb19f8e0311aa62b844a06c', 'Pembeli', '1'),
('zidan@gmail.com', '827ccb0eea8a706c4c34a16891f84e7b', 'Pembeli', '1');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admin`
--
ALTER TABLE `admin`
  ADD PRIMARY KEY (`id_admin`),
  ADD KEY `email` (`email`);

--
-- Indexes for table `kategori`
--
ALTER TABLE `kategori`
  ADD PRIMARY KEY (`id_kategori`);

--
-- Indexes for table `keranjang`
--
ALTER TABLE `keranjang`
  ADD PRIMARY KEY (`id_keranjang`),
  ADD KEY `id_pembeli` (`id_pembeli`),
  ADD KEY `id_produk` (`id_produk`);

--
-- Indexes for table `komplain`
--
ALTER TABLE `komplain`
  ADD PRIMARY KEY (`id_komplain`),
  ADD KEY `no_invoice` (`no_invoice`),
  ADD KEY `id_pembeli` (`id_pembeli`);

--
-- Indexes for table `konfirm_bayar`
--
ALTER TABLE `konfirm_bayar`
  ADD KEY `no_invoice` (`no_invoice`);

--
-- Indexes for table `kota`
--
ALTER TABLE `kota`
  ADD PRIMARY KEY (`id_kota`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD KEY `id_kota` (`id_kota`),
  ADD KEY `id_pembeli` (`id_pembeli`),
  ADD KEY `id_penjual` (`id_penjual`);

--
-- Indexes for table `orders_detail`
--
ALTER TABLE `orders_detail`
  ADD KEY `no_invoice` (`no_invoice`),
  ADD KEY `id_produk` (`id_produk`);

--
-- Indexes for table `pembeli`
--
ALTER TABLE `pembeli`
  ADD PRIMARY KEY (`id_pembeli`),
  ADD KEY `email` (`email`),
  ADD KEY `id_kota` (`id_kota`);

--
-- Indexes for table `penjual`
--
ALTER TABLE `penjual`
  ADD PRIMARY KEY (`id_penjual`);

--
-- Indexes for table `produk`
--
ALTER TABLE `produk`
  ADD PRIMARY KEY (`id_produk`),
  ADD KEY `id_kategori` (`id_kategori`),
  ADD KEY `id_penjual` (`id_penjual`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`email`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin`
--
ALTER TABLE `admin`
  ADD CONSTRAINT `admin_ibfk_1` FOREIGN KEY (`email`) REFERENCES `users` (`email`);

--
-- Constraints for table `keranjang`
--
ALTER TABLE `keranjang`
  ADD CONSTRAINT `keranjang_ibfk_1` FOREIGN KEY (`id_produk`) REFERENCES `produk` (`id_produk`),
  ADD CONSTRAINT `keranjang_ibfk_2` FOREIGN KEY (`id_pembeli`) REFERENCES `pembeli` (`id_pembeli`);

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id_pembeli`) REFERENCES `pembeli` (`id_pembeli`),
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`id_kota`) REFERENCES `kota` (`id_kota`);

--
-- Constraints for table `orders_detail`
--
ALTER TABLE `orders_detail`
  ADD CONSTRAINT `orders_detail_ibfk_1` FOREIGN KEY (`id_produk`) REFERENCES `produk` (`id_produk`);

--
-- Constraints for table `produk`
--
ALTER TABLE `produk`
  ADD CONSTRAINT `produk_ibfk_1` FOREIGN KEY (`id_kategori`) REFERENCES `kategori` (`id_kategori`),
  ADD CONSTRAINT `produk_ibfk_2` FOREIGN KEY (`id_penjual`) REFERENCES `penjual` (`id_penjual`);
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 */;

domain: sql


text:
-- User Table
INSERT INTO user(email, password, first_name, last_name, phone_number, provider, admin)
VALUES ("matti.koivunen@putkiyritys.fi", "passw05rd", "matti", "koivunen", "022-4445672", true, false);

-- Provider detail
INSERT INTO provider_detail (user_id, name, business_id, reliable)
VALUES (5, "Putki Yritys Oy", "76553-77", true);

-- Service
INSERT INTO service(name, description, price, provider_id)
VALUES("Katonpesu", "Laitamme kattosi kiiltämään", 630, 5);

-- Tag
INSERT INTO tag(name)
VALUES("katto"), ("ikkuna"), ("putki"), ("ilmanvaihto"), ("lattia");

-- Service tags
INSERT INTO service_tag(tag_id, service_id)
VALUES(1,3);

-- Review
INSERT INTO service_review(service_id, user_id, rating, title, description)
VALUES(2, 1, 5, "Loistavaa", "Loistavaa palvelua"), (2, 3, 4, "Ihan ok", "Ihan ok palvelua");

-- Address
INSERT INTO address(user_id, address, zip, city)
VALUES(5, "Yrityskatu 3", 24100, "Salo");


text:
-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 03, 2021 at 03:12 PM
-- Server version: 10.4.20-MariaDB
-- PHP Version: 7.4.21

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: `sanggar`
--

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

--
-- Table structure for table `nilai`
--

CREATE TABLE `nilai` (
  `id_nilai` int(11) NOT NULL,
  `id_siswa` int(11) NOT NULL,
  `nis` int(11) NOT NULL,
  `kimia` int(11) NOT NULL,
  `biologi` int(11) NOT NULL,
  `fisika` int(11) NOT NULL,
  `matematika` int(11) NOT NULL,
  `bhs_inggris` int(11) NOT NULL,
  `bhs_indonesia` int(11) NOT NULL,
  `realistis` int(11) NOT NULL,
  `intelektual` int(11) NOT NULL,
  `artistik` int(11) NOT NULL,
  `sosial` int(11) NOT NULL,
  `enterprise` int(11) NOT NULL,
  `konvensional` int(11) NOT NULL,
  `linguistik` int(11) NOT NULL,
  `matematikal` int(11) NOT NULL,
  `musikal` int(11) NOT NULL,
  `kinestik` int(11) NOT NULL,
  `spartial` int(11) NOT NULL,
  `intrapersonal` int(11) NOT NULL,
  `interpersonal` int(11) NOT NULL,
  `naturalistik` int(11) NOT NULL,
  `eksistensial` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `nilai`
--
ALTER TABLE `nilai`
  ADD PRIMARY KEY (`id_nilai`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `nilai`
--
ALTER TABLE `nilai`
  MODIFY `id_nilai` int(11) NOT NULL AUTO_INCREMENT;
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 */;


text:
insert into item (item_id, item_name, item_desc, item_price) values(1,'item One', 'test 1', 35.12);

insert into item (item_id, item_name, item_desc, item_price) values(2,'Pogo stick', 'Pogo stick', 466.12);

insert into item (item_id, item_name, item_desc, item_price) values(3,'Raft', 'Raft', 345.12);

insert into item (item_id, item_name, item_desc, item_price) values(4,'Skate Board', 'Skating', 135.71);

insert into item (item_id, item_name, item_desc, item_price) values(5,'Umbrella', 'Umbrella for Rain', 619.25);

insert into item (item_id, item_name, item_desc, item_price) values(6,'Glue', 'Glue for home', 432.73);

insert into item (item_id, item_name, item_desc, item_price) values(7,'Paint', 'Paint for Room', 1311.40);

insert into item (item_id, item_name, item_desc, item_price) values(8,'Red paint', 'Red paint for room', 1135.71);

insert into item (item_id, item_name, item_desc, item_price) values(9,'Household Chairs', 'Chairs for house', 25.71);

insert into item (item_id, item_name, item_desc, item_price) values(10,'Office Chairs', 'Chairs for office', 395.98);

insert into item (item_id, item_name, item_desc, item_price) values(11,'Outdoor Chairs', 'Chairs for outdoor activities', 1234.36);

insert into EMPLOYEE (id, name, salary) values(1,'Steve Jobs', 55000);

insert into EMPLOYEE (id, name, salary) values(1,'Bill Hages', 45000);

insert into EMPLOYEE (id, name, salary) values(1,'Mark clinch', 57000);


text:
DROP DATABASE IF EXISTS post;

CREATE DATABASE post;

USE post;

CREATE TABLE user (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    passwd VARCHAR(64),

    PRIMARY KEY(id),
    UNIQUE(username)
);

CREATE TABLE post (
    id INT AUTO_INCREMENT,
    user_id_fk INT,
    text TEXT,
    date DATETIME,

    PRIMARY KEY (id),
    FOREIGN KEY (user_id_fk) REFERENCES user(id)
);

-- INSERTS

INSERT INTO user VALUES (NULL,"klawx3", SHA2('123',0));

INSERT INTO post VALUES (NULL,(SELECT id FROM user WHERE username = "klawx3"), "texto 1",now());
INSERT INTO post VALUES (NULL,(SELECT id FROM user WHERE username = "klawx3"), "texto 2",now());


text:
/*
   Copyright 2016 Aaron Morelli

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

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

	PROJECT NAME: ChiRho https://github.com/AaronMorelli/ChiRho

	PROJECT DESCRIPTION: A T-SQL toolkit for troubleshooting performance and stability problems on SQL Server instances

	FILE NAME: AutoWho.trgUPD_AutoWhoUserCollectionOptions.sql

	TRIGGER NAME: AutoWho.trgUPD_AutoWhoUserCollectionOptions

	AUTHOR:			Aaron Morelli
					aaronmorelli@zoho.com
					@sqlcrossjoin
					sqlcrossjoin.wordpress.com

	PURPOSE: Copies data updated in the UserCollectionOptions table to the history table.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [AutoWho].[trgUPD_AutoWhoUserCollectionOptions] ON [AutoWho].[UserCollectionOptions]

FOR UPDATE
AS 	BEGIN

INSERT INTO [AutoWho].[UserCollectionOptions_History](
	[OptionSet],
	[IncludeIdleWithTran],
	[IncludeIdleWithoutTran],
	[DurationFilter],
	[IncludeDBs],
	[ExcludeDBs],
	[HighTempDBThreshold],
	[CollectSystemSpids],
	[HideSelf],
	[ObtainBatchText],
	[ObtainQueryPlanForStatement],
	[ObtainQueryPlanForBatch],
	[ObtainLocksForBlockRelevantThreshold],
	[InputBufferThreshold],
	[ParallelWaitsThreshold],
	[QueryPlanThreshold],
	[QueryPlanThresholdBlockRel],
	[BlockingChainThreshold],
	[BlockingChainDepth],
	[TranDetailsThreshold],
	[DebugSpeed],
	[SaveBadDims],
	[Enable8666],
	[ResolvePageLatches],
	[ResolveLockWaits],
	[UseBackgroundThresholdIgnore],

	[HistoryInsertDate],
	[HistoryInsertDateUTC],
	[TriggerAction],
	[LastModifiedUser]
)
SELECT
	[OptionSet],
	[IncludeIdleWithTran],
	[IncludeIdleWithoutTran],
	[DurationFilter],
	[IncludeDBs],
	[ExcludeDBs],
	[HighTempDBThreshold],
	[CollectSystemSpids],
	[HideSelf],
	[ObtainBatchText],
	[ObtainQueryPlanForStatement],
	[ObtainQueryPlanForBatch],
	[ObtainLocksForBlockRelevantThreshold],
	[InputBufferThreshold],
	[ParallelWaitsThreshold],
	[QueryPlanThreshold],
	[QueryPlanThresholdBlockRel],
	[BlockingChainThreshold],
	[BlockingChainDepth],
	[TranDetailsThreshold],
	[DebugSpeed],
	[SaveBadDims],
	[Enable8666],
	[ResolvePageLatches],
	[ResolveLockWaits],
	[UseBackgroundThresholdIgnore],
	GETDATE(),
	GETUTCDATE(),
	'Update',
	SUSER_SNAME()
FROM inserted
;

END
GO


text:
CREATE DATABASE  IF NOT EXISTS `users_sign_up_details`;
USE `users_sign_up_details`;

--
-- Table structure for table `users`
--

CREATE TABLE `usersdetails` (
  `firstName` varchar(15) NOT NULL,
  `lastName` varchar(15) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `city` varchar(15) NOT NULL,
  `password` varchar(15) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `users` (
  `username` varchar(50) NOT NULL,
  `password` varchar(15) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `authorities` (
  `username` varchar(50) NOT NULL,
  `authority` varchar(20) NOT NULL,
  UNIQUE KEY `authorities_idx_1` (`username`,`authority`),
  CONSTRAINT `authorities_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `root` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) NOT NULL,
  `rootName` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `USER_AND_ROOTNAME_UNIQUE` (`userName`,`rootName`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

CREATE TABLE `subroot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subrootName` varchar(50) DEFAULT NULL,
  `root_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `USER_AND_ROOTNAME_SUBROOTNAME_UNIQUE` (`subrootName`,`root_id`),
  KEY `FK_ROOT_ID_idx` (`root_id`),
  CONSTRAINT `FK_ROOT` FOREIGN KEY (`root_id`) REFERENCES `root` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=latin1


text:
-- This SQL code was generated by sklearn2sql (development version).
-- Copyright 2018

-- Model : KerasClassifier_Dense
-- Dataset : iris_str_tgt
-- Database : pgsql

-- This SQL code can contain one or more statements, to be executed in the order they appear in this file.

-- Model deployment code

WITH keras_input AS
(SELECT "ADS"."KEY" AS "KEY", "ADS"."Feature_0" AS "Feature_0", "ADS"."Feature_1" AS "Feature_1", "ADS"."Feature_2" AS "Feature_2", "ADS"."Feature_3" AS "Feature_3"
FROM iris_str_tgt AS "ADS"),
keras_input_1 AS
(SELECT keras_input."KEY" AS "KEY", keras_input."Feature_0" AS "Feature_0", keras_input."Feature_1" AS "Feature_1", keras_input."Feature_2" AS "Feature_2", keras_input."Feature_3" AS "Feature_3"
FROM keras_input),
layer_dense_1 AS
(SELECT keras_input_1."KEY" AS "KEY", 0.0 + -0.11281444952672026 * keras_input_1."Feature_0" + -0.6290437167939718 * keras_input_1."Feature_1" + -0.68533367483761 * keras_input_1."Feature_2" + 0.09062090724345317 * keras_input_1."Feature_3" AS output_1, -0.4390126008080478 + 0.23114170420204763 * keras_input_1."Feature_0" + -0.06872032881625609 * keras_input_1."Feature_1" + -0.1094032335192306 * keras_input_1."Feature_2" + 0.5379434624360198 * keras_input_1."Feature_3" AS output_2, 0.0 + -0.5357525615511114 * keras_input_1."Feature_0" + 0.4315035234678404 * keras_input_1."Feature_1" + -0.7939947958188902 * keras_input_1."Feature_2" + -0.8410838228616502 * keras_input_1."Feature_3" AS output_3, 0.0 + -0.32287987074367663 * keras_input_1."Feature_0" + -0.11664781485514286 * keras_input_1."Feature_1" + -0.30826797999578026 * keras_input_1."Feature_2" + -0.3120039953711615 * keras_input_1."Feature_3" AS output_4
FROM keras_input_1),
activation_relu AS
(SELECT layer_dense_1."KEY" AS "KEY", greatest(layer_dense_1.output_1, 0) AS output_1, greatest(layer_dense_1.output_2, 0) AS output_2, greatest(layer_dense_1.output_3, 0) AS output_3, greatest(layer_dense_1.output_4, 0) AS output_4
FROM layer_dense_1),
activation_relu_1 AS
(SELECT activation_relu."KEY" AS "KEY", activation_relu.output_1 AS output_1, activation_relu.output_2 AS output_2, activation_relu.output_3 AS output_3, activation_relu.output_4 AS output_4
FROM activation_relu),
activation_relu_1_1 AS
(SELECT activation_relu_1."KEY" AS "KEY", activation_relu_1.output_1 AS output_1, activation_relu_1.output_2 AS output_2, activation_relu_1.output_3 AS output_3, activation_relu_1.output_4 AS output_4
FROM activation_relu_1),
layer_dense_2 AS
(SELECT activation_relu_1_1."KEY" AS "KEY", 0.263699557502044 + 0.6305062762098601 * activation_relu_1_1.output_1 + -0.3585335779138224 * activation_relu_1_1.output_2 + 0.5741738380977317 * activation_relu_1_1.output_3 + 0.07926929534297522 * activation_relu_1_1.output_4 AS output_1, 0.2282176511352423 + -0.7566051101956481 * activation_relu_1_1.output_1 + -0.13540007001209264 * activation_relu_1_1.output_2 + 0.60860151581279 * activation_relu_1_1.output_3 + -0.6083992426049094 * activation_relu_1_1.output_4 AS output_2, -0.3723983663467255 + -0.5296362740553289 * activation_relu_1_1.output_1 + 0.5671672549949567 * activation_relu_1_1.output_2 + 0.5610256732633128 * activation_relu_1_1.output_3 + 0.8782704380577033 * activation_relu_1_1.output_4 AS output_3
FROM activation_relu_1_1),
layer_dense_2_1 AS
(SELECT layer_dense_2."KEY" AS "KEY", layer_dense_2.output_1 AS output_1, layer_dense_2.output_2 AS output_2, layer_dense_2.output_3 AS output_3
FROM layer_dense_2),
score_soft_max_step1 AS
(SELECT layer_dense_2_1."KEY" AS "KEY", layer_dense_2_1.output_1 AS "Score_0", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_1))) AS "exp_Score_0", layer_dense_2_1.output_2 AS "Score_1", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_2))) AS "exp_Score_1", layer_dense_2_1.output_3 AS "Score_2", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_3))) AS "exp_Score_2"
FROM layer_dense_2_1),
score_class_union_soft AS
(SELECT soft_scu."KEY" AS "KEY", soft_scu.class AS class, soft_scu."exp_Score" AS "exp_Score"
FROM (SELECT score_soft_max_step1."KEY" AS "KEY", 0 AS class, score_soft_max_step1."exp_Score_0" AS "exp_Score"
FROM score_soft_max_step1 UNION ALL SELECT score_soft_max_step1."KEY" AS "KEY", 1 AS class, score_soft_max_step1."exp_Score_1" AS "exp_Score"
FROM score_soft_max_step1 UNION ALL SELECT score_soft_max_step1."KEY" AS "KEY", 2 AS class, score_soft_max_step1."exp_Score_2" AS "exp_Score"
FROM score_soft_max_step1) AS soft_scu),
score_soft_max AS
(SELECT score_soft_max_step1."KEY" AS "KEY", score_soft_max_step1."Score_0" AS "Score_0", score_soft_max_step1."exp_Score_0" AS "exp_Score_0", score_soft_max_step1."Score_1" AS "Score_1", score_soft_max_step1."exp_Score_1" AS "exp_Score_1", score_soft_max_step1."Score_2" AS "Score_2", score_soft_max_step1."exp_Score_2" AS "exp_Score_2", sum_exp_t."KEY_sum" AS "KEY_sum", sum_exp_t."sum_ExpScore" AS "sum_ExpScore"
FROM score_soft_max_step1 LEFT OUTER JOIN (SELECT score_class_union_soft."KEY" AS "KEY_sum", sum(score_class_union_soft."exp_Score") AS "sum_ExpScore"
FROM score_class_union_soft GROUP BY score_class_union_soft."KEY") AS sum_exp_t ON score_soft_max_step1."KEY" = sum_exp_t."KEY_sum"),
layer_softmax AS
(SELECT score_soft_max."KEY" AS "KEY", score_soft_max."exp_Score_0" / score_soft_max."sum_ExpScore" AS output_1, score_soft_max."exp_Score_1" / score_soft_max."sum_ExpScore" AS output_2, score_soft_max."exp_Score_2" / score_soft_max."sum_ExpScore" AS output_3
FROM score_soft_max),
orig_cte AS
(SELECT layer_softmax."KEY" AS "KEY", CAST(NULL AS FLOAT) AS "Score_CLASS_0", CAST(NULL AS FLOAT) AS "Score_CLASS_1", CAST(NULL AS FLOAT) AS "Score_CLASS_2", layer_softmax.output_1 AS "Proba_CLASS_0", layer_softmax.output_2 AS "Proba_CLASS_1", layer_softmax.output_3 AS "Proba_CLASS_2", CAST(NULL AS FLOAT) AS "LogProba_CLASS_0", CAST(NULL AS FLOAT) AS "LogProba_CLASS_1", CAST(NULL AS FLOAT) AS "LogProba_CLASS_2", CAST(NULL AS BIGINT) AS "Decision", CAST(NULL AS FLOAT) AS "DecisionProba"
FROM layer_softmax),
score_class_union AS
(SELECT scu."KEY_u" AS "KEY_u", scu.class AS class, scu."LogProba" AS "LogProba", scu."Proba" AS "Proba", scu."Score" AS "Score"
FROM (SELECT orig_cte."KEY" AS "KEY_u", 'CLASS_0' AS class, orig_cte."LogProba_CLASS_0" AS "LogProba", orig_cte."Proba_CLASS_0" AS "Proba", orig_cte."Score_CLASS_0" AS "Score"
FROM orig_cte UNION ALL SELECT orig_cte."KEY" AS "KEY_u", 'CLASS_1' AS class, orig_cte."LogProba_CLASS_1" AS "LogProba", orig_cte."Proba_CLASS_1" AS "Proba", orig_cte."Score_CLASS_1" AS "Score"
FROM orig_cte UNION ALL SELECT orig_cte."KEY" AS "KEY_u", 'CLASS_2' AS class, orig_cte."LogProba_CLASS_2" AS "LogProba", orig_cte."Proba_CLASS_2" AS "Proba", orig_cte."Score_CLASS_2" AS "Score"
FROM orig_cte) AS scu),
score_max AS
(SELECT orig_cte."KEY" AS "KEY", orig_cte."Score_CLASS_0" AS "Score_CLASS_0", orig_cte."Score_CLASS_1" AS "Score_CLASS_1", orig_cte."Score_CLASS_2" AS "Score_CLASS_2", orig_cte."Proba_CLASS_0" AS "Proba_CLASS_0", orig_cte."Proba_CLASS_1" AS "Proba_CLASS_1", orig_cte."Proba_CLASS_2" AS "Proba_CLASS_2", orig_cte."LogProba_CLASS_0" AS "LogProba_CLASS_0", orig_cte."LogProba_CLASS_1" AS "LogProba_CLASS_1", orig_cte."LogProba_CLASS_2" AS "LogProba_CLASS_2", orig_cte."Decision" AS "Decision", orig_cte."DecisionProba" AS "DecisionProba", max_select."KEY_m" AS "KEY_m", max_select."max_Proba" AS "max_Proba"
FROM orig_cte LEFT OUTER JOIN (SELECT score_class_union."KEY_u" AS "KEY_m", max(score_class_union."Proba") AS "max_Proba"
FROM score_class_union GROUP BY score_class_union."KEY_u") AS max_select ON orig_cte."KEY" = max_select."KEY_m"),
union_with_max AS
(SELECT score_class_union."KEY_u" AS "KEY_u", score_class_union.class AS class, score_class_union."LogProba" AS "LogProba", score_class_union."Proba" AS "Proba", score_class_union."Score" AS "Score", score_max."KEY" AS "KEY", score_max."Score_CLASS_0" AS "Score_CLASS_0", score_max."Score_CLASS_1" AS "Score_CLASS_1", score_max."Score_CLASS_2" AS "Score_CLASS_2", score_max."Proba_CLASS_0" AS "Proba_CLASS_0", score_max."Proba_CLASS_1" AS "Proba_CLASS_1", score_max."Proba_CLASS_2" AS "Proba_CLASS_2", score_max."LogProba_CLASS_0" AS "LogProba_CLASS_0", score_max."LogProba_CLASS_1" AS "LogProba_CLASS_1", score_max."LogProba_CLASS_2" AS "LogProba_CLASS_2", score_max."Decision" AS "Decision", score_max."DecisionProba" AS "DecisionProba", score_max."KEY_m" AS "KEY_m", score_max."max_Proba" AS "max_Proba"
FROM score_class_union LEFT OUTER JOIN score_max ON score_class_union."KEY_u" = score_max."KEY"),
arg_max_cte AS
(SELECT score_max."KEY" AS "KEY", score_max."Score_CLASS_0" AS "Score_CLASS_0", score_max."Score_CLASS_1" AS "Score_CLASS_1", score_max."Score_CLASS_2" AS "Score_CLASS_2", score_max."Proba_CLASS_0" AS "Proba_CLASS_0", score_max."Proba_CLASS_1" AS "Proba_CLASS_1", score_max."Proba_CLASS_2" AS "Proba_CLASS_2", score_max."LogProba_CLASS_0" AS "LogProba_CLASS_0", score_max."LogProba_CLASS_1" AS "LogProba_CLASS_1", score_max."LogProba_CLASS_2" AS "LogProba_CLASS_2", score_max."Decision" AS "Decision", score_max."DecisionProba" AS "DecisionProba", score_max."KEY_m" AS "KEY_m", score_max."max_Proba" AS "max_Proba", "arg_max_t_Proba"."KEY_Proba" AS "KEY_Proba", "arg_max_t_Proba"."arg_max_Proba" AS "arg_max_Proba"
FROM score_max LEFT OUTER JOIN (SELECT union_with_max."KEY" AS "KEY_Proba", max(union_with_max.class) AS "arg_max_Proba"
FROM union_with_max
WHERE union_with_max."max_Proba" <= union_with_max."Proba" GROUP BY union_with_max."KEY") AS "arg_max_t_Proba" ON score_max."KEY" = "arg_max_t_Proba"."KEY_Proba")
 SELECT arg_max_cte."KEY" AS "KEY", arg_max_cte."Score_CLASS_0" AS "Score_CLASS_0", arg_max_cte."Score_CLASS_1" AS "Score_CLASS_1", arg_max_cte."Score_CLASS_2" AS "Score_CLASS_2", arg_max_cte."Proba_CLASS_0" AS "Proba_CLASS_0", arg_max_cte."Proba_CLASS_1" AS "Proba_CLASS_1", arg_max_cte."Proba_CLASS_2" AS "Proba_CLASS_2", CASE WHEN (arg_max_cte."Proba_CLASS_0" IS NULL OR arg_max_cte."Proba_CLASS_0" > 0.0) THEN ln(arg_max_cte."Proba_CLASS_0") ELSE -1.79769313486231e+308 END AS "LogProba_CLASS_0", CASE WHEN (arg_max_cte."Proba_CLASS_1" IS NULL OR arg_max_cte."Proba_CLASS_1" > 0.0) THEN ln(arg_max_cte."Proba_CLASS_1") ELSE -1.79769313486231e+308 END AS "LogProba_CLASS_1", CASE WHEN (arg_max_cte."Proba_CLASS_2" IS NULL OR arg_max_cte."Proba_CLASS_2" > 0.0) THEN ln(arg_max_cte."Proba_CLASS_2") ELSE -1.79769313486231e+308 END AS "LogProba_CLASS_2", arg_max_cte."arg_max_Proba" AS "Decision", arg_max_cte."max_Proba" AS "DecisionProba"
FROM arg_max_cte


text:
/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 100121
Source Host           : localhost:3306
Source Database       : jobs

Target Server Type    : MYSQL
Target Server Version : 100121
File Encoding         : 65001

Date: 2019-06-01 21:37:47
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hash_url` varchar(64) COLLATE utf8_german2_ci DEFAULT NULL,
  `job_url` varchar(255) COLLATE utf8_german2_ci DEFAULT NULL,
  `name` varchar(64) COLLATE utf8_german2_ci DEFAULT NULL,
  `max_salary` int(11) DEFAULT NULL,
  `min_salary` int(11) DEFAULT NULL,
  `settlement` varchar(16) COLLATE utf8_german2_ci DEFAULT NULL,
  `address` varchar(128) COLLATE utf8_german2_ci DEFAULT NULL,
  `company_name` varchar(128) COLLATE utf8_german2_ci DEFAULT NULL,
  `job_info` text COLLATE utf8_german2_ci,
  `work_year` varchar(64) COLLATE utf8_german2_ci DEFAULT NULL,
  `experience` varchar(16) COLLATE utf8_german2_ci DEFAULT NULL,
  `publish_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_url` (`hash_url`) USING HASH
) ENGINE=MyISAM AUTO_INCREMENT=13852 DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;


text:
CREATE TABLE `admin_location_requested` (
  `id_admin_location_requested` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_admin` int(11) unsigned DEFAULT NULL,
  `status` enum('permitted','denied') DEFAULT 'permitted',
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id_admin_location_requested`),
  KEY `id_admin` (`id_admin`),
  CONSTRAINT `admin_location_requested_ibfk_1` FOREIGN KEY (`id_admin`) REFERENCES `admin` (`id_admin`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


text:
--
--    Copyright 2009-2017 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

INSERT INTO sequence VALUES('ordernum', 1000);

INSERT INTO signon VALUES('j2ee','j2ee');
INSERT INTO signon VALUES('ACID','ACID');

INSERT INTO account VALUES('j2ee','yourname@yourdomain.com','ABC', 'XYX', 'OK', '901 San Antonio Road', 'MS UCUP02-206', 'Palo Alto', 'CA', '94303', 'USA',  '555-555-5555');
INSERT INTO account VALUES('ACID','acid@yourdomain.com','ABC', 'XYX', 'OK', '901 San Antonio Road', 'MS UCUP02-206', 'Palo Alto', 'CA', '94303', 'USA',  '555-555-5555');

INSERT INTO profile VALUES('j2ee','english','DOGS',1,1);
INSERT INTO profile VALUES('ACID','english','CATS',1,1);

INSERT INTO bannerdata VALUES ('FISH','<image src="../images/banner_fish.gif">');
INSERT INTO bannerdata VALUES ('CATS','<image src="../images/banner_cats.gif">');
INSERT INTO bannerdata VALUES ('DOGS','<image src="../images/banner_dogs.gif">');
INSERT INTO bannerdata VALUES ('REPTILES','<image src="../images/banner_reptiles.gif">');
INSERT INTO bannerdata VALUES ('BIRDS','<image src="../images/banner_birds.gif">');

INSERT INTO category VALUES ('FISH','Fish','<image src="../images/fish_icon.gif"> Fish');
INSERT INTO category VALUES ('DOGS','Dogs','<image src="../images/dogs_icon.gif"> Dogs');
INSERT INTO category VALUES ('REPTILES','Reptiles','<image src="../images/reptiles_icon.gif"> Reptiles');
INSERT INTO category VALUES ('CATS','Cats','<image src="../images/cats_icon.gif"> Cats');
INSERT INTO category VALUES ('BIRDS','Birds','<image src="../images/birds_icon.gif"> Birds');

INSERT INTO product VALUES ('FI-SW-01','FISH','Angelfish','<image src="../images/fish1.gif">Salt Water fish from Australia');
INSERT INTO product VALUES ('FI-SW-02','FISH','Tiger Shark','<image src="../images/fish4.gif">Salt Water fish from Australia');
INSERT INTO product VALUES ('FI-FW-01','FISH', 'Koi','<image src="../images/fish3.gif">Fresh Water fish from Japan');
INSERT INTO product VALUES ('FI-FW-02','FISH', 'Goldfish','<image src="../images/fish2.gif">Fresh Water fish from China');
INSERT INTO product VALUES ('K9-BD-01','DOGS','Bulldog','<image src="../images/dog2.gif">Friendly dog from England');
INSERT INTO product VALUES ('K9-PO-02','DOGS','Poodle','<image src="../images/dog6.gif">Cute dog from France');
INSERT INTO product VALUES ('K9-DL-01','DOGS', 'Dalmation','<image src="../images/dog5.gif">Great dog for a Fire Station');
INSERT INTO product VALUES ('K9-RT-01','DOGS', 'Golden Retriever','<image src="../images/dog1.gif">Great family dog');
INSERT INTO product VALUES ('K9-RT-02','DOGS', 'Labrador Retriever','<image src="../images/dog5.gif">Great hunting dog');
INSERT INTO product VALUES ('K9-CW-01','DOGS', 'Chihuahua','<image src="../images/dog4.gif">Great companion dog');
INSERT INTO product VALUES ('RP-SN-01','REPTILES','Rattlesnake','<image src="../images/snake1.gif">Doubles as a watch dog');
INSERT INTO product VALUES ('RP-LI-02','REPTILES','Iguana','<image src="../images/lizard1.gif">Friendly green friend');
INSERT INTO product VALUES ('FL-DSH-01','CATS','Manx','<image src="../images/cat2.gif">Great for reducing mouse populations');
INSERT INTO product VALUES ('FL-DLH-02','CATS','Persian','<image src="../images/cat1.gif">Friendly house cat, doubles as a princess');
INSERT INTO product VALUES ('AV-CB-01','BIRDS','Amazon Parrot','<image src="../images/bird2.gif">Great companion for up to 75 years');
INSERT INTO product VALUES ('AV-SB-02','BIRDS','Finch','<image src="../images/bird1.gif">Great stress reliever');

INSERT INTO supplier VALUES (1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797');
INSERT INTO supplier VALUES (2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797');

INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-24','K9-RT-02',255.50, 92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-25','K9-RT-02',325.29, 90.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female');

INSERT INTO inventory (itemid, qty ) VALUES ('EST-1',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-2',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-3',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-4',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-5',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-6',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-7',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-8',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-9',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-10',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-11',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-12',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-13',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-14',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-15',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-16',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-17',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-18',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-19',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-20',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-21',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-22',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-23',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-24',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-25',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-26',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-27',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-28',10000);

select * from inventory;


text:
define expected_grants = "&1"
declare
  c_expected_grants constant dbmsoutput_linesarray := dbmsoutput_linesarray( &expected_grants );

  l_expected_grants dbmsoutput_linesarray := c_expected_grants;
  l_missing_grants varchar2(4000);
begin
  if user != SYS_CONTEXT('userenv','current_schema') then
    for i in 1 .. l_expected_grants.count loop
      if l_expected_grants(i) != 'ADMINISTER DATABASE TRIGGER' then
        l_expected_grants(i) := replace(l_expected_grants(i),' ',' ANY ');
      end if;
    end loop;
  end if;

  with
    x as (
         select '' as remove from dual
         union all
         select ' ANY' as remove  from dual
    )
  select listagg(' -  '||privilege,CHR(10)) within group(order by privilege)
    into l_missing_grants
    from (
      select column_value as privilege
        from table(l_expected_grants)
      minus (
      select replace(p.privilege, x.remove) as privilege
        from role_sys_privs p
        join session_roles r using (role)
        cross join  x
      union all
      select replace(p.privilege, x.remove) as privilege
        from user_sys_privs p
        cross join  x
       )
    );
  if l_missing_grants is not null then
    raise_application_error(
        -20000
        , 'The following privileges are required for user "'||user||'" to install into schema "'||SYS_CONTEXT('userenv','current_schema')||'"'||CHR(10)
          ||l_missing_grants
          ||'Please read the installation documentation at http://utplsql.org/utPLSQL/'
    );
  end if;
end;
/


text:
-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 04, 2021 at 12:14 PM
-- Server version: 10.1.38-MariaDB
-- PHP Version: 7.3.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `skutbandung`
--

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

--
-- Table structure for table `alats`
--

CREATE TABLE `alats` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `id_alat` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `alats`
--

INSERT INTO `alats` (`id`, `id_alat`, `status`, `created_at`, `updated_at`) VALUES
(2, '45170845', 0, '2021-06-27 19:29:45', '2021-07-15 03:25:32'),
(3, '46170846', 0, '2021-07-15 03:25:45', '2021-07-15 03:25:45');

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

--
-- Table structure for table `cuacas`
--

CREATE TABLE `cuacas` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `id_user` int(11) DEFAULT NULL,
  `id_destinasi` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `suhu` float NOT NULL DEFAULT '0',
  `kelembapan` float NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cuacas`
--

INSERT INTO `cuacas` (`id`, `id_user`, `id_destinasi`, `suhu`, `kelembapan`, `status`, `created_at`, `updated_at`) VALUES
(65, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:14:29', '2021-07-07 15:14:29'),
(66, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:14:33', '2021-07-07 15:14:33'),
(67, NULL, '45170845', 26.4, 95, 0, '2021-07-07 15:14:39', '2021-07-07 15:14:39'),
(68, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:14:45', '2021-07-07 15:14:45'),
(69, NULL, '45170845', 26.5, 95, 0, '2021-07-07 15:14:51', '2021-07-07 15:14:51'),
(70, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:14:57', '2021-07-07 15:14:57'),
(71, NULL, '45170845', 26.7, 95, 0, '2021-07-07 15:15:02', '2021-07-07 15:15:02'),
(72, NULL, '45170845', 26.5, 95, 0, '2021-07-07 15:15:08', '2021-07-07 15:15:08'),
(73, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:14', '2021-07-07 15:15:14'),
(74, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:19', '2021-07-07 15:15:19'),
(75, NULL, '45170845', 26.7, 95, 0, '2021-07-07 15:15:25', '2021-07-07 15:15:25'),
(76, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:31', '2021-07-07 15:15:31'),
(77, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:37', '2021-07-07 15:15:37'),
(78, NULL, '45170845', 26.7, 95, 0, '2021-07-07 15:15:42', '2021-07-07 15:15:42'),
(79, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:48', '2021-07-07 15:15:48'),
(80, NULL, '45170845', 26.6, 95, 0, '2021-07-07 15:15:54', '2021-07-07 15:15:54'),
(81, NULL, '45170845', 26.7, 95, 0, '2021-07-07 15:15:59', '2021-07-07 15:15:59'),
(82, NULL, '45170845', 26.8, 95, 0, '2021-07-07 15:16:05', '2021-07-07 15:16:05'),
(83, NULL, '45170845', 26.7, 95, 0, '2021-07-07 15:16:10', '2021-07-07 15:16:10'),
(84, NULL, '45170845', 26.8, 95, 0, '2021-07-07 15:16:16', '2021-07-07 15:16:16'),
(85, NULL, '45170845', 27.3, 95, 0, '2021-07-09 03:55:02', '2021-07-09 03:55:02'),
(86, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:09', '2021-07-09 03:55:09'),
(87, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:14', '2021-07-09 03:55:14'),
(88, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:20', '2021-07-09 03:55:20'),
(89, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:26', '2021-07-09 03:55:26'),
(90, NULL, '45170845', 27, 95, 0, '2021-07-09 03:55:32', '2021-07-09 03:55:32'),
(91, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:37', '2021-07-09 03:55:37'),
(92, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:43', '2021-07-09 03:55:43'),
(93, NULL, '45170845', 27.5, 95, 0, '2021-07-09 03:55:49', '2021-07-09 03:55:49'),
(94, NULL, '45170845', 27.4, 95, 0, '2021-07-09 03:55:55', '2021-07-09 03:55:55'),
(95, NULL, '45170845', 27.3, 95, 0, '2021-07-09 03:56:01', '2021-07-09 03:56:01'),
(96, NULL, '45170845', 27.3, 95, 0, '2021-07-09 03:56:08', '2021-07-09 03:56:08'),
(97, NULL, '45170845', 27.3, 95, 0, '2021-07-09 03:56:14', '2021-07-09 03:56:14'),
(98, NULL, '45170845', 27.3, 95, 0, '2021-07-09 03:56:19', '2021-07-09 03:56:19'),
(99, NULL, '45170845', 27.7, 95, 0, '2021-07-09 11:15:04', '2021-07-09 11:15:04'),
(100, NULL, '45170845', 27.7, 95, 0, '2021-07-09 11:15:10', '2021-07-09 11:15:10'),
(101, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:15:16', '2021-07-09 11:15:16'),
(102, NULL, '45170845', 27.7, 95, 0, '2021-07-09 11:15:22', '2021-07-09 11:15:22'),
(103, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:15:28', '2021-07-09 11:15:28'),
(104, NULL, '45170845', 27.7, 95, 0, '2021-07-09 11:15:34', '2021-07-09 11:15:34'),
(105, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:15:40', '2021-07-09 11:15:40'),
(106, NULL, '45170845', 28.1, 95, 0, '2021-07-09 11:15:46', '2021-07-09 11:15:46'),
(107, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:15:59', '2021-07-09 11:15:59'),
(108, NULL, '45170845', 28, 95, 0, '2021-07-09 11:16:01', '2021-07-09 11:16:01'),
(109, NULL, '45170845', 27.7, 95, 0, '2021-07-09 11:16:07', '2021-07-09 11:16:07'),
(110, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:16:13', '2021-07-09 11:16:13'),
(111, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:16:19', '2021-07-09 11:16:19'),
(112, NULL, '45170845', 27.9, 95, 0, '2021-07-09 11:16:25', '2021-07-09 11:16:25'),
(113, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:16:31', '2021-07-09 11:16:31'),
(114, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:16:37', '2021-07-09 11:16:37'),
(115, NULL, '45170845', 27.9, 95, 0, '2021-07-09 11:16:43', '2021-07-09 11:16:43'),
(116, NULL, '45170845', 28, 95, 0, '2021-07-09 11:16:48', '2021-07-09 11:16:48'),
(117, NULL, '45170845', 28.1, 95, 0, '2021-07-09 11:17:28', '2021-07-09 11:17:28'),
(118, NULL, '45170845', 27.8, 95, 0, '2021-07-09 11:17:34', '2021-07-09 11:17:34'),
(119, NULL, '45170845', 27.3, 95, 0, '2021-07-09 12:30:55', '2021-07-09 12:30:55'),
(120, NULL, '45170845', 27.3, 95, 0, '2021-07-09 12:31:01', '2021-07-09 12:31:01'),
(121, NULL, '45170845', 27.3, 95, 0, '2021-07-09 12:31:07', '2021-07-09 12:31:07'),
(122, NULL, '45170845', 27.3, 95, 0, '2021-07-09 12:31:12', '2021-07-09 12:31:12'),
(123, NULL, '45170845', 27.4, 95, 0, '2021-07-09 12:31:18', '2021-07-09 12:31:18'),
(124, NULL, '45170845', 27.4, 95, 0, '2021-07-09 12:31:24', '2021-07-09 12:31:24'),
(125, NULL, '45170845', 27.5, 95, 0, '2021-07-09 12:31:29', '2021-07-09 12:31:29'),
(126, NULL, '45170845', 27.7, 95, 0, '2021-07-09 12:31:35', '2021-07-09 12:31:35'),
(127, NULL, '45170845', 27.7, 95, 0, '2021-07-09 12:31:41', '2021-07-09 12:31:41'),
(128, NULL, '45170845', 28.3, 95, 0, '2021-07-09 12:31:46', '2021-07-09 12:31:46'),
(129, NULL, '45170845', 29.3, 95, 0, '2021-07-09 12:31:52', '2021-07-09 12:31:52'),
(130, NULL, '45170845', 29.3, 95, 0, '2021-07-09 12:31:58', '2021-07-09 12:31:58'),
(131, NULL, '45170845', 29.1, 95, 0, '2021-07-09 12:32:03', '2021-07-09 12:32:03'),
(132, NULL, '45170845', 29, 95, 0, '2021-07-09 12:32:09', '2021-07-09 12:32:09'),
(133, NULL, '45170845', 28.9, 95, 0, '2021-07-09 12:32:15', '2021-07-09 12:32:15'),
(134, NULL, '45170845', 28.4, 95, 0, '2021-07-09 12:32:20', '2021-07-09 12:32:20'),
(135, NULL, '45170845', 28.5, 95, 0, '2021-07-09 12:32:26', '2021-07-09 12:32:26'),
(136, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:11', '2021-07-12 06:05:11'),
(137, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:15', '2021-07-12 06:05:15'),
(138, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:19', '2021-07-12 06:05:19'),
(139, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:23', '2021-07-12 06:05:23'),
(140, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:27', '2021-07-12 06:05:27'),
(141, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:30', '2021-07-12 06:05:30'),
(142, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:34', '2021-07-12 06:05:34'),
(143, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:38', '2021-07-12 06:05:38'),
(144, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:42', '2021-07-12 06:05:42'),
(145, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:46', '2021-07-12 06:05:46'),
(146, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:50', '2021-07-12 06:05:50'),
(147, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:54', '2021-07-12 06:05:54'),
(148, NULL, '45170845', 35, 95, 0, '2021-07-12 06:05:58', '2021-07-12 06:05:58'),
(149, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:02', '2021-07-12 06:06:02'),
(150, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:06', '2021-07-12 06:06:06'),
(151, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:10', '2021-07-12 06:06:10'),
(152, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:14', '2021-07-12 06:06:14'),
(153, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:19', '2021-07-12 06:06:19'),
(154, NULL, '45170845', 35, 95, 0, '2021-07-12 06:06:23', '2021-07-12 06:06:23'),
(155, NULL, '45170845', 27.3, 82, 0, '2021-07-12 08:50:54', '2021-07-12 08:50:54'),
(156, NULL, '45170845', 27.3, 81, 0, '2021-07-12 08:51:00', '2021-07-12 08:51:00'),
(157, NULL, '45170845', 27.5, 81, 0, '2021-07-12 08:51:06', '2021-07-12 08:51:06'),
(158, NULL, '45170845', 27.5, 81, 0, '2021-07-12 08:51:11', '2021-07-12 08:51:11'),
(159, NULL, '45170845', 27.6, 82, 0, '2021-07-12 08:51:17', '2021-07-12 08:51:17'),
(160, NULL, '45170845', 27.6, 91, 0, '2021-07-12 08:51:22', '2021-07-12 08:51:22'),
(161, NULL, '45170845', 27.8, 83, 0, '2021-07-12 08:51:28', '2021-07-12 08:51:28'),
(162, NULL, '45170845', 27.6, 81, 0, '2021-07-12 08:51:33', '2021-07-12 08:51:33'),
(163, NULL, '45170845', 27.7, 79, 0, '2021-07-12 08:51:39', '2021-07-12 08:51:39'),
(164, NULL, '45170845', 28, 80, 0, '2021-07-12 08:51:44', '2021-07-12 08:51:44'),
(165, NULL, '45170845', 28.1, 79, 0, '2021-07-12 08:51:50', '2021-07-12 08:51:50'),
(166, NULL, '45170845', 28.2, 75, 0, '2021-07-12 09:20:35', '2021-07-12 09:20:35'),
(167, NULL, '45170845', 27.8, 73, 0, '2021-07-12 09:20:41', '2021-07-12 09:20:41'),
(168, NULL, '45170845', 27.8, 73, 0, '2021-07-12 09:20:46', '2021-07-12 09:20:46'),
(169, NULL, '45170845', 27.9, 73, 0, '2021-07-12 09:20:52', '2021-07-12 09:20:52'),
(170, NULL, '45170845', 27.9, 73, 0, '2021-07-12 09:20:57', '2021-07-12 09:20:57'),
(171, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:03', '2021-07-12 09:21:03'),
(172, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:08', '2021-07-12 09:21:08'),
(173, NULL, '45170845', 28, 74, 0, '2021-07-12 09:21:13', '2021-07-12 09:21:13'),
(174, NULL, '45170845', 28.3, 74, 0, '2021-07-12 09:21:19', '2021-07-12 09:21:19'),
(175, NULL, '45170845', 28.3, 74, 0, '2021-07-12 09:21:24', '2021-07-12 09:21:24'),
(176, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:29', '2021-07-12 09:21:29'),
(177, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:35', '2021-07-12 09:21:35'),
(178, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:41', '2021-07-12 09:21:41'),
(179, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:46', '2021-07-12 09:21:46'),
(180, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:52', '2021-07-12 09:21:52'),
(181, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:21:58', '2021-07-12 09:21:58'),
(182, NULL, '45170845', 28.2, 74, 0, '2021-07-12 09:22:04', '2021-07-12 09:22:04'),
(183, NULL, '45170845', 28, 75, 0, '2021-07-12 09:22:11', '2021-07-12 09:22:11'),
(184, NULL, '45170845', 27.9, 74, 0, '2021-07-12 09:22:16', '2021-07-12 09:22:16'),
(185, NULL, '45170845', 27.9, 73, 0, '2021-07-12 09:22:22', '2021-07-12 09:22:22'),
(186, NULL, '45170845', 28, 74, 0, '2021-07-12 09:22:28', '2021-07-12 09:22:28'),
(187, NULL, '45170845', 28.1, 75, 0, '2021-07-12 09:22:36', '2021-07-12 09:22:36'),
(188, NULL, '45170845', 28.1, 74, 0, '2021-07-12 09:22:41', '2021-07-12 09:22:41'),
(189, NULL, '45170845', 28, 76, 0, '2021-07-12 09:22:47', '2021-07-12 09:22:47'),
(190, NULL, '45170845', 28, 76, 0, '2021-07-12 09:22:52', '2021-07-12 09:22:52'),
(191, NULL, '45170845', 27.9, 75, 0, '2021-07-12 09:22:58', '2021-07-12 09:22:58'),
(192, NULL, '45170845', 28, 75, 0, '2021-07-12 09:23:04', '2021-07-12 09:23:04'),
(193, NULL, '45170845', 28.1, 75, 0, '2021-07-12 09:23:10', '2021-07-12 09:23:10'),
(194, NULL, '45170845', 28.1, 76, 0, '2021-07-12 09:23:16', '2021-07-12 09:23:16'),
(195, NULL, '45170845', 28.3, 76, 0, '2021-07-12 09:23:22', '2021-07-12 09:23:22'),
(196, NULL, '45170845', 27.9, 75, 0, '2021-07-12 09:23:27', '2021-07-12 09:23:27'),
(197, NULL, '45170845', 28.4, 76, 0, '2021-07-12 09:23:33', '2021-07-12 09:23:33'),
(198, NULL, '45170845', 28.2, 76, 0, '2021-07-12 09:23:39', '2021-07-12 09:23:39'),
(199, NULL, '45170845', 28.1, 76, 0, '2021-07-12 09:23:44', '2021-07-12 09:23:44'),
(200, NULL, '45170845', 28.1, 75, 0, '2021-07-12 09:23:50', '2021-07-12 09:23:50'),
(201, NULL, '45170845', 28.3, 75, 0, '2021-07-12 09:23:55', '2021-07-12 09:23:55'),
(202, NULL, '45170845', 28.9, 70, 0, '2021-07-16 03:45:04', '2021-07-16 03:45:04'),
(203, NULL, '45170845', 28.7, 70, 0, '2021-07-16 03:45:10', '2021-07-16 03:45:10'),
(204, NULL, '45170845', 28.8, 69, 0, '2021-07-16 03:45:15', '2021-07-16 03:45:15'),
(205, NULL, '45170845', 29.1, 69, 0, '2021-07-16 03:45:21', '2021-07-16 03:45:21'),
(206, NULL, '45170845', 28.5, 68, 0, '2021-07-16 03:45:29', '2021-07-16 03:45:29'),
(207, NULL, '45170845', 29.5, 68, 0, '2021-07-16 03:45:35', '2021-07-16 03:45:35'),
(208, NULL, '45170845', 29.4, 68, 0, '2021-07-16 03:45:40', '2021-07-16 03:45:40'),
(209, NULL, '45170845', 29.8, 68, 0, '2021-07-16 03:45:46', '2021-07-16 03:45:46'),
(210, NULL, '45170845', 29.6, 68, 0, '2021-07-16 03:45:51', '2021-07-16 03:45:51'),
(211, NULL, '45170845', 28.8, 67, 0, '2021-07-16 03:45:56', '2021-07-16 03:45:56'),
(212, NULL, '45170845', 28.6, 67, 0, '2021-07-16 03:46:02', '2021-07-16 03:46:02'),
(213, NULL, '45170845', 29.7, 68, 0, '2021-07-16 03:46:07', '2021-07-16 03:46:07'),
(214, NULL, '45170845', 28.7, 67, 0, '2021-07-16 03:46:13', '2021-07-16 03:46:13'),
(215, NULL, '45170845', 28, 68, 0, '2021-07-16 03:46:19', '2021-07-16 03:46:19'),
(216, NULL, '45170845', 28.8, 68, 0, '2021-07-16 03:46:24', '2021-07-16 03:46:24'),
(217, NULL, '45170845', 28.8, 67, 0, '2021-07-16 03:46:36', '2021-07-16 03:46:36'),
(218, NULL, '45170845', 27.4, 66, 0, '2021-07-16 03:46:42', '2021-07-16 03:46:42'),
(219, NULL, '45170845', 27, 66, 0, '2021-07-16 03:46:47', '2021-07-16 03:46:47'),
(220, NULL, '45170845', 30.3, 68, 0, '2021-07-16 03:46:53', '2021-07-16 03:46:53'),
(221, NULL, '45170845', 27.3, 66, 0, '2021-07-16 03:46:58', '2021-07-16 03:46:58'),
(222, NULL, '45170845', 29.4, 69, 0, '2021-07-16 03:47:04', '2021-07-16 03:47:04'),
(223, NULL, '45170845', 27.1, 67, 0, '2021-07-16 03:47:09', '2021-07-16 03:47:09'),
(224, NULL, '45170845', 26.7, 67, 0, '2021-07-16 03:47:15', '2021-07-16 03:47:15'),
(225, NULL, '45170845', 28.2, 68, 0, '2021-07-16 03:47:21', '2021-07-16 03:47:21'),
(226, NULL, '45170845', 28.2, 68, 0, '2021-07-16 03:47:26', '2021-07-16 03:47:26'),
(227, NULL, '45170845', 28.9, 68, 0, '2021-07-16 03:47:31', '2021-07-16 03:47:31'),
(228, NULL, '45170845', 28.3, 68, 0, '2021-07-16 03:48:01', '2021-07-16 03:48:01'),
(229, NULL, '45170845', 26.6, 67, 0, '2021-07-16 03:48:02', '2021-07-16 03:48:02'),
(230, NULL, '45170845', 28.1, 69, 0, '2021-07-16 03:48:02', '2021-07-16 03:48:02'),
(231, NULL, '45170845', 28, 70, 0, '2021-07-16 03:48:06', '2021-07-16 03:48:06'),
(232, NULL, '45170845', 28.1, 70, 0, '2021-07-16 03:48:12', '2021-07-16 03:48:12'),
(233, NULL, '45170845', 28, 70, 0, '2021-07-16 03:48:29', '2021-07-16 03:48:29'),
(234, NULL, '45170845', 28.1, 70, 0, '2021-07-16 03:48:37', '2021-07-16 03:48:37'),
(235, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:48:42', '2021-07-16 03:48:42'),
(236, NULL, '45170845', 28.1, 70, 0, '2021-07-16 03:48:48', '2021-07-16 03:48:48'),
(237, NULL, '45170845', 26.5, 69, 0, '2021-07-16 03:48:53', '2021-07-16 03:48:53'),
(238, NULL, '45170845', 28, 70, 0, '2021-07-16 03:48:59', '2021-07-16 03:48:59'),
(239, NULL, '45170845', 28.9, 70, 0, '2021-07-16 03:49:04', '2021-07-16 03:49:04'),
(240, NULL, '45170845', 27.9, 69, 0, '2021-07-16 03:49:10', '2021-07-16 03:49:10'),
(241, NULL, '45170845', 29.2, 71, 0, '2021-07-16 03:49:16', '2021-07-16 03:49:16'),
(242, NULL, '45170845', 28.1, 70, 0, '2021-07-16 03:49:21', '2021-07-16 03:49:21'),
(243, NULL, '45170845', 26.7, 69, 0, '2021-07-16 03:49:30', '2021-07-16 03:49:30'),
(244, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:49:36', '2021-07-16 03:49:36'),
(245, NULL, '45170845', 27.2, 69, 0, '2021-07-16 03:49:41', '2021-07-16 03:49:41'),
(246, NULL, '45170845', 29.1, 71, 0, '2021-07-16 03:49:47', '2021-07-16 03:49:47'),
(247, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:49:52', '2021-07-16 03:49:52'),
(248, NULL, '45170845', 28.6, 69, 0, '2021-07-16 03:50:01', '2021-07-16 03:50:01'),
(249, NULL, '45170845', 28.8, 70, 0, '2021-07-16 03:50:07', '2021-07-16 03:50:07'),
(250, NULL, '45170845', 26.8, 69, 0, '2021-07-16 03:50:12', '2021-07-16 03:50:12'),
(251, NULL, '45170845', 29.8, 71, 0, '2021-07-16 03:50:17', '2021-07-16 03:50:17'),
(252, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:50:26', '2021-07-16 03:50:26'),
(253, NULL, '45170845', 26.4, 69, 0, '2021-07-16 03:50:32', '2021-07-16 03:50:32'),
(254, NULL, '45170845', 27.4, 69, 0, '2021-07-16 03:50:37', '2021-07-16 03:50:37'),
(255, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:50:46', '2021-07-16 03:50:46'),
(256, NULL, '45170845', 27.1, 69, 0, '2021-07-16 03:50:52', '2021-07-16 03:50:52'),
(257, NULL, '45170845', 27.1, 69, 0, '2021-07-16 03:50:57', '2021-07-16 03:50:57'),
(258, NULL, '45170845', 28, 70, 0, '2021-07-16 03:51:03', '2021-07-16 03:51:03'),
(259, NULL, '45170845', 28.7, 70, 0, '2021-07-16 03:51:12', '2021-07-16 03:51:12'),
(260, NULL, '45170845', 26.9, 69, 0, '2021-07-16 03:51:17', '2021-07-16 03:51:17'),
(261, NULL, '45170845', 29.2, 71, 0, '2021-07-16 03:51:27', '2021-07-16 03:51:27'),
(262, NULL, '45170845', 27.2, 69, 0, '2021-07-16 03:51:32', '2021-07-16 03:51:32'),
(263, NULL, '45170845', 27.6, 70, 0, '2021-07-16 03:51:38', '2021-07-16 03:51:38'),
(264, NULL, '45170845', 28.6, 71, 0, '2021-07-16 03:51:43', '2021-07-16 03:51:43'),
(265, NULL, '45170845', 27.7, 70, 0, '2021-07-16 03:51:52', '2021-07-16 03:51:52'),
(266, NULL, '45170845', 28.9, 71, 0, '2021-07-16 03:51:58', '2021-07-16 03:51:58'),
(267, NULL, '45170845', 27.8, 70, 0, '2021-07-16 03:52:10', '2021-07-16 03:52:10'),
(268, NULL, '45170845', 27.1, 70, 0, '2021-07-16 03:52:15', '2021-07-16 03:52:15'),
(269, NULL, '45170845', 27, 70, 0, '2021-07-16 03:52:24', '2021-07-16 03:52:24'),
(270, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:52:33', '2021-07-16 03:52:33'),
(271, NULL, '45170845', 27.8, 69, 0, '2021-07-16 03:52:39', '2021-07-16 03:52:39'),
(272, NULL, '45170845', 27.2, 69, 0, '2021-07-16 03:52:48', '2021-07-16 03:52:48'),
(273, NULL, '45170845', 28.3, 71, 0, '2021-07-16 03:52:53', '2021-07-16 03:52:53'),
(274, NULL, '45170845', 28.6, 71, 0, '2021-07-16 03:53:02', '2021-07-16 03:53:02'),
(275, NULL, '45170845', 28.4, 71, 0, '2021-07-16 03:53:07', '2021-07-16 03:53:07'),
(276, NULL, '45170845', 27.8, 87, 0, '2021-07-16 11:25:52', '2021-07-16 11:25:52'),
(277, NULL, '45170845', 27.7, 87, 0, '2021-07-16 11:25:52', '2021-07-16 11:25:52'),
(278, NULL, '45170845', 28.6, 88, 0, '2021-07-16 11:26:03', '2021-07-16 11:26:03'),
(279, NULL, '45170845', 27, 87, 0, '2021-07-16 11:26:09', '2021-07-16 11:26:09'),
(280, NULL, '45170845', 27.7, 87, 0, '2021-07-16 11:26:14', '2021-07-16 11:26:14'),


text:
--DROP PROC uspComInsMemberItem
CREATE PROC uspComInsMemberItem
@MemberItemID nvarchar(MAX) = NULL
,@MemberID nvarchar(MAX) = NULL
,@ItemListID nvarchar(MAX) = NULL
,@ItemCount nvarchar(MAX) = NULL
,@ItemStatus nvarchar(MAX) = NULL
,@sCol1 nvarchar(MAX) = NULL
,@sCol2 nvarchar(MAX) = NULL
,@sCol3 nvarchar(MAX) = NULL
,@sCol4 nvarchar(MAX) = NULL
,@sCol5 nvarchar(MAX) = NULL
,@sCol6 nvarchar(MAX) = NULL
,@sCol7 nvarchar(MAX) = NULL
,@sCol8 nvarchar(MAX) = NULL
,@sCol9 nvarchar(MAX) = NULL
,@sCol10 nvarchar(MAX) = NULL
AS
--set, lock option check
set nocount on
INSERT INTO MemberItems(MemberItemID, MemberID, ItemListID, ItemCount, ItemStatus, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10)
values(@MemberItemID, @MemberID, @ItemListID, @ItemCount, @ItemStatus, @sCol1, @sCol2, @sCol3, @sCol4, @sCol5, @sCol6, @sCol7, @sCol8, @sCol9, @sCol10)
select @@ROWCOUNT as Reslut
GO

/*
-----------------------------------------------------------------------
EXEC uspComInsMemberItem 'MemberItemsID19', 'aaa', 'itemid4', '9', 'ItemStatus', 'sCol1', 'sCol2', 'sCol3', 'sCol4', 'sCol5', 'sCol6', 'sCol7', 'sCol8', 'sCol9', 'sCol10'
-----------------------------------------------------------------------
select * from MemberItems
select * from ItemLists
*/


text:
alter table ACT_HI_TASKINST
	add CLAIM_TIME_ timestamp;

alter table ACT_HI_TASKINST
  add FORM_KEY_ varchar(255);

Call Sysproc.admin_cmd ('REORG TABLE ACT_HI_TASKINST');

alter table ACT_RU_IDENTITYLINK
  add PROC_INST_ID_ varchar(64);

alter table ACT_RU_IDENTITYLINK
    add constraint ACT_FK_IDL_PROCINST
    foreign key (PROC_INST_ID_)
    references ACT_RU_EXECUTION (ID_);

Call Sysproc.admin_cmd ('REORG TABLE ACT_RU_IDENTITYLINK');

create index ACT_IDX_HI_ACT_INST_EXEC on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_);


text:
-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Waktu pembuatan: 25 Sep 2021 pada 03.47
-- Versi server: 10.4.20-MariaDB
-- Versi PHP: 8.0.9

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: `projekci_db`
--

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

--
-- Struktur dari tabel `pendaftar`
--

CREATE TABLE `pendaftar` (
  `id` int(11) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `umur` varchar(100) NOT NULL,
  `judul` varchar(100) NOT NULL,
  `pemrakarsa` varchar(100) NOT NULL,
  `penanggung_jawab` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data untuk tabel `pendaftar`
--

INSERT INTO `pendaftar` (`id`, `nama`, `email`, `umur`, `judul`, `pemrakarsa`, `penanggung_jawab`) VALUES
(1, 'agung', 'agungdwika99@gmail.com', '22', 'apa aja deh', 'banda aceh ', 'banda aceh'),
(3, 'rifki', 'agungdwika99@gmail.com', '22', 'sdsdsdsds', 'wdwdwdwdwd', 'ddggefd'),
(4, 'Agung Dwika Rudiyanto', 'agungdwika99@gmail.com', '22', 'sdsdsdsds', 'wdwdwdwdwd', 'ddggefd'),
(6, 'didik', 'adan@gmail.com', '22', 'sdsdsdsds', 'sasasasas', 'ddggefd');

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

--
-- Struktur dari tabel `tb_instansi`
--

CREATE TABLE `tb_instansi` (
  `instansi_id` int(11) NOT NULL,
  `instansi_nama` varchar(100) NOT NULL,
  `instansi_jenis_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data untuk tabel `tb_instansi`
--

INSERT INTO `tb_instansi` (`instansi_id`, `instansi_nama`, `instansi_jenis_id`) VALUES
(2, 'kabupaten aceh tengah 4', 1),
(12, 'kabupaten aceh tengah', 3);

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

--
-- Struktur dari tabel `tb_jenis_instansi`
--

CREATE TABLE `tb_jenis_instansi` (
  `jenis_id` int(11) NOT NULL,
  `jenis_nama` varchar(50) NOT NULL,
  `jenis_desk` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data untuk tabel `tb_jenis_instansi`
--

INSERT INTO `tb_jenis_instansi` (`jenis_id`, `jenis_nama`, `jenis_desk`) VALUES
(1, 'jakarta', '2'),
(2, 'jakarta barat', '3'),
(3, 'jakarta barat', '3'),
(5, 'jaksel', '1'),
(7, 'bogor', '1');

--
-- Indexes for dumped tables
--

--
-- Indeks untuk tabel `pendaftar`
--
ALTER TABLE `pendaftar`
  ADD PRIMARY KEY (`id`);

--
-- Indeks untuk tabel `tb_instansi`
--
ALTER TABLE `tb_instansi`
  ADD PRIMARY KEY (`instansi_id`);

--
-- Indeks untuk tabel `tb_jenis_instansi`
--
ALTER TABLE `tb_jenis_instansi`
  ADD PRIMARY KEY (`jenis_id`);

--
-- AUTO_INCREMENT untuk tabel yang dibuang
--

--
-- AUTO_INCREMENT untuk tabel `pendaftar`
--
ALTER TABLE `pendaftar`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT untuk tabel `tb_instansi`
--
ALTER TABLE `tb_instansi`
  MODIFY `instansi_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT untuk tabel `tb_jenis_instansi`
--
ALTER TABLE `tb_jenis_instansi`
  MODIFY `jenis_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
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 */;


text:
CREATE TABLE IF NOT EXISTS `txffc` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `qishu` CHAR(20) NOT NULL COMMENT  '开奖期数',
  `one` TINYINT(3) NOT NULL COMMENT  '万位 第一位号码',
  `two` TINYINT(3) NOT NULL COMMENT  '千位 第二位号码',
  `three` TINYINT(3) NOT NULL COMMENT  '百位 第三位号码',
  `four` TINYINT(3) NOT NULL COMMENT  '十位 第四位号码',
  `five` TINYINT(3) NOT NULL COMMENT  '个位 第五位号码',
  `code` CHAR(20) NOT NULL COMMENT  '开奖号码',
  `front_three_type` TINYINT(1) NOT NULL COMMENT  '前三类型
  1=>组6
2=>组3',
  `center_three_type` TINYINT(1) NOT NULL COMMENT  '中三类型
1=>组6
2=>组3',
  `after_three_type` TINYINT(1) NOT NULL COMMENT  '后三类型
1=>组6
2=>组3',
  `kj_time` CHAR(50) NULL DEFAULT NULL COMMENT  '开奖时间',
  `time` INT(11) NOT NULL COMMENT  '数据记录时间',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT =  '腾讯分分彩';

CREATE TABLE IF NOT EXISTS `analysisTxffc` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `txffc_id` INT(11) NOT NULL COMMENT  '腾讯分分彩 数据分析表',
  `front_three_lucky_txt` TEXT NULL DEFAULT NULL COMMENT  '前三 中奖号码',
  `front_three_regret_txt` TEXT NULL DEFAULT NULL COMMENT  '前三 未中奖号码',
  `center_three_lucky_txt` TEXT NULL DEFAULT NULL COMMENT  '中三中奖号',
  `center_three_regret_txt` TEXT NULL DEFAULT NULL COMMENT  '中三未中奖号',
  `after_three_lucky_txt` TEXT NULL DEFAULT NULL COMMENT  '后三中奖号码',
  `after_three_regret_txt` TEXT NULL DEFAULT NULL COMMENT  '后三未中奖号码',
  `data_txt` TEXT NULL DEFAULT NULL COMMENT  '当前导入的数据',
  `type` INT(11) NOT NULL COMMENT  '腾讯数据包id',
  `time` INT(11) NOT NULL COMMENT  '数据创建日期',
  PRIMARY KEY (`id`),
  INDEX `fk_analysisCqssc_cqssc10_idx` (`txffc_id` ASC),
  CONSTRAINT `fk_analysisCqssc_cqssc10`
    FOREIGN KEY (`txffc_id`)
    REFERENCES `txffc` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT =  '腾讯分分彩';

CREATE TABLE IF NOT EXISTS `txdata` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `alias` CHAR(100) NOT NULL COMMENT  '数据包别名',
  `data_txt` TEXT NOT NULL COMMENT  '数据包内容',
  `start` CHAR(3) NOT NULL COMMENT  '报警开始时间',
  `end` CHAR(3) NOT NULL COMMENT  '报警结束时间',
  `regret_number` INT(11) NOT NULL COMMENT  '邮件报警 几期未中奖报警',
  `forever` TINYINT(1) NOT NULL DEFAULT 0 COMMENT  '是否开启每一期中奖与未中奖通知 默认为关闭',
  `state` TINYINT(1) NOT NULL DEFAULT 0 COMMENT  '报警状态 默认关闭',
  `time` INT(11) NOT NULL COMMENT  '数据创建时间',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT =  '腾讯分分彩数据包';

ALTER TABLE `packet`
CHANGE COLUMN `type` `type` TINYINT(1) NOT NULL COMMENT  '彩种类型
1=>重庆时时彩
2=>天津时时彩
3=>新疆时时彩
4=>台湾五分彩
5=>腾讯分分彩';


text:
-- CreateEnum
CREATE TYPE "gender" AS ENUM ('FEMININO', 'MASCULINO', 'SEM_GENERO');

-- CreateTable
CREATE TABLE "user" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "firstName" VARCHAR(50) NOT NULL,
    "lastName" VARCHAR(100) NOT NULL,
    "email" VARCHAR(100) NOT NULL,
    "gender" "gender" NOT NULL,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "dateOfBirth" TIMESTAMP(6) NOT NULL,
    "createdAt" TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deletedAt" TIMESTAMP(6),

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "address" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "street" VARCHAR(255) NOT NULL,
    "city" VARCHAR(255) NOT NULL,
    "state" VARCHAR(4) NOT NULL,
    "zipCode" VARCHAR(50) NOT NULL,
    "country" VARCHAR(255) NOT NULL,
    "userId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "student" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "grade" VARCHAR(255) NOT NULL,
    "fathersName" VARCHAR(255) NOT NULL,
    "mothersName" VARCHAR(255) NOT NULL,
    "parentEmail" VARCHAR(255) NOT NULL,
    "userId" UUID NOT NULL,
    "createdAt" TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deletedAt" TIMESTAMP(6),

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "professor" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "graduationLevel" VARCHAR(255) NOT NULL,
    "userId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "studentScore" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "score" INTEGER NOT NULL,
    "expertiseId" UUID NOT NULL,
    "professorId" UUID NOT NULL,
    "studentId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "professorScore" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "score" INTEGER NOT NULL,
    "professorId" UUID NOT NULL,
    "studentId" UUID NOT NULL,
    "expertiseId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "expertise" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "price" DECIMAL(10,2) NOT NULL,
    "duration" INTEGER NOT NULL,
    "professorId" UUID NOT NULL,
    "classTypeId" UUID NOT NULL,
    "gradeId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "classType" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "name" VARCHAR(255) NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "grade" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "name" VARCHAR(255) NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "classSchedule" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "timeSlot" INTEGER NOT NULL,
    "meetingLink" VARCHAR(255) NOT NULL,
    "date" DATE NOT NULL,
    "reasonToDecline" TEXT NOT NULL,
    "accepted" BOOLEAN NOT NULL,
    "professorId" UUID NOT NULL,
    "studentId" UUID NOT NULL,
    "classTypeId" UUID NOT NULL,
    "gradeId" UUID NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "user.email_unique" ON "user"("email");

-- CreateIndex
CREATE UNIQUE INDEX "address_userId_unique" ON "address"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "student_userId_unique" ON "student"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "professor_userId_unique" ON "professor"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "studentScore_expertiseId_unique" ON "studentScore"("expertiseId");

-- CreateIndex
CREATE UNIQUE INDEX "studentScore_professorId_unique" ON "studentScore"("professorId");

-- CreateIndex
CREATE UNIQUE INDEX "studentScore_studentId_unique" ON "studentScore"("studentId");

-- CreateIndex
CREATE UNIQUE INDEX "professorScore_professorId_unique" ON "professorScore"("professorId");

-- CreateIndex
CREATE UNIQUE INDEX "professorScore_studentId_unique" ON "professorScore"("studentId");

-- CreateIndex
CREATE UNIQUE INDEX "professorScore_expertiseId_unique" ON "professorScore"("expertiseId");

-- CreateIndex
CREATE UNIQUE INDEX "expertise_professorId_unique" ON "expertise"("professorId");

-- CreateIndex
CREATE UNIQUE INDEX "expertise_classTypeId_unique" ON "expertise"("classTypeId");

-- CreateIndex
CREATE UNIQUE INDEX "expertise_gradeId_unique" ON "expertise"("gradeId");

-- CreateIndex
CREATE UNIQUE INDEX "classSchedule_professorId_unique" ON "classSchedule"("professorId");

-- CreateIndex
CREATE UNIQUE INDEX "classSchedule_studentId_unique" ON "classSchedule"("studentId");

-- CreateIndex
CREATE UNIQUE INDEX "classSchedule_classTypeId_unique" ON "classSchedule"("classTypeId");

-- CreateIndex
CREATE UNIQUE INDEX "classSchedule_gradeId_unique" ON "classSchedule"("gradeId");

-- AddForeignKey
ALTER TABLE "professorScore" ADD FOREIGN KEY ("professorId") REFERENCES "professor"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "professorScore" ADD FOREIGN KEY ("studentId") REFERENCES "student"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "professorScore" ADD FOREIGN KEY ("expertiseId") REFERENCES "expertise"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "classSchedule" ADD FOREIGN KEY ("professorId") REFERENCES "professor"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "classSchedule" ADD FOREIGN KEY ("studentId") REFERENCES "student"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "classSchedule" ADD FOREIGN KEY ("classTypeId") REFERENCES "classType"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "classSchedule" ADD FOREIGN KEY ("gradeId") REFERENCES "grade"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "address" ADD FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "professor" ADD FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "student" ADD FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "studentScore" ADD FOREIGN KEY ("expertiseId") REFERENCES "expertise"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "studentScore" ADD FOREIGN KEY ("professorId") REFERENCES "professor"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "studentScore" ADD FOREIGN KEY ("studentId") REFERENCES "student"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "expertise" ADD FOREIGN KEY ("professorId") REFERENCES "professor"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "expertise" ADD FOREIGN KEY ("classTypeId") REFERENCES "classType"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "expertise" ADD FOREIGN KEY ("gradeId") REFERENCES "grade"("id") ON DELETE CASCADE ON UPDATE CASCADE;


text:
-- This script links each loop to the nearest hectopunten, using a datset provided by CSResearch, rws_raw.utrecht_rws_loops

-- add columns to dataset provided by CS Research for road direction and dvk_letter
alter table rws_raw.utrecht_rws_loops
add column "road_dir" varchar,
add column "dvk_or_dir" varchar,
add column "road_num" decimal;

-- add road dir to utrecht data
update rws_raw.utrecht_rws_loops
set road_dir =
case when "highway" = 'A2' then
	case when "dir" = 'positive' then 'Li'
		else 'Re'
		end
	when "highway" = 'A2 hrb' then
		case when "dir" = 'positive' then 'Li'
		else 'Re'
		end
	else
		case when "dir" = 'positive' then 'Re'
		else 'Li'
		end
	end;

-- set variable that is set to Li/Re when main carriageway, and dvk letter if not
-- This will need to be updated when we have roads besides main carriageway
update rws_raw.utrecht_rws_loops
set dvk_or_dir =
(case
	when "desc_en" = 'mainCarriageway' then road_dir
	else NULL
end);

update rws_raw.utrecht_rws_loops
set road_num = cast(substring(highway,2,2) as decimal);

-- Create table to link each loop to a hectokey.
-- Give each loop a unique identifier (measurementSiteReference is not unique across lanes)
drop table if exists rws_clean.flow_hect_link;
create table rws_clean.flow_hect_link(
	loop_unique_key varchar not null,
	loop_id varchar,
	hectokey varchar not null,
	road_lane varchar,
	road_num decimal,
	road_type varchar,
	hecto decimal,
	road_dir varchar,
	lat decimal,
	long decimal
);

insert into rws_clean.flow_hect_link
select
	-- add logic if we use loops beside MONIBAS
	concat(loop_id,'.',lane,'.',dvk_or_dir,'.',road_dir) as loop_unique_key,
	loop_id,
	concat(road_num,'.',"proximal_hecto",'.',dvk_or_dir, '.',road_dir) as hectokey,
	lane AS road_lane,
	road_num,
	--note for our data now we only have main carriageway, this will need to be updated if we use other roads
	case
		when u.desc_en = 'mainCarriageway' then null
		else 'TBD'
	end as road_type,
	u.proximal_hecto*.1 as hecto,
	u.road_dir as road_dir,
	lat,
	lon as long
from rws_raw.utrecht_rws_loops as u;

-- check there are no loop ids with more than one hectopunten
--select count(distinct(hectokey))
--from rws_clean.flow_hect_link
--group by loop_unique_key
--having count(distinct(hectokey)) > 1;


text:
-- PostRR - src/postrr_comments.sql
-- Copyright (C) 2012 Sebastian 'tokkee' Harl <sh@tokkee.org>
-- All rights reserved.
--
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:
-- 1. Redistributions of source code must retain the above copyright
--    notice, this list of conditions and the following disclaimer.
-- 2. Redistributions in binary form must reproduce the above copyright
--    notice, this list of conditions and the following disclaimer in the
--    documentation and/or other materials provided with the distribution.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
-- TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
-- PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR
-- CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
-- EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
-- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
-- OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
-- WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
-- OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
-- ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

--
-- PostRR - PostgreSQL Round-Robin Extension
--

COMMENT ON TYPE RRTimeslice IS 'postrr type: A timeslice implementing round-robin features. It is defined by the length of the slice and the number of slices before wrapping around.';

COMMENT ON TYPE CData IS 'cdata type: A floating point data type (double precision) implementing consolidation functions.';

-- vim: set tw=78 sw=4 ts=4 noexpandtab :