namespace Tiobon.Core.Services;

/// <summary>
/// 证件 (服务)
/// </summary>
public class Ghrh_ResumeLicenceServices : BaseServices<Ghrh_ResumeLicence, Ghrh_ResumeLicenceDto, InsertGhrh_ResumeLicenceInput, EditGhrh_ResumeLicenceInput>, IGhrh_ResumeLicenceServices
{
    private readonly IBaseRepository<Ghrh_ResumeLicence> _dal;
    public Ghrh_ResumeLicenceServices(ICaching caching, IBaseRepository<Ghrh_ResumeLicence> dal)
    {
        this._dal = dal;
        base.BaseDal = dal;
        base._caching = caching;
    }

    public override async Task<List<Ghrh_ResumeLicenceDto>> QueryDto(Expression<Func<Ghrh_ResumeLicence, bool>> whereExpression)
    {
        var data = await base.QueryDto(whereExpression);
        var ids = data.Select(x => x.Id.ToString()).ToList();
        var attachments = await Db.Queryable<Ghrs_Attachment>().Where(x => ids.Contains(x.TableName)).ToListAsync();

        data.ForEach(async x =>
        {
            await SetLabel(x);
            x.AttachmentIDs = attachments
            .Where(o => o.TableName == x.Id.ToString())
            .Select(o => new Ghrs_Attachment()
            {
                AttachmentID = o.AttachmentID,
                AttachFileExtension = o.AttachFileExtension,
                AttachFileSize = o.AttachFileSize,
                RelativePath = o.RelativePath,
                AttachmentName = o.AttachmentName,
                RemarkSz = o.RemarkSz
            }).ToList();
        });

        return data;
    }
    public override async Task<List<long>> Add(List<InsertGhrh_ResumeLicenceInput> listEntity)
    {
        var result = new List<long>();
        for (int i = 0; i < listEntity.Count; i++)
        {
            var id = await base.Add(listEntity[i]);
            if (listEntity[i].AttachmentIDs != null && listEntity[i].AttachmentIDs.Any())
            {
                var ids = listEntity[i].AttachmentIDs.Select(x => x.RelativePath).ToList();
                await Db.Updateable<Ghrs_Attachment>()
                        .SetColumns(x => new Ghrs_Attachment() { TableName = id.ToString(), UpdateTime = DateTime.Now })
                         .Where(it => it.AttachmentID != null && ids.Contains(it.RelativePath))
                         .ExecuteCommandAsync();
            }
            result.Add(id);
        }
        return result;
    }



    public override async Task<long> Add(InsertGhrh_ResumeLicenceInput entity)
    {
        var result = await Add([entity]);

        return result.First();
    }


    public override async Task<bool> Update(long Id, EditGhrh_ResumeLicenceInput editModel)
    { 
        var result = await base.Update(Id, editModel);

        if (editModel.AttachmentIDs != null && editModel.AttachmentIDs.Any())
        {
            await Db.Updateable<Ghrs_Attachment>()
                           .SetColumns(x => new Ghrs_Attachment() { TableName = null })
                            .Where(it => it.TableName == Id.ToString())
                            .ExecuteCommandAsync();

            var ids = editModel.AttachmentIDs.Select(x => x.RelativePath).ToList();
            await Db.Updateable<Ghrs_Attachment>()
                    .SetColumns(x => new Ghrs_Attachment() { TableName = Id.ToString() })
                     .Where(it => it.AttachmentID != null && ids.Contains(it.RelativePath))
                     .ExecuteCommandAsync();
        }

        return result;
    }

    public override async Task<ServiceFormResult<Ghrh_ResumeLicenceDto>> QueryForm(QueryForm body)
    {
        var result = await base.QueryForm(body);


        result.result.DT_TableDataT1[0].AttachmentIDs = await Db.Queryable<Ghrs_Attachment>().Where(x => x.TableName == body.id.ObjToString()).ToListAsync();
       return result;

    }

    #region 字典映射、全称、单位转换等
    /// <summary>
    /// 字典映射、全称、单位转换等
    /// </summary>
    /// <param name="view"></param>
    private async Task SetLabel(Ghrh_ResumeLicenceDto x)
    {
        if (x != null)
        {
            x.BeginDate1 = DateTimeHelper.ConvertToDayString(x.BeginDate);
            x.EndDate1 = DateTimeHelper.ConvertToDayString(x.EndDate);
        }
    }
    #endregion

