Untitled

DROP PROCEDURE IF EXISTS collectionsmax.SP_CleanCollect_New_Pool_V2;

DELIMITER //
CREATE PROCEDURE collectionsmax.SP_CleanCollect_New_Pool_V2(p_drop_user VARCHAR(25))
BEGIN

# Required for app
DECLARE p_message VARCHAR(255);
DECLARE p_account_id INT DEFAULT 0;

# Parameters for troubleshooting
DECLARE p_file_number INT DEFAULT 0;

DECLARE p_agent_id INT DEFAULT 0; 
DECLARE p_pool_id INT DEFAULT 0;
DECLARE p_conc_lock INT DEFAULT 15; # Minutes Default concurrency lockout in the case the pool is missing it from collectionsmax.Drops
DECLARE p_padding INT DEFAULT 0; # Days
DECLARE p_weight INT DEFAULT 0;
DECLARE p_seq INT DEFAULT 0;
DECLARE p_max_seq INT DEFAULT 0;
DECLARE p_avg_window_diff INT;


# Parameters for get acct loop
DECLARE p_attempts INT DEFAULT 0;
DECLARE p_max_attempts INT DEFAULT 20;
DECLARE p_workflow_template_id INT;


# TZ and phone parameters
DECLARE p_window_index INT DEFAULT ELT(DAYOFWEEK(CURDATE()),8,2,3,4,5,6,7);
DECLARE p_pct_required_tz_open DECIMAL(16,2) DEFAULT 1;
# Percentage of phones tz open.
DECLARE p_pct_acct_tz_open  DECIMAL(16,2) DEFAULT 0;
DECLARE p_in_dst_observance TINYINT DEFAULT 1;

# Parameters for lock
DECLARE p_pool_lock_id VARCHAR(25) DEFAULT '0';
DECLARE p_lock_status INT DEFAULT 0; # Lock Status
DECLARE p_lock_timeout INT DEFAULT 5; # Seconds


# Parameters for lock timeout
DECLARE p_retries INT DEFAULT 0;
DECLARE p_done INT DEFAULT 0;
DECLARE p_wait DECIMAL(16,2) DEFAULT 0.2; # Seconds
DECLARE p_max_retries INT DEFAULT 5;


## NOTES ##
-- NEED TO GET TZD FUNCTION WORKING AND INCLUDED IN ORDER BY MAY NEED TO VARIABLIZE IT SO IT CAN USED IN ORDER BY IN SECONDARY CTE; 

DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN 
        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        INSERT INTO `collectionsmax`.`error_logs`(message,error_source) VALUES (CONCAT(@p1, ':', @p2),CONCAT('collectionsmax.SP_CleanCollect_New_Pool'));
        SELECT IS_FREE_LOCK(p_pool_lock_id) INTO p_lock_status;
		IF p_lock_status = 1
		THEN
		# Release Lock
		DO RELEASE_LOCK(p_pool_lock_id);
        END IF;
        SELECT CONCAT('Error/Exception:', CONCAT(@p1, ':', @p2));
    END;
    
    



SELECT id INTO p_agent_id FROM collectionsmax.collectorinfo WHERE collectorloginname = p_drop_user;


IF p_agent_id != 0
THEN
# Check if we're in DST observance


SET @drop_table = CONCAT('DROP TEMPORARY TABLE IF EXISTS temp_pool_',p_agent_id );


PREPARE stmt FROM @drop_table;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @create_table = CONCAT('CREATE TEMPORARY TABLE temp_pool_',p_agent_id,' AS
SELECT
	dr.dropid AS `pool_id`,
	dp.pool_name AS `pool`,
    dr.drop_weight AS `weight`,
    dr.`concurrency_lock_timer` AS `conc_lock`,
    dr.padding,
    ROW_NUMBER() OVER(ORDER BY drop_weight DESC) AS `seq`,
    dr.workflow_template_id
FROM
	AutoDropper.drop_pools dp
		INNER JOIN
	collectionsmax.Drops dr ON dr.DropId = dp.drop_id
WHERE
	dp.agent_id =',p_agent_id,'
	AND dp.is_active = 1'
    );

PREPARE stmt FROM @create_table;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    
SET @select_table = CONCAT('SELECT MIN(seq),MAX(seq) INTO @p_seq, @p_max_seq FROM temp_pool_',p_agent_id);

PREPARE stmt FROM @select_table;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET p_seq = @p_seq,
	p_max_seq = @p_max_seq;

SET p_attempts = 0;
SELECT collectionsmax.FN_In_DST() INTO p_in_dst_observance;

WHILE p_account_id = 0 AND p_attempts <= p_max_attempts AND p_seq <= p_max_seq
DO

