销售库存查询报表调整

问题分析：

查询报表时，输入SKU无法查到数据，输入SPU能查到数据。期望结果：无论输入SKU/SPU都能找到对应的商品

源码分析：(以销售订单为例)

WITH RECURSIVE categorys AS (
                SELECT
                    pc.id
                FROM nt_product_category pc
                where case when cast(%(category_id)s as text) = '0' then False else pc.parent_id in %(category_id)s or pc.id in %(category_id)s end
                union ALL
                SELECT
                    pc1.id
                FROM categorys c
                inner join nt_product_category pc1 on pc1.parent_id = c.id
    )
    select nss.code as shop_code,nss.name as shop_name,np.code as product_code,np.name as product_name,npvc.color_name,npvs.size_name,line.wait_stock_count,line.stock_count,line.stock_count*np.sale_price as stock_amount,line.sale_count,line.sale_amount
        from
        (
            select al.product_id,al.shop_id,sum(al.wait_stock_count) as wait_stock_count,sum(al.stock_count) as stock_count,sum(al.sale_count) as sale_count,sum(al.sale_amount) as sale_amount
                from
                (
                        select product_id,shop_id,COALESCE(sum(transit_count),0.00) as wait_stock_count, 0 as stock_count,0 as sale_count,0 as sale_amount
                            from
                            (
                            SELECT
                            nsol.product_id,
                            nso.shop_id,
                            0 as wait_stock_count,
                            0 as stock_count,
                            COALESCE(SUM(nsol.quantity),0) sale_count,
                            COALESCE(ROUND(SUM(nsol.amount_after_discount),2),0) sale_amount
                        FROM
                            nt_sale_order_line nsol
                        JOIN
                            nt_sale_order nso ON nsol.order_id = nso.id
                        left join nt_product np
                            on np.id = nsol.product_id
                        left join nt_product np2
                            on np2.id = np.parent_id
                        WHERE
                            nso.shop_id in %(shop_ids)s AND to_char(nso.order_day, 'YYYY-MM-DD') >= %(start_date)s AND to_char(nso.order_day, 'YYYY-MM-DD') <= %(end_date)s AND nso.state = 'done'
                            and case when cast(%(product_ids)s as text) <> '0' then (case when np.parent_id is not null then np2.id IN %(product_ids)s else np.id IN %(product_ids)s end) else True end
                            and case when cast(%(year)s as text) <> '0' then (case when np.parent_id is not null then np2.year in %(year)s else np.year in %(year)s end) else True end
                            and case when cast(%(season)s as text) <> '0'then (case when np.parent_id is not null then np2.season in %(season)s else np.season in %(season)s end) else True end
                            and case when cast(%(sex)s as text) <> '0' then (case when np.parent_id is not null then np2.sex in %(sex)s else np.sex in %(sex)s end) else True end
                            and case when cast(%(wave)s as text) <> '0' then (case when np.parent_id is not null then np2.wave in %(wave)s else np.wave in %(wave)s end) else True end
                            and case when cast(%(series)s as text) <> '0' then (case when np.parent_id is not null then np2.series in %(series)s else np.series in %(series)s end) else True end
                            and case when cast(%(bigclass)s as text) <> '0' then (case when np.parent_id is not null then np2.bigclass in %(bigclass)s else np.bigclass in %(bigclass)s end) else True end
                            and case when cast(%(subclass)s as text) <> '0' then (case when np.parent_id is not null then np2.subclass in %(subclass)s else np.subclass in %(subclass)s end) else True end
                            and case when cast(%(settle_class)s as text) <> '0' then (case when np.parent_id is not null then np2.settle_class in %(settle_class)s else np.settle_class in %(settle_class)s end) else True end
                            and case when cast(%(style_attribute)s as text) <> '0' then (case when np.parent_id is not null then np2.style_attribute in %(style_attribute)s else np.style_attribute in %(style_attribute)s end) else True end
                            and case when cast(%(category_id)s as text) <> '0' then (case when np.parent_id is not null then np2.category_id in (select id from categorys group by id) else np.category_id in (select id from categorys group by id) end) else True end
                        GROUP BY
                            nso.shop_id, nsol.product_id
                ) as al
                group by al.product_id,al.shop_id
        ) as line
        left join nt_product np
            on np.id = line.product_id
        left join nt_sale_shop nss
            on nss.id = line.shop_id
        left join (
            select ppvvr.product_id,npvo.name as color_name from product_product_variant_value_rel ppvvr
                join nt_product_variant_value npvv
                    on npvv.id = ppvvr.value_id
                join nt_product_variant_type npvt
                    on (npvt.id = npvv.type_id and npvt.type = 'color')
                join nt_product_variant_option npvo
                    on (npvo.id = npvv.option_id and npvo.type_id = npvt.id)
        ) npvc
        on npvc.product_id = np.id
        left join (
            select ppvvr.product_id,npvo.name as size_name from product_product_variant_value_rel ppvvr
                join nt_product_variant_value npvv
                    on npvv.id = ppvvr.value_id
                join nt_product_variant_type npvt
                    on (npvt.id = npvv.type_id and npvt.type = 'size')
                join nt_product_variant_option npvo
                    on (npvo.id = npvv.option_id and npvo.type_id = npvt.id)
        ) npvs
        on npvs.product_id = np.id
        where line.wait_stock_count <> 0 or line.stock_count <> 0 or line.sale_count <> 0 or sale_amount <> 0
        order by nss.code,np.code

解析：源码中：

FROM
                            nt_sale_order_line nsol
                        JOIN
                            nt_sale_order nso ON nsol.order_id = nso.id
                        left join nt_product np
                            on np.id = nsol.product_id
                        left join nt_product np2
                            on np2.id = np.parent_id

1.由上源码销售订单得知：np为SKU,np2为SPU

2.输入的product_ids不为空时，输入的为SKU时，会找np2的product_id，否则找np.id。所以输入SKU商品时，找不到对应的商品，输入SPU时却能找到：

and case when cast(%(product_ids)s as text) <> '0' then (case when np.parent_id is not null then np2.id IN %(product_ids)s else np.id IN %(product_ids)s end) else True end

3.在查询语句中加入以上逻辑：创建一个商品列表，包含了sku_id或spu_id

product_list as (
            select id from nt_product where parent_id in %(product_ids)s
            union
            select id from nt_product where id in %(product_ids)s
    )

4.查询时，输入的商品id不为空，商品的id在product_list中，无论输入的是sku还是spu，结果都能在列表中找到对应的商品

                                    and case when cast(%(product_ids)s as text) <> '0' then np.id IN (select id from product_list) else True end