dotnet/molilian.api/Controllers/admin/TracksAdminController.cs

using System.Text.Json;
using System.Threading.Tasks;
using CSRedis;
using Dapper;
using dodohold.core;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using molilian.core;

namespace molilian.api.Controllers
{
    [ApiController]
    [MyAuthorize("admin")]
    [Route("api/[controller]/[action]")]
    public class TracksAdminController : ControllerBase
    {
        private const string PddTrackWhere = "(LOWER(r.platform)='pdd' OR r.platform='拼多多' OR r.track_link_id IN (21,22) OR r.unique_id LIKE '9|%')";
        private const string VisiblePddAccountWhere = "NOT EXISTS (SELECT 1 FROM pdd_pool hp WHERE hp.id=r.account_id AND hp.is_hide=1)";

        readonly IAuthorizationProvider provider = new AdminProvider();
        protected IHttpContextAccessor _accessor;

        public TracksAdminController(IHttpContextAccessor accessor)
        {
            _accessor = accessor;
        }

        [HttpPost]
        public ActionResult list([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);

            int page = form.Read("current", 1);
            int size = form.Read("pageSize", 10);
            bool getTotal = form.Read("getTotal", true);
            string sort = form.Read("sort", "id");
            string order = form.Read("order", "DESC");
            string keyword = form.Read("keyword", string.Empty);

            string filter = string.Empty;
            if (!string.IsNullOrEmpty(keyword))
            {
                filter += " AND (event_type LIKE @keyword OR platform LIKE @keyword OR typename LIKE @keyword OR scene LIKE @keyword OR unique_id LIKE @keyword)";
                keyword = $"%{keyword}%";
            }
            filter = filter.StringTrimStart(" AND ");

            order = "descending".Equals(order, System.StringComparison.OrdinalIgnoreCase) ? "DESC" :
                    "ascending".Equals(order, System.StringComparison.OrdinalIgnoreCase) ? "ASC" : order;
            string orderBy = string.IsNullOrEmpty(sort) ? "id DESC" : $"{sort} {order}";

            var result = new DBContext.Table("track_links")
                .Where(filter, new { keyword })
                .Page(size, page)
                .Order(orderBy)
                .PageList<dynamic>(getTotal);

            return new APIResult(new { data = result });
        }

        [HttpGet]
        public ActionResult info([FromQuery] int id)
        {
            var token = provider.Get(_accessor.HttpContext);
            var item = new DBContext.Table("track_links").Get<TrackLinkDTO>("id=@id", new { id });
            return new APIResult(new { data = item });
        }

