DROP PROCEDURE IF EXISTS getMaterials;
CREATE PROCEDURE getMaterials
(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` TINYINT(1),
    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` TINYINT(1), 
    IN `_prismaSX` DECIMAL(8,2),
    IN `_basePrismaSX` INT, 
    IN `_decentramentoSX` DECIMAL(8,2),
    IN `_baseSpecialeSX` INT,
    IN `_id_geometria` INT,
    IN `_id_disponibilita` INT,
    IN `_id_famiglia` INT,
    IN `_id_disegno` 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 m.id, m.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								
						JOIN materials m ON m.id = p.material_id
						WHERE p.orderable = 1				
						
						-- filtro per geometria
						AND gm.id = ?
						
						-- filtro per disponibilita
						AND d.id = ?
						
						-- filtro per famiglia
						AND f.id = ?
						
						-- filtro per disegno
						AND ds.id = ?
						
						-- lente DX
						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') AND ? BETWEEN ds.min_height AND ds.max_height, 1=1)
						AND IF(? = 1, UPPER(d.name) = 'RICETTA', 1=1)			
						
						-- lente SX
						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') 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 @id_famiglia = _id_famiglia;
					SET @id_disegno = _id_disegno;
					
					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, @id_famiglia, @id_disegno,
					@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 m.id, m.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	
						JOIN materials m ON m.id = p.material_id
						WHERE p.orderable = 1
						
						-- filtro per geometria
						AND gm.id = ?
						
						-- filtro per disponibilita
						AND d.id = ?
						
						-- filtro per famiglia
						AND f.id = ?
						
						-- filtro per disegno
						AND ds.id = ?
						
						-- dati di prescrizione
						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') 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 @id_famiglia = _id_famiglia;
					SET @id_disegno = _id_disegno;
					
					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, @id_famiglia, @id_disegno,
					@diametro, @diametro, @sfero, @cilindro, @cilindro, @gamma, @addizione, @addizione, @altezzaMontaggio, @altezzaMontaggio, @prescrizioniUlteriori;								
					
					DEALLOCATE PREPARE stmt;
					
			END IF;																																								
														

	 END;