客户成交信息

SELECT
cus.code 客户编码,
cus.full_name 客户名称,
so_summary.近一年销售数量::float / NULLIF(GREATEST(so_summary.近一年销售数量, inq_summary.近一年询价数量), 0) 近一年成交率,
inq_summary.近一年询价数量,
so_summary.近一年销售数量,
so_summary.近一年销售金额,
rd32_summary.客户近一年毛利率
FROM "public".t_crm_custom cus

LEFT JOIN LATERAL(
    SELECT
--  inq_m.customer_name,
    count(distinct (inq_d.inq_order_id, inq_d.brand_no)) 近一年询价数量
    FROM "public".t_inq_order inq_m

    LEFT JOIN "public".t_inq_order_detail inq_d
    ON inq_d.inq_order_id = inq_m.id

    WHERE 1=1
--  AND inq_m.customer_name = cus.full_name
    AND inq_m.is_delete = 1
    AND inq_d.is_delete = 1
    AND inq_d.brand_name ilike 'cato'
    AND inq_d.brand_no ~ any(array['^C4X-', '^C3D-', '^C\d+', '^CP\d+'])  -- 药物线
    AND inq_m.create_time >= now() - '1 year'::interval

    AND inq_m.customer_name = cus.full_name
) inq_summary
ON TRUE

LEFT JOIN LATERAL(
    SELECT
    count(*) 近一年销售数量,
    sum(so_d."iSum" - coalesce(crm_so_d.loss_amount, 0)) 近一年销售金额
    FROM u8_009."SO_SOMain" so_m
--  ON so_m."cCusCode" = u8_cus."cCusCode"

    LEFT JOIN u8_009."SO_SODetails" so_d
    ON so_d."ID" = so_m."ID"

    LEFT JOIN "public".t_inq_sync_crm_detail crm_so_d
    ON crm_so_d.sal_order_detail_id = so_d."iSOsID"::text
    AND crm_so_d.is_delete = 1

    LEFT JOIN u8_009."Inventory" inv
    ON inv."cInvCode" = so_d."cInvCode"

    WHERE 1=1
    AND so_m."cCusCode" = cus.code
    AND so_m.dverifysystime is not null
    AND inv."cInvCCode" in (
    '03021005'  -- 药物线
    )
    AND so_m."dDate" >= now() - '1 year'::interval

    GROUP BY so_m."cCusCode"
) so_summary
-- ON so_summary."cCusCode" = cus.code
ON TRUE

LEFT JOIN LATERAL(
    SELECT
    avg(((so_d."iSum" - coalesce(crm_so_d.loss_amount, 0)) / so_d."iQuantity" * rd32_d."iQuantity" - rd32_d."iPrice") / nullif(((so_d."iSum" - coalesce(crm_so_d.loss_amount, 0)) / so_d."iQuantity" * rd32_d."iQuantity"), 0)) 客户近一年毛利率
    FROM u8_009.rdrecord32 rd32_m

    INNER JOIN u8_009.rdrecords32 rd32_d
    ON rd32_d."ID" = rd32_m."ID"

    INNER JOIN u8_009."SO_SODetails" so_d
    ON so_d."iSOsID" = rd32_d.iorderdid

    LEFT JOIN "public".t_inq_sync_crm_detail crm_so_d
    ON crm_so_d.sal_order_detail_id = so_d."iSOsID"::text
    AND crm_so_d.is_delete = 1

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

    WHERE 1=1
    AND rd32_m."cCusCode" = cus.code
    AND rd32_m."dDate" >= now() - '1 year'::interval
    AND rd32_m.dnverifytime is not null
    AND rd32_d."iPrice" is not null
    AND rd32_d."iQuantity" > 0
    AND inv."cInvCCode" in (
    '03021005'  -- 药物线
    )
) rd32_summary
ON TRUE

WHERE cus.is_delete = 1
AND cus.code = '12763'  -- 客户编码输入到这里