package com.example.dahua.dao;

import com.example.dahua.bean.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

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

@Repository
@Mapper
public interface UserDao {

    @Select(" select student_num from SZ_V_School_Student where school_id = #{school_id} and studentcode = #{studentcode}")
    public String getStudentNum(@Param("school_id") String school_id, @Param("studentcode") String studentcode);

    @Select("select school_id from SZ_Attendance where clint_id = #{clint_id} ")
    String getSchoolIdbyClint_id( @Param("clint_id") String clint_id);

    @Select("select * from SZ_Attendance where school_id = #{schoolId} and clint_type = 29")
    List<String> selectDeviceBySchoolId(@Param("schoolId") Integer schoolId);
    /**
     * 获取设备出入类型
     * @param clint_id
     * @return
     */
    @Select("select * from SZ_Attendance where clint_id = #{clint_id} ")
    AttendanceBean getTypeByClint_id( @Param("clint_id") String clint_id);

    @Select("select * from SZ_Attendance where school_id = #{school_id} and clint_type = #{clint_type}")
    List<AttendanceBean> getAttendanceBeans(@Param("school_id") String school_id, @Param("clint_type") String clint_type);

    @Select("select * from SZ_Attendance where school_id = #{school_id} and clint_type = #{clint_type} and clint_id = #{clint_id}")
    List<AttendanceBean> getAttendanceBeanWithId(@Param("school_id") String school_id, @Param("clint_type") String clint_type, @Param("clint_id") String clint_id);

    @Select(" select Top(1)* from SZ_V_School_Student where school_id = #{school_id} and studentcode = #{studentcode}")
    UserInfoBean getUserInfo(@Param("school_id") String school_id, @Param("studentcode") String studentcode);

    @Select("select * from SZ_V_School_Student where school_id = 1066 and class_id between 78506 and 78514")
    List<UserInfoBean> getStuUserInfos();

    @Select(" select Top(1)* from SZ_V_School_Teacher where school_id = #{school_id} and num = #{num}")
    TeacherBean getTeacher(@Param("school_id") String school_id, @Param("num") String num);

    @Select(" select Top(1)* from SZ_V_School_Teacher where teacher_id = #{teacher_id}")
    TeacherBean getTeacherWithId(@Param("teacher_id") String teacher_id);

    @Select(" select * from SZ_V_School_Teacher where school_id = 562 and name = #{name}")
    List<TeacherBean> getTeachersWithName(@Param("name") String name);

    /**
     * 记录用户和设备集编号对应关系
     *
     * @param user_id
     * @return
     */
    @Select("select top 1 recordNo from SZ_Student_RecoderNo where user_id = #{user_id} and deviceId = #{deviceId}")
    String getRecordNo(@Param("user_id") String user_id, @Param("deviceId") String deviceId);

    /**
     * 获取学生考勤类型
     * @param school_id
     * @param customerid
     * @return
     */
    @Select(" select Top(1) student_type from SZ_V_School_Student where school_id = #{school_id} and student_id = #{customerid}")
    String getStudentType(@Param("school_id") String school_id, @Param("customerid") String customerid);

    @Insert("insert into SZ_Student_RecoderNo values(#{user_id},#{recordNo},#{student_name},#{deviceId})")
    int saveRecordNo(@Param("user_id") String user_id, @Param("recordNo") int recordNo, @Param("student_name") String student_name, @Param("deviceId") String deviceId);

    @Select("select CardNum from HS_StudentCards where StudentId = #{StudentId} and CardType = #{CardType}")
    String getCardNum(@Param("StudentId") String StudentId, @Param("CardType") String CardType);

    @Delete("delete from SZ_Student_RecoderNo where user_id = #{user_id} and recordNo = #{recordNo}")
    void deleteRecordNo(@Param("user_id") String user_id, @Param("recordNo") int recordNo);

    @Update("update SZ_Student_RecoderNo set user_id = #{user_id} where deviceId = #{deviceId} and recordNo = #{recordNo}")
    void updateRecordNo(@Param("user_id") String user_id, @Param("recordNo") int recordNo, @Param("deviceId") String deviceId);


    @Select("select clint_id from SZ_Attendance  where clint_type = #{clint_type} and school_id = #{school_id}")
    List<String> getDeviceIds(@Param("clint_type") String clint_type, @Param("school_id") int school_id);

