# ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: 127.0.0.1 (MySQL 5.7.18)
# Database: dbs_fsconnect
# Generation Time: 2020-02-25 10:17:47 +0000
# ************************************************************




# Dump of table asset_types
# ------------------------------------------------------------


CREATE TABLE `asset_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `asset_types_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `asset_types` (`id`, `name`)
VALUES
	(2,'ที่ดินพร้อมสิ่งปลูกสร้าง'),
	(1,'ที่ดินว่างเปล่า'),
	(3,'ห้องชุด'),
	(4,'เครื่องจักร');



# Dump of table jobs
# ------------------------------------------------------------


CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `note` text COLLATE utf8mb4_unicode_ci,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `worker_id` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_order_id_foreign` (`order_id`),
  KEY `jobs_worker_id_foreign` (`worker_id`),
  KEY `jobs_created_by_foreign` (`created_by`),
  KEY `jobs_updated_by_foreign` (`updated_by`),
  CONSTRAINT `jobs_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `jobs_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `jobs_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `jobs_worker_id_foreign` FOREIGN KEY (`worker_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `jobs` (`id`, `note`, `order_id`, `worker_id`, `created_by`, `updated_by`, `status`, `created_at`, `updated_at`)
VALUES
	(1,'Test',1,3,1,3,2,'2020-02-18 09:39:21','2020-02-19 03:07:44'),
	(2,'Note Test',3,1,1,1,2,'2020-02-19 01:37:27','2020-02-19 02:06:48');



# Dump of table media
# ------------------------------------------------------------


CREATE TABLE `media` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  `collection_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mime_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `disk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int(10) unsigned NOT NULL,
  `manipulations` json NOT NULL,
  `custom_properties` json NOT NULL,
  `responsive_images` json NOT NULL,
  `order_column` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `media_model_type_model_id_index` (`model_type`,`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table migrations
# ------------------------------------------------------------


CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `migrations` (`id`, `migration`, `batch`)
VALUES
	(1,'2014_10_12_000000_create_users_table',1),
	(2,'2014_10_12_100000_create_password_resets_table',1),
	(3,'2020_01_22_080138_create_permission_tables',2),
	(4,'2020_01_22_081908_create_media_table',3),
	(5,'2020_02_04_060604_create_objectives_table',4),
	(6,'2020_02_05_061559_create_asset_types_table',5),
	(7,'2020_02_05_064418_create_orders_table',5),
	(8,'2020_02_05_083532_create_order_costs_table',5),
	(9,'2020_02_11_073333_create_vouchers_table',6),
	(10,'2020_02_11_073347_create_voucher_items_table',6),
	(11,'2020_02_18_080535_create_jobs_table',7);



# Dump of table model_has_permissions
# ------------------------------------------------------------


CREATE TABLE `model_has_permissions` (
  `permission_id` int(10) unsigned NOT NULL,
  `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`model_id`,`model_type`),
  KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table model_has_roles
# ------------------------------------------------------------


CREATE TABLE `model_has_roles` (
  `role_id` int(10) unsigned NOT NULL,
  `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`model_id`,`model_type`),
  KEY `model_has_roles_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `model_has_roles` (`role_id`, `model_type`, `model_id`)
VALUES
	(4,'App\\User',1),
	(8,'App\\User',2),
	(10,'App\\User',3);



# Dump of table objectives
# ------------------------------------------------------------


CREATE TABLE `objectives` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `objectives_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `objectives` (`id`, `name`)
VALUES
	(2,'เพื่อป็นหลักประกันสินเชื่อให้สถาบันการเงิน'),
	(1,'เพื่อวัตถุประสงค์สาธารณะ');



# Dump of table order_costs
# ------------------------------------------------------------


CREATE TABLE `order_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` double(8,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_costs_order_id_foreign` (`order_id`),
  CONSTRAINT `order_costs_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `order_costs` (`id`, `order_id`, `description`, `amount`)
VALUES
	(1,1,'Fee',1000.25),
	(4,1,'Service',200.00),
	(5,1,'ค่าเดินทาง',500.00),
	(6,3,'ค่าเดินทาง',250.00);



# Dump of table orders
# ------------------------------------------------------------


CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quotation_date` date DEFAULT NULL,
  `order_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `order_date` date NOT NULL,
  `customer_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_tel` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_tel` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `asset_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `asset_type_id` bigint(20) unsigned NOT NULL,
  `asset_document` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `asset_document_count` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `begin_date` date NOT NULL,
  `duration` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_date` date NOT NULL,
  `document_is_thai` tinyint(1) NOT NULL DEFAULT '0',
  `document_thai_count` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_is_eng` tinyint(1) NOT NULL DEFAULT '0',
  `document_eng_count` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_condition` smallint(6) NOT NULL,
  `payment_status` smallint(6) NOT NULL,
  `receipt_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `receipt_address` text COLLATE utf8mb4_unicode_ci,
  `quotation_is_other` tinyint(1) NOT NULL DEFAULT '0',
  `quotation_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quotation_address` text COLLATE utf8mb4_unicode_ci,
  `survey_date` date DEFAULT NULL,
  `survey_time` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `survey_address` text COLLATE utf8mb4_unicode_ci,
  `surveyer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coordinator` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coordinator_tel` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postbox_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postbox_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objective_id` bigint(20) unsigned DEFAULT NULL,
  `objective_is_other` tinyint(1) NOT NULL DEFAULT '0',
  `objective_other_text` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orders_asset_type_id_foreign` (`asset_type_id`),
  KEY `orders_objective_id_foreign` (`objective_id`),
  KEY `orders_created_by_foreign` (`created_by`),
  KEY `orders_updated_by_foreign` (`updated_by`),
  CONSTRAINT `orders_asset_type_id_foreign` FOREIGN KEY (`asset_type_id`) REFERENCES `asset_types` (`id`),
  CONSTRAINT `orders_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `orders_objective_id_foreign` FOREIGN KEY (`objective_id`) REFERENCES `objectives` (`id`),
  CONSTRAINT `orders_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `orders` (`id`, `quotation_no`, `quotation_date`, `order_no`, `order_date`, `customer_code`, `customer_name`, `customer_tel`, `owner_name`, `owner_tel`, `asset_address`, `asset_type_id`, `asset_document`, `asset_document_count`, `begin_date`, `duration`, `end_date`, `document_is_thai`, `document_thai_count`, `document_is_eng`, `document_eng_count`, `payment_condition`, `payment_status`, `receipt_name`, `receipt_address`, `quotation_is_other`, `quotation_name`, `quotation_address`, `survey_date`, `survey_time`, `survey_address`, `surveyer`, `coordinator`, `coordinator_tel`, `report_name`, `report_title`, `report_address`, `postbox_name`, `postbox_address`, `objective_id`, `objective_is_other`, `objective_other_text`, `created_by`, `updated_by`, `status`, `created_at`, `updated_at`)
VALUES
	(1,'REF01','2020-02-05','FSC-21212121','2020-02-05','C0001','Niyom Chamaree','0944208258','Pracha Rack','0812889888','Bangkok, Thailand 18000',2,'NP-100','1','2020-02-06','2','2020-02-08',1,'3',0,NULL,2,1,'Customer','Customer Address 111',1,NULL,NULL,'2020-02-06','22:32','Bangkok, Thailand','Survey Name','Contact Name','099999999','Report Name','Title','Report Address','Post Name','Post Address',1,0,NULL,1,3,2,'2020-02-05 15:34:11','2020-02-19 03:07:44'),
	(3,'REF03','2020-02-03','FSC-21212122','2020-02-07','121212','Niyom Chamaree','0944208258','Pracha Rack','0812889888','Bangkok, Thailand 18000',4,'NP-500','1','2020-02-15','10','2020-02-25',1,'4',1,'4',2,1,'Customer','Customer Address 111',0,'Test Name','Test Address','2020-02-08','14:56','Bangkok, Thailand','Survey Name','Contact Name','1212','Report Name','Title','Report Address','Post Name','Post Address',NULL,1,'ทดสอบ',1,1,2,'2020-02-06 08:00:05','2020-02-19 02:09:42');




# Dump of table permissions
# ------------------------------------------------------------


CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `permissions` (`id`, `name`, `guard_name`, `created_at`, `updated_at`)
VALUES
	(1,'New Order','web',NULL,NULL),
	(2,'User Settings','web',NULL,NULL),
	(3,'Active Orders','web',NULL,NULL),
	(4,'Success Orders','web',NULL,NULL),
	(5,'Payment Voucher','web',NULL,NULL),
	(6,'Active Jobs','web',NULL,NULL),
	(7,'Jobs','web',NULL,NULL);



# Dump of table role_has_permissions
# ------------------------------------------------------------


CREATE TABLE `role_has_permissions` (
  `permission_id` int(10) unsigned NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `role_has_permissions_role_id_foreign` (`role_id`),
  CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
VALUES
	(1,4),
	(2,4),
	(3,4),
	(4,4),
	(5,4),
	(6,4),
	(7,4),
	(2,5),
	(1,8),
	(5,9),
	(5,10),
	(7,10);



# Dump of table roles
# ------------------------------------------------------------


CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `roles` (`id`, `name`, `guard_name`, `created_at`, `updated_at`)
VALUES
	(4,'Administrator','web','2020-02-02 07:14:37','2020-02-02 07:14:37'),
	(5,'Manager','web','2020-02-02 07:59:22','2020-02-02 07:59:22'),
	(8,'Marketing','web','2020-02-02 08:51:57','2020-02-02 08:51:57'),
	(9,'Accounting','web','2020-02-06 07:30:56','2020-02-06 07:30:56'),
	(10,'Worker','web','2020-02-18 07:48:00','2020-02-18 07:48:00');



# Dump of table voucher_items
# ------------------------------------------------------------


CREATE TABLE `voucher_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `voucher_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` double(8,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `voucher_items_voucher_id_foreign` (`voucher_id`),
  CONSTRAINT `voucher_items_voucher_id_foreign` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `voucher_items` (`id`, `voucher_id`, `description`, `amount`)
VALUES
	(1,1,'ค่าเดินทาง',1000.99),
	(2,2,'Service',100.00),
	(3,3,'ค่าเดินทาง',1000.00);



# Dump of table vouchers
# ------------------------------------------------------------


CREATE TABLE `vouchers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `voucher_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `voucher_date` date NOT NULL,
  `order_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pay_for_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_method` char(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bank_account_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_method` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vouchers_created_by_foreign` (`created_by`),
  KEY `vouchers_updated_by_foreign` (`updated_by`),
  CONSTRAINT `vouchers_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `vouchers_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `vouchers` (`id`, `voucher_no`, `voucher_date`, `order_no`, `pay_for_name`, `payment_method`, `bank_account_no`, `bank_name`, `other_method`, `created_by`, `updated_by`, `status`, `created_at`, `updated_at`)
VALUES
	(1,'T-199','2020-02-12','FSC-21212121','Niyom Chamaree','TRANSFER','09434304344','KBank',NULL,1,1,1,'2020-02-11 07:44:28','2020-02-11 08:42:20'),
	(2,'VR-1000','2020-02-19','FSC-21212121','test','CASH',NULL,NULL,NULL,3,NULL,1,'2020-02-19 03:17:08','2020-02-19 03:17:08'),
	(3,'VR-9000','2020-02-19','FSC-21212122','Niyom Chamaree','CASH',NULL,NULL,NULL,1,NULL,1,'2020-02-19 03:38:39','2020-02-19 03:38:39');




