myc/ln/services/ExcelService.php

<?php

// +----------------------------------------------------------------------
// | CRMEB [ CRMEB赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2020 https://www.crmeb.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed CRMEB并不是自由软件，未经许可不能去掉CRMEB相关版权
// +----------------------------------------------------------------------
// | Author: CRMEB Team <admin@crmeb.com>
// +----------------------------------------------------------------------

namespace crmeb\services;

use app\common\repositories\store\order\StoreImportDeliveryRepository;
use app\common\repositories\store\order\StoreOrderRepository;
use app\common\repositories\store\ExcelRepository;
use app\common\repositories\store\order\StoreRefundOrderRepository;
use app\common\repositories\system\merchant\FinancialRecordRepository;
use app\common\repositories\system\merchant\MerchantIntentionRepository;
use app\common\repositories\user\UserBillRepository;
use app\common\repositories\user\UserVisitRepository;
use think\Exception;
use think\facade\Db;

class ExcelService
{

    public function getAll($data)
    {
        set_time_limit(0);
        ini_set('memory_limit', '-1');
        $this->{$data['type']}($data['where'], $data['excel_id']);
    }

    /**
     * TODO 导出操作
     * @param $id
     * @param $path
     * @param $header
     * @param $title
     * @param array $export
     * @param string $filename
     * @param array $end
     * @param string $suffix
     * @author Qinii
     * @day 3/17/21
     */
    public function export($id, $path, $header, $title, $export = [], $filename = '',$end = [],$suffix = 'xlsx')
    {
        try{
            $_path = SpreadsheetExcelService::instance()
                ->createOrActive()
                ->setExcelHeader($header,count($title['mark']) + 2)
                ->setExcelTile($title)
                ->setExcelContent($export)
                ->setExcelEnd($end)
                ->excelSave($filename, $suffix, $path);

            app()->make(ExcelRepository::class)->update($id,[
                'name' => $filename.'.'.$suffix,
                'status' => 1,
                'path' => '/'.$_path
            ]);
        }catch (Exception $exception){
            app()->make(ExcelRepository::class)->update($id,[
                'name' => $filename.'.'.$suffix,
                'status' => 2,
                'message' => $exception->getMessage()
            ]);
        }
    }

    /**
     * TODO 搜索记录导出
     * @param array $where
     * @param int $id
     * @author xaboy
     * @day 6/10/21
     */
    public function searchLog(array $where, int $id)
    {
        $query = app()->make(UserVisitRepository::class)->search($where);

        $header = ['序号', '用户ID', '用户昵称', '用户类型', '搜索词', '搜索时间', '首次访问时间'];
        $title = [
            'title' => '搜索记录',
            'sheets' => '搜索记录',
            'mark' => ['生成时间:' . date('Y-m-d H:i:s', time())],
        ];
        $searchLog = [];
        $user_type = [
            'h5' => 'H5',
            'wechat' => '公众号',
            'routine' => '小程序',
        ];

        $query->with(['user' => function ($query) {
            $query->field('uid,nickname,avatar,user_type,create_time');
        }])->order('create_time DESC')->chunk(100, function ($logs) use (&$export, $user_type) {
            foreach ($logs as $log) {
                $searchLog[] = [
                    $log['user_visit_id'],
                    $log['user'] ? $log['user']['uid'] : '未登录',
                    $log['user'] ? $log['user']['nickname'] : '未知',
                    $log['user'] ? ($user_type[$log['user']['user_type']] ?? $log['user']['user_type']) : '未知',
                    $log['content'],
                    $log['create_time'],
                    $log['user'] ? $log['user']['create_time'] : '未知',
                ];
            }
        });

        $filename = '搜索记录_' . date('YmdHis');
        $end = [];
        return $this->export($id, 'searchLog', $header, $title, $searchLog, $filename, $end);
    }

    /**
     * TODO 导出订单
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 2020-08-10
     */
    public function order(array $where,int $id)
    {
        $make = app()->make(StoreOrderRepository::class);
        $status = $where['status'];
        $del = $where['mer_id'] > 0 ? 0 : null;
        unset($where['status']);
        $query = $make->search($where,$del)->where($make->getOrderType($status))->with([
            'orderProduct',
            'merchant' => function ($query) {return $query->field('mer_id,mer_name');},
            'user.spread'
        ])->order('order_id ASC');

        $header = ['序号','订单编号','订单类型','推广人','用户信息', '商品名称','商品规格','单商品总数','商品价格(元)','优惠','实付邮费(元)','实付金额(元)','已退款金额(元)', '收货人','收货人电话','收货地址','物流单号/送货人电话','下单时间','支付方式','支付状态','商家备注'];
        $title = [
            'title' => '订单列表',
            'sheets' => '订单信息',
            'mark' => ['生成时间:' . date('Y-m-d H:i:s',time())],
        ];
        $export = [];
        $limit = 20;
        $count = ceil(($query->count())/$limit);
        $i = 1;
        $paytype = [
            0 => '余额',
            1 => '微信',
            2 => '小程序',
            3 => 'H5',
            4 => '支付宝',
            5 => '支付宝扫码',
            6 => '微信扫码',
        ];
        for( $page = 1; $page <= $count; $page++ ) {
            $list = $query->page($page, $limit)->select()->each(function($item){
                $item['refund_price'] = app()->make(StoreRefundOrderRepository::class)->refundPirceByOrder([$item['order_id']]);
                return $item;
            });
            foreach ($list as $item){
                foreach ($item['orderProduct'] as $key => $value){
                    $export[] = [
                        $i,
                        $item['order_sn'],
                        $item['order_type'] ? '核销订单':'普通订单',
                        $item['user']['spread']['nickname'],
                        $item['user']['nickname'],
                        $value['cart_info']['product']['store_name'],
                        $value['cart_info']['productAttr']['sku'],
                        $value['product_num'],
                        $value['cart_info']['productAttr']['price'],
                        ($key == 0 ) ? $item['coupon_price'] : 0,
                        ($key == 0 ) ? $item['pay_postage'] : 0,
                        $value['product_price'],
                        ($key == 0 ) ? $item['refund_price'] : 0,
                        $item['real_name'],
                        $item['user_phone'],
                        $item['user_address'],
                        $item['delivery_id'],
                        $item['create_time'],
                        $paytype[$item['pay_type']],
                        $item['paid'] ? '已支付':'未支付',
                        $item['remark']
                    ];
                    $i++;
                }
            }
        }

        $filename = '订单列表_'.date('YmdHis');
        $end = [];
        return $this->export($id, 'order', $header, $title, $export,$filename, $end);
    }

    /**
     * TODO 流水记录导出
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 2020-08-10
     */
    public function financial(array $where,int $id)
    {
        $_key = [
            'mer_accoubts' => '财务对账',
            'sys_accoubts' => '财务对账',
            'refund_order' => '退款订单',
            'brokerage_one' => '一级分佣',
            'brokerage_two' => '二级分佣',
            'refund_brokerage_one' => '返还一级分佣',
            'refund_brokerage_two' => '返还二级分佣',
            'order' => '订单支付',
        ];
        $make = app()->make(FinancialRecordRepository::class);
        $query = $make->search($where)->with(['merchant']);


        $header = ['序号','商户名称','交易流水单号','总订单号','子订单号','用户名','用户ID','交易类型','收入/支出','金额','创建时间'];
        $title = [
            'title' => '流水列表',
            'sheets' => '流水信息',
            'mark' => ['生成时间:' . date('Y-m-d H:i:s',time())],
        ];

        $export = [];
        $limit = 20;
        $count = ceil(($query->count())/$limit);
        $i = 1;
        $order_make = app()->make(StoreOrderRepository::class);
        for( $page = 1; $page <= $count; $page++ ) {
            $list = $query->page($page,$limit)->select();
            foreach ($list as $k => $v) {
                $order = $order_make->get($v['order_id']);
                $export[] = [
                    $i,
                    $v['merchant']['mer_name'],
                    $v['financial_record_sn'],
                    $order['groupOrder']['group_order_sn'] ?? '无数据',
                    $v['order_sn'],
                    $v['user_info'],
                    $v['user_id'],
                    $_key[$v['financial_type']],
                    $v['financial_pm'] ? '收入' : '支出',
                    ($v['financial_pm'] ? '+ ' : '- ') . $v['number'],
                    $v['create_time'],
                ];
                $i++;
            }
        }

        $filename = '流水列表_'.date('YmdHis');

        return $this->export($id,'financial',$header,$title,$export,$filename,[],'xlsx');
    }

