<?php
namespace app\controller;
use think\facade\Db;
// 如果你的 isempty() 是全局函数，就直接用
// 如果是助手函数，也直接可用
// 这里加个保险：如果没加载，就定义一个兼容版
if (!function_exists('isempty')) {
function isempty($var)
{
return empty($var) || $var === '' || $var === null || $var === '0' || $var === 0;
}
}
class StockController
{
public function StockQuery(array $data)
{
$type = $data['type'] ?? '';
if (!in_array($type, ['A','B','C','D','E','F','G'])) {
return [];
}
// 分页
$page = max(1, (int)($data['page'] ?? 1));
$limit = max(0, (int)($data['limit'] ?? 100));
$offset = ($page - 1) * $limit;
// B 类型进货查询：不查库存表
$isPurchase = ($type === 'B' && isempty($data['store']) && (isempty($data['num']) || $data['num'] === 'All'));
if ($isPurchase) {
$query = Db::table('glg_base_goods')->alias('a')
->join('glg_goods_exp b', 'b.goodsid = a.goodsid')
->where('a.status', '0')
->where('b.status', '0')
->where('a.type', $type);
} else {
$query = Db::table('glg_stock')->alias('c')
->leftJoin('glg_info_base d', 'c.stockid = d.uid');
}
// 复杂的 A/C/D 双眼 OR 查询
if (!empty($data['s']) && in_array($type, ['A','C','D'])) {
$pattern = '/^[+-]?(0\.(00|25|50|75)|[1-9]\d*\.(00|25|50|75))$/';
$check = $type === 'A' ? ['sphr','cylr','sphl','cyll'] : ['sphr','sphl'];
foreach ($check as $f) {
if ($type === 'C' && str_contains($f, 'cyl')) continue;
if (!empty($data[$f]) && !preg_match($pattern, trim($data[$f]))) {
return '参数错误';
}
}
if (!$isPurchase) {
$query->leftJoin('glg_goods_exp b', 'b.goodsname = c.goodsid AND b.status = 0')
->leftJoin('glg_base_goods a', 'b.goodsid = a.goodsid');
}
$query->where(function ($q) use ($data, $type) {
$hasCondition = false;
if (!empty($data['sphr'])) {
$q->where(function ($qr) use ($data, $type) {
$qr->where('b.sph', $data['sphr']);
if ($type === 'A' && !empty($data['cylr'])) {
$qr->where('b.cyl', $data['cylr']);
}
});
$hasCondition = true;
}
if (!empty($data['sphl'])) {
$q->whereOr(function ($ql) use ($data, $type) {
$ql->where('b.sph', $data['sphl']);
if ($type === 'A' && !empty($data['cyll'])) {
$ql->where('b.cyl', $data['cyll']);
}
});
$hasCondition = true;
}
if (!$hasCondition) {
$q->whereRaw('1 = 0'); // 防止空 OR 导致全表扫描
}
});
if ($type === 'D' && !empty($data['sph'])) {
$query->where('b.sph', $data['sph']);
}
} else {
// 普通情况下的 JOIN
if (!$isPurchase) {
if (in_array($type, ['A','C','D','E'])) {
$query->leftJoin('glg_goods_exp b', 'b.goodsname = c.goodsid AND b.status = 0')
->leftJoin('glg_base_goods a', 'b.goodsid = a.goodsid');
} elseif (in_array($type, ['F','G'])) {
$query->leftJoin('glg_base_goods a', 'c.goodsid = a.goodsid');
}
}
}
// 通用条件
if (!$isPurchase) {
$query->where('a.status', '0');
}
$query->where('a.type', $type);
if (!isempty($data['brand'])) $query->where('a.brand', $data['brand']);
if (!isempty($data['py'])) $query->where('a.py', 'like', $data['py'] . '%');
if (!isempty($data['stuff'])) $query->where('a.stuff', $data['stuff']);
if (!isempty($data['refractive'])) $query->where('a.refractive', $data['refractive']);
if (!isempty($data['store']) && !$isPurchase) $query->where('c.stockid', $data['store']);
if (!isempty($data['addlight'])) $query->where('b.addlight', $data['addlight']);
if (!isempty($data['model'])) {
$field = in_array($type, ['B','E']) ? 'b.model' : 'a.model';
$query->where($field, $data['model']);
}
// 普通 sph/cyl
if (!in_array($type, ['A','C','D'])) {
if (!isempty($data['sph'])) {
$val = isempty($data['axis']) ? $data['sph'] : substr($data['axis'],0,1).$data['sph'];
$query->where('b.sph', isempty($data['axis']) ? 'like' : '=', isempty($data['axis']) ? "%{$val}" : $val);
}
if (!isempty($data['cyl'])) {
$val = isempty($data['axis']) ? $data['cyl'] : substr($data['axis'],1,1).$data['cyl'];
$query->where('b.cyl', isempty($data['axis']) ? 'like' : '=', isempty($data['axis']) ? "%{$val}" : $val);
}
}
// 价格范围
$saleField = in_array($type, ['F','G']) ? 'a.sale' : 'b.sale';
$buyField = in_array($type, ['F','G']) ? 'a.buy' : 'b.buy';
if (isset($data['sale_min']) || isset($data['sale_max'])) {
$min = $data['sale_min'] ?? 0;
$max = $data['sale_max'] ?? 10000;
$query->whereBetween($saleField, [$min, $max]);
}
if (isset($data['buy_min']) || isset($data['buy_max'])) {
$min = $data['buy_min'] ?? 0;
$max = $data['buy_max'] ?? 10000;
$query->whereBetween($buyField, [$min, $max]);
}
// 库存数量
if (!$isPurchase && !isempty($data['num']) && $data['num'] !== 'All') {
$map = [
'>=' => ['c.number', '>=', 0],
'<=' => ['c.number', '<=', 0],
'>' => ['c.number', '>', 0],
'<' => ['c.number', '<', 0],
'=' => ['c.number', '=', 0],
'1-10'=> ['c.number', 'between', [1,10]],
];
if (isset($map[$data['num']])) {
[$f,$op,$v] = $map[$data['num']];
$op === 'between' ? $query->whereBetween($f, $v) : $query->where($f, $op, $v);
}
}
// 字段 & 排序
if ($isPurchase) {
$query->field('b.goodsname,a.goodsid,b.buy,b.buyave,b.sale,b.num,b.model,
a.type,a.brand,a.py,a.unit')
->order('a.py,b.model');
} else {
switch ($type) {
case 'A':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
b.goodsname,b.goodsid,b.sph,b.cyl,b.addlight,b.buy,b.buyave,b.sale,
a.commission,b.num,a.type,a.brand,a.py,a.refractive,a.stuff,a.addraylogo,a.unit')
->order('c.stockid,a.py,a.refractive,a.stuff DESC,b.sph,b.cyl');
break;
case 'B':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
b.goodsname,a.goodsid,b.buy,b.buyave,b.sale,a.commission,b.num,b.model,
a.type,a.brand,a.py,a.unit')
->order('c.stockid,a.py,b.model');
break;
case 'C':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
b.goodsname,b.goodsid,b.sph,b.cyl,b.addlight,b.buy,b.buyave,b.sale,
a.commission,b.num,a.type,a.brand,a.py,a.unit')
->order('c.stockid,a.py,b.sph');
break;
case 'D':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
b.goodsname,a.goodsid,b.sph,b.buy,b.buyave,b.sale,a.commission,b.num,
a.model,a.type,a.brand,a.py,a.unit')
->order('c.stockid,a.py,a.model');
break;
case 'E':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
b.goodsname,a.goodsid,b.buy,b.buyave,b.sale,a.commission,b.num,b.model,
a.type,a.brand,a.py,a.unit')
->order('c.stockid,a.py,b.model');
break;
case 'F':
case 'G':
$query->field('c.stockid,d.name stockname,c.number,c.upper,c.lower,
c.goodsid as goodsname,a.goodsid,a.buy,a.buyave,a.sale,a.model,
a.type,a.brand,a.py,a.unit')
->order('c.stockid,a.py,a.model');
break;
}
}
// 执行
$total = $query->count();
$list = $limit > 0 ? $query->limit($offset, $limit)->select()->toArray()
: $query->select()->toArray();
if ($list) {
$list[0]['c'] = $total;
}
// 数据后处理（完全同原版）
foreach ($list as &$v) {
$v['number'] ??= 0;
$v['buyave'] ??= 0;
$v['sale'] ??= 0;
$v['amount'] = (int)$v['number'] * $v['buyave'];
$v['total'] = (int)$v['number'] * $v['sale'];
switch ($v['type'] ?? '') {
case 'A':
$v['types'] = '镜片';
$txt = $v['sph'] ?? '';
if (!in_array($v['cyl'] ?? '', ['-0.00','+0.00'])) $txt .= $v['cyl'];
if (!empty($v['addlight']) && trim($v['addlight']) !== '') {
$txt .= (str_contains($txt, $v['cyl'] ?? '') ? '下加-:' : '下加:') . $v['addlight'];
}
$v['brands'] = ($v['brand']??'') . ($v['refractive']??'') . ($v['stuff']??'') . $txt;
break;
case 'B': $v['types'] = '镜架'; $v['brands'] = ($v['brand']??'') . ($v['model']??''); break;
case 'C': $v['types'] = '隐形眼镜'; $v['brands'] = ($v['brand']??'') . ($v['sph']??'') . (!in_array($v['cyl']??'',['-0.00','+0.00']) ? ($v['cyl']??'') : ''); break;
case 'D': $v['types'] = '老花成镜'; $v['brands'] = ($v['brand']??'') . ($v['model']??'') . ($v['sph']??''); break;
case 'E': $v['types'] = '太阳镜'; $v['brands'] = ($v['brand']??'') . ($v['model']??''); break;
case 'F': $v['types'] = '护理产品'; $v['brands'] = ($v['brand']??'') . ($v['model']??''); break;
case 'G': $v['types'] = '其它产品'; $v['brands'] = ($v['brand']??'') . ($v['model']??''); break;
}
}
return $list;
}
}
版权归属：
Administrator
许可协议：
本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权
评论区