Thinkphp5.1 使用笔记分享

tp5 常用写法总结 tp5 常用写法总结

模型 where多条件关联查询 like 查询

$where = [];
$where[] = ['cardno', '=', $list["start_code"]];
$where[] = ['cardno', 'between', [$list["start_code"],$list["end_code"]]];

$where 列表的过滤

 public function index()
    {
        $list = $this->request->param();
        $where = [];
        if($list){
            if (isset($list['start_code']) && !empty($list['start_code'])) {
                $where[] = ['cardno', '=', $list["start_code"]];
            }
            if (isset($list['end_code']) && !empty($list['end_code'])) {
                $where[] = ['cardno', '=', $list["end_code"]];
            }
            if (isset($list['start_code']) && !empty($list['start_code']) && isset($list['end_code']) && !empty($list['end_code'])) {
                $where[] = ['cardno', 'between', [$list["start_code"],$list["end_code"]]];
            }
            if (isset($list['active']) && !empty($list['active'])) {
                $where[] = ['active', '=', $list["active"]];
            }
            if (isset($list['sale']) && !empty($list['sale'])) {
                $where[] = ['sale', '=', $list["sale"]];
            }
            $res = CardModel::with('goods')->where($where)->order('id','desc')->paginate(15, false, ['query' => $this->request->param()]);
        }else{
            $res = CardModel::with('goods')->order('id','desc')->paginate(15);
        }
        
    	$goods = GoodsModel::all();
        $reperson = Reperson::all();
    	$this->assign('goods',$goods);
        $this->assign('list', $res);
        $this->assign('reperson', $reperson);
        $this->assign('search', $list);
        return $this->view->fetch();
    }

$where 闭包$query 的 写法

public function deleteAllCode(){
    	$res = CardModel::destroy(function($query){
            $query->where('cardno', 'between', [ $this->request->param('start_code'), $this->request->param('end_code') ]);
        });
    	if($res){
    		return $this->success('批量删除成功！');
    	}
    }

excel的导入和导出

excel的导入

public function importExecl($file)
    {
        if (!file_exists($file)) {
            return array("error" => 0, 'message' => 'file not found!');
        }
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        try {
            $PHPReader = $objReader->load($file);
        } catch (Exception $e) {
        }
        if (!isset($PHPReader)) return array("error" => 0, 'message' => 'read error!');
        $allWorksheets = $PHPReader->getAllSheets();
        $i = 0;
        foreach ($allWorksheets as $objWorksheet) {
            $sheetname = $objWorksheet->getTitle();
            $allRow = $objWorksheet->getHighestRow();//how many rows
            $highestColumn = $objWorksheet->getHighestColumn();//how many columns
            $allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $array[$i]["Title"] = $sheetname;
            $array[$i]["Cols"] = $allColumn;
            $array[$i]["Rows"] = $allRow;
            $arr = array();
            $isMergeCell = array();
            foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells
                foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }
            for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
                $row = array();
                for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++) {
                    ;
                    $cell = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                    $afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn + 1);
                    $bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn - 1);
                    $col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
                    $address = $col . $currentRow;
                    $value = $objWorksheet->getCell($address)->getValue();
                    if (substr($value, 0, 1) == '=') {
                        return array("error" => 0, 'message' => 'can not use the formula!');
                        exit;
                    }
                    if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
                        $cellstyleformat = $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat();
                        $formatcode = $cellstyleformat->getFormatCode();
                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
                            $value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
                        } else {
                            $value = PHPExcel_Style_NumberFormat::toFormattedString($value, $formatcode);
                        }
                    }
                    if ($isMergeCell[$col . $currentRow] && $isMergeCell[$afCol . $currentRow] && !empty($value)) {
                        $temp = $value;
                    } elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$col . ($currentRow - 1)] && empty($value)) {
                        $value = $arr[$currentRow - 1][$currentColumn];
                    } elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$bfCol . $currentRow] && empty($value)) {
                        $temp = $value;
                    }
                    $row[$currentColumn] = $value;
                }
                $arr[$currentRow] = $row;
            }
            $array[$i]["Content"] = $arr;
            $i++;
        }
        spl_autoload_register(array('Think', 'autoload'));//must, resolve ThinkPHP and PHPExcel conflicts
        unset($objWorksheet);
        unset($PHPReader);
        unset($PHPExcel);
        unlink($file);
        return array("error" => 1, "data" => $array);
    }

excel的导出

protected function exportExcel($expTitle, $expCellName, $expTableData)
    {
        $topNumber = 2;
        // $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
        $fileName = $expTitle . date('_YmdHis');
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        $objPHPExcel = new \PHPExcel();
        $cellName = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
        ];
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . date('Y-m-d H:i:s'));

        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }

        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
       	ob_end_clean();
        header('pragma:public');
        header('Cache-Control: max-age=0');//禁止缓存
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $expTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls 调用工厂类
        // return $objWriter->save('php://output');
        $objWriter->save('exports/'.$expTitle.date('YmdHis').'.xls');
        $url = $this->request->domain().'/exports/'.$expTitle.date('YmdHis').'.xls';
        return $this->success('导出成功',$url);
    }

excel 使用案例

public function down()
    {
        // 读取数据表信息
        $res = CardModel::with(['goods', 'reperson'])->select();
        foreach ($res as $vo) {
            $list[] = [
                'id'             => $vo['id'],
                'cardno'         => $vo['cardno'],
                'encrypt_cardno' => $vo['encrypt_cardno'],
                'sale'           => $vo['sale'] == 1 ? '已销售' : '未销售',
                'active'         => $vo['active'] == 1 ? '已激活' : '未激活',
                'name'           => $vo['reperson']['name'],
                'title'          => $vo['goods']['title'],
                'price'          => $vo['goods']['price'],
                'active_time'    => $vo['active_time'],
                'sale_time'      => $vo['sale_time'],
                'record_time'    => $vo['record_time']
            ];
        }
        $xlsName = "卡片核销信息";
        $xlsCell = [
            ['id', '序号'],
            ['cardno', '表卡号'],
            ['encrypt_cardno', '加密卡'],
            ['sale', '是否销售'],
            ['active', '是否激活'],
            ['name', '销售人'],
            ['title', '商品名称'],
            ['price', '价格'],
            ['active_time', '激活时间'],
            ['sale_time', '销售时间'],
            ['record_time', '核销时间']
        ];
        $this->exportExcel($xlsName, $xlsCell, $list);
    }

微信获取openID

private static function getOpenId($config){
        $url = 'https://api.weixin.qq.com/sns/jscode2session?appid='
            .$config['appid'] . '&secret=' . $config['appsecret'] . '&js_code='
            . $config['code'] . '&grant_type=authorization_code';
        $res = curl($url);
        $result = json_decode($res,true);
        return $result;
    }