xdmy/doc/wxload/psql.sql

select
(select 
group_concat(
 distinct concat_ws(',',positionName,appearanceName,failureCause,treatmentMethod )
  order by repirsTime SEPARATOR ' | '
) from maintain_appearance where maintainid =mt.id  ) mas,
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.`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= 3 OR mt.pickId =3) 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` = 3 AND m.`path`='Repair' ),0) IN (1,2,3))

   AND (eq.departmentId IN (SELECT id FROM department WHERE pastureId =18 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` = 3 AND m.`path`='Repair') AND department_id IN (SELECT id FROM department WHERE pastureId = 18)),3) 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 =3 OR mt.requesterId= 3 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` =3 AND m.`path`='Repair' ),0) IN (2,3))
   AND (p.id= 18 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` = 3 AND m.`path`='Repair' ),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')>='2022-03-02' AND DATE_FORMAT(mt.`requestTime`, '%Y-%m-%d')<='2022-04-02'  OR '2022-03-02'='')
AND (
   IF (''=0,mt.flowCompeleted IN (2,3) ,
        IF(''=1,mt.flowCompeleted=5,mt.flowCompeleted IN (4,6))
   )
    OR '' = ''
  )