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 ;