SHARE
TWEET

Property

a guest Jan 17th, 2019 69 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top