cucu_YOS/src/custom/restcontroller/webmanage/sale/order/SQL/查询可添加商品列表.sql

SELECT t1.itemid,
       t1.itemno,
       t1.itemname,
       t1.model,
       t1.batchcontrol,
       t1.orderminqty_auxunit,
       t1.orderminqty,
       t1.orderaddqty_auxunit,
       t1.orderaddqty,
       t1.spec,
       t1.standards,
       t1.marketprice,
       t4.unitname unit,
       t5.unitname auxunit,
       t2.conversionrate,
       t3.material,
       t3.erpitemno,
       t3.erpitemname,
       t3.specalnote,
       t3.prodline,
       t3.device
from plm_item t1
         LEFT JOIN plm_unitgroup t2 ON t2.unitgroupid = t1.unitgroupid and t2.siteid = t1.siteid
         left join plm_itemextend t3 on t3.itemid = t1.itemid and t3.siteid = t1.siteid
         LEFT JOIN plm_unit t4 ON t4.unitid = t1.unitid AND t4.siteid = t1.siteid
         LEFT JOIN plm_unit t5 ON t5.unitid = t2.auxunitid AND t5.siteid = t2.siteid
WHERE t1.siteid = $siteid$
  and t1.status = '审核'
  and t1.isonsale = 1
  and t1.istool = 0
  and $where$
  and t1.itemid in (SELECT itemid from plm_item_tradefield WHERE siteid = $siteid$ and tradefield = $tradefield$)
  and t1.itemid in (SELECT t1.itemid
                    from sa_itemsaleclass t1
                             INNER JOIN plm_itemclass t2 ON t1.itemclassid = t2.itemclassid and t1.siteid = t2.siteid
                    WHERE t1.siteid = $siteid$
                      and t2.sa_brandid = $sa_brandid$
                      and $where2$)