DROP PROCEDURE IF EXISTS getFamilies;
CREATE PROCEDURE getFamilies (
        IN _tipo ENUM("DX", "SX", "COPPIA"),
        IN _sferoDX DECIMAL(8, 2), 
        IN _cilindroDX DECIMAL(8, 2), 
        IN _asseDX INT,
        IN _diametroDX INT,
        IN _addizioneDX DECIMAL(8, 2),
        IN _altezzaMontaggioDX DECIMAL(8, 2),
        IN _prescrizioniUlterioriDX INT,
        IN _prismaDX DECIMAL(8, 2),
        IN _basePrismaDX INT,
        IN _decentramentoDX DECIMAL(8, 2),
        IN _baseSpecialeDX INT,
        IN _sferoSX DECIMAL(8, 2),
        IN _cilindroSX DECIMAL(8, 2),
        IN _asseSX INT,
        IN _diametroSX INT,
        IN _addizioneSX DECIMAL(8, 2),
        IN _altezzaMontaggioSX DECIMAL(8, 2),
        IN _prescrizioniUlterioriSX INT,
        IN _prismaSX DECIMAL(8, 2),
        IN _basePrismaSX INT,
        IN _decentramentoSX DECIMAL(8, 2),
        IN _baseSpecialeSX INT,
        IN _id_geometria INT,
        IN _id_disponibilita INT
         )
