/*
 Navicat Premium Data Transfer

 Source Server         : Dev
 Source Server Type    : MySQL
 Source Server Version : 80026
 Source Host           : sigmentech.com:3306
 Source Schema         : sigmentech_sigmenhealth

 Target Server Type    : MySQL
 Target Server Version : 80026
 File Encoding         : 65001

 Date: 01/10/2021 14:05:28
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for pathology_report_master
-- ----------------------------
DROP TABLE IF EXISTS `pathology_report_master`;
CREATE TABLE `pathology_report_master`  (
  `id` int NOT NULL,
  `bill_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `reference_bill_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `case_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sub_case_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `patient_id` int NOT NULL,
  `const_doc_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0',
  `const_doc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'N/A',
  `collection_date` date NULL DEFAULT NULL,
  `collection_slot` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `collection_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `patient_contact` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `patient_age` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `patient_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `total_charge` decimal(10, 2) NOT NULL,
  `payemnt_status` enum('1','2','3') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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',
  `home_collection_status` enum('0','0.1','0.2') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '0 - Booked, 0.1 - Bhalomotmy Assigned , 0.2 -> collected',
  `doctor_verified_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `collected_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_at` datetime(0) NOT NULL,
  `colelctor_id` int NOT NULL,
  `collected_datetime` datetime(0) NULL DEFAULT NULL,
  `rejected_datetime` datetime(0) NULL DEFAULT NULL,
  `referral_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `referral` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `stats` enum('0','1') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `is_radiology` enum('0','1') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `business_type` enum('0','1','2') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '\'0=b2c\', \'1=b2b\'',
  `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(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `discount_remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NA',
  `discount_approval_by` int NULL DEFAULT 0,
  `discount_referred_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `discount_is_approved` enum('0','1') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `refund_amt` decimal(11, 2) NOT NULL DEFAULT 0.00,
  `refund_is_approved` enum('0','1') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0',
  `refund_approved_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `refund_approve_date_time` datetime(0) NULL DEFAULT NULL,
  `pathology_bill` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT 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,
  `designation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `employee_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `claim_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `hc_case_barcode` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `cc_id` int NOT NULL DEFAULT 0,
  `is_master` enum('ML','CC') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'CC',
  `refund_amount` decimal(10, 2) NULL DEFAULT 0.00,
  `ref_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `is_cut_money_generated` enum('0','1') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `updated_at` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `patient_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `prev_collector_id` int NOT NULL,
  `current_collector_id` int NOT NULL,
  `changeRider_reason` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `changeRider_date` datetime(0) NULL DEFAULT NULL,
  `temporary_booking_id` int NOT NULL,
  `registration_charge` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for pathology_report_new
-- ----------------------------
DROP TABLE IF EXISTS `pathology_report_new`;
CREATE TABLE `pathology_report_new`  (
  `id` int NOT NULL,
  `cc_id` int NOT NULL DEFAULT 0,
  `is_master` enum('ML','CC') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'CC',
  `case_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sub_case_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pathology_test_id` int NOT NULL,
  `pathology_group_id` int NOT NULL,
  `patient_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `reporting_date` date NULL DEFAULT NULL,
  `pathology_report` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci 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) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_interfaced` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
  `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('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sample_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `container_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collector_note` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `status` enum('-1','0','1','1.2','2','3','4','5','0.2','0.1') CHARACTER SET utf8 COLLATE utf8_general_ci 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') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '1- Sent, 2- Pending, 3 - Completed, 4- Cancelled Shipment',
  `stats` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `is_radiology` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `is_sample_rejected` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `is_sample_to_colelcted` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `rejected_reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci 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) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `re_open_count` int NOT NULL DEFAULT 0,
  `recollection_remarks` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `is_sample_rerun` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  `cancelled_by` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cancelled_on` datetime(0) NULL DEFAULT NULL,
  `in_bill` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `in_pkg` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `modified_on` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
