tool/application/admin/controller/Report.php

<?php
/**
 * author xtj
 * createtime 2020/4/14 0014 下午 1:21
 * return array obj json bool
 * param
 */

namespace app\admin\controller;

use app\common\controller\Backend;
use think\Db;
use think\Session;
use think\Where;
use PHPExcel_IOFactory;
use PHPExcel;
use think\paginator\driver\Bootstrap;

class Report extends Backend{

    protected $searchFields = "*";
    //领用报表
    public function index(){
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
//            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $params = input('search');
            if (!empty($params)){
                $product =  Db::connect('db2')->query("select * from 工单_基本资料 where Gd_gdbh = '{$params}' limit 1");
                if (empty($product)){
                    return array('status'=>0,'msg'=>'数据错误，请联系管理员');
                }
                $product_number = rtrim($product[0]['Gd_cpdh']);
                $where[] = ['exp',Db::raw("FIND_IN_SET($product_number,product_number)")];
                $list = Db::name('formula')->where($where)->field('id,color')->select();
                foreach ($list as $key=>$value){
                    $bach = Db::name('warehousing_detail')->where('cid',$value['id'])->column('bach_number');
                    $collarWeight = Db::name('collar_use')->where('order_number',$params)->where('status',1)->where('warehousing','in',$bach)->column('weight');
                    $backWeight = Db::name('collar_use')->where('order_number',$params)->where('status',2)->where('back','in',$bach)->column('back_weight');
                    $list[$key]['collarWeight'] = array_sum($collarWeight) / 1000;
                    $list[$key]['backWeight'] = array_sum($backWeight) / 1000;
                    $consume = array_sum($collarWeight) - array_sum($backWeight);
                    $list[$key]['consume'] = $consume / 1000;
                    $list[$key]['order_number'] = $params;
                    $list[$key]['product_name'] = trim($product[0]['成品名称']);
                }
                $total = count($list);
              }else{
                $total = 0;
                $list = [];
            }
            $result = array("total" => $total, "rows" => $list);
            return json($result);
        }
        return $this->view->fetch();
    }

    //批次报表
    public function bach(){
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $param = input('');
            $wheres = [];
            //批次号查询 产品名称查询
            if(!empty($param['search'])){
                if(is_numeric($param['search'])){
                    $wheres['order_number'] = ['=',$param['search']];
                }else{
                    $wheres['product_name'] = ['like','%'.$param['search'].'%'];
                }
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $list = Db::name('warehousing_detail')
                ->where('status',0)
                ->limit($offset, $limit)
                ->where($wheres)
                ->order('id desc')
                ->field('id,product_name,bach_number,color,weight,create,order_number')
                ->select();
            $total = Db::name('warehousing_detail')
                ->where('status',0)
                ->where($wheres)
                ->limit($offset, $limit)
                ->count();
            foreach ($list as $key=>$value){
                $list[$key]['ink_status'] = '专色墨';
                if ($value['weight'] > 0){
                    $list[$key]['weight'] = $value['weight'] / 1000;
                }
            }
//            $ink = Db::name('matter')->where('weight','>',0)->field('code,name,weight,create')->select();
//            foreach ($ink as $k=>$v){
//                $ink[$k]['ink_status'] = '油墨';
//                $ink[$k]['color'] = '';
//                $ink[$k]['product_name'] = $v['name'];
//                $ink[$k]['bach_number'] = $v['code'];
//                $ink[$k]['weight'] = $value['weight'] / 1000;
//                unset( $ink[$k]['name']);
//                unset( $ink[$k]['code']);
//                array_push($list,$ink[$k]);
//            }
//            $total = count($list);
//            for ($i=0;$i<$total;$i++){
//                $list[$i]['id'] = $i+1;
//            }
            $result = array("total" => $total, "rows" => $list);
            return json($result);
        }
        return $this->view->fetch();
    }

    //月度报表
    public function mouth(){
        //设置过滤方法
        $param = $this->request->param();
        if (!empty($param['k_time'])) {
            $this->assign('k_time',$param['k_time']);
        } else {
            $this->assign('k_time','');
        }
        if (!empty($param['j_time'])) {
            $this->assign('j_time',$param['j_time']);
        } else {
            $this->assign('j_time','');
        }
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $wheres = [];
            $param = input('');
            //开始时间--结束时间查询
            if(!empty($param['k_time']) && !empty($param['j_time'])){
                $wheres['create'] = ['between time',[$param['k_time'].' 00:00:00',$param['j_time'].' 23:59:59']];
            }else{
                $mouthStart = date('Y-m-01', strtotime(date("Y-m-d")));
                $mouthEnd = date('Y-m-d', strtotime("$mouthStart +1 month -1 day"));
                $wheres['create'] = ['between',[$mouthStart.' 00:00:00',$mouthEnd.' 23:59:59']];
            }
            //批次号查询、产品名称查询
            if(!empty($param['search'])){
                if(is_numeric($param['search'])){
                    $wheres['order_number'] = ['=',$param['search']];
                }else{
                    $wheres['product_name'] = ['like','%'.$param['search'].'%'];
                }
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
//            $mouthStart = date('Y-m-01', strtotime(date("Y-m-d")));
//            $mouthEnd = date('Y-m-d', strtotime("$mouthStart +1 month -1 day"));
            $list = Db::name('collar_use')
//                ->where('create','between time',[$mouthStart,$mouthEnd])
                ->where($wheres)
                ->field('id,order_number,product_name,warehousing,weight,back,back_weight,status,machine_number,create')
                ->limit($offset, $limit)
                ->order('id desc')
                ->select();

            $total = Db::name('collar_use')
//                ->where('create','between time',[$mouthStart,$mouthEnd])
                ->where($wheres)
                ->limit($offset, $limit)
                ->order('id desc')
                ->count();

            foreach ($list as $key=>$value){
                $list[$key]['ink_status'] = '专色墨';
                if ($value['status'] == 1){
                    $list[$key]['status'] = '领用';
                }else{
                    $list[$key]['status'] = '退回';
                }
                if (!empty($value['weight'])){
                    $list[$key]['weight'] = $value['weight'] / 1000;
                    $list[$key]['color'] = Db::name('warehousing_detail')->where('bach_number',$value['warehousing'])->value('color');
                    unset($list[$key]['back']);
                    unset($list[$key]['back_weight']);
                }
                if (!empty($value['back_weight'])){
                    $list[$key]['weight'] = $value['back_weight'] / 1000;
                    $list[$key]['color'] = Db::name('warehousing_detail')->where('bach_number',$value['back'])->value('color');
                    unset($list[$key]['back_weight']);
                }
                unset($list[$key]['warehousing']);
                unset($list[$key]['back']);
            }
            $result = array("total" => $total, "rows" => $list);
            return json($result);
        }

        return $this->view->fetch();

//        $ink = Db::name('matter_use')->where('create','between time',[$mouthStart,$mouthEnd])->field('order_number,ink_name,out,out_weight,back,back_weight,status,machine_number,create')->order('order_number')->select();
//        foreach ($ink as $k=>$v){
//            $ink[$k]['ink_status'] = '油墨';
//            $ink[$k]['product_name'] = $ink[$k]['ink_name'];
//            $ink[$k]['color'] = '';
//            if ($v['status'] == 1){
//                $ink[$k]['status'] = '领用';
//            }else{
//                $ink[$k]['status'] = '退回';
//            }
//            if (!empty($v['out_weight'])){
//                $ink[$k]['weight'] = $v['out_weight'] / 1000;
//
//            }
//            if (!empty($v['back_weight'])){
//                $ink[$k]['weight'] = $v['back_weight'] / 1000;
//
//            }
//            unset($ink[$k]['out']);
//            unset($ink[$k]['out_weight']);
//            unset($ink[$k]['back']);
//            unset($ink[$k]['back_weight']);
//            array_push($list,$ink[$k]);
//        }
    }

    //专色墨换算成原墨
    public function warehousing(){
        die;
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();




            $result = array("total" => $total, "rows" => $list);
            return json($result);
        }
        return $this->view->fetch();

        $params = input('productNumber');
        if (empty($params)){

            $allOrder = Db::name('collar_use')->where('status',1)->order('create desc')->column('order_number');
            $allOrder = array_merge(array_unique($allOrder));

            $limitOrder = array_slice($allOrder,0,10);
            $bach = [];
            foreach ($limitOrder as $key => $value){
                //循环查出各个工单领用总重量
                $where = [];
                $where['order_number'] = $value;
                $where['status'] = 1;
                $where['l_status'] = 2;
                $data = Db::name('collar_use')->where($where)->field('order_number,warehousing,weight')->select();
                $count = count($data);
                $warehousing = [];
                for ($j=0;$j<$count;$j++){
                    $warehousing[$j]= $data[$j]['warehousing'];
                }
                $bach[$key] = array_merge(array_unique($warehousing));
            }
            $i = 0;
            foreach ($bach as $k1=>$v1){
                foreach ($v1 as $k2=>$v2){
                    $list[$i]['order_number'] = Db::name('collar_use')->where('warehousing',$v2)->value('order_number');
                    $list[$i]['product_name'] = Db::name('warehousing_detail')->where('bach_number',$v2)->value('product_name');
                    $list[$i]['warehousing'] = $v2;
                    $list[$i]['out_weight'] = Db::name('collar_use')->where(['status'=>1,'l_status'=>2])->where('warehousing',$v2)->sum('weight');
                    $list[$i]['out_weight'] =  $list[$i]['out_weight'] / 1000;
                    $list[$i]['back_weight'] = Db::name('collar_use')->where('status',2)->where('back',$v2)->sum('weight');
                    if ($list[$i]['back_weight'] > 0){
                        $list[$i]['back_weight'] = $list[$i]['back_weight'] / 1000;
                    }
                    //报废
                    $list[$i]['scrap'] = Db::name('scrap_ink')->where(['order_number'=>$list[$i]['order_number'],'bach_number'=>$v2])->sum('weight');
                    if ($list[$i]['scrap'] > 0){
                        $list[$i]['scrap'] = $list[$i]['scrap'] / 1000;
                    }
                    $list[$i]['color'] = Db::name('warehousing_detail')->where('bach_number',$v2)->value('color');
                    $list[$i]['total'] = $list[$i]['out_weight'] - $list[$i]['back_weight'] - $list[$i]['scrap'];
                    $i = $i + 1;
                }
            }
        }else{
            $result = Db::connect('db2')->query("select * from 工单_基本资料 where Gd_gdbh = '{$params}' limit 1");
            if (empty($result)){
                return array('status'=>0,'msg'=>'未查到相关信息,请核查工单号');
            }
            $product_name = trim($result[0]['成品名称']);
            //领用总重量-退回总重量 = 消耗总重量
            $where = [];
            $where['order_number'] = $params;
            $where['status'] = 1;
            $where['l_status'] = 2;
            $data = Db::name('collar_use')->where($where)->field('order_number,warehousing,weight')->select();
            if (empty($data)){
                return $this->error('暂无数据');
            }
            //获得领用的所有专色批次号
            $bach = [];
            foreach ($data as $key=>$value){
                $bach[$key]= $value['warehousing'];
            }
            $bach = array_merge(array_unique($bach));
            //计算领走的总重量
            $mapOut = [];
            $mapOut['status'] = 1;
            $mapOut['l_status'] = 2;
            //退回查询条件
            $mapBack['status'] = 2;
            $list = [];
            foreach ($bach as $k=>$v){
                $list[$k]['order_number'] = $params;
                $list[$k]['product_name'] = $product_name;
                $list[$k]['warehousing'] = $v;
                //领走
                $list[$k]['out_weight'] = Db::name('collar_use')->where($mapOut)->where('warehousing',$v)->sum('weight');
                $list[$k]['out_weight'] =  $list[$k]['out_weight'] / 1000;
                //退回
                $list[$k]['back_weight'] = Db::name('collar_use')->where($mapBack)->where('back',$v)->sum('weight');
                if ($list[$k]['back_weight'] > 0){
                    $list[$k]['back_weight'] = $list[$k]['back_weight'] / 1000;
                }
                //报废
                $list[$k]['scrap'] = Db::name('scrap_ink')->where(['order_number'=>$params,'bach_number'=>$v])->sum('weight');
                if ($list[$k]['scrap'] > 0){
                    $list[$k]['scrap'] = $list[$k]['scrap'] / 1000;
                }
                $list[$k]['color'] = Db::name('warehousing_detail')->where('bach_number',$v)->value('color');
                $list[$k]['total'] = $list[$k]['out_weight'] - $list[$k]['back_weight'] - $list[$k]['scrap'];
            }

        }
        return $this->view->fetch('',compact('list'));
    }

    //原墨领用报表
    public function matter(){

        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $search = input('search');
            $map = [];
            if (!empty($search)){
                $where = [];
                $map['order_number'] = $search;
            }
            $list = Db::name('matter_use')
                ->where($where)
                ->where($map)
                ->order($sort, $order)
                ->limit($offset,$limit)
                ->select();
            $total = Db::name('matter_use')
                ->where($where)
                ->where($map)
                ->count();
            foreach ($list as $k=>$v){
                if (!empty($v['out_weight'])){
                    $list[$k]['out_weight'] = $v['out_weight'] / 1000;
                }
                if (!empty($v['back_weight'])){
                    $list[$k]['back_weight'] = $v['back_weight'] / 1000;
                }
            }


            $result = array("total" => $total, "rows" => $list);
//            print_r($result);die;
            return json($result);
        }
        return $this->view->fetch();
    }
    //原墨统计
    public function ink(){
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $search = input('search');
            $map = [];
            if (!empty($search)){
                $map['order_number'] = $search;
            }
            //统计
            $res = [];
            if (!empty($map)){
                $data = Db::name('matter_use')
                    ->where($map)
                    ->order('status asc')
                    ->select();
                if (!empty($data)){
                    $y = 0;
                    foreach ($data as $key=>$value){
                        $isHave = $this->deep_in_array($value['ink_name'],$res);
                        if ($isHave){
                            foreach ($res as $j=>$i){
                                if ($value['ink_name'] == $i['ink_name'] && $value['status'] == 1){ //出库重量相加
                                    $res[$j]['out_weight'] = $i['out_weight'] + $value['out_weight'];
                                }
                                if ($value['ink_name'] == $i['ink_name'] && $value['status'] == 2){ //退库重量相加
                                    $res[$j]['back_weight'] = $i['back_weight'] + $value['back_weight'];
                                }
                            }
                        }else{
                            $res[$y]['order_number'] = $value['order_number'];
                            $res[$y]['ink_name'] = $value['ink_name'];
                            $res[$y]['out_weight'] = $value['out_weight'];
                            $res[$y]['back_weight'] = $value['back_weight'];
                            $y++;
                        }
                    }
                    foreach ($res as &$item){
                        $item['out_weight'] = $item['out_weight'] / 1000;
                        $item['back_weight'] = $item['back_weight'] / 1000;
                        $item['weight'] = $item['out_weight'] - $item['back_weight'];
                    }
                }
            }
            $total = count($res);
            $result = array("total" => $total, "rows" => $res);

            return json($result);
        }
        return $this->view->fetch();
    }
    //领用报表导出
    public function out(){
        $params = input('search');
        print_r($params);die;
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        $list = Db::name('collar_use')->alias('a')
            ->join('warehousing_detail b','a.warehousing=b.bach_number','LEFT')
            ->where('a.status',1)
            ->field('a.order_number,a.product_name,a.weight,b.color,a.create')
            ->select();
        foreach ($list as $key=>$value){
            $list[$key]['ink_status'] = '专色墨';
            $list[$key]['weight'] = $value['weight'] / 1000;
        }
        $inkList = Db::name('matter_use')
            ->where('status',1)
            ->field('product_name,out_weight,create')
            ->select();
        foreach ($inkList as $k=>$v){
            $inkList[$k]['ink_status'] = '油墨';
            $inkList[$k]['color'] = '';
            $inkList[$k]['weight'] = $v['out_weight'] / 1000;
            unset( $inkList[$k]['out_weight']);
            array_push($list,$inkList[$k]);
        }
        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = $list;

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '名称')
            ->setCellValue('B1', '油墨类别')
            ->setCellValue('C1', '领用重量(kg)')
            ->setCellValue('D1', '领用日期');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['ink_status']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['create']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle('领用库存报表');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="领用库存报表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }
    //批次报表导出
    public function bachOut(){
        $params = input('');
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $list = Db::name('warehousing_detail')->where('status',0)->field('product_name,bach_number,color,weight,create')->select();
        foreach ($list as $key=>$value){
            $list[$key]['ink_status'] = '专色墨';
            if ($value['weight'] > 0){
                $list[$key]['weight'] = $value['weight'] / 1000;
            }
        }
        $ink = Db::name('matter')->where('weight','>',0)->field('code,name,weight,create')->select();
        foreach ($ink as $k=>$v){
            $ink[$k]['ink_status'] = '油墨';
            $ink[$k]['color'] = '';
            $ink[$k]['product_name'] = $v['name'];
            $ink[$k]['bach_number'] = $v['code'];
            $ink[$k]['weight'] = $value['weight'] / 1000;
            unset( $ink[$k]['name']);
            unset( $ink[$k]['code']);
            array_push($list,$ink[$k]);
        }

        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = $list;

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '产品名称')
            ->setCellValue('B1', '油墨类别')
            ->setCellValue('C1', '颜色')
            ->setCellValue('D1', '批次号')
            ->setCellValue('E1', '重量(kg)')
            ->setCellValue('F1', '入库时间');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['ink_status']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['color']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['bach_number']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $sql[$i-2]['create']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle('批次报表');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="批次报表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }
    //月度报表导出
    public function mouthOut(){
        $params = input('');
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        $mouthStart = date('Y-m-01', strtotime(date("Y-m-d")));
        $mouthEnd = date('Y-m-d', strtotime("$mouthStart +1 month -1 day"));
        $list = Db::name('collar_use')->where('create','between time',[$mouthStart,$mouthEnd])->field('order_number,product_name,warehousing,weight,back,back_weight,status,machine_number,create')->select();
        foreach ($list as $key=>$value){
            $list[$key]['ink_status'] = '专色墨';
            if ($value['status'] == 1){
                $list[$key]['status'] = '领用';
            }else{
                $list[$key]['status'] = '退回';
            }
            if (!empty($value['weight'])){
                $list[$key]['weight'] = $value['weight'] / 1000;
                $list[$key]['color'] = Db::name('warehousing_detail')->where('bach_number',$value['warehousing'])->value('color');
                unset($list[$key]['back']);
                unset($list[$key]['back_weight']);
            }
            if (!empty($value['back_weight'])){
                $list[$key]['weight'] = $value['back_weight'] / 1000;
                $list[$key]['color'] = Db::name('warehousing_detail')->where('bach_number',$value['back'])->value('color');
                unset($list[$key]['back_weight']);
            }
            unset($list[$key]['warehousing']);
            unset($list[$key]['back']);
        }

        $ink = Db::name('matter_use')->where('create','between time',[$mouthStart,$mouthEnd])->field('order_number,product_name,out,out_weight,back,back_weight,status,machine_number,create')->order('order_number')->select();
        foreach ($ink as $k=>$v){
            $ink[$k]['ink_status'] = '油墨';
            $ink[$k]['color'] = '';
            if ($v['status'] == 1){
                $ink[$k]['status'] = '领用';
            }else{
                $ink[$k]['status'] = '退回';
            }
            if (!empty($v['out_weight'])){
                $ink[$k]['weight'] = $v['out_weight'] / 1000;

            }
            if (!empty($v['back_weight'])){
                $ink[$k]['weight'] = $v['back_weight'] / 1000;

            }
            unset($ink[$k]['out']);
            unset($ink[$k]['out_weight']);
            unset($ink[$k]['back']);
            unset($ink[$k]['back_weight']);
            array_push($list,$ink[$k]);
        }
        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = $list;

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '工单编号')
            ->setCellValue('B1', '产品名称')
            ->setCellValue('C1', '状态')
            ->setCellValue('D1', '重量')
            ->setCellValue('E1', '油墨类别')
            ->setCellValue('F1', '颜色')
            ->setCellValue('G1', '机台号')
            ->setCellValue('H1', '日期');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['order_number']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['status']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['ink_status']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $sql[$i-2]['color']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $sql[$i-2]['machine_number']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $sql[$i-2]['create']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle('月度报表');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="月度报表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }
    //专色墨换算成原墨导出
    public function warehousingOut(){
        $params = input('productNumber');
        if (empty($params)){
            return $this->error('请输入工单号导出');
        }
        //查出产品编号
        $result = Db::connect('db2')->query("select * from 工单_基本资料 where Gd_gdbh = '{$params}' limit 1");
        if (empty($result)){
            return array('status'=>0,'msg'=>'未查到相关信息,请核查工单号');
        }
//        $product_name = trim($result[0]['成品名称']);
        $product_number = trim($result[0]['Gd_cpdh']);
        //领用总重量-退回总重量 = 消耗总重量
        $where = [];
        $where['order_number'] = $params;
        $where['status'] = 1;
        $where['l_status'] = 2;
        $data = Db::name('collar_use')->where($where)->field('order_number,warehousing,weight')->select();
        if (empty($data)){
            return $this->error('暂无数据');
        }
        //获得领用的所有专色批次号
        $bach = [];
        foreach ($data as $key=>$value){
            $bach[$key]= $value['warehousing'];
        }
        $bach = array_merge(array_unique($bach));
        //计算领走的总重量
        $mapOut = [];
        $mapOut['status'] = 1;
        $mapOut['l_status'] = 2;
        //退回查询条件
        $mapBack['status'] = 2;
        $list = [];
        foreach ($bach as $k=>$v){
//            $list[$k]['order_number'] = $params;
//            $list[$k]['product_name'] = $product_name;
//            $list[$k]['warehousing'] = $v;
            //领走
            $outWeight = Db::name('collar_use')->where($mapOut)->where('warehousing',$v)->sum('weight');
            $outWeight =  $outWeight / 1000;
            //退回
            $backWeight = Db::name('collar_use')->where($mapBack)->where('back',$v)->sum('weight');
            if ($backWeight > 0){
                $backWeight = $backWeight / 1000;
            }
            //报废
            $scrap = Db::name('scrap_ink')->where(['order_number'=>$params,'bach_number'=>$v])->sum('weight');
            if ($scrap > 0){
                $scrap = $scrap / 1000;
            }
            $list[$k]['cid'] = Db::name('warehousing_detail')->where('bach_number',$v)->value('cid');
            $list[$k]['total'] = $outWeight - $backWeight - $scrap;
        }
        //查出所有专色，去掉重量为0的专色
        $map[] = ['exp',Db::raw("FIND_IN_SET($product_number,product_number)")];
        $allColor = Db::name('formula')->where($map)->field('id')->select();
        foreach ($allColor as $k1=>$v1){
            $allColor[$k1]['weight'] = 0;
            foreach ($list as $key1=>$value1){
                if ($value1['cid'] == $allColor[$k1]['id']){
                    $allColor[$k1]['weight'] = $allColor[$k1]['weight'] + $value1['total'];
                }
            }
            if ($allColor[$k1]['weight'] == 0){
                unset($allColor[$k1]);
            }

        }
        //根据配方计算消耗原墨重量
        $allColor = array_merge($allColor);
        $formulaData = Db::name('formula_detail')->where($map)->column('ink');
        $formulaData = array_merge(array_unique($formulaData));
        $returnData = [];
        $i = 0;
        foreach ($allColor as $key2=>$value2){
            $formula = Db::name('formula_detail')->where('father',$value2['id'])->field('father,ink,ink_number')->select();
            $totalInk = array_sum(array_column($formula,'ink_number'));
            foreach ($formula as $k2=>$v2){
//                $detailInk[$key2][$k2]['cid'] = $formula[$k2]['father'];
//                $detailInk[$key2][$k2]['ink'] = $formula[$k2]['ink'];
                $ratio = $formula[$k2]['ink_number'] /$totalInk ;
                $total = $value2['weight'] * $ratio * 1000;
//                $detailInk[$key2][$k2]['total'] = round($total);
                $returnData[$i]['ink'] = $v2['ink'];
                $returnData[$i]['total'] = round($total);
                $i++;
            }
        }
        foreach ($formulaData as $k3=>$v3){
            $lastData[$k3]['weight'] = 0;
            $lastData[$k3]['ink'] = $v3;
            foreach ($returnData as $key3=>$value3){
                if ($value3['ink'] == $v3){
                    $lastData[$k3]['weight'] = $lastData[$k3]['weight'] + $value3['total'];
                }
            }
        }
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = $lastData;

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '序号')
            ->setCellValue('B1', '原墨颜色')
            ->setCellValue('C1', '消耗重量(g)');


        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-1);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['ink']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['weight']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle($params.'专墨换算原墨报表');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="'.$params.'专墨换算原墨报表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件


    }
    //原墨导出
    public function matterOut(){
        $params = input('order_number');
        $where = [];
        if (!empty($params)){
            $where['order_number'] = $params;
        }
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        $list = Db::name('matter_use')
            ->where('status',1)
            ->where($where)
            ->field('product_name,out_weight,create')
            ->order('order_number asc')
            ->select();
        foreach ($list as $k=>$v){
            $list[$k]['ink_status'] = '原墨';
            $list[$k]['color'] = '';
            $list[$k]['weight'] = $v['out_weight'] / 1000;
            unset( $list[$k]['out_weight']);
        }
        // 实例化完了之后就先把数据库里面的数据查出来
        $sql = $list;

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '名称')
            ->setCellValue('B1', '油墨类别')
            ->setCellValue('C1', '领用重量(kg)')
            ->setCellValue('D1', '领用日期');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['ink_status']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['create']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle('原墨领用报表');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

        header('Content-Disposition: attachment;filename="原墨领用报表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

    }

    //php判断二维数组中是否含有某个值
    public function deep_in_array($value, $array) {
        foreach($array as $item) {
            if(!is_array($item)) {
                if ($item == $value) {
                    return true;
                } else {
                    continue;
                }
            }
            if(in_array($value, $item)) {
                return true;
            } else if($this->deep_in_array($value, $item)) {
                return true;
            }
        }
        return false;
    }
}