326 lines
13 KiB
C#
326 lines
13 KiB
C#
using Microsoft.AspNetCore.Mvc;
|
||
using MySql.Data.MySqlClient;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace WxCheckMvc.Controllers
|
||
{
|
||
[Route("api/[controller]/[action]")]
|
||
[ApiController]
|
||
public class AdminController : ControllerBase
|
||
{
|
||
private readonly MySqlConnection _connection;
|
||
|
||
public AdminController(MySqlConnection connection)
|
||
{
|
||
_connection = connection;
|
||
}
|
||
|
||
[HttpPost]
|
||
public async Task<IActionResult> QueryConversations([FromBody] ConversationQueryRequest request)
|
||
{
|
||
try
|
||
{
|
||
if (_connection.State != ConnectionState.Open)
|
||
{
|
||
await _connection.OpenAsync();
|
||
}
|
||
|
||
List<ConversationQueryResponse> conversations = [];
|
||
|
||
string query = @"SELECT c.Id, c.Guid, c.UserKey, c.ConversationContent, c.SendMethod,
|
||
c.UserLocation, c.Latitude, c.Longitude, c.RecordTime,
|
||
c.RecordTimeUTCStamp, c.IsDeleted, c.CreateTime, c.MessageType, c.SpeakingTime,
|
||
u.UserName, u.WeChatName, u.PhoneNumber, u.AvatarUrl, u.Department
|
||
FROM xcx_conversation c
|
||
LEFT JOIN xcx_users u ON c.UserKey = u.UserKey
|
||
WHERE c.IsDeleted = 0";
|
||
|
||
var parameters = new List<MySqlParameter>();
|
||
|
||
if (!string.IsNullOrEmpty(request.UserKey))
|
||
{
|
||
query += " AND c.UserKey = @UserKey";
|
||
parameters.Add(new MySqlParameter("@UserKey", request.UserKey));
|
||
}
|
||
|
||
if (request.MessageType.HasValue)
|
||
{
|
||
query += " AND c.MessageType = @MessageType";
|
||
parameters.Add(new MySqlParameter("@MessageType", request.MessageType.Value));
|
||
}
|
||
|
||
if (request.StartTime.HasValue)
|
||
{
|
||
long startUtcStamp = new DateTimeOffset(request.StartTime.Value).ToUnixTimeMilliseconds();
|
||
query += " AND c.RecordTimeUTCStamp >= @StartTime";
|
||
parameters.Add(new MySqlParameter("@StartTime", startUtcStamp));
|
||
}
|
||
|
||
if (request.EndTime.HasValue)
|
||
{
|
||
long endUtcStamp = new DateTimeOffset(request.EndTime.Value).ToUnixTimeMilliseconds();
|
||
query += " AND c.RecordTimeUTCStamp <= @EndTime";
|
||
parameters.Add(new MySqlParameter("@EndTime", endUtcStamp));
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(request.Department))
|
||
{
|
||
query += " AND u.Department = @Department";
|
||
parameters.Add(new MySqlParameter("@Department", request.Department));
|
||
}
|
||
|
||
query += " ORDER BY c.RecordTimeUTCStamp DESC";
|
||
|
||
int offset = (request.Page - 1) * request.PageSize;
|
||
query += " LIMIT @Limit OFFSET @Offset";
|
||
parameters.Add(new MySqlParameter("@Limit", request.PageSize));
|
||
parameters.Add(new MySqlParameter("@Offset", offset));
|
||
|
||
using (MySqlCommand cmd = new(query, _connection))
|
||
{
|
||
cmd.Parameters.AddRange(parameters.ToArray());
|
||
|
||
using (var reader = await cmd.ExecuteReaderAsync())
|
||
{
|
||
while (await reader.ReadAsync())
|
||
{
|
||
conversations.Add(new ConversationQueryResponse
|
||
{
|
||
Id = reader.GetInt64(0),
|
||
Guid = reader.IsDBNull(1) ? "" : reader.GetString(1),
|
||
UserKey = reader.GetString(2),
|
||
ConversationContent = reader.GetString(3),
|
||
SendMethod = reader.GetString(4),
|
||
UserLocation = reader.IsDBNull(5) ? "" : reader.GetString(5),
|
||
Latitude = reader.IsDBNull(6) ? "" : reader.GetString(6),
|
||
Longitude = reader.IsDBNull(7) ? "" : reader.GetString(7),
|
||
RecordTime = reader.GetDateTime(8),
|
||
RecordTimeUTCStamp = reader.GetInt64(9),
|
||
IsDeleted = reader.GetBoolean(10),
|
||
CreateTime = reader.GetDateTime(11),
|
||
MessageType = reader.GetInt32(12),
|
||
SpeakingTime = reader.IsDBNull(13) ? null : reader.GetInt32(13),
|
||
UserName = reader.IsDBNull(14) ? "" : reader.GetString(14),
|
||
WeChatName = reader.IsDBNull(15) ? "" : reader.GetString(15),
|
||
PhoneNumber = reader.IsDBNull(16) ? "" : reader.GetString(16),
|
||
AvatarUrl = reader.IsDBNull(17) ? "" : reader.GetString(17),
|
||
Department = reader.IsDBNull(18) ? "" : reader.GetString(18)
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return Ok(new { success = true, data = conversations });
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
return StatusCode(500, new { success = false, message = "查询失败", error = ex.Message });
|
||
}
|
||
finally
|
||
{
|
||
if (_connection.State == ConnectionState.Open)
|
||
{
|
||
await _connection.CloseAsync();
|
||
}
|
||
}
|
||
}
|
||
|
||
[HttpGet]
|
||
public async Task<IActionResult> QueryUsers()
|
||
{
|
||
try
|
||
{
|
||
if (_connection.State != ConnectionState.Open)
|
||
{
|
||
await _connection.OpenAsync();
|
||
}
|
||
|
||
List<UserQueryResponse> users = [];
|
||
|
||
string query = @"SELECT Id, UserName, UserKey, WeChatName, PhoneNumber,
|
||
FirstLoginTime, IsDisabled, CreateTime, UpdateTime, AvatarUrl, Department
|
||
FROM xcx_users
|
||
WHERE PhoneNumber IS NOT NULL
|
||
AND PhoneNumber != ''
|
||
AND UserName IS NOT NULL
|
||
AND UserName != ''
|
||
AND UserKey IS NOT NULL
|
||
AND UserKey != ''
|
||
AND IsDisabled = 0
|
||
ORDER BY FirstLoginTime DESC";
|
||
|
||
using (MySqlCommand cmd = new(query, _connection))
|
||
{
|
||
using (var reader = await cmd.ExecuteReaderAsync())
|
||
{
|
||
while (await reader.ReadAsync())
|
||
{
|
||
users.Add(new UserQueryResponse
|
||
{
|
||
Id = reader.GetInt64(0),
|
||
UserName = reader.IsDBNull(1) ? "" : reader.GetString(1),
|
||
UserKey = reader.GetString(2),
|
||
WeChatName = reader.IsDBNull(3) ? "" : reader.GetString(3),
|
||
PhoneNumber = reader.IsDBNull(4) ? "" : reader.GetString(4),
|
||
FirstLoginTime = reader.GetDateTime(5),
|
||
IsDisabled = reader.GetBoolean(6),
|
||
CreateTime = reader.GetDateTime(7),
|
||
UpdateTime = reader.GetDateTime(8),
|
||
AvatarUrl = reader.IsDBNull(9) ? "" : reader.GetString(9),
|
||
Department = reader.IsDBNull(10) ? "" : reader.GetString(10)
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return Ok(new { success = true, data = users });
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
return StatusCode(500, new { success = false, message = "查询失败", error = ex.Message });
|
||
}
|
||
finally
|
||
{
|
||
if (_connection.State == ConnectionState.Open)
|
||
{
|
||
await _connection.CloseAsync();
|
||
}
|
||
}
|
||
}
|
||
|
||
[HttpGet]
|
||
public async Task<IActionResult> QueryStats()
|
||
{
|
||
try
|
||
{
|
||
if (_connection.State != ConnectionState.Open)
|
||
{
|
||
await _connection.OpenAsync();
|
||
}
|
||
|
||
// 1) 活跃用户：最近 7 天登录（UpdateTime）且 UserKey/PhoneNumber 不为空
|
||
long activeUsers;
|
||
using (MySqlCommand cmd = new(@"
|
||
SELECT COUNT(1)
|
||
FROM xcx_users
|
||
WHERE UpdateTime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
||
AND UserKey IS NOT NULL AND UserKey <> ''
|
||
AND PhoneNumber IS NOT NULL AND PhoneNumber <> ''", _connection))
|
||
{
|
||
activeUsers = Convert.ToInt64(await cmd.ExecuteScalarAsync());
|
||
}
|
||
|
||
// 2) 总会话记录数
|
||
long totalConversations;
|
||
using (MySqlCommand cmd = new("SELECT COUNT(1) FROM xcx_conversation", _connection))
|
||
{
|
||
totalConversations = Convert.ToInt64(await cmd.ExecuteScalarAsync());
|
||
}
|
||
|
||
// 3) 今日新增会话记录（CreateTime 在今天内）
|
||
long todayNewConversations;
|
||
using (MySqlCommand cmd = new(@"
|
||
SELECT COUNT(1)
|
||
FROM xcx_conversation
|
||
WHERE CreateTime >= CURDATE()
|
||
AND CreateTime < DATE_ADD(CURDATE(), INTERVAL 1 DAY)", _connection))
|
||
{
|
||
todayNewConversations = Convert.ToInt64(await cmd.ExecuteScalarAsync());
|
||
}
|
||
|
||
// 4) 总用户数：UserKey/PhoneNumber 不为空
|
||
long totalUsers;
|
||
using (MySqlCommand cmd = new(@"
|
||
SELECT COUNT(1)
|
||
FROM xcx_users
|
||
WHERE UserKey IS NOT NULL AND UserKey <> ''
|
||
AND PhoneNumber IS NOT NULL AND PhoneNumber <> ''", _connection))
|
||
{
|
||
totalUsers = Convert.ToInt64(await cmd.ExecuteScalarAsync());
|
||
}
|
||
|
||
var data = new AdminStatsResponse
|
||
{
|
||
ActiveUsers = activeUsers,
|
||
TotalConversations = totalConversations,
|
||
TodayNewConversations = todayNewConversations,
|
||
TotalUsers = totalUsers
|
||
};
|
||
|
||
return Ok(new { success = true, data });
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
return StatusCode(500, new { success = false, message = "查询失败", error = ex.Message });
|
||
}
|
||
finally
|
||
{
|
||
if (_connection.State == ConnectionState.Open)
|
||
{
|
||
await _connection.CloseAsync();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
public class ConversationQueryRequest
|
||
{
|
||
public DateTime? StartTime { get; set; }
|
||
public DateTime? EndTime { get; set; }
|
||
public string UserKey { get; set; }
|
||
public int? MessageType { get; set; }
|
||
public string Department { get; set; }
|
||
public int Page { get; set; } = 1;
|
||
public int PageSize { get; set; } = 20;
|
||
}
|
||
|
||
public class ConversationQueryResponse
|
||
{
|
||
public long Id { get; set; }
|
||
public string Guid { get; set; }
|
||
public string UserKey { get; set; }
|
||
public string ConversationContent { get; set; }
|
||
public string SendMethod { get; set; }
|
||
public string UserLocation { get; set; }
|
||
public string Latitude { get; set; }
|
||
public string Longitude { get; set; }
|
||
public DateTime RecordTime { get; set; }
|
||
public long RecordTimeUTCStamp { get; set; }
|
||
public bool IsDeleted { get; set; }
|
||
public DateTime CreateTime { get; set; }
|
||
public int MessageType { get; set; }
|
||
public int? SpeakingTime { get; set; }
|
||
public string UserName { get; set; }
|
||
public string WeChatName { get; set; }
|
||
public string PhoneNumber { get; set; }
|
||
public string AvatarUrl { get; set; }
|
||
public string Department { get; set; }
|
||
}
|
||
|
||
public class UserQueryResponse
|
||
{
|
||
public long Id { get; set; }
|
||
public string UserName { get; set; }
|
||
public string UserKey { get; set; }
|
||
public string WeChatName { get; set; }
|
||
public string PhoneNumber { get; set; }
|
||
public DateTime FirstLoginTime { get; set; }
|
||
public bool IsDisabled { get; set; }
|
||
public DateTime CreateTime { get; set; }
|
||
public DateTime UpdateTime { get; set; }
|
||
public string AvatarUrl { get; set; }
|
||
public string Department { get; set; }
|
||
}
|
||
|
||
public class AdminStatsResponse
|
||
{
|
||
public long ActiveUsers { get; set; }
|
||
public long TotalConversations { get; set; }
|
||
public long TodayNewConversations { get; set; }
|
||
public long TotalUsers { get; set; }
|
||
}
|
||
}
|