SET @select_pool = CONCAT('SELECT pool_id,weight,conc_lock,padding,workflow_template_id INTO @p_pool_id, @p_weight, @p_conc_lock,@p_padding,@p_workflow_template_id FROM temp_pool_',p_agent_id,' WHERE seq = ',p_seq );
SET p_pool_lock_id = CAST(@p_pool_id AS CHAR);
SELECT IS_FREE_LOCK(p_pool_lock_id) INTO p_lock_status;

PREPARE stmt FROM @select_pool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

	SET p_pool_id = @p_pool_id,
		p_weight = @p_weight,
        p_conc_lock = @p_conc_lock,
        p_padding = @p_padding,
        p_workflow_template_id = @p_workflow_template_id;
 
SET p_max_retries = 5;

WHILE p_lock_status = 0 AND p_retries <= p_max_retries
DO 
	DO SLEEP(p_wait);
    SET p_retries = p_retries + 1;
    SELECT IS_FREE_LOCK(p_pool_lock_id) INTO p_lock_status;
END WHILE;


IF p_lock_status = 1
	THEN
		SET p_lock_timeout = 5;
		# Create lock
		DO GET_LOCK(p_pool_lock_id,p_lock_timeout);
		
WITH CTE_Tzd_Data AS
							(
							SELECT
								tz.IdType,
								tz.Id,
								CASE 
									WHEN HOUR(CONVERT_TZ(UTC_TIMESTAMP,'+00:00', tz.UTCOffsetString) + INTERVAL CASE WHEN tz.DaylightSavingsTime = 1 AND p_in_dst_observance = 1 THEN 1 ELSE 0 END HOUR)
									BETWEEN 8 AND 20 
									THEN 1
									ELSE 0
								END AS `is_open`
								FROM
								`rapidcontact`.`TimeZoneData` tz
							)
                  
                        SELECT
							d.id,
							d.filenumber
							INTO
                            p_account_id,
                            p_file_number
	
						FROM
							collectionsmax.dbase d
								INNER JOIN
							collectionsmax.DropDetails dd ON dd.drop_id = p_pool_id AND dd.dbase_id = d.id
								INNER JOIN
							collectionsmax.workflow_template_status_index s ON s.workflow_template_id = p_workflow_template_id
                            AND d.status_id = s.status_id
								LEFT JOIN
							CTE_Tzd_Data tzd ON IdType = 2 AND tzd.Id = LEFT(d.zip,5)
								
						WHERE
							d.drop_id = p_pool_id
                         
							AND DATE(d.agent_status_timestamp) <= CURDATE() - INTERVAL p_padding DAY
							AND dd.concurrency_timestamp <= NOW() - INTERVAl p_conc_lock MINUTE
                     
							AND CASE
									WHEN HOUR(NOW()) BETWEEN 8 AND 20
									THEN 1
									WHEN COALESCE(`collectionsmax`.`FN_Pct_TZ_Open_Accts`(d.id,p_in_dst_observance),0) AND COALESCE(tzd.is_open,0) =1
                                    THEN 1
									ELSE 0
								END = 1
							
                            ORDER BY 
								CASE WHEN d.daysinstatus > 7 THEN d.daysinstatus ELSE 0 END DESC,
                                s.sequence ASC,
								DATE(d.agent_status_timestamp) ASC,
                                s.sub_sequence ASC
							LIMIT 1;
	

 DO RELEASE_LOCK(p_pool_lock_id);


 
 END IF; # Lock In Effect

 DO RELEASE_lock(p_pool_lock_id);
 IF p_account_id != 0
 THEN UPDATE collectionsmax.DropDetails
 SET
	concurrency_timestamp = NOW(6),
    concurrency_last_user = p_drop_user
WHERE
	dbase_id = p_account_id
    AND drop_id = p_pool_id;
    SET p_message = 'Account Found';
END IF;


 SET p_seq = p_seq + 1;
 
 
END WHILE; # Find Acct Loop
    
IF p_account_id = 0
THEN SET p_message = 'No Account Found';
END IF; 
  
 IF p_lock_status = 0
 THEN SET p_message = CONCAT('Lock In Effect Lock ID: ',p_pool_lock_id,'Please try again');
 END IF;
 
 END IF;
 
 IF p_agent_id = 0
THEN SET p_message = 'Agent Does Not Exist';
END IF;

	
    SELECT
		p_account_id AS `AccoutId`,
        p_message AS `Message`;
    
	# For troubleshooting
	/* 
     SELECT 
		p_pool_id,
		p_weight,
        p_conc_lock,
        p_seq,
        p_account_id,
        p_workflow_template_id,
        p_pct_acct_tz_open,
        p_attempts,
        p_seq,
        p_max_seq,
        collectionsmax.FN_Pct_TZ_Open_Accts(p_account_id,p_in_dst_observance),
        p_message,
        p_agent_id,
        p_pool_lock_id,
        p_file_number,
        p_lock_status,
        @select_pool;
      */
      
      
	
END //
DELIMITER ;