From 5787f5b5b547c04a52ac814ee6215503114ab617 Mon Sep 17 00:00:00 2001 From: Helius <wangdoubleone@gmail.com> Date: Fri, 22 Jan 2021 17:41:26 +0800 Subject: [PATCH] modify --- zq-erp/src/main/java/com/matrix/system/hiveErp/dao/TjVipSumDao.java | 5 + zq-erp/src/main/java/com/matrix/system/app/action/ApiStatisticsAction.java | 50 ++++++++- zq-erp/src/main/resources/mybatis/mapper/hive/TjVipSumDao.xml | 189 +++++++++++++++++++++++++++++++++++++ zq-erp/src/main/java/com/matrix/system/app/dto/LabelDto.java | 2 4 files changed, 238 insertions(+), 8 deletions(-) diff --git a/zq-erp/src/main/java/com/matrix/system/app/action/ApiStatisticsAction.java b/zq-erp/src/main/java/com/matrix/system/app/action/ApiStatisticsAction.java index 6f16768..08a4bcb 100644 --- a/zq-erp/src/main/java/com/matrix/system/app/action/ApiStatisticsAction.java +++ b/zq-erp/src/main/java/com/matrix/system/app/action/ApiStatisticsAction.java @@ -134,6 +134,16 @@ return tjVipSumDao.selectRepayment(timeSpaceParam, shopId, null); case "10": return tjVipSumDao.selectPerCustomCnt(timeSpaceParam, shopId, null); + case "11": + return tjVipSumDao.selectCustomGoodsCnt(timeSpaceParam, shopId, null); + case "12": + return tjVipSumDao.selectPerCustomPrice(timeSpaceParam, shopId, null); + case "13": + return tjVipSumDao.selectCost(timeSpaceParam, shopId, null); + case "14": + return tjVipSumDao.selectGrossProfit(timeSpaceParam, shopId, null); + case "15": + return tjVipSumDao.selectGrossProfitRate(timeSpaceParam, shopId, null); case "16" : return tjVipSumDao.selectHisConsume(timeSpaceParam, shopId, null); case "17" : @@ -162,15 +172,39 @@ case "3" : return tjVipSumDao.selectCardUse(timeSpaceParam, null, companyId); case "4" : - return tjVipSumDao.selectHisConsume(timeSpaceParam, null, companyId); - case "5" : - return tjVipSumDao.selectFreeConsume(timeSpaceParam, null, companyId); - case "6" : - return tjVipSumDao.selectCashRefund(timeSpaceParam, null, companyId); - case "7" : - return tjVipSumDao.selectCardRefund(timeSpaceParam, null, companyId); - case "8" : return tjVipSumDao.selectArrears(timeSpaceParam, null, companyId); + case "5" : + return tjVipSumDao.selectCashRefund(timeSpaceParam, null, companyId); + case "6" : + return tjVipSumDao.selectCardRefund(timeSpaceParam, null, companyId); + case "7": + return tjVipSumDao.selectProductAchieve(timeSpaceParam, null, companyId); + case "8": + return tjVipSumDao.selectCardAchieve(timeSpaceParam, null, companyId); + case "9": + return tjVipSumDao.selectRepayment(timeSpaceParam, null, companyId); + case "10": + return tjVipSumDao.selectPerCustomCnt(timeSpaceParam, null, companyId); + case "11": + return tjVipSumDao.selectCustomGoodsCnt(timeSpaceParam, null, companyId); + case "12": + return tjVipSumDao.selectPerCustomPrice(timeSpaceParam, null, companyId); + case "13": + return tjVipSumDao.selectCost(timeSpaceParam, null, companyId); + case "14": + return tjVipSumDao.selectCost(timeSpaceParam, null, companyId); + case "15": + return tjVipSumDao.selectCost(timeSpaceParam, null, companyId); + case "16" : + return tjVipSumDao.selectHisConsume(timeSpaceParam, null, companyId); + case "17" : + return tjVipSumDao.selectFreeConsume(timeSpaceParam, null, companyId); + case "18" : + return tjVipSumDao.selectPeopleCnt(timeSpaceParam, null, companyId); + case "19" : + return tjVipSumDao.selectProjConsumeCnt(timeSpaceParam, null, companyId); + case "20" : + return tjVipSumDao.selectTimeLength(timeSpaceParam, null, companyId); default : return tjVipSumDao.selectBusinessInCome(timeSpaceParam, null, companyId); } diff --git a/zq-erp/src/main/java/com/matrix/system/app/dto/LabelDto.java b/zq-erp/src/main/java/com/matrix/system/app/dto/LabelDto.java index 635a1ea..36b64f7 100644 --- a/zq-erp/src/main/java/com/matrix/system/app/dto/LabelDto.java +++ b/zq-erp/src/main/java/com/matrix/system/app/dto/LabelDto.java @@ -2,6 +2,7 @@ import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; +import org.hibernate.validator.constraints.Length; /** * @author wzy @@ -10,6 +11,7 @@ @ApiModel(value = "LabelDto", description = "添加标签接收参数接收类") public class LabelDto { + @Length(max = 10, message = "不能超过10") @ApiModelProperty(value = "标签内容", example = "123") private String label; diff --git a/zq-erp/src/main/java/com/matrix/system/hiveErp/dao/TjVipSumDao.java b/zq-erp/src/main/java/com/matrix/system/hiveErp/dao/TjVipSumDao.java index c197ba8..4b580f6 100644 --- a/zq-erp/src/main/java/com/matrix/system/hiveErp/dao/TjVipSumDao.java +++ b/zq-erp/src/main/java/com/matrix/system/hiveErp/dao/TjVipSumDao.java @@ -52,14 +52,19 @@ Map<String, BigDecimal> selectPerCustomCnt(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 客品数 + Map<String, BigDecimal> selectCustomGoodsCnt(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 客单价 + Map<String, BigDecimal> selectPerCustomPrice(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 成本 + Map<String, BigDecimal> selectCost(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 毛利 + Map<String, BigDecimal> selectGrossProfit(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 毛利率 + Map<String, BigDecimal> selectGrossProfitRate(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); // 本金消耗 Map<String, BigDecimal> selectHisConsume(@Param("list") List<StatisticsTimeDaoParam> timeSpaceParam, @Param("shopId") Long shopId, @Param("companyId") Long companyId); diff --git a/zq-erp/src/main/resources/mybatis/mapper/hive/TjVipSumDao.xml b/zq-erp/src/main/resources/mybatis/mapper/hive/TjVipSumDao.xml index 119a641..e0f903f 100644 --- a/zq-erp/src/main/resources/mybatis/mapper/hive/TjVipSumDao.xml +++ b/zq-erp/src/main/resources/mybatis/mapper/hive/TjVipSumDao.xml @@ -297,6 +297,195 @@ from area where id=1 </select> + <select id="selectCustomGoodsCnt" resultType="java.util.TreeMap"> + select + <foreach collection="list" index="index" item="item" separator="," > + ( + select case a.perCustomCnt when 0 then 0 else a.totalSaleCnt/a.perCustomCnt end from ( + select + (select IFNULL(count(1),0) from sys_order + where STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) perCustomCnt, + (select IFNULL(sum(b.COUNT),0) from sys_order a inner join sys_order_item b on a.ID=b.ORDER_ID + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) totalSaleCnt from dual + ) a + ) as t${index} + </foreach> + from area where id=1 + </select> + + <select id="selectPerCustomPrice" resultType="java.util.TreeMap"> + select + <foreach collection="list" index="index" item="item" separator="," > + ( + select case a.perCustomCnt when 0 then 0 else a.totalPay/a.perCustomCnt end from ( + select + (select IFNULL(count(1),0) from sys_order + where STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) perCustomCnt, + (select IFNULL(sum(amount),0) from sys_order_flow + where <![CDATA[ create_time > #{item.beginTime} and create_time < #{item.endTime}]]> and pay_method!='欠款' + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) totalPay from dual + ) a + ) as t${index} + </foreach> + from area where id=1 + </select> + + <select id="selectCost" resultType="java.util.TreeMap"> + select + <foreach collection="list" index="index" item="item" separator="," > + ( + select a.goodsCost+a.otherCost end from ( + select + (select IFNULL(sum(e.count * d.GOODS_PRICE),0) from sys_order a + inner join sys_out_store b on a.id=b.ORDER_ID + inner join sys_out_store_item c on b.id=c.OUT_STORE_ID + inner join sys_store_info d on c.store_id = d.ID + inner join sys_order_item e on a.ID=e.ORDER_ID + inner join shopping_goods f on e.goods_id=f.id and f.good_type='家居产品' + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) goodsCost, + (select IFNULL(sum(b.COUNT * IFNULL(c.price, 0)),0) from sys_order a + inner join sys_order_item b on a.ID=b.ORDER_ID + inner join shopping_goods c on c.good_type != '家居产品' and b.goods_id=c.id + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) otherCost from dual + ) a + ) as t${index} + </foreach> + from area where id=1 + </select> + + <select id="selectGrossProfit" resultType="java.util.TreeMap"> + select + <foreach collection="list" index="index" item="item" separator="," > + ( + select a.totalPay - a.goodsCost - a.otherCost end from ( + select + (select IFNULL(sum(e.count * d.GOODS_PRICE),0) from sys_order a + inner join sys_out_store b on a.id=b.ORDER_ID + inner join sys_out_store_item c on b.id=c.OUT_STORE_ID + inner join sys_store_info d on c.store_id = d.ID + inner join sys_order_item e on a.ID=e.ORDER_ID + inner join shopping_goods f on e.goods_id=f.id and f.good_type='家居产品' + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) goodsCost, + (select IFNULL(sum(b.COUNT * IFNULL(c.price, 0)),0) from sys_order a + inner join sys_order_item b on a.ID=b.ORDER_ID + inner join shopping_goods c on c.good_type != '家居产品' and b.goods_id=c.id + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) otherCost, + (select IFNULL(sum(amount),0) from sys_order_flow + where <![CDATA[ create_time > #{item.beginTime} and create_time < #{item.endTime}]]> and pay_method!='欠款' + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) totalPay from dual + ) a + ) as t${index} + </foreach> + from area where id=1 + </select> + + <select id="selectGrossProfitRate" resultType="java.util.TreeMap"> + select + <foreach collection="list" index="index" item="item" separator="," > + ( + select case a.totalPay when 0 then 0 else (a.totalPay - a.goodsCost - a.otherCost)/a.totalPay end from ( + select + (select IFNULL(sum(e.count * d.GOODS_PRICE),0) from sys_order a + inner join sys_out_store b on a.id=b.ORDER_ID + inner join sys_out_store_item c on b.id=c.OUT_STORE_ID + inner join sys_store_info d on c.store_id = d.ID + inner join sys_order_item e on a.ID=e.ORDER_ID + inner join shopping_goods f on e.goods_id=f.id and f.good_type='家居产品' + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) goodsCost, + (select IFNULL(sum(b.COUNT * IFNULL(c.price, 0)),0) from sys_order a + inner join sys_order_item b on a.ID=b.ORDER_ID + inner join shopping_goods c on c.good_type != '家居产品' and b.goods_id=c.id + where a.STATU in ('已付款', '欠款') and <![CDATA[ ORDER_TIME > #{item.beginTime} and ORDER_TIME < #{item.endTime} ]]> + <if test="shopId != null"> + and a.shop_id=#{shopId} + </if> + <if test="companyId != null"> + and a.company_id=#{companyId} + </if> + ) otherCost, + (select IFNULL(sum(amount),0) from sys_order_flow + where <![CDATA[ create_time > #{item.beginTime} and create_time < #{item.endTime}]]> and pay_method!='欠款' + <if test="shopId != null"> + and shop_id=#{shopId} + </if> + <if test="companyId != null"> + and company_id=#{companyId} + </if> + ) totalPay from dual + ) a + ) as t${index} + </foreach> + from area where id=1 + </select> + <select id="selectCashRefund" resultType="java.util.TreeMap"> select <foreach collection="list" index="index" item="item" separator="," > -- Gitblit v1.9.1