<?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.rongwei.bscommon.sys.dao.EquOverhaulInventorySetMapperDao">

    <update id="updateInventorySetWarnStatus">
        UPDATE equ_overhaul_inventory_set
        SET WARNSTATUS = CASE
            WHEN EXPIREDATE IS NULL AND INVENTORYNUMBER >= IFNULL( SAFETYSTOCKUPPER, 0 ) * 1.5 THEN 10
            WHEN EXPIREDATE IS NULL AND INVENTORYNUMBER >= IFNULL( SAFETYSTOCKUPPER, 0 ) THEN 20
            WHEN EXPIREDATE IS NULL AND INVENTORYNUMBER >= IFNULL( SAFETYSTOCKLOWER, 0 ) THEN 30
            WHEN EXPIREDATE IS NULL AND INVENTORYNUMBER >= IFNULL( SAFETYSTOCKLOWER, 0 ) * 0.5 THEN 40
            WHEN EXPIREDATE IS NULL AND IFNULL( SAFETYSTOCKLOWER, 0 ) * 0.5 > INVENTORYNUMBER THEN 50
            WHEN EXPIREDATE IS NOT NULL AND DATEDIFF(EXPIREDATE,now()) >= 90 AND REMINDERSTATUS IN ('10', '20') THEN 20
            WHEN EXPIREDATE IS NOT NULL AND (DATEDIFF(EXPIREDATE,now()) between 30 and 90) AND REMINDERSTATUS IN ('10', '20', '30') THEN 30
            WHEN EXPIREDATE IS NOT NULL AND (DATEDIFF(EXPIREDATE,now()) between 0 and 30) AND REMINDERSTATUS IN ('10', '20', '30', '40') THEN 60
            WHEN EXPIREDATE IS NOT NULL AND 0 >= DATEDIFF(EXPIREDATE,now()) AND REMINDERSTATUS IN ('10', '20', '30', '40', '50') THEN 70
            ELSE WARNSTATUS
        END;
    </update>

</mapper>
