with tmp_invpositionsum_009 as (
        -- 匹配最大库存量 批次 数量
        select
        row_number() over (partition by stock.cInvCode order by stock.iQuantity desc) as r,
        stock.Autoid,
        stock.cInvCode,
        stock.cBatch,
        stock.iQuantity,
        stock.cFree1,
        batch.cBatchProperty7,
        batch.cBatchProperty10,
        case
         when extra.dw = 'g' and extra.isnum='1' then cast(extra.num as numeric(10,4))*1000  --1g=1000mg
         when extra.dw = 'μg' and extra.isnum='1'  then cast(extra.num as numeric(10,4))*0.001  --1μg=0.001mg
         when extra.dw = 'kg' and extra.isnum='1'  then cast(extra.num as numeric(10,4))*1000000  --1kg=1000000mg
         when extra.dw = 'ml' and extra.isnum='1'  then cast(extra.num as numeric(10,4))*1000  --1ml=1000mg
         when extra.dw = 'l' and extra.isnum='1'  then cast(extra.num as numeric(10,4))*1000000  --1L=1000000mg
         when extra.dw = 'ng' and extra.isnum='1'   then cast(extra.num as numeric(10,4))*0.001 --1ng=0.001mg
         when extra.dw = 'mg' and extra.isnum='1'  then cast(extra.num as numeric(10,4))
         else null
        end as cFree1_tomg,
        count(stock.autoid) over (partition by stock.cInvCode) as "U8总批次数",
        sum(stock.iQuantity) over (partition by stock.cInvCode) as  "U8总库存量",

    count(case when  DateAdd(yy,1,getdate()) <  CONVERT(TIMESTAMP, batch.cBatchProperty10) and stock.cBatch is not null then autoid end ) over (partition by stock.cInvCode) as "U8有效批次",

    sum(case when DateAdd(yy,1,getdate()) <  CONVERT(TIMESTAMP, batch.cBatchProperty10) and stock.cBatch is not null then iQuantity end ) over (partition by stock.cInvCode) as  "U8有效库存量"

        from InvPositionSum_U8_009 stock with(nolock)

        left join AA_BatchProperty_U8_009 batch with(nolock)
        on stock.cInvCode = batch.cInvCode
        and stock.cbatch = batch.cBatch

        OUTER apply (
            SELECT
            case
            when LEN(stock.cFree1)-PATINDEX('%[0-9]%',reverse(stock.cFree1))+1 <0 then ''
            else ISNUMERIC(substring(stock.cFree1,1,LEN(stock.cFree1)-PATINDEX('%[0-9]%',reverse(stock.cFree1))+1))
        end as isnum,

        case
            when LEN(stock.cFree1)-PATINDEX('%[0-9]%',reverse(stock.cFree1))+1 <0 then stock.cFree1
            else replace(substring(stock.cFree1,1,LEN(stock.cFree1)-PATINDEX('%[0-9]%',reverse(stock.cFree1))+1),',','.')
        end as num,

        case
            when PATINDEX('%[0-9]%',reverse(stock.cFree1))-1<1 then stock.cFree1
            else LTRIM(reverse(substring(reverse(stock.cFree1),1,PATINDEX('%[0-9]%',reverse(stock.cFree1))-1)))
        end as dw
        )extra

)
, tmp_drug_line as (

-- 药物线

select

inv_009.cInvCCode,
LIST_PROD_SO_PO_COLLECT.new_cat_no as prd_cat_no,
LIST_PROD_SO_PO_COLLECT.prd_cas,
LIST_PROD_SO_PO_COLLECT.prd_en_name,
LIST_PROD_SO_PO_COLLECT.prd_cn_name,
LIST_PROD_SO_PO_COLLECT.prd_api,
LIST_PROD_SO_PO_COLLECT.prd_appearance,
LIST_PROD_SO_PO_COLLECT.prd_storage,
LIST_PROD_SO_PO_COLLECT.prd_remark,

case
    when inv_009.cInvCCode like '0301%' then '非药线'
    when inv_009.cInvCCode like '0302%' then '药物线'
    when inv_009.cInvCCode like '0303%' then '中间体'
else null
end "产品线",

coalesce(so_count_2yearago, 0) "前两年销售次数",  --前两年销售次数
coalesce(so_count_1yearago, 0) "前一年销售次数",  --前一年销售次数

coalesce(so_count_12month, 0) "近一年销售次数", --近一年销售次数
coalesce(so_iqu_12month, 0) "近一年销售瓶数", --近一年销售瓶数
coalesce(inq_cus_12monthago, 0) "近一年询价客户数", --近一年询价客户数
coalesce(inq_12monthago, 0) "近一年询价次数", --近一年询价次数,
coalesce(so_tomg_iqu_12month, 0) "近一年销售量(mg)", --近一年销售量(mg)
coalesce(so_iSum_12month, 0) "近一年销售金额",
coalesce(po_iNatSum_12month, 0) "近一年采购价税合计",
coalesce(po_iqu_12month, 0) "近一年采购mg",
coalesce(quo_iqu_12monthago, 0) "近一年询价mg",


coalesce(so_tomg_iqu_3month, 0) "近三个月销售量(mg)", --近三个月销售量(mg)
coalesce(so_tomg_iqu_2month, 0) "近两个月销售量(mg)", --近两个月销售量(mg)


coalesce(so_tomg_iqu_6month, 0) "近半年销售量(mg)", --近半年销售量(mg)
coalesce(so_iSum_6month, 0) "近半年销售金额",--近半年销售金额
coalesce(po_iNatSum_6month, 0) "近半年采购价税合计",
coalesce(po_iqu_6month, 0) "近半年采购mg",
coalesce(inq_cus_6monthago, 0) "近半年询价客户数",
coalesce(inq_6monthago, 0) "近半年询价次数",
coalesce(quo_iqu_6monthago, 0) "近半年询价mg",

coalesce(inq_cus_3monthago, 0) "近三个月询价客户数",

coalesce(so_iSum_1month, 0) "近一个月销售金额",
coalesce(so_tomg_iqu_1month, 0) "近一个月销售量(mg)",
coalesce(inq_cus_1monthago, 0) "近一个月询价客户数",
coalesce(inq_1monthago, 0) "近一个月询价次数",
coalesce(po_iNatSum_1month, 0) "近一个月采购价税合计",
coalesce(po_iqu_1month, 0) "近一个月采购mg",
coalesce(quo_iqu_1monthago, 0) "近一个月询价mg",

ischeck 是否免检,

case when coalesce(tmp_invpositionsum_009.U8总库存量,0) < (so_tomg_iqu_6month/3) then '是' else '否' end 现存数量是否小于近两个月销售数量,
case when coalesce(tmp_invpositionsum_009.U8总库存量,0) < (so_tomg_iqu_3month) then '是' else '否' end 现存数量是否小于近三个月销售数量,
coalesce(tmp_invpositionsum_009.U8总库存量,0) + coalesce(LIST_PROD_SO_PO_COLLECT.po_iqu_own_afloat, 0) + COALESCE(onprogress_synth_prj.[在途研发量mg],0) 展望库存数量,
case when coalesce(tmp_invpositionsum_009.U8总库存量,0) + coalesce(LIST_PROD_SO_PO_COLLECT.po_iqu_own_afloat, 0) + COALESCE(onprogress_synth_prj.[在途研发量mg],0)< (so_tomg_iqu_3month) then '是' else '否' end 展望数量是否小于近三个月销售数量,

case when DateAdd(yy,1,getdate()) <= tmp_invpositionsum_009.cBatchProperty10 then '是' else '否'  end 证书有效期是否大于等于1年,

cast(tmp_invpositionsum_009.U8有效库存量 as float) U8有效库存量,
tmp_invpositionsum_009.U8有效批次,

cast(tmp_invpositionsum_009.U8总库存量 as float) U8总库存量,
tmp_invpositionsum_009.U8总批次数,

cast(tmp_invpositionsum_009.iquantity as float)最大库存量,
tmp_invpositionsum_009.cbatch  最大库存量批号,


tmp_invpositionsum_009.cBatchProperty10 最大库存量证书过期日期,

tmp_invpositionsum_009.cBatchProperty7 纯度,

so_tomg_iqu_6month "目标备货量",
LIST_PROD_SO_PO_COLLECT.po_iqu_own_afloat "采购在途量",
inv_009.cInvDefine2 负责人,
ST_iQuantity 原料可用库存,
onprogress_synth_prj.[在途研发量mg],
tmp_rd08.[研发入库数量]

from LIST_PROD_SO_PO_COLLECT with(nolock)

left join Inventory_U8_009 inv_009 with(nolock)
on LIST_PROD_SO_PO_COLLECT.new_cat_no = inv_009.cInvCode

left join tmp_invpositionsum_009 with(nolock)
on tmp_invpositionsum_009.cInvCode = 'M-' + LIST_PROD_SO_PO_COLLECT.new_cat_no
AND tmp_invpositionsum_009.r = 1

OUTER apply (
    SELECT sum(ST_iQuantity) ST_iQuantity
    FROM LIST_Stock_Surplus_Num_u8_009 with(nolock)
    where cInvCName='cato'
    AND ST_cInvCode='M-'+prd_cat_no
) st  --可用原料库存

OUTER apply (
    -- 研发入库
        select
        sum(rd08_d.iQuantity) 研发入库数量
        from RdRecords08_U8_009 rd08_d with(nolock)

        left join RdRecord08_U8_009 rd08_m
        on rd08_d.ID = rd08_m.ID

        where rd08_m.cRdCode = '0110'
        AND rd08_d.cInvCode = 'M-' + prd_cat_no
) tmp_rd08

OUTER APPLY (
        select
        sum(quantity) as 在途研发量mg

        from Lims_bv_po_synthetic_u8_009 bv_po_synthetic  with(nolock)

        left join Lims_bv_product_u8_009 bv_product with(nolock)
        on bv_po_synthetic.product_id = bv_product.id

        where bv_po_synthetic.synthetic_status in (1,2)  -- 未开始 在研
        AND bv_product.material_article_no = 'M-' + prd_cat_no
        AND bv_product.brand = 'cato'

) onprogress_synth_prj

where 1=1
AND LIST_PROD_SO_PO_COLLECT.prd_cat_no not in ('1','0','2','3','4','5','6','7','8','9','10')

)
-- SELECT * FROM tmp_drug_line;
, drug_line_catalog as (

    select
    tmp_drug_line.* ,
    cato_prd.img_url,
    product_pakcage.cost as 最大规格成本,
    product_pakcage.package_value as 最大规格,
    product_pakcage.每毫克成本单价,
    stockup_type."备货潜力标识",
    stockup_type."核心备货标识",
    case
    when stockup_type."核心备货标识" = '是' then '常规备货'
    when stockup_type."备货潜力标识" = '是' then '潜力备货'
    end "备货类型",

    case
        when COALESCE("近一年销售量(mg)", 0) < 200 then 200
        when COALESCE("近一年销售量(mg)", 0) BETWEEN 200 and 500 then "近一年销售量(mg)"
        when COALESCE("近一年销售量(mg)", 0) > 500 then 500
    end '潜力备货规格',

    blacklist.*,
    coalesce(CASE WHEN blacklist.黑名单结束日期 > getdate() THEN '是' ELSE '否' END, '否') 是否黑名单

    from tmp_drug_line

    OUTER apply (

        select TOP 1
        cost,
        package_value,
        cost / COALESCE(cfree1_tomg, 1) 每毫克成本单价
        from t_product_package pkg with(nolock)
        where pkg.brand_name= 'cato'
        AND pkg.is_delete = 1
        AND pkg.cat_no = prd_cat_no
        ORDER BY pkg.cfree1_tomg desc
    )product_pakcage

    OUTER apply (
        SELECT TOP 1
        'https://web.cato-chem.com' + img_url img_url
        FROM t_product prd with(nolock)
        WHERE brand_name = 'cato'
        AND is_delete = 1
        AND prd.cat_no = prd_cat_no
        ORDER BY create_time desc
    ) cato_prd

    OUTER apply (
        SELECT TOP 1
        bl.blacklist_endtime 黑名单结束日期,
        bl.remark 黑名单原因
        FROM stock_up_blacklist bl with(nolock)
        WHERE bl.cat_no = prd_cat_no
        ORDER BY created_at desc
    ) blacklist

    OUTER APPLY (
        SELECT
        case
            when 近一年询价客户数 >= 10
            or 近半年询价客户数 >= 8
            or 近三个月询价客户数 >= 6
            or 近一个月询价客户数 >= 4
            then '是'
--          when ${if(len(潜在_近一年询价客户数)=0,"1=0","近一年询价客户数 >= ('"+潜在_近一年询价客户数+"')")}
--          or ${if(len(潜在_近半年询价客户数)=0,"1=0","近半年询价客户数 >= ('"+潜在_近半年询价客户数+"')")}
--          or ${if(len(潜在_近三个月询价客户数)=0,"1=0","近三个月询价客户数 >= ('"+潜在_近三个月询价客户数+"')")}
--          or ${if(len(潜在_近一个月询价客户数)=0,"1=0","近一个月询价客户数 >= ('"+潜在_近一个月询价客户数+"')")}
--      then '是'
    else '否'
    end '备货潜力标识',

    case
        when 近一年询价客户数 >= 4
        and 近一年询价次数 >= 5
        AND 近一年销售次数 >= 3
        and 近一年销售瓶数 >= 4
        then '是'
--      when ${if(len(近一年销售次数)=0,"1=1","近一年销售次数 >= ('"+近一年销售次数+"')")}
--      and ${if(len(近一年销售瓶数)=0,"1=1","近一年销售瓶数 >= ('"+近一年销售瓶数+"')")}
--      and ${if(len(近一年询价客户数)=0,"1=1","近一年询价客户数 >= ('"+近一年询价客户数+"')")}
--      and ${if(len(近一年询价次数)=0,"1=1","近一年询价次数 >= ('"+近一年询价次数+"')")}
--      then '是'
    else '否'
    end '核心备货标识'
    )stockup_type

)

