fix

-- phpMyAdmin SQL Dump
-- version 4.4.15.9
-- https://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 21, 2018 at 08:11 AM
-- Server version: 5.6.37
-- PHP Version: 5.6.31

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

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

--
-- Table structure for table `mst_provider`
--

CREATE TABLE IF NOT EXISTS `mst_provider` (
  `provider_id` int(11) NOT NULL,
  `provider_code` varchar(200) NOT NULL,
  `provider_name` longtext NOT NULL,
  `provider_description` longtext NOT NULL,
  `provider_photo` varchar(200) NOT NULL,
  `provider_contact_person` varchar(200) NOT NULL,
  `provider_phone` varchar(100) NOT NULL,
  `provider_phone2` varchar(100) NOT NULL,
  `provider_fax` varchar(100) NOT NULL,
  `provider_email` varchar(200) NOT NULL,
  `provider_city` varchar(200) NOT NULL,
  `provider_postal_code` varchar(100) NOT NULL,
  `provider_address` text NOT NULL,
  `provider_keyword` varchar(200) NOT NULL,
  `provider_date_created` datetime NOT NULL,
  `provider_last_updated` datetime NOT NULL,
  `provider_log` longtext NOT NULL,
  `provider_deposit` int(11) DEFAULT '0',
  `provider_hutang` int(11) DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=690 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `mst_provider`
--

INSERT INTO `mst_provider` (`provider_id`, `provider_code`, `provider_name`, `provider_description`, `provider_photo`, `provider_contact_person`, `provider_phone`, `provider_phone2`, `provider_fax`, `provider_email`, `provider_city`, `provider_postal_code`, `provider_address`, `provider_keyword`, `provider_date_created`, `provider_last_updated`, `provider_log`, `provider_deposit`, `provider_hutang`) VALUES
(1, 'PS', 'PRIMA SPRING', '', '', 'Bu nani ', '0818450116', '081325646723', '', '', 'Semarang', '', 'Jl. Gedung Batu tengah no 14 Semarang', '', '0000-00-00 00:00:00', '2016-02-22 00:00:00', 'update by dwi', 0, 60037500),
(689, 'AHLER', 'AHLER ', '', '', '', '', '', '', '', '', '', '', '', '2018-04-09 14:04:55', '2018-04-09 14:04:56', '', 0, 1486);

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

--
-- Table structure for table `trx_purchase_order`
--

CREATE TABLE IF NOT EXISTS `trx_purchase_order` (
  `purchase_order_id` int(11) NOT NULL,
  `provider_id` int(11) NOT NULL,
  `sales_order_id` int(11) NOT NULL,
  `purchase_order_categories` enum('sales','buffer') NOT NULL DEFAULT 'sales',
  `purchase_order_code` varchar(200) NOT NULL,
  `purchase_order_date` date NOT NULL,
  `purchase_order_delivery_date` date NOT NULL,
  `purchase_order_note` longtext NOT NULL,
  `purchase_order_date_created` datetime NOT NULL,
  `purchase_order_last_updated` datetime NOT NULL,
  `purchase_order_log` longtext NOT NULL,
  `purchase_order_deposit` int(11) DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=1544 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `trx_purchase_order`
--

INSERT INTO `trx_purchase_order` (`purchase_order_id`, `provider_id`, `sales_order_id`, `purchase_order_categories`, `purchase_order_code`, `purchase_order_date`, `purchase_order_delivery_date`, `purchase_order_note`, `purchase_order_date_created`, `purchase_order_last_updated`, `purchase_order_log`, `purchase_order_deposit`) VALUES
(1542, 54, 211, 'sales', 'BSF043018', '2018-04-30', '2018-06-14', 'test', '2018-04-30 00:00:00', '2018-04-30 00:00:00', 'insert by dwi', 0),
(1543, 50, 212, 'sales', 'KM051118', '2018-05-11', '2018-06-25', 'test', '2018-05-11 00:00:00', '2018-05-11 00:00:00', 'insert by dwi', 0);

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

--
-- Table structure for table `trx_purchase_order_detail`
--

CREATE TABLE IF NOT EXISTS `trx_purchase_order_detail` (
  `purchase_order_detail_id` int(11) NOT NULL,
  `purchase_order_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `purchase_order_detail_qty` int(11) NOT NULL,
  `purchase_order_detail_price` int(11) DEFAULT NULL,
  `purchase_order_detail_desc` longtext NOT NULL,
  `purchase_order_detail_remax` longtext,
  `purchase_order_detail_date_created` datetime NOT NULL,
  `purchase_order_detail_last_updated` datetime NOT NULL,
  `purchase_order_detail_log` longtext NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `trx_purchase_order_detail`
--

INSERT INTO `trx_purchase_order_detail` (`purchase_order_detail_id`, `purchase_order_id`, `material_id`, `product_id`, `purchase_order_detail_qty`, `purchase_order_detail_price`, `purchase_order_detail_desc`, `purchase_order_detail_remax`, `purchase_order_detail_date_created`, `purchase_order_detail_last_updated`, `purchase_order_detail_log`) VALUES
(1, 1542, 1493, 1200, 10, 2150000, 'magohany', '', '2018-04-30 00:00:00', '2018-04-30 00:00:00', 'insert by dwi'),
(2, 1543, 2026, 1296, 10, 80, 'test', 'test', '2018-05-11 00:00:00', '2018-05-11 00:00:00', 'insert by dwi');

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

--
-- Table structure for table `trx_purchase_order_sample`
--

CREATE TABLE IF NOT EXISTS `trx_purchase_order_sample` (
  `purchase_order_sample_id` int(11) NOT NULL,
  `purchase_order_sample_code` varchar(11) NOT NULL DEFAULT '0',
  `provider_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `material_id` int(11) DEFAULT NULL,
  `id_induk` int(11) DEFAULT '0',
  `qty` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `desc` text,
  `remax` text,
  `delivery_date` date DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `trx_purchase_order_sample`
--

INSERT INTO `trx_purchase_order_sample` (`purchase_order_sample_id`, `purchase_order_sample_code`, `provider_id`, `product_id`, `material_id`, `id_induk`, `qty`, `price`, `desc`, `remax`, `delivery_date`, `date`) VALUES
(1, 'VV051218sam', 52, 1628, 1628, 0, 1, 700000, 'sampel', '', '2018-05-12', '2018-06-11'),
(2, 'MAR051618', 51, 1628, 1628, 0, 1, 850000, '', '', '2018-06-15', '2018-05-16'),
(3, 'DLJ051618', 55, 1635, 1635, 0, 1, 1800000, '', '', '2018-06-15', '2018-05-16');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mst_provider`
--
ALTER TABLE `mst_provider`
  ADD PRIMARY KEY (`provider_id`);

--
-- Indexes for table `trx_purchase_order`
--
ALTER TABLE `trx_purchase_order`
  ADD PRIMARY KEY (`purchase_order_id`),
  ADD KEY `provider_id` (`provider_id`);

--
-- Indexes for table `trx_purchase_order_detail`
--
ALTER TABLE `trx_purchase_order_detail`
  ADD PRIMARY KEY (`purchase_order_detail_id`),
  ADD KEY `purchase_order_id` (`purchase_order_id`),
  ADD KEY `material_id` (`material_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `trx_purchase_order_sample`
--
ALTER TABLE `trx_purchase_order_sample`
  ADD PRIMARY KEY (`purchase_order_sample_id`),
  ADD KEY `FK_trx_purchase_order_sample_mst_provider` (`provider_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `mst_provider`
--
ALTER TABLE `mst_provider`
  MODIFY `provider_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=690;
--
-- AUTO_INCREMENT for table `trx_purchase_order`
--
ALTER TABLE `trx_purchase_order`
  MODIFY `purchase_order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1544;
--
-- AUTO_INCREMENT for table `trx_purchase_order_detail`
--
ALTER TABLE `trx_purchase_order_detail`
  MODIFY `purchase_order_detail_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `trx_purchase_order_sample`
--
ALTER TABLE `trx_purchase_order_sample`
  MODIFY `purchase_order_sample_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
/*!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 */;




SELECT m.provider_id, m.provider_name, p.purchase_order_code,NULL as purchase_order_sample_code,p.provider_id  as provider_order
FROM mst_provider AS m
left JOIN trx_purchase_order AS p ON (m.provider_id = p.provider_id)
union

SELECT m.provider_id, m.provider_name, p.purchase_order_code,NULL as purchase_order_sample_code,p.provider_id  as provider_order
FROM mst_provider AS m
right JOIN trx_purchase_order AS p ON (m.provider_id = p.provider_id)
union

SELECT m.provider_id, m.provider_name,NULL,    s.purchase_order_sample_code ,s.provider_id
FROM mst_provider AS m

right JOIN trx_purchase_order_sample AS s ON (s.provider_id = m.provider_id);