package com.sincere.haikangface.dao;

import com.sincere.haikangface.bean.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserDao {

    /**
     * 没有记录取最新十条下发
     *
     * @return
     */
    @Select("select top (10) * from HS_StudentUpdateCard order by AddTime desc")
    List<StudentBean> getStudents();

    @Select("select student_num,name,studentcode from SZ_V_School_Student where school_id = 479")
    List<StudentBean> getStu();

    /**
     * 获取所有的学生
     *
     * @return
     */
    @Select("select * from HS_StudentUpdateCard where ID > #{id} and StudentType = #{StudentType} order by AddTime desc")
    List<StudentBean> getAllStudents(@Param("id") long id, @Param("StudentType") int StudentType);
//    @Select("select * from HS_StudentUpdateCard where ID > #{id} order by AddTime desc")
//    List<StudentBean> getAllStudents(@Param("id")long id);

    @Update("update SZ_V_School_Student set name = #{name} where student_id = #{student_id}")
    void updateTest(@Param("student_id") String student_id, @Param("name") String name);

    @Select("select Top(1) student_num from SZ_V_School_Student where student_id = #{student_id} and role_state = 1")
    String getStudent_Num(@Param("student_id") String student_id);

//    @Select("select * from TK_QuestionBar")
//    List<TK_Question> getQuestion();

    @Insert("insert into TK_Question (Question,Qtype,Answer,CorrectAnswer,Analysis,State,Intime,ExamineFlag,ExamineUserId, " +
            "CreateUserId,SubjectId,SuggestionTime,DifficulteId,KnowledgeId,TestId,ChapterId,\n" +
            "GradeId,SourceId,OrderId,SId,StemId,AutomaticCorrection,SchoolId) values(#{Question},#{Qtype},#{Answer},#{CorrectAnswer},#{Analysis}" +
            ",#{State},#{Intime},#{ExamineFlag},#{ExamineUserId},#{CreateUserId},#{SubjectId},#{SuggestionTime},#{DifficulteId},#{KnowledgeId},#{TestId}" +
            ",#{ChapterId},#{GradeId},#{SourceId},#{OrderId},#{SId},#{StemId},#{AutomaticCorrection},#{SchoolId})")
    int insertTK(@Param("Question") String Question, @Param("Qtype") String Qtype, @Param("Answer") String Answer, @Param("CorrectAnswer") String CorrectAnswer,
                 @Param("Analysis") String Analysis, @Param("State") String State, @Param("Intime") String Intime, @Param("ExamineFlag") String ExamineFlag, @Param("ExamineUserId") String ExamineUserId,
                 @Param("CreateUserId") String CreateUserId, @Param("SubjectId") String SubjectId, @Param("SuggestionTime") String SuggestionTime, @Param("DifficulteId") String DifficulteId,
                 @Param("KnowledgeId") String KnowledgeId, @Param("TestId") String TestId, @Param("ChapterId") String ChapterId, @Param("GradeId") String GradeId, @Param("SourceId") String SourceId,
                 @Param("OrderId") String OrderId, @Param("SId") String SId, @Param("StemId") String StemId, @Param("AutomaticCorrection") String AutomaticCorrection, @Param("SchoolId") String SchoolId);

    @Select("select * from SZ_User where user_id = #{user_id}")
    UserBean getUser(@Param("user_id") String user_id);


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

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

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

    @Select("select * from HS_ResFiles where UserId in(select user_id from SZ_V_School_Teacher where school_id = 1021)")
    List<FileBean> getFileRes();

    @Select("select * from HS_ResFiles where UserId = 'zy405704' and id >= 4476 ")
    List<FileBean> getFIlesReses();

    //
    @Select("(select Name from HS_ResGrade where SchoolId = 350 and id = #{id})")
    String getGradeName(@Param("id") String id);

    //
    @Select("select id from HS_ResGrade where SchoolId = 349 and name = #{name}")
    String getGradeId(@Param("name") String name);

    @Select("select * from HS_ResGrade where  SchoolId = 350")
    List<HSGrade> getGrades();


    @Insert("insert into HS_ResGrade values (#{name},#{addTime},#{status},#{userId},#{schoolId},#{shortName},#{schoolYear},#{year},#{remark},#{isFinish})")
    void addGrade(@Param("name") String name, @Param("addTime") String addTime, @Param("status") int status, @Param("userId") String userId,
                  @Param("schoolId") int schoolId, @Param("shortName") String shortName, @Param("schoolYear") int schoolYear, @Param("year") int year, @Param("remark") String remark, @Param("isFinish") int isFinish);

    //
    @Select("select * from HS_ResBook where SchoolId = 350")
    List<HS_ResBook> getHSResBook();

    @Insert(" insert  into HS_ResBook values (#{name},#{addTime},#{status},#{userId},#{schoolId},#{press},#{pressTimes},#{price})")
    void addHSResBook(@Param("name") String name, @Param("addTime") String addTime, @Param("status") String status, @Param("userId") String userId, @Param("schoolId") String schoolId, @Param("press") String press,
                      @Param("pressTimes") String pressTimes, @Param("price") String price);

    @Select("select * from SZ_Subject where school_id =350 and Status = 1")
    List<SubjectBean> getSubjects();

    @Insert("insert into SZ_Subject values(#{school_id},#{subject_name},#{intime},#{subId},#{isElective},#{status},#{typeId},#{maxPerson},#{shortName})")
    void addSubjectbean(@Param("school_id") int school_id, @Param("subject_name") String subject_name, @Param("intime") String intime,
                        @Param("subId") int subId, @Param("isElective") int isElective, @Param("status") int status, @Param("typeId") int typeId, @Param("maxPerson") int maxPerson, @Param("shortName") String shortName);

    @Select("select Top(1)subject_id from SZ_Subject where subject_name = #{subject_name} and school_id = 349")
    String getSubjectId(@Param("subject_name") String subject_name);

    @Select("select Top(1) subject_name from SZ_Subject where subject_id = #{subject_id} and  school_id = 350")
    String getSubjectName(@Param("subject_id") int subject_id);

    @Select("select Top(1) name from HS_ResBook where SchoolId = 350 and id = #{id}")
    String getBookName(@Param("id") int bookId);

    @Select("select Top(1) id from HS_ResBook where SchoolId = 349 and name = #{name}")
    int getBookId(@Param("name") String bookName);

    @Select("select id from HS_ResBookVersion where SchoolId = #{SchoolId}")
    List<String> getbookversionId(@Param("SchoolId") String SchoolId);

    @Select("select * from HS_ResChapter where SchoolId = 350")
    List<HS_ResChapter> getChapters();

    //
    @Select("  select * from HS_ResBookVersion where SchoolId = 350")
    List<HS_ResBookVersion> getResBookVersion();

    @Insert("insert into HS_ResBookVersion values(#{bookId},#{gradeId},#{subjectId},#{isUse},#{state},#{intime},#{schoolId})")
    void addResBookVersion(@Param("bookId") int bookId, @Param("gradeId") int gradeId, @Param("subjectId") int subjectId, @Param("isUse") int isUse, @Param("state") int state,
                           @Param("intime") String intime, @Param("schoolId") int schoolId);

    @Insert("insert into HS_ResChapter values(#{name},#{subjectId},#{gradeId},#{pid},#{jiaoCaiId},#{bookVersionId},#{sort},#{type},#{addTime},#{status},#{userId},#{schoolId})")
    void addChapter(@Param("name") String name, @Param("subjectId") int subjectId, @Param("gradeId") int gradeId, @Param("pid") int pid, @Param("jiaoCaiId") int jiaoCaiId, @Param("bookVersionId") int bookVersionId,
                    @Param("sort") int sort, @Param("type") int type, @Param("addTime") String addTime, @Param("status") int status, @Param("userId") String userId, @Param("schoolId") int schoolId);

    @Select("Select Top(1)name from HS_ResChapter where id = #{pid} ")
    String getChapterName(@Param("pid") int pid);

    @Select(" Select  Top(1)id from HS_ResChapter where name=#{name} and SchoolId = 349")
    String getChapterId(@Param("name") String name);

    @Update("update HS_ResChapter set Pid = #{pid} where SchoolId = 349 and userId = #{userId} and name = #{name}")
    void updateChapter(@Param("userId") String userId, @Param("name") String name, @Param("pid") String chapterId);

    @Select("select id from HS_ResBook where SchoolId = 885 and name =(select name from HS_ResBook where  SchoolId = 1021 and Id = #{id})")
    String getJiaoCaiId(@Param("id") int id);

    @Select("select id from HS_ResCatalog where SchoolId = 885 and name = #{name}")
    String getResCatalog(@Param("name") String name);

    @Select("select name from HS_ResCatalog where SchoolId = 1021 and id = #{id}")
    String getResCatalogName(@Param("id") String id);

    @Select("select id from HS_ResType where SchoolId = 885 and name = #{name}")
    String getResType(@Param("name") String name);

    @Select("select name from HS_ResType where SchoolId = 1021 and id = #{id}")
    String getResTypeName(@Param("id") String id);

    //
    @Insert("insert into HS_ResFiles values(#{name},#{catalogId},#{typeId},#{gradeId},#{subjectId},#{jiaoCaiId},#{charpId},#{fileUrl},#{descript},#{keywords}" +
            ",#{addTime},#{status},#{userId},#{targetUrl},#{clicks},#{userList},#{isShare},#{charpId1},#{knowledgeId})")
    void addFiles(@Param("name") String name, @Param("catalogId") String catalogId, @Param("typeId") int typeId, @Param("gradeId") int gradeId,
                  @Param("subjectId") int subjectId, @Param("jiaoCaiId") int jiaoCaiId, @Param("charpId") int charpId, @Param("fileUrl") String fileUrl,
                  @Param("descript") String descript, @Param("keywords") String keywords, @Param("addTime") String addTime, @Param("status") int status,
                  @Param("userId") String userId, @Param("targetUrl") String targetUrl, @Param("clicks") int clicks, @Param("userList") String userList, @Param("isShare") int isShare,
                  @Param("charpId1") int charpId1, @Param("knowledgeId") int knowledgeId);

    @Select("select *\n" +
            "from SZ_V_School_Student where name = #{name} and school_id = #{school_id}")
    List<StudentBean> getAllStudentsWithSchoolId(@Param("school_id") String school_id, @Param("name") String name);

    @Select("select * from SZ_V_School_Student where school_id = #{school_id} and student_type = 2")
    List<StudentBean> getAllStuWithSchoolId(@Param("school_id") String school_id);

    @Select(" select * from SZ_V_School_Student where school_id = 562 and student_num is not null and photo is not  null and len(photo)>0")
    List<StudentInfo> getAllStus();

    @Select("select user_id\n" +
            "from SZ_V_School_Student where school_id = 13 and student_type  =2 and len(student_num)=0\n")
    List<String> getUserids();

    @Insert("insert into HS_ResRalation (FileId,AddTime,UserId) values (#{FileId},#{AddTime},#{userId})")
    void addRelation(@Param("FileId") String id, @Param("AddTime") String time, @Param("userId") String userId);

    @Select("select student_id from SZ_V_School_Student where school_id = #{s} and name = #{name}")
    List<String> getStudentIdWidthName(@Param("name") String trim, @Param("s") String s);

    @Insert("insert into Virtual_Card values(#{intCardNum},#{i},#{cardNum})")
    void addVirtual_Card(@Param("intCardNum") long intCardNum, @Param("i") int i, @Param("cardNum") String cardNum);

    @Select("select class_id from SZ_V_School_Student where school_id = 479 and  student_id = #{customerId} ")
    String getClassName(@Param("customerId") String customerId);

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


    @Select("select StudentId from SS_RoomNumber where Pid in (select id\n" +
            "                                         from SS_Room\n" +
            "                                         where SchoolId = 479 and SS_Room.Pid =10284 )")
    List<String> getStudentIdWithRoom();

    @Select("select CardNum from Virtual_Card where state =0 and id < 5475 ;")
    List<String> getVirCards();

    @Update("update  SZ_V_School_Student set student_num =#{student_num} where school_id = 13 and student_type  =2 and len(student_num)=0 and user_id = #{user_id}")
    void updateStuCards(@Param("student_num") String card, @Param("user_id") String user_id);


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

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

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

    @Insert("insert into CW_StuTemperatureReocrd values (#{UserId},#{StudentId},#{Name},#{ClassId},#{ClassName},#{Temperature},#{Type},#{SchoolId},#{Intime})")
    void addWGTem(@Param("UserId") String UserId, @Param("StudentId") String StudentId, @Param("Name") String Name, @Param("ClassId") String ClassId,
                  @Param("ClassName") String ClassName, @Param("Temperature") String Temperature, @Param("Type") String Type, @Param("SchoolId") String SchoolId, @Param("Intime") String Intime);

    @Select("select student_num from SZ_V_School_Student where school_id = #{schoolId} and student_num<>''")
    List<String> getStudentCardsWidthSchoolId(@Param("schoolId") String schoolId);

    @Select("select student_num from SZ_V_School_Teacher where school_id = #{schoolId} and student_num<>''")
    List<String> getTeaCardsWidthSchoolId(@Param("schoolId") String schoolId);

    @Select("select Top(1) teacher_num from SZ_V_School_Teacher where user_id = #{userId}")
    String getTeaCardWithUserId(@Param("userId") String userId);


    @Select("select student_num from SZ_V_School_Student where user_id = #{userId}")
    String getStudentCardWithUserId(@Param("userId") String userId);

    @Select("select Top(1) * from SZ_V_School_Teacher where user_id = #{userId} and teacher_num<>''")
    TeacherBean getTeacherWithUserId(@Param("userId") String userId);


    List<String> getStudentWiSidAndSex(@Param("schoolId")String schoolId , @Param("sexs")List<String> sexs);


    List<String> getDeviceRoomRelation(@Param("clint_id")String clint_id);


}