mingde-bd/application/admin/controller/CompanyGoods.php

<?php
namespace app\admin\controller;

use app\common\model\MateGoodsLog;
use app\common\util\ExcelUtil;
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];
            }

            $having = '1=1';
            $snums = input('kc_status','','trim');
            if($snums == 1) { // 正常
                $having = 'mnums >= 0 and nnums >= 0';
            }else if($snums == 2){ // 偏多
                $having = 'mnums < 0';
            }else if($snums == 3){ // 偏少
                $having = 'nnums < 0';
            }

            $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)
//                ->field('id,price,nums,type,title,unit,brand,total_price,spec,buy_time,remark,create_time,enable,cate_id,s_limit,x_limit,IFNULL(s_limit-nums,0) as mnums,IFNULL(nums- x_limit,0) as nnums')
                ->where($map)
                ->limit($start,$length)
//                ->group('id')
//                ->having($having)
                ->order($order)
                ->select();

            foreach ($lists as $k=>$v){
                $lists[$k]['cate_name'] = Db::name('mate_cate')->where('id',$v['cate_id'])->value('title');
                $kc_txt = '正常';

//                if($lists[$k]['s_limit'] != 0 && $lists[$k]['x_limit'] != 0){
//                    if($lists[$k]['s_limit'] > 0 && $v['nums'] > $lists[$k]['s_limit']){
//                        $kc_txt = '<span style="color: #DCAC6C">偏多</span> ';
//                    }
//                    if($lists[$k]['s_limit'] > 0 &&  $v['nums'] < $lists[$k]['s_limit']){
//                        $kc_txt = '<span style="color: red">偏少</span> ';
//                    }
//                }
                if($v['s_limit'] > 0){
                    if($v['nums'] > $v['s_limit']){
                        $kc_txt = '<span style="color: #DCAC6C">偏多</span> ';
                    }elseif ($v['nums'] <  $v['s_limit'] && $v['nums'] > $v['x_limit']){
                        $kc_txt = '正常';
                    }elseif ($v['nums'] == 0 || $v['nums'] <  $v['x_limit']){
                        $kc_txt = '<span style="color: red">偏少</span> ';
                    }
                }

                $lists[$k]['kc_txt'] = $kc_txt;
            }


            //数据返回
            $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','H','I','J','K');
        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,'备注');
        $sheet->setCellValueByColumnAndRow(7,1,'单价');
        $sheet->setCellValueByColumnAndRow(8,1,'数量');
        $sheet->setCellValueByColumnAndRow(9,1,'库存上限');
        $sheet->setCellValueByColumnAndRow(10,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');
        $excel->getActiveSheet()->setCellValue('H2', '10');
        $excel->getActiveSheet()->setCellValue('I2', '100');
        $excel->getActiveSheet()->setCellValue('J2', '100');
        $excel->getActiveSheet()->setCellValue('K2', '0');

        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(empty($cateId)){
                       $ps =[
                           'title'=>$v[1],
                           'org_id'=>$this->orgId,
                           'create_time'=>getTime(),
                       ];
                       $cateId = Db::name('mate_cate')->insertGetId($ps);
                   }
                }

                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],
                    'price'=>$v[7],
                    'nums'=>$v[8],
                    's_limit'=>$v[9],
                    'x_limit'=>$v[10],
                    '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('请上传文件');
        }

    }

    public function xhtj(){

        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 month')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 00:00:00';
        $list = $this->xhtjData($start1, $end1);
        $this->assign('list', $list['list']);
        $this->assign('zj', $list['zj']);
        $this->assign('start', $start);
        $this->assign('end', $end);
        return $this->fetch();

    }

    public  function xhtjData($start1, $end1){

        $map[] = ['ma.create_time', '>=', $start1];
        $map[] = ['ma.create_time', '<=', $end1];
        $map[] = ['ma.org_id', '=', $this->orgId];

        $list = Db::name('mate_apply')
            ->alias('ma')
            ->join('mate_apply_goods mag','ma.id=mag.apply_id')
            ->join('mate_goods mg','mg.id=mag.goods_id')
            ->where($map)
            ->field('mag.goods_id,mag.price,mg.title')
            ->group('mag.goods_id')
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['num'] = Db::name('mate_apply')
                ->alias('ma')
                ->join('mate_apply_goods mag','ma.id=mag.apply_id')
                ->join('mate_goods mg','mg.id=mag.goods_id')
                ->where('mag.goods_id',$v['goods_id'])
                ->where($map)
                ->sum('mag.nums');
            $list[$k]['total_price'] = round($list[$k]['num']*$v['price'],1);
        }


        $map1[] = ['tm.create_time', '>=', $start1];
        $map1[] = ['tm.create_time', '<=', $end1];
        $map1[] = ['tm.org_id', '=', $this->orgId];

        $list1 = Db::name('todo_mate')
            ->alias('tm')
            ->join('todo_mate_item tmi','tmi.todo_mate_id=tm.id')
            ->join('mate_goods mg','mg.id=tmi.items_id')
            ->where($map1)
            ->group('tmi.items_id')
            ->field('tmi.items_id as goods_id,tmi.money as price,mg.title')
            ->select();

        foreach ($list1 as $k=>$v){
            $list1[$k]['num'] = Db::name('todo_mate')
                ->alias('tm')
                ->join('todo_mate_item tmi','tmi.todo_mate_id=tm.id')
                ->where($map1)
                ->where('tmi.items_id',$v['goods_id'])
                ->sum('tmi.total');

            $list1[$k]['total_price'] = round($list1[$k]['num']*$v['price'],1);
        }


