Property

SELECT product.*, product_detail.*, product_meta.*, features.*, features.count as count, coverages.*, questionnaires.*, additional_coverages.*, zone_territory.*, @zone:=0, @others:=0, @percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0 THEN (100000000/100)*@percentages_price ELSE 0 END), (CASE WHEN zone_territory.zone_id=1 THEN (CASE WHEN coverages.zone_one_rate="fixed" THEN @fixed:=0.76 ELSE @fixed:=coverages.zone_one_rate END) WHEN zone_territory.zone_id=2 THEN (CASE WHEN coverages.zone_two_rate="fixed" THEN @fixed:=0.79 ELSE @fixed:=coverages.zone_two_rate END) WHEN zone_territory.zone_id=3 THEN (CASE WHEN coverages.zone_three_rate="fixed" THEN @fixed:=1.04 ELSE @fixed:=coverages.zone_three_rate END) WHEN zone_territory.zone_id=4 THEN (CASE WHEN coverages.zone_four_rate="fixed" THEN @fixed:=1.35 ELSE @fixed:=coverages.zone_four_rate END) ELSE (CASE WHEN coverages.zone_five_rate="fixed" THEN @fixed:=1.6 ELSE @fixed:=coverages.zone_five_rate END) END), @premi:=(CASE WHEN product.type_of_insurance="earthquake" THEN (CASE WHEN coverages.zone_custom_rate IS NOT NULL AND coverages.zone_custom_rate > 0 THEN (100000000/1000)*coverages.zone_custom_rate ELSE (100000000/1000)*@fixed END) END), @premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premi+@amount_price, @premi), @premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), @premi:=@premi+@zone, @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_property) questionnaires ON questionnaires.product_id = product.id LEFT JOIN (SELECT * FROM insurance_feature_property) features ON features.product_id = product.id LEFT JOIN (SELECT * FROM insurance_coverage_property_fire_earthquake) coverages ON coverages.product_id = product.id LEFT JOIN (SELECT * FROM insurance_additional_property) as 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_territory_earthquake) zone_territory ON true WHERE (IF(questionnaires.earthquake_zone_restriction IS NOT NULL, questionnaires.earthquake_zone_restriction NOT LIKE "%zone_five%", questionnaires.earthquake_zone_restriction IS NULL) AND IF(questionnaires.what_type_of_the_property IS NOT NULL, questionnaires.what_type_of_the_property LIKE "%house%", questionnaires.what_type_of_the_property IS NULL) AND IF(questionnaires.why_are_you_looking_for_cover IS NOT NULL, questionnaires.why_are_you_looking_for_cover LIKE "%bought.a.new.house%", questionnaires.why_are_you_looking_for_cover IS NULL) AND IF(questionnaires.earthquake_class_construction IS NOT NULL, questionnaires.earthquake_class_construction LIKE "ęrthquake_class_construction_one%", questionnaires.earthquake_class_construction IS NULL) ) AND product.type = "property" AND product.is_active = 1 AND zone_territory.city_id=62 AND coverages.category=1 AND coverages.class=1 AND product.type_of_insurance="earthquake" 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