motor

SELECT product.*, product_detail.*, product_meta.*, features.*, features.count as count, coverages.*, questionnaires.*, additional_coverages.*, zone_territory.*, @zone_one_top:=CAST(2.11 AS DECIMAL(15,2)) , @zone_one_bottom:=CAST(1.76 AS DECIMAL(15,2)) , @zone_two_top:=CAST(2.16 AS DECIMAL(15,2)) , @zone_two_bottom:=CAST(1.8 AS DECIMAL(15,2)) , @zone_three_top:=CAST(0.8 AS DECIMAL(15,2)) , @zone_three_bottom:=CAST(0.67 AS DECIMAL(15,2)) , (CASE WHEN questionnaires.degradation_year_condition<1 THEN @value_motorcycle:=100000000-(100000000/100)*questionnaires.degradation_rate ELSE @value_motorcycle:=100000000 END), @percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0 THEN (@value_motorcycle/100)*@percentages_price ELSE 0 END), (CASE WHEN coverages.territory_custom_rate IS NULL THEN (CASE WHEN zone_territory.zone_id=1 THEN (CASE WHEN coverages.territory_one_rate="top" THEN @percentages:=@zone_one_top WHEN coverages.territory_one_rate="bottom" THEN @percentages:=@zone_one_bottom ELSE @percentages:=CAST(coverages.territory_one_rate AS DECIMAL(15,2)) END) WHEN zone_territory.zone_id=2 THEN (CASE WHEN coverages.territory_two_rate="top" THEN @percentages:=@zone_two_top WHEN coverages.territory_two_rate="bottom" THEN @percentages:=@zone_two_bottom ELSE @percentages:=CAST(coverages.territory_two_rate AS DECIMAL(15,2)) END) ELSE (CASE WHEN coverages.territory_three_rate="top" THEN @percentages:=@zone_three_top WHEN coverages.territory_three_rate="bottom" THEN @percentages:=@zone_three_bottom ELSE @percentages:=CAST(coverages.territory_three_rate AS DECIMAL(15,2)) END) END) ELSE @percentages:=CAST(coverages.territory_custom_rate AS DECIMAL(15,2)) END), (CASE WHEN questionnaires.additional_year_condition<1 THEN @addons_percentages:=(questionnaires.additional_rate/100) ELSE @addons_percentages:=0 END), @premx:=(@value_motorcycle/100)*(@percentages + (1-questionnaires.additional_year_condition ) * (@percentages * @addons_percentages)), @premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premx+@amount_price, @premx), @premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), @premi:=IF(@calculate IS NOT NULL AND @calculate > 0, @premi+@calculate, @premi), @premi:=IF(@lawsuit IS NOT NULL AND @lawsuit > 0, @premi+@lawsuit, @premi), @premi:=IF(@motorcycle_in IS NOT NULL AND @motorcycle_in > 0, @premi+@motorcycle_in, @premi), @admin_fee:=(CASE WHEN questionnaires.admin_fee < 100 THEN (@premi/100)*questionnaires.admin_fee ELSE questionnaires.admin_fee END), @admin_fee:=IF(@admin_fee=NULL, 0, @admin_fee), @materai_fee:=(CASE WHEN questionnaires.materai_fee < 100 THEN (@premi/100)*questionnaires.materai_fee ELSE questionnaires.materai_fee END), @materai_fee:=IF(@materai_fee=NULL, 0, @materai_fee), @premi:=ROUND(@premi+@admin_fee+@materai_fee), CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, product.id as product_id FROM insurance_product product LEFT JOIN (SELECT * FROM insurance_questionnaire_motorcycle) questionnaires ON questionnaires.product_id = product.id LEFT JOIN (SELECT * FROM insurance_feature_motorcycle) features ON features.product_id = product.id LEFT JOIN (SELECT * FROM insurance_coverage_motorcycle_total_loss_only) coverages ON coverages.product_id = product.id LEFT JOIN (SELECT * FROM insurance_additional_motorcycle) additional_coverages ON additional_coverages.product_id = product.id LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id LEFT JOIN (SELECT * FROM zone_license_plate_mv) zone_territory ON true WHERE (IF(questionnaires.motorcycle_value_min IS NOT NULL, questionnaires.motorcycle_value_min <= 100000000, questionnaires.motorcycle_value_min IS NULL) AND IF(questionnaires.motorcycle_value_max IS NOT NULL, questionnaires.motorcycle_value_max >= 100000000, questionnaires.motorcycle_value_max IS NULL) AND IF(questionnaires.age_vehicle_max IS NOT NULL, questionnaires.age_vehicle_max >= 1, questionnaires.age_vehicle_max IS NULL) ) AND product.type = "motorcycle" AND product.is_active = 1 AND zone_territory.license_code="G" AND product.type_of_insurance="total_loss_only" AND provider.is_active=1 GROUP BY product.id HAVING premi IS NOT NULL ORDER BY premi ASC, features.count DESC,features.total_amount_of_features DESC, product.rates DESC, (product.promo > "") DESC,product.viewers DESC, product.id DESC LIMIT 0,1