Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT product.*, product_detail.*, product_meta.*, features.*, features.count as count, coverages.*, questionnaires.*, additional_coverages.*, zone_territory.*, @zone_one_top:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.56 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.69 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.46 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.3 WHEN questionnaires.car_premi_calc>800000000 THEN 0.24 END), @zone_two_top:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.78 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.53 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.42 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.3 WHEN questionnaires.car_premi_calc>800000000 THEN 0.24 END), @zone_three_top:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.56 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.48 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.35 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.27 WHEN questionnaires.car_premi_calc>800000000 THEN 0.24 END), @zone_one_bottom:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.47 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.63 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.41 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.25 WHEN questionnaires.car_premi_calc>800000000 THEN 0.2 END), @zone_two_bottom:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.65 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.44 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.38 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.25 WHEN questionnaires.car_premi_calc>800000000 THEN 0.2 END), @zone_three_bottom:=(CASE WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<=125000000 THEN 0.51 WHEN questionnaires.car_premi_calc>125000000 AND questionnaires.car_premi_calc<=200000000 THEN 0.44 WHEN questionnaires.car_premi_calc>200000000 AND questionnaires.car_premi_calc<=400000000 THEN 0.29 WHEN questionnaires.car_premi_calc>400000000 AND questionnaires.car_premi_calc<=800000000 THEN 0.23 WHEN questionnaires.car_premi_calc>800000000 THEN 0.2 END), @accident:=5000000, @value_car:=(CASE WHEN questionnaires.degradation_year_condition<1 THEN 100000000-(100000000/100)*questionnaires.degradation_rate ELSE 100000000 END), @percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0 THEN (@value_car/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_car/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(@car_in IS NOT NULL AND @car_in > 0, @premi+@car_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:=FLOOR(@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 *,(CASE WHEN degradation_year_condition<1 THEN @value_car:=100000000-(((100000000/100)*degradation_rate)) ELSE @value_car:=100000000 END) as car_premi_calc FROM insurance_questionnaire_car) questionnaires ON questionnaires.product_id = product.id LEFT JOIN (SELECT * FROM insurance_feature_car) features ON features.product_id = product.id LEFT JOIN (SELECT * FROM insurance_coverage_car_total_loss_only) coverages ON coverages.product_id = product.id LEFT JOIN (SELECT * FROM insurance_additional_car) 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.car_value_min IS NOT NULL, questionnaires.car_value_min <= 100000000, questionnaires.car_value_min IS NULL) AND IF(questionnaires.car_value_max IS NOT NULL, questionnaires.car_value_max >= 100000000, questionnaires.car_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 = "car" AND product.is_active = 1 AND zone_territory.license_code="Z" AND product.type_of_insurance="total_loss_only" AND (CASE WHEN questionnaires.car_premi_calc > 800000000 THEN coverages.up_to=0 WHEN questionnaires.car_premi_calc > 400000000 AND questionnaires.car_premi_calc <=800000000 THEN coverages.up_to=800000000 WHEN questionnaires.car_premi_calc > 200000000 AND questionnaires.car_premi_calc <=400000000 THEN coverages.up_to=400000000 WHEN questionnaires.car_premi_calc > 125000000 AND questionnaires.car_premi_calc <=200000000 THEN coverages.up_to=200000000 ELSE coverages.up_to=125000000 END) 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
