sina-video/app/admin/controller/Award.php

<?php



namespace app\admin\controller;

use think\admin\Controller;
use think\admin\extend\DataExtend;
use think\admin\helper\QueryHelper;
use think\admin\service\AdminService;
use think\admin\service\MenuService;
use think\admin\service\NodeService;
use think\facade\Db;

/**
 * 奖品订单管理
 * Class Menu
 * @package app\admin\controller
 */
class Award extends Controller
{

    /**
     * 当前操作数据库
     * @var string
     */
    private $table = 'Order';

    /**
     * 排行榜前50名
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function index()
    {
//        $lists = Db::query("select l.id, l.uid, u.nickname, o.name, o.mobile, o.address, g.name as giftName, FROM_UNIXTIME(o.create_at) as createAt, l.duration, l.number from awards_user_task_log as l
//    left join awards_user_info as u on l.uid = u.uid
//    left join awards_order as o on o.uid = l.uid
//    left join awards_gift as g on o.gift_id = g.id
//where u.portrait != '' and u.portrait is not null  and l.number >= 15 group by l.uid order by l.id desc limit 100");

        $lists = Db::query("select u.uid,u.nickname, o.name, o.mobile, o.address, g.name as giftName, o.rank as giftName from awards_order as o 
    left join awards_user_info as u on o.uid = u.uid 
    left join awards_gift as g on o.gift_id = g.id where o.type = 2 order by rank asc");
        $this->assign('lists', $lists);
        $this->fetch();
    }

    /**
     * 导出排行榜前50名
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function award()
    {

        $data = Db::query("select u.uid, u.nickname, o.name, o.mobile, o.address, g.name as giftName, o.rank as giftName from awards_order as o 
    left join awards_user_info as u on o.uid = u.uid 
    left join awards_gift as g on o.gift_id = g.id where o.type = 2 order by rank asc");

        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        $letter = explode(',', "A,B,C,D,E,F,G");
        $arrHeader = ['微博UID', '微博昵称', '收件人姓名', '联系人电话', '收件地址', '奖品名称','排名'];
        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($data as $k => $v) {
            $k += 2;
            //表格内容
            $objActSheet->setCellValue('A' . $k, $v['uid']." ");
            $objActSheet->setCellValue('B' . $k, $v['nickname']);
            $objActSheet->setCellValue('C' . $k, $v['name']);
            $objActSheet->setCellValue('D' . $k, $v['mobile']." ");
            $objActSheet->setCellValue('E' . $k, $v['address']);
            $objActSheet->setCellValue('F' . $k, $v['giftName']);
            $objActSheet->setCellValue('G' . $k, $v['rank']);
        }

        $outfile = "排行榜前50名" . date('Ymd') . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /**
     * 中奖信息
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function luckDraw()
    {
        $this->_query($this->table)->layTable(function (){
            $this->title = '中奖信息';
            $this->type = input('type', 'luckDraw');
        },function (QueryHelper $query){
            $query->alias('o')
                ->leftJoin('awards_user_info u', 'o.uid = u.uid')
                ->leftJoin('awards_gift g', 'o.gift_id = g.id')
                ->field(['o.id', 'u.uid', 'u.nickname', 'o.name', 'o.mobile', 'o.address', 'g.name as giftName', 'FROM_UNIXTIME(o.create_at) as createAt'])
                ->where('o.type', 1)
                ->order('o.id desc');
            $query->equal('o.uid')->like('u.nickname');
        });
    }

    /**
     * 导出中奖信息
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function drawAward()
    {
        $data = $this->app->db->name('Order')->alias('o')
            ->leftJoin('awards_user_info u', 'o.uid = u.uid')
            ->leftJoin('awards_gift g', 'o.gift_id = g.id')
            ->field(['o.id', 'o.uid', 'u.nickname', 'o.name', 'o.mobile', 'o.address', 'g.name as giftName', 'FROM_UNIXTIME(o.create_at) as createAt'])
            ->where('o.type', 1)
            ->order('o.id desc')->select();

        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        $letter = explode(',', "A,B,C,D,E,F,G");
        $arrHeader = ['微博UID', '微博昵称', '收件人姓名', '联系人电话', '收件地址', '奖品名称','中奖时间'];
        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($data as $k => $v) {
            $k += 2;
            //表格内容
            $objActSheet->setCellValue('A' . $k, $v['uid']." ");
            $objActSheet->setCellValue('B' . $k, $v['nickname']);
            $objActSheet->setCellValue('C' . $k, $v['name']);
            $objActSheet->setCellValue('D' . $k, $v['mobile']." ");
            $objActSheet->setCellValue('E' . $k, $v['address']);
            $objActSheet->setCellValue('F' . $k, $v['giftName']);
            $objActSheet->setCellValue('G' . $k, $v['createAt']);
        }

        $outfile = "中奖信息" . date('Ymd') . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    public function dataEncode() {

        $orders = $this->app->db->name('Order')->select();

        foreach ($orders as $order) {

            $name = $this->dataEncrypt($order['name']);
            $mobile = $this->dataEncrypt($order['mobile']);
            $address = $this->dataEncrypt($order['address']);

            if (strlen($name) > 15) {
                continue;
            }

            $this->app->db->name('Order')->where('id', $order['id'])->update([
                'name' => $name,
                'mobile' => $mobile,
                'address' => $address
            ]);
        }

        $this->success('加密成功');
    }

    public function dataEncrypt($str)
    {
        $aesKey = "sinaVideo1234";
        $data = openssl_encrypt($str, 'AES-128-ECB', $aesKey, OPENSSL_RAW_DATA);
        return base64_encode($data);
    }

    public function dataDecrypt($str)
    {
        $aesKey = "sinaVideo1234";
        return openssl_decrypt(base64_decode($str), 'AES-128-ECB', $aesKey, OPENSSL_RAW_DATA);
    }
}