hhqtest/application/admin/controller/CompanyGoods.php

<?php
namespace app\admin\controller;

use app\common\model\MateGoodsLog;
use think\App;
use think\Db;
use think\Exception;

class CompanyGoods extends Auth
{
    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->model = new \app\common\model\MateGoods();
        $this->table = $this->model->table;
    }
    public function index(){
        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('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $cate_id = input('cate_id','','trim');
            if($cate_id != ''){
                $map[] = ['cate_id','=',$cate_id];
            }
            $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]['cate_name'] = Db::name('mate_cate')->where('id',$v['cate_id'])->value('title');
            }


            //数据返回
            $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{
            $this->assign('meta_title','物品管理');
            $cate  = Db::name('mate_cate')
                ->where('enable',1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->select();
            $this->assign('cate',$cate);
            return $this->fetch();
        }
    }

    public function selectGoods(){
        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('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $map[] = ['enable','=',1];
            $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();


            //数据返回
            $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{
            return $this->fetch();
        }
    }

    /**
     * 新增/编辑
     */
    public function add($id=0){
        $model = $this->model;
        if(request()->isPost()){
            $res = $model->updates();
            if($res){
                $this->success('操作成功',url('index'));
            }else{
                $this->error($model->getError());
            }
        }else{
            $meta_title = '新增物品';
            if($id){
                $info =db($this->table)->where('id',$id)->find();
                $this->assign('info',$info);
                $meta_title = '编辑物品';
            }
            $cate = Db::name('mate_cate')->where('del',0)->where('org_id',$this->orgId)->select();
            $this->assign('cate',$cate);
            $this->assign('meta_title',$meta_title);
            return $this->fetch();
        }
    }

    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        if(db('mate_apply_goods')->where('goods_id',$id)->find()){
            $this->error('该商品有入库或出库记录，暂不能删除');
        }
        $res = db($this->table)->where('id',$id)->update(['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)->update([$fn => $fv]);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }
    /**
     * 处置
     */
    public function option($id=0){
        $model = new MateGoodsLog();
        if(request()->isPost()){
            $data = request()->post();
            if(empty($data['nums'])) $this->error('请输入数量');
            if(empty($data['remark'])) $this->error('请输入备注');
            $info = db('mate_goods')
                ->where('id',$id)
                ->find();
            if($info['nums'] <$data['nums']) $this->error('库存数量不足');
            $sData = [
                'org_id'=>$this->orgId,
                'goods_id'=>$id,
                'nums'=>$data['nums'],
                'remark'=>$data['remark'],
                'price'=>$info['price'],
            ];
            Db::startTrans();
            try{
                $res = $model->saveData($sData);
                if(!$res){
                    \exception($model->getError());
                }
                $ret = Db::name('mate_goods')->where('id',$id)->setDec('nums',$data['nums']);
                if(!$ret){
                    \exception('操作失败');
                }
                Db::commit();
            }catch (Exception $e){
                Db::rollback();
                $this->error($e->getMessage());
            }
            $this->success('操作成功');
        }else{
            $this->assign('id',$id);
            return $this->fetch();
        }
    }
    public function import(){
        return $this->fetch();
    }
    /**
     * 下载点模板
     */
    public function downloadtem(){
        set_time_limit(0);
        ini_set("memory_limit","512M");
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        $types = model('Address')->getTypes();
        ksort($types);
        $fileName = '物品模板.xlsx';
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F');
        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,'规格');
        $sheet->setCellValueByColumnAndRow(5,1,'类型(固定资产/耗材)');
        $sheet->setCellValueByColumnAndRow(6,1,'备注');


        $excel->getActiveSheet()->setCellValue('A2', '测试物品');
        $excel->getActiveSheet()->setCellValue('B2', '测试分类编号（2）');
        $excel->getActiveSheet()->setCellValue('C2', '个');
        $excel->getActiveSheet()->setCellValue('D2', '格力');
        $excel->getActiveSheet()->setCellValue('E2', '10个/包');
        $excel->getActiveSheet()->setCellValue('F2', '固定资产');
        $excel->getActiveSheet()->setCellValue('G2', 'xxxx');

        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(){
        set_time_limit(0);
        ini_set("memory_limit", -1);
        ob_flush();//清空缓存
        flush();//刷新缓存
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        $orgId = $this->orgId;
        if(request()->file()) {
            $file = request()->file('file');
            //获取文件后缀
            $e = explode('.',$_FILES['file']['name']);
            $ext = $e[count($e)-1];
            if($ext == 'xls'){
                \PHPExcel_IOFactory::createReader( 'Excel5');
            }else{
                \PHPExcel_IOFactory::createReader('Excel2007');
            }
            $newArr=['xls','xlsx'];
            if(!in_array($ext,$newArr)){
                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 = './uploads/files/' . $info->getSaveName();
            $filePath = str_replace('\\', '/', $img);
            $newPath = \PHPExcel_IOFactory::load($filePath);

            $excelArray = $newPath->getsheet(0)->toArray();   //转换为数组格式

            array_shift($excelArray);  //删除第一个数组(标题);
            $a = [
                '固定资产'=>1,
                '耗材'=>2,
            ];
            if(empty($excelArray)){
                exit('文件内容为空');
            }

            foreach ($excelArray as $k => $v) {

                if(!$v[0]){
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行，名称为空，未导入</font><br />";
                    continue;
                }
                $cateId = 0;
                if(trim($v[1])){
                   $cateId = Db::name('mate_cate')->where('id',$v[1])->value('id');
                }

                if(!$v[5]){
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行，类型为空，未导入</font><br />";
                    continue;
                }
                $check = Db::name('mate_goods')
                    ->where('del',0)
                    ->where('title',$v[0])
                    ->find();
                if($check){
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行，名称已存在，未导入</font><br />";
                    continue;
                }
                if(!isset($a[$v[5]])){
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行，类型格式错误，未导入</font><br />";
                    continue;
                }
                $rData = [
                    'org_id'=>$this->orgId,
                    'title'=>$v[0],
                    'cate_id'=>$cateId?$cateId:0,
                    'unit'=>$v[2],
                    'brand'=>$v[3],
                    'spec'=>$v[4],
                    'type'=>$a[$v[5]],
                    'remark'=>$v[6],
                    'create_time'=>getTime(),
                    'buy_time'=>date('Y-m-d')
                ];
                $ret=Db::name('mate_goods')->insert($rData);

                if(!$ret){
                    echo "<font color=\"red\" style='margin-left: 20px;font-size: 17px'>第".($k+1)."行，导入失败</font><br />";
                }else{
                    echo "<font color=\"green\" style='margin-left:20px;font-size: 17px'>第".($k+1)."行，导入成功</font><br />";
                }

            }
        }else{
            exit('请上传文件');
        }

    }
}