$param=Request::get();
$sParams=array_key_exists('searchParams',$param)?$param['searchParams']:"";
$sarra=['keys'=>'','starttime'=>'','endtime'=>'','sort'=>'','scondition'=>[]];
if(!empty($sParams)){
if(!is_array($sParams)){
$sarra=object_to_array(json_decode($sParams));
}else{
$sarra=$sParams;
}
}
$keys = $sarra['keys'];
$starttime = $sarra['starttime'];
$endtime = $sarra['endtime'];
$sort = $sarra['sort'];
$scondition = $sarra['scondition'];
$condition=[];
$whereOr=[];
if(!empty($keys)){
$condition[]=['deadname|idcard','like','%'.$keys.'%'];
}
if(!empty($starttime)&&!empty($endtime)){
$condition[]=['laydate','between',[$starttime,$endtime]];
}
if(!empty($scondition)){
$scarr=json_decode($scondition,true);
foreach ($scarr as $k=>$vo){
$fval=$vo['field_value'];
$where_str=$vo['where_str'];
if($where_str=="为空"){
$fval="is null";
$where_str="=";
}else if($where_str=="不为空"){
$fval="is not null";
$where_str="=";
}else if($where_str=="like"){
$fval="%$fval%";
}else if($where_str=="not like"){
$fval="%$fval%";
}
if(is_numeric($vo['field_value'])){
$fval=(int)$fval;
if($vo['joinstr']=="AND"){
$condition[]=[''.$vo['field_str'].'',''.$where_str.'',$fval];
}else if($vo['joinstr']=="OR"){
$whereOr[]=[''.$vo['field_str'].'',''.$where_str.'',$fval];
}
}else{
if($vo['joinstr']=="AND"){
$condition[]=[''.$vo['field_str'].'',''.$where_str.'',''.$fval.''];
}else if($vo['joinstr']=="OR"){
$whereOr[]=[''.$vo['field_str'].'',''.$where_str.'',''.$fval.''];
}
}
}
}
// dump($whereOr);
// die();
$order='laydate desc';
switch ($sort)
{
case "laydatedesc":
$order='laydate desc';
break;
case "laydateasc":
$order='laydate asc,deadid asc';
break;
default:
$order='deadid desc';
}
$lists=$this->BserviceService->getNoPageDataList($condition,$whereOr,$order);
$res['data']=$lists['data'];
$res['count']=$lists['count'];$fields="a.deadid,a.shiftno,a.laydate,a.deadname,a.age,a.birthday,a.deadcause,a.coldno,a.deadarea,a.deadrange,a.deadplace,a.arrivaltime,a.idcard,a.nativeaddr
,a.deadremark,a.inputdate,a.handler,a.handleridcard,a.handleraddr,a.handlerphone,a.cremationdate,b.serviceno,b.servicedate,b.servicetime,b.cashdate
,(select name FROM ".$this->PREFIX."dictionary where cid in(SELECT cid from zq_dictionary_cate where idcode='sex') and is_company=0 and value=a.sex limit 1) AS sex
,(select name FROM ".$this->PREFIX."dictionary where cid in(SELECT cid from zq_dictionary_cate where idcode='relation') and is_company=0 and value=a.deadrelation limit 1) AS deadrelation
,(select ifnull(sum(amount),0) as Expr1 FROM ".$this->PREFIX."by_charge as Charge01 where dead_id=a.deadid and chargetype1 ='费用1') AS chargebase
,(select ifnull(sum(amount),0) as Expr2 FROM ".$this->PREFIX."by_charge as Charge02 where dead_id=a.deadid and chargetype1 ='费用2') AS chargeused
,(select ifnull(sum(amount),0) as Expr3 FROM ".$this->PREFIX."by_charge as Charge03 where dead_id=a.deadid and chargetype1 ='费用3') AS chargespec
,(select ifnull(sum(amount),0) as Expr4 FROM ".$this->PREFIX."by_charge as Charge04 where dead_id=a.deadid and chargetype1 ='费用4') AS chargeother
,(select ifnull(sum(amount),0) as Expr5 FROM ".$this->PREFIX."by_charge as Charge05 where dead_id=a.deadid and chargetype1 ='费用5') AS chargeflower
,(select ifnull(sum(amount),0) as Expr6 FROM ".$this->PREFIX."by_charge as Charge06 where dead_id=a.deadid) AS amountsum
,(select ifnull(sum(reduce),0) as Expr7 FROM ".$this->PREFIX."by_charge as Charge07 where dead_id=a.deadid) AS reducesum
,(select ifnull(sum(jemoney),0) as Expr8 FROM ".$this->PREFIX."by_charge as Charge08 where dead_id=a.deadid) AS jemoneysum";
$subQuery = Db::name('by_dinfo')->alias('a')
->join(' by_service b','a.deadid = b.dead_id','left')
->field($fields)
->buildSql();
$fields2='deadid,shiftno,laydate,deadname,sex,age,birthday,deadcause,coldno,deadarea,deadrange,deadplace,arrivaltime,idcard,nativeaddr
,deadremark,deadrelation,inputdate,handler,handleridcard,handleraddr,handlerphone,cremationdate,serviceno,servicedate,servicetime,cashdate
,chargebase,chargeused,chargespec,chargeother,chargeflower,amountsum,reducesum,jemoneysum';
$lists=Db::table($subQuery . ' temserivce')
->field($fields2)
->where($condition)
->whereOr($whereOr)
->order($order)
->select()->toArray();3. mysql联合查询(UNION),其中by_deinfo和by_deother 有相同的字段
https://blog.csdn.net/czh500/article/details/85265518
SELECT `deadid`,`deadname` FROM `by_deinfo` UNION ( SELECT deadid,deadname FROM by_deother )
4.日期语句查询
SELECT dead_id,DATE_FORMAT(cashdate,'%Y-%m-%d') cashdate,`explain` as deadname,chargeid,chargetype1,chargetype2,chargeitem,unit,quantity,price,amount FROM `zq_by_charge` WHERE `chargetype2_id` = 28 AND `cashdate` >= '2022-01-01 00:00:00' AND `cashdate` <= '2022-01-08 23:59:59' AND `ynsingle` = 1
