with RECURSIVE prd_catalog as (

    SELECT
    id,
    pid,
    cn_name,
    en_name,
    handler
    FROM public.t_product_catalog t
    where t.en_name = 'cato'

    UNION ALL

    SELECT
    t.id,
    t.pid,
    t.cn_name,
    t.en_name,
    COALESCE(t.handler, prd_catalog.handler) "handler"
    FROM public.t_product_catalog t
    INNER JOIN prd_catalog
    ON prd_catalog.id = t.pid

), initial_wa as (
    select
    "VoucherId"
    from "WFAudit" wa
    where wa."VoucherType" = N'MO21'
    --  AND wa.OperationDate >= cast(? / 1000 as datetime)
    ), processed_wa as (

    SELECT
    "VoucherId",
    "VoucherType",
    "VoucherCode",
    "Action",
    "OperatorName",
    "OperationDate",
    row_number() over(partition by "VoucherId" order by "OperationDate" asc) action_r

    from "WFAudit" wa
    where wa."VoucherType" = N'MO21'
    AND wa."VoucherId" in (select initial_wa."VoucherId" from initial_wa)

), filtered_wa as (
    -- 后面有5都不要
    SELECT
    t1.*
    FROM processed_wa t1

    where not EXISTS (
    SELECT
    1 as ignored
    FROM processed_wa t2
    where t2."VoucherId" = t1."VoucherId"
    and t2."Action" = 5
    and t2.action_r >= t1.action_r
    )

), first_audit as(
    -- Action
    -- 0 提交
    -- 1 同意
    -- 7 弃审
    -- 5 撤销
    SELECT
    *
    FROM (
        SELECT
        filtered_wa.*,
        case when filtered_wa."VoucherId" is null then 0
        else sum(case when filtered_wa."Action" = 7 then 1 else 0 end) over(partition by filtered_wa."VoucherId") end n_canceled,  -- 取消次数
        case when filtered_wa."VoucherId" is null then 0
        else row_number() over(partition by filtered_wa."VoucherId", filtered_wa."Action" order by filtered_wa."OperationDate" asc) end nth_submit  -- 第几次审核

        from filtered_wa
    ) tmp
    WHERE tmp."Action" = 1
    AND nth_submit = n_canceled + 1  -- 初审记录
), raw_material_summary as (
    SELECT
    *
    FROM (
        SELECT

        rd10_d."cInvCode",
        rd10_d."cBatch",
        mom_d."MoDId",
        rd11_d."cInvCode" "raw_cInvCode",
        rd11_d."cBatch" "raw_cBatch",
        rd11_d."iQuantity" "raw_iQuantity",
        row_number() over(partition by rd10_d."cInvCode", rd10_d."cBatch" ORDER BY rd11_d."AutoID" desc) r
        FROM "rdrecords10" rd10_d

        LEFT JOIN "mom_orderdetail" mom_d
        ON mom_d."MoDId" = rd10_d."iMPoIds"

        LEFT JOIN "mom_moallocate" mom_moa
        ON mom_d."MoDId" = mom_moa."MoDId"

        LEFT JOIN "rdrecords11" rd11_d
        ON mom_moa."AllocateId" = rd11_d."iMPoIds"
    ) tmp
    where tmp.r = 1
), po_summary as (
    -- 采购入库明细
    SELECT
    *
    FROM (
        SELECT
        app_m."cMaker" app_cmaker,
        po_m."cMaker" po_cmaker,
        po_m."cPOID" po_cpoid,
        po_m."cAuditTime" "po_cAuditTime",
        po_d."cDefine30" "po_cDefine30",
        ven."cVenName" "po_cVenName",
        rd01_m."dDate" "rd01_dDate",
        rd01_m."cVenCode",
        rd01_d."cInvCode" "rd01_cInvCode",
        rd01_d."cBatch" "rd01_cBatch",
        rd01_d."iQuantity" "rd01_iQuantity",
        rd01_d."iSum" "rd01_iSum",
        rd01_d."iSum" / rd01_d."iQuantity" unit_cost,
        row_number() over(partition by rd01_d."cInvCode", rd01_d."cBatch" ORDER BY rd01_d."AutoID" desc) r,
        count(*) over(partition by rd01_d."cInvCode", rd01_d."cBatch") c
        FROM rdrecords01 rd01_d

        LEFT JOIN "RdRecord01" rd01_m
        ON rd01_m."ID" = rd01_d."ID"

        LEFT JOIN "Vendor" ven
        ON ven."cVenCode" = rd01_m."cVenCode"

        LEFT JOIN "PO_Podetails" po_d
        ON rd01_d."iPOsID" = po_d."ID"

        LEFT JOIN "PO_Pomain" po_m
        ON po_d."POID" = po_m."POID"

        LEFT JOIN "PU_AppVouchs" app_d
        ON app_d."AutoID" = po_d."iAppIds"

        LEFT JOIN "PU_AppVouch" app_m
        ON app_m."ID" = app_d."ID"

        where rd01_m.dnverifytime is not null
        AND rd01_d."iQuantity" > 0
    ) tmp
    where tmp.r = 1
), raw_base as (

    SELECT

    row_number() over(partition by po_summary."rd01_cInvCode" ORDER BY rd32_m."dDate" ASC, rd32_d."AutoID" ASC) rd32_r,  -- 关键，算rolling min
    rd32_d.iorderdid "so_isosid",
    rd32_d."iDLsID" "rd32_idlsid",
    rd32_d."AutoID" "rd32_AutoID",
    rd32_m."cCode" "rd32_cCode",
    rd32_m."dDate" "rd32_dDate",
    --  so_m.cSOCode,
    inv."cInvCCode",
    inv_cls."cInvCName",
    rd32_d."cInvCode" "rd32_cInvCode",
    rd32_d."cFree1" "rd32_cFree1",
    rd32_d."iQuantity" "rd32_iQuantity",
    --  so_d.iQuantity so_iQuantity,
    --  so_d.iSum so_iSum,
    --  so_d_ex.cbdefine38,
    --  so_d_ex.cbdefine49,
    --  so_d_ex.cbdefine58,
    --  so_d_ex.cbdefine59,
    raw_material_summary."MoDId",
    raw_material_summary."raw_cInvCode",
    raw_material_summary."raw_cBatch",
    po_summary.*

    FROM rdrecords32 rd32_d

    LEFT JOIN rdrecord32 rd32_m
    ON rd32_d."ID" = rd32_m."ID"

    LEFT JOIN "Inventory" inv
    ON inv."cInvCode" = rd32_d."cInvCode"

    LEFT JOIN "InventoryClass" inv_cls
    ON inv_cls."cInvCCode" = inv."cInvCCode"
    --
    --  LEFT JOIN SO_SODetails so_d
    --  ON rd32_d.iorderdid = so_d.iSOsID
    --
    --  LEFT JOIN SO_SODetails_extradefine so_d_ex
    --  ON so_d_ex.iSOsID = rd32_d.iorderdid
    --
    --  LEFT JOIN SO_SOMain so_m
    --  ON so_d.ID = so_m.ID
    --
    --  LEFT JOIN DispatchLists disp_d
    --  ON disp_d.iDLsID = rd32_d.iDLsID

    -- 使用什么原料批次
    LEFT JOIN raw_material_summary
    ON raw_material_summary."cInvCode" = rd32_d."cInvCode"
    AND raw_material_summary."cBatch" = rd32_d."cBatch"

    -- TODO LEFT JOIN purchase time/vendor/cost
    LEFT JOIN po_summary
    ON raw_material_summary."raw_cInvCode" = po_summary."rd01_cInvCode"
    AND raw_material_summary."raw_cBatch" = po_summary."rd01_cBatch"

), rolling_min_unit_cost as (
-- 关键
-- 算出累计单价最低的供应商，以及最低单价
    SELECT
    raw_base."rd32_AutoID",
    raw_base."rd01_cInvCode",
    raw_base.rd32_r,
    raw_base.unit_cost min_unit_cost,
    raw_base."cVenCode" "min_cost_cVenCode"
    FROM raw_base
    WHERE raw_base."rd01_cInvCode" is not null
    AND raw_base.rd32_r = 1

    UNION ALL

    SELECT
    raw_base."rd32_AutoID",
    raw_base."rd01_cInvCode",
    raw_base.rd32_r,
    case when raw_base.unit_cost < t.min_unit_cost then raw_base.unit_cost else t.min_unit_cost end min_unit_cost,
    case when raw_base.unit_cost < t.min_unit_cost then raw_base."cVenCode" else t."min_cost_cVenCode" end "min_cost_cVenCode"
    FROM raw_base
    INNER JOIN  rolling_min_unit_cost t
    ON raw_base."rd01_cInvCode" = t."rd01_cInvCode"
    AND raw_base.rd32_r = t.rd32_r + 1

)
-- SELECT * FROM rolling_min_unit_cost
, base as (

    SELECT
    raw_base.*,
    case
    when raw_base."cInvCName" != 'CATO' then null
    when not exists(
    SELECT 1 FROM raw_base t where t."rd32_cInvCode" = raw_base."rd32_cInvCode" and t."rd32_dDate" < raw_base."rd32_dDate"
    ) then 1
    else 0
    end is_new_1,  -- 之前没出库过
    CASE
    when raw_base."cInvCName" != 'CATO' then null
    when rolling_min_unit_cost."min_cost_cVenCode" != raw_base."cVenCode" and rolling_min_unit_cost.min_unit_cost > raw_base.unit_cost then 1  -- 关键
    else 0
    end is_new_2  -- 价格比以前低，且是新供应商
    FROM raw_base

    LEFT JOIN rolling_min_unit_cost
    ON rolling_min_unit_cost."rd32_AutoID" = raw_base."rd32_AutoID"

)

