DROP FUNCTION IF EXISTS `fn_get_analyte_ip_details`;
CREATE FUNCTION fn_get_analyte_ip_details(analyte_id INT, doctor_id INT, applicable_rate INT) RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
	SET @departmentid = 0; SET @subdepartmentid = 0;
	SET @doctorid = doctor_id; SET @testid = analyte_id;
	SET @alliprate = '0'; SET @alliptype = '0'; SET @allipIfExists = '0';
	SET @deptiprate = 0; SET @deptiptype = 'A'; SET @deptipIfExists = 0;
	SET @subdeptiprate = '0'; SET @subdeptiptype = '0'; SET @subdeptipIfExists = '0';
	SET @testiprate = '0'; SET @testiptype = '0'; SET @testipIfExists = '0';
	SET @returnValue = '';
	SET SESSION sql_mode = '';
	SELECT lab_analyte_master.department_id, lab_analyte_master.sub_department_id INTO @departmentid,@subdepartmentid FROM lab_analyte_master WHERE id = @testid;
	
	#Check if data with all is present in DB
	SELECT COALESCE(doctor_ip_config.ip_rate,0), COALESCE(doctor_ip_config.ip_type,'A'), COUNT(*) as count INTO @alliprate,@alliptype,@allipIfExists FROM doctor_ip_config WHERE ip_config_category = 'ALL' AND doctor_ip_config.doctor_id = @doctorid AND ip_rate != 0;
	

	#Check if data with department data present or not
	SELECT COALESCE(doctor_ip_config.ip_rate,0), COALESCE(doctor_ip_config.ip_type,'A'), COUNT(*) INTO @deptiprate,@deptiptype,@deptipIfExists FROM doctor_ip_config WHERE ip_config_category = 'DEPT' AND doctor_ip_config.doctor_id = @doctorid AND category_id = @departmentid AND ip_rate != 0;
	
	#Check if data with Sub Department present or Not
	SELECT COALESCE(doctor_ip_config.ip_rate,0), COALESCE(doctor_ip_config.ip_type,'A'), COUNT(*) as count INTO @subdeptiprate,@subdeptiptype,@subdeptipIfExists FROM doctor_ip_config WHERE ip_config_category = 'SUB_DEPT' AND doctor_ip_config.doctor_id = @doctorid AND category_id = @subdepartmentid AND ip_rate != 0;
	
	#Check if Data with Test id Present or Not
	SELECT COALESCE(doctor_ip_config.ip_rate,0), COALESCE(doctor_ip_config.ip_type,'A'), COUNT(*) as count INTO @testiprate,@testiptype,@testipIfExists FROM doctor_ip_config WHERE ip_config_category = 'TEST' AND doctor_ip_config.doctor_id = @doctorid AND category_id = @testid AND ip_rate != 0;

	CASE
			WHEN (@testipIfExists > 0 AND @testiprate > 0)  THEN
				IF @testiptype = 'A' THEN
					SET @returnValue = @testiprate;
				ELSE
					SET @returnValue = (@testiprate/100) * applicable_rate;
				END IF;	
				
			WHEN (@subdeptipIfExists > 0 AND @subdeptiprate > 0) THEN
				IF @subdeptiptype = 'A' THEN
					SET @returnValue = @subdeptiprate;
				ELSE
					SET @returnValue = (@subdeptiprate/100) * applicable_rate;
				END IF;	
				
			WHEN (@deptipIfExists > 0 AND @deptiprate > 0) THEN
				IF @deptiptype = 'A' THEN
					SET @returnValue = @deptiprate;
				ELSE
					SET @returnValue = (@deptiprate/100) * applicable_rate;
				END IF;
				
			WHEN (@allipIfExists > 0 AND @alliprate) THEN
				IF @alliptype = 'A' THEN
					SET @returnValue = @alliprate;
				ELSE
					SET @returnValue = (@alliprate/100) * applicable_rate;
				END IF;
				
			ELSE 
				SET @returnValue = 0;
	END CASE;
	
	RETURN @returnValue;
	
END;
