xdmy/doc/sql/t4.sql

update apisql set sqlstr="
select t.*, IFNULL(pr.reportery,0) reporteryQ
from (
SELECT
CONCAT('uploads/thumbnail/',ep.`picpath`,'/',ep.`newpicname`) picpath,
  CONCAT('uploads/image/',ep.`picpath`,'/',ep.`newpicname`) srcpath,
  b.`brandName`,c.`id`,c.`brand`,c.`brandId`,c.`partCode`,c.`partId`,c.`partName`,c.`pastureId`,c.`pastureName`,
TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.`price`))) price,
 c.`specification`,c.`unit`
FROM
    contract c
LEFT JOIN brand b  ON b.id = c.brandId
LEFT JOIN bigcontract bc  ON bc.id = c.bigId
LEFT JOIN bigcontract bc2 ON bc2.contractCode = left(bc.contractCode,12)
join pasture_bigcontract pbt on pbt.bigcontract_id =bc2.id
LEFT JOIN parts ps  ON ps.id = c.partId
LEFT JOIN eq_pic ep  ON ep.id = ps.picpath
  WHERE 
bc.`providerId` = ? AND c.enable = 1
AND bc.`stopTime`>=DATE_FORMAT( NOW(),'%Y-%m-%d') 
and  bc.statue=1 and bc.SHStatus = 7 
AND if(?='' ,bc.SHStatus = 7,c.`partCode` =? OR c.`partName` = ? OR c.`specification` =?)
and (bc.pastureId=? or  ?='' or  pbt.pasture_id = ?) 
)t 
left join 
(select reportery,contractId from part_repertory pr2 left join warehouse w  on pr2.locationId =w.id
where pr2.pastureId =? or (pr2.pastureId =18 and  w.pastureId =  ?) 
) pr  ON pr.`contractId` = t.`id` ",
params ="providerId,partCode,partCode,partCode,partCode,pastureId,pastureId,pastureId,pastureId,pastureId"
where  sqlname ='getPartListByProvider'