1.花材统计报表

陶杰

2024-12-18 84098781635f1babaa6f4507200afa10283f4a58

1.花材统计报表

 src/main/java/com/mzl/flower/dto/request/statisticAnalysis/FlowerMaterialDTO.java

@@ -1,7 +1,32 @@
package com.mzl.flower.dto.request.statisticAnalysis;

import com.mzl.flower.base.annotation.DictTrans;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class FlowerMaterialDTO {

    @ApiModelProperty(value = "供应商ID")
    private Long supplierId;
    @ApiModelProperty(value = "供应商名称")
    private String supplierName;
    @ApiModelProperty(value = "注册手机号")
    private String loginName;
    @ApiModelProperty(value = "联系方式")
    private String contactTel;
    @ApiModelProperty(value = "所属集货站")
    private String stationId;
    @ApiModelProperty(value = "品类")
    private String flowerCategory;
    @ApiModelProperty(value = "状态")
    private String flowerStatus;
    @ApiModelProperty(value = "等级-FLOWER_LEVEL")
    private String flowerLevel;
    @ApiModelProperty(value = "父品类",hidden = true)
    private String parentflowerCategory;
    @ApiModelProperty(value = "排序字段")
    private String orderField;
    @ApiModelProperty(value = "排序类型，ASC 升序，DESC 降序")
    private String orderType;
}

 src/main/java/com/mzl/flower/dto/response/statisticAnalysis/FlowerMaterialStaticVO.java

@@ -1,7 +1,20 @@
package com.mzl.flower.dto.response.statisticAnalysis;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
@ApiModel("花材列表统计")
public class FlowerMaterialStaticVO {

    @ApiModelProperty(value = "供应商总数")
    private String supplierAmount;

    @ApiModelProperty(value = "商品总数")
    private String flowerAmount;

    @ApiModelProperty(value = "商品总库存")
    private String flowerStockAmount;

}

 src/main/java/com/mzl/flower/dto/response/statisticAnalysis/FlowerMaterialVO.java

@@ -1,7 +1,46 @@
package com.mzl.flower.dto.response.statisticAnalysis;

