package zbooksoft.fams.dao; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.Date; import java.time.LocalDate; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.regex.Pattern; /** * 资产统计分析的汇总查询 * @author ycq * @date 2026-10-16 */ @Repository public class AssetStatDao { /** 有效资产:未删除、已编号 */ private static final String VALID_CARD = "ifnull(c.DELETE_STATE, 0) = 0 and ifnull(c.ASSET_NO, '') <> ''"; private static final String NOT_SCRAPPED = " and ifnull(c.ASSET_STATE, '') <> '报废'"; private static final Pattern SAFE_COLUMN = Pattern.compile("^[A-Z_]+$"); @Autowired private JdbcTemplate jdbcTemplate; /** * 按资产状态统计数量与原值(含报废) * @return ASSET_STATE、CNT、AMOUNT */ public List> countByState() { return jdbcTemplate.queryForList("select ifnull(c.ASSET_STATE, '未设置') as ASSET_STATE, count(1) as CNT, ifnull(sum(c.ORIGINAL_VALUE), 0) as AMOUNT " + "from BUS_ASSET_CARD c where " + VALID_CARD + " group by ifnull(c.ASSET_STATE, '未设置')"); } /** * 按一级类别统计数量与原值 * @param includeScrapped 是否包含报废资产 * @return NAME、CNT、AMOUNT,按数量降序 */ public List> countByTopCategory(boolean includeScrapped) { return jdbcTemplate.queryForList("select ifnull(t.CATEGORY_NAME, ifnull(c.TOP_CATEGORY_CODE, '未分类')) as NAME, count(1) as CNT, " + "ifnull(sum(c.ORIGINAL_VALUE), 0) as AMOUNT from BUS_ASSET_CARD c " + "left join BUS_ASSET_CATEGORY t on t.CATEGORY_LEVEL = 1 and t.CATEGORY_CODE = c.TOP_CATEGORY_CODE " + "where " + VALID_CARD + (includeScrapped ? "" : NOT_SCRAPPED) + " group by ifnull(t.CATEGORY_NAME, ifnull(c.TOP_CATEGORY_CODE, '未分类')) order by CNT desc"); } /** * 按使用部门统计数量与原值,取数量最多的若干个 * @param includeScrapped 是否包含报废资产 * @param top 取前几名 * @return NAME、CNT、AMOUNT,按数量降序 */ public List> countByDept(boolean includeScrapped, int top) { return jdbcTemplate.queryForList("select ifnull(nullif(c.USE_DEPT_NAME, ''), '未分配部门') as NAME, count(1) as CNT, ifnull(sum(c.ORIGINAL_VALUE), 0) as AMOUNT " + "from BUS_ASSET_CARD c where " + VALID_CARD + (includeScrapped ? "" : NOT_SCRAPPED) + " group by ifnull(nullif(c.USE_DEPT_NAME, ''), '未分配部门') order by CNT desc limit ?", top); } /** * 按月、按变动类型统计流转记录条数 * @param from 起始日期(含) * @return MONTH(yyyy-MM)、CHANGE_TYPE、CNT */ public List> countChangesByMonth(LocalDate from) { return jdbcTemplate.queryForList("select date_format(OPERATE_TIME, '%Y-%m') as MONTH, CHANGE_TYPE, count(1) as CNT from BUS_ASSET_CHANGE_LOG " + "where OPERATE_TIME >= ? group by date_format(OPERATE_TIME, '%Y-%m'), CHANGE_TYPE", Date.valueOf(from)); } /** * 按月统计已确认的采购入库资产数与金额 * @param from 起始日期(含) * @return MONTH(yyyy-MM)、CNT、AMOUNT */ public List> sumReceiptsByMonth(LocalDate from) { return jdbcTemplate.queryForList("select date_format(CONFIRM_TIME, '%Y-%m') as MONTH, ifnull(sum(ASSET_COUNT), 0) as CNT, ifnull(sum(TOTAL_AMOUNT), 0) as AMOUNT " + "from BUS_ASSET_RECEIPT where BILL_STATE = '已入库' and CONFIRM_TIME >= ? group by date_format(CONFIRM_TIME, '%Y-%m')", Date.valueOf(from)); } /** * 统计某个到期日字段落在区间内的资产数(不含报废) * @param column 到期日字段(白名单内) * @param borrowOnly 是否只统计借用中的资产 * @param from 起始日期(含),null 表示不限 * @param to 截止日期(含) * @return 资产数 */ public long countDue(String column, boolean borrowOnly, LocalDate from, LocalDate to) { if (!SAFE_COLUMN.matcher(column).matches()) { throw new IllegalArgumentException("非法列名:" + column); } StringBuilder sql = new StringBuilder("select count(1) from BUS_ASSET_CARD c where " + VALID_CARD + NOT_SCRAPPED + " and c." + column + " is not null"); List args = new ArrayList<>(); if (from != null) { sql.append(" and c.").append(column).append(" >= ?"); args.add(Date.valueOf(from)); } sql.append(" and c.").append(column).append(" < ?"); args.add(Date.valueOf(to.plusDays(1))); if (borrowOnly) { sql.append(" and c.ASSET_STATE = '借用'"); } Long count = jdbcTemplate.queryForObject(sql.toString(), Long.class, args.toArray()); return count == null ? 0 : count; } }