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', 'like', '%' . $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 = [1 => '专用', 2 => '普通', 3 => '电子专用', 4 => '电子普通', 5 => '全电子票'];//开票种类(发票类型)

        $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.viceCode=a.invNo 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 = [0 => '上传发票', 1 => '待识别验证', 2 => '待校验识别', 3 => '待财务审核', 4 => '待认证', 5 => '认证成功待确认完成', 6 => '回票完成7识别失败', 8 => '验证失败', 9 => '财务驳回', 10 => '认证失败', 11 => '校验超过次数第二天校验', 12 => '回票申请废弃已结束',];//认证状态
        $invoiceType = ['special' => '增值税专用发票', 'normal' => '增值税普通发票', 'electronic' => '增值税电子普通发票', 'special_electronic' => '增值税电子专用发票', 'toll' => '增值税电子普通发票(通行费)', 'roll' => '增值税普通发票(卷票)'];//发票类型

        $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 回票申请编号,DATE_FORMAT(a.addtime,"%Y") 年,DATE_FORMAT(a.addtime,"%m") 月,a.invoiceNumber 发票号,a.open_time 开票日期,a.inv_fee 金额,c.subtotal_tax 税额,"" 税率,b.total_fee 总额,b.supplierName 供应商,"采购回票" 业务类型,a.invoiceType 发票类型,a.status 认证状态,a.updatetime 认证时间,a.remark 备注')
            ->where($where)
            ->withAttr('发票类型', function ($val) use ($invoiceType) {
                return $invoiceType[$val] ?? '';
            })->withAttr('认证状态', function ($val) use ($status) {
                return $status[$val] ?? '';
            })
            ->order('a.addtime desc')
            ->select()
            ->toArray();

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

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

    //资金认领导出
    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 => '驳回'];//认领审批状态

        $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);
    }
}