package thyyxxk.webserver.dao.his.ybkf;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface OutpatientCoordinationDao {

    @Select(" select convert(varchar(100), request_day, 112) rq, cast(count(1) as varchar) rs " +
            " from mzy_reqrec with(nolock) where cancel_mark=0 " +
            " and convert(varchar(6), request_day, 112) = #{month} " +
            " and unit_code != '1500010' " +
            " group by convert(varchar(100), request_day, 112) ")
    List<Map<String, String>> selectRegisterPeople(@Param("month") String month);

    @Select(" select cast(count(distinct t.pat_no) as varchar) as rs, convert(varchar(100), t.create_datetime, 112) rq " +
            " from t_si_pat_info t, mzy_reqrec m " +
            " where t.pat_no = m.patient_id " +
            " and t.med_type='11' " +
            " and t.insutype='310' " +
            " and m.cancel_mark=0 " +
            " and convert(varchar(100), t.create_datetime, 112) = convert(varchar(100), m.request_day, 112) " +
            " and convert(varchar(6), t.create_datetime, 112) = #{month} " +
            " group by CONVERT(varchar(100), t.create_datetime, 112) ")
    List<Map<String, String>> selectRegisterOutpatientCoordinationPeople(@Param("month") String month);

    @Select(" select cast(count(1) as varchar) rs, t1.rq from " +
            " (select pat_no, convert(varchar(100), setl_time, 112) rq " +
            " from t_si_setlinfo where med_type='11' and insutype='310' and revoked=0 " +
            " and convert(varchar(6), setl_time, 112) = #{month} ) t1, " +
            " (select distinct patient_id, convert(varchar(100), request_day, 112) rq " +
            " from mzy_reqrec with(nolock) " +
            " where cancel_mark=0 " +
            " and convert(varchar(6), request_day, 112) = #{month} ) t2 " +
            " where t1.pat_no = t2.patient_id " +
            " and t1.rq = t2.rq " +
            " group by t1.rq ")
    List<Map<String, String>> selectUsedOutpatientCoordinationPeople(@Param("month") String month);

    @Select(" select cast(sum(tt.rs) as varchar) rs, tt.rq, tt.code, tt.dept from ( " +
            " select count(1) rs, t1.rq, " +
            " code = (select d.code from a_employee_mi a, zd_unit_code d WHERE a.code = t1.staff_id and d.code = a.dept_code ), " +
            " dept = (select d.name from a_employee_mi a, zd_unit_code d WHERE a.code = t1.staff_id and d.code = a.dept_code ) " +
            " from " +
            " (select pat_no, convert(varchar(100), setl_time, 112) rq, staff_id " +
            " from t_si_setlinfo where med_type='11' and insutype='310' and revoked=0 " +
            " and convert(varchar(6), setl_time, 112) = #{month} ) t1, " +
            " (select distinct patient_id, CONVERT(varchar(100), request_day, 112) rq " +
            " from mzy_reqrec with(nolock) " +
            " where cancel_mark=0 " +
            " and convert(varchar(6), request_day, 112) = #{month} ) t2 " +
            " where t1.pat_no = t2.patient_id " +
            " and t1.rq = t2.rq " +
            " group by t1.rq,t1.staff_id) tt" +
            " group by tt.rq, tt.code, tt.dept ")
    List<Map<String, String>> selectUsedOutpatientCoordinationDeptPeople(@Param("month") String month);

}
