Пример #1
0
        /// <summary>
        /// 根据搜索条件,推送计划Id列表,查询出模板变更记录列表【2】
        /// </summary>
        /// <param name="pushPlanIds"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public static async Task <List <TemplateModifyLogInfo> > GetTemplateModifyLogInfoWithIdsAndQueryInfo(List <int> pushPlanIds, TemplateLogQueryInfo query)
        {
            var stringBuilder = new StringBuilder();

            if (query == null)
            {
                query = new TemplateLogQueryInfo()
                {
                    PageIndex = 1, PageSize = 15
                };
            }

            var parameters = new List <SqlParameter>
            {
                new SqlParameter("@pagesize", query.PageSize == 0 ? 50 : query.PageSize),
                new SqlParameter("@pageindex", query.PageIndex == 0 ? 1 : query.PageIndex),
            };

            stringBuilder.Append("SELECT Pkid, [User] as ModifyUser,OriginTemplate,NewTemplate,TemplateId as PushPlanId,LastUpdateDateTime  from  Tuhu_log..PushTemplateModifyLog WITH(NOLOCK) where templateId>0 ");

            if (!pushPlanIds.Any())
            {
                pushPlanIds.Add(0);
            }

            stringBuilder.Append($" and TemplateId in({string.Join(",", pushPlanIds)})");


            if (!string.IsNullOrEmpty(query.ModifyUser))
            {
                parameters.Add(new SqlParameter("@ModifyUser", query.ModifyUser));
                stringBuilder.Append($" and  [user] like  '%' + @ModifyUser + '%'");
            }

            if (query.ModifyStartTime.HasValue)
            {
                parameters.Add(new SqlParameter("@ModifyStartTime", query.ModifyStartTime.Value));
                stringBuilder.Append($" and  lastupdateDateTime>=@ModifyStartTime ");
            }
            if (query.ModifyEndTime.HasValue)
            {
                parameters.Add(new SqlParameter("@ModifyEndTime", query.ModifyEndTime.Value));
                stringBuilder.Append($" and  lastupdateDateTime<=@ModifyEndTime ");
            }

            stringBuilder.Append($"order by Pkid desc  OFFSET  ( @pagesize * (@pageindex - 1 ) )  ROWS  FETCH NEXT @pagesize  ROWS ONLY;");



            var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;

            if (SecurityHelp.IsBase64Formatted(conn))
            {
                conn = SecurityHelp.DecryptAES(conn);
            }
            using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
            {
                return((await dbHelper.ExecuteDataTableAsync(stringBuilder.ToString(), CommandType.Text, parameters.ToArray())).ConvertTo <TemplateModifyLogInfo>()?.ToList());
            }
        }
Пример #2
0
        /// <summary>
        /// 根据搜索条件,推送计划Id列表,查询出模板变更记录列表【2】
        /// </summary>
        /// <param name="pushPlanIds"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public static async Task <int> GetTemplateModifyLogCountWithIdsAndQueryInfo(List <int> pushPlanIds, TemplateLogQueryInfo query)
        {
            var stringBuilder = new StringBuilder();
            var parameters    = new List <SqlParameter>();

            if (query == null)
            {
                query = new TemplateLogQueryInfo()
                {
                };
            }

            stringBuilder.Append("SELECT count(1)  from  Tuhu_log..PushTemplateModifyLog WITH(NOLOCK) where 1=1 ");

            if (!pushPlanIds.Any())
            {
                pushPlanIds.Add(0);
            }

            stringBuilder.Append($" and TemplateId in({string.Join(",", pushPlanIds)})");

            if (!string.IsNullOrEmpty(query.ModifyUser))
            {
                parameters.Add(new SqlParameter("@ModifyUser", query.ModifyUser));
                stringBuilder.Append($" and  [user] like  '%' + @ModifyUser + '%'");
            }

            if (query.ModifyStartTime.HasValue)
            {
                parameters.Add(new SqlParameter("@ModifyStartTime", query.ModifyStartTime.Value));
                stringBuilder.Append($" and  lastupdateDateTime>=@ModifyStartTime ");
            }
            if (query.ModifyEndTime.HasValue)
            {
                parameters.Add(new SqlParameter("@ModifyEndTime", query.ModifyEndTime.Value));
                stringBuilder.Append($" and  lastupdateDateTime<=@ModifyEndTime ");
            }

            var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;

            if (SecurityHelp.IsBase64Formatted(conn))
            {
                conn = SecurityHelp.DecryptAES(conn);
            }

            using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
            {
                var result = await dbHelper.ExecuteScalarAsync(stringBuilder.ToString(), CommandType.Text, parameters.ToArray());

                int count = 1;
                int.TryParse(result?.ToString(), out count);
                return(count);
            }
        }
