DROP PROCEDURE IF EXISTS getProducts; CREATE PROCEDURE getProducts ( 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, IN _id_famiglia INT, IN _id_disegno INT, IN _id_materiale INT, IN _id_shop 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; block_prodotti:BEGIN DECLARE _id_prodotto INTEGER; DECLARE _prezzo, _private_price, _shop_price, _supplementi DECIMAL(8,2) DEFAULT 0.00; DECLARE _codice, _nome, _pagina_catalogo, _geometria, _disponibilita, _famiglia, _disegno, _canale, _materiale VARCHAR(255); DECLARE _modulo_ist, _ist_monitor, _angolo_avv, _colorable , _treatable TINYINT(1); DECLARE _diametro_min INT(11); DECLARE _diametro_max INT(11); DECLARE _diametro_dx INT(11); DECLARE _diametro_sx INT(11); DECLARE _id_gamma BIGINT(20); DECLARE _avviso TEXT; DECLARE _rifrazione_materiale DECIMAL(8,2); DECLARE products_HAS_ROWS TINYINT DEFAULT 1; DECLARE products_copy CURSOR FOR SELECT DISTINCT p.id, p.code, p.name, p.module_ist, p.monitor_ist, p.angle_adv, p.colorable, p.treatable, p.notice, p.catalog_page, gm.name as geometry, d.name as availability, MAX(gDX.diametar_max) as diameter_max, MIN(gDX.diametar_min) as diameter_dx, MIN(gSX.diametar_min) as diameter_sx, gDX.id as range_id, f.name as family, ds.name as design, c.name as channel, m.name as material, m.refraction, CASE WHEN (gDX.public_price IS NULL AND gSX.public_price IS NULL) THEN (p.public_price + p.public_price) WHEN (gDX.public_price IS NOT NULL AND gSX.public_price IS NULL) THEN (gDX.public_price + p.public_price) WHEN (gDX.public_price IS NULL AND gSX.public_price IS NOT NULL) THEN (gSX.public_price + p.public_price) WHEN (gDX.public_price IS NOT NULL AND gSX.public_price IS NOT NULL) THEN (gDX.public_price + gSX.public_price) END as public_price, CASE WHEN (gDX.private_price IS NULL AND gSX.private_price IS NULL) THEN (p.private_price + p.private_price) WHEN (gDX.private_price IS NOT NULL AND gSX.private_price IS NULL) THEN (gDX.private_price + p.private_price) WHEN (gDX.private_price IS NULL AND gSX.private_price IS NOT NULL) THEN (gSX.private_price + p.private_price) WHEN (gDX.private_price IS NOT NULL AND gSX.private_price IS NOT NULL) THEN (gDX.private_price + gSX.private_price) END as private_price , product_shop.price as shop_price FROM ranges gDX INNER JOIN ranges gSX USING(product_id) JOIN products p ON p.id = gDX.product_id LEFT JOIN product_shop ON p.id = product_shop.product_id AND product_shop.shop_id=_id_shop 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 LEFT JOIN channel_design dsc ON ds.id = dsc.design_id LEFT JOIN channels c ON c.id = dsc.channel_id JOIN materials m ON m.id = p.material_id WHERE p.orderable = 1 AND IF(_id_geometria IS NOT NULL, gm.id = _id_geometria, 1=1) AND IF(_id_disponibilita IS NOT NULL, d.id = _id_disponibilita, 1=1) AND IF(_id_famiglia IS NOT NULL, f.id = _id_famiglia, 1=1) AND IF(_id_disegno IS NOT NULL, ds.id = _id_disegno, 1=1) AND IF(_id_materiale IS NOT NULL, m.id = _id_materiale, 1=1) AND IF(UPPER(d.name) = 'SERIE', ABS(_diametroDX) <= gDX.diametar_max, 1=1) AND IF(UPPER(d.name) = 'RICETTA', _diametroDX BETWEEN gDX.diametar_min AND gDX.diametar_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _sferoDX = gDX.sfero_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _cilindroDX = gDX.cylinder_max, ABS(_cilindroDX) <= gDX.cylinder_max) AND IF(UPPER(d.name) = 'RICETTA', _gammaDX BETWEEN gDX.min_range AND gDX.max_range, 1=1) AND IF(_addizioneDX IS NOT NULL, UPPER(gm.name)!='MONOFOCALE' AND _addizioneDX BETWEEN gDX.min_addition AND gDX.max_addition, UPPER(gm.name)='MONOFOCALE') AND IF(_altezzaMontaggioDX IS NOT NULL, (UPPER(gm.name)='PROGRESSIVA' OR UPPER(gm.name)='BIFOCALE' OR UPPER(gm.name)='PROGRESSIVA INDOOR') AND _altezzaMontaggioDX BETWEEN ds.min_height AND ds.max_height, 1=1) AND IF(_prescrizioniUlterioriDX = 1, UPPER(d.name) = 'RICETTA', 1=1) AND IF(UPPER(d.name) = 'SERIE', ABS(_diametroSX) <= gSX.diametar_max, 1=1) AND IF(UPPER(d.name) = 'RICETTA', _diametroSX BETWEEN gSX.diametar_min AND gSX.diametar_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _sferoSX = gSX.sfero_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _cilindroSX = gSX.cylinder_max, ABS(_cilindroSX) <= gSX.cylinder_max) AND IF(UPPER(d.name) = 'RICETTA', _gammaSX BETWEEN gSX.min_range AND gSX.max_range, 1=1) AND IF(_addizioneSX IS NOT NULL, UPPER(gm.name)!='MONOFOCALE' AND _addizioneSX BETWEEN gSX.min_addition AND gSX.max_addition, UPPER(gm.name)='MONOFOCALE') AND IF(_altezzaMontaggioSX IS NOT NULL, (UPPER(gm.name)='PROGRESSIVA' OR UPPER(gm.name)='BIFOCALE' OR UPPER(gm.name)='PROGRESSIVA INDOOR') AND _altezzaMontaggioSX BETWEEN ds.min_height AND ds.max_height, 1=1) AND IF(_prescrizioniUlterioriSX = 1, UPPER(d.name) = 'RICETTA', 1=1) GROUP BY p.id; DECLARE products_single CURSOR FOR SELECT DISTINCT p.id, p.code, p.name, p.module_ist, p.monitor_ist, p.angle_adv, p.colorable, p.treatable, p.notice, p.catalog_page, gm.name as geometry, d.name as availability, MAX(g.diametar_max), MIN(g.diametar_min), g.id as range_id, f.name as family, ds.name as design, c.name as channel, m.name as material, m.refraction, IF(g.public_price IS NULL, p.public_price, g.public_price) as public_price, IF(g.private_price IS NULL, p.private_price, g.private_price) as private_price, product_shop.price as shop_price FROM ranges g JOIN products p ON p.id = g.product_id LEFT JOIN product_shop ON p.id = product_shop.product_id AND product_shop.shop_id=_id_shop 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 LEFT JOIN channel_design dsc ON ds.id = dsc.design_id LEFT JOIN channels c ON c.id = dsc.channel_id JOIN materials m ON m.id = p.material_id WHERE p.orderable = 1 AND IF(_id_geometria IS NOT NULL, gm.id = _id_geometria, 1=1) AND IF(_id_disponibilita IS NOT NULL, d.id = _id_disponibilita, 1=1) AND IF(_id_famiglia IS NOT NULL, f.id = _id_famiglia, 1=1) AND IF(_id_disegno IS NOT NULL, ds.id = _id_disegno, 1=1) AND IF(_id_materiale IS NOT NULL, m.id = _id_materiale, 1=1) AND IF(UPPER(d.name) = 'SERIE', ABS(_diametro) <= g.diametar_max, 1=1) AND IF(UPPER(d.name) = 'RICETTA', _diametro BETWEEN g.diametar_min AND g.diametar_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _sfero = g.sfero_max, 1=1) AND IF(UPPER(d.name) = 'SERIE', _cilindro = g.cylinder_max, ABS(_cilindro) <= g.cylinder_max) AND IF(UPPER(d.name) = 'RICETTA', _gamma BETWEEN g.min_range AND g.max_range, 1=1) AND IF(_addizione IS NOT NULL, UPPER(gm.name)!='MONOFOCALE' AND _addizione BETWEEN g.min_addition AND g.max_addition, UPPER(gm.name)='MONOFOCALE') AND IF(_altezzaMontaggio IS NOT NULL, (UPPER(gm.name)='PROGRESSIVA' OR UPPER(gm.name)='BIFOCALE' OR UPPER(gm.name)='PROGRESSIVA INDOOR') AND _altezzaMontaggio BETWEEN ds.min_height AND ds.max_height, 1=1) AND IF(_prescrizioniUlteriori = 1, UPPER(d.name) = 'RICETTA', 1=1) GROUP BY p.id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET products_HAS_ROWS = 0; IF(_tipo='COPPIA') THEN OPEN products_copy; PREPARE drop_tmp_products FROM 'DROP TEMPORARY TABLE IF EXISTS tmp_products'; PREPARE create_tmp_products FROM 'CREATE TEMPORARY TABLE tmp_products (id INT(11), code VARCHAR(200), name VARCHAR(200), module_ist TINYINT(1), monitor_ist TINYINT(1), angle_adv TINYINT(1), colorable TINYINT(1), treatable TINYINT(1), notice TEXT, catalog_page INTEGER, geometry VARCHAR(200), availability VARCHAR(200), diameter_max INT(11), diameter_dx INT(11), diameter_sx INT(11), id_gamma INT(11), family VARCHAR(200), design VARCHAR(200), channel VARCHAR(200), material VARCHAR(200), refraction_material DECIMAL(8,2), public_price DECIMAL(8,2),private_price DECIMAL(8,2),shop_price DECIMAL(8,2));'; EXECUTE drop_tmp_products; EXECUTE create_tmp_products; ELSE OPEN products_single; PREPARE drop_tmp_products FROM 'DROP TEMPORARY TABLE IF EXISTS tmp_products'; PREPARE create_tmp_products FROM 'CREATE TEMPORARY TABLE tmp_products (id INT(11), code VARCHAR(200), name VARCHAR(200), module_ist TINYINT(1), monitor_ist TINYINT(1), angle_adv TINYINT(1), colorable TINYINT(1), treatable TINYINT(1), notice TEXT, catalog_page INTEGER, geometry VARCHAR(200), availability VARCHAR(200), diameter_max INT(11), diameter_min INT(11), id_gamma INT(11), family VARCHAR(200), design VARCHAR(200), channel VARCHAR(200), material VARCHAR(200), refraction_material DECIMAL(8,2), public_price DECIMAL(8,2),private_price DECIMAL(8,2),shop_price DECIMAL(8,2));'; EXECUTE drop_tmp_products; EXECUTE create_tmp_products; END IF; loop_prodotti: LOOP IF products_HAS_ROWS THEN IF(_tipo='COPPIA')THEN FETCH products_copy INTO _id_prodotto, _codice, _nome, _modulo_ist, _ist_monitor, _angolo_avv, _colorable, _treatable, _avviso, _pagina_catalogo, _geometria, _disponibilita, _diametro_max, _diametro_dx, _diametro_sx, _id_gamma, _famiglia, _disegno, _canale, _materiale, _rifrazione_materiale, _prezzo, _private_price,_shop_price; ELSE FETCH products_single INTO _id_prodotto, _codice, _nome, _modulo_ist, _ist_monitor, _angolo_avv, _colorable, _treatable, _avviso, _pagina_catalogo, _geometria, _disponibilita, _diametro_max, _diametro_min, _id_gamma, _famiglia, _disegno, _canale, _materiale, _rifrazione_materiale, _prezzo, _private_price,_shop_price; END IF; block_supplementi:BEGIN DECLARE _id_product_supplement INT(11); DECLARE _operand, _operator VARCHAR(200); DECLARE _reference DECIMAL(8,2); DECLARE _supplement DECIMAL(8,2) DEFAULT 0.00; DECLARE supplements_HAS_ROWS TINYINT DEFAULT 1; DECLARE supplements CURSOR FOR SELECT product_id, operand, operator, reference, price FROM supplements ORDER BY product_id ASC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET supplements_HAS_ROWS = 0; OPEN supplements; loop_supplementi: LOOP IF supplements_HAS_ROWS THEN FETCH supplements INTO _id_product_supplement, _operand, _operator, _reference, _supplement; IF(_id_product_supplement = _id_prodotto OR _id_product_supplement IS NULL) THEN IF(UPPER(_tipo)='COPPIA')THEN IF(UPPER(_operand) = 'DIAMETRO') THEN CALL add_supplement(ABS(_diametroDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_diametroSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'SFERO') THEN CALL add_supplement(ABS(_sferoDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_sferoSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'CILINDRO') THEN CALL add_supplement(ABS(_cilindroDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_cilindroSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ADDIZIONE') THEN CALL add_supplement(ABS(_addizioneDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_addizioneSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ALTEZZA_MONTAGGIO') THEN CALL add_supplement(ABS(_altezzaMontaggioDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_altezzaMontaggioSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ASSE') THEN CALL add_supplement(ABS(_asseDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_asseSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'BASE_PRISMA') THEN CALL add_supplement(ABS(_basePrismaDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_basePrismaSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'BASE_SPECIALE') THEN CALL add_supplement(ABS(_baseSpecialeDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_baseSpecialeSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'DECENTRAMENTO') THEN CALL add_supplement(ABS(_decentramentoDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_decentramentoSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'PRISMA') THEN CALL add_supplement(ABS(_prismaDX), _operator, _reference, _supplement, _supplementi); CALL add_supplement(ABS(_prismaSX), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'GAMMA') THEN CALL add_supplement(_gammaDX, _operator, _reference, _supplement, _supplementi); CALL add_supplement(_gammaSX, _operator, _reference, _supplement, _supplementi); END IF; ELSE IF(UPPER(_operand) = 'DIAMETRO') THEN CALL add_supplement(ABS(_diametro), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'SFERO') THEN CALL add_supplement(ABS(_sfero), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'CILINDRO') THEN CALL add_supplement(ABS(_cilindro), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ADDIZIONE') THEN CALL add_supplement(ABS(_addizione), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ALTEZZA_MONTAGGIO') THEN CALL add_supplement(ABS(_altezzaMontaggio), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'ASSE') THEN CALL add_supplement(ABS(_asse), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'BASE_PRISMA') THEN CALL add_supplement(ABS(_basePrisma), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'BASE_SPECIALE') THEN CALL add_supplement(ABS(_baseSpeciale), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'DECENTRAMENTO') THEN CALL add_supplement(ABS(_decentramento), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'PRISMA') THEN CALL add_supplement(ABS(_prisma), _operator, _reference, _supplement, _supplementi); ELSEIF(UPPER(_operand) = 'GAMMA') THEN CALL add_supplement(_gamma, _operator, _reference, _supplement, _supplementi); END IF; END IF; SET _supplement = 0; END IF; ELSE CLOSE supplements; LEAVE loop_supplementi; END IF; END LOOP loop_supplementi; END block_supplementi; IF(_tipo='COPPIA')THEN INSERT INTO tmp_products (id, code, name, module_ist, monitor_ist, angle_adv, colorable, treatable, notice, catalog_page, geometry, availability, diameter_max, diameter_dx, diameter_sx, id_gamma, family, design, channel, material, refraction_material, public_price, private_price,shop_price) VALUES(_id_prodotto, _codice, _nome, _modulo_ist, _ist_monitor, _angolo_avv, _colorable, _treatable, _avviso, _pagina_catalogo, _geometria, _disponibilita, _diametro_max, _diametro_dx, _diametro_sx, _id_gamma, _famiglia, _disegno, _canale, _materiale, _rifrazione_materiale, (_prezzo + _supplementi), (_private_price + _supplementi),_shop_price); ELSE INSERT INTO tmp_products (id, code, name, module_ist, monitor_ist, angle_adv, colorable, treatable, notice, catalog_page, geometry, availability, diameter_max, diameter_min, id_gamma, family, design, channel, material, refraction_material, public_price, private_price,shop_price) VALUES(_id_prodotto, _codice, _nome, _modulo_ist, _ist_monitor, _angolo_avv, _colorable, _treatable, _avviso, _pagina_catalogo, _geometria, _disponibilita, _diametro_max, _diametro_min, _id_gamma, _famiglia, _disegno, _canale, _materiale, _rifrazione_materiale, (_prezzo + _supplementi),(_private_price + _supplementi),_shop_price); END IF; SET _supplementi = 0; ELSE IF(_tipo='COPPIA')THEN CLOSE products_copy; ELSE CLOSE products_single; END IF; LEAVE loop_prodotti; END IF; END LOOP loop_prodotti; SET @sql = CONCAT('SELECT DISTINCT * FROM tmp_products ORDER BY name ASC'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; EXECUTE drop_tmp_products; DEALLOCATE PREPARE drop_tmp_products; DEALLOCATE PREPARE create_tmp_products; END block_prodotti; END;