SELECT
-- base.*,
base.so_isosid "销售明细ID",
base."rd32_AutoID" "销售出库明细ID",
base."rd32_cCode" "销售出库单号",
base."rd32_dDate" "销售出库日期",
so_m."cSOCode" "销售单号",
base."cInvCCode" "存货大类编码",
base."cInvCName" "存货大类名称",
base."rd32_cInvCode" "存货编码",
base."rd32_cFree1" "规格",
base."rd32_iQuantity" "出库数量",
-- dbo.extract_numeric(base.rd32_cFree1),
normalize_package(base."rd32_cFree1") * base."rd32_iQuantity" "出库数量(mg)",
so_d."iQuantity" "销售数量",
so_d."iSum" "销售含税小计",
coalesce(so_d_ex.cbdefine38, prd_pkg.cost) "核算成本单价",
so_d_ex."cbdefine49" "销售预计发货日期",
so_d_ex."cbdefine59" "询价单明细ID",
base."MoDId" "生产订单ID",
base."raw_cInvCode" "原料编码",
base."raw_cBatch" "原料批次",
base."app_cmaker" 请购人,
base."po_cmaker" 采购员,
base."po_cpoid" 采购单号,
base."po_cAuditTime" 采购单审核时间,
base."po_cDefine30" 采购类型,
base."po_cVenName" 采购单供应商名称,
base."rd01_dDate" "原料采购入库日期",
base."cVenCode" "原料采购供应商编号",
base."rd01_cInvCode" "原料采购存货编码",
base."rd01_cBatch" "原料采购批号",
base."rd01_iQuantity" "原料采购入库数量",
base."rd01_iSum" "原料采购入库含税合计",
base.unit_cost "原料采购单价",
is_new_1,
is_new_2,
first_audit."OperationDate" as 一审时间,
case
when cast(first_audit."OperationDate" as date) <= cast(so_d_ex.cbdefine49 as date) then '准时'
when cast(first_audit."OperationDate" as date) > cast(so_d_ex.cbdefine49 as date) then '超时'
else null
end 是否准时,
case
when base."cInvCName" != 'CATO' then null
when base.is_new_1 = 1 then 1
when base.is_new_2 = 1 then 1
when exists(
    SELECT 1 FROM base t
    WHERE 1=1
    AND t."rd32_dDate" <= base."rd32_dDate"
    AND t."rd32_dDate" >= (base."rd32_dDate" - interval '6month')
    AND t.is_new_1 = 1 OR t.is_new_2 = 1
) then 1
else 0
end 是否新产品,
coalesce(inq.offer_handler, prd_catalog.handler, prd.handler) 负责人,
base.rd32_r