import com.mzl.flower.base.AbstractTransDTO;
import com.mzl.flower.base.annotation.DictTrans;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class FlowerMaterialVO {
@ApiModel("花材列表")
public class FlowerMaterialVO extends AbstractTransDTO {

    @ApiModelProperty(value = "供应商ID")
    private String supplierId;
    @ApiModelProperty(value = "供应商名称")
    private String supplierName;
    @ApiModelProperty(value = "注册手机号")
    private String loginName;
    @ApiModelProperty(value = "联系方式")
    private String contactTel;
    @ApiModelProperty(value = "所属集货站")
    private String stationName;
    @ApiModelProperty(value = "品类")
    private String categoryName;
    @ApiModelProperty(value = "商品名称")
    private String flowerName;
    @ApiModelProperty(value = "等级-FLOWER_LEVEL")
    @DictTrans(target = "flowerLevelStr", codeType = "FLOWER_LEVEL")
    private String flowerLevel;
    @ApiModelProperty(value = "等级")
    private String flowerLevelStr;
    @ApiModelProperty(value = "商品规格")
    private String flowerUnit;
    @ApiModelProperty(value = "价格")
    private String flowerPrice;
    @ApiModelProperty(value = "库存")
    private String flowerStock;
    @ApiModelProperty(value = "状态")
    @DictTrans(target = "flowerStatusStr", codeType = "FLOWER_STATUS")
    private String flowerStatus;
    @ApiModelProperty(value = "状态")
    private String flowerStatusStr;
    @ApiModelProperty(value = "花ID")
    private String flowerId;

}

 src/main/java/com/mzl/flower/mapper/statisticsAnalysis/FlowerMaterialMapper.java

对比新文件
@@ -0,0 +1,17 @@
package com.mzl.flower.mapper.statisticsAnalysis;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mzl.flower.dto.request.statisticAnalysis.FlowerMaterialDTO;
import com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialStaticVO;
import com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface FlowerMaterialMapper {
    Page<FlowerMaterialVO> getFlowerMaterialPage(Page page,@Param("dto") FlowerMaterialDTO dto);

    List<FlowerMaterialVO> getFlowerMaterialList(@Param("dto") FlowerMaterialDTO dto);

    List<FlowerMaterialStaticVO> getFlowerMaterialStatis(@Param("dto") FlowerMaterialDTO dto);
}

 src/main/java/com/mzl/flower/service/impl/statisticsAnalysis/StatisticAnalysisServiceImpl.java

@@ -1,30 +1,93 @@
package com.mzl.flower.service.impl.statisticsAnalysis;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mzl.flower.base.cache.DictCacheClient;
import com.mzl.flower.dto.request.statisticAnalysis.FlowerMaterialDTO;
import com.mzl.flower.dto.response.report.OrderReportResultVO;
import com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialStaticVO;
import com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialVO;
import com.mzl.flower.mapper.statisticsAnalysis.FlowerMaterialMapper;
import com.mzl.flower.service.BaseService;
import com.mzl.flower.service.statisticsAnalysis.StatisticAnalysisService;
import com.mzl.flower.utils.ExcelExportUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class StatisticAnalysisServiceImpl implements StatisticAnalysisService {
public class StatisticAnalysisServiceImpl extends BaseService implements StatisticAnalysisService {

    @Autowired
    private DictCacheClient dictCacheClient;

    @Autowired
    private FlowerMaterialMapper flowerMaterialMapper;

    @Override
    public Page<FlowerMaterialVO> getFlowerMaterialPage(Page page, FlowerMaterialDTO dto) {
        return null;

        Page<FlowerMaterialVO> result=flowerMaterialMapper.getFlowerMaterialPage(page, dto);

        return result;
    }

    @Override
    public FlowerMaterialStaticVO getFlowerMaterialStatis(FlowerMaterialDTO dto) {
        return null;
       List<FlowerMaterialStaticVO> list=  flowerMaterialMapper.getFlowerMaterialStatis(dto);
       if(!CollectionUtils.isEmpty(list)){
           return list.get(0);
       }
       return null;
    }

    @Override
    public void exportFlowerMaterialSales(HttpServletResponse response, FlowerMaterialDTO dto) {
        List<FlowerMaterialVO> list=flowerMaterialMapper.getFlowerMaterialList(dto);

        String[] rowsName = new String[]{
                "序号"
                ,"供应商ID", "供应商名称","注册手机号","联系方式","所属集货站","品类","商品名称","等级","商品规格","价格","库存","状态",};
        List<Object[]> dataList = new ArrayList<>();
        int sn = 1;
        for (FlowerMaterialVO o : list) {
            Object[] objs = new Object[rowsName.length];
            int a = 0;
            objs[a++] = sn; // 序号
            objs[a++] = o.getSupplierId(); // 供应商ID
            objs[a++] = o.getSupplierName(); // 供应商名称
            objs[a++] = o.getLoginName(); // 注册手机号
            objs[a++] = o.getContactTel(); // 联系方式"
            objs[a++] = o.getStationName(); // 所属集货站
            objs[a++] = o.getCategoryName(); // 品类
            objs[a++] = o.getFlowerName(); //商品名称
//            String dictValue = dictCacheClient.getDict("FLOWER_LEVEL", o.getFlowerLevel());
//            objs[a++] = dictValue; //等级
            objs[a++] = o.getFlowerLevelStr(); //等级
            objs[a++] = o.getFlowerUnit(); //商品规格
            objs[a++] = o.getFlowerPrice();//价格
            objs[a++] = o.getFlowerStock();//库存
//            String dictValueStatus = dictCacheClient.getDict("FLOWER_STATUS", o.getFlowerStatus());
            //            objs[a++] =dictValueStatus;
            objs[a++] = o.getFlowerStatusStr(); // 状态

            dataList.add(objs);
            sn++;
        }

        ExcelExportUtil excelExportUtil = new ExcelExportUtil("花材统计报表", rowsName, dataList, response);
        try {
            response.addHeader("filename", URLEncoder.encode("花材统计报表.xls", "UTF-8"));
            response.addHeader("Access-Control-Expose-Headers", "filename");
            excelExportUtil.export();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }
}

 src/main/resources/mapper/statisticsAnalysis/FlowerMaterialMapper.xml

对比新文件
@@ -0,0 +1,136 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mzl.flower.mapper.statisticsAnalysis.FlowerMaterialMapper">


    <select id="getFlowerMaterialPage"
            resultType="com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialVO">
        <include refid="flowerMaterialSql"></include>
    </select>
    <select id="getFlowerMaterialList"
            resultType="com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialVO">
        <include refid="flowerMaterialSqlWidthDict"></include>
    </select>
    <select id="getFlowerMaterialStatis"
            resultType="com.mzl.flower.dto.response.statisticAnalysis.FlowerMaterialStaticVO">
        select
            count(distinct si.id) as supplier_amount,
            count(f.id) as flower_amount,
            sum(f.stock) as flower_stock_amount
        from t_flower f
        left join t_supplier_info si
        on f.supplier_id=si.id
        left join t_user u
        on si.user_id =u.id
        left join t_station s
        on si.station_id=s.id
        left join t_flower_category fc
        on f.category=fc.id
        <include refid="flowerMaterialSql_where"></include>
    </select>

    <sql id="flowerMaterialSql">
        select
            si.id as supplier_id
             ,si.name as supplier_name
             ,u.login_name
             ,si.contact_tel
             ,s.name as station_name
             ,fc.name as category_name
             ,f.name as flower_name
             ,f.`level` as flower_level
             ,f.unit as flower_unit
             ,f.price as flower_price
             ,f.stock flower_stock
             ,f.status as flower_status
        from t_flower f
        left join t_supplier_info si
        on f.supplier_id=si.id
        left join t_user u
        on si.user_id =u.id
        left join t_station s
        on si.station_id=s.id
        left join t_flower_category fc
        on f.category=fc.id

--           and u.deleted=0 and s.deleted=0 and fc.deleted=0
        <include refid="flowerMaterialSql_where"></include>
        <if test="dto.orderField !=null and dto.orderField !=''">
            order by ${dto.orderField} ${dto.orderType}
        </if>
    </sql>

    <sql id="flowerMaterialSqlWidthDict">
        select
            si.id as supplier_id
             ,si.name as supplier_name
             ,u.login_name
             ,si.contact_tel
             ,s.name as station_name
             ,fc.name as category_name
             ,f.name as flower_name
             ,f.`level` as flower_level
             ,f.unit as flower_unit
             ,f.price as flower_price
             ,f.stock flower_stock
             ,f.status as flower_status
             ,code.label as flower_status_str
             , code2.label as flower_level_str
        from t_flower f
        left join t_supplier_info si
        on f.supplier_id=si.id
        left join t_user u
        on si.user_id =u.id
        left join t_station s
        on si.station_id=s.id
        left join t_flower_category fc
        on f.category=fc.id
        LEFT JOIN (SELECT ct.label, ct.value from t_code_value ct where type_code = 'FLOWER_STATUS') code ON f.status = code.value
        LEFT JOIN (SELECT ct.label, ct.value from t_code_value ct where type_code = 'FLOWER_LEVEL') code2 ON f.`level` = code2.value
        <include refid="flowerMaterialSql_where"></include>
        <if test="dto.orderField !=null and dto.orderField !=''">
            order by ${dto.orderField} ${dto.orderType}
        </if>
    </sql>

    <sql id="flowerMaterialSql_where">
        where f.deleted=0  and si.deleted =0
        <if test="dto.supplierId != null">
            and f.supplier_id = #{dto.supplierId}
        </if>
        <if test="dto.supplierName !=null and dto.supplierName !=''">
            AND  si.name LIKE concat('%', #{dto.supplierName},'%')
        </if>
        <if test="dto.loginName !=null and dto.loginName !=''">
            AND  u.login_name LIKE concat('%', #{dto.loginName},'%')
        </if>

        <if test="dto.contactTel !=null and dto.contactTel !=''">
            AND  si.contact_tel LIKE concat('%', #{dto.contactTel},'%')
        </if>

        <if test="dto.stationId !=null and dto.stationId !=''">
            AND  si.station_id = #{dto.stationId}
        </if>

        <if test="dto.flowerCategory !=null and dto.flowerCategory !=''">
            AND ( f.category = #{dto.flowerCategory} or f.category in (
                select id from t_flower_category where parent_id = #{dto.flowerCategory}
            ))

        </if>
        <if test="dto.flowerStatus !=null and dto.flowerStatus !=''">
            AND  f.status = #{dto.flowerStatus}
        </if>
        <if test="dto.flowerLevel !=null and dto.flowerLevel !=''">
            AND  f.`level` = #{dto.flowerLevel}
        </if>




    </sql>



</mapper>