/*

Lookup - sample_type, container_type
  Lookup - slot for collection / Booking / discount_type
  
  */

Truncate lab_analyte_master

DROP TABLE IF EXISTS lab_analyte_master;
CREATE TABLE lab_analyte_master  (
  id int NOT NULL AUTO_INCREMENT,
  analyteid varchar(50) NOT NULL,
  analytecode varchar(50) NOT NULL,
  analyte_name varchar(255) NOT NULL,
  analyte_display_text varchar(255) NOT NULL,
  department_id int NOT NULL,
  sub_department_id int NOT NULL,
  analyte_category varchar(25) NOT NULL,
  method_id int NOT NULL,
  sample_id int NOT NULL,
  analyte_uom varchar(50) NOT NULL,
	result_type varchar(50) NOT NULL,
  container_id int NOT NULL,
  analyte_sequence_no decimal(10, 2) NULL DEFAULT 0,
  standard_charge decimal(10, 2) NULL DEFAULT 0,
  is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_printable enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_reportable enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_orderable enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_radiology enum('Y','N')  NOT NULL DEFAULT 'N',
  is_out_source enum('Y','N')  NOT NULL DEFAULT 'N',
  is_auto_authorize enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_qty_chargable enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_amount_editable enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_print_seperate_page enum('Y','N')  NOT NULL DEFAULT 'N',
  is_discount enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_history_required enum('Y','N')  NOT NULL DEFAULT 'N',
  is_mandetory enum('Y','N')  NOT NULL DEFAULT 'N',
  is_interface enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_consent enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_range_reference_required enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_flag_required enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_unit_required enum('Y','N')  NOT NULL DEFAULT 'Y',
  is_ip enum('Y','N')  NOT NULL DEFAULT 'Y',
  ip_type enum('A','P') NOT NULL DEFAULT 'P',
  standard_ip_rate decimal(10, 2) NULL DEFAULT 0.00,
  inner_notes varchar(1000) NULL,
  consent_form varchar(1000) NULL,
  remarks varchar(1000) NULL,

  PRIMARY KEY (id) USING BTREE

) ENGINE = InnoDB;

