public IActionResult Register([FromBody] PortCallDetails portCallDetails)
{
Console.WriteLine("DETAILS ID: " + portCallDetails.PortCallDetailsId + "\n\n\n");
if (!ValidateDetails(portCallDetails))
{
return(BadRequest("Invalid port call details form data."));
}
try
{
if (_context.PortCallDetails.Any(details => details.PortCallDetailsId == portCallDetails.PortCallDetailsId))
{
_context.PortCallDetails.Update(portCallDetails);
}
else
{
_context.PortCallDetails.Add(portCallDetails);
}
_context.SaveChanges();
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
return(Json(portCallDetails));
}
public IActionResult SetStatusDraft(int portCallId)
{
try
{
if (!_context.PortCall.Any(pc => pc.PortCallId == portCallId))
{
return(NotFound("Port call with id: " + portCallId + " could not be found in database."));
}
var portCall = _context.PortCall.Where(pc => pc.PortCallId == portCallId).Include(pc => pc.OrganizationPortCall).FirstOrDefault();
portCall.PortCallStatusId = Constants.Integers.DatabaseTableIds.PORT_CALL_STATUS_DRAFT;
foreach (OrganizationPortCall opc in portCall.OrganizationPortCall)
{
opc.Cleared = null;
opc.Remark = null;
}
_context.Update(portCall);
_context.SaveChanges();
return(Json(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult DeletePortCall([FromBody] PortCall portCall)
{
Console.WriteLine(portCall.PortCallId + "\n" + portCall.UserId.ToString());
try
{
var userId = User.FindFirst(cl => cl.Type == Constants.Strings.JwtClaimIdentifiers.Id).Value;
var user = _context.User.Where(usr => usr.UserId.ToString().Equals(userId)).Include(u => u.Role).FirstOrDefault();
var userIsAdmin = user.Role.Name.Equals(Constants.Strings.UserRoles.SuperAdmin);
var pcIsByUserOrg = (user.OrganizationId != null && _context.OrganizationPortCall.Any(opc => opc.PortCallId == portCall.PortCallId && opc.OrganizationId == user.OrganizationId));
if (userIsAdmin || (portCall.UserId != null && portCall.UserId.ToString().Equals(userId)) || pcIsByUserOrg)
{
PortCall removePortCall = _context.PortCall.Where(pc => pc.PortCallId == portCall.PortCallId)
.Include(pc => pc.PortCallDetails)
.Include(pc => pc.OrganizationPortCall)
.Include(pc => pc.PortCallHasPortCallPurpose)
.Include(pc => pc.CustomsCargo)
.Include(pc => pc.DpgOnBoard).FirstOrDefault();
_context.PortCallDetails.RemoveRange(removePortCall.PortCallDetails.AsEnumerable());
_context.OrganizationPortCall.RemoveRange(removePortCall.OrganizationPortCall.AsEnumerable());
_context.PortCallHasPortCallPurpose.RemoveRange(removePortCall.PortCallHasPortCallPurpose.AsEnumerable());
_context.CustomsCargo.RemoveRange(removePortCall.CustomsCargo.AsEnumerable());
_context.DpgOnBoard.RemoveRange(removePortCall.DpgOnBoard.AsEnumerable());
_context.PortCall.Remove(removePortCall);
_context.SaveChanges();
return(Json("Port call deleted."));
}
return(BadRequest("Delete request denied: you must either be an administrator or the be an user at the same organization as the user who created the port call in order to delete it."));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult Register([FromBody] PortCall portCall)
{
try
{
var clearanceAgencies = _context.Organization.Where(org => org.OrganizationTypeId == Constants.Integers.DatabaseTableIds.ORGANIZATION_TYPE_GOVERNMENT_AGENCY).ToList();
List <OrganizationPortCall> organizationPortCallList = new List <OrganizationPortCall>();
if (clearanceAgencies.Any())
{
foreach (Organization agency in clearanceAgencies)
{
OrganizationPortCall opc = new OrganizationPortCall
{
OrganizationId = agency.OrganizationId,
PortCallId = portCall.PortCallId
};
organizationPortCallList.Add(opc);
_context.OrganizationPortCall.Add(opc);
}
_context.SaveChanges();
return(Json(organizationPortCallList));
}
else
{
return(BadRequest("Warning: clearance list for port call is empty: no authorities could be found"));
}
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
protected IActionResult ExceptionHandler(Exception ex, string errorMethodName)
{
try
{
if (ex.GetType().Name.Equals("SocketException"))
{
SocketException pgex = (SocketException)ex;
if (pgex.SocketErrorCode.ToString().Equals("ConnectionRefused"))
{
return(StatusCode(500, "DBNoConnect"));
}
}
else if (ex.InnerException != null && ex.InnerException.GetType().Name.Equals("PostgresException"))
{
Npgsql.PostgresException pgex = (Npgsql.PostgresException)ex.InnerException;
if (pgex.SqlState.Equals("23502"))
{
return(StatusCode(400, pgex.ColumnName + "IsNull"));
}
}
}
catch (Exception baseEx)
{
_logger.LogError(baseEx.StackTrace);
}
_logger.LogError(ex.StackTrace);
return(StatusCode(500, errorMethodName));
}
public override Tuple <int, int> GetErrorPosition(Exception t, string sql, int offset)
{
if (!(t is Npgsql.PostgresException))
{
return(null);
}
Npgsql.PostgresException ex = (Npgsql.PostgresException)t;
int p0 = ex.Position;
if (p0 <= 0)
{
return(null);
}
p0--;
TokenizedSQL tsql = new TokenizedSQL(sql.Substring(offset));
bool wasColon = false;
int seq = 1;
Dictionary <string, int> pdict = new Dictionary <string, int>();
foreach (Token token in tsql.Tokens)
{
if (wasColon && token.Kind == TokenKind.Identifier)
{
// パラメータは内部的に数字に置換して実行し、
// 置換後のSQLでの文字位置が返るため
// そのままでは位置がずれてしまう
int idx;
if (!pdict.TryGetValue(token.Value, out idx))
{
idx = seq++;
pdict.Add(token.Value, idx);
}
p0 += (token.Value.Length - idx.ToString().Length);
}
wasColon = (token.ID == TokenID.Colon);
}
int n = sql.Length;
int p;
for (p = p0; p < n && !char.IsWhiteSpace(sql, p); p++)
{
;
}
if (p0 < offset)
{
return(null);
}
return(new Tuple <int, int>(p0 - offset, p - p0));
}
public IActionResult RemovePurposeForPortCall(int portCallId)
{
try
{
var removeList = _context.PortCallHasPortCallPurpose.Where(pcHasPurpose => pcHasPurpose.PortCallId == portCallId);
_context.PortCallHasPortCallPurpose.RemoveRange(removeList);
_context.SaveChanges();
return(Json("Purposes removed from port call with ID: " + portCallId));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult RegisterNewPortCall([FromBody] PortCall portCall)
{
try
{
var userId = User.FindFirst(cl => cl.Type == Constants.Strings.JwtClaimIdentifiers.Id).Value;
portCall.UserId = Guid.Parse(userId);
var statusDraftId = Constants.Integers.DatabaseTableIds.PORT_CALL_STATUS_DRAFT;
portCall.PortCallStatusId = statusDraftId;
return(RegisterPortCall(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult RegisterNewPortCall([FromBody] PortCall portCall)
{
try
{
var userId = this.GetUserId();
portCall.UserId = Guid.Parse(userId);
var statusDraftId = Constants.Integers.DatabaseTableIds.PORT_CALL_STATUS_DRAFT;
portCall.PortCallStatusId = statusDraftId;
return(RegisterPortCall(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult SetAsDeleted(int portCallId)
{
try
{
var portCall = _context.PortCall.Where(pc => pc.PortCallId == portCallId).Include(pc => pc.User).FirstOrDefault();
if (portCall == null)
{
return(BadRequest("Port Call not found."));
}
var userId = this.GetUserId();
var user = _context.User.Where(usr => usr.UserId.ToString().Equals(userId))
.Include(u => u.PortCall)
.Include(u => u.Organization.OrganizationPortCall)
.Include(u => u.Role)
.FirstOrDefault();
if (user == null)
{
return(BadRequest("User not found."));
}
bool portCallIsByUser = portCall.UserId.ToString().Equals(userId);
bool portCallIsByUserOrganization = portCall.User.OrganizationId == user.OrganizationId;
bool userIsAdmin = user.Role.Name.Equals(Constants.Strings.UserRoles.SuperAdmin) || user.Role.Name.Equals(Constants.Strings.UserRoles.Admin);
if (!(portCallIsByUser || portCallIsByUserOrganization || userIsAdmin))
{
return(BadRequest("Deletion request denied: port call does not belong to the user, nor any user from their organization."));
}
portCall.PortCallStatusId = Constants.Integers.DatabaseTableIds.PORT_CALL_STATUS_DELETED;
_context.Update(portCall);
_context.SaveChanges();
return(Ok(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
catch (Exception e)
{
return(BadRequest(e));
}
}
public IActionResult Update([FromBody] PortCallDetails portCallDetails)
{
if (!ValidateDetails(portCallDetails))
{
return(BadRequest("Invalid port call details from data."));
}
try
{
_context.PortCallDetails.Update(portCallDetails);
_context.SaveChanges();
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
return(Json(portCallDetails));
}
public IActionResult SetStatusCancelled(int portCallId)
{
try
{
if (!_context.PortCall.Any(pc => pc.PortCallId == portCallId))
{
return(NotFound("Port call with id: " + portCallId + " could not be found in database."));
}
PortCall portCall = _context.PortCall.Where(pc => pc.PortCallId == portCallId).FirstOrDefault();
portCall.PortCallStatusId = Constants.Integers.DatabaseTableIds.PORT_CALL_STATUS_CANCELLED;
_context.Update(portCall);
_context.SaveChanges();
return(Json(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult Update([FromBody] PortCall portCall)
{
if (portCall == null)
{
return(BadRequest("Empty body."));
}
try
{
if (!_context.PortCall.Any(pc => pc.PortCallId == portCall.PortCallId))
{
return(NotFound("Port call with id: " + portCall.PortCallId + " could not be found in database."));
}
_context.PortCall.Update(portCall);
return(Json(portCall));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
}
public IActionResult Save([FromBody] OrganizationPortCall organizationPortCall)
{
try
{
if (_context.OrganizationPortCall.Any(opc => opc.OrganizationPortCallId == organizationPortCall.OrganizationPortCallId))
{
_context.OrganizationPortCall.Update(organizationPortCall);
}
else
{
_context.OrganizationPortCall.Add(organizationPortCall);
}
_context.SaveChanges();
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
return(Json(organizationPortCall));
}
public IActionResult SetPurposeForPortCall([FromBody] List <PortCallHasPortCallPurpose> pcHasPurposeList)
{
try
{
var removeList = _context.PortCallHasPortCallPurpose.Where(dbObj => pcHasPurposeList.Exists(listObj => dbObj.PortCallId == listObj.PortCallId)).ToList();
_context.PortCallHasPortCallPurpose.RemoveRange(removeList);
_context.PortCallHasPortCallPurpose.AddRange(pcHasPurposeList);
_context.SaveChanges();
return(Json(pcHasPurposeList));
}
catch (DbUpdateException ex) when(ex.InnerException is Npgsql.PostgresException)
{
Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
return(BadRequest("PostgreSQL Error Code: " + innerEx.SqlState));
}
// foreach (PortCallHasPortCallPurpose pcHasPurpose in pcHasPurposeList)
// {
// try
// {
// if (_context.PortCall.Any(pc => pc.PortCallId == pcHasPurpose.PortCallId))
// {
// if (_context.PortCallPurpose.Any(purpose => purpose.PortCallPurposeId == pcHasPurpose.PortCallPurposeId))
// {
// _context.PortCallHasPortCallPurpose.Add(pcHasPurpose);
// _context.SaveChanges();
// }
// return BadRequest("Unable to find purpose with id: " + pcHasPurpose.PortCallPurposeId + ".");
// }
// return BadRequest("Unable to find port call with id: " + pcHasPurpose.PortCallId + ".");
// }
// catch (DbUpdateException ex) when (ex.InnerException is Npgsql.PostgresException)
// {
// Npgsql.PostgresException innerEx = (Npgsql.PostgresException)ex.InnerException;
// return BadRequest("PostgreSQL Error Code: " + innerEx.SqlState);
// }
// }
}
private static string GetExceptionMessage(Npgsql.PostgresException t)
{
StringBuilder buf = new StringBuilder();
buf.Append(t.Message);
if (!string.IsNullOrEmpty(t.Detail))
{
buf.AppendLine();
buf.Append(t.Detail);
}
if (!string.IsNullOrEmpty(t.Hint))
{
buf.AppendLine();
buf.Append(t.Hint);
}
if (!string.IsNullOrEmpty(t.Where))
{
buf.AppendLine();
buf.AppendLine();
buf.Append(t.Where);
}
return(buf.ToString());
}
