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