Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- namespace Thanatos\Modules\Search\Http\Controllers;
- use Illuminate\Http\Request;
- use Illuminate\Support\Facades\Auth;
- use Illuminate\Support\Facades\Cookie;
- use Illuminate\Support\Facades\DB;
- use Illuminate\Support\Facades\URL;
- use Session;
- use Thanatos\Http\Controllers\Controller;
- use LaravelLocalization;
- use Thanatos\Modules\Insurance\Http\Controllers\AdditionalController;
- use Thanatos\Modules\Insurance\Http\Controllers\FeatureController;
- use Thanatos\Modules\Insurance\Http\Controllers\RateController;
- use Thanatos\Modules\Insurance\Models\Insurance_Features_Additional_Prices;
- use Thanatos\Modules\Insurance\Models\Insurance_Product;
- use Thanatos\Modules\Insurance\Models\Insurance_Product_Detail;
- use Thanatos\Modules\Insurance\Models\Insurance_Product_Meta;
- use Thanatos\Modules\Insurance\Models\Insurance_Product_View;
- use Thanatos\Modules\Insurance\Models\Insurance_Provider;
- use Thanatos\Modules\Insurance\Models\Insurance_Provider_Detail;
- use Thanatos\Modules\Insurance\Models\InsuranceProductReview;
- use Thanatos\Modules\Insurance\Models\InsuranceProductPartner;
- use Thanatos\Modules\Insurance\Models\Zone_Territory_Car;
- use Thanatos\Modules\Insurance\Models\Zone_Territory_Earthquake;
- use Thanatos\Modules\Insurance\Models\Zone_Territory_Motorcycle;
- use Thanatos\Modules\Search\Models\Search_Record;
- use Thanatos\User;
- use App;
- use Carbon\Carbon;
- class SearchController extends Controller
- {
- public function __construct()
- {
- DB::enableQueryLog();
- }
- public function generateCompareHistory()
- {
- }
- public function getCompareQuery(Request $request){
- if ($request->has('compare')) {
- $in = '';
- $request = $request->all();
- $type = $request['type'];
- $compare = json_decode($request['compare'], true);
- $sql = 'SELECT product.*, features.*, ib.*, product_detail.*, product_meta.*, iprov.provider_name, iprov.provider_slug FROM insurance_product ip ';
- $sql .= 'LEFT JOIN (SELECT id, name as provider_name, slug as provider_slug FROM insurance_provider) iprov ON iprov.id = product.provider_id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) ipd ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) ipm ON product_meta.product_id = product.id ';
- switch ($type) {
- case 'property':
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_property) ifeat ON ifeat.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_benefit_property) ib ON ib.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_features_additional_prices) ifap ON ifap.product_id = product.id';
- $sql .= 'WHERE ';
- foreach ($compare as $key => $value) {
- $in .= '"' . $value . '"';
- if ($key < count($compare) - 1) {
- $in .= ',';
- }
- }
- $sql .= 'product.slug IN (' . $in . ') AND product.type = "' . $type . '"';
- break;
- case 'car':
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_car) ifeat ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_benefit_car) ib ON ib.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_features_additional_prices) ifap ON ifap.product_id = product.id';
- $sql .= 'WHERE ';
- foreach ($compare as $key => $value) {
- $in .= '"' . $value . '"';
- if ($key < count($compare) - 1) {
- $in .= ',';
- }
- }
- $sql .= 'product.slug IN (' . $in . ') AND product.type = "' . $type . '"';
- break;
- case 'motorcycle':
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_motorcycle) ifeat ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_benefit_motorcycle) ib ON ib.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_features_additional_prices) ifap ON ifap.product_id = product.id';
- $sql .= 'WHERE ';
- foreach ($compare as $key => $value) {
- $in .= '"' . $value . '"';
- if ($key < count($compare) - 1) {
- $in .= ',';
- }
- }
- $sql .= 'product.slug IN (' . $in . ') AND product.type = "' . $type . '"';
- break;
- }
- $sql .= 'GROUP BY product.name ';
- $database = DB::select($sql);
- if (count($database) > 1) {
- $previous = json_decode(file_get_contents(app_path('DatabaseJSON/compare.json')), true);
- $dumped = $previous;
- $comparer = array('on' => date('Y-m-d H:i:s'), 'between' => $database);
- $email = 'kzulfazriawan@gmail.com';
- if (count($previous) > 0) {
- foreach ($previous as $k => $v) {
- if ($k == $email) {
- array_push($dumped[$k], $comparer);
- } else {
- $dumped[$k] = array($comparer);
- }
- }
- } else {
- $dumped[$email] = array($comparer);
- }
- $product = json_encode($dumped);
- file_put_contents(app_path('DatabaseJSON/compare.json'), $product);
- return $database;
- }
- return [];
- }
- }
- public function snapshotIt(Request $request)
- {
- // clean the array first before processing
- $clone_data = $request->data;
- if (is_array($clone_data)) {
- foreach ($clone_data as $k => $v) {
- foreach ($v as $kEy => $vAl) {
- if (strpos($kEy, '@') !== false || strpos($kEy, '(') !== false) {
- unset($clone_data[$k][$kEy]);
- }
- }
- }
- }
- $request->data = $clone_data;
- if (Auth::check()) {
- $user = Auth::user()->id;
- } else {
- $user = 0;
- }
- /* yang gua butuhin itu
- * - bypass, name,
- *
- */
- // if(!file_exists(app_path('DatabaseJSON/searches-of-'.$user.'.json'))){
- // fopen(app_path('DatabaseJSON/searches-of-'.$user.'.json'), 'w');
- // }
- if (count($request->data) > 1 and !is_null($user)) {
- $dumped = array();
- // $previous = json_decode(file_get_contents(app_path('DatabaseJSON/searches-of-'.$user.'.json')), true);
- // $dumped = $previous;
- // if(count($previous) > 0) {
- // foreach ($previous as $k => $v) {
- // $dumped[date('Y-m-d H:i:s')] = $request->data;
- // }
- // } else {
- $id = generateSS();
- $tmp = $request->data;
- foreach($request->data as $key => $data) {
- if(isset($tmp[$key]['brief_id'])) {
- unset($tmp[$key]['brief_id']);
- }
- if(isset($tmp[$key]['brief_en'])){
- unset($tmp[$key]['brief_en']);
- }
- unset($tmp[$key]['affiliate_url']);
- unset($tmp[$key]['promo_url']);
- unset($tmp[$key]['promo']);
- unset($tmp[$key]['partner']);
- unset($tmp[$key]['features']);
- unset($tmp[$key]['is_active']);
- unset($tmp[$key]['last_login']);
- }
- $request->data = $tmp;
- $dumped = $request->data;
- // }
- // $product = json_encode($dumped);
- $search_record = new Search_Record;
- if(isset($search_record->datas)){
- unset($search_record->datas);
- }
- $search_record->id = $id;
- $search_record->user_id = $user;
- $search_record->datas = $dumped;
- $search_record->type = $request->data[0]['type'];
- $search_record->bypass = $request->data[0]['bypass'];
- $search_record->questionnaire = $request->data[0]['questionnaire'];
- // remove quesetionnaire array in every request->data
- foreach($request->data as $index => $none){
- unset($request->data[$index]['questionnaire']);
- }
- if ($search_record->save()) {
- return 'oke';
- } else {
- return 'oce';
- }
- }
- }
- /**
- * @param \Illuminate\Http\Request $request
- * @param null $exclude
- * @param \Thanatos\Modules\Insurance\Http\Controllers\FeatureController $featureController
- * @param \Thanatos\Modules\Insurance\Http\Controllers\AdditionalController $additionalController
- * @param \Thanatos\Modules\Insurance\Http\Controllers\RateController $rateController
- *
- * @return array
- */
- public function getSearchQuery(Request $request, $exclude = null, FeatureController $featureController, AdditionalController $additionalController, RateController $rateController)
- {
- if ($request->has('type') and $request->has('query')) {
- App::setLocale($request->language);
- $request = $request->all();
- $absolute = ' ';
- $counter = 0;
- $type = $request['type'];
- $factor = $request['factor'];
- $query = json_decode($request['query'], true);
- $setcook = $query;
- $requests_additional = [];
- $amount_features = '';
- $percentages_features = '';
- // START BEGIN THE MOST COMPLICATED QUERY EVER !!!
- $sql = 'SELECT product.*,
- product_detail.*,
- product_meta.*,
- features.*,
- features.count as count,
- coverages.*,
- questionnaires.*,
- additional_coverages.*,
- zone_territory.*, ';
- /**
- * ini buat nge-get features berdasarkan keys tertentu, dan juga tipe tertentu yang nantinya akan masuk ke perhitungan data.
- * kaya perhitungan value dan juga perhitungan premi, yang nantinya diakumulasi kembali.
- * PLEASE BE CAREFUL !!
- */
- if (isset($request['feature'])) {
- $request_features = json_decode($request['feature'], true);
- $count_request_features = count($request_features);
- if (!empty($request_features) and $count_request_features > 0) {
- $setcook['feature'] = $request_features;
- $setcook['additionals'] = $requests_additional;
- $absolute = 'AND ';
- $in = '';
- foreach ($request_features as $keys => $vals) {
- // special condition for some
- switch($vals){
- case 'pre_existing_condition':
- $vals = 'pre-existing_condition';
- break;
- case 'post_travel_medical_expenses':
- $vals = 'post-travel_medical_expenses';
- break;
- }
- $in .= ' \'' . $vals . '\' ';
- $absolute .= 'features.' . '`'.$vals.'`' . ' IS NOT NULL ';
- if ($keys < $count_request_features - 1) {
- $in .= ',';
- $absolute .= 'AND ';
- }
- }
- // get amount price yang diakumulasi untuk perhitungan premi langsung.
- // -------------------------------------------------------------------
- $amount_features .= '(CASE WHEN EXISTS(SELECT NULL FROM insurance_features_additional_prices WHERE type=\'amount\' AND insurance_features_additional_prices.key IN (' . $in . ') AND product_id=product.id) ';
- $amount_features .= 'THEN @amount_price:=(SELECT SUM(`value`) FROM insurance_features_additional_prices WHERE type=\'amount\' AND (insurance_features_additional_prices.key IN (' . $in . ') AND product_id=product.id)) ';
- $amount_features .= 'ELSE @amount_price:=NULL ';
- $amount_features .= 'END), ';
- // get percentages prices yang diakumulasi untuk perhitungan value assets dari barang yang diasuransikan.
- // -------------------------------------------------------------------
- $percentages_features .= '(CASE WHEN EXISTS(SELECT NULL FROM insurance_features_additional_prices WHERE type=\'percentages\' AND insurance_features_additional_prices.key IN (' . $in . ') AND product_id=product.id) ';
- $percentages_features .= 'THEN @percentages_price:=(SELECT SUM(`value`) FROM insurance_features_additional_prices WHERE type=\'percentages\' AND (insurance_features_additional_prices.key IN (' . $in . ') AND product_id=product.id)) ';
- $percentages_features .= 'ELSE @percentages_price:=NULL ';
- $percentages_features .= 'END), ';
- }
- }
- // buat biaya tambahan
- $additional_fee_admin = '@admin_fee:=(CASE WHEN questionnaires.admin_fee < 100
- THEN (@premi/100)*questionnaires.admin_fee
- ELSE questionnaires.admin_fee END), ';
- $additional_fee_admin.= '@admin_fee:=IF(@admin_fee=NULL, 0, @admin_fee), ';
- $additional_fee_materai = '@materai_fee:=(CASE WHEN questionnaires.materai_fee < 100
- THEN (@premi/100)*questionnaires.materai_fee
- ELSE questionnaires.materai_fee END), ';
- $additional_fee_materai.= '@materai_fee:=IF(@materai_fee=NULL, 0, @materai_fee), ';
- switch ($type) {
- case 'car':
- /**
- * bikin variable khususnya terlebih dahulu,
- */
- $gap_age = date('Y') - (integer)$query['car_year'];
- $flood = '';
- $earthquake = '';
- $liability = '';
- $sort = [1 => 'one', 2 => 'two', 3 => 'three'];
- if ($query['level_of_insurance'] == 'comprehensive') {
- $additioning = $additionalController->comprehensive;
- $rationing = $rateController->comprehensive['car'];
- } else {
- $additioning = $additionalController->total_loss_only;
- $rationing = $rateController->total_loss_only['car'];
- }
- // SHOW ALL
- $variable = [1 => 125000000, 2 => 200000000, 3 => 400000000, 4 => 800000000, 5 => 800000000];
- $classer = [1 => 'zone_one', 2 => 'zone_two', 3 => 'zone_three'];
- $case_off = '';
- foreach ($classer as $k => $v) {
- $case_off .= '@' . $v . '_top:=(CASE ';
- foreach ($variable as $key => $value) {
- if ($key == 5) {
- $case_off .= 'WHEN questionnaires.car_premi_calc>' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['top'] . ' ';
- } elseif ($key == 1) {
- $case_off .= 'WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['top'] . ' ';
- } else {
- $case_off .= 'WHEN questionnaires.car_premi_calc>=' . $variable[$key - 1] . ' AND questionnaires.car_premi_calc<' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['top'] . ' ';
- }
- }
- $case_off .= 'END), ';
- }
- foreach ($classer as $k => $v) {
- $case_off .= '@' . $v . '_bottom:=(CASE ';
- foreach ($variable as $key => $value) {
- if ($key == 5) {
- $case_off .= 'WHEN questionnaires.car_premi_calc>' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['bottom'] . ' ';
- } elseif ($key == 1) {
- $case_off .= 'WHEN questionnaires.car_premi_calc>=0 AND questionnaires.car_premi_calc<' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['bottom'] . ' ';
- } else {
- $case_off .= 'WHEN questionnaires.car_premi_calc>=' . $variable[$key - 1] . ' AND questionnaires.car_premi_calc<' . $variable[$key] . '
- THEN ' . $rationing[$key][$k]['bottom'] . ' ';
- }
- }
- $case_off .= 'END), ';
- }
- $sql .= $case_off;
- $sql .= '@accident:=5000000, ';
- /**
- * NOW THE QUERY CHANGED AFTER LATENESS DATA. the changed also effecting into degradation of data and
- * also changed effect into everywhere ...
- */
- $sql .= '@value_car:=(CASE WHEN questionnaires.degradation_year_condition<' . $gap_age . '
- THEN ' . $query['car_value'] . '-(' . $query['car_value'] . '/100)*questionnaires.degradation_rate
- ELSE ' . $query['car_value'] . ' END), ';
- if (isset($request['addition'])) {
- $requests_additional = json_decode($request['addition'], true);
- $count_request_additional = count($requests_additional);
- if (!empty($requests_additional) and $count_request_additional > 0) {
- $setcook['addition'] = $requests_additional;
- $interval_requests_additional = 0;
- $iteration = $requests_additional;
- unset($iteration['flood_with_tornado']);
- unset($iteration['earthquake_with_tsunami']);
- unset($iteration['passenger_seat']);
- if (count($iteration) - 1 > 0) {
- $absolute .= 'AND ';
- foreach ($iteration as $k => $v) {
- $absolute .= 'additional_coverages.' . $k . ' ';
- if ($interval_requests_additional < count($iteration) - 1) {
- $absolute .= 'AND ';
- }
- $interval_requests_additional++;
- }
- }
- if (isset($requests_additional['accident_passenger'])) {
- $sql .= '@passenger:=' . $requests_additional['passenger'] . ',';
- }
- $sql .= '@passenger:=3,';
- /**
- * in this part, you'll face the first layer of calculation also called the additional calculation. why
- * additional calculation is doing in the first time ? the answer is ... ASK YOUR BOSS !!! Anirud
- * so in this part you will do some of calculation of additional car such as flood, earthquake,etc whatever
- * also with territorial. so enjoy !
- * ---------------------------------
- */
- // flood first ----------------
- if (isset($requests_additional['flood_with_tornado'])) {
- $flood .= '@flood:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE ';
- foreach ($sort as $key => $value) {
- $flood .= 'WHEN zone_territory.zone_id=' . $key . ' AND additional_coverages.flood_with_tornado_zone_' . $value . ' IS NOT NULL
- THEN (CASE WHEN additional_coverages.flood_with_tornado_zone_' . $value . ' = "top" THEN ' . $additioning['car']['flood_with_tornado'][$key]['top'] . '
- WHEN additional_coverages.flood_with_tornado_zone_' . $value . ' = "bottom" THEN ' . $additioning['car']['flood_with_tornado'][$key]['bottom'] . '
- ELSE CAST(additional_coverages.flood_with_tornado_zone_' . $value . ' AS DECIMAL(20,2))
- END) ';
- }
- $flood .= 'ELSE CAST(additional_coverages.flood_with_tornado_zone_custom AS DECIMAL(20,2)) END) ';
- $flood .= 'ELSE 0 END), ';
- $flood .= '@flood:=(@value_car/100)*@flood, ';
- if (!isset($requests_additional['earthquake_with_tsunami'])) {
- $flood .= '@disaster:=@flood, ';
- }
- }
- if (isset($requests_additional['earthquake_with_tsunami'])) {
- $earthquake .= '@earthquake:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE ';
- foreach ($sort as $key => $value) {
- $earthquake .= 'WHEN zone_territory.zone_id=' . $key . ' AND additional_coverages.earthquake_with_tsunami_zone_' . $value . ' IS NOT NULL
- THEN (CASE WHEN additional_coverages.earthquake_with_tsunami_zone_' . $value . ' = "top" THEN ' . $additioning['car']['earthquake_with_tsunami'][$key]['top'] . '
- WHEN additional_coverages.earthquake_with_tsunami_zone_' . $value . ' = "bottom" THEN ' . $additioning['car']['earthquake_with_tsunami'][$key]['bottom'] . '
- ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_' . $value . ' AS DECIMAL(20,2))
- END) ';
- }
- $earthquake .= 'ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_custom AS DECIMAL(20,2)) END) ';
- $earthquake .= 'ELSE 0 END), ';
- $earthquake .= '@earthquake:=(@value_car/100)*@earthquake, ';
- $earthquake .= '@disaster:=IF(@flood IS NOT NULL, @flood+@earthquake, @earthquake), ';
- }
- $sql .= $flood . $earthquake;
- /*
- // then earthquake ------------
- foreach($sort as $key=>$value){
- $earthquake.= '(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN zone_territory.zone_id=1 AND additional_coverages.earthquake_with_tsunami_zone_'.$value.' IS NOT NULL
- THEN @flood:=(CASE WHEN additional_coverages.earthquake_with_tsunami_zone_'.$value.' = "top" THEN '.$additioning['car']['earthquake_with_tsunami'][1]['top'].'
- WHEN additional_coverages.earthquake_with_tsunami_zone_'.$value.' = "bottom" THEN '.$additioning['car']['earthquake_with_tsunami'][1]['bottom'].'
- ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_'.$value.' AS UNSIGNED)
- END), ';
- }
- /**
- * okay, that's territorial zone done, now you'll facing the single zone ...
- * in this part there's two case condition such as terrorism and chaos part literaly
- * ----------------------------
- */
- // chaos and riot ----------------
- if (isset($requests_additional['chaos_and_riot'])) {
- $srcc = '@chaos_and_riot:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.chaos_and_riot IS NOT NULL THEN CAST(' . $additioning['car']['riot_and_chaos'] . ' AS DECIMAL(3,3)) END)
- ELSE 0 END), ';
- $srcc .= '@chaos_and_riot:=(@value_car/100)*@chaos_and_riot, ';
- if (!isset($requests_additional['terrorism_and_sabotage'])) {
- $srcc .= '@commotion:=@chaos_and_riot, ';
- }
- $sql .= $srcc;
- }
- if (isset($requests_additional['terrorism_and_sabotage'])) {
- // terrorism and sabotage --------
- $terrorism = '@terrorism_and_sabotage:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.terrorism_and_sabotage IS NOT NULL THEN CAST(' . $additioning['car']['terrorism_and_sabotage'] . ' AS DECIMAL(3,3)) END)
- ELSE 0 END), ';
- $terrorism .= '@terrorism_and_sabotage:=(@value_car/100)*@terrorism_and_sabotage, ';
- $terrorism .= '@commotion:=IF(@chaos_and_riot IS NOT NULL, @chaos_and_riot+@terrorism_and_sabotage, @terrorism_and_sabotage), ';
- $sql .= $terrorism;
- }
- if (isset($requests_additional['responsibility_third_party_by_law'])) {
- $rtpl = (integer)$requests_additional['responsibility_third_party_by_law'];
- $pieces_of_prices_law = [];
- // sequence 1
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[0] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[0] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 2
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[1] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[1] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 3
- if ($rtpl > 0) {
- if ($rtpl <= 50000000) {
- $pieces_of_prices_law[2] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[2] = 50000000;
- $rtpl = $rtpl - 50000000;
- }
- }
- // sequence 4 sisanya
- if ($rtpl > 0) {
- $pieces_of_prices_law[3] = $rtpl;
- $rtpl = 0;
- }
- if (isset($pieces_of_prices_law[0])) {
- DB::statement('SET @third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED)');
- $tpl = '@third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- } else {
- DB::statement('SET @third_party_liability_one:=0 ');
- $tpl = '@third_party_liability_one:=0, ';
- }
- if (isset($pieces_of_prices_law[1])) {
- DB::statement('SET @third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED)');
- $tpl .= '@third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_two:=0 ');
- $tpl .= '@third_party_liability_two:=0, ';
- }
- if (isset($pieces_of_prices_law[2])) {
- DB::statement('SET @third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED)');
- $tpl .= '@third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['car']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_three:=0 ');
- $tpl .= '@third_party_liability_three:=0, ';
- }
- if (isset($pieces_of_prices_law[3])) {
- DB::statement('SET @third_party_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_third_party_by_law AS UNSIGNED) ');
- $tpl .= '@third_party_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_third_party_by_law AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_four:=0 ');
- $tpl .= '@third_party_liability_four:=0, ';
- }
- $tpl .= '@third_party_liability:=@third_party_liability_one+@third_party_liability_two+@third_party_liability_three+@third_party_liability_four, ';
- $sql .= $tpl;
- }
- if (isset($requests_additional['responsibility_law_for_passenger'])) {
- /*
- $rtpl = (integer)$requests_additional['responsibility_law_for_passenger'];
- DB::statement('SET @passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED)');
- //$tpl = '@tpl_one=@tpl_one+1, ';
- $tpl = '@passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- DB::statement('SET @passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED), ';
- DB::statement('SET @passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability_four:=CAST(IF(@responsibility>100000000,(@responsibility/100)*additional_coverages.responsibility_law_for_passenger,0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability:=@passenger_liability_one+@passenger_liability_two+@passenger_liability_three+@passenger_liability_four, ';
- $sql .= $tpl;
- */
- $rtpl = (integer)$requests_additional['responsibility_law_for_passenger'];
- $pieces_of_prices_law = [];
- // sequence 1
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[0] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[0] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 2
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[1] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[1] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 3
- if ($rtpl > 0) {
- if ($rtpl <= 50000000) {
- $pieces_of_prices_law[2] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[2] = 50000000;
- $rtpl = $rtpl - 50000000;
- }
- }
- // sequence 4 sisanya
- if ($rtpl > 0) {
- $pieces_of_prices_law[3] = $rtpl;
- $rtpl = 0;
- }
- if (isset($pieces_of_prices_law[0])) {
- DB::statement('SET @passenger_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED)');
- $tpl = '@passenger_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- } else {
- DB::statement('SET @passenger_liability_one:=0 ');
- $tpl .= '@passenger_liability_one:=0, ';
- }
- if (isset($pieces_of_prices_law[1])) {
- DB::statement('SET @passenger_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED)');
- $tpl .= '@passenger_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_two:=0 ');
- $tpl .= '@passenger_liability_two:=0, ';
- }
- if (isset($pieces_of_prices_law[2])) {
- DB::statement('SET @passenger_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED)');
- $tpl .= '@passenger_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_three:=0 ');
- $tpl .= '@passenger_liability_three:=0, ';
- }
- if (isset($pieces_of_prices_law[3])) {
- DB::statement('SET @passenger_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_law_passenger AS UNSIGNED) ');
- $tpl .= '@passenger_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_law_passenger AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_four:=0 ');
- $tpl .= '@third_party_liability_four:=0, ';
- }
- $tpl .= '@passenger_liability:=@passenger_liability_one+@passenger_liability_two+@passenger_liability_three+@passenger_liability_four, ';
- $sql .= $tpl;
- }
- if (isset($requests_additional['self_accident_for_driver'])) {
- $driver = '@driver:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.self_accident_for_driver IS NOT NULL THEN CAST(' . $additioning['car']['accident_driver'] . ' AS DECIMAL(3,2)) END)
- ELSE 0 END), ';
- $driver .= '@driver:=(' . $requests_additional['self_accident_for_driver'] . '/100)*@driver, ';
- if (!isset($requests_additional['self_accident_for_passenger'])) {
- $driver .= '@car_in:=@driver, ';
- }
- $sql .= $driver;
- }
- if (isset($requests_additional['self_accident_for_passenger'])) {
- $passenger = '@passengers:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.self_accident_for_passenger IS NOT NULL THEN CAST(' . $additioning['car']['accident_passenger'] . ' AS DECIMAL(3,2)) END)
- ELSE 0 END), ';
- $passenger .= '@passengers:=((' . $requests_additional['self_accident_for_passenger'] . '/100)*@passengers)*' . $requests_additional['passenger_seat'] . ', ';
- $passenger .= '@car_in:=IF(@driver IS NOT NULL, @driver+@passengers, @passengers), ';
- $sql .= $passenger;
- }
- $sql .= '@calculate:=(CASE WHEN @disaster IS NOT NULL AND @commotion IS NULL
- THEN @disaster
- WHEN @disaster IS NULL AND @commotion IS NOT NULL
- THEN @commotion
- WHEN @disaster IS NOT NULL AND @commotion IS NOT NULL
- THEN @disaster+@commotion
- ELSE 0 END), ';
- $sql .= '@lawsuit:=(CASE WHEN @third_party_liability IS NOT NULL AND @passenger_liability IS NOT NULL
- THEN @third_party_liability+@passenger_liability
- WHEN @liability IS NULL AND @passenger_liability IS NOT NULL
- THEN @passenger_liability
- WHEN @third_party_liability IS NOT NULL AND @passenger_liability IS NULL
- THEN @third_party_liability
- ELSE 0 END), ';
- // END OF ACCUMULATION ADDITIONAL !!!!!!!! ----------------------------------------------------------------------
- }
- } else {
- $sql .= '@accumulation:=0, ';
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= '@percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@value_car/100)*@percentages_price
- ELSE 0 END), ';
- // disini query harus ngeliat apa dia punya override atau gak, kalo emang gak, ya berarti di
- // pake dong territorial ratenya, dan juga territory diliat lagi tipe zonenya
- $sql .= '(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), ';
- $sql .= '(CASE
- WHEN questionnaires.additional_year_condition<' . $gap_age . ' THEN @addons_percentages:=(questionnaires.additional_rate/100)
- ELSE @addons_percentages:=0
- END), ';
- /**
- * calculate everything into one pieces, but first you need an raw prices and then
- * you can go into accumulation prices based on.
- */
- $sql .= '@premx:=(@value_car/100)*(@percentages + (@percentages * @addons_percentages)), ';
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premx+@amount_price, @premx), ';
- $sql .= '@premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), ';
- $sql .= '@premi:=IF(@calculate IS NOT NULL AND @calculate > 0, @premi+@calculate, @premi), ';
- $sql .= '@premi:=IF(@lawsuit IS NOT NULL AND @lawsuit > 0, @premi+@lawsuit, @premi), ';
- $sql .= '@premi:=IF(@car_in IS NOT NULL AND @car_in > 0, @premi+@car_in, @premi), ';
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=FLOOR(@premi+@admin_fee+@materai_fee), ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT *,';
- // ini gua ngambil harga yang di degradasi, belum di round ya
- $sql .= '(CASE
- WHEN degradation_year_condition<' . $gap_age . ' THEN @value_car:=' . $query['car_value'] . '-(((' . $query['car_value'] . '/100)*degradation_rate))
- ELSE @value_car:=' . $query['car_value'] . '
- END) as car_premi_calc ';
- $sql .= 'FROM insurance_questionnaire_car) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_car) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_car_' . $query['level_of_insurance'] . ') coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_additional_car) additional_coverages ON additional_coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM zone_territory_car) zone_territory ON TRUE ';
- $sql .= 'WHERE ( ';
- unset($query['car_year']);
- unset($query['car_year_brand']);
- unset($query['car_year_brand_model']);
- unset($query['car_year_brand_model_type']);
- // PAKE LAGI KALO MAU GUNAKAN FLEXIBLE QUESTIONNAIRE
- /*
- foreach ($query as $key => $value) {
- switch ($key) {
- case 'car_value':
- $sql .= 'questionnaires.car_value_min IS NULL OR questionnaires.car_value_min < ' . $value . ' AND questionnaires.car_value_max IS NULL OR questionnaires.car_value_max > ' . $value . ' ';
- $sql .= '';
- break;
- case 'car_set':
- $sql .= 'cav.car_additional_id = ' . $value . ' OR cav.car_additional_id IS NULL ';
- break;
- case 'date_of_birth':
- $age = calculateAge($value);
- $setcook['age'] = $age;
- $sql .= 'questionnaires.age_min IS NULL OR questionnaires.age_min < ' . $age . ' AND questionnaires.age_max IS NULL OR questionnaires.age_max > ' . $age . ' ';
- break;
- default:
- $sql .= 'questionnaires.' . $key . ' IS NULL OR questionnaires.' . $key . ' LIKE "%' . $value . '%" ';
- break;
- }
- if ($counter < count($query) - 1) {
- $sql .= 'AND ';
- }
- $counter++;
- IF(questionnaires.car_value_min IS NOT NULL, questionnaires.car_value_min < 900000000, questionnaires.car_value_min IS NULL) AND
- IF(questionnaires.car_value_max IS NOT NULL, questionnaires.car_value_max > 900000000, questionnaires.car_value_max IS NULL)
- }*/
- $sql .= 'IF(questionnaires.car_value_min IS NOT NULL, questionnaires.car_value_min < ' . $query['car_value'] . ', questionnaires.car_value_min IS NULL) AND ';
- $sql .= 'IF(questionnaires.car_value_max IS NOT NULL, questionnaires.car_value_max > ' . $query['car_value'] . ', questionnaires.car_value_max IS NULL) AND ';
- $sql .= 'IF(questionnaires.age_vehicle_max IS NOT NULL, questionnaires.age_vehicle_max > ' . $gap_age . ', questionnaires.age_vehicle_max IS NULL) ';
- break;
- case 'motorcycle':
- $gap_age = date('Y') - (integer)$query['motorcycle_year'];
- $flood = '';
- $earthquake = '';
- $liability = '';
- $sort = [1 => 'one', 2 => 'two', 3 => 'three'];
- if ($query['level_of_insurance'] == 'comprehensive') {
- $additioning = $additionalController->comprehensive;
- $rationing = $rateController->comprehensive['motorcycle'];
- } else {
- $additioning = $additionalController->total_loss_only;
- $rationing = $rateController->total_loss_only['motorcycle'];
- }
- // SHOW ALL
- // $variable = [ 1 => 125000000, 2 => 200000000, 3 => 400000000, 4 => 800000000, 5 => 800000000 ];
- $classer = [1 => 'zone_one', 2 => 'zone_two', 3 => 'zone_three'];
- $case_off = '';
- foreach ($classer as $k => $v) {
- DB::statement('SET @' . $v . '_top:=CAST(' . $rationing[1][$k]['top'] . ' AS DECIMAL(15,2)) ');
- DB::statement('SET @' . $v . '_bottom:=CAST(' . $rationing[1][$k]['bottom'] . ' AS DECIMAL(15,2)) ');
- $case_off .= '@' . $v . '_top:=CAST(' . $rationing[1][$k]['top'] . ' AS DECIMAL(15,2)) , ';
- $case_off .= '@' . $v . '_bottom:=CAST(' . $rationing[1][$k]['bottom'] . ' AS DECIMAL(15,2)) , ';
- }
- $sql .= $case_off;
- // ini gua ngambil harga yang di degradasi, belum di round ya
- $sql .= '(CASE
- WHEN questionnaires.degradation_year_condition<' . $gap_age . ' THEN @value_motorcycle:=' . $query['motorcycle_value'] . '-(' . $query['motorcycle_value'] . '/100)*questionnaires.degradation_rate
- ELSE @value_motorcycle:=' . $query['motorcycle_value'] . '
- END), ';
- if (isset($request['addition'])) {
- $requests_additional = json_decode($request['addition'], true);
- $count_request_additional = count($requests_additional);
- if (!empty($requests_additional) and $count_request_additional > 0) {
- $setcook['addition'] = $requests_additional;
- $interval_requests_additional = 0;
- $iteration = $requests_additional;
- unset($iteration['flood_with_tornado']);
- unset($iteration['earthquake_with_tsunami']);
- unset($iteration['passenger_seat']);
- if (count($iteration) - 1 > 0) {
- $absolute .= 'AND ';
- foreach ($iteration as $k => $v) {
- $absolute .= 'additional_coverages.' . $k . ' ';
- if ($interval_requests_additional < count($iteration) - 1) {
- $absolute .= 'AND ';
- }
- $interval_requests_additional++;
- }
- }
- if (isset($requests_additional['accident_passenger'])) {
- $sql .= '@passenger:=' . $requests_additional['passenger'] . ',';
- }
- $sql .= '@passenger:=3,';
- /**
- * in this part, you'll face the first layer of calculation also called the additional calculation. why
- * additional calculation is doing in the first time ? the answer is ... ASK YOUR BOSS !!! Anirud
- * so in this part you will do some of calculation of additional motorcycle such as flood, earthquake,etc whatever
- * also with territorial. so enjoy !
- * ---------------------------------
- */
- // flood first ----------------
- if (isset($requests_additional['flood_with_tornado'])) {
- $flood .= '@flood:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE ';
- foreach ($sort as $key => $value) {
- $flood .= 'WHEN zone_territory.zone_id=' . $key . ' AND additional_coverages.flood_with_tornado_zone_' . $value . ' IS NOT NULL
- THEN (CASE WHEN additional_coverages.flood_with_tornado_zone_' . $value . ' = "top" THEN ' . $additioning['motorcycle']['flood_with_tornado'][$key]['top'] . '
- WHEN additional_coverages.flood_with_tornado_zone_' . $value . ' = "bottom" THEN ' . $additioning['motorcycle']['flood_with_tornado'][$key]['bottom'] . '
- ELSE CAST(additional_coverages.flood_with_tornado_zone_' . $value . ' AS DECIMAL(20,2))
- END) ';
- }
- $flood .= 'ELSE CAST(additional_coverages.flood_with_tornado_zone_custom AS DECIMAL(20,2)) END) ';
- $flood .= 'ELSE 0 END), ';
- $flood .= '@flood:=(@value_motorcycle/100)*@flood, ';
- if (!isset($requests_additional['earthquake_with_tsunami'])) {
- $flood .= '@disaster:=@flood, ';
- }
- }
- if (isset($requests_additional['earthquake_with_tsunami'])) {
- $earthquake .= '@earthquake:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE ';
- foreach ($sort as $key => $value) {
- $earthquake .= 'WHEN zone_territory.zone_id=' . $key . ' AND additional_coverages.earthquake_with_tsunami_zone_' . $value . ' IS NOT NULL
- THEN (CASE WHEN additional_coverages.earthquake_with_tsunami_zone_' . $value . ' = "top" THEN ' . $additioning['motorcycle']['earthquake_with_tsunami'][$key]['top'] . '
- WHEN additional_coverages.earthquake_with_tsunami_zone_' . $value . ' = "bottom" THEN ' . $additioning['motorcycle']['earthquake_with_tsunami'][$key]['bottom'] . '
- ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_' . $value . ' AS DECIMAL(20,2))
- END) ';
- }
- $earthquake .= 'ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_custom AS DECIMAL(20,2)) END) ';
- $earthquake .= 'ELSE 0 END), ';
- $earthquake .= '@earthquake:=(@value_motorcycle/100)*@earthquake, ';
- $earthquake .= '@disaster:=IF(@flood IS NOT NULL, @flood+@earthquake, @earthquake), ';
- }
- $sql .= $flood . $earthquake;
- /*
- // then earthquake ------------
- foreach($sort as $key=>$value){
- $earthquake.= '(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN zone_territory.zone_id=1 AND additional_coverages.earthquake_with_tsunami_zone_'.$value.' IS NOT NULL
- THEN @flood:=(CASE WHEN additional_coverages.earthquake_with_tsunami_zone_'.$value.' = "top" THEN '.$additioning['motorcycle']['earthquake_with_tsunami'][1]['top'].'
- WHEN additional_coverages.earthquake_with_tsunami_zone_'.$value.' = "bottom" THEN '.$additioning['motorcycle']['earthquake_with_tsunami'][1]['bottom'].'
- ELSE CAST(additional_coverages.earthquake_with_tsunami_zone_'.$value.' AS UNSIGNED)
- END), ';
- }
- /**
- * okay, that's territorial zone done, now you'll facing the single zone ...
- * in this part there's two case condition such as terrorism and chaos part literaly
- * ----------------------------
- */
- // chaos and riot ----------------
- if (isset($requests_additional['chaos_and_riot'])) {
- $srcc = '@chaos_and_riot:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.chaos_and_riot IS NOT NULL THEN CAST(' . $additioning['motorcycle']['riot_and_chaos'] . ' AS DECIMAL(3,3)) END)
- ELSE 0 END), ';
- $srcc .= '@chaos_and_riot:=(@value_motorcycle/100)*@chaos_and_riot, ';
- if (!isset($requests_additional['terrorism_and_sabotage'])) {
- $srcc .= '@commotion:=@chaos_and_riot, ';
- }
- $sql .= $srcc;
- }
- if (isset($requests_additional['terrorism_and_sabotage'])) {
- // terrorism and sabotage --------
- $terrorism = '@terrorism_and_sabotage:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.terrorism_and_sabotage IS NOT NULL THEN CAST(' . $additioning['motorcycle']['terrorism_and_sabotage'] . ' AS DECIMAL(3,3)) END)
- ELSE 0 END), ';
- $terrorism .= '@terrorism_and_sabotage:=(@value_motorcycle/100)*@terrorism_and_sabotage, ';
- $terrorism .= '@commotion:=IF(@chaos_and_riot IS NOT NULL, @chaos_and_riot+@terrorism_and_sabotage, @terrorism_and_sabotage), ';
- $sql .= $terrorism;
- }
- /*if (isset($requests_additional['responsibility_third_party_by_law'])) {
- $rtpl = (integer)$requests_additional['responsibility_third_party_by_law'];
- $pieces_of_prices_law = [];
- // sequence 1
- if($rtpl >= 25000000){
- $pieces_of_prices_law[0] = $rtpl-25000000;
- $rtpl = $rtpl-25000000;
- } else {
- $pieces_of_prices_law[0] = $rtpl;
- $rtpl = 0;
- }
- // sequence 2
- if($rtpl >= 50000000){
- $pieces_of_prices_law[1] = $rtpl-50000000;
- $rtpl = $rtpl-50000000;
- } elseif($rtpl >= 25000000){
- $pieces_of_prices_law[1] = $rtpl-25000000;
- $rtpl = $rtpl-25000000;
- } else {
- $pieces_of_prices_law[1] = $rtpl;
- $rtpl = 0;
- }
- // sequence 3
- if($rtpl >= 100000000){
- $pieces_of_prices_law[2] = $rtpl-100000000;
- $rtpl = $rtpl-100000000;
- } elseif($rtpl >= 50000000){
- $pieces_of_prices_law[2] = $rtpl-50000000;
- $rtpl = $rtpl-50000000;
- } elseif($rtpl >= 25000000){
- $pieces_of_prices_law[2] = $rtpl-25000000;
- $rtpl = $rtpl-25000000;
- } else {
- $pieces_of_prices_law[2] = $rtpl;
- $rtpl = 0;
- }
- // sequence 4 sisanya
- if($rtpl > 0){
- $pieces_of_prices_law[3] = $rtpl;
- $rtpl = 0;
- }
- if(isset($pieces_of_prices_law[0])){
- DB::statement('SET @third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED)');
- $tpl = '@third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- }
- if(isset($pieces_of_prices_law[1])){
- DB::statement('SET @third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED)');
- $tpl .= '@third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED), ';
- }
- if(isset($pieces_of_prices_law[2])){
- DB::statement('SET @third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED)');
- $tpl .= '@third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED), ';
- }
- if(isset($pieces_of_prices_law[3])){
- $tpl .= '@third_party_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_third_party_by_law AS UNSIGNED), ';
- }
- $tpl .= '@third_party_liability:=@third_party_liability_one+@third_party_liability_two+@third_party_liability_three+@third_party_liability_four, ';
- $sql .= $tpl;
- }
- if (isset($requests_additional['responsibility_law_for_passenger'])) {
- $rtpl = (integer)$requests_additional['responsibility_law_for_passenger'];
- DB::statement('SET @passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED)');
- //$tpl = '@tpl_one=@tpl_one+1, ';
- $tpl = '@passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- DB::statement('SET @passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED), ';
- DB::statement('SET @passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability_four:=CAST(IF(@responsibility>100000000,(@responsibility/100)*additional_coverages.responsibility_law_for_passenger,0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability:=@passenger_liability_one+@passenger_liability_two+@passenger_liability_three+@passenger_liability_four, ';
- $sql .= $tpl;
- }*/
- if (isset($requests_additional['responsibility_third_party_by_law'])) {
- $rtpl = (integer)$requests_additional['responsibility_third_party_by_law'];
- $pieces_of_prices_law = [];
- // sequence 1
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[0] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[0] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 2
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[1] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[1] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 3
- if ($rtpl > 0) {
- if ($rtpl <= 50000000) {
- $pieces_of_prices_law[2] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[2] = 50000000;
- $rtpl = $rtpl - 50000000;
- }
- }
- // sequence 4 sisanya
- if ($rtpl > 0) {
- $pieces_of_prices_law[3] = $rtpl;
- $rtpl = 0;
- }
- if (isset($pieces_of_prices_law[0])) {
- DB::statement('SET @third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED)');
- $tpl = '@third_party_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- } else {
- DB::statement('SET @third_party_liability_one:=0 ');
- $tpl = '@third_party_liability_one:=0, ';
- }
- if (isset($pieces_of_prices_law[1])) {
- DB::statement('SET @third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED)');
- $tpl .= '@third_party_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_two:=0 ');
- $tpl .= '@third_party_liability_two:=0, ';
- }
- if (isset($pieces_of_prices_law[2])) {
- DB::statement('SET @third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED)');
- $tpl .= '@third_party_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_third_party_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_three:=0 ');
- $tpl .= '@third_party_liability_three:=0, ';
- }
- if (isset($pieces_of_prices_law[3])) {
- DB::statement('SET @third_party_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_third_party_by_law AS UNSIGNED) ');
- $tpl .= '@third_party_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_third_party_by_law AS UNSIGNED), ';
- } else {
- DB::statement('SET @third_party_liability_four:=0 ');
- $tpl .= '@third_party_liability_four:=0, ';
- }
- $tpl .= '@third_party_liability:=@third_party_liability_one+@third_party_liability_two+@third_party_liability_three+@third_party_liability_four, ';
- $sql .= $tpl;
- }
- if (isset($requests_additional['responsibility_law_for_passenger'])) {
- /*
- $rtpl = (integer)$requests_additional['responsibility_law_for_passenger'];
- DB::statement('SET @passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED)');
- //$tpl = '@tpl_one=@tpl_one+1, ';
- $tpl = '@passenger_liability_one:=CAST((' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- DB::statement('SET @passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_two:=CAST(IF(' . $rtpl . '>25000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ',0) AS UNSIGNED), ';
- DB::statement('SET @passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED)');
- $tpl .= '@passenger_liability_three:=CAST(IF(' . $rtpl . '>50000000,(' . $rtpl . '/100)*' . $additioning['car']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ',0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability_four:=CAST(IF(@responsibility>100000000,(@responsibility/100)*additional_coverages.responsibility_law_for_passenger,0) AS UNSIGNED), ';
- $tpl .= '@passenger_liability:=@passenger_liability_one+@passenger_liability_two+@passenger_liability_three+@passenger_liability_four, ';
- $sql .= $tpl;
- */
- $rtpl = (integer)$requests_additional['responsibility_law_for_passenger'];
- $pieces_of_prices_law = [];
- // sequence 1
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[0] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[0] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 2
- if ($rtpl > 0) {
- if ($rtpl <= 25000000) {
- $pieces_of_prices_law[1] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[1] = 25000000;
- $rtpl = $rtpl - 25000000;
- }
- }
- // sequence 3
- if ($rtpl > 0) {
- if ($rtpl <= 50000000) {
- $pieces_of_prices_law[2] = $rtpl;
- $rtpl = 0;
- } else {
- $pieces_of_prices_law[2] = 50000000;
- $rtpl = $rtpl - 50000000;
- }
- }
- // sequence 4 sisanya
- if ($rtpl > 0) {
- $pieces_of_prices_law[3] = $rtpl;
- $rtpl = 0;
- }
- if (isset($pieces_of_prices_law[0])) {
- DB::statement('SET @passenger_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED)');
- $tpl = '@passenger_liability_one:=CAST((' . $pieces_of_prices_law[0] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['less_than_25'] . ' AS UNSIGNED) , ';
- } else {
- DB::statement('SET @passenger_liability_one:=0 ');
- $tpl .= '@passenger_liability_one:=0, ';
- }
- if (isset($pieces_of_prices_law[1])) {
- DB::statement('SET @passenger_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED)');
- $tpl .= '@passenger_liability_two:=CAST((' . $pieces_of_prices_law[1] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_25_and_less_than_50'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_two:=0 ');
- $tpl .= '@passenger_liability_two:=0, ';
- }
- if (isset($pieces_of_prices_law[2])) {
- DB::statement('SET @passenger_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED)');
- $tpl .= '@passenger_liability_three:=CAST((' . $pieces_of_prices_law[2] . '/100)*' . $additioning['motorcycle']['responsibility_law_passenger']['more_than_50_and_less_than_100'] . ' AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_three:=0 ');
- $tpl .= '@passenger_liability_three:=0, ';
- }
- if (isset($pieces_of_prices_law[3])) {
- DB::statement('SET @passenger_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_law_passenger AS UNSIGNED) ');
- $tpl .= '@passenger_liability_four:=CAST((' . $pieces_of_prices_law[3] . '/100)*additional_coverages.responsibility_law_passenger AS UNSIGNED), ';
- } else {
- DB::statement('SET @passenger_liability_four:=0 ');
- $tpl .= '@third_party_liability_four:=0, ';
- }
- $tpl .= '@passenger_liability:=@passenger_liability_one+@passenger_liability_two+@passenger_liability_three+@passenger_liability_four, ';
- $sql .= $tpl;
- }
- if (isset($requests_additional['self_accident_for_driver'])) {
- $driver = '@driver:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.self_accident_for_driver IS NOT NULL THEN CAST(' . $additioning['motorcycle']['accident_driver'] . ' AS DECIMAL(3,2)) END)
- ELSE 0 END), ';
- $driver .= '@driver:=(' . $requests_additional['self_accident_for_driver'] . '/100)*@driver, ';
- if (!isset($requests_additional['self_accident_for_passenger'])) {
- $driver .= '@motorcycle_in:=@driver, ';
- }
- $sql .= $driver;
- }
- if (isset($requests_additional['self_accident_for_passenger'])) {
- $passenger = '@passengers:=(CASE WHEN additional_coverages.id IS NOT NULL AND additional_coverages.id > 0
- THEN (CASE WHEN additional_coverages.self_accident_for_passenger IS NOT NULL THEN CAST(' . $additioning['motorcycle']['accident_passenger'] . ' AS DECIMAL(3,2)) END)
- ELSE 0 END), ';
- $passenger .= '@passengers:=((' . $requests_additional['self_accident_for_passenger'] . '/100)*@passengers)*' . $requests_additional['passenger_seat'] . ', ';
- $passenger .= '@motorcycle_in:=IF(@driver IS NOT NULL, @driver+@passengers, @passengers), ';
- $sql .= $passenger;
- }
- $sql .= '@calculate:=(CASE WHEN @disaster IS NOT NULL AND @commotion IS NULL
- THEN @disaster
- WHEN @disaster IS NULL AND @commotion IS NOT NULL
- THEN @commotion
- WHEN @disaster IS NOT NULL AND @commotion IS NOT NULL
- THEN @disaster+@commotion
- ELSE 0 END), ';
- $sql .= '@lawsuit:=(CASE WHEN @third_party_liability IS NOT NULL AND @passenger_liability IS NOT NULL
- THEN @third_party_liability+@passenger_liability
- WHEN @liability IS NULL AND @passenger_liability IS NOT NULL
- THEN @passenger_liability
- WHEN @third_party_liability IS NOT NULL AND @passenger_liability IS NULL
- THEN @third_party_liability
- ELSE 0 END), ';
- // END OF ACCUMULATION ADDITIONAL !!!!!!!! ----------------------------------------------------------------------
- }
- } else {
- $sql .= '@accumulation:=0, ';
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= '@percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@value_motorcycle/100)*@percentages_price
- ELSE 0 END), ';
- // disini query harus ngeliat apa dia punya override atau gak, kalo emang gak, ya berarti di
- // pake dong territorial ratenya, dan juga territory diliat lagi tipe zonenya
- $sql .= '(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), ';
- $sql .= '(CASE
- WHEN questionnaires.additional_year_condition<' . $gap_age . ' THEN @addons_percentages:=(questionnaires.additional_rate/100)
- ELSE @addons_percentages:=0
- END), ';
- $sql .= '@premx:=(@value_motorcycle/100)*(@percentages + (@percentages * @addons_percentages)), ';
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premx+@amount_price, @premx), ';
- $sql .= '@premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), ';
- $sql .= '@premi:=IF(@calculate IS NOT NULL AND @calculate > 0, @premi+@calculate, @premi), ';
- $sql .= '@premi:=IF(@lawsuit IS NOT NULL AND @lawsuit > 0, @premi+@lawsuit, @premi), ';
- $sql .= '@premi:=IF(@motorcycle_in IS NOT NULL AND @motorcycle_in > 0, @premi+@motorcycle_in, @premi), ';
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=ROUND(@premi+@admin_fee+@materai_fee), ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_questionnaire_motorcycle) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_motorcycle) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_motorcycle_' . $query['level_of_insurance'] . ') coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_additional_motorcycle) additional_coverages ON additional_coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM zone_territory_motorcycle) zone_territory ON true ';
- $sql .= 'WHERE (';
- unset($query['motorcycle_year']);
- unset($query['motorcycle_year_brand']);
- unset($query['motorcycle_year_brand_model']);
- unset($query['motorcycle_year_brand_model_type']);
- /*
- foreach ($query as $key => $value) {
- switch ($key) {
- case 'motorcycle_value':
- $sql .= 'questionnaires.motorcycle_value_min IS NULL OR questionnaires.motorcycle_value_min < ' . $value . ' AND questionnaires.motorcycle_value_max IS NULL OR questionnaires.motorcycle_value_max > ' . $value . ' ';
- $sql .= '';
- break;
- case 'motorcycle_set':
- $sql .= 'cav.motorcycle_additional_id = ' . $value . ' OR cav.motorcycle_additional_id IS NULL ';
- break;
- case 'date_of_birth':
- $age = calculateAge($value);
- $setcook['age'] = $age;
- $sql .= 'questionnaires.age_min IS NULL OR questionnaires.age_min < ' . $age . ' AND questionnaires.age_max IS NULL OR questionnaires.age_max > ' . $age . ' ';
- break;
- default:
- $sql .= 'questionnaires.' . $key . ' IS NULL OR questionnaires.' . $key . ' LIKE "%' . $value . '%" ';
- break;
- }
- if ($counter < count($query) - 1) {
- $sql .= 'AND ';
- }
- $counter++;
- }
- */
- $sql .= 'IF(questionnaires.motorcycle_value_min IS NOT NULL, questionnaires.motorcycle_value_min < ' . $query['motorcycle_value'] . ', questionnaires.motorcycle_value_min IS NULL) AND ';
- $sql .= 'IF(questionnaires.motorcycle_value_max IS NOT NULL, questionnaires.motorcycle_value_max > ' . $query['motorcycle_value'] . ', questionnaires.motorcycle_value_max IS NULL) AND ';
- $sql .= 'IF(questionnaires.age_vehicle_max IS NOT NULL, questionnaires.age_vehicle_max > ' . $gap_age . ', questionnaires.age_vehicle_max IS NULL) ';
- break;
- case 'property':
- $sql .= '@zone:=0, ';
- $sql .= '@others:=0, ';
- DB::statement('SET @zone:=0 ');
- DB::statement('SET @others:=0 ');
- $yo_property = $query['level_of_insurance'];
- // jika dia perhitungannya cuma content
- if ($query['type_of_insurance'] == 'contents.only') {
- $counting = $query['estimated_value_of_the_contents_to_cover'];
- } else {
- $counting = $query['estimated_value_of_the_property_to_cover'];
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= '@percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (' . $counting . '/100)*@percentages_price
- ELSE 0 END), ';
- if ($query['level_of_insurance'] == 'all_risk' or $query['level_of_insurance'] == 'standard') {
- if ($query['level_of_insurance'] == 'all_risk') {
- $additioning = $additionalController->all_risk['property'];
- $rationing = $rateController->all['property'];
- $yo_property = 'all';
- //cuman buat all risk doang dia ada additional
- if (isset($request['addition'])) {
- $requests_additional = json_decode($request['addition'], true);
- $count_request_additional = count($requests_additional);
- $setcook['addition'] = $requests_additional;
- if ($query['province'] == '3' or $query['province'] == 6 or $query['province'] == 9) {
- if (isset($requests_additional['around_jakarta_zone_one'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['around_jakarta_zone_one']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['around_jakarta_zone_one']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_two'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_three'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_four'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- }
- } else {
- if (isset($requests_additional['outside_around_jakarta_zone_one'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_one']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_one']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.outside_around_jakarta_zone_one AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_two'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_two']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_two']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_three'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['top'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['bottom'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_four'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['top'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['bottom'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- }
- }
- if (isset($requests_additional['rsmd'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.rsmd IS NOT NULL
- THEN (' . $counting . '/100)*additional_coverages.rsmd
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- if (isset($requests_additional['cc'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.cc IS NOT NULL
- THEN @others+(' . $counting . '/100)*additional_coverages.cc
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- if (isset($requests_additional['other'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.other IS NOT NULL
- THEN @others+(' . $counting . '/100)*additional_coverages.other
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- }
- } else {
- $additioning = $additionalController->all_risk['property'];
- $rationing = $rateController->standard['property'];
- $yo_property = 'standard';
- //cuman buat all risk doang dia ada additional
- if (isset($request['addition'])) {
- $requests_additional = json_decode($request['addition'], true);
- $count_request_additional = count($requests_additional);
- $setcook['addition'] = $requests_additional;
- if ($query['province'] == '3' or $query['province'] == 6 or $query['province'] == 9) {
- if (isset($requests_additional['around_jakarta_zone_one'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['around_jakarta_zone_one']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['around_jakarta_zone_one']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_two'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_two) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_three'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['around_jakarta_zone_four'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['top'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- WHEN additional_coverages.around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['around_jakarta_zone_one']['bottom'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.around_jakarta_zone_one AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- }
- } else {
- if (isset($requests_additional['outside_around_jakarta_zone_one'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_one IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_one="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_one']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_one="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_one']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.outside_around_jakarta_zone_one AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_two'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_two']['top'] . ' AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*' . $additioning['outside_around_jakarta_zone_two']['bottom'] . ' AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2)) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_three'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['top'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['bottom'] . '+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_three) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- } else if (isset($requests_additional['outside_around_jakarta_zone_four'])) {
- $sql .= '(CASE WHEN (coverages.bundle_zone=0 AND additional_coverages.outside_around_jakarta_zone_two IS NOT NULL)
- THEN @zone:=(CASE WHEN additional_coverages.outside_around_jakarta_zone_two="top"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['top'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- WHEN additional_coverages.outside_around_jakarta_zone_two="bottom"
- THEN CAST((' . $counting . '/100)*(' . $additioning['outside_around_jakarta_zone_two']['bottom'] . '+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- ELSE CAST((' . $counting . '/100)*(CAST(additional_coverages.outside_around_jakarta_zone_two AS DECIMAL(8,2))+additional_coverages.around_jakarta_zone_four) AS UNSIGNED)
- END)
- ELSE@zone:=0 END), ';
- }
- }
- if (isset($requests_additional['rsmd'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.rsmd IS NOT NULL
- THEN (' . $counting . '/100)*additional_coverages.rsmd
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- if (isset($requests_additional['cc'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.cc IS NOT NULL
- THEN @others+(' . $counting . '/100)*additional_coverages.cc
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- if (isset($requests_additional['other'])) {
- $sql .= '(CASE WHEN coverages.rsmd_zone=0
- THEN @others:=(CASE WHEN additional_coverages.other IS NOT NULL
- THEN @others+(' . $counting . '/100)*additional_coverages.other
- ELSE 0 END)
- ELSE @others:=@others END), ';
- }
- }
- }
- $case_off = '';
- $classer = [1 => 'class_one_rate', 2 => 'class_two_rate', 3 => 'class_three_rate'];
- $variable = [
- 1 => 'below.6',
- 2 => 'in.6-18',
- 3 => 'up.to.18',
- 4 => 'park.up.to.24',
- 5 => 'less.than.3.storeys',
- 6 => 'boarding.house',
- 7 => 'floating',
- ];
- // jika ada construcation property query string
- foreach ($variable as $key => $value) {
- if ($query['class_property'] == $value) {
- if (isset($query['construction_property'])) {
- $case_off .= '@top:=' . $rationing[$key][$query['construction_property']]['top'] . ', ';
- DB::statement('SET @top:=' . $rationing[$key][$query['construction_property']]['top'] . '');
- $case_off .= '@bottom:=' . $rationing[$key][$query['construction_property']]['bottom'] . ', ';
- DB::statement('SET @bottom:=' . $rationing[$key][$query['construction_property']]['bottom'] . '');
- }
- }
- }
- $sql .= $case_off;
- $sql .= '@premi:=(CASE WHEN product.type_of_insurance="' . $query['level_of_insurance'] . '"
- THEN (CASE WHEN coverages.class_custom_rate IS NOT NULL AND coverages.class_custom_rate > 0
- THEN (' . $counting . '/1000)*coverages.class_custom_rate
- ELSE (CASE WHEN coverages.' . $classer[$query['construction_property']] . '="top"
- THEN (' . $counting . '/1000)*@top
- WHEN coverages.' . $classer[$query['construction_property']] . '="bottom"
- THEN (' . $counting . '/1000)*@bottom
- ELSE (' . $counting . '/1000)*coverages.' . $classer[$query['construction_property']] . ' END)
- END)
- END), ';
- } else {
- $rationing = $rateController->earthquake['property'];
- $yo_property = 'earthquake';
- $case_off = '';
- $classer = [1 => 'zone_one_rate', 2 => 'zone_two_rate', 3 => 'zone_three_rate', 4 => 'zone_four_rate', 5 => 'zone_five_rate'];
- $limiting = [1 => [1 => 1],
- 2 => [1 => 1]];
- $variable = [
- 1 => 'below.6',
- 2 => 'in.6-18',
- 3 => 'up.to.18',
- 4 => 'park.up.to.24',
- 5 => 'less.than.3.storeys',
- 6 => 'boarding.house',
- 7 => 'floating',
- ];
- // jika ada construcation property query string
- if (isset($query['construction_property'])) {
- $case_off .= '(CASE
- WHEN zone_territory.zone_id=1
- THEN (CASE WHEN coverages.' . $classer[1] . '="fixed"
- THEN @fixed:=' . $rationing[$query['construction_property']][1][1]['fixed'] . '
- ELSE @fixed:=coverages.' . $classer[1] . '
- END)
- WHEN zone_territory.zone_id=2
- THEN (CASE WHEN coverages.' . $classer[2] . '="fixed"
- THEN @fixed:=' . $rationing[$query['construction_property']][1][2]['fixed'] . '
- ELSE @fixed:=coverages.' . $classer[2] . '
- END)
- WHEN zone_territory.zone_id=3
- THEN (CASE WHEN coverages.' . $classer[3] . '="fixed"
- THEN @fixed:=' . $rationing[$query['construction_property']][1][3]['fixed'] . '
- ELSE @fixed:=coverages.' . $classer[3] . '
- END)
- WHEN zone_territory.zone_id=4
- THEN (CASE WHEN coverages.' . $classer[4] . '="fixed"
- THEN @fixed:=' . $rationing[$query['construction_property']][1][4]['fixed'] . '
- ELSE @fixed:=coverages.' . $classer[4] . '
- END)
- ELSE (CASE WHEN coverages.' . $classer[5] . '="fixed"
- THEN @fixed:=' . $rationing[$query['construction_property']][1][5]['fixed'] . '
- ELSE @fixed:=coverages.' . $classer[5] . '
- END)
- END), ';
- }
- $sql .= $case_off;
- $sql .= '@premi:=(CASE WHEN product.type_of_insurance="' . $query['level_of_insurance'] . '"
- THEN (CASE WHEN coverages.zone_custom_rate IS NOT NULL AND coverages.zone_custom_rate > 0
- THEN (' . $counting . '/1000)*coverages.zone_custom_rate
- ELSE (' . $counting . '/1000)*@fixed END)
- END), ';
- }
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premi+@amount_price, @premi), ';
- $sql .= '@premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), ';
- $sql .= '@premi:=@premi+@others, ';
- $sql .= '@premi:=@premi+@zone, ';
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=ROUND(@premi+@admin_fee+@materai_fee), ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_questionnaire_property) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_property) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_property_fire_' . $yo_property . ') coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_additional_property) as additional_coverages ON additional_coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM zone_territory_earthquake) zone_territory ON true ';
- $sql .= 'WHERE (';
- $level_of_insurance = $query['level_of_insurance'];
- $construction_property = $query['construction_property'];
- if (isset($query['class_property'])) {
- $class_property = $query['class_property'];
- }
- unset($query['level_of_insurance']);
- unset($query['class_property']);
- unset($query['type_of_insurance']);
- $sql .= 'IF(questionnaires.what_type_of_the_property IS NOT NULL, questionnaires.what_type_of_the_property LIKE "%' . $query['what_type_of_the_property'] . '%", questionnaires.what_type_of_the_property IS NULL) AND ';
- $sql .= 'IF(questionnaires.why_are_you_looking_for_cover IS NOT NULL, questionnaires.why_are_you_looking_for_cover LIKE "%' . $query['why_are_you_looking_for_cover'] . '%", questionnaires.why_are_you_looking_for_cover IS NULL) AND ';
- switch((integer)$query['construction_property']){
- case 1:
- $cons_class = 'one';
- break;
- case 2:
- $cons_class = 'two';
- break;
- case 3:
- $cons_class = 'three';
- break;
- }
- // tambah lagi class construction
- if ($level_of_insurance == 'all_risk' or $level_of_insurance == 'standard') {
- $var = 'flexas_class_construction_'.$cons_class;
- $sql .= 'IF(questionnaires.flexas_class_construction IS NOT NULL, questionnaires.flexas_class_construction LIKE "%' . $var . '%", questionnaires.flexas_class_construction IS NULL) ';
- } else {
- $var = 'earthquake_class_construction_'.$cons_class;
- $sql .= 'IF(questionnaires.earthquake_class_construction IS NOT NULL, questionnaires.earthquake_class_construction LIKE "%' . $var . '%", questionnaires.earthquake_class_construction IS NULL) ';
- }
- break;
- case 'personal-accident':
- $sql = 'SELECT product.*, product_detail.*, product_meta.*, features.*, features.count as count, questionnaires.claim, ';
- $sql .= $amount_features . $percentages_features;
- switch ($query['type_of_work']) {
- case 'one':
- $sql .= '@coverages_main:=coverages.class_one_rate, ';
- break;
- case 'two':
- $sql .= '@coverages_main:=coverages.class_two_rate, ';
- break;
- case 'three':
- $sql .= '@coverages_main:=coverages.class_three_rate, ';
- break;
- case 'four':
- $sql .= '@coverages_main:=coverages.class_four_rate, ';
- break;
- default:
- $sql .= '@coverages_main:=coverages.class_five_rate, ';
- break;
- }
- $use_calc = '@premi:=@coverages_main, ';
- if ($query['type_of_work_couple'] != '' and $query['type_of_cover'] == 'couple') {
- switch ($query['type_of_work_couple']) {
- case 'one':
- $sql .= '@coverages_other:=coverages_detail.class_one_rate, ';
- break;
- case 'two':
- $sql .= '@coverages_other:=coverages_detail.class_two_rate, ';
- break;
- case 'three':
- $sql .= '@coverages_other:=coverages_detail.class_three_rate, ';
- break;
- case 'four':
- $sql .= '@coverages_other:=coverages_detail.class_four_rate, ';
- break;
- default:
- $sql .= '@coverages_other:=coverages_detail.class_five_rate, ';
- break;
- }
- $use_calc = '@premi:=@coverages_main+@coverages_other, ';
- }
- $sql .= $use_calc;
- $sql .= '@percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@premi/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premi+@amount_price, @premi), ';
- $sql .= '@premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), ';
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=@premi+@admin_fee+@materai_fee, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT *,';
- $sql .= 'CASE WHEN label_claim_two IS NOT NULL AND (amount_claim_two > amount_claim_one AND amount_claim_two > amount_claim_three AND amount_claim_two > amount_claim_four AND amount_claim_two > amount_claim_five )THEN @claim:=amount_claim_two
- WHEN label_claim_three IS NOT NULL AND (amount_claim_three > amount_claim_one AND amount_claim_three > amount_claim_two AND amount_claim_three > amount_claim_four AND amount_claim_three > amount_claim_five )THEN @claim:=amount_claim_three
- WHEN label_claim_four IS NOT NULL AND (amount_claim_four > amount_claim_one AND amount_claim_four > amount_claim_two AND amount_claim_four > amount_claim_three AND amount_claim_four > amount_claim_five )THEN @claim:=amount_claim_four
- WHEN label_claim_five IS NOT NULL AND (amount_claim_five > amount_claim_one AND amount_claim_five > amount_claim_two AND amount_claim_five > amount_claim_three AND amount_claim_five > amount_claim_four )THEN @claim:=amount_claim_five
- ELSE @claim:=amount_claim_one END AS claim ';
- $sql .= 'FROM insurance_questionnaire_personal_accident) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_personal_accident) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_self_accident) coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_self_accident_other) coverages_detail ON coverages_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'WHERE (';
- // $value_of_cover = $query['value_of_cover'];
- unset($query['product_level']);
- unset($query['value_of_cover']);
- /*foreach ($query as $key => $value) {
- switch ($key) {
- default:
- $sql .= 'questionnaires.' . $key . ' IS NULL OR questionnaires.' . $key . ' LIKE "%' . $value . '%" ';
- break;
- }
- if ($counter < count($query) - 1) {
- if ($value != '') {
- $sql .= 'AND ';
- }
- }
- $counter++;
- }
- */
- $sql .= 'IF(questionnaires.type_of_cover IS NOT NULL, questionnaires.type_of_cover LIKE "%' . $query['type_of_cover'] . '%", questionnaires.type_of_cover IS NULL) AND ';
- $sql .= 'IF(questionnaires.type_of_work IS NOT NULL, questionnaires.type_of_work LIKE "%' . $query['type_of_work'] . '%", questionnaires.type_of_work IS NULL) AND ';
- //$sql .= 'IF(questionnaires.warranty_status IS NOT NULL, questionnaires.warranty_status LIKE "%' . $query['warranty_status'] . '%", questionnaires.warranty_status IS NULL) AND ';
- $sql .= 'IF(questionnaires.gender IS NOT NULL, questionnaires.gender = "' . $query['gender'] . '" OR questionnaires.gender = "male.or.female", questionnaires.gender IS NULL) ';
- break;
- case 'travel':
- $sql = 'SELECT product.*,
- product_detail.*,
- product_meta.*,
- features.*,
- features.count as count,
- questionnaires.*, ';
- $arrival = date_create(str_replace('/', '-', $query['arrival_date']));
- $departure = date_create(str_replace('/', '-', $query['departure_date']));
- $date_diff = date_diff($arrival, $departure);
- $add_one_date_diff = $date_diff->format('%a') + 1;
- $week_diff = ceil($add_one_date_diff / 7);
- $sql .= $amount_features . $percentages_features;
- if ($query['who_is_the_insurance_for'] != 'family') {
- $people = ['just.me' => 'one_person', 'couple' => 'two_person'];
- $people = $people[$query['who_is_the_insurance_for']];
- $extra = 'extra_' . $people;
- $select = $people;
- } else {
- $counted = [3 => 'three_person', 4 => 'four_person', 5 => 'five_person'];
- $people = $counted[(integer)$query['group_count']];
- $extra = 'extra_' . $people;
- $select = $people;
- }
- if ($query['trip_type'] != 'annual.trip') {
- switch ($query['travel_category']) {
- case 'asean':
- $sql .= '@premi:=(CASE
- -- Gua ngambil dulu days_min tertinggi abis itu limit 1.
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_asean where product_id = product.id AND days_min <' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_asean WHERE product_id = product.id AND days_min <' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Disiini days_min yang emang udah pas, gak kurang gak lebih, jadi di kondisi ke satu itu kondisi dimana days_min masih lebih besar
- -- disini kondisi dimana days_min sesuai dan pas gak kurang gak lebih
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_asean where product_id = product.id AND days_min = ' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_asean WHERE product_id = product.id AND days_min = ' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Selain itu ada kondisi tambahan, jika dia punya extra weekly maka premi bakalan ditambahain sesuai weekly
- -- kalo emang days atau daily, ditambahain perharian selain itu NULL
- ELSE
- (CASE WHEN EXISTS (SELECT 1 from insurance_coverage_travel_asean WHERE product_id = product.id AND (extra = "weekly" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+(ceil((' . $add_one_date_diff . '-days_min)/7)*' . $extra . ') from insurance_coverage_travel_asean WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1 )
- WHEN EXISTS (SELECT 1 from insurance_coverage_travel_asean WHERE product_id = product.id AND (extra = "days" OR extra = "daily" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+((' . $add_one_date_diff . '-days_min)*' . $extra . ') from insurance_coverage_travel_asean WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1)
- ELSE NULL
- END)
- END) AS premi, ';
- break;
- case 'asia':
- $sql .= '@premi:=(CASE
- -- Gua ngambil dulu days_min tertinggi abis itu limit 1.
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_asia where product_id = product.id AND days_min <' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_asia WHERE product_id = product.id AND days_min <' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Disiini days_min yang emang udah pas, gak kurang gak lebih, jadi di kondisi ke satu itu kondisi dimana days_min masih lebih besar
- -- disini kondisi dimana days_min sesuai dan pas gak kurang gak lebih
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_asia where product_id = product.id AND days_min = ' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_asia WHERE product_id = product.id AND days_min = ' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Selain itu ada kondisi tambahan, jika dia punya extra weekly maka premi bakalan ditambahain sesuai weekly
- -- kalo emang days atau daily, ditambahain perharian selain itu NULL
- ELSE
- (CASE WHEN EXISTS (SELECT 1 from insurance_coverage_travel_asia WHERE product_id = product.id AND (extra = "weekly" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+(ceil((' . $add_one_date_diff . '-days_min)/7)*' . $extra . ') from insurance_coverage_travel_asia WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1 )
- WHEN EXISTS (SELECT 1 from insurance_coverage_travel_asia WHERE product_id = product.id AND (extra = "days" OR extra = "daily" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+((' . $add_one_date_diff . '-days_min)*' . $extra . ') from insurance_coverage_travel_asia WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1)
- ELSE NULL
- END)
- END) AS premi, ';
- break;
- case 'worldwide_incl_shenzeng':
- $sql .= '@premi:=(CASE
- -- Gua ngambil dulu days_min tertinggi abis itu limit 1.
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_wwis where product_id = product.id AND days_min <' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_wwis WHERE product_id = product.id AND days_min <' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Disiini days_min yang emang udah pas, gak kurang gak lebih, jadi di kondisi ke satu itu kondisi dimana days_min masih lebih besar
- -- disini kondisi dimana days_min sesuai dan pas gak kurang gak lebih
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_wwis where product_id = product.id AND days_min = ' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_wwis WHERE product_id = product.id AND days_min = ' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Selain itu ada kondisi tambahan, jika dia punya extra weekly maka premi bakalan ditambahain sesuai weekly
- -- kalo emang days atau daily, ditambahain perharian selain itu NULL
- ELSE
- (CASE WHEN EXISTS (SELECT 1 from insurance_coverage_travel_wwis WHERE product_id = product.id AND (extra = "weekly" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+(ceil((' . $add_one_date_diff . '-days_min)/7)*' . $extra . ') from insurance_coverage_travel_wwis WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1 )
- WHEN EXISTS (SELECT 1 from insurance_coverage_travel_wwis WHERE product_id = product.id AND (extra = "days" OR extra = "daily" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+((' . $add_one_date_diff . '-days_min)*' . $extra . ') from insurance_coverage_travel_wwis WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1)
- ELSE NULL
- END)
- END) AS premi, ';
- break;
- case 'worldwide_excl_shenzeng':
- $sql .= '@premi:=(CASE
- -- Gua ngambil dulu days_min tertinggi abis itu limit 1.
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_wwes where product_id = product.id AND days_min <' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_wwes WHERE product_id = product.id AND days_min <' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Disiini days_min yang emang udah pas, gak kurang gak lebih, jadi di kondisi ke satu itu kondisi dimana days_min masih lebih besar
- -- disini kondisi dimana days_min sesuai dan pas gak kurang gak lebih
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_wwes where product_id = product.id AND days_min = ' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_wwes WHERE product_id = product.id AND days_min = ' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Selain itu ada kondisi tambahan, jika dia punya extra weekly maka premi bakalan ditambahain sesuai weekly
- -- kalo emang days atau daily, ditambahain perharian selain itu NULL
- ELSE
- (CASE WHEN EXISTS (SELECT 1 from insurance_coverage_travel_wwes WHERE product_id = product.id AND (extra = "weekly" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+(ceil((' . $add_one_date_diff . '-days_min)/7)*' . $extra . ') from insurance_coverage_travel_wwes WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1 )
- WHEN EXISTS (SELECT 1 from insurance_coverage_travel_wwes WHERE product_id = product.id AND (extra = "days" OR extra = "daily" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+((' . $add_one_date_diff . '-days_min)*' . $extra . ') from insurance_coverage_travel_wwes WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1)
- ELSE NULL
- END)
- END) AS premi, ';
- break;
- default:
- $sql .= '@premi:=(CASE
- -- Gua ngambil dulu days_min tertinggi abis itu limit 1.
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_domestic where product_id = product.id AND days_min <' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_domestic WHERE product_id = product.id AND days_min <' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Disiini days_min yang emang udah pas, gak kurang gak lebih, jadi di kondisi ke satu itu kondisi dimana days_min masih lebih besar
- -- disini kondisi dimana days_min sesuai dan pas gak kurang gak lebih
- WHEN EXISTS(SELECT 1 from insurance_coverage_travel_domestic where product_id = product.id AND days_min = ' . $add_one_date_diff . ')
- THEN (SELECT ' . $select . ' from insurance_coverage_travel_domestic WHERE product_id = product.id AND days_min = ' . $add_one_date_diff . ' ORDER BY `days_min` DESC LIMIT 1 )
- -- Selain itu ada kondisi tambahan, jika dia punya extra weekly maka premi bakalan ditambahain sesuai weekly
- -- kalo emang days atau daily, ditambahain perharian selain itu NULL
- ELSE
- (CASE WHEN EXISTS (SELECT 1 from insurance_coverage_travel_domestic WHERE product_id = product.id AND (extra = "weekly" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+(ceil((' . $add_one_date_diff . '-days_min)/7)*' . $extra . ') from insurance_coverage_travel_domestic WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1 )
- WHEN EXISTS (SELECT 1 from insurance_coverage_travel_domestic WHERE product_id = product.id AND (extra = "days" OR extra = "daily" AND extra IS NOT NULL))
- THEN (SELECT ' . $people . '+((' . $add_one_date_diff . '-days_min)*' . $extra . ') from insurance_coverage_travel_domestic WHERE product_id = product.id ORDER BY days_min DESC LIMIT 1)
- ELSE NULL
- END)
- END) AS premi, ';
- break;
- }
- $dump = '';
- } else {
- $annual = ['asean' => 'asean', 'asia' => 'asia', 'domestic' => 'domestic', 'worldwide_incl_shenzeng' => 'wwis', 'worldwide_excl_shenzeng' => 'wwes'];
- $sql .= '@premi:=(SELECT ' . $people . ' from insurance_coverage_travel_annual WHERE product_id = product.id AND type="' . $annual[$query['travel_category']] . '" LIMIT 1) AS premi, ';
- $dump = 'AND (SELECT ' . $people . ' from insurance_coverage_travel_annual WHERE product_id = product.id AND type="' . $annual[$query['travel_category']] . '" LIMIT 1) IS NOT NULL AND (SELECT ' . $people . ' from insurance_coverage_travel_annual WHERE product_id = product.id AND type="' . $annual[$query['travel_category']] . '" LIMIT 1) > 0 ';
- }
- $sql .= '@percentage_feature_prices:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@premi/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premi+@amount_price, @premi), ';
- $sql .= '@premi:=IF(@percentage_feature_prices IS NOT NULL AND @percentage_feature_prices > 0, @premi+@percentage_feature_prices, @premi), ';
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=@premi+@admin_fee+@materai_fee, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_questionnaire_travel) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_travel) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'WHERE (';
- $ab = 'IF(questionnaires.product_level IS NOT NULL, questionnaires.product_level LIKE "%' . $query['product_level'] . '%", questionnaires.product_level IS NULL) ' . $dump;
- unset($query['product_level']);
- unset($query['country']);
- unset($query['travel_province']);
- unset($query['travel_city']);
- // unset($query['travel_category']);
- unset($query['departure_date']);
- unset($query['arrival_date']);
- unset($query['group_count']);
- $sql .= 'IF(questionnaires.who_is_the_insurance_for IS NOT NULL, questionnaires.who_is_the_insurance_for LIKE "%' . $query['who_is_the_insurance_for'] . '%", questionnaires.who_is_the_insurance_for IS NULL) AND ';
- $sql .= 'IF(questionnaires.coverage_level IS NOT NULL, questionnaires.coverage_level LIKE "%' . str_replace('_', '.', $query['travel_category']) . '%", questionnaires.coverage_level IS NULL) AND ';
- $sql .= 'IF(questionnaires.travel_category IS NOT NULL, questionnaires.travel_category LIKE "%' . $query['travel_category'] . '%", questionnaires.travel_category IS NULL) ';
- break;
- case 'health':
- $sql = 'SELECT product.*,
- product_detail.*,
- product_meta.*,
- features.*,
- features.count as count,
- questionnaires.*, ';
- $ab = '';
- $fams = '';
- $now = date_create(date('Y-m-d'));
- $index = 0;
- if ($query['who_need_the_cover'] == 'couple') {
- $birth = [
- 'guy' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_guy'])))),
- 'girl' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_girl'])))),
- ];
- unset($query['couple_guy']);
- unset($query['couple_girl']);
- $zz = 0;
- $counter_birth = count($birth) - 1;
- // di loop aja biar singkat
- foreach ($birth as $k => $v) {
- if ($k == 'guy') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- // absolute searches for main
- $ab .= 'coverages.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- } else {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_others.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_others.limitation >= ' . date_diff($now, $v)->y . ' ';
- if ($zz < $counter_birth - 1) {
- $ab .= 'AND ';
- }
- $zz++;
- }
- }
- $sql .= '@premi:=@guy_yearly+@girl_yearly, ';
- $sql .= '@semester:=@guy_semester+@girl_semester, ';
- $sql .= '@quartal:=@guy_quartal+@girl_quartal, ';
- $sql .= '@monthly:=@guy_monthly+@girl_monthly, ';
- } elseif ($query['who_need_the_cover'] == 'family') {
- $birth = [
- 'husband' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_husband'])))),
- 'wife' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_wife'])))),
- ];
- $children = [1, 2, 3, 4];
- foreach ($children as $kid) {
- if (isset($query['family_children_' . $kid])) {
- $birth['kid_' . $kid] = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_children_' . $kid]))));
- unset($query['family_children_' . $kid]);
- }
- unset($query['family_husband']);
- unset($query['family_wife']);
- }
- $zz = 0;
- $fam_yearly = '';
- $fam_semester = '';
- $fam_quartal = '';
- $fam_monthly = '';
- $counter_birth = count($birth) - 2;
- foreach ($birth as $k => $v) {
- if ($k == 'husband') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverages.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- $fam_yearly .= '@' . $k . '_yearly+';
- $fam_semester .= '@' . $k . '_semester+';
- $fam_quartal .= '@' . $k . '_quartal+';
- $fam_monthly .= '@' . $k . '_monthly+';
- } elseif ($k == 'wife') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_others.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_others.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- $fam_yearly .= '@' . $k . '_yearly+';
- $fam_semester .= '@' . $k . '_semester+';
- $fam_quartal .= '@' . $k . '_quartal+';
- $fam_monthly .= '@' . $k . '_monthly+';
- } else {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_health_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_childrens.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_childrens.limitation >= ' . date_diff($now, $v)->y . ' ';
- $fam_yearly .= '@' . $k . '_yearly';
- $fam_semester .= '@' . $k . '_semester';
- $fam_quartal .= '@' . $k . '_quartal';
- $fam_monthly .= '@' . $k . '_monthly';
- if ($zz < $counter_birth - 1) {
- $fam_yearly .= '+';
- $fam_semester .= '+';
- $fam_quartal .= '+';
- $fam_monthly .= '+';
- $ab .= 'AND ';
- }
- $zz++;
- }
- }
- $sql .= '@premi:=' . $fam_yearly . ', ';
- $sql .= '@semester:=' . $fam_semester . ', ';
- $sql .= '@quartal:=' . $fam_quartal . ', ';
- $sql .= '@monthly:=' . $fam_monthly . ', ';
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['single_date']))));
- $sql .= '@single_yearly:=(SELECT yearly_rates FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_health WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@premi:=@single_yearly, ';
- $sql .= '@semester:=@single_semester, ';
- $sql .= '@quartal:=@single_quartal, ';
- $sql .= '@monthly:=@single_monthly, ';
- $ab .= 'coverages.age_min <= ' . date_diff($now, $birth)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $birth)->y . ' ';
- unset($query['single_date']);
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=@premi+@admin_fee+@materai_fee, ';
- $sql .= 'IF(@semester > 0, @semester:=@semester+@admin_fee+@materai_fee, @semester:=0), ';
- $sql .= 'IF(@quartal > 0, @quartal:=@quartal+@admin_fee+@materai_fee, @quartal:=0), ';
- $sql .= 'IF(@monthly > 0, @monthly:=@monthly+@admin_fee+@materai_fee, @monthly:=0), ';
- /*
- // FEATURE ADDITIONAL
- $sql .= '@percentage_feature_prices_yearly:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@premi/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@percentage_feature_prices_semester:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@semester/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@percentage_feature_prices_quartal:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@quartal/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@percentage_feature_prices_monthly:=(CASE WHEN @percentages_price IS NOT NULL AND @percentages_price > 0
- THEN (@monthly/100)*@percentages_price
- ELSE 0 END), ';
- $sql .= '@premi:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @premi+(@amount_price*12), @premi), ';
- $sql .= '@premi:=IF(@percentage_feature_prices_yearly IS NOT NULL AND @percentage_feature_prices_yearly > 0, @premi+@percentage_feature_prices_yearly, @premi), ';
- $sql .= '@semester:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @semester+(@amount_price*6), @semester), ';
- $sql .= '@semester:=IF(@percentage_feature_prices_semester IS NOT NULL AND @percentage_feature_prices_semester > 0, @semester+@percentage_feature_prices_semester, @semester), ';
- $sql .= '@quartal:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @quartal+(@amount_price*4), @quartal), ';
- $sql .= '@quartal:=IF(@percentage_feature_prices_quartal IS NOT NULL AND @percentage_feature_prices_quartal > 0, @quartal+@percentage_feature_prices_quartal, @quartal), ';
- $sql .= '@monthly:=IF(@amount_price IS NOT NULL AND @amount_price > 0, @monthly+@amount_price, @monthly), ';
- $sql .= '@monthly:=IF(@percentage_feature_prices_monthly IS NOT NULL AND @percentage_feature_prices_monthly > 0, @monthly+@percentage_feature_prices_monthly, @monthly), ';
- // END OF FEATURE ADDITIONAL
- */
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @semester, product.premi) AS UNSIGNED) as semester, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @quartal, product.premi) AS UNSIGNED) as quartal, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @monthly, product.premi) AS UNSIGNED) as bulanan, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_questionnaire_health) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_health) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_health) coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_health_others) coverage_others ON coverage_others.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_health_childrens) coverage_childrens ON coverage_childrens.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'WHERE (';
- $ab .= ' AND IF(questionnaires.product_level IS NOT NULL, questionnaires.product_level LIKE "%' . $query['product_level'] . '%", questionnaires.product_level IS NULL) ';
- unset($query['product_level']);
- $sql .= 'IF(questionnaires.what_type_of_cover_would_you_like IS NOT NULL, questionnaires.what_type_of_cover_would_you_like LIKE "%' . $query['what_type_of_cover_would_you_like'] . '%", questionnaires.what_type_of_cover_would_you_like IS NULL) AND ';
- $sql .= 'IF(questionnaires.who_need_cover IS NOT NULL, questionnaires.who_need_cover LIKE "%' . $query['who_need_the_cover'] . '%", questionnaires.who_need_cover IS NULL) AND ';
- $sql .= 'IF(questionnaires.do_you_currently_have_a_health_insurance IS NOT NULL, questionnaires.do_you_currently_have_a_health_insurance LIKE "%' . $query['do_you_currently_have_a_health_insurance'] . '%", questionnaires.do_you_currently_have_a_health_insurance IS NULL) AND ';
- $sql .= 'IF(questionnaires.gender IS NOT NULL, questionnaires.gender = "' . $query['gender'] . '" OR questionnaires.gender = "male.or.female", questionnaires.gender IS NULL) ';
- break;
- case 'life':
- $sql = 'SELECT product.*,
- product_detail.*,
- product_meta.*,
- features.*,
- features.count as count,
- questionnaires.*, ';
- $ab = '';
- switch ($query['type_of_product']) {
- case 'basic':
- $fams = '';
- $now = date_create(date('Y-m-d'));
- $index = 0;
- if ($query['how_many_people_insured'] == 'couple') {
- $birth = [
- 'guy' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_guy'])))),
- 'girl' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_girl'])))),
- ];
- unset($query['couple_guy']);
- unset($query['couple_girl']);
- $zz = 0;
- $counter_birth = count($birth) - 1;
- // di loop aja biar singkat
- foreach ($birth as $k => $v) {
- if ($k == 'guy') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- // absolute searches for main
- $ab .= 'coverages.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- } else {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_others.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_others.limitation >= ' . date_diff($now, $v)->y . ' ';
- if ($zz < $counter_birth - 1) {
- $ab .= 'AND ';
- }
- $zz++;
- }
- }
- $sql .= '@premi:=@guy_yearly+@girl_yearly, ';
- $sql .= '@semester:=@guy_semester+@girl_semester, ';
- $sql .= '@quartal:=@guy_quartal+@girl_quartal, ';
- $sql .= '@monthly:=@guy_monthly+@girl_monthly, ';
- } elseif ($query['how_many_people_insured'] == 'family') {
- $birth = [
- 'husband' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_husband'])))),
- 'wife' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_wife'])))),
- ];
- $children = [1, 2, 3, 4];
- foreach ($children as $kid) {
- if (isset($query['family_children_' . $kid])) {
- $birth['kid_' . $kid] = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['family_children_' . $kid]))));
- unset($query['family_children_' . $kid]);
- }
- unset($query['family_husband']);
- unset($query['family_wife']);
- }
- $zz = 0;
- $fam_yearly = '';
- $fam_semester = '';
- $fam_quartal = '';
- $fam_monthly = '';
- $counter_birth = count($birth) - 2;
- foreach ($birth as $k => $v) {
- if ($k == 'husband') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverages.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- $fam_yearly .= '@' . $k . '_yearly+';
- $fam_semester .= '@' . $k . '_semester+';
- $fam_quartal .= '@' . $k . '_quartal+';
- $fam_monthly .= '@' . $k . '_monthly+';
- } elseif ($k == 'wife') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_others WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_others.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_others.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- $fam_yearly .= '@' . $k . '_yearly+';
- $fam_semester .= '@' . $k . '_semester+';
- $fam_quartal .= '@' . $k . '_quartal+';
- $fam_monthly .= '@' . $k . '_monthly+';
- } else {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_childrens WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_childrens.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_childrens.limitation >= ' . date_diff($now, $v)->y . ' ';
- $fam_yearly .= '@' . $k . '_yearly';
- $fam_semester .= '@' . $k . '_semester';
- $fam_quartal .= '@' . $k . '_quartal';
- $fam_monthly .= '@' . $k . '_monthly';
- if ($zz < $counter_birth - 1) {
- $fam_yearly .= '+';
- $fam_semester .= '+';
- $fam_quartal .= '+';
- $fam_monthly .= '+';
- $ab .= 'AND ';
- }
- $zz++;
- }
- }
- $sql .= '@premi:=' . $fam_yearly . ', ';
- $sql .= '@semester:=' . $fam_semester . ', ';
- $sql .= '@quartal:=' . $fam_quartal . ', ';
- $sql .= '@monthly:=' . $fam_monthly . ', ';
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['single_date']))));
- $sql .= '@single_yearly:=(SELECT yearly_rates FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_life_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@premi:=@single_yearly, ';
- $sql .= '@semester:=@single_semester, ';
- $sql .= '@quartal:=@single_quartal, ';
- $sql .= '@monthly:=@single_monthly, ';
- $ab .= 'coverages.age_min <= ' . date_diff($now, $birth)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $birth)->y . ' ';
- unset($query['single_date']);
- }
- break;
- default:
- break;
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=@premi+@admin_fee+@materai_fee, ';
- $sql .= 'IF(@semester > 0, @semester:=@semester+@admin_fee+@materai_fee, @semester:=0), ';
- $sql .= 'IF(@quartal > 0, @quartal:=@quartal+@admin_fee+@materai_fee, @quartal:=0), ';
- $sql .= 'IF(@monthly > 0, @monthly:=@monthly+@admin_fee+@materai_fee, @monthly:=0), ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @semester, product.premi) AS UNSIGNED) as semester, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @quartal, product.premi) AS UNSIGNED) as quartal, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @monthly, product.premi) AS UNSIGNED) as bulanan, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_questionnaire_life) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_life) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_life_basic) coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_life_others) coverage_others ON coverage_others.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_life_childrens) coverage_childrens ON coverage_childrens.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'WHERE (';
- $sql .= 'IF(questionnaires.how_many_people_insured IS NOT NULL, questionnaires.how_many_people_insured LIKE "%' . $query['how_many_people_insured'] . '%", questionnaires.how_many_people_insured IS NULL) AND ';
- $sql .= 'IF(questionnaires.gender IS NOT NULL, questionnaires.gender = "' . $query['gender'] . '" OR questionnaires.gender = "male.or.female", questionnaires.gender IS NULL) ';
- break;
- case 'personal-accident-age':
- $sql = 'SELECT product.*, product_detail.*, product_meta.*, features.*, features.count as count, questionnaires.claim, ';
- $sql .= $amount_features . $percentages_features;
- $ab = '';
- $now = date_create(date('Y-m-d'));
- if ($query['type_of_cover'] == 'couple') {
- $birth = [
- 'guy' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_guy'])))),
- 'girl' => date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['couple_girl'])))),
- ];
- unset($query['couple_guy']);
- unset($query['couple_girl']);
- $zz = 0;
- $counter_birth = count($birth) - 1;
- // di loop aja biar singkat
- foreach ($birth as $k => $v) {
- if ($k == 'guy') {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- // absolute searches for main
- $ab .= 'coverages.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $v)->y . ' AND ';
- } else {
- $sql .= '@' . $k . '_yearly:=(SELECT yearly_rates FROM insurance_coverage_self_accident_age_couple WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_self_accident_age_couple WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_self_accident_age_couple WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@' . $k . '_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_self_accident_age_couple WHERE age_min <= ' . date_diff($now, $v)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $ab .= 'coverage_others.age_min <= ' . date_diff($now, $v)->y . ' AND ';
- $ab .= 'coverage_others.limitation >= ' . date_diff($now, $v)->y . ' ';
- if ($zz < $counter_birth - 1) {
- $ab .= 'AND ';
- }
- $zz++;
- }
- }
- $sql .= '@premi:=@guy_yearly+@girl_yearly, ';
- $sql .= '@semester:=@guy_semester+@girl_semester, ';
- $sql .= '@quartal:=@guy_quartal+@girl_quartal, ';
- $sql .= '@monthly:=@guy_monthly+@girl_monthly, ';
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $query['single_date']))));
- $sql .= '@single_yearly:=(SELECT yearly_rates FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_semester:=(SELECT IF(semester_rates IS NOT NULL,semester_rates, 0) as semester FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_quartal:=(SELECT IF(quartal_rates IS NOT NULL,quartal_rates, 0) as quartal FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@single_monthly:=(SELECT IF(monthly_rates IS NOT NULL,monthly_rates, 0) as monthly FROM insurance_coverage_self_accident_age_basic WHERE age_min <= ' . date_diff($now, $birth)->y . ' AND product_id = product.id ORDER BY age_min DESC LIMIT 1), ';
- $sql .= '@premi:=@single_yearly, ';
- $sql .= '@semester:=@single_semester, ';
- $sql .= '@quartal:=@single_quartal, ';
- $sql .= '@monthly:=@single_monthly, ';
- $ab .= 'coverages.age_min <= ' . date_diff($now, $birth)->y . ' AND ';
- $ab .= 'coverages.limitation >= ' . date_diff($now, $birth)->y . ' ';
- unset($query['single_date']);
- }
- $sql .= $amount_features . $percentages_features;
- $sql .= $additional_fee_admin;
- $sql .= $additional_fee_materai;
- $sql .= '@premi:=@premi+@admin_fee+@materai_fee, ';
- $sql .= 'IF(@semester > 0, @semester:=@semester+@admin_fee+@materai_fee, @semester:=0), ';
- $sql .= 'IF(@quartal > 0, @quartal:=@quartal+@admin_fee+@materai_fee, @quartal:=0), ';
- $sql .= 'IF(@monthly > 0, @monthly:=@monthly+@admin_fee+@materai_fee, @monthly:=0), ';
- $sql .= 'CAST(IF(product.premi IS NULL, @premi, product.premi) AS UNSIGNED) as premi, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @semester, product.premi) AS UNSIGNED) as semester, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @quartal, product.premi) AS UNSIGNED) as quartal, ';
- $sql .= 'CAST(IF(product.premi IS NULL, @monthly, product.premi) AS UNSIGNED) as bulanan, ';
- $sql .= 'product.id as product_id ';
- $sql .= 'FROM insurance_product product ';
- $sql .= 'LEFT JOIN (SELECT *,';
- $sql .= 'CASE WHEN label_claim_two IS NOT NULL AND (amount_claim_two > amount_claim_one AND amount_claim_two > amount_claim_three AND amount_claim_two > amount_claim_four AND amount_claim_two > amount_claim_five )THEN @claim:=amount_claim_two
- WHEN label_claim_three IS NOT NULL AND (amount_claim_three > amount_claim_one AND amount_claim_three > amount_claim_two AND amount_claim_three > amount_claim_four AND amount_claim_three > amount_claim_five )THEN @claim:=amount_claim_three
- WHEN label_claim_four IS NOT NULL AND (amount_claim_four > amount_claim_one AND amount_claim_four > amount_claim_two AND amount_claim_four > amount_claim_three AND amount_claim_four > amount_claim_five )THEN @claim:=amount_claim_four
- WHEN label_claim_five IS NOT NULL AND (amount_claim_five > amount_claim_one AND amount_claim_five > amount_claim_two AND amount_claim_five > amount_claim_three AND amount_claim_five > amount_claim_four )THEN @claim:=amount_claim_five
- ELSE @claim:=amount_claim_one END AS claim ';
- $sql .= 'FROM insurance_questionnaire_personal_accident) questionnaires ON questionnaires.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_feature_personal_accident) features ON features.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_self_accident_age_basic) coverages ON coverages.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_coverage_self_accident_age_couple) coverages_others ON coverages_others.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT * FROM insurance_product_detail) product_detail ON product_detail.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id, image, product_id FROM insurance_product_meta) product_meta ON product_meta.product_id = product.id ';
- $sql .= 'LEFT JOIN (SELECT id,is_active FROM insurance_provider) provider ON product.provider_id = provider.id ';
- $sql .= 'LEFT JOIN (SELECT id, provider_id, last_login FROM users) users ON users.provider_id = provider.id ';
- $sql .= 'WHERE (';
- unset($query['product_level']);
- unset($query['value_of_cover']);
- $sql .= 'IF(questionnaires.type_of_cover IS NOT NULL, questionnaires.type_of_cover LIKE "%' . $query['type_of_cover'] . '%", questionnaires.type_of_cover IS NULL) AND ';
- $sql .= 'IF(questionnaires.type_of_work IS NOT NULL, questionnaires.type_of_work LIKE "%' . $query['type_of_work'] . '%", questionnaires.type_of_work IS NULL) AND ';
- //$sql .= 'IF(questionnaires.warranty_status IS NOT NULL, questionnaires.warranty_status LIKE "%' . $query['warranty_status'] . '%", questionnaires.warranty_status IS NULL) AND ';
- $sql .= 'IF(questionnaires.gender IS NOT NULL, questionnaires.gender = "' . $query['gender'] . '" OR questionnaires.gender = "male.or.female", questionnaires.gender IS NULL) ';
- break;
- }
- $type = str_replace('-', '.', $type);
- /*
- * Menambahkan logic ketika ada PRODUCT LEVEL untuk travel, personal accident dan health
- * maka ditambahkan logic untuk WHERE yang ada diluar lingkaran maut itu untuk mendapatkan
- * produk yang sesuai dengan level produknya
- */
- // if (isset($setcook['product_level'])) {
- // $sql .= ') AND product.type = "' . $type . '" AND product.is_active = 1 AND questionnaires.product_level="' . $setcook['product_level'] . '"';
- // } else {
- $sql .= ') AND product.type = "' . $type . '" AND product.is_active = 1 ';
- // }
- switch ($type) {
- case 'motorcycle':
- $sql .= 'AND zone_territory.province_id=' . $query['province'] . ' AND product.type_of_insurance="' . $query['level_of_insurance'] . '"';
- break;
- case 'car':
- $sql .= 'AND zone_territory.province_id=' . $query['province'] . ' AND product.type_of_insurance="' . $query['level_of_insurance'] . '" 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) ';
- break;
- case 'property':
- $sql .= 'AND zone_territory.city_id=' . $query['city'] . ' ';
- if ($level_of_insurance != 'earthquake') {
- $sql .= 'AND coverages.property="' . $class_property . '" AND product.type_of_insurance="' . $level_of_insurance . '" ';
- } else {
- $sql .= 'AND coverages.category='.$construction_property.' AND coverages.class=1 AND product.type_of_insurance="' . $level_of_insurance . '" ';
- }
- break;
- case 'health':
- $sql .= 'AND ' . $ab;
- break;
- case 'life':
- $sql .= 'AND ' . $ab;
- break;
- case 'travel':
- //Ini apa ya? Bikin error di search result
- $sql .= 'AND ' . $ab;
- break;
- case 'personal-accident':
- case 'personal-accident-age':
- $sql .= 'AND ' . $ab;
- break;
- }
- $sql .= $absolute;
- if ($factor == 'rates') {
- $sql .= ' AND product.rates > 0 ';
- }
- $sql .= ' AND provider.is_active=1 GROUP BY product.id ';
- // nanti kalo yang mau dipake buat average total_amount_of_features desc, awas jangan typo
- switch ($factor) {
- case 'price':
- $sql .= '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 ';
- break;
- case 'feature':
- $sql .= 'ORDER BY features.count DESC, features.total_amount_of_features DESC, premi ASC , product.rates DESC, (product.promo > "") DESC, product.viewers DESC,product.id DESC ';
- break;
- case 'expensive':
- $sql .= 'ORDER BY premi DESC, features.count ASC,features.total_amount_of_features ASC, product.rates ASC, (product.promo > "") DESC, product.viewers ASC, product.id DESC ';
- break;
- case 'rates':
- $sql .= 'ORDER BY product.rates DESC ';
- break;
- case 'cheapest':
- $sql .= '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 ';
- break;
- case 'average':
- $sql .= 'ORDER BY product.promo DESC, product.rates DESC, product.viewers DESC, users.last_login DESC';
- break;
- }
- if (isset($request['sort'])) {
- switch ($request['sort']) {
- default:
- $sql .= '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 ';
- break;
- case 'expensive':
- $sql .= 'ORDER BY premi DESC, features.count ASC,features.total_amount_of_features ASC, product.rates ASC, (product.promo > "") DESC, product.viewers ASC, product.id DESC ';
- break;
- }
- }
- if ($factor != 'average' and $factor != 'cheapest' and $factor != 'expensive') {
- $sql .= 'LIMIT 0,1';
- } elseif ($factor == 'average') {
- //$sql .= 'LIMIT 0,3';
- } elseif (isset($request['offset']) and $request['offset']) {
- $sql .= 'LIMIT ' . $request['offset'] . ',10';
- } else {
- $sql .= 'LIMIT 10';
- }
- //print($sql);die();
- $res = DB::select(DB::raw($sql));
- $features = collect([])->toArray();
- switch ($type) {
- case 'car':
- $ftr = $featureController->car;
- break;
- case 'motorcycle':
- $ftr = $featureController->motorcycle;
- break;
- case 'property':
- $ftr = $featureController->property;
- break;
- case 'health':
- $ftr = $featureController->health;
- break;
- case 'travel':
- $ftr = $featureController->travel;
- break;
- case 'personal.accident':
- case 'personal.accident.age':
- $ftr = $featureController->personal_accident;
- break;
- case 'life':
- $ftr = $featureController->life;
- break;
- }
- // features insertion
- foreach ($res as $key => $val) {
- $zo = 1;
- if ($val->sort_features != null and $val->sort_features != 'null') {
- $featurez = json_decode($val->sort_features);
- if (json_last_error() == JSON_ERROR_NONE) {
- $tmp_f = [];
- foreach ($val as $ka => $va) {
- foreach ($ftr as $k => $v) {
- if ($v['name'] == $ka and !is_null($va)) {
- if ($zo <= 4) {
- // $features = collect($features);
- foreach ($featurez as $keys => $vals) {
- $replace = trans('feature.' . str_replace('_', '.', $keys));
- /*if ( strlen( $replace ) > 20 ) {
- $replace = substr( $replace, 0, 20 ) . '...';
- }*/
- $tmp_f[$vals] = $replace;
- }
- ksort($tmp_f);
- foreach ($tmp_f as $vv) {
- $res[$key]->features[$vv] = $va;
- }
- $zo++;
- }
- }
- }
- }
- }
- } else {
- foreach ($val as $ka => $va) {
- foreach ($ftr as $k => $v) {
- if ($v['name'] == $ka and !is_null($va)) {
- if ($zo <= 4) {
- $features[$ka] = $va;
- $replace = trans('feature.' . str_replace('_', '.', $ka));
- /*
- if ( strlen( $replace ) > 20 ) {
- $replace = substr( $replace, 0, 20 ) . '...';
- }*/
- $res[$key]->features[$replace] = $va;
- $zo++;
- }
- }
- }
- }
- }
- }
- if ($factor == 'cheapest' or $factor == 'expensive') {
- if (count($res) < 2) {
- $res['limit'] = 'yes';
- }
- }
- if ($factor == 'average' and $type != "personal.accident") {
- $sum = 0;
- $count = count($res);
- foreach ($res as $k => $v) {
- $sum += $v->premi;
- }
- $avg = ($count < 1 ? 0 : $sum / $count); // fix division by zero
- // add 10% to $avg
- $avg *= (1 + 50 / 100);
- }
- // foreach
- foreach ($res as $k => $r) {
- if (!is_string($r)) {
- // create score object
- if ($factor == 'average' and $type != 'personal.accident') {
- if ($r->premi > $avg) {
- unset($res[$k]); // unset the product with premi above average
- continue;
- }
- $counter = ($r->count < 1 ? 1 : $r->count);
- $r->score = (int)($r->premi / $counter);
- $r->avg = $avg;
- $r->score = (int)($r->premi / $counter);
- $r->promo_bool = (!empty($r->promo)?100:50);
- $r->last_login = Insurance_Provider::where('id', $r->provider_id)->with([
- 'users.user' => function ($q) {
- return $q->orderBy('last_login', 'DESC');
- },
- ])->first()->last_login;
- }
- //end
- $r->int_premi = $r->premi;
- $r->premi = rupiah($r->premi);
- if(isset($r->semester)){
- if(is_int($r->semester)){
- $r->semester = rupiah($r->semester);
- }
- }
- if(isset($r->quartal)){
- if(is_int($r->quartal)){
- $r->quartal = rupiah($r->quartal);
- }
- }
- if(isset($r->bulanan)){
- if(is_int($r->bulanan)){
- $r->bulanan = rupiah($r->bulanan);
- }
- }
- switch ($type) {
- case 'personal.accident':
- $r->claim = rupiah($r->claim);
- break;
- case 'property':
- $r->values = (integer)$query['estimated_value_of_the_property_to_cover'];
- break;
- case 'car':
- $r->values = (integer)$query['car_value'];
- break;
- case 'motorcycle':
- $r->values = (integer)$query['motorcycle_value'];
- break;
- }
- $provider_slug = Insurance_Provider::find($r->provider_id)->slug;
- $url = url('/insurance') . '/' . $provider_slug . '/product/' . $r->slug . '';
- $r->partner = InsuranceProductPartner::where('product_id', $r->product_id)->count();
- $r->url = $url;
- $r->questionnaire = $setcook;
- $r->bypass = encrypt($setcook);
- }
- }
- // comment
- if ($factor == 'average' and $type != "personal.accident") {
- // sorting rules
- // udah muncul
- $udah = (array) json_decode($request['ids']);
- // konversi
- $uKeys = array_keys($udah);
- $uValues = array_values($udah);
- $uIntKeys = array_map('intval', $uKeys);
- $udah = array_combine($uIntKeys, $uValues);
- // done
- $excl = [];
- foreach($udah as $k=>$v){
- if($v>1){
- $excl[] = $k;
- }
- }
- $collection = collect($res);
- // sortby collection caranya terbalik dari eloquent, kalau disini dia dari kanan ke kiri
- $lastavg = clone $collection; // clone the object for last avg
- // if excl are more than 1, AND THERE IS OTHER RESULT then it must have whereNot In
- if(count($excl)>0){
- /** $try = array_slice($collection->whereNotIn('product_id', $excl)->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $try = array_slice($collection->whereNotIn('product_id', $excl)->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- if(count($try)>0){
- $x = $try;
- }else{
- /** $x = array_slice($collection->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $x = array_slice($collection->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- }
- }else{
- /** $x = array_slice($collection->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $x = array_slice($collection->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- }
- // not include
- // first increment to "udah" array
- $newUdah = [];
- foreach ($x as $item) {
- foreach($udah as $kUdah=>$vUdah){
- if($kUdah==$item->product_id){
- $udah[$item->product_id]++;
- }else{
- // if the product are never shown on cheapest and MF, assign it into newUdah
- if(!isset($udah[$item->product_id])){
- $newUdah[$item->product_id] = 1;
- }
- }
- }
- }
- // now we create the second exclude
- $notIn = [];
- // from newUdah
- foreach($newUdah as $k=>$v){
- $notIn[] = $k;
- }
- // and from udah, but if the product already showing twice
- foreach($udah as $k=>$v){
- if($v>1){
- $notIn[] = $k;
- }
- }
- // then prepare
- /**
- * Zul, semua script yang kaya dibawah ini diubah jadi sortByMulti ya, ini gw kasi contoh satu, sisanya lu tinggal ikutin..
- * Ini ga ad di dokumentasi laravel, karena ini custom..
- * Nah.. berhubung ini masih test.. script yang lama jangan dihapus.. melainkan di comment aja.. kaya contoh yang gw lakuin ini
- *
- * JANGAN LUPA!! Order nya dari kanan > kiri , jadi atas > bawah pas di sortByMulti, lihat contoh bawah
- $y = array_slice($lastavg->whereNotIn('product_id', $notIn)->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('int_premi')->sortBy('score')->values()->all(), 0, 1);
- * Pake yang dibawah ini ya
- * Thankyou zul
- **/
- $y = array_slice($lastavg->whereNotIn('product_id', $notIn)->sortByMulti([
- 'score' => 'ASC',
- 'int_premi' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,1);
- // if $y ( most value ) are below 1 then use the cheapes > mf > follow by others
- if(count($y)<1){
- /** $y = array_slice($lastavg->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortByDesc('total_amount_of_features')->sortByDesc('count')->sortBy('int_premi')->values()->all(), 0, 1); **/
- $y = array_slice($lastavg->sortByMulti([
- 'int_premi' => 'ASC',
- 'count' => 'DESC',
- 'total_amount_of_features' => 'DESC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC',
- ])->values()->all(),0,1);
- }
- // fill $x with the same product if there is only one
- // kage bushin no jutsu
- if (count($x) == 1) {
- $tmp_x = $x[0];
- array_push($x, $tmp_x);
- //$x[] = $tmp_x;
- }
- return array_merge($x, $y);
- //return $twoavg;
- } else {
- return $res;
- }
- }
- }
- public function addingViewers(Request $request, Insurance_Product_View $insurance_Product_View)
- {
- $result = false;
- $slugProduct = $request->slugProduct;
- $product_id = $request->product_id;
- $ip = $request->ip();
- // set session token
- // jika emang dia punya auth user maka dipake auth usernya buat otentikasi data, kalo enggak ya dibikinin random.
- if (!Auth::check()) {
- if (!Session::has('who')) {
- Session::put('who', hash('whirlpool', str_random(30)) . $ip);
- }
- $data_who = Session::get('who');
- } else {
- $data_who = Auth::user()->id;
- }
- // config token waktu produk yang saat ini lagi dilihat.
- if (Session::has($data_who)) {
- if (isset(Session::get($data_who)[$slugProduct])) {
- // ngambil waktu dulu berdasarkan produk sama token usernya juga, jika kurang dari maka next step kalo gak dibuatin baru
- if (Session::get($data_who)[$slugProduct]['token_time'] > time()) {
- if (Session::get($data_who)[$slugProduct]['token_break'] < 1) {
- if (Session::get($data_who)[$slugProduct]['break_time'] > time()) {
- // jika token time masih valid, tapi token break juga masih kurang dari 2
- // maka data view di input dan break_tokennya juga ditambah
- Session::put($data_who, array(
- $slugProduct => [
- 'token_time' => Session::get($data_who)[$slugProduct]['token_time'],
- 'token_break' => Session::get($data_who)[$slugProduct]['token_break'] + 1,
- 'break_time' => Session::get($data_who)[$slugProduct]['break_time']
- ]
- ));
- $result = $this->addingNewViewers($product_id, hash('whirlpool', $slugProduct), $insurance_Product_View, $ip);
- }
- }
- } else {
- Session::put($data_who, array(
- $slugProduct => [
- 'token_time' => time() + 3600,
- 'token_break' => 0,
- 'break_time' => time() + 30
- ]
- ));
- $result = $this->addingNewViewers($product_id, hash('whirlpool', $slugProduct), $insurance_Product_View, $ip);
- }
- } else {
- Session::put($data_who, array(
- $slugProduct => [
- 'token_time' => time() + 3600,
- 'token_break' => 0,
- 'break_time' => time() + 30
- ]
- ));
- $result = $this->addingNewViewers($product_id, hash('whirlpool', $slugProduct), $insurance_Product_View, $ip);
- }
- } else {
- Session::put($data_who, array(
- $slugProduct => [
- 'token_time' => time() + 3600,
- 'token_break' => 0,
- 'break_time' => time() + 30
- ]
- ));
- $result = $this->addingNewViewers($product_id, hash('whirlpool', $slugProduct), $insurance_Product_View, $ip);
- }
- if ($result) {
- return 'ok';
- } else {
- $remaining = Session::get($data_who)[$slugProduct]['token_time'];
- $now = time();
- $rems = date('H:i:s', $remaining - $now);
- return $rems . ' Remaining!';
- }
- }
- public function addingNewViewers($product_id, $token, $insurance_Product_View, $ip)
- {
- $insurance_Product_View->ip = $ip;
- $insurance_Product_View->product_id = $product_id;
- $insurance_Product_View->token = $token;
- DB::table('insurance_product')->whereId($product_id)->increment('viewers');
- return $insurance_Product_View->save();
- }
- /**
- * View search five result
- *
- * @param \Illuminate\Http\Request $request
- * @param \Thanatos\Modules\Insurance\Http\Controllers\FeatureController $featureController
- * @param \Thanatos\Modules\Insurance\Http\Controllers\AdditionalController $additionalController
- *
- * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
- */
- public function viewSearchFiveResult(Request $request, FeatureController $featureController, AdditionalController $additionalController)
- {
- //echo $request->fullUrl();exit;
- if ($request->has('type')) {
- if ($request->type == 'car') {
- if ($request->level_of_insurance == 'comprehensive') {
- $additional = $additionalController->comprehensive['car'];
- } else {
- $additional = $additionalController->total_loss_only['car'];
- }
- } elseif ($request->type == 'property') {
- if ($request->level_of_insurance == 'standard' or $request->level_of_insurance == 'all_risk') {
- $additional = $additionalController->all_risk['property'];
- if ($request->province == '3' or $request->province == '6' || $request->province == '9') {
- unset($additional['outside_around_jakarta_zone_one']);
- unset($additional['outside_around_jakarta_zone_two']);
- unset($additional['outside_around_jakarta_zone_three']);
- unset($additional['outside_around_jakarta_zone_four']);
- } else {
- unset($additional['around_jakarta_zone_one']);
- unset($additional['around_jakarta_zone_two']);
- unset($additional['around_jakarta_zone_three']);
- unset($additional['around_jakarta_zone_four']);
- }
- }
- } elseif ($request->type == 'motorcycle') {
- if ($request->level_of_insurance == 'comprehensive') {
- $additional = $additionalController->comprehensive['motorcycle'];
- } else {
- $additional = $additionalController->total_loss_only['motorcycle'];
- }
- }
- $title = trans('global.searchResult') . ' - ' . trans('global.title');
- $data = [
- 'types' => $request->type,
- 'qs' => parse_url($request->fullUrl())['query'],
- 'type' => $request->type,
- 'title' => $title,
- 'features' => $featureController->getAllByType($request, true),
- 'en' => str_replace('/search', '/en/search', $request->fullUrl()),
- 'id' => str_replace('/en', '', $request->fullUrl()),
- ];
- if (isset($additional)) {
- $data['additional'] = $additional;
- }
- return view('front.search.result', $data);
- }
- }
- public function viewSearchAllResult(Request $request, FeatureController $featureController, AdditionalController $additionalController)
- {
- if ($request->has('type')) {
- if ($request->type == 'car') {
- if ($request->level_of_insurance == 'comprehensive') {
- $additional = $additionalController->comprehensive['car'];
- } else {
- $additional = $additionalController->total_loss_only['car'];
- }
- } elseif ($request->type == 'property') {
- if ($request->level_of_insurance == 'all_risk' or $request->level_of_insurance == 'standard') {
- $additional = $additionalController->all_risk['property'];
- if ($request->province == '3' or $request->province == '6' || $request->province == '9') {
- unset($additional['outside_around_jakarta_zone_one']);
- unset($additional['outside_around_jakarta_zone_two']);
- unset($additional['outside_around_jakarta_zone_three']);
- unset($additional['outside_around_jakarta_zone_four']);
- } else {
- unset($additional['around_jakarta_zone_one']);
- unset($additional['around_jakarta_zone_two']);
- unset($additional['around_jakarta_zone_three']);
- unset($additional['around_jakarta_zone_four']);
- }
- }
- } elseif ($request->type == 'motorcycle') {
- if ($request->level_of_insurance == 'comprehensive') {
- $additional = $additionalController->comprehensive['motorcycle'];
- } else {
- $additional = $additionalController->total_loss_only['motorcycle'];
- }
- }
- $title = trans('global.showAllTitle') . ' - ' . trans('global.title');
- $view = array(
- 'qs' => parse_url($request->fullUrl())['query'],
- 'features' => $featureController->getAllByType($request, true),
- 'title' => $title,
- 'type' => $request->type,
- 'bypass' => $request->config,
- 'en' => str_replace('/search', '/en/search', $request->fullUrl()),
- 'id' => str_replace('/en', '', $request->fullUrl())
- );
- if (isset($additional)) {
- $view['additional'] = $additional;
- }
- return view('front.search.product-list', $view);
- }
- }
- public function viewComparerResult(Request $request, FeatureController $featureController)
- {
- if ($request->has('type') and $request->has('first') and $request->has('second')) {
- $view = array(
- 'features' => $featureController->getAllByType($request, true),
- );
- return view('front.search.product-list', $view);
- }
- }
- public function viewAllDetailFront(Request $request, $slug, $slugProduct, Insurance_Product_View $insurance_Product_View, AdditionalController $additionalController, RateController $rateController, FeatureController $featureController){
- $config = array();
- $detail = $this->getDetailProduct($slug, $slugProduct, $request->configuration, $request->ip(), $insurance_Product_View, $additionalController, $rateController, $featureController);
- $detail['config'] = $request->configuration;
- $detail['raw'] = $detail['configuration'];
- $detail['info_addons'] = [];
- $detail['info_features'] = [];
- $conf = decrypt($request->configuration);
- foreach ($conf as $k => $v) {
- if (!is_array($v)) {
- if ($k != 'age') {
- $types = str_replace('.', '-', $detail["product"]["type"]);
- $exclude = ['Provinsi', 'Kota', 'Kecamatan', 'Province', 'City', 'Subdistrict'];
- $k = trans('questionnaire.' . $types . '.' . str_replace('_', '.', $k));
- $v = trans('questionnaire.' . $types . '.' . str_replace('_', '.', $v));
- if (!in_array($k, $exclude)) {
- $v = str_replace('questionnaire.car.', '', $v);
- $v = str_replace('questionnaire.motorcycle.', '', $v);
- $v = str_replace('questionnaire.property.', '', $v);
- $v = str_replace('questionnaire.personal-accident.', '', $v);
- $v = str_replace('questionnaire.personal-accident-age.', '', $v);
- $v = str_replace('questionnaire.travel.', '', $v);
- $v = str_replace('questionnaire.health.', '', $v);
- $v = str_replace('questionnaire.life.', '', $v);
- $config[$k] = $v;
- }
- }
- }
- }
- if (isset($conf['addition'])) {
- foreach ($conf['addition'] as $k => $v) {
- if ($v > 100) {
- $detail['info_addons'][trans('additional.' . $detail['product']['type'] . "." . str_replace('_', '.', $k))] = rupiah($v);
- } else {
- $detail['info_addons'][trans('additional.' . $detail['product']['type'] . "." . str_replace('_', '.', $k))] = $v;
- }
- }
- }
- if (isset($conf['feature'])) {
- $rp = '';
- $ext = '';
- switch ($k) {
- case 'grace_period_for_decease_benefit':
- case 'grace_period_for_critical_illness_benefit':
- case 'survival_period_for_critical_illness_benefit':
- $ext = ' Days';
- break;
- case 'preexisting_condition_for_decease_benefit':
- case 'preexisting_condition_for_critical_illness_benefit':
- case 'policy_reinstatement':
- case 'policy_termination':
- $ext = ' Mo.';
- break;
- case 'investment_benefit':
- case 'automatic_premium_leave':
- $ext = ' Yr';
- break;
- default:
- if ($v < 100) {
- $ext = '%';
- } else {
- $rp = 'Rp. ';
- }
- break;
- }
- foreach ($conf['feature'] as $k => $v) {
- $detail['info_features'][trans('feature.' . str_replace('_', '.', $v))] = "true";
- }
- }
- $detail['configuration'] = $config;
- if(isset($detail['product']['semester'])){
- if($detail['product']['semester'] > 0){
- $detail['product']['semester'] = rupiah($detail['product']['semester']);
- }
- }
- if(isset($detail['product']['quartal'])){
- if($detail['product']['quartal'] > 0){
- $detail['product']['quartal'] = rupiah($detail['product']['quartal']);
- }
- }
- if(isset($detail['product']['monthly'])){
- if($detail['product']['monthly'] > 0){
- $detail['product']['monthly'] = rupiah($detail['product']['monthly']);
- }
- }
- if (LaravelLocalization::getCurrentLocale() == 'en') {
- $interest = "If you're interest with this product, please click the button below";
- $detail['en'] = $request->fullUrl();
- $detail['id'] = str_replace('/en/insurance/', '/insurance/', $request->fullUrl());
- /*
- $detail['product']['premi'] = str_replace('Rp.','Rp.',$detail['product']['premi']);
- if(isset($detail['product']['semester'])){
- if($detail['product']['semester'] != ''){
- $detail['product']['semester'] = str_replace('Rp.','Rp.',$detail['product']['semester']);
- }
- }
- if(isset($detail['product']['quartal'])){
- if($detail['product']['quartal'] != ''){
- $detail['product']['quartal'] = str_replace('Rp.','Rp.',$detail['product']['quartal']);
- }
- }
- if(isset($detail['product']['monthly'])){
- if($detail['product']['monthly'] != ''){
- $detail['product']['monthly'] = str_replace('Rp.','Rp.',$detail['product']['monthly']);
- }
- }*/
- } else {
- $interest = "Jika Anda tertarik dengan produk ini, silahkan klik tombol di bawah ini";
- $detail['en'] = str_replace('/insurance/', '/en/insurance/', $request->fullUrl());
- $detail['id'] = $request->fullUrl();
- }
- $detail['share_url'] = $request->fullUrl();
- $detail['slugProduct'] = $slugProduct;
- $detail['slugProvider'] = $slug;
- if($detail['product']['type']=='car' || $detail['product']['type']=='motorcycle'){
- $tmp_vehicle_price = $detail['product']['transparency']['value_price'];
- $tmp_trans = $detail['product']['transparency'];
- unset($detail['product']['transparency']);
- $detail['product']['transparency'] = ['vehicle_price' => $tmp_vehicle_price];
- $detail['product']['transparency'] = array_merge($detail['product']['transparency'], $tmp_trans);
- unset($detail['product']['transparency']['value_price']);
- } elseif($detail['product']['type']=='property'){
- $tmp_prop_price = null;
- if(isset($detail['product']['transparency']['value_price'])){
- $tmp_prop_price = $detail['product']['transparency']['value_price'];
- }
- $tmp_trans = $detail['product']['transparency'];
- unset($detail['product']['transparency']);
- if(!is_null($tmp_prop_price)){
- $detail['product']['transparency'] = ['property_price' => $tmp_prop_price];
- $detail['product']['transparency'] = array_merge($detail['product']['transparency'], $tmp_trans);
- } else {
- $detail['product']['transparency'] = $tmp_trans;
- }
- unset($detail['product']['transparency']['value_price']);
- }
- $detail['interested'] = $interest;
- return view('front.search.detail', $detail);
- }
- public function bacaKweri(Request $request, $exclude = null, FeatureController $featureController, AdditionalController $additionalController, RateController $rateController){
- $type = $request['type'];
- if($type != 'personal-accident'){
- return $this->getSearchQuery($request, $exclude = null, $featureController, $additionalController, $rateController);
- }
- else {
- $bagian_1 = $this->getSearchQuery($request, $exclude = null, $featureController, $additionalController, $rateController);
- // problemnya adalah ketika getSearch query dilempar maka semua data dengan jumlah minimal 3 akan dihitung.
- // yang perlu dilakukan adalah dengan meng-group-by product_id yang sama.
- $dd_tmp_1 = [];
- $temp_bag_1 = collect($bagian_1)->groupBy('product_id')->toArray();
- foreach($temp_bag_1 as $key => $val){
- $dd_tmp_1[] = $val[0];
- }
- $bagian_1 = $dd_tmp_1;
- $request['type'] = 'personal-accident-age';
- $bagian_2 = $this->getSearchQuery($request, $exclude = null, $featureController, $additionalController, $rateController);
- // problemnya adalah ketika getSearch query dilempar maka semua data dengan jumlah minimal 3 akan dihitung.
- // yang perlu dilakukan adalah dengan meng-group-by product_id yang sama.
- $dd_tmp_2 = [];
- $temp_bag_2 = collect($bagian_2)->groupBy('product_id')->toArray();
- foreach($temp_bag_2 as $key => $val){
- $dd_tmp_2[] = $val[0];
- }
- $bagian_2 = $dd_tmp_2;
- $gabungke = $bagian_1;
- foreach($bagian_2 as $bag_k => $bag_v){
- $gabungke[] = $bag_v;
- }
- $gabungke = collect($gabungke);
- /*
- switch ($factor) {
- case 'price':
- $sql .= '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 ';
- break;
- case 'feature':
- $sql .= 'ORDER BY features.count DESC, features.total_amount_of_features DESC, premi ASC , product.rates DESC, (product.promo > "") DESC, product.viewers DESC,product.id DESC ';
- break;
- case 'expensive':
- $sql .= 'ORDER BY premi DESC, features.count ASC,features.total_amount_of_features ASC, product.rates ASC, (product.promo > "") DESC, product.viewers ASC, product.id DESC ';
- break;
- case 'rates':
- $sql .= 'ORDER BY product.rates DESC ';
- break;
- case 'cheapest':
- $sql .= '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 ';
- break;
- case 'average':
- $sql .= 'ORDER BY product.promo DESC, product.rates DESC, product.viewers DESC, users.last_login DESC';
- break;
- }
- } elseif (isset($request['offset']) and $request['offset']) {
- $sql .= 'LIMIT ' . $request['offset'] . ',10';
- * */
- // if(isset($))
- if($request['factor']=='cheapest' || $request['factor']=='price'){
- $hasile = $gabungke->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc('rates')->sortByDesc('total_amont_of_features')->sortByDesc('count')->sortBy('int_premi');
- $hasile = $hasile->values()->toArray();
- } elseif($request['factor']=='feature') {
- $hasile = $gabungke->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc('rates')->sortBy('premi')->sortByDesc('total_amont_of_features')->sortByDesc('count');
- $hasile = $hasile->values()->toArray();
- } elseif($request['factor']=='expensive') {
- $hasile = $gabungke->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc('rates')->sortByDesc('total_amont_of_features')->sortByDesc('count')->sortByDesc('int_premi');
- $hasile = $hasile->values()->toArray();
- } else {
- $hasile = $gabungke->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc('rates');
- $hasile = $hasile->values()->toArray();
- $sum = 0;
- $count = count($hasile);
- foreach ($hasile as $k => $v) {
- $sum += $v->int_premi;
- }
- $avg = ($count < 1 ? 0 : $sum / $count); // fix division by zero
- // add 10% to $avg
- $avg *= (1 + 50 / 100);
- // foreach
- foreach ($hasile as $k => $r) {
- if (!is_string($r)) {
- // create score object
- if ($r->int_premi > $avg) {
- unset($hasile[$k]); // unset the product with premi above average
- continue;
- }
- $counter = ($r->count < 1 ? 1 : $r->count);
- $r->score = (int)($r->int_premi / $counter);
- $r->avg = $avg;
- $r->score = (int)($r->int_premi / $counter);
- $r->last_login = Insurance_Provider::where('id', $r->provider_id)->with([
- 'users.user' => function ($q) {
- return $q->orderBy('last_login', 'DESC');
- },
- ])->first()->last_login;
- // YANG GAK DIPAKE GAK PERLU DI DECLARE ULANG
- /*
- $r->int_premi = $r->premi;
- $r->premi = rupiah($r->premi);
- $r->claim = rupiah($r->claim);
- $provider_slug = Insurance_Provider::find($r->provider_id)->slug;
- $url = url('/insurance') . '/' . $provider_slug . '/product/' . $r->slug . '';
- $r->partner = InsuranceProductPartner::where('product_id', $r->product_id)->count();
- $r->url = $url;
- $r->questionnaire = $setcook;
- $r->bypass = encrypt($setcook);
- */
- }
- }
- // udah muncul
- $udah = (array) json_decode($request['ids']);
- // konversi
- $uKeys = array_keys($udah);
- $uValues = array_values($udah);
- $uIntKeys = array_map('intval', $uKeys);
- $udah = array_combine($uIntKeys, $uValues);
- // done
- $excl = [];
- foreach($udah as $k=>$v){
- if($v>1){
- $excl[] = $k;
- }
- }
- $collection = collect($hasile);
- // sortby collection caranya terbalik dari eloquent, kalau disini dia dari kanan ke kiri
- $lastavg = clone $collection; // clone the object for last avg
- // if excl are more than 1, AND THERE IS OTHER RESULT then it must have whereNot In
- if(count($excl)>0){
- /** $try = array_slice($collection->whereNotIn('product_id', $excl)->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $try = array_slice($collection->whereNotIn('product_id', $excl)->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- if(count($try)>0){
- $x = $try;
- }else{
- /** $x = array_slice($collection->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $x = array_slice($collection->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- }
- }else{
- /** $x = array_slice($collection->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortBy('score')->values()->all(), 0, 2); **/
- $x = array_slice($collection->sortByMulti([
- 'score' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,2);
- }
- // not include
- // first increment to "udah" array
- $newUdah = [];
- foreach ($x as $item) {
- foreach($udah as $kUdah=>$vUdah){
- if($kUdah==$item->product_id){
- $udah[$item->product_id]++;
- }else{
- // if the product are never shown on cheapest and MF, assign it into newUdah
- if(!isset($udah[$item->product_id])){
- $newUdah[$item->product_id] = 1;
- }
- }
- }
- }
- // now we create the second exclude
- $notIn = [];
- // from newUdah
- foreach($newUdah as $k=>$v){
- $notIn[] = $k;
- }
- // and from udah, but if the product already showing twice
- foreach($udah as $k=>$v){
- if($v>1){
- $notIn[] = $k;
- }
- }
- // then prepare
- $y = array_slice($lastavg->whereNotIn('product_id', $notIn)->sortByMulti([
- 'score' => 'ASC',
- 'int_premi' => 'ASC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC'
- ])->values()->all(),0,1);
- // if $y ( most value ) are below 1 then use the cheapes > mf > follow by others
- if(count($y)<1){
- /** $y = array_slice($lastavg->sortByDesc('product_id')->sortByDesc('viewers')->sortByDesc(function($arr,$k){
- return !empty($arr->promo);
- })->sortByDesc('rates')->sortByDesc('total_amount_of_features')->sortByDesc('count')->sortBy('int_premi')->values()->all(), 0, 1); **/
- $y = array_slice($lastavg->sortByMulti([
- 'int_premi' => 'ASC',
- 'count' => 'DESC',
- 'total_amount_of_features' => 'DESC',
- 'rates' => 'DESC',
- 'promo_bool' => 'DESC',
- 'viewers' => 'DESC',
- 'product_id' => 'DESC',
- ])->values()->all(),0,1);
- }
- // fill $x with the same product if there is only one
- // kage bushin no jutsu
- if (count($x) == 1) {
- $tmp_x = $x[0];
- array_push($x, $tmp_x);
- //$x[] = $tmp_x;
- }
- return array_merge($x, $y);
- //return $twoavg;
- }
- if($request['factor'] != 'average' and ($request['factor'] == 'price' or $request['factor'] == 'feature')){
- if(isset($hasile[0])){
- return array($hasile[0]);
- } else {
- return [];
- }
- } elseif(isset($request['offset']) and $request['offset']){
- $tmp = [];
- $akhiran = count($hasile);
- if($akhiran >= 10){
- $awal = (integer)$request['offset'];
- $akhiran = ($awal + 10);
- for($ix = $awal; $ix < $akhiran; $ix++){
- $tmp[] = $hasile[$ix];
- }
- } else {
- $tmp['limit'] = 'yes';
- }
- return $tmp;
- } elseif($request['factor'] == 'cheapest' or $request['factor'] == 'expensive') {
- $awal = (integer)$request['offset'];
- $akhiran = count($hasile);
- if($akhiran > 10){
- $akhiran = 10;
- }
- $tmp = [];
- for($ix = $awal; $ix < $akhiran; $ix++){
- if($hasile[$ix]!='yes'){
- $tmp[] = $hasile[$ix];
- }
- }
- return $tmp;
- }
- }
- }
- public function getDetailProduct($slug, $slugProduct, $bypass, $ip, $insurance_Product_View, $additional_controller, $rate_controller, $feature_controller, $life_count = 'yearly')
- {
- // get provider information
- $provider = Insurance_Provider::where('slug', $slug)->first()->toArray();
- // feature and benefit relation call conditional
- $products = Insurance_Product::where('slug', $slugProduct)->where('provider_id', $provider['id'])->first();
- $type = $products->type;
- $prefix = str_replace('-', '_', $type);
- $prefix = str_replace('.', '_', $prefix);
- if($prefix == 'personal_accident_age'){
- $prefix = 'personal_accident';
- }
- $questionnaire = 'quest_' . $prefix;
- $feature = 'feature_' . $prefix;
- $additional = 'additional_' . $prefix;
- $setcook = '';
- // end
- $wherein = array();
- if (isset($bypass) && !empty($bypass)) {
- $setcook = decrypt($bypass);
- }
- if ($type == 'car' || $type == 'motorcycle') {
- $benefit = 'benefit_' . $prefix;
- $coverage = 'coverage_' . $prefix . '_' . $setcook['level_of_insurance'];
- $product = Insurance_Product::with($questionnaire, $benefit, $feature, $coverage, $additional, 'insurance_product_partners','features_additional_prices')
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- $product['additional'] = $product['additional_' . $prefix];
- $product['additional'] = array();
- } else if ($type == 'property') {
- $benefit = 'benefit_' . $prefix;
- $coverage = 'coverage_' . $prefix . '_' . $setcook['level_of_insurance'];
- if ($setcook['level_of_insurance'] == 'all_risk') {
- $coverage = 'coverage_' . $prefix . '_all';
- }
- if($setcook['level_of_insurance'] != 'earthquake'){
- $product = Insurance_Product::with($questionnaire, $benefit, $feature, $additional, 'insurance_product_partners','features_additional_prices')
- ->with([$coverage => function ($query) use ($setcook) {
- $query->where('property', $setcook['class_property']);
- $query->limit(1);
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- $product['additional'] = array();
- } else {
- $class_property = array('below.6' =>1,
- 'in.6-18' =>2,
- 'up.to.18'=>3,
- 'park.up.to.24'=>4,
- 'less.than.3.storeys'=>5,
- 'boarding.house'=>6,
- 'floating'=>7);
- $limiting = array(1=>[1=>1,2=>2,3=>3],
- 2=>[1=>1,2=>2,3=>3],
- 3=>[1=>1,2=>2,3=>3],
- 4=>[1=>1,2=>2,3=>3],
- 5=>[1=>1,2=>2,3=>2],
- 6=>[1=>1,2=>2,3=>2],
- 7=>[1=>1,2=>2,3=>2]);
- //$setcook['construction_property'] = $limiting[$class_property[$setcook['class_property']]][$setcook['construction_property']];
- $product = Insurance_Product::with($questionnaire, $benefit, $feature, $additional, 'insurance_product_partners','features_additional_prices')
- ->with([$coverage => function ($query) use ($setcook,$class_property) {
- $query->where('class', 1);
- $query->where('category', $setcook['construction_property']);
- $query->limit(1);
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- $product['additional'] = array();
- }
- } elseif ($type == 'personal.accident' or $type == 'personal.accident.age') {
- if($type == 'personal.accident'){
- $coverage = 'coverage_self_accident';
- $product = Insurance_Product::with($questionnaire, $feature, $coverage, 'coverage_self_accident_other', 'insurance_product_partners')
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- }elseif($type == 'personal.accident.age'){
- $now = date_create(date('Y-m-d'));
- $coverage = 'coverage_self_accident_age_basic';
- $product = Insurance_Product::with($questionnaire, $feature, 'insurance_product_partners')
- ->with(['coverage_self_accident_age_basic' => function ($query) use ($setcook, $now) {
- if ($setcook['type_of_cover'] == 'couple') {
- $guy = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_guy']))));
- $guy = date_diff($now, $guy)->y;
- $query->where('age_min', '<=', $guy);
- $query->where('limitation', '>=', $guy);
- } elseif ($setcook['type_of_cover'] == 'family') {
- $husband = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_husband']))));
- $husband = date_diff($now, $husband)->y;
- $query->where('age_min', '<=', $husband);
- $query->where('limitation', '>=', $husband);
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['single_date']))));
- $birth = date_diff($now, $birth)->y;
- $query->where('age_min', '<=', $birth);
- $query->where('limitation', '>=', $birth);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);}
- ])
- ->with(['coverage_self_accident_age_couple' => function ($query) use ($setcook, $now) {
- if ($setcook['type_of_cover'] == 'couple') {
- $girl = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_girl']))));
- $girl = date_diff($now, $girl)->y;
- $query->where('age_min', '<=', $girl);
- $query->where('limitation', '>=', $girl);
- } elseif ($setcook['type_of_cover'] == 'family') {
- $wife = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_wife']))));
- $wife = date_diff($now, $wife)->y;
- $query->where('age_min', '<=', $wife);
- $query->where('limitation', '>=', $wife);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()
- ->toArray();
- }
- } elseif ($type == 'travel') {
- $arrival = date_create(str_replace('/', '-', $setcook['arrival_date']));
- $departure = date_create(str_replace('/', '-', $setcook['departure_date']));
- $date_diff = (integer)date_diff($arrival, $departure)->format('%a') + 1;
- $product = Insurance_Product::with($questionnaire, $feature, 'insurance_product_partners')
- ->with(['coverage_travel_domestic' => function ($query) use ($setcook, $date_diff) {
- $query->where('days_min', '<=', $date_diff);
- $query->orderBy('days_min', 'DESC');
- $query->limit(1);
- }])
- ->with(['coverage_travel_asia' => function ($query) use ($setcook, $date_diff) {
- $query->where('days_min', '<=', $date_diff);
- $query->orderBy('days_min', 'DESC');
- $query->limit(1);
- }])
- ->with(['coverage_travel_asean' => function ($query) use ($setcook, $date_diff) {
- $query->where('days_min', '<=', $date_diff);
- $query->orderBy('days_min', 'DESC');
- $query->limit(1);
- }])
- ->with(['coverage_travel_worldwide_exclude_shenzeng' => function ($query) use ($setcook, $date_diff) {
- $query->where('days_min', '<=', $date_diff);
- $query->orderBy('days_min', 'DESC');
- $query->limit(1);
- }])
- ->with(['coverage_travel_worldwide_include_shenzeng' => function ($query) use ($setcook, $date_diff) {
- $query->where('days_min', '<=', $date_diff);
- $query->orderBy('days_min', 'DESC');
- $query->limit(1);
- }])
- ->with(['coverage_travel_annual' => function ($query) use ($setcook) {
- $query->where('type', '<=', $setcook['travel_category']);
- $query->limit(1);
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- } elseif ($type == 'health') {
- $now = date_create(date('Y-m-d'));
- $coverage = 'coverage_health';
- $product = Insurance_Product::with($questionnaire, $feature, 'insurance_product_partners')
- ->with([
- $coverage => function ($query) use ($setcook, $now) {
- if ($setcook['who_need_the_cover'] == 'couple') {
- $guy = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_guy']))));
- $guy = date_diff($now, $guy)->y;
- $query->where('age_min', '<=', $guy);
- $query->where('limitation', '>=', $guy);
- } elseif ($setcook['who_need_the_cover'] == 'family') {
- $husband = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_husband']))));
- $husband = date_diff($now, $husband)->y;
- $query->where('age_min', '<=', $husband);
- $query->where('limitation', '>=', $husband);
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['single_date']))));
- $birth = date_diff($now, $birth)->y;
- $query->where('age_min', '<=', $birth);
- $query->where('limitation', '>=', $birth);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);
- }
- ])
- ->with(['coverage_health_others' => function ($query) use ($setcook, $now) {
- if ($setcook['who_need_the_cover'] == 'couple') {
- $girl = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_girl']))));
- $girl = date_diff($now, $girl)->y;
- $query->where('age_min', '<=', $girl);
- $query->where('limitation', '>=', $girl);
- } elseif ($setcook['who_need_the_cover'] == 'family') {
- $wife = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_wife']))));
- $wife = date_diff($now, $wife)->y;
- $query->where('age_min', '<=', $wife);
- $query->where('limitation', '>=', $wife);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);
- }])
- ->with(['coverage_health_childrens' => function ($query) use ($setcook, $now) {
- if ($setcook['who_need_the_cover'] == 'family') {
- $children = array(1, 2, 3, 4);
- foreach ($children as $kid) {
- if (isset($setcook['family_children_' . $kid])) {
- $lato = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_children_' . $kid]))));
- $births['kid_' . $kid] = date_diff($now, $lato)->y;
- }
- }
- foreach ($births as $role => $fam) {
- $query->orWhere('age_min', '<=', $fam);
- }
- foreach ($births as $role => $fam) {
- $query->where('limitation', '>=', $fam);
- }
- }
- $query->orderBy('age_min', 'desc');
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()->toArray();
- } elseif ($type == 'life') {
- $now = date_create(date('Y-m-d'));
- $coverage = 'coverage_life_' . $setcook['type_of_product'];
- $product = Insurance_Product::with($questionnaire, $feature, 'insurance_product_partners')
- ->with([
- $coverage => function ($query) use ($setcook, $now) {
- if ($setcook['how_many_people_insured'] == 'couple') {
- $guy = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_guy']))));
- $guy = date_diff($now, $guy)->y;
- $query->where('age_min', '<=', $guy);
- $query->where('limitation', '>=', $guy);
- } elseif ($setcook['how_many_people_insured'] == 'family') {
- $husband = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_husband']))));
- $husband = date_diff($now, $husband)->y;
- $query->where('age_min', '<=', $husband);
- $query->where('limitation', '>=', $husband);
- } else {
- $birth = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['single_date']))));
- $birth = date_diff($now, $birth)->y;
- $query->where('age_min', '<=', $birth);
- $query->where('limitation', '>=', $birth);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);
- }
- ])
- ->with(['coverage_life_others' => function ($query) use ($setcook, $now) {
- if ($setcook['how_many_people_insured'] == 'couple') {
- $girl = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['couple_girl']))));
- $girl = date_diff($now, $girl)->y;
- $query->where('age_min', '<=', $girl);
- $query->where('limitation', '>=', $girl);
- } elseif ($setcook['how_many_people_insured'] == 'family') {
- $wife = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_wife']))));
- $wife = date_diff($now, $wife)->y;
- $query->where('age_min', '<=', $wife);
- $query->where('limitation', '>=', $wife);
- }
- $query->orderBy('age_min', 'desc');
- $query->limit(1);
- }])
- ->with(['coverage_life_childrens' => function ($query) use ($setcook, $now) {
- if ($setcook['how_many_people_insured'] == 'family') {
- $children = array(1, 2, 3, 4);
- foreach ($children as $kid) {
- if (isset($setcook['family_children_' . $kid])) {
- $lato = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_children_' . $kid]))));
- $births['kid_' . $kid] = date_diff($now, $lato)->y;
- }
- }
- foreach ($births as $role => $fam) {
- $query->orWhere('age_min', '<=', $fam);
- }
- foreach ($births as $role => $fam) {
- $query->where('limitation', '>=', $fam);
- }
- }
- $query->orderBy('age_min', 'desc');
- }])
- ->where('slug', $slugProduct)
- ->where('provider_id', $provider['id'])
- ->first()
- ->toArray();
- }
- // dd($product);
- if ((int)$product['premi'] < 1) {
- $condition = [];
- $formulation = [];
- if ($type == 'car') {
- $zone_tan = [1 => 'one', 2 => 'two', 3 => 'three'];
- $zone = Zone_Territory_Car::where('province_id', $setcook['province'])->first()->toArray();
- if ($setcook['level_of_insurance'] == 'comprehensive') {
- $rates = $rate_controller->comprehensive['car'];
- } else {
- $rates = $rate_controller->total_loss_only['car'];
- }
- $formulation = array(
- 'degradation_rate' => $product['quest_car']['degradation_rate'],
- 'additional_rate' => $product['quest_car']['additional_rate'],
- 'car_year' => $setcook['car_year'],
- 'additional' => $additional_controller,
- );
- if ($product['quest_car']['degradation_year_condition'] < (date('Y') - $formulation['car_year'])) {
- $now_value = $setcook['car_value'] - ($setcook['car_value'] / 100) * $formulation['degradation_rate'];
- } else {
- $now_value = $setcook['car_value'];
- }
- foreach ($product['coverage_car_' . $setcook['level_of_insurance']] as $key => $value) {
- if ($value['up_to'] > 0) {
- if ($value['up_to'] >= $now_value) {
- $formulation['territory_one_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][$key]['territory_one_rate'];
- $formulation['territory_two_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][$key]['territory_two_rate'];
- $formulation['territory_three_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][$key]['territory_three_rate'];
- break;
- }
- } else {
- $formulation['territory_one_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][4]['territory_one_rate'];
- $formulation['territory_two_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][4]['territory_two_rate'];
- $formulation['territory_three_rate'] = $product['coverage_car_' . $setcook['level_of_insurance']][4]['territory_three_rate'];
- }
- }
- $condition = array(
- 'rates_condition' => $rates,
- 'degradation_year_condition' => $product['quest_car']['degradation_year_condition'],
- 'additional_year_condition' => $product['quest_car']['additional_year_condition'],
- 'zone_id' => $zone['zone_id'],
- 'product_id' => $product['id'],
- 'level' => $setcook['level_of_insurance'],
- );
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if (isset($setcook['addition'])) {
- $condition['additional'] = $setcook['addition'];
- }
- $formulation['admin_fee'] = $product['quest_car']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_car']['materai_fee'];
- $prems = $this->doCalculation($type, $setcook['car_value'], $formulation, $condition);
- $product['premi'] = rupiah($prems['premi']);
- $product['premi_num'] = $prems['premi'];
- $product['transparency'] = $prems['sorted'];
- } elseif ($type == 'motorcycle') {
- $zone_tan = [1 => 'one', 2 => 'two', 3 => 'three'];
- $zone = Zone_Territory_Motorcycle::where('province_id', $setcook['province'])->first()->toArray();
- if ($setcook['level_of_insurance'] == 'comprehensive') {
- $rates = $rate_controller->comprehensive['motorcycle'];
- } else {
- $rates = $rate_controller->total_loss_only['motorcycle'];
- }
- $formulation = array(
- 'degradation_rate' => $product['quest_motorcycle']['degradation_rate'],
- 'additional_rate' => $product['quest_motorcycle']['additional_rate'],
- 'motorcycle_year' => $setcook['motorcycle_year'],
- 'territory_one_rate' => $product['coverage_motorcycle_' . $setcook['level_of_insurance']][0]['territory_one_rate'],
- 'territory_two_rate' => $product['coverage_motorcycle_' . $setcook['level_of_insurance']][0]['territory_two_rate'],
- 'territory_three_rate' => $product['coverage_motorcycle_' . $setcook['level_of_insurance']][0]['territory_three_rate'],
- 'additional' => $additional_controller,
- //'territory_custom_rate' => $product[ 'coverage_motorcycle_' . $setcook['level_of_insurance'] ][0]['territory_custom_rate'],
- );
- $condition = array(
- 'rates_condition' => $rates,
- 'degradation_year_condition' => $product['quest_motorcycle']['degradation_year_condition'],
- 'additional_year_condition' => $product['quest_motorcycle']['additional_year_condition'],
- 'zone_id' => $zone['zone_id'],
- 'product_id' => $product['id'],
- 'level' => $setcook['level_of_insurance'],
- );
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if (isset($setcook['addition'])) {
- $condition['additional'] = $setcook['addition'];
- }
- /*
- if(count($product[ 'additional_' . $prefix ])>0){
- foreach ( $product[ 'additional_' . $prefix ] as $label => $value ) {
- if ( strpos( $label, 'earthquake_with_tsunami' ) !== false ) {
- if ( $label == 'earthquake_with_tsunami_zone_' . $zone_tan[ $condition['zone_id'] ] and ! is_null( $value ) ) {
- $product['additional']['earthquake_with_tsunami'] = array(
- 'value' => (float) $value,
- 'type' => $additional_controller['vehicle']['earthquake_with_tsunami']
- );
- } elseif ( $label == 'earthquake_with_tsunami_zone_custom' and ! is_null( $value ) ) {
- $product['additional']['earthquake_with_tsunami'] = array(
- 'value' => (float) $value,
- 'type' => $additional_controller['vehicle']['earthquake_with_tsunami']
- );
- }
- }
- if ( strpos( $label, 'earthquake_with_tsunami' ) !== false ) {
- if ( $label == 'earthquake_with_tsunami_zone_' . $zone_tan[ $condition['zone_id'] ] and ! is_null( $value ) ) {
- $product['additional']['earthquake_with_tsunami'] = array(
- 'value' => (float) $value,
- 'type' => $additional_controller['vehicle']['earthquake_with_tsunami']
- );
- } elseif ( $label == 'earthquake_with_tsunami_custom' and ! is_null( $value ) ) {
- $product['additional']['earthquake_with_tsunami'] = array(
- 'value' => (float) $value,
- 'type' => $additional_controller['vehicle']['earthquake_with_tsunami']
- );
- }
- }
- /*
- if ( ( strpos( $label, 'earthquake_with_tsunami' ) === false and strpos( $label, 'earthquake_with_tsunami' ) === false ) and $value != 0 ) {
- if ( isset( $additional_controller['vehicle'][ $label ] ) ) {
- $product['additional'][ $label ] = array(
- 'value' => $value,
- 'type' => $additional_controller['vehicle'][ $label ]
- );
- }
- }*/
- $formulation['admin_fee'] = $product['quest_motorcycle']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_motorcycle']['materai_fee'];
- $prems = $this->doCalculation($type, $setcook['motorcycle_value'], $formulation, $condition);
- $product['premi'] = rupiah($prems['premi']);
- $product['premi_num'] = $prems['premi'];
- $product['transparency'] = $prems['sorted'];
- } elseif ($type == 'property') {
- if ($setcook['level_of_insurance'] == 'standard') {
- $rates = $rate_controller->standard['property'];
- $rates = $rates[$product['coverage_property_standard'][0]['category']];
- } elseif ($setcook['level_of_insurance'] == 'earthquake') {
- $rates = $rate_controller->earthquake['property'];
- $zone = Zone_Territory_Earthquake::where('city_id', $setcook['city'])->first()->toArray();
- } elseif ($setcook['level_of_insurance'] == 'all_risk') {
- $rates = $rate_controller->all['property'];
- $rates = $rates[$product['coverage_property_all'][0]['category']];
- }
- $formulation = array(
- 'additional' => $additional_controller,
- );
- // jika dia perhitungannya cuma content
- if ($setcook['type_of_insurance'] == 'contents.only') {
- $counting = $setcook['estimated_value_of_the_contents_to_cover'];
- } else {
- $counting = $setcook['estimated_value_of_the_property_to_cover'];
- }
- if ($product['type_of_insurance'] == 'all_risk') {
- $cons = array('1' => 'class_one_rate', '2' => 'class_two_rate', '3' => 'class_three_rate');
- $cns = $cons[$setcook['construction_property']];
- foreach ($product['coverage_property_all'] as $key => $value) {
- if ($value['property'] == $setcook['class_property']) {
- if (!is_null($product['coverage_property_all'][$key]['class_custom_rate']) and $product['coverage_property_all'][$key]['class_custom_rate'] != '') {
- $formulation['custom_rate'] = $product['coverage_property_all'][$key]['class_custom_rate'];
- } else {
- if ($product['coverage_property_all'][0][$cns] == 'top' or $product['coverage_property_all'][0][$cns] == 'bottom') {
- $formulation['custom_rate'] = $rates[$setcook['construction_property']][$product['coverage_property_all'][0][$cns]];
- } else {
- $formulation['custom_rate'] = (float)$product['coverage_property_all'][0][$cns];
- }
- }
- }
- }
- } elseif ($product['type_of_insurance'] == 'standard') {
- $cons = array('1' => 'class_one_rate', '2' => 'class_two_rate', '3' => 'class_three_rate');
- $cns = $cons[$setcook['construction_property']];
- foreach ($product['coverage_property_standard'] as $key => $value) {
- if ($value['property'] == $setcook['class_property']) {
- if (!is_null($product['coverage_property_standard'][$key]['class_custom_rate']) and $product['coverage_property_standard'][$key]['class_custom_rate'] != '') {
- $formulation['custom_rate'] = $product['coverage_property_standard'][$key]['class_custom_rate'];
- } else {
- if ($product['coverage_property_standard'][0][$cns] == 'top' or $product['coverage_property_standard'][0][$cns] == 'bottom') {
- $formulation['custom_rate'] = $rates[$setcook['construction_property']][$product['coverage_property_standard'][0][$cns]];
- } else {
- $formulation['custom_rate'] = (float)$product['coverage_property_standard'][0][$cns];
- }
- }
- }
- }
- } else {
- $cons = array('1' => 'zone_one_rate', '2' => 'zone_two_rate', '3' => 'zone_three_rate', 4 => 'zone_four_rate', 5=>'zone_five_rate');
- $cns = $cons[$zone['zone_id']];
- $class_property = array('below.6' =>1,
- 'in.6-18' =>2,
- 'up.to.18'=>3,
- 'park.up.to.24'=>4,
- 'less.than.3.storeys'=>5,
- 'boarding.house'=>6,
- 'floating'=>7);
- $csp = $setcook['construction_property'];
- //dd(['class' => $csp, 'cate' => $setcook['construction_property'], 'product' => $product['coverage_property_earthquake']]);
- foreach ($product['coverage_property_earthquake'] as $key => $value) {
- if ($value['category'] == $csp and $value['class']==1) {
- if (!is_null($product['coverage_property_earthquake'][$key]['zone_custom_rate']) and (integer)$product['coverage_property_earthquake'][$key]['zone_custom_rate'] > 0) {
- $formulation['custom_rate'] = $product['coverage_property_standard'][$key]['zone_custom_rate'];
- } else {
- if ($product['coverage_property_earthquake'][0][$cns] == 'fixed') {
- $formulation['custom_rate'] = $rates[$csp][1][$zone['zone_id']][$product['coverage_property_earthquake'][0][$cns]];
- } else {
- $formulation['custom_rate'] = (float)$product['coverage_property_earthquake'][0][$cns];
- }
- }
- }
- }
- // dd($formulation);
- }
- if (isset($product['additional_' . $prefix]) and is_array(isset($product['additional_' . $prefix]))) {
- foreach ($product['additional_' . $prefix] as $label => $value) {
- if ($setcook['province'] == '3' or $setcook['province'] == '6' or $setcook['province'] == '9') {
- $product['additional']['around_jakarta_zone_one'] = array(
- 'value' => $product['additional_' . $prefix]['around_jakarta_zone_one'],
- 'type' => $additional_controller['property']['around_jakarta_zone_one']
- );
- } else {
- $product['additional']['outside_around_jakarta_zone_one'] = array(
- 'value' => $product['additional_' . $prefix]['outside_around_jakarta_zone_one'],
- 'type' => $additional_controller['property']['outside_around_jakarta_zone_one']
- );
- $product['additional']['outside_around_jakarta_zone_two'] = array(
- 'value' => $product['additional_' . $prefix]['outside_around_jakarta_zone_two'],
- 'type' => $additional_controller['property']['outside_around_jakarta_zone_two']
- );
- }
- if (strpos($label, 'outside_around_jakarta_zone') === false and strpos($label, 'around_jakarta_zone') === false) {
- if ($value > 0 and !is_null($value)) {
- if (isset($additional_controller['property'][$label])) {
- $product['additional'][$label] = array(
- 'value' => $value,
- 'type' => $additional_controller['property'][$label]
- );
- }
- }
- }
- }
- }
- $formulation['admin_fee'] = $product['quest_property']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_property']['materai_fee'];
- $condition = array(
- 'product_id' => $product['id'],
- 'level' => $setcook['level_of_insurance'],
- 'province' => $setcook['province'],
- 'bundle_zone' => 1,
- 'rsmd_zone' => 1,
- );
- if(isset($product['coverage_property_standard'][0]['bundle_zone'])){
- $condition['bundle_zone'] = $product['coverage_property_standard'][0]['bundle_zone'];
- }
- if(isset($product['coverage_property_standard'][0]['rsmd_zone'])){
- $condition['rsmd_zone'] = $product['coverage_property_standard'][0]['rsmd_zone'];
- }
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if (isset($setcook['addition'])) {
- $condition['additional'] = $setcook['addition'];
- }
- $prems = $this->doCalculation($type, $counting, $formulation, $condition);
- $product['base_premi'] = (integer)$prems['premi'];
- $product['premi'] = rupiah($prems['premi']);
- $product['premi_num'] = $prems['premi'];
- $product['transparency'] = $prems['sorted'];
- } elseif ($type == 'personal.accident') {
- switch ($setcook['type_of_work']) {
- case 'one':
- $premis = $product['coverage_self_accident'][0]['class_one_rate'];
- break;
- case 'two':
- $premis = $product['coverage_self_accident'][0]['class_two_rate'];
- break;
- case 'three':
- $premis = $product['coverage_self_accident'][0]['class_three_rate'];
- break;
- case 'four':
- $premis = $product['coverage_self_accident'][0]['class_four_rate'];
- break;
- default:
- $premis = $product['coverage_self_accident'][0]['class_five_rate'];
- break;
- }
- if (isset($setcook['type_of_work_couple']) and $setcook['type_of_cover'] == 'couple') {
- switch ($setcook['type_of_work_couple']) {
- case 'one':
- $premis += $product['coverage_self_accident_other'][0]['class_one_rate'];
- break;
- case 'two':
- $premis += $product['coverage_self_accident_other'][0]['class_two_rate'];
- break;
- case 'three':
- $premis += $product['coverage_self_accident_other'][0]['class_three_rate'];
- break;
- case 'four':
- $premis += $product['coverage_self_accident_other'][0]['class_four_rate'];
- break;
- default:
- $premis += $product['coverage_self_accident_other'][0]['class_five_rate'];
- break;
- }
- }
- $formulation['admin_fee'] = $product['quest_personal_accident']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_personal_accident']['materai_fee'];
- $prems = $this->doCalculation($type, $premis, $formulation, $condition);
- $product['premi'] = rupiah($prems['premi']);
- $product['premi_num'] = $prems['premi'];
- $product['transparency'] = $prems['sorted'];
- } elseif ($type == 'personal.accident.age') {
- $condition = array('product_id' => $product['id']);
- $now = date_create(date('Y-m-d'));
- // $condition = array('yearly');
- // $prems = $this->doCalculation($type, 0, $formulation, $condition);
- $formulation['use'] = $life_count;
- $condition['premi']['main'] = array();
- $condition['premi']['couple'] = array();
- $condition['premi']['main']['yearly'] = 0;
- $condition['premi']['main']['semester'] = 0;
- $condition['premi']['main']['quartal'] = 0;
- $condition['premi']['main']['monthly'] = 0;
- $condition['premi']['couple']['yearly'] = [];
- $condition['premi']['couple']['semester'] = [];
- $condition['premi']['couple']['quartal'] = [];
- $condition['premi']['couple']['monthly'] = [];
- $condition['premi']['main']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['main']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['main']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['main']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if ($setcook['type_of_cover'] == 'couple') {
- foreach ($product['coverage_self_accident_age_couple'] as $k => $value) {
- $condition['premi']['couple']['yearly'][$k] = $value['yearly_rates'];
- $condition['premi']['couple']['semester'][$k] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['couple']['quartal'][$k] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['couple']['monthly'][$k] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- }
- } else {
- $condition['premi']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- }
- $formulation['admin_fee'] = $product['quest_personal_accident']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_personal_accident']['materai_fee'];
- $prems = $this->doCalculation($type, 0, $formulation, $condition);
- foreach ($condition['premi'] as $key => $val) {
- $product[$key] = rupiah($val);
- }
- $product['premi'] = rupiah($prems['premi']);
- $product['transparency'] = $prems['sorted'];
- foreach ($prems['any'] as $key => $val) {
- $product[$key] = $val;
- }
- } elseif ($type == 'travel') {
- switch ($setcook['travel_category']) {
- case 'asean':
- $premis = $product['coverage_travel_asean'][0];
- break;
- case 'asia':
- $premis = $product['coverage_travel_asia'][0];
- break;
- case 'worldwide_incl_shenzeng':
- $premis = $product['coverage_travel_worldwide_include_shenzeng'][0];
- break;
- case 'worldwide_excl_shenzeng':
- $premis = $product['coverage_travel_worldwide_exclude_shenzeng'][0];
- break;
- default:
- $premis = $product['coverage_travel_domestic'][0];
- break;
- }
- $setcook['travel_category'] = trans('questionnaire.travel.' . $setcook['travel_category']);
- $arrival = date_create(str_replace('/', '-', $setcook['arrival_date']));
- $departure = date_create(str_replace('/', '-', $setcook['departure_date']));
- $date_diff = (integer)date_diff($arrival, $departure)->format('%a') + 1;
- if ($setcook['who_is_the_insurance_for'] != 'family') {
- $people = [
- 'just.me' => 'one_person',
- 'couple' => 'two_person',
- ];
- $select = $people[$setcook['who_is_the_insurance_for']];
- $setrow = $premis[$select];
- $count = 1;
- } else {
- $counted = [3 => 'three_person', 4 => 'four_person', 5 => 'five_person'];
- $select = $counted[$setcook['group_count']];
- $setrow = $premis[$select];
- $count = 1;
- }
- $condition = [
- 'max_days' => $premis['days_min'],
- 'difference' => $date_diff - $premis['days_min'],
- 'people' => $select, 'days' => $date_diff,
- 'extra' => $premis['extra'],
- 'trip_type' => $setcook['trip_type']
- ];
- $formulation = [
- 'harga' => $count * $setrow,
- 'extra' => $premis['extra_' . $select],
- 'annual' => $product['coverage_travel_annual'][0][$select]
- ];
- if (isset($setcook['group_count'])) {
- $formulation['group_count'] = $setcook['group_count'];
- }
- $formulation['admin_fee'] = $product['quest_travel']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_travel']['materai_fee'];
- $prems = $this->doCalculation($type, $premis, $formulation, $condition);
- $product['premi'] = rupiah($prems['premi']);
- $product['premi_num'] = $prems['premi'];
- $product['transparency'] = $prems['sorted'];
- } elseif ($type == 'health') {
- $condition = array('product_id' => $product['id']);
- $now = date_create(date('Y-m-d'));
- // $condition = array('yearly');
- // $prems = $this->doCalculation($type, 0, $formulation, $condition);
- $formulation['use'] = $life_count;
- $condition['premi']['main'] = array();
- $condition['premi']['other'] = array();
- $condition['premi']['main']['yearly'] = 0;
- $condition['premi']['main']['semester'] = 0;
- $condition['premi']['main']['quartal'] = 0;
- $condition['premi']['main']['monthly'] = 0;
- $condition['premi']['other']['yearly'] = [];
- $condition['premi']['other']['semester'] = [];
- $condition['premi']['other']['quartal'] = [];
- $condition['premi']['other']['monthly'] = [];
- $condition['premi']['children']['yearly'] = [];
- $condition['premi']['children']['semester'] = [];
- $condition['premi']['children']['quartal'] = [];
- $condition['premi']['children']['monthly'] = [];
- $condition['premi']['main']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['main']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['main']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['main']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if ($setcook['who_need_the_cover'] == 'couple') {
- foreach ($product['coverage_health_others'] as $k => $value) {
- $condition['premi']['other']['yearly'][$k] = $value['yearly_rates'];
- $condition['premi']['other']['semester'][$k] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['other']['quartal'][$k] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['other']['monthly'][$k] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- }
- } elseif ($setcook['who_need_the_cover'] == 'family') {
- foreach ($product['coverage_health_others'] as $k => $value) {
- $condition['premi']['other']['yearly'][$k] = $value['yearly_rates'];
- $condition['premi']['other']['semester'][$k] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['other']['quartal'][$k] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['other']['monthly'][$k] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- }
- $births = array();
- $children = array(1, 2, 3, 4);
- foreach ($children as $kid) {
- if (isset($setcook['family_children_' . $kid])) {
- $lato = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_children_' . $kid]))));
- $births['kid_' . $kid] = date_diff($now, $lato)->y;
- $get_what['kid_' . $kid] = null;
- }
- }
- $i = 0;
- foreach ($births as $role => $fam) {
- if (is_null($get_what[$role])) {
- foreach ($product['coverage_health_childrens'] as $value) {
- if ($value['age_min'] <= $fam) {
- $condition['premi']['children']['yearly'][$i] = $value['yearly_rates'];
- $condition['premi']['children']['semester'][$i] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['children']['quartal'][$i] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['children']['monthly'][$i] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- goto adding_get_what_to_health;
- }
- }
- adding_get_what_to_health:
- $get_what[$role] = 'filled';
- $i++;
- }
- }
- } else {
- $condition['premi']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- }
- $formulation['admin_fee'] = $product['quest_health']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_health']['materai_fee'];
- $prems = $this->doCalculation($type, 0, $formulation, $condition);
- foreach ($condition['premi'] as $key => $val) {
- $product[$key] = rupiah($val);
- }
- $product['premi'] = rupiah($prems['premi']);
- $product['transparency'] = $prems['sorted'];
- foreach ($prems['any'] as $key => $val) {
- $product[$key] = $val;
- }
- } elseif ($type == 'life') {
- $condition = array('product_id' => $product['id']);
- $now = date_create(date('Y-m-d'));
- // $condition = array('yearly');
- // $prems = $this->doCalculation($type, 0, $formulation, $condition);
- $formulation['use'] = $life_count;
- $condition['premi']['main'] = array();
- $condition['premi']['other'] = array();
- $condition['premi']['main']['yearly'] = 0;
- $condition['premi']['main']['semester'] = 0;
- $condition['premi']['main']['quartal'] = 0;
- $condition['premi']['main']['monthly'] = 0;
- $condition['premi']['other']['yearly'] = [];
- $condition['premi']['other']['semester'] = [];
- $condition['premi']['other']['quartal'] = [];
- $condition['premi']['other']['monthly'] = [];
- $condition['premi']['children']['yearly'] = [];
- $condition['premi']['children']['semester'] = [];
- $condition['premi']['children']['quartal'] = [];
- $condition['premi']['children']['monthly'] = [];
- $condition['premi']['main']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['main']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['main']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['main']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- if (isset($setcook['feature'])) {
- $condition['features'] = $setcook['feature'];
- }
- if ($setcook['how_many_people_insured'] == 'couple') {
- foreach ($product['coverage_life_others'] as $k => $value) {
- $condition['premi']['other']['yearly'][$k] = $value['yearly_rates'];
- $condition['premi']['other']['semester'][$k] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['other']['quartal'][$k] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['other']['monthly'][$k] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- }
- } elseif ($setcook['how_many_people_insured'] == 'family') {
- foreach ($product['coverage_life_others'] as $k => $value) {
- $condition['premi']['other']['yearly'][$k] = $value['yearly_rates'];
- $condition['premi']['other']['semester'][$k] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['other']['quartal'][$k] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['other']['monthly'][$k] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- }
- $births = array();
- $children = array(1, 2, 3, 4);
- foreach ($children as $kid) {
- if (isset($setcook['family_children_' . $kid])) {
- $lato = date_create(date('Y-m-d', strtotime(str_replace('/', '-', $setcook['family_children_' . $kid]))));
- $births['kid_' . $kid] = date_diff($now, $lato)->y;
- $get_what['kid_' . $kid] = null;
- }
- }
- $i = 0;
- foreach ($births as $role => $fam) {
- if (is_null($get_what[$role])) {
- foreach ($product['coverage_life_childrens'] as $value) {
- if ($value['age_min'] <= $fam) {
- $condition['premi']['children']['yearly'][$i] = $value['yearly_rates'];
- $condition['premi']['children']['semester'][$i] = is_null($value['semester_rates']) ? 0 : $value['semester_rates'];
- $condition['premi']['children']['quartal'][$i] = is_null($value['quartal_rates']) ? 0 : $value['quartal_rates'];
- $condition['premi']['children']['monthly'][$i] = is_null($value['monthly_rates']) ? 0 : $value['monthly_rates'];
- goto adding_get_what_to;
- }
- }
- adding_get_what_to:
- $get_what[$role] = 'filled';
- $i++;
- }
- }
- } else {
- $condition['premi']['yearly'] = $product[$coverage][0]['yearly_rates'];
- $condition['premi']['semester'] = is_null($product[$coverage][0]['semester_rates']) ? 0 : $product[$coverage][0]['semester_rates'];
- $condition['premi']['quartal'] = is_null($product[$coverage][0]['quartal_rates']) ? 0 : $product[$coverage][0]['quartal_rates'];
- $condition['premi']['monthly'] = is_null($product[$coverage][0]['monthly_rates']) ? 0 : $product[$coverage][0]['monthly_rates'];
- }
- $formulation['admin_fee'] = $product['quest_life']['admin_fee'];
- $formulation['materai_fee'] = $product['quest_life']['materai_fee'];
- $prems = $this->doCalculation($type, 0, $formulation, $condition);
- foreach ($condition['premi'] as $key => $val) {
- $product[$key] = rupiah($val);
- }
- $product['premi'] = rupiah($prems['premi']);
- $product['transparency'] = $prems['sorted'];
- foreach ($prems['any'] as $key => $val) {
- $product[$key] = $val;
- }
- }
- }
- $product_meta = Insurance_Product_Meta::where('product_id', $product['id'])->first();
- if (isset($product_meta->image) && $product_meta != '') {
- $product['img'] = $product_meta->image;
- } else {
- $product['img'] = url('/image/global/dummy-logo.jpg');
- }
- $product['share_url'] = URL::full();
- $product['testimonies'] = InsuranceProductReview::with('member')->where('product_id', $product["id"])->get()->toArray();
- $data = array(
- 'provider' => $provider,
- 'product' => $product,
- 'custom_css' => 'product_detail.css'
- );
- // prepare feature and benefit
- $_feature = $product[$feature];
- if (isset($benefit)) {
- $_benefit = $product[$benefit];
- }
- $feature = [];
- $benefit = [];
- $x = 0;
- if (sizeof($_feature) > 0):
- $dummy = json_decode($_feature['dummy_benefit'],true);
- foreach ($_feature as $k => $v) {
- if ($k != 'product_id' && $k != 'count' && $k != "sort_features" && $k != 'dummy_benefit') {
- if($v != 0 and !is_null($v)){
- $ben = null;
- if(isset($dummy[$k])){
- $ben = $dummy[$k];
- }
- $kess = str_replace("_", '.', $k);
- $feature[$x] = ['name' => trans('feature.' . $kess), 'available' => $v,'benefit' => $ben];
- $x++;
- }
- }
- }
- endif;
- /*foreach($featureController->property as $value){
- $feature->orWhere($value['name'], 1);
- }*/
- $data['product']['feature'] = $feature;
- if (isset($_benefit)) {
- if (sizeof($_benefit) > 0) :
- foreach ($_benefit as $k => $v) {
- if ($k != 'product_id') {
- $k = ___('benefit.' . $product['type'] . '.' . $k);
- $benefit[$k] = rupiah($v);
- }
- }
- endif;
- }
- $data['product']['benefit'] = $benefit;
- $lang = LaravelLocalization::getCurrentLocale();
- $data['product']['detail'] = Insurance_Product_Detail::where('product_id', $product['id'])->where('lang', $lang)->first()->toArray();
- $data['provider']['detail'] = Insurance_Provider_Detail::where('provider_id', $provider['id'])->first()->toArray();
- $data['title'] = $product['name'] . ' by ' . $provider['name'] . ' - Bandingin.com';
- if (Auth::check()) {
- $data['member'] = User::find(Auth::user()->id)->toArray();
- $data['member']['extra'] = json_decode($data['member']['data'], true);
- }
- // data configuration
- $data['configuration'] = $setcook;
- $data['product_ids'] = $products->id;
- return $data;
- }
- /**
- * @param $type
- * @param $value
- * @param null $formulation
- * @param null $condition
- *
- * @return array
- */
- public function doCalculation($type, $value, $formulation = null, $condition = null)
- {
- $base_premi = 0;
- $value = (integer)$value;
- $sorted = array();
- $features_breakdown = [];
- $additional_breakdown = [];
- $amount = 0;
- $percented = 0;
- $addons = 0;
- $any = [];
- if (isset($condition['features'])) {
- $features = Insurance_Features_Additional_Prices::where('product_id', $condition['product_id'])
- ->whereIn('key', $condition['features'])
- ->get()
- ->toArray();
- // features now based on percentages
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $amount += (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $percented += (float)$items['value'];
- }
- }
- }
- switch ($type) {
- case 'car':
- // ini gua ngambil harga yang di degradasi, belum di round ya
- // value divide by 100
- // then multiply by degradation rate
- // then now date min car year
- // the all the result will be multiply
- $sorted['value_price'] = ['message' => rupiah($value)];
- if ($condition['degradation_year_condition'] < (date('Y') - $formulation['car_year'])) {
- $degradation_premi = $value - ($value / 100) * $formulation['degradation_rate'];
- } else {
- $degradation_premi = $value;
- }
- //dd($degradation_premi);
- $sorted['degradation'] = [
- 'year' => date('Y') - $formulation['car_year'],
- 'message' => $formulation['degradation_rate'] . '%'
- ];
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($degradation_premi / 100) * (float)$items['value'];
- }
- $sorted['is_features_use_trans' . str_replace('_', '.', $items['key'])] = [
- $items['key'] => $val,
- 'message' => rupiah($val)
- ];
- }
- }
- // disini query harus ngeliat apa dia punya override atau gak, kalo emang gak, ya berarti di
- // pake dong territorial ratenya, dan juga territory diliat lagi tipe zonenya.
- // value divide by 100 then multiply by territory_rate
- // gua cek uptonya dulu ke degradation premi
- $tmp = array();
- if ($degradation_premi > 0 and $degradation_premi <= 125000000) {
- $tmp = $condition['rates_condition'][1];
- } elseif ($degradation_premi >= 125000000 and $degradation_premi < 200000000) {
- $tmp = $condition['rates_condition'][2];
- } elseif ($degradation_premi >= 200000000 and $degradation_premi < 400000000) {
- $tmp = $condition['rates_condition'][3];
- } elseif ($degradation_premi >= 400000000 and $degradation_premi < 800000000) {
- $tmp = $condition['rates_condition'][4];
- } else {
- $tmp = $condition['rates_condition'][5];
- }
- if ($condition['zone_id'] == 1) {
- if ($formulation['territory_one_rate'] == 'bottom' or $formulation['territory_one_rate'] == 'top') {
- $percentages = $tmp[1][$formulation['territory_one_rate']];
- } else {
- $percentages = (float)$formulation['territory_one_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- } else if ($condition['zone_id'] == 2) {
- if ($formulation['territory_two_rate'] == 'bottom' or $formulation['territory_two_rate'] == 'top') {
- $percentages = $tmp[2][$formulation['territory_two_rate']];
- } else {
- $percentages = (float)$formulation['territory_two_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- } else {
- if ($formulation['territory_three_rate'] == 'bottom' or $formulation['territory_three_rate'] == 'top') {
- $percentages = $tmp[3][$formulation['territory_three_rate']];
- } else {
- $percentages = (float)$formulation['territory_three_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- }
- // jadi disini gua ngubah kalkukasi harusnya itung persen dulu sebelu hitung harga.
- if ($condition['additional_year_condition'] < (date('Y') - $formulation['car_year'])) {
- $addon_percentages = ($formulation['additional_rate'] / 100);
- } else {
- $addon_percentages = 0;
- }
- $sorted['loading_percentages'] = [
- 'rate' => $addon_percentages,
- 'message' => $addon_percentages . '%'
- ];
- $calculations = $percentages + ($percentages * $addon_percentages);
- $base_premi = ($degradation_premi / 100) * $calculations;
- $sorted['base_premi'] = ['premi' => $base_premi, 'message' => rupiah($base_premi)];
- $passing = array(
- 'value' => $degradation_premi,
- 'type' => 'car',
- 'additional' => array(),
- 'level' => $condition['level'],
- 'id' => $condition['product_id'],
- 'zone' => $condition['zone_id']
- );
- // additional
- if (isset($condition['additional'])) {
- foreach ($condition['additional'] as $k => $v) {
- $passing['additional'][$k] = $v;
- }
- }
- $percented = ($degradation_premi / 100) * $percented;
- $additions = $formulation['additional']->duplicateAdditionalCalculation($passing);
- $subtotal = $base_premi + $amount + $percented;
- foreach ($additions as $k => $v) {
- $addons += $v;
- $sorted['is_additional_use_transcar.' . str_replace('_', '.', $k)] = [
- $k => $v,
- 'message' => rupiah($v)
- ];
- }
- $subtotal = $subtotal + $addons;
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'motorcycle':
- // ini gua ngambil harga yang di degradasi, belum di round ya
- // value divide by 100
- // then multiply by degradation rate
- // then now date min motorcycle year
- // the all the result will be multiply
- $sorted['value_price'] = ['message' => rupiah($value)];
- if ($condition['degradation_year_condition'] < (date('Y') - $formulation['motorcycle_year'])) {
- $degradation_premi = $value - ($value / 100) * $formulation['degradation_rate'];
- } else {
- $degradation_premi = $value;
- }
- $sorted['degradation'] = [
- 'year' => date('Y') - $formulation['motorcycle_year'],
- 'message' => rupiah($degradation_premi)
- ];
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($degradation_premi / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- // disini query harus ngeliat apa dia punya override atau gak, kalo emang gak, ya berarti di
- // pake dong territorial ratenya, dan juga territory diliat lagi tipe zonenya.
- // value divide by 100 then multiply by territory_rate
- if ($condition['zone_id'] == 1) {
- if ($formulation['territory_one_rate'] == 'bottom' or $formulation['territory_one_rate'] == 'top') {
- $percentages = $condition['rates_condition'][1][1][$formulation['territory_one_rate']];
- } else {
- $percentages = (float)$formulation['territory_one_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- } else if ($condition['zone_id'] == 2) {
- if ($formulation['territory_two_rate'] == 'bottom' or $formulation['territory_two_rate'] == 'top') {
- $percentages = $condition['rates_condition'][1][2][$formulation['territory_two_rate']];
- } else {
- $percentages = (float)$formulation['territory_two_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- } else {
- if ($formulation['territory_three_rate'] == 'bottom' or $formulation['territory_three_rate'] == 'top') {
- $percentages = $condition['rates_condition'][1][3][$formulation['territory_three_rate']];
- } else {
- $percentages = (float)$formulation['territory_three_rate'];
- }
- $sorted['premi_percentages'] = [
- 'rate' => $percentages,
- 'message' => $percentages . '%'
- ];
- }
- // jadi disini gua ngubah kalkukasi harusnya itung persen dulu sebelu hitung harga.
- if ($condition['additional_year_condition'] < (date('Y') - $formulation['motorcycle_year'])) {
- $addon_percentages = ($formulation['additional_rate'] / 100);
- } else {
- $addon_percentages = 0;
- }
- $sorted['loading_percentages'] = [
- 'rate' => $addon_percentages,
- 'message' => $addon_percentages . '%'
- ];
- $calculations = $percentages + ($percentages * $addon_percentages);
- $base_premi = ($degradation_premi / 100) * $calculations;
- $sorted['base_premi'] = ['premi' => $base_premi, 'message' => rupiah($base_premi)];
- $passing = array(
- 'value' => $degradation_premi,
- 'type' => 'motorcycle',
- 'additional' => array(),
- 'level' => $condition['level'],
- 'id' => $condition['product_id'],
- 'zone' => $condition['zone_id']
- );
- // additional
- if (isset($condition['additional'])) {
- foreach ($condition['additional'] as $k => $v) {
- $passing['additional'][$k] = $v;
- }
- }
- $percented = ($degradation_premi / 100) * $percented;
- $additions = $formulation['additional']->duplicateAdditionalCalculation($passing);
- $subtotal = $base_premi + $amount + $percented;
- foreach ($additions as $k => $v) {
- $addons += $v;
- $sorted['is_additional_use_transmotorcycle.' . str_replace('_', '.', $k)] = [
- $k => $v,
- 'message' => rupiah($v)
- ];
- }
- $subtotal = $subtotal + $addons;
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'property':
- // approachingnya sama kaya sql
- // cuma ini dipersingkat banget.
- $premi = ($value / 1000) * $formulation['custom_rate'];
- $sorted['premi'] = ['rate' => $formulation['custom_rate'], 'message' => rupiah($premi)];
- $percented = ($value / 100) * $percented;
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($premi / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $passing = array(
- 'value' => $value,
- 'type' => 'property',
- 'additional' => array(),
- 'level' => $condition['level'],
- 'province' => $condition['province'],
- 'bundle_zone' => $condition['bundle_zone'],
- 'rsmd_zone' => $condition['rsmd_zone'],
- 'id' => $condition['product_id']
- );
- // additional
- if (isset($condition['additional'])) {
- foreach ($condition['additional'] as $k => $v) {
- $passing['additional'][$k] = $v;
- }
- }
- $additions = $formulation['additional']->duplicateAdditionalCalculation($passing);
- $base_premi = $premi;
- $subtotal = $base_premi + $amount + $percented;
- foreach ($additions as $k => $v) {
- $addons += $v;
- $sorted['is_additional_use_transprop.' . str_replace('_', '.', $k)] = [
- $k => $v,
- 'message' => rupiah($v)
- ];
- }
- $subtotal = $subtotal + $addons;
- $sorted['base_premi'] = ['premi' => $base_premi, 'message' => rupiah($base_premi)];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'personal.accident':
- $premi = $value;
- $sorted['premi'] = ['premi' => $premi, 'message' => rupiah($premi)];
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($premi / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $base_premi = $premi;
- $subtotal = $base_premi + $amount + $percented;
- $sorted['base_premi'] = ['premi' => $base_premi, 'message' => rupiah($base_premi)];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'travel':
- $people = $condition['people'];
- if ($condition['trip_type'] != 'annual.trip') {
- $difference = $condition['difference'];
- if ($condition['extra'] == 'weekly') {
- $difference = ceil($condition['difference'] / 7);
- }
- $extra = $difference > 0 ? $difference * $formulation['extra'] : 0;
- $premi = $formulation['harga'] + $extra;
- } else {
- $premi = $formulation['annual'];
- }
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($premi / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $base_premi = $premi;
- $subtotal = $base_premi + $amount + $percented;
- $sorted['base_premi'] = ['premi' => $premi, 'message' => rupiah($base_premi)];
- $sorted['days'] = ['days' => $premi, 'message' => $condition['days']];
- $sorted['people'] = ['people' => $people, 'message' => $people];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'health':
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($condition['premi']['main'][$formulation['use']] / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $data = [];
- $harga = ['yearly' => 0, 'semester' => 0, 'quartal' => 0, 'monthly' => 0];
- foreach ($condition['premi']['main'] as $key => $value) {
- $harga[$key] += $value;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- foreach ($condition['premi']['other'] as $key => $value) {
- foreach ($value as $item) {
- $harga[$key] += $item;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- }
- foreach ($condition['premi']['children'] as $key => $value) {
- foreach ($value as $item) {
- $harga[$key] += $item;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- }
- $subtotal = $data[$formulation['use'] . '_subtotal'];
- $any = [
- 'semester' => $data['semester_subtotal'],
- 'quartal' => $data['quartal_subtotal'],
- 'monthly' => $data['monthly_subtotal'],
- 'yearly' => $data['yearly_subtotal']];
- $sorted['base_premi'] = ['base_premi' => $data['yearly_subtotal'], 'message' => rupiah($data['yearly_subtotal'])];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'life':
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($condition['premi']['main'][$formulation['use']] / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $data = [];
- $harga = ['yearly' => 0, 'semester' => 0, 'quartal' => 0, 'monthly' => 0];
- foreach ($condition['premi']['main'] as $key => $value) {
- $harga[$key] += $value;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- foreach ($condition['premi']['other'] as $key => $value) {
- foreach ($value as $item) {
- $harga[$key] += $item;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- }
- foreach ($condition['premi']['children'] as $key => $value) {
- foreach ($value as $item) {
- $harga[$key] += $item;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- }
- $subtotal = $data[$formulation['use'] . '_subtotal'];
- $any = [
- 'semester' => $data['semester_subtotal'],
- 'quartal' => $data['quartal_subtotal'],
- 'monthly' => $data['monthly_subtotal'],
- 'yearly' => $data['yearly_subtotal']];
- $sorted['base_premi'] = ['base_premi' => $data['yearly_subtotal'], 'message' => rupiah($data['yearly_subtotal'])];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- case 'personal.accident.age':
- // breakdown features
- // features now based on percentages
- if (isset($features) && is_array($features)) {
- foreach ($features as $items) {
- if ($items['type'] == 'amount') {
- $val = (integer)$items['value'];
- } elseif ($items['type'] == 'percentages') {
- $val = ($condition['premi']['main'][$formulation['use']] / 100) * (float)$items['value'];
- }
- $features_breakdown[] = [$items['key'] => $val];
- }
- $sorted['features_breakdown'] = $features_breakdown;
- }
- $data = [];
- $harga = ['yearly' => 0, 'semester' => 0, 'quartal' => 0, 'monthly' => 0];
- foreach ($condition['premi']['main'] as $key => $value) {
- $harga[$key] += $value;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- foreach ($condition['premi']['couple'] as $key => $value) {
- foreach ($value as $item) {
- $harga[$key] += $item;
- $data[$key . '_subtotal'] = $harga[$key] + $amount + $percented;
- }
- }
- $subtotal = $data[$formulation['use'] . '_subtotal'];
- $any = [
- 'semester' => $data['semester_subtotal'],
- 'quartal' => $data['quartal_subtotal'],
- 'monthly' => $data['monthly_subtotal'],
- 'yearly' => $data['yearly_subtotal']];
- $sorted['base_premi'] = ['base_premi' => $data['yearly_subtotal'], 'message' => rupiah($data['yearly_subtotal'])];
- $sorted['premi'] = ['premi' => $subtotal, 'message' => rupiah($subtotal)];
- break;
- }
- $sorted['subtotal_features'] = [
- 'features' => $amount + $percented,
- 'message' => rupiah($amount + $percented)
- ];
- // admin fee and materai fee
- if(!is_null($formulation['admin_fee'])){
- if ($formulation['admin_fee'] < 100) {
- $admin_fee = $subtotal / 100 * $formulation['admin_fee'];
- } else {
- $admin_fee = $formulation['admin_fee'];
- }
- } else {
- $admin_fee = 0;
- }
- if(!is_null($formulation['materai_fee'])){
- if ($formulation['materai_fee'] < 100) {
- $materai_fee = $subtotal / 100 * $formulation['materai_fee'];
- } else {
- $materai_fee = $formulation['materai_fee'];
- }
- } else {
- $materai_fee = 0;
- }
- $sorted['admin_fee'] = ['admin_fee' => $formulation['admin_fee'], 'message' => rupiah($admin_fee)];
- $sorted['materai_fee'] = ['materai_fee' => $formulation['materai_fee'], 'message' => rupiah($materai_fee)];
- $subtotal = $subtotal + $admin_fee + $materai_fee;
- if (count($any) > 0) {
- $ahny = [];
- foreach ($any as $k => $v) {
- $ahny[$k] = $v + $admin_fee + $materai_fee;
- }
- $any = $ahny;
- }
- $subtotal = round($subtotal);
- $sorted['subtotal'] = ['subtotal' => $subtotal, 'message' => rupiah($subtotal)];
- return array('premi' => $subtotal, 'sorted' => $sorted, 'any' => $any);
- }
- public function viewCompareProduct(Request $request)
- {
- $compare = array(
- $request->first,
- $request->second,
- );
- if ($request->has('third')) {
- array_push($compare, $request->third);
- }
- $title = trans('global.compareResult') . ' - ' . trans('global.title');
- $x = 0;
- $type = $request->type;
- $prefix = str_replace('-', '_', $type);
- if($prefix == 'personal_accident_age'){
- $prefix = 'personal_accident';
- }
- $questionnaire = 'quest_' . $prefix;
- $benefit = 'benefit_' . $prefix;
- $feature = 'feature_' . $prefix;
- foreach ($compare as $p) {
- if ($p != ''):
- $product[$x] = Insurance_product::with(
- $feature,
- $benefit,
- $questionnaire,
- 'logo',
- 'detail',
- 'providerdata',
- 'features_additional_prices',
- 'insurance_type_product_rels',
- 'insurance_product_questionnaire.questionnaire',
- 'insurance_product_reviews.member',
- 'insurance_product_partners', 'subscriber_rels')->where('slug', $p)->first()->toArray();
- $product = collect($product);
- $product->transform(function($item, $key){
- $type = str_replace('.','_',$item['type']);
- $prefix = $type;
- if($prefix == 'personal_accident_age'){
- $prefix = 'personal_accident';
- }
- $temp_feature = [];
- if(!is_null($item['feature_'.$prefix])){
- foreach($item['feature_'.$prefix] as $key => $val){
- $temp_feature[$key] = $val;
- if(!is_null($val)){
- if((integer)$val == 1){
- $temp_feature[$key] = 'Y';
- }
- }
- if($key == 'dummy_benefit') {
- if(!is_array($val)){
- $temp_feature[$key] = json_decode($val, true);
- }
- }
- }
- $item['feature_'.$prefix] = $temp_feature;
- if(isset($temp_feature['dummy_benefit'])){
- if(!is_null($temp_feature['dummy_benefit']) and is_array($temp_feature['dummy_benefit'])){
- if(count($temp_feature['dummy_benefit']) > 0){
- foreach($temp_feature['dummy_benefit'] as $xxk => $xxv){
- foreach($xxv as $tttype => $sub_item){
- if($tttype == 'days'){
- $item['feature_'.$prefix][$xxk] = $sub_item.$tttype;
- } else {
- $item['feature_'.$prefix][$xxk] = $sub_item;
- }
- }
- }
- }
- }
- }
- }
- /*
- if(count($item['features_additional_prices'])>0){
- foreach($item['features_additional_prices'] as $xk => $xv){
- if($xv['type']=='days'){
- $item['feature_'.$type][$xv['key']] = str_replace('.00','',$xv['value']).$xv['type'];
- } else {
- $item['feature_'.$type][$xv['key']] = $xv['value'];
- }
- }
- }*/
- return $item;
- });
- $product->toArray();
- $x++;
- endif;
- }
- $data['product'] = $product;
- $data['type'] = $type;
- $data['title'] = $title;
- $data['feature'] = $feature;
- $data['benefit'] = $benefit;
- $data['questionnaire'] = $questionnaire;
- $data['footer_js'] = ['/js/compare.js'];
- $data['en'] = str_replace('/compare', '/en/compare', $request->fullUrl());
- $data['id'] = str_replace('/en', '', $request->fullUrl());
- return view('front.search.compare', $data);
- }
- public function getProductsByType(Request $request)
- {
- $prod = Insurance_Product::where('type', $request->type)->get();
- $htm = '';
- foreach ($prod as $pr) {
- $provider = Insurance_Provider::find($pr->provider_id)->name;
- $htm .= '<option value="' . $pr->slug . '">' . $pr->name . ' - ' . $provider . '</option>';
- }
- return json_encode(['html' => $htm]);
- }
- public function getProductsBySlug(Request $request)
- {
- $slug = $request->slug;
- $prefix = $request->type;
- $datatarget = str_replace('.', '', $request->target);
- $questionnaire = 'quest_' . $prefix;
- $benefit = 'benefit_' . $prefix;
- $feature = 'feature_' . $prefix;
- $prod = Insurance_product::with(
- $feature,
- $benefit,
- $questionnaire,
- 'logo',
- 'detail',
- 'providerdata',
- 'features_additional_prices',
- 'insurance_type_product_rels',
- 'insurance_product_questionnaire.questionnaire',
- 'insurance_product_reviews.member',
- 'insurance_product_partners', 'subscriber_rels')->where('slug', $slug)->first()->toArray();
- $prod['features'] = $prod[$feature];
- foreach ($prod[$benefit] as $k => $v) {
- $ben[$k] = rupiah($v);
- }
- $prod['benefits'] = $ben;
- $prod['thead'] = '<span data-target="' . $datatarget . '" class="compare-remove"><i class="fa fa-remove"></i></span><div class="clearfix"></div><img width="96" src="' . $prod["logo"]["image"] . '" class="img-responsive center product-image"><h4 class="text-center product-name">' . $prod["name"] . '</h4>';
- return json_encode(['product' => $prod]);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement