cxinv/app/admin/controller/Export.php

<?php


namespace app\admin\controller;


use app\admin\BaseController;
use think\facade\Db;

class Export extends BaseController
{

    //销售发票申请信息导出
    public function invoicePoolExport()
    {
        $param = $this->request->only(['start' => '', 'end' => '', 'status' => '', 'inv_type' => '', 'inv_out' => '', 'invNo' => '', 'relaComNo' => '', 'inv_number' => '', 'inv_code' => '', 'inv_company' => '', 'buyer_name' => '', 'apply_id' => '', 'apply_name' => '',], 'post', 'trim');
        $where = [['a.is_del', '=', 0]];
        if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.addtime', 'between', [$param['start'] . ' 00:00:00', $param['end'] . ' 23:59:59']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['inv_type'] != '') $where[] = ['a.inv_type', '=', $param['inv_type']];
        if ($param['inv_out'] != '') $where[] = ['a.inv_out', '=', $param['inv_out']];
        if ($param['invNo'] != '') $where[] = ['a.invNo', 'in', $param['invNo']];
        if ($param['relaComNo'] != '') $where[] = ['a.inv_out', '=', $param['relaComNo']];
        if ($param['inv_number'] != '') $where[] = ['c.inv_number', 'like', '%' . $param['inv_number'] . '%'];
        if ($param['inv_code'] != '') $where[] = ['c.inv_code', 'like', '%' . $param['inv_code'] . '%'];
        if ($param['inv_company'] != '') $where[] = ['a.inv_company', 'like', '%' . $param['inv_company'] . '%'];
        if ($param['buyer_name'] != '') $where[] = ['b.buyer_title', 'like', '%' . $param['buyer_name'] . '%'];
        if ($param['apply_id'] != '') $where[] = ['a.apply_id', '=', $param['apply_id']];
        if ($param['apply_name'] != '') $where[] = ['a.apply_name', 'like', '%' . $param['apply_name'] . '%'];

        $open_type = [1 => '金税开票', 2 => '金税线下', 3 => '纯线下'];//开票方式
        $inv_type = config('invoiceType.invoiceName');//开票种类(发票类型)

        $list = Db::name('invoice_pool')
            ->alias('a')
            ->field('a.inv_out 卖方公司编码,a.inv_company 卖方公司名称,a.invNo 申请编号,f.department 申请部门,a.apply_name 申请人,b.buyer_title 发票买方公司名称,b.buyer_code 发票买方企业纳税识别号,b.buyer_addr 发票买方企业地址,b.buyer_mobile 发票买方企业联系方式,b.buyer_bank 发票买方企业收款银行,b.buyer_bankNo 发票买方企业财务账户,d.orderCode 公司订单号,b.buyer_title 订单企业客户,d.goodName 订单商品名,g.inv_cat_code 税收分类编码,g.inv_cat_name 税收分类名称,d.goodName 发票明细商品名称,CONCAT(d.catName,"*",d.goodName) 发票货物或应税劳务、服务名称,d.unitName 单位,d.goodNum 数量,d.goodPrice 单价,d.tax 税率,d.totalPrice 总价,a.inv_type 开票种类,a.open_type 开票方式,a.remark 申请备注,c.remark 发票备注')
            ->leftJoin('invoice_pool_info b', 'a.invNo=b.invNo')
            ->leftJoin('invoice_ticket c', 'a.invNo=c.invNo and c.type=0')
            ->leftJoin('invoice_good d', 'd.invNo=a.invNo')
            ->leftJoin('assoc e', 'e.orderCode=d.orderCode AND e.status=1 AND e.is_del=0')
            ->leftJoin('qrd_info f', 'f.sequenceNo=e.orderCode')
            ->leftJoin('good g', 'g.spuCode=d.goodNo')
            ->withAttr('开票方式', function ($val) use ($open_type) {
                return $open_type[$val] ?? '';
            })->withAttr('开票种类', function ($val) use ($inv_type) {
                return $inv_type[$val] ?? '';
            })
            ->where($where)
            ->order('a.addtime desc')
            ->select()
            ->toArray();

        if (empty($list)) $list[] = ['导出数据为空' => ''];

        excelExport('销售发票申请信息导出', array_keys($list[0]), $list);
    }

    //进项发票等级导出
    public function invListExport()
    {
        $param = $this->request->only(['start' => '', 'end' => '', 'invType' => '', 'companyNo' => '', 'relaComNo' => '', 'supplierNo' => '', 'hpNo' => '', 'payNo' => '', 'invoiceType' => '', 'status' => '', 'invoiceNumber' => '', 'checkApi' => '', 'invoiceCode' => '', 'open_start' => '', 'open_end' => '', 'apply_id' => '', 'apply_name' => ''], 'post', 'trim');

        $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
        if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.addtime', 'between', [$param['start'], $param['end']]];
        if ($param['invType'] != '') $where[] = ['a.invType', '=', $param['invType']];
        if ($param['companyNo'] != '') $where[] = ['b.companyNo', 'like', '%' . $param['companyNo'] . '%'];
        if ($param['relaComNo'] != '') $where[] = ['b.companyNo|b.supplierNo', 'like', '%' . $param['relaComNo'] . '%'];
        if ($param['supplierNo'] != '') $where[] = ['b.supplierNo', '=', $param['supplierNo']];
        if ($param['hpNo'] != '') $where[] = ['a.hpNo', 'like', '%' . $param['hpNo'] . '%'];
        if ($param['payNo'] != '') $where[] = ['a.payNo', 'like', '%' . $param['payNo'] . '%'];
        if ($param['invoiceType'] != '') $where[] = ['a.invoiceType', '=', $param['invoiceType']];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['invoiceNumber'] != '') $where[] = ['a.invoiceNumber', '=', $param['invoiceNumber']];
        if ($param['checkApi'] != '') $where[] = ['a.checkApi', '=', $param['checkApi']];
        if ($param['invoiceCode'] != '') $where[] = ['a.invoiceCode', '=', $param['invoiceCode']];
        if (($param['open_start'] != '') && ($param['open_end'] != '')) $where[] = ['a.open_time', 'between', [$param['open_start'], $param['open_end']]];
        if ($param['apply_id'] != '') $where[] = ['a.apply_id', '=', $param['apply_id']];
        if ($param['apply_name'] != '') $where[] = ['a.apply_name', 'like', '%' . $param['apply_name'] . '%'];

        $status = [1 => '待系统验证', 2 => '买方公司审核', 3 => '待买方公司认证', 4 => '认证成功', 5 => '验证失败', 6 => '买方审核驳回', 7 => '认证失败', 8 => '回票流程终止', 9 => '验证超次数', 10 => '回票已退'];//认证状态

        $invoiceType = config('invoiceType.invoiceName');//开票种类(发票类型)

        $list = Db::name('pay_invoice')
            ->alias('a')
            ->leftJoin('pay b', 'a.payNo=b.payNo')
            ->leftJoin('invoice_info c', 'c.hpNo=a.hpNo AND c.status=1')
            ->field('"" 序号,a.hpNo,a.addtime,a.invoiceNumber,a.open_time,b.supplierName,a.invoiceType,a.status,a.updatetime,a.remark,c.item_list,c.total')
            ->where($where)
            ->order('a.addtime desc')
            ->cursor();