Пример #3
0
        /// <summary>
        /// 搜索满足条件的数据,最多抓取500条记录
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static async Task <List <TemplateModifyLogInfo> > GetExportTemplateModifyLogDatas(TemplateLogQueryInfo query)
        {
            try
            {
                query.PageIndex = 1;
                query.PageSize  = 500;

                var pushIds = await GetPushPlanIdsWithQueryInfo(query);

                var logList = await GetTemplateModifyLogInfoWithIdsAndQueryInfo(pushIds, query);

                var countresult = await GetTemplateModifyLogCountWithIdsAndQueryInfo(pushIds, query);

                var templateList = await GetTemplateInfoFromIds(logList?.Select(o => o.PushPlanId).Distinct().ToList());

                foreach (var item in logList)
                {
                    var templateInfo = templateList.FirstOrDefault(o => o.PushPlanId == item.PushPlanId);

                    if (templateInfo == null)
                    {
                        continue;
                    }

                    item.TemplateId     = templateInfo.TemplateId;
                    item.TemplateTitle  = templateInfo.TemplateTitle;
                    item.PushPlanTitle  = templateInfo.PushPlanTitle;
                    item.DeviceType     = templateInfo.DeviceType;
                    item.NewTemplate    = item.NewTemplate.Replace('\"', '\'');
                    item.OriginTemplate = item.OriginTemplate.Replace('\"', '\'');
                }
                return(logList);
            }
            catch (Exception ex)
            {
                return(new List <TemplateModifyLogInfo>());
            }
        }
Пример #4
0
        /// <summary>
        /// 分页获取操作历史列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static async Task <TemplateLogInfoQueryResult> SelectPushTemplateModifyLogsAsync(TemplateLogQueryInfo query)
        {
            try
            {
                var pushIds = await GetPushPlanIdsWithQueryInfo(query);

                var logList = await GetTemplateModifyLogInfoWithIdsAndQueryInfo(pushIds, query);

                var countresult = await GetTemplateModifyLogCountWithIdsAndQueryInfo(pushIds, query);

                var templateList = await GetTemplateInfoFromIds(logList?.Select(o => o.PushPlanId).Distinct().ToList());

                foreach (var item in logList)
                {
                    var templateInfo = templateList.FirstOrDefault(o => o.PushPlanId == item.PushPlanId);

                    if (templateInfo == null)
                    {
                        continue;
                    }

                    item.TemplateId    = templateInfo.TemplateId;
                    item.TemplateTitle = templateInfo.TemplateTitle;
                    item.PushPlanTitle = templateInfo.PushPlanTitle;
                    item.DeviceType    = templateInfo.DeviceType;
                }

                var result = new TemplateLogInfoQueryResult()
                {
                    TotalCount = countresult,
                    PageIndex  = query.PageIndex,
                    Result     = logList.ToList(),
                    PageSize   = query.PageSize
                };

                return(result);
            }
            catch (Exception ex)
            {
                return(new TemplateLogInfoQueryResult()
                {
                });
            }
        }
Пример #5
0
        /// <summary>
        /// 根据搜索条件查询出推送计划Id列表【1】
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static async Task <List <int> > GetPushPlanIdsWithQueryInfo(TemplateLogQueryInfo query)
        {
            var wheresb = new StringBuilder();

            var parameters = new List <SqlParameter>()
            {
            };

            wheresb.Append("SELECT  pkid FROM Tuhu_notification..tbl_PushTemplate WITH(NOLOCK) where 1=1 ");

            if (query.TemplateId > 0)
            {
                parameters.Add(new SqlParameter("@BatchId", query.TemplateId));
                wheresb.Append($" and batchId=@BatchId ");
            }

            if (query.PushPlanId > 0)
            {
                parameters.Add(new SqlParameter("@PushPlanId", query.PushPlanId));
                wheresb.Append($" and  pkid=@PushPlanId ");
            }

            if (!string.IsNullOrEmpty(query.PushPlanTitle))
            {
                parameters.Add(new SqlParameter("@PushPlanTitle", query.PushPlanTitle));
                wheresb.Append($" and  title like  '%' + @PushPlanTitle + '%' ");
            }

            if (query.DeviceType.HasValue)
            {
                parameters.Add(new SqlParameter("@DeviceType", (int)query.DeviceType.Value));
                wheresb.Append($" and  DeviceType=@DeviceType ");
            }

            wheresb.Append("order by pkid desc  OFFSET 0 ROWS  FETCH NEXT 1000 ROWS ONLY;");

            var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;

            if (SecurityHelp.IsBase64Formatted(conn))
            {
                conn = SecurityHelp.DecryptAES(conn);
            }

            try
            {
                using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
                {
                    var result = await dbHelper.ExecuteDataTableAsync(wheresb.ToString(), CommandType.Text, parameters.ToArray());

                    if (result != null && result.Rows.Count > 0)
                    {
                        var data = result.AsEnumerable().Select(x => int.Parse(x[0].ToString())).ToList();
                        return(data);
                    }
                    return(new List <int>());
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }