poDept.md

queryByCondition
===
    

	select 
    @pageTag(){
    t.*
    @}
    from core_org t
    where 1=1
    	@if(!isEmpty(name)){
    		and t.name like #'%'+name+'%'#
    	@}
    ORDER BY ORG_ORDER ASC

queryByParent
===

    select 
    @pageTag(){
    t.*
    @}
    from po_dept t
    where 1=1
    and t.PARENT_CODE = #string#
    	ORDER BY t.SORT


batchDelPoDeptByIds
===

* 批量删除

    delete from po_dept where ID  in( #join(ids)#)
    
queryPoDeptByPoPerson
===

    select * from po_dept where ID  in(
    		select PO_DEPT_ID from po_dept_person 	where PO_PERSON_ID in (
    			select PO_PERSON_ID from po_person where 	PO_PERSON_ID = 				#poPersonId#
    		)
    )
    
statHspByCondition
===

    select 
    @pageTag(){ 
     hsp.code hspId,hsp.name,	sum( CASE WHEN d.DEPT_LEVEL = '0' THEN 1 ELSE 0 END ) deptyi,
	sum( CASE WHEN d.DEPT_LEVEL = '1' THEN 1 ELSE 0 END ) depter,
	sum(case when d.DEPT_CLASSFY='1' then 1 else 0 end) ywcnt,
	sum(case when d.DEPT_CLASSFY='0' then 1 else 0 end) zncnt,
	sum(d.CHK_STAFF_CNT) chkstaffcnt,sum(d.CHK_PRINCIPAL_CNT) chkprincipalcnt,sum(d.CHK_DEPUTY_CNT) chkdeputycnt,
	s.instaffcnt,
	s.outstaffcnt,
	s.leavecnt,
	s.retirecnt,
	zzcnt,
	fzcnt 
	@}
	from (  SELECT * FROM core_org WHERE parent_org_id IN ( SELECT id FROM core_org WHERE CODE = 'hospital' ) ) hsp
	LEFT JOIN po_dept d ON d.HSP_ID = hsp.
	CODE LEFT JOIN (
	SELECT
		pp.HSP_ID,
		sum( CASE WHEN isf.job_level = '4' THEN 1 ELSE 0 END ) zzcnt,
		sum( CASE WHEN isf.job_level = '5'  THEN 1 ELSE 0 END ) fzcnt 
	FROM
		po_person pp
		INNER JOIN po_person_instaff isf ON isf.po_person_id = pp.po_person_id 
		LEFT JOIN moveout_info mv on pp.PO_PERSON_ID=mv.PO_PERSON_ID
	WHERE
		pp.STATUS = '1' 
		AND  ISNULL(mv.PO_PERSON_ID)
	GROUP BY
		HSP_ID 
	) p ON hsp.CODE = p.HSP_ID
	LEFT JOIN (
	SELECT
		pp.HSP_ID,
		sum( CASE WHEN pp.person_type = '1' THEN 1 ELSE 0 END ) instaffcnt,
		sum( CASE WHEN pp.person_type = '2' THEN 1 ELSE 0 END ) outstaffcnt,
		sum( CASE WHEN pp.person_type = '3' THEN 1 ELSE 0 END ) leavecnt,
		sum( CASE WHEN pp.person_type = '4' THEN 1 ELSE 0 END ) retirecnt 
	FROM
		po_person pp 
			LEFT JOIN moveout_info mv on pp.PO_PERSON_ID=mv.PO_PERSON_ID
	WHERE
		pp.STATUS = '1' 
			AND  ISNULL(mv.PO_PERSON_ID)
	GROUP BY
		HSP_ID 
	) s ON hsp.CODE = s.HSP_ID 
	where 1=1
		AND ISNULL(p.HSP_ID)=0 and LENGTH(trim(p.HSP_ID))>0
	@if(!isEmpty(hspName)){
    		and hsp.name like #'%'+hspName+'%'#
    	@}
	group by p.HSP_ID 
	order by hsp.org_order
    
statDeptByCondition
===

    select 
    @pageTag(){ 
    hsp.name hspname,p.*
    @}
	from core_org hsp
	inner join 
	(select d.HSP_ID,d.id,d.name,d.DEPT_CLASSFY deptclassfy,d.CHK_STAFF_CNT chkstaffcnt,d.CHK_PRINCIPAL_CNT chkprincipalcnt,
	d.CHK_DEPUTY_CNT chkdeputycnt,d.STANDARD_CODE stcode,pd.NAME stname,	d.DEPT_LEVEL,
	sum(case when pp.person_type='1' then 1 else 0 end) instaffcnt,
	sum(case when pp.person_type='2' then 1 else 0 end) outstaffcnt,
	sum(case when pp.person_type='3' then 1 else 0 end) leavecnt,
	sum(case when pp.person_type='4' then 1 else 0 end) retirecnt,
	sum(case when pp.job_level='2' then 1 else 0 end) zzcnt,
	sum(case when pp.job_level='1' or pp.job_level='3' then 1 else 0 end) fzcnt
	from po_dept d left join pro_dept pd on d.STANDARD_CODE = pd.code 
	left join 
	po_dept_person pdp on pdp.po_dept_id =d.id 
	left join
	(select pp.*,isf.job_level from po_person pp inner join po_person_instaff isf on isf.po_person_id = pp.po_person_id where status='1') pp
	on pp.po_person_id = pdp.po_person_id
	where d.parent_code !='0' and d.id!='0'
	@if(!isEmpty(standardName)){
    		and pd.name like #'%'+standardName+'%'#
    	@} 
    	@if(!isEmpty(deptClassfy)){
    		and d.DEPT_CLASSFY = #deptClassfy#
    	@}
    	@if(!isEmpty(name)){
    		and d.name like #'%'+name+'%'#
    	@} 
    	 	@if(!isEmpty(deptLevel)){
    		and d.DEPT_LEVEL = #deptLevel#
    	@} 
	group by d.id,d.name,d.DEPT_CLASSFY,d.CHK_STAFF_CNT,d.CHK_PRINCIPAL_CNT,d.CHK_DEPUTY_CNT,d.STANDARD_CODE,pd.NAME,d.hsp_id) p
	on hsp.code = p.HSP_ID
	where 1=1
	@if(!isEmpty(hspId)){
    		and hsp.code = #hspId#
    	@}