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.JSONArray;
import com.alibaba.fastjson.JSONObject;
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.Transactional;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @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
     */
    public MixEnvelop<Boolean, Boolean> insert(ActivityDO activityDO,String value1,String value2,String value3){
        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());
//        if (1 == activityDO.getStatus()) {
//            activityDO.setReleaseTime(new Date());
//        }
        activityDO.setDelFlag(1);
      /*  activityDO.setDel(1);*/
        activityDO= activityDao.save(activityDO);
        if (value3 !=null && value3 !=""){
            JSONObject object = JSONObject.parseObject(value3);
            String exchangeType = object.getString("exchangeType");
            JSONArray array = object.getJSONArray("goodDetail");
            for (int i =0;i<array.size();i++){
                JSONObject jsonObject = array.getJSONObject(i);
                if (exchangeType.equals("2")){
                    TaskGoodsDO taskGoodsDO = new TaskGoodsDO();
                    taskGoodsDO.setActivityId(activityDO.getId());
                    taskGoodsDO.setCoupon(jsonObject.getInteger("integrate"));
                    taskGoodsDO.setImg(jsonObject.getString("img"));
                    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
     */
    public MixEnvelop<Boolean, Boolean> update(ActivityDO activityDO, String value1, String value2, String value3) throws Exception {
        if (StringUtils.isBlank(activityDO.getId())) {
            throw new Exception("活动id不能为空！");
        }
        ActivityRuleDO activityRuleDO = activityRuleDao.selectByActivityId(activityDO.getId());
        activityRuleDO.setUpdateTime(new Date());
        activityRuleDO.setSaasId("dev");
//        if (1 == activityDO.getStatus()) {
//            activityDO.setReleaseTime(new Date());
//        }
        activityRuleDO.setValue1(value1);
        activityRuleDO.setValue2(value2);
        activityRuleDO.setValue3(value3);
        activityRuleDO.setDelFlag(1);
        activityRuleDO= activityRuleDao.save(activityRuleDO);
        activityDao.save(activityDO);
        activityDO.setActivityRuleDO(activityRuleDO);
        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 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()+"' AND union_id = '"+ activityDO.getUnionId()+"')" +
                " )" +
                " 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()+"' AND union_id = '"+ activityDO.getUnionId()+"'";
            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()+"' AND union_id = '"+ activityDO.getUnionId()+"' )" +
                " )";
        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)){
            //上线
            String ruleJson=null!=activityRuleDO?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")? DateUtil.strToDate(sdf2.format(sdf1.parse(obj.get("registrationTime").toString())),"yyyy-MM-dd HH:mm"):null);
            //参与截止时间
            Date deadLine=(null!=obj.get("deadLine")? DateUtil.strToDate(sdf2.format(sdf1.parse(obj.get("deadLine").toString())),"yyyy-MM-dd HH:mm"):null);
            //活动下线时间
            Date activityOfflineTime=(null!=obj.get("activityOfflineTime")? DateUtil.strToDate(sdf2.format(sdf1.parse(obj.get("activityOfflineTime").toString())),"yyyy-MM-dd HH:mm"):null);
            //获取当前时间
            Date now = DateUtil.strToDate(sdf2.format(new Date()),"yyyy-MM-dd HH:mm");
            if(activityOfflineTime.compareTo(now)<=0){
                //活动下线
                activityDO.setStatus(2);
            }else if(activityOfflineTime.compareTo(now)>0 && deadLine.compareTo(now)<=0){
                //活动结束
                activityDO.setStatus(-1);

            }else if(deadLine.compareTo(now)>0&&registrationTime.compareTo(now)<=0){
                //活动未结束
                activityDO.setStatus(0);
            }else if (registrationTime.compareTo(now)>1){
                //报名未截止
                activityDO.setStatus(1);
            }
        }
//        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.update_time AS updateTime FROM ( SELECT sum(btpd.total) AS total, btpd.patient_id,btpd.update_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 LIMIT " + (page-1)*size+","+size;
        List<TaskPatientDetailDO> patientDetailDOS = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        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);
        }
        //活动劵排行
        String sqlCoupon  ="SELECT btpd.patient_id AS patientId,btpd.couponTotal,btpd.update_time AS updateTime  FROM ( SELECT sum(btpd.coupon_total) AS couponTotal, btpd.patient_id,btpd.update_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 LIMIT " + (page-1)*size+","+size;
        List<TaskPatientDetailDO> patientDetailDOS1 = jdbcTemplate.query(sqlCoupon,new BeanPropertyRowMapper<>(TaskPatientDetailDO.class));
        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);
            }
            taskPatientDetailDO.setExchangeGoodsDO(exchangeGoodsDOS);
            taskPatientDetailDO.setTimeSeparated(timeSeparated);
        }
        //活动总积分排名
        String rankingSql1 = "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()+"' 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>> 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 " +
                "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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail " +
                "GROUP BY patient_id )btpd1 " +
                "WHERE " +
                " btpd1.activity_id = '"+activityDO.getId()+"' 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>> 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 " +
                "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 "+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 count(1)+1 AS total FROM (SELECT " +
                " SUM(btpd.coupon_total) AS sum , " +
                " btpd.patient_id, " +
                " btpd.activity_id " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "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.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+"')" ;
        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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "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+"')" ;
        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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "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.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+"')" ;
        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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "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+"')" ;
        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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "GROUP BY patient_id )btpd1 " +
                "WHERE " +
                " btpd1.activity_id = '"+activityDO.getId()+"' and  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+"')" ;
        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 " +
                "FROM " +
                " wlyy_health_bank_task_patient_detail btpd " +
                "GROUP BY patient_id )btpd1 " +
                "WHERE " +
                " btpd1.activity_id = '"+activityDO.getId()+"' and  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+"')" ;
        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",count4);//活动劵全区排名
            jsonObject.put("activityStreetCouponRanking",count3);//活动劵街道排名
            jsonObject.put("activityCouponRanking",count2);//活动劵的排名
            jsonObject.put("couponTotal",taskPatientDetailDO1.getCouponTotal());//本人活动劵总数
            jsonObject.put("couponRanking",patientDetailDOS1);//活动劵人数排名
            jsonObject.put("signCouponRanking",count7);//活动劵团队排名
            jsonObject.put("differTotal",total2-total3);//相距前一个差多少活动劵
        }else if (type ==1){
            jsonObject.put("IntegrateRanking",patientDetailDOS);
            jsonObject.put("activityStreetIntegrateRanking",count5);//积分街道排名
            jsonObject.put("total",taskPatientDetailDO1.getTotal());//本人总积分
            jsonObject.put("activityTownIntegrateRanking",count6);//积分全区排名
            jsonObject.put("activityRanking",count);//积分排名
            jsonObject.put("signRanking",count8);//积分团队排名
            jsonObject.put("differTotal",total-total1);//相距前一个差多少积分
        }
        jsonObject.put("type",type);//积分还是活动劵
        envelop.setObj(jsonObject);
        return envelop;
    }

}