    #region 查询
    public override async Task<ServicePageResult<Ghrh_ResumeLicenceDto>> QueryFilterPage(QueryBody filter, string condition, bool? IsEnable = true)
    {
        var result = await base.QueryFilterPage(filter, condition, IsEnable);
        var list = result.result.DT_TableDataT1;
        var resumeIds = list.Where(x => x.ResumeId != null).Select(x => x.ResumeId.Value).ToList();
        var resumes = await Db.Queryable<Ghrh_Resume>().Where(x => resumeIds.Contains(x.Id)).ToListAsync();

        list.ForEach(async x =>
        {
            await SetLabel(x);
            x.StaffName = resumes.Where(o => x.ResumeId == o.Id).FirstOrDefault()?.StaffName;
        });

        return result;

    }
    #endregion
    #region Excel导入
    public override async Task<ServiceResult<string>> DownloadExcel(string menuName)
    {
        return await ExtensionHelper.GenerateReumeArchivesImportTemplate(Db, "Licence", "证件");
    }
    public override async Task<ServiceResult<ExcelData>> ImportExcel(IFormFile file, string menuName = null, long? MasterId = null)
    {
        var data = new ExcelData();
        long id = SnowFlakeSingle.instance.getID();
        var physicsPath = $"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot";
        var path = $"{$"{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}import{Path.DirectorySeparatorChar}{id}{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(physicsPath + path))
            Directory.CreateDirectory(physicsPath + path);

        var filepath = physicsPath + path + file.FileName;
        using (var stream = File.Create(filepath))
        {
            await file.CopyToAsync(stream);
        }
        string extension = Path.GetExtension(filepath);

        bool isExistError = false;
        var id1 = SnowFlakeSingle.instance.getID();
        string errorFileName = path + SnowFlakeSingle.instance.getID() + extension;

        var dt = NPOIHelper.ImportExcel(filepath, "证件");
        if (dt.Columns["Comments"] == null)
            dt.Columns.Add("Comments", typeof(string));

        var formColumns = await ExtensionHelper.QueryResumeFormColumn(Db, "Licence");

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var comments = new List<string>();
            bool isContinue = false;

            var dict = new Dictionary<string, object>
                {
                { "Id",  SnowFlakeSingle.Instance.NextId() },
                { "CreateBy", App.User.ID },
                { "CreateTime", DateTime.Now },
                { "UpdateTime", DateTime.Now }
              };
            if (!dt.Columns.Contains("姓名"))
            {
                comments.Add("未查询到【姓名】列！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                isContinue = true;
            }

            formColumns.ForEach(x =>
            {
                if (!dt.Columns.Contains(x.ColumnNameDesc))
                {
                    comments.Add("未查询到【" + x.ColumnNameDesc + "】列！");
                    data.ErrorCount++;
                    dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                    isExistError = true;
                    isContinue = true;
                }
            });

            if (isContinue)
                continue;

            for (int j = 0; j < formColumns.Count; j++)
            {
                var column = formColumns[j];
                var value = dt.Rows[i][column.ColumnNameDesc].ToString();
                if (column.IsRequired == true && value.IsNullOrEmpty())
                {
                    comments.Add(column.ColumnNameDesc + "不能为空！");
                    data.ErrorCount++;
                    dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                    isExistError = true;
                    continue;
                }
                if (column.DataSourceType.IsNullOrEmpty() && value.IsNotEmptyOrNull())
                {
                    if (column.DataType == "int" || column.DataType == "decimal")
                    {
                        try
                        {
                            Convert.ToInt64(value);
                        }
                        catch (Exception)
                        {
                            comments.Add(column.ColumnNameDesc + "无效的数字类型！");
                            data.ErrorCount++;
                            dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                            isExistError = true;
                            continue;
                        }
                    }
                    else if (column.DataType == "date")
                    {
                        try
                        {
                            Convert.ToDateTime(value);
                        }
                        catch (Exception)
                        {
                            comments.Add(column.ColumnNameDesc + "无效的日期类型！");
                            data.ErrorCount++;
                            dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                            isExistError = true;
                            continue;
                        }
                    }
                }


                if (value.IsNotEmptyOrNull())
                {
                    if (column.ColumnType == "Switch")
                        value = value == "是" ? "true" : "false";
                    if (column.DataSource.IsNotEmptyOrNull() && column.DataSourceType.IsNotEmptyOrNull())
                    {

                        if (column.DataSourceType == "CommonList")
                        {
                            var commonSql = await Db.Queryable<Ghrs_ListCommonSql>().Where(x => x.ListCommonSqlId == column.DataSource.ObjToInt()).FirstAsync();

                            if (commonSql != null)
                            {
                                string sql = @$"SELECT [value]
                                FROM ({commonSql.SelectSql}) A
                                WHERE label = '{value}'";
                                sql = sql.Replace("{@LangID}", "1");
                                var id2 = await Db.Ado.GetLongAsync(sql);

                                dict.Add(column.ColumnName, id2);
                            }
                        }
                        else if (column.DataSourceType == "ParaDetailNo")
                        {
                            var sql = @$"SELECT ParaDetailNo
                                FROM Ghrs_ParaDetail
                                WHERE     ParaMasterId IN (SELECT ParaMasterId
                                                           FROM Ghrs_ParaMaster
                                                           WHERE ParaMasterId = {column.DataSource.ObjToInt()})
                                      AND IsEnable = 1
                                      AND ParaDetailName = '{value}'";
                            var id2 = await Db.Ado.GetStringAsync(sql);

                            dict.Add(column.ColumnName, id2);
                        }
                    }
                    else
                        dict.Add(column.ColumnName, value);
                }

            }

            var StaffName = dt.Rows[i]["姓名"].ToString();

            var resume = await Db.Queryable<Ghrh_Resume>().Where(x => x.StaffName == StaffName || x.StaffEname == StaffName).FirstAsync();
            if (resume is null)
            {
                comments.Add("未查询到【" + StaffName + "】相关数据！");
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                isContinue = true;
            }
            else dict.Add("ResumeId", resume.Id);
            if (isExistError)
                continue;
            try
            {
                await Db.Insertable(dict).AS("Ghrh_ResumeLicence").ExecuteCommandAsync();
                data.SuccessCount++;
            }
            catch (Exception E)
            {
                comments.Add(E.Message);
                data.ErrorCount++;
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                isExistError = true;
                continue;
            }
        }
        if (isExistError)
        {
            NPOIHelper.ExportExcel(dt, null, "证件", physicsPath + errorFileName);
            data.filePath = "/Advanced" + errorFileName;
        }

        return ServiceResult<ExcelData>.OprateSuccess("导入成功！", data);
    }
    #endregion
}