using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using Dapper;
using DigitalSchoolApi.Models;
using Hangfire;
using Learun.Application.Organization;
using Learun.Util;
using Newtonsoft.Json;
using Swagger.Net;
using Swagger.Net.Annotations;

namespace DigitalSchoolApi.Controllers
{
    /// <summary>
    /// 基础接口
    /// </summary>
    public class SchoolController : BaseController
    {
        private readonly string _sqlConnection = ConfigurationManager.ConnectionStrings["ConnectionPfcMisDBString"].ConnectionString;
        private readonly string _coresqlConnection = ConfigurationManager.ConnectionStrings["CoreDBString"].ConnectionString;

        /// <summary>
        /// 登录接口
        /// </summary>
        /// <param name="uid">帐户</param>
        /// <param name="pwd">帐户密码，32位MD5加密（小写）</param>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<object>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> CheckLogin(string uid, string pwd)
        {
            try
            {
                if (string.IsNullOrEmpty(uid)||string.IsNullOrEmpty(pwd))
                {
                    return ResultInfo(HttpStatusCode.InternalServerError, info: "参数错误");
                }
                var sql = "select * from LR_Base_User where F_Account='" + uid + "' and F_DeleteMark = 0";
                using (IDbConnection conn = new SqlConnection(_coresqlConnection))
                {
                    UserEntity userinfo = await conn.QueryFirstOrDefaultAsync<UserEntity>(sql);
                    if (userinfo != null)
                    {
                        if (userinfo.F_EnabledMark==1)
                        {
                            string dbPassword = Md5Helper.Encrypt(DESEncrypt.Encrypt(pwd.ToLower(), userinfo.F_Secretkey).ToLower(), 32).ToLower();
                            if (dbPassword == userinfo.F_Password)
                            {
                                return ResultInfo(HttpStatusCode.OK, info: "登录成功");
                            }
                            else
                            {
                                return ResultInfo(HttpStatusCode.NotFound, info: "密码和账户名不匹配!");
                            }
                        }
                        else
                        {
                            return ResultInfo(HttpStatusCode.NotFound, info: "账户被系统锁定,请联系管理员!");
                        }
                    }
                    else
                    {
                        return ResultInfo(HttpStatusCode.NotFound,info: "账户不存在");
                    }
                }
            }
            catch (Exception e)
            {
                return ResultInfo(HttpStatusCode.InternalServerError, result: Result.Fail);
            }


        }

