using MongoDB.Driver.Linq;
using static Tiobon.Core.Model.Consts;

namespace Tiobon.Core.Services;

/// <summary>
/// 考试 (服务)
/// </summary>
public class Ghre_ExamServices : BaseServices<Ghre_Exam, Ghre_ExamDto, InsertGhre_ExamInput, EditGhre_ExamInput>, IGhre_ExamServices
{
    private readonly IBaseRepository<Ghre_Exam> _dal;
    private readonly IGhre_CourseServices _ghre_CourseServices;
    private readonly IGhre_CourseSceneServices _ghre_CourseSceneServices;
    private readonly IGhra_StaffServices _ghra_StaffSceneServices;
    private readonly IGhre_ExamStaffServices _ghre_ExamStaffServices;
    private readonly IGhre_ExamRecordServices _ghre_ExamRecordServices;
    private readonly IGhre_ExamMessageLogServices _ghre_ExamMessageLogServices;
    private readonly IGhre_ExamPaperServices _ghre_ExamPaperServices;
    private readonly IGhre_ExamPaperQuestionServices _ghre_ExamPaperQuestionServices;
    private readonly IGhre_ExamRecordDetailServices _ghre_ExamRecordDetailServices;
    private readonly IGhre_QuestionServices _ghre_QuestionServices;
    private readonly IGhre_QuestionAnswerServices _ghre_QuestionAnswerServices;
    public Ghre_ExamServices(ICaching caching,
            IGhre_CourseServices ghre_CourseServices,
            IGhre_CourseSceneServices ghre_CourseSceneServices,
            IGhra_StaffServices ghra_StaffSceneServices,
            IGhre_ExamStaffServices ghre_ExamStaffServices,
            IGhre_ExamRecordServices ghre_ExamRecordServices,
            IGhre_ExamMessageLogServices ghre_ExamMessageLogServices,
            IGhre_ExamPaperServices ghre_ExamPaperServices,
            IGhre_ExamPaperQuestionServices ghre_ExamPaperQuestionServices,
            IGhre_ExamRecordDetailServices ghre_ExamRecordDetailServices,
            IGhre_QuestionServices ghre_QuestionServices,
            IGhre_QuestionAnswerServices ghre_QuestionAnswerServices,
            IBaseRepository<Ghre_Exam> dal)
    {
        this._dal = dal;
        base.BaseDal = dal;
        base._caching = caching;
        _ghre_CourseServices = ghre_CourseServices;
        _ghre_CourseSceneServices = ghre_CourseSceneServices;
        _ghra_StaffSceneServices = ghra_StaffSceneServices;
        _ghre_ExamStaffServices = ghre_ExamStaffServices;
        _ghre_ExamRecordServices = ghre_ExamRecordServices;
        _ghre_ExamMessageLogServices = ghre_ExamMessageLogServices;
        _ghre_QuestionServices = ghre_QuestionServices;
        _ghre_QuestionAnswerServices = ghre_QuestionAnswerServices;
        _ghre_ExamPaperServices = ghre_ExamPaperServices;
        _ghre_ExamPaperQuestionServices = ghre_ExamPaperQuestionServices;
        _ghre_ExamRecordDetailServices = ghre_ExamRecordDetailServices;
    }
    public async Task<dynamic> GetModuleInfo(ModuleParam param)
    {
        dynamic result = new ExpandoObject();
        dynamic data = new ExpandoObject();

        var Toolbar = new List<Toolbar>();

        #region 定义页面的操作按钮
        string sql = @"SELECT fnKey,
                       fnKeyValue,
                       [dbo].[FLangKeyToValue] (fnKeyValue, {2}, fnKeyValue) fnTitle,
                       fnType,
                       position,
                       icon,
                       'true' display
                    FROM (SELECT CASE
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'NewYN' THEN 1
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'ToExcelYN' THEN 2
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'FromExcelYN' THEN 3
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'BatchUpdateYN' THEN 4
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'BatchDelYN' THEN 5
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'LogYN' THEN 6
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'ManualYN' THEN 7
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'UpdateYN' THEN 2
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'DelYN' THEN 4
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'CopyYN' THEN 3
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'DetailYN' THEN 1
                                    ELSE 8
                                 END fnKeySeq,
                                 substring (fnKey, 3, 99) + 'YN' fnKey,
                                 'Ghr_FnKey_' + fnKey fnKeyValue,
                                 CASE
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('NewYN',
                                                                             'ToExcelYN',
                                                                             'FromExcelYN',
                                                                             'BatchUpdateYN',
                                                                             'BatchDelYN',
                                                                             'RecycleYN',
                                                                             'CancelRecycleYN',
                                                                             'ManualYN',
                                                                             'LogYN',
                                                                             'BatchDelYN',
                                                                             'BatchUpdateYN')
                                    THEN
                                       'table'
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('UpdateYN',
                                                                             'DelYN',
                                                                             'CopyYN',
                                                                             'DetailYN')
                                    THEN
                                       'row'
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('QueryYN',
                                                                             'AdvanceQueryYN',
                                                                             'QuerySettingYN',
                                                                             '')
                                    THEN
                                       'search'
                                    ELSE
                                       ''
                                 END fnType,
                                 'left' position,
                                 CASE substring (fnKey, 3, 99) + 'YN'
                                    WHEN 'NewYN' THEN 'ghr-icon-add'
                                    WHEN 'ToExcelYN' THEN 'ghr-file-export'
                                    WHEN 'FromExcelYN' THEN 'ghr-file-import'
                                    WHEN 'UpdateYN' THEN 'ghr-icon-edit'
                                    WHEN 'BatchUpdateYN' THEN 'ghr-batch-update'
                                    WHEN 'DelYN' THEN 'ghr-icon-trash'
                                    WHEN 'BatchDelYN' THEN 'ghr-batch-del'
                                    WHEN 'LogYN' THEN 'ghr-log'
                                    WHEN 'ManualYN' THEN 'ghr-operation-manual'
                                    WHEN 'CopyYN' THEN 'ghr-icon-copy'
                                    WHEN 'DetailYN' THEN 'ghr-icon-eye-open'
                                    ELSE ''
                                 END icon
                          FROM (SELECT max (c.IsWaterMark) IsWaterMark,
                                       max (c.IsPWDAgain) IsPWDAgain,
                                       max (c.IsFromExcel) IsFromExcel,
                                       max (c.IsToExcel) IsToExcel,
                                       max (c.IsNew) IsNew,
                                       max (c.IsDetail) IsDetail,
                                       max (c.IsDel) IsDel,
                                       max (c.IsDel) IsBatchDel,
                                       max (c.IsUpdate) IsUpdate,
                                       max (c.IsUpdate) IsBatchUpdate,
                                       max (c.IsQuery) IsQuery,
                                       max (c.IsPrint) IsPrint,
                                       0  IsLog,
                                       max (c.IsManual) IsManual,
                                       max (c.IsCopy) IsCopy
                                FROM Ghrs_UserRole a, Ghrs_RoleMenu c, Ghrs_Menu d
                                WHERE     a.IsEnable = 1
                                      AND c.IsEnable = 1
                                      AND d.IsEnable = 1
                                      AND EXISTS
                                             (SELECT 1
                                              FROM Ghrs_Role kk
                                              WHERE kk.roleid = a.roleid AND kk.IsEnable = 1)
                                      AND a.UserID = '{1}'
                                      AND a.RoleId = c.RoleID
                                      AND c.MenuId = d.MenuId
                                      AND d.MenuNo = '{0}') kk
                               UNPIVOT (fnKeyValue
                                       FOR fnKey
                                       IN (IsWaterMark,
                                          IsPWDAgain,
                                          IsFromExcel,
                                          IsToExcel,
                                          IsNew,
                                          IsDetail,
                                          IsUpdate,
                                          IsDel,
                                          IsQuery,
                                          IsPrint,
                                          IsLog,
                                          IsManual,
                                          IsCopy,
                                          IsBatchUpdate,
                                          IsBatchDel)) AS tt
                          WHERE fnKeyValue > 0
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'QuerySettingYN' fnKey,
                                 'GHR_Common_000064' fnTitle,
                                 'search' fnType,
                                 'left' position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'LabelYN' fnKey,
                                 'GHR_Common_000065' fnTitle,
                                 NULL fnType,
                                 NULL position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'MoreStyleYN' fnKey,
                                 'GHR_Common_000066' fnTitle,
                                 NULL fnType,
                                 NULL position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'GridView' fnKey,
                                 'GHR_Common_000067' fnTitle,
                                 'table' fnType,
                                 'right' position,
                                 'ant-design:setting-outlined' icon
                        ) A
                    WHERE fnKey NOT IN
                             (SELECT b.field
                              FROM Ghrs_UserPageSettingQueryColumn a, Ghrs_PageSettingQuery b
                              WHERE     a.PageSettingQueryId = b.PageSettingQueryId
                                    AND a.UserID = '{1}'
                                    AND b.PageNo = '{0}'
                                    AND a.IsEnable = 1
                                    AND b.IsEnable = 1
                                    AND a.isHidden = 'true'
                                    AND b.elementType = 'FnKey') order by fnKeySeq";

        sql = string.Format(sql, param.menuName, App.User.ID, param.langId);
        var toolbars = DbAccess.QueryList<Toolbar>(sql);

        sql = $@"SELECT A.RoleId, B.RoleNo, B.RoleName
                    FROM Ghrs_UserRole A LEFT JOIN Ghrs_Role B ON A.RoleId = B.RoleId
                    WHERE     A.UserID = {App.User.ID}
                          AND A.IsEnable = 1
                          AND B.IsEnable = 1
                          AND B.RoleNo LIKE 'TrainingExam%'";
        var toolbarRoles = DbAccess.QueryList<ToolbarRole>(sql);

        if (toolbars.Any(x => x.fnKey == "NewYN"))
            Toolbar.Add(new Toolbar()
            {
                fnKey = "NewYN",
                fnKeyValue = null,
                fnTitle = "新增",
                fnType = "table",
                position = "left",
                icon = "ghr-icon-add",
                display = true
            });
        if (param.menuName == "F_ExamManageDraft")
            Toolbar.Add(new Toolbar()
            {
                fnKey = "Release",
                fnKeyValue = null,
                fnTitle = "发布",
                fnType = "table",
                position = "left",
                icon = "ghr-publish",
                display = true
            });

        if (param.menuName == "F_ExamManageReleased" && toolbarRoles.Any(x => x.RoleNo == "TrainingExamCancelRelease"))
            Toolbar.Add(new Toolbar()
            {
                fnKey = "CancelRelease",
                fnKeyValue = null,
                fnTitle = "取消发布",
                fnType = "table",
                position = "left",
                icon = "ghr-publish",
                display = true
            });
        //if (toolbars.Any(x => x.fnKey == "ToExcelYN"))
        //    Toolbar.Add(toolbars.First(x => x.fnKey == "ToExcelYN"));
        #endregion


        var DT_Procedure = new DT_Procedure();

        sql = "select QueryProcedure, EditProcedure, IUDProcedure from Ghrs_Menu where MenuNo='{0}'";
        sql = string.Format(sql, param.menuName, App.User.ID, param.langId);
        var dt = Db.Ado.GetDataTable(sql);
        if (dt.Rows.Count > 0)
        {
            DT_Procedure.QueryProcedure = dt.Rows[0]["QueryProcedure"].ToString();
            DT_Procedure.EditProcedure = dt.Rows[0]["EditProcedure"].ToString();
            DT_Procedure.IUDProcedure = dt.Rows[0]["IUDProcedure"].ToString();
        }
        sql = $@"SELECT PageSettingQueryId,
                       elementType,
                       field,
                       [dbo].[FLangKeyToValue] (Meky, {param.langId}, label)
                          label,
                       Meky,
                       multipleSelect,
                       dataType,
                       required,
                       palceholder,
                       dataSourceType,
                       dbo.FS_GetdataSourceBySet
                       (dataSource, APIDataSourceType, Ghrs_PageSettingQuery.APIDataSourceID)
                          dataSource,
                       sortable,
                       sortOrder,
                       defaultHidden,
                       ConfigIsShow,
                       filterable,
                       CAST(CASE WHEN width = '' THEN '100' ELSE width END AS INT) width,
                       align,
                       fixed fixed1,
                       editable,
                       searchable,
                       searchRequired,
                       searchColumnSeq searchColumnSeq1,
                       multipleSearchValue,
                       maxTagCount,
                       selfDefine,
                       selectUI,
                       isPrimaryKey,
                       'false'
                          isSorted,
                       'false'
                          isHidden,
                       CASE
                          WHEN searchColumnSeq IS NULL OR searchColumnSeq = '' THEN 'false'
                          ELSE 'true'
                       END
                          isSearched,
                       SortNo,
                       --  searchCanOperator,
       ( select ParaDetailNo operator,
										 [dbo].[FLangKeyToValue](MKey,1,ParaDetailName) operatorDesc,
										 SqlFunction operatorIcon
									from Ghrs_ParaDetail where ParaMasterId=13 and IsEnable=1 
									 and ParaDetailNo in (select Value from dbo.Fs1_GHR30_SplitString2(searchCanOperator,','))
									 order by (select positon from  dbo.Fs1_GHR30_SplitString2(searchCanOperator,',')
									            where value= ParaDetailNo)  
									 FOR JSON Path  ) searchOperator1,
                       ''
                          searchColumnDefaultValue,
                       ''
                          AppColumnType
                FROM Ghrs_PageSettingQuery
                WHERE     PageNo = '{param.menuName}'
                      AND elementType NOT IN ('PageGroup')
                      AND IsEnable = 1
                ORDER BY SortNo ASC";

        sql = string.Format(sql, param.menuName, App.User.ID, param.langId);
        var TableColumns = Db.Ado.SqlQuery<TableColumn1>(sql);

        TableColumns.ForEach(x =>
        {
            try
            {
                if (!string.IsNullOrWhiteSpace(x.searchColumnSeq1))
                    x.searchColumnSeq = Convert.ToInt32(x.searchColumnSeq1);

            }
            catch (Exception)
            {
            }
        });
        TableColumns = TableColumns.Where(x => x.field != "ExamModeLabel").ToList();
        var SearchFormFields = Mapper.Map(TableColumns).ToANew<List<TableColumn>>();
        SearchFormFields.ForEach(x =>
        {
            if (!string.IsNullOrWhiteSpace(x.searchOperator1))
                x.searchOperator = JsonConvert.DeserializeObject<List<searchOperator>>(x.searchOperator1);
            x.searchOperator1 = null;
        });
        #region DT_PageMutiMsg
        var DT_PageMutiMsg = await _caching.GetAsync<List<DT_PageMutiMsg>>("DT_PageMutiMsg");
        if (DT_PageMutiMsg == null || (DT_PageMutiMsg != null && !DT_PageMutiMsg.Any()))
        {
            sql = $@"SELECT Langkey field,
                       CASE {param.langId}
                          WHEN 1 THEN isnull (Value01, LangValue)
                          WHEN 2 THEN isnull (Value02, LangValue)
                          WHEN 3 THEN isnull (Value03, LangValue)
                          WHEN 4 THEN isnull (Value04, LangValue)
                          WHEN 5 THEN isnull (Value05, LangValue)
                          WHEN 6 THEN isnull (Value06, LangValue)
                          WHEN 7 THEN isnull (Value07, LangValue)
                          WHEN 8 THEN isnull (Value08, LangValue)
                          WHEN 9 THEN isnull (Value09, LangValue)
                          WHEN 10 THEN isnull (Value10, LangValue)
                       END label
                FROM Ghrs_LangKey
                WHERE     (LangKey LIKE 'GHR_Page%' OR LangKey LIKE 'GHR_Common%')
                      AND IsEnable = 1";
            DT_PageMutiMsg = Db.Ado.SqlQuery<DT_PageMutiMsg>(sql);
            if (DT_PageMutiMsg.Any())
                await _caching.SetAsync("DT_PageMutiMsg", DT_PageMutiMsg);
        }
        #endregion


        switch (param.menuName)
        {

            case "F_ExamManageDraft":

                DT_Procedure.ExportExcel = "/api/Ghre_Exam/ExportExcel/Draft";

                break;
            case "F_ExamManageReleased":

                DT_Procedure.ExportExcel = "/api/Ghre_Exam/ExportExcel/Released";
                break;
            case "F_ExamManageDisabled":

                DT_Procedure.ExportExcel = "/api/Ghre_Exam/ExportExcel/Disabled";
                break;
        }

        data.Toolbar = Toolbar;
        data.DT_Procedure = DT_Procedure;
        data.SearchFormFields = SearchFormFields;
        data.DT_PageMutiMsg = DT_PageMutiMsg;

        result.Success = true;
        result.Status = 200;
        result.Data = data;
        result.Message = "查询成功";
        return result;
    }

