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<Map<String, Object>> 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<Map<String, Object>> 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<Map<String, Object>> 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<Map<String, Object>> 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<Map<String, Object>> 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<Object> 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;
|
}
|
}
|