xdmy/doc/mcs/sql/getBigupkeepListAPP.sql

SELECT
  qq.*
FROM
  (SELECT
 IFNULL(bpa1.cou,0) laidcou,
    buk.id,
    buk.`eqId`,
 buk.pushbackId,
    buk.bigupkeeptemplateId,
    CONCAT(
      '名称：',
      but.`upkeepName`,
      '|保养级别：',
      but.`upkeepLevel`
    ) upNameLevel,
    buk.`upkeepCode`,
    buk.workflowNote,
   buk.scores,
   buk.chargeDate,
   buk.useChargeDate,
buk.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,
buk.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.`flowCompeleted` = 1 AND DATE_FORMAT(NOW(), '%Y-%m-%d') = buk.`plantime` or (buk.pushbackId<>0 and DATE_SUB(DATE_FORMAT( buk.`plantime`,'%Y-%m-%d'),INTERVAL -1 DAY)=DATE_FORMAT(NOW(), '%Y-%m-%d')AND  buk.`flowCompeleted` = 1 AND  buk.`flowCompeleted` = 1),
      1,
     if( buk.`flowCompeleted` = 1 AND DATE_FORMAT(NOW(), '%Y-%m-%d') > buk.`plantime`,0,2
      )
    ) statue,
    IF(bpa.cou  IS NULL,'未领用','已领用') LYStatue,
    IF(rf.cou  IS NULL,'未录入','已录入') LRStatue,
    buk.flowCompeleted  SHStatue,
    (select ifnull(device,0) from `user` where empId = ? ) isVideoBtnShow, # 录制视屏按钮
    case 
      when (select  id  from video where  upkeepId =buk.id) is null then '未录制'
      when (select  id  from video where  upkeepId =162283 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, # 录制地址
		(select  deviceId from device_information where empId = ?)	deviceId,# 设备id
		(select  `password` from device_information where empId = ?) pwd, #密码
		(select  uId from device_information where empId = ?) uId, # uid
		ifnull((select  DATE_FORMAT(createTime,'%Y-%m-%d')  from video where  upkeepId =buk.id),'') videoTime,
    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` = ? AND buk.`pastureId` =? and
buk.upkeepCode IS NOT NULL

) qq
WHERE IF(?=0,qq.SHStatue IN(4,6) OR (DATE_FORMAT(NOW(), '%Y-%m-%d') = qq.`plantime` AND qq.SHStatue = 1 )
OR (DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY), '%Y-%m-%d')  = qq.`plantime`  AND qq.SHStatue = 1 )
OR (qq.pushbackId <>0 AND DATE_SUB(DATE_FORMAT( qq.`plantime`,'%Y-%m-%d'),INTERVAL -1 DAY)=DATE_FORMAT(NOW(), '%Y-%m-%d') AND qq.SHStatue = 1) ,qq.SHStatue > 1)
ORDER BY qq.plantime DESC