jili2/application/admin/controller/MateReport.php

<?php

namespace app\admin\controller;
use app\common\util\ExcelUtil;
use think\Db;

class MateReport extends Auth {
    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', 'asc', 'trim');        //排序方式
            $order = $sortRow . ' ' . $sort;
            $title = input('title', '', 'trim');
            if ($title) {
                $map[] = ['title', 'like', '%' . $title . '%'];
            }
            $map[] = ['org_id', '=', $this->orgId];
            $map = empty($map) ? true : $map;
            //数据查询
            $lists = Db::name('mate_report')
                ->where($map)->limit($start, $length)
                ->order($order)->select();
            //数据返回
            $totalCount = Db::name('mate_report')->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', '月份统计记录');
            return $this->fetch();
        }
    }

    public function detail($rid=0) {
        if (request()->isAjax()) {
            //分页参数
            $length = input('rows', 10, 'intval');   //每页条数
            $page = input('page', 1, 'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx', 'a.id', 'trim');      //排序列
            $sort = input('sord', 'asc', 'trim');        //排序方式
            $order = $sortRow . ' ' . $sort;
            $title = input('title', '', 'trim');
            if ($title) {
                $map[] = ['b.title', 'like', '%' . $title . '%'];
            }
            $map[] = ['a.report_id', '=', $rid];
            $map = empty($map) ? true : $map;
            //数据查询
            $lists = Db::name('mate_report_goods')
                ->alias('a')
                ->join('mate_goods b','a.goods_id = b.id')
                ->field('a.*,b.title,b.brand,b.spec')
                ->where($map)->limit($start, $length)
                ->order($order)->select();
            //数据返回
            $totalCount = Db::name('mate_report_goods')
                ->alias('a')
                ->join('mate_goods b','a.goods_id = b.id')
                ->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('rid',$rid);
            $this->assign('meta_title', '月份统计记录详情');
            return $this->fetch();
        }
    }

    public function add(){
        $sday = date('Y-m', strtotime('last month')).'-25 ';
        $starttime = $sday." 00:00:00";
        $eday = date('Y-m').'-25 ';
        $endtime = $eday."00:00:00";
        $orgs = Db::name("org")->where('type',2)->where('enable',1)->where('del',0)->select();
        foreach($orgs as $k=>$v){
            $goods = Db::name('mate_goods')
                ->where('org_id',$v['id'])
                ->where('del',0)
                ->where('enable',1)
                ->field('id,nums')
                ->select();
            if(!$goods){ // 无商品不生成记录
                continue;
            }
            $innums = 0;
            $outnums = 0;
            $usenums = 0;
            $cznums = 0;
            $nums = 0;

            $reportGoods = [];
            foreach ($goods as $kk=>$vv){
                $innums1 = Db::name('mate_apply_goods')
                    ->alias('a')
                    ->join('mate_apply b','a.apply_id = b.id')
                    ->where('b.del',0)
                    ->where('b.type',1)
                    ->where('b.org_id',$v['id'])
                    ->where('a.goods_id',$vv['id'])
                    ->where('b.create_time','>=',$starttime)
                    ->where('b.create_time','<',$endtime)
                    ->sum('a.nums');
                $innums += $innums1;
                $outnums1 = Db::name('mate_apply_goods')
                    ->alias('a')
                    ->join('mate_apply b','a.apply_id = b.id')
                    ->where('b.del',0)
                    ->where('b.type',2)
                    ->where('b.org_id',$v['id'])
                    ->where('a.goods_id',$vv['id'])
                    ->where('b.create_time','>=',$starttime)
                    ->where('b.create_time','<',$endtime)
                    ->sum('a.nums');
                $outnums += $outnums1;

                $usenums1 = Db::name('todo_mate_item')
                    ->alias('a')
                    ->join('todo_mate b','a.todo_mate_id = b.id')
                    ->where('b.org_id',$v['id'])
                    ->where('a.items_id',$vv['id'])
                    ->where('b.create_time','>=',$starttime)
                    ->where('b.create_time','<',$endtime)
                    ->sum('a.total');
                $usenums += $usenums1;

                $cznums1 = Db::name('mate_goods_log')
                    ->where('type',1)
                    ->where('org_id',$v['id'])
                    ->where('goods_id',$vv['id'])
                    ->where('create_time','>=',$starttime)
                    ->where('create_time','<',$endtime)
                    ->sum('nums');
                $cznums += $cznums1;

                $nums += $vv['nums'];

                $reportGoods[] = [
                    'org_id' => $v['id'],
                    'goods_id' => $vv['id'],
                    'in_nums' => $innums1,
                    'out_nums' => $outnums1,
                    'use_nums' => $usenums1,
                    'cz_nums' => $cznums1,
                    'nums' => $vv['nums'],
                ];
            }

            Db::startTrans();
            try{
                $reportId = Db::name("mate_report")->insertGetId([
                    'org_id' => $v['id'],
                    'start_time' => $sday,
                    'end_time' => $eday,
                    'in_nums' => $innums,
                    'out_nums' => $outnums,
                    'use_nums' => $usenums,
                    'cz_nums' => $cznums,
                    'nums' => $nums,
                    'create_time' => date("Y-m-d H:i:s")
                ]);

                $arr = [];
                foreach ($reportGoods as $kk=>$vv){
                    $vv['report_id'] = $reportId;
                    $arr[] = $vv;
                    if(count($arr) == 200){
                        $res = Db::name("mate_report_goods")->insertAll($arr);
                        if($res != count($arr)){
                            exception("操作失败");
                        }
                        $arr = [];
                    }
                }
                if(count($arr) > 0){
                    $res = Db::name("mate_report_goods")->insertAll($arr);
                    if($res != count($arr)){
                        exception("操作失败");
                    }
                }

                Db::commit();
            }catch (\Exception $e){
                Db::rollback();
            }
        }
    }

    public function export($rid=0){
        $title = input('title', '', 'trim');
        if ($title) {
            $map[] = ['b.title', 'like', '%' . $title . '%'];
        }
        $map[] = ['a.report_id', '=', $rid];
        $map = empty($map) ? true : $map;
        //数据查询
        $lists = Db::name('mate_report_goods')
            ->alias('a')
            ->join('mate_goods b','a.goods_id = b.id')
            ->field('a.*,b.title,b.brand,b.spec')
            ->where($map)
            ->order('a.id asc')->select();

        $header = [
            ['title' => '名称', 'name' => 'title','width'=>'20'],
            ['title' => '品牌', 'name' => 'brand','width'=>'20'],
            ['title' => '规格', 'name' => 'spec','width'=>'20'],
            ['title' => '入库', 'name' => 'in_nums','width'=>'20'],
            ['title' => '出库', 'name' => 'out_nums','width'=>'20'],
            ['title' => '订单消耗', 'name' => 'use_nums','width'=>'20'],
            ['title' => '处置', 'name' => 'cz_nums','width'=>'20'],
            ['title' => '结余', 'name' => 'nums','width'=>'20']
        ];

        $filename = '统计详情';
        ExcelUtil::export($filename,$header,$lists);
    }
}