    /**
     * TODO 获取待发货订单 发货信息
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 3/13/21
     */
    public function delivery(array $where,int $id)
    {
        $make = app()->make(StoreOrderRepository::class);
        $query = $make->search($where)->where('order_type',0)->with(['orderProduct'])->order('order_id ASC');
        $header =    ['序号','订单编号','物流公司','物流编码','物流单号', '发货地址','用户信息','手机号','商品信息','支付时间'];
        $title = [
            'title' => '批量发货单',
            'sheets' => '发货信息',
            'mark' => ['生成时间:' . date('Y-m-d H:i:s',time())],
        ];
        $filename = '批量发货单_'.date('YmdHis');
        $export = [];
        $limit = 20;
        $count = ceil(($query->count())/$limit);
        $i = 1;
        for( $page = 1; $page <= $count; $page++ ) {
            $data = $query->page($page,$limit)->select();
            foreach ($data as $key => $item){
                $product = '';
                foreach ($item['orderProduct'] as $value){
                    $product = $product.$value['cart_info']['product']['store_name'].'【'. $value['cart_info']['productAttr']['sku'] .'】【' . $value['product_num'].'】'.PHP_EOL;
                }
                $export[] = [
                    $i,
                    $item['order_sn'] ?? '',
                    '',
                    $item['delivery_name']??"",
                    $item['delivery_id']??"",
                    $item['user_address']??"",
                    $item['real_name'] ?? '',
                    $item['user_phone'] ?? '',
                    $product,
                    $item['pay_time'] ?? '',
                ];
                $i++;
            }
        }
        $end = [];
        return $this->export($id,'delivery',$header,$title,$export,$filename,$end);
    }

    /**
     * TODO 导出 发货导入记录
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 3/17/21
     */
    public function importDelivery(array $where,int $id)
    {
        $make = app()->make(StoreImportDeliveryRepository::class);
        $query = $make->getSearch($where)->order('create_time ASC');
        $title = [
            'title' => '发货记录',
            'sheets' => '发货信息',
            'mark' => [
                '生成时间:' . date('Y-m-d H:i:s',time())
            ],
        ];
        $header = ['序号','订单编号','物流公司','物流单号', '发货状态','备注'];
        $filename = '发货单记录_'.date('YmdHis');
        $export = [];
        $limit = 20;
        $count = ceil(($query->count())/$limit);
        $i = 1;
        for( $page = 1; $page <= $count; $page++ ) {
            $data = $query->page($page,$limit)->select();
            foreach ($data as $key => $item){
                $export[] = [
                    $i,
                    $item['order_sn'],
                    $item['delivery_name'],
                    $item['delivery_id'],
                    $item['status'],
                    $item['mark'],
                ];
                $i++;
            }

        }
        $end = [];
        return $this->export($id,'delivery',$header,$title,$export,$filename,$end);
    }

