988 lines
28 KiB
C#
988 lines
28 KiB
C#
using BCrypt.Net;
|
|
using Microsoft.AspNetCore.Http.Features;
|
|
using Microsoft.AspNetCore.Identity.Data;
|
|
using Microsoft.AspNetCore.WebSockets;
|
|
using Microsoft.IdentityModel.Tokens;
|
|
using MySqlConnector;
|
|
using SIPSorcery.Net;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IdentityModel.Tokens.Jwt;
|
|
using System.Net.WebSockets;
|
|
using System.Reflection.Metadata;
|
|
using System.Reflection.PortableExecutable;
|
|
using System.Security.Claims;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
|
|
|
|
var builder = WebApplication.CreateBuilder(args);
|
|
|
|
var connectionString = builder.Configuration.GetConnectionString("Default");
|
|
var jwtSecret = builder.Configuration["Jwt:Secret"] ?? "CHANGE_ME_TO_A_LONG_RANDOM_SECRET";
|
|
var jwtIssuer = builder.Configuration["Jwt:Issuer"] ?? "Ticket-System";
|
|
var jwtAudience = builder.Configuration["Jwt:Audience"] ?? "Ticket-System";
|
|
|
|
builder.Services.AddTransient(_ => new MySqlConnection(connectionString));
|
|
|
|
builder.Services.AddWebSockets(options =>
|
|
{
|
|
options.KeepAliveInterval = TimeSpan.FromSeconds(120);
|
|
});
|
|
|
|
var app = builder.Build();
|
|
|
|
app.UseWebSockets();
|
|
|
|
app.Use(async (context, next) =>
|
|
{
|
|
|
|
|
|
var path = context.Request.Path.Value ?? "";
|
|
|
|
if (path.Equals("/signin", StringComparison.OrdinalIgnoreCase)
|
|
|| path.Equals("/webrtc", StringComparison.OrdinalIgnoreCase)
|
|
|| path.Equals("/user/create", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
await next();
|
|
return;
|
|
}
|
|
|
|
if (!context.Request.Headers.TryGetValue("Authorization", out var authHeaderValues))
|
|
{
|
|
context.Response.StatusCode = StatusCodes.Status403Forbidden;
|
|
await context.Response.WriteAsJsonAsync(new { error = "Please add the JWT token to the header" });
|
|
return;
|
|
}
|
|
|
|
var authHeader = authHeaderValues.ToString();
|
|
var parts = authHeader.Split(' ', StringSplitOptions.RemoveEmptyEntries);
|
|
|
|
if (parts.Length != 2 || !parts[0].Equals("Bearer", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
context.Response.StatusCode = StatusCodes.Status409Conflict;
|
|
await context.Response.WriteAsJsonAsync(new { error = "Invalid token specified" });
|
|
return;
|
|
}
|
|
|
|
var token = parts[1];
|
|
|
|
var principal = ValidateJwt(token, jwtSecret, jwtIssuer, jwtAudience);
|
|
if (principal == null)
|
|
{
|
|
context.Response.StatusCode = StatusCodes.Status403Forbidden;
|
|
await context.Response.WriteAsJsonAsync(new { error = "Invalid token specified" });
|
|
return;
|
|
}
|
|
context.Items["user"] = principal.FindFirstValue(ClaimTypes.NameIdentifier) ?? "";
|
|
//context.Items["user"] = principal.Identity?.Name ?? principal.FindFirstValue(ClaimTypes.Name) ?? "";
|
|
await next();
|
|
});
|
|
|
|
|
|
|
|
app.MapPost("/signin", async (SignInRequest req, MySqlConnection conn) =>
|
|
{
|
|
try
|
|
{
|
|
const string sql = "SELECT userID, username, password FROM User WHERE username = @username LIMIT 1;";
|
|
|
|
await conn.OpenAsync();
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
cmd.Parameters.AddWithValue("@username", req.user);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
if (!await reader.ReadAsync())
|
|
return Results.Unauthorized();
|
|
|
|
var userId = reader.GetInt32("userID").ToString();
|
|
var username = reader.GetString("username");
|
|
var pwHash = reader.GetString("password");
|
|
|
|
var ok = BCrypt.Net.BCrypt.Verify(req.password, pwHash);
|
|
if (!ok)
|
|
return Results.Unauthorized();
|
|
|
|
conn.Close();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
var token = CreateJwt(userId, username, jwtSecret, jwtIssuer, jwtAudience, minutesValid: 120);
|
|
|
|
const string cleanupSql = "DELETE FROM ValidateToken\r\nWHERE STR_TO_DATE(validationDate, '%Y-%m-%d %H:%i:%s')\r\n < (UTC_TIMESTAMP() - INTERVAL 24 HOUR);\r\n";
|
|
await using (var cleanup = new MySqlCommand(cleanupSql, conn))
|
|
{
|
|
await cleanup.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
conn.Close();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string insertTokenSql = """
|
|
INSERT INTO ValidateToken (validationDate, token)
|
|
VALUES (@validationdate, @token);
|
|
""";
|
|
|
|
await using (var ins = new MySqlCommand(insertTokenSql, conn))
|
|
{
|
|
ins.Parameters.AddWithValue("@token", token);
|
|
ins.Parameters.AddWithValue("@validationdate", DateTime.Now);
|
|
await ins.ExecuteNonQueryAsync();
|
|
}
|
|
|
|
conn.Close();
|
|
|
|
return Results.Ok(new ResponseToken(token));
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return Results.Problem(ex.Message);
|
|
}
|
|
});
|
|
|
|
#region web rtc
|
|
|
|
app.Map("/webrtc", async context =>
|
|
{
|
|
if (!context.WebSockets.IsWebSocketRequest)
|
|
{
|
|
context.Response.StatusCode = 400;
|
|
return;
|
|
}
|
|
|
|
var token = context.Request.Query["token"].ToString();
|
|
var principal = string.IsNullOrWhiteSpace(token) ? null : ValidateJwt(token, jwtSecret, jwtIssuer, jwtAudience);
|
|
if (principal == null)
|
|
{
|
|
context.Response.StatusCode = 401;
|
|
return;
|
|
}
|
|
|
|
using var socket = await context.WebSockets.AcceptWebSocketAsync();
|
|
|
|
var pc = new RTCPeerConnection(new RTCConfiguration
|
|
{
|
|
iceServers = new List<RTCIceServer>
|
|
{
|
|
new RTCIceServer { urls = "http://10.204.192.64:3000" }
|
|
}
|
|
});
|
|
|
|
pc.oniceconnectionstatechange += (state) =>
|
|
Console.WriteLine($"[webrtc] ICE: {state}");
|
|
|
|
pc.onconnectionstatechange += (state) =>
|
|
Console.WriteLine($"[webrtc] PC: {state}");
|
|
|
|
// Server -> Client: ICE candidates
|
|
pc.onicecandidate += async cand =>
|
|
{
|
|
if (cand == null || socket.State != WebSocketState.Open) return;
|
|
|
|
var json = JsonSerializer.Serialize(cand);
|
|
await socket.SendAsync(Encoding.UTF8.GetBytes(json),
|
|
WebSocketMessageType.Text, true, CancellationToken.None);
|
|
};
|
|
|
|
var buffer = new byte[32 * 1024];
|
|
var jsonOpts = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
|
|
|
|
while (socket.State == WebSocketState.Open)
|
|
{
|
|
using var ms = new MemoryStream();
|
|
WebSocketReceiveResult res;
|
|
|
|
do
|
|
{
|
|
res = await socket.ReceiveAsync(buffer, CancellationToken.None);
|
|
|
|
if (res.MessageType == WebSocketMessageType.Close)
|
|
{
|
|
await socket.CloseAsync(WebSocketCloseStatus.NormalClosure, "bye", CancellationToken.None);
|
|
return;
|
|
}
|
|
|
|
ms.Write(buffer, 0, res.Count);
|
|
}
|
|
while (!res.EndOfMessage);
|
|
|
|
var msg = Encoding.UTF8.GetString(ms.ToArray());
|
|
|
|
if (msg.Contains("\"sdp\"", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
// Browser sendet meist { type: "offer", sdp: "..." }
|
|
var sdp = JsonSerializer.Deserialize<RTCSessionDescriptionInit>(msg, jsonOpts);
|
|
if (sdp == null)
|
|
{
|
|
Console.WriteLine("[webrtc] SDP deserialize = null");
|
|
continue;
|
|
}
|
|
|
|
// SIPSorcery kann je nach Version sync/async sein -> wir behandeln BEIDES:
|
|
var setRemoteOk = await SetRemote(pc, sdp);
|
|
if (!setRemoteOk)
|
|
{
|
|
Console.WriteLine("[webrtc] setRemoteDescription failed");
|
|
continue;
|
|
}
|
|
|
|
if (sdp.type == RTCSdpType.offer)
|
|
{
|
|
var answer = pc.createAnswer(null);
|
|
|
|
var setLocalOk = await SetLocal(pc, answer);
|
|
if (!setLocalOk)
|
|
{
|
|
Console.WriteLine("[webrtc] setLocalDescription failed");
|
|
continue;
|
|
}
|
|
|
|
var json = JsonSerializer.Serialize(answer, jsonOpts);
|
|
await socket.SendAsync(Encoding.UTF8.GetBytes(json),
|
|
WebSocketMessageType.Text, true, CancellationToken.None);
|
|
}
|
|
|
|
continue;
|
|
}
|
|
|
|
// ---- ICE candidate from client
|
|
if (msg.Contains("candidate", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
var ice = JsonSerializer.Deserialize<RTCIceCandidateInit>(msg, jsonOpts);
|
|
if (ice == null)
|
|
{
|
|
Console.WriteLine("[webrtc] ICE deserialize = null");
|
|
continue;
|
|
}
|
|
|
|
pc.addIceCandidate(ice);
|
|
continue;
|
|
}
|
|
|
|
Console.WriteLine($"[webrtc] unknown msg: {msg}");
|
|
}
|
|
});
|
|
static async Task HandleWebRtc(WebSocket socket)
|
|
{
|
|
var pc = new RTCPeerConnection(new RTCConfiguration
|
|
{
|
|
iceServers = new List<RTCIceServer>
|
|
{
|
|
new RTCIceServer { urls = "stun:stun.l.google.com:19302" }
|
|
}
|
|
});
|
|
|
|
pc.onicecandidate += async candidate =>
|
|
{
|
|
if (candidate != null)
|
|
{
|
|
var json = JsonSerializer.Serialize(candidate);
|
|
await socket.SendAsync(
|
|
Encoding.UTF8.GetBytes(json),
|
|
WebSocketMessageType.Text,
|
|
true,
|
|
CancellationToken.None
|
|
);
|
|
}
|
|
};
|
|
|
|
pc.onconnectionstatechange += (state) =>
|
|
{
|
|
Console.WriteLine($"[webrtc] connection state: {state}");
|
|
};
|
|
|
|
pc.oniceconnectionstatechange += (state) =>
|
|
{
|
|
Console.WriteLine($"[webrtc] ICE state: {state}");
|
|
};
|
|
|
|
pc.onicecandidate += (cand) =>
|
|
{
|
|
if (cand != null) Console.WriteLine("[webrtc] ICE candidate generated");
|
|
};
|
|
|
|
|
|
var buffer = new byte[8192];
|
|
|
|
var jsonOptions = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
|
|
|
|
while (socket.State == WebSocketState.Open)
|
|
{
|
|
var result = await socket.ReceiveAsync(buffer, CancellationToken.None);
|
|
|
|
if (result.MessageType == WebSocketMessageType.Close)
|
|
break;
|
|
|
|
var msg = Encoding.UTF8.GetString(buffer, 0, result.Count);
|
|
|
|
if (msg.Contains("\"sdp\""))
|
|
{
|
|
var sdp = JsonSerializer.Deserialize<RTCSessionDescriptionInit>(msg, jsonOptions);
|
|
if (sdp == null) continue;
|
|
|
|
var r1 = pc.setRemoteDescription(sdp);
|
|
if (r1 != SetDescriptionResultEnum.OK)
|
|
{
|
|
Console.WriteLine($"setRemoteDescription failed: {r1}");
|
|
continue;
|
|
}
|
|
|
|
if (sdp.type == RTCSdpType.offer)
|
|
{
|
|
var answer = pc.createAnswer(null);
|
|
|
|
var r2 = pc.setLocalDescription(answer);
|
|
|
|
var json = JsonSerializer.Serialize(answer, jsonOptions);
|
|
await socket.SendAsync(
|
|
Encoding.UTF8.GetBytes(json),
|
|
WebSocketMessageType.Text,
|
|
true,
|
|
CancellationToken.None
|
|
);
|
|
}
|
|
}
|
|
else if (msg.Contains("candidate"))
|
|
{
|
|
var ice = JsonSerializer.Deserialize<RTCIceCandidateInit>(msg, jsonOptions);
|
|
if (ice == null) continue;
|
|
|
|
pc.addIceCandidate(ice);
|
|
}
|
|
}
|
|
}
|
|
|
|
static async Task<bool> SetRemote(RTCPeerConnection pc, RTCSessionDescriptionInit sdp)
|
|
{
|
|
var r = pc.setRemoteDescription(sdp);
|
|
|
|
if (r is SetDescriptionResultEnum e)
|
|
return e == SetDescriptionResultEnum.OK;
|
|
|
|
if (r != null && r.GetType().Name.Contains("SetDescriptionResult"))
|
|
{
|
|
var prop = r.GetType().GetProperty("Result");
|
|
if (prop?.GetValue(r) is SetDescriptionResultEnum e2)
|
|
return e2 == SetDescriptionResultEnum.OK;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
static async Task<bool> SetLocal(RTCPeerConnection pc, RTCSessionDescriptionInit sdp)
|
|
{
|
|
var r = pc.setLocalDescription(sdp);
|
|
|
|
//if (r is SetDescriptionResultEnum e)
|
|
// return e == SetDescriptionResultEnum.OK;
|
|
|
|
//if (r != SetDescriptionResultEnum.OK)
|
|
//{
|
|
// Console.WriteLine($"setLocalDescription failed: {r}");
|
|
// //continue;
|
|
//}
|
|
|
|
if (r is Task<SetDescriptionResultEnum> t)
|
|
return (await t) == SetDescriptionResultEnum.OK;
|
|
|
|
if (r != null && r.GetType().Name.Contains("SetDescriptionResult"))
|
|
{
|
|
var prop = r.GetType().GetProperty("Result");
|
|
if (prop?.GetValue(r) is SetDescriptionResultEnum e2)
|
|
return e2 == SetDescriptionResultEnum.OK;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
#endregion
|
|
|
|
static ClaimsPrincipal? ValidateJwtFromQuery(HttpContext ctx, string secret, string issuer, string audience)
|
|
{
|
|
var token = ctx.Request.Query["token"].ToString();
|
|
if (string.IsNullOrEmpty(token)) return null;
|
|
return ValidateJwt(token, secret, issuer, audience);
|
|
}
|
|
|
|
app.MapGet("/token/validate", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
if (!ctx.Request.Headers.TryGetValue("Authorization", out var authHeaderValues))
|
|
return Results.Unauthorized();
|
|
|
|
var parts = authHeaderValues.ToString().Split(' ', StringSplitOptions.RemoveEmptyEntries);
|
|
if (parts.Length != 2 || !parts[0].Equals("Bearer", StringComparison.OrdinalIgnoreCase))
|
|
return Results.Unauthorized();
|
|
|
|
var token = parts[1];
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string checkSql = """
|
|
SELECT 1
|
|
FROM ValidateToken
|
|
WHERE token = @token
|
|
AND STR_TO_DATE(validationDate, '%Y-%m-%d %H:%i') >= (UTC_TIMESTAMP() - INTERVAL 24 HOUR)
|
|
LIMIT 1;
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(checkSql, conn);
|
|
cmd.Parameters.AddWithValue("@token", token);
|
|
|
|
var ok = (await cmd.ExecuteScalarAsync()) != null;
|
|
return Results.Ok(new TokenIsValid(ok));
|
|
});
|
|
|
|
app.MapGet("/me", (HttpContext ctx) =>
|
|
{
|
|
var user = ctx.Items["user"]?.ToString() ?? "";
|
|
return Results.Ok(new { user });
|
|
});
|
|
|
|
|
|
app.MapPost("/user/create", async (RegisterRequest req, MySqlConnection conn) =>
|
|
{
|
|
await conn.OpenAsync();
|
|
|
|
var pwHash = BCrypt.Net.BCrypt.HashPassword(req.password);
|
|
var userId = Guid.NewGuid().ToString("N");
|
|
|
|
const string insertSql = """
|
|
INSERT INTO User (userID, username, password)
|
|
VALUES (@userID, @username, @password);
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(insertSql, conn);
|
|
cmd.Parameters.AddWithValue("@userID", userId);
|
|
cmd.Parameters.AddWithValue("@username", req.user);
|
|
cmd.Parameters.AddWithValue("@password", pwHash);
|
|
|
|
var rows = await cmd.ExecuteNonQueryAsync();
|
|
if (rows != 1)
|
|
return Results.Problem("User konnte nicht angelegt werden.");
|
|
|
|
var token = CreateJwt(userId, req.user, jwtSecret, jwtIssuer, jwtAudience, minutesValid: 120);
|
|
|
|
return Results.Created($"/user/{userId}", new ResponseToken(token));
|
|
});
|
|
|
|
app.MapGet("/user/show/all", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string sql = """
|
|
SELECT *
|
|
FROM User
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
var user = new List<object>();
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
user.Add(new
|
|
{
|
|
userID = reader.GetInt64("userID"),
|
|
username = reader.GetString("username"),
|
|
isEmployee = reader.GetInt32("isEmployee"),
|
|
});
|
|
}
|
|
|
|
return Results.Ok(user);
|
|
});
|
|
|
|
app.MapGet("/categories", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
await conn.OpenAsync();
|
|
|
|
const string sql = """
|
|
SELECT categoryname
|
|
FROM Category
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
List<string> categories = new List<string>();
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
categories.Add(reader.GetString("categoryname"));
|
|
}
|
|
|
|
return Results.Ok(categories);
|
|
});
|
|
|
|
|
|
app.MapGet("/user/hasrights/categories", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
await conn.OpenAsync();
|
|
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
const string sql = """
|
|
SELECT c.categoryname
|
|
FROM User u
|
|
JOIN Category c ON c.categoryId = u.categoryId
|
|
WHERE u.userId = @userId;
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
cmd.Parameters.AddWithValue("@userID", userId);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
List<string> categorys = new List<string>();
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
categorys.Add(reader.GetString("categoryname"));
|
|
}
|
|
|
|
return Results.Ok(categorys);
|
|
});
|
|
|
|
app.MapGet("/user/hasrights", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string sql = """
|
|
SELECT isEmployee
|
|
FROM User
|
|
WHERE userID = @userID
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
cmd.Parameters.AddWithValue("@userID", userId);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
if (reader.GetInt32("isEmployee") == 1)
|
|
{
|
|
return Results.Ok($"User: {userId} has rights = true");
|
|
}
|
|
else
|
|
{
|
|
return Results.Ok($"User: {userId} has rights = false");
|
|
}
|
|
}
|
|
|
|
return Results.Unauthorized();
|
|
});
|
|
|
|
app.MapPost("/ticket/create", async (HttpContext ctx, Ticket req, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
int categoryId = 0;
|
|
|
|
switch (req.category)
|
|
{
|
|
case "Hardware":
|
|
categoryId = 1;
|
|
break;
|
|
|
|
case "Software":
|
|
categoryId = 2;
|
|
break;
|
|
|
|
case "Schäden":
|
|
categoryId = 3;
|
|
break;
|
|
|
|
case "Personal-Probleme":
|
|
categoryId = 4;
|
|
break;
|
|
}
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string insertSql = """
|
|
INSERT INTO Ticket (
|
|
userID,
|
|
ticketname,
|
|
status,
|
|
category,
|
|
description,
|
|
categoryId,
|
|
priority,
|
|
opendAt
|
|
)
|
|
VALUES (
|
|
@userID,
|
|
@ticketname,
|
|
@status,
|
|
@category,
|
|
@description,
|
|
@categoryId,
|
|
@priority,
|
|
@opendAt
|
|
);
|
|
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(insertSql, conn);
|
|
cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(userId));
|
|
cmd.Parameters.AddWithValue("@ticketname", req.ticketname);
|
|
cmd.Parameters.AddWithValue("@category", req.category);
|
|
cmd.Parameters.AddWithValue("@categoryId", categoryId);
|
|
cmd.Parameters.AddWithValue("@description", req.description);
|
|
cmd.Parameters.AddWithValue("@status", Convert.ToInt32(req.status));
|
|
cmd.Parameters.AddWithValue("@priority", Convert.ToInt32(req.priority));
|
|
cmd.Parameters.AddWithValue("@opendAt", DateTime.UtcNow);
|
|
|
|
|
|
var rows = await cmd.ExecuteNonQueryAsync();
|
|
if (rows != 1)
|
|
return Results.Problem("Ticket konnte nicht angelegt werden");
|
|
|
|
var ticketId = cmd.LastInsertedId;
|
|
|
|
return Results.Created($"/ticket/show/{ticketId}", new
|
|
{
|
|
ticketID = ticketId
|
|
});
|
|
});
|
|
|
|
app.MapGet("/ticket/show/all", async (HttpContext ctx, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string sql = """
|
|
SELECT
|
|
t.ticketID,
|
|
t.ticketname,
|
|
owner.username AS owner_name,
|
|
t.status,
|
|
t.priority,
|
|
t.categoryId
|
|
FROM Ticket t
|
|
LEFT JOIN User owner ON t.userID = owner.userID
|
|
CROSS JOIN User u_search
|
|
WHERE u_search.userID = @userID
|
|
AND (t.userID = u_search.userID OR t.categoryId = u_search.categoryId)
|
|
ORDER BY t.priority;
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
cmd.Parameters.AddWithValue("@userID", userId);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
|
|
var tickets = new List<object>();
|
|
|
|
while (await reader.ReadAsync())
|
|
{
|
|
tickets.Add(new
|
|
{
|
|
ticketID = reader.GetInt64("ticketID"),
|
|
ticketname = reader.GetString("ticketname"),
|
|
username = reader.IsDBNull("owner_name") ? "" : reader.GetString("owner_name"),
|
|
status = reader.GetInt32("status"),
|
|
priority = reader.GetInt32("priority"),
|
|
categoryId = reader.IsDBNull("categoryId") ? 0 : Convert.ToInt32(reader.GetInt32("categoryId")),
|
|
});
|
|
}
|
|
|
|
|
|
return Results.Ok(tickets);
|
|
});
|
|
|
|
app.MapPost("/ticket/update/{ticketId:int}", async (HttpContext ctx, int ticketID, Ticket req, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
int categoryId = 0;
|
|
|
|
switch (req.category)
|
|
{
|
|
case "Hardware":
|
|
categoryId = 1;
|
|
break;
|
|
|
|
case "Software":
|
|
categoryId = 2;
|
|
break;
|
|
|
|
case "Schäden":
|
|
categoryId = 3;
|
|
break;
|
|
|
|
case "Personal-Probleme":
|
|
categoryId = 4;
|
|
break;
|
|
}
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string updateSql = """
|
|
UPDATE Ticket
|
|
SET
|
|
ticketname = @ticketname,
|
|
status = @status,
|
|
priority = @priority,
|
|
opendAt = @opendAt,
|
|
description = @description,
|
|
category = @category,
|
|
categoryID = @categoryId
|
|
WHERE
|
|
ticketID = @ticketID
|
|
AND userID = @userID;
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(updateSql, conn);
|
|
cmd.Parameters.AddWithValue("@ticketID", ticketID);
|
|
cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(userId));
|
|
cmd.Parameters.AddWithValue("@ticketname", req.ticketname);
|
|
cmd.Parameters.AddWithValue("@category", req.category);
|
|
cmd.Parameters.AddWithValue("@categoryId", categoryId);
|
|
cmd.Parameters.AddWithValue("@description", req.description);
|
|
cmd.Parameters.AddWithValue("@status", Convert.ToInt32(req.status));
|
|
cmd.Parameters.AddWithValue("@priority", Convert.ToInt32(req.priority));
|
|
cmd.Parameters.AddWithValue("@opendAt", DateTime.UtcNow);
|
|
|
|
var rows = await cmd.ExecuteNonQueryAsync();
|
|
if (rows != 1)
|
|
return Results.Problem("Ticket konnte nicht aktualisiert werden");
|
|
|
|
return Results.Ok(new
|
|
{
|
|
ticketID = ticketID,
|
|
updated = true
|
|
});
|
|
});
|
|
|
|
app.MapGet("/ticket/show/{ticketId:int}", async (
|
|
HttpContext ctx,
|
|
int ticketId,
|
|
MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrWhiteSpace(userId))
|
|
return Results.Unauthorized();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string ticketSql = """
|
|
SELECT
|
|
t.ticketID,
|
|
t.ticketname,
|
|
t.userID,
|
|
u.username,
|
|
t.status,
|
|
t.description,
|
|
t.priority,
|
|
t.opendAt,
|
|
t.closedAt,
|
|
t.category
|
|
FROM Ticket t
|
|
JOIN User u ON u.userID = t.userID
|
|
WHERE t.ticketID = @ticketId
|
|
AND t.userID = @userId
|
|
LIMIT 1;
|
|
""";
|
|
|
|
long vticketID;
|
|
string vticketname;
|
|
long vuserID;
|
|
string vusername;
|
|
int vstatus;
|
|
string vdescription;
|
|
int vpriority;
|
|
string vopenAt;
|
|
string? vclosedAt;
|
|
string vcategory;
|
|
|
|
await using (var cmd = new MySqlCommand(ticketSql, conn))
|
|
{
|
|
cmd.Parameters.AddWithValue("@ticketId", ticketId);
|
|
cmd.Parameters.AddWithValue("@userId", userId);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
if (!await reader.ReadAsync())
|
|
return Results.NotFound("Ticket nicht gefunden");
|
|
|
|
vticketID = reader.GetInt64("ticketID");
|
|
vticketname = reader.GetString("ticketname");
|
|
vuserID = reader.GetInt64("userID");
|
|
vusername = reader.GetString("username");
|
|
vstatus = reader.GetInt32("status");
|
|
vdescription = reader.IsDBNull("description") ? "" : reader.GetString("description");
|
|
|
|
vpriority = reader.GetInt32("priority");
|
|
vopenAt = reader.GetString("opendAt");
|
|
vclosedAt = reader.IsDBNull("closedAt") ? null : reader.GetString("closedAt");
|
|
vcategory = reader.GetString("category");
|
|
}
|
|
|
|
const string messagesSql = """
|
|
SELECT
|
|
m.messageID,
|
|
m.sequence,
|
|
m.sendAt,
|
|
m.content,
|
|
m.sender,
|
|
u.username AS senderUsername
|
|
FROM Messages m
|
|
LEFT JOIN User u ON u.userID = m.sender
|
|
WHERE m.ticketID = @ticketId
|
|
ORDER BY m.sequence ASC;
|
|
""";
|
|
|
|
var vmessages = new List<object>();
|
|
|
|
await using (var cmd = new MySqlCommand(messagesSql, conn))
|
|
{
|
|
cmd.Parameters.AddWithValue("@ticketId", ticketId);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
vmessages.Add(new
|
|
{
|
|
messageID = reader.GetInt64("messageID"),
|
|
sequence = reader.GetInt32("sequence"),
|
|
sendAt = reader.GetString("sendAt"),
|
|
content = reader.GetString("content"),
|
|
sender = reader.GetInt64("sender"),
|
|
senderUsername = reader.IsDBNull("senderUsername") ? null : reader.GetString("senderUsername")
|
|
});
|
|
}
|
|
}
|
|
|
|
return Results.Ok(new
|
|
{
|
|
ticketID = vticketID,
|
|
ticketname = vticketname,
|
|
userID = vuserID,
|
|
username = vusername,
|
|
description = vdescription,
|
|
status = vstatus,
|
|
priority = vpriority,
|
|
openAt = vopenAt,
|
|
closedAt = vclosedAt,
|
|
category = vcategory,
|
|
messages = vmessages
|
|
});
|
|
});
|
|
|
|
|
|
app.MapPost("/message/attachment", async (HttpContext ctx, AddAttachments req, MySqlConnection conn) =>
|
|
{
|
|
var userId = ctx.Items["user"]?.ToString();
|
|
if (string.IsNullOrEmpty(userId))
|
|
return Results.Unauthorized();
|
|
|
|
await conn.OpenAsync();
|
|
|
|
const string sql = """
|
|
INSERT INTO Attachments (ticketID, messageID, attachment) values (@ticketId, @messageId, @attachment);
|
|
""";
|
|
|
|
await using var cmd = new MySqlCommand(sql, conn);
|
|
cmd.Parameters.AddWithValue("@ticketId", req.ticketId);
|
|
cmd.Parameters.AddWithValue("@messageID", req.messageId);
|
|
cmd.Parameters.AddWithValue("@attachments", req.attachments);
|
|
|
|
var rows = await cmd.ExecuteNonQueryAsync();
|
|
if (rows != 1)
|
|
return Results.Problem("User konnte nicht angelegt werden.");
|
|
|
|
return Results.Ok();
|
|
});
|
|
|
|
static string CreateJwt(string userId, string username, string secret, string issuer, string audience, int minutesValid)
|
|
{
|
|
var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(secret));
|
|
var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
|
|
|
|
var claims = new List<Claim>
|
|
{
|
|
new Claim(ClaimTypes.NameIdentifier, userId),
|
|
new Claim(ClaimTypes.Name, username),
|
|
};
|
|
|
|
var token = new JwtSecurityToken(
|
|
issuer: issuer,
|
|
audience: audience,
|
|
claims: claims,
|
|
notBefore: DateTime.UtcNow,
|
|
expires: DateTime.UtcNow.AddMinutes(minutesValid),
|
|
signingCredentials: creds
|
|
);
|
|
|
|
return new JwtSecurityTokenHandler().WriteToken(token);
|
|
}
|
|
|
|
static ClaimsPrincipal? ValidateJwt(string token, string secret, string issuer, string audience)
|
|
{
|
|
var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(secret));
|
|
var handler = new JwtSecurityTokenHandler();
|
|
|
|
try
|
|
{
|
|
var principal = handler.ValidateToken(token, new TokenValidationParameters
|
|
{
|
|
ValidateIssuer = true,
|
|
ValidIssuer = issuer,
|
|
ValidateAudience = true,
|
|
ValidAudience = audience,
|
|
ValidateIssuerSigningKey = true,
|
|
IssuerSigningKey = key,
|
|
ValidateLifetime = true,
|
|
ClockSkew = TimeSpan.FromSeconds(30)
|
|
}, out _);
|
|
|
|
return principal;
|
|
}
|
|
catch
|
|
{
|
|
return null;
|
|
}
|
|
;
|
|
}
|
|
|
|
app.Run();
|
|
|
|
public record RegisterRequest(string user, string password);
|
|
public record SignInRequest(string user, string password);
|
|
|
|
public record AddAttachments(string ticketId, string messageId, Blob attachments);
|
|
public record ResponseToken(string token);
|
|
public record TokenIsValid(bool is_valid);
|
|
public record Ticket(
|
|
int status,
|
|
int priority,
|
|
string category,
|
|
string username,
|
|
string ticketname,
|
|
string description
|
|
);
|
|
|