DROP FUNCTION IF EXISTS `fn_get_analyte_signup_charge_details`;
CREATE FUNCTION `fn_get_analyte_signup_charge_details`(analyte_id INT, doctor_id INT, applicable_rate INT) RETURNS varchar(50) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
	SET @departmentid = 0; SET @subdepartmentid = 0;
	SET @doctorid = doctor_id; SET @testid = analyte_id;
	SET @allsignrate = '0'; SET @allsigntype = '0'; SET @allsignIfExists = '0';
	SET @deptsignrate = 0; SET @deptsigntype = 'A'; SET @deptsignIfExists = 0;
	SET @subdeptsignrate = '0'; SET @subdeptsigntype = '0'; SET @subdeptsignIfExists = '0';
	SET @testsignrate = '0'; SET @testsigntype = '0'; SET @testsignIfExists = '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_signup_charge.signup_charge_rate,0), COALESCE(doctor_signup_charge.signup_charge_type,'A'), COUNT(*) as count INTO @allsignrate,@allsigntype,@allsignIfExists FROM doctor_signup_charge WHERE signup_config_category = 'ALL' AND doctor_signup_charge.doctor_id = @doctorid AND doctor_signup_charge.is_active = 'Y' AND signup_charge_rate != 0;
	

	#Check if data with department data present or not
	SELECT COALESCE(doctor_signup_charge.signup_charge_rate,0), COALESCE(doctor_signup_charge.signup_charge_type,'A'), COUNT(*) INTO @deptsignrate,@deptsigntype,@deptsignIfExists FROM doctor_signup_charge WHERE signup_config_category = 'DEPT' AND doctor_signup_charge.doctor_id = @doctorid AND category_id = @departmentid  AND doctor_signup_charge.is_active = 'Y' AND signup_charge_rate != 0;
	
	#Check if data with Sub Department present or Not
	SELECT COALESCE(doctor_signup_charge.signup_charge_rate,0), COALESCE(doctor_signup_charge.signup_charge_type,'A'), COUNT(*) as count INTO @subdeptsignrate,@subdeptsigntype,@subdeptsignIfExists FROM doctor_signup_charge WHERE signup_config_category = 'SUB_DEPT' AND doctor_signup_charge.doctor_id = @doctorid AND category_id = @subdepartmentid AND doctor_signup_charge.is_active = 'Y' AND signup_charge_rate != 0;
	
	#Check if Data with Test id Present or Not
	SELECT COALESCE(doctor_signup_charge.signup_charge_rate,0), COALESCE(doctor_signup_charge.signup_charge_type,'A'), COUNT(*) as count INTO @testsignrate,@testsigntype,@testsignIfExists FROM doctor_signup_charge WHERE signup_config_category = 'TEST' AND doctor_signup_charge.doctor_id = @doctorid AND category_id = @testid AND doctor_signup_charge.is_active = 'Y' AND signup_charge_rate != 0;

	CASE
			WHEN (@testsignIfExists > 0 AND @testsignrate > 0)  THEN
				IF @testsigntype = 'A' THEN
					SET @returnValue = @testsignrate;
				ELSE
					SET @returnValue = (@testsignrate/100) * applicable_rate;
				END IF;	
				
			WHEN (@subdeptsignIfExists > 0 AND @subdeptsignrate > 0) THEN
				IF @subdeptsigntype = 'A' THEN
					SET @returnValue = @subdeptsignrate;
				ELSE
					SET @returnValue = (@subdeptsignrate/100) * applicable_rate;
				END IF;	
				
			WHEN (@deptsignIfExists > 0 AND @deptsignrate > 0) THEN
				IF @deptsigntype = 'A' THEN
					SET @returnValue = @deptsignrate;
				ELSE
					SET @returnValue = (@deptsignrate/100) * applicable_rate;
				END IF;
				
			WHEN (@allsignIfExists > 0 AND @allsignrate) THEN
				IF @allsigntype = 'A' THEN
					SET @returnValue = @allsignrate;
				ELSE
					SET @returnValue = (@allsignrate/100) * applicable_rate;
				END IF;
				
			ELSE 
				SET @returnValue = 0;
	END CASE;
	
	RETURN @returnValue;
	
END