CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_RandCluster`()
BEGIN

	declare var_uid text;
	declare var_hh02 text;
	declare var_hh03 text;
	declare var_hh07 text;
	declare var_hh08 text;
	declare var_hhdt text;
	declare var_id_org text;

	declare var_hh02_new text;
	declare var_count integer;
	declare var_total double;
	declare var_sample_interval double;
	declare var_rStart double;
    declare var_nextStop double;
	declare var_rcount integer;
	declare var_randno1 integer;
	declare var_randno2 integer;
	DECLARE done INT DEFAULT 0;
	DECLARE done1 INT DEFAULT 0;



	Block1: BEGIN       
    

		#declare cur cursor for SELECT cluster from nns2018.enumblock where closed='1' order by cluster;       
		declare cur cursor for  
    SELECT 
        `l`.`hh02` AS `cluster`
		FROM
			`nns2018`.`listings` `l` 
			STRAIGHT_JOIN 
				(
					SELECT 
						MAX(LPAD(`nns2018`.`listings`.`_id`, 9, 0)) AS `_id`,
						`nns2018`.`listings`.`hh02` AS `hh02`
					FROM
						`nns2018`.`listings` 
					WHERE
						((`nns2018`.`listings`.`username` NOT IN ('dmu@aku'))
						AND (NOT ((`nns2018`.`listings`.`username` LIKE '%user%')))
						AND (`nns2018`.`listings`.`clustercode` < 6000))
					GROUP BY `nns2018`.`listings`.`hh02`
                ) `s` 
			ON (((LPAD(`l`.`_id`, 9, 0) = `s`.`_id`)
				AND (`l`.`hh02` = `s`.`hh02`)))
               left join (Select hh02 from nns2018.bl_randomised group by hh02) r on l.hh02 = r.hh02 
		WHERE
			(`l`.`hh04` = 8)
            AND ((`l`.`username` NOT IN ('dmu@aku'))
            AND NOT (`l`.`username` LIKE '%user%'))
            AND (`l`.`hh02` < 6000)
            and r.hh02 is null
		ORDER BY `l`.`hh02`; 

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
		open cur;
		REPEAT            
			fetch cur into var_hh02;
                IF (((SELECT COUNT(*) FROM bl_randomised WHERE hh02=var_hh02) <= 0) 
                AND (SELECT count(*) FROM nns2018.linelisting_status group by cluster_code having cluster_code = var_hh02) =1 ) Then
					Block2:BEGIN					
						DECLARE cur1 cursor for  SELECT a.uid, a.hh02, lpad(a.hh03, 4, '0'), if(a.hh07 > 0, lpad(a.hh07, 3, '0'), a.hh07), a.hh08, a.hhdt, e.id_org FROM nns2018.listings a 
												left join nns2018.enumblock e on a.hh02 = e.cluster
												WHERE (hh07 > 0) and a.hh02 = var_hh02 and (left(a.hh08,2) != 'dd' and left(a.hh08, 4) != 'dele')
												ORDER BY lpad(a.hh03,4,0), lpad(hh07,3,0);
												
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
						
						open cur1;			
		                
						select FOUND_ROWS() into var_total;				 
						if (var_total > 20) Then
                        
							set var_sample_interval = var_total / 20;
							set var_rStart = rand() * (var_sample_interval - 1) + 1;
							set var_nextStop = var_rStart;
							set var_count = 0;
							set var_rCount = 0;
                            #select var_total, var_hh02, var_sample_interval, var_rStart;
							REPEAT
								
								fetch cur1 into var_uid, var_hh02, var_hh03, var_hh07, var_hh08, var_hhdt, var_id_org;
									
									set var_count = var_count + 1;    
								   
									if (var_count = floor(var_nextStop)) then
										set var_nextStop = var_nextStop + var_sample_interval;
                                        
                                        # Fixed blank Head of Household name
                                        if var_hh08 = '' then set var_hh08 = '-none-'; End if;
                                        set var_rCount = var_rCount + 1;
										INSERT into nns2018.bl_randomised(uid, hh02, hh03, hh07, hh08, hhss, compid, total, randno, quot, sno, hhdt, id_org, con_id, user_id, server_id) values(var_uid, var_hh02, var_hh03, var_hh07, var_hh08, 1, var_count, var_total,var_rStart, var_sample_interval, var_rCount, var_hhdt, var_id_org, connection_id(), user(), @@server_id);
                                        
									end if;
								
							UNTIL done1 or var_rCount >= 20 END REPEAT;
						end if;
                        if ( var_total > 0 and var_total <=20) then
							set var_nextStop = var_rStart;
							set var_count = 0;
							set var_rCount = 0;
                            #select var_total, var_hh02, var_sample_interval, var_rStart;
							REPEAT
								
								fetch cur1 into var_uid, var_hh02, var_hh03, var_hh07, var_hh08, var_hhdt, var_id_org;
									
									set var_count = var_count + 1;    
								   #var_rStart, var_sample_interval,
								
                                        
                                        # Fixed blank Head of Household name
                                        if var_hh08 = '' then set var_hh08 = '-none-'; End if;
                                        set var_rCount = var_rCount + 1;
										INSERT into nns2018.bl_randomised(uid, hh02, hh03, hh07, hh08, hhss, compid, total, randno, quot, sno, hhdt, id_org, con_id, user_id, server_id) values(var_uid, var_hh02, var_hh03, var_hh07, var_hh08, 1, var_count, var_total,var_rStart, var_sample_interval, var_rCount, var_hhdt, var_id_org, connection_id(), user(), @@server_id);
                                        
									
								
							UNTIL done1 or var_rCount >= var_total END REPEAT;
												                         
                            
                        End if;

						close cur1;                         
					END Block2; 
                    set done1 = null;
				End IF;
			UNTIL done END REPEAT;
		close cur;    
	END Block1;
END