升鲜宝分拣系统 具体实现(一）

升鲜宝分拣系统 具体实现(一）

 

按商品分拣

     1.商品汇总（分页）

     2.客户汇总（分页）

     3.分拣一条商品

     4.撤消一条已分拣商品

     5.缺货一条订单商品记录

     6.一条订单商品记录的操作记录

     7.获取分拣的商品分类

     8.获取分拣的线路列表

     9.分拣的百分比

<?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.loveinway.pick.dao.SortingDao">

  <sql id="BaseWhere">
    WHERE i.del_flag = 0
      <if test="p.startDate != null"> AND ob.delivery_date_time >= #{p.startDate}</if>
      <if test="p.endDate != null"> AND ob.delivery_date_time <= #{p.endDate}</if>
      <if test="p.goodsNameLike != null and p.goodsNameLike != ''"> AND g.goods_name LIKE CONCAT('%', #{p.goodsNameLike}, '%')</if>
      <if test="p.skuNameLike != null and p.skuNameLike != ''"> AND s.sku_name LIKE CONCAT('%', #{p.skuNameLike}, '%')</if>
      <if test="p.categoryId != null"> AND g.category_id = #{p.categoryId}</if>
      <if test="p.lineId != null"> AND ob.line_id = #{p.lineId}</if>
      <if test="p.customerId != null"> AND ob.shop_id = #{p.customerId}</if>
      <if test="p.meterType != null"> AND u.meter_type = #{p.meterType}</if>
      <if test="p.workhouseId != null"> AND ob.workhouse_id = #{p.workhouseId}</if>
      <if test="p.brandId != null"> AND g.brand_id = #{p.brandId}</if>
      <if test="p.enabled != null"> AND ms.enabled = #{p.enabled}</if>
      <if test="p.sortState != null"> AND i.sort_state = #{p.sortState}</if>
      <if test="p.stockoutType != null"> AND i.stockout_type = #{p.stockoutType}</if>
      <if test="p.deliveryTypeId != null"> AND ob.delivery_type_id = #{p.deliveryTypeId}</if>
      <if test="p.anonymousType != null"> AND ms.anonymous_type = #{p.anonymousType}</if>
      <if test="p.pricingMode != null"> AND ms.pricing_mode = #{p.pricingMode}</if>
  </sql>

  <select id="goodsSummary" resultType="com.loveinway.pick.dto.RespGoodsSummaryRow">
    SELECT
      <choose>
        <when test="p.groupBy == 'meterType'">
          u.meter_type AS groupId, CASE u.meter_type WHEN 0 THEN '计重' WHEN 1 THEN '计件' ELSE '未知' END AS groupName
        </when>
        <when test="p.groupBy == 'line'">
          ob.line_id AS groupId, CONCAT('线路-', ob.line_id) AS groupName
        </when>
        <when test="p.groupBy == 'customer'">
          ob.shop_id AS groupId, ms.shop_name AS groupName
        </when>
        <when test="p.groupBy == 'category'">
          g.category_id AS groupId, CONCAT('分类-', g.category_id) AS groupName
        </when>
        <when test="p.groupBy == 'brand'">
          g.brand_id AS groupId, CONCAT('品牌-', g.brand_id) AS groupName
        </when>
        <when test="p.groupBy == 'deliveryType'">
          ob.delivery_type_id AS groupId, CONCAT('配送-', ob.delivery_type_id) AS groupName
        </when>
        <otherwise>
          i.sku_unit_id AS groupId, CONCAT(g.goods_name, ' / ', s.sku_name) AS groupName
        </otherwise>
      </choose>,
      SUM(i.pre_num) AS preTotal,
      SUM(i.sendout_num) AS sortedTotal,
      SUM(i.pre_num - i.sendout_num) AS waitTotal,
      COUNT(i.id) AS detailCount,
      SUM(CASE WHEN i.sort_state = 0 THEN 1 ELSE 0 END) AS waitCount,
      SUM(CASE WHEN i.sort_state = 1 THEN 1 ELSE 0 END) AS doneCount,
      CONCAT(ROUND((SUM(i.sendout_num)/NULLIF(SUM(i.pre_num),0))*100,2),'%') AS finishRate
    FROM oms_order_bill_info i
      LEFT JOIN oms_order_bill ob ON ob.order_code = i.order_code
      LEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_id
      LEFT JOIN pms_goods_sku s ON s.id = su.product_sku_id
      LEFT JOIN pms_goods g ON g.id = s.goods_id
      LEFT JOIN pms_goods_unit u ON u.id = su.unit_id
      LEFT JOIN mall_shop ms ON ms.id = ob.shop_id
    <include refid="BaseWhere"/>
    GROUP BY
      <choose>
        <when test="p.groupBy == 'meterType'"> u.meter_type </when>
        <when test="p.groupBy == 'line'"> ob.line_id </when>
        <when test="p.groupBy == 'customer'"> ob.shop_id </when>
        <when test="p.groupBy == 'category'"> g.category_id </when>
        <when test="p.groupBy == 'brand'"> g.brand_id </when>
        <when test="p.groupBy == 'deliveryType'"> ob.delivery_type_id </when>
        <otherwise> i.sku_unit_id </otherwise>
      </choose>
  </select>

  <select id="goodsDetail" resultType="com.loveinway.pick.dto.RespOrderDetail">
    SELECT i.id, i.order_code AS orderCode, ob.shop_id AS shopId, ms.shop_name AS shopName, ob.line_id AS lineId, ob.workhouse_id AS workhouseId,
           i.sku_unit_id AS skuUnitId, g.goods_code AS goodsCode, g.goods_name AS goodsName, s.sku_name AS skuName,
           u.meter_type AS meterType, u.unit_name AS unitName, i.pre_num AS preNum, i.sendout_num AS sendoutNum, i.sort_state AS sortState, i.stockout_type AS stockoutType
    FROM oms_order_bill_info i
      LEFT JOIN oms_order_bill ob ON ob.order_code = i.order_code
      LEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_id
      LEFT JOIN pms_goods_sku s ON s.id = su.product_sku_id
      LEFT JOIN pms_goods g ON g.id = s.goods_id
      LEFT JOIN pms_goods_unit u ON u.id = su.unit_id
      LEFT JOIN mall_shop ms ON ms.id = ob.shop_id
    <include refid="BaseWhere"/>
  </select>

  <select id="customerSummary" resultType="com.loveinway.pick.dto.RespGoodsSummaryRow">
    SELECT
      <choose>
        <when test="p.groupBy == 'meterType'">
          u.meter_type AS groupId, CASE u.meter_type WHEN 0 THEN '计重' WHEN 1 THEN '计件' ELSE '未知' END AS groupName
        </when>
        <when test="p.groupBy == 'line'">
          ob.line_id AS groupId, CONCAT('线路-', ob.line_id) AS groupName
        </when>
        <when test="p.groupBy == 'category'">
          g.category_id AS groupId, CONCAT('分类-', g.category_id) AS groupName
        </when>
        <when test="p.groupBy == 'brand'">
          g.brand_id AS groupId, CONCAT('品牌-', g.brand_id) AS groupName
        </when>
        <when test="p.groupBy == 'deliveryType'">
          ob.delivery_type_id AS groupId, CONCAT('配送-', ob.delivery_type_id) AS groupName
        </when>
        <otherwise>
          ob.shop_id AS groupId, ms.shop_name AS groupName
        </otherwise>
      </choose>,
      SUM(i.pre_num) AS preTotal,
      SUM(i.sendout_num) AS sortedTotal,
      SUM(i.pre_num - i.sendout_num) AS waitTotal,
      COUNT(i.id) AS detailCount,
      SUM(CASE WHEN i.sort_state = 0 THEN 1 ELSE 0 END) AS waitCount,
      SUM(CASE WHEN i.sort_state = 1 THEN 1 ELSE 0 END) AS doneCount,
      CONCAT(ROUND((SUM(i.sendout_num)/NULLIF(SUM(i.pre_num),0))*100,2),'%') AS finishRate
    FROM oms_order_bill_info i
      LEFT JOIN oms_order_bill ob ON ob.order_code = i.order_code
      LEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_id
      LEFT JOIN pms_goods_sku s ON s.id = su.product_sku_id
      LEFT JOIN pms_goods g ON g.id = s.goods_id
      LEFT JOIN pms_goods_unit u ON u.id = su.unit_id
      LEFT JOIN mall_shop ms ON ms.id = ob.shop_id
    <include refid="BaseWhere"/>
    GROUP BY
      <choose>
        <when test="p.groupBy == 'meterType'"> u.meter_type </when>
        <when test="p.groupBy == 'line'"> ob.line_id </when>
        <when test="p.groupBy == 'category'"> g.category_id </when>
        <when test="p.groupBy == 'brand'"> g.brand_id </when>
        <when test="p.groupBy == 'deliveryType'"> ob.delivery_type_id </when>
        <otherwise> ob.shop_id </otherwise>
      </choose>
  </select>

  <select id="customerDetail" resultType="com.loveinway.pick.dto.RespOrderDetail">
    SELECT i.id, i.order_code AS orderCode, ob.shop_id AS shopId, ms.shop_name AS shopName, ob.line_id AS lineId, ob.workhouse_id AS workhouseId,
           i.sku_unit_id AS skuUnitId, g.goods_code AS goodsCode, g.goods_name AS goodsName, s.sku_name AS skuName,
           u.meter_type AS meterType, u.unit_name AS unitName, i.pre_num AS preNum, i.sendout_num AS sendoutNum, i.sort_state AS sortState, i.stockout_type AS stockoutType
    FROM oms_order_bill_info i
      LEFT JOIN oms_order_bill ob ON ob.order_code = i.order_code
      LEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_id
      LEFT JOIN pms_goods_sku s ON s.id = su.product_sku_id
      LEFT JOIN pms_goods g ON g.id = s.goods_id
      LEFT JOIN pms_goods_unit u ON u.id = su.unit_id
      LEFT JOIN mall_shop ms ON ms.id = ob.shop_id
    <include refid="BaseWhere"/>
  </select>

  <update id="addSendout">
    UPDATE oms_order_bill_info
    SET sendout_num = sendout_num + #{num},
        sort_state = CASE WHEN sendout_num + #{num} >= pre_num THEN 1 ELSE sort_state END
    WHERE id = #{id} AND del_flag = 0
  </update>

  <update id="revokeSendout">
    UPDATE oms_order_bill_info
    SET sendout_num = GREATEST(0, sendout_num - #{num}),
        sort_state = CASE WHEN GREATEST(0, sendout_num - #{num}) < pre_num THEN 0 ELSE sort_state END
    WHERE id = #{id} AND del_flag = 0
  </update>

  <update id="setStockout">
    UPDATE oms_order_bill_info SET stockout_type = 1 WHERE id = #{id} AND del_flag = 0
  </update>

  <insert id="insertLog">
    INSERT INTO pms_sorting_log(id, order_info_id, sku_unit_id, sendout_num, oper_type, enabled, del_flag, sort_code, creator, create_date, remark)
    VALUES(#{id}, #{orderInfoId}, #{skuUnitId}, #{num}, #{type}, 1, 0, 1, #{creator}, #{createDate}, #{remark})
  </insert>

  <select id="selectLogs" resultType="com.loveinway.pick.entity.PmsSortingLog">
    SELECT id, order_info_id AS orderInfoId, sku_unit_id AS skuUnitId, sendout_num AS sendoutNum, oper_type AS operType,
           enabled, del_flag AS delFlag, sort_code AS sortCode, creator, create_date AS createDate, updater, update_date AS updateDate, remark
    FROM pms_sorting_log WHERE del_flag = 0 ORDER BY id DESC
  </select>
</mapper>