复核分配复核台解析-待完善

--零货复核台分布示意
--复核台资料
SELECT a.Fhtbh AS '复核台编号',a.Fhtmch AS '复核台名称',a.fhthwgx AS '复核货位关系',b.ZCWBH AS '占存位编号',b.zhuangtai AS '是否占用' 
FROM Wms_fhtzl AS a
     INNER JOIN wms_nfhzcw AS b ON a.Fhtbh = b.fhtbh 
WHERE a.isdj='否'
ORDER BY a.Fhtbh,b.ZCWBH
--单据可用复核台，取第一个	  
SELECT aa.Fhtbh AS '复核台编号',aa.Fhtmch AS '复核台名称',ISNULL(ab.tms,0) AS '复核条目数',ROUND(ISNULL(ab.tms,0)*0.4,2) AS '优先级'
      ,CASE WHEN aa.Fhtbh <='30' THEN ISNULL(ac.fh_tms,0) ELSE 0 END AS '复核单据数',aa.lastmodifytime AS '最后使用时间' 
FROM Wms_fhtzl AS aa
     LEFT JOIN (
                SELECT Fhtbh,COUNT(*) AS tms 
                FROM pf_PHLRMX (NOLOCK) 
                WHERE is_fuhe='否' 
                GROUP BY Fhtbh) AS ab ON aa.Fhtbh = ab.Fhtbh
     LEFT JOIN (
                SELECT Fhtbh,COUNT(DISTINCT fhdjbh ) AS fh_tms  
                FROM pf_PHLRMX (NOLOCK) 
                WHERE is_fuhe='否' 
                GROUP BY Fhtbh) AS ac ON aa.Fhtbh = ac.Fhtbh
             
WHERE EXISTS( 
              SELECT 1 
              FROM pf_PHLRMX AS a (nolock)
                   INNER JOIN huoweizl AS b (nolock) ON a.hw = b.hw AND LEFT(b.type,1)='零'  
			  WHERE aa.fhthwgx LIKE '%'+ b.hwbh + '%'			        
			        AND a.duiydjbh ='XSGZDA00018776'
			 )
     AND EXISTS(
                SELECT 1 
                FROM wms_nfhzcw AS a
                WHERE a.fhtbh=aa.Fhtbh and a.zhuangtai='否'
               )
ORDER BY '优先级' ASC,'最后使用时间' ASC,aa.Fhtbh ASC 
--占存位，取第一个
SELECT * FROM wms_nfhzcw WHERE isdj='否' AND zhuangtai<>'是' AND fhtbh='06' ORDER BY ZCWBH 

--查询复核占存位状态为是，但未释放出来，导致分配不了复核台 
SELECT a.*
FROM wms_nfhzcw AS a
WHERE NOT EXISTS(SELECT * FROM pf_PHLRMX AS b WHERE a.fhtbh= b.fhtbh AND a.ZCWBH = b.zcwbh AND b.is_fuhe='否')
	  AND a.zhuangtai='是'
--更新释放复核台
/*	  
UPDATE a
	SET a.zhuangtai='否'
FROM wms_nfhzcw AS a
WHERE NOT EXISTS(SELECT * FROM pf_PHLRMX AS b WHERE a.fhtbh= b.fhtbh AND a.ZCWBH = b.zcwbh AND b.is_fuhe='否')
      AND a.zhuangtai='是'
*/