CREATE DATABASE  IF NOT EXISTS `yginsurance` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `yginsurance`;
--
-- Host: localhost    Database: yginsurance
-- ------------------------------------------------------


--
-- Table structure for table `tbl_insuranceapprovaldetails`
--

CREATE TABLE `tbl_insuranceapprovaldetails` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) DEFAULT NULL,
  `branch_approvalrowid` int(11) DEFAULT NULL,
  `counseling_id` int(11) DEFAULT NULL,
  `surgicalappointment_id` int(11) DEFAULT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `patientcode` varchar(15) DEFAULT NULL,
  `patientname` varchar(100) DEFAULT NULL,
  `patient_phone_number` varchar(20) DEFAULT NULL,
  `patientaddress` tinytext,
  `branch_status` int(11) DEFAULT NULL,
  `branch_status_text` varchar(25) DEFAULT NULL,
  `surgery_date` datetime DEFAULT NULL,
  `type_of_surgery` int(11) DEFAULT NULL,
  `type_of_surgeryname` varchar(250) DEFAULT NULL,
  `procedure_id` int(11) DEFAULT NULL,
  `procedure_name` tinytext,
  `isurance_id` int(11) DEFAULT NULL,
  `insurance_name` varchar(250) DEFAULT NULL,
  `individual_or_corporate` varchar(20) DEFAULT NULL,
  `corporate_employee_id` varchar(50) DEFAULT NULL,
  `healthcard_number` varchar(100) DEFAULT NULL,
  `corporate_name` varchar(250) DEFAULT NULL,
  `account_beneficiary` varchar(250) DEFAULT NULL,
  `tpa_id` varchar(100) DEFAULT NULL,
  `tpa_name` varchar(250) DEFAULT NULL,
  `relationship` varchar(250) DEFAULT NULL,
  `policy_number` varchar(250) DEFAULT NULL,
  `branch_submitted_date` datetime DEFAULT NULL,
  `branch_remarks` text,
  `claim_amount` decimal(20,0) DEFAULT NULL,
  `submitted_branch_user_id` int(11) DEFAULT NULL,
  `submitted_branch_user_name` varchar(100) DEFAULT NULL,
  `branch_inserteddate` datetime DEFAULT NULL,
  `branch_lastmodifieddate` datetime DEFAULT NULL,
  `branch_isactive` int(11) DEFAULT NULL,
  `central_preauth_date` datetime DEFAULT NULL,
  `central_modeofdocument` varchar(100) DEFAULT NULL,
  `central_document_referenceno` varchar(100) DEFAULT NULL,
  `central_status` int(11) DEFAULT NULL,
  `central_status_text` varchar(25) DEFAULT NULL,
  `central_remarks` text,
  `central_deneid_date` datetime DEFAULT NULL,
  `central_deneid_remarks_tpa` text,
  `central_submitted_userid` int(11) DEFAULT NULL,
  `central_userid` int(11) DEFAULT NULL,
  `central_inserteddate` datetime DEFAULT NULL,
  `central_lastmodifieddate` datetime DEFAULT NULL,
  `central_isactive` int(11) DEFAULT NULL,
  `central_isdeleted` int(11) DEFAULT NULL,
  `central_preauthnumber` varchar(250) DEFAULT NULL,
  `central_approveddate` datetime DEFAULT NULL,
  `central_approvedamt` float DEFAULT NULL,
  `central_copaypercent` float DEFAULT NULL,
  `central_copay` float DEFAULT NULL,
  `central_tds` float DEFAULT NULL,
  `central_balance` float DEFAULT NULL,
  `central_agreed_discount` float DEFAULT NULL,
  `documentId` int(11) DEFAULT NULL,
  `docketNumber` varchar(250) DEFAULT NULL,
  `comOrPerName` varchar(250) DEFAULT NULL,
  `surgeonName` varchar(100) DEFAULT NULL,
  `counsilingStatus` int(11) DEFAULT '0',
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_insuranceapprovaldetails`
--


--
-- Table structure for table `tbl_insurancebranch_app_db_configdetails`
--

CREATE TABLE `tbl_insurancebranch_app_db_configdetails` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `nbranchid` int(11) DEFAULT NULL,
  `branch_publicip` varchar(45) DEFAULT NULL,
  `branch_publicport` varchar(10) DEFAULT NULL,
  `branch_testimagename_path` varchar(250) DEFAULT NULL,
  `inserteddate` datetime DEFAULT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  `isActive` tinyint(4) DEFAULT NULL,
  `isDeleted` tinyint(4) DEFAULT NULL,
  `nuserid` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_insurancebranch_app_db_configdetails`
--

INSERT INTO `tbl_insurancebranch_app_db_configdetails` VALUES (1,1,'106.51.16.208','1010','http://106.51.16.208:1010/yaragoemrjavaclient/img/testImage_1kb.jpg','2018-06-26 17:11:55','2018-06-26 17:11:55',1,0,'1');

--
-- Table structure for table `tbl_insuranceclaimsubmitdetails`
--