    /**
     * TODO 平台/商户 导出日月账单信息
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 3/25/21
     */
    public function exportFinancial(array $where,int $id)
    {
        /*
           order 收入 公共 新订单
           brokerage_one 支出 公共 一级佣金
           brokerage_two 支出 公共 二级佣金
           order_charge 支出 商户 手续费
           order_true 支出 平台 商户入账
           refund_order 支出 公共 退款
           refund_brokerage_one 收入 公共 返还一级佣金
           refund_brokerage_two 收入 公共 返还二级佣金
           refund_charge 收入 商户 返还手续费
           refund_true 收入 平台 商户返还入账
           presell 收入 公共 新订单
           presell_charge 支出 商户 手续费
           presell_true 支出 平台 商户入账
        */
        $financialType = [
            'order'         => '订单支付',
            'presell'       => '预售订单（尾款）',
            'brokerage_one' => '一级佣金',
            'brokerage_two' => '二级佣金',
            'order_charge'  => '手续费',
            'order_true'    => '商户入账',
            'refund_order'  => '退款',
            'refund_charge' => '返还手续费',
            'refund_true'   => '商户返还入账',
            'presell_charge'=> '预售订单（手续费）',
            'presell_true'  => '商户入账',
            'refund_brokerage_one' => '返还一级佣金',
            'refund_brokerage_two' => '返还二级佣金',
            'mer_presell'   => '预售订单（总额）',
            'order_presell'   => '预售订单（定金）'
        ];
        $sys_pm_1 = ['order','presell','order_charge','order_presell','presell_charge','refund_brokerage_one','refund_brokerage_two'];
        $mer_pm_1 = ['order','presell','refund_charge','refund_brokerage_one','refund_brokerage_two','mer_presell'];
        $date_ = $where['date'];unset($where['date']);
        $make = app()->make(FinancialRecordRepository::class);

        $query = $make->search($where)->with(['orderInfo','refundOrder','merchant.merchantCategory']);

        if($where['type'] == 1){
            $title_ = '日账单';
            $start_date = $date_.' 00:00:00';
            $end_date = $date_.' 23:59:59';
            $query->whereDay('create_time',$date_);
        }else{
            $title_ = '月账单';
            $start_date = (date('Y-m-01', strtotime($date_)));
            $end_date = date('Y-m-d', strtotime("$start_date +1 month -1 day"));
            $query->whereMonth('create_time',$date_);
        }



        $income = $make->countIncome($where['type'],$where,$date_);
        $expend = $make->countExpend($where['type'],$where,$date_);
        $refund = $make->countRefund($where['type'],$where,$date_);
        $charge = bcsub($income['number'],$expend['number'],2);
        $filename = $title_.'('.$date_.')'.time();
        $title = [];
        $header = [];
        $export = [];
        $end = [];
        $limit = 20;
        $count = ceil(($query->count())/$limit);
        $i = 1;
        $order_make = app()->make(StoreOrderRepository::class);
        //平台
        if(!$where['is_mer']){
            $header = ['序号','商户类别','商户分类','商户名称','总订单号','订单编号','交易流水号','交易时间', '对方信息','交易类型','收支金额','备注'];
            for( $page = 1; $page <= $count; $page++ ) {
                $list = $query->page($page, $limit)->order('create_time DESC')->select();
                foreach ($list as $key => $value) {
                    $order = $order_make->get($value['order_id']);
                    $export[] = [
                        $i,
                        $value['merchant']['is_trader'] ? '自营' : '非自营',
                        $value['merchant']['merchantCategory']['category_name'] ?? '平台',
                        $value['merchant']['mer_name'] ?? '平台',
                        $order['groupOrder']['group_order_sn'] ?? '无数据',
                        $value['order_sn'],
                        $value['financial_record_sn'],
                        $value['create_time'],
                        $value['user_info'],
                        $financialType[$value['financial_type']],
                        (in_array($value['financial_type'], $sys_pm_1) ? '+' : '-') . $value['number'],
                        ''
                    ];
                    $i++;
                }
            }
            $end = [
                '合计：平台应入账手续费 '.$charge,
                '收入合计： '.'订单支付'.$income['count'].'笔,'.'实际支付金额共:'.$income['number'].'元;',
                '支出合计： '.'佣金支出'.$expend['count_brokerage'].'笔,支出金额：'.$expend['number_brokerage'].'元；商户入账支出'.$expend['count_order'].'笔，支出金额：'.$expend['number_order'].'元；退款手续费'.$expend['count_charge'].'笔，支出金额'.$expend['number_charge'].'元；合计支出'.$expend['number'],
            ];
         //商户
        }else{
            $header = ['序号','总订单号','子订单编号','交易流水号','交易时间', '对方信息','交易类型','收支金额','备注'];
            $mer_name = '';

            for( $page = 1; $page <= $count; $page++ ) {
                $list = $query->page($page, $limit)->order('create_time DESC')->select();


                foreach ($list as $key => $value) {
                    $order = $order_make->get($value['order_id']);
                    $export[] = [
                        $i,
                        $order['groupOrder']['group_order_sn'] ?? '无数据',
                        $value['order_sn'],
                        $value['financial_record_sn'],
                        $value['create_time'],
                        $value['user_info'],
                        $financialType[$value['financial_type']],
                        (in_array($value['financial_type'], $mer_pm_1) ? '+' : '-') . $value['number'],
                        ''
                    ];
                    $i++;
                    $mer_name = $mer_name ? $mer_name : ($value['merchant']['mer_name'] ?? '');
                }
            }
            $count_brokeage = $expend['count_brokerage'] + $expend['count_refund_brokerage'];
            $number_brokeage = bcsub($expend['number_brokerage'],$expend['number_refund_brokerage'],2);
            $count_charge = $expend['count_charge']+$expend['count_order_charge'];
            $number_charge = bcsub($expend['number_order_charge'],$expend['number_charge'],2);
            $end = [
                '合计：商户应入金额 '.$charge,
                '收入合计： '.'订单支付'.$income['count'].'笔,'.'实际支付金额共:'.$income['number'].'元;',
                '支出合计： '.'佣金支出'.$count_brokeage.'笔,支出金额：'.$number_brokeage.'元；退款'.$expend['count_refund'].'笔，支出金额:'.$expend['number_refund'].'元；平台手续费'.$count_charge.'笔，支出金额：'.$number_charge.'元；合计支出金额：'.$expend['number'].'元；',
                //'商户应入金额 '.$charge,
            ];
            $mer_name = '商户名称:'.$mer_name;
        }

        $title = [
            'title' => $title_,
            'sheets' => $title_.'信息',
            'mark' => [
                $mer_name ?? '平台',
                '结算账期：【' .$start_date.'】至【'.$end_date.'】',
                '生成时间:' . date('Y-m-d H:i:s',time())
            ],
        ];
        return $this->export($id,'financial',$header,$title,$export,$filename,$end);
    }