        $data = [];
        $i = 1;
        foreach ($list as $item) {
            if ($item['item_list']) {
                $item_list = json_decode($item['item_list'], true);
                foreach ($item_list as $val) {
                    $data[] = [
                        '序号' => $i++,
                        '回票申请编号' => $item['hpNo'],
                        '年' => date('Y', strtotime($item['addtime'])),
                        '月' => date('m', strtotime($item['addtime'])),
                        '发票号' => $item['invoiceNumber'],
                        '开票日期' => $item['open_time'],
                        '金额' => $val['amount'],
                        '税额' => $val['tax'],
                        '税率' => $val['tax_rate'],
                        '总额' => $item['total'],
                        '供应商' => $item['supplierName'],
                        '业务类型' => '采购回票',
                        '发票类型' => $invoiceType[$item['invoiceType']] ?? '',
                        '认证状态' => $status[$item['status']] ?? '',
                        '认证时间' => $item['updatetime'],
                        '备注' => $item['remark'],
                    ];
                }
            } else {
                $data[] = [
                    '序号' => $i++,
                    '回票申请编号' => $item['hpNo'],
                    '年' => date('Y', strtotime($item['addtime'])),
                    '月' => date('m', strtotime($item['addtime'])),
                    '发票号' => $item['invoiceNumber'],
                    '开票日期' => $item['open_time'],
                    '金额' => '',
                    '税额' => '',
                    '税率' => '',
                    '总额' => $item['total'],
                    '供应商' => $item['supplierName'],
                    '业务类型' => '采购回票',
                    '发票类型' => $invoiceType[$item['invoiceType']] ?? '',
                    '认证状态' => $status[$item['status']] ?? '',
                    '认证时间' => $item['updatetime'],
                    '备注' => $item['remark'],
                ];
            }
        }

        if (empty($data)) $data[] = ['导出数据为空' => ''];

        excelExport('进项发票等级导出', array_keys($data[0]), $data);
    }

    //资金认领导出
    public function orderPayExport()
    {
        $param = $this->request->only(['start' => '', 'end' => '', 'name' => '', 'bank' => '', 'status' => '', 'tradNo' => '', 'companyNo' => '', 'userd_lower' => '', 'used_upper' => ''], 'post', 'trim');
        $where = [['a.is_del', '=', 0], ['b.is_del', '=', 0]];
        if (($param['start'] != '') && ($param['end'] != '')) $where[] = ['a.trade_time', 'between', [$param['start'], $param['end']]];
        if ($param['name'] != '') $where[] = ['a.trade_out', 'like', '%' . $param['name'] . '%'];
        if ($param['bank'] != '') $where[] = ['a.trade_bank', 'like', '%' . $param['bank'] . '%'];
        if ($param['status'] != '') $where[] = ['a.status', '=', $param['status']];
        if ($param['tradNo'] != '') $where[] = ['a.tradNo', 'like', '%' . $param['tradNo'] . '%'];
        if ($param['companyNo'] != '') $where[] = ['a.companyNo', 'like', '%' . $param['companyNo'] . '%'];
        if ($param['userd_lower'] != '') $where[] = ['a.used_fee', '>=', $param['userd_lower']];
        if ($param['used_upper'] != '') $where[] = ['a.used_fee', '<=', $param['used_upper']];

        $status = [1 => '未认领', 2 => '部分认领', 3 => '全部认领'];//状态
        $b_status = [1 => '待审批', 2 => '审批通过', 3 => '审批驳回', 4 => '退款', 5 => '解除认领'];//认领审批状态

        $list = Db::name('trade')
            ->alias('a')
            ->field('a.companyNo 收款方公司编码,a.trade_in 收款单位名称,a.trade_account 付款银行单位账号,a.trade_out 付款银行单位名称,a.trade_remark 付款备注,a.trade_time 交易时间,a.tradNo 资金编号,a.status 状态,a.total_fee 收入金额,a.balance 未认领金额,a.used_fee 已认领金额,b.logNo 资金认领编号,a.customerNo 认领企业,c.orderCode 订单编号,d.poCode 平台编号,d.goodName 产品名称,d.ownerName 资金创建人,c.apply_name 认领创建人,b.status 认领审批状态,b.remark 驳回原因')
            ->leftJoin('trade_pool b', 'b.tradNo=a.tradNo')
            ->leftJoin('assoc c', 'c.viceCode=b.logNo')
            ->leftJoin('qrd_info d', 'd.sequenceNo=c.orderCode')
            ->withAttr('状态', function ($val) use ($status) {
                return $status[$val] ?? '';
            })->withAttr('认领审批状态', function ($val) use ($b_status) {
                return $b_status[$val] ?? '';
            })
            ->where($where)
            ->order('a.trade_time desc')
            ->select()
            ->toArray();

        if (empty($list)) $list[] = ['导出数据为空' => ''];

        excelExport('资金认领导出', array_keys($list[0]), $list);
    }


   

}