select
drug_line_catalog.*,
last_po.last_po_cfree1,
last_po.last_po_iQuantity,
last_po.last_po_isum

from drug_line_catalog

OUTER APPLY(

    SELECT TOP 1
    po_d.cFree1 last_po_cfree1,
    po_d.iQuantity last_po_iQuantity,
    po_d.iSum last_po_isum
    FROM PO_Podetails_U8_009 po_d
    LEFT JOIN PO_Pomain_U8_009 po_m
    ON po_d.POID = po_m.POID

    WHERE po_d.cInvCode = 'M-' + drug_line_catalog.prd_cat_no
    ORDER BY po_m.cAuditTime desc
)last_po


where 1=1
and 产品线 in ('药物线')
and 备货类型 in ('常规备货', '潜力备货')
-- and 是否黑名单 = '否'
-- and (
--  (备货类型 = '常规备货' and 展望数量是否小于近三个月销售数量 = '是')
--  or (备货类型 = '潜力备货' and (展望库存数量 < case when [近三个月销售量(mg)] > 潜力备货规格 then [近三个月销售量(mg)] else 潜力备货规格 -- -- end))
-- )
--------
-- ${if(len(货号)=0,"","and prd_cat_no  like ('%"+trim(货号)+"%')")}
-- ${if(len(中文名)=0,"","and prd_cn_name like ('%"+trim(中文名)+"%')")}
-- ${if(len(cas)=0,"","and prd_cas like ('%"+trim(cas)+"%')")}
-- ${if(len(多货号)=0,"","and prd_cat_no in ('"+Replace(多货号,"\n","','")+"')")}
-- ${if(len(备货类型) = 0,"","and 备货类型 in ('" + replace(备货类型,",","','") + "')")}
-- ${if(len(产品线) = 0,"","and 产品线 in ('" + replace(产品线,",","','") + "')")}
-- ${if(剔除黑名单 = "是","and 是否黑名单 = '否'", "")}
-- ${if(只显示备货 = "是","and ((备货类型 = '常规备货' and 展望数量是否小于近三个月销售数量 = '是') or (备货类型 = '潜力备货' and (展望库存数量 < case when [近三个月销售量(mg)] > 潜力备货规格 then [近三个月销售量(mg)] else 潜力备货规格 end)))", "")}
--------
-- and ${if(len(前一年销售次数)=0,"1=1","前一年销售次数 >= ('"+前一年销售次数+"')")}
-- and ${if(len(前两年销售次数)=0,"1=1","前两年销售次数 >= ('"+前两年销售次数+"')")}
-- and ${if(len(近一年销售次数)=0,"1=1","近一年销售次数 >= ('"+近一年销售次数+"')")}
-- and ${if(len(近一年销售瓶数)=0,"1=1","近一年销售瓶数 >= ('"+近一年销售瓶数+"')")}
-- and ${if(len(近一年询价客户数)=0,"1=1","近一年询价客户数 >= ('"+近一年询价客户数+"')")}
-- and ${if(len(近一年询价次数)=0,"1=1","近一年询价次数 >= ('"+近一年询价次数+"')")}
-- and ${if(len(现存数量是否小于近两个月销售数量)=0,"1=1","现存数量是否小于近两个月销售数量 in ('"+现存数量是否小于近两个月销售数量+"')")}
--
-- and ${if(len(近一个月询价次数)=0,"1=1","近一个月询价次数 > ('"+近一个月询价次数+"')")}
-- and ${if(len(近一个月询价客户数)=0,"1=1","近一个月询价客户数 > ('"+近一个月询价客户数+"')")}

--and ${if(len(api)=0,"1=1","API in ('"+api+"')")}
order by prd_cat_no