', '']; $map[] = ['is_appraisal', '<>', 0]; $map[] = ['appraisal_date', 'between time', ['2018-06-01 00:00:00', '2018-08-01 00:00:00']]; // $map[] = ['id', '=', '2020051801096']; $count = Db::name('old_appraisal')->where($map)->count(); for ($i=0;$iwhere($map) ->order('appraisal_date asc') ->limit($i * 10, 10) ->select(); Db::startTrans(); foreach ($inquiry_data as $key=>$value) { // 询价单号 $order_no = 'PGZZ'.$value['id']; // 旧系统询价详情数据 $inquiry_detail_data = Db::name('old_appraisal_detail')->where(['appraisal_id'=>$value['id']])->find(); // 业务员信息 $saleman_info = $this->getNewUserId($value['sale_man']); if ($saleman_info) { $user_id = $saleman_info['id']; $user_name = $saleman_info['name']; $user_phone = $saleman_info['tel']; $department_id = $saleman_info['deptid']; $department_name = $saleman_info['deptname']; } else { $user_id = ''; $user_name = $value['sale_man']; $user_phone = NULL; $department_id = NULL; $department_name = NULL; } // 银行 $bank_branch = Db::name('bank')->where(['bank_id'=>$value['branch_id'],'type'=>3])->find(); $bank_sub = Db::name('bank')->where(['id'=>$bank_branch['pid'],'type'=>2])->find(); $bank = Db::name('bank')->where(['id'=>$bank_sub['pid'],'type'=>1])->find(); if ($bank_branch) { $bank_id = $bank['id']; $bank_name = $bank['name']; $bank_branch_id = $bank_sub['id']; $bank_branch_name = $bank_sub['name']; $bank_sub_id = $bank_branch['id']; $bank_sub_name = $bank_branch['name']; } else { $bank_id = ''; $bank_name = ''; $bank_branch_id = ''; $bank_branch_name = $value['bank_sub_short_name']; $bank_sub_id = ''; $bank_sub_name = $value['branch_short_name']; } // 客户经理 $customer_data = Db::name('account_manager')->where(['name'=>$value['cust_name']])->find(); if ($customer_data) { $bank_customer_mgr_id = $customer_data['id']; $bank_customer_mgr_name = $customer_data['name']; $bank_customer_mgr_phone = $customer_data['mobile']; } else { $bank_customer_mgr_id = ''; $bank_customer_mgr_name = $value['cust_name']; $bank_customer_mgr_phone = ''; } // 签章 $seal_status = $value['is_sign']==1?2:1; if ($seal_status==2) { $estimate_no_year = substr($value['appraisal_no'], 0, 4); $estimate_no_date = substr($value['appraisal_no'], 4); $estimated_no = $estimate_no_year.'-X'.$estimate_no_date; $sign_data = Db::name('old_appraisal_sign')->where(['appraisal_id'=>$value['id']])->order('sign_time desc')->find(); if ($sign_data) { $sign_user_id = Db::name('valuer')->where(['name'=>$sign_data['sign_name']])->find(); $appraiser_ids = $sign_user_id['id']; $seal_time = $sign_data['sign_time']; } else { $appraiser_ids = NULL; $seal_time = NULL; } } else { $estimated_no = NULL; $appraiser_ids = NULL; $seal_time = NULL; } $status = $value['is_appraisal']==1?2:1; // 查勘列表数据 $survey_data = Db::name('old_survey_list')->where(['appraisal_id'=>$value['id']])->find(); if ($survey_data) { if ($survey_data['is_order']==1 && $survey_data['is_complete']==1) { $status = 5; } elseif ($survey_data['is_order']==1 && $survey_data['is_complete']==0) { $status = 4; } else { $status = 3; } } // 报告列表数据 $report_data = Db::name('old_app_report')->where(['appraisal_id'=>$value['id']])->find(); if ($report_data) { if ($report_data['is_make']==1) { if ($report_data['is_finished']==1) { if ($report_data['is_audit']==1) { $status = 8; } else { $status = 7; } } else { $status = 6; } } else { $status = 5; } } $inquiry_ins_data = [ 'order_no' => $order_no, 'user_id' => $user_id, 'user_name' => $user_name, 'user_phone' => $user_phone, 'bank_id' => $bank_id, 'bank_name' => $bank_name, 'bank_branch_id' => $bank_branch_id, 'bank_branch_name' => $bank_branch_name, 'bank_sub_id' => $bank_sub_id, 'bank_sub_name' => $bank_sub_name, 'bank_customer_mgr_id' => $bank_customer_mgr_id, 'bank_customer_mgr_name' => $bank_customer_mgr_name, 'bank_customer_mgr_phone' => $bank_customer_mgr_phone, 'eva_purpose' => $inquiry_detail_data['purpose'], 'type' => 1, 'status' => $status!=1?$status:0, 'seal_status' => $seal_status, 'appraiser_ids' => $appraiser_ids, 'department_id' => $department_id, 'department_name' => $department_name, 'estimated_no' => $estimated_no, 'estimated_desc' => $inquiry_detail_data['instructions'], 'is_simple' => $report_data['report_type']==2?1:0, 'create_time' => $value['appraisal_date'], 'update_time' => $value['manu_appdate'], 'seal_time' => $seal_time ]; $inquiry_insert_result = Db::name('inquiry')->insert($inquiry_ins_data); if ($inquiry_insert_result) { $quot_id = Db::name('inquiry')->getLastInsID(); /* 签章历史表数据迁移 */ $sign_data_migrate = Db::name('old_appraisal_sign')->where(['appraisal_id' => $value['id']])->select(); if ($sign_data_migrate) { foreach ($sign_data_migrate as $k => $v) { $signature_record_ins_data = [ 'quot_id' => $quot_id, 'name' => $v['sign_name'], 'create_time' => strtotime($v['sign_time']) ]; $signature_record_insert_result = Db::name('signature_record')->insert($signature_record_ins_data); if (!$signature_record_insert_result) { Db::rollback(); } } } /* 询价详情表数据迁移开始 */ if ($value['city_name'] == NULL) { $city_id = NULL; } elseif ($value['city_name'] == '广州') { $city_id = '440100'; } elseif ($value['city_name'] == '东莞') { $city_id = '441900'; } elseif ($value['city_name'] == '惠州') { $city_id = '441300'; } elseif ($value['city_name'] == '深圳') { $city_id = '440300'; } elseif ($value['city_name'] == '北京') { $city_id = '110100'; } elseif ($value['city_name'] == '武汉') { $city_id = '420100'; } else { $city_id_result = Db::name('region')->where(['shortname'=>$value['city_name']])->field('id')->find(); if ($city_id_result) { $city_id = $city_id_result['id']; } else { $city_id = NULL; } } // 是否满两年 if ($inquiry_detail_data['buy_years'] > 2) { $is_tran_tax_free = 1; } else { $is_tran_tax_free = 2; } $attachments = NULL; if (!empty($inquiry_detail_data['attached'])) { $attachments = $this->picHandle($inquiry_detail_data['attached']); } elseif (Db::name('old_appraisal_image')->where(['appraisal_imageid'=>$value['id']])->find()) { $attach = Db::name('old_appraisal_image')->where(['appraisal_imageid'=>$value['id']])->find(); $attachments = ''; if (!empty($attach['image1'])) { $attachments .= $this->picHandle($attach['image1']); } if (!empty($attach['image2'])) { $attachments .= ','.$this->picHandle($attach['image2']); } if (!empty($attach['image3'])) { $attachments .= ','.$this->picHandle($attach['image3']); } if (!empty($attach['image4'])) { $attachments .= ','.$this->picHandle($attach['image4']); } if (!empty($attach['image5'])) { $attachments .= ','.$this->picHandle($attach['image5']); } $attachments = trim($attachments, ','); } elseif (Db::name('old_survey_list')->where(['appraisal_id'=>$value['id']])->find()) { $attach = Db::name('old_survey_list')->where(['appraisal_id'=>$value['id']])->find(); $attachments = $this->picHandle($attach['property_card']); } $usage_code = getDictionaryCode('HOUSE_USAGE', $inquiry_detail_data['house_use']); if ($usage_code) { $usage = $usage_code['code']; } else { $usage = NULL; } if (!empty($survey_data['surveypeople_name'])) { $survey_user_info = $this->getNewUserId($survey_data['surveypeople_name']); if ($survey_user_info) { $survey_user_id = $survey_user_info['id']; $survey_user_name = $survey_user_info['name']; $survey_time = $survey_data['surveylist_date']; } else { $survey_user_id = NULL; $survey_user_name = $survey_data['surveypeople_name']; $survey_time = $survey_data['surveylist_date']; } } else { $survey_user_id = NULL; $survey_user_name = NULL; $survey_time = NULL; } $inquiry_detail_ins_data = [ 'quot_id' => $quot_id, 'city' => $value['city_name'], 'city_id' => $city_id, 'property_full_name' => $value['building_name'].$value['building_no'].$value['house_name'], 'building_name' => $value['building_name'], 'building_unit_no' => $value['building_no'].$value['house_name'], 'building_no' => $value['building_no'], 'unit_no' => $value['house_name'], 'size' => $value['gfa'], 'reg_price' => $inquiry_detail_data['original_amount'], 'is_tran_tax_free' => $is_tran_tax_free, 'property_cert' => $inquiry_detail_data['estate_certificate_no'], 'attachments' => $attachments, 'remark' => $value['remarkinner'], 'usage' => $usage, 'evaluated_unit_price' => $value['manu_unitprice'], 'survey_user_id' => $survey_user_id, 'survey_user_name' => $survey_user_name, 'survey_time' => $survey_time, 'create_time' => $value['appraisal_date'], 'update_time' => $value['manu_appdate'] ]; $inquiry_detail_insert_result = Db::name('property_cert_info')->insert($inquiry_detail_ins_data); /* 询价详情表数据迁移结束 */ if (!$inquiry_detail_insert_result) { Db::rollback(); } else { $property_cert_info_id = Db::name('property_cert_info')->getLastInsID(); /* 回价数据迁移开始 */ if ($value['is_appraisal'] > 0) { // 回价历史数据 // $r_map[] = ['appraisal_id', '=', $value['id']]; // $r_map[] = ['old_data', 'like', '%---%']; // $r_map[] = ['old_data', '<>', '原栋号:------------']; // $record_data = Db::name('old_appraisal_modify_record')->where($r_map)->select(); $record_data = Db::name('old_appraisal_modify_record') ->where([ ['appraisal_id', '=', $value['id']], ['old_data', 'like', '%---%'], ['old_data', '<>', '原栋号:------------'] ]) ->select(); if ($record_data) { $record_return_price_ins_data['property_cert_info_id'] = $property_cert_info_id; $record_return_price_ins_data['property_name'] = $value['building_name'].$value['building_no'].$value['house_name']; $record_return_price_ins_data['building_unit_no'] = $value['building_no'].$value['house_name']; $record_return_price_ins_data['building_no'] = $value['building_no']; $record_return_price_ins_data['unit_no'] = $value['house_name']; foreach ($record_data as $re_key=>$re_value) { $old_data = explode('---', $re_value['old_data']); if ($old_data[3]!=$value['manu_unitprice']) { // 回价修改人信息 $record_appraiser_info = $this->getNewUserId($re_value['modify_user_name']); if ($record_appraiser_info) { $record_appraiser_id = $record_appraiser_info['id']; $record_appraiser_name = $record_appraiser_info['name']; } else { $record_appraiser_id = NULL; $record_appraiser_name = $re_value['modify_user_name']; } $record_return_price_ins_data['area'] = sprintf('%.2f', $old_data[2]); $record_return_price_ins_data['eva_unit_price'] = sprintf('%.2f', $old_data[3]); $record_return_price_ins_data['eva_total_value'] = sprintf('%.2f', $old_data[4]); $record_return_price_ins_data['appraiser_id'] = $record_appraiser_id; $record_return_price_ins_data['appraiser_name'] = $record_appraiser_name; $record_return_price_ins_data['create_time'] = strtotime($re_value['modify_datetime']); Db::name('return_price')->insert($record_return_price_ins_data); } } } // 相关税费数据 $corporate_income_tax = 0; $tran_service_fee = 0; $edu_surcharge = 0; $urban_construction_tax = 0; $deed_tax = 0; $stamp_duty = 0; $added_tax = 0; $land_value_added_tax = 0; $personal_income_tax = 0; $auction_fee = 0; $tax_data = Db::name('old_appraisal_tax')->where(['appraisal_id'=>$value['id']])->select(); if ($tax_data) { foreach ($tax_data as $tax_key=>$tax_value) { if ($tax_value['tax_info_id']=='M') { $added_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='O') { $tran_service_fee = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='L') { $edu_surcharge = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='N') { $urban_construction_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='V') { $deed_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='P') { $stamp_duty = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='X') { $added_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='Q') { $land_value_added_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='R') { $personal_income_tax = $tax_value['tax_amount']; } if ($tax_value['tax_info_id']=='T') { $auction_fee = $tax_value['tax_amount']; } } } else { $corporate_income_tax = NULL; $tran_service_fee = NULL; $edu_surcharge = NULL; $urban_construction_tax = NULL; $deed_tax = NULL; $stamp_duty = NULL; $added_tax = NULL; $land_value_added_tax = NULL; $personal_income_tax = NULL; $auction_fee = NULL; } $appraiser_info = $this->getNewUserId($value['manu_user_name']); if ($appraiser_info) { $appraiser_id = $appraiser_info['id']; $appraiser_name = $appraiser_info['name']; } else { $appraiser_id = NULL; $appraiser_name = $value['manu_user_name']; } $return_price_ins_data = [ 'property_cert_info_id' => $property_cert_info_id, 'property_name' => $value['building_name'].$value['building_no'].$value['house_name'], 'area' => sprintf('%.2f', $value['gfa']), 'building_unit_no' => $value['building_no'].$value['house_name'], 'building_no' => $value['building_no'], 'unit_no' => $value['house_name'], 'eva_unit_price' => sprintf('%.2f', $value['manu_unitprice']), 'eva_total_value' => sprintf('%.2f', $value['manu_totalamo']), 'eva_net_value' => sprintf('%.2f', $inquiry_detail_data['manu_netamo']), 'eva_net_value2' => sprintf('%.2f', $inquiry_detail_data['manu_netamo2']), 'corporate_income_tax' => sprintf('%.2f', $corporate_income_tax), 'tran_service_fee' => sprintf('%.2f', $tran_service_fee), 'edu_surcharge' => sprintf('%.2f', $edu_surcharge), 'urban_construction_tax' => sprintf('%.2f', $urban_construction_tax), 'deed_tax' => sprintf('%.2f', $deed_tax), 'stamp_duty' => sprintf('%.2f', $stamp_duty), 'added_tax' => sprintf('%.2f', $added_tax), 'land_value_added_tax' => sprintf('%.2f', $land_value_added_tax), 'personal_income_tax' => sprintf('%.2f', $personal_income_tax), 'auction_fee' => sprintf('%.2f', $auction_fee), 'total_taxes1' => sprintf('%.2f', $inquiry_detail_data['manu_taxamo']), 'total_taxes2' => 0, 'internal_remarks' => $inquiry_detail_data['remark_inner'], 'external_remarks' => $inquiry_detail_data['remark_outer'], 'appraiser_id' => $appraiser_id, 'appraiser_name' => $appraiser_name, 'create_time' => strtotime($value['manu_appdate']) ]; Db::name('return_price')->insert($return_price_ins_data); } /* 回价数据迁移结束 */ /* 查勘数据迁移开始 */ if ($survey_data) { // 旧系统查勘详情数据 $survey_detail_data = Db::name('old_surveyrecord')->where(['id'=>$value['id']])->find(); // 报告详情数据 $report_detail_data = Db::name('old_app_report_detail')->where(['appreport_id'=>$report_data['app_report_id']])->find(); if ($survey_detail_data) { if ($survey_data['is_order']==1) { if ($survey_data['is_complete']==1) { $survey_status = 3; } else { $survey_status = -2; } } else { $survey_status = -1; } $survey_ins_data = [ 'order_no' => $order_no, 'property_cert_info_id' => $property_cert_info_id, 'contact_name' => !empty($survey_detail_data['checkcontacts'])?$survey_detail_data['checkcontacts']:'', 'contact_phone' => !empty($survey_detail_data['checkphone'])?$survey_detail_data['checkphone']:'', 'area' => $survey_detail_data['small_district_name'], 'area_id' => 0, 'complete_time' => $survey_data['surveylist_date'], 'user_id' => $survey_user_id, 'user_name' => $survey_user_name, 'remark' => $survey_detail_data['illustration'], 'status' => $survey_status, 'create_time' => !empty($survey_data['surveylist_date'])?$survey_data['surveylist_date']:'', 'update_time' => !empty($survey_data['surveylist_date'])?$survey_data['surveylist_date']:'' ]; Db::name('survey')->insert($survey_ins_data); // 查勘详细数据迁移 $survey_id = Db::name('survey')->getLastInsID(); // 朝向 if (!empty($report_detail_data['orientations'])) { $survey_detail_data['orientations'] = $report_detail_data['orientations']; } $towards_code = Db::name('options_automation')->where(['key'=>'residence_towards','name'=>$survey_detail_data['orientations']])->field('code')->find(); if ($towards_code) { $towards = $towards_code['code']; } else { $towards = NULL; } // 评估楼层 if (!empty($report_detail_data['floor_appraisal'])) { $floor_no = rtrim($report_detail_data['floor_appraisal'], '层楼'); } elseif (!empty($survey_detail_data['FloorAppraisal'])) { $floor_no = rtrim($survey_detail_data['FloorAppraisal'], '层楼'); } else { $floor_no = NULL; } // 总楼层 // if (!empty($report_detail_data['total_floors'])) { // preg_match('/\d+/', $report_detail_data['total_floors'], $arr); // $total_floors = $arr[0]; // } elseif (!empty($survey_detail_data['FloorTotal'])) { // preg_match('/\d+/', $survey_detail_data['FloorTotal'], $arr); // $total_floors = $arr[0]; // } else { // $total_floors = NULL; // } $floor_total_str = !empty($report_detail_data['total_floors'])?$report_detail_data['total_floors']:$survey_detail_data['FloorTotal']; if (!empty($floor_total_str)) { $floor_total_str = trim($floor_total_str); preg_match('/\d+/', $floor_total_str, $arr); if ($floor_total_str=='无' || !$arr) { $total_floors = 0; } else { $total_floors = $arr[0]; } } else { $total_floors = NULL; } // 电梯 $lift_str = !empty($report_detail_data['lift'])?$report_detail_data['lift']:$survey_detail_data['Lift']; if (!empty($lift_str)) { $lift_str = trim($lift_str); preg_match('/\d+/', $lift_str, $arr); if ($lift_str=='无' || !$arr) { $lift = 0; } else { $lift = $arr[0]; } } else { $lift = NULL; } // 停车场库 $parking_lot = Db::name('options_automation')->where(['key'=>'residence_parking_garage'])->field('code,name')->select(); $parking_garage_str = ''; $car_park = !empty($report_detail_data['car_park'])?$report_detail_data['car_park']:$survey_detail_data['CarPark']; foreach ($parking_lot as $pg_key=>$pg_value) { if (strpos($car_park, $pg_value['name'])!==false) { $parking_garage_str .= ','.$pg_value['code']; } } $parking_garage_str = trim($parking_garage_str, ','); // 停车场库使用状态 $parking_lot_usage = Db::name('options_automation')->where(['key'=>'residence_parking_garage_status'])->field('code,name')->select(); $parking_garage_status_str = ''; $use_status = !empty($report_detail_data['use_status'])?$report_detail_data['use_status']:$survey_detail_data['UseStatus']; foreach ($parking_lot_usage as $pgs_key=>$pgs_value) { if (strpos($use_status, $pgs_value['name'])!==false) { $parking_garage_status_str .= ','.$pgs_value['code']; } } $parking_garage_status_str = trim($parking_garage_status_str, ','); // 装修档次 if (!empty($report_detail_data['hourse_grade'])) { $hourse_grade = $report_detail_data['hourse_grade']; } elseif (!empty($survey_detail_data['HourseGrade'])) { $hourse_grade = $survey_detail_data['HourseGrade']; } else { $hourse_grade = ''; } $decoration_code = Db::name('options_automation')->where(['key'=>'residence_decoration_grade','name'=>$hourse_grade])->field('code')->find(); if ($decoration_code) { $decoration = $decoration_code['code']; } else { $decoration = NULL; } // 实际用途，1：住宅，2：办公，3：商务公寓（住宅），4：商业 $residence_usage = !empty($report_detail_data['hourse_real_use'])?$report_detail_data['hourse_real_use']:$survey_detail_data['HourseRealUse']; if ($residence_usage == '住宅') { $residence_usage_code = 1; } elseif ($residence_usage == '办公') { $residence_usage_code = 2; } elseif ($residence_usage == '商务公寓（住宅）') { $residence_usage_code = 3; } elseif ($residence_usage == '商业') { $residence_usage_code = 4; } else { $residence_usage_code = NULL; } // 成新率 $newness_rate = !empty($report_detail_data['hourse_new'])?$report_detail_data['hourse_new']:$survey_detail_data['HourseNew']; if ($newness_rate == '一成新' || $newness_rate == '二成新' || $newness_rate == '三成新' || $newness_rate == '四成新' || $newness_rate == '五成新') { $newness_rate = 6; } elseif ($newness_rate == '6成' || $newness_rate == '6成新' || $newness_rate == '6层' || $newness_rate == '6层新' || $newness_rate == '六成' || $newness_rate == '六成新' || $newness_rate == '六层' || $newness_rate == '六层新') { $newness_rate = 6; } elseif ($newness_rate == '7成' || $newness_rate == '7成新' || $newness_rate == '7层' || $newness_rate == '7层新' || $newness_rate == '七成' || $newness_rate == '七成新' || $newness_rate == '七层' || $newness_rate == '七层新') { $newness_rate = 7; } elseif ($newness_rate == '8成' || $newness_rate == '8成新' || $newness_rate == '8层' || $newness_rate == '8层新' || $newness_rate == '八成' || $newness_rate == '八成新' || $newness_rate == '八层' || $newness_rate == '八层新' || $newness_rate == '8.5' || $newness_rate == '八五成') { $newness_rate = 8; } elseif ($newness_rate == '9成' || $newness_rate == '9成新' || $newness_rate == '9层' || $newness_rate == '9层新' || $newness_rate == '九成' || $newness_rate == '九成新' || $newness_rate == '九层' || $newness_rate == '九层新' || $newness_rate == '90%') { $newness_rate = 9; } elseif ($newness_rate == '十成' || $newness_rate == '十成新' || $newness_rate == '十层' || $newness_rate == '十层新' || $newness_rate == '全新') { $newness_rate = 10; } else { $newness_rate = NULL; } // 查勘图库 $appraisal_img_ids = ''; // 外景 if (!empty($report_detail_data['decorate_outer_wall'])) { $outdoor_scene_code = $this->surveyCode($report_detail_data['decorate_outer_wall'], 'residence_outdoor_scene', ','); } elseif (!empty($survey_detail_data['DecorateOuterWall'])) { $outdoor_scene_code = $this->surveyCode($survey_detail_data['DecorateOuterWall'], 'residence_outdoor_scene', ','); } else { $outdoor_scene_code = NULL; } // 外景图片 if (!empty($report_detail_data['attachment_estate_exterior'])) { $exterior_view_img_ids = $this->picHandle($report_detail_data['attachment_estate_exterior']); } elseif (!empty($survey_detail_data['imageestateexterior'])) { $exterior_view_img_ids = $this->picHandle($survey_detail_data['imageestateexterior']); } else { $exterior_view_img_ids = 0; } if ($exterior_view_img_ids) { $appraisal_img_ids .= ','.$exterior_view_img_ids; } // 门窗 if (!empty($report_detail_data['decorate_doors_windows'])) { $doorplate_code = $this->surveyCode($report_detail_data['decorate_doors_windows'], 'residence_doorplate', ','); } elseif (!empty($survey_detail_data['DecorateDoorsWindows'])) { $doorplate_code = $this->surveyCode($survey_detail_data['DecorateDoorsWindows'], 'residence_doorplate', ','); } else { $doorplate_code = NULL; } // 门窗图片 if (!empty($report_detail_data['attachment_estate_doorplate'])) { $doorplate_img_ids = $this->picHandle($report_detail_data['attachment_estate_doorplate']); } elseif (!empty($survey_detail_data['imageestatedoorplate'])) { $doorplate_img_ids = $this->picHandle($survey_detail_data['imageestatedoorplate']); } else { $doorplate_img_ids = 0; } if ($doorplate_img_ids) { $appraisal_img_ids .= ','.$doorplate_img_ids; } // 客厅内墙 if (!empty($report_detail_data['hourse_liveroom_innerwall'])) { $living_room_inside_wall_code = $this->surveyCode($report_detail_data['hourse_liveroom_innerwall'], 'residence_living_room_inside_wall', ','); } elseif (!empty($survey_detail_data['HourseLiveroomInnerwall'])) { $living_room_inside_wall_code = $this->surveyCode($survey_detail_data['HourseLiveroomInnerwall'], 'residence_living_room_inside_wall', ','); } else { $living_room_inside_wall_code = ""; } // 客厅天花板 if (!empty($report_detail_data['hourse_liveroom_celling'])) { $living_room_ceiling_code = $this->surveyCode($report_detail_data['hourse_liveroom_celling'], 'residence_living_room_ceiling', ','); } elseif (!empty($survey_detail_data['HourseLiveroomCelling'])) { $living_room_ceiling_code = $this->surveyCode($survey_detail_data['HourseLiveroomCelling'], 'residence_living_room_inside_wall', ','); } else { $living_room_ceiling_code = ""; } // 客厅地面 if (!empty($report_detail_data['hourse_liveroom_ground'])) { $living_room_ground_code = $this->surveyCode($report_detail_data['hourse_liveroom_ground'], 'residence_living_room_ground', ','); } elseif (!empty($survey_detail_data['HourseLiveroomGround'])) { $living_room_ground_code = $this->surveyCode($survey_detail_data['HourseLiveroomGround'], 'residence_living_room_ground', ','); } else { $living_room_ground_code = ""; } $living_room = [ 'residence_living_room_ground' => $living_room_ground_code, 'residence_living_room_ceiling' => $living_room_ceiling_code, 'residence_living_room_inside_wall' => $living_room_inside_wall_code ]; $living_room = json_encode($living_room); // 客厅图片 if (!empty($report_detail_data['attachment_estate_liveroom'])) { $living_room_img_ids = $this->picHandle($report_detail_data['attachment_estate_liveroom']); } elseif (!empty($survey_detail_data['imageestateliveroom'])) { $living_room_img_ids = $this->picHandle($survey_detail_data['imageestateliveroom']); } else { $living_room_img_ids = 0; } if ($living_room_img_ids) { $appraisal_img_ids .= ','.$living_room_img_ids; } // 卧室内墙 if (!empty($report_detail_data['hourse_bedroom_innerwall'])) { $bedroom_inside_wall_code = $this->surveyCode($report_detail_data['hourse_bedroom_innerwall'], 'residence_bedroom_inside_wall', ','); } elseif (!empty($survey_detail_data['HourseBedroomInnerwall'])) { $bedroom_inside_wall_code = $this->surveyCode($survey_detail_data['HourseBedroomInnerwall'], 'residence_bedroom_inside_wall', ','); } else { $bedroom_inside_wall_code = ""; } // 卧室天花板 if (!empty($report_detail_data['hourse_bedroom_celling'])) { $bedroom_ceiling_code = $this->surveyCode($report_detail_data['hourse_bedroom_celling'], 'residence_bedroom_ceiling', ','); } elseif (!empty($survey_detail_data['HourseBedroomCelling'])) { $bedroom_ceiling_code = $this->surveyCode($survey_detail_data['HourseBedroomCelling'], 'residence_bedroom_ceiling', ','); } else { $bedroom_ceiling_code = ""; } // 卧室地面 if (!empty($report_detail_data['hourse_bedroom_ground'])) { $bedroom_ground_code = $this->surveyCode($report_detail_data['hourse_bedroom_ground'], 'residence_bedroom_ground', ','); } elseif (!empty($survey_detail_data['HourseBedroomGround'])) { $bedroom_ground_code = $this->surveyCode($survey_detail_data['HourseBedroomGround'], 'residence_bedroom_ground', ','); } else { $bedroom_ground_code = ""; } $bedroom = [ 'residence_bedroom_ground' => $bedroom_ground_code, 'residence_bedroom_ceiling' => $bedroom_ceiling_code, 'residence_bedroom_inside_wall' => $bedroom_inside_wall_code ]; $bedroom = json_encode($bedroom); // 卧室图片 if (!empty($report_detail_data['attachment_estate_bedroom'])) { $bedroom_img_ids = $this->picHandle($report_detail_data['attachment_estate_bedroom']); } elseif (!empty($survey_detail_data['imageestatebedroom'])) { $bedroom_img_ids = $this->picHandle($survey_detail_data['imageestatebedroom']); } else { $bedroom_img_ids = 0; } if ($bedroom_img_ids) { $appraisal_img_ids .= ','.$bedroom_img_ids; } // 厨房 if (!empty($report_detail_data['decorate_kitchen'])) { if ($report_detail_data['decorate_kitchen']=='毛坯' || $report_detail_data['decorate_kitchen']=='毛坯,') { $kitchen_wall_code = "2"; $kitchen_ground_code = "5"; $kitchen_ceiling_code = "5"; $kitchen_equipment_code = "3"; } else { $kitchen_wall_code = ""; $kitchen_ground_code = ""; $kitchen_ceiling_code = ""; $kitchen_equipment_code = ""; $decorate_kitchen_str = trim($report_detail_data['decorate_kitchen'], ';'); $decorate_kitchen_arr = explode('；', $decorate_kitchen_str); foreach ($decorate_kitchen_arr as $kitchen_key=>$kitchen_value) { $kitchen_temp_arr = explode('：', $kitchen_value); if ($kitchen_temp_arr[0] == '天花') { $kitchen_ceiling_code = $this->surveyCode($kitchen_temp_arr[1], 'residence_kitchen_ceiling', ','); } if ($kitchen_temp_arr[0] == '墙面') { $kitchen_wall_code = $this->surveyCode($kitchen_temp_arr[1], 'residence_kitchen_wall', ','); } if ($kitchen_temp_arr[0] == '地面') { $kitchen_ground_code = $this->surveyCode($kitchen_temp_arr[1], 'residence_kitchen_ground', ','); } if ($kitchen_temp_arr[0] == '厨房设备') { $kitchen_equipment_code = $this->surveyCode($kitchen_temp_arr[1], 'residence_kitchen_equipment', ','); } } } } elseif (!empty($survey_detail_data['decoratekitchen1']) || !empty($survey_detail_data['decoratekitchen2']) || !empty($survey_detail_data['decoratekitchen3']) || !empty($survey_detail_data['decoratekitchen4'])) { $kitchen_ceiling_code = $this->surveyCode($survey_detail_data['decoratekitchen1'], 'residence_kitchen_ceiling', ','); $kitchen_wall_code = $this->surveyCode($survey_detail_data['decoratekitchen2'], 'residence_kitchen_wall', ','); $kitchen_ground_code = $this->surveyCode($survey_detail_data['decoratekitchen3'], 'residence_kitchen_ground', ','); $kitchen_equipment_code = $this->surveyCode($survey_detail_data['decoratekitchen4'], 'residence_kitchen_equipment', ','); } else { $kitchen_wall_code = ""; $kitchen_ground_code = ""; $kitchen_ceiling_code = ""; $kitchen_equipment_code = ""; } $kitchen = [ 'residence_kitchen_wall' => $kitchen_wall_code, 'residence_kitchen_ground' => $kitchen_ground_code, 'residence_kitchen_ceiling' => $kitchen_ceiling_code, 'residence_kitchen_equipment' => $kitchen_equipment_code ]; $kitchen = json_encode($kitchen); // 厨房图片 if (!empty($report_detail_data['attachment_estate_kitchen'])) { $kitchen_img_ids = $this->picHandle($report_detail_data['attachment_estate_kitchen']); } elseif (!empty($survey_detail_data['imageestatekitchen'])) { $kitchen_img_ids = $this->picHandle($survey_detail_data['imageestatekitchen']); } else { $kitchen_img_ids = 0; } if ($kitchen_img_ids) { $appraisal_img_ids .= ','.$kitchen_img_ids; } // 卫生间 // 天花：塑板天花；墙面：瓷片贴墙到顶；地面：防滑地砖地面；卫生洁具：洁具+冲凉房; if (!empty($report_detail_data['decorate_toilet'])) { if ($report_detail_data['decorate_toilet']=='毛坯' || $report_detail_data['decorate_toilet']=='毛坯,') { $bathroom_wall_code = "2"; $bathroom_ground_code = "3"; $bathroom_ceiling_code = "5"; $bathroom_sanitary_code = "3"; } else { $bathroom_wall_code = ""; $bathroom_ground_code = ""; $bathroom_ceiling_code = ""; $bathroom_sanitary_code = ""; $decorate_toilet_str = trim($report_detail_data['decorate_toilet'], ';'); $decorate_toilet_arr = explode('；', $decorate_toilet_str); foreach ($decorate_toilet_arr as $toilet_key=>$toilet_value) { $toilet_temp_arr = explode('：', $toilet_value); if ($toilet_temp_arr[0] == '天花') { $bathroom_ceiling_code = $this->surveyCode($toilet_temp_arr[1], 'residence_bathroom_ceiling', ','); } if ($toilet_temp_arr[0] == '墙面') { $bathroom_wall_code = $this->surveyCode($toilet_temp_arr[1], 'residence_bathroom_wall', ','); } if ($toilet_temp_arr[0] == '地面') { $bathroom_ground_code = $this->surveyCode($toilet_temp_arr[1], 'residence_bathroom_ground', ','); } if ($toilet_temp_arr[0] == '卫生洁具') { $bathroom_sanitary_code = $this->surveyCode($toilet_temp_arr[1], 'residence_bathroom_sanitary', ','); } } } } elseif (!empty($survey_detail_data['DecorateToilet']) || !empty($survey_detail_data['decorate_toilet2']) || !empty($survey_detail_data['decorate_toilet3']) || !empty($survey_detail_data['decorate_toilet4'])) { $bathroom_ceiling_code = $this->surveyCode($survey_detail_data['DecorateToilet'], 'residence_bathroom_ceiling', ','); $bathroom_wall_code = $this->surveyCode($survey_detail_data['decorate_toilet2'], 'residence_bathroom_wall', ','); $bathroom_ground_code = $this->surveyCode($survey_detail_data['decorate_toilet3'], 'residence_bathroom_ground', ','); $bathroom_sanitary_code = $this->surveyCode($survey_detail_data['decorate_toilet4'], 'residence_bathroom_sanitary', ','); } else { $bathroom_wall_code = ""; $bathroom_ground_code = ""; $bathroom_ceiling_code = ""; $bathroom_sanitary_code = ""; } $bathroom = [ 'residence_bathroom_wall' => $bathroom_wall_code, 'residence_bathroom_ground' => $bathroom_ground_code, 'residence_bathroom_ceiling' => $bathroom_ceiling_code, 'residence_bathroom_sanitary' => $bathroom_sanitary_code ]; $bathroom = json_encode($bathroom); // 卫生间图片 if (!empty($report_detail_data['attachment_estate_toilet'])) { $bathroom_img_ids = $this->picHandle($report_detail_data['attachment_estate_toilet']); } elseif (!empty($survey_detail_data['imageestatetoilet'])) { $bathroom_img_ids = $this->picHandle($survey_detail_data['imageestatetoilet']); } else { $bathroom_img_ids = 0; } if ($bathroom_img_ids) { $appraisal_img_ids .= ','.$bathroom_img_ids; } // 设施 // 水电设施 if (!empty($report_detail_data['water_elec_facilities'])) { $facility_hydropower_code = $this->surveyCode($report_detail_data['water_elec_facilities'], 'residence_facility_hydropower', ','); } elseif (!empty($survey_detail_data['WaterElecFacilities'])) { $facility_hydropower_code = $this->surveyCode($survey_detail_data['WaterElecFacilities'], 'residence_facility_hydropower', ','); } else { $facility_hydropower_code = ""; } // 通讯设施 if (!empty($report_detail_data['hourse_communication'])) { $facility_communication_code = $this->surveyCode($report_detail_data['hourse_communication'], 'residence_facility_communication', '，'); } elseif (!empty($survey_detail_data['HourseCommunication'])) { $facility_communication_code = $this->surveyCode($survey_detail_data['HourseCommunication'], 'residence_facility_communication', '，'); } else { $facility_communication_code = ""; } $facility = [ 'residence_facility_hydropower' => $facility_hydropower_code, 'residence_facility_communication' => $facility_communication_code ]; $facility = json_encode($facility); // 其他 if (!empty($report_detail_data['decorate_other'])) { $other_code = $this->surveyCode($report_detail_data['decorate_other'], 'residence_other', ','); } elseif (!empty($survey_detail_data['DecorateOther'])) { $other_code = $this->surveyCode($survey_detail_data['DecorateOther'], 'residence_other', ','); } else { $other_code = ""; } $other = ['residence_other'=>$other_code]; $other = json_encode($other); // 物业位置图 if (!empty($report_detail_data['attachment_estate_address'])) { $loc_img_ids = $this->picHandle($report_detail_data['attachment_estate_address']); } elseif (!empty($survey_detail_data['locationmap'])) { $loc_img_ids = $this->picHandle($survey_detail_data['locationmap']); } else { $loc_img_ids = 0; } if ($loc_img_ids) { $appraisal_img_ids .= ','.$loc_img_ids; } // 补充图片 $supplement_img_ids = ''; if (!empty($report_detail_data['attachment_estate_other1'])) { $supplement_img_ids .= $this->picHandle($report_detail_data['attachment_estate_other1']); } if (!empty($report_detail_data['attachment_estate_other2'])) { $supplement_img_ids .= ','.$this->picHandle($report_detail_data['attachment_estate_other2']); } if (!empty($report_detail_data['attachment_estate_other3'])) { $supplement_img_ids .= ','.$this->picHandle($report_detail_data['attachment_estate_other3']); } if (!empty($report_detail_data['attachment_estate_other4'])) { $supplement_img_ids .= ','.$this->picHandle($report_detail_data['attachment_estate_other4']); } if (!empty($report_detail_data['attachment_estate_other5'])) { $supplement_img_ids .= ','.$this->picHandle($report_detail_data['attachment_estate_other5']); } if (!empty($report_detail_data['attachment_estate_other6'])) { $supplement_img_ids .= ','.$this->picHandle($report_detail_data['attachment_estate_other6']); } if (!empty($survey_detail_data['imageestateother1'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother1']); } if (!empty($survey_detail_data['imageestateother2'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother2']); } if (!empty($survey_detail_data['imageestateother3'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother3']); } if (!empty($survey_detail_data['imageestateother4'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother4']); } if (!empty($survey_detail_data['imageestateother5'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother5']); } if (!empty($survey_detail_data['imageestateother6'])) { $supplement_img_ids .= ','.$this->picHandle($survey_detail_data['imageestateother6']); } $supplement_img_ids = trim($supplement_img_ids, ','); $survey_detail_ins_data = [ 'survey_id' => $survey_id, 'user_id' => $user_id, 'unit_type' => !empty($report_detail_data['hourse_type'])?$report_detail_data['hourse_type']:$survey_detail_data['HourseType'], 'towards' => $towards, 'elevator_apartment_count' => !empty($report_detail_data['stairs_door'])?$report_detail_data['stairs_door']:$survey_detail_data['stairs_door'], 'floor_no' => is_numeric($floor_no)?$floor_no:0, 'total_floors' => $total_floors, 'floor_height' => '', 'elevator' => $lift, 'parking_lot' => $parking_garage_str, 'parking_lot_usage' => $parking_garage_status_str, 'decoration' => $decoration, 'usage' => $residence_usage_code, 'newness_rate' => $newness_rate, 'structure' => !empty($report_detail_data['hourse_structure'])?$report_detail_data['hourse_structure']:$survey_detail_data['HourseStructure'], 'layout' => !empty($report_detail_data['hourse_surface'])?$report_detail_data['hourse_surface']:$survey_detail_data['HourseSurface'], 'greening' => !empty($report_detail_data['greening'])?$report_detail_data['greening']:$survey_detail_data['greening'], 'exterior_view' => $outdoor_scene_code, 'exterior_view_img_ids' => $exterior_view_img_ids, 'doorplate' => $doorplate_code, 'doorplate_img_ids' => $doorplate_img_ids, 'living_room' => $living_room, 'living_room_img_ids' => $living_room_img_ids, 'kitchen' => $kitchen, 'kitchen_img_ids' => $kitchen_img_ids, 'bathroom' => $bathroom, 'bathroom_img_ids' => $bathroom_img_ids, 'bedroom' => $bedroom, 'bedroom_img_ids' => $bedroom_img_ids, 'other' => $other, 'facility' => $facility, 'loc_img_ids' => $loc_img_ids, 'supplement_img_ids' => $supplement_img_ids, 'appraisal_img_ids' => trim($appraisal_img_ids, ','), 'submit_time' => $survey_data['surveylist_date'], 'create_time' => $survey_data['surveylist_date'] ]; $survey_detail_insert_result = Db::name('survey_detail')->insert($survey_detail_ins_data); } } /* 查勘数据迁移结束 */ /* 报告数据迁移开始 */ if ($report_data) { $result = Db::name('old_app_report')->alias('oar') ->join('old_app_report_detail oard','oard.appreport_id = oar.app_report_id') ->leftJoin('old_bank_sub obs', 'obs.bank_sub_id = oar.bank_sub_id') ->where(['oar.app_report_id'=>$report_data['app_report_id']]) ->field('oar.*,oard.*,obs.pdf_number,obs.reportno_suffix,obs.reportno_suffix2') ->find(); // 报告编号 $report_no = $result['report_no'].$result['reportno_suffix2']; // 电子提取码 $report_no_strs = $result['report_no']; $report_no_str = explode("-", $report_no_strs); $e_case_code = '04'.$result['pdf_number'].$result['report_year'].$report_no_str[1].'2'; if($status==8){ $status = 3; } elseif ($status==7) { $status = 2; } else { $status = 1; } // 报告制作人信息 $report_maker_info = $this->getNewUserId($result['report_make_user_name']); // 报告审核人信息 $report_check_info = $this->getNewUserId($result['report_check_user_name']); // 估价师1姓名 $appraiser_one = Db::name('valuer')->where(['id'=>$result['appraisaler']])->find(); // 估价师2姓名 $appraiser2_name = Db::name('valuer')->where(['id'=>$result['appraisaler1']])->find(); $seal_status = strpos($result['bank_sub_short_name'], '建')!==false?2:0; // 报告模板id $report_tmpl_id = ''; $cur_tmpl_id = ''; if (strpos($result['bank_sub_short_name'], '建') !== false) { if (strpos($inquiry_detail_data['purpose'], '二手')) { $report_tmpl_id = 13; $cur_tmpl_id = 135; } elseif (strpos($inquiry_detail_data['purpose'], '云')) { $report_tmpl_id = 14; $cur_tmpl_id = 133; } } elseif ( strpos($result['bank_sub_short_name'], '兴业') !== false || strpos($result['bank_sub_short_name'], '东莞') !== false || strpos($result['bank_sub_short_name'], '友利') !== false || strpos($result['bank_sub_short_name'], '北京') !== false || strpos($result['bank_sub_short_name'], '融兴') !== false || strpos($result['bank_sub_short_name'], '华南') !== false ) { $report_tmpl_id = 30; $cur_tmpl_id = 129; } elseif (strpos($result['bank_sub_short_name'], '光大') !== false) { $report_tmpl_id = 72; $cur_tmpl_id = 143; } elseif (strpos($result['bank_sub_short_name'], '民生') !== false) { $report_tmpl_id = 26; $cur_tmpl_id = 136; } elseif (strpos($result['bank_sub_short_name'], '大众') !== false) { $report_tmpl_id = 11; $cur_tmpl_id = 138; } elseif (strpos($result['bank_sub_short_name'], '工') !== false) { $report_tmpl_id = 62; $cur_tmpl_id = 130; } // 评估时间 if (!empty($result['appraisal_date'])) { $appraisal_time = substr($result['appraisal_date'], 0, 10); } else { $appraisal_time = '0000-00-00'; } // 有效时间 if (!empty($result['effective_date'])) { $validity_time = substr($result['effective_date'], 0, 10); } else { $validity_time = '0000-00-00'; } // 价值时点 if (!empty($result['app_point_date'])) { $valuation_time = substr($result['app_point_date'], 0, 10); } else { $valuation_time = '0000-00-00'; } $report_ins_data = [ 'quot_id' => $quot_id, 'report_no' => $report_no, 'e_case_code' => $e_case_code, 'status' => $status, 'maker_id' => $report_maker_info['id'], 'producer_name' => $report_maker_info['name'], 'reviewer_id' => $report_check_info['id'], 'reviewer_name' => $report_check_info['name'], 'review_time' => $result['report_check_date'], 'company' => '深圳市国中资产土地房地产评估有限公司', 'appraiser_id' => $result['appraisaler'], 'appraiser_name' => $appraiser_one?$appraiser_one['name']:'', 'appraiser2_id' => $result['appraisaler1'], 'appraiser2_name' => $appraiser2_name?$appraiser2_name['name']:'', 'print_cpy_no' => 1, 'completion_time' => strtotime($result['finish_date']), 'report_tmpl_id' => $report_tmpl_id, // 报告模板id（主干） 'appraisal_time' => $appraisal_time, 'validity_time' => $validity_time, 'valuation_time' => $valuation_time, 'old_report_url' => 'http://www.ecspg.com/index.php/Xingadmin/Appreportfinished/preview/?id='.$result['app_report_id'], // 旧评估系统报告链接 'report_qrcode' => 'http://www.ecspg.com/Uploads/Qrcode/formal/'.$result['app_report_id'].'.png', // 报告防伪二维码 'order_src' => 1, 'is_housing_fund' => 0, 'pay_type' => 2, 'business_type' => 1, 'cur_tmpl_id' => $cur_tmpl_id, // 报告模板id（分支） 'eva_total_value' => $result['manu_totalamo'], // 评估总值（物业总值） 'size' => $result['gfa'], // 建筑面积（物业总面积） 'report_source' => 2, // 报告来源，1：新系统，2：老系统 'seal_status' => $seal_status, // 报告盖章状态，0：不需要，1：待盖章，2：已盖章（仅限建设银行） 'market_background' => '1', // 市场背景，1：住宅，2：商业，3：办公，多选逗号隔开 'commission_note' => $result['delegationnote'], // 委托函说明 'create_time' => $value['manu_appdate'] ]; $report_insert_result = Db::name('report')->insert($report_ins_data); if ($report_insert_result) { $report_id = Db::name('report')->getLastInsID(); /* 报告详情数据迁移开始 */ // 房屋状况，1：期房，2：初次交易现房，3：多次交易现房，4：自建房 $house_condition = ''; if ($result['house_now'] == '期房') { $house_condition = 1; } elseif ($result['house_now'] == '初次交易现房') { $house_condition = 2; } elseif ($result['house_now'] == '多次交易现房') { $house_condition = 3; } // 竣工日期 $finished_date_str = date('Y-m-d', strtotime($result['finished_date'])); $finished_date_arr = explode('-', $finished_date_str); if (count($finished_date_arr)==3 && checkdate($finished_date_arr[1], $finished_date_arr[2], $finished_date_arr[0])) { $finished_date = $finished_date_str; } else { $finished_date = NULL; } // 物业位置图片 $loc_img_ids = $this->picHandle($result['attachment_estate_address']); // 摘要图片 $report_summary_img_ids = $this->picHandle($result['attachment_report_face']); $report_detail_ins_data = [ 'report_id' => $report_id, 'property_cert_info_id' => $property_cert_info_id, 'building_name' => $value['building_name'], 'property_cert' => $result['property_no'], // 房产证号 'area_id' => $city_id, 'area' => $value['city_name'], 'obligee' => $result['equity_name'], // 权利人 'client' => $result['client_name'], // 委托方 'parcel_no' => $result['parcel_number'], // 宗地号 'parcel_area' => $result['parcel_area'], // 宗地面积 'parcel_usage' => $result['parcel_usage'], // 土地用途 'use_right_source' => $result['owner_source'], // 使用权来源 'completion_date' => $finished_date, // 竣工日期（年-月-日） 'purchase_date' => $result['registdate'], // 登记/购买日期（年-月-日） 'reg_date' => date('Y-m-d', strtotime($result['appraisal_date'])), // 评估日期（年-月-日） 'max_land_use_years' => $result['land_years'], // 土地使用年限 'land_use_start_time' => $result['land_years1'], // 土地使用开始时间（年-月-日） 'land_use_end_time' => $result['land_years2'], // 土地使用结束时间（年-月-日） 'land_location' => $result['estate_address'], // 土地位置 'house_cert_img_ids' => $attachments, // 房产证照，对应附件表id，多张逗号分隔 'building_area' => $result['gfa'], // 建筑面积 'is_vacant' => $result['is_show_bus']==2?1:2, // 是否空置，1：是，2：否 'test_method' => 1, // 测试方法，1：比较法，2：收益法 'report_summary_img_ids' => $report_summary_img_ids?$report_summary_img_ids:'', // 报告摘要，对应附件表id，多张逗号分隔 'new_online_info' => '【房产详细资料】'.$result['msg_record'], // 最新查档信息 'grand_district_name' => $result['big_district'], 'small_district_name' => $result['small_district'], 'adjacent_property' => $result['near_estate'], 'bus_lines' => $result['bus_station'], 'boundaries' => $result['four_come'], 'pub_serv' => $result['config_facilities'], 'property_intro' => $result['hourse_special_illustration'], 'loc_img_ids' => $loc_img_ids?$loc_img_ids:'', 'remark' => $result['financial_remarks'], 'house_condition' => $house_condition// 房屋状况，1：期房，2：初次交易现房，3：多次交易现房，4：自建房 ]; Db::name('report_detail')->insert($report_detail_ins_data); /* 报告详情数据迁移结束 */ // 比较法数据迁移 $case = Db::name('old_app_report_case')->where(['app_report_id'=>$result['app_report_id']])->select(); if ($case) { array_splice($case, 0, 1); foreach ($case as $case_key=>$case_value) { if ($case_key==0) { $item = '案例一'; } elseif ($case_key==1) { $item = '案例二'; } elseif ($case_key==2) { $item = '案例三'; } else { $item = ''; } // 交易时间 $trade_date = ''; if (!empty($case_value['deal_date'])) { if (strpos($case_value['deal_date'], '年') !==false) { $trade_date = $case_value['deal_date']; } elseif (strpos($case_value['deal_date'], ' 12:00AM') !==false) { $date = explode(' ', $case_value['deal_date']); if (empty($date[1])) { array_splice($date, 1, 1); } $trade_date = $date[2].'年'.$date[0].'月'; } } $method_simple_ins_data = [ 'property_cert_info_id' => $property_cert_info_id, 'item' => $item, 'trade_date' => $trade_date, 'building_name' => $case_value['building_name'], 'size' => $case_value['gfa'], 'floor_number1' => $case_value['floors'], 'unit_type' => $result['hourse_type'], 'floor_number2' => '', 'price' => $case_value['unitprice'], 'transactions' => $case_value['deal_status'] ]; Db::name('method_simple')->insert($method_simple_ins_data); } } // 费用管理 pg_old_app_report.is_make=1 AND pg_old_app_report.is_audit=1 AND pg_old_app_report.is_finished=1; if ($result['is_make']==1 && $result['is_audit']==1 && $result['is_finished']==1) { // 评估目的code $assessment_purpose_id = 0; if ($inquiry_detail_data['purpose'] == '个人经营贷') { $assessment_purpose_id = 6; } elseif ($inquiry_detail_data['purpose'] == '中小企业') { $assessment_purpose_id = 5; } elseif ($inquiry_detail_data['purpose'] == '了解市场价值') { $assessment_purpose_id = 11; } elseif ($inquiry_detail_data['purpose'] == '二手房按揭贷款') { $assessment_purpose_id = 1; } elseif ($inquiry_detail_data['purpose'] == '小微授信业务') { $assessment_purpose_id = 7; } elseif ($inquiry_detail_data['purpose'] == '异地抵押贷款(云)') { $assessment_purpose_id = 10; } elseif ($inquiry_detail_data['purpose'] == '抵押贷款') { $assessment_purpose_id = 3; } elseif ($inquiry_detail_data['purpose'] == '抵押贷款(云)') { $assessment_purpose_id = 4; } elseif ($inquiry_detail_data['purpose'] == '押旧买新贷款') { $assessment_purpose_id = 2; } elseif ($inquiry_detail_data['purpose'] == '经营贷') { $assessment_purpose_id = 6; } elseif ($inquiry_detail_data['purpose'] == '零售') { $assessment_purpose_id = 9; } elseif ($inquiry_detail_data['purpose'] == '零售公司(小企业)') { $assessment_purpose_id = 8; } $charge_ins_data = [ 'order_no' => $order_no, // 询价单编号 'report_no'=> $report_no, // 报告编号 'report_code' => $e_case_code, // 报告电子提取码 'report_type' => 1, // 报告类型（业务类型），1：个贷（住宅），2：商贷（商业） 'city_id' => $city_id, // 城市id 'city' => $value['city_name'], // 城市名称 'building_name' => $value['building_name'].$value['building_no'].$value['house_name'], // 物业名称，多个物业用逗号隔开 'estate_num' => 1, // 物业数量 'estate_area' => $result['gfa'], // 面积，多个物业用'/'隔开 'assessment_purpose_id' => $assessment_purpose_id, // 评估目的字典id 'assessment_purpose' => $inquiry_detail_data['purpose'], // 评估目的 'collection_status' => 3, 'charge_status' => 3, 'is_confirm' => 2, 'receivable' => sprintf('%.2f', $result['lowest_charges']), // 应收金额 'received' => sprintf('%.2f', $result['lowest_charges']), 'confirm_amount' => sprintf('%.2f', $result['lowest_charges']), 'check_amount' => sprintf('%.2f', $result['lowest_charges']), 'uncollected_amount' => 0, 'report_completion_time' => $result['finish_date'], // 出报告时间日期，格式：2020-05-10 12:10:08 'salesman_id' => $user_id, // 业务员id 'salesman_name' => $user_name, // 业务员姓名 'department_id' => $department_id, // 业务员所属部门id 'department_name' => $department_name, // 业务员所属部门名称 'bank_id' => $bank_id, 'bank' => $bank_name, // 银行名称 'bank_branch_id' => $bank_branch_id, 'bank_branch' => $bank_branch_name, // 分行名称 'bank_sub_branch_id' => $bank_sub_id, 'bank_sub_branch' => $bank_sub_name, // 支行名称 'account_manager_name' => $result['cust_name'], // 客户经理姓名 'department_manager_id' => '', // 业务员所属部门的部门经理id 'department_manager_name' => '', // 业务员所属部门的部门经理姓名 'eva_unit_price' => $result['manu_unitprice'], // 评估单价，多个物业用'/'隔开 'assessment_total' => $result['manu_totalamo'], // 评估总值，多个物业用'/'隔开 'taxation_total_one' => $result['manu_tax_amo'], // 税费1合计，多个物业用'/'隔开 'taxation_total_two' => 0, // 税费2合计，多个物业用'/'隔开 'assessment_net_total_one' => $result['manu_net_amo'], // 评估净值1，多个物业用'/'隔开 'assessment_net_total_two' => $result['manu_net_amo2'], // 评估净值2，多个物业用'/'隔开 'loan_type' => 1, // 贷款类型，1：个贷，2：商贷 'inquiry_type' => 1, // 询价类型，1：住宅，2：商业 'inquiry_time' => $result['appraisal_date'], // 询价时间 'obligee' => $result['equity_name'], // 权利人，多套用'/'隔开 'property_cert' => $result['property_no'], // 房产证号，多套用'/'隔开 'purchase_date' => $result['registdate'], // 房产证登记日期，多套用'/'隔开 'is_housing_fund' => 0, // 是否公积金，1：是，0：否 'pay_type' => 2, // 收费方式，1：月结，2：个人 'business_type' => 1, // 业务类别，1：个贷，2：对公 'report_source' => 2, // 报告来源，1：新系统，2：老系统 'is_simple'=> $result['report_type']==2?1:0, // 是否简易报告，1：是，0：否 'simple_collection_status' => 3, 'survey_username_id' => $survey_user_id, 'survey_user_name' => $survey_user_name, 'create_time' => date('Y-m-d H:i:s'), 'update_time' => date('Y-m-d H:i:s') ]; $charge_insert_result = Db::name('charge')->insert($charge_ins_data); if (!$charge_insert_result) { Db::rollback(); } } } else { Db::rollback(); } } /* 报告数据迁移结束 */ } } else { Db::rollback(); } } Db::commit(); } $end = time();// 总耗时 $total_time = $end - $start; file_put_contents('migrate.log', PHP_EOL.'迁移时间：'.$migrate_time.PHP_EOL.'迁移内容：单套住宅数据迁移'.PHP_EOL.'数据记录数：'.$count.PHP_EOL.'总耗时(单位：秒)：'.$total_time.PHP_EOL, FILE_APPEND); return $this->buildSuccess('', '单套住宅数据迁移成功'); } /** * 商业询价数据迁移（查勘和报告都在线下操作完成） */ public function businessMigrate() { set_time_limit(0); $start_time = time(); $migrate_time = date('Y-m-d H:i:s'); $map[] = ['shop_id', '<>', 0]; $map[] = ['shop_name', 'not null', '']; $map[] = ['shop_name', '<>', '']; $map[] = ['is_appraisal', '<>', 0]; $map[] = ['appraisal_date', 'between time', ['2019-07-01 00:00:00', '2020-01-01 00:00:00']]; $count = Db::name('old_business')->where($map)->count(); for ($i=0;$iwhere($map) ->order('appraisal_date asc') ->limit($i * 10, 10) ->select(); Db::startTrans(); foreach ($business_data as $key=>$value) { // 询价单号 $order_no = self::INQUERY_NUMBER_RESIDENCE_PREFIX.$value['shop_id']; // 业务员信息 $saleman_info = $this->getNewUserId($value['sale_man']); if ($saleman_info) { $user_id = $saleman_info['id']; $user_name = $saleman_info['name']; $user_phone = $saleman_info['tel']; $department_id = $saleman_info['deptid']; $department_name = $saleman_info['deptname']; } else { $user_id = 0; $user_name = $value['sale_man']; $user_phone = ''; $department_id = 0; $department_name = ''; } // 预估单说明 $estimated_desc = Db::name('old_business_make')->where(['make_id'=>$value['shop_id']])->field('instructions')->find(); $estimated_desc = $estimated_desc?$estimated_desc['instructions']:''; // 询价类型 $type = 2; $status = $value['is_appraisal']==0?1:2; // 签章 $seal_status = $value['is_signs']==1?2:1; if ($seal_status==2) { // 预估单号 if (!empty($value['shop_no'])) { $shop_no = explode('X', $value['shop_no']); $estimated_no = $shop_no[0].'-X'.$shop_no[1]; } else { $estimated_no = NULL; } $sign_data = Db::name('old_business_sign')->where(['business_id'=>$value['shop_id']])->order('sign_time desc')->find(); if ($sign_data) { $sign_user_id = Db::name('valuer')->where(['name'=>$sign_data['sign_name']])->find(); $appraiser_ids = $sign_user_id['id']; $seal_time = $sign_data['sign_time']; } else { $appraiser_ids = NULL; $seal_time = NULL; } } else { $estimated_no = NULL; $appraiser_ids = NULL; $seal_time = NULL; } // 银行 $bank_branch = Db::name('bank')->where(['bank_id'=>$value['branch_id'],'type'=>3])->find(); $bank_sub = Db::name('bank')->where(['id'=>$bank_branch['pid'],'type'=>2])->find(); $bank = Db::name('bank')->where(['id'=>$bank_sub['pid'],'type'=>1])->find(); if ($bank_branch) { $bank_id = $bank['id']; $bank_name = $bank['name']; $bank_branch_id = $bank_sub['id']; $bank_branch_name = $bank_sub['name']; $bank_sub_id = $bank_branch['id']; $bank_sub_name = $bank_branch['name']; } else { $bank_id = ''; $bank_name = ''; $bank_branch_id = ''; $bank_branch_name = $value['bank_sub_short_name']; $bank_sub_id = ''; $bank_sub_name = $value['branch_short_name']; } // 客户经理 $customer_data = Db::name('account_manager')->where(['name'=>$value['cust_name']])->find(); if ($customer_data) { $bank_customer_mgr_id = $customer_data['id']; $bank_customer_mgr_name = $customer_data['name']; $bank_customer_mgr_phone = $customer_data['mobile']; } else { $bank_customer_mgr_id = ''; $bank_customer_mgr_name = $value['cust_name']; $bank_customer_mgr_phone = ''; } $inquiry_ins_data = [ 'order_no' => $order_no, 'user_id' => $user_id, 'user_name' => $user_name, 'user_phone' => $user_phone, 'bank_id' => $bank_id, 'bank_name' => $bank_name, 'bank_branch_id' => $bank_branch_id, 'bank_branch_name' => $bank_branch_name, 'bank_sub_id' => $bank_sub_id, 'bank_sub_name' => $bank_sub_name, 'bank_customer_mgr_id' => $bank_customer_mgr_id, 'bank_customer_mgr_name' => $bank_customer_mgr_name, 'bank_customer_mgr_phone' => $bank_customer_mgr_phone, 'eva_purpose' => $value['street_type'], 'type' => $type, 'status' => $status, 'seal_status' => $seal_status, 'appraiser_ids' => $appraiser_ids, 'department_id' => $department_id, 'department_name' => $department_name, 'estimated_no' => $estimated_no, 'estimated_desc' => $estimated_desc, 'create_time' => $value['appraisal_date'], 'update_time' => $value['manu_appdate'], 'seal_time' => $seal_time ]; $inquiry_insert_result = Db::name('inquiry')->insert($inquiry_ins_data); if ($inquiry_insert_result) { $quot_id = Db::name('inquiry')->getLastInsID(); /* 签章历史表数据迁移 */ $sign_data_migrate = Db::name('old_business_sign')->where(['business_id'=>$value['shop_id']])->select(); if ($sign_data_migrate) { foreach ($sign_data_migrate as $k=>$v) { $signature_record_ins_data = [ 'quot_id' => $quot_id, 'name' => $v['sign_name'], 'create_time' => strtotime($v['sign_time']) ]; $signature_record_insert_result = Db::name('signature_record')->insert($signature_record_ins_data); if (!$signature_record_insert_result) { Db::rollback(); } } } /* 询价详情表数据迁移 */ $sz_district_arr = ['龙岗区', '罗湖区', '南山区', '福田区', '宝安区', '盐田区', '龙华区']; if ($value['city_name'] == NULL && in_array($value['district_name'], $sz_district_arr)) { $value['city_name'] = '深圳'; } $sz_short_district_arr = ['龙岗', '罗湖', '南山', '福田', '宝安', '盐田', '龙华']; if ($value['city_name'] == '' && in_array($value['district_name'], $sz_short_district_arr)) { $value['city_name'] = '深圳'; } if ($value['city_name'] == '深圳') { $city_id = '440300'; } elseif ($value['city_name'] == '北京') { $city_id = '110100'; } elseif ($value['city_name'] == '武汉') { $city_id = '420100'; } else { $city_id_result = Db::name('region')->where(['shortname'=>$value['city_name']])->field('id')->find(); if ($city_id_result) { $city_id = $city_id_result['id']; } else { $city_id = NULL; } } // 是否满两年 if ($value['buy_years'] > 2) { $is_tran_tax_free = 1; } else { $is_tran_tax_free = 2; } if ($value['equity'] == '个人') { $ownership_type = 1; } elseif ($value['equity'] == '企业') { $ownership_type = 2; } else { $ownership_type = NULL; } if ($value['attached']) { $value['attached'] = $this->picHandle($value['attached']); } // 物业用途，由于新系统没有商铺、酒店、仓库、会议、配电房、产业研发用房等物业用途，默认值为NULL if ($value['usage'] == '仓储[物流]' ) { $value['usage'] = '仓储(物流)'; } if ($value['usage'] == '工业厂房（生产研发）' ) { $value['usage'] = '工业厂房(生产研发)'; } $usage_code = getDictionaryCode('HOUSE_USAGE', $value['usage']); if ($usage_code) { $usage = $usage_code['code']; } else { $usage = NULL; } // 登记日期处理 $value['registrationdate'] = preg_replace('/\s/', '', $value['registrationdate']); $register_time_arr = explode('-', $value['registrationdate']); if (count($register_time_arr)==3 && is_numeric($register_time_arr[0]) && is_numeric($register_time_arr[1]) && is_numeric($register_time_arr[2]) && $register_time_arr[0]>999 && $register_time_arr[0]<9999 && checkdate($register_time_arr[1], $register_time_arr[2], $register_time_arr[0])) { $purchase_date = $value['registrationdate']; } else { $purchase_date = NULL; } $inquiry_detail_ins_data = [ 'quot_id' => $quot_id, 'city' => $value['city_name'], 'city_id' => $city_id, 'property_full_name' => $value['shop_name'], 'building_name' => $value['shop_name'], 'size' => $value['gfa'], 'reg_price' => $value['original_price'], 'is_tran_tax_free' => $is_tran_tax_free, 'ownership_type' => $ownership_type, 'attachments' => $value['attached'], 'remark' => $value['remark'], 'usage' => $usage, 'evaluated_unit_price' => $value['manu_unitprice'], 'purchase_date' => $purchase_date, 'create_time' => $value['appraisal_date'], 'update_time' => $value['manu_appdate'] ]; $inquiry_detail_insert_result = Db::name('property_cert_info')->insert($inquiry_detail_ins_data); if (!$inquiry_detail_insert_result) { Db::rollback(); } else { if ($value['is_appraisal'] > 0) { $property_cert_info_id = Db::name('property_cert_info')->getLastInsID(); /* 回价历史数据迁移 */ $record_data = Db::name('old_businessrecords')->where(['shopid'=>$value['shop_id']])->select(); if ($record_data) { $return_price_record_ins_data = [ 'property_cert_info_id' => $property_cert_info_id, 'property_name' => $value['shop_name'] ]; foreach ($record_data as $ke=>$va) { $arr = explode('--', $va['records']); if ($arr[2]!=$value['manu_unitprice']) { $return_price_record_ins_data['area'] = sprintf('%.2f', $arr[1]); $return_price_record_ins_data['eva_unit_price'] = sprintf('%.2f', $arr[2]); $return_price_record_ins_data['eva_total_value'] = sprintf('%.2f', $return_price_record_ins_data['area'] * $return_price_record_ins_data['eva_unit_price']); $return_price_record_ins_data['total_taxes1'] = sprintf('%.2f', $arr[3]); $return_price_record_ins_data['eva_total_value'] = $return_price_record_ins_data['eva_total_value'] - $return_price_record_ins_data['total_taxes1']; // 回价修改人信息 $record_appraiser_info = $this->getNewUserId($va['operation']); if ($record_appraiser_info) { $record_appraiser_id = $record_appraiser_info['id']; $record_appraiser_name = $record_appraiser_info['name']; } else { $record_appraiser_id = NULL; $record_appraiser_name = $va['operation']; } $return_price_record_ins_data['appraiser_id'] = $record_appraiser_id; $return_price_record_ins_data['appraiser_name'] = $record_appraiser_name; $return_price_record_ins_data['create_time'] = strtotime($va['businessrecordsdate']); Db::name('return_price')->insert($return_price_record_ins_data); } } } /* 回价表数据迁移 */ $appraiser_info = $this->getNewUserId($value['manu_user_name']); if ($appraiser_info) { $appraiser_id = $appraiser_info['id']; $appraiser_name = $appraiser_info['name']; } else { $appraiser_id = NULL; $appraiser_name = $value['manu_user_name']; } $return_price_ins_data = [ 'property_cert_info_id' => $property_cert_info_id, 'property_name' => $value['shop_name'], 'area' => sprintf('%.2f', $value['gfa']), 'eva_unit_price' => sprintf('%.2f', $value['manu_unitprice']), 'eva_total_value' => sprintf('%.2f', $value['manu_totalamo']), 'eva_total_value' => sprintf('%.2f', $value['manu_netamo']), 'eva_net_value2' => sprintf('%.2f', $value['manu_netamo1']), 'corporate_income_tax' => 0, 'tran_service_fee' => sprintf('%.2f', $value['taxvalue6']), 'edu_surcharge' => 0, 'urban_construction_tax' => sprintf('%.2f', $value['taxvalue2']), 'deed_tax' => sprintf('%.2f', $value['taxvalue7']), 'stamp_duty' => sprintf('%.2f', $value['taxvalue3']), 'added_tax' => sprintf('%.2f', $value['taxvalue1']), 'land_value_added_tax' => sprintf('%.2f', $value['taxvalue4']), 'personal_income_tax' => sprintf('%.2f', $value['taxvalue5']), 'auction_fee' => 0, 'total_taxes1' => sprintf('%.2f', $value['manu_taxamo']), 'total_taxes2' => 0, 'internal_remarks' => $value['remark'], 'external_remarks' => $value['remarkinner'], 'appraiser_id' => $appraiser_id, 'appraiser_name' => $appraiser_name, 'create_time' => strtotime($value['manu_appdate']) ]; Db::name('return_price')->insert($return_price_ins_data); } } } else { Db::rollback(); } } Db::commit(); } $end_time = time(); // 总耗时 $total_time = $end_time - $start_time; file_put_contents('migrate.log', PHP_EOL.'迁移时间：'.$migrate_time.PHP_EOL.'迁移内容：商业询价数据迁移'.PHP_EOL.'数据记录数：'.$count.PHP_EOL.'总耗时(单位：秒)：'.$total_time.PHP_EOL, FILE_APPEND); return $this->buildSuccess('', '商业询价数据迁移成功'); } /** * 获取OA权限系统用户id、电话、所属部门id、部门名称 * @param $name * @return array */ public function getNewUserId($name) { $name = preg_replace('/[0-9a-zA-Z]+/', '', $name); $map[] = ['name', 'like', '%'.$name.'%']; // $map[] = ['deptid', 'in', $this->getDepartmentId()]; $map[] = ['deptid', 'in', [6,45,48,76,207,191]]; return Db::name('oa_admin')->where($map)->order(['id'=>'desc'])->find(); } /** * 获取房产评估事业部下属所有部门id */ public function getDepartmentId() { $department_id = getDictionaryCode('DEPARTMENT', '房产评估事业部'); if (!$department_id) { return $this->buildFailed('查无数据，请确认字典数据表是否设置业务部相关数据'); } $data = [ 'departIds' => $department_id['code'] ]; $Auth = new AuthApi(); $result = $Auth->getBusinessDepartment($data); $result = json_decode($result,true); $arr = []; foreach ($result['data'] as $key=>$value) { $arr[] = $value['key']; foreach ($value['children'] as $ke=>$va) { $arr[] = $va['key']; foreach ($va['children'] as $k=>$v) { $arr[] = $v['key']; } } } return $arr; } /** * 旧系统图片处理 * @param $pic_path * @return int|string */ public function picHandle($pic_path) { $pic_path = trim($pic_path, ' '); if (empty($pic_path)) { return 0; } // 获取图片后缀 $suffix_arr = explode('.', $pic_path); $ext = $suffix_arr[1]; // 获取图片文件名 $file_name_arr = explode('/', $pic_path); $file_name = array_pop($file_name_arr); $ins_data = [ 'name' => $file_name, 'savename' => $file_name, 'path' => '', 'url' => $pic_path, 'ext' => $ext ]; $insert_result = Db::name('attachment')->insert($ins_data); if ($insert_result) { return Db::name('attachment')->getLastInsID(); } else { return 0; } } public function surveyCode($str, $type, $delimiter) { $code = ''; $str_arr = explode($delimiter, trim($str, $delimiter)); foreach ($str_arr as $key=>$value) { $code_data = Db::name('options_automation')->where(['key'=>$type,'name'=>$value])->field('code')->find(); if ($code_data) { $code .= ','.$code_data['code']; } } $code = trim($code, ','); return $code; } }