xdmy/doc/mcs/sql/getBigupkeepList.sql

SELECT
  qq.*
FROM
  (SELECT
 IFNULL(bpa1.cou,0) laidcou,
    buk.id,
    buk.`eqId`,
    buk.bigupkeeptemplateId,
    CONCAT(
      '名称：',
      but.`upkeepName`,
      '|保养级别：',
      but.`upkeepLevel`
    ) upNameLevel,
    buk.`upkeepCode`,
   buk.scores,
    buk.workflowNote,
    DATE_FORMAT(buk.chargeDate,'%Y-%m-%d %H:%i:%s') chargeDate,
    DATE_FORMAT(buk.useChargeDate,'%Y-%m-%d %H:%i:%s') useChargeDate,
    DATE_FORMAT(buk.finishedTime,'%Y-%m-%d %H:%i:%s')  finishedTime,
    em1.`empname`  upkeepPerson,
    em2.`empname`  useChargePerson,
    em3.`empname`  chargePerson,
em4.empname deptChargePerson,IFNULL( buk.useEmpId, e.employeeId) useEmpId,IFNULL(em5.empname ,em.empname) useEmpName,
  DATE_FORMAT(buk.deptChargeDate,'%Y-%m-%d %H:%i:%s') deptChargeDate,
buk.deptChargeId,
    IFNULL(em.`empname`,employeName) employeName,
    e.employeeId,
    e.`departmentId`,
    buk.upkeepPerson upkeepPersonId,
    e.`eqCode`,
    e.`eqName`,
    e.`specification`,
    p.`name` pastureName,
    d.`name` departmentName,
    IF(
      buk.`statue` = 0
     AND (DATE_FORMAT(NOW(), '%Y-%m-%d') = buk.`plantime` OR buk.`flowCompeleted`>1 
   OR DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY), '%Y-%m-%d') = buk.`plantime` ),
      1,
      buk.`statue`
    ) statue,
    IF(bpa.cou  IS NULL,'未领用','已领用') LYStatue,
    IF(rf.cou  IS NULL,'未录入','已录入') LRStatue,
    buk.flowCompeleted  SHStatue,
    case 
      when (select  id  from video where  upkeepId =buk.id) is null then '未录制'
      when (select  id  from video where  upkeepId =buk.id and endRecord  =2 ) is not null then '录制中'
      when (select  id  from video where  upkeepId =buk.id and newCreateTime=createTime and location <>'') is not null then '已录制'
      else '已录制上传中'
    end as videoTxt,# 录制状态
    ifnull((select  location  from video where  upkeepId =buk.id),'')  videoPath, # 录制地址
    buk.`plantime`
  FROM
    bigupkeep buk
    INNER JOIN equipment e
      ON buk.`eqId` = e.`id`
    INNER JOIN pasture p
      ON p.`id` = e.`pastureId`
    LEFT JOIN department d
      ON e.`departmentId` = d.`id`
    LEFT JOIN emp em
      ON em.id = e.employeeId
    LEFT JOIN emp em1
      ON em1.id = buk.upkeepPerson
    LEFT JOIN emp em2
      ON em2.id = buk.useChargeId
 LEFT JOIN emp em3
      ON em3.id = buk.chargeId
 LEFT JOIN emp em4
      ON em4.id = buk.deptChargeId
 LEFT JOIN emp em5
      ON em5.id = buk.useEmpId
    LEFT JOIN bigupkeeptemplate but
      ON but.id = buk.bigupkeeptemplateId
   LEFT JOIN (SELECT RUCode,COUNT(id)cou FROM  bigpartapply GROUP BY  RUCode ) bpa
    ON bpa.`RUCode` = buk.`upkeepCode`
 LEFT JOIN (SELECT repairCode,COUNT(id)cou FROM  refuse GROUP BY  repairCode)rf
    ON rf.`repairCode` = buk.`upkeepCode`
   LEFT JOIN (SELECT RUCode,COUNT(id)cou FROM  bigpartapply WHERE statue =0 AND (SHStatus IN (2,3,5,7,9)) GROUP BY  RUCode ) bpa1
    ON bpa1.`RUCode` = buk.`upkeepCode`
