ruoyi-admin/src/main/java/com/ruoyi/web/controller/api/AssetStatisticsController.java
@@ -2,10 +2,20 @@ import com.ruoyi.common.basic.PageInfo; import com.ruoyi.common.core.domain.R; import com.ruoyi.common.core.domain.entity.TDept; import com.ruoyi.framework.web.service.TokenService; import com.ruoyi.system.dto.asset.AssetTypeDTO; import com.ruoyi.system.model.AssetMain; import com.ruoyi.system.query.AssetInventoryListQuery; import com.ruoyi.system.query.AssetStatisticsListDetailQuery; import com.ruoyi.system.query.AssetStatisticsListQuery; import com.ruoyi.system.query.NotificationListQuery; import com.ruoyi.system.service.AssetInventoryRecordService; import com.ruoyi.system.service.AssetMainService; import com.ruoyi.system.service.AssetTypeService; import com.ruoyi.system.service.TDeptService; import com.ruoyi.system.vo.AssetInventoryVO; import com.ruoyi.system.vo.AssetStatisticsDetailVO; import com.ruoyi.system.vo.AssetStatisticsVO; import com.ruoyi.system.vo.asset.AssetTypeTreeVO; import com.ruoyi.system.vo.system.NotificationVO; @@ -13,11 +23,17 @@ import io.swagger.annotations.ApiOperation; import io.swagger.annotations.ApiParam; import lombok.RequiredArgsConstructor; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.util.StringUtils; import org.springframework.validation.annotation.Validated; import org.springframework.web.bind.annotation.*; import javax.validation.Valid; import java.time.LocalDateTime; import java.time.YearMonth; import java.util.ArrayList; import java.util.List; import java.util.stream.Collectors; /** * <p> @@ -27,14 +43,23 @@ * @author WuGuanFengYue * @since 2025-09-15 */ @Api(tags = {"资产-资产汇总"}) @Api(tags = {"资产-资产汇总-资产出入库-闲置房产"}) @Validated @RestController @RequestMapping("/asset-statistics") @RequiredArgsConstructor public class AssetStatisticsController { private final AssetTypeService assetTypeService; @Autowired private AssetTypeService assetTypeService; @Autowired private AssetInventoryRecordService assetInventoryRecordService; @Autowired private AssetMainService assetMainService; @Autowired private TokenService tokenService; @Autowired private TDeptService deptService; @ApiOperation("获取资产类型树形数据") @GetMapping("/tree") @@ -42,15 +67,82 @@ List<AssetTypeTreeVO> treeData = assetTypeService.getAssetTypeTree(); return R.ok(treeData); } public List<Integer> getAllSubDeptIds(Integer parentId) { List<Integer> allSubIds = new ArrayList<>(); allSubIds.add(parentId); getSubDeptIdsRecursive(parentId, allSubIds); return allSubIds.stream().distinct().collect(Collectors.toList()); } private void getSubDeptIdsRecursive(Integer parentId, List<Integer> allSubIds) { // 查询直接下级 List<Integer> directSubIds = deptService.lambdaQuery().eq(TDept::getParentId, parentId).list() .stream().map(TDept::getId).collect(Collectors.toList()); for (Integer subId : directSubIds) { allSubIds.add(subId); // 递归查询下级的下级 getSubDeptIdsRecursive(subId, allSubIds); } } @ApiOperation("资产汇总分页列表") @PostMapping("/pageList") public R<PageInfo<AssetStatisticsVO>> pageList(@RequestBody AssetStatisticsListQuery query) { String deptId = tokenService.getLoginUser().getDeptId(); TDept dept = deptService.getById(tokenService.getLoginUser().getDeptId()); List<Integer> deptIds = deptService.getAllSubDeptIds(deptId); if (dept.getDeptName().contains("资产管理")){ // 可以查询所有数据 }else{ if (deptIds.isEmpty()){ } } return R.ok(assetTypeService.pageList(query)); } @ApiOperation("资产明细") @PostMapping("/pageListDetail") public R<PageInfo<AssetStatisticsVO>> pageListDetail(@RequestBody AssetStatisticsListQuery query) { return R.ok(assetTypeService.pageList(query)); public R<PageInfo<AssetStatisticsDetailVO>> pageListDetail(@RequestBody AssetStatisticsListDetailQuery query) { query.setDeptId(Integer.valueOf(tokenService.getLoginUser().getDeptId())); PageInfo<AssetStatisticsDetailVO> res = assetTypeService.pageListDetail(query); return R.ok(res); } @ApiOperation("资产出入库分页列表") @PostMapping("/pageListInventory") public R<PageInfo<AssetInventoryVO>> pageListInventory(@RequestBody AssetInventoryListQuery query) { query.setDeptId(Integer.valueOf(tokenService.getLoginUser().getDeptId())); String[] dateList = query.getDate().split(","); String month = dateList[1]; int monthValue = Integer.parseInt(month); int year = LocalDateTime.now().getYear(); LocalDateTime firstDay = LocalDateTime.of(year, monthValue, 1, 0, 0, 0); LocalDateTime lastDay = LocalDateTime.of(year, monthValue, YearMonth.of(year, monthValue).lengthOfMonth(), 23, 59, 59); query.setDateStart(firstDay); query.setDateEnd(lastDay); if (StringUtils.hasLength(query.getNameOrCode())){ // 查询出资产名称或者资产编号符合条件的code List<Integer> assetTypeIds = assetMainService.lambdaQuery().like(AssetMain::getAssetName, query.getNameOrCode()) .or() .like(AssetMain::getAssetCode, query.getNameOrCode()) .list() .stream() .map(AssetMain::getAssetTypeId) .collect(Collectors.toList()); query.setAssetTypeIds(assetTypeIds); if (assetTypeIds.isEmpty()){ return R.ok(new PageInfo<>()); } } return R.ok(assetTypeService.pageListInventory(query)); } @ApiOperation("资产出入库明细分页列表") @PostMapping("/pageListInventoryDetail") public R<PageInfo<AssetStatisticsDetailVO>> pageListInventoryDetail(@RequestBody AssetStatisticsListDetailQuery query) { PageInfo<AssetStatisticsDetailVO> res = assetTypeService.pageListDetail(query); return R.ok(res); }
@@ -154,6 +154,7 @@ List<OaApprovalApplications> approvalApplications = approvalApplicationsService.lambdaQuery().in(OaApprovalApplications::getId, applicationIds).list(); for (OaApprovalApplications approvalApplication : approvalApplications) { ApprovalTodoVO approvalTodoVO = new ApprovalTodoVO(); approvalTodoVO.setApplicationReason(approvalApplication.getApplicationReason()); approvalTodoVO.setApprovalCategory(approvalMap.get(approvalApplication.getApprovalId()).getApprovalCategory()); switch (approvalTodoVO.getApprovalCategory()){ case 1: @@ -266,7 +267,7 @@ case 13: OaApprovalApplicationMoney oaApprovalApplicationMoney = oaApprovalApplicationMoneyMap.get(approvalApplication.getId()); if (oaApprovalApplicationMoney!=null){ approvalTodoVO.setProjectName(oaApprovalApplicationMoney.getProjectName()); approvalTodoVO.setAmount(oaApprovalApplicationMoney.getApplyAmount()); approvalTodoVO.setDescription(oaApprovalApplicationMoney.getDescription()); @@ -275,7 +276,8 @@ case 15: OaApprovalApplicationPayment oaApprovalApplicationPayment = approvalApplicationPaymentMap.get(approvalApplication.getId()); if (oaApprovalApplicationPayment!=null){ // todo 当前阶段没有项目id 后续完善 approvalTodoVO.setProjectName("项目名称"); approvalTodoVO.setAmount(oaApprovalApplicationPayment.getApplyAmount()); approvalTodoVO.setDescription(approvalApplication.getApplicationReason()); @@ -359,6 +361,7 @@ } } List<ApprovalFlowNodeListVO> flowNodeList = getFlowNodeList(sysUser, approvalFlowAudits,approvalFlowNodes); res.setContractAmount(approvalApplicationPayment.getContractAmount()); res.setApprovalFlowNodeListVOS(flowNodeList); res.setApprovalApplicationId(approvalApplications.getId()); res.setApplicationReason(approvalApplications.getApplicationReason()); @@ -939,8 +942,8 @@ public R<ContactDetailVO> detailContact(@ApiParam("申请单d")Integer id) { OaApprovalApplications approvalApplications = approvalApplicationsService.getById(id); OaApprovalApplicationMoney approvalApplicationMoney = approvalApplicationMoneyService.lambdaQuery() .eq(OaApprovalApplicationMoney::getApprovalApplicationId, approvalApplications.getId()) OaApprovalApplicationContact approvalApplicationContact = approvalApplicationContactService.lambdaQuery() .eq(OaApprovalApplicationContact::getApprovalApplicationId, approvalApplications.getId()) .last("limit 1").one(); List<OaApprovalFlowAudit> approvalFlowAudits = approvalFlowAuditService.lambdaQuery().eq(OaApprovalFlowAudit::getApprovalApplicationId, approvalApplications.getId()) @@ -966,7 +969,7 @@ res.setApprovalApplicationId(approvalApplications.getId()); res.setApplicationReason(approvalApplications.getApplicationReason()); res.setAttachmentUrl(approvalApplications.getAttachmentUrl()); res.setDescription(approvalApplicationMoney.getDescription()); res.setDescription(approvalApplicationContact.getDescription()); return R.ok(res); } @ApiOperation(value = "广告制作审批详情") ruoyi-system/src/main/java/com/ruoyi/system/mapper/AssetTypeMapper.java
@@ -3,7 +3,9 @@ import com.ruoyi.common.basic.PageInfo; import com.ruoyi.system.model.AssetType; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.ruoyi.system.query.AssetStatisticsListDetailQuery; import com.ruoyi.system.query.AssetStatisticsListQuery; import com.ruoyi.system.vo.AssetStatisticsDetailVO; import com.ruoyi.system.vo.AssetStatisticsVO; import org.apache.ibatis.annotations.Param; @@ -22,4 +24,7 @@ List<AssetStatisticsVO> pageList(@Param("query")AssetStatisticsListQuery query, @Param("pageInfo")PageInfo<AssetStatisticsVO> pageInfo); List<AssetStatisticsVO> pageListNoLimit(@Param("query")AssetStatisticsListQuery query); List<AssetStatisticsDetailVO> pageListDetail(@Param("query")AssetStatisticsListDetailQuery query, @Param("pageInfo")PageInfo<AssetStatisticsDetailVO> pageInfo); } ruoyi-system/src/main/java/com/ruoyi/system/query/AssetInventoryListQuery.java
New file @@ -0,0 +1,28 @@ package com.ruoyi.system.query; import com.ruoyi.common.core.domain.BasePage; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.time.LocalDateTime; import java.util.List; @Data @ApiModel(value = "资产出入库分页列表query") public class AssetInventoryListQuery extends BasePage { @ApiModelProperty(value = "部门id 前端忽略") private Integer deptId; @ApiModelProperty(value = "年月 yy-MM") private String date; @ApiModelProperty(value = "资产名称或编号") private String nameOrCode; @ApiModelProperty(value = "资产分类id") private Integer assetTypeId; @ApiModelProperty(value = "资产分类ids 前端忽略") private List<Integer> assetTypeIds; @ApiModelProperty(value = "开始时间前端忽略") private LocalDateTime dateStart; @ApiModelProperty(value = "结束时间前端忽略") private LocalDateTime dateEnd; } ruoyi-system/src/main/java/com/ruoyi/system/query/AssetStatisticsListDetailQuery.java
@@ -13,10 +13,12 @@ @ApiModelProperty(value = "资产名称或编号") private String nameOrCode; @ApiModelProperty(value = "资产类别") private String category; @ApiModelProperty(value = "资产子类id") private Integer assetTypeIdSecond; @ApiModelProperty(value = "资产状态") private String assetStatus; @ApiModelProperty(value = "资产ids 前端忽略") private List<Integer> assetMainIds; @ApiModelProperty(value = "部门id 前端忽略") private Integer deptId; } ruoyi-system/src/main/java/com/ruoyi/system/query/AssetStatisticsListQuery.java
@@ -17,4 +17,6 @@ private Integer assetTypeId; @ApiModelProperty(value = "资产分类ids 前端忽略") private List<Integer> assetTypeIds; @ApiModelProperty(value = "部门id 前端忽略") private Integer deptId; } ruoyi-system/src/main/java/com/ruoyi/system/service/AssetTypeService.java
@@ -4,7 +4,9 @@ import com.ruoyi.common.basic.PageInfo; import com.ruoyi.system.dto.asset.AssetTypeDTO; import com.ruoyi.system.model.AssetType; import com.ruoyi.system.query.AssetStatisticsListDetailQuery; import com.ruoyi.system.query.AssetStatisticsListQuery; import com.ruoyi.system.vo.AssetStatisticsDetailVO; import com.ruoyi.system.vo.AssetStatisticsVO; import com.ruoyi.system.vo.asset.AssetTypeTreeVO; @@ -58,4 +60,6 @@ String getAssetCodePrefix(Integer assetTypeId); PageInfo<AssetStatisticsVO> pageList(AssetStatisticsListQuery query); PageInfo<AssetStatisticsDetailVO> pageListDetail(AssetStatisticsListDetailQuery query); } ruoyi-system/src/main/java/com/ruoyi/system/service/TDeptService.java
@@ -20,4 +20,6 @@ List<TDept> selectDeptTreeList(); List<Integer> getAllSubDeptIds(String deptId); } ruoyi-system/src/main/java/com/ruoyi/system/service/impl/AssetTypeServiceImpl.java
@@ -13,9 +13,11 @@ import com.ruoyi.system.mapper.AssetTypeMapper; import com.ruoyi.system.model.AssetMain; import com.ruoyi.system.model.AssetType; import com.ruoyi.system.query.AssetStatisticsListDetailQuery; import com.ruoyi.system.query.AssetStatisticsListQuery; import com.ruoyi.system.service.AssetMainService; import com.ruoyi.system.service.AssetTypeService; import com.ruoyi.system.vo.AssetStatisticsDetailVO; import com.ruoyi.system.vo.AssetStatisticsVO; import com.ruoyi.system.vo.asset.AssetTypeTreeVO; import com.ruoyi.system.vo.system.NotificationVO; @@ -48,6 +50,7 @@ public class AssetTypeServiceImpl extends ServiceImpl<AssetTypeMapper, AssetType> implements AssetTypeService { private final AssetMainService assetMainService; private final AssetTypeService assetTypeService; @Override public List<AssetTypeTreeVO> getAssetTypeTree() { @@ -319,7 +322,9 @@ if (assetTypeIds.isEmpty()){ return new PageInfo<>(); } Map<Integer, List<AssetMain>> assetMainMap = assetMainService.lambdaQuery().in(AssetMain::getAssetTypeId, assetTypeIds).list() Map<Integer, List<AssetMain>> assetMainMap = assetMainService.lambdaQuery() .eq(AssetMain::getOwnershipDeptId, query.getDeptId()) .in(AssetMain::getAssetTypeId, assetTypeIds).list() .stream() .collect(Collectors.groupingBy(AssetMain::getAssetTypeId)); for (AssetStatisticsVO asset : list) { @@ -357,4 +362,38 @@ return pageInfo; } @Override public PageInfo<AssetStatisticsDetailVO> pageListDetail(AssetStatisticsListDetailQuery query) { String assetTypeName = null; AssetType assetType = assetTypeService.getById(query.getAssetTypeIdSecond()); if (assetType!=null){ AssetType assetTypeParent = assetTypeService.getById(assetType.getParentId()); if (assetTypeParent!=null){ assetTypeName = assetTypeParent.getTypeName()+">"+assetType.getTypeName(); } } if (StringUtils.hasLength(query.getNameOrCode())){ // 查询出资产名称或者资产编号符合条件的code List<Integer> assetTypeIds = assetMainService.lambdaQuery().like(AssetMain::getAssetName, query.getNameOrCode()) .or() .like(AssetMain::getAssetCode, query.getNameOrCode()) .list() .stream() .map(AssetMain::getAssetTypeId) .collect(Collectors.toList()); query.setAssetMainIds(assetTypeIds); if (assetTypeIds.isEmpty()){ return new PageInfo<>(); } } PageInfo<AssetStatisticsDetailVO> pageInfo = new PageInfo<>(query.getPageNum(), query.getPageSize()); List<AssetStatisticsDetailVO> list = this.baseMapper.pageListDetail(query,pageInfo); for (AssetStatisticsDetailVO assetStatisticsDetailVO : list) { assetStatisticsDetailVO.setAssetTypeName(assetTypeName); } pageInfo.setRecords(list); return pageInfo; } } ruoyi-system/src/main/java/com/ruoyi/system/service/impl/TDeptServiceImpl.java
@@ -1,5 +1,6 @@ package com.ruoyi.system.service.impl; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.ruoyi.common.basic.PageInfo; import com.ruoyi.common.core.domain.entity.TDept; @@ -7,6 +8,7 @@ import com.ruoyi.system.service.TDeptService; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.List; import java.util.stream.Collectors; @@ -32,6 +34,23 @@ return buildDeptTree(depts); } @Override public List<Integer> getAllSubDeptIds(String deptId) { List<Integer> allSubIds = new ArrayList<>(); allSubIds.add(Integer.valueOf(deptId)); getSubDeptIdsRecursive(Integer.valueOf(deptId), allSubIds); return allSubIds.stream().distinct().collect(Collectors.toList()); } private void getSubDeptIdsRecursive(Integer parentId, List<Integer> allSubIds) { // 查询直接下级 List<Integer> directSubIds = this.baseMapper.selectList(new LambdaQueryWrapper<TDept>().eq(TDept::getParentId, parentId)).stream() .map(TDept::getId).collect(Collectors.toList()); for (Integer subId : directSubIds) { allSubIds.add(subId); // 递归查询下级的下级 getSubDeptIdsRecursive(subId, allSubIds); } } /** * 构建部门树结构 * @param depts 部门列表 ruoyi-system/src/main/java/com/ruoyi/system/vo/ApprovalTodoVO.java
@@ -28,6 +28,8 @@ private Double duration; @ApiModelProperty(value = "申请人名称") private String applyUserName; @ApiModelProperty(value = "事由/原因/说明") private String applicationReason; @ApiModelProperty(value = "申请人部门名称") private String applyDeptName; @ApiModelProperty(value = "申请单号") @@ -46,7 +48,7 @@ @ApiModelProperty(value = "入库类型 0-正常入库(资产入库)") private Integer storageType; @ApiModelProperty(value = "领用/借用/归还日期（物品借用/领用归还）") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8") private LocalDate operateTime; @ApiModelProperty(value = "处置方式 0-报废，1-报损，2-捐赠(资产处置)") @@ -61,6 +63,8 @@ private String description; @ApiModelProperty(value = "内容（广告制作）") private String content; @ApiModelProperty(value = "项目名称") private String projectName; @ApiModelProperty(value = "审批状态 0-草稿，1-待审批，2-审批通过，3-审批拒绝 ，4-已撤回") private Integer approvalStatus;
New file @@ -0,0 +1,34 @@ package com.ruoyi.system.vo; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; import java.math.BigDecimal; @Data @ApiModel(value = "资产出入库列表分页返回VO") public class AssetInventoryVO implements Serializable { @ApiModelProperty(value = "资产大类名称") private String assetTypeNameFirst; @ApiModelProperty(value = "资产大类id") private Integer assetTypeIdFirst; @ApiModelProperty(value = "资产子类名称") private String assetTypeNameSecond; @ApiModelProperty(value = "资产子类id") private Integer assetTypeIdSecond; @ApiModelProperty(value = "本月入库") private Integer inCount; @ApiModelProperty(value = "本月入库金额") private BigDecimal inMoney; @ApiModelProperty(value = "本月出库") private Integer outCount; @ApiModelProperty(value = "本月出库金额") private BigDecimal outMoney; @ApiModelProperty(value = "当前在库") private Integer inStockCount; @ApiModelProperty(value = "合计总价") private BigDecimal inStockMoney; } ruoyi-system/src/main/java/com/ruoyi/system/vo/AssetStatisticsDetailVO.java
New file @@ -0,0 +1,58 @@ package com.ruoyi.system.vo; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; import java.math.BigDecimal; import java.time.LocalDate; @Data @ApiModel(value = "资产汇总分页返回VO") public class AssetStatisticsDetailVO implements Serializable { @ApiModelProperty(value = "资产名称") private String assetName; @ApiModelProperty(value = "规格型号") private String specificationModel; @ApiModelProperty(value = "资产类型") private String assetTypeName; @ApiModelProperty(value = "资产类别") private String category; @ApiModelProperty(value = "计量单位") private String measurementUnit; @ApiModelProperty(value = "数量") private Integer quantity; @ApiModelProperty(value = "单价") private BigDecimal unitPrice; @ApiModelProperty(value = "总价值（数量*单价）") private BigDecimal totalValue; @ApiModelProperty(value = "入账时间") private LocalDate accountingDate; @ApiModelProperty(value = "会计凭证号") private String accountingVoucherNo; @ApiModelProperty(value = "会计科目") private String accountingSubject; @ApiModelProperty(value = "入账金额") private BigDecimal accountingAmount; @ApiModelProperty(value = "入账状态") private String accountingStatus; @ApiModelProperty(value = "资产状态") private String assetStatus; @ApiModelProperty(value = "归属部门") private String ownershipDeptName; @ApiModelProperty(value = "使用人") private String userName; @ApiModelProperty(value = "使用部门/位置") private String useName; @ApiModelProperty(value = "备注") private String remarks; @ApiModelProperty(value = "位置类型 0-部门，1-仓库，2-录入地址") private Integer addressType; } ruoyi-system/src/main/java/com/ruoyi/system/vo/PaymentDetailVO.java
@@ -26,6 +26,8 @@ private String projectName; @ApiModelProperty(value = "申请金额") private BigDecimal applyAmount; @ApiModelProperty(value = "合同金额") private BigDecimal contractAmount; @ApiModelProperty(value = "是否需要签名 0-否，1-是") private Boolean signFlag; @ApiModelProperty(value = "审批流程") ruoyi-system/src/main/resources/mapper/system/AssetTypeMapper.xml
@@ -55,5 +55,35 @@ AND t1.disabled = ${@com.ruoyi.common.enums.DisabledEnum@NO.getCode()} AND t2.disabled = ${@com.ruoyi.common.enums.DisabledEnum@NO.getCode()} </select> <select id="pageListDetail" resultType="com.ruoyi.system.vo.AssetStatisticsDetailVO"> select t1.* ,t2.type_name as assetTypeName,t4.dept_name as ownershipDeptName, case when t1.use_dept_id is not null and t1.use_dept_id != '' then t3.dept_name when t1.address is not null and t1.address != '' then t1.address when t1.warehouse_name is not null and t1.warehouse_name != '' then t1.warehouse_name else null end as useName from asset_main left join asset_type t2 on t2.id = t1.asset_type_id left join t_dept t3 on t3.id = t1.use_dept_id left join t_dept t4 on t4.id = t1.ownership_dept_id where 1=1 <if test="query.assetTypeIdSecond != null"> AND t1.asset_type_id = #{query.assetTypeIdSecond} </if> <if test="query.deptId != null"> AND t1.asset_type_id = #{query.assetTypeIdSecond} </if> AND t1.disabled = ${@com.ruoyi.common.enums.DisabledEnum@NO.getCode()} <if test="query.assetMainIds != null and query.assetMainIds.size()>0"> AND t1.id IN <foreach collection="query.assetMainIds" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> order by t1.create_time desc </select> </mapper> ruoyi-system/src/main/resources/mapper/system/OaApprovalApplicationStorageMapper.xml
@@ -60,6 +60,8 @@ left join t_dept t3 on t3.id = t1.use_dept_id left join t_dept t4 on t4.id = t1.ownership_dept_id where t1.approval_application_id = #{query.id} AND t1.disabled = ${@com.ruoyi.common.enums.DisabledEnum@NO.getCode()} order by t1.create_time desc
