huahuibao/biz-ghs/custom/classes/CustomBalanceChangeClass.php

<?php

namespace bizGhs\custom\classes;

use common\components\util;
use bizHd\base\classes\BaseClass;
use Yii;

class CustomBalanceChangeClass extends BaseClass
{

    public static $baseFile = '\bizGhs\custom\models\CustomBalanceChange';

    public static function getChangeList($where)
    {
        $data = self::getList('*', $where, 'addTime DESC,id DESC');
        return $data;
    }


    public static function exportData($respond, $mainId)
    {
        $orderList = $respond['list'] ?? [];
        if (empty($orderList)) {
            util::fail('没有数据需要导出');
        }

        $phpExcelFile = Yii::getAlias("@vendor/phpoffice/phpexcel/");
        require_once($phpExcelFile . 'Classes/PHPExcel.php');

        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("file");

        $ghsTitle = '余额变动记录';
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(date('n月j日') . " " . $ghsTitle);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.7);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);

        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '日期');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '客户ID');
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '客户名称');
        $objPHPExcel->getActiveSheet()->setCellValue('E1', '类型');
        $objPHPExcel->getActiveSheet()->setCellValue('F1', '支付方式');
        $objPHPExcel->getActiveSheet()->setCellValue('G1', '事项');
        $objPHPExcel->getActiveSheet()->setCellValue('H1', '人员');
        $objPHPExcel->getActiveSheet()->setCellValue('I1', '变动');
        $objPHPExcel->getActiveSheet()->setCellValue('J1', '余额');
        $objPHPExcel->getActiveSheet()->setCellValue('K1', '备注');

        //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);

        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setSize(9)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setSize(9)->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $baseRow = 2;
        foreach ($orderList as $key => $order) {
            $i = $baseRow + $key;
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $order['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $order['addTime']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $order['customId']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $order['customName']);
            $capitalType = $order['capitalType'];
            $capName = '-';
            if ($capitalType == 75) {
                $capName = '售后返充';
            }
            if ($capitalType == 62) {
                $capName = '线下充值';
            }
            if ($capitalType == 66) {
                $capName = '线上充值';
            }
            if ($capitalType == 64) {
                $capName = '结账';
            }
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $capName);
            $payWay = $order['payWay'];
            $payName = '-';
            if ($capitalType == 62 || $capitalType == 66) {
                if ($payWay == 0) {
                    $payName = '微信';
                }
                if ($payWay == 1) {
                    $payName = '支付宝';
                }
                if ($payWay == 2) {
                    $payName = '余额';
                }
                if ($payWay == 4) {
                    $payName = '现金';
                }
                if ($payWay == 5) {
                    $payName = '银行卡';
                }
            }
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $payName);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $order['event']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $order['staffName']);
            $amount = isset($order['io']) && $order['io'] == 1 ? '+'.$order['amount'] : '-'.$order['amount'];
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $amount);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $order['balance']);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $order['remark']);
            //居中
            $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }

        $fileName = '余额变动记录-' . time();
        $objPHPExcel->getActiveSheet()->setTitle($fileName);
        $objPHPExcel->setActiveSheetIndex(0);
        $dir = './priceTable/' . $mainId;
        if (file_exists($dir) == false) {
            mkdir($dir, 0777, true);
        }
        $date = $fileName;
        $file = $date . '.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        if (file_exists($dir . '/' . $file)) {
            unlink($dir . '/' . $file);
        }
        $objWriter->save($dir . '/' . $file);
        $fileUrl = Yii::$app->params['ghsHost'] . '/priceTable/' . $mainId . '/' . $file;
        util::success(['file' => $fileUrl, 'shortFile' => $file]);
    }

}