    @Select("select Top(1) * from HS_StudentUpdateCard where CustomerId = #{CustomerId} order by AddTime desc")
    StudentBean getStudentUpdate(@Param("CustomerId") String customerid);

    @Select("select school_name from SZ_School where school_id = #{school_id}")
    String getSchoolName(@Param("school_id") String school_id);

    @Select(" select studentcode from SZ_V_School_Student where student_id = #{customerid}")
    String getStudentCode(@Param("customerid") String customerid);

    @Select(" select top 1* from SZ_V_School_Student where student_id = #{student_id}")
    UserInfoBean getStudentWithid(@Param("student_id") String student_id);

    @Select(" select top 1* from SZ_V_School_Student where user_id = #{userId}")
    UserInfoBean getStudentByUserId(@Param("userId") String userId);

    @Select(" select * from SZ_V_School_Student where student_num = #{student_num} and name = #{name}")
    UserInfoBean getStudent(@Param("student_num") String student_num, @Param("name") String name);

    @Select(" select * from SZ_V_School_Student where school_id = #{school_id} and sex = #{sex} and student_type = #{student_type}")
    List<UserInfoBean> getStudentWithSchoolId(@Param("school_id") String school_id, @Param("sex") String sex, @Param("student_type") String student_type);

    @Select(" select * from SZ_V_School_Student where school_id = #{school_id}")
    List<UserInfoBean> getStudentBySchoolId(@Param("school_id") Integer school_id);

    @Select("select * from SZ_V_School_Student where school_id = 479 and student_type = 2")
    List<UserInfoBean> getUsers();

    @Select("select * from SZ_V_School_Student where school_id = 479 and student_type = 2 and student_id in(select  StudentId from ss_roomnumber " +
            "where SchoolId = 479 and Pid in(select id from SS_Room where SchoolId = 479 and pid =10287))")
    List<UserInfoBean> getUserInfos();

    @Select("select clint_id from SZ_Attendance where school_id = #{school_id} and clint_type = 4 ")
    List<String> getAllDecWithSch(@Param("school_id") int school_id);

    @Select("select * from SZ_Attendance where clint_type = #{clint_type} and school_id = #{school_id}")
    List<AttendanceBean> selectDeviceWithschool_id(@Param("clint_type") String clint_type, @Param("school_id") String school_id);

    @Select("select * from SZ_Attendance where school_id = #{school_id}")
    List<AttendanceBean> getAllDevices(@Param("school_id") String school_id);

    @Select("select * from SZ_V_School_Student where school_id = #{school_id} and len(photo)>0 and len(student_num)>0")
    List<StudentBean> getAllStudentsWithSchoolId(@Param("school_id") String school_id);


    @Select("select Top(1) user_id from SZ_V_School_Student where student_num = #{student_num}")
    String getUerIdWithNum(@Param("student_num") String student_num);

    @Select("select StudentId from SS_RoomNumber where Pid in (select id from SS_Room where SchoolId = 479)")
    List<String> getStudentIds();

    @Select("select Top(1) * from SZ_V_Card where num=#{num}")
    CardBean getCards(@Param("num") String num);

    @Select("select clint_id from SZ_Attendance where school_id = #{schoolId} and (clint_type = 8 or clint_type = 9 or clint_type = 10)")
    List<String> getClintIds(@Param("schoolId") String schoolId);

    @Select("select Top(1) school_id from SZ_V_School_Student where student_num = #{cardNum}")
    String getSchoolIdWidthCardNum(@Param("cardNum") String cardNum);

    @Select("select Top(1) PlaceId from XA_PlaceAttendance where KaoQinAttendance = #{qiandaoDevId}")
    String getPlaceIdWithKaoqinDevid(@Param("qiandaoDevId") String qiandaoDevId);

    @Select("select Top(1) PlaceId from XA_PlaceAttendance where KanBanAttendance = #{kanBanDevId}")
    String getPlaceIdWithKanBanDevid(@Param("kanBanDevId") String kanBanDevId);

    @Select("select KanBanAttendance from XA_PlaceAttendance where PlaceId = #{PlaceId} and (len(KanBanAttendance)>0 and KanBanAttendance is not null)")
    List<String> getKanBanIdWithPlaceId(@Param("PlaceId") String placeId);

    @Select("select user_id,studentcode from SZ_V_School_Student where school_id = 27")
    List<StudentBean> getStudentUserIdsWith();

    @Select(" select * from SZ_V_School_Teacher where school_id = #{school_id}")
    List<TeacherBean> getTeachers(@Param("school_id") String school_id);

    @Select("select * from SZ_User where user_id =#{userId} and state = 1")
    void getUserWidthUserId(@Param("userId") String userId);

    @Select("select * from SZ_V_School_Student where user_id = #{userId}")
    List<StudentBean> getStudentWithUserId(@Param("userId") String userId);

    @Select("select * from SZ_V_School_Teacher where user_id = #{userId}")
    List<TeacherBean> getTeaWithUserId(@Param("userId") String userId);

    @Select("select Top(1) user_id from SZ_V_School_Student where studentcode = #{studentcode}")
    String getStuWithCode(@Param("studentcode") String code);

    @Select("select top(1) num from SZ_V_School_Teacher where teacher_num=#{num}")
    String getTeaNumWithCard(@Param("num") String num);

    @Select("select clint_type from SZ_Attendance where clint_id = #{clint_id}")
    String getClintType(@Param("clint_id") String deviceID);

    @Select("select v.* from SZ_Student as s inner join SZ_V_School_Student as v on s.student_id = v.student_id where s.intime>'2020-09-01' and school_id = 654 order by student_id desc ")
    List<StudentBean> getStus();

    @Insert("insert into HS_StudentUpdateCard(UserId,CustomerId,StudentType,UserType,Name,ClassId,ClassName,Card,SchoolId,IsNew,UpdateType,AddTime,Sex,StudentCode,mobile) values (" +
            "#{UserId},#{CustomerId},#{StudentType},#{UserType},#{Name},#{ClassId},#{ClassName},#{Card},#{SchoolId},#{IsNew},#{UpdateType},#{AddTime},#{Sex},#{StudentCode},#{mobile})")
    void insertUpdateCard(@Param("UserId")String UserId,@Param("CustomerId")String CustomerId,@Param("StudentType")String StudentType,@Param("UserType")String UserType,
                          @Param("Name")String Name,@Param("ClassId")String ClassId,@Param("ClassName")String ClassName,@Param("Card")String Card,@Param("SchoolId")String SchoolId,
                          @Param("IsNew")String IsNew,@Param("UpdateType")String UpdateType,@Param("AddTime")String AddTime,@Param("Sex")String Sex,@Param("StudentCode")String StudentCode,
                          @Param("mobile")String mobile);

    @Select("select Top(1)* from SZ_V_School_Teacher where num = #{num} and school_id = #{schoolId}")
    StudentBean getTeacherWithstudentcode(@Param("num") String num, @Param("schoolId") String schoolId);

    @Select("select Top(1)* from SZ_V_School_Student where studentcode = #{studentcode} and school_id = #{schoolId}")
    StudentBean getStudentWithstudentcode(@Param("studentcode") String studentcode, @Param("schoolId") String schoolId);

    @Update("update SZ_User set face = #{httpUrl} where user_id =(\n" +
            "    select user_id\n" +
            "    from SZ_V_School_Student\n" +
            "    where studentcode = #{studentcode} and school_id = #{school_id}\n" +
            ")")
    void updateStu(@Param("studentcode") String studentCode,@Param("httpUrl") String httpUrl,@Param("school_id")String school_id);

    /**
     * 获取学生信息
     * @param cardNum
     * @return
     */
    @Select(" select school_id as schoolId , class_id as classId ,name ,student_id as studentId from\n" +
            "             (select top 1 b.school_id, b.class_id, b.name, student_id from SZ_V_Card a\n" +
            "             inner join SZ_V_School_Student b on a.user_id = b.student_id\n" +
            "             and b.role_state = 1 where a.type = 2  and a.num = #{cardNum}\n" +
            "             union\n" +
            "             select top 1 b.school_id, 0 as class_id, b.name,b.teacher_id as student_id from SZ_V_Card a\n" +
            "             inner join SZ_V_School_Teacher b on a.user_id = b.teacher_id\n" +
            "             and b.role_state = 1 where a.type = 0  and a.num = #{cardNum}\n" +
            "             ) x ")
    List<User> selectUserByCardNum(@Param("cardNum") String cardNum);

    /**
     * 获取卡号
     * @param cardNo
     * @return
     */
    @Select("select Top(1) StudentNum from HS_LeaveExaminePassList where StudentNum= #{cardNo} and GETDATE() > LeaveTime and GETDATE() < ReturnTime")
    String checkLeave(@Param("cardNo") String cardNo);

    /**
     * 获取学生父母电话
     * @param cardNo
     * @return
     */
    @Select("select school_id , ParentMobile , name from SZ_V_School_Student where student_num = #{cardNo}")
    Message selectByParentMobile(@Param("cardNo") String cardNo);

    /*************************************** 下发用户信息、人脸、卡、权限操作查询 add by xuquan  ************************************/

    @Select(" select Top(1)* from SZ_V_School_Student where school_id = #{school_id} and user_id = #{userId}")
    UserInfoBean getUserInfoByUserId(@Param("school_id") String school_id,@Param("userId") String userId);

    @Select(" select Top(1)* from SZ_V_School_Teacher where school_id = #{school_id} and user_id = #{userId}")
    TeacherBean getTeacherByUserId(@Param("school_id") String school_id, @Param("userId") String userId);

    /*************************************** 下发用户信息、人脸、卡、权限操作查询 add by xuquan  ************************************/


    /***************************************   仅供测试，下发失败使用接口  start ************************************/

    @Insert("insert into SZ_Student_RecoderNo values(#{user_id},#{recordNo},#{student_name},#{deviceId})")
    int saveRecordNoTest(@Param("user_id") String user_id, @Param("recordNo") int recordNo, @Param("student_name") String student_name, @Param("deviceId") String deviceId);

    @Delete("delete from SZ_Student_RecoderNo where user_id = #{user_id} and deviceId = #{deviceId}")
    void deleteRecordNoTest(@Param("user_id") String user_id,@Param("deviceId") String deviceId);

    @Select("select recordNo from SZ_Student_RecoderNo where student_name = #{student_name} and user_id = #{user_id} and deviceId =#{deviceId}")
    List<String> getRecordNoTest(@Param("student_name") String name,@Param("user_id") String user_id,@Param("deviceId") String deviceId);

    @Select("select Top(1) user_id from SZ_V_School_Student where student_num = #{student_num} and school_id = #{school_id}")
    String getUerIdWithNumTest(@Param("student_num") String student_num,@Param("school_id") Integer schoolId);

    @Select("select * from SZ_V_School_Student where school_id = #{school_id}")
    List<UserInfoBean> getUserIdTest(@Param("school_id") String schoolId);

    @Select("select clint_id from SZ_Attendance where school_id = #{school_id} and clint_type = 22 ")
    List<String> getAttendanceTest(@Param("school_id") String schoolId);

    /***************************************   仅供测试，下发失败使用接口  end ************************************/

    @Select("select Top(1)* from SZ_V_School_Student where student_num = #{card} and school_id = #{schoolId}")
    StudentBean getStudentWithCard(@Param("card") String card,@Param("schoolId") Integer schoolId);

    @Select("select Top(1)* from SZ_V_School_Teacher where teacher_num = #{teacher_num} and school_id = #{schoolId}")
    StudentBean getTeacherWithCard(@Param("teacher_num") String teacher_num,@Param("schoolId") Integer schoolId);

    List<StudentBean> getStudentList(@Param("schoolId") Integer schoolId,@Param("studentType")Integer studentType,@Param("sex") List<Integer> sex);

    List<StudentBean> getTeacherList(@Param("schoolId") Integer schoolId);

    List<DeviceAuthRecord> getAuthRecord(@Param("schoolId") Integer schoolId,@Param("studentType")Integer studentType);

    List<AttendanceBean> queryClintList(@Param("schoolId") Integer schoolId,@Param("deviceId")String deviceId);

    List<String> queryStudentIdList(@Param("schoolId") Integer schoolId,@Param("roomId")Integer roomId);

    List<StudentBean> getStudentByClassId(@Param("schoolId") int schoolId,@Param("classIds") List<Integer> classIds);

    List<Integer> getGroupByClassId(@Param("classId") Integer classId);
}