升鲜宝分拣系统 具体实现(一）
按商品分拣
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 finishRateFROM oms_order_bill_info iLEFT JOIN oms_order_bill ob ON ob.order_code = i.order_codeLEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_idLEFT JOIN pms_goods_sku s ON s.id = su.product_sku_idLEFT JOIN pms_goods g ON g.id = s.goods_idLEFT JOIN pms_goods_unit u ON u.id = su.unit_idLEFT 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 stockoutTypeFROM oms_order_bill_info iLEFT JOIN oms_order_bill ob ON ob.order_code = i.order_codeLEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_idLEFT JOIN pms_goods_sku s ON s.id = su.product_sku_idLEFT JOIN pms_goods g ON g.id = s.goods_idLEFT JOIN pms_goods_unit u ON u.id = su.unit_idLEFT 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 finishRateFROM oms_order_bill_info iLEFT JOIN oms_order_bill ob ON ob.order_code = i.order_codeLEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_idLEFT JOIN pms_goods_sku s ON s.id = su.product_sku_idLEFT JOIN pms_goods g ON g.id = s.goods_idLEFT JOIN pms_goods_unit u ON u.id = su.unit_idLEFT 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 stockoutTypeFROM oms_order_bill_info iLEFT JOIN oms_order_bill ob ON ob.order_code = i.order_codeLEFT JOIN pms_goods_sku_unit su ON su.id = i.sku_unit_idLEFT JOIN pms_goods_sku s ON s.id = su.product_sku_idLEFT JOIN pms_goods g ON g.id = s.goods_idLEFT JOIN pms_goods_unit u ON u.id = su.unit_idLEFT JOIN mall_shop ms ON ms.id = ob.shop_id<include refid="BaseWhere"/></select><update id="addSendout">UPDATE oms_order_bill_infoSET sendout_num = sendout_num + #{num},sort_state = CASE WHEN sendout_num + #{num} >= pre_num THEN 1 ELSE sort_state ENDWHERE id = #{id} AND del_flag = 0</update><update id="revokeSendout">UPDATE oms_order_bill_infoSET sendout_num = GREATEST(0, sendout_num - #{num}),sort_state = CASE WHEN GREATEST(0, sendout_num - #{num}) < pre_num THEN 0 ELSE sort_state ENDWHERE 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, remarkFROM pms_sorting_log WHERE del_flag = 0 ORDER BY id DESC</select>
</mapper>