### 更改會員表狀態
* 1 號機手的dimotsuc的復重統系卡來出詢查sql過通
```
select * from customs where linktel in (select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1);
```
*2 查詢條件 1至尊卡余額,2 通寶余額 3 是否有余額消費記錄4是否有通寶消費記錄(不包含2.0)5 是否有通寶消費記錄(包含2.0)篩選條件如果去除通寶2.0 如果沒有則沒有對應4
```
public function updateStatus()
{
$page = 1000;
$countSql = "(select * from customs where linktel in (select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1)) tp";
//1至尊卡余額,2 通寶余額 3 是否有余額消費記錄4是否有通寶消費記錄(不包含2.0)5 是否有通寶消費記錄(包含2.0)
$count = (new Model())->table($countSql)->count();
$sql = "(select customid,wm_concat(type||'|'||status) type from (select customid,res,type,
CASE res
WHEN 0 THEN 0
ELSE 1
END status
from (select c.customid,nvl(SUM(a.amount),0) res,1 type from customs c
left join customs_c cc on cc.customid=c.customid
left join account a on a.customid=cc.cid and a.type='00'
where c.linktel in (
select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1) GROUP BY c.customid
UNION all
select c.customid,nvl(SUM(a.REMINDAMOUNT),0)res,2 type from customs c
left join customs_c cc on cc.customid=c.customid
left join coin_account a on a.cardid=cc.cid
where c.linktel in (
select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1) GROUP BY c.customid
UNION all
select c.customid,nvl(count(a.TRADEAMOUNT),0)res,3 type from customs c
left join customs_c cc on cc.customid=c.customid
left join TRADE_WASTEBOOKS a on a.customid=cc.cid and a.TRADEAMOUNT>0
where c.linktel in (
select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1) GROUP BY c.customid
UNION all
select c.customid,nvl(count(a.TRADEPOINT),0)res,4 type from customs c
left join customs_c cc on cc.customid=c.customid
left join TRADE_WASTEBOOKS a on a.customid=cc.cid and a.TRADEPOINT>0
left join COIN_CONSUME cce on cce.tradeid=a.tradeid
left join coin_account ca on ca.coinid=cce.coinid
where ca.status='01' and c.linktel in (
select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1) GROUP BY c.customid
UNION all
select c.customid,nvl(count(a.TRADEPOINT),0) res,5 type from customs c
left join customs_c cc on cc.customid=c.customid
left join TRADE_WASTEBOOKS a on a.customid=cc.cid and a.TRADEPOINT>0
left join COIN_CONSUME cce on cce.tradeid=a.tradeid
left join coin_account ca on ca.coinid=cce.coinid
where c.linktel in (
select linktel from customs where customlevel='建業線上會員' group by linktel having count(*)>1) GROUP BY c.customid
) order by customid asc,type asc)
GROUP BY customid) ts";
$array_s = [];
$status_12 = [];
$status_13 = [];
for ($i = 1; $i <= ceil($count / $page); $i++) {
$data = (new Model())->table($sql)->page($i, $page)->select();
$update = 'UPDATE customs set status1 = CASE customid';
$array = [];
$array_12 = [];
$array_13 = [];
foreach ($data as $key => $item) {
//如果1,2,3,4,5 都為0
$status12 = '1|0,5|0,3|0,2|0';
//如果1,2,3,4 都為0
$status13 = '1|0,5|1,3|0,2|0';
$type = stream_get_contents($item['type']);
if ($type == $status12) {
$array[] = $item['customid'];
$array_s[] = $item['customid'];
$status_12[] = $item['customid'];
$array_12[] = $item['customid'];
// echo $item['customid'].' '.$type;
$update .= " WHEN '{$item['customid']}' THEN '12' ";
} elseif ($type == $status13) {
$update .= " WHEN '{$item['customid']}' THEN '13' ";
$array[] = $item['customid'];
$array_s[] = $item['customid'];
$status_13[] = $item['customid'];
$array_13[] = $item['customid'];
}
}
$result = 0;
if (!empty($array)) {
$where = sqlIn($array, 'customid in', 'or');
$update .= " END WHERE {$where}";
$result = (new Model())->execute($update);
// dump($result);
}
$log = "第{$i}頁,可執行數據" . count($array) . "條,數據類型12共:" . count($array_12) . ",數據類型13共:" . count($array_13) . ",執行結果:{$result}\r\n";
seaslog($log);
echo $log;
}
$log = "共" . ceil($count / $page) . "頁,可執行數據" . count($array_s) . "條,數據類型12共:" . count($status_13) . ",數據類型13共:" . count($status_13) . "\r\n";
seaslog($log);
echo $log;
}
```