采购订单预警

 强哥SQL ,订单级别

-- 190614_缺货预警报表_订单级

SELECT
    TIMESTAMPDIFF( DAY, t.confirm_time, NOW( ) ) AS 预警时间,
    t.id_onl,
    t.confirm_time,
    t.org_name,
    t.org_id,
    t.consignee_name,
    t.consignee_phone,
    tg.goods_barcode,
    tg.goods_id_ofl,
    tg.goods_name,
    COUNT( tg.id ) AS goods_num 
FROM
    mabao51.trade_goods_onl_4_bi tg
    LEFT JOIN mabao51.trade_onl t ON tg.trade_onl_id = t.id 
WHERE
    tg.org_id != 4000 
    AND t.`status` BETWEEN 2 AND 8 
    AND tg.`status` = 0 
    AND tg.goods_name NOT LIKE "%测试%" 
    AND tg.goods_barcode NOT IN ( "6944639802717", "6798532102036-1" ) 
    AND tg.goods_name NOT LIKE "欢乐51大放价，门店购物好时节" 
GROUP BY
    t.id_onl,
    tg.goods_barcode 
ORDER BY
    t.confirm_time

 

商品级别SQL

-- 190614_缺货预警报表_商品级

SELECT
    tg.goods_barcode,
    tg.goods_id_ofl,
    tg.goods_name,
    COUNT( tg.id ) AS goods_num,
    (
    CASE
            
            WHEN tg.goods_id_ofl = "" THEN
            "无资料" 
            WHEN tg.goods_barcode != gb.bar_code THEN
            "条码错" ELSE "正常" 
        END 
        ) AS `status` 
    FROM
        mabao51.trade_goods_onl_4_bi tg
        LEFT JOIN arm_changsha.goods_base gb ON gb.id = tg.goods_id_ofl 
    WHERE
        tg.org_id != 4000 
        AND tg.`status` = 0 
        AND tg.goods_name NOT LIKE "%测试%" 
        AND tg.goods_barcode NOT IN ( "6944639802717", "6798532102036-1" ) 
        AND tg.goods_name NOT LIKE "欢乐51大放价，门店购物好时节" 
GROUP BY
    tg.goods_barcode

 

 

 

 

缺货我的垃圾订单查询

SELECT s.*, b.all_number AS 仓库可用库存
    , TIMESTAMPDIFF(DAY, s.confirm_time, NOW()) AS 预警时间

FROM   (
    SELECT t.id_onl, tg.status_label AS tg_status_label, t.confirm_time, t.org_name, tg.org_id
        , t.consignee_name, t.consignee_phone, tg.goods_barcode, tg.goods_id_ofl, tg.goods_name
        , COUNT(tg.goods_barcode) AS number
    FROM mabao51.trade_goods_onl_4_bi tg
        LEFT JOIN mabao51.trade_onl t ON tg.trade_onl_id = t.id
    WHERE tg.org_id != 4000
        AND t.`status` BETWEEN 2 AND 8
        AND tg.`status` BETWEEN 0 AND 0
        AND tg.goods_name NOT LIKE "%测试%"
    GROUP BY t.id_onl, tg.goods_barcode
    ) s
    LEFT JOIN (
        SELECT gb.bar_code AS bar_code, gb.`name` AS NAME, SUM(igs.number) AS all_number
        FROM arm_changsha.inv_goods_stock igs
            INNER JOIN arm_changsha.goods_base gb ON gb.id = igs.goods_id
        WHERE igs.del_flag = "0"
            AND gb.`name` NOT LIKE "测试%"
            AND igs.department_id IN (25192, 1000)
            AND gb.bar_code IN (
                SELECT gb.bar_code
                FROM arm_changsha.inv_goods_stock igs
                    INNER JOIN arm_changsha.goods_base gb ON gb.id = igs.goods_id
                WHERE igs.department_id = 25192
            )
        GROUP BY gb.bar_code
        ORDER BY igs.department_id DESC
       ) b
ON b.bar_code = s.goods_barcode
WHERE s.goods_barcode NOT IN (6944639802717, "6798532102036-1")
    AND s.goods_name NOT LIKE "欢乐51大放价，门店购物好时节"
ORDER BY s.confirm_time DESC
LIMIT 3000