    public async Task<dynamic> QueryList(QueryBody body, string status)
    {
        var data = await QueryFilterPage1(body, status);

        dynamic return1 = new ExpandoObject();
        dynamic result = new ExpandoObject();
        dynamic DT_TablePageInfoT1 = new ExpandoObject();

        result.DT_TableDataT1 = data.result.DT_TableDataT1;
        DT_TablePageInfoT1.PageNum = body.pageNum;
        DT_TablePageInfoT1.PageSize = body.pageSize;
        DT_TablePageInfoT1.TotalCount = data.result.DT_TablePageInfoT1.TotalCount;
        result.DT_TablePageInfoT1 = DT_TablePageInfoT1;
        dynamic CardConfig = new ExpandoObject();
        CardConfig.cardTitle = "ExamName";
        CardConfig.cardImage = "CoverUrl";
        CardConfig.column1 = "AfterHowLongLabel";
        CardConfig.column1Icon = "ghr-form-designer-deteSelect";
        CardConfig.column1Label = "考试时间";
        CardConfig.column2 = "ExamPaperName";
        CardConfig.column2Icon = "ghr-exam-paper";
        CardConfig.column2Label = "试卷";
        CardConfig.column3 = "ExamModeLabel";
        CardConfig.column3Icon = "ghr-online-exam";
        CardConfig.column3Label = "考试形式";
        //CardConfig.column4 = "OpenClassName";
        //CardConfig.column4Icon = "ghr-link-openClass";
        CardConfig.column4 = "";
        CardConfig.column4Icon = "";
        CardConfig.column5 = "";
        CardConfig.column5Icon = "";
        CardConfig.column6 = "";
        CardConfig.column6Icon = "";
        result.CardConfig = CardConfig;


        return1.result = result;
        return1.message = "查询成功！";
        return1.Success = true;
        return1.code = 0;
        return1.type = "success";
        return return1;
    }

    public async Task<ServicePageResult<Ghre_ExamDto>> QueryFilterPage1(QueryBody filter, string status = null, List<long> ids = null)
    {
        RefAsync<int> totalCount = 0;
        string sql = @$"SELECT *
                        FROM (SELECT A.*,
                                     CASE
                                        WHEN A.LinkType = 'CourseId' THEN B.CourseName
                                        WHEN A.LinkType = 'CourseSceneId' THEN D.SceneName
                                     END CourseName,
                                     CASE
                                        WHEN A.LinkType = 'CourseId' THEN A.CourseId
                                        WHEN A.LinkType = 'CourseSceneId' THEN A.CourseSceneId
                                     END CourseSceneId1,
                                     E.PaperName ExamPaperName, ISNULL(A.UpdateTime, A.CreateTime) CreateTime1
                              FROM Ghre_Exam A
                                   LEFT JOIN Ghre_Course B ON A.CourseId = B.Id
                                   LEFT JOIN Ghre_CourseScene D ON A.CourseSceneId = D.Id
                                   LEFT JOIN Ghre_ExamPaper E ON A.ExamPaperId = E.Id
                              WHERE A.IsEnable = 1 AND dbo.FUserDataBelongPriv ({App.User.ID}, A.DataBelongID, NULL) = 1) A";

        if (string.IsNullOrWhiteSpace(filter.orderBy))
            filter.orderBy = "CreateTime1 DESC";

        string conditions = "1=1";
        if (filter.jsonParam != null)
            foreach (JProperty jProperty in filter.jsonParam.Properties())
            {
                var name = jProperty.Name;
                var value = jProperty.Value.ToString();
                if (name == "page" || name == "pageSize")
                    continue;

                if (name == "ExamDate")
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam1>(value);
                    conditions += $" AND ((BeginTime BETWEEN '{jsonParam.columnValue[0]}' AND '{jsonParam.columnValue[1]}') OR (EndTime BETWEEN '{jsonParam.columnValue[0]}' AND '{jsonParam.columnValue[1]}'))";

                    continue;
                }

                if (!string.IsNullOrWhiteSpace(value))
                {
                    var jsonParam = JsonConvert.DeserializeObject<JsonParam>(value);

                    switch (jsonParam.operationKey)
                    {
                        case "Include":
                            conditions += $" AND {name} LIKE '%{jsonParam.columnValue}%'";
                            break;
                        case "NotInclude":
                            conditions += $" AND {name} NOT LIKE '%{jsonParam.columnValue}%'";
                            break;
                        case "IsNull":
                            conditions += $" AND {name} IS NULL";
                            break;
                        case "NotNull":
                            conditions += $" AND {name} IS NOT NULL";
                            break;
                        case "Equal":
                            conditions += $" AND {name} ='{jsonParam.columnValue}'";
                            break;
                        case "NotEqual":
                            conditions += $" AND {name} !='{jsonParam.columnValue}'";
                            break;
                        default:
                            break;
                    }
                }
            }
        if (!string.IsNullOrWhiteSpace(status))
            conditions += $" AND Status ='{status}'";

