常用库.md
压缩图片
/**
* 图片处理相关
* composer require intervention/image:2.7.2
*/
class ImageResize
{
protected $abpath = null; // 传入文件的绝对路径
protected $config = [];
/**
* @var \think\File
*/
protected $file = null; // 压缩之后的文件
protected $fileInfo = null;
public function __construct($publicbasepath)
{
$abpath = ROOT_PATH . $publicbasepath;
if (!file_exists($abpath)) {
throw new SystemException('文件不存在!');
}
$this->abpath = $abpath;
$this->config = Config::get('upload');
}
/**
* 压缩文件并返回文件信息
* @param string $publicbasepath public开头的位置
* @param integer $max_width
* @param integer $quality
* @return \think\File
* @copyright jcleng
* @author jcleng
*/
public function resizeimg($max_width = 750, $quality = 90)
{
$manager = new ImageManager(['driver' => 'gd']);
if (!file_exists($this->abpath)) {
return false;
}
$_file = new \think\File($this->abpath);
// 检查是不是图像
if (!$_file->checkImg()) {
return false;
}
$image = $manager->make($this->abpath);
// dump($image->width());
if ($image->width() <= 740) {
return false;
}
$image->widen($max_width)->encode('jpg', $quality);
// dump($image->dirname); // /srv/public/uploads/20240103
// dump($image->filename); // bf5de6af4968df778fca841b9516c710
// dump($image->extension); // jpg
// ! 先生成缓存,再覆盖
$_temp_name = $image->dirname . '/' . 'temp_' . $image->filename . '.' . $image->extension;
// dump($_temp_name);
$image->save($_temp_name);
if (!file_exists($_temp_name)) {
return false;
}
$image_temp = $manager->make($_temp_name);
if ($image_temp->width() > 0) {
try {
rename($_temp_name, $this->abpath);
} catch (\Throwable $th) {
throw new SystemException($th->getMessage());
}
// 文件信息
$this->file = new \think\File($this->abpath);
return true;
}
return false;
}
// 生成后的文件
public function file()
{
return $this->file;
}
// 获取生成文件的文件信息
public function fileInfo()
{
// 是空的
$fileInfo = $this->file->getInfo();
// trace('old fileInfo', 'error');
// trace($fileInfo, 'error');
$suffix = strtolower(pathinfo($fileInfo['name'], PATHINFO_EXTENSION));
$suffix = $suffix && preg_match("/^[a-zA-Z0-9]+$/", $suffix) ? $suffix : 'file';
$fileInfo['suffix'] = $suffix;
$fileInfo['imagewidth'] = 0;
$fileInfo['imageheight'] = 0;
$fileInfo['filesize'] = filesize($this->abpath);
$this->fileInfo = $fileInfo;
$this->checkImage();
return $this->fileInfo;
}
// 获取图片宽度和高度
protected function checkImage()
{
$imgInfo = getimagesize($this->abpath);
if (!$imgInfo || !isset($imgInfo[0]) || !isset($imgInfo[1])) {
throw new SystemException(__('不是有效的图片文件!'));
}
$this->fileInfo['imagewidth'] = isset($imgInfo[0]) ? $imgInfo[0] : 0;
$this->fileInfo['imageheight'] = isset($imgInfo[1]) ? $imgInfo[1] : 0;
return true;
}
}
zincsearch 日志
/**
* ZsLog的日志驱动,批量写入日志到es,在日志配置文件的type使用该类
* @link https://github.com/zincsearch/zincsearch
*/
class ZsLog
{
// zs地址
private static $URL;
// 密码: base64_encode('admin:123456')
private static $BASIC_PASSWORD;
/**
* 统一写入日志到es
* ! 这个位置只能放数据, 不允许获取上下文信息, 队列限制
* @param array $log 多个日志信息
* @param array $ext_data 附加数据: 用户信息,请求信息,等
* @return bool
*/
public function save(array $log, $ext_data)
{
$requestid = $ext_data['requestid'];
$starttime = $ext_data['starttime'];
$uid = $ext_data['uid'];
$mobile = $ext_data['mobile'];
$ip = $ext_data['ip'];
$endtime = $ext_data['endtime']; // 毫秒
$sqllog = $ext_data['sqllog'];
$formattedDateTime = $ext_data['formattedDateTime']; // 当前时间
$httpstatus = $ext_data['httpstatus'];
// * 配置
self::$URL = Env::get('zincsearch.url');
self::$BASIC_PASSWORD = Env::get('zincsearch.authorization');
// 请求并写入
$index_name = self::createIndex();
$rand_key = time() . '_' . rand(100,200);
foreach ($log as $value) {
self::createData([
'content' => [
// 这里使用一个键, 防止字段一致, 类型不一致导致zs报错
'info' => $value,
],
'publish_date' => $formattedDateTime,
// 统一增加 uid
'uid' => $uid,
'mobile' => $mobile,
'ip' => $ip,
// 初始到日志写入的时间
'space_time' => $endtime - $starttime,
'sqllog' => $sqllog,
'httpstatus' => $httpstatus
], $index_name);
}
return true;
}
/**
* 创建一个供日志保存的索引
*
* @param string $index_name 索引名称
* @param boolean $if_exist_cache 如果已经创建过了,进行缓存一下,下次不进行创建
* @return bool
*/
private static function createIndex($index_name = null, $if_exist_cache = true)
{
if (empty($index_name)) {
// 每个月一个索引
$index_name = 'logs_' . date('Ym');
}
if ($if_exist_cache) {
// 读取缓存, 如果存在, 不再创建
$cache_data = Cache::get('zslog_' . $index_name);
if (!empty($cache_data)) {
return $cache_data;
}
}
$params = [
"name" => $index_name,
"dynamic" => false, // 创建索引时禁用动态映射
"storage_type" => "disk",
"mappings" => [
"properties" => [
"content" => [
"type" => "text",
"index" => true,
"store" => true,
"highlightable" => true,
],
"publish_date" => [
"type" => "date",
"format" => "2006-01-02 15:04:05",
"index" => true,
"sortable" => true,
"aggregatable" => true,
],
],
],
];
// ! post请求
$client = new Client();
try {
$client->post(self::$URL . '/api/index', [
'headers' => [
'Content-Type' => 'application/json',
'Authorization' => 'Basic ' . self::$BASIC_PASSWORD,
],
'json' => $params,
'timeout' => QueueLog::STATUS ? 10 : 1,
]);
} catch (GuzzleException $th) {
if (\think\helper\Str::contains($th->getMessage(), "already exists")) {
if ($if_exist_cache) {
// 缓存
Cache::set('zsLog_' . $index_name, 1, 2 * 60);
}
}
}
return $index_name;
}
/**
* 提交单个日志
*
* @param array $data
* @param string $index_name
* @return bool
* @throws \App\Exception\JsonRpcException
*/
private static function createData($data, $index_name = 'tplog')
{
// ! post请求
$client = new Client();
try {
$client->post(self::$URL . '/api/' . $index_name . '/_doc', [
'headers' => [
'Content-Type' => 'application/json',
'Authorization' => 'Basic ' . self::$BASIC_PASSWORD,
],
'json' => $data,
'timeout' => QueueLog::STATUS ? 10 : 1,
]);
} catch (GuzzleException $th) {
// 写入异常提示
// echo $th->getMessage();
// throw $th;
}
return true;
}
}
上下文记录请求数据
/**
* 上下文, 只能在fpm里面使用;非fpm请增加init()方法每个请求清楚历史数据
*
* @copyright jcleng
* @author jcleng
*/
class Context
{
private static $data = [];
/**
* 获取数据
*
* @param string $key
* @param boolean $when_empty_exception 当数据为空时是否抛出异常
* @param string $exception_msg 异常提示文本
* @return mixed
* @copyright jcleng
* @author jcleng
*/
public static function get($key, $when_empty_exception = false, $exception_msg = '数据为空!')
{
$data = self::$data[$key] ?? null;
if (empty($data) && !empty($when_empty_exception)) {
throw new SystemException($exception_msg);
}
return $data;
}
public static function set($key, $value)
{
self::$data[$key] = $value;
}
public static function has($key)
{
return array_key_exists($key, self::$data);
}
public static function remove($key)
{
unset(self::$data[$key]);
}
public static function toArray()
{
return self::$data;
}
}
/**
* 日志上下文
* 需要在 app_end/app_exception 进行hook操作
* 通常是在: [应用行为(behavior事件)]请求开始/请求结束返回数据之前/请求当作写日志
// 请求结束
'response_end' => [
'app\\common\\behavior\\Log',
],
* @copyright jcleng
* @author jcleng
*/
class ContextLog
{
private static $logs = [];
private static $requestid = null;
private static $starttime = null; // 初始化时间, 毫秒
private static $lasthttpstatus = null; // 最后响应数据的http状态码
/**
* 每个请求都需要初始化一次, 通常是基类
*
* @return void
* @copyright jcleng
* @author jcleng
*/
public static function init()
{
self::$logs = [];
self::$requestid = date('YmdHis') . '_' . rand(10000, 90000);
self::$starttime = round(microtime(true) * 1000);
}
/**
* 设置一个日志到logs, 可以重复执行多次, 请求结束之后get()可以拿到所有的数据
*
* @param array $value
* @return void
* @copyright jcleng
* @author jcleng
*/
public static function set($value)
{
if (!empty($value['httpstatus'])) {
self::$lasthttpstatus = $value['httpstatus'];
}
self::$logs[] = array_merge([
'requestid' => self::$requestid
], $value);
}
/**
* 获取数据
*
* @param string $key
* @param boolean $when_empty_exception 当数据为空时是否抛出异常
* @param string $exception_msg 异常提示文本
* @return mixed
* @copyright jcleng
* @author jcleng
*/
public static function get()
{
return self::$logs;
}
/**
* 获取当前请求的requestid
*
* @return string
* @copyright jcleng
* @author jcleng
*/
public static function getRequestid() {
return self::$requestid;
}
/**
* 获取初始化时间,毫秒
*
* @return int 毫秒
* @copyright jcleng
* @author jcleng
*/
public static function getStarttime() {
return self::$starttime;
}
/**
* 获取最后的http状态码
*
* @return int http状态码
* @copyright jcleng
* @author jcleng
*/
public static function getLastHttpstatus() {
return self::$lasthttpstatus;
}
}
/**
* sql执行的所有语句放到这个位置
* ! database.debug 需要设置为开
* @copyright jcleng
* @author jcleng
*/
class ContextSql
{
private static $logs = [];
/**
* 每个请求都需要初始化一次, 通常是基类
* @param boolean $force_record_log 默认情况下这个位置注册listen()之后就[不再写到默认的文件日志]中, 这里打开, 写sql日志到2个位置
* @return void
* @copyright jcleng
* @author jcleng
*/
public static function init($force_record_log = true)
{
self::$logs = [];
Db::listen(function ($sql, $time, $explain, $master) use ($force_record_log) {
self::$logs[] = [
'sql' => $sql,
'time' => $time,
'explain' => $explain,
'master' => $master,
];
if ($force_record_log) {
// 同时写sql日志到本地文件中
Log::record('[ SQL ] ' . $sql . ' [ ' . $master . 'RunTime:' . $time . 's ]', 'sql');
if (!empty($explain)) {
Log::record('[ EXPLAIN : ' . var_export($explain, true) . ' ]', 'sql');
}
}
});
}
/**
* 获取sql数据日志列表
*
* @param string $key
* @param boolean $when_empty_exception 当数据为空时是否抛出异常
* @param string $exception_msg 异常提示文本
* @return mixed
* @copyright jcleng
* @author jcleng
*/
public static function get()
{
return self::$logs;
}
}
orm 更新 json 字段
/**
* 获取json_set的sql value的值, 最多支持二维数组; 注意json字段默认的值应该为{}否则不能更新
* ! 查询json字段为{}的值: JSON_UNQUOTE(JSON_EXTRACT(field, '$')) = '{}' 或者 like "%{}%"
* ! 在orm使用需要 Db::raw()
* @param string $field 需要更新json的表的字段
* @param array $jsondata 更新的json键值对
* @return string
* @throws \App\Exception\JsonRpcException
*/
function makeJsonSetSqlValue($field, $jsondata = [])
{
if (empty($jsondata)) {
return $field;
}
$sql = 'JSON_SET(' . $field . '';
foreach ($jsondata as $key => $value) {
if (is_array($value)) {
foreach ($value as $subKey => $subValue) {
$sql .= ', \'$.' . $key . '.' . $subKey . '\', \'' . $subValue . '\'';
}
} else {
$sql .= ', \'$.' . $key . '\', \'' . $value . '\'';
}
}
$sql .= ')';
return $sql;
}
swoole 导入大批量 csv 文件
/**
* 导入.csv到 鉴定号&流水号管理
* 主要使用多协程和yield
* @author lxx
*/
class ImportCsv
{
const MAX_BING = 20;
/**
* 读取每行的文件
*
* @param string $ab_path
* @return yield
* @author lxx
*/
public function readCvs($ab_path)
{
$handle = fopen($ab_path, 'r');
while (feof($handle) === false) {
yield fgetcsv($handle);
}
fclose($handle);
}
/**
* @param string $ab_path
* @param string $importId 批次id
*/
public function import($ab_path, $importId)
{
$result = $this->readCvs($ab_path);
$row_space = [];
$jump = 1000; // 单个数组的最大值
$i = 0;
$wg = new \Swoole\Coroutine\WaitGroup();
$err = 0;
$errmsg = [];
try {
foreach ($result as $key => $row) {
$i = $i + 1;
if ($i == 1) {
continue;
}
if (empty($row[0])) {
continue;
}
$serial_number = trim($row[0]);
$identify_code = trim($row[1]);
// ! 格式化数据
$sectionList = [];
if (strpos($identify_code, 'http') !== false) {
if (empty($serial_number) || empty($identify_code)) throw new MineException('内容不允许有空值，请仔细检查后重新操作');
$band_device_id = 0;
$band_device_store_id = 0;
if ($i == 1) {
$sectionList = container()->get(SupplierDeviceSectionMapper::class)->getModel()->where('status', '=', 1)->get(['id', 'device_id', 'device_store_id', 'section_sta', 'section_end'])->toArray();
if (empty($sectionList)) throw new MineException('请进行设置后重新操作');
}
// 检查数据是否有效
foreach ($sectionList as $value) {
if ($serial_number_val >= $value['section_sta'] && $serial_number_val <= $value['section_end']) {
$band_device_id = $value['device_id'];
$band_device_store_id = $value['device_store_id'];
break;
}
}
if (empty($band_device_id) || empty($band_device_store_id)) throw new MineException('流水号内容(' . $serial_number_val . ')不在流水号段内，请仔细检查后重新操作');
} else {
// FIXME: 设置数据
$band_device_id = 1;
$band_device_store_id = 2;
$serial_number_val = $serial_number;
$identify_code_val = $identify_code;
}
// ! 入库数据
$row_space[] = [
'batch_id' => $importId,
'serial_number' => $serial_number_val,
'report_number' => $identify_code_val,
'band_device_id' => $band_device_id,
'band_device_store_id' => $band_device_store_id
];
if (count($row_space) % $jump == 0) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$errmsg) {
$wg->add();
try {
TableData::insert($row_space);
} catch (\Throwable $th) {
$err = $err + 1;
$errmsg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
$row_space = [];
}
}
if (!empty($row_space)) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$errmsg) {
$wg->add();
try {
TableData::insert($row_space);
} catch (\Throwable $th) {
$err = $err + 1;
$errmsg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
}
$wg->wait();
} catch (\Throwable $e) {
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
TableData::where('batch_id', $importId)->delete();
throw new MineException('导入失败,' . $e->getMessage(), 500);
} finally {
// 尝试回滚
if (!empty($err)) {
// 删除已经入库的数据
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
TableData::where('batch_id', $importId)->delete();
throw new MineException('导入失败' . json_encode($errmsg), 500);
}
}
return true;
}
}
关于批量casewhen更新
<?php
// 通过不同的_order_third_no更新outbound_time
if (!empty($store_process_list_arr)) {
$sql = 'CASE `merchant_order_no` ';
foreach ($store_process_list_arr as $id => $item) {
if (empty($item['operate_time'])) {
continue;
}
$_order_third_no = $item['order_third_no'];
$_receiving_time = $item['operate_time'];
$sql .= 'WHEN "' . $_order_third_no . '" THEN ' . "'$_receiving_time' ";
}
$sql .= 'ELSE outbound_time END';
$res = MerchantIdentifyOrder::whereNull('outbound_time')
->whereIn('merchant_order_no', $merchant_order_no_s)
->update([
'outbound_time' => Db::raw($sql),
]);
}
当group之后需要分组之后最新的一条数据用
MAX取一条数据即可
<?php
$log_list = StoreProcessOperate::whereIn('type', ['RETURN_OUTSTORE', 'NORMAL_OUTSTORE'])
->whereIn('process_id', $process_id_s)
->groupBy('process_id')
->orderBy('id', 'desc')
->selectRaw("process_id, operate_time, MAX(id) AS max_id")
->get()->toArray();
把in条件作为行数据
$ids = ['1', '2', '99993000'];
$result = DB::table(DB::raw("(" . implode(' UNION ALL ', array_map(function ($id) {
return "SELECT '$id' AS in_id";
}, $ids)) . ") A"))
->leftJoin('admin_log B', 'A.in_id', '=', 'B.id')
->whereNull('B.id')
->selectRaw("A.*")
->selectRaw('B.id')
->get();
var_export($result);
// 实际sql
SELECT * FROM (
SELECT '1864186161043653' AS id
UNION ALL SELECT '1667466847244513'
UNION ALL SELECT '648250874697543'
UNION ALL SELECT '2759509227772092'
UNION ALL SELECT '1340692903883273'
) AS ids
leftJoin product_account on product_account.id = ids.id
// ! 临时表作为join主表进行查询
SELECT * FROM (
SELECT '1864186161043653' AS id
UNION ALL SELECT '1667466847244513'
UNION ALL SELECT '648250874697543'
UNION ALL SELECT '2759509227772092'
UNION ALL SELECT '1340692903883273'
) as temptable
left Join product_account on product_account.id = temptable.id
控制台打印日志
/**
* 控制台打印trace日志
*
* @param mixed $mixdata
* @return void
* @author jcleng
*/
function debuglog($mixdata = '')
{
if (php_sapi_name() === 'cli') {
fwrite(STDERR, "\n\n");
fwrite(STDERR, "\033[31m" . '===========DEBUGLOG===========' . "\033[0m\n");
fwrite(STDERR, "\033[31m" . '==========='.date('Y-m-d H:i:s') . "\033[0m\n");
fwrite(STDERR, "\033[31m" . json_encode(debug_backtrace(1, 6), 128 + 256) . "\033[0m\n");
fwrite(STDERR, "\n\n");
} else {
error_log("\n\n");
error_log("\033[31m" . '===========DEBUGLOG===========' . "\033[0m\n");
error_log("\033[31m" . '==========='.date('Y-m-d H:i:s') . "\033[0m\n");
error_log("\033[31m" . json_encode(debug_backtrace(1, 6), 128 + 256) . "\033[0m\n");
error_log("\n\n");
}
}
使用
Vtiful\Excel转换csv为xlsx
use Vtiful\Kernel\Excel;
public function csv2Xlsx($csvPath = '')
{
// $csvPath = BASE_PATH . "/.vscode/20250806_213328_明细.csv";
$re_path = BASE_PATH . '/.vscode/';
$config = ['path' => $re_path]; // 输出目录
$excel = new Excel($config);
// 创建 Excel 文件对象
$filename = pathinfo($csvPath)['filename'] ?? time();
$file_name = "{$filename}.xlsx";
$fileObject = $excel->fileName($file_name);
// 打开 CSV 文件
$csvFile = fopen($csvPath, "r");
if ($csvFile === false) {
die("无法打开 CSV 文件");
}
// 读取第一行作为表头
$headers = fgetcsv($csvFile);
$fileObject->header($headers); // 写入表头
// 逐行读取并写入 Excel
$row_all = [];
while (($row = fgetcsv($csvFile)) !== false) {
$row_all[] = $row;
}
$fileObject->data($row_all);
// 关闭 CSV 文件
fclose($csvFile);
// 保存 Excel 文件
$fileObject->output();
return $re_path . $file_name;
}