当前位置：首页 > news >正文

【fastadmin开发实战】财务数据快速导入系统（复制导入）

news 2025/5/10 22:58:50

项目场景：

需要把各个楼层、各个店铺、各个年份的年度应收、已收、欠款，导入系统，最后提供数据api给图表，实现数据的可视化。

难点及解决方案：

1、excel表格记录，数据超级多，而且数据不规范，但是表格格式相对统一，可以采取复制导入的方式，相对于excel导入更灵活一些。

2、项目基于fastadmin框架；

实施步骤：

1、数据库建表

DROP TABLE IF EXISTS `fa_ffep_huikuan`;
CREATE TABLE `fa_ffep_huikuan` (`id` int(11) NOT NULL AUTO_INCREMENT,`finance_code` varchar(255) DEFAULT NULL COMMENT '财务编号',`company` varchar(255) DEFAULT NULL COMMENT '承租方',`linkman` varchar(255) DEFAULT NULL COMMENT '委托代表人',`owner` varchar(255) DEFAULT NULL COMMENT '责任人',`brand` varchar(255) DEFAULT NULL COMMENT '品牌',`floor` varchar(255) DEFAULT NULL COMMENT '楼层',`booth` varchar(255) DEFAULT NULL COMMENT '展位号',`year_month` varchar(255) DEFAULT NULL COMMENT '年月',`yingshou` decimal(10,2) DEFAULT NULL COMMENT '应收',`yishou` decimal(10,2) DEFAULT NULL COMMENT '已收',`qiankuan` decimal(10,2) DEFAULT NULL COMMENT '欠款',`createtime` int(11) DEFAULT NULL COMMENT '创建时间',`updatetime` int(11) DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`id`),UNIQUE KEY `ym` (`year_month`,`finance_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6458 DEFAULT CHARSET=utf8mb4 COMMENT='应收表';SET FOREIGN_KEY_CHECKS = 1;

2、导入页面

<form id="add-form" class="form-horizontal" role="form" data-toggle="validator" method="POST" action=""><div class="form-group"><label for="c-avatar" class="control-label col-xs-12 col-sm-2">字段:</label><div class="col-xs-12 col-sm-8"><input id="c-field" data-rule="required" data-source="ffep/huikuan/field"data-multiple="true"  class="form-control selectpage" name="field" type="text" value=""><span><label>导入的顺序必须严格遵循字段顺序！！！</label></span></div></div><div class="form-group"><label class="control-label col-xs-12 col-sm-2">{:__('批量导入')}:</label><div class="col-xs-12 col-sm-8"><!--<input id="c-shorturl" class="form-control" name="row[shorturl]" type="text" value="">--><textarea class="form-control" name="imports" id=imports rows="18" cols="40"></textarea></div></div><span class="alert-danger description-block">请仔细核对年份和楼层信息，导错了只能删除重新导入！</span><div class="form-group"><label class="control-label col-xs-12 col-sm-2">{:__('年份')}:</label><div class="col-xs-12 col-sm-8"><input id="c-year_month" class="form-control datetimepicker" data-date-format="YYYY" data-use-current="true" name="year_month" type="text" value="{:date('Y-m-d H:i:s')}"></div></div><div class="form-group"><label class="control-label col-xs-12 col-sm-2">{:__('floor')}:</label><div class="col-xs-12 col-sm-8">{:Form::selectpicker('floor', $floorArr, ['data-rule'=>'required'])}<span><label></label></span></div></div><div class="form-group layer-footer"><label class="control-label col-xs-12 col-sm-2"></label><div class="col-xs-12 col-sm-8"><button type="submit" class="btn btn-primary btn-embossed disabled">{:__('OK')}</button></div></div>
</form>

index页面，主要增加了tab左右两边的选项卡

index的html代码

<div class="panel panel-default panel-intro"><div class="panel-heading">{:build_heading(null,FALSE)}<ul class="nav nav-tabs" data-field="floor"><li class="active"><a href="#t-all" data-value="" data-toggle="tab">{:__('All')}</a></li>{foreach name="$floorArr" item="vo"}<li><a href="#t-{$key}" data-value="{$key}" data-toggle="tab">{$vo}</a></li> {/foreach}<li class="pull-right dropdown filter-type"><a href="javascript:" class="dropdown-toggle" data-toggle="dropdown"><i class="fa fa-filter"></i> {:__('选择年份')}</a><ul class="dropdown-menu text-left" role="menu"><li class="active"><a href="javascript:" data-value="">{:__('All')}</a></li>{foreach name="yearList" id="item"}<li><a href="javascript:" data-value="{$key}">{$item}</a></li>{/foreach}</ul></li></ul></div><div class="panel-body"><div id="myTabContent" class="tab-content"><div class="tab-pane fade active in" id="one"><div class="widget-body no-padding"><div id="toolbar" class="toolbar"><a href="javascript:;" class="btn btn-primary btn-refresh" title="{:__('Refresh')}" ><i class="fa fa-refresh"></i> </a><a href="javascript:;" class="btn btn-success btn-add {:$auth->check('ffep/huikuan/add')?'':'hide'}" title="{:__('Add')}" ><i class="fa fa-plus"></i> {:__('Add')}</a><a href="javascript:;" class="btn btn-success btn-edit btn-disabled disabled {:$auth->check('ffep/huikuan/edit')?'':'hide'}" title="{:__('Edit')}" ><i class="fa fa-pencil"></i> {:__('Edit')}</a><a href="javascript:;" class="btn btn-danger btn-del btn-disabled disabled {:$auth->check('ffep/huikuan/del')?'':'hide'}" title="{:__('Delete')}" ><i class="fa fa-trash"></i> {:__('Delete')}</a><div class="dropdown btn-group {:$auth->check('ffep/huikuan/multi')?'':'hide'}"><a class="btn btn-primary btn-more dropdown-toggle btn-disabled disabled" data-toggle="dropdown"><i class="fa fa-cog"></i> {:__('More')}</a><ul class="dropdown-menu text-left" role="menu"><li><a class="btn btn-link btn-multi btn-disabled disabled" href="javascript:;" data-params="status=normal"><i class="fa fa-eye"></i> {:__('Set to normal')}</a></li><li><a class="btn btn-link btn-multi btn-disabled disabled" href="javascript:;" data-params="status=hidden"><i class="fa fa-eye-slash"></i> {:__('Set to hidden')}</a></li></ul></div></div><table id="table" class="table table-striped table-bordered table-hover table-nowrap"data-operate-edit="{:$auth->check('ffep/huikuan/edit')}"data-operate-del="{:$auth->check('ffep/huikuan/del')}"width="100%"></table></div></div></div></div>
</div>

tab选项卡的js部分

            // 绑定过滤事件$('.filter-type ul li a', table.closest(".panel-intro")).on('click', function (e) {$(this).closest("ul").find("li").removeClass("active");$(this).closest("li").addClass("active");var field = 'year_month';var value = $(this).data("value") || '';var object = $("[name='" + field + "']", table.closest(".bootstrap-table").find(".commonsearch-table"));if (object.prop('tagName') == "SELECT") {$("option[value='" + value + "']", object).prop("selected", true);} else {object.val(value);}table.trigger("uncheckbox");table.bootstrapTable('refresh', {pageNumber: 1});});

3、后端处理逻辑

public function add(){if ($this->request->isGet()) {return parent::add();}if ($this->request->isPost()) {$params = $this->request->param();$year_month = trim($params['year_month'] ?? '');$floor = trim($params['floor'] ?? '');// 校验必填参数if (empty($year_month) || empty($floor)) {$this->error('年份月份（year_month）和楼层（floor）为必填项');}$importData = trim($params['imports'] ?? '');$fields = explode(',', $params['field'] ?? ''); // 字段顺序：finance_code,brand,booth,yingshou,yishou,qiankuan$failarr = [];$insertAll = [];// 解析数据行$rows = explode("\n", $importData);foreach ($rows as $rowNum => $rowData) {$rowData = trim($rowData);if (empty($rowData)) {continue; // 跳过空行}$columns = preg_split('/\t+/', $rowData); // 处理多制表符分隔if (count($columns) < count($fields)) {$failarr[] = ['row'    => $rowNum + 1,'error'  => "字段数量不足（期望 " . count($fields) . " 个，实际 " . count($columns) . " 个）",'raw'    => $rowData];continue;}$dataItem = ['year_month'    => $year_month,'floor'         => $floor,'updatetime'    => time(),];// 按字段赋值并清洗foreach ($fields as $colIndex => $field) {$value = trim($columns[$colIndex]);switch ($field) {case 'finance_code':$dataItem[$field] = strtoupper($value); // 统一大写（可选）break;case 'booth':$dataItem[$field] = str_replace('、', ',', $value); // 展位分隔符统一break;case 'yingshou':case 'yishou':case 'qiankuan':$cleanAmount = str_replace([' ', '　', '–', '—', '¥', ','], '', $value);$dataItem[$field] = $cleanAmount === '-' ? 0.00 : (float)$cleanAmount;break;default:$dataItem[$field] = $value;}}// 校验基础字段非空（finance_code 必须存在）if (empty($dataItem['finance_code'])) {$failarr[] = ['row'    => $rowNum + 1,'error'  => '财务编号（finance_code）不能为空','raw'    => $rowData];continue;}$insertAll[] = $dataItem;}// 使用 onDuplicate 更新重复记录（ThinkPHP 6.0+ 支持）$saveResult = $this->model->insertAll($insertAll, ['on_duplicate' => ['brand',        // 更新品牌'booth',        // 更新展位'yingshou',     // 更新应收'yishou',       // 更新已收'qiankuan',     // 更新欠款'floor',        // 更新楼层（可选，根据业务是否允许修改）]]);// $this->model->commit();$this->success("处理完成：成功 " . count($insertAll) . " 条（含更新）",'',['fail' => $failarr]);}}