    /**
     * TODO 退款单导出
     * @param array $where
     * @param int $id
     * @author Qinii
     * @day 6/10/21
     */
    public function refundOrder(array $where,int $id)
    {
        $query = app()->make(StoreRefundOrderRepository::class)->search($where)->where('is_system_del', 0)->with(['order' => function ($query) {
            $query->field('order_id,order_sn,activity_type,real_name,user_address');
        }, 'refundProduct.product', 'user' => function ($query) {
            $query->field('uid,nickname,phone');
        }])->order('create_time DESC');

        $title = [
            'title' => '退款订单',
            'sheets' => '退款单信息',
            'mark' => [
                '生成时间:' . date('Y-m-d H:i:s',time())
            ],
        ];
        $header = ['序号','退款单号','申请时间','审核时间', '退款金额','退货件量','退货商品信息','退款类型','订单状态','拒绝理由','退货人','退货地址','相关订单号','退货物流公司','退货物流单号','备注'];
        $filename = '退款订单'.time();
        $path = 'refundOrder';

        $status = [
            0 => '待审核',
            1 => '待退货',
            2 => '待收货',
            3 => '已退款',
            -1=> '审核未通过',
        ];
        $limit = 20;
        $count= ceil(($query->count())/$limit);
        $i = 1;
        for( $page = 1; $page <= $count; $page++ ){
            $data = $query->page($page,$limit)->select()->toArray();
            foreach ($data as $k => $datum){
                $product = '';
                foreach ($datum['refundProduct'] as $value){
                    $product .= '【'.$value['product']['cart_info']['product']['product_id'].'】'.$value['product']['cart_info']['product']['store_name'].'*'.$value['refund_num'].$value['product']['cart_info']['product']['unit_name'].PHP_EOL;
                }
                $export[] = [
                    $i,
                    $datum['refund_order_sn'],
                    $datum['create_time'],
                    $datum['status_time'],
                    $datum['refund_price'],
                    $datum['refund_num'],
                    $product,
                    ($datum['refund_type'] == 1 ) ? '仅退款' : '退款退货',
                    $status[$datum['status']],
                    $datum['fail_message'],
                    $datum['order']['real_name'],
                    $datum['order']['user_address'],
                    $datum['order']['order_sn'],
                    $datum['delivery_type'],
                    $datum['delivery_id'],
                    $datum['mark'],
                ];
                $i++;
            }

        }
        $end = [];
        $this->export($id, $path, $header, $title, $export, $filename);
    }

    /**
     * TODO 积分日志导出
     * @param $where
     * @param $id
     * @author Qinii
     * @day 6/10/21
     */
    public function integralLog($where,$id)
    {
        $title = [
            'title' => '积分日志',
            'sheets' => '积分日志',
            'mark' => [
                '生成时间:' . date('Y-m-d H:i:s',time())
            ],
        ];
        $header = ['用户ID','用户昵称','积分标题','变动积分', '当前积分余额','备注','时间'];
        $filename = '积分日志'.time();
        $path = 'integralLog';
        $export = [];
        app()->make(UserBillRepository::class)->searchJoin($where)->order('a.create_time DESC')
            ->chunk(100,function($list) use(&$export){
                foreach ($list as $item) {
                    $export[] = [
                        $item['uid'],
                        $item['nickname'],
                        $item['title'],
                        $item['number'],
                        $item['balance'],
                        $item['mark'],
                        $item['create_time'],
                    ];
                }
            });
        $this->export($id, $path, $header, $title, $export, $filename);
    }

    public function intention($where,$id)
    {
        $title = [
            'title' => '申请列表',
            'sheets' => '申请列表',
            'mark' => [
                '生成时间:' . date('Y-m-d H:i:s',time())
            ],
        ];
        $header = ['商户姓名','联系方式','备注','店铺名称','店铺分类','时间'];
        $filename = '申请列表'.time();
        $path = 'intention';
        $export = [];
        app()->make(MerchantIntentionRepository::class)->search($where)->with(['merchantCategory', 'merchantType'])->order('a.create_time DESC')
            ->chunk(100,function($list) use(&$export){
                foreach ($list as $item) {
                    $export[] = [
                        $item['name'],
                        $item['phone'],
                        $item['mark'],
                        $item['mer_name'],
                        $item['category_name'],
                        $item['create_time'],
                    ];
                }
            });
        $this->export($id, $path, $header, $title, $export, $filename);
    }
}