· 8 years ago · May 21, 2018, 08:16 AM
1-- phpMyAdmin SQL Dump
2-- version 4.4.15.9
3-- https://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Generation Time: May 21, 2018 at 08:11 AM
7-- Server version: 5.6.37
8-- PHP Version: 5.6.31
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8mb4 */;
18
19--
20-- Database: `fb_demo`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `mst_provider`
27--
28
29CREATE TABLE IF NOT EXISTS `mst_provider` (
30 `provider_id` int(11) NOT NULL,
31 `provider_code` varchar(200) NOT NULL,
32 `provider_name` longtext NOT NULL,
33 `provider_description` longtext NOT NULL,
34 `provider_photo` varchar(200) NOT NULL,
35 `provider_contact_person` varchar(200) NOT NULL,
36 `provider_phone` varchar(100) NOT NULL,
37 `provider_phone2` varchar(100) NOT NULL,
38 `provider_fax` varchar(100) NOT NULL,
39 `provider_email` varchar(200) NOT NULL,
40 `provider_city` varchar(200) NOT NULL,
41 `provider_postal_code` varchar(100) NOT NULL,
42 `provider_address` text NOT NULL,
43 `provider_keyword` varchar(200) NOT NULL,
44 `provider_date_created` datetime NOT NULL,
45 `provider_last_updated` datetime NOT NULL,
46 `provider_log` longtext NOT NULL,
47 `provider_deposit` int(11) DEFAULT '0',
48 `provider_hutang` int(11) DEFAULT '0'
49) ENGINE=InnoDB AUTO_INCREMENT=690 DEFAULT CHARSET=utf8;
50
51--
52-- Dumping data for table `mst_provider`
53--
54
55INSERT 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
56(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),
57(689, 'AHLER', 'AHLER ', '', '', '', '', '', '', '', '', '', '', '', '2018-04-09 14:04:55', '2018-04-09 14:04:56', '', 0, 1486);
58
59-- --------------------------------------------------------
60
61--
62-- Table structure for table `trx_purchase_order`
63--
64
65CREATE TABLE IF NOT EXISTS `trx_purchase_order` (
66 `purchase_order_id` int(11) NOT NULL,
67 `provider_id` int(11) NOT NULL,
68 `sales_order_id` int(11) NOT NULL,
69 `purchase_order_categories` enum('sales','buffer') NOT NULL DEFAULT 'sales',
70 `purchase_order_code` varchar(200) NOT NULL,
71 `purchase_order_date` date NOT NULL,
72 `purchase_order_delivery_date` date NOT NULL,
73 `purchase_order_note` longtext NOT NULL,
74 `purchase_order_date_created` datetime NOT NULL,
75 `purchase_order_last_updated` datetime NOT NULL,
76 `purchase_order_log` longtext NOT NULL,
77 `purchase_order_deposit` int(11) DEFAULT '0'
78) ENGINE=InnoDB AUTO_INCREMENT=1544 DEFAULT CHARSET=utf8;
79
80--
81-- Dumping data for table `trx_purchase_order`
82--
83
84INSERT 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
85(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),
86(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);
87
88-- --------------------------------------------------------
89
90--
91-- Table structure for table `trx_purchase_order_detail`
92--
93
94CREATE TABLE IF NOT EXISTS `trx_purchase_order_detail` (
95 `purchase_order_detail_id` int(11) NOT NULL,
96 `purchase_order_id` int(11) NOT NULL,
97 `material_id` int(11) NOT NULL,
98 `product_id` int(11) NOT NULL,
99 `purchase_order_detail_qty` int(11) NOT NULL,
100 `purchase_order_detail_price` int(11) DEFAULT NULL,
101 `purchase_order_detail_desc` longtext NOT NULL,
102 `purchase_order_detail_remax` longtext,
103 `purchase_order_detail_date_created` datetime NOT NULL,
104 `purchase_order_detail_last_updated` datetime NOT NULL,
105 `purchase_order_detail_log` longtext NOT NULL
106) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
107
108--
109-- Dumping data for table `trx_purchase_order_detail`
110--
111
112INSERT 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
113(1, 1542, 1493, 1200, 10, 2150000, 'magohany', '', '2018-04-30 00:00:00', '2018-04-30 00:00:00', 'insert by dwi'),
114(2, 1543, 2026, 1296, 10, 80, 'test', 'test', '2018-05-11 00:00:00', '2018-05-11 00:00:00', 'insert by dwi');
115
116-- --------------------------------------------------------
117
118--
119-- Table structure for table `trx_purchase_order_sample`
120--
121
122CREATE TABLE IF NOT EXISTS `trx_purchase_order_sample` (
123 `purchase_order_sample_id` int(11) NOT NULL,
124 `purchase_order_sample_code` varchar(11) NOT NULL DEFAULT '0',
125 `provider_id` int(11) DEFAULT NULL,
126 `product_id` int(11) DEFAULT NULL,
127 `material_id` int(11) DEFAULT NULL,
128 `id_induk` int(11) DEFAULT '0',
129 `qty` int(11) DEFAULT NULL,
130 `price` int(11) DEFAULT NULL,
131 `desc` text,
132 `remax` text,
133 `delivery_date` date DEFAULT NULL,
134 `date` date DEFAULT NULL
135) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
136
137--
138-- Dumping data for table `trx_purchase_order_sample`
139--
140
141INSERT 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
142(1, 'VV051218sam', 52, 1628, 1628, 0, 1, 700000, 'sampel', '', '2018-05-12', '2018-06-11'),
143(2, 'MAR051618', 51, 1628, 1628, 0, 1, 850000, '', '', '2018-06-15', '2018-05-16'),
144(3, 'DLJ051618', 55, 1635, 1635, 0, 1, 1800000, '', '', '2018-06-15', '2018-05-16');
145
146--
147-- Indexes for dumped tables
148--
149
150--
151-- Indexes for table `mst_provider`
152--
153ALTER TABLE `mst_provider`
154 ADD PRIMARY KEY (`provider_id`);
155
156--
157-- Indexes for table `trx_purchase_order`
158--
159ALTER TABLE `trx_purchase_order`
160 ADD PRIMARY KEY (`purchase_order_id`),
161 ADD KEY `provider_id` (`provider_id`);
162
163--
164-- Indexes for table `trx_purchase_order_detail`
165--
166ALTER TABLE `trx_purchase_order_detail`
167 ADD PRIMARY KEY (`purchase_order_detail_id`),
168 ADD KEY `purchase_order_id` (`purchase_order_id`),
169 ADD KEY `material_id` (`material_id`),
170 ADD KEY `product_id` (`product_id`);
171
172--
173-- Indexes for table `trx_purchase_order_sample`
174--
175ALTER TABLE `trx_purchase_order_sample`
176 ADD PRIMARY KEY (`purchase_order_sample_id`),
177 ADD KEY `FK_trx_purchase_order_sample_mst_provider` (`provider_id`);
178
179--
180-- AUTO_INCREMENT for dumped tables
181--
182
183--
184-- AUTO_INCREMENT for table `mst_provider`
185--
186ALTER TABLE `mst_provider`
187 MODIFY `provider_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=690;
188--
189-- AUTO_INCREMENT for table `trx_purchase_order`
190--
191ALTER TABLE `trx_purchase_order`
192 MODIFY `purchase_order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1544;
193--
194-- AUTO_INCREMENT for table `trx_purchase_order_detail`
195--
196ALTER TABLE `trx_purchase_order_detail`
197 MODIFY `purchase_order_detail_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
198--
199-- AUTO_INCREMENT for table `trx_purchase_order_sample`
200--
201ALTER TABLE `trx_purchase_order_sample`
202 MODIFY `purchase_order_sample_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
203/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
204/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
205/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
206
207
208
209
210SELECT m.provider_id, m.provider_name, p.purchase_order_code,NULL as purchase_order_sample_code,p.provider_id as provider_order
211FROM mst_provider AS m
212left JOIN trx_purchase_order AS p ON (m.provider_id = p.provider_id)
213union
214
215SELECT m.provider_id, m.provider_name, p.purchase_order_code,NULL as purchase_order_sample_code,p.provider_id as provider_order
216FROM mst_provider AS m
217right JOIN trx_purchase_order AS p ON (m.provider_id = p.provider_id)
218union
219
220SELECT m.provider_id, m.provider_name,NULL, s.purchase_order_sample_code ,s.provider_id
221FROM mst_provider AS m
222
223right JOIN trx_purchase_order_sample AS s ON (s.provider_id = m.provider_id);