CUCU2/src/custom/restcontroller/webmanage/sale/item/SQL/货品档案列表.sql

SELECT t1.itemid,
       t1.itemno,
       t1.itemname,
       t1.model,
       t1.spec,
       t1.marketprice,
       t2.unitname,
       t1.unitid,
       t1.createdate,
       t1.isonsale,
       t1.status,
       t1.orderaddqty,
       t1.orderminqty,
       t1.remarks,
       t1.barcode,
       t1.skucontrol,
       t1.batchcontrol,
       t1.grossweight,
       t1.weight,
       t1.height,
       t1.width,
       t1.length,
       t1.delivery,
       t1.istool,
       t1.volume,
       t1.marketingcategory,
       t1.ismodule,
       t1.packageqty,
       t1.iswoodproducts,
       t1.financeclasstype,
       t1.color,
       t1.cheek,
       t1.delistingstatus,
       t1.stockno,
       t1.widthschemeid,
       t1.lengthschemeid,
       t1.iscustomsize,
       t1.customprice,
       t1.pricingmetod,
       t1.saleprice,
       t1.soldbase,
       t1.saleqty,
       t1.byname,
       t1.cost,
       t1.grossprofit,
       t1.grossprofitmargin,
       t6.auxunitid,
       t6.unitgroupname,
       t7.unitname axunitname,
       t8.caliber,
       t8.pressure,
       t8.material,
       t8.butterflyplatedrive,
       t8.connection,
       t8.valveplatematerial,
       t8.bodymaterial,
       t8.actuatortype,
       t8.actuatorbrand,
       t8.isbutterfly,
       t8.erpitemno,
       t8.erpitemname,
       t8.specalnote,
       t8.prodline,
       t8.device,
       t4.schemename widthschemename,
       t5.schemename lengthschemename,
       t9.price price1,
       t10.price price2,
       t11.price price3,
       t12.price price4,
       t13.price price5
FROM plm_item t1
         LEFT JOIN plm_unit t2 ON t2.unitid = t1.unitid AND t2.siteid = t1.siteid
         LEFT JOIN plm_unitgroup t6 ON t6.unitgroupid = t1.unitgroupid
         LEFT JOIN plm_unit t7 ON t7.unitid = t6.unitid AND t7.siteid = t6.siteid
         LEFT JOIN plm_itemextend t8 ON t8.itemid = t1.itemid AND t8.siteid = t1.siteid
         left join sa_sizecustomizedscheme t4 on t1.widthschemeid=t4.sa_sizecustomizedschemeid and t1.siteid=t4.siteid
         left join sa_sizecustomizedscheme t5 on t1.lengthschemeid=t5.sa_sizecustomizedschemeid and t1.siteid=t5.siteid
         left join sa_itemprice t9 on t9.itemid = t1.itemid AND t9.siteid = t1.siteid and t9.pricegrade=1
         left join sa_itemprice t10 on t10.itemid = t1.itemid AND t10.siteid = t1.siteid and t10.pricegrade=2
         left join sa_itemprice t11 on t11.itemid = t1.itemid AND t11.siteid = t1.siteid and t11.pricegrade=3
         left join sa_itemprice t12 on t12.itemid = t1.itemid AND t12.siteid = t1.siteid and t12.pricegrade=4
         left join sa_itemprice t13 on t12.itemid = t1.itemid AND t12.siteid = t1.siteid and t12.pricegrade=5
WHERE t1.siteid = $siteid$
  and $where$