using Backend.Services;
using PublicAPI.Models;
using Dapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Options;
using Backend.Extensions;
using System.Data;
using System.Xml.Linq;
using System.Diagnostics;
using PublicAPI.Controllers;
using Newtonsoft.Json;
namespace PublicAPI.Controllers {
[Authorize]
[ApiController]
[Route("api/[controller]")]
/**
* Table controller (methods for getting tables, lookups and CRUD operations)
@module TableController
*/
public class TableController : Controller {
private readonly AppOptions _options;
private readonly Db _db;
private readonly IDistributedCache _cache;
private readonly ILogger<HomeController> _logger;
private readonly AuthService _authService;
private readonly CurrentUserInfo _currentUserInfo;
public TableController(IOptions<AppOptions> options, Db db, IDistributedCache cache, ILogger<HomeController> logger, AuthService authService, CurrentUserInfo currentUserInfo) {
this._options = options.Value;
this._db = db;
this._cache = cache;
this._logger = logger;
this._authService = authService;
_currentUserInfo = currentUserInfo;
}
/**
* Add a row to a table
* @return {ActionResult} - Result of the operation
*/
[HttpPost("{table}")]
public async Task<ActionResult> AddRow(string table) {
using (StreamReader reader = new StreamReader(Request.Body)) {
string json = await reader.ReadToEndAsync();
var ret = await _db.QueryScalarAsync("zzglc." + table + "_c", new { Json = json }, _options.ConnectionString, true);
return Ok(ret);
}
}
/**
* Update a row in a table
* @return {ActionResult} - Result of the operation
*/
[HttpPut("{table}")]
public async Task<ActionResult> UpdateRow(string table) {
using (StreamReader reader = new StreamReader(Request.Body)) {
string json = await reader.ReadToEndAsync();
var ret = await _db.QueryScalarAsync("zzglc." + table + "_u", new { Json = json }, _options.ConnectionString, true);
return Ok(ret);
}
}
/**
* Get table
* @return {ActionResult} - Result of the operation
*/
[HttpGet("{table}")]
public async Task<ActionResult> GetRows(string table, string pars) {
object p = null;
if (pars != null && pars != "{}") {
p = new { Params = pars};
}
var ret = await _db.QueryAsyncFrugal("zzglc." + table + "_r", p, _options.ConnectionString, true);
return Ok(ret);
}
/**
* Delete a row from a table
* @param {string} table - Table
* @param {int} key - Key
* @return {ActionResult} - Result of the operation
*/
[HttpDelete("{table}/{key}")]
public async Task<ActionResult> DeleteRow(string table, int key) {
var ret = await _db.QueryScalarAsync("zzglc." + table + "_d", new { Key = key }, _options.ConnectionString, true);
return Ok(ret);
}
/**
* Delete rows from a table
* @param {string} table - Table
* @param {Dictionary<string, object>} pars - Parameters, must contain keys
* @return {ActionResult} - Result of the operation
*/
[HttpPut("DeleteRows/{table}")]
public async Task<ActionResult> DeleteRows(string table, [FromBody] Dictionary<string, object> pars) {
int[] keys = JsonConvert.DeserializeObject<int[]>(pars["keys"].ToString());
var ret = await _db.QueryScalarAsync("zzglc." + table + "_d", new { Keys = keys }, _options.ConnectionString, true);
return Ok(ret);
}
/**
* Get a lookup for a foreign key
* @param {Dictionary<string, string>} pars - Function
* @return {ActionResult} - Lookup
*/
[HttpGet("GetLookup/{table}")]
public async Task<ActionResult> GetLookup(string table) {
var schema = await _db.QueryScalarAsyncCached("meta.get_schema_name", new { TableName = table }, _options.ConnectionString, "long");
if (schema == "") return Ok("[]");
var ret = await _db.QueryAsyncCached("zzgll." + schema + "_" + table + "_l", new { SearchValue = "%", Key = (int?)null }, _options.ConnectionString, "long");
return Ok(ret);
}
/**
* Get a lookup for a parameter
* @param {Dictionary<string, string>} pars - Function
* @return {ActionResult} - Lookup
*/
[HttpGet("GetParamLookup/{lookup}")]
public async Task<ActionResult> GetParamLookup(string lookup) {
var ret = await _db.QueryAsyncCached(lookup, new { SearchValue = "%", Key = (int?)null }, _options.ConnectionString, "long");
return Ok(ret);
}
/**
* Get a table
* @param {Dictionary<string, string>} pars - Function, frugal, json
* @return {ActionResult} - Table
*/
[AllowAnonymous]
[HttpGet("GetTable")]
public async Task<ActionResult> GetTable(string dbFunction, bool frugal, bool json, string pars, string preprocess) {
string safe = "True";
if (!dbFunction.StartsWith("zzglc.")) {
safe = await _db.QueryScalarAsyncCached("meta.is_function_safe", new { Function = dbFunction }, _options.ConnectionString, "long");
}
if (safe != "True") {
return Ok(Helper.CreateError(_logger, dbFunction + " unauthorized " + safe));
}
string ret;
if (dbFunction.StartsWith("zzglc.")) {
ret = await _db.QueryAsyncFrugalCached(dbFunction, null, _options.ConnectionString, "long");
} else if (frugal) {
ret = await _db.QueryAsyncFrugalCached(dbFunction, new { Params = pars, PersonId = _currentUserInfo.PersonId, LangId = _currentUserInfo.LangId }, _options.ConnectionString, "long");
} else if (json) {
ret = await _db.QueryJsonAsyncCached(dbFunction, new { Params = pars, PersonId = _currentUserInfo.PersonId, LangId = _currentUserInfo.LangId }, _options.ConnectionString, "long");
} else {
ret = await _db.QueryAsyncCached(dbFunction, new { Params = pars, PersonId = _currentUserInfo.PersonId, LangId = _currentUserInfo.LangId }, _options.ConnectionString, "long");
}
PreprocessService.Preprocess(preprocess, ref ret);
return Ok(ret);
}
/**
* Clones a row of a table
* @return {ActionResult} - Result of the operation
*/
[HttpPost("Clone/{table}/{id}")]
public async Task<ActionResult> Clone(string table, int id) {
var ret = await _db.QueryScalarAsync("meta." + table + "_clone", new { Id = id }, _options.ConnectionString, true);
return Ok(ret);
}
}
}