daily pastebin goal
24%
SHARE
TWEET

motor

a guest Jan 17th, 2019 68 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_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
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