
using Tiobon.Core.IServices;
using Tiobon.Core.Model.Models;
using Tiobon.Core.Services.BASE;
using Tiobon.Core.IRepository.Base;
using Tiobon.Core.Common.Caches;
using System.Collections.Generic;
using MySqlX.XDevAPI.Relational;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using NPOI.POIFS.NIO;
using static Microsoft.Extensions.Logging.EventSource.LoggingEventSource;
using MySqlX.XDevAPI.Common;
using System.Text;
using Microsoft.AspNetCore.Identity;
using System.Linq;
using Amazon.Runtime.Internal.Transform;

namespace Tiobon.Core.Services
{
    /// <summary>
    /// 人员群组 (服务)
    /// </summary>
    public class Ghra_StaffGroupServices : BaseServices<Ghra_StaffGroup, Ghra_StaffGroupDto, InsertGhra_StaffGroupInput, EditGhra_StaffGroupInput>, IGhra_StaffGroupServices
    {
        private readonly IBaseRepository<Ghra_StaffGroup> _dal;
        private IGhra_StaffGroupDetailServices _StaffGroupDetailServices;
        public Ghra_StaffGroupServices(ICaching caching, IBaseRepository<Ghra_StaffGroup> dal,
                                        IGhra_StaffGroupDetailServices StaffGroupDetailServices
            )
        {
            this._dal = dal;
            base.BaseDal = dal;
            base._caching = caching;
            _StaffGroupDetailServices = StaffGroupDetailServices;
        }

        public override async Task<long> Add(InsertGhra_StaffGroupInput entity)
        {

            var result = await base.Add(entity);
            entity.StaffGroupDetail.ForEach(x =>
            {
                x.StaffGroupID = result;
            });

            await _StaffGroupDetailServices.Add(entity.StaffGroupDetail);

            return result;
        }

        public override async Task<bool> Update(long Id, EditGhra_StaffGroupInput editModel)
        {
            await _StaffGroupDetailServices.Delete(x => x.StaffGroupID == Id);
             
            editModel.StaffGroupDetail.ForEach(x =>
            {
                x.StaffGroupID = Id;
            });
            await _StaffGroupDetailServices.Add(editModel.StaffGroupDetail);

            return await base.Update(Id, editModel);
        }
        public override async Task<bool> DeleteById1(object id)
        {
            var entity = await BaseDal.QueryById(id);
            BasePoco ent = entity as BasePoco;
            ent.IsEnable = 0;
            HttpRequest request = UserContext.Context.Request;
            var api = request.Path.ObjToString().TrimEnd('/').ToLower();
            var ip = GetUserIp(UserContext.Context);
            string sql = @$"update Ghra_StaffGroupDetail
                              set IsEnable=0,
                                  UpdateBy={App.User.ID.ToString()},
                                  UpdateIP={ip},
                                  UpdateProg={api},
                                  UpdateTime=GETDATE()
                            where StaffGroupID={id.ToString()}
                              and IsEnable= 1";

            return await BaseDal.Update(entity);
        }

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

            var StaffGroupDetail = await _StaffGroupDetailServices.Query(x => x.StaffGroupID == body.id);
            result.result.DT_TableDataT1[0].StaffGroupDetail = StaffGroupDetail.OrderBy(x => x.SortNo).ToList();
            result.result.DT_TableDataT1[0].StaffGroupDetail.ForEach((x) =>
            {
                if (x.FieldValue != null)
                {
                    if (JsonHelper.IsJson(x.FieldValue.ToString()))
                    {
                        try
                        {
                            x.FieldValue = JsonHelper.JsonToObj<List<int>>(x.FieldValue.ToString());
                        }
                        catch (Exception)
                        {
                            x.FieldValue = JsonHelper.JsonToObj<List<string>>(x.FieldValue.ToString());
                        }
                    }
                }
            });
            if (body.doType == "Copy")
            {
                result.result.DT_TableDataT1[0].StaffGroupNo = null;
                result.result.DT_TableDataT1[0].StaffGroupName = null;
            }
            return result;
        }
        public override async Task<ServicePageResult<Ghra_StaffGroupDto>> QueryFilterPage(QueryBody filter, string condition, bool? IsEnable = true)
        {

            var result = await base.QueryFilterPage(filter, condition, IsEnable);

            result.result.DT_TableDataT1.ForEach(async x =>
            {
                x.DetailInfo =  Db.Ado.SqlQuerySingle<string>($@"select stuff((select  distinct  ',' + (select top 1 dbo.FLangKeyToValue(MKey,1,StaffInfoColumnName) from Ghra_StaffInfoColumn where IsEnable=1 and StaffInfoGroupId = 1 and IsRelease = 1 and StaffInfoColumnNo=a.StaffField) 
			                                                  from Ghra_StaffGroupDetail  a
			                                                  where StaffGroupID = {x.Id} 
			                                                  for XML path(''),type).value('.','nvarchar(max)'),1,1,'')");
            });

