<?php
namespace app\admin\controller;
use think\App;
use think\Db;
use think\Exception;
class Owner extends Auth
{
public function __construct(App $app = null) {
parent::__construct($app);
$this->model= new \app\common\model\Owner();
$this->table= $this->model->table;
}
public function index($import_id=0){
if(request()->isAjax()){
//分页参数
$length = input('rows',10,'intval'); //每页条数
$page = input('page',1,'intval'); //第几页
$start = ($page - 1) * $length; //分页开始位置
//排序
$sortRow = input('sidx','id','trim'); //排序列
$sort = input('sord','desc','trim'); //排序方式
$order = $sortRow.' '.$sort;
$title = input('name','','trim');
if($title){
$map[] = ['name','like','%'.$title.'%'];
}
$title1 = input('phone','','trim');
if($title1){
$map[] = ['phone','like','%'.$title1.'%'];
}
$title2 = input('card','','trim');
if($title2){
$map[] = ['card','like','%'.$title2.'%'];
}
$enable = input('enable','','trim');
if($enable != ''){
$map[] = ['enable','=',$enable];
}
if($import_id >0){
$map[] = ['import_log_id','=',$import_id];
}else{
$map[] = ['del','=',0];
}
$map[] = ['org_id','=',$this->orgId];
$map= empty($map) ? true: $map;
//数据查询
$lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
foreach ($lists as $k=>$v){
$lists[$k]['is_show_option'] =$import_id>0?false:true;
}
//数据返回
$totalCount = db($this->table)->where($map)->count();
$totalPage = ceil($totalCount/$length);
$result['page'] = $page;
$result['total'] = $totalPage;
$result['records'] = $totalCount;
$result['rows'] = $lists;
return json($result);
}else{
$logInfo = [];
if($import_id>0){
$logInfo = Db::name('import_log')
->where('id',$import_id)
->find();
}
$this->assign('import_id',$import_id);
$this->assign('importInfo',$logInfo);
$this->assign('m_name','业主');
return $this->fetch();
}
}
/**
* 新增/编辑
*/
public function add($id=0){
if(request()->isPost()){
$res = $this->model->updates();
if($res){
$this->success('操作成功',url('index'));
}else{
$this->error($this->model->getError());
}
}else{
if($id){
$info =db($this->table)->where('id',$id)->find();
$this->assign('info',$info);
}
return $this->fetch();
}
}
/**
* 删除记录
* @param int $id
*/
public function del($id=0){
if(!$id){
$this->error('参数错误');
}
$res = db($this->table)->where('id',$id)->setField('del',1);
if($res){
$this->success('删除成功');
}else{
$this->error('删除失败');
}
}
/**
* 改变字段值
* @param int $fv
* @param string $fn
* @param int $fv
*/
public function changeField($id=0,$fn='',$fv=0){
if(!$fn||!$id){
$this->error('参数错误');
}
$res = db($this->table)->where('id',$id)->setField($fn,$fv);
if($res){
$this->success('操作成功');
}else{
$this->error('操作失败');
}
}
// excel
public function import(){
return $this->fetch();
}
/**
* 下载模板
*/
public function downloadtem(){
include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
set_time_limit(0);
ini_set("memory_limit","512M");
$fileName = '业主列表.xlsx';
$excel = new \PHPExcel();
$sheet = $excel->setActiveSheetIndex(0);
$arr = array('A','B','C','D','E');
foreach($arr as $k=>$v){
$excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(true);//换行
$excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
$excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
}
$sheet->setCellValueByColumnAndRow(0,1,'姓名');
$sheet->setCellValueByColumnAndRow(1,1,'联系电话');
$sheet->setCellValueByColumnAndRow(2,1,'身份证号');
$sheet->setCellValueByColumnAndRow(3,1,'预付款');
$sheet->setCellValueByColumnAndRow(4,1,'类型(业主/租户)');
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output'); //文件通过浏览器下载
}
/**
* 导入
*/
public function importexcel(){
include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
set_time_limit(0);
ini_set("memory_limit", -1);
ob_flush();//清空缓存
flush();//刷新缓存
$orgid = cur_org_id();
$file = request()->file('file');
if(!$file){
exit('请上传文件');
}
// 移动到框架应用根目录/uploads/ 目录下
$info = $file->validate([ 'size'=>config('app.max_upload_file_size') ])
->move(env('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads'. DIRECTORY_SEPARATOR . 'files');
if(!$info){
exit('上传文件失败');
}
$img = env('root_path').'/public/uploads/files/' . $info->getSaveName();
$finfo = $info->getInfo();
$fileExt = explode('.',$finfo['name']);
$finfo['file_name'] = $finfo['name'];
$filepath = str_replace('\\', '/', $img);
$file_type = $fileExt[count($fileExt)-1];
if($file_type == 'xls'){
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
}else{
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
}
$imData = [
'org_id'=>$orgid,
'title'=>$finfo['file_name'],
'type'=>2,
'create_time'=>getTime(),
];
$importId =Db::name('import_log')->insertGetId($imData);
$objPHPExcel = $objReader->load($filepath,$encode='utf-8');
$table = array(0,1,2,3,4);
$allobjWorksheets = $objPHPExcel->getAllSheets();
try{
@unlink($filepath);
foreach($allobjWorksheets as $key=>$objWorksheet){
$highestRow = $objWorksheet->getHighestRow();
$success = 0;
$error = 0;
$errorMsg = [];
for ($row = 2; $row <= $highestRow; ++$row) {
$oneData = array();
for ($col = 0; $col < count($table); ++$col) {
$cell = $objWorksheet->getCellByColumnAndRow($col, $row);
$value = (string)$cell->getValue();
$oneData[$table[$col]]= $value;
}
if(!$oneData[0]||!$oneData[1] ||!$oneData[2]){
$msg = "第".$row."行，数据存在空值，未导入";
echo "<font color=\"red\">".$msg."</font><br />";
$error++;
$errorMsg[] = $msg;
continue;
}
if($oneData[4]){
if(!in_array($oneData[4],['业主','租户'])){
$msg = "第".$row."行，类型错误，未导入";
echo "<font color=\"red\">".$msg."</font><br />";
$error++;
$errorMsg[] = $msg;
continue;
}
$type = $oneData[4]=='业主'?1:2;
}else{
$type = 1;
}
$check = Db::name('owner')
->where('card',$oneData[2])
->where('del',0)
->where('org_id',$orgid)
->find();
if($check){
$msg = "第".$row."行，姓名<".$oneData[0].">已存在，未导入";
echo "<font color=\"red\">".$msg."</font><br />";
$error++;
$errorMsg[] = $msg;
continue;
}
if($oneData[3]){
if(!is_numeric($oneData[3])){
$msg = "第".$row."行，预存款字段数据格式错误(只能为纯数字)，未导入";
echo "<font color=\"red\">".$msg."</font><br />";
$error++;
$errorMsg[] = $msg;
continue;
}
$money = $oneData[3];
}else{
$money = '0.00';
}
$data = [
'org_id'=>$orgid,
'name'=>$oneData[0],
'phone'=>$oneData[1],
'card'=>$oneData[2],
'money'=>$money,
'type'=>$type,
'create_time'=>getTime(),
'import_log_id'=>$importId
];
$Id = Db::name('owner')->insertGetId($data);
if(!$Id){
$msg = "第".$row."行，导入失败";
echo "<font color=\"red\">".$msg."</font><br />";
$error++;
$errorMsg[] = $msg;
continue;
}
$success++;
}
Db::name('import_log')->where('id',$importId)
->update(
[
'success'=>$success,
'error'=>$error,
'error_msg'=>json_encode($errorMsg),
]
);
}
echo "<font color=\"green\">导入完成</font><br />";
}catch (Exception $e){
echo $e->getMessage();
echo "<font color=\"red\">数据异常，已停止导入</font><br />";
}
}
}