        [HttpPost]
        public ActionResult report([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            int page = form.Read("current", 1);
            int size = form.Read("pageSize", 10);
            bool getTotal = form.Read("getTotal", true);
            int linkId = form.Read("track_link_id", 0);
            int accountId = form.Read("account_id", -1);
            bool includeAccountBreakdown = form.Read("include_account_breakdown", false);
            bool accountBreakdownOnly = form.Read("account_breakdown_only", false);
            string eventType = form.Read("event_type", string.Empty);
            string platform = form.Read("platform", string.Empty);
            string typename = form.Read("typename", string.Empty);
            string scene = form.Read("scene", string.Empty);
            string uniqueId = form.Read("unique_id", string.Empty);
            DateTime? start = form.Read<DateTime?>("start", null);
            DateTime? end = form.Read<DateTime?>("end", null);

            page = Math.Max(page, 1);
            size = Math.Max(size, 1);

            var parameters = new DynamicParameters(new
            {
                linkId,
                accountId,
                eventType,
                platform,
                typename,
                scene,
                uniqueId,
                start,
                end,
                offset = (page - 1) * size,
                size
            });

            var baseWhere = new List<string>();
            if (linkId > 0) baseWhere.Add("r.track_link_id=@linkId");
            if (!string.IsNullOrWhiteSpace(eventType)) baseWhere.Add("r.event_type=@eventType");
            if (!string.IsNullOrWhiteSpace(platform)) baseWhere.Add("r.platform=@platform");
            if (!string.IsNullOrWhiteSpace(typename)) baseWhere.Add("r.typename=@typename");
            if (!string.IsNullOrWhiteSpace(scene)) baseWhere.Add("r.scene=@scene");
            if (!string.IsNullOrWhiteSpace(uniqueId)) baseWhere.Add("r.unique_id=@uniqueId");
            if (start.HasValue) baseWhere.Add("r.report_date>=@start");
            if (end.HasValue) baseWhere.Add("r.report_date<=@end");

            string unionSql = BuildTrackReportSql(baseWhere, accountId, includeAccountBreakdown, accountBreakdownOnly);
            string dataSql = $@"
SELECT *
FROM ({unionSql}) t
ORDER BY t.report_date DESC, t.id DESC
LIMIT @offset,@size";

            using var conn = DBContext.GetOpenConnection();
            var list = SqlMapper.Query<TrackDailyReportDTO>(conn, dataSql, parameters).ToList();
            int count = list.Count;
            if (getTotal)
            {
                count = conn.ExecuteScalar<int>($"SELECT COUNT(1) FROM ({unionSql}) t", parameters);
            }

            return new APIResult(new { data = new { list, count } });
        }

        private static string BuildTrackReportSql(List<string> baseWhere, int accountId, bool includeAccountBreakdown, bool accountBreakdownOnly)
        {
            var selects = new List<string>();

            if (accountBreakdownOnly)
            {
                selects.Add(BuildStoredReportSelect([.. baseWhere, "r.account_id>0", $"(NOT {PddTrackWhere} OR {VisiblePddAccountWhere})"]));
            }
            else if (accountId > 0)
            {
                selects.Add(BuildStoredReportSelect([.. baseWhere, "r.account_id=@accountId", $"(NOT {PddTrackWhere} OR {VisiblePddAccountWhere})"]));
            }
            else if (accountId == 0 || !includeAccountBreakdown)
            {
                selects.Add(BuildStoredReportSelect([.. baseWhere, "r.account_id=0", $"NOT {PddTrackWhere}"]));
                selects.Add(BuildPddTotalReportSelect([.. baseWhere, "r.account_id>0", PddTrackWhere, VisiblePddAccountWhere]));
            }
            else
            {
                selects.Add(BuildStoredReportSelect([.. baseWhere, $"NOT {PddTrackWhere}"]));
                selects.Add(BuildStoredReportSelect([.. baseWhere, "r.account_id>0", PddTrackWhere, VisiblePddAccountWhere]));
                selects.Add(BuildPddTotalReportSelect([.. baseWhere, "r.account_id>0", PddTrackWhere, VisiblePddAccountWhere]));
            }

            return string.Join("\nUNION ALL\n", selects);
        }

        private static string BuildStoredReportSelect(List<string> where)
        {
            return $@"
SELECT
    r.id,
    r.report_date,
    r.event_type,
    r.platform,
    r.typename,
    r.scene,
    r.unique_id,
    r.track_link_id,
    r.account_id,
    r.event_count,
    r.create_time,
    r.update_time
FROM track_daily_report r
{BuildWhere(where)}";
        }

        private static string BuildPddTotalReportSelect(List<string> where)
        {
            return $@"
SELECT
    MIN(r.id) AS id,
    r.report_date,
    r.event_type,
    r.platform,
    r.typename,
    r.scene,
    r.unique_id,
    r.track_link_id,
    0 AS account_id,
    CAST(SUM(r.event_count) AS SIGNED) AS event_count,
    MIN(r.create_time) AS create_time,
    MAX(r.update_time) AS update_time
FROM track_daily_report r
{BuildWhere(where)}
GROUP BY
    r.report_date,
    r.event_type,
    r.platform,
    r.typename,
    r.scene,
    r.unique_id,
    r.track_link_id
HAVING SUM(r.event_count)>0";
        }

        private static string BuildWhere(List<string> where)
        {
            return where.Count == 0 ? string.Empty : $"WHERE {string.Join(" AND ", where)}";
        }

        [HttpPost]
        public async Task<ActionResult> hourly([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            int linkId = form.Read("track_link_id", 0);
            int accountId = form.Read("account_id", 0);
            string dateText = form.Read("date", string.Empty);
            string scene = form.Read("scene", string.Empty);
            DateTime date = DateTime.TryParse(dateText, out var parsed) ? parsed.Date : DateTime.Now.Date;

            var list = await TracksCore.GetHourlyReportAsync(linkId, date, scene, accountId);
            return new APIResult(new
            {
                data = new
                {
                    list,
                    count = list.Count
                }
            });
        }

        [HttpPost]
        public async Task<ActionResult> parse_metrics([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            int page = form.Read("current", 1);
            int size = form.Read("pageSize", 10);
            bool getTotal = form.Read("getTotal", true);
            int accountId = form.Read("account_id", -1);
            bool accountBreakdownOnly = form.Read("account_breakdown_only", false);
            string platform = form.Read("platform", string.Empty);
            string riskStrategy = form.Read("risk_strategy", string.Empty);
            int launchSceneValue = form.Read("launch_scene", int.MinValue);
            int? launchScene = launchSceneValue == int.MinValue ? null : launchSceneValue;
            DateTime? start = form.Read<DateTime?>("start", null);
            DateTime? end = form.Read<DateTime?>("end", null);

            DateTime startDate = start?.Date ?? DateTime.Now.Date;
            DateTime endDate = end?.Date ?? startDate;
            var result = await TracksCore.GetParseMetricReportAsync(
                startDate,
                endDate,
                page,
                size,
                getTotal,
                accountId,
                accountBreakdownOnly,
                platform,
                riskStrategy,
                launchScene);

            return new APIResult(new { data = result });
        }

        [HttpPost]
        public async Task<ActionResult> parse_metric_hourly([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            string dateText = form.Read("date", string.Empty);
            string platform = form.Read("platform", string.Empty);
            string riskStrategy = form.Read("risk_strategy", string.Empty);
            int launchSceneValue = form.Read("launch_scene", int.MinValue);
            int? launchScene = launchSceneValue == int.MinValue ? null : launchSceneValue;
            int accountId = form.Read("account_id", 0);
            DateTime date = DateTime.TryParse(dateText, out var parsed)
                ? parsed.Date
                : DateTime.Now.Date;

            var list = await TracksCore.GetParseMetricHourlyReportAsync(
                date,
                platform,
                riskStrategy,
                launchScene,
                accountId);

            return new APIResult(new
            {
                data = new
                {
                    list,
                    count = list.Count
                }
            });
        }

        [HttpPost]
        public async Task<ActionResult> backfill_parse_metrics([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            string reportDateText = form.Read("reportDate", string.Empty);
            string platform = form.Read("platform", string.Empty);
            string riskStrategy = form.Read("risk_strategy", string.Empty);
            int launchSceneValue = form.Read("launch_scene", int.MinValue);
            int? launchScene = launchSceneValue == int.MinValue ? null : launchSceneValue;
            DateTime targetDate = DateTime.TryParse(reportDateText, out var parsed)
                ? parsed.Date
                : DateTime.Now.Date;

            var result = await TracksCore.BackfillParseMetricCountersAsync(targetDate, platform, riskStrategy, launchScene);
            return new APIResult(new
            {
                data = new
                {
                    success = true,
                    msg = "重新计算完成",
                    result
                }
            });
        }

        [HttpPost]
        public async Task<ActionResult> create([FromBody] TrackLinkDTO data)
        {
            var token = provider.Get(_accessor.HttpContext);
            if (string.IsNullOrWhiteSpace(data.typename)) data.typename = data.platform;
            if (string.IsNullOrWhiteSpace(data.platform)) data.platform = data.typename;
            var link = await TracksCore.CreateLinkAsync(data.event_type, data.platform, data.typename, data.scene, data.unique_id, data.description, true);
            bool success = link != null && link.id > 0;
            return new APIResult(new
            {
                data = link,
                success,
                msg = success ? "创建成功" : "创建失败"
            });
        }

        [HttpPost]
        public ActionResult delete([FromBody] JsonElement form)
        {
            var token = provider.Get(_accessor.HttpContext);
            int id = form.Read<int>("id");
            using var conn = DBContext.GetOpenConnection();
            var item = new DBContext.Table(conn, "track_links").Get<TrackLinkDTO>("id=@id", new { id });
            if (item == null) return new APIResult(new { data = new { success = false, msg = "记录不存在" } });

            var result = new DBContext.Table("track_links").Delete("id=@id", new { id });
            bool success = result > 0;
            if (success)
            {
                string eventType = (item.event_type ?? string.Empty).Trim().ToLowerInvariant();
                string platform = (item.platform ?? string.Empty).Trim();
                string typename = (item.typename ?? string.Empty).Trim();
                string scene = string.IsNullOrWhiteSpace(item.scene) ? string.Empty : item.scene.Trim();
                string uniqueId = string.IsNullOrWhiteSpace(item.unique_id) ? string.Empty : item.unique_id.Trim();

                string cacheKey = TracksCore.GetLinkCacheKey(eventType, platform, typename, scene, uniqueId);
                RedisHelper.Del(cacheKey);
                RedisHelper.Del(TracksCore.GetLinkIdCacheKey(item.id));
            }
            return new APIResult(new
            {
                data = new { success, msg = success ? "删除成功" : "删除失败" },
            });
        }
    }
}