· 6 years ago · Oct 24, 2019, 09:18 AM
1SELECT *
2FROM users
3
4SELECT *
5FROM schedule_own
6
7SELECT *
8FROM task_comment
9INNER JOIN schedule_own_task
10ON task_comment.task_id = schedule_own_task.id
11INNER JOIN schedule_own
12ON schedule_own_task.schedule_own_id = schedule_own.id
13
14SELECT *
15FROM task_comment
16INNER JOIN users
17ON users.username
18LIKE CONCAT('%', task_comment.username, '%')
19
20SELECT *
21FROM task_comment
22WHERE username like '%johndoe%'
23
24SELECT *
25FROM task_comment
26WHERE username like '%superadmin%'
27
28DELETE
29FROM task_comment
30WHERE username like '%superadmin%'
31
32SELECT *
33FROM schedule_own_task
34
35SELECT *
36FROM schedule_own_detail
37
38SELECT *
39FROM schedule_own_history
40
41SELECT *
42FROM schedule_own
43
44DELETE *
45FROM schedule_own
46WHERE id = 3
47
48SELECT *
49FROM schedule_own
50
51SELECT *
52FROM users
53
54SELECT *
55FROM master_role
56
57SELECT *
58FROM master_location
59
60SELECT *
61FROM location_type
62
63SELECT *
64FROM json_form_task
65
66SELECT *
67FROM schedule_own_task
68
69SELECT *
70FROM account_list
71
72CREATE TABLE account_list (
73 id int NOT NULL AUTO_INCREMENT,
74 bp_code varchar(255),
75 account_name varchar(255),
76 outlet_type int,
77 sub_outlet int,
78 address varchar(255),
79 city varchar(255),
80 phone varchar(255),
81 province varchar(255),
82 credit_limit int,
83 router_delivery varchar(255),
84 router_call varchar(255),
85 owner_name varchar(255),
86 owner_ktp varchar(255),
87 birth_date datetime,
88 owner_religion varchar(255),
89 owner_phone varchar(255),
90 bde varchar(255),
91 smpg varchar(255),
92 sa varchar(255),
93 PRIMARY KEY (id)
94)
95
96CREATE TABLE division_personel (
97 id int NOT NULL AUTO_INCREMENT,
98 division_id int(11),
99 personel_id int(11),
100 PRIMARY KEY (id)
101)
102
103CREATE TABLE user_division_personel (
104 id int NOT NULL AUTO_INCREMENT,
105 division_id int(11),
106 personel_id int(11),
107 user_id int(11),
108 PRIMARY KEY (id)
109)
110
111ALTER TABLE master_personnel DROP division_id
112
113SELECT *
114FROM master_division
115
116SELECT *
117FROM master_role
118
119SELECT *
120FROM master_personnel
121
122SELECT *
123FROM master_division
124
125ALTER TABLE users CHANGE `urole` `udivision` int(5)
126
127SELECT *
128FROM role_schedule
129
130SELECT *
131FROM division_personel
132INNER JOIN master_personnel
133ON division_personel.personel_id = master_personnel.id
134WHERE division_id = 40
135
136SELECT *
137FROM user_division_personnel
138
139SELECT *
140FROM division_personnel
141
142SELECT *
143FROM users
144
145SELECT *
146FROM master_role_v2
147
148CREATE TABLE master_role_v2 (
149 id int(11) NOT NULL AUTO_INCREMENT,
150 name varchar(50),
151 description varchar(255),
152 idelete int(1) default 0,
153 PRIMARY KEY (id)
154)
155
156DROP TABLE master_role_v2
157
158SELECT *
159FROM personnel_role
160
161SELECT *
162FROM master_shop
163
164SELECT *
165FROM shop_meta_address
166
167SELECT *
168FROM shop_meta_phone
169
170SELECT *
171FROM master_shop as ms
172INNER JOIN shop_meta_address as sma
173ON sma.shop_id = ms.id
174INNER JOIN shop_meta_phone as smp
175ON smp.shop_id = ms.id
176WHERE ms.id = 15
177
178SELECT *
179FROM users
180
181CREATE TABLE shop_type (
182 id int(11) NOT NULL AUTO_INCREMENT,
183 shop_id int(11),
184 shop_type_id int(11),
185 PRIMARY KEY (id)
186)
187
188CREATE TABLE master_shop_type (
189 id int(11) NOT NULL AUTO_INCREMENT,
190 name varchar(50),
191 idelete int(1) default 0,
192 PRIMARY KEY (id)
193)
194
195CREATE TABLE master_shop_stock (
196 id int(11) NOT NULL AUTO_INCREMENT,
197 shop_id int(11),
198 stock int(11),
199 PRIMARY KEY (id)
200)
201
202CREATE TABLE master_stock_type (
203 id int(11) NOT NULL AUTO_INCREMENT,
204 name varchar(50),
205 PRIMARY KEY (id)
206)
207
208DROP TABLE master_stock_type
209
210SELECT *
211FROM shop_type
212
213SELECT *
214FROM master_stock_type
215
216SELECT *
217FROM master_shop_type
218
219SELECT *
220FROM master_shop_stock
221
222SELECT *
223FROM assigned_pm
224
225SELECT *
226FROM master_shop
227
228SELECT *
229FROM confirmation
230
231ALTER TABLE confirmation
232ADD status int(1)
233
234DROP TABLE confirmation
235
236ALTER TABLE master_shop
237ADD stock int(11)
238
239ALTER TABLE master_shop
240ADD store_type int(1)
241
242ALTER TABLE master_shop
243ADD stock_type_id int(2)
244
245CREATE TABLE confirmation (
246 id int(11) NOT NULL AUTO_INCREMENT,
247 shop_id int(11),
248 stock int(11),
249 stock_type_id int(11),
250 audio text,
251 PRIMARY KEY (id)
252)
253
254CREATE TABLE created_po (
255 id int(11) NOT NULL AUTO_INCREMENT,
256 shop_id int(11),
257 product_id int(11),
258 total int(11),
259 created_date datetime,
260 audio text,
261 PRIMARY KEY (id)
262)
263
264CREATE TABLE master_product (
265 id int(11) NOT NULL AUTO_INCREMENT,
266 name varchar(255),
267 price int(11),
268 PRIMARY KEY (id)
269)
270
271CREATE TABLE master_payment_method (
272 id int(11) NOT NULL AUTO_INCREMENT,
273 name varchar(255),
274 PRIMARY KEY (id)
275)
276
277ALTER TABLE created_po
278ADD payment_method int(1)
279
280SELECT *
281FROM master_product
282
283DESCRIBE master_product
284
285SELECT *
286FROM created_po
287
288SELECT *
289FROM master_shop
290
291SELECT *
292FROM created_gt03
293
294SELECT *
295FROM master_payment_method
296
297CREATE TABLE created_gt03 (
298 id int(11) NOT NULL AUTO_INCREMENT,
299 shop_id int(11),
300 product_id int(11),
301 total int(11),
302 po_date datetime,
303 shop_type int(11),
304 audio text,
305 PRIMARY KEY (id)
306)
307
308CREATE TABLE cc_gt_r01 (
309 id int(11) NOT NULL AUTO_INCREMENT,
310 name varchar(50),
311 description varchar(255),
312 stock int(11),
313 stock_type_id int(2),
314 shop_type int(1),
315 idelete int(1) default 0,
316 PRIMARY KEY (id)
317)
318
319CREATE TABLE cc_gt_r02 (
320 id int(11) NOT NULL AUTO_INCREMENT,
321 name varchar(50),
322 description varchar(255),
323 stock int(11),
324 stock_type_id int(2),
325 shop_type int(1),
326 idelete int(1) default 0,
327 PRIMARY KEY (id)
328)
329
330DROP TABLE cc_gt_r01
331
332SELECT *
333FROM cc_gt_r01
334
335SELECT *
336FROM cc_gt_r02
337
338ALTER TABLE cc_gt_r02
339ADD shop_id int(11)
340
341SELECT *
342FROM master_shop
343
344ALTER TABLE master_shop
345Change store_type shop_type int(1)
346
347SELECT *
348FROM confirmation
349
350SELECT *
351FROM created_po
352
353SELECT *
354FROM created_gt03
355
356SELECT *
357FROM schedule_own
358
359SELECT *
360FROM schedule_own_history
361
362CREATE TABLE po_hdr (
363 id int(11) NOT NULL AUTO_INCREMENT,
364 no_po varchar(25),
365 shop_id int(11),
366 created_date datetime,
367 update_date datetime,
368 audio text,
369 sub_total double,
370 total double,
371 discount int(11),
372 discount_value double,
373 approved TINYINT,
374 user_approved varchar(25),
375 date_approved datetime,
376 payment_method TINYINT,
377 status TINYINT,
378 user_role int(11),
379 PRIMARY KEY (id)
380)
381
382SELECT *
383FROM po_hdr
384
385DESCRIBE po_hdr
386
387ALTER TABLE po_hdr
388ADD no_tx int(11)
389
390CREATE TABLE po_dtl (
391 id int(11) NOT NULL AUTO_INCREMENT,
392 no_po varchar(25),
393 product_id int(11),
394 product_name varchar(25),
395 unit TINYINT,
396 qty double,
397 price double,
398 total_price double,
399 remark text,
400 created_date datetime,
401 updated_date datetime,
402 PRIMARY KEY (id)
403)
404
405CREATE TABLE confirmation_hdr (
406 id int(11) NOT NULL AUTO_INCREMENT,
407 no_tx varchar(25),
408 stock_type int(11),
409 shop_id int(11),
410 created_date datetime,
411 updated_date datetime,
412 audio text,
413 sub_total double,
414 total double,
415 discount int(11),
416 discount_value double,
417 approved TINYINT,
418 user_approved varchar(25),
419 date_approved datetime,
420 payment_method TINYINT,
421 status TINYINT,
422 user_role int(11),
423 shop_type int(11),
424 created_by int(11),
425 task_id int(11),
426 PRIMARY KEY (id)
427)
428
429CREATE TABLE confirmation_dtl (
430 id int(11) NOT NULL AUTO_INCREMENT,
431 no_tx varchar(25),
432 product_id int(11),
433 product_name varchar(25),
434 unit TINYINT,
435 qty double,
436 price double,
437 total_price double,
438 remark text,
439 created_date datetime,
440 updated_date datetime,
441 stock_type int(11),
442 PRIMARY KEY (id)
443)
444
445SELECT *
446FROM confirmation_hdr
447
448SELECT *
449FROM confirmation_dtl
450
451SELECT *
452FROM po_hdr
453
454SELECT *
455FROM master_shop
456
457SELECT *
458FROM po_dtl
459
460ALTER TABLE confirmation_hdr
461ADD task_id int(11)
462
463ALTER TABLE confirmation_hdr
464ADD created_by int(11)
465
466ALTER TABLE po_hdr
467ADD task_id int(11)
468
469DROP TABLE confirmation_hdr
470
471CREATE TABLE assigned_pm (
472 id int(11) NOT NULL AUTO_INCREMENT,
473 user_id int(11),
474 no_tx varchar(25),
475 type int(1),
476 created_date datetime,
477 updated_date datetime,
478 status int(1),
479 idelete int(1) default 0,
480 PRIMARY KEY (id)
481)
482
483SELECT *
484FROM assigned_pm
485
486DROP TABLE assigned_pm
487
488ALTER TABLE assigned_pm
489MODIFY no_tx varchar(25)
490
491SELECT *
492FROM users
493
494SELECT MAX(no_po)
495FROM po_hdr
496WHERE no_po LIKE '%20190903%'
497ORDER BY no_po DESC
498
499ALTER TABLE po_hdr
500MODIFY no_tx varchar(20)
501
502SELECT *
503FROM cc_gt_r01
504
505SELECT *
506FROM master_division
507
508SELECT *
509FROM master_role_v2
510
511SELECT *
512FROM visits
513
514SELECT *
515FROM tasks
516
517ALTER TABLE tasks
518MODIFY status int(11)
519
520ALTER TABLE tasks
521ADD shop_id int(11)
522
523SELECT *
524FROM master_role_v2
525
526SELECT *
527FROM users
528
529SELECT *
530FROM confirmation_hdr
531INNER JOIN tasks ON tasks.id = confirmation_hdr.task_id
532
533SELECT *
534FROM master_role_v2
535
536SELECT *
537FROM master_division
538
539SELECT *
540FROM master_personnel
541
542SELECT *
543FROM user_division_personnel
544
545CREATE TABLE collector_meta (
546 id int(11) NOT NULL AUTO_INCREMENT,
547 task_id int(11),
548 created_by int(11),
549 shop_id int(11),
550 picture text,
551 remark varchar(25),
552 created_date datetime,
553 updated_date datetime,
554 qa_tandatrima TINYINT(2) default 0,
555 qa_ivc TINYINT(2) default 0,
556 qa_hitung TINYINT(2) default 0,
557 qa_check_uv TINYINT(2) default 0,
558 desc_gagalcollect varchar(512),
559 desc_tundasetor varchar(512),
560 desc_lainlain varchar(512),
561 PRIMARY KEY (id)
562) ENGINE=MyISAM DEFAULT CHARSET=latin1
563
564CREATE TABLE collector_meta_picture (
565 id int(11) NOT NULL AUTO_INCREMENT,
566 collector_meta_id int(11),
567 name text,
568 PRIMARY KEY (id)
569) ENGINE=MyISAM DEFAULT CHARSET=latin1
570
571CREATE TABLE collector_meta_picture2 (
572 id int(11) NOT NULL AUTO_INCREMENT,
573 collector_meta_id int(11),
574 name text,
575 PRIMARY KEY (id)
576) ENGINE=MyISAM DEFAULT CHARSET=latin1
577
578CREATE TABLE collector_meta_picture3 (
579 id int(11) NOT NULL AUTO_INCREMENT,
580 collector_meta_id int(11),
581 name text,
582 PRIMARY KEY (id)
583) ENGINE=MyISAM DEFAULT CHARSET=latin1
584
585CREATE TABLE driver_meta (
586 id int(11) NOT NULL AUTO_INCREMENT,
587 task_id int(11),
588 created_by int(11),
589 shop_id int(11),
590 picture text,
591 remark varchar(25),
592 created_date datetime,
593 updated_date datetime,
594 qa_suratjalan TINYINT(2) default 0,
595 desc_suratjalan varchar(512),
596 file_suratjalan_tt text,
597 qa_retur TINYINT(2) default 0,
598 desc_retur varchar(512),
599 file_retur text,
600 PRIMARY KEY (id)
601) ENGINE=MyISAM DEFAULT CHARSET=latin1
602
603SELECT *
604FROM collector_meta
605
606SELECT *
607FROM collector_meta_picture2
608
609SELECT *
610FROM driver_meta
611
612DROP TABLE collector_meta
613DROP TABLE driver_meta
614
615SELECT *
616FROM discussions
617ORDER BY created_date ASC
618
619SELECT *
620FROM alerts
621
622SELECT a.name, b.name
623FROM discussions
624INNER JOIN users a ON a.id = discussions.recipient_id
625INNER JOIN users b ON b.id = discussions.sender_id
626WHERE discussions.id = 1
627
628SELECT *
629FROM users
630
631SELECT *
632FROM user_division_personnel
633
634SELECT *
635FROM master_personnel
636
637SELECT *
638FROM master_role_v2
639
640SELECT *
641FROM po_dtl
642ORDER BY created_date DESC
643
644CREATE TABLE `driver_req_log` (
645 `id` int(11) NOT NULL AUTO_INCREMENT,
646 `user_id` int(11) DEFAULT NULL,
647 `username` varchar(30) DEFAULT NULL,
648 `tx_date` datetime DEFAULT NULL,
649 `payload` varchar(1024) DEFAULT NULL,
650 PRIMARY KEY (`id`)
651) ENGINE=MyISAM DEFAULT CHARSET=latin1
652
653DROP TABLE driver_req_log
654
655SELECT *
656FROM driver_req_log
657
658SELECT *
659FROM tasks
660ORDER BY id
661
662ALTER TABLE tasks
663ADD b_fwp TINYINT(2)
664
665CREATE TABLE `callcenter_meta` (
666 `id` int(11) NOT NULL AUTO_INCREMENT,
667 `task_id` int(11),
668 `qa_problem` TINYINT(2) default 0,
669 `qa_greets` TINYINT(2) default 0,
670 `qa_resched` TINYINT(2) default 0,
671 `desc_problem` varchar(512),
672 `desc_resched` datetime,
673 PRIMARY KEY (`id`)
674) ENGINE=MyISAM DEFAULT CHARSET=latin1
675
676SELECT *
677FROM callcenter_meta
678
679SELECT *
680FROM user_division_personnel
681
682SELECT *
683FROM master_personnel
684
685SELECT *
686FROM users
687
688SELECT *
689FROM master_role_v2
690
691SELECT *
692FROM master_division
693
694SELECT *
695FROM division_personnel
696
697SELECT *
698FROM personnel_role
699
700SELECT *
701FROM users
702INNER JOIN user_division_personnel udp ON udp.user_id = users.id
703INNER JOIN personnel_role pr ON pr.personnel_id = udp.personnel_id
704INNER JOIN master_role_v2 mr ON mr.id = pr.role_id
705WHERE users.id = 16
706
707SELECT *
708FROM menu
709
710CREATE TABLE `noo_meta` (
711 `id` int(11) NOT NULL AUTO_INCREMENT,
712 `task_id` int(11),
713 `remark` varchar(25),
714 `created_date` datetime,
715 `created_by` int(11),
716 PRIMARY KEY (`id`)
717) ENGINE=MyISAM DEFAULT CHARSET=latin1
718
719SELECT *
720FROM noo_meta
721
722DROP TABLE noo_meta
723
724SELECT *
725FROM noo_file3
726
727DROP TABLE noo_file3
728
729CREATE TABLE noo_file (
730 id int(11) NOT NULL AUTO_INCREMENT,
731 noo_id int(11),
732 name text,
733 PRIMARY KEY (id)
734) ENGINE=MyISAM DEFAULT CHARSET=latin1
735
736CREATE TABLE noo_file2 (
737 id int(11) NOT NULL AUTO_INCREMENT,
738 noo_id int(11),
739 name text,
740 PRIMARY KEY (id)
741) ENGINE=MyISAM DEFAULT CHARSET=latin1
742
743CREATE TABLE noo_file3 (
744 id int(11) NOT NULL AUTO_INCREMENT,
745 noo_id int(11),
746 name text,
747 PRIMARY KEY (id)
748) ENGINE=MyISAM DEFAULT CHARSET=latin1
749
750CREATE TABLE `tailor_meta` (
751 `id` int(11) NOT NULL AUTO_INCREMENT,
752 `task_id` int(11),
753 `remark` varchar(25),
754 `created_date` datetime,
755 `created_by` int(11),
756 PRIMARY KEY (`id`)
757) ENGINE=MyISAM DEFAULT CHARSET=latin1
758
759SELECT *
760FROM tailor_meta
761
762DROP TABLE tailor_meta
763
764DROP TABLE tailor_file3
765
766CREATE TABLE tailor_file (
767 id int(11) NOT NULL AUTO_INCREMENT,
768 tailor_id int(11),
769 name text,
770 PRIMARY KEY (id)
771) ENGINE=MyISAM DEFAULT CHARSET=latin1
772
773CREATE TABLE tailor_file2 (
774 id int(11) NOT NULL AUTO_INCREMENT,
775 tailor_id int(11),
776 name text,
777 PRIMARY KEY (id)
778) ENGINE=MyISAM DEFAULT CHARSET=latin1
779
780CREATE TABLE tailor_file3 (
781 id int(11) NOT NULL AUTO_INCREMENT,
782 tailor_id int(11),
783 name text,
784 PRIMARY KEY (id)
785) ENGINE=MyISAM DEFAULT CHARSET=latin1
786
787CREATE TABLE `man_power_meta` (
788 `id` int(11) NOT NULL AUTO_INCREMENT,
789 `task_id` int(11),
790 `remark` varchar(25),
791 `created_date` datetime,
792 `created_by` int(11),
793 PRIMARY KEY (`id`)
794) ENGINE=MyISAM DEFAULT CHARSET=latin1
795
796SELECT *
797FROM man_power_meta
798
799DROP TABLE man_power_meta
800
801SELECT *
802FROM man_power_file3
803
804DROP TABLE man_power_file3
805
806CREATE TABLE man_power_file (
807 id int(11) NOT NULL AUTO_INCREMENT,
808 man_power_id int(11),
809 name text,
810 PRIMARY KEY (id)
811) ENGINE=MyISAM DEFAULT CHARSET=latin1
812
813CREATE TABLE man_power_file2 (
814 id int(11) NOT NULL AUTO_INCREMENT,
815 man_power_id int(11),
816 name text,
817 PRIMARY KEY (id)
818) ENGINE=MyISAM DEFAULT CHARSET=latin1
819
820CREATE TABLE man_power_file3 (
821 id int(11) NOT NULL AUTO_INCREMENT,
822 man_power_id int(11),
823 name text,
824 PRIMARY KEY (id)
825) ENGINE=MyISAM DEFAULT CHARSET=latin1
826
827CREATE TABLE `join_visit_meta` (
828 `id` int(11) NOT NULL AUTO_INCREMENT,
829 `task_id` int(11),
830 `remark` varchar(25),
831 `created_date` datetime,
832 `created_by` int(11),
833 PRIMARY KEY (`id`)
834) ENGINE=MyISAM DEFAULT CHARSET=latin1
835
836SELECT *
837FROM join_visit_meta
838
839DROP TABLE join_visit_meta
840
841SELECT *
842FROM join_visit_file2
843
844DROP TABLE join_visit_file3
845
846CREATE TABLE join_visit_file (
847 id int(11) NOT NULL AUTO_INCREMENT,
848 join_visit_id int(11),
849 name text,
850 PRIMARY KEY (id)
851) ENGINE=MyISAM DEFAULT CHARSET=latin1
852
853CREATE TABLE join_visit_file2 (
854 id int(11) NOT NULL AUTO_INCREMENT,
855 join_visit_id int(11),
856 name text,
857 PRIMARY KEY (id)
858) ENGINE=MyISAM DEFAULT CHARSET=latin1
859
860CREATE TABLE join_visit_file3 (
861 id int(11) NOT NULL AUTO_INCREMENT,
862 join_visit_id int(11),
863 name text,
864 PRIMARY KEY (id)
865) ENGINE=MyISAM DEFAULT CHARSET=latin1
866
867CREATE TABLE `tele_coaching_meta` (
868 `id` int(11) NOT NULL AUTO_INCREMENT,
869 `task_id` int(11),
870 `remark` varchar(25),
871 `created_date` datetime,
872 `created_by` int(11),
873 PRIMARY KEY (`id`)
874) ENGINE=MyISAM DEFAULT CHARSET=latin1
875
876SELECT *
877FROM tele_coaching_meta
878
879DROP TABLE tele_coaching_meta
880
881SELECT *
882FROM tele_coaching_file
883
884DROP TABLE tele_coaching_file3
885
886CREATE TABLE tele_coaching_file (
887 id int(11) NOT NULL AUTO_INCREMENT,
888 tele_coaching_id int(11),
889 name text,
890 PRIMARY KEY (id)
891) ENGINE=MyISAM DEFAULT CHARSET=latin1
892
893CREATE TABLE tele_coaching_file2 (
894 id int(11) NOT NULL AUTO_INCREMENT,
895 tele_coaching_id int(11),
896 name text,
897 PRIMARY KEY (id)
898) ENGINE=MyISAM DEFAULT CHARSET=latin1
899
900CREATE TABLE tele_coaching_file3 (
901 id int(11) NOT NULL AUTO_INCREMENT,
902 tele_coaching_id int(11),
903 name text,
904 PRIMARY KEY (id)
905) ENGINE=MyISAM DEFAULT CHARSET=latin1
906
907CREATE TABLE `hiring_man_meta` (
908 `id` int(11) NOT NULL AUTO_INCREMENT,
909 `task_id` int(11),
910 `remark` varchar(25),
911 `created_date` datetime,
912 `created_by` int(11),
913 PRIMARY KEY (`id`)
914) ENGINE=MyISAM DEFAULT CHARSET=latin1
915
916SELECT *
917FROM hiring_man_meta
918
919DROP TABLE hiring_man_meta
920
921SELECT *
922FROM hiring_man_file2
923
924DROP TABLE hiring_man_file3
925
926CREATE TABLE hiring_man_file (
927 id int(11) NOT NULL AUTO_INCREMENT,
928 hiring_man_id int(11),
929 name text,
930 PRIMARY KEY (id)
931) ENGINE=MyISAM DEFAULT CHARSET=latin1
932
933CREATE TABLE hiring_man_file2 (
934 id int(11) NOT NULL AUTO_INCREMENT,
935 hiring_man_id int(11),
936 name text,
937 PRIMARY KEY (id)
938) ENGINE=MyISAM DEFAULT CHARSET=latin1
939
940CREATE TABLE hiring_man_file3 (
941 id int(11) NOT NULL AUTO_INCREMENT,
942 hiring_man_id int(11),
943 name text,
944 PRIMARY KEY (id)
945) ENGINE=MyISAM DEFAULT CHARSET=latin1
946
947CREATE TABLE `join_visit_rm_meta` (
948 `id` int(11) NOT NULL AUTO_INCREMENT,
949 `task_id` int(11),
950 `remark` varchar(25),
951 `created_date` datetime,
952 `created_by` int(11),
953 PRIMARY KEY (`id`)
954) ENGINE=MyISAM DEFAULT CHARSET=latin1
955
956SELECT *
957FROM join_visit_rm_meta
958
959DROP TABLE join_visit_rm_meta
960
961SELECT *
962FROM join_visit_rm_file3
963
964DROP TABLE join_visit_rm_file3
965
966CREATE TABLE join_visit_rm_file (
967 id int(11) NOT NULL AUTO_INCREMENT,
968 join_visit_rm_id int(11),
969 name text,
970 PRIMARY KEY (id)
971) ENGINE=MyISAM DEFAULT CHARSET=latin1
972
973CREATE TABLE join_visit_rm_file2 (
974 id int(11) NOT NULL AUTO_INCREMENT,
975 join_visit_rm_id int(11),
976 name text,
977 PRIMARY KEY (id)
978) ENGINE=MyISAM DEFAULT CHARSET=latin1
979
980CREATE TABLE join_visit_rm_file3 (
981 id int(11) NOT NULL AUTO_INCREMENT,
982 join_visit_rm_id int(11),
983 name text,
984 PRIMARY KEY (id)
985) ENGINE=MyISAM DEFAULT CHARSET=latin1
986
987CREATE TABLE `temporary_vacant_meta` (
988 `id` int(11) NOT NULL AUTO_INCREMENT,
989 `task_id` int(11),
990 `remark` varchar(25),
991 `created_date` datetime,
992 `created_by` int(11),
993 PRIMARY KEY (`id`)
994) ENGINE=MyISAM DEFAULT CHARSET=latin1
995
996SELECT *
997FROM temporary_vacant_meta
998
999DROP TABLE temporary_vacant_meta
1000
1001SELECT *
1002FROM temporary_vacant_file2
1003
1004DROP TABLE temporary_vacant_file
1005
1006CREATE TABLE temporary_vacant_file (
1007 id int(11) NOT NULL AUTO_INCREMENT,
1008 temporary_vacant_id int(11),
1009 name text,
1010 PRIMARY KEY (id)
1011) ENGINE=MyISAM DEFAULT CHARSET=latin1
1012
1013CREATE TABLE temporary_vacant_file2 (
1014 id int(11) NOT NULL AUTO_INCREMENT,
1015 temporary_vacant_id int(11),
1016 name text,
1017 PRIMARY KEY (id)
1018) ENGINE=MyISAM DEFAULT CHARSET=latin1
1019
1020CREATE TABLE temporary_vacant_file3 (
1021 id int(11) NOT NULL AUTO_INCREMENT,
1022 temporary_vacant_id int(11),
1023 name text,
1024 PRIMARY KEY (id)
1025) ENGINE=MyISAM DEFAULT CHARSET=latin1
1026
1027CREATE TABLE `kunjungan_so_meta` (
1028 `id` int(11) NOT NULL AUTO_INCREMENT,
1029 `task_id` int(11),
1030 `remark` varchar(25),
1031 `created_date` datetime,
1032 `created_by` int(11),
1033 PRIMARY KEY (`id`)
1034) ENGINE=MyISAM DEFAULT CHARSET=latin1
1035
1036SELECT *
1037FROM kunjungan_so_meta
1038
1039DROP TABLE kunjungan_so_meta
1040
1041SELECT *
1042FROM kunjungan_so_file3
1043
1044DROP TABLE kunjungan_so_file
1045
1046CREATE TABLE kunjungan_so_file (
1047 id int(11) NOT NULL AUTO_INCREMENT,
1048 kunjungan_so_id int(11),
1049 name text,
1050 PRIMARY KEY (id)
1051) ENGINE=MyISAM DEFAULT CHARSET=latin1
1052
1053CREATE TABLE kunjungan_so_file2 (
1054 id int(11) NOT NULL AUTO_INCREMENT,
1055 kunjungan_so_id int(11),
1056 name text,
1057 PRIMARY KEY (id)
1058) ENGINE=MyISAM DEFAULT CHARSET=latin1
1059
1060CREATE TABLE kunjungan_so_file3 (
1061 id int(11) NOT NULL AUTO_INCREMENT,
1062 kunjungan_so_id int(11),
1063 name text,
1064 PRIMARY KEY (id)
1065) ENGINE=MyISAM DEFAULT CHARSET=latin1
1066
1067CREATE TABLE `customer_profile_meta` (
1068 `id` int(11) NOT NULL AUTO_INCREMENT,
1069 `task_id` int(11),
1070 `remark` varchar(25),
1071 `created_date` datetime,
1072 `created_by` int(11),
1073 PRIMARY KEY (`id`)
1074) ENGINE=MyISAM DEFAULT CHARSET=latin1
1075
1076SELECT *
1077FROM customer_profile_meta
1078
1079DROP TABLE customer_profile_meta
1080
1081SELECT *
1082FROM customer_profile_file
1083
1084DROP TABLE customer_profile_file3
1085
1086CREATE TABLE customer_profile_file (
1087 id int(11) NOT NULL AUTO_INCREMENT,
1088 customer_profile_id int(11),
1089 name text,
1090 PRIMARY KEY (id)
1091) ENGINE=MyISAM DEFAULT CHARSET=latin1
1092
1093CREATE TABLE customer_profile_file2 (
1094 id int(11) NOT NULL AUTO_INCREMENT,
1095 customer_profile_id int(11),
1096 name text,
1097 PRIMARY KEY (id)
1098) ENGINE=MyISAM DEFAULT CHARSET=latin1
1099
1100CREATE TABLE customer_profile_file3 (
1101 id int(11) NOT NULL AUTO_INCREMENT,
1102 customer_profile_id int(11),
1103 name text,
1104 PRIMARY KEY (id)
1105) ENGINE=MyISAM DEFAULT CHARSET=latin1
1106
1107CREATE TABLE `kunjungan_so_motorist_meta` (
1108 `id` int(11) NOT NULL AUTO_INCREMENT,
1109 `task_id` int(11),
1110 `remark` varchar(25),
1111 `created_date` datetime,
1112 `created_by` int(11),
1113 PRIMARY KEY (`id`)
1114) ENGINE=MyISAM DEFAULT CHARSET=latin1
1115
1116SELECT *
1117FROM kunjungan_so_motorist_meta
1118
1119DROP TABLE kunjungan_so_motorist_meta
1120
1121SELECT *
1122FROM kunjungan_so_motorist_file2
1123
1124DROP TABLE kunjungan_so_motorist_file3
1125
1126CREATE TABLE kunjungan_so_motorist_file (
1127 id int(11) NOT NULL AUTO_INCREMENT,
1128 kunjungan_so_id int(11),
1129 name text,
1130 PRIMARY KEY (id)
1131) ENGINE=MyISAM DEFAULT CHARSET=latin1
1132
1133CREATE TABLE kunjungan_so_motorist_file2 (
1134 id int(11) NOT NULL AUTO_INCREMENT,
1135 kunjungan_so_id int(11),
1136 name text,
1137 PRIMARY KEY (id)
1138) ENGINE=MyISAM DEFAULT CHARSET=latin1
1139
1140CREATE TABLE kunjungan_so_motorist_file3 (
1141 id int(11) NOT NULL AUTO_INCREMENT,
1142 kunjungan_so_id int(11),
1143 name text,
1144 PRIMARY KEY (id)
1145) ENGINE=MyISAM DEFAULT CHARSET=latin1
1146
1147CREATE TABLE `customer_profile_motorist_meta` (
1148 `id` int(11) NOT NULL AUTO_INCREMENT,
1149 `task_id` int(11),
1150 `remark` varchar(25),
1151 `created_date` datetime,
1152 `created_by` int(11),
1153 PRIMARY KEY (`id`)
1154) ENGINE=MyISAM DEFAULT CHARSET=latin1
1155
1156SELECT *
1157FROM customer_profile_motorist_meta
1158
1159DROP TABLE customer_profile_motorist_meta
1160
1161SELECT *
1162FROM customer_profile_motorist_file2
1163
1164DROP TABLE customer_profile_motorist_file3
1165
1166CREATE TABLE customer_profile_motorist_file (
1167 id int(11) NOT NULL AUTO_INCREMENT,
1168 customer_profile_id int(11),
1169 name text,
1170 PRIMARY KEY (id)
1171) ENGINE=MyISAM DEFAULT CHARSET=latin1
1172
1173CREATE TABLE customer_profile_motorist_file2 (
1174 id int(11) NOT NULL AUTO_INCREMENT,
1175 customer_profile_id int(11),
1176 name text,
1177 PRIMARY KEY (id)
1178) ENGINE=MyISAM DEFAULT CHARSET=latin1
1179
1180CREATE TABLE customer_profile_motorist_file3 (
1181 id int(11) NOT NULL AUTO_INCREMENT,
1182 customer_profile_id int(11),
1183 name text,
1184 PRIMARY KEY (id)
1185) ENGINE=MyISAM DEFAULT CHARSET=latin1
1186
1187SELECT *
1188FROM tasks
1189where role_id = 85
1190ORDER BY id
1191
1192update tasks
1193set status = 159
1194where id = 65
1195
1196SELECT *
1197FROM tasks
1198ORDER BY id
1199
1200SELECT *
1201FROM visits
1202
1203SELECT *
1204FROM users
1205
1206SELECT *
1207FROM personnel_role
1208
1209SELECT *
1210FROM master_personnel
1211
1212SELECT *
1213FROM master_role_v2
1214
1215SELECT
1216 zz.id, zz.name, zz.description,
1217 SUM(zz.total_task) total_task,
1218 SUM(zz.task_finish) task_finish
1219FROM (
1220 SELECT
1221 a.*,
1222 COUNT(t.role_id) total_task,
1223 0 task_finish
1224 FROM master_role_v2 a
1225 INNER JOIN personnel_role pr on pr.role_id = a.id
1226 INNER JOIN tasks t on t.role_id = a.id
1227 WHERE pr.personnel_id = 73
1228 GROUP BY t.role_id
1229 UNION ALL
1230 SELECT
1231 a.*,
1232 0 total_task,
1233 count(t.role_id) task_finish
1234 FROM master_role_v2 a
1235 INNER JOIN personnel_role pr on pr.role_id = a.id
1236 INNER JOIN tasks t on t.role_id = a.id
1237 WHERE pr.personnel_id = 73 and t.status = 159
1238 GROUP BY t.role_id
1239) zz
1240GROUP BY zz.id, zz.name, zz.description
1241
1242SELECT
1243 zz.id, zz.name, zz.description,
1244 CASE WHEN udp.id IS NOT NULL THEN 1 ELSE 0 END allowed,
1245 SUM(zz.total_task) total_task,
1246 SUM(zz.task_finish) task_finish
1247FROM (
1248 SELECT
1249 mp.*,
1250 dp.division_id,
1251 dp.personnel_id,
1252 0 total_task,
1253 0 task_finish
1254 FROM master_personnel mp
1255 LEFT JOIN division_personnel dp on dp.personnel_id = mp.id
1256 LEFT JOIN personnel_role pr on pr.personnel_id = dp.personnel_id
1257 LEFT JOIN tasks t on t.role_id = pr.role_id
1258 WHERE dp.division_id = 70 AND mp.idelete = 0
1259 UNION ALL
1260 SELECT
1261 mp.*,
1262 dp.division_id,
1263 dp.personnel_id,
1264 COUNT(t.role_id) total_task,
1265 0 task_finish
1266 FROM master_personnel mp
1267 LEFT JOIN division_personnel dp on dp.personnel_id = mp.id
1268 LEFT JOIN personnel_role pr on pr.personnel_id = dp.personnel_id
1269 LEFT JOIN tasks t on t.role_id = pr.role_id
1270 WHERE dp.division_id = 70 AND mp.idelete = 0
1271 GROUP BY t.role_id
1272 UNION ALL
1273 SELECT
1274 mp.*,
1275 dp.division_id,
1276 dp.personnel_id,
1277 0 total_task,
1278 COUNT(t.role_id) task_finish
1279 FROM master_personnel mp
1280 LEFT JOIN division_personnel dp on dp.personnel_id = mp.id
1281 LEFT JOIN personnel_role pr on pr.personnel_id = dp.personnel_id
1282 LEFT JOIN tasks t on t.role_id = pr.role_id
1283 WHERE dp.division_id = 70 AND mp.idelete = 0 AND t.status = 159 AND t.user_id = 2
1284 GROUP BY t.role_id
1285) zz
1286LEFT JOIN user_division_personnel udp on udp.division_id = zz.division_id AND udp.personnel_id = zz.personnel_id AND udp.user_id = 2
1287GROUP BY zz.id, zz.name, zz.description
1288
1289SELECT *
1290FROM absence
1291
1292CREATE TABLE IF NOT EXISTS `absence` (
1293 `id` int(11) NOT NULL AUTO_INCREMENT,
1294 `username` varchar(100) NOT NULL,
1295 `log_type` varchar(1) NOT NULL COMMENT 'M = Absen Masuk, K = Absen Keluar',
1296 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1297 `date` datetime NOT NULL,
1298 `lat` double NOT NULL,
1299 `lng` double NOT NULL,
1300 PRIMARY KEY (id)
1301) ENGINE=MyISAM DEFAULT CHARSET=latin1