BEGIN	 			
            
                    DECLARE _diametro, _asse, _basePrisma, _baseSpeciale INTEGER;
                    DECLARE _sfero, _cilindro, _addizione, _gamma, _gammaDX, _gammaSX, _altezzaMontaggio, _decentramento, _prisma DECIMAL(8, 2);																																																																													
                    DECLARE _prescrizioniUlteriori TINYINT(1);
                                                                                    
                    
                    if(_sferoDX > 0 AND _cilindroDX < 0) then
                        SET _sferoDX = (_sferoDX + _cilindroDX);
                        SET _cilindroDX = -(_cilindroDX);				
                    end if;
                    
                    
                    if(_sferoSX > 0 AND _cilindroSX < 0) then
                        SET _sferoSX = (_sferoSX + _cilindroSX);
                        SET _cilindroSX = -(_cilindroSX);				
                    end if;
                                
                    
                    
                    IF(_tipo="DX") THEN				
                        SET _sfero = _sferoDX;																
                        SET _cilindro = _cilindroDX;	
                        SET _asse = _asseDX;
                        SET _diametro = _diametroDX;
                        SET _addizione = _addizioneDX;
                        SET _altezzaMontaggio = _altezzaMontaggioDX;				
                        SET _prescrizioniUlteriori = _prescrizioniUlterioriDX;
                        SET _basePrisma = _basePrismaDX;
                        SET _baseSpeciale = _baseSpecialeDX;
                        SET _decentramento = _decentramentoDX;
                        SET _prisma = _prismaDX;
                        SET _gamma = (_sferoDX+_cilindroDX);		
                    ELSEIF(_tipo="SX") THEN
                        SET _sfero = _sferoSX;																
                        SET _cilindro = _cilindroSX;	
                        SET _asse = _asseSX;
                        SET _diametro = _diametroSX;
                        SET _addizione = _addizioneSX;
                        SET _altezzaMontaggio = _altezzaMontaggioSX;				
                        SET _prescrizioniUlteriori = _prescrizioniUlterioriSX;
                        SET _basePrisma = _basePrismaSX;
                        SET _baseSpeciale = _baseSpecialeSX;
                        SET _decentramento = _decentramentoSX;
                        SET _prisma = _prismaSX;
                        SET _gamma = (_sferoSX+_cilindroSX);
                    ELSE						
                        SET _gammaDX = (_sferoDX+_cilindroDX);								
                        SET _gammaSX = (_sferoSX+_cilindroSX);				
                    END IF;		
        
                    IF(_tipo="COPPIA") THEN		
                                
                            PREPARE stmt FROM "
                                SELECT DISTINCT f.id, f.name 			
                                FROM ranges gDX INNER JOIN ranges gSX USING(product_id)
                                JOIN products p ON p.id = gDX.product_id 				
                                JOIN availabilities d ON d.id = p.availability_id				
                                JOIN geometries gm ON gm.id = p.geometry_id
                                JOIN families f ON f.id = p.family_id
                                JOIN designs ds ON ds.id = p.design_id								
                                WHERE p.orderable = 1
                                -- filter for geometry
                                AND gm.id = ?
                                -- filter for availability
                                AND d.id = ?				
                                
                               
                                AND IF(UPPER(d.name) = \"SERIE\", ABS(?) <= gDX.diametar_max, 1=1)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN gDX.diametar_min AND gDX.diametar_max, 1=1)			
                                AND IF(UPPER(d.name) = \"SERIE\", ? = gDX.sfero_max, 1=1)
                                AND IF(UPPER(d.name) = \"SERIE\", ? = gDX.cylinder_max, ABS(?) <= gDX.cylinder_max)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN gDX.min_range AND gDX.max_range, 1=1)
                                AND IF(? IS NOT NULL, UPPER(gm.name)!=\"MONOFOCALE\" AND ? BETWEEN gDX.min_addition AND gDX.max_addition, UPPER(gm.name)=\"MONOFOCALE\")
                                AND IF(? IS NOT NULL, (UPPER(gm.name)=\"PROGRESSIVA\" OR UPPER(gm.name)=\"BIFOCALE\" OR UPPER(gm.name)=\"PROGRESSIVA INDOOR\") AND ? BETWEEN ds.min_height AND ds.max_height, 1=1)
                                AND IF(? = 1, UPPER(d.name) = \"RICETTA\", 1=1)			
                                
                               
                                AND IF(UPPER(d.name) = \"SERIE\", ABS(?) <= gSX.diametar_max, 1=1)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN gSX.diametar_min AND gSX.diametar_max, 1=1)				
                                AND IF(UPPER(d.name) = \"SERIE\", ? = gSX.sfero_max, 1=1)
                                AND IF(UPPER(d.name) = \"SERIE\", ? = gSX.cylinder_max, ABS(?) <= gSX.cylinder_max)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN gSX.min_range AND gSX.max_range, 1=1)
                                AND IF(? IS NOT NULL, UPPER(gm.name)!=\"MONOFOCALE\" AND ? BETWEEN gSX.min_addition AND gSX.max_addition, UPPER(gm.name)=\"MONOFOCALE\")
                                AND IF(? IS NOT NULL, (UPPER(gm.name)=\"PROGRESSIVA\" OR UPPER(gm.name)=\"BIFOCALE\" OR UPPER(gm.name)=\"PROGRESSIVA INDOOR\") AND ? BETWEEN ds.min_height AND ds.max_height, 1=1)
                                AND IF(? = 1, UPPER(d.name) = \"RICETTA\", 1=1)
                            ";
                           
                            SET @id_geometria = _id_geometria;
                            SET @id_disponibilita = _id_disponibilita;
                            SET @diametroDX = _diametroDX;
                            SET @sferoDX = _sferoDX;
                            SET @cilindroDX = _cilindroDX;
                            SET @gammaDX = _gammaDX;
                            SET @addizioneDX = _addizioneDX;
                            SET @altezzaMontaggioDX = _altezzaMontaggioDX;
                            SET @prescrizioniUlterioriDX = _prescrizioniUlterioriDX;
                            SET @diametroSX = _diametroSX;
                            SET @sferoSX = _sferoSX;
                            SET @cilindroSX = _cilindroSX;
                            SET @gammaSX = _gammaSX;
                            SET @addizioneSX = _addizioneSX;
                            SET @altezzaMontaggioSX = _altezzaMontaggioSX;
                            SET @prescrizioniUlterioriSX = _prescrizioniUlterioriSX;
                            
                            
                            EXECUTE stmt USING @id_geometria, @id_disponibilita, @diametroDX, @diametroDX, @sferoDX, @cilindroDX, @cilindroDX, @gammaDX, @addizioneDX, @addizioneDX, @altezzaMontaggioDX, @altezzaMontaggioDX, @prescrizioniUlterioriDX,
                            @diametroSX, @diametroSX, @sferoSX, @cilindroSX, @cilindroSX, @gammaSX, @addizioneSX, @addizioneSX, @altezzaMontaggioSX, @altezzaMontaggioSX, @prescrizioniUlterioriSX;
                            DEALLOCATE PREPARE stmt;
                
                    ELSE										
                        
                            PREPARE stmt FROM "
                                SELECT DISTINCT f.id, f.name							
                                FROM ranges g 
                                JOIN products p ON p.id = g.product_id 
                                JOIN availabilities d ON d.id = p.availability_id
                                JOIN geometries gm ON gm.id = p.geometry_id
                                JOIN families f ON f.id = p.family_id
                                JOIN designs ds ON ds.id = p.design_id				
                                WHERE p.orderable = 1
                                -- filter for geometry
                                AND gm.id = ?
                                -- filter for availability
                                AND d.id = ?

                                AND IF(UPPER(d.name) = \"SERIE\", ABS(?) <= g.diametar_max, 1=1)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN g.diametar_min AND g.diametar_max, 1=1)
                                AND IF(UPPER(d.name) = \"SERIE\", ? = g.sfero_max, 1=1)
                                AND IF(UPPER(d.name) = \"SERIE\", ? = g.cylinder_max, ABS(?) <= g.cylinder_max)
                                AND IF(UPPER(d.name) = \"RICETTA\", ? BETWEEN g.min_range AND g.max_range, 1=1)
                                AND IF(? IS NOT NULL, UPPER(gm.name)!=\"MONOFOCALE\" AND ? BETWEEN g.min_addition AND g.max_addition, UPPER(gm.name)=\"MONOFOCALE\")		
                                AND IF(? IS NOT NULL, (UPPER(gm.name)=\"PROGRESSIVA\" OR UPPER(gm.name)=\"BIFOCALE\" OR UPPER(gm.name)=\"PROGRESSIVA INDOOR\") AND ? BETWEEN ds.min_height AND ds.max_height, 1=1)
                                AND IF(? = 1, UPPER(d.name) = \"RICETTA\", 1=1)";

                            SET @id_geometria = _id_geometria;
                            SET @id_disponibilita = _id_disponibilita;             
                            SET @diametro = _diametro;
                            SET @sfero = _sfero;
                            SET @cilindro = _cilindro;
                            SET @gamma = _gamma;
                            SET @addizione = _addizione;
                            SET @altezzaMontaggio = _altezzaMontaggio;
                            SET @prescrizioniUlteriori = _prescrizioniUlteriori;
                                                                                     
                            EXECUTE stmt USING @id_geometria, @id_disponibilita, @diametro, @diametro, @sfero, @cilindro, @cilindro, @gamma, @addizione, @addizione, @altezzaMontaggio, @altezzaMontaggio, @prescrizioniUlteriori;								
                            DEALLOCATE PREPARE stmt;
                            
                    END IF;																																								
                                                                
        
             END;