db hardi

-- phpMyAdmin SQL Dump
-- version 4.4.15.9
-- https://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 24, 2018 at 05:28 PM
-- Server version: 5.6.37
-- PHP Version: 7.1.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `fb_hardi`
--

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

--
-- Table structure for table `t_admin`
--

CREATE TABLE IF NOT EXISTS `t_admin` (
  `id_admin` tinyint(2) NOT NULL,
  `username` varchar(35) NOT NULL,
  `fullname` varchar(45) NOT NULL,
  `password` varchar(255) NOT NULL,
  `level` tinyint(1) NOT NULL,
  `email` varchar(100) NOT NULL,
  `reset` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_detail_order`
--

CREATE TABLE IF NOT EXISTS `t_detail_order` (
  `id_row` int(11) NOT NULL,
  `id_order` int(10) NOT NULL,
  `id_item` int(7) NOT NULL,
  `qty` int(4) NOT NULL,
  `biaya` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_items`
--

CREATE TABLE IF NOT EXISTS `t_items` (
  `id_item` int(7) NOT NULL,
  `nama_item` varchar(255) NOT NULL,
  `harga` int(10) NOT NULL,
  `berat` int(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `gambar` varchar(255) NOT NULL,
  `deskripsi` text NOT NULL,
  `stock` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_kategori`
--

CREATE TABLE IF NOT EXISTS `t_kategori` (
  `id_kategori` smallint(6) NOT NULL,
  `kategori` varchar(30) NOT NULL,
  `url` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_konfirmasi`
--

CREATE TABLE IF NOT EXISTS `t_konfirmasi` (
  `id_konfirm` int(10) NOT NULL,
  `id_order` int(15) NOT NULL,
  `bukti_tf` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_order`
--

CREATE TABLE IF NOT EXISTS `t_order` (
  `id_order` int(15) NOT NULL,
  `id_user` int(7) NOT NULL,
  `total` double NOT NULL,
  `tujuan` varchar(255) NOT NULL,
  `pos` int(5) NOT NULL,
  `kota` varchar(25) NOT NULL,
  `kurir` varchar(5) NOT NULL,
  `service` varchar(50) NOT NULL,
  `tgl_pesan` date NOT NULL,
  `bts_bayar` date NOT NULL,
  `status` enum('belum','proses','lain-lain','batal') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `t_order`
--
DELIMITER $$
CREATE TRIGGER `detail_after_update` AFTER UPDATE ON `t_order`
 FOR EACH ROW begin

insert into z_history(action,id_reff,type) values(new.status,new.id_order,'order');

if new.status = 'proses' then
begin
    update t_order_detail
    set proses=1
    where id_order=new.id_order;
end;
end if;

end
$$
DELIMITER ;

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

--
-- Table structure for table `t_order_detail`
--

CREATE TABLE IF NOT EXISTS `t_order_detail` (
  `id_row` int(11) NOT NULL,
  `id_order` int(10) NOT NULL,
  `id_item` int(7) NOT NULL,
  `qty` int(4) NOT NULL,
  `biaya` int(9) NOT NULL,
  `proses` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `t_order_detail`
--
DELIMITER $$
CREATE TRIGGER `update_stock_2` AFTER UPDATE ON `t_order_detail`
 FOR EACH ROW begin

insert into z_history(action,id_reff,type) values(new.proses,new.id_row,'order detail');

if new.proses = 1 then
begin
    update t_items
    set stock=stock-new.qty
    where id_item=new.id_item;
end;
end if;

END
$$
DELIMITER ;

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

--
-- Table structure for table `t_rkategori`
--

CREATE TABLE IF NOT EXISTS `t_rkategori` (
  `id_row` int(5) NOT NULL,
  `id_item` int(7) NOT NULL,
  `id_kategori` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `t_users`
--

CREATE TABLE IF NOT EXISTS `t_users` (
  `id_user` int(7) NOT NULL,
  `username` varchar(35) NOT NULL,
  `fullname` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `jk` enum('L','P') NOT NULL,
  `telp` varchar(20) NOT NULL,
  `alamat` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `reset` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `z_history`
--

CREATE TABLE IF NOT EXISTS `z_history` (
  `id` int(11) NOT NULL,
  `id_reff` int(11) NOT NULL,
  `action` varchar(30) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `t_detail_order`
--
ALTER TABLE `t_detail_order`
  ADD PRIMARY KEY (`id_row`);

--
-- Indexes for table `t_items`
--
ALTER TABLE `t_items`
  ADD PRIMARY KEY (`id_item`);

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

--
-- Indexes for table `t_konfirmasi`
--
ALTER TABLE `t_konfirmasi`
  ADD PRIMARY KEY (`id_konfirm`);

--
-- Indexes for table `t_order`
--
ALTER TABLE `t_order`
  ADD PRIMARY KEY (`id_order`);

--
-- Indexes for table `t_order_detail`
--
ALTER TABLE `t_order_detail`
  ADD PRIMARY KEY (`id_row`),
  ADD KEY `proses` (`proses`);

--
-- Indexes for table `t_rkategori`
--
ALTER TABLE `t_rkategori`
  ADD PRIMARY KEY (`id_row`);

--
-- Indexes for table `t_users`
--
ALTER TABLE `t_users`
  ADD PRIMARY KEY (`id_user`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `z_history`
--
ALTER TABLE `z_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id_reff` (`id_reff`),
  ADD KEY `type` (`type`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `t_admin`
--
ALTER TABLE `t_admin`
  MODIFY `id_admin` tinyint(2) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_detail_order`
--
ALTER TABLE `t_detail_order`
  MODIFY `id_row` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_items`
--
ALTER TABLE `t_items`
  MODIFY `id_item` int(7) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_kategori`
--
ALTER TABLE `t_kategori`
  MODIFY `id_kategori` smallint(6) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_konfirmasi`
--
ALTER TABLE `t_konfirmasi`
  MODIFY `id_konfirm` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_order`
--
ALTER TABLE `t_order`
  MODIFY `id_order` int(15) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_order_detail`
--
ALTER TABLE `t_order_detail`
  MODIFY `id_row` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_rkategori`
--
ALTER TABLE `t_rkategori`
  MODIFY `id_row` int(5) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `t_users`
--
ALTER TABLE `t_users`
  MODIFY `id_user` int(7) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `z_history`
--
ALTER TABLE `z_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;