/**
* 编辑
*
* @param hzOrderPage
* @return
*/
@AutoLog(value = "患者检验项目-编辑")
@ApiOperation(value="患者检验项目-编辑", notes="患者检验项目-编辑")
//@RequiresPermissions("org.jeecg.modules.demo:hz_order:edit")
@RequestMapping(value = "/edit", method = {RequestMethod.PUT,RequestMethod.POST})
public Result<String> edit(@RequestBody HzOrderPage hzOrderPage) {
HzOrder hzOrder = new HzOrder();
BeanUtils.copyProperties(hzOrderPage, hzOrder);
HzOrder hzOrderEntity = hzOrderService.getById(hzOrder.getId());
if(hzOrderEntity==null) {
return Result.error("未找到对应数据");
}
hzOrderService.updateMain(hzOrder, hzOrderPage.getOrderxmList());
return Result.OK("编辑成功!");
}
String a = "c";
switch (a) {
case "a":
System.out.println("匹配成功a");
//break;
case "b":
System.out.println("匹配成功b");
//break;
case "c":
System.out.println("匹配成功c");
break;
default:
System.out.println("hhhh");
break;
}
## 控制器获取json参数赋值给实体对象
```java
SysUser user = jsonObject.getObject("user",SysUser.class);
JwAjryxxList jwAjryxxList = jsonObject.getObject("ajryList",JwAjryxxList.class);
List<String> collect1 = jwAjryxxList.getAjryList().stream().map(JwAjryxx::getUserid).distinct().collect(Collectors.toList());
if(collect1.size()!=jwAjryxxList.getAjryList().size()){
return Result.error("有重复人员数据，请核对后再提交！");
}
@Test
public void t2() {
List<User> list = new ArrayList<>();
User user1 = new User("zhangsan", "beijing", 30);
User user2 = new User("zhangsan", "beijing", 40);
User user3 = new User("lisi", "shanghai", 35);
User user4 = new User("lisi", "shanghai", 28);
User user5 = new User("lisim", "shanghai", 32);
list.add(user1); list.add(user2);list.add(user3); list.add(user4);list.add(user5);
System.out.println("原始数据："+list);
//判断姓名是否有重复,练习使用java8的stream方法
//方法1. distinct, 直接比较大小，只知道是否有重复
List<String> collect1 = list.stream().map(User::getName).distinct().collect(Collectors.toList());
System.out.println(collect1.size()!=list.size()?"方法1-姓名有重复":"无重复");
//方法2.用户姓名计数
Map<Object, Long> collect2 = list.stream().collect(
Collectors.groupingBy( User::getName , Collectors.counting() ) );
System.out.println("姓名重复计数情况："+collect2);
//筛出有重复的姓名
List<Object> collect3 = collect2.keySet().stream().
filter(key -> collect2.get(key) > 1).collect(Collectors.toList());
//可以知道有哪些姓名有重复
System.out.println("方法2-重复的姓名 ： "+collect3);
//方法3，对重复的姓名保留计数
List<Map<String, Long>> collect4 = collect2.keySet().stream().
filter(key -> collect2.get(key) > 1).map(key -> {
Map<String, Long> map = new HashMap<>();
map.put((String) key, collect2.get(key));
return map;
}).collect(Collectors.toList());
System.out.println("方法3-重复的姓名及计数："+collect4);
}
原始数据：[User(name=zhangsan, address=beijing, age=30), User(name=zhangsan, address=beijing, age=40), User(name=lisi, address=shanghai, age=35), User(name=lisi, address=shanghai, age=28), User(name=lisim, address=shanghai, age=32)]
方法1-姓名有重复
姓名重复计数情况：{lisi=2, zhangsan=2, lisim=1}
方法2-重复的姓名 ： [lisi, zhangsan]
方法3-重复的姓名及计数：[{lisi=2}, {zhangsan=2}]
org.apache.commons.lang3.StringUtils
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
isBlank()在参数为null、""、" "时，值全部为true
JwZd deviceInfo = jwZdService.getOne(new LambdaQueryWrapper<JwZd>().eq(JwZd::getId,deviceList[i]));
QueryWrapper<KfdxExamination> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("pid",pid);
queryWrapper.ge("remainder",1);
List<KfdxExamination> roomlist = kfdxExaminationService.list(queryWrapper);
public Result<?> queryPageList(JwZzcwh jwZzcwh,
@RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
@RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
HttpServletRequest req) {
QueryWrapper<JwZzcwh> queryWrapper;
String[] zcfl = req.getParameterMap().get("zcfl");
if(zcfl!=null && zcfl.length>0){
Map<String,String[]> parmetermap = new HashMap(req.getParameterMap());
parmetermap.remove("zcfl");
jwZzcwh.setZcfl(null);
queryWrapper = QueryGenerator.initQueryWrapper(jwZzcwh, parmetermap);
List<JwZcfl> zcfllist = jwZcflService.list(new LambdaQueryWrapper<JwZcfl>().eq(JwZcfl::getId,zcfl[0]).or().eq(JwZcfl::getPid,zcfl[0]));
String[] insql = new String[zcfllist.size()];
for(int i=0;i<zcfllist.size();i++){
insql[i] = zcfllist.get(i).getId();
}
queryWrapper.in("zcfl",insql);
}else{
queryWrapper = QueryGenerator.initQueryWrapper(jwZzcwh, req.getParameterMap());
}
Page<JwZzcwh> page = new Page<JwZzcwh>(pageNo, pageSize);
IPage<JwZzcwh> pageList = jwZzcwhService.page(page, queryWrapper);
return Result.OK(pageList);
}
/**
* 获取有效考点列表
* @param
* @param
* @return
*/
@AutoLog(value = "考场信息表-获取有效考点列表")
@ApiOperation(value="考场信息表-获取有效考点列表", notes="考场信息表-获取有效考点列表")
@GetMapping(value = "/getKdList")
public Result<?> getKdList() {
List<KfdxExamination> kdlist = kfdxExaminationService.getKdList();
List<KfdxRecruitDate> datelist = kfdxExaminationService.getDateList();
Map<String,List<?>> result = new HashMap<>();
result.put("kdlist",kdlist);
result.put("datelist",datelist);
return Result.OK(result);
}
/**
* 消费明细导出
*
*
* @param
* @param
* @param
* @return
*/
@RequestMapping(value = "/getXfjlListExcel")
public ModelAndView getXfjlListExcel(@RequestParam(name="serialNo",required = false) String serialNo,
@RequestParam(name="name",required = false) String name,
@RequestParam(name="canting",required = false) String canting,
@RequestParam(name="tiemStart",required = false) String tiemStart,
@RequestParam(name="tiemEnd",required = false) String tiemEnd) {
List<JwXfjlPageListXls> infoList = jwXfjlService.getXfjlListExcel(serialNo,name,canting,tiemStart,tiemEnd);
//JwXfjlTotal total = jwXfjlService.getXfjlListTotal(serialNo,name,canting,tiemStart,tiemEnd);
ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
mv.addObject(NormalExcelConstants.FILE_NAME, "餐厅消费明细"); //此处设置的filename无效 ,前端会重更新设置一下
mv.addObject(NormalExcelConstants.CLASS, JwXfjlPageListXls.class);
//update-begin--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------
ExportParams exportParams=new ExportParams("餐厅消费明细" + "报表", "导出人:管理员" , "餐厅消费明细列表");
exportParams.setImageBasePath("D://");
//update-end--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------
mv.addObject(NormalExcelConstants.PARAMS,exportParams);
mv.addObject(NormalExcelConstants.DATA_LIST, infoList);
return mv;
}
java中如何将2021-04-05T13:08:22+08:00转成date呢
public static Date dealDateFormat(String oldDateStr) {
DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssXXX");
try {
Date date = df.parse(oldDateStr);
return date;
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
dealDateFormat("2021-04-05T13:08:22+08:00");
1）将当前时间转为和需要判断的时间区间相同格式的时间
例如判断当前时间是否在HH:mm的时间区间内（例时间区间0点到6点 [00:06]）
String strStartTime = “0:00”;
String strEndTime = “6:00”;
当前时间需转为HH:mm的时间格式：
SimpleDateFormat sdf = new SimpleDateFormat(“HH:mm”);
String now = sdf.format(new Date());
//HH:mm格式的当前时间
Date nowTime = sdf.parse(now);
//时间区间String转Date
Date startTime = sdf.parse(strStartTime);
Date endTime = sdf.parse(strEndTime);
2）调用isEffectiveDate(Date nowTime, Date startTime, Date endTime) 方法
在区间内返回true ，不在区间内返回false
/**
* 判断当前时间是否在[startTime, endTime]区间，注意三个参数的时间格式要一致
* @param nowTime
* @param startTime
* @param endTime
* @return 在时间段内返回true，不在返回false
*/
public static boolean isEffectiveDate(Date nowTime, Date startTime, Date endTime) {
if (nowTime.getTime() == startTime.getTime()
|| nowTime.getTime() == endTime.getTime()) {
return true;
}
Calendar date = Calendar.getInstance();
date.setTime(nowTime);
Calendar begin = Calendar.getInstance();
begin.setTime(startTime);
Calendar end = Calendar.getInstance();
end.setTime(endTime);
return date.after(begin) && date.before(end);
}
public static void main(String[] args) {
/**
* 判断当前时间是否在一个时间段内 HH:mm 格式
*/
String strStartTime1 = "00:00";
String strEndTime1 = "6:00";
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
String now = sdf.format(new Date());
Date nowTime;
try{
nowTime = sdf.parse(now);
Date startTime1 = sdf.parse(strStartTime1);
Date endTime1 = sdf.parse(strEndTime1);
// 注：也可以通过此方式判断当前时间是否具体时间段内 yyyy-MM-dd HH:mm:ss格式 [2022-03-09 12:00:00,2022-03-10 15:00:00]
// 当前时间和时间段的格式保持一致即可判断
if (isEffectiveDate(nowTime, startTime1, endTime1)) {
System.out.println("当前时间在时间段内["+strStartTime1+","+strEndTime1+"]");
} else {
System.out.println("当前时间不再时间段内["+strStartTime1+","+strEndTime1+"]");
}
}catch (Exception e){
e.printStackTrace();
}
}
// 昨天16:00
public static Date getStartTime() {
Calendar time = Calendar.getInstance();
time.add(Calendar.DATE, -1);
time.set(Calendar.HOUR_OF_DAY, 16);
time.set(Calendar.MINUTE, 0);
time.set(Calendar.SECOND, 0);
time.set(Calendar.MILLISECOND, 0);
return time.getTime();
}
// 当天16:00
public static Date getEndTime() {
Calendar time = Calendar.getInstance();
time.set(Calendar.HOUR_OF_DAY, 16);
time.set(Calendar.MINUTE, 00);
time.set(Calendar.SECOND, 00);
time.set(Calendar.MILLISECOND, 999);
return time.getTime();
}
public SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(timesBetween(dateFormat.format(ajxxkfxxList.get(j).getStartTime()),currentdate)<6){
log.info(kfxx.getFjh()+"客房起始日期："+dateFormat.format(ajxxkfxxList.get(j).getStartTime())
+"距离计费截止日期："+currentdate+"时差："
+timesBetween(dateFormat.format(ajxxkfxxList.get(j).getStartTime()),currentdate)
+"个小时，低于6小时，此房间不计费！"
);
continue;
}
/**
* @Description: TODO(计算两个日期【字符串类型】之间的时间距离)
* @param @param sdate
* @param @param bdate
* @param @return 设定文件
* @throws
*/
public static Long timesBetween(String sdate, String bdate) {
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
long day = 0;
long hour = 0;
long min = 0;
long sec = 0;
long diff = 0;
try {
Date startDate= df.parse(sdate);
Date bindDate= df.parse(bdate);
long stime = startDate.getTime();
long btime = bindDate.getTime();
if(stime>btime){
diff = stime - btime;
}else{
diff = btime - stime;
}
//day = diff/(24*60*60*1000);
hour = diff/(60*60*1000) - day*24;
//min = diff/(60*1000) - day*24*60 - hour*60;
//sec = diff/1000-day*24*60*60-hour*60*60-min*60;
} catch (ParseException e) {
e.printStackTrace();
}
return hour;
}
DecimalFormat gtmoneydf = new DecimalFormat("0.00");
double gtmoney = Double.valueOf(gtmoneydf.format((float)hjmoney / ajsl));
double money = jwAjxxCzjl.getCzje();
double score = new BigDecimal(money).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
//不足两位则补0
DecimalFormat decimalFormat = new DecimalFormat("0.00#");
String zbjebllwxs = decimalFormat.format(score);
//数字保留小数点2位补0转换为人民币大写
DoubleToString(new BigDecimal(money).setScale(2, RoundingMode.HALF_UP))
public static String DoubleToString(BigDecimal num) {
//将数字变成单纯的字符串
String string = String.valueOf(num).replace(".", "");
// 大写数字
String[] numbers = {"零","壹","贰","叁","肆","伍","陆","柒","捌","玖"};
//数位对应名称
String[] d = {"分","角","元","拾","佰","仟","万","拾","佰","仟","亿"};
StringBuffer sb = new StringBuffer();
char[] c = string.toCharArray();//后面的是分。所以倒叙遍历
int idx = 0;
for(int i =c.length-1;i>=0;i-- ) {
//我确定我传入的数字是两位小数的，从分开始。所以不做额外判断
//也因为不会超过亿，所以idx也不判断
sb.append(d[idx++]+(numbers[c[i]-'0']));
}
//之前是倒叙追加的。现在直接反转
return sb.reverse().toString();
}
我们可以直接使用sun提供的Double.doubleToLongBits（）方法来判断
第一种：BigDecimal(double val)
第二种：BigDecimal(String val) //通常采用这种方式
来看看BigDecimal的四则运算：
加法：add
减法：subtract
乘法：multiply
除法：divide
可参考下面代码：
BigDecimal bignum1 = new BigDecimal("10");
BigDecimal bignum2 = new BigDecimal("5");
BigDecimal bignum3 = null;
//加法
bignum3 = bignum1.add(bignum2);
System.out.println("求和：" + bignum3);
//减法
bignum3 = bignum1.subtract(bignum2);
System.out.println("求差：" + bignum3);
//乘法
bignum3 = bignum1.multiply(bignum2);
System.out.println("乘法积：" + bignum3);
//除法
bignum3 = bignum1.divide(bignum2);
System.out.println("除法结果：" + bignum3);
BigDecimal比较大小一般用的是BigDecimal的compareTo方法
int result = bigdemical1.compareTo(bigdemical2)
result = -1,表示bigdemical1小于bigdemical2；
result = 0,表示bigdemical1等于bigdemical2；
result = 1,表示bigdemical1大于bigdemical2；
001-010
String startNo = "001";
String endNo = "010";
System.err.println("开始编号Str:"+startNo);
System.err.println("结束编号Str:"+endNo);
Integer startInt = Integer.parseInt(startNo);
Integer endInt = Integer.parseInt(endNo);
System.err.println("开始编号转成Int:"+startInt);
System.err.println("结束编号转成Int:"+endInt);
for (int i = startInt; i <= endInt; i++){
// 重要
// 0 代表前面补0 字符串长度 代表0+i会补到这个长度 d 代表整数型
String no = String.format("%0"+startNo.length()+"d",i);
System.err.println("循环:"+no);
}
两个编号不同长度
String startNo = "998";
String endNo = "1005";
System.err.println("开始编号Str:"+startNo);
System.err.println("结束编号Str:"+endNo);
Integer startInt = Integer.parseInt(startNo);
Integer endInt = Integer.parseInt(endNo);
System.err.println("开始编号转成Int:"+startInt);
System.err.println("结束编号转成Int:"+endInt);
for (int i = startInt; i <= endInt; i++){
// 重要
// 0 代表前面补0 字符串长度 代表0+i会补到这个长度 d 代表整数型
String no = String.format("%0"+startNo.length()+"d",i);
System.err.println("循环:"+no);
}
SimpleDateFormat format = new SimpleDateFormat("yyMMdd");
Integer jrdaycount = hhxhisAccountInfoService.getJrDayZhrs();
int num = jrdaycount+1; // 需要补零的数字
int len = 6; // 补零之后数字的总长度
StringBuilder dtnumnber = new StringBuilder();
dtnumnber.append(num);
int temp = len - dtnumnber.length();
if (temp > 0)
while (dtnumnber.length() < len) //若长度不足进行补零
dtnumnber.insert(0, "0"); // 每次都在最前面补零
dah = format.format(new Date())+dtnumnber;
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
if(sysUser!=null){
jwDhrkd.setLrr(sysUser.getId());
jwDhrkd.setLrbm(sysUser.getDepartIds());
}
基本原理就是在把数据给前端的时候，把数据转换成string类型，这样就不会丢失小数点后面的（.00）数据。需要注意的是，前端接收到的是string类型数据，如果涉及到数据计算问题，需要前端进行数据转换！
@JsonFormat(shape = JsonFormat.Shape.STRING)
private BigDecimal amt;
Spring Boot默认配置文件为CLASSPATH下的application.properties
在application.properties中添加server.port=8011，可将tomcat默认端口号修改为8011
Spring Boot修改内置Tomcat端口号
@TableField(exist = false)：表示该属性不为数据库表字段，但又是必须使用的。
@TableField(exist = false)
private java.lang.String sexvalue;
<!-- 根据手机号码、卡号查询 -->
<select id="getUserInfoByCardPhone" resultType="org.jeecg.modules.system.entity.SysUser">
select * from sys_user where del_flag = 0
<if test="cardUrl != null and cardUrl != '' and cardUrl != 'null'">
and card_url = #{cardUrl}
</if>
<if test="phone != null and phone != ''">
and phone = #{phone}
</if>
<if test="tiemStart != null and tiemStart !=''">
and xfjl.create_time <![CDATA[ >= ]]> #{tiemStart}
</if>
<if test="tiemEnd != null and tiemEnd !=''">
and xfjl.create_time <![CDATA[ <= ]]> #{tiemEnd}
</if>
<if test="kssj != null and kssj != ''">
<![CDATA[ AND create_time >= #{kssj}]]>
</if>
<if test="jssj != null and jssj != ''">
<![CDATA[ AND create_time <= #{jssj}]]>
</if>
ORDER BY org_code ASC
limit 10
</select>
<update id="upUserStatus">
UPDATE kfdx_user SET status = #{status},remark = #{remark} where id = #{sysUserId}
</update>
<delete id="deleteLogicDeleted">
DELETE FROM sys_user WHERE del_flag = 1 AND id IN (${userIds})
</delete>
<if test="mobile != null and mobile !=''">
and us.mobile like concat(concat('%',#{mobile}),'%')
</if>
<select id="ryListByAjId" resultType="org.jeecg.modules.aj.ajryxx.entity.JwAjryxx">
select id,name
from jw_ajryxx
where
<choose>
<when test="ajId != null and ajId !=''">
ajid = #{ajId}
</when>
<otherwise>
ajid = '0'
</otherwise>
</choose>
</select>
<select id="queryByDepIds" resultType="org.jeecg.modules.system.entity.SysUser">
select * from sys_user where del_flag = 0
<if test="departIds!=null and departIds.size()>0">
and id in (select user_id from sys_user_depart where dep_id in
<foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
#{id}
</foreach>
)
</if>
<if test="username!=null and username!=''">
and username != #{username}
</if>
</select>
(CASE WHEN sex = '1' THEN '男' WHEN sex = '1' THEN '女' ELSE '未知' END) as sexvalue
/**
* 根据部门Ids查询
* @param list的部门id
* @return list的用户实体
*/
List<SysUser> queryByDepIds(List<String> departIds, String username);
<!-- 通过多个部门IDS，查询部门下的用户信息 -->
<select id="queryByDepIds" resultType="org.jeecg.modules.system.entity.SysUser">
select * from sys_user where del_flag = 0
<if test="departIds!=null and departIds.size()>0">
and id in (select user_id from sys_user_depart where dep_id in
<foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
#{id}
</foreach>
)
</if>
<if test="username!=null and username!=''">
and username != #{username}
</if>
</select>
<!-- SQL片段：getUserByOrgCode 的 FROM 和 WHERE 部分 -->
<sql id="getUserByOrgCodeFromSql">
FROM
sys_depart
INNER JOIN sys_user_depart ON sys_user_depart.dep_id = sys_depart.id
INNER JOIN sys_user ON sys_user.id = sys_user_depart.user_id
WHERE
sys_user.del_flag = 0 AND sys_depart.org_code LIKE '${orgCode}%'
<if test="userParams != null">
<if test="userParams.realname != null and userParams.realname != ''">
AND sys_user.realname LIKE concat(concat('%',#{userParams.realname}),'%')
</if>
<if test="userParams.workNo != null and userParams.workNo != ''">
AND sys_user.work_no LIKE concat(concat('%',#{userParams.workNo}),'%')
</if>
</if>
</sql>
<!-- 根据 orgCode 查询用户，包括子部门下的用户 -->
<select id="getUserByOrgCode" resultType="org.jeecg.modules.system.model.SysUserSysDepartModel">
SELECT
sys_user.id AS id,
sys_user.realname AS realname,
sys_user.avatar AS avatar,
sys_user.work_no AS workNo
<include refid="getUserByOrgCodeFromSql"/>
ORDER BY
sys_depart.org_code ASC
</select>
<!-- 查询 getUserByOrgCode 的总数-->
<select id="getUserByOrgCodeTotal" resultType="java.lang.Integer">
SELECT COUNT(1) <include refid="getUserByOrgCodeFromSql"/>
</select>
条件：tb1的字段key的值不在tbl2表中
SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.id WHERE tb2.id IS NULL;
例子1通过ajid查询所有不在tb2存在out_time和in_time的tb1记录
select
t1.*
from
tb1 as t1
LEFT JOIN tb2 as t2 ON t1.userid = t2.ryid
where t1.czsj is null and t1.ajid = '1573501179925041154' and t2.out_time is null and t2.in_time is null
<!--例子2查看所有分配谈话室的案件列表 -->
<select id="getNoTargetRoomAjxxListByAjztAjlx" resultType="org.jeecg.modules.aj.ajxx.entity.JwAjxx">
select ajxx.*
from jw_ajxx as ajxx
LEFT JOIN jw_ajxx_kfxx as ajkf ON ajxx.id = ajkf.ajid and ajkf.kfid in (select kf.id from jw_kfxx as kf where kf.fjlx = '1')
where ((ajkf.start_time is null and ajkf.end_time is null) or (ajkf.start_time is not null and ajkf.end_time is not null))
<if test="ajzt != null and ajzt != ''">
and ajxx.ajzt = #{ajzt}
</if>
<if test="ajlx != null and ajlx != ''">
and ajxx.ajlx = #{ajlx}
</if>
order by ajxx.create_time desc
</select>
select feeDate,SUM(czje) czje,SUM(tfje) tfje,SUM(xfje) xfje from(
select DATE_FORMAT(a.create_time,'%Y-%m-%d') as feeDate, if(czlx=1,SUM(a.czje),0) czje,if(czlx=2,SUM(a.czje),0) tfje,0 xfje from jw_czjl a GROUP BY feeDate,czlx
union
select DATE_FORMAT(x.create_time,'%Y-%m-%d') as feeDate,0 czje,0 tfje,SUM(x.xfje) xfje from jw_xfjl x
where x.jlrylx in ("1","2","3","4") and (x.xfstatus=9 or x.xfstatus=1)
GROUP BY feeDate
) temp
GROUP BY feeDate ORDER BY feeDate desc
select ANY_VALUE(DATE_FORMAT(a.create_time,'%Y-%m-%d')) as feeDate, ANY_VALUE(ct.ctmc) as ctmc,
count(case when sdmc='早餐' then a.id end) as zcnum,COALESCE(sum(case when sdmc='早餐' then a.btje end),0) zcbtje,
count(case when sdmc='午餐' then a.id end) as zwnum,COALESCE(sum(case when sdmc='午餐' then a.btje end) ,0) zwbtje,
count(case when sdmc='晚餐' then a.id end) as wcnum,COALESCE(sum(case when sdmc='晚餐' then a.btje end),0) wcbtje
from jw_xfjl a
left join jw_xfgz c on a.xfgzid=c.id
left join jw_xfbz f on c.xfbzid=f.id
left join sys_user b on b.id=a.userid
left join sys_user_depart d on b.id=d.user_id
left join sys_depart e on e.id=d.dep_id
left join jw_zd zd on a.zdid=zd.id
left join jw_canting ct on ct.id=zd.ctid
where a.jlrylx in ("1","2","3","4") and (a.xfstatus=9 or a.xfstatus=1) and a.create_time between '${startDate}' and '${endDate}'
and b.realname like '%${name}%' and e.id like '%${depId}%' and ct.id like '%${ctid}%'
GROUP BY feeDate,ct.id
order by feeDate desc
