xdmy/doc/sql/getPartApplyByBig.sql

SELECT
CONCAT('uploads/thumbnail/',ep.`picpath`,'/',ep.`newpicname`) picpath,
  CONCAT('uploads/image/',ep.`picpath`,'/',ep.`newpicname`) srcpath,
IF (bpa.`applyType`='0','非维保',
IF (bpa.`applyType`='1','维修','保养')) applyType,
pa.pastureId,
  pr.`id`,
pa.id paid,
  pa.`partCode`,
  pa.`partId`,
  pr.`brandId`,
TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM  pa.`price`))) price,
  pa.`specification`,
 ROUND(pa.amount,2) amount, 
TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM pa.`sumPrice`))) sumPrice ,
  pa.`eqCode`,
  pa.`eqName`,
  pa.`providerId`,
  pa.`useTypeV`,
  pa.`note`,
pa.partName,
pr.reportery,
pr.`location`,
pr.`locationId`,
pr.`reportery`,
pr.`unit`,
pr.contractId,
pr.`brand`,
pv.providerName  

FROM
partapply pa
INNER JOIN bigpartapply bpa 
ON pa.`bigId` = bpa.`id`
 LEFT JOIN part_repertory pr
 ON IF(pa.`contractId`='' OR pa.`contractId` IS NULL ,(pa.`partCode` = pr.`partCode` AND  pa.`providerId` = pr.`providerId`  AND bpa.pastureId = pr.pastureId   AND (pr. reportery>0 OR  (SELECT bc.`statue`   FROM  contract c
INNER JOIN bigcontract bc
ON c.`bigId` = bc.`id`
 WHERE c.id = pr.`contractId`)=1 )
  # 新添加的
 and( (pa.`locationId`='' or pa.`locationId` is null) or pa.`locationId`= pr.`locationId` ) 
#####
 ),(pa.`contractId`=pr.`contractId` AND pa.`brandid`=pr.`brandid`)
 # 新添加的
 and( (pa.`locationId` is null) or (pr.`locationId` is null) or (pa.`locationId`= pr.`locationId`) ) 
#####
)
LEFT JOIN provider pv
    ON (pv.`id` = pr.`providerId` or pv.`id` = pa.`providerId`)
LEFT JOIN parts ps
    ON ps.id = pa.partId
    LEFT JOIN eq_pic ep
    ON ep.id = ps.picpath
WHERE pa.`bigId` = ?
and 
(
  pa.pastureId =18 or
(select pastureId from warehouse where id = pr.`locationId`)=?)
ORDER BY partCode