wlyy2.0/svr/svr-wlyy-health-bank/src/main/java/com/yihu/jw/service/ActivityService.java

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<ActivityDO,ActivityDao> {

    @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<Boolean, Boolean> insert(ActivityDO activityDO,String value1,String value2,String value3,String registrationNumber){
        if (value2 != null && value2 !=""){
            JSONObject object = JSONObject.parseObject(value2);
            activityDO.setActivityOfflineTime(object.getDate("activityOfflineTime"));
            activityDO.setRegistrationTime(object.getDate("registrationTime"));
            activityDO.setDeadLine(object.getDate("deadLine"));
        }
        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<array1.size();i++){
                        JSONObject jsonObject = array1.getJSONObject(i);
                        TaskGoodsDO taskGoodsDO = new TaskGoodsDO();
                        taskGoodsDO.setCreateTime(new Date());
                        taskGoodsDO.setUpdateTime(new Date());
                        taskGoodsDO.setActivityId(activityDO.getId());
                        taskGoodsDO.setCoupon(jsonObject.getInteger("couponNum"));
                        taskGoodsDO.setImg(jsonObject.getString("picUrl"));
                        taskGoodsDO.setName(jsonObject.getString("name"));
                        taskGoodsDO.setSaasId("dev");
                        taskGoodsDO.setStatus(1);
                        taskGoodsDao.save(taskGoodsDO);
                    }
                }
            }
        }
        TaskDO taskDO = new TaskDO();
        taskDO.setTitle(activityDO.getTitle());
        taskDO.setStatus(1);
        taskDO.setTransactionId(activityDO.getId());
        taskDO.setType("ACTIVITY_TASK");
        taskDO.setSaasId("dev");
        taskDO.setContent(activityDO.getDescription());
        taskDO.setPeriod(1);
        taskDO.setTaskCode("ACTIVITY_QUESTION");
        taskDO.setStartTime(activityDO.getRegistrationTime());
        taskDO.setEndTime(activityDO.getActivityOfflineTime());
        taskDO.setCreateTime(new Date());
        taskDO.setUpdateTime(new Date());
        taskDao.save(taskDO);
        ActivityRuleDO activityRuleDO = new ActivityRuleDO();
        activityRuleDO.setCreateTime(new Date());
        activityRuleDO.setUpdateTime(new Date());
        activityRuleDO.setActivityId(activityDO.getId());
        activityRuleDO.setSaasId("dev");
        activityRuleDO.setValue1(value1);

        activityRuleDO.setValue2(value2);
        activityRuleDO.setValue3(value3);
        activityRuleDO.setDelFlag(1);
      /*  activityRuleDO.setDel(1);*/
        activityRuleDao.save(activityRuleDO);
        MixEnvelop<Boolean, Boolean> envelop = new MixEnvelop<>();
        envelop.setObj(true);
        return envelop;
    }


    /**
     *  find by condition
     *
     * @param activityDO 活动参数对象
     * @param page 页码
     * @param size 每页大小
     * @return
     * @throws ParseException
     */
    public MixEnvelop<ActivityDO, ActivityDO> findByCondition(ActivityDO activityDO, Integer page, Integer size) throws ParseException {
        String sql = new ISqlUtils().getSql(activityDO,page,size,"*");
        List<ActivityDO> 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<Map<String,Object>> 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<TaskPatientDetailDO> 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<TaskDO> taskDOList = jdbcTemplate.query(tasksql,new BeanPropertyRowMapper(TaskDO.class));
            List<TaskGoodsDO> 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<Map<String,Object>> 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<Boolean, Boolean> 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);
            activityDO.setActivityOfflineTime(object.getDate("activityOfflineTime"));
            activityDO.setRegistrationTime(object.getDate("registrationTime"));
            activityDO.setDeadLine(object.getDate("deadLine"));
        }
        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<array1.size();i++){
                        JSONObject jsonObject = array1.getJSONObject(i);
                        TaskGoodsDO taskGoodsDO = new TaskGoodsDO();
                        taskGoodsDO.setActivityId(activityDO.getId());
                        taskGoodsDO.setCoupon(jsonObject.getInteger("couponNum"));
                        taskGoodsDO.setImg(jsonObject.getString("picUrl"));
                        taskGoodsDO.setName(jsonObject.getString("name"));
                        taskGoodsDO.setCreateTime(new Date());
                        taskGoodsDO.setUpdateTime(new Date());
                        taskGoodsDO.setSaasId("dev");
                        taskGoodsDO.setStatus(1);
                        taskGoodsDao.save(taskGoodsDO);
                    }
                }
            }
        }
        ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO.getId());
        activityRuleDO.setUpdateTime(new Date());
        activityRuleDO.setSaasId("dev");
        activityRuleDO.setValue1(value1);
        activityRuleDO.setValue2(value2);
        activityRuleDO.setValue3(value3);
        activityRuleDO.setDelFlag(1);
        activityRuleDao.save(activityRuleDO);
        System.out.println("----------------------------update--"+activityDO.getId()+activityDO.getAreaName());
//        activityDO.setCreateTime(new Date());
        activityDao.save(activityDO);
        MixEnvelop<Boolean, Boolean> envelop = new MixEnvelop<>();
        envelop.setObj(true);
        return envelop;
    }

    /**
     * 获取参与的活动
     *
     * @param activityDO 活动对象
     * @param page 页码
     * @param size 分页大小
     * @return
     */
    public MixEnvelop<ActivityDO, ActivityDO> selectByPatient(ActivityDO activityDO, Integer page, Integer size){
        String condition = "";
        if (activityDO.getUnionId()!=null&&activityDO.getUnionId() !=""){
            condition="AND union_id = '"+ activityDO.getUnionId()+"'";
        }
        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+")" +
                " )" +
                " LIMIT "+(page-1)*size +","+size;
        List<ActivityDO> 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<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> rstotal2 = jdbcTemplate.queryForList(taskSql);
            Long count2 = 0L;
            if(rstotal2!=null&&rstotal2.size()>0){
                count2 = (Long) rstotal2.get(0).get("total1");
            }
            activityDO1.setTotal(count2);
        }
        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+ ")" +
                " )";
        List<Map<String,Object>> 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<Boolean, Boolean> batchDelete(List<String> ids){
        MixEnvelop<Boolean, Boolean> envelop = new MixEnvelop<>();
        for (int i =0;i<ids.size();i++){
            List<TaskDO> 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<TaskPatientDetailDO> 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<Boolean, Boolean> daleteActivity(String id){
        MixEnvelop<Boolean, Boolean> envelop = new MixEnvelop<>();
            List<TaskDO> taskDOList = taskDao.selectByActivityId(id);
            for (TaskDO taskDO:taskDOList){
                taskDO.setStatus(0);
                taskDO.setCreateTime(new Date());
                taskDO.setUpdateTime(new Date());
                taskDao.save(taskDO);
            }
            List<TaskPatientDetailDO> 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)){
            //推荐
            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<ActivityDO>  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");
                //报名截止时间
                Date registrationTime=(null!=obj.get("registrationTime")? sdf2.parse(obj.get("registrationTime").toString()):null);
                //参与截止时间
                Date deadLine=(null!=obj.get("deadLine")? sdf2.parse(obj.get("deadLine").toString()):null);
                //活动下线时间
                Date activityOfflineTime=(null!=obj.get("activityOfflineTime")? sdf2.parse(obj.get("activityOfflineTime").toString()):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&&registrationTime.compareTo(now)<=0)){
                    //活动未结束
                    activityDO.setStatus(0);
                }else if (null!=registrationTime&&registrationTime.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<JSONObject,JSONObject> selectActivityRanking(String activityId,String patient,Integer page,Integer size,String street,String town){
        MixEnvelop<JSONObject,JSONObject> 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<TaskPatientDetailDO> patientDetailDOS = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count=0L;
        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<ExchangeGoodsDO> 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<TaskPatientDetailDO> patientDetailDOS1 = jdbcTemplate.query(sqlCoupon,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count2 = 0l;
        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<ExchangeGoodsDO> 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<Map<String,Object>> 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+"')" +
                " limit "+countTotal+",1" ;
        List<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> 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+"')" +
                " limit "+countTotal1+",1" ;
        List<Map<String,Object>> 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<Map<String,Object>> 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<TaskPatientDetailDO> patientDetailDOS2 = jdbcTemplate.query(rankingSql3,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count3=0L;
        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<ExchangeGoodsDO> 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 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<TaskPatientDetailDO> patientDetailDOS3 = jdbcTemplate.query(rankingSql4,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count4=0L;
        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<ExchangeGoodsDO> 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;
            }
        }

        //活动劵排名
        //街道
        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<TaskPatientDetailDO> patientDetailDOS4 = jdbcTemplate.query(rankingSql5,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count5 = 0l;
        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<ExchangeGoodsDO> 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<TaskPatientDetailDO> patientDetailDOS5 = jdbcTemplate.query(rankingSql6,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        long count6 = 0l;
        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<ExchangeGoodsDO> 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())){
                count6+=1;
            }
            taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS);
            taskPatientDetailDO.setTimeSeparated(timeSeparated);
        }





        //街道排名
       /* 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<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> 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.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<Map<String,Object>> 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.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<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> 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<Map<String,Object>> 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);//活动劵团队排名
            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);//积分团队排名
            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<String> types = Arrays.asList(type.split(","));
        for(String str:types){
            List<ActivityDO>  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<ActivityDO> 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<Map<String,Object>> 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<ActivityDO> select(){
        return activityDao.selectBySort();
    }


    /**
     * 调整活动的顺序
     *
     * @param array
     * @return
     */
    public MixEnvelop updateSort(JSONArray array){
        MixEnvelop envelop = new MixEnvelop();
        for (int i=0;i<array.size();i++){
            JSONObject jsonObject = array.getJSONObject(i);
            ActivityDO activityDO = activityDao.selectById(jsonObject.getString("id"));
            activityDO.setSort(jsonObject.getInteger("sort"));
            activityDao.save(activityDO);
        }
        envelop.setObj(true);
        return envelop;
    }


    /**
     *  查询参与的竟步走活动
     *
     * @param unionId
     * @param patient
     * @param page
     * @param size
     * @return
     */
    public MixEnvelop<ActivityDO,ActivityDO> 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<ActivityDO> 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("activityOfflineTime");
            List<String> idList = Arrays.asList(activityOfflineTime.split(","));
            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);
                }else {
                    activityDO.setFlag(0);
                }
            }
            activityDO.setActivityRuleDO(activityRuleDO);
            List<TaskDO> taskDOS = taskDao.selectByActivityId(activityDO.getId());
            if (taskDOS!=null&&taskDOS.size()!=0){
                activityDO.setTaskDOS(taskDOS);
            }
        }
        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<Map<String,Object>> 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,activityDOList,page,size,count);

    }


    /**
     *  获取当前居民参加的活动
     *
     * @param unionId
     * @param patient
     * @param idCard
     * @return
     */
    public List<ActivityDO> 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<ActivityDO> activityDOList = jdbcTemplate.query(sql,new BeanPropertyRowMapper(ActivityDO.class));
        List<ActivityDO> 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<String> idList = Arrays.asList(activityOfflineTime.split(","));
            activityDO.setActivityRuleDO(activityRuleDO);
            List<TaskDO> 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 patients
     * @param taskId
     * @param size
     * @return
     */
    public JSONObject selectAppletsRanking(JSONArray patients,String taskId,Integer size,String patient){
        StringBuffer buffer = new StringBuffer();
        if (patients!= null&&patients.size()!=0){
            buffer.append("and bcd.patient_id IN(");
            for (int i=0;i<patients.size();i++){
                buffer.append("'"+patients.getString(i)+"',");
            }
            buffer.deleteCharAt(buffer.length()-1);
            buffer.append(") ");
        }
        String sql = "SELECT @rownum :=@rownum + 1 AS rank, r.sum, r.patient_id AS patient FROM " +
                "(SELECT@rowNum := 0) b," +
                " (SELECT count(bcd.integrate) AS sum, bcd.patient_id, bcd.create_time FROM wlyy_health_bank_credits_detail bcd\n" +
                "WHERE bcd.transaction_id = '"+taskId+"' " +buffer+
                "GROUP BY bcd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC LIMIT 0,"+size;
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);

        String sql1= "select s.rank,s.sum,s.patient_id from (SELECT @rownum :=@rownum + 1 AS rank, r.sum, r.patient_id FROM " +
                "(SELECT@rowNum := 0) b," +
                " (SELECT count(bcd.integrate) AS sum, bcd.patient_id, bcd.create_time FROM wlyy_health_bank_credits_detail bcd\n" +
                "WHERE bcd.transaction_id = '"+taskId+"' " +buffer+
                "GROUP BY bcd.patient_id) r ORDER BY r.sum DESC,r.create_time DESC)s where s.patient_id ='"+patient+"'";
        List<Map<String,Object>> 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;
    }


}