//        $map2[] = ['mau.create_time', '>=', $start1];
//        $map2[] = ['mau.create_time', '<=', $end1];
//        $map2[] = ['mau.org_id', '=', $this->orgId];
//        $list2 = Db::name('mate_apply_use')
//            ->alias('mau')
//            ->join('mate_apply_use_goods maug','mau.id=maug.apply_id')
//            ->join('mate_goods mg','mg.id=maug.goods_id')
//            ->where($map2)
//            ->field('maug.goods_id,maug.price,mg.title')
//            ->group('maug.goods_id')
//            ->select();
//
//        foreach ($list2 as $k => $v) {
//            $list2[$k]['num'] = Db::name('mate_apply_use')
//                ->alias('mau')
//                ->join('mate_apply_use_goods maug','mau.id=maug.apply_id')
//                ->join('mate_goods mg','mg.id=maug.goods_id')
//                ->where('maug.goods_id',$v['goods_id'])
//                ->where($map2)
//                ->sum('maug.nums');
//            $list2[$k]['total_price'] = round($list2[$k]['num']*$v['price'],1);
//        }


        foreach ($list as $k=>$v){
            $totalPrice = 0;
            $tnum = 0;
            foreach ($list1 as $kk=>$vv){
                if($v['goods_id'] == $vv['goods_id']){
                    $totalPrice = $v['total_price'] + $vv['total_price'];
                    $tnum = $v['num'] + $vv['num'];
                    unset($list1[$kk]);
                }
            /*    foreach ($list2 as $kkk=>$vvv){
                    if($v['goods_id'] == $vv['goods_id']){
                        $totalPrice = $v['total_price'] + $vv['total_price'];
                        $tnum = $v['num'] + $vv['num'];
                        unset($list1[$kk]);
                    }elseif ($v['goods_id'] == $vvv['goods_id']){
                        $totalPrice = $v['total_price'] + $vvv['total_price'];
                        $tnum = $v['num'] + $vvv['num'];
                        unset($list2[$kkk]);
                    }elseif ($v['goods_id'] == $vv['goods_id'] && $v['goods_id'] == $vvv['goods_id']){
                        $totalPrice = $v['total_price'] + $vv['total_price'] + $vvv['total_price'];
                        $tnum = $v['num'] + $vv['num'] + $vvv['num'];
                        unset($list1[$kk]);
                        unset($list2[$kkk]);
                    }
                }*/


            }
            if($totalPrice > 0 && $tnum > 0){
                $list[$k]['total_price'] = $totalPrice;
                $list[$k]['num'] = $tnum;
            }
        }

        $array = array_merge($list,$list1);

        $zj = 0;
        foreach ($array as $k=>$v){
            $zj +=$v['total_price'];
        }

        return ['list'=>$array,'zj'=>$zj];

    }

    public function qrcode($id=0){
        $info = Db::name('mate_goods')->where('id',$id)->find();
        $code = get_qrcode_str('mate_goods',$id);
        $this->assign('code',$code);
        $this->assign('info',$info);
        return $this->fetch();
    }

    public function wptj(){
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 month')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';

        $title = input('title','','trim');

        $cateTitle = input('cateTitle','');

        $list = $this->wptjData($start1, $end1,$title,$cateTitle);
        $this->assign('list', $list);
        $this->assign('start', $start);
        $this->assign('end', $end);
        $this->assign('title', $title);
        $this->assign('cateTitle', $cateTitle);
        return $this->fetch();

    }

    public function wptjData($start1, $end1,$title,$cateTitle) {
        $map1[] = ['ma.create_time', '>=', $start1];
        $map1[] = ['ma.create_time', '<=', $end1];

        $map =[];
        if($title){
            $map[] = ['title','like','%'.$title.'%'];
        }
        if($cateTitle){
            $cateIds = Db::name('mate_cate')->where('title','like','%'.$cateTitle.'%')->column('id');
            $map[] = ['cate_id','in',$cateIds];
        }

        $list = Db::name('mate_goods')
            ->where('org_id', $this->orgId)
            ->where('del', 0)
            ->where('enable', 1)
            ->where($map)
            ->select();
        foreach ($list as $k => $v) {
            $list[$k]['ck'] = Db::name('mate_apply')
                ->alias('ma')
                ->join('mate_apply_goods mag','mag.apply_id=ma.id')
                ->where('ma.type',1)
                ->where('mag.goods_id',$v['id'])
                ->where($map1)
                ->sum('mag.nums');

            $list[$k]['rk'] = Db::name('mate_apply')
                ->alias('ma')
                ->join('mate_apply_goods mag','mag.apply_id=ma.id')
                ->where('ma.type',2)
                ->where('mag.goods_id',$v['id'])
                ->where($map1)
                ->sum('mag.nums');
            $tt = '';
            if($v['consume'] == 1){
                $tt ='使用消耗';
            }elseif ($v['consume'] == 2){
                $tt ='出库消耗';
            }
            $list[$k]['consume_title'] = $tt;

            $list[$k]['cate_title'] = Db::name('mate_cate')->where('id',$v['cate_id'])->value('title');
        }
        return $list;
    }


    public function wptjDataExport() {
        $cur = date('Y-m-d');
        $start = input('start', date('Y-m-d', strtotime('' . $cur . ' -1 week')));
        $end = input('end', date('Y-m-d'));
        $start1 = $start . ' 00:00:00';
        $end1 = $end . ' 23:59:59';

        $title = input('title','','trim');

        $cateTitle = input('cateTitle','');

        $list = $this->wptjData($start1, $end1,$title,$cateTitle);

        $filename = '物品统计';
        $header = [
            ['title' => '分类', 'name' => 'cate_title','width'=>'20'],
            ['title' => '物品名称', 'name' => 'title','width'=>'20'],
            ['title' => '品牌', 'name' => 'brand','width'=>'20'],
            ['title' => '规格', 'name' => 'spec','width'=>'20'],
            ['title' => '入库数量', 'name' => 'ck','width'=>'20'],
            ['title' => '出库数量', 'name' => 'rk','width'=>'20'],
            ['title' => '当前余量', 'name' => 'nums','width'=>'20'],
            ['title' => '类型', 'name' => 'consume_title','width'=>'20'],

        ];
        ExcelUtil::export($filename,$header,$list);
    }

}