        if (ids != null && ids.Any())
            conditions += $" AND Id IN({string.Join(",", ids)})";

        sql += " WHERE " + conditions;
        if (filter.pageSize == 0)
            filter.pageSize = 10000;
        var data = await Db.SqlQueryable<Ghre_ExamDto>(sql)
            .OrderBy(filter.orderBy)
            .ToPageListAsync(filter.pageNum, filter.pageSize, totalCount);

        #region 定义页面的操作按钮
        sql = @"SELECT fnKey,
                       fnKeyValue,
                       [dbo].[FLangKeyToValue] (fnKeyValue, {2}, fnKeyValue) fnTitle,
                       fnType,
                       position,
                       icon,
                       'true' display
                    FROM (SELECT CASE
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'NewYN' THEN 1
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'ToExcelYN' THEN 2
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'FromExcelYN' THEN 3
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'BatchUpdateYN' THEN 4
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'BatchDelYN' THEN 5
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'LogYN' THEN 6
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'ManualYN' THEN 7
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'UpdateYN' THEN 2
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'DelYN' THEN 4
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'CopyYN' THEN 3
                                    WHEN substring (fnKey, 3, 99) + 'YN' = 'DetailYN' THEN 1
                                    ELSE 8
                                 END fnKeySeq,
                                 substring (fnKey, 3, 99) + 'YN' fnKey,
                                 'Ghr_FnKey_' + fnKey fnKeyValue,
                                 CASE
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('NewYN',
                                                                             'ToExcelYN',
                                                                             'FromExcelYN',
                                                                             'BatchUpdateYN',
                                                                             'BatchDelYN',
                                                                             'RecycleYN',
                                                                             'CancelRecycleYN',
                                                                             'ManualYN',
                                                                             'LogYN',
                                                                             'BatchDelYN',
                                                                             'BatchUpdateYN')
                                    THEN
                                       'table'
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('UpdateYN',
                                                                             'DelYN',
                                                                             'CopyYN',
                                                                             'DetailYN')
                                    THEN
                                       'row'
                                    WHEN substring (fnKey, 3, 99) + 'YN' IN ('QueryYN',
                                                                             'AdvanceQueryYN',
                                                                             'QuerySettingYN',
                                                                             '')
                                    THEN
                                       'search'
                                    ELSE
                                       ''
                                 END fnType,
                                 'left' position,
                                 CASE substring (fnKey, 3, 99) + 'YN'
                                    WHEN 'NewYN' THEN 'ghr-icon-add'
                                    WHEN 'ToExcelYN' THEN 'ghr-file-export'
                                    WHEN 'FromExcelYN' THEN 'ghr-file-import'
                                    WHEN 'UpdateYN' THEN 'ghr-icon-edit'
                                    WHEN 'BatchUpdateYN' THEN 'ghr-batch-update'
                                    WHEN 'DelYN' THEN 'ghr-icon-trash'
                                    WHEN 'BatchDelYN' THEN 'ghr-batch-del'
                                    WHEN 'LogYN' THEN 'ghr-log'
                                    WHEN 'ManualYN' THEN 'ghr-operation-manual'
                                    WHEN 'CopyYN' THEN 'ghr-icon-copy'
                                    WHEN 'DetailYN' THEN 'ghr-icon-eye-open'
                                    ELSE ''
                                 END icon
                          FROM (SELECT max (c.IsWaterMark) IsWaterMark,
                                       max (c.IsPWDAgain) IsPWDAgain,
                                       max (c.IsFromExcel) IsFromExcel,
                                       max (c.IsToExcel) IsToExcel,
                                       max (c.IsNew) IsNew,
                                       max (c.IsDetail) IsDetail,
                                       max (c.IsDel) IsDel,
                                       max (c.IsDel) IsBatchDel,
                                       max (c.IsUpdate) IsUpdate,
                                       max (c.IsUpdate) IsBatchUpdate,
                                       max (c.IsQuery) IsQuery,
                                       max (c.IsPrint) IsPrint,
                                       0  IsLog,
                                       max (c.IsManual) IsManual,
                                       max (c.IsCopy) IsCopy
                                FROM Ghrs_UserRole a, Ghrs_RoleMenu c, Ghrs_Menu d
                                WHERE     a.IsEnable = 1
                                      AND c.IsEnable = 1
                                      AND d.IsEnable = 1
                                      AND EXISTS
                                             (SELECT 1
                                              FROM Ghrs_Role kk
                                              WHERE kk.roleid = a.roleid AND kk.IsEnable = 1)
                                      AND a.UserID = '{1}'
                                      AND a.RoleId = c.RoleID
                                      AND c.MenuId = d.MenuId
                                      AND d.MenuNo = '{0}') kk
                               UNPIVOT (fnKeyValue
                                       FOR fnKey
                                       IN (IsWaterMark,
                                          IsPWDAgain,
                                          IsFromExcel,
                                          IsToExcel,
                                          IsNew,
                                          IsDetail,
                                          IsUpdate,
                                          IsDel,
                                          IsQuery,
                                          IsPrint,
                                          IsLog,
                                          IsManual,
                                          IsCopy,
                                          IsBatchUpdate,
                                          IsBatchDel)) AS tt
                          WHERE fnKeyValue > 0
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'QuerySettingYN' fnKey,
                                 'GHR_Common_000064' fnTitle,
                                 'search' fnType,
                                 'left' position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'LabelYN' fnKey,
                                 'GHR_Common_000065' fnTitle,
                                 NULL fnType,
                                 NULL position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'MoreStyleYN' fnKey,
                                 'GHR_Common_000066' fnTitle,
                                 NULL fnType,
                                 NULL position,
                                 NULL icon
                          UNION ALL
                          SELECT NULL fnKeySeq,
                                 'GridView' fnKey,
                                 'GHR_Common_000067' fnTitle,
                                 'table' fnType,
                                 'right' position,
                                 'ant-design:setting-outlined' icon
                        ) A
                    WHERE fnKey NOT IN
                             (SELECT b.field
                              FROM Ghrs_UserPageSettingQueryColumn a, Ghrs_PageSettingQuery b
                              WHERE     a.PageSettingQueryId = b.PageSettingQueryId
                                    AND a.UserID = '{1}'
                                    AND b.PageNo = '{0}'
                                    AND a.IsEnable = 1
                                    AND b.IsEnable = 1
                                    AND a.isHidden = 'true'
                                    AND b.elementType = 'FnKey') order by fnKeySeq";

        sql = string.Format(sql, filter.menuName, App.User.ID, filter.langId);
        var toolbars = DbAccess.QueryList<Toolbar>(sql);

        sql = $@"SELECT A.RoleId, B.RoleNo, B.RoleName
                    FROM Ghrs_UserRole A LEFT JOIN Ghrs_Role B ON A.RoleId = B.RoleId
                    WHERE     A.UserID = {App.User.ID}
                          AND A.IsEnable = 1
                          AND B.IsEnable = 1
                          AND B.RoleNo LIKE 'TrainingExam%'";
        var toolbarRoles = DbAccess.QueryList<ToolbarRole>(sql);
        #endregion

        data.ForEach(x =>
        {
            x.ExamName = x.ExamName + "（" + x.ExamNo + "）";

            if (x.Status == Consts.DIC_EXAM_STATUS.RELEASED || x.Status == Consts.DIC_EXAM_STATUS.DISABLED)
            {
                x.canEdit = 0;
                x.canDelete = 0;
            }
            if (x.Status == Consts.DIC_EXAM_STATUS.DRAFT)
            {
                x.showLink = 0;
                x.showResult = 0;
                x.canClose = 0;
            }

            if (x.Status == Consts.DIC_EXAM_STATUS.DISABLED)
            {
                x.canClose = 0;
                x.canPreview = 0;
            }

            if (x.DateType == Consts.DicExamDateType.EXAM_DATE)
            {
                if (x.BeginTime != null && x.EndTime != null)
                    x.AfterHowLongLabel = DateTimeHelper.ConvertToDayString(x.BeginTime.Value) + " ~ " + DateTimeHelper.ConvertToDayString(x.EndTime.Value);
            }
            else
                x.AfterHowLongLabel = $"学完{x.AfterHowLong}天";

            x.ExamModeLabel = (x.ExamMode == "Online" || x.ExamMode.IsNullOrEmpty()) ? "线上考试" : "线下实操";

            if (!toolbars.Any(x => x.fnKey == "UpdateYN"))
                x.canEdit = 0;
            if (!toolbars.Any(x => x.fnKey == "DelYN"))
                x.canDelete = 0;
            if (!toolbars.Any(x => x.fnKey == "DetailYN"))
                x.canDetail = 0;

            if (!toolbarRoles.Any(x => x.RoleNo == "TrainingExamCopy"))
                x.canCopy = 0;
            if (!toolbarRoles.Any(x => x.RoleNo == "TrainingExamPreview"))
                x.canPreview = 0;
            if (!toolbarRoles.Any(x => x.RoleNo == "TrainingExamLink"))
                x.showLink = 0;
            if (!toolbarRoles.Any(x => x.RoleNo == "TrainingExamGrade"))
                x.showResult = 0;
            if (!toolbarRoles.Any(x => x.RoleNo == "TrainingExamArchive"))
                x.canClose = 0;
        });