WHERE 
((buk.upkeepPerson= ? OR buk.useEmpId=? OR IFNULL((SELECT  MAX(r.`datarole`) FROM `user` u
   LEFT JOIN user_role ur
   ON ur.`user_id`= u.`id`
   LEFT JOIN role r
   ON r.`id` = ur.`role_id`
   LEFT JOIN role_menu rm
   ON r.`id` = rm.`role_id`
   LEFT JOIN menu m
   ON m.`id` = rm.`menu_id`
   WHERE u.`empid` = ? AND m.`path`=?),0) IN (1,2,3))

   AND (e.departmentId IN (SELECT id FROM department WHERE pastureId =? AND id IN (
	 
SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(ss.www,',',b.seq+1),',',-1) AS 'ids' 
				 FROM 
					(SELECT @i:=1 i,IFNULL((SELECT GROUP_CONCAT(department_id)SSSS 
				 FROM role_department WHERE role_id = (SELECT  MAX(r.id) FROM `user` u
					 LEFT JOIN user_role ur
					 ON ur.`user_id`= u.`id`
					 LEFT JOIN role r
					 ON r.`id` = ur.`role_id`
					 LEFT JOIN role_menu rm
					 ON r.`id` = rm.`role_id` 
					 LEFT JOIN role_department rd
					 ON r.`id` = rd.`role_id`
					 LEFT JOIN menu m
					 ON m.`id` = rm.`menu_id`
					 WHERE u.`empid` = ? AND m.`path`=?) AND department_id IN (SELECT id FROM department WHERE pastureId = ?)),?) www) ss
					 JOIN   sequence b ON b.seq < (LENGTH(ss.www) - LENGTH(REPLACE(ss.www,',',''))+1)
					WHERE ss.i =1
					GROUP BY ids				
					) 
	 ) 
	 OR buk.upkeepPerson= ? OR buk.useEmpId=? OR IFNULL((SELECT  MAX(r.`datarole`) FROM `user` u
   LEFT JOIN user_role ur
   ON ur.`user_id`= u.`id`
   LEFT JOIN role r
   ON r.`id` = ur.`role_id`
   LEFT JOIN role_menu rm
   ON r.`id` = rm.`role_id`
   LEFT JOIN menu m
   ON m.`id` = rm.`menu_id`
   WHERE u.`empid` =? AND m.`path`=? ),0) IN (2,3))
   
   AND (p.id= ? OR IFNULL((SELECT  MAX(r.`datarole`)  FROM `user` u
   LEFT JOIN user_role ur
   ON ur.`user_id`= u.`id`
   LEFT JOIN role r
   ON r.`id` = ur.`role_id`
   LEFT JOIN role_menu rm
   ON r.`id` = rm.`role_id`
   LEFT JOIN menu m
   ON m.`id` = rm.`menu_id`
   WHERE u.`empid` = ? AND m.`path`=? ),0) = 3)) AND
(p.`name`=? OR ?='现代牧业') AND (buk.upkeepCode LIKE CONCAT('%',?,'%')OR ?='') 
AND (e.`eqName` LIKE CONCAT('%',?,'%')OR ?='')
AND (e.`eqCode` LIKE CONCAT('%',?,'%')OR ?='')
AND (e.`departmentId`=? OR ?='')

AND (buk.`plantime` >=? AND buk.`plantime`<=?  OR ?='')
AND  buk.upkeepCode IS NOT NULL   AND (
   IF (?=0,buk.flowCompeleted IN (2,3,5) ,
        IF(?=1,buk.flowCompeleted=7,buk.flowCompeleted IN (4,6,8))
   )
    OR ? = ''
  )) qq
WHERE qq.statue=? OR ?=''
ORDER BY qq.plantime DESC, qq.upkeepCode  DESC