CREATE TABLE `tbl_insuranceclaimsubmitdetails` (
  `aId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `branchId` int(11) DEFAULT NULL,
  `branch_ClaimrowId` int(11) DEFAULT NULL,
  `ipId` int(11) DEFAULT NULL,
  `opId` int(11) DEFAULT NULL,
  `centralApprovalId` int(11) DEFAULT NULL,
  `patientId` int(11) DEFAULT NULL,
  `patientCode` varchar(15) DEFAULT NULL,
  `patientName` varchar(100) DEFAULT NULL,
  `patientPhNo` varchar(20) DEFAULT NULL,
  `patientAddress` tinytext,
  `branch_Status` int(11) DEFAULT NULL,
  `branch_Status_Text` varchar(25) DEFAULT NULL,
  `branch_Submitted_Date` date DEFAULT NULL,
  `branch_submitted_userid` int(11) DEFAULT NULL,
  `branch_submitted_user_name` varchar(100) DEFAULT NULL,
  `branch_Remarks` text,
  `branch_ModeOfDocumentSent` varchar(25) DEFAULT NULL,
  `branch_DocRefNum` varchar(50) DEFAULT NULL,
  `branch_InsertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `branch_LastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `central_InsuranceStatus` int(11) DEFAULT NULL,
  `central_InsuranceStatus_Text` varchar(25) DEFAULT NULL,
  `central_SubmittedDate` date DEFAULT NULL,
  `central_ModeOfDocumentSent` varchar(25) DEFAULT NULL,
  `central_DocRefNum` varchar(25) DEFAULT NULL,
  `central_Remarks` text,
  `central_CentralDeniedDate` date DEFAULT NULL,
  `central_CentralDeniedRemarks` text,
  `central_TPAonHoldDate` date DEFAULT NULL,
  `central_TPAonHoldRemarks` text,
  `central_Submitted_UserId` int(11) DEFAULT NULL,
  `central_UserId` int(11) DEFAULT NULL,
  `central_InsertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `central_LastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `central_isActive` tinyint(4) DEFAULT '1',
  `central_isDeleted` tinyint(4) DEFAULT '0',
  `central_Payment_RecivedDate` date DEFAULT NULL,
  `central_Payment_RecivedComment` text,
  `central_Payment_ApprovedAmt` float DEFAULT NULL,
  `central_Payment_RecivedAmt` float DEFAULT NULL,
  `central_Payment_DueAmt` float DEFAULT NULL,
  `central_Payment_ModeOfPayment` varchar(20) DEFAULT NULL,
  `central_Payment_RefNum` varchar(20) DEFAULT NULL,
  `central_Payment_PaymentPaid` varchar(20) DEFAULT NULL,
  `central_Payment_PaidAmt` float DEFAULT NULL,
  `corier_com_name` varchar(250) DEFAULT NULL,
  `chequeOrNeftAmt` double DEFAULT NULL,
  `chequeOrNeftNumber` varchar(250) DEFAULT NULL,
  `chequeOrNeftDate` date DEFAULT NULL,
  `cental_Payment_TdsAmt` double DEFAULT NULL,
  `cental_Payment_deductionAmt` double DEFAULT NULL,
  `cental_Payment_DiscountAmt` double DEFAULT NULL,
  `eyeToBeOperated` varchar(45) DEFAULT NULL,
  `billDate` datetime DEFAULT NULL,
  `billNumber` varchar(150) DEFAULT NULL,
  `patientPaid` float DEFAULT '0',
  `discount` int(11) DEFAULT '0',
  `modeOfPayment` varchar(45) DEFAULT NULL,
  `cental_excess_amount` float DEFAULT '0',
  PRIMARY KEY (`aId_pk`),
  KEY `approvaldetails_idx` (`centralApprovalId`),
  CONSTRAINT `approvaldetails` FOREIGN KEY (`centralApprovalId`) REFERENCES `tbl_insuranceapprovaldetails` (`aid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_insuranceclaimsubmitdetails`
--


--
-- Table structure for table `tbl_modules`
--

CREATE TABLE `tbl_modules` (
  `aModuleId` int(11) NOT NULL AUTO_INCREMENT,
  `priority` float NOT NULL,
  `moduleName` varchar(100) DEFAULT NULL,
  `moduleNameForMobile` varchar(100) DEFAULT NULL,
  `isHavingSubModule` int(11) DEFAULT NULL,
  `isMobileOrWeb` int(11) DEFAULT NULL,
  `moduleId` int(11) DEFAULT NULL COMMENT 'Module Id refers to aModuleId of same table',
  `fnOnClick` varchar(250) NOT NULL COMMENT 'This is the function name that will be triggered (in js) on click of a link in jsp',
  `moduleIcon` varchar(45) DEFAULT NULL,
  `fnOnClickForMobile` varchar(250) DEFAULT NULL,
  `moduleIconForMobile` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  `nHospitalId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aModuleId`),
  UNIQUE KEY `moduleName_UNIQUE` (`moduleName`),
  KEY `fk_tblModules_1_idx` (`nUserId`),
  CONSTRAINT `fk_tblModules_1` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`userId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_modules`
--

INSERT INTO `tbl_modules` VALUES (1,1,'Home',NULL,1,1,NULL,'home','fa fa-shopping-cart','Flash','fa fa-shopping-cart','0000-00-00 00:00:00',NULL,1,0,1,1,1),(2,0,'VerifyAPI',NULL,1,1,NULL,'verifyapi','fa fa-shopping-cart',NULL,NULL,NULL,NULL,1,0,1,1,1),(3,2,'Insurance Approval',NULL,1,1,NULL,'insuranceapproval','fa fa-shopping-cart',NULL,NULL,NULL,NULL,1,0,1,1,1),(4,3,'Admin',NULL,1,1,NULL,'admin','insuranceapproval',NULL,NULL,NULL,NULL,1,0,1,1,1),(5,4,'Insurance Claim',NULL,1,1,NULL,'insuranceclaim','insuranceapproval',NULL,NULL,NULL,NULL,1,0,1,1,1),(6,5,'OPDInsuranceClaim',NULL,1,1,NULL,'opdInsuranceClaim','fa fa-shopping-cart',NULL,NULL,NULL,NULL,1,0,1,1,1),(7,6,'Insurance Report',NULL,1,1,NULL,'insuranceReport','fa fa-shopping-cart',NULL,NULL,NULL,NULL,1,1,1,1,1),(8,7,'Insurance Claim Summary Report',NULL,1,1,NULL,'insuranceClaimSummaryReport','fa fa-shopping-cart',NULL,NULL,NULL,NULL,1,0,1,1,1);

--
-- Table structure for table `tbl_mst_insurancetpa`
--

CREATE TABLE `tbl_mst_insurancetpa` (
  `aITPA_Id` int(11) NOT NULL AUTO_INCREMENT,
  `insuranceTPAName` varchar(100) DEFAULT NULL,
  `insuranceTypeId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `modifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(4) DEFAULT NULL,
  `agreedDiscountPerctage` float DEFAULT '0',
  `discntStartDate` date DEFAULT NULL,
  `discntEndDate` date DEFAULT NULL,
  PRIMARY KEY (`aITPA_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_insurancetpa`
--

INSERT INTO `tbl_mst_insurancetpa` VALUES (1,' Alankit Health Care TPA Ltd ',NULL,'2018-07-06 14:55:34','2018-07-06 14:55:34',1,0,NULL,NULL),(2,' Apollo Munich Health Insurance Company Ltd ',NULL,'2018-07-06 14:58:38','2018-07-06 14:58:38',1,0,NULL,NULL),(3,' Bajaj Allianz General Insurance co Ltd ',NULL,'2018-07-07 12:14:05','2018-07-07 12:14:05',1,0,NULL,NULL),(4,' Chola MS General Insurance Co. Ltd. ',NULL,'2018-07-07 12:38:08','2018-07-07 12:38:08',1,0,NULL,NULL),(5,' Dedicated Healthcare Service TPA (India) Pvt   Ltd ',NULL,'2018-07-07 12:40:49','2018-07-07 12:40:49',1,0,NULL,NULL),(6,' East West Assist Pvt Ltd. ',NULL,'2018-07-07 12:45:56','2018-07-07 12:45:56',1,0,NULL,NULL),(7,' E-meditek (TPA) Services Ltd ',NULL,'2018-07-07 12:47:11','2018-07-07 12:47:11',1,0,NULL,NULL),(8,' Family Health Plan Insurance TPA Ltd ',NULL,'2018-07-07 12:49:44','2018-07-07 12:49:44',1,0,NULL,NULL),(9,' Focus Health Insurance TPA Pvt Ltd ',NULL,'2018-07-07 12:57:06','2018-07-07 12:57:06',1,0,NULL,NULL),(10,' Future Generali India Insurance Company Ltd. ',NULL,'2018-07-07 12:58:05','2018-07-07 12:58:05',1,0,NULL,NULL),(11,' Genins India Insurance TPA Ltd. ',NULL,'2018-07-07 12:59:47','2018-07-07 12:59:47',1,0,NULL,NULL),(12,' Good Health Insurance TPA Ltd ',NULL,'2018-07-07 13:01:44','2018-07-07 13:01:44',1,0,NULL,NULL),(13,' HDFC ERGO General Insurance Company Ltd ',NULL,'2018-07-07 13:05:37','2018-07-07 13:05:37',1,0,NULL,NULL),(14,' Health India Insurance TPA Services Pvt Ltd.                                             ',NULL,'2018-07-07 13:09:25','2018-07-07 13:09:25',1,0,NULL,NULL),(15,' Health Insurance TPA of India Ltd ',NULL,'2018-07-07 13:10:38','2018-07-07 13:10:38',1,0,NULL,NULL),(16,' Heritage Health Insurance TPA Pvt Ltd ',NULL,'2018-07-07 13:13:18','2018-07-07 13:13:18',1,0,NULL,NULL),(17,' ICICI Lombard General Insurance Company Ltd ',NULL,'2018-07-07 13:14:12','2018-07-07 13:14:12',1,0,NULL,NULL),(18,' ICICI Prudential Life Insurance Company Ltd. ',NULL,'2018-07-07 13:19:21','2018-07-07 13:19:21',1,0,NULL,NULL),(19,' IFFCO TOKIO General Insurance Co TPA Pvt, Ltd ',NULL,'2018-07-07 13:29:48','2018-07-07 13:29:48',1,0,NULL,NULL),(20,' Max Bupa Health Insurance Company Ltd ',NULL,'2018-07-07 13:30:52','2018-07-07 13:30:52',1,0,NULL,NULL),(21,' MD INDIA HEALTHCARE SERVICES (P) LTD., ',NULL,'2018-07-07 13:31:33','2018-07-07 13:31:33',1,0,NULL,NULL),(22,' Medi Assist India TPA Pvt. Ltd. ',NULL,'2018-07-07 13:32:26','2018-07-07 13:32:26',1,0,NULL,NULL),(23,' Medicare TPA Services (I) Pvt Ltd ',NULL,'2018-07-07 13:33:10','2018-07-07 13:33:10',1,0,NULL,NULL),(24,' MedSave Health Insurance TPA Ltd. ',NULL,'2018-07-07 14:44:32','2018-07-07 14:44:32',1,0,NULL,NULL),(25,' Paramount Health Services & Insurance TPA Pvt. Ltd. ',NULL,'2018-07-07 17:52:51','2018-07-07 17:52:51',1,0,NULL,NULL),(26,' Park Mediclaim TPA Pvt. Ltd. ',NULL,'2018-07-07 17:59:13','2018-07-07 17:59:13',1,0,NULL,NULL),(27,' Raksha Health Insurance TPA Pvt. Ltd ',NULL,'2018-07-07 17:59:52','2018-07-07 17:59:52',1,0,NULL,NULL),(28,' Reliance General Insurance Company Ltd ',NULL,'2018-07-07 18:08:50','2018-07-07 18:08:50',1,0,NULL,NULL),(29,' Religare Health Insurance Co Ltd ',NULL,'2018-07-07 18:25:21','2018-07-07 18:25:21',1,0,NULL,NULL),(30,' Safeway Insurance Tpa Pvt Ltd ',NULL,'2018-07-10 10:51:32','2018-07-10 10:51:32',1,0,NULL,NULL),(31,' Star Health and Allied Insurance Company Ltd ',NULL,'2018-07-10 16:32:28','2018-07-10 16:32:28',1,0,NULL,NULL),(32,' United Healthcare TPA Pvt Ltd ',NULL,'2018-07-10 17:12:45','2018-07-10 17:12:45',1,0,NULL,NULL),(33,' Universal Sompo General Insurance Co Ltd, ',NULL,'2018-07-11 13:52:39','2018-07-11 13:52:39',1,0,NULL,NULL),(34,' Vidal Health Insurance TPA Pvt. Ltd. ',NULL,'2018-07-11 17:51:27','2018-07-11 17:51:27',1,0,NULL,NULL),(35,' Vipul MedCorp Insurance TPA Private Limited ',NULL,'2018-07-13 19:18:24','2018-07-13 19:18:24',1,0,NULL,NULL),(36,'ERRICSON',NULL,'2019-12-13 11:55:43','2019-12-13 11:55:43',1,0,NULL,NULL);

--
-- Table structure for table `tbl_mst_insurancetypes`
--

CREATE TABLE `tbl_mst_insurancetypes` (
  `insuid` int(11) NOT NULL AUTO_INCREMENT,
  `insucode` varchar(45) DEFAULT NULL,
  `insuname` varchar(100) DEFAULT NULL,
  `tpaname` varchar(45) DEFAULT NULL,
  `tpalogo` varchar(45) DEFAULT NULL,
  `tpaaddress` varchar(45) DEFAULT NULL,
  `tpaemail` varchar(45) DEFAULT NULL,
  `govtscheme` varchar(45) DEFAULT NULL,
  `cashless` varchar(45) DEFAULT NULL,
  `reimbursement` varchar(45) DEFAULT NULL,
  `inserteddate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastmodifieddate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nuserid` varchar(45) DEFAULT NULL,
  `maininsurance_id` int(11) DEFAULT '0',
  `insunamedescription` mediumtext,
  `schemegroup` varchar(250) DEFAULT '0',
  `nInsuranceType` int(11) DEFAULT '2' COMMENT 'nInsuranceType - Type of Insurance, if Values = 1 (OPD), 2 (IPD), 3 (Both)',
  `nGovtorPrivatePanel` int(11) DEFAULT '1' COMMENT 'nGovtorPrivatePanel - is Insurance is Private or Govt., \nif Values = 1 (Government), 2 (Private) insurances',
  `isCreditAvailable` int(11) DEFAULT '1' COMMENT 'isCreditAvailable - For Insur Credit Option is Available or not, \nif Values = 1 (Yes), 2 (No)',
  PRIMARY KEY (`insuid`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_insurancetypes`
--

INSERT INTO `tbl_mst_insurancetypes` VALUES (1,NULL,'Non-Insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',1,0,NULL,1,NULL,'1',2,1,1),(2,NULL,'CGHS',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',1,0,NULL,1,NULL,'1',2,1,1),(3,NULL,'RSBY',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',0,1,NULL,1,NULL,'1',2,1,1),(4,NULL,'Corporate',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',1,0,NULL,1,NULL,'0',2,1,1),(5,NULL,'Aditya Birla General Insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',1,0,NULL,0,NULL,'0',2,1,1),(6,NULL,'DHFL general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:21','2019-04-26 18:41:21',1,0,NULL,0,NULL,'0',2,1,1),(7,NULL,'Edelweiss',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(8,NULL,'Future generali insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(9,NULL,'L and T general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(10,NULL,'Liberty Health Insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(11,NULL,'Magma Life insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(12,NULL,'SBI general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(13,NULL,'Max life general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(14,NULL,'Royal Sundaram',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(15,NULL,'Tata AIG',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(16,NULL,'Universal Shampoo general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(17,NULL,'CIGNA TTK health insurance company ltd ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(18,NULL,'Mediassist',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(19,NULL,'FHPL',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(20,NULL,'Paramount',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(21,NULL,'Vidal',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(23,NULL,'Star Health and Allied Insurance Co.Ltd',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(24,NULL,'ICICI Lombard ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(25,NULL,'Apollo Munich ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(26,NULL,'Iffco Tokio',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(28,NULL,'Reliance general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(29,NULL,'Bharati AXA general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(32,NULL,'India First life insurance co. Ltd',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(33,NULL,'Bajaj Allianz general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(34,NULL,'Sbi general insiurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(35,NULL,'Liberty videocon general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(36,NULL,'Reliance nippo life insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(37,NULL,'Aviva life insurance company ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(38,NULL,'Tata Aig general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(39,NULL,'Birla sunlife insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(40,NULL,'SBI life insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:22','2019-04-26 18:41:22',1,0,NULL,0,NULL,'0',2,1,1),(41,NULL,'PNB metlife india insurance ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(42,NULL,'Life insurance corporation of india',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(43,NULL,'Chola mandalam MS general insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(44,NULL,'Apollo Munich Health Insurance ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(45,NULL,'Bharti AXA insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(46,NULL,'HDFC ERGO',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(47,NULL,'United India Insurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-04-26 18:41:23','2019-04-26 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(48,NULL,'The New India Assurance Co.LTD.',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-05-13 18:41:23','2019-05-13 18:41:23',1,0,NULL,0,NULL,'0',2,1,1),(49,NULL,'MediCount HealthCare PVT LTD',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-08-30 13:20:23','2019-08-30 13:20:23',1,0,NULL,0,NULL,'0',2,1,1),(50,NULL,'GIPSA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-09-16 13:20:23','2019-09-16 13:20:23',1,0,NULL,0,NULL,'0',2,1,1),(51,NULL,'The New India Assurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-09-16 13:20:23','2019-09-16 13:20:23',1,0,NULL,0,NULL,'0',2,1,1),(52,NULL,'United India Assurance',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-09-16 13:20:23','2019-09-16 13:20:23',1,0,NULL,0,NULL,'0',2,1,1),(53,NULL,'National Insurance co ltd',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-09-16 13:20:23','2019-09-16 13:20:23',1,0,NULL,0,NULL,'0',2,1,1),(54,NULL,'The Oriental Insurance co ltd',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-09-16 13:20:23','2019-09-16 13:20:23',1,0,NULL,0,NULL,'0',2,1,1);

--
-- Table structure for table `tbl_opdinsurance_claimdetails_incentral`
--

CREATE TABLE `tbl_opdinsurance_claimdetails_incentral` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `npatientid` int(11) DEFAULT NULL,
  `nappointmentid` int(11) DEFAULT NULL,
  `nbillid` int(11) DEFAULT NULL,
  `nbillautoid` int(11) DEFAULT NULL,
  `statuss` int(11) DEFAULT NULL,
  `statustext` varchar(25) DEFAULT NULL,
  `pending_actiondate` datetime DEFAULT NULL,
  `pending_submitteddate` datetime DEFAULT NULL,
  `pending_remarks` text,
  `modeofdoc` int(11) DEFAULT NULL,
  `modeofdoctext` varchar(25) DEFAULT NULL,
  `receiveddate` datetime DEFAULT NULL,
  `receivedamount` float DEFAULT NULL,
  `modeofpayment` varchar(25) DEFAULT NULL,
  `referencenum` tinytext,
  `dueamt` float DEFAULT NULL,
  `submitted_remarks` text,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `nuserid` int(11) DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `bdeleted` int(11) DEFAULT NULL,
  `submitted_status` int(11) DEFAULT NULL,
  `submitted_date` datetime DEFAULT NULL,
  `submitted_text` text,
  `branch_userid` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `central_submited_remarks` text,
  `PatientName` varchar(250) DEFAULT NULL,
  `OpdPAnelNAme` text,
  `CardNumber` varchar(45) DEFAULT NULL,
  `CardHolderName` varchar(45) DEFAULT NULL,
  `PermissLetternum` varchar(45) DEFAULT NULL,
  `mrdNo` varchar(45) DEFAULT NULL,
  `billAmount` float DEFAULT NULL,
  `billDate` date DEFAULT NULL,
  `ationDate` date DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_opdinsurance_claimdetails_incentral`
--


--
-- Table structure for table `tbl_user`
--

CREATE TABLE `tbl_user` (
  `userId` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(100) NOT NULL,
  `password` varchar(250) NOT NULL,
  `nSilkIndustryId` int(11) DEFAULT NULL,
  `imageName` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `isloggedin` int(11) DEFAULT NULL,
  `nHospitalId` int(11) DEFAULT NULL,
  `nBranchId` varchar(250) DEFAULT NULL,
  `isWebOrMobileUser` int(11) DEFAULT NULL,
  `IsCentralOrBranchUser` int(11) DEFAULT NULL,
  `sessionid` tinytext,
  `authkey` tinytext,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_user`
--

INSERT INTO `tbl_user` VALUES (1,'admin','a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3',1,NULL,NULL,'2022-05-20 00:00:00',1,0,1,1,'1',1,1,NULL,NULL);

--
-- Table structure for table `tbl_user_module_map`
--

CREATE TABLE `tbl_user_module_map` (
  `aModuleMapId` int(11) NOT NULL AUTO_INCREMENT,
  `nUserId` int(11) DEFAULT NULL,
  `nModuleId` int(11) DEFAULT NULL,
  `marketid` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `bDeleted` int(11) DEFAULT NULL,
  `nLoggedInUserId` int(11) DEFAULT NULL,
  `nHospitalId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aModuleMapId`),
  KEY `fk_tblUserModuleMap_1_idx` (`nModuleId`),
  KEY `fk_tblUserModuleMap_2_idx` (`nUserId`),
  KEY `fk_tblUserModuleMap_3_idx` (`nLoggedInUserId`),
  CONSTRAINT `fk_tblUserModuleMap_1` FOREIGN KEY (`nModuleId`) REFERENCES `tbl_modules` (`aModuleId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tblUserModuleMap_2` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`userId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tblUserModuleMap_3` FOREIGN KEY (`nLoggedInUserId`) REFERENCES `tbl_user` (`userId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_user_module_map`
--

INSERT INTO `tbl_user_module_map` VALUES (1,1,1,NULL,NULL,NULL,0,0,1,1,1),(2,1,2,NULL,NULL,NULL,0,0,1,1,1),(3,1,3,NULL,NULL,NULL,1,0,1,1,1),(4,1,4,NULL,NULL,NULL,1,0,1,1,1),(5,1,5,NULL,NULL,NULL,1,0,1,1,1),(6,1,6,NULL,NULL,NULL,0,0,1,1,1),(8,1,7,NULL,NULL,NULL,1,0,1,1,1);

--
-- Table structure for table `tbl_user_profiledetails`
--

CREATE TABLE `tbl_user_profiledetails` (
  `userpriId` int(11) NOT NULL AUTO_INCREMENT,
  `userfullname` varchar(100) NOT NULL,
  `mobileNumber` varchar(100) DEFAULT NULL,
  `address` text,
  `employeId` varchar(100) DEFAULT NULL,
  `emailId` varchar(100) DEFAULT NULL,
  `userhospetalbranches` text,
  `userModules` text,
  `userId` int(11) DEFAULT NULL,
  `isbranchORcentraluser` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `userprefix` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`userpriId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_user_profiledetails`
--


--
-- Table structure for table `tblhospitalbranches`
--

CREATE TABLE `tblhospitalbranches` (
  `abranchId` int(11) NOT NULL AUTO_INCREMENT,
  `branchHospName` varchar(1000) DEFAULT NULL,
  `bhospitalOwnerName` varchar(250) DEFAULT NULL,
  `bhospitalAddress` varchar(1000) DEFAULT NULL,
  `bhospitalCity` varchar(100) DEFAULT NULL,
  `bhospitalState` varchar(100) DEFAULT NULL,
  `bhospitalCountry` varchar(100) DEFAULT NULL,
  `bPincode` varchar(50) DEFAULT NULL,
  `bhospitalPhNo` varchar(250) DEFAULT NULL,
  `bhospitalEmergencyNo` varchar(250) DEFAULT NULL,
  `StartTime` time DEFAULT NULL,
  `EndTime` time DEFAULT NULL,
  `WeeklyOff` varchar(250) DEFAULT NULL,
  `branchActive` int(11) DEFAULT NULL,
  `nhospitalId` int(11) DEFAULT NULL,
  `nhospitalActive` int(11) DEFAULT NULL,
  `tEmailId` varchar(250) DEFAULT NULL,
  `nbranchcode` varchar(250) DEFAULT NULL,
  `regNo` varchar(45) DEFAULT NULL,
  `PANNo` varchar(45) DEFAULT NULL,
  `distCode` varchar(45) DEFAULT NULL,
  `bhospitalDistrict` varchar(50) DEFAULT NULL,
  `bGSTINno` varchar(45) DEFAULT NULL,
  `st_number` varchar(50) DEFAULT NULL,
  `cin_number` varchar(50) DEFAULT NULL,
  `rohini_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`abranchId`),
  KEY `nhospitalId` (`nhospitalId`),
  CONSTRAINT `tblhospitalbranches_ibfk_1` FOREIGN KEY (`nhospitalId`) REFERENCES `tblhospitalsdetails` (`aHospitalId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblhospitalbranches`
--

INSERT INTO `tblhospitalbranches` VALUES (1,'YaraGo One','Dr ','Shop No 9 - 10Twin Tower, Tirupati Nagar Phase II, Tirupati Nagar, Virar West - 401303','1','1','1','401303','9017011623','','08:00:00','20:00:00','',1,1,1,' iec1002@infigoeye.com','10020000','','','10020000','0',NULL,'','','');

--
-- Table structure for table `tblhospitalsdetails`
--

CREATE TABLE `tblhospitalsdetails` (
  `aHospitalId` int(11) NOT NULL AUTO_INCREMENT,
  `hospitalName` varchar(500) DEFAULT NULL,
  `hospitalOwnerName` varchar(250) DEFAULT NULL,
  `hospitalAddress` varchar(1000) DEFAULT NULL,
  `hospitalCity` varchar(100) DEFAULT NULL,
  `hospitalState` varchar(100) DEFAULT NULL,
  `hospitalCountry` varchar(100) DEFAULT NULL,
  `Pincode` varchar(50) DEFAULT NULL,
  `hospitalPhNo` varchar(250) DEFAULT NULL,
  `hospitalEmergencyNo` varchar(250) DEFAULT NULL,
  `StartTime` varchar(500) DEFAULT NULL,
  `EndTime` varchar(500) DEFAULT NULL,
  `WeeklyOff` varchar(250) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  `hospitalEmail` varchar(500) DEFAULT NULL,
  `hospitalType` varchar(500) DEFAULT NULL,
  `hospitalNameBarcode` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`aHospitalId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblhospitalsdetails`
--

INSERT INTO `tblhospitalsdetails` VALUES (1,'YaraGo ','Dr','Bangalore',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL);

--
-- Dumping events for database 'yginsurance'
--

--
-- Dumping routines for database 'yginsurance'
--
/*!50003 DROP PROCEDURE IF EXISTS `sproc_check_username_availability` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_check_username_availability`(
in inuserName varchar(200))
BEGIN

SELECT 
    userName AS insureseusername, userId
FROM
    tbl_user
WHERE
    userName LIKE inuserName
LIMIT 0 , 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_CreateNewInsuranceMaster` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_CreateNewInsuranceMaster`(
In_insuranceCode TEXT,
In_insuranceName TEXT,
In_insuranceType INT,
In_EMR INT

)
BEGIN

INSERT INTO tbl_mst_insurancetypes
(
insucode,
insuname,
nInsuranceType
)
VALUES 
(
In_insuranceCode,
In_insuranceName,
In_insuranceType
);




END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_create_insurence_user` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_create_insurence_user`(

in userFullName varchar(100),
in userMobileNumber varchar(100),
in userAddress text,
in employeId varchar(100),
in emailId varchar(100),
in username varchar(100),
in password1 varchar(250),
in confirmPassword varchar(250),
in nameprific varchar(20),
in branchvalue int(11),
in modulesIds text,
in hospetalBranches text,
out userAutoId int(11)

)
BEGIN


insert into tbl_user(
userName,
`password`,
insertedDate,
lastModifiedDate,
isActive,
isDeleted,
IsCentralOrBranchUser,
nBranchId,
isWebOrMobileUser

)
values(
username,
password1,
now(),
now(),
1,
0,
branchvalue,
hospetalBranches,
1
);
set userAutoId = LAST_INSERT_ID(); 
-- select userAutoId;

insert into tbl_user_profiledetails(
userfullname,
mobileNumber,
address,
employeId,
emailId,
userhospetalbranches,
userModules,
userId,
isbranchORcentraluser,
insertedDate,
lastModifiedDate,
isActive,
isDeleted,
userprefix
)values(
userFullName,
userMobileNumber,
userAddress,
employeId,
emailId,
hospetalBranches,
modulesIds,
userAutoId,
branchvalue,
now(),
now(),
1,
0,
nameprific
);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getActiveInsuranceTPAList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getActiveInsuranceTPAList`()
BEGIN
SELECT 
    insuid, insuname
FROM
    tbl_mst_insurancetypes
WHERE
    isActive = 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_GetExitInsurance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_GetExitInsurance`(
In_insuranceCode TEXT,
In_insuranceName TEXT,
In_insuranceType INT

)
BEGIN

SET @insucode = (SELECT COUNT(*) AS insucode FROM tbl_mst_insurancetypes WHERE nInsuranceType = In_insuranceType AND insucode = In_insuranceCode);

SET @insuname = (SELECT COUNT(*) AS insuname FROM tbl_mst_insurancetypes WHERE nInsuranceType = In_insuranceType AND insuname = In_insuranceName);
 

SELECT (@insucode + @insuname) AS COUNT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getHospetalBrachList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getHospetalBrachList`()
BEGIN
SELECT 
    abranchId AS branchId, branchHospName AS branchHospName
FROM
    tblhospitalbranches
WHERE
    branchActive = 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getinsuracne_claim_compdetail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getinsuracne_claim_compdetail`(
in centralclaimautorowid int
)
BEGIN
SELECT 
    t1.*,
    t2.patient_id,
    t2.patientcode,
    t2.patientname,
    t2.type_of_surgeryname,
    t2.procedure_name,
    t2.insurance_name,
    ifnull(date(t2.central_approveddate), '') appr_central_approval_approveddate,
	ifnull(t2.central_preauthnumber, '') appr_central_preauthnumber,
	ifnull(t2.central_approvedamt, 0) appr_central_approvedamt,
	ifnull(t2.central_copaypercent, 0) appr_central_copaypercent,
	ifnull(t2.central_copay, 0) appr_central_copay,
	ifnull(t2.central_tds, 0) appr_central_tds,
	ifnull(t2.central_balance, 0) appr_central_balance,
	ifnull(t2.central_agreed_discount, 0) appr_central_agreed_discount,
    ifnull(t2.central_modeofdocument, '') central_modeofdocument_approval,
    t3.branchHospName
FROM
    (SELECT 
        aId_pk,
            branchId,
            centralApprovalId,
            branch_Status,
            branch_Status_Text,
            branch_submitted_userid,
            branch_submitted_user_name,
            ifnull(date(branch_Submitted_Date), '') branch_Submitted_Date,
            ifnull(branch_Remarks, '') branch_Remarks,
            ifnull(central_InsuranceStatus, '' ) central_InsuranceStatus,
            ifnull(central_InsuranceStatus_Text, '') central_InsuranceStatus_Text,
            ifnull(date(central_SubmittedDate), '') central_SubmittedDate,
			ifnull(central_ModeOfDocumentSent, '') central_ModeOfDocumentSent,
			ifnull(central_DocRefNum, '') central_DocRefNum,
			ifnull(central_Remarks, '') central_Remarks,
			ifnull(date(central_CentralDeniedDate), '') central_CentralDeniedDate,
			ifnull(central_CentralDeniedRemarks, '') central_CentralDeniedRemarks,
			ifnull(date(central_TPAonHoldDate), '') central_TPAonHoldDate,
			ifnull(central_TPAonHoldRemarks, '') central_TPAonHoldRemarks,
			ifnull(central_Submitted_UserId, '') central_Submitted_UserId,
			ifnull(date(central_Payment_RecivedDate), '') central_Payment_RecivedDate,
			ifnull(central_Payment_RecivedComment, 0) central_Payment_RecivedComment,
			ifnull(central_Payment_ApprovedAmt, 0) central_Payment_ApprovedAmt,
			ifnull(central_Payment_DueAmt, 0) central_Payment_DueAmt,
			ifnull(central_Payment_ModeOfPayment, '') central_Payment_ModeOfPayment,
			ifnull(central_Payment_RefNum, '') central_Payment_RefNum,
			ifnull(central_Payment_PaymentPaid, '') central_Payment_PaymentPaid,
			ifnull(central_Payment_PaidAmt, 0) central_Payment_PaidAmt ,
            ifnull(patientPaid, 0) patientPaid,
			ifnull(discount, 0) discount ,
		    ifnull(modeOfPayment, '') modeOfPayment
    FROM
        tbl_insuranceclaimsubmitdetails
    WHERE
        aId_pk = centralclaimautorowid ) t1
        JOIN
		tbl_insuranceapprovaldetails t2 ON t2.aid = t1.centralApprovalId
        JOIN
        tblhospitalbranches t3 ON t3.abranchId = t2.branch_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getinsuracne_pending_apporval_compdetail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getinsuracne_pending_apporval_compdetail`(
in aprovalid int
)
BEGIN
SELECT 
    IFNULL(t1.aid, '') aid,
    IFNULL(t1.branch_id, '') branch_id,
    IFNULL(t1.patient_id, '') patient_id,
    IFNULL(t1.patientcode, '') patientcode,
    IFNULL(t1.patientname, '') patientname,
    IFNULL(DATE(t1.surgery_date), '') surgery_date,
    IFNULL(t1.type_of_surgeryname, '') type_of_surgeryname,
    IFNULL(t1.procedure_name, '') procedure_name,
    IFNULL(t1.isurance_id, '') isurance_id,
    IFNULL(t1.insurance_name, '') insurance_name,
    IFNULL(t1.individual_or_corporate, '') individual_or_corporate,
    IFNULL(t1.corporate_employee_id, '') corporate_employee_id,
    IFNULL(t1.healthcard_number, '') healthcard_number,
    IFNULL(t1.corporate_name, '') corporate_name,
    IFNULL(t1.account_beneficiary, '') account_beneficiary,
    IFNULL(t1.tpa_id, '') tpa_id,
    IFNULL(t1.tpa_name, '') tpa_name,
    IFNULL(t1.relationship, '') relationship,
    IFNULL(t1.policy_number, '') policy_number,
    IFNULL(DATE(t1.branch_submitted_date), '') branch_submitted_date,
    IFNULL(t1.branch_remarks, '') branch_remarks,
    IFNULL(t1.claim_amount, '') claim_amount,
    IFNULL(t1.submitted_branch_user_name, '') submitted_branch_user_name,
    t2.branchHospName,
    ifnull(t1.central_remarks, '') central_remarks,
	ifnull(t1.central_preauthnumber, '') central_preauthnumber,
    ifnull(date(t1.central_approveddate), '') central_approveddate,
    ifnull(t1.central_approvedamt, '') central_approvedamt,
    ifnull(t1.central_copaypercent, '') central_copaypercent,
    ifnull(t1.central_copay, '') central_copay,
    ifnull(t1.central_tds, '') central_tds,
    ifnull(t1.central_balance, '') central_balance,
    ifnull(t1.central_agreed_discount, '') central_agreed_discount,
    ifnull(t1.central_modeofdocument, '') central_modeofdocument,
    ifnull(t1.central_document_referenceno, '') central_document_referenceno,
    ifnull(date(t1.central_deneid_date), '') central_deneid_date,
    ifnull(t1.central_deneid_remarks_tpa, '') central_deneid_remarks_tpa,
        ifnull(documentId,0) as documentId,
	ifnull(docketNumber,'') as docketNumber,
	ifnull(comOrPerName,'') as comOrPerName

FROM
    tbl_insuranceapprovaldetails t1
        JOIN
    tblhospitalbranches t2 ON t1.aid = aprovalid
        AND t2.abranchId = t1.branch_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getInsuranceClaimSummaryDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getInsuranceClaimSummaryDetails`(
in fromDate varchar(25),
in toDate varchar(25),
in in_BranchId int
)
BEGIN



SELECT 
TBLIAD.insurance_name as  insuranceName,
(
SELECT  
    COUNT(*) AS count

   FROM  
    tbl_insuranceapprovaldetails TBLIADD
      inner   JOIN 
    tbl_insuranceclaimsubmitdetails TBLISD ON TBLISD.centralApprovalId = TBLIADD.aid
    
    where TBLIADD.branch_id = in_BranchId
        AND DATE(TBLISD.billDate) BETWEEN DATE(fromDate) AND DATE(toDate) and  TBLIADD.isurance_id= TBLIAD.isurance_id 

) as dichargedCount ,
sum(if(isnull(TBLISD.central_InsuranceStatus_Text),1,0)) as notSubmittedCount ,
sum(if(TBLISD.central_InsuranceStatus_Text = 'Document Submitted',1,0)) as submittedCount,
sum(if(TBLISD.central_InsuranceStatus_Text = 'Payment Completed',1,0)) as compltedCount,
sum(if(TBLISD.central_InsuranceStatus_Text = 'Payment On-Hold',1,0)) as onHoldCount ,
 
sum(ifnull(TBLISD.central_Payment_ApprovedAmt,0)) as  expectedAmount,
sum(ifnull(TBLISD.chequeOrNeftAmt,0)) as  receivedAmount, 
sum(ifnull(TBLISD.central_Payment_DueAmt,0)) as  dueAmount,
    TBLIAD.isurance_id as  insuranceId

FROM  
    tbl_insuranceapprovaldetails TBLIAD
      inner   JOIN 
    tbl_insuranceclaimsubmitdetails TBLISD ON TBLISD.centralApprovalId = TBLIAD.aid
    
    where TBLIAD.branch_id = in_BranchId
        AND (DATE(TBLISD.billDate) BETWEEN DATE(fromDate) AND DATE(toDate))
GROUP BY TBLIAD.isurance_id ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getModuleDetailsForAdmin` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getModuleDetailsForAdmin`()
BEGIN
SELECT 
    aModuleId AS moduleId, moduleName AS moduleName
FROM
    tbl_modules
WHERE
    isActive = 1;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getOpdCreditDetailsToCentralForPending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getOpdCreditDetailsToCentralForPending`(
in branchIds text,
in searchid int,
in searchparam text
)
BEGIN 

SELECT 
    tocic.*,thb.branchHospName
    
FROM
    tbl_opdinsurance_claimdetails_incentral tocic
        LEFT JOIN
    tblhospitalbranches thb ON tocic.branch_id = thb.abranchId
WHERE
    tocic.submitted_status > 0  and tocic.statuss is null and 
    
    if(searchid=0,find_in_set(tocic.branch_id,branchIds),if(searchid=1,mrdNo like searchparam,PatientName like concat('%',searchparam,'%')))
    
;
    
  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getOpdCreditDetailsToCentralForSubmited` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getOpdCreditDetailsToCentralForSubmited`(
in statusId int(11),
in branchIds text,
in searchid int,
in searchparam text
)
BEGIN 

SELECT 
    tocic.*,thb.branchHospName,date(tocic.receiveddate) as receiveddatefromsubmit
    
FROM
    tbl_opdinsurance_claimdetails_incentral tocic
        LEFT JOIN
    tblhospitalbranches thb ON tocic.branch_id = thb.abranchId
WHERE
    tocic.submitted_status > 0  and tocic.statuss = statusId and 
        if(searchid=0,find_in_set(tocic.branch_id,branchIds),if(searchid=1,mrdNo like searchparam,PatientName like concat('%',searchparam,'%')));
    
  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_credit_billing_pening_firstlist` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_credit_billing_pening_firstlist`()
BEGIN
SELECT 
    SUM(IFNULL(t1.dueamt, 0)) AS pendingAmt,
    COUNT(t1.dueamt) pendingCases,
    DATE(MIN(t1.receiveddate)) oldestPendingDate,
    t1.OpdPAnelNAme
FROM
    tbl_opdinsurance_claimdetails_incentral t1
    where IFNULL(t1.dueamt, 0) > 0
GROUP BY t1.OpdPAnelNAme;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_credit_billing_pening_secondlist` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_credit_billing_pening_secondlist`()
BEGIN
SELECT 
    DATE(MAX(t1.receiveddate)) latestReceivedDate,
    t1.OpdPAnelNAme
FROM
    tbl_opdinsurance_claimdetails_incentral t1
GROUP BY t1.OpdPAnelNAme;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceapproval_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insuranceapproval_details`(
in branchids text,
in searchid int,
in searchparam text
)
BEGIN
select t1.*, t2.branchHospName from(
SELECT 
    aid,
    branch_id,
    patient_id,
    IFNULL(patientcode, '') patientcode,
    IFNULL(patientname, '') patientname,
    IFNULL(patient_phone_number, '') patient_phone_number,
    IFNULL(patientaddress, '') patientaddress,
    IFNULL(date(surgery_date),'') surgery_date,
    type_of_surgeryname,
    procedure_name,
    insurance_name, 
    tpa_name,
    DATE(branch_submitted_date) branch_submitted_date,
    ifnull(central_status_text, '') central_status,
    ifnull(branch_status_text, '') branch_status_text,
    ifnull(surgeonName, '') surgeonName,
    counsilingStatus
FROM
    tbl_insuranceapprovaldetails
WHERE
    branch_status in(1,2) and branch_isactive = 1
        AND (central_status_text IS NULL || central_status_text = 'Denied_CentralUser')
        -- AND find_in_set(branch_id, branchids)
        AND IF(searchid = 0, 
				FIND_IN_SET(branch_id, branchids),  
                IF(searchid = 1, patientcode like searchparam,  if(searchid = 2,patientname LIKE CONCAT('%', searchparam, '%'),DATE(surgery_date) = DATE(searchparam)))
                )
        LIMIT 1500) t1
join tblhospitalbranches t2 on t2.abranchId = t1.branch_id
ORDER BY aid DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceapproval_details_forapporvaltab` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insuranceapproval_details_forapporvaltab`(
in branchids text,
in searchid int,
in searchparam text
)
BEGIN
select t1.*, t2.branchHospName from(
SELECT 
    aid,
    branch_id,
    patient_id,
    IFNULL(patientcode, '') patientcode,
    IFNULL(patientname, '') patientname,
    IFNULL(patient_phone_number, '') patient_phone_number,
    IFNULL(patientaddress, '') patientaddress,
    IFNULL(date(surgery_date), '') surgery_date,
    type_of_surgeryname,
    procedure_name,
    insurance_name,
    tpa_name,
    DATE(branch_submitted_date) branch_submitted_date,
    ifnull(central_status_text, '') central_status,
    ifnull(branch_status_text, '') branch_status_text,
    ifnull(surgeonName, '') surgeonName ,
    counsilingStatus
FROM
    tbl_insuranceapprovaldetails
WHERE
    branch_status in(1,2) and branch_isactive = 1
        AND central_status_text = 'Sent for Approval'
      --  AND find_in_set(branch_id, branchids)
       AND IF(searchid = 0, 
				FIND_IN_SET(branch_id, branchids), 
                IF(searchid = 1, patientcode like searchparam, if(searchid = 2,patientname LIKE CONCAT('%', searchparam, '%'),DATE(surgery_date) = DATE(searchparam)))
                )
        LIMIT 50) t1
join tblhospitalbranches t2 on t2.abranchId = t1.branch_id
ORDER BY aid DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceapproved_details_forapporedltab` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insuranceapproved_details_forapporedltab`(
in branchids text,
in searchid int,
in searchparam text

)
BEGIN
select t1.*, t2.branchHospName from(
SELECT 
    aid,
    branch_id,
    patient_id,
    IFNULL(patientcode, '') patientcode,
    IFNULL(patientname, '') patientname,
    IFNULL(patient_phone_number, '') patient_phone_number,
    IFNULL(patientaddress, '') patientaddress,
    IFNULL(date(surgery_date), '') surgery_date,
    type_of_surgeryname,
    procedure_name,
    insurance_name,
    tpa_name,
    DATE(branch_submitted_date) branch_submitted_date,
    ifnull(central_status_text, '') central_status,
    ifnull(branch_status_text, '') branch_status_text,
    ifnull(DATE(central_approveddate), '') central_approveddate,
    ifnull(surgeonName, '') surgeonName ,
    counsilingStatus
FROM
    tbl_insuranceapprovaldetails
WHERE
    branch_status in(1,2)
        AND central_status_text = 'Approved'
     --   AND find_in_set(branch_id, branchids)
      AND IF(searchid = 0, 
				FIND_IN_SET(branch_id, branchids), 
                IF(searchid = 1, patientcode like searchparam, if(searchid = 2,patientname LIKE CONCAT('%', searchparam, '%'),DATE(surgery_date) = DATE(searchparam)))
                )
        LIMIT 50) t1
join tblhospitalbranches t2 on t2.abranchId = t1.branch_id
ORDER BY aid DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceclaim_details_complete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insuranceclaim_details_complete`(
in searchid int,
in searchparam text,
in branchids tinytext
)
BEGIN
SELECT 
    t1.*,
    t2.patient_id,
    t2.patientcode,
    t2.patientname,
    t2.type_of_surgeryname,
    t2.procedure_name, 
    t2.insurance_name,
    ifnull(date(t2.central_approveddate), '') central_approval_approveddate,
    t3.branchHospName
FROM
    (SELECT 
        aId_pk,
            branchId,
            centralApprovalId,
            branch_Status,
            branch_Status_Text,
            branch_submitted_userid,
            branch_submitted_user_name,
            ifnull(branch_Remarks, '') branch_Remarks,
            ifnull(central_InsuranceStatus, '' ) central_InsuranceStatus,
            ifnull(central_InsuranceStatus_Text, '') central_InsuranceStatus_Text,
            ifnull(date(central_SubmittedDate), '') central_SubmittedDate,
            ifnull(date(central_CentralDeniedDate), '') central_CentralDeniedDate,
            patientPaid  ,
            discount  ,
            ifnull(modeOfPayment,'') as  modeOfPayment
		FROM
        tbl_insuranceclaimsubmitdetails
    WHERE
        branch_status IN (1,2)
            AND (central_InsuranceStatus_Text = 'Payment Completed' || central_InsuranceStatus_Text = 'Payment NotCompleted' || central_InsuranceStatus_Text = 'Payment Re-Completed')
            AND IF(searchid = 0, FIND_IN_SET(branchId, branchids), if(searchid = 3,DATE(billDate) = DATE(searchparam),true))
    LIMIT 100000) t1
        JOIN
		tbl_insuranceapprovaldetails t2 ON t2.aid = t1.centralApprovalId
			AND IF(searchid <> 0 and searchid <> 3,
			IF(searchid = 1, t2.patientcode like searchparam, t2.patientname LIKE CONCAT('%', searchparam, '%')),
			'1=1')
        JOIN
        tblhospitalbranches t3 ON t3.abranchId = t2.branch_id
	ORDER BY t1.aId_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceclaim_details_onhold` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insuranceclaim_details_onhold`(
in searchid int,
in searchparam text,
in branchids tinytext
)
BEGIN
SELECT
    t1.*,
    t2.patient_id,
    t2.patientcode,
    t2.patientname,
    t2.type_of_surgeryname,
    t2.procedure_name,
    t2.insurance_name,
    ifnull(date(t2.central_approveddate), '') central_approval_approveddate,
    t3.branchHospName
FROM
    (SELECT
        aId_pk,
            branchId,
            centralApprovalId,
            branch_Status,
            branch_Status_Text,
            branch_submitted_userid,
            branch_submitted_user_name,
            ifnull(branch_Remarks, '') branch_Remarks,
            ifnull(central_InsuranceStatus, '' ) central_InsuranceStatus,
            ifnull(central_InsuranceStatus_Text, '') central_InsuranceStatus_Text,
            ifnull(date(central_SubmittedDate), '') central_SubmittedDate,
            patientPaid  ,
            discount  ,
            ifnull(modeOfPayment,'') as  modeOfPayment
    FROM
        tbl_insuranceclaimsubmitdetails
    WHERE
        branch_status IN (1,2)
            AND (central_InsuranceStatus_Text = 'Payment On-Hold')
            AND IF(searchid = 0, FIND_IN_SET(branchId, branchids), if(searchid = 3,DATE(billDate) = DATE(searchparam),true))
    LIMIT 500) t1
        JOIN
tbl_insuranceapprovaldetails t2 ON t2.aid = t1.centralApprovalId
AND IF(searchid <> 0 and searchid <> 3,
IF(searchid = 1, t2.patientcode like searchparam, t2.patientname LIKE CONCAT('%', searchparam, '%')),
'1=1')
        JOIN
        tblhospitalbranches t3 ON t3.abranchId = t2.branch_id
ORDER BY t1.aId_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceclaim_details_pending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insuranceclaim_details_pending`(
in searchid int,
in searchparam text,
in branchids tinytext
)
BEGIN
SELECT 
    t1.*,
    t2.patient_id,
    t2.patientcode,
    t2.patientname,
    t2.type_of_surgeryname,
    t2.procedure_name,
    t2.insurance_name,
    ifnull(date(t2.central_approveddate), '') central_approval_approveddate,
    t3.branchHospName
FROM
    (SELECT 
        aId_pk,
            branchId,
            centralApprovalId,
            branch_Status,
            branch_Status_Text,
            branch_submitted_userid,
            branch_submitted_user_name,
            ifnull(branch_Remarks, '') branch_Remarks,
            ifnull(central_InsuranceStatus, '' ) central_InsuranceStatus,
            ifnull(central_InsuranceStatus_Text, '') central_InsuranceStatus_Text ,
            patientPaid  ,
            discount  ,
            ifnull(modeOfPayment,'') as  modeOfPayment
    FROM
        tbl_insuranceclaimsubmitdetails
    WHERE 
        branch_status IN (1,2)
            AND (central_InsuranceStatus IS NULL || central_InsuranceStatus_Text = 'Denied_CentralUser')
            AND IF(searchid = 0, FIND_IN_SET(branchId, branchids), if(searchid = 3,DATE(billDate) = DATE(searchparam),true))  ORDER BY aId_pk DESC limit 100000
  ) t1
        JOIN
		tbl_insuranceapprovaldetails t2 ON t2.aid = t1.centralApprovalId
			AND IF(searchid <> 0 and searchid <> 3,
			IF(searchid = 1, t2.patientcode like searchparam, t2.patientname LIKE CONCAT('%', searchparam, '%')),
			'1=1')
        JOIN
        tblhospitalbranches t3 ON t3.abranchId = t2.branch_id;
	-- ORDER BY t1.aId_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insuranceclaim_details_submitted` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insuranceclaim_details_submitted`(
in searchid int,
in searchparam text,
in branchids tinytext
)
BEGIN
SELECT 
    t1.*,
    t2.patient_id,
    t2.patientcode,
    t2.patientname,
    t2.type_of_surgeryname,
    t2.procedure_name,
    t2.insurance_name,
    ifnull(date(t2.central_approveddate), '') central_approval_approveddate,
    t3.branchHospName
FROM
    (SELECT 
        aId_pk,
            branchId,
            centralApprovalId,
            branch_Status,
            branch_Status_Text,
            branch_submitted_userid,
            branch_submitted_user_name,
            ifnull(branch_Remarks, '') branch_Remarks,
            ifnull(central_InsuranceStatus, '' ) central_InsuranceStatus,
            ifnull(central_InsuranceStatus_Text, '') central_InsuranceStatus_Text,
            patientPaid  ,
            discount  ,
            ifnull(modeOfPayment,'') as  modeOfPayment
    FROM
        tbl_insuranceclaimsubmitdetails
    WHERE
        branch_status IN (1,2)
            AND (central_InsuranceStatus_Text = 'Document Submitted' || central_InsuranceStatus_Text = 'Document Re-Submitted')
            AND IF(searchid = 0, FIND_IN_SET(branchId, branchids), if(searchid = 3,DATE(billDate) = DATE(searchparam),true))
    LIMIT 500) t1
        JOIN
		tbl_insuranceapprovaldetails t2 ON t2.aid = t1.centralApprovalId
			AND IF(searchid <> 0 and searchid <> 3,
			IF(searchid = 1, t2.patientcode like searchparam, t2.patientname LIKE CONCAT('%', searchparam, '%')),
			'1=1')
        JOIN
        tblhospitalbranches t3 ON t3.abranchId = t2.branch_id
	ORDER BY t1.aId_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurancedeneid_details_fordeneidtab` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insurancedeneid_details_fordeneidtab`(
in branchids text,
in searchid int,
in searchparam text
)
BEGIN
select t1.*, t2.branchHospName from(
SELECT 
    aid,
    branch_id,
    patient_id,
    IFNULL(patientcode, '') patientcode,
    IFNULL(patientname, '') patientname,
    IFNULL(patient_phone_number, '') patient_phone_number,
    IFNULL(patientaddress, '') patientaddress,
    IFNULL(date(surgery_date), '') surgery_date,
    type_of_surgeryname,
    procedure_name,
    insurance_name,
    tpa_name,
    DATE(branch_submitted_date) branch_submitted_date,
    ifnull(central_status_text, '') central_status,
    ifnull(branch_status_text, '') branch_status_text,
    ifnull(DATE(central_deneid_date), '') central_deneid_date,
    ifnull(surgeonName, '') surgeonName,
    counsilingStatus
FROM
    tbl_insuranceapprovaldetails
WHERE
    branch_status in(1,2)
        AND central_status_text = 'Denied'
     --   AND find_in_set(branch_id, branchids)
           AND IF(searchid = 0, 
				FIND_IN_SET(branch_id, branchids), 
                IF(searchid = 1, patientcode like searchparam, if(searchid = 2,patientname LIKE CONCAT('%', searchparam, '%'),DATE(surgery_date) = DATE(searchparam)))
                )
        LIMIT 50) t1
join tblhospitalbranches t2 on t2.abranchId = t1.branch_id
ORDER BY aid DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurance_ClaimDetails_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insurance_ClaimDetails_report`(

in fromDate varchar(25),
in toDate varchar(25),
in in_BranchId int
)
BEGIN



SELECT 
    IFNULL(TBLIAD.patientname, '') AS patientname,
    IFNULL(TBLIAD.surgeonName, '') AS doctorName,
    '' AS dateOfAdmission,
    IFNULL(TBLIAD.claim_amount, 0) AS totalBill,
    IFNULL(TBLISD.central_Payment_RecivedAmt, 0) AS amountReceived,
    IFNULL(TBLISD.patientPaid, 0) AS patientPaidAmt,
    IFNULL(TBLISD.discount, 0) AS disAmount,
    IFNULL(TBLISD.central_Payment_RecivedAmt, 0)  AS settlementAmt,
    IFNULL(TBLISD.cental_Payment_TdsAmt, 0) AS tdsAmount,
    IFNULL(TBLISD.chequeOrNeftAmt, 0) AS netAmt, 
    IF(TBLISD.central_InsuranceStatus_Text = 'Payment Completed',
        'Closed',
        'Pending') AS remarks,
    IFNULL(TBLIAD.central_preauthnumber, '') AS preAuthNummber,
    IFNULL(TBLIAD.patientcode, '') AS patientcode,
    IFNULL(TBLISD.central_Payment_RefNum, '') AS claimNo,
    IFNULL(TBLIAD.insurance_name, '') AS insurance_name,
    IFNULL(TBLISD.chequeOrNeftNumber, '') AS neftNumber,
    IFNULL(DATE(TBLISD.chequeOrNeftDate), '') AS neftDate,
    TBLIAD.isurance_id AS insuranceId,
    IFNULL(TBLISD.cental_Payment_deductionAmt, 0) AS cental_Payment_deductionAmt,
    IFNULL(TBLISD.cental_excess_amount, 0) AS cental_excess_amount,
    IFNULL(DATE(TBLIAD.central_preauth_date), '') central_preauth_date,
    IFNULL(TBLIAD.central_agreed_discount, 0) AS central_agreed_discount,
    IFNULL(DATE(TBLIAD.surgery_date), '') surgery_date,
    IFNULL(DATE(TBLISD.billDate), '') billDate,
    IFNULL(DATE(TBLISD.central_SubmittedDate), '') central_SubmittedDate,
    IFNULL(DATE(TBLISD.central_Payment_RecivedDate),
            '') receivedDate
FROM
    tbl_insuranceapprovaldetails TBLIAD
        INNER JOIN
    tbl_insuranceclaimsubmitdetails TBLISD ON TBLISD.centralApprovalId = TBLIAD.aid
WHERE
    TBLIAD.branch_id = in_BranchId
        AND (DATE(TBLISD.billDate) BETWEEN DATE(fromDate) AND DATE(toDate))
;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurance_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insurance_report`(
in fromDate varchar(25),
in toDate varchar(25),
in branch_id_in int,
in insuranceIds text,
in in_statusId int
)
BEGIN
SELECT 
    t2.aId_pk AS aId_pk,
    IFNULL(t1.patientname, '') patientname,
    IFNULL(t1.patientcode, '') patientcode,
    IFNULL(t1.insurance_name, '') insurance_name,
    IFNULL(t1.tpa_name, '') tpa_name,
    IFNULL(t1.account_beneficiary, '') account_beneficiary,
    IFNULL(t1.relationship, '') relationship,
    IFNULL(t1.corporate_name, '') corporate_name,
    IFNULL(t1.healthcard_number, '') healthcard_number,
    IFNULL(t1.policy_number, '') policy_number,
    IFNULL(DATE(t1.central_preauth_date), '') central_preauth_date,
    IFNULL(t1.claim_amount, '') claim_amount,
    IFNULL(t1.central_status_text, '') central_status_text,
    IFNULL(t2.central_InsuranceStatus_Text, '') central_InsuranceStatus_Text,
    IFNULL(t1.central_approvedamt, '') central_approvedamt,
    IFNULL(t1.central_preauthnumber, '') central_preauthnumber,
    IFNULL(t1.central_copay, '') central_copay,
    IFNULL(DATE(t1.surgery_date), '') surgery_date,
    IFNULL(DATE(t2.branch_LastModifiedDate), '') branch_LastModifiedDate,
    IFNULL(t2.central_Payment_RecivedDate, '') central_Payment_RecivedDate,
    IFNULL(t2.cental_Payment_DiscountAmt, '') cental_Payment_DiscountAmt,
	IFNULL(t2.cental_Payment_deductionAmt, 0) cental_Payment_deductionAmt,

    IFNULL(t2.central_Payment_RecivedAmt, '') central_Payment_RecivedAmt,
    IFNULL(t2.cental_Payment_TdsAmt, '') central_tds,
    IFNULL(t2.central_Payment_DueAmt, '') central_balance,
    IFNULL(t2.central_Payment_ModeOfPayment, '') central_Payment_ModeOfPayment,
    IFNULL(t2.central_Remarks, '') central_Payment_RecivedComment,
    IFNULL(t2.eyeToBeOperated, '') eyeToBeOperated,
    IFNULL(DATE(t2.billDate), '') billDate,
    IFNULL(t2.billNumber, '') billNumber,
    IFNULL(t2.chequeOrNeftNumber, '') central_Payment_RefNum,
    t1.isurance_id as insuranceId,
     IFNULL(t2.patientPaid, 0) patientPaid,
      IFNULL(t2.discount, 0) patientdiscount,
      IFNULL(t2.chequeOrNeftAmt, 0) recevedAmount,

    IFNULL(t2.cental_excess_amount, '') cental_excess_amount,
     ifnull(date(t1.central_approveddate), '') central_approveddate

FROM
    tbl_insuranceapprovaldetails t1
        LEFT JOIN
    tbl_insuranceclaimsubmitdetails t2 ON t2.centralApprovalId = t1.aid
WHERE
    t1.branch_id = branch_id_in
        AND (DATE(t2.central_SubmittedDate) BETWEEN DATE(fromDate) AND DATE(toDate))
        AND FIND_IN_SET(t1.isurance_id, insuranceIds)
        AND IF(in_statusId = '1',
        (central_InsuranceStatus_Text = 'Document Submitted' ),
        IF(in_statusId = '2',
            central_InsuranceStatus_Text = 'Payment Completed',
            IF(in_statusId = '3',
                central_InsuranceStatus_Text = 'Payment On-Hold',
                TRUE)))
        order by t1.isurance_id
;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurance_TDS_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_get_insurance_TDS_report`(

in fromDate varchar(25),
in toDate varchar(25),
in branch_id_in int,
in insuranceIds text,
in in_statusId int
)
BEGIN


SELECT 
    IFNULL(t1.patientname, '') patientname,
    IFNULL(t1.patientcode, '') patientcode,
    IFNULL(t1.insurance_name, '') insurance_name,
    IFNULL(DATE(t2.billDate), '') billDate,
    IFNULL(t2.central_Payment_RecivedDate, '') amountReceivedDate,
    IFNULL(t2.chequeOrNeftAmt, 0) amountReceived,
    IFNULL(t2.cental_Payment_TdsAmt, 0) tdsAmount,
    IFNULL(t2.central_Payment_DueAmt, 0) dueAmount,
   IFNULL(t2.cental_Payment_DiscountAmt, 0) disAmount,
    IFNULL(t2.central_Payment_ModeOfPayment, '') modeOfPayment,
    IFNULL(t2.chequeOrNeftNumber, '') neftNumber,
    t1.isurance_id as  insuranceId
FROM
    tbl_insuranceapprovaldetails t1 
        JOIN
    tbl_insuranceclaimsubmitdetails t2 ON t2.centralApprovalId = t1.aid
WHERE
    t1.branch_id = branch_id_in
        AND (DATE(t2.central_SubmittedDate) BETWEEN DATE(fromDate) AND DATE(toDate))
        AND FIND_IN_SET(t1.isurance_id, insuranceIds)
        AND IF(in_statusId = '1',
        (central_InsuranceStatus_Text = 'Document Submitted'),
        IF(in_statusId = '2',
            central_InsuranceStatus_Text = 'Payment Completed',
            IF(in_statusId = '3',
                central_InsuranceStatus_Text = 'Payment On-Hold',
                TRUE)))
        
;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurance_tpa_pening_firstlist` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insurance_tpa_pening_firstlist`()
BEGIN
SELECT 
    SUM(IFNULL(t1.central_Payment_DueAmt, 0)) AS pendingAmt,
    COUNT(t1.central_Payment_DueAmt) pendingCases,
    MIN(t1.central_Payment_RecivedDate) oldestPendingDate,
    t2.insurance_name as tpa_name 
FROM
    tbl_insuranceclaimsubmitdetails t1
        JOIN
    tbl_insuranceapprovaldetails t2 ON t1.centralApprovalId = t2.aid
        AND IFNULL(t1.central_Payment_DueAmt, 0) > 0
GROUP BY t2.insurance_name;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurance_tpa_pening_secondlist` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insurance_tpa_pening_secondlist`()
BEGIN
SELECT 
    MAX(t1.central_Payment_RecivedDate) latestReceivedDate,
    t2.insurance_name tpa_name
FROM
    tbl_insuranceclaimsubmitdetails t1
        JOIN
    tbl_insuranceapprovaldetails t2 ON t1.centralApprovalId = t2.aid
GROUP BY t2.insurance_name;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_insurence_user_list` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_insurence_user_list`()
BEGIN

SELECT 
    tup.*,
    tu.userName AS insurenceUsername,
    tu.isActive AS activePermission,
    tu.nBranchId as nBranchIdS
    
FROM
    tbl_user tu
        JOIN
    tbl_user_profiledetails tup ON tup.userId = tu.userId
WHERE
    tu.isDeleted = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_insr_approval_frombranchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insert_insr_approval_frombranchuser`(
in branch_ids int,
in branch_approvalrowids int,
in counseling_ids int,
in surgicalappointment_ids int,
in patient_ids int,
in patientcodes text,
in patientnames text,
in patient_phone_numbers text,
in patientaddresss text,
in branch_statuss text,
in branch_status_texts text,
in surgery_dates text,
in type_of_surgerys text,
in type_of_surgerynames text,
in procedure_ids int,
in procedure_names text,
in isurance_ids int,
in insurance_names text,
in individual_or_corporates text,
in corporate_employee_ids text,
in healthcard_numbers text,
in corporate_names text,
in account_beneficiarys text,
in tpa_ids int,
in tpa_names text,
in relationships text,
in policy_numbers text,
in branch_submitted_dates text,
in branch_remarkss text,
in claim_amounts float,
in submitted_branch_user_ids int,
in submitted_branch_user_names text,
in documentId int,
in docketNum varchar(250),
in comorPerName varchar(250),
in in_surgeonName varchar(250),
in in_councilingStatus int,
out centralApprovalAutoID int
)
BEGIN
declare in_approvalid int;
if(counseling_ids > 0 and in_councilingStatus > 0)
then
set in_approvalid = (select aid from tbl_insuranceapprovaldetails where counseling_id = counseling_ids limit 1);
end if ;

if(!isnull(in_approvalid) and in_approvalid > 0)
then

update tbl_insuranceapprovaldetails 
set 
counsilingStatus = in_councilingStatus ,
branch_lastmodifieddate = now()

where aid = in_approvalid ;

set centralApprovalAutoID = in_approvalid;

else

INSERT INTO tbl_insuranceapprovaldetails
(
branch_id,
branch_approvalrowid,
counseling_id,
surgicalappointment_id,
patient_id,
patientcode,
patientname,
patient_phone_number,
patientaddress,
branch_status,
branch_status_text,
surgery_date,
type_of_surgery,
type_of_surgeryname,
procedure_id,
procedure_name,
isurance_id,
insurance_name,
individual_or_corporate,
corporate_employee_id,
healthcard_number,
corporate_name,
account_beneficiary,
tpa_id,
tpa_name,
relationship,
policy_number,
branch_submitted_date,
branch_remarks,
claim_amount,
submitted_branch_user_id,
submitted_branch_user_name,
branch_inserteddate,
branch_lastmodifieddate,
branch_isactive,
documentId,
docketNumber,
comOrPerName ,
surgeonName ,
counsilingStatus)
VALUES
(
branch_ids,
branch_approvalrowids,
counseling_ids,
surgicalappointment_ids,
patient_ids,
patientcodes,
patientnames,
patient_phone_numbers,
patientaddresss,
branch_statuss,
branch_status_texts,
surgery_dates,
type_of_surgerys,
type_of_surgerynames,
procedure_ids,
procedure_names,
isurance_ids,
insurance_names,
individual_or_corporates,
corporate_employee_ids,
healthcard_numbers,
corporate_names,
account_beneficiarys,
tpa_ids,
tpa_names,
relationships,
policy_numbers,
branch_submitted_dates,
branch_remarkss,
claim_amounts,
submitted_branch_user_ids,
submitted_branch_user_names,
now(),
now(),
1,
documentId,
docketNum,
comorPerName,
in_surgeonName ,
in_councilingStatus);

set centralApprovalAutoID = LAST_INSERT_ID();
end if;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_insr_claim_frombranchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_insert_insr_claim_frombranchuser`(
in branchIds int,
in branch_ClaimrowIds int,
in ipIds int,
in opIds int,
in incentralApprovalIds int,
in branch_Statuss int,
in branch_Status_Texts varchar(50),
in branch_Submitted_Dates varchar(25),
in branch_Remarkss text,
in branch_ModeOfDocumentSents tinytext,
in branch_DocRefNums tinytext,
in branch_submitted_userids int,
in branch_submitted_user_names varchar(250),
in eyeToBeOperateds varchar(250),
in billDates varchar(250),
in billNumbers varchar(250),
in in_patientPaid float,
in in_discountAmt float,
in in_payMode varchar(250),
out centralClaimAutoId int
)
BEGIN

set centralClaimAutoId = (select aId_pk from tbl_insuranceclaimsubmitdetails where centralApprovalId = incentralApprovalIds limit 1) ;

if(centralClaimAutoId > 0)
then 
update tbl_insuranceclaimsubmitdetails
set
billDate  = billDates,
billNumber = billNumbers ,
branch_LastModifiedDate = now(),
patientPaid = in_patientPaid ,
discount = in_discountAmt ,
modeOfPayment = in_payMode

where aId_pk = centralClaimAutoId ; 
else
INSERT INTO tbl_insuranceclaimsubmitdetails
	(
	branchId,
	branch_ClaimrowId,
	ipId,
	opId,
	centralApprovalId,
	branch_Status,
	branch_Status_Text,
	branch_Submitted_Date,
	branch_Remarks,
	branch_ModeOfDocumentSent,
	branch_DocRefNum,
    branch_submitted_userid,
    branch_submitted_user_name,
    eyeToBeOperated,
    billDate,
    billNumber  ,
    patientPaid,
    discount,
    modeOfPayment
    )
	VALUES
	(
	branchIds,
	branch_ClaimrowIds,
	ipIds,
	opIds,
	incentralApprovalIds, -- centralApprovalIds
	branch_Statuss,
	branch_Status_Texts,
	branch_Submitted_Dates,
	branch_Remarkss,
	branch_ModeOfDocumentSents,
	branch_DocRefNums,
    branch_submitted_userids,
    branch_submitted_user_names,
    eyeToBeOperateds,
    if(billDates='',null,billDates),
    billNumbers ,
    in_patientPaid ,
    in_discountAmt ,
    in_payMode);
    
set centralClaimAutoId = last_insert_id();
end if;

	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_User_MOdules` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_insert_User_MOdules`(
in userId int(11),
in moduleId int(11)
)
BEGIN

insert into tbl_user_module_map(
nUserId,
nModuleId,
isActive,
bDeleted
)values(
userId,
moduleId,
1,
0
);



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveOpdCrerditDetailsToCentral` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_saveOpdCrerditDetailsToCentral`(
in patientId int(11),
in appoinmentid int(11),
in billId int(11),
in billAoutoId int(11),
in submittedStatus int(11),
in submittedDate datetime,
in submittedText Text,
in branchUserId int(10),
in branchId int(10),
in centeralSubmitedRemarks Text,
in mrdNo varchar(45),
in patientName varchar(250), 
in opdPanelName text,
in cardNuumber varchar(45),
in cardHolderName varchar(45),
in permissionNumLetter varchar(45),
in billAmount float,
in billDate date,
in ationDate date,
out centraOPDId int(12)
)
BEGIN

INSERT INTO `tbl_opdinsurance_claimdetails_incentral`
(npatientid,
nappointmentid,
nbillid,
nbillautoid,
submitted_status,
submitted_date,
submitted_text,
branch_userid,
branch_id,
central_submited_remarks,
insertedDate,
lastModifiedDate,
isActive,
bdeleted,
PatientName,
OpdPAnelNAme,
CardNumber,
CardHolderName,
PermissLetternum,
mrdNo,
billAmount,
billDate,
ationDate
)
VALUES 
(patientId,
appoinmentid,
billId,
billAoutoId,
submittedStatus,
submittedDate,
submittedText,
branchUserId,
branchId,
centeralSubmitedRemarks,
now(),
now(),
1,
0,
patientName,
opdPanelName,
cardNuumber,
cardHolderName,
permissionNumLetter,
mrdNo,
billAmount,
date(billDate),
date(ationDate)
);

set centraOPDId = LAST_INSERT_ID();

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateBillDetailsToInsurance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateBillDetailsToInsurance`(
in billDates varchar(250),
in billNumbers varchar(250),
in in_inPatId int,
in in_patientPaid float,
in in_discountAmt float,
in in_payMode varchar(250)
)
BEGIN

declare centralClaimAutoId int ;

set centralClaimAutoId = (select aId_pk from tbl_insuranceclaimsubmitdetails where ipId = in_inPatId limit 1) ;


if(centralClaimAutoId > 0)
then 
update tbl_insuranceclaimsubmitdetails
set
billDate  = billDates,
billNumber = billNumbers ,
branch_LastModifiedDate = now(),
patientPaid = in_patientPaid ,
discount = in_discountAmt ,
modeOfPayment = in_payMode
where aId_pk = centralClaimAutoId ; 
end if;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatecreditDetailsToCentraalFromSubmited` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updatecreditDetailsToCentraalFromSubmited`(
in aidKey int(11),
in receivdate datetime,
in receivamount float,
in modepayment varchar(20),
in refcenumber tinytext,
in dueamount float,
in submitedremarks text,
in statuss varchar(20)
 
)
BEGIN

update tbl_opdinsurance_claimdetails_incentral set receiveddate=receivdate,receivedamount=receivamount,modeofpayment=modepayment,referencenum=refcenumber,dueamt=dueamount,submitted_remarks=submitedremarks
,statuss=2,statustext=statuss where aid=aidKey;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateOpdCrerditDetailsToCentralFormPending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updateOpdCrerditDetailsToCentralFormPending`(

in pendingsubmitteddate datetime,
in pendingremarks text,
in modeOfDocumetext varchar(50),
in pendingactiondate datetime,
in modeofdocument int(11),
in aidkey int(11),
in centraluserid int(11) 
)
BEGIN
update tbl_opdinsurance_claimdetails_incentral set 
statuss = 1,statustext ='SUBMITTED',pending_remarks = pendingremarks,modeofdoctext = modeOfDocumetext,pending_submitteddate = pendingsubmitteddate,pending_actiondate = pendingactiondate,modeofdoc = modeofdocument,nuserid = centraluserid
 where aid = aidkey;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_ClaimDetails_From_OnHold` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_ClaimDetails_From_OnHold`(
in centralInsuranceStatus INT(11),
IN centralInsuranceStatusText varchar(25),
IN centralTPAonHoldDate datetime,
IN centralTPAonHoldRemarks TEXT,
IN centralClaimAutoId INT(11)
)
BEGIN

UPDATE tbl_insuranceclaimsubmitdetails 
SET 
central_InsuranceStatus = centralInsuranceStatus,
central_InsuranceStatus_Text = centralInsuranceStatusText,
central_TPAonHoldDate = DATE(centralTPAonHoldDate),
central_TPAonHoldRemarks = centralTPAonHoldRemarks
WHERE
    aId_pk = centralClaimAutoId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_ClaimDetails_From_Pending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_ClaimDetails_From_Pending`(
IN bracnhStaus int(11),
IN branchStatusText varchar(25),
IN centralInsuranceStatus int(11),
IN centralInsuranceStatusText varchar(25),
IN centralSubmittedDate Datetime,
IN centralModeOfDocumentSent varchar(25),
IN centralDocRefNum varchar(25),
IN centralRemarks text,
IN centralCentralDeniedDate Datetime,
IN centralCentralDeniedRemarks text,
IN centralSubmittedUserId int(11),
IN centralUserId int(11),
IN centeralAutoPrimaryId int(11),
in corierComName varchar(250)
)
BEGIN

UPDATE tbl_insuranceclaimsubmitdetails 
SET 
    -- branch_Status = bracnhStaus,
    -- branch_Status_Text = branchStatusText,
    central_InsuranceStatus = centralInsuranceStatus,
    central_InsuranceStatus_Text = centralInsuranceStatusText,
    central_SubmittedDate = date(centralSubmittedDate),
    central_ModeOfDocumentSent = centralModeOfDocumentSent,
    central_DocRefNum = centralDocRefNum,
    central_Remarks = centralRemarks,
    central_CentralDeniedDate = date(centralCentralDeniedDate),
    central_CentralDeniedRemarks = centralCentralDeniedRemarks,
    central_InsertedDate = now(),
    central_LastModifiedDate = now(),
    central_Submitted_UserId = centralSubmittedUserId,
    central_UserId = centralUserId,
    corier_com_name = corierComName
WHERE
    aId_pk = centeralAutoPrimaryId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_ClaimDetails_From_Submitted` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_ClaimDetails_From_Submitted`(
IN centralSubmittedUserId INT(11),
IN centralUserId  INT(11),
IN centralPaymentRecivedDate DATETIME,
IN centralPaymentRecivedComment TEXT,
IN centralPaymentApprovedAmt FLOAT,
IN centralPaymentRecivedAmt FLOAT,
IN centralPaymentDueAmt FLOAT,
IN centralPaymentModeOfPayment VARCHAR(20),
IN centralPaymentRefNum VARCHAR(20),
IN centralPaymentPaymentPaid VARCHAR(20),
IN centralPaymentPaidAmt FLOAT,
IN centralClaimAutoId INT(11),
in central_InsuranceStatuss int,
in central_InsuranceStatus_Texts varchar(50),
in central_payment_cheqOrNeft_amt double,
in central_payment_cheqOrNeft_number varchar(250),
in central_payment_cheqOrNeft_date date,
in central_payment_Tds_amt double,
in central_payment_deduction_amt double,
in central_payment_discoun_amt double ,
in central_aggreed_discoun_amt double,
in in_central_excess_amt double
)
BEGIN
declare in_centralApproveId int;

set in_centralApproveId = (select centralApprovalId from tbl_insuranceclaimsubmitdetails where aId_pk = centralClaimAutoId limit 1);
UPDATE tbl_insuranceclaimsubmitdetails 
SET 
central_Submitted_UserId = centralSubmittedUserId,
central_UserId = centralUserId,
central_LastModifiedDate = now(),
central_Payment_RecivedDate = Date(centralPaymentRecivedDate),
central_Payment_RecivedComment = centralPaymentRecivedComment,
central_Payment_ApprovedAmt = centralPaymentApprovedAmt,
central_Payment_RecivedAmt = centralPaymentRecivedAmt,
central_Payment_DueAmt = centralPaymentDueAmt,
central_Payment_ModeOfPayment = centralPaymentModeOfPayment,
central_Payment_RefNum = centralPaymentRefNum,
central_Payment_PaymentPaid = centralPaymentPaymentPaid,
central_Payment_PaidAmt = centralPaymentPaidAmt,
central_InsuranceStatus = central_InsuranceStatuss,
central_InsuranceStatus_Text = central_InsuranceStatus_Texts,
chequeOrNeftAmt = central_payment_cheqOrNeft_amt,
chequeOrNeftNumber = central_payment_cheqOrNeft_number,
chequeOrNeftDate = central_payment_cheqOrNeft_date,
cental_Payment_TdsAmt = central_payment_Tds_amt,
cental_Payment_deductionAmt = central_payment_deduction_amt,
cental_Payment_DiscountAmt = central_payment_discoun_amt,
cental_excess_amount = in_central_excess_amt
WHERE
    aId_pk = centralClaimAutoId;

if(!isnull(in_centralApproveId) and in_centralApproveId > 0)
then 

update tbl_insuranceapprovaldetails
set

central_agreed_discount = central_aggreed_discoun_amt

where aid = in_centralApproveId ;

end if;





END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_inapproval_from_approval` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_update_inapproval_from_approval`(
in central_preauthnumbers text,
in central_approveddates varchar(25),
in central_approvedamts float,
in central_copaypercents float,
in central_copays float,
in central_tdss float,
in central_balances float,
in central_agreed_discounts float,
in central_statuss int,
in central_status_texts text,
in central_deneid_dates varchar(25),
in central_deneid_remarks_tpas text,
in central_userids int,
in centralaprrowid int,

in in_insuranceName varchar(250),
in in_insuCorpEmpId varchar(50),
in in_insuHealthCardNum varchar(100),
in in_insuCorpName varchar(250),
in in_insuAccBenificiary varchar(250) ,
in in_insuTpaName varchar(250) ,
in in_insuRelationShip varchar(250) ,
in in_insuPolicyName varchar(250)  ,
in in_indiOrCorporate varchar(20)
)
BEGIN
update tbl_insuranceapprovaldetails
set central_preauthnumber = central_preauthnumbers,
central_approveddate = central_approveddates,
central_approvedamt = central_approvedamts,
central_copaypercent = central_copaypercents,
central_copay = central_copays,
central_tds = central_tdss,
central_balance = central_balances,
central_agreed_discount = central_agreed_discounts,
central_status = central_statuss,
central_status_text = central_status_texts,
central_deneid_date = central_deneid_dates,
central_deneid_remarks_tpa = central_deneid_remarks_tpas,
central_userid = central_userids,
central_lastmodifieddate = now() ,
 insurance_name = in_insuranceName ,
    corporate_employee_id = in_insuCorpEmpId,
    healthcard_number = in_insuHealthCardNum,
    corporate_name = in_insuCorpName ,
    account_beneficiary = in_insuAccBenificiary ,
    tpa_name = in_insuTpaName ,
    relationship = in_insuRelationShip,
    policy_number = in_insuPolicyName,
    individual_or_corporate =  in_indiOrCorporate
where aid = centralaprrowid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_inapproval_from_deneid` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_update_inapproval_from_deneid`(
in central_statuss int,
in central_status_texts text,
in central_userids int,
in centralaprrowid int,
in in_insuranceName varchar(250),
in in_insuCorpEmpId varchar(50),
in in_insuHealthCardNum varchar(100),
in in_insuCorpName varchar(250),
in in_insuAccBenificiary varchar(250) ,
in in_insuTpaName varchar(250) ,
in in_insuRelationShip varchar(250) ,
in in_insuPolicyName varchar(250)  ,
in in_indiOrCorporate varchar(20)
)
BEGIN
update tbl_insuranceapprovaldetails
	set 
	central_status = central_statuss,
	central_status_text = central_status_texts,
	central_userid = central_userids,
	central_lastmodifieddate = now(),
    insurance_name = in_insuranceName,
    corporate_employee_id = in_insuCorpEmpId,
    healthcard_number = in_insuHealthCardNum,
    corporate_name = in_insuCorpName,
    account_beneficiary = in_insuAccBenificiary,
    tpa_name = in_insuTpaName,
    relationship = in_insuRelationShip,
    policy_number = in_insuPolicyName,
    individual_or_corporate = in_indiOrCorporate
where aid = centralaprrowid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_inapproval_from_pending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_update_inapproval_from_pending`(
in claim_amounts float,
in central_preauth_dates varchar(25),
in central_modeofdocuments varchar(25),
in central_document_referencenos varchar(250),
in central_statuss varchar(25),
in central_status_texts varchar(25),
in central_remarkss text,
in central_deneid_dates varchar(25),
in central_deneid_remarks_tpas text,
in central_submitted_userids int,
in central_userids int,
in centralapprovalrowid int,
in in_insuranceName varchar(250),
in in_insuCorpEmpId varchar(50),
in in_insuHealthCardNum varchar(100),
in in_insuCorpName varchar(250),
in in_insuAccBenificiary varchar(250) ,
in in_insuTpaName varchar(250) ,
in in_insuRelationShip varchar(250) ,
in in_insuPolicyName varchar(250)  ,
in in_indiOrCorporate varchar(20)
)
BEGIN
UPDATE tbl_insuranceapprovaldetails 
SET 
    claim_amount = claim_amounts,
    central_preauth_date = central_preauth_dates,
    central_modeofdocument = central_modeofdocuments,
    central_document_referenceno = central_document_referencenos,
    central_status = central_statuss,
    central_status_text = central_status_texts,
    central_remarks = central_remarkss,
    central_deneid_date = central_deneid_dates,
    central_deneid_remarks_tpa = central_deneid_remarks_tpas,
    central_submitted_userid = central_submitted_userids,
    central_userid = central_userids,
    central_inserteddate = NOW(),
    central_lastmodifieddate = NOW(),
    central_isactive = 1,
    central_isdeleted = 0,
    insurance_name = in_insuranceName ,
    corporate_employee_id = in_insuCorpEmpId,
    healthcard_number = in_insuHealthCardNum,
    corporate_name = in_insuCorpName ,
    account_beneficiary = in_insuAccBenificiary ,
    tpa_name = in_insuTpaName ,
    relationship = in_insuRelationShip,
    policy_number = in_insuPolicyName,
    individual_or_corporate =  in_indiOrCorporate
WHERE
    aid = centralapprovalrowid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_insr_approval_frombranchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_insr_approval_frombranchuser`(
in branch_ids int,
in branch_approvalrowids int,
in counseling_ids int,
in surgicalappointment_ids int,
in patient_ids int,
in patientcodes text,
in patientnames text,
in patient_phone_numbers text,
in patientaddresss text,
in branch_statuss text,
in branch_status_texts text,
in surgery_dates text,
in type_of_surgerys text,
in type_of_surgerynames text,
in procedure_ids int,
in procedure_names text,
in isurance_ids int,
in insurance_names text,
in individual_or_corporates text,
in corporate_employee_ids text,
in healthcard_numbers text,
in corporate_names text,
in account_beneficiarys text,
in tpa_ids int,
in tpa_names text,
in relationships text,
in policy_numbers text,
in branch_submitted_dates text,
in branch_remarkss text,
in claim_amounts float,
in submitted_branch_user_ids int,
in submitted_branch_user_names text,
in documentId int,
in docketNum varchar(250),
in comorPerName varchar(250),
in in_centralApprovalRowId int
)
BEGIN


update tbl_insuranceapprovaldetails
set
surgery_date = surgery_dates,
type_of_surgery = type_of_surgerys,
type_of_surgeryname = type_of_surgerynames,
procedure_id = procedure_ids,
procedure_name = procedure_names,
isurance_id = isurance_ids,
insurance_name = insurance_names,
individual_or_corporate = individual_or_corporates,
corporate_employee_id = corporate_employee_ids,
healthcard_number = healthcard_numbers,
corporate_name = corporate_names,
account_beneficiary = account_beneficiarys,
tpa_id = tpa_ids,
tpa_name = tpa_names,
relationship = relationships,
policy_number = policy_numbers,
branch_submitted_date = branch_submitted_dates,
branch_remarks = branch_remarkss,
claim_amount = claim_amounts,
submitted_branch_user_id = submitted_branch_user_ids,
submitted_branch_user_name = submitted_branch_user_names,
branch_lastmodifieddate = now(),
branch_isactive = 1,
documentId = documentId,
docketNumber = docketNum,
comOrPerName = comorPerName

where
   ( ISNULL(central_status))
        AND aid = in_centralApprovalRowId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_insr_claim_frombranchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_update_insr_claim_frombranchuser`(
in branch_Statuss int,
in branch_Status_Texts tinytext,
in branch_Remarkss text,
in centralClaimRowIds int
)
BEGIN

UPDATE tbl_insuranceclaimsubmitdetails 
SET 
    branch_Status = branch_Statuss,
    branch_Status_Text = branch_Status_Texts,
    branch_Remarks = branch_Remarkss
WHERE
    aId_pk = centralClaimRowIds;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_Insurence_ApproveDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_Insurence_ApproveDetails`(
in in_centralApprovalRowId int
)
BEGIN
UPDATE tbl_insuranceapprovaldetails 
SET 
    branch_isactive = 0
WHERE
    ( ISNULL(central_status))
        AND aid = in_centralApprovalRowId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Update_insurence_user` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_Update_insurence_user`(
in userFullName varchar(100),
in userMobileNumber varchar(100),
in userAddress text,
in employeId varchar(100),
in emailId varchar(100),
in username varchar(100),
in password1 varchar(250),
in confirmPassword varchar(250),
in nameprific varchar(20),
in branchvalue int(11),
in modulesIds text,
in hospetalBranches text,
in insurenceuserId int(11),
out updatedUserId int(11)

)
BEGIN

update tbl_user set 

userName = username,
`password` = password1,
lastModifiedDate = now(),
IsCentralOrBranchUser = branchvalue,
nBranchId = hospetalBranches

 where userId = insurenceuserId;

update tbl_user_profiledetails set 
userfullname = userFullName,
mobileNumber = userMobileNumber,
address = userAddress,
employeId = employeId,
emailId = emailId,
userhospetalbranches = hospetalBranches,
userModules = modulesIds,
isbranchORcentraluser = branchvalue,
lastModifiedDate = now(),
userprefix = nameprific where userId = insurenceuserId;

set  updatedUserId = insurenceuserId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_validate_user_login` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_validate_user_login`(
in userNames varchar(100),
in passwords varchar(250),
in hospId int,
in branchid int
)
BEGIN	 
    select distinct moduleId,userId, userName, password, imageName,
    
    aModuleId, moduleName,TUM.isActive, isHavingSubModule, moduleId, fnOnClick,moduleIcon
    
    from tbl_user TU
    left join tbl_user_module_map TUM on TU.userId = TUM.nUserId 
    left join tbl_modules TM on TM.aModuleId = TUM.nModuleId
    where TU.userName = userNames and TU.password = passwords
   -- and find_in_set(marketIds, TU.nMarketIds)-- TU.nMarketIds in (marketIds)
    and TU.isActive = 1 and TU.isDeleted = 0 and TU.IsCentralOrBranchUser = 1 and TUM.isActive = 1 and TUM.bDeleted = 0  AND TM.isMobileOrWeb = 1
    -- and TU.nHospitalId = hospId and TU.nBranchId = branchid
    -- and TUM.nHospitalId = hospId  and TUM.nBranchId = branchid
    order by priority;   
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_validate_user_login_branchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_validate_user_login_branchuser`(
in userNames varchar(100),
in passwords varchar(250),
in hospId int,
in branchid int
)
BEGIN
select userId, userName,  imageName, password
    from tbl_user TU
    where TU.userName = userNames and TU.password = passwords
		and TU.isActive = 1 and TU.isDeleted = 0 and TU.isWebOrMobileUser = 1 
		and TU.IsCentralOrBranchUser = 2 and find_in_set(branchid,TU.nBranchId);
END ;;
DELIMITER ;