            return result;
        }

        public async Task<ServiceResult<object>> GetStaffInfoColumnData()
        {
            string sql = @$"select StaffInfoColumnNo,
	                               dbo.FLangKeyToValue(MKey,1,StaffInfoColumnName) StaffInfoColumnName,
	                               ColumnType elementType,
	                               DataType dataType, 
	                               case when ColumnType in ('OrgTreeSelect','ApiSelect','StaffSelect') then 'dict'
	                               else '' end  dataSourceType,    
	                               dbo.FS_GetdataSourceBySet('',DataSourceType, DataSource) dataSource ,
	                               case when ColumnType in ('OrgTreeSelect','ApiSelect','StaffSelect') then 1
	                               else 0 end multipleSelectInt,  
	                               (select ParaDetailNo operatorNo,ParaDetailName operatorDesc,SqlFunction operatorIcon,SortNo 
	                               from Ghrs_ParaDetail where IsEnable= 1 
						                                and ParaDetailNo in (select value from dbo.Fs1_GHR30_SplitString(case when ColumnType in ('OrgTreeSelect','ApiSelect','StaffSelect')
																									                            then 'EqualAny,NotEqualAny'
																									                            when ColumnType in ('Input') 
																									                            then 'Include,NotInclude,Equal,NotEqual'
																									                            when ColumnType in ('InputNumber') 
																									                            then 'Equal,NotEqual,Greater,GreaterOrEqual,Less,LessOrEqual'
																									                            when ColumnType in ('GDatePicker') 
																									                            then 'Greater,GreaterOrEqual,Less,LessOrEqual' 
																									                            when ColumnType in ('Switch') 
																									                            then 'Equal,NotEqual'
																									                            else 'Equal,NotEqual,Include,NotInclude,EqualAny,NotEqualAny,IsNull,NotNull,Greater,GreaterOrEqual,Less,LessOrEqual,Range'
																							                               end,',')) 
							                            and ParaMasterNo = 'DataQueryOperator'
							                            and ParaTypeNo='GHRPara'
							                            order by SortNo for JSON path ) OperatorDataStr
                            from Ghra_StaffInfoColumn
                            where IsEnable = 1
                              and StaffInfoGroupId = 1 
                              and IsRelease = 1
                              and StaffInfoColumnNo != 'AttachmentIDs' ";

            var result = Db.Ado.SqlQuery<StaffInfoColumn>(sql);
            result.ForEach(x =>
            {
                x.operatorData = JsonConvert.DeserializeObject<List<OperatorData>>(x.OperatorDataStr);
            });
            return ServiceResult<object>.OprateSuccess("查询成功！", result);
        }

        public async Task<ServiceResult<Dictionary<string, object>>> GetStaffGroupInfoByID(long Id,JObject FBody)
        {
            string LangID = App.User.GetLangId().ToString();
            string BaseDate = FBody["BaseDate"]?.Value<string>() ?? DateTime.Now.ToString("yyyy-MM-dd");
            if (LangID == "0") LangID = "1";
            string sql = @$"select case when Operator not in ('IsNull','NotNull') then iif(IsChangeColumn = 1,'b.','a.')+StaffField 
				                    + case when Operator = 'Equal' 
						                     then ' = '+' '''+ISNULL(FieldValue,'')+''' '
						                     when Operator = 'NotEqual' 
						                     then ' != '+' '''+ISNULL(FieldValue,'')+''' '
						                     when Operator = 'Include' 
						                     then  ' like '+' ''%'+ISNULL(FieldValue,'')+'%'' '
						                     when Operator = 'NotInclude' 
						                     then ' not like ' +' ''%'+ISNULL(FieldValue,'')+'%'' '
						                     when Operator = 'EqualAny' and ISJSON(FieldValue) =1 
						                     then ' in '+'(select value from openjson('''+ISNULL(FieldValue,'')+'''))'
						                     when Operator = 'NotEqualAny'  and ISJSON(FieldValue) =1 
						                     then  ' not in ' +'(select value from openjson('''+ISNULL(FieldValue,'')+'''))' 
						                     when Operator = 'Greater' 
						                     then  ' > ' +' '''+ISNULL(FieldValue,'')+''' '
						                     when Operator = 'GreaterOrEqual' 
						                     then  ' >= ' +' '''+ISNULL(FieldValue,'')+''' '
						                     when Operator = 'Less' 
						                     then  ' < ' +' '''+ISNULL(FieldValue,'')+''' '
						                     when Operator = 'LessOrEqual' 
						                     then  ' <= ' +' '''+ISNULL(FieldValue,'')+''' '
				                    end 
		                    else  
		                    ISNULL('+StaffField+','') + iif(IsChangeColumn = 1,'b.','a.')+
			                       case when Operator = 'IsNull' 
						                     then ' = '''' '
						                     when Operator = 'NotNull' 
						                     then '  != '''' '
				                    end 
	                    end WhereStr,
	                     case when b.DataSourceType='ParaDetailNo' then 
									                                 '(select dbo.FLangKeyToValue(MKey,'+ convert(nvarchar(10), {LangID})+',ParaDetailName)  from Ghrs_ParaDetail kk where kk.ParaMasterId='+b.DataSource+' and IsEnable=1 and ParaDetailNo=a.'+b.ColumnName+') '
                                                                when b.DataSourceType='ParaDetail' then 
									                                 '(select dbo.FLangKeyToValue(MKey,'+ convert(nvarchar(10), {LangID})+',ParaDetailName)  from Ghrs_ParaDetail kk where kk.ParaDetailId=a.'+b.ColumnName+') '
                                                                when b.DataSourceType='OrgTreeWithoutPriv' then 
									                                 '(select dbo.FS_CommonLangName(''Ghro_Dept'',kk.DeptID, kk.DeptName,'+convert(nvarchar(100),{LangID})+')   from Ghro_Dept kk where kk.DeptID=a.'+b.ColumnName+') '
                                                                when b.DataSourceType='OrgTreeWithPriv' then 
									                                 '(select dbo.FS_CommonLangName(''Ghro_Dept'',kk.DeptID, kk.DeptName,'+convert(nvarchar(100),{LangID})+')   from Ghro_Dept kk where kk.DeptID=a.'+b.ColumnName+') '
											                    when b.DataSourceType='StaffWithoutPriv'  and b.ColumnName!='StaffID' then 
									                                 '(select [dbo].[FA_StaffLangName](kk.StaffID,kk.StaffName,kk.StaffEname,'+ convert(nvarchar(10), {LangID})+') StaffName from Ghra_Staff kk where kk.StaffID=a.'+b.ColumnName+') '
                                                                when b.DataSourceType='StaffWithPriv' and b.ColumnName!='StaffID' then 
									                                 '(select [dbo].[FA_StaffLangName](kk.StaffID,kk.StaffName,kk.StaffEname,'+ convert(nvarchar(10), {LangID})+') StaffName from Ghra_Staff kk where kk.StaffID=a.'+b.ColumnName+') '
				                                                when b.ColumnType in ('Switch','CheckBox') then '(select convert(nvarchar(10), iif(convert(nvarchar(100),a.'+b.ColumnName+') in (''1'',''true''),'''+[dbo].[FLangKeyToValue]('GHR_Common_000195',{LangID},'是' )+''','''+[dbo].[FLangKeyToValue]('GHR_Common_000196',{LangID},'否' )+''') ) )'    
                                                                when b.DataSourceType='CommonList' then  
											                         '('+ (select replace(replace(SqlIDToName,'{{@LangID}}',Convert(nvarchar(10),{LangID})),'{{@KeyWords}}','a.'+b.ColumnName) from Ghrs_ListCommonSql where ListCommonSqlID=b.DataSource )+') ' 												  
								                           else '' end 
								                      + b.ColumnName SqlStr
	                    ,ColumnName ColumnNo
	                    ,dbo.FLangKeyToValue(MKey,{LangID},StaffInfoColumnName) ColumnName
	                    ,b.SortNo
                    from Ghra_StaffGroupDetail a  inner join Ghra_StaffInfoColumn b on a.StaffField=b.StaffInfoColumnNo
                    where a.IsEnable = 1
                      and b.IsEnable = 1
                      and b.StaffInfoGroupId = 1
                      and b.IsRelease = 1
                      and StaffGroupID = '{Id}'
                    order by b.SortNo";

            DataTable dt = await Db.Ado.GetDataTableAsync(sql);
            string WhereSQL = string.Empty;
            string SqlStr = string.Empty; 
           
            List<StaffInfoColumn> staffInfoColumns = new List<StaffInfoColumn>();
            staffInfoColumns.Add(new StaffInfoColumn() { StaffInfoColumnNo = "StaffID", StaffInfoColumnName = "ID",isHidden=true });
            staffInfoColumns.Add(new StaffInfoColumn() { StaffInfoColumnNo = "StaffNo", StaffInfoColumnName = "工号" });
            staffInfoColumns.Add(new StaffInfoColumn() { StaffInfoColumnNo = "StaffName", StaffInfoColumnName = "姓名" });
            staffInfoColumns.Add(new StaffInfoColumn() { StaffInfoColumnNo = "DeptFullPateName", StaffInfoColumnName = "全部门" });
            foreach (DataRow dr in dt.Rows) 
            {
                WhereSQL += " and "+dr["WhereStr"].ToString();

                StaffInfoColumn staffInfo = new StaffInfoColumn();
                staffInfo.StaffInfoColumnNo = dr["ColumnNo"].ToString();
                staffInfo.StaffInfoColumnName = dr["ColumnName"].ToString();
                
;                if ( !staffInfoColumns.Any(x => x.StaffInfoColumnNo == dr["ColumnNo"].ToString())) 
                {
                    staffInfoColumns.Add(staffInfo); 
                    SqlStr += ","+dr["SqlStr"].ToString();
                }
            }

            string StaffSql = @$"select a.StaffID,a.StaffNo,a.StaffName,
	                                   dbo.FA_StaffInfoRepItemStaffInfo(a.StaffID,null,{LangID},'DeptFullPateName') DeptFullPateName 
                                   {SqlStr}
                                from Ghra_Staff a inner join (select * from Ghra_StaffChange where IsEnable=1 and ChangeDate= [dbo].[FLatestStaffChangeDate](StaffID,'{BaseDate}')  )b
						                                  on a.StaffID = b.StaffID
                                where a.IsEnable=1 
                                  and a.InDate <= '{BaseDate}'  
                                  and (a.OutDate is null or a.OutDate>= '{BaseDate}'  ) "
                                + WhereSQL;

            DataTable staffTable = await Db.Ado.GetDataTableAsync(StaffSql);
            Dictionary<string, object> result = new Dictionary<string, object>()
            {
                ["TableColumns"] = staffInfoColumns,
                ["TableData"] = staffTable
            };   
            return ServiceResult<Dictionary<string, object>>.OprateSuccess("查询成功！", result);
        }
        public class StaffInfoColumn()
        {
            public string StaffInfoColumnNo { get; set; }
            public string StaffInfoColumnName { get; set; }
            public bool isHidden { get; set; } = false; 
            public string elementType { get; set; }
            public string dataType { get; set; }
            public string dataSourceType { get; set; }
            public string dataSource { get; set; }
            public int multipleSelectInt { get; set; }
            public bool multipleSelect => multipleSelectInt == 1 ? true : false;
            public string OperatorDataStr { get; set; }
            public List<OperatorData> operatorData { get; set; }
        }
        public class OperatorData()
        {
            public string operatorNo { get; set; }
            public string ParaDetailName { get; set; }
            public string operatorIcon { get; set; }
            public string SortNo { get; set; }
        } 

    }


}