        return new ServicePageResult<Ghre_ExamDto>(filter.pageNum, totalCount, filter.pageSize, data);
    }

    public async Task<ServiceResult<string>> ExportExcel(QueryExport body, string status)
    {
        QueryBody filter = new QueryBody();
        filter.pageNum = 1;
        filter.pageSize = 1000000;
        filter.langId = body.langId;

        var ids = new List<long>();
        if (body.exportSet.SelectRowKeys != null && body.exportSet.SelectRowKeys.Any())
            ids = body.exportSet.SelectRowKeys;
        body.exportSet.TitleName = "考试管理";
        var data = await QueryFilterPage1(filter, status, ids);


        string sql = $@"SELECT *
                            FROM Ghrs_PageSettingQuery
                            WHERE     IsEnable = 1
                                  AND PageNo = '{body.menuName}'
                                  AND (defaultHidden = 'false' or defaultHidden is null)
                            ORDER BY SortNo ASC";

        var columns = await Db.Ado.SqlQueryAsync<QueryExportColumn>(sql);

        var fieldDescs = new Dictionary<string, string>();
        if (body.exportSet.ExFields.Any())
            body.exportSet.ExFields.ForEach(x =>
            {
                if (columns.Any(o => o.field == x))
                    fieldDescs.Add(x, columns.FirstOrDefault(o => o.field == x)?.label);
            });
        else
            fieldDescs = columns.ToDictionary(item => item.field, item => item.label);
        var dt = ToDataTable(data.result.DT_TableDataT1, fieldDescs, null);
        // 获取所有列名
        var dtColumns = dt.Columns;

        var id = SnowFlakeSingle.instance.getID();
        var physicsPath = $"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot";
        var path = $"{$"{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}export{Path.DirectorySeparatorChar}{id}{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(physicsPath + path))
            Directory.CreateDirectory(physicsPath + path);

        path = path + body.exportSet.TitleName + ".xlsx";
        NPOIHelper.ExportExcel(dt, null, "sheet1", physicsPath + path);
        return ServiceResult<string>.OprateSuccess("导出成功", path);
    }

    public async Task<ServiceResult<DefaultGhre_ExamInput>> QueryDefault(long Id)
    {
        var input = new DefaultGhre_ExamInput();

        var companyCode = await QueryCompanyCode();

        #region baseColumns
        var baseColumns = new List<DefaultGhre_ExamColumn>
        {
            new DefaultGhre_ExamColumn()
            {
                label = "关联课程",
                field = "LinkType",
                elementType = "RadioBox",
                required = true,
                multipleSelect = false,
                editable = false,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "课程名称",
                field = "CourseId",
                elementType = "ApiSelect",
                required = true,
                multipleSelect = false,
                editable = true,
                dataSource = "/api/Common/GetSelect/Ghre_Course?FW=DOTNETCORE"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "课程场景",
                field = "CourseSceneId",
                elementType = "ApiSelect",
                required = true,
                multipleSelect = false,
                editable = true,
                dataSource = "/api/Common/GetSelect/Ghre_CourseScene?FW=DOTNETCORE"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "评分规则",
                field = "CreditRuleName",
                elementType = "Input",
                required = false,
                multipleSelect = false,
                editable = false
            },
            new DefaultGhre_ExamColumn()
            {
                label = "选择试卷",
                field = "ExamPaperId",
                elementType = "ApiSelect",
                required = true,
                multipleSelect = false,
                editable = true,
                dataSource = "/api/Ghre_ExamPaper/GetSelect?FW=DOTNETCORE&linkId="
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试编号",
                field = "ExamNo",
                elementType = "Input",
                required =false ,
                multipleSelect = false,
                editable = false,
                placeholder="保存后自动生成"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试名称",
                field = "ExamName",
                elementType = "Input",
                required = true,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试日期",
                field = "DateType",
                elementType = "RadioBox",
                required = true,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试区间",
                field = "ExamDate",
                elementType = "GRangePicker",
                required = true,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "学完多久",
                field = "AfterHowLong",
                elementType = "RadioBox",
                required = false,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试形式",
                field = "ExamMode",
                elementType = "RadioBox",
                required = false,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "评估规则",
                field = "ExamMode1",
                elementType = "RadioBox",
                required = false,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "线下考试地点",
                field = "ExamPlace",
                elementType = "Input",
                required = true,
                multipleSelect = false,
                editable = true,
            },
            new DefaultGhre_ExamColumn()
            {
                label = "是否需要学习",
                field = "IsRequireStudy",
                elementType = "RadioBox",
                required = true,
                multipleSelect = false,
                editable = true
            },
            new DefaultGhre_ExamColumn()
            {
                label = "培训计划",
                field = "PlanId",
                elementType = "ApiSelect",
                required = false,
                multipleSelect = false,
                editable = true,
                dataSource = "CommonList_TrainingPlan"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试成绩规则",
                field = "ScoreRule",
                elementType = "ApiSelect",
                required = true,
                multipleSelect = false,
                editable = true,
                dataSource = "TBParaDetail_Train_TrainScoreRule"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "学习完成规则",
                field = "StudyFinishedRule",
                elementType = "ApiSelect",
                required= companyCode == "Viavi"?false: true,
                multipleSelect = false,
                editable = true,
                dataSource = "TBParaDetail_Train_TrainStudyFinishedRule"
            },
            new DefaultGhre_ExamColumn()
            {
                label = "是否允许查看答案和解析",
                field = "IsViewResult",
                elementType = "RadioBox",
                required = true,
                multipleSelect = false,
                editable = true
            },
            new DefaultGhre_ExamColumn()
            {
                label = "  查看答案和解析后，是否允许重新考试",
                field = "IsAfterViewResultRetake",
                elementType = "RadioBox",
                required = true,
                multipleSelect = false,
                editable = true
            },
            new DefaultGhre_ExamColumn()
            {
                label = "考试说明",
                field = "RemarkSz",
                elementType = "InputTextArea",
                required = true,
                multipleSelect = false,
                editable = true
            },
            new DefaultGhre_ExamColumn()
            {
                label = "集团数据归属",
                field = "DataBelongID",
                elementType = "ApiSelect",
                required = false,
                multipleSelect = false,
                editable = true,
                dataSource = "CommonList_DataBelongAll",
                placeholder = ""
            }
        };

        if (companyCode == "Viavi")
            baseColumns = baseColumns.Where(x => x.field != "ExamMode1").ToList();
        #endregion

        #region staffTableColumns 

        var staffTableColumns = new List<DefaultGhre_ExamColumn>
        {
            new DefaultGhre_ExamColumn()
            {
                label = "工号",
                field = "StaffNo",
                align = "center",
                width=100
            },
            new DefaultGhre_ExamColumn()
            {
                label = "姓名",
                field = "StaffName",
                align = "center",
                width=100
            }, new DefaultGhre_ExamColumn()
            {
                label = "部门",
                field = "DeptName",
                align = "center",
                width=100
            },  new DefaultGhre_ExamColumn()
            {
                label = "岗位",
                field = "TitleName",
                align = "center",
                width=100
            },  new DefaultGhre_ExamColumn()
            {
                label = "入职日期",
                field = "InDate",
                align = "center",
                width=100
            },  new DefaultGhre_ExamColumn()
            {
                label = "邮箱",
                field = "Mail",
                align = "center",
                width=100
            },  new DefaultGhre_ExamColumn()
            {
                label = "数据来源",
                field = "DataSourceLabel",
                align = "center",
                width=100
            }
        };
        #endregion


        #region pageData
        if (Id != 0)
        {
            var exam = await base.QueryById(Id);
            var pageData = Mapper.Map(exam).ToANew<DefaultGhre_ExamPageData>();

            if (pageData.AfterHowLong != 7 && pageData.AfterHowLong != 15 && pageData.AfterHowLong != 30)
            {
                pageData.AfterHowLong1 = pageData.AfterHowLong;
                pageData.AfterHowLong = 0;
            }
            if (pageData.DateType == Consts.DicExamDateType.EXAM_DATE) pageData.ExamDate.Add(pageData.BeginTime.Value.ToString("yyyy/MM/dd"));
            if (pageData.DateType == Consts.DicExamDateType.EXAM_DATE) pageData.ExamDate.Add(pageData.EndTime.Value.ToString("yyyy/MM/dd"));
            input.pageData = pageData;

            //var staffs = await _ghre_ExamStaffServices.Query(x => x.ExamId == Id);

            //var ids = staffs.Select(x => Convert.ToInt32(x.StaffId)).ToList();
            var staffTableData = await QueryStaff1(Id);
            pageData.staffTableData = staffTableData.Data;
        }
        else input.pageData.ScoreRule = "Highest";
        #endregion

        input.baseColumns = baseColumns;
        input.staffTableColumns = staffTableColumns;
        return ServiceResult<DefaultGhre_ExamInput>.OprateSuccess("查询成功！", input);
    }

    public async Task<ServiceResult<List<StaffTableData>>> QueryStaff(List<int> Ids, string type)
    {
        string sql = @"SELECT A.StaffID,
                           A.StaffName,
                           A.StaffNo,
                           A.Email Mail,
                           B.DeptName,
                           C.TitleName,
                           FORMAT(A.Indate, 'yyyy/MM/dd') Indate
                    FROM Ghra_Staff A
                         LEFT JOIN Ghro_Dept B ON A.DeptID = B.DeptID
                         LEFT JOIN Ghra_Title C ON A.TitleID = C.TitleID
                         WHERE 1=1 AND A.IsEnable=1 ";

        if (Ids.Any())
            sql += $"AND StaffID IN({string.Join(",", Ids)}) ";
        else sql += "AND 1!=1 ";
        //var list = await _ghra_StaffSceneServices.Query(x => Ids.Contains(x.StaffID));

        var data = await Db.Ado.SqlQueryAsync<StaffTableData>(sql);
        data.ForEach(x =>
        {
            if (x.DataSource.IsNull())
            {
                x.DataSource = Consts.DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED;
                x.DataSourceLabel = "手动必修";
            }
        });

        return ServiceResult<List<StaffTableData>>.OprateSuccess("查询成功！", data);
    }

    public async Task<ServiceResult<List<StaffTableData>>> QueryStaff1(long examId)
    {
        string sql = @$"SELECT D.Id,
                       D.CreateTime,
                       A.StaffID,
                       A.StaffName,
                       A.StaffNo,
                       A.Email Mail,
                       B.DeptName,
                       C.TitleName,
                       FORMAT (A.Indate, 'yyyy/MM/dd') Indate,
                       D.Source DataSource
                FROM Ghre_ExamStaff D
                     LEFT JOIN Ghra_Staff A ON A.StaffID = D.StaffId
                     LEFT JOIN Ghro_Dept B ON A.DeptID = B.DeptID
                     LEFT JOIN Ghra_Title C ON A.TitleID = C.TitleID
                WHERE 1 = 1 AND A.IsEnable = 1  AND D.IsEnable = 1 AND D.ExamId = '{examId}'
                ORDER BY D.CreateTime ASC";

        var data = await Db.Ado.SqlQueryAsync<StaffTableData>(sql);
        data.ForEach(async x =>
        {
            if (x.DataSource.IsNull())
                x.DataSource = DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED;

            if (x.DataSource == DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED)
                x.DataSourceLabel = "手动必修";
            else if (x.DataSource == DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED)
                x.DataSourceLabel = "必修规则";
            else if (x.DataSource == DIC_EXAM_STAFF_SOURCE.MANUAL_ELECTIVE)
                x.DataSourceLabel = "手动选修";

        });




        return ServiceResult<List<StaffTableData>>.OprateSuccess("查询成功！", data);
    }

    public async Task<ServiceResult<long>> Insert1(long id, DefaultGhre_ExamPageData pageData)
    {
        if (pageData.DateType == Consts.DicExamDateType.EXAM_DATE)
        {
            pageData.BeginTime = Convert.ToDateTime(pageData.ExamDate[0]);
            pageData.EndTime = Convert.ToDateTime(pageData.ExamDate[1]);
        }
        if (pageData.AfterHowLong == 0)
            pageData.AfterHowLong = pageData.AfterHowLong1;

        if (pageData.CourseId != null)
        {

            var course = await Db.Queryable<Ghre_Course>().Where(it => it.Id == pageData.CourseId).FirstAsync();
            pageData.CoverUrl = course?.CoverUrl;
            pageData.UseDefaultCoverImage = course?.UseDefaultCoverImage;
            pageData.DefaultCoverImageName = course?.DefaultCoverImageName;
        }
        else if (pageData.CourseSceneId != null)
        {
            var course = await Db.Queryable<Ghre_CourseScene>().Where(it => it.Id == pageData.CourseSceneId).FirstAsync();
            pageData.CoverUrl = course?.CoverUrl;
            pageData.UseDefaultCoverImage = course?.UseDefaultCoverImage;
            pageData.DefaultCoverImageName = course?.DefaultCoverImageName;
        }

        if (string.IsNullOrWhiteSpace(pageData.ExamNo))
            pageData.ExamNo = await GenerateContinuousSequence();

        if (id == 0)
        {
            if (string.IsNullOrEmpty(pageData.Status))
                pageData.Status = Consts.DIC_EXAM_STATUS.DRAFT;
            var insert = Mapper.Map(pageData).ToANew<InsertGhre_ExamInput>();
            id = await base.Add(insert);

            var insertStaffs = pageData.staffTableData
                .Where(x => x.DataSource == Consts.DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED)
                .Select(x => new InsertGhre_ExamStaffInput()
                {
                    ExamId = id,
                    StaffId = x.StaffID,
                    Source = x.DataSource ?? Consts.DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED
                }).ToList();

            await _ghre_ExamStaffServices.Add(insertStaffs);

            insertStaffs = pageData.staffTableData
                .Where(x => x.DataSource == Consts.DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED)
                .Select(x => new InsertGhre_ExamStaffInput()
                {
                    ExamId = id,
                    StaffId = x.StaffID,
                    Source = x.DataSource ?? Consts.DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED
                }).ToList();

            await _ghre_ExamStaffServices.Add(insertStaffs);

            await Db.Updateable<Ghre_ExamPaper>().SetColumns(it => it.LinkTimes == it.LinkTimes + 1).Where(it => it.Id == insert.ExamPaperId).ExecuteCommandAsync();

            return ServiceResult<long>.OprateSuccess("添加成功！", id);
        }
        else
        {
            var exam = await base.QueryById(id);
            if (exam.ExamPaperId != pageData.ExamPaperId)
            {
                await Db.Updateable<Ghre_ExamPaper>().SetColumns(it => it.LinkTimes == it.LinkTimes - 1).Where(it => it.Id == exam.ExamPaperId && it.LinkTimes > 0).ExecuteCommandAsync();
                await Db.Updateable<Ghre_ExamPaper>().SetColumns(it => it.LinkTimes == it.LinkTimes + 1).Where(it => it.Id == pageData.ExamPaperId).ExecuteCommandAsync();
            }
            var insert = Mapper.Map(pageData).ToANew<EditGhre_ExamInput>();
            await base.Update(id, insert);
            await _ghre_ExamStaffServices.Delete(x => x.ExamId == id && (x.Source == DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED || x.Source == null));
            var staffIds = await Db.Queryable<Ghre_ExamStaff>().Where(x => x.ExamId == id && x.Source == DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED).Select(x => x.StaffId).ToListAsync();

            var insertStaffs = pageData.staffTableData.Where(x => x.DataSource == DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED)
                .Select(x => new InsertGhre_ExamStaffInput()
                {
                    ExamId = id,
                    StaffId = x.StaffID,
                    Source = x.DataSource ?? DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED
                }).ToList();

            await _ghre_ExamStaffServices.Add(insertStaffs);

            insertStaffs = pageData.staffTableData
               .Where(x => !staffIds.Contains(x.StaffID) && x.DataSource == DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED)
               .Select(x => new InsertGhre_ExamStaffInput()
               {
                   ExamId = id,
                   StaffId = x.StaffID,
                   Source = x.DataSource ?? DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED
               }).ToList();

            await _ghre_ExamStaffServices.Add(insertStaffs);

            if (exam.Status == DIC_EXAM_STATUS.RELEASED)
                await GenerateStudy(id);

            return ServiceResult<long>.OprateSuccess("修改成功！", id);
        }
    }


    //public bool IsTimePeriodOverlap(DateTime firstStart, DateTime firstEnd, DateTime secondStart, DateTime secondEnd)
    //{ 
    //    if (!(secondEnd < firstStart || secondStart > firstEnd))
    //    {
    //        return true ;
    //    }
    //    return false;
    //}

    public async Task<ServiceResult> UpdateStatus(long[] ids, string status)
    {

        if (status == Consts.DIC_EXAM_STATUS.DRAFT)
        {
            string sql = $@"SELECT A.RoleId, B.RoleNo, B.RoleName
                    FROM Ghrs_UserRole A LEFT JOIN Ghrs_Role B ON A.RoleId = B.RoleId
                    WHERE     A.UserID = {App.User.ID}
                          AND A.IsEnable = 1
                          AND B.IsEnable = 1
                          AND B.RoleNo ='TrainingExamCancelRelease'";
            var toolbarRoles = DbAccess.QueryList<ToolbarRole>(sql);
            if (!toolbarRoles.Any())
                return ServiceResult.OprateFailed("暂无取消发布权限！");
        }

        if (status != Consts.DIC_EXAM_STATUS.RELEASED && status != Consts.DIC_EXAM_STATUS.DISABLED && status != Consts.DIC_EXAM_STATUS.DRAFT)
            throw new Exception("无效的状态");

        HttpRequest request = UserContext.Context.Request;
        var api = request.Path.ObjToString().TrimEnd('/').ToLower();
        var ip = GetUserIp(UserContext.Context);

        var entities = new List<Ghre_Exam>();
        foreach (var id in ids)
        {
            if (id == null || !BaseDal.Any(id))
                continue;

            var entity = await BaseDal.QueryById(id);

            BasePoco ent = entity;
            ent.UpdateIP = ip;
            ent.UpdateProg = api;

            if (entity.Status == DIC_EXAM_STATUS.RELEASED && status == DIC_EXAM_STATUS.DRAFT)
            {
                if (await Db.Queryable<Ghre_StudyRecord>().AnyAsync(x => x.ExamId == id && x.StudyStatus != DIC_STUDY_RECORD_STUDY_STATUS.NO_JOIN))
                    return ServiceResult.OprateFailed("已有学员正在学习，不可取消发布！");

                if (await _ghre_ExamRecordServices.AnyAsync(x => x.ExamId == id && x.Status == DIC_EXAM_RECORD_STATUS.EXAMING))
                    return ServiceResult.OprateFailed("已有学员参与考试，不可取消发布！");
            }
            //--如果考试时间是区间，那过了这个区间后，员工学完也考不了。那么，只要是过了考试时间，后台可以手动归档。
            //--如果考试时间是学完多久，需要等1）员工考完；2）员工没考完但过了学习时间，才能归档。

            if (entity.Status == DIC_EXAM_STATUS.RELEASED && status == DIC_EXAM_STATUS.DISABLED)
            {
                if (entity.DateType == DicExamDateType.EXAM_DATE)
                {
                    if (entity.BeginTime.Value.Date <= DateTime.Now.Date && entity.EndTime.Value.Date >= DateTime.Now.Date)
                    {
                        if (await Db.Queryable<Ghre_StudyRecord>().AnyAsync(x => x.ExamId == id && x.StudyStatus != DIC_STUDY_RECORD_STUDY_STATUS.NO_JOIN))
                            return ServiceResult.OprateFailed("已有学员正在学习，不可归档！");

                        if (await _ghre_ExamRecordServices.AnyAsync(x => x.ExamId == id && x.Status == DIC_EXAM_RECORD_STATUS.EXAMING))
                            return ServiceResult.OprateFailed("已有学员参与考试，不可归档！");
                    }
                }
                else
                {

                    if (await Db.Queryable<Ghre_StudyRecord>().AnyAsync(x => x.ExamId == id && x.StudyStatus != DIC_STUDY_RECORD_STUDY_STATUS.NO_JOIN))
                        return ServiceResult.OprateFailed("已有学员正在学习，不可归档！");

                    if (await _ghre_ExamRecordServices.AnyAsync(x => x.ExamId == id && x.Status == DIC_EXAM_RECORD_STATUS.EXAMING))
                        return ServiceResult.OprateFailed("已有学员参与考试，不可归档！");
                }

            }

            if (entity.Status == DIC_EXAM_STATUS.DISABLED && status == DIC_EXAM_STATUS.RELEASED)
            {

                #region 判断相关联课件是否存在
                if (entity.IsRequireStudy == true)
                {
                    var sql = $@"SELECT count(0)
                            FROM Ghre_CourseWare A
                            WHERE A.Id IN
                                     (SELECT CourseWareId
                                      FROM Ghre_Course
                                      WHERE    (Id = '{entity.CourseId}'  OR CourseSceneId = '{entity.CourseSceneId}' OR CourseSceneIds like '%{entity.CourseSceneId}%') AND IsEnable = 1)";
                    var count = await Db.Ado.GetIntAsync(sql);
                    if (count == 0)
                    {
                        if (entity.LinkType == "CourseId")
                            return ServiceResult.OprateFailed("该考试关联的课程未维护课件，暂不可取消归档！");
                    }
                }
                #endregion

                if (entity.LinkType == "CourseId")
                {
                    var course = await _ghre_CourseServices.QueryById(entity.CourseId);
                    if (course is null)
                        return ServiceResult.OprateFailed("该考试关联的课程已被删除，暂不可取消归档！");

                    if (course.Status != Consts.DIC_EXAM_STATUS.RELEASED)
                        return ServiceResult.OprateFailed($"该考试关联的课程【{course.CourseName}({course.CourseNo})】状态为【{(course.Status == Consts.DIC_EXAM_STATUS.DISABLED ? "已停用" : "草稿箱")}】，暂不可取消归档！");
                }

                var examPaper = await _ghre_ExamPaperServices.QueryById(entity.ExamPaperId);
                if (examPaper is null)
                    return ServiceResult.OprateFailed("该考试关联的试卷已被删除，暂不可取消归档！");
                if (examPaper.Status != Consts.DIC_EXAM_STATUS.RELEASED)
                    return ServiceResult.OprateFailed($"该考试关联的试卷【{examPaper.PaperName}({examPaper.PaperNo})】状态为【{(examPaper.Status == Consts.DIC_EXAM_STATUS.DISABLED ? "已停用" : "草稿箱")}】，暂不可取消归档！");
            }

            if (status == DIC_EXAM_STATUS.RELEASED && entity.Status == DIC_EXAM_STATUS.DRAFT)
            {

                #region 判断相关联课件是否存在
                if (entity.IsRequireStudy == true)
                {
                    var sql = $@"SELECT count(0)
                            FROM Ghre_CourseWare A
                            WHERE A.Id IN
                                     (SELECT CourseWareId
                                      FROM Ghre_Course
                                      WHERE    (Id = '{entity.CourseId}'  OR CourseSceneId = '{entity.CourseSceneId}' OR CourseSceneIds like '%{entity.CourseSceneId}%') AND IsEnable = 1)";
                    var count = await Db.Ado.GetIntAsync(sql);
                    if (count == 0)
                    {
                        if (entity.LinkType == "CourseId")
                            return ServiceResult.OprateFailed("该考试关联的课程未维护课件，暂不可取消归档！");
                    }
                }
                #endregion

                if (entity.LinkType == DIC_EXAM_LINK_TYPE_COURSE)
                {
                    var ids1 = await Db.Queryable<Ghre_Exam>()
                        .Where(x =>
                        x.Source == null &&
                        x.Status == DIC_EXAM_STATUS.RELEASED &&
                        x.CourseId == entity.CourseId &&
                        x.Id != id)
                        .Select(x => x.Id).ToListAsync();

                    #region 校验同一个课程下 考试时间不允许重叠
                    var exams = await base.Query(x => (
                    x.Source == null &&
                    x.Status == DIC_EXAM_STATUS.RELEASED && x.CourseId == entity.CourseId && x.Id != id) || ids1.Contains(x.Id));
                    if (exams.Any())
                        for (int i = 0; i < exams.Count; i++)
                        {
                            var exam = exams[i];
                            var course = await _ghre_CourseServices.QuerySingle(entity.CourseId);
                            if (exam.DateType != entity.DateType || exam.DateType == DicExamDateType.AFTER_HOW_LONG)
                                return ServiceResult.OprateFailed($"课程【{course.CourseName}】已存在有效的考试管理数据【{exam.ExamName}({exam.ExamNo})】！");

                            else if (exam.DateType == DicExamDateType.EXAM_DATE)
                            {
                                if ((entity.BeginTime >= exam.BeginTime && entity.BeginTime <= exam.EndTime)
                                    || (entity.EndTime >= exam.BeginTime && entity.EndTime <= exam.EndTime)
                                    || (exam.EndTime >= entity.BeginTime && exam.EndTime <= entity.EndTime)
                                    || (exam.EndTime >= entity.BeginTime && exam.EndTime <= entity.EndTime))
                                    //if (IsTimePeriodOverlap(entity.BeginTime.Value.Date, entity.EndTime.Value.Date, exam.BeginTime.Value.Date, exam.EndTime.Value.Date))
                                    return ServiceResult.OprateFailed($"课程【{course.CourseName}】已存在有效的考试管理数据【{exam.ExamName}({exam.ExamNo})】时间重叠！");
                            }
                        }
                    #endregion
                }
                else
                {
                    var ids1 = await Db.Queryable<Ghre_Exam>()
                        .Where(x =>
                        x.Source == null &&
                        x.Status == DIC_EXAM_STATUS.RELEASED &&
                        x.CourseSceneId == entity.CourseSceneId &&
                        x.Id != id)
                        .Select(x => x.Id).ToListAsync();
                    #region 校验同一个课程场景下 考试时间不允许重叠
                    var exams = await base.Query(x => (x.Source == null && x.Status == DIC_EXAM_STATUS.RELEASED && x.CourseSceneId == entity.CourseSceneId && x.Id != id) || ids1.Contains(x.Id));
                    if (exams.Any())
                        for (int i = 0; i < exams.Count; i++)
                        {
                            var exam = exams[i];
                            var course = await _ghre_CourseSceneServices.QuerySingle(entity.CourseSceneId);
                            if (exam.DateType != entity.DateType || exam.DateType == DicExamDateType.AFTER_HOW_LONG)
                                return ServiceResult.OprateFailed($"课程场景【{course.CourseName}】已存在有效的考试管理数据【{exam.ExamName}({exam.ExamNo})】！");

                            else if (exam.DateType == DicExamDateType.EXAM_DATE)
                            {
                                if ((entity.BeginTime >= exam.BeginTime && entity.BeginTime <= exam.EndTime)
                                    || (entity.EndTime >= exam.BeginTime && entity.EndTime <= exam.EndTime)
                                    || (exam.EndTime >= entity.BeginTime && exam.EndTime <= entity.EndTime)
                                    || (exam.EndTime >= entity.BeginTime && exam.EndTime <= entity.EndTime))
                                    return ServiceResult.OprateFailed($"课程场景【{course.CourseName}】已存在有效的考试管理数据【{exam.ExamName}({exam.ExamNo})】时间重叠！");
                            }
                        }
                    #endregion
                }
            }

            #region 生成学习记录
            if (status == Consts.DIC_EXAM_STATUS.RELEASED && entity.Status == Consts.DIC_EXAM_STATUS.DRAFT)
                await GenerateStudy(id);
            #endregion

            #region 考试取消，学习记录与考试解绑
            if (entity.Status == DIC_EXAM_STATUS.RELEASED && status == DIC_EXAM_STATUS.DRAFT)
                await Db.Updateable<Ghre_StudyRecord>()
                        .SetColumns(it => new Ghre_StudyRecord() { ExamId = null })
                        .Where(it => it.ExamId != null && it.ExamId == id)
                        .ExecuteCommandAsync();

            #endregion

            if (status == DIC_EXAM_STATUS.RELEASED || status == DIC_EXAM_STATUS.DISABLED || status == DIC_EXAM_STATUS.DRAFT)
            {
                entity.Status = status;
                await BaseDal.Update(entity);
            }
        }

        //var result = await BaseDal.Update(entities);
        if (status == DIC_EXAM_STATUS.RELEASED)
            return ServiceResult.OprateSuccess("发布成功！");
        else if (status == DIC_EXAM_STATUS.DRAFT)
            return ServiceResult.OprateSuccess("取消发布成功！");
        else
            return ServiceResult.OprateSuccess("归档成功！");

    }

    #region 生成学习记录
    public async Task GenerateStudy(long id)
    {
        var entity = await BaseDal.QueryById(id);
        var staffs = await _ghre_ExamStaffServices.Query(x => x.ExamId == id && x.Source == Consts.DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED);
        var inserts = new List<Ghre_StudyRecord>();

        var snap = await Db.Queryable<Ghre_CourseSnap>().FirstAsync(x => x.CourseId == entity.CourseId);
        var courseTime = Db.GetDate();
        var courseEndTime = courseTime.Date.AddMonths(snap?.ValidityPeriod ?? 1);

        #region 自动给学习记录匹配考试ID
        var studyRecords = await Db.Queryable<Ghre_StudyRecord>()
            .WhereIF(!entity.CourseId.IsNull(), x => x.CourseId == entity.CourseId)
            .WhereIF(!entity.CourseSceneId.IsNull(), x => x.CourseSceneId == entity.CourseSceneId)
            .Where(x => x.ExamId == null).ToListAsync();
        studyRecords.ForEach(x => x.ExamId = entity.Id);
        await Db.Updateable(studyRecords).UpdateColumns(it => new { it.ExamId }).ExecuteCommandAsync();
        #endregion

        //if (entity.DateType == DicExamDateType.EXAM_DATE)
        //{
        //    courseEndTime = entity.EndTime.Value.Date.AddDays(1);
        //    courseTime = courseEndTime.AddDays(-(snap?.ValidityPeriod ?? 1) * 30);
        //}

        for (int i = 0; i < staffs.Count; i++)
        {
            var staff = staffs[i];

            var exist = await Db.Queryable<Ghre_StudyRecord>()
            .WhereIF(!entity.CourseId.IsNull(), x => x.CourseId == entity.CourseId)
            .WhereIF(!entity.CourseSceneId.IsNull(), x => x.CourseSceneId == entity.CourseSceneId)
            //.Where(x => (x.CourseBeginTime <= courseTime.Date && x.CourseEndTime >= courseTime.Date) ||
            //(x.CourseBeginTime <= courseEndTime && x.CourseEndTime >= courseEndTime))
            .Where(x => x.ExamId == id && x.StaffId == staff.StaffId && x.CourseType != "ExcelImport").AnyAsync();

            if (!exist)
                inserts.Add(new Ghre_StudyRecord()
                {
                    StaffId = staff.StaffId,
                    ExamId = id,
                    CourseSnapId = snap?.Id,
                    CourseId = entity.CourseId,
                    CourseSceneId = entity.CourseSceneId,
                    JoinTime = courseTime,
                    CourseBeginTime = courseTime.Date,
                    CourseEndTime = courseEndTime,
                    CourseType = DIC_EXAM_STAFF_SOURCE.MANUAL_REQUIRED,
                    CourseStatus = Consts.DIC_STUDY_RECORD_COURSE_STATUS_IN,
                    StudyStatus = DIC_STUDY_RECORD_STUDY_STATUS.NO_JOIN,
                    PlanId = entity.PlanId,
                });
        }
        if (inserts.Any())
            await Db.Insertable(inserts).ExecuteReturnSnowflakeIdListAsync();
    }
    #endregion

    public async Task<ServiceResult> GetStatus(long id)
    {
        string sql = $@"SELECT A.RoleId, B.RoleNo, B.RoleName
                    FROM Ghrs_UserRole A LEFT JOIN Ghrs_Role B ON A.RoleId = B.RoleId
                    WHERE     A.UserID = {App.User.ID}
                          AND A.IsEnable = 1
                          AND B.IsEnable = 1
                          AND B.RoleNo ='TrainingExamCancelRelease'";
        var toolbarRoles = DbAccess.QueryList<ToolbarRole>(sql);
        if (!toolbarRoles.Any())
            return ServiceResult.OprateFailed("暂无取消发布权限！");

        if (await _ghre_ExamRecordServices.AnyAsync(x => x.ExamId == id))
            return ServiceResult.OprateFailed("已有学员参与考试，不可取消发布！");

        return ServiceResult.OprateSuccess("可以取消发布！");

    }

    public async Task<ServiceResult> StartExam(long id)
    {

        if (!await _ghre_ExamRecordServices.AnyAsync(x => x.ExamId == id))
        {
            var dt = DateTime.Now;
            var exam = await base.QueryById(id);


            if (exam.DateType == DicExamDateType.EXAM_DATE)
            {
                if (exam.BeginTime > dt)
                    return ServiceResult.OprateFailed("考试尚未开始！");

                if (exam.EndTime < dt)
                    return ServiceResult.OprateFailed("考试已结束！");
            }
            var insrt = new InsertGhre_ExamRecordInput()
            {
                ExamId = id,
                //UserId = UserContext.Current.User_Id,
                //ActualBeginTime = dt,
                ExamPaperId = exam.ExamPaperId
            };
            await _ghre_ExamRecordServices.Add(insrt);
        }

        return ServiceResult.OprateSuccess("操作成功！");

    }

    public async Task<string> GenerateContinuousSequence()
    {
        try
        {
            string prefixTemp = "E";
            string result = string.Empty;
            string tableCode = "Ghre_Exam";
            string columnCode = "ExamNo";
            int length = 6;
            int tempLength = 5;
            int sequence;

            #region 查询
            DbSelect dbSelect = new DbSelect(tableCode + " A", "A", null);
            dbSelect.IsInitDefaultValue = false;

            if (!string.IsNullOrEmpty(prefixTemp))
                dbSelect.Select("MAX(SUBSTRING(A." + columnCode + "," + (prefixTemp.Length + 1).ToString() + "," + tempLength.ToString() + "))");
            else
                dbSelect.Select("MAX(A." + columnCode + ")");
            //}
            //dbSelect.Select("MAX(CONVERT(DECIMAL,SUBSTRING(A.ISSUE_NO," + (prefix.Length + dateString.Length + 1).ToString() + "," + tempLength.ToString() + ")))");
            if (!string.IsNullOrEmpty(prefixTemp))
                dbSelect.Where("SUBSTRING(A." + columnCode + ",1," + (prefixTemp.Length).ToString() + ")", " = ", prefixTemp);
            dbSelect.Where("LEN(A." + columnCode + ")", "=", length);
            string sql = dbSelect.GetSql();
            //await Db.Ado.GetScalarAsync(sql)
            string maxSequence = Convert.ToString(await Db.Ado.GetScalarAsync(sql));
            #endregion
            //tempLength = tempLength - dateString.Length;
            if (string.IsNullOrEmpty(maxSequence))
                result = prefixTemp + Convert.ToString(1).PadLeft(tempLength, '0');
            else
            {
                if (!string.IsNullOrEmpty(prefixTemp))
                {
                    if (int.TryParse(maxSequence, out sequence))
                    {
                        sequence += 1;
                        if (sequence.ToString().Length > tempLength)
                            throw new Exception("自动生成字串长度已经超过设定长度!");
                    }
                    else
                        throw new Exception("表中的数据无法进行自动编号,请联系软件开发商!");
                    result = prefixTemp + sequence.ToString().PadLeft(tempLength, '0');
                }
                else
                {
                    if (int.TryParse(maxSequence, out sequence))
                    {
                        sequence += 1;
                        if (sequence.ToString().Length > length)
                            throw new Exception("自动生成字串长度已经超过设定长度!");
                    }
                    else
                        throw new Exception("表中的数据无法进行自动编号,请联系软件开发商!");
                    result = sequence.ToString().PadLeft(length, '0');
                }
            }
            return result;
        }
        catch (Exception) { throw; }
    }


    public async Task<ServiceResult> Copy(long id)
    {
        var exam = await base.QueryById(id);
        var staffs = await _ghre_ExamStaffServices.Query(x => x.ExamId == id);
        exam.Status = Consts.DIC_EXAM_STATUS.DRAFT;

        var insert = Mapper.Map(exam).ToANew<InsertGhre_ExamInput>();

        insert.ExamNo = await GenerateContinuousSequence();
        insert.ExamName = insert.ExamName + "_复制";
        var insertStaffs = Mapper.Map(exam).ToANew<List<InsertGhre_ExamStaffInput>>();
        id = await base.Add(insert);
        insertStaffs.ForEach(x => x.ExamId = id);
        if (insertStaffs.Any())
            await _ghre_ExamStaffServices.Add(insertStaffs);

        return ServiceResult.OprateSuccess("复制成功！");

    }

    public async Task<ServiceResult<Ghre_ExamMessageLogDto>> QueryMessageLog(long id)
    {

        var exam = await base.QueryById(id);
        if (exam is null)
            return ServiceResult<Ghre_ExamMessageLogDto>.OprateFailed("无效的考试ID！");

        var view = await _ghre_ExamMessageLogServices.QuerySingleDto(x => x.ExamId == id);
        if (view == null) view = new Ghre_ExamMessageLogDto()
        {
            NotificationScope = "All",
            IsFirstEntry = true,
        };

        if (view.IsFirstEntry == false)
            view.NotificationScope = "Need";
        if (!string.IsNullOrWhiteSpace(view.SendType))
            view.SendExamMessage = JsonConvert.DeserializeObject<List<string>>(view.SendType);

        var examPaper = await _ghre_ExamPaperServices.QueryById(exam.ExamPaperId);
        view.ExamName = exam.ExamName;
        if (exam.BeginTime != null)
            view.BeginDate = exam.BeginTime.Value.ToString("yyyy-MM-dd");

        if (exam.EndTime != null)
            view.EndDate = exam.EndTime.Value.ToString("yyyy-MM-dd");
        view.ExamMode = exam.ExamMode;
        view.RemarkSz = exam.RemarkSz;
        view.AnswerTime = examPaper.AnswerTime;
        view.Id = id;

        return ServiceResult<Ghre_ExamMessageLogDto>.OprateSuccess("查询成功！", view);

    }

    public async Task<ServiceResult> InsertMessageLog(Ghre_ExamMessageLogDto insert)
    {
        insert.IsFirstEntry = false;
        insert.SendType = JsonConvert.SerializeObject(insert.SendExamMessage);
        Db.Updateable<Ghre_ExamMessageLog>().SetColumns(it => it.IsEnable == 0).Where(it => it.ExamId == insert.Id && it.IsEnable == 1).ExecuteCommand();

        var insert1 = Mapper.Map(insert).ToANew<InsertGhre_ExamMessageLogInput>();
        insert1.ExamId = insert.Id;
        await _ghre_ExamMessageLogServices.Add(insert1);
        return ServiceResult.OprateSuccess("提交成功！");

    }

    public async Task<ServiceResult> SystemMark(long id)
    {

        var record = await Db.Queryable<Ghre_ExamRecord>().FirstAsync(x => x.Id == id);
        var examPaper = await _ghre_ExamPaperServices.QuerySingleDto(x => x.Id == record.ExamPaperId && x.ScoreMethod == "System");
        if (examPaper != null)
        {
            var details = await Db.Queryable<Ghre_ExamRecordDetail>().Where(x => x.ExamRecordId == record.Id).ToListAsync();
            var detailIds = details.Select(x => x.Id).ToList();
            var recordAnswers = await Db.Queryable<Ghre_ExamRecordAnswer>()
                .OrderBy(x => x.TaxisNo)
                .Where(x => x.ExamRecordDetailId != null && detailIds.Contains(x.ExamRecordDetailId.Value))
                .ToListAsync();
            await ExamHelper.SystemMarkAsync(Db, record, details, recordAnswers);

            await Db.Updateable(details).UpdateColumns(it => new { it.Score }).ExecuteCommandAsync();
            await Db.Updateable(record).UpdateColumns(it => new { it.Score }).ExecuteCommandAsync();
        }

        return ServiceResult.OprateSuccess("提交成功！");
    }

    public async Task<ServiceResult<List<StaffTableData>>> QueryRuleStaff(long examId)
    {


        var Ids = await Db.Queryable<Ghre_StudyRecord>()
            .Where(x => x.ExamId == null && (x.CourseId == examId || x.CourseSceneId == examId))
            .Select(x => x.StaffId).Distinct().ToListAsync();
        string sql = @"SELECT A.StaffID,
                           A.StaffName,
                           A.StaffNo,
                           A.Email Mail,
                           B.DeptName,
                           C.TitleName,
                           FORMAT(A.Indate, 'yyyy/MM/dd') Indate
                    FROM Ghra_Staff A
                         LEFT JOIN Ghro_Dept B ON A.DeptID = B.DeptID
                         LEFT JOIN Ghra_Title C ON A.TitleID = C.TitleID
                         WHERE 1=1 AND A.IsEnable=1 ";

        //var list = await _ghra_StaffSceneServices.Query(x => Ids.Contains(x.StaffID));
        if (Ids.Any())
            sql += $"AND StaffID IN({string.Join(",", Ids)}) ";
        else sql += "AND 1!=1 ";
        var data = await Db.Ado.SqlQueryAsync<StaffTableData>(sql);
        data.ForEach(async x =>
        {
            if (x.DataSource.IsNull())
            {
                x.DataSource = Consts.DIC_EXAM_STAFF_SOURCE.STUDY_RULE_REQUIRED;
                x.DataSourceLabel = "必修规则";
            }

            //if (!await _ghre_ExamStaffServices.AnyAsync(o => o.ExamId == examId && o.StaffId == x.StaffID))
            //{
            //    var insertStaffs = new InsertGhre_ExamStaffInput()
            //    {
            //        ExamId = examId,
            //        StaffId = x.StaffID,
            //        Source = x.DataSource
            //    };
            //    await _ghre_ExamStaffServices.Add(insertStaffs);
            //}

        });




        return ServiceResult<List<StaffTableData>>.OprateSuccess("查询成功！", data);
    }


    public async Task<ServiceResult> ExecuteExamOver()
    {
        var now = Db.GetDate();

        var exams = await Db.Queryable<Ghre_Exam>().Where(x => x.Status == DIC_EXAM_STATUS.RELEASED &&
        ((x.DateType == DicExamDateType.EXAM_DATE && x.EndTime != null && x.EndTime.Value.Date == now.Date.AddDays(-1)) || x.DateType == DicExamDateType.AFTER_HOW_LONG)
        ).ToListAsync();

        for (int i = 0; i < exams.Count; i++)
        {
            var exam = exams[i];
            //if (await Db.Queryable<Ghre_ExamPaper>().Where(x => x.ScoreMethod == DIC_EXAM_PAPER_SCORE_METHOD.MANUAL && x.Id == exam.ExamPaperId).AnyAsync())
            //    continue;

            var studyRecords = new List<Ghre_StudyRecord>();
            if (exam.DateType == DicExamDateType.EXAM_DATE)
            {
                #region 固定日期
                studyRecords = await Db.Queryable<Ghre_StudyRecord>()
                  .Where(x => x.ExamId == exam.Id && x.StudyStatus != DIC_STUDY_RECORD_STUDY_STATUS.HAS_FINISH)
                  .ToListAsync();
                #endregion
            }
            else
            {
                #region 考完周期
                exam.AfterHowLong = (exam.AfterHowLong ?? 7) + 1;
                var examEndDt = now.AddDays(-exam.AfterHowLong.Value);
                studyRecords = await Db.Queryable<Ghre_StudyRecord>()
                    .Where(x =>
                    x.ExamId == exam.Id &&
                    x.StudyStatus != DIC_STUDY_RECORD_STUDY_STATUS.HAS_FINISH &&
                    x.CourseEndTime != null &&
                    x.CourseEndTime.Value.Date == examEndDt.Date)
                    .ToListAsync();
                #endregion
            }
            var inserts = new List<Ghre_ExamRecord>();
            var updates = new List<Ghre_ExamRecord>();

            if (studyRecords.Any())
            {

                for (int j = 0; j < studyRecords.Count; j++)
                {
                    studyRecords[j].StudyStatus = DIC_STUDY_RECORD_STUDY_STATUS.HAS_FINISH;
                    studyRecords[j].RemarkSz = "考试结束自动关闭！";

                    var examRecord = await Db.Queryable<Ghre_ExamRecord>().Where(x => x.StudyRecordId == studyRecords[j].Id && x.ExamId == exam.Id).FirstAsync();
                    if (examRecord.IsNull())
                    {
                        inserts.Add(new Ghre_ExamRecord()
                        {
                            Id = SnowFlakeSingle.Instance.NextId(),
                            ExamId = exam.Id,
                            StudyRecordId = studyRecords[j].Id,
                            StaffId = studyRecords[j].StaffId,
                            CourseSnapId = studyRecords[j].CourseSnapId,
                            ExamPaperId = exam.ExamPaperId,
                            ExamDate = DateTime.Now.Date.AddDays(-1),
                            RetakeTimes = 0,
                            Score = 0,
                            AdjustScore = 0,
                            IsPass = false,
                            Comment = "考试结束自动出分！",
                            Status = DIC_EXAM_RECORD_STATUS.SYSTEM_END,
                            ScoreStatus = DIC_EXAM_RECORD_SCORE_STATUS.HAS_SCORE,
                            BeginTime = exam.DateType == DicExamDateType.EXAM_DATE ? exam.BeginTime : DateTime.Now.Date.AddDays(-exam.AfterHowLong.Value),
                            EndTime = exam.DateType == DicExamDateType.EXAM_DATE ? exam.EndTime : DateTime.Now.Date.AddDays(-1)
                        });
                    }
                    else
                    {
                        examRecord.Score = 0;
                        examRecord.AdjustScore = 0;
                        examRecord.Status = DIC_EXAM_RECORD_STATUS.SYSTEM_END;
                        examRecord.ScoreStatus = DIC_EXAM_RECORD_SCORE_STATUS.HAS_SCORE;
                        examRecord.IsPass = false;
                        examRecord.Comment = "考试结束自动出分！";
                        updates.Add(examRecord);
                    }
                }

                await Db.Insertable(inserts).ExecuteCommandAsync();
                await Db.Updateable(updates).UpdateColumns(it => new { it.Score, it.AdjustScore, it.Status, it.ScoreStatus, it.IsPass, it.Comment }).ExecuteCommandAsync();
                await Db.Updateable(studyRecords).UpdateColumns(it => new { it.StudyStatus, it.RemarkSz }).ExecuteCommandAsync();
            }
        }

        //var Ghre_Courses = await Db.Queryable<Ghre_Course>().ToListAsync();
        //for (int i = 0; i < Ghre_Courses.Count; i++)
        //{
        //    #region 课程ID 同步至课件
        //    if (!Ghre_Courses[i].CourseWareId.IsNull())
        //    {
        //        var wares = await Db.Queryable<Ghre_CourseWare>().Where(x => x.CourseIds.Contains(Ghre_Courses[i].Id.ToString()) && x.Id != Ghre_Courses[i].CourseWareId).ToListAsync();
        //        for (int j = 0; j < wares.Count; j++)
        //        {
        //            var courseIds2 = new List<long>();
        //            if (!string.IsNullOrWhiteSpace(wares[j].CourseIds))
        //                courseIds2 = JsonConvert.DeserializeObject<List<long>>(wares[j].CourseIds);
        //            courseIds2 = courseIds2.Where(x=>x!= Ghre_Courses[i].Id).ToList();
        //            wares[j].CourseIds = JsonConvert.SerializeObject(courseIds2);
        //        }
        //        await Db.Updateable(wares).UpdateColumns(it => new { it.CourseIds }).ExecuteCommandAsync();

        //        var ware = await Db.Queryable<Ghre_CourseWare>().FirstAsync(x => x.Id == Ghre_Courses[i].CourseWareId);
        //        if (ware != null)
        //        {
        //            var courseIds2 = new List<long>();
        //            if (!string.IsNullOrWhiteSpace(ware.CourseIds))
        //                courseIds2 = JsonConvert.DeserializeObject<List<long>>(ware.CourseIds);

        //            if (!courseIds2.Any(x => x == Ghre_Courses[i].Id))
        //            {
        //                courseIds2.Add(Ghre_Courses[i].Id);

        //                ware.CourseIds = JsonConvert.SerializeObject(courseIds2);
        //                await Db.Updateable<Ghre_CourseWare>().SetColumns(it => it.CourseIds == ware.CourseIds).Where(it => it.Id == ware.Id).ExecuteCommandAsync();
        //            }
        //        }
        //    }
        //    #endregion
        //}
        return ServiceResult.OprateSuccess("执行成功！");
    }

    #region 批量删除

    public override async Task<bool> DeleteById1(object id)
    {
        return await DeleteByIds1([id.ObjToLong()]);
    }
    /// <summary>
    /// 删除指定ID集合的数据(批量删除)
    /// </summary>
    /// <param name="ids">主键ID集合</param>
    /// <returns></returns>
    public override async Task<bool> DeleteByIds1(long[] ids)
    {
        await Db.Updateable<Ghre_Exam>()
           .SetColumns(it => new Ghre_Exam() { IsEnable = 0 }, true)
           .Where(it => ids.Contains(it.Id))
           .ExecuteCommandAsync();

        await Db.Updateable<Ghre_StudyRecord>()
            .SetColumns(it => new Ghre_StudyRecord() { ExamId = null }, true)
            .Where(it => it.ExamId != null && ids.Contains(it.ExamId.Value))
            .ExecuteCommandAsync();

        return true;
    }
    #endregion
}