CREATE TABLE `booking_no2` (
  `id` int(11) NOT NULL,
  `department` varchar(50) NOT NULL,
  `unit_name` varchar(20) NOT NULL,
  `lab_code` int(11) NOT NULL,
  `unit_code` int(11) NOT NULL,
  `initials` int(11) NOT NULL,
  `year_code` int(11) NOT NULL,
  `booking` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `booking_no2`
--

INSERT INTO `booking_no2` (`id`, `department`, `unit_name`, `lab_code`, `unit_code`, `initials`, `year_code`, `booking`) VALUES
(1, 'Hematology', 'Hematology', 2000, 2, 2, 21, 29727),
(2, 'Chemical Pathology', 'Routine Chemistry', 3000, 3, 3, 21, 12563),
(3, 'Microbiology', 'Microbiology', 4000, 6, 6, 21, 1409),
(4, 'Radiology', 'Radiology', 5000, 1, 1, 21, 5780),
(5, 'Chemical Pathology', 'Tumor Markers', 3000, 4, 4, 21, 57),
(6, 'Chemical Pathology', 'Hormones', 3000, 5, 5, 21, 491),
(7, 'Microbiology', 'Serology', 4000, 7, 7, 21, 5184),
(8, 'Cardiology', 'Cardiology', 6000, 1, 1, 21, 5780);

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `query_code` (IN `query_type` VARCHAR(50), IN `in_key` VARCHAR(20), IN `in_value` VARCHAR(20))  NO SQL
IF query_type = 'get_barcode' THEN
	SELECT * FROM barcode;
ELSEIF query_type = 'get_labno' THEN
	SELECT * FROM booking_no2;
ELSEIF query_type = 'update_barcode' THEN
	UPDATE barcode SET barcode = in_value WHERE lab_code=in_key;
ELSEIF query_type = 'update_booking' THEN
	UPDATE booking_no2 SET booking = in_value WHERE unit_code=in_key;
END IF$$
DELIMITER ;

ALTER TABLE `lab_requisition`  ADD `receivable_head_name` VARCHAR(100) NULL  AFTER `discount_amount`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_barcode_details` (IN `in_request_id` VARCHAR(50))  NO SQL
SELECT description, group_head, booking_no, name, patient_id, created_at, specimen, code, label_type, label_name, noOfLabels, department FROM lab_info WHERE request_id = in_request_id$$

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_receipt_no` (IN `in_old_receipt_no` VARCHAR(50), IN `in_new_receipt_no` VARCHAR(50), IN `in_patient_id` VARCHAR(50))  NO SQL
UPDATE lab_requisition SET receiptNo = in_new_receipt_no WHERE patient_id=in_patient_id AND receiptNo=in_old_receipt_no$$