FROM base

LEFT JOIN "SO_SODetails" so_d
ON base.so_isosid = so_d."iSOsID"

LEFT JOIN "SO_SODetails_extradefine" so_d_ex
ON so_d_ex."iSOsID" = base.so_isosid

LEFT JOIN "SO_SOMain" so_m
ON so_d."ID" = so_m."ID"

LEFT JOIN "DispatchLists" disp_d
ON disp_d."iDLsID" = base."rd32_idlsid"

-- TODO join mom_first_audit from pmc
LEFT JOIN first_audit
ON first_audit."VoucherId" = base."MoDId"::text
--
-- TODO join inquiry get primary handler
LEFT JOIN public.t_inq_sync_crm_detail inq_sync
ON inq_sync.id::text = so_d_ex.cbdefine59
--
LEFT JOIN public.t_inq_order_detail inq
ON inq.id = inq_sync.inq_order_detail_id
--
-- TODO join product get backup handler
LEFT JOIN public.t_product prd
ON prd.brand_name = 'cato'
AND prd.is_delete = 1
and prd.cat_no = base."rd32_cInvCode"
--
LEFT JOIN (
    SELECT
    *
    FROM (
        SELECT
        *,
        row_number() over(partition by prd_pkg.brand_name, prd_pkg.cat_no, lower(prd_pkg.package_value) ORDER BY coalesce(update_time, create_time) desc) r
        FROM t_product_package prd_pkg
        ) tmp
        WHERE tmp.r = 1
) prd_pkg
ON prd_pkg.brand_name = 'cato'
AND prd_pkg.is_delete = 1
AND prd_pkg.cat_no = base."rd32_cInvCode"
AND lower(prd_pkg.package_value) = lower(base."rd32_cFree1")
--
LEFT JOIN prd_catalog
ON prd.catalog_id = prd_catalog.id
--
ORDER BY base."rd32_dDate" desc, so_m."cSOCode"
--
--
--
--