package com.yihu.jw.service;/** * Created by nature of king on 2018/4/27. */ import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.fasterxml.jackson.databind.ObjectMapper; import com.google.common.base.Joiner; import com.netflix.discovery.util.StringUtil; import com.yihu.jw.dao.*; import com.yihu.jw.entity.health.bank.*; import com.yihu.jw.restmodel.web.MixEnvelop; import com.yihu.jw.rm.health.bank.HealthBankMapping; import com.yihu.jw.util.DateUtils; import com.yihu.jw.util.ISqlUtils; import com.yihu.jw.util.date.DateUtil; import com.yihu.mysql.query.BaseJpaService; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import javax.transaction.RollbackException; import javax.transaction.Transactional; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; /** * @author wangzhinan * @create 2018-04-27 14:38 * @desc health activity Service **/ @Service @Transactional public class ActivityService extends BaseJpaService { @Autowired private ActivityDao activityDao; @Autowired private TaskPatientDetailDao taskPatientDetailDao; @Autowired private TaskDao taskDao; @Autowired private JdbcTemplate jdbcTemplate; @Autowired private ActivityRuleDao activityRuleDao; @Autowired private TaskGoodsDao taskGoodsDao; @Autowired private AccountDao accountDao; @Autowired private ExchangeGoodsDao exchangeGoodsDao; /** * insert activityDO * * @param activityDO 活动参数对象 * @return */ @org.springframework.transaction.annotation.Transactional(rollbackFor = Exception.class) public MixEnvelop insert(ActivityDO activityDO,String value1,String value2,String value3,String registrationNumber){ if (value2 != null && value2 !=""){ JSONObject object = JSONObject.parseObject(value2); String activityOfflineTime = object.getString("activityOfflineTime"); String[] str = activityOfflineTime.split(","); if (str.length == 2){ activityDO.setActivityOfflineTime(DateUtil.strToDate(str[1])); }else { activityDO.setActivityOfflineTime(DateUtil.strToDate(str[0])); } String registrationTime = object.getString("registrationTime"); String[] str1 = registrationTime.split(","); if(str1.length==2){ activityDO.setRegistrationTime(DateUtil.strToDate(str1[1])); }else { activityDO.setRegistrationTime(DateUtil.strToDate(str1[0])); } String deadLine = object.getString("deadLine"); String[] str2 = deadLine.split(","); if (str2.length==2){ activityDO.setDeadLine(DateUtil.strToDate(str2[1])); }else { activityDO.setDeadLine(DateUtil.strToDate(str2[0])); } } activityDO.setCreateTime(new Date()); activityDO.setUpdateTime(new Date()); activityDO.setDelFlag(1); activityDO.setRegistrationNumber(registrationNumber); activityDO= activityDao.save(activityDO); if (value3 !=null && value3 !=""){ JSONObject object = JSONObject.parseObject(value3); String exchangeType = object.getString("exchangeType"); if (exchangeType.equals("1")){ String array = (null!=object.get("goodDetail")?String.valueOf(object.get("goodDetail")):""); if (StringUtils.isNotBlank(array)){ JSONArray array1= JSON.parseArray(array); for (int i =0;i envelop = new MixEnvelop<>(); envelop.setObj(true); return envelop; } public MixEnvelop createActivity(String planCode,String patient,String name,String idcard,String hospital,String hospitalName,String templateName){ MixEnvelop envelop = new MixEnvelop<>(); AccountDO accountDO = accountDao.selectByPatientId(patient); if (accountDO==null){ accountDO = new AccountDO(); accountDO.setIdCard(idcard); accountDO.setStatus(1); accountDO.setTotal(0); accountDO.setPatientId(patient); accountDO.setCouponTotal(0L); accountDO.setPassword("123456"); if(idcard.length()>=4){// 判断是否长度大于等于4 String cardNumber=idcard.substring(idcard.length()- 4,idcard.length());//截取两个数字之间的部分 int random = (int)((Math.random()*9+1)*100000); accountDO.setCardNumber(cardNumber+Integer.toString(random)); } accountDO.setAccountName(name); accountDO.setHospitalName(hospitalName); accountDO.setHospital(hospital); accountDO.setSaasId("dev"); accountDO.setCreateTime(new Date()); accountDO.setUpdateTime(new Date()); accountDO = accountDao.save(accountDO); } ActivityDO activityDO = activityDao.selectByTemplateIdAndType(planCode,"健康计划"); if (activityDO==null){ activityDO = new ActivityDO(); activityDO.setStatus(1); activityDO.setSaasId("dev"); activityDO.setCreateTime(new Date()); activityDO.setUpdateTime(new Date()); activityDO.setOrganizer("SYSTEM"); activityDO.setTitle(templateName); activityDO.setType("健康计划"); activityDO.setDescription(templateName); activityDO.setGlobelTemplate(planCode); activityDO.setIntroduction(templateName); activityDO.setIsFlag(-1); activityDO.setDelFlag(1); activityDO = activityDao.save(activityDO); TaskDO taskDO = new TaskDO(); taskDO.setSaasId("dev"); taskDO.setCreateTime(new Date()); taskDO.setUpdateTime(new Date()); taskDO.setTitle(templateName); taskDO.setType("ACTIVITY_TASK"); taskDO.setTaskCode("ACTIVITY_PLAN"); taskDO.setTransactionId(activityDO.getId()); taskDO.setContent(templateName); taskDO.setStatus(1); taskDO = taskDao.save(taskDO); TaskPatientDetailDO taskPatientDetailDO = taskPatientDetailDao.selectByTaskIdAndPatientId(taskDO.getId(),patient); if (taskPatientDetailDO==null){ taskPatientDetailDO = new TaskPatientDetailDO(); taskPatientDetailDO.setSaasId("dev"); taskPatientDetailDO.setTaskId(taskDO.getId()); taskPatientDetailDO.setActivityId(activityDO.getId()); taskPatientDetailDO.setPatientIdcard(idcard); taskPatientDetailDO.setPatientId(patient); taskPatientDetailDO.setHospital(hospital); taskPatientDetailDO.setHospitalName(hospitalName); taskPatientDetailDO.setCreateTime(new Date()); taskPatientDetailDO.setUpdateTime(new Date()); taskPatientDetailDO.setTotal(0L); taskPatientDetailDO.setCouponTotal(0L); taskPatientDetailDO.setStatus(1); taskPatientDetailDao.save(taskPatientDetailDO); } }else { List taskDOS = taskDao.selectByActivityId(activityDO.getId()); if (taskDOS!=null&&taskDOS.size()!=0){ TaskDO taskDO = taskDOS.get(0); TaskPatientDetailDO taskPatientDetailDO = taskPatientDetailDao.selectByTaskIdAndPatientId(taskDO.getId(),patient); if (taskPatientDetailDO==null){ taskPatientDetailDO = new TaskPatientDetailDO(); taskPatientDetailDO.setSaasId("dev"); taskPatientDetailDO.setTaskId(taskDO.getId()); taskPatientDetailDO.setActivityId(activityDO.getId()); taskPatientDetailDO.setPatientIdcard(idcard); taskPatientDetailDO.setPatientId(patient); taskPatientDetailDO.setHospital(hospital); taskPatientDetailDO.setHospitalName(hospitalName); taskPatientDetailDO.setCreateTime(new Date()); taskPatientDetailDO.setUpdateTime(new Date()); taskPatientDetailDO.setTotal(0L); taskPatientDetailDO.setCouponTotal(0L); taskPatientDetailDO.setStatus(1); taskPatientDetailDao.save(taskPatientDetailDO); } } } envelop.setObj(activityDO); return envelop; } /** * find by condition * * @param activityDO 活动参数对象 * @param page 页码 * @param size 每页大小 * @return * @throws ParseException */ public MixEnvelop findByCondition(ActivityDO activityDO, Integer page, Integer size) throws ParseException { String sql = new ISqlUtils().getSql(activityDO,page,size,"sort"); List activityDOS = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class)); for (ActivityDO activityDO1:activityDOS){ String taskSql = "SELECT" + " COUNT(1) AS total1 " + "FROM " + " ( " + " SELECT DISTINCT " + " (btpd.patient_id) " + " FROM " + " wlyy_health_bank_task_patient_detail btpd " + " WHERE " + " activity_id = '" +activityDO1.getId()+ "' ) btpd1"; List> rstotal = jdbcTemplate.queryForList(taskSql); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ count = (Long) rstotal.get(0).get("total1"); } activityDO1.setTotal(count); String taskSql1 = "select * from wlyy_health_bank_task_patient_detail btpd where activity_id = '"+activityDO1.getId() +"' and (patient_idcard = '"+activityDO.getPatientIdcard()+"' OR union_id = '"+activityDO.getUnionId()+"')"; List taskPatientDetailDOS = jdbcTemplate.query(taskSql1,new BeanPropertyRowMapper(TaskPatientDetailDO.class)); activityDO1.setTaskPatientDetailDOS(taskPatientDetailDOS); String tasksql = "select * from wlyy_health_bank_task where transaction_id = '"+activityDO1.getId()+"'"; List taskDOList = jdbcTemplate.query(tasksql,new BeanPropertyRowMapper(TaskDO.class)); List taskGoodsDOS = taskGoodsDao.selectByActivityId(activityDO1.getId()); for (TaskGoodsDO taskGoodsDO:taskGoodsDOS){ ExchangeGoodsDO exchangeGoodsDO = exchangeGoodsDao.selectByActivityGoodsIdAndPatientId(taskGoodsDO.getId(),activityDO.getPatientId()); taskGoodsDO.setExchangeGoodsDO(exchangeGoodsDO); } activityDO1.setTaskGoodsDOS(taskGoodsDOS); ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO1.getId()); activityDO1.setActivityRuleDO(activityRuleDO); activityDO1.setTaskDOS(taskDOList); } String sqlcount = new ISqlUtils().getSql(activityDO,0,0,"count"); List> rstotal = jdbcTemplate.queryForList(sqlcount); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ count = (Long) rstotal.get(0).get("total"); } return MixEnvelop.getSuccessListWithPage(HealthBankMapping.api_success,activityDOS,page,size,count); } /** * update activityDO * * @param activityDO 活动参数对象 * @return */ @org.springframework.transaction.annotation.Transactional(rollbackFor = Exception.class) public MixEnvelop update(ActivityDO activityDO, String value1, String value2, String value3,String registrationNumber) throws Exception { if (StringUtils.isBlank(activityDO.getId())) { throw new Exception("活动id不能为空！"); } if (StringUtils.isNotBlank(value2)){ JSONObject object = JSONObject.parseObject(value2); String activityOfflineTime = object.getString("activityOfflineTime"); String[] str = activityOfflineTime.split(","); if (str.length == 2){ activityDO.setActivityOfflineTime(DateUtil.strToDate(str[1])); }else { activityDO.setActivityOfflineTime(DateUtil.strToDate(str[0])); } String registrationTime = object.getString("registrationTime"); String[] str1 = registrationTime.split(","); if(str1.length==2){ activityDO.setRegistrationTime(DateUtil.strToDate(str1[1])); }else { activityDO.setRegistrationTime(DateUtil.strToDate(str1[0])); } String deadLine = object.getString("deadLine"); String[] str2 = deadLine.split(","); if (str2.length==2){ activityDO.setDeadLine(DateUtil.strToDate(str2[1])); }else { activityDO.setDeadLine(DateUtil.strToDate(str2[0])); } } activityDO.setRegistrationNumber(registrationNumber); activityDO.setUpdateTime(new Date()); activityDO.setDelFlag(1); //删除活动任务与奖品 taskGoodsDao.deleteByActivityId(activityDO.getId()); if (value3 !=null && value3 !=""){ JSONObject object = JSONObject.parseObject(value3); String exchangeType = object.getString("exchangeType"); if (exchangeType.equals("1")){ String array = (null!=object.get("goodDetail")?String.valueOf(object.get("goodDetail")):""); if (StringUtils.isNotBlank(array)){ JSONArray array1= JSON.parseArray(array); for (int i =0;i envelop = new MixEnvelop<>(); envelop.setObj(true); return envelop; } /** * 获取参与的活动 * * @param activityDO 活动对象 * @param page 页码 * @param size 分页大小 * @return */ public MixEnvelop selectByPatient(ActivityDO activityDO,String flag, Integer page, Integer size){ String condition = ""; if (activityDO.getUnionId()!=null&&activityDO.getUnionId() !=""){ condition="AND union_id = '"+ activityDO.getUnionId()+"'"; } String condition1=""; if (!StringUtils.isNoneBlank(flag)){ condition1 =" and type <> '健康计划' "; } String sql ="SELECT * " + " FROM wlyy_health_bank_activity " + "WHERE " + "id IN ( " + "SELECT bt.transaction_id " + "FROM wlyy_health_bank_task bt " + "WHERE id IN (" + " SELECT task_id FROM " + "wlyy_health_bank_task_patient_detail" + " WHERE " + " patient_idcard = '"+activityDO.getPatientIdcard()+"' "+ condition+")" + " ) AND del_flag =1 " +condition1+ " LIMIT "+(page-1)*size +","+size; List activityDOS = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class)); for (ActivityDO activityDO1:activityDOS){ String activitySql ="SELECT btpd1.sum AS total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " patient_openid, " + " patient_idcard, " + " activity_id," + " union_id " + " FROM " + " wlyy_health_bank_task_patient_detail " + "GROUP BY patient_openid,patient_idcard,union_id)btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO1.getId() +"' AND patient_openid = '"+activityDO.getOpenId()+ "' AND patient_idcard = '"+activityDO.getPatientIdcard()+"' "+condition; List> rstotal = jdbcTemplate.queryForList(activitySql); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ Object object = rstotal.get(0).get("total"); count = Long.parseLong(object.toString()); } activityDO1.setSum(count); String rankingSql = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " patient_openid, " + " patient_idcard, " + " activity_id " + "FROM " + " wlyy_health_bank_task_patient_detail " + "GROUP BY patient_openid,patient_idcard,union_id)btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO1.getId()+"' AND btpd1.sum >" +activityDO1.getSum() ; List> rstotal1 = jdbcTemplate.queryForList(rankingSql); Integer count1 = 0; if(rstotal1!=null&&rstotal1.size()>0){ Object object = rstotal1.get(0).get("total"); count1 = Integer.parseInt(object.toString()); } activityDO1.setActivityRanking(count1); String taskSql = "SELECT" + " COUNT(1) AS total1 " + "FROM " + " ( " + " SELECT * " + " FROM " + " wlyy_health_bank_task_patient_detail btpd " + " WHERE " + " activity_id = '" +activityDO1.getId()+ "' GROUP BY patient_openid,patient_idcard,union_id) btpd1"; List> rstotal2 = jdbcTemplate.queryForList(taskSql); Long count2 = 0L; if(rstotal2!=null&&rstotal2.size()>0){ count2 = (Long) rstotal2.get(0).get("total1"); } activityDO1.setTotal(count2); ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO1.getId()); activityDO1.setActivityRuleDO(activityRuleDO); } String sqlcount = "SELECT count(1) AS total" + " FROM wlyy_health_bank_activity " + "WHERE " + "id IN ( " + "SELECT bt.transaction_id " + "FROM wlyy_health_bank_task bt " + "WHERE id IN (" + " SELECT task_id FROM " + "wlyy_health_bank_task_patient_detail" + " WHERE " + " patient_openid = '"+activityDO.getOpenId()+ "' AND patient_idcard = '"+activityDO.getPatientIdcard()+"'"+condition+ ")" + " ) "+condition1; List> rstotal = jdbcTemplate.queryForList(sqlcount); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ count = (Long) rstotal.get(0).get("total"); } return MixEnvelop.getSuccessListWithPage(HealthBankMapping.api_success,activityDOS,page,size,count); } /** * 批量删除活动 * * @param ids 活动id集合 * @return */ public MixEnvelop batchDelete(List ids){ MixEnvelop envelop = new MixEnvelop<>(); for (int i =0;i taskDOList = taskDao.selectByActivityId(ids.get(i)); for (TaskDO taskDO:taskDOList){ taskDO.setStatus(0); taskDO.setCreateTime(new Date()); taskDO.setUpdateTime(new Date()); taskDao.save(taskDO); } List taskPatientDetailDOS = taskPatientDetailDao.selectByActivityId(ids.get(i)); for(TaskPatientDetailDO taskPatientDetailDO:taskPatientDetailDOS){ taskPatientDetailDO.setStatus(-1); taskPatientDetailDO.setCreateTime(new Date()); taskPatientDetailDO.setUpdateTime(new Date()); taskPatientDetailDao.save(taskPatientDetailDO); } ActivityRuleDO ruleDO = activityRuleDao.selectByActivityId(ids.get(i)); if(null!=ruleDO){ ruleDO.setDelFlag(0); activityRuleDao.save(ruleDO); } ActivityDO activityDO = activityDao.findOne(ids.get(i)); activityDO.setStatus(-1); activityDO.setCreateTime(new Date()); activityDO.setUpdateTime(new Date()); activityDO.setDelFlag(0); activityDao.save(activityDO); } return envelop; } /** * 删除活动 * * @param id 活动id * @return */ public MixEnvelop daleteActivity(String id){ MixEnvelop envelop = new MixEnvelop<>(); List taskDOList = taskDao.selectByActivityId(id); for (TaskDO taskDO:taskDOList){ taskDO.setStatus(0); taskDO.setCreateTime(new Date()); taskDO.setUpdateTime(new Date()); taskDao.save(taskDO); } List taskPatientDetailDOS = taskPatientDetailDao.selectByActivityId(id); for(TaskPatientDetailDO taskPatientDetailDO:taskPatientDetailDOS){ taskPatientDetailDO.setStatus(-1); taskPatientDetailDO.setCreateTime(new Date()); taskPatientDetailDO.setUpdateTime(new Date()); taskPatientDetailDao.save(taskPatientDetailDO); } ActivityRuleDO ruleDO = activityRuleDao.selectByActivityId(id); if(null!=ruleDO){ ruleDO.setDelFlag(0); activityRuleDao.save(ruleDO); } ActivityDO activityDO = activityDao.findOne(id); activityDO.setStatus(-1); activityDO.setCreateTime(new Date()); activityDO.setUpdateTime(new Date()); activityDO.setDelFlag(0); activityDao.save(activityDO); return envelop; } /** * 编辑活动：上、下线，推荐 * @param id * @param type 操作类型：上线：0，下线：2，推荐：3，取消推荐：4 * @return * @throws Exception */ public ActivityDO editActivity(String id, String type) throws Exception { ActivityDO activityDO = activityDao.findOne(id); ActivityRuleDO activityRuleDO= activityRuleDao.selectByActivityId(id); if("3".equals(type)){ //推荐 List activityDOS = activityDao.findByIdAndRecommended(1); if (activityDOS!=null&& activityDOS.size()>=3){ throw new Exception("只允许推荐三个！"); }else { activityDO.setRecommended(1); } }else if("4".equals(type)){ //取消推荐 activityDO.setRecommended(0); } else if("2".equals(type)){ //下线 activityDO.setStatus(2); }else if("0".equals(type)){ //判断该活动是不是竞走，只能存在一条有效竞走 /*if("竞走".equals(activityDO.getType())){ List lst= activityDao.findByIdAndType(activityDO.getType()); if(null!=lst&&lst.size()>0){ throw new Exception("已存在一条上线的竞走活动！"); } }*/ //上线 if(null!=activityRuleDO){ String ruleJson=activityRuleDO.getValue2(); JSONObject obj=JSONObject.parseObject(ruleJson); // SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"); SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm"); //报名截止时间 String registrationTime1 = obj.getString("registrationTime"); String[] str1 = registrationTime1.split(","); String s = null; if (str1.length==2) { s=str1[1]; }else { s=str1[0]; } Date registrationTime=(""!=s&&null!=s? sdf2.parse(s):null); //参与截止时间 String deadLine2 = obj.getString("deadLine"); String[] str2 = deadLine2.split(","); String s1= null; if (str2.length==2){ s1=str2[1]; }else { s1=str2[0]; } Date deadLine=(null!=s1&&""!=s1? sdf2.parse(s1):null); //活动下线时间 String activityOfflineTime1 = obj.getString("activityOfflineTime"); String[] str = activityOfflineTime1.split(","); String s2= null; if (str.length==2){ s2=str[1]; }else { s2=str[0]; } Date activityOfflineTime=(""!=s2&&null!=s2 ? sdf2.parse(s2):null); //获取当前时间 Date now = DateUtil.strToDate(sdf2.format(new Date()),"yyyy-MM-dd HH:mm"); if(null!=activityOfflineTime&&activityOfflineTime.compareTo(now)<=0){ //活动下线 activityDO.setStatus(2); }else if((null!=activityOfflineTime&&activityOfflineTime.compareTo(now)>0) && (null!=deadLine&&deadLine.compareTo(now)<=0)){ //活动结束 activityDO.setStatus(-1); }else if((null!=deadLine&&deadLine.compareTo(now)>0)&&(null!=registrationTime&®istrationTime.compareTo(now)<=0)){ //活动未结束 activityDO.setStatus(0); }else if (null!=registrationTime&®istrationTime.compareTo(now)>1){ //报名未截止 activityDO.setStatus(1); }else{ //没有时间限制，上线 activityDO.setStatus(0); } }else { throw new Exception("活动规则缺失！"); } } // activityDO.setReleaseTime(new Date()); activityDO= activityDao.save(activityDO); return activityDO; } /** * 根据活动id获取活动详情+规则 * @param id * @return * @throws Exception */ public JSONObject findActivityById(String id)throws Exception{ JSONObject obj=new JSONObject(); ActivityDO activityDO = activityDao.findOne(id); obj.put("jsonData",activityDO); ActivityRuleDO activityRuleDO=new ActivityRuleDO(); if(null!=activityDO){ activityRuleDO= activityRuleDao.selectByActivityId(id); activityDO.setActivityRuleDO(activityRuleDO); } obj.put("value1", null == activityRuleDO ? null : activityRuleDO.getValue1()); obj.put("value2", null == activityRuleDO ? null : activityRuleDO.getValue2()); obj.put("value3", null == activityRuleDO ? null : activityRuleDO.getValue3()); return obj; } /** * 活动排名 * * @param activityId * @param patient * @param page * @param size * @return */ public MixEnvelop selectActivityRanking(String activityId,String patient,Integer page,Integer size,String street,String town,String hospital){ MixEnvelop envelop = new MixEnvelop<>(); ActivityDO activityDO = activityDao.selectById(activityId); JSONObject jsonObject = new JSONObject(); TaskPatientDetailDO taskPatientDetailDO1 = taskPatientDetailDao.selectByActivityIdAndPatientId(activityId,patient); //积分排行 String sql ="SELECT btpd.patient_id AS patientId,btpd.total,btpd.create_time AS updateTime FROM ( SELECT sum(btpd.total) AS total, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd " + "ORDER BY " + " btpd.total DESC,btpd.create_time ASC LIMIT " + (page-1)*size+","+size; List patientDetailDOS = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count=1L; int j =0; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); j++; if (taskPatientDetailDO.getPatientId().equals(patient)){ count = j; } } //活动劵排行 String sqlCoupon ="SELECT btpd.patient_id AS patientId,btpd.couponTotal,btpd.create_time AS updateTime FROM ( SELECT sum(btpd.coupon_total) AS couponTotal, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd " + "ORDER BY " + " btpd.couponTotal DESC,btpd.create_time ASC LIMIT " + (page-1)*size+","+size; List patientDetailDOS1 = jdbcTemplate.query(sqlCoupon,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count2 = 1l; int i=0; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS1){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } i++; if (taskPatientDetailDO.getPatientId().equals(patient)){ count2=i; } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); } //活动总积分排名 /*String rankingSql1 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " activity_id, " + " create_time "+ " FROM " + " wlyy_health_bank_task_patient_detail htpd " + " where htpd.activity_id ='"+activityId+"' AND htpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"') " + " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal1 = jdbcTemplate.queryForList(rankingSql1); Long count = 0L; if(rstotal1!=null&&rstotal1.size()>0){ count = (Long) rstotal1.get(0).get("total"); }*/ Long countTotal = 0L; if ((count-2)>=0){ countTotal = (count-2); } //排名前一个积分 String rankingSql9 = "SELECT btpd1.sum as total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " activity_id " + " FROM " + " wlyy_health_bank_task_patient_detail btpd where btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO.getId()+"' AND btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" + " ORDER BY btpd1.sum desc "+ " limit "+countTotal+",1" ; List> rstotal9 = jdbcTemplate.queryForList(rankingSql9); Integer total = 0; if(rstotal9!=null&&rstotal9.size()>0){ total = Integer.parseInt(rstotal9.get(0).get("total").toString()) ; } String totalSql = " SELECT SUM(s.total) as total " + " FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"'"; List> rstotal10 = jdbcTemplate.queryForList(totalSql); Integer total1 = 0; if(rstotal10!=null&&rstotal10.size()>0){ total1 = Integer.parseInt( rstotal10.get(0).get("total").toString()); } //总活动劵的排名 /*String rankingSql2 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(coupon_total) AS sum , " + " patient_id, " + " activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail htpd WHERE htpd.activity_id ='"+activityId+"' AND htpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"+ " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"; List> rstotal2 = jdbcTemplate.queryForList(rankingSql2); Long count2 = 0L; if(rstotal2!=null&&rstotal2.size()>0){ count2 = (Long) rstotal2.get(0).get("total"); }*/ Long countTotal1 = 0L; if ((count2-2)>=0){ countTotal1=(count2-2); } //活动劵前一个活动劵 String rankingSql10 = "SELECT btpd1.sum as total FROM (SELECT " + " SUM(coupon_total) AS sum , " + " patient_id, " + " activity_id "+ "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" + " ORDER BY btpd1.sum desc "+ " limit "+countTotal1+",1" ; List> totalSql10 = jdbcTemplate.queryForList(rankingSql10); Integer total2 = 0; if(totalSql10!=null&&totalSql10.size()>0){ total2 = Integer.parseInt(totalSql10.get(0).get("total").toString()); } String totalSql1 = " SELECT SUM(s.coupon_total) as total " + " FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"'"; List> totalSql11 = jdbcTemplate.queryForList(totalSql1); Integer total3 = 0; if(totalSql11!=null&&totalSql11.size()>0){ total3 = Integer.parseInt(totalSql11.get(0).get("total").toString()); } //活动劵 //街道 String rankingSql3 ="SELECT btpd1.patientId, btpd1.total, btpd1.create_time AS updateTime FROM (SELECT btpd.patient_id AS patientId,btpd.total,btpd.create_time AS create_time FROM ( SELECT sum(btpd.total) AS total, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd "+ "ORDER BY " + " btpd.total DESC)btpd1 WHERE btpd1.patientId IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.street = '"+street+"')"+" ORDER BY btpd1.create_time "; List patientDetailDOS2 = jdbcTemplate.query(rankingSql3,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count3=1L; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS2){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); if (!taskPatientDetailDO.getPatientId().equals(patient)){ count3+=1; } } //积分社区排行 String sql1= "select s.rank,IFNULL(s.sum,0) as sum,s.patient_id from (SELECT @rownum :=@rownum + 1 AS rank, r.sum, r.patient_id FROM " + "(SELECT@rowNum := 0) b," + " (SELECT sum(bcd.integrate) AS sum, hbtd.patient_id, hbtd.create_time FROM " + "wlyy_health_bank_task_patient_detail hbtd " + " LEFT JOIN wlyy_health_bank_credits_detail bcd ON hbtd.patient_id=bcd.patient_id " + "AND bcd.transaction_id='"+taskPatientDetailDO1.getTaskId()+"'" + "WHERE hbtd.task_id = '"+taskPatientDetailDO1.getTaskId()+"' " +"and hbtd.hospital = '"+hospital+"'"+ "GROUP BY hbtd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC)s where s.patient_id ='"+patient+"'"; List> maps = jdbcTemplate.queryForList(sql1); long count10=0L; if (maps!= null && maps.size()!=0){ count10 =new Double((Double) maps.get(0).get("rank")).longValue(); } //区排名 String rankingSql4 ="SELECT btpd1.patientId, btpd1.total, btpd1.create_time AS updateTime FROM (SELECT btpd.patient_id AS patientId,btpd.total,btpd.create_time AS create_time FROM ( SELECT sum(btpd.total) AS total, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd "+ "ORDER BY " + " btpd.total DESC)btpd1 WHERE btpd1.patientId IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.town = '"+town+"')"+" ORDER BY btpd1.create_time "; List patientDetailDOS3 = jdbcTemplate.query(rankingSql4,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count4=1L; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS3){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); if (!taskPatientDetailDO.getPatientId().equals(patient)){ count4+=1; }else { break; } } //活动劵排名 //街道 String rankingSql5 ="SELECT btpd1.patientId, btpd1.couponTotal, btpd1.create_time AS updateTime FROM (SELECT btpd.patient_id AS patientId,btpd.couponTotal,btpd.create_time AS create_time FROM ( SELECT sum(btpd.coupon_total) AS couponTotal, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd " + "ORDER BY " + " btpd.couponTotal DESC )btpd1 WHERE btpd1.patientId IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.street = '"+street+"') ORDER BY btpd1.create_time "; List patientDetailDOS4 = jdbcTemplate.query(rankingSql5,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count5 = 1l; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS4){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } if (taskPatientDetailDO.getPatientId().equals(taskPatientDetailDO.getPatientId())){ count5+=1; } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); } //区排名 String rankingSql6 ="SELECT btpd1.patientId, btpd1.couponTotal, btpd1.create_time AS updateTime FROM (SELECT btpd.patient_id AS patientId,btpd.couponTotal,btpd.create_time AS create_time FROM ( SELECT sum(btpd.coupon_total) AS couponTotal, btpd.patient_id,btpd.create_time " + "FROM wlyy_health_bank_task_patient_detail btpd " + "WHERE btpd.activity_id = '"+activityId + "' GROUP BY btpd.patient_id " + " ) btpd " + "ORDER BY " + " btpd.couponTotal DESC )btpd1 WHERE btpd1.patientId IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.town = '"+town+"') ORDER BY btpd1.create_time "; List patientDetailDOS5 = jdbcTemplate.query(rankingSql6,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class)); long count6 = 1l; for (TaskPatientDetailDO taskPatientDetailDO:patientDetailDOS5){ String timeSeparated = DateUtils.getDatePoor(new Date(),taskPatientDetailDO.getUpdateTime()); String goodsSql = "select * from wlyy_health_bank_exchange_goods where patient_id ='"+taskPatientDetailDO.getPatientId()+"' " + "and activity_goods_id in(select id from wlyy_health_bank_activity_goods where activity_id ='"+activityId+"')"; List exchangeGoodsDOS = jdbcTemplate.query(goodsSql,new BeanPropertyRowMapper<>(ExchangeGoodsDO.class)); for (ExchangeGoodsDO exchangeGoodsDO :exchangeGoodsDOS){ TaskGoodsDO taskGoodsDO = taskGoodsDao.selectById(exchangeGoodsDO.getActivityGoodsId()); exchangeGoodsDO.setTaskGoodsDO(taskGoodsDO); } taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS); taskPatientDetailDO.setTimeSeparated(timeSeparated); if (!taskPatientDetailDO.getPatientId().equals(taskPatientDetailDO.getPatientId())){ count6+=1; }else { break; } } //社区排行 String sql8= "select s.rank,IFNULL(s.sum,0) as sum,s.patient_id from (SELECT @rownum :=@rownum + 1 AS rank, r.sum, r.patient_id FROM " + "(SELECT@rowNum := 0) b," + " (SELECT sum(bcd.coupon) AS sum, hbtd.patient_id, hbtd.create_time FROM " + "wlyy_health_bank_task_patient_detail hbtd " + " LEFT JOIN wlyy_health_bank_credits_detail bcd ON hbtd.patient_id=bcd.patient_id " + "AND bcd.transaction_id='"+taskPatientDetailDO1.getTaskId()+"'" + "WHERE hbtd.task_id = '"+taskPatientDetailDO1.getTaskId()+"' " +"and hbtd.hospital = '"+hospital+"'"+ "GROUP BY hbtd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC)s where s.patient_id ='"+patient+"'"; List> maps1 = jdbcTemplate.queryForList(sql8); long count11=0L; if (maps1!= null && maps1.size()!=0){ count11 = new Double((Double) maps1.get(0).get("rank")).longValue(); } //街道排名 /* String rankingSql3 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.coupon_total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE btpd1.patient_id IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.street = '"+street+"') AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"+ " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal3 = jdbcTemplate.queryForList(rankingSql3); Long count3 = 0L; if(rstotal3!=null&&rstotal3.size()>0){ count3 = (Long) rstotal3.get(0).get("total"); }*/ //区排名 /* String rankingSql4 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.coupon_total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE btpd1.patient_id IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.town = '"+town+"') AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"+ " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal4 = jdbcTemplate.queryForList(rankingSql4); Long count4 = 0L; if(rstotal4!=null&&rstotal4.size()>0){ count4= (Long) rstotal4.get(0).get("total"); }*/ //积分 //街道排名 /*String rankingSql5 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE btpd1.patient_id IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.street = '"+street+"') AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"+ " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal5 = jdbcTemplate.queryForList(rankingSql5); Long count5 = 0L; if(rstotal5!=null&&rstotal5.size()>0){ count5 = (Long) rstotal5.get(0).get("total"); } //区排名 String rankingSql6 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO.getId()+"' and btpd1.patient_id IN(SELECT p.code FROM wlyy.wlyy_patient p WHERE p.town = '"+town+"') AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" + " AND btpd1.create_time >=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal6 = jdbcTemplate.queryForList(rankingSql6); Long count6 = 0L; if(rstotal6!=null&&rstotal6.size()>0){ count6= (Long) rstotal6.get(0).get("total"); } */ //活动劵团队排名 String rankingSql7 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.coupon_total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE btpd1.patient_id IN(SELECT sf.patient FROM wlyy.wlyy_sign_family sf WHERE sf.status=1 " + "AND sf.admin_team_code=(SELECT sf.admin_team_code FROM wlyy.wlyy_sign_family sf WHERE sf.status=1 AND sf.patient='"+patient+"') ) AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')"+ " AND btpd1.create_time <=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal7 = jdbcTemplate.queryForList(rankingSql7); Long count7 = 0L; if(rstotal7!=null&&rstotal7.size()>0){ count7 = (Long) rstotal7.get(0).get("total"); } //积分团队排名 String rankingSql8 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(btpd.total) AS sum , " + " btpd.patient_id, " + " btpd.activity_id," + " create_time " + "FROM " + " wlyy_health_bank_task_patient_detail btpd WHERE btpd.activity_id ='"+activityId+"' AND btpd.patient_id !='"+patient+ "' GROUP BY patient_id )btpd1 " + "WHERE btpd1.patient_id IN(SELECT sf.patient FROM wlyy.wlyy_sign_family sf WHERE sf.status=1 " + "AND sf.admin_team_code=(SELECT sf.admin_team_code FROM wlyy.wlyy_sign_family sf WHERE sf.status=1 AND sf.patient='"+patient+"') ) AND btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" + " AND btpd1.create_time <=(SELECT s.create_time " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal8 = jdbcTemplate.queryForList(rankingSql8); Long count8 = 0L; if(rstotal8!=null&&rstotal8.size()>0){ count8 = (Long) rstotal8.get(0).get("total"); } /* //活动劵 String rankingSql4 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(coupon_total) AS sum , " + " patient_id, " + " activity_id " + "FROM " + " wlyy_health_bank_task_patient_detail " + "GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO.getId()+"'"+buffer+" AND btpd1.sum >= (SELECT SUM(s.coupon_total) as couponTotal " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal4 = jdbcTemplate.queryForList(rankingSql4); Long count4 = 0L; if(rstotal4!=null&&rstotal4.size()>0){ count4= (Long) rstotal4.get(0).get("total"); } //积分 String rankingSql5 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " activity_id " + "FROM " + " wlyy_health_bank_task_patient_detail " + "GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO.getId()+"'"+buffer+" AND btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal5 = jdbcTemplate.queryForList(rankingSql5); Long count5 = 0L; if(rstotal5!=null&&rstotal5.size()>0){ count5 = (Long) rstotal5.get(0).get("total"); } String rankingSql6 = "SELECT count(1)+1 AS total FROM (SELECT " + " SUM(total) AS sum , " + " patient_id, " + " activity_id " + "FROM " + " wlyy_health_bank_task_patient_detail " + "GROUP BY patient_id )btpd1 " + "WHERE " + " btpd1.activity_id = '"+activityDO.getId()+"'"+buffer+" AND btpd1.sum >= (SELECT SUM(s.total) as total " + "FROM wlyy_health_bank_task_patient_detail s WHERE s.patient_id ='"+patient+"' AND s.activity_id='"+activityId+"')" ; List> rstotal6 = jdbcTemplate.queryForList(rankingSql6); Long count6 = 0L; if(rstotal6!=null&&rstotal6.size()>0){ count6= (Long) rstotal6.get(0).get("total"); }*/ ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityId); JSONObject object = JSONObject.parseObject(activityRuleDO.getValue1()); Integer type = object.getInteger("type"); if (type == 2){ jsonObject.put("activityTownCouponRanking",count6);//活动劵全区排名 jsonObject.put("activityStreetCouponRanking",count5);//活动劵街道排名 jsonObject.put("activityCouponRanking",count2);//活动劵的排名 jsonObject.put("couponTotal",taskPatientDetailDO1.getCouponTotal());//本人活动劵总数 jsonObject.put("couponRanking",patientDetailDOS1);//活动劵人数排名 jsonObject.put("signCouponRanking",count7);//活动劵团队排名 jsonObject.put("activityHospitalCouponRanking",count11);//社区排名 if (count2 ==1){ jsonObject.put("differTotal",0); }else { jsonObject.put("differTotal",total2-total3);//相距前一个差多少活动劵 } }else if (type ==1){ jsonObject.put("IntegrateRanking",patientDetailDOS); jsonObject.put("activityStreetIntegrateRanking",count3);//积分街道排名 jsonObject.put("total",taskPatientDetailDO1.getTotal());//本人总积分 jsonObject.put("activityTownIntegrateRanking",count4);//积分全区排名 jsonObject.put("activityRanking",count);//积分排名 jsonObject.put("signRanking",count8);//积分团队排名 jsonObject.put("activityHospitalIntegrateRanking",count10);//社区排名 if (count==1){ jsonObject.put("differTotal",0);//相距前一个差多少积分 }else { jsonObject.put("differTotal",total-total1);//相距前一个差多少积分 } } jsonObject.put("type",type);//积分还是活动劵 envelop.setObj(jsonObject); return envelop; } public JSONObject findActivityByType(String type){ JSONObject object=new JSONObject(); List types = Arrays.asList(type.split(",")); for(String str:types){ List lst= activityDao.findByIdAndType(str); if(null!=lst&&lst.size()>0){ object.put(str,true); }else { object.put(str,false); } } return object; } public JSONObject ListActivityDO(String type, String status,String crowdType,String releaseTime,String activityOfflineTime, String filter,Integer page,Integer size)throws ParseException { JSONObject obj=new JSONObject(); StringBuffer stringBuffer=new StringBuffer(); if (StringUtils.isNotEmpty(type)) { stringBuffer.append(" and type='").append(type).append("'"); } if (StringUtils.isNotEmpty(filter)) { stringBuffer.append(" and (title like '%").append(filter).append("%' or ").append("organizer like '%").append(filter).append("%' or ").append("area_name like '%").append(filter).append("%')"); } if (null != status && "2".equals(status) ) { stringBuffer.append(" and status in (-1,2) "); } else if (null != status && "1".equals(status) ) { stringBuffer.append(" and status in (0,1) "); } if(null!=crowdType&&StringUtils.isNotBlank(crowdType)){ stringBuffer.append(" and crowd_type="+crowdType); } if(StringUtils.isNotBlank(releaseTime)){ stringBuffer.append(" and activity_offline_time>='"+releaseTime+" 00:00:00' "); } if(StringUtils.isNotBlank(activityOfflineTime)){ stringBuffer.append(" and activity_offline_time<='"+ activityOfflineTime+" 59:59:59' "); } stringBuffer.append(" order by create_time desc"); String sql = "SELECT * FROM wlyy_health_bank.wlyy_health_bank_activity WHERE 1=1 AND del_flag=1 "+stringBuffer.toString()+" LIMIT "+(page-1)*size+","+size; List activityDOList = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class)); String sqlcount = "SELECT COUNT(1) AS total FROM wlyy_health_bank.wlyy_health_bank_activity WHERE 1=1 AND del_flag=1 "+stringBuffer.toString(); List> rstotal = jdbcTemplate.queryForList(sqlcount); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ count = (Long) rstotal.get(0).get("total"); } obj .put("activityDOList",activityDOList); obj .put("count",count); return obj; } /** * 查询排序 * * @return */ public List select(){ return activityDao.selectBySort(); } /** * 调整活动的顺序 * * @param array * @return */ public MixEnvelop updateSort(JSONArray array){ MixEnvelop envelop = new MixEnvelop(); for (int i=0;i selectByUnionId(String unionId,String patient,String idCard,Integer page,Integer size){ String sql ="select hba.* from wlyy_health_bank_activity hba right join wlyy_health_bank_task_patient_detail htpd on htpd.activity_id=hba.id " + "AND htpd.union_id='"+unionId+"' AND htpd.patient_id ='"+patient+"' and htpd.patient_idcard ='"+idCard+"' " + "where hba.del_flag=1 and hba.type='竞走' order by hba.create_time desc LIMIT " + (page-1)*size+","+size; List activityDOList = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class)); for (ActivityDO activityDO:activityDOList){ ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO.getId()); JSONObject object = JSONObject.parseObject(activityRuleDO.getValue2()); String activityOfflineTime = object.getString("deadLine"); List idList = Arrays.asList(activityOfflineTime.split(",")); if (idList !=null && idList.size()!=0){ String startTime = idList.get(0); Date startDate = DateUtil.strToDateLong(startTime); String endTime = idList.get(1); Date endDate = DateUtil.strToDateLong(endTime); String nowTime = DateUtil.dateToStrLong(DateUtil.getNowDate()); Date nowDate = DateUtil.strToDateLong(nowTime); if(startDate.getTime()<=nowDate.getTime()&&endDate.getTime()>=nowDate.getTime()){ activityDO.setFlag(1); }else { activityDO.setFlag(0); } } activityDO.setActivityRuleDO(activityRuleDO); List taskDOS = taskDao.selectByActivityId(activityDO.getId()); if (taskDOS!=null&&taskDOS.size()!=0){ activityDO.setTaskDOS(taskDOS); String sqlCount1 = "select sum(integrate) as total from wlyy_health_bank_credits_detail where transaction_id='"+taskDOS.get(0).getId()+"' and patient_id = '"+patient+"'" + "and create_time >= '"+DateUtils.getDayBegin()+"' and create_time <='"+DateUtils.getDayEnd()+"' AND description IS NULL "; List> rstotal1 = jdbcTemplate.queryForList(sqlCount1); Long count1 = 0L; if(rstotal1!=null&&rstotal1.size()>0){ Object count = rstotal1.get(0).get("total"); if (count!=null&&count!=""){ count1 =Long.parseLong(count.toString()) ; } } activityDO.setNowTotal(count1); } } List activityDOList1 = new ArrayList<>(); for (ActivityDO activityDO :activityDOList){ if(activityDO.getFlag()==1){ activityDOList1.add(0,activityDO); }else { activityDOList1.add(activityDO); } } String sqlCount ="select count(1) AS total from wlyy_health_bank_activity hba right join wlyy_health_bank_task_patient_detail htpd on htpd.activity_id=hba.id " + "where hba.del_flag=1 and hba.type='竞走' order by hba.create_time desc "; List> rstotal = jdbcTemplate.queryForList(sqlCount); Long count = 0L; if(rstotal!=null&&rstotal.size()>0){ count = (Long) rstotal.get(0).get("total"); } return MixEnvelop.getSuccessListWithPage(HealthBankMapping.api_success,activityDOList1,page,size,count); } /** * 获取当前居民参加的活动 * * @param unionId * @param patient * @param idCard * @return */ public List selectNowByUnionId(String unionId,String patient,String idCard){ String sql ="select hba.* from wlyy_health_bank_activity hba right join wlyy_health_bank_task_patient_detail htpd on htpd.activity_id=hba.id " + "AND htpd.union_id='"+unionId+"' AND htpd.patient_id ='"+patient+"' and htpd.patient_idcard ='"+idCard+"' " + "where hba.del_flag=1 and hba.type='竞走' order by hba.create_time desc " ; List activityDOList = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class)); List activityDOS = new ArrayList<>(); for (ActivityDO activityDO:activityDOList){ ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO.getId()); JSONObject object = JSONObject.parseObject(activityRuleDO.getValue2()); String activityOfflineTime = object.getString("activityOfflineTime"); List idList = Arrays.asList(activityOfflineTime.split(",")); activityDO.setActivityRuleDO(activityRuleDO); List taskDOS = taskDao.selectByActivityId(activityDO.getId()); if (taskDOS!=null&&taskDOS.size()!=0){ activityDO.setTaskDOS(taskDOS); } if (idList !=null && idList.size()!=0){ String startTime = idList.get(0); String endTime = idList.get(1); String nowTime = DateUtil.dateToStrLong(DateUtil.getNowDate()); if((startTime.compareTo(nowTime)==-1&&endTime.compareTo(nowTime)==1)||startTime.compareTo(nowTime)==0||endTime.compareTo(nowTime)==0){ activityDO.setFlag(1); activityDOS.add(activityDO); } } } return activityDOS; } /** * 小程序活动排名 * * @param taskId * @param size * @return */ public JSONObject selectAppletsRanking(Integer flag,String flagName,String taskId,Integer size,String patient){ StringBuffer buffer = new StringBuffer(); if (flag == 1){ buffer.append(""); }else if (flag == 2){ buffer.append(" and hbtd.town='"+flagName+"' "); }else if (flag==3){ buffer.append(" and hbtd.hospital = '"+flagName+"'"); } String sql = "SELECT @rownum :=@rownum + 1 AS rank, IFNULL(r.sum,0) as sum , r.patient_id AS patient FROM " + "(SELECT@rowNum := 0) b," + " (SELECT " + " sum(bcd.integrate) AS sum, hbtd.patient_id, hbtd.create_time FROM wlyy_health_bank_task_patient_detail hbtd " + " LEFT JOIN wlyy_health_bank_credits_detail bcd ON hbtd.patient_id=bcd.patient_id AND bcd.transaction_id= '"+taskId + "' WHERE hbtd.task_id = '"+taskId+"' " +buffer+ "GROUP BY hbtd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC LIMIT 0,"+size; List> list = jdbcTemplate.queryForList(sql); String sql1= "select s.rank,IFNULL(s.sum,0) as sum,s.patient_id from (SELECT @rownum :=@rownum + 1 AS rank, r.sum, r.patient_id FROM " + "(SELECT@rowNum := 0) b," + " (SELECT sum(bcd.integrate) AS sum, hbtd.patient_id, hbtd.create_time FROM " + "wlyy_health_bank_task_patient_detail hbtd " + " LEFT JOIN wlyy_health_bank_credits_detail bcd ON hbtd.patient_id=bcd.patient_id " + "AND bcd.transaction_id='"+taskId+"'" + "WHERE hbtd.task_id = '"+taskId+"' " +buffer+ "GROUP BY hbtd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC)s where s.patient_id ='"+patient+"'"; List> list1 = jdbcTemplate.queryForList(sql1); JSONObject object = new JSONObject(); object.put("list",list); if (list1!=null&&list1.size()!=0){ object.put("patient",list1.get(0)); }else { object.put("patient",null); } return object; } }