127 lines
4.6 KiB
PHP
127 lines
4.6 KiB
PHP
<?php
|
|
|
|
namespace app\admin\controller;
|
|
|
|
use app\model\Estimate as EstimateModel;
|
|
use think\Db;
|
|
|
|
class Finance extends Base{
|
|
public function exportFinanceExcel()
|
|
{
|
|
$start_date = input('start_date');
|
|
$end_date = input('end_date');
|
|
|
|
$dateInterval = date_diff(date_create($end_date), date_create($start_date));
|
|
$diff_month = $dateInterval->m;
|
|
$diff_day = $dateInterval->d;
|
|
|
|
if ($diff_month > 6 || ($diff_month == 6 && $diff_day > 0)) {
|
|
return $this->buildFailed('日期范围不能超过6个月');
|
|
}
|
|
|
|
Db::execute("SET @row_num = 0");
|
|
$list = Db::name('property_cert_info')->alias('pci')
|
|
->field("
|
|
CAST(@row_num := @row_num + 1 AS CHAR) AS row_num,
|
|
r.create_reportNo_time as create_reportno_time,
|
|
'国中' as estate_eva_institution,
|
|
i.buss_username,
|
|
IFNULL(rd.client, '') as client,
|
|
IFNULL(i.loan_type, '') as loan_type,
|
|
CONCAT(i.bank_name, i.bank_branch_name, i.bank_sub_name) as bank_full_name,
|
|
pci.property_full_name,
|
|
i.eva_purpose,
|
|
CASE WHEN i.eva_purpose = '抵押' THEN '抵押价值'
|
|
ELSE '市场价值' END AS eva_type,
|
|
IFNULL(r.valuation_time, '') as valuation_time,
|
|
r.report_no,
|
|
pci.size,
|
|
pci.eva_unit_price,
|
|
ROUND(pci.eva_total_value/10000, 2) as eva_total_value,
|
|
CASE WHEN pci.usage = 1 THEN '住宅'
|
|
WHEN pci.usage = 2 THEN '商业'
|
|
WHEN pci.usage = 3 THEN '办公'
|
|
WHEN pci.usage = 4 THEN '厂房'
|
|
WHEN pci.usage = 5 THEN '土地'
|
|
WHEN pci.usage = 6 THEN '商务公寓'
|
|
ELSE '其他' END AS estate_usage,
|
|
'王晨阳' as project_leader,
|
|
pci.appraiser_name,
|
|
FROM_UNIXTIME(r.completion_time, '%Y-%d-%m') as completion_time,
|
|
CAST(COUNT(DISTINCT pci2.id) as CHAR) as estate_count,
|
|
IFNULL(pci.city, '') as city,
|
|
IFNULL(rd.area, '') as area,
|
|
CONCAT(IFNULL(pci.land_location, ''), pci.property_full_name) as address,
|
|
CASE WHEN rd.test_method = 1 THEN '比较法'
|
|
ELSE '收益法' END AS test_method
|
|
")
|
|
->leftJoin("pg_inquiry i", "i.id = pci.quot_id")
|
|
->leftJoin("pg_report r", "r.quot_id = i.id")
|
|
->leftJoin("pg_report_detail rd", "rd.report_id = r.id")
|
|
->leftJoin("pg_property_cert_info pci2", "i.id = pci2.quot_id")
|
|
->where("r.completion_time >= UNIX_TIMESTAMP('{$start_date} 00:00:00') and r.completion_time <= UNIX_TIMESTAMP('{$end_date} 23:59:59')")
|
|
->where('i.status = 8')
|
|
->order("r.completion_time asc")
|
|
->group('pci.id')
|
|
->select();
|
|
if (empty($list)) {
|
|
return $this->buildFailed('没有数据可以导出');
|
|
}
|
|
$indexKey = [ //
|
|
'row_num',
|
|
'create_reportno_time',
|
|
'estate_eva_institution',
|
|
'buss_username',
|
|
'client',
|
|
'loan_type',
|
|
'bank_full_name',
|
|
'property_full_name',
|
|
'eva_purpose',
|
|
'eva_type',
|
|
'valuation_time',
|
|
'report_no',
|
|
'size',
|
|
'eva_unit_price',
|
|
'eva_total_value',
|
|
'estate_usage',
|
|
'project_leader',
|
|
'appraiser_name',
|
|
'completion_time',
|
|
'estate_count',
|
|
'city',
|
|
'area',
|
|
'address',
|
|
'test_method',
|
|
];
|
|
$indexValue = [
|
|
'序号',
|
|
'日期',
|
|
'房地产估价机构',
|
|
'业务员',
|
|
'估价委托人/银行',
|
|
'业务来源/业务条线',
|
|
'支行',
|
|
'估价项目',
|
|
'估价目的',
|
|
'价值类型',
|
|
'价值时点',
|
|
'估价报告编号',
|
|
'项目总建筑面积(平方米)',
|
|
'估价对象单价(元/平方米)',
|
|
'项目总价值(万元)',
|
|
'房屋用途',
|
|
'项目负责人',
|
|
'签字估价师',
|
|
'项目完成时间(出报告日期)',
|
|
'本报告的估价对象个数',
|
|
'估价对象所在城市',
|
|
'估价对象所在行政区',
|
|
'估价对象详细地址(跟估价项目)',
|
|
'估价方法',
|
|
];
|
|
|
|
return $this->buildSuccess([
|
|
'url' => exportExcel($list, 'finance_'.date('YmdHis'), $indexKey, $indexValue),
|
|
]);
|
|
}
|
|
} |