-- https://www.phpmyadmin.net/
--


--
-- Database: `dhp`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `FN_COURSE_CHECKIN` (`uid` INT, `ucourse` INT, `teacher` INT, `sdate` DATE, `stime` TIME, `etime` TIME, `admin` INT) RETURNS INT(11) NO SQL
BEGIN 

	DECLARE productID INT(11);
    DECLARE validTerm INT(11);
    DECLARE numberOfTime INT(11);
    DECLARE cntCC INT(11);
    DECLARE eDate DATE;

	SELECT COUNT(*) INTO cntCC FROM course_checkin cc WHERE cc.user_course_id = ucourse;
    
    SELECT p.product_id, p.valid_term, p.number_of_time INTO productID, validTerm, numberOfTime FROM product p JOIN user_course uc ON p.product_id = uc.course_id WHERE uc.id = ucourse;
    
	SELECT DATE_ADD(sdate, INTERVAL validTerm DAY) INTO eDate;

	IF cntCC = 0 THEN 
    	
        INSERT INTO course_checkin (user_id, user_course_id, teacher_id, date, start_time, end_time, create_at, create_by) VALUES (uid, ucourse, teacher, sdate, stime, etime, NOW(), admin);
        
        UPDATE user_course SET start_date = sdate, end_date = eDate, status = 'Active', update_at = NOW(), update_by = admin WHERE id = ucourse;
        
    ELSEIF cntCC > 0 AND cntCC <= numberOfTime THEN
    
    	INSERT INTO course_checkin (user_id, user_course_id, teacher_id, date, start_time, end_time, create_at, create_by) VALUES (uid, ucourse, teacher, sdate, stime, etime, NOW(), admin);
        
    ELSEIF cntCC >= numberOfTime THEN
    	UPDATE user_course SET status = 'Expired', update_at = NOW(), update_by = admin WHERE id = ucourse;
    END IF;
    
	RETURN productID;

END$$

DELIMITER ;

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

--
-- Table structure for table `course_checkin`
--

CREATE TABLE `course_checkin` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_course_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `create_at` datetime NOT NULL,
  `create_by` int(11) NOT NULL,
  `update_at` datetime DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `course_checkin`
--

INSERT INTO `course_checkin` (`id`, `user_id`, `user_course_id`, `teacher_id`, `date`, `start_time`, `end_time`, `create_at`, `create_by`, `update_at`, `update_by`) VALUES
(1, 2, 6, 4, '1970-01-01', '02:38:00', '03:38:00', '2019-03-14 02:38:25', 1, NULL, NULL),
(2, 2, 7, 4, '1970-01-01', '02:41:00', '03:41:00', '2019-03-14 02:42:06', 1, NULL, NULL),
(3, 2, 8, 3, '2019-03-14', '02:47:00', '03:47:00', '2019-03-14 02:47:33', 1, NULL, NULL),
(4, 2, 6, 4, '2019-03-14', '03:09:00', '04:09:00', '2019-03-14 03:10:54', 1, NULL, NULL),
(5, 2, 7, 7, '2019-03-14', '03:11:00', '04:11:00', '2019-03-14 03:11:24', 1, NULL, NULL);

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

--
-- Table structure for table `parent_info`
--

CREATE TABLE `parent_info` (
  `parent_id` int(11) NOT NULL,
  `firstname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `relation` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_at` datetime NOT NULL,
  `create_by` int(11) NOT NULL,
  `update_at` datetime DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `parent_info`
--

INSERT INTO `parent_info` (`parent_id`, `firstname`, `lastname`, `relation`, `tel`, `email`, `create_at`, `create_by`, `update_at`, `update_by`) VALUES
(1, 'Payomss', 'Jankrut', 'Dad', '080-789-1234', 'pop@gmail.com', '2019-03-13 00:00:00', 0, '2019-03-13 07:52:03', 1),
(2, 'asdasd', 'asd', 'asdas', '093-802-9480', 'p@d.co', '2019-03-14 10:39:13', 1, NULL, NULL),
(3, 'sdvsdv', 'sdbsdb', 'sdvsv', '098-345-8045', 'po@f.co', '2019-03-14 10:45:17', 1, NULL, NULL),
(4, 'sdvs', 'sdvsdv', 'sdvsv', '324-786-9234', 'po@c.o', '2019-03-14 10:46:22', 1, NULL, NULL),
(5, 'dfbdfb', 'dfbdfb', 'dfbdfb', '487-972-3498', 'po@f.co', '2019-03-14 10:47:25', 1, NULL, NULL),
(6, 'sdvsdhj', 'sdjhvb', 'jhbsdv', '721-637-1682', 'p@f.co', '2019-03-14 10:49:46', 1, NULL, NULL);

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

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name_th` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `desc_en` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `desc_th` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_term` int(11) DEFAULT NULL,
  `number_of_time` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `create_at` datetime DEFAULT NULL,
  `create_by` int(11) DEFAULT NULL,
  `update_at` datetime DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`product_id`, `name_en`, `name_th`, `desc_en`, `desc_th`, `type`, `valid_term`, `number_of_time`, `price`, `create_at`, `create_by`, `update_at`, `update_by`) VALUES
