notice/doc/sql/repire/repire.sql

 SELECT
IFNULL(bpa1.cou,0) laidcou,
  eq.`specification`,
 DATE_FORMAT(mt.`chargeDate`,'%Y-%m-%d %H:%i:%s') chargeDate,
  mt.scores,
  mt.`chargeId`,
  mt.`checkResult`,
  mt.`dealTime`,
  eq.`departmentId`,
  mt.`details`,
  mt.`disposeId`,
  mt.`eqClassId`,
  mt.`eqCode`,
  mt.`eqId`,
  mt.`eqName`,
  mt.`id`,
  mt.`orderStatue`,
  DATE_FORMAT(mt.`orderTime`, '%Y-%m-%d %H:%i:%s') orderTime,
  mt.`pastureId`,
  mt.`phenomenId`,
  mt.`pickId`,
  mt.`positionId`,
  mt.`repairCode`,
  mt.`repairDept`,
  mt.`repairNote`,
  mt.`requesterId`,
  DATE_FORMAT(mt.`requestTime`,'%Y-%m-%d %H:%i:%s') requestTime,
  DATE_FORMAT(mt.`stopTime`,'%Y-%m-%d %H:%i:%s') stopTime,
  p.`name` pastureName,
  IF(bpa.cou  IS NULL,'未领用','已领用') LYStatue,
  IF(rf.cou  IS NULL,'未录入','已录入') LRStatue,
  mt.flowCompeleted SHStatue,
  d.`name` departmentName,
  d1.`name` repairDeptName,
  e.`empname` requesterName,
  e1.`empname` pickName,
 em2.`empname`  useChargePerson,
    em3.`empname`  chargePerson,
		em4.`empname` shutdownPerson,
		mt.shutdownReason,
		DATE_FORMAT(   mt.shutdownDate,'%Y-%m-%d %H:%i:%s') shutdownDate,
 DATE_FORMAT(   mt.useChargeDate,'%Y-%m-%d %H:%i:%s') useChargeDate,

mt.workflowNote
FROM
  maintain mt
  INNER JOIN pasture p
    ON p.`id` = mt.`pastureId`
  INNER JOIN department d1
    ON d1.`id` = mt.`repairDept`
  INNER JOIN emp e
    ON e.`id` = mt.`requesterId`
  LEFT JOIN emp e1
    ON e1.`id` = mt.`pickId`
  LEFT JOIN emp em2
      ON em2.id = mt.useChargeId
 LEFT JOIN emp em3
      ON em3.id = mt.chargeId
	LEFT JOIN emp em4
			ON em4.id = mt.shutdownPerson

  INNER JOIN equipment eq
    ON eq.`id` = mt.`eqId`
  LEFT JOIN department d
    ON d.`id` = eq.`departmentId`
  LEFT JOIN (SELECT RUCode,COUNT(id)cou FROM  bigpartapply GROUP BY  RUCode ) bpa
    ON bpa.`RUCode` = mt.`repairCode`
  LEFT JOIN (SELECT repairCode,COUNT(id)cou FROM  refuse GROUP BY  repairCode)rf
    ON rf.`repairCode` = mt.`repairCode`
 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` = mt.`repairCode`
WHERE


(((mt.requesterId= ? OR mt.pickId =?) 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 (eq.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 mt.pickId =? OR mt.requesterId= ? 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 (mt.repairCode LIKE CONCAT('%',?,'%')OR ?='')
AND (mt.`eqName` LIKE CONCAT('%',?,'%')OR ?='')
AND (mt.`eqCode` LIKE CONCAT('%',?,'%')OR ?='')
AND (mt.`orderStatue` =? OR ?='')
AND (DATE_FORMAT(mt.`requestTime`, '%Y-%m-%d')>=? AND DATE_FORMAT(mt.`requestTime`, '%Y-%m-%d')<=?  OR ?='')
AND (
   IF (?=0,mt.flowCompeleted IN (2,3) ,
        IF(?=1,mt.flowCompeleted=5,mt.flowCompeleted IN (4,6))
   )
    OR ? = ''
  )
ORDER BY mt.`id` DESC