DROP TABLE IF EXISTS lab_analyte_category_map;
CREATE TABLE lab_analyte_category_map  (
 id int NOT NULL AUTO_INCREMENT,
 parent_lab_analyte_id int NOT NULL,
 child_lab_analyte_id int NOT NULL,
 is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
 PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


describe lab_analyte_master


Insert Into lab_analyte_master
(analyteid, analytecode, analyte_name, analyte_display_text, department_id, sub_department_id, analyte_category,
method_id, sample_id, analyte_uom, result_type, container_id, analyte_sequence_no, standard_charge,
is_interface, is_range_reference_required, is_flag_required, is_unit_required,
is_active, 
is_printable, 
is_reportable, 
is_orderable,
ip_type, standard_ip_rate, inner_notes, consent_form, remarks
)
Select analyteid, analytecode, analyte_name, analyte_display_text, 0, 0, '',
0, 0,  analyte_uom, result_type, 0, analyte_sequence_no, standard_charge,
is_interface, is_range_reference_required, is_flag_required, is_unit_required,
Case when Upper(is_active) = 'YES' Then 'Y' ELSE 'N' END, 
Case when Upper(printable) = 'YES' Then 'Y' ELSE 'N' END, 
Case when Upper(is_reportable) = 'YES' Then 'Y' ELSE 'N' END, 
Case when Upper(orderable) = 'YES' Then 'Y' ELSE 'N' END, 
ip_type, ip_rate, inner_notes, consent_form, remarks
 from pathology_master
 where is_active = 'Yes'
 group by analyteid, analyte_name;
 

 Select Count(*) from lab_analyte_master;
 
 DROP TABLE IF EXISTS lab_booking;
CREATE TABLE lab_booking  (
  id int NOT NULL,
  bill_no varchar(255)  NOT NULL,
  reference_bill_no varchar(255)  NULL DEFAULT NULL,
  case_id varchar(255)  NOT NULL,
  sub_case_id varchar(255)  NOT NULL,
  patient_id int NOT NULL,
  const_doc_id varchar(25)  NOT NULL DEFAULT '0',
  const_doc varchar(255)  NULL DEFAULT 'N/A',
  referral_type varchar(100)  NOT NULL,
  referral varchar(50)  NULL DEFAULT NULL,
  booking_date	date NOT NULL,
  business_type enum('0','1','2')  NULL DEFAULT NULL COMMENT '0=b2c, 1=b2b',

  stats enum('0','1')  NOT NULL,
  payemnt_status enum('1','2','3')  NOT NULL DEFAULT '1' COMMENT '\'1\'=>not paid, \'2\'=>partial paid, 3=>full paid \'3\'',
	
  case_status enum('-1','0','0.2','1','1.1','1.2','2','3','4','5','6','7','8','9','10','11','12','0.1','8.1','1.15')  NOT NULL DEFAULT '0' COMMENT '\'0\'=>booked;\'0.2\' =>  Barcode Generated \'1\'=>partially collected,\'1.1\' =>Intransit Mode \'2\'=>collected;\'3\' =>partialy generated; \'4\' => full generated; \'5\'=>prtially Finalized;\'6\'=>completely finalized;\'7\'=>partially published;\'8\'=>fully published, \'9\'=> Samples Partially Accepted ,\'1.2\' => Samples Accepted Completely, \'11\' => Samples Cancelled. \'8.1\' => Case Re-Opned \'1.15\' => Intransit Complete. Ready For Sample Acknowledgement',
  total_charge decimal(10, 2) NOT NULL,
  home_collection_status enum('0','0.1','0.2')  NOT NULL COMMENT '0 - Booked, 0.1 - Bhalomotmy Assigned , 0.2 -> collected',
  doctor_verified_details longtext  NOT NULL,
  total_paid decimal(10, 2) NOT NULL,
  due_charge decimal(10, 2) NOT NULL,
  disc_amount decimal(10, 2) NOT NULL,
  actual_discount decimal(10, 2) NOT NULL,
  discount decimal(10, 2) NOT NULL,
  discount_type varchar(50)  NULL DEFAULT NULL,
  discount_remarks varchar(255)  NOT NULL DEFAULT 'NA',
  discount_approval_by int NULL DEFAULT 0,
  discount_referred_by varchar(50)  NOT NULL,
  discount_is_approved enum('0','1')  NOT NULL,
  refund_amt decimal(11, 2) NOT NULL DEFAULT 0.00,
  refund_is_approved enum('0','1')  NULL DEFAULT '0',
  refund_approved_by varchar(100)  NULL DEFAULT NULL,
  refund_approve_date_time datetime(0) NULL DEFAULT NULL,

  rejected_datetime datetime(0) NULL DEFAULT NULL,
  cancell_amt decimal(11, 2) NOT NULL DEFAULT 0.00,
  outstand decimal(11, 2) NOT NULL DEFAULT 0.00,
  print_count int NULL DEFAULT 2,
  location varchar(255)  NULL DEFAULT NULL,
  employee_id varchar(255)  NULL DEFAULT NULL,
  claim_id varchar(255)  NULL DEFAULT NULL,
  hc_case_barcode varchar(200)  NULL DEFAULT NULL,
  cc_id int NOT NULL DEFAULT 0,
  refund_amount decimal(10, 2) NULL DEFAULT 0.00,
  ref_type varchar(50)  NOT NULL,
  is_cut_money_generated enum('0','1')  NOT NULL,
  registration_charge int NOT NULL,
  remarks text  NULL,
  created_at datetime(0) NOT NULL,
  created_by varchar(50) NOT NULL,
  updated_at datetime(0) NULL,
  updated_by varchar(50) NULL,
  PRIMARY KEY (id) USING BTREE
  
) ENGINE = InnoDB ;


DROP TABLE IF EXISTS lab_booking_details;
CREATE TABLE lab_booking_details  (
  id int NOT NULL,
  lab_booking int NOT NULL,
  branch_id	int NOT NULL,
  case_id varchar(255)  NOT NULL,
  sub_case_id varchar(255)  NOT NULL,
  lab_analyte_id int NOT NULL,

  test_schedule_date	date NOT NULL,
  test_schedule_slot varchar(50)	NOT NULL,
  collection_date date NULL DEFAULT NULL,
  collection_slot varchar(50)  NOT NULL,
  collection_address varchar(255)  NOT NULL,
  collected_by varchar(255)  NOT NULL,
  colelctor_id int NOT NULL,
  collected_datetime datetime(0) NULL DEFAULT NULL, 
  reporting_date date NULL DEFAULT NULL,

  apply_charge decimal(15, 2) NOT NULL DEFAULT 0.00,
  test_rate decimal(10, 2) NOT NULL DEFAULT 0.00,
  discount_amount decimal(10, 2) NOT NULL DEFAULT 0.00,
  paid_amt decimal(11, 2) NOT NULL DEFAULT 0.00,
  sample_no varchar(255)  NULL DEFAULT NULL,
  
  is_interfaced enum('Y','N')  NULL DEFAULT 'Y',
  interface_call_on datetime(0) NULL DEFAULT NULL,
  interface_resulted_on datetime(0) NULL DEFAULT NULL,
  total_recollection_count int NULL DEFAULT NULL,
  
  is_recollection_settled enum('Y','N')  NULL DEFAULT NULL,
  sample_type varchar(50)  NULL DEFAULT NULL,
  container_type varchar(50)  NULL DEFAULT NULL,
  collector_note text  NULL,
  status enum('-1','0','1','1.2','2','3','4','5','0.2','0.1')  NOT NULL DEFAULT '0' COMMENT '\'-1\'=>cancelled,\'0\'=>booked;\'1\'=>collected;\'1.2\' => Sample Acknowledged \'2\'=>generated;\'3\'=>finalized;\'4\'=>published,\'5\'=>sample cancelled,\'0.2\' => Barcode Generated',
  in_transit_status enum('1','2','3','4')  NOT NULL DEFAULT '1' COMMENT '1- Sent, 2- Pending, 3 - Completed, 4- Cancelled Shipment',
  stats enum('Y','N')  NOT NULL DEFAULT 'N',
  
  
  is_sample_rejected enum('Y','N')  NOT NULL DEFAULT 'N',
  is_sample_to_colelcted enum('Y','N')  NOT NULL DEFAULT 'Y',
  rejected_reason varchar(255)  NULL DEFAULT NULL,
  is_case_re_opened int NOT NULL DEFAULT 0,
  case_re_opened_on datetime(0) NULL DEFAULT NULL,
  case_re_opened_by varchar(200)  NULL DEFAULT NULL,
  
  re_open_count int NOT NULL DEFAULT 0,
  recollection_remarks text  NULL,
  
  is_sample_rerun enum('Y','N')  NOT NULL DEFAULT 'N',
  cancelled_by varchar(100)  NULL DEFAULT NULL,
  cancelled_on datetime(0) NULL DEFAULT NULL,
  in_bill enum('N','Y')  NOT NULL DEFAULT 'Y',

  created_at datetime(0) NOT NULL,
  created_by varchar(50) NOT NULL,
  updated_at datetime(0) NULL,
  updated_by varchar(50) NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- --------------------------------------------------------
-- Table structure for lab_analyte_range_reference
-- --------------------------------------------------------
DROP TABLE IF EXISTS lab_analyte_range_reference;
CREATE TABLE lab_analyte_range_reference  (
  id int NOT NULL AUTO_INCREMENT,
  lab_analyte_id INT NOT NULL,
  gender_code varchar(100)  NOT NULL,
  age_from varchar(100)  NOT NULL,
  age_from_type varchar(100)  NOT NULL,
  age_from_days varchar(100)  NOT NULL,
  age_to varchar(100)  NOT NULL,
  age_to_type varchar(100)  NOT NULL,
  age_to_days varchar(100)  NOT NULL,
  range_from varchar(100)  NOT NULL,
  range_to varchar(200)  NOT NULL,
  display_rr text NOT NULL,
  is_active enum('Y','N')  NOT NULL DEFAULT 'Y',
  created_at datetime(0) NOT NULL,
  created_by varchar(50) NOT NULL,
  updated_at datetime(0) NULL,
  updated_by varchar(50) NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;

DROP TABLE IF EXISTS lab_formula_master;
CREATE TABLE `lab_formula_master` (
  `id` int NOT NULL AUTO_INCREMENT,
  `master_test` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `range_effective_ant_code` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `range_from` decimal(10,3) DEFAULT '0.000',
  `range_to` decimal(10,3) DEFAULT '0.000',
  `formula` longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `is_active` enum('Y','N') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