(1, 'Finger scan for children up to 7 years', 'สแกนลายนิ้วมือ สำหรับเด็กอายุไม่เกิน 7 ขวบ', NULL, NULL, 'Product', NULL, NULL, 6500, '2019-03-12 00:00:00', 0, NULL, NULL),
(2, 'Finger scan for children over 7 years', 'สแกนลายนิ้วมือ สำหรับเด็กอายุ 7 ขวบขึ้นไป', NULL, NULL, 'Product', NULL, NULL, 11000, '2019-03-12 00:00:00', 0, NULL, NULL),
(3, 'Clay art', 'ปั้นดาว', NULL, 'ส่งเสริมกระบวนการทำงาน | ให้เด็กได้มีการวางแผน มีสมาธิ | ผ่านการปั้นดิน เป็นรูปร่างต่าวๆ', 'Course', 120, 12, 6500, '2019-03-12 00:00:00', 0, NULL, NULL),
(4, 'Creative art', 'วาดฝัน', NULL, 'ส่งเสริมกระบวนการทำงาน | ให้เด็กได้มีการวางแผน มีสมาธิ | ผ่านการวาดรูป และประดิษฐ์ของเล่นด้วยตนเอง', 'Course', 120, 12, 6500, '2019-03-12 00:00:00', 0, NULL, NULL),
(5, 'Mix art', 'วาดฝัน+ปั้นดาว', NULL, 'ส่งเสริมกระบวนการทำงาน | ให้เด็กได้มีการวางแผน มีสมาธิ | เหมาะสำหรับเด็กอายุ 5 ขวบขึ้นไป', 'Course', 120, 12, 7000, '2019-03-12 00:00:00', 0, NULL, NULL),
(6, 'Hour', 'รายชั่วโมง', NULL, 'ส่งเสริมกระบวนการทำงาน | ให้เด็กได้มีการวางแผน มีสมาธิ | Walk-ins welcome', 'Course', 1, 1, 650, '2019-03-12 00:00:00', 0, NULL, NULL);

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

--
-- Table structure for table `staff_checkin`
--

CREATE TABLE `staff_checkin` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `create_at` datetime NOT NULL,
  `create_by` int(11) NOT NULL,
  `update_at` datetime DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `sub_course`
--

CREATE TABLE `sub_course` (
  `sub_course_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `name_th` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `desc_en` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `desc_th` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `create_at` datetime NOT NULL,
  `create_by` int(11) NOT NULL,
  `update_at` datetime NOT NULL,
  `update_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `user_course`
--

CREATE TABLE `user_course` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remark` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `create_by` int(11) NOT NULL,
  `update_at` datetime DEFAULT NULL,
  `update_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `user_course`
--

INSERT INTO `user_course` (`id`, `user_id`, `course_id`, `start_date`, `end_date`, `status`, `remark`, `create_at`, `create_by`, `update_at`, `update_by`) VALUES
(6, 2, 3, NULL, NULL, 'Wating', NULL, '2019-03-12 19:19:51', 0, NULL, NULL),
(7, 2, 5, '1970-01-01', '1970-05-01', 'Active', NULL, '2019-03-13 08:32:44', 0, '2019-03-14 02:42:06', 1),
(8, 2, 4, '2019-03-14', '2019-07-12', 'Active', 'RR', '2019-03-13 13:29:58', 0, '2019-03-14 02:47:33', 1);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `course_checkin`
--
ALTER TABLE `course_checkin`
  ADD PRIMARY KEY (`id`),
  ADD KEY `userCourseKey` (`user_course_id`);

--
-- Indexes for table `parent_info`
--
ALTER TABLE `parent_info`
  ADD PRIMARY KEY (`parent_id`);

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`product_id`);

--
-- Indexes for table `staff_checkin`
--
ALTER TABLE `staff_checkin`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `sub_course`
--
ALTER TABLE `sub_course`
  ADD PRIMARY KEY (`sub_course_id`),
  ADD KEY `prosubcourse` (`product_id`);

--
-- Indexes for table `user_course`
--
ALTER TABLE `user_course`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `course_checkin`
--
ALTER TABLE `course_checkin`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `parent_info`
--
ALTER TABLE `parent_info`
  MODIFY `parent_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `staff_checkin`
--
ALTER TABLE `staff_checkin`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sub_course`
--
ALTER TABLE `sub_course`
  MODIFY `sub_course_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_course`
--
ALTER TABLE `user_course`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `course_checkin`
--
ALTER TABLE `course_checkin`
  ADD CONSTRAINT `userCourseKey` FOREIGN KEY (`user_course_id`) REFERENCES `user_course` (`id`);

--
-- Constraints for table `sub_course`
--
ALTER TABLE `sub_course`
  ADD CONSTRAINT `prosubcourse` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`);