        /// <summary>
        /// 空闲教室
        /// </summary>
        /// <param name="lessonDate">上课日期,格式为yyyy-MM-dd</param>
        /// <param name="pageIndex">分页页码，默认第一页</param>
        /// <param name="pageNum">分页每页记录条数,默认每页10条</param>
        /// <param name="lessonTime">上课时间(如 11 代表星期一第一节课），第几节课支持多选</param>
        /// <param name="buildingNo">教学楼号，如09</param>
        /// <param name="classRoomName">教室名称</param>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<FreeClassroom>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> FreeClassRooms(string lessonDate, int pageIndex = 1, int pageNum = 10, [FromUri]IEnumerable<string> lessonTime = null, string buildingNo = "", string classRoomName = "")
        {
            var queryParam = "";
            var countSql = "";
            if (lessonTime != null && lessonTime.Any())
            {
                queryParam += "and a.LessonTime in @LessonTime";
            }
            var infoSql = @"select e.ClassroomBuildingNo,rtrim(f.ClassroomBuildingName) ClassroomBuildingName,e.ClassroomNo,e.ClassroomName,e.ClassroomFloor,e.ContainStuNum from (
select rtrim(ClassroomBuildingNo) ClassroomBuildingNo,rtrim(ClassroomNo) ClassroomNo,rtrim(ClassroomName) ClassroomName,ClassroomFloor,ContainStuNum from ClassroomInfo 
except (SELECT c.ClassroomBuildingNo,a.ClassroomNo,c.ClassroomName,c.ClassroomFloor,c.ContainStuNum FROM ArrangeLessonTerm a 
                left join ClassroomInfo c on c.ClassroomNo = a.ClassroomNo
				where a.LessonDate=@LessonDate " + queryParam + ")) e left join ClassroomBuilding f on f.ClassroomBuildingNo=e.ClassroomBuildingNo where 1=1";
            countSql = @"select count(1) from (
select rtrim(ClassroomBuildingNo) ClassroomBuildingNo,rtrim(ClassroomNo) ClassroomNo,rtrim(ClassroomName) ClassroomName,ClassroomFloor,ContainStuNum from ClassroomInfo 
except (SELECT c.ClassroomBuildingNo,a.ClassroomNo,c.ClassroomName,c.ClassroomFloor,c.ContainStuNum FROM ArrangeLessonTerm a 
                left join ClassroomInfo c on c.ClassroomNo = a.ClassroomNo
				where a.LessonDate=@LessonDate " + queryParam + ")) e left join ClassroomBuilding f on f.ClassroomBuildingNo=e.ClassroomBuildingNo where 1=1";
            if (!string.IsNullOrEmpty(buildingNo.Trim()))
            {
                infoSql += " and e.ClassroomBuildingNo=@ClassroomBuildingNo ";
                countSql += " and e.ClassroomBuildingNo=@ClassroomBuildingNo ";
            }
            if (!string.IsNullOrEmpty(classRoomName.Trim()))
            {
                infoSql += " and e.ClassroomName like @ClassroomName ";
                countSql += " and e.ClassroomName like @ClassroomName ";
            }
            infoSql += " order by e.ClassroomNo OFFSET (@PageIndex-1)*@Pagenum ROWS FETCH next @Pagenum rows only";
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    var info = await conn.QueryAsync<FreeClassroom>(infoSql, new { LessonDate = DateTime.ParseExact(lessonDate.Trim(), "yyyy-MM-dd", CultureInfo.CurrentCulture), LessonTime = lessonTime, ClassroomBuildingNo = buildingNo.Trim(), ClassroomName = $"%{classRoomName.Trim()}%", PageIndex = pageIndex, Pagenum = pageNum });
                    if (info == null)
                    {
                        return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
                    }
                    var totalRowCount = conn.QueryFirstOrDefault<int>(countSql, new { LessonDate = DateTime.ParseExact(lessonDate.Trim(), "yyyy-MM-dd", CultureInfo.CurrentCulture), LessonTime = lessonTime, ClassroomBuildingNo = buildingNo.Trim(), ClassroomName = $"%{classRoomName.Trim()}%" });
                    var totalPage = Convert.ToInt32(Math.Ceiling((double)totalRowCount / pageNum));
                    return ResultInfo(new { pagination = new { total = totalRowCount, totalPages = totalPage, perPage = pageNum, currentPage = pageIndex }, info });
                }
            }
            catch (Exception e)
            {
                return ResultInfo(HttpStatusCode.InternalServerError, result: Result.Fail);
            }
        }

        /// <summary>
        /// 教学楼信息查询
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<object>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> TeachingBuildings()
        {
            var infoSql = @"select ClassroomBuildingNo BuildingNo,rtrim(ClassroomBuildingName) BuildingName from ClassroomBuilding";
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    var info = await conn.QueryAsync(infoSql);
                    if (info == null)
                    {
                        return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
                    }
                    return ResultInfo(new { totalCount = info.Count(), buildings = info });
                }
            }
            catch
            {
                return ResultInfo(HttpStatusCode.InternalServerError, result: Result.Fail);
            }
        }


        /// <summary>   
        /// 学生课程表
        /// </summary>
        /// <param name="stuNum">学号</param>
        /// <param name="academicYear">学年，格式如：18-19,默认为当前学年</param>
        /// <param name="term">学期，如1，默认为当前学期</param>
        /// <param name="lessonStartDate">上课开始日期,格式为yyyy-MM-dd</param>
        /// <param name="lessonEndDate">上课截止日期,格式为yyyy-MM-dd</param>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<IEnumerable<TimeTable>>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> Timetable(string stuNum, string academicYear = "", string term = "", string lessonStartDate = "", string lessonEndDate = "")
        {
            var semesterAndYear = Common.GetSemesterAndYear();
            var strAcademicYear = semesterAndYear.AcademicYearShort;
            var strSemester = semesterAndYear.Semester;
            //var startDate = Common.CalculateFirstDateOfWeek(DateTime.Today);
            //var endDate = Common.CalculateLastDateOfWeek(DateTime.Today);
            if (!string.IsNullOrEmpty(academicYear))
            {
                strAcademicYear = academicYear;
            }
            if (!string.IsNullOrEmpty(term))
            {
                strSemester = term;
            }
            var strWhere = $"a.StuNo=@stuNum and a.AcademicYearNo='{strAcademicYear}' and a.Semester='{strSemester}' ";
            var sql = "SELECT " +
                      "b.LessonName,CONVERT(varchar(100), b.LessonDate, 23) LessonDate,b.LessonTime,b.Empname,c.ClassroomName,b.Semester from StuSelectLessonList a " +
                      "left join ArrangeLessonTerm b on b.TeachClassNo=RTRIM(a.LessonName) + RTRIM(a.PartCode) + RTRIM(a.TeachClassNo) " +
                      "left join ClassroomInfo c on c.ClassroomNo=b.classroomNo " +
                      $"where {strWhere} " +
                      $"and b.AcademicYearNo='{strAcademicYear}' and b.Semester='{strSemester}'  and b.CheckMark=1 ";
            //$"and b.LessonDate>='{startDate}' and b.LessonDate<='{endDate}' and b.CheckMark=1";
            if (!string.IsNullOrEmpty(lessonStartDate))
            {
                sql += $" and b.LessonDate>='{lessonStartDate}' ";
            }
            if (!string.IsNullOrEmpty(lessonEndDate))
            {
                sql += $" and b.LessonDate<='{lessonEndDate}' ";
            }
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    var getStudentTClass = await conn.QueryAsync(sql, new { stuNum = stuNum.Trim() });
                    if (getStudentTClass == null)
                    {
                        return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
                    }
                    return ResultInfo(getStudentTClass);
                }
            }
            catch
            {
                return ResultInfo(HttpStatusCode.InternalServerError, result: Result.Fail);
            }

        }

        /// <summary>
        /// 学生成绩
        /// </summary>
        /// <param name="stuNum">学号</param>
        /// <param name="academicYear">学年，格式如：18-19</param>
        /// <param name="term">学期，数字整数，如1</param>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<object>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> StudentScore(string stuNum, string academicYear = "", string term = "")
        {
            dynamic info;
            dynamic score;
            var infoSql = @"select distinct a.StuName,d.MajorName,c.ClassName from StuScore a
                left join Empinfo b on b.empno = a.empno
                left join ClassInfo c on c.ClassNo = a.ClassNo
                left join CdMajor d on d.MajorNo = a.MajorNo
                where StuNo =@StuNo";
            var scoreSql = @"select a.LessonNo,a.LessonName,b.EmpName,a.StudyScore,a.Score from StuScore a
left join Empinfo b on b.empno=a.empno
where StuNo=@StuNo ";
            if (!string.IsNullOrEmpty(academicYear))
            {
                scoreSql += "and AcademicYearNo=@AcademicYearNo ";
            }
            if (!string.IsNullOrEmpty(term))
            {
                scoreSql += "and semester=@semester ";
            }

            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {

                    info = await conn.QueryAsync(infoSql, new { StuNo = stuNum.Trim() });
                    score = await conn.QueryAsync(scoreSql, new { StuNo = stuNum.Trim(), AcademicYearNo = academicYear.Trim(), semester = term.Trim() });
                    if (score == null)
                    {
                        return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
                    }
                    return ResultInfo(new { info[0].StuName, info[0].MajorName, info[0].ClassName, Scores = score });
                }
            }
            catch
            {
                return ResultInfo(HttpStatusCode.InternalServerError, result: Result.Fail);
            }
        }

        /// <summary>
        /// 教职工和学生基本信息
        /// </summary>
        /// <param name="category">人员类型 0学生 1教职工</param>
        /// <param name="pageIndex">分页页码，默认第一页</param>
        /// <param name="pageNum">分页每页记录条数,默认每页10条</param>
        /// <returns></returns>
        [HttpGet]
        [SwaggerResponse(HttpStatusCode.OK, Type = typeof(OperateResult<object>))]
        [SwaggerResponse(HttpStatusCode.InternalServerError)]
        public async Task<IHttpActionResult> BasicInfo(int category, int pageIndex = 1, int pageNum = 10)
        {
            dynamic info;
            int rowCount;
            string infoSql = "";
            string rowCountSql = "";
            if (category == 0)
            {
                rowCountSql = @"SELECT rows FROM sysindexes
                WHERE id = OBJECT_ID('[StuInfoBasic]') AND indid< 2";
                infoSql = @"SELECT [StuNo]
                    ,[StuName]
                    ,[NoticeNo]
                    ,[DeptName]
                    ,[MajorName]
                    ,[Grade]
                    ,[ClassNo]
                    ,[SpellFull]
                    ,[SpellBrief]
                    ,[Birthday]
                    ,[PartyFace]
                    ,[Nationality]
                    ,[FamilyOrigin]
                    ,[HealthStatus]
                    ,[WillNo]
                    ,[TestStuSubject]
                    ,[PlanForm]
                    ,[IsThreeGood]
                    ,[IsExcellent]
                    ,[IsNormalCadre]
                    ,[MatriculateSort]
                    ,[HighSchoolNo]
                    ,[HighSchoolName]
                    ,[EntranceDate]
                    ,[Religion]
                    ,[GoodAt]
                    ,[IdentityCardNo]
                    ,[JoinPartyDate]
                    ,[JoinLeagueDate]
                    ,[InSchoolAddress]
                    ,[InSchoolTelephone]
                    ,[AbmormityMoveMark]
                    ,[AwardMark]
                    ,[PunishMark]
                    ,[LinkmanMark]
                    ,[StuNoChangeMark]
                    ,[FinishSchoolMark]
                    ,[CurrentRegisterMark]
                    ,[FinishSchoolDate]
                    ,[DiplomaNo]
                    ,[DiplomaRemark]
                    ,[Remark]
                    ,[RegisterDate]
                    ,[Photo]
                    ,[TeachPlanNo]
                    ,[mobile]
                    ,[EMail]
                    ,[QQ]
                    ,[FatherName]
                    ,[FatherUnit]
                    ,[FatherPhone]
                    ,[MatherName]
                    ,[MatherUnit]
                    ,[MatherPhone]
                    ,[Gender]
                    ,[Residence]
                    ,[CheckMark]
                    ,[DeptNo]
                    ,[MajorNo]
                    ,[ClassName]
                    ,[IsProvinceFirstThree]
                    ,[OverseasChineseName]
                    ,[ComeProvince]
                    ,[MailAddress]
                    ,[LengthOfSchooling]
                    ,[DormBuildingNo]
                    ,[UnitNo]
                    ,[DormNo]
                    ,[StuDorm]
                    ,[DeptShortName]
                    ,[ksh]
                    ,[GovMajorNo]
                    ,[GovMajorName]
                    ,[PartyFaceNo]
                    ,[NationalityNo]
                    ,[MajorDetailNo]
                    ,[MajorDetailName]
                    ,[ClassTutorNo]
                    ,[ClassDiredctorNo]
                    ,[InSchoolStatus]
                    ,[AbmormityMove]
                    ,[ResumeCheck]
                FROM [vwStuInfoBasicInCollege] order by StuNo OFFSET (@PageIndex-1)*@Pagenum ROWS FETCH next @Pagenum rows only";
            }
            else if (category == 1)
            {
                rowCountSql = @"SELECT rows FROM sysindexes
                WHERE id = OBJECT_ID('[EmpInfo]') AND indid< 2";
                infoSql = @"SELECT [EmpNo]
      ,[EmpName]
      ,[Age]
      ,[Birthday]
      ,[TitleOfTechPost]
      ,RTRIM([DegreeName]) DegreeName
      ,[PartyFace]
      ,[ProvinceName]
      ,RTRIM([SubjectSpecies]) SubjectSpecies
      ,[OverseasChineseName]
      ,[FamilyOrigin]
      ,RTRIM([Nationality]) Nationality
      ,[DeptName]
      ,[ForeignLanguageName]
      ,[HealthStatus]
      ,[CultureDegree]
      ,[GraduateSchoolName]
      ,[SpellBrief]
      ,[InSchoolDate]
      ,[mobile]
      ,[EMail]
      ,[resume]
      ,[LocusOfArchives]
      ,[LocusOfResidence]
      ,[Linkman]
      ,[RelationOfLinkman]
      ,[PhoneOfLinkman]
      ,[IdentityCardNo]
      ,[Photo]
      ,[Gender]
      ,[EmpFullTime]
      ,[EmpSort]
      ,[CheckMark]
      ,[IsInActiveStatus]
      ,[StaffRoomName]
      ,[DeptShortName]
      ,[DeptNo]
      ,[Birthplace]
      ,[Homeaddress]
      ,[Postcode]
      ,[HandMobile]
      ,[PartyFaceNo]
      ,[NationalityNo]
      ,[position]
      ,[ResumeCheck]
      ,[ResumeCheckNo]
      ,[Marry]
      ,[isTeacher]
      ,[DegreeNo]
      ,[ProvinceNo]
      ,[WorkDate]
      ,[CultureDegree2]
      ,[Degree2]
      ,[GraduateSchoolName2]
      ,[GraduateDate1]
      ,[GraduateDate2]
      ,[Major1]
      ,[Major2]
  FROM [vwEmpInfoAll] order by EmpNo OFFSET (@PageIndex-1)*@Pagenum ROWS FETCH next @Pagenum rows only";
            }
            else
            {
                return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
            }
            using (IDbConnection conn = new SqlConnection(_sqlConnection))
            {
                info = await conn.QueryAsync(infoSql, new { PageIndex = pageIndex, Pagenum = pageNum });
                rowCount = await conn.QueryFirstOrDefaultAsync<int>(rowCountSql);
                if (info == null)
                {
                    return ResultInfo(HttpStatusCode.NotFound, result: Result.NotFound);
                }
                var totalPage = Convert.ToInt32(Math.Ceiling((double)rowCount / pageNum));
                return ResultInfo(new { pagination = new { total = rowCount, totalPages = totalPage, perPage = pageNum, currentPage = pageIndex }, info });
            }
        }


        //todo:测试

        /// <summary>
        /// 测试
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IHttpActionResult BeginCeshi()
        {
            //RecurringJob.AddOrUpdate("CHANGYANGYKT1",
            //    () => YKTTrabs.GetChangeYangYKT(),
            //    Cron.Daily(9), TimeZoneInfo.Local);
            try
            {
                using (IDbConnection conn = new SqlConnection(_coresqlConnection))
                {
                    conn.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'测试','进入测试方法',getdate())");

                    //调接口
                    var responsejson = HttpMethods.HttpGet("http://localhost:8088//TransferApi/BasicInfo/sendStudentInfo");
                    if (!string.IsNullOrEmpty(responsejson))
                    {
                        conn.Execute("insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'测试','调接口结果为："+ responsejson + "',getdate())");
                    }

                }
            }
            catch (Exception ex)
            {
                using (IDbConnection conn = new SqlConnection(_coresqlConnection))
                {
                    conn.Execute(
                        $"insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),999,'测试','{ex.Message}',getdate())");
                }
            }

            return Ok();
        }



    }
}
