从数据库中查询马上过生日的人并统计各年龄段及性别所占的人数

蔚落 2023-07-18 03:23 3阅读 0赞

业务需求：

> 从员工表中查询5天之内过生日的人，以及五天之内合同到期的人，返回一个 Map 集合，封装了员工的姓名及还有几天过生日；

**Dao 层如下：**

@Repository
    public interface EmpMapper  extends BaseMapper<Employee> { 
    
    	@Select("select * from employee\n" +
                "where DATE_FORMAT(birthday,'%m-%d') >= DATE_FORMAT(now(),'%m-%d')\n" +
                " and DATE_FORMAT(birthday,'%m-%d') <= DATE_FORMAT(date_add(now(), INTERVAL 4 DAY),'%m-%d')")
        List<Employee> birthdayReminder();
    
        @Select("select * from employee\n" +
                "where DATE_FORMAT(endContract,'%m-%d') >= DATE_FORMAT(now(),'%m-%d')\n" +
                " and DATE_FORMAT(endContract,'%m-%d') <= DATE_FORMAT(date_add(now(), INTERVAL 4 DAY),'%m-%d')")
        List<Employee> contractExpires();
    
    }

> 第二个需求是从数据库中查询员工各年龄段所占的比例，以及各性别所占的比例；

**Controller 层如下：**

@Autowired
    	EmpMapper empMapper;
    
     /** * 查询 5 天之内过生日的人 * * @return */
        @RequestMapping(value = "/birth", method = RequestMethod.GET)
        public RespBean birthdayReminder() { 
            List<Employee> employees = empMapper.birthdayReminder();
            List<Map<String, String>> list = new ArrayList<>();
            Date date = new Date();
            Map map = new HashMap();
            if (employees != null) { 
                //这里面的查询结果都是最近 5 天之内生日的
                for (Employee employee : employees) { 
                    int bir = getDay(employee.getBirthday());
                    int now = getDay(date);
                    map.put("name", employee.getName());
                    map.put("day", bir - now);
                    list.add(map);
                    map.clear();
                }
                return RespBean.ok("查询成功", list);
            }
            return RespBean.error("查询为空", null);
        }
    
        /** * 检查 5 天之内合同到期的 * * @return */
        @RequestMapping(value = "/contract", method = RequestMethod.GET)
        public RespBean contractExpires() { 
            List<Employee> employees = empMapper.contractExpires();
            List<Map<String, String>> list = new ArrayList<>();
            Date date = new Date();
            Map map = new HashMap();
            if (employees != null) { 
                //这里面的查询结果都是最近 5 天之内合同到期的
                for (Employee employee : employees) { 
                    map.put("name", employee.getName());
                    map.put("day", getDay(employee.getEndContract()) - getDay(date));
                    list.add(map);
                    map.clear();
                }
                return RespBean.ok("查询成功", list);
            }
            return RespBean.error("查询为空", null);
        }
    
        /** * 获取当前日期是该年中的多少天 * * @param date * @return */
        private int getDay(Date date) { 
            Calendar c = Calendar.getInstance();
            c.setTime(date);
            return c.get(Calendar.DAY_OF_YEAR);
        }
    
        /** * 统计一下性别信息和年龄信息，然后返回给前端绘图 * 性别分为 男,女,未统计; * - 存在 Map 中,键为 man women none ,值为他们所占的比例(均取整数,比如 30 代表 30%), * - 这样前端直接获取值然后按照所占的比例放在饼图中 * 年龄分为 0-30,31-40,41-50,51以上; * - 存在 Map 中,键为 thirty forty fifty older 中,值为它们所占的比例(整数) * - 前端根据键获取值,也就是获取比例,然后绘图展示 * 方法: GET * 返回值: Response,包括 状态信息 和 Map集合,集合中的内容上述内容 */
        @RequestMapping(value = "/genderAndAge", method = RequestMethod.GET)
        public RespBean getGenderAndAgeRatio() { 
            //获取所有成员信息
            List<Employee> employees = empMapper.getAllEmployees();
            Map<String, Integer> count;
            //判断为空
            if (employees == null) { 
                return RespBean.error("查询为空", null);
            } else { 
                count = new HashMap<>();
                count.put("man", 0);
                count.put("woman", 0);
                count.put("none", 0);
                count.put("thirty", 0);
                count.put("forty", 0);
                count.put("fifty", 0);
                count.put("older", 0);
                //遍历统计给 Map 赋值
                for (Employee employee : employees) { 
                    String gender = employee.getGender();
                    //性别计数
                    if (gender.equals("男")) { 
                        count.put("man", count.get("man") + 1);
                    } else if (gender.equals("女")) { 
                        count.put("woman", count.get("woman") + 1);
                    } else { 
                        count.put("none", count.get("none") + 1);
                    }
                    //求每一个员工的年龄
                    int age = getAgeByBirth(employee.getBirthday());
                    //年龄计数
                    if (age >= 0 && age <= 30) { 
                        count.put("thirty", count.get("thirty") + 1);
                    } else if (age >= 31 && age <= 40) { 
                        count.put("forty", count.get("forty") + 1);
                    } else if (age >= 41 && age <= 50) { 
                        count.put("fifty", count.get("fifty") + 1);
                    } else { 
                        count.put("older", count.get("older") + 1);
                    }
                }
                //计算比例
                int total = employees.size();
                count.put("man", count.get("man") * 100 / total);
                count.put("woman", count.get("woman") * 100 / total);
                count.put("none", 100 - count.get("man") - count.get("woman"));
                count.put("thirty", count.get("thirty") * 100 / total);
                count.put("forty", count.get("forty") * 100 / total);
                count.put("fifty", count.get("fifty") * 100 / total);
                count.put("older", 100 - count.get("thirty") - count.get("forty") - count.get("fifty"));
                //经过这次循环之后,Map中的计数值就对了,可以返回了
            }
            return RespBean.ok("获取成功", count);
        }
    
        /** * 根据日期计算年龄 * * @param birthday * @return */
        private static int getAgeByBirth(Date birthday) { 
            int age;
            try { 
                Calendar now = Calendar.getInstance();
                now.setTime(new Date());// 当前时间
    
                Calendar birth = Calendar.getInstance();
                birth.setTime(birthday);
    
                if (birth.after(now)) { //如果传入的时间，在当前时间的后面，返回0岁
                    age = 0;
                } else { 
                    age = now.get(Calendar.YEAR) - birth.get(Calendar.YEAR);
                    if (now.get(Calendar.DAY_OF_YEAR) > birth.get(Calendar.DAY_OF_YEAR)) { 
                        age += 1;
                    }
                }
                return age;
            } catch (Exception e) { //兼容性更强,异常后返回数据
                return 0;
            }
        }