public IActionResult Post([FromBody] SetCalendarLogRq request)
{
var result = new CalendarLogRs();
if (request == null || string.IsNullOrEmpty(request.Title))
{
return(StatusCode(400, "Please fill correctly request."));
}
else
{
_logger.LogInformation($" [Request] ... {JsonConvert.SerializeObject(request)}");
}
try {
using (MySqlConnection connection = new MySqlConnection(_appSettings.GetDataBaseConnectionString())) {
connection.Open();
var query = $"INSERT INTO " +
$"CALENDAR_EVENT(`title`,`start`,`end`,`timestamp`) " +
$"VALUES (@title,@start,@end,@timestamp)";
var command = new MySqlCommand(query, connection);
command.Parameters.Add("@title", MySqlDbType.String);
command.Parameters["@title"].Value = request.Title;
command.Parameters.Add("@start", MySqlDbType.DateTime);
command.Parameters["@start"].Value = request.Start;
command.Parameters.Add("@end", MySqlDbType.DateTime);
command.Parameters["@end"].Value = request.End;
command.Parameters.Add("@timestamp", MySqlDbType.DateTime);
command.Parameters["@timestamp"].Value = DateTime.Now;
Int32 id = (int)command.ExecuteScalar();
result.Id = id;
_logger.LogInformation($" [Insered id] ... {id}");
}
} catch (System.Exception ex) {
_logger.LogError($"{ex.Message}");
return(StatusCode(500));
}
return(StatusCode(200, result));
}
public ActionResult <CalendarLogRs> Get(int id)
{
var result = new CalendarLogRs();
_logger.LogInformation($" [*********************** >][Request - id] : {id}");
try {
using (MySqlConnection connection = new MySqlConnection(_appSettings.GetDataBaseConnectionString())) {
var value = id;
connection.Open();
var query = $"SELECT * FROM CALENDAR_EVENT " +
$"WHERE id = @id ";
var command = new MySqlCommand(query, connection);
command.Parameters.Add("@id", MySqlDbType.Int32);
command.Parameters["@id"].Value = value;
var sqlReader = command.ExecuteReader();
if (sqlReader.HasRows)
{
result = new CalendarLogRs();
while (sqlReader.Read())
{
result.Id = sqlReader.GetInt32(sqlReader.GetOrdinal("id"));
result.Title = sqlReader.GetString(sqlReader.GetOrdinal("title"));
result.Start = sqlReader.GetDateTime(sqlReader.GetOrdinal("start"));
result.End = sqlReader.GetDateTime(sqlReader.GetOrdinal("end"));
}
}
else
{
_logger.LogInformation($" No results .");
return(StatusCode(204));
}
}
} catch (System.Exception ex) {
_logger.LogError($"{ex.Message}");
return(StatusCode(500));
}
_logger.LogInformation($" [*********************** >][Result - id] : {JsonConvert.SerializeObject(result)}");
return(result);
}
public ActionResult <CalendarDataTableRs> GetDataTable(CalendarDataTableRq request)
{
if (request == null)
{
return(StatusCode(400));
}
else
{
_logger.LogInformation($" [*********************** >][Request] : {JsonConvert.SerializeObject(request)}");
}
var result = new CalendarDataTableRs();
List <CalendarLogRs> DataBaseResult = new List <CalendarLogRs> ();
try {
using (MySqlConnection connection = new MySqlConnection(_appSettings.GetDataBaseConnectionString())) {
var queryConditions = "";
if (request.order != null && request.order.Any())
{
foreach (var item in request.order)
{
foreach (var itemOfDicctionary in item)
{
if (itemOfDicctionary.Key.Equals("column"))
{
queryConditions += $" ORDER BY id ";
}
if (itemOfDicctionary.Key.Equals("dir"))
{
queryConditions += $" {itemOfDicctionary.Value.ToUpper()} ";
}
}
}
queryConditions += $"";
}
if (request.length > 0)
{
queryConditions += $" LIMIT {request.start},{request.length} ";
}
connection.Open();
var query = $"SELECT * FROM CALENDAR_EVENT " +
$"WHERE 1=1 " +
$"{queryConditions}" +
$"";
var command = new MySqlCommand(query, connection);
var sqlReader = command.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
var log = new CalendarLogRs();
log.Id = sqlReader.GetInt32(sqlReader.GetOrdinal("id"));
log.Title = sqlReader.GetString(sqlReader.GetOrdinal("title"));
log.Start = sqlReader.GetDateTime(sqlReader.GetOrdinal("start"));
log.End = sqlReader.GetDateTime(sqlReader.GetOrdinal("end"));
log.Timestamp = sqlReader.GetDateTime(sqlReader.GetOrdinal("timestamp"));
DataBaseResult.Add(log);
}
// satisfy datatable model
result.Data = DataBaseResult;
result.Draw = request.draw;
if (DataBaseResult != null && DataBaseResult.Any())
{
result.RecordsTotal = this.Get().Value.ToList().Count;
result.RecordsFiltered = this.Get().Value.ToList().Count;
}
}
else
{
_logger.LogInformation($" No results .");
return(StatusCode(204));
}
}
} catch (System.Exception ex) {
_logger.LogError($"{ex.Message}");
return(StatusCode(500));
}
_logger.LogInformation($" [*********************** >][Result - count] : {result.RecordsTotal}");
return(result);
}
