package com.yihu.ehr.basic.org.dao;

import com.yihu.ehr.basic.org.model.OrgDept;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author HZY
 * @vsrsion 1.0
 * Created at 2017/2/15.
 */
public interface OrgDeptRepository extends PagingAndSortingRepository<OrgDept, Integer> {

    @Query("select dept from OrgDept dept where dept.parentDeptId = :parentDeptId order by dept.sortNo desc ")
    List<OrgDept> searchByParentDeptId(@Param("parentDeptId") Integer parentDeptId);

    @Query("select dept from OrgDept dept where dept.orgId = :orgId and dept.delFlag=0 order by dept.sortNo desc ")
    List<OrgDept> searchByOrgId(@Param("orgId") String orgId);

    @Query("select dept from OrgDept dept where dept.orgId = :orgId and dept.name=:name and dept.delFlag=0 order by dept.sortNo desc ")
    List<OrgDept> searchByOrgIdAndName(@Param("orgId") String orgId,@Param("name") String name);

    @Query("select dept from OrgDept dept where dept.orgId=?1 and dept.parentDeptId = ?2 and dept.delFlag = 0 order by dept.sortNo desc ")
    List<OrgDept> findByOrgIdAndParentDeptId(String orgId,Integer parentDeptId);

    @Query("select case when  max(dept.sortNo) is null then 1 else max(dept.sortNo) end from OrgDept dept where dept.parentDeptId = :parentDeptId")
    int searchParentIdOfMaxSortNo(@Param("parentDeptId") Integer parentDeptId);

    @Query("select dept.id from OrgDept dept where dept.code = :code and dept.delFlag = 0")
    List<Integer> findIdByCode(@Param("code") String code);

    @Query("select dept from OrgDept dept where dept.orgId = :orgId and dept.code=:code and dept.delFlag=0 order by dept.sortNo desc ")
    List<OrgDept> searchByOrgIdAnddeptCode(@Param("orgId") String orgId,@Param("code") String code);
}
