using System; using System.Text; using System.Threading.Tasks; using System.Collections.Generic; using System.Net; using System.Net.Http; using Amazon.Lambda.Core; using Amazon.Lambda.APIGatewayEvents; using Newtonsoft.Json; using MySql.Data.MySqlClient; using Newtonsoft.Json.Linq; using System.Linq; using System.Data; using PMF.Services; using Model.ResponseModel; using classes; using Services; using Model; using AwsDotnetCsharp.Model; // Assembly attribute to enable the Lambda function's JSON input to be converted into a .NET class. [assembly: LambdaSerializer( typeof(Amazon.Lambda.Serialization.Json.JsonSerializer))] namespace PMF { public class Handler { private readonly MySqlConnection _con; MySqlDataReader _reader; private readonly DbUser dbUser; Logger _logger; bool errorInducedEmpty = false; public Handler() { dbUser = SecretsManager.GetDBSecret(); string connectionString = $@"server={dbUser.host};userid={dbUser.username};password={dbUser.password};database={dbUser.dbInstanceIdentifier};convert zero datetime=True"; _con = new MySqlConnection(connectionString); _reader = null; _logger = new Logger(dbUser); } /******** LAMBDA FUNCTIONS ***************/ //DB: Internal #region INTERNAL API'S public APIGatewayProxyResponse GetLoggedInUserDetails(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; Dictionary dict = new Dictionary(); string statement = "DB::GetLoggedInUserDetails"; string strCognitoUuid = ""; string userId = ""; try { if (!Helper.IsRequestEmpty(request)) { if (request.QueryStringParameters.ContainsKey("userId")) { string validationMessage = string.Empty; var validCognitoUuidRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validCognitoUuidRequest) { userId = Helper.CleanInput(request.QueryStringParameters["userId"]); statement = "SELECT * FROM `user` WHERE UserID = '" + userId + "'"; } if (!validCognitoUuidRequest && validationMessage.Length > 0) return HttpService.CreateResponse(400, payload: validationMessage); } else if (request.QueryStringParameters.ContainsKey("strCognitoUuid")) { string validationMessageId = string.Empty; var validIdRequest = Helper.ValidateRequestParameters(ref validationMessageId, request.QueryStringParameters, "strCognitoUuid"); if (validIdRequest) { strCognitoUuid = Helper.CleanInput(request.QueryStringParameters["strCognitoUuid"]); statement = "SELECT * FROM `user` WHERE strCognitoUuid = '" + strCognitoUuid + "'"; } if (!validIdRequest && validationMessageId.Length > 0) return HttpService.CreateResponse(400, payload: validationMessageId); } else { return HttpService.CreateResponse(400, payload: "Please supply a CognitoUuid or UserId"); } if (_con.State == ConnectionState.Closed) _con.Open(); var cmd = new MySqlCommand(statement, _con); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { dict.Add(_reader.GetName(0), _reader.GetValue(0).ToString()); //userId dict.Add(_reader.GetName(2), _reader.GetValue(2).ToString()); //strName dict.Add(_reader.GetName(3), _reader.GetValue(3).ToString()); //strSurname dict.Add(_reader.GetName(5), _reader.GetValue(5).ToString()); //strEmail dict.Add(_reader.GetName(14), _reader.GetValue(14).ToString()); //strIdNumber } _reader.Close(); } else { response = HttpService.CreateResponse(200, payload: "User not found", forcedResult: false); _logger.WriteLog(strCognitoUuid, statement, response.Body.ToString(), "DB"); return response; } response = HttpService.CreateResponse(200, payload: dict); _logger.WriteLog(strCognitoUuid, statement, response.Body.ToString(), "DB"); return response; } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); return response; } } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed - Please add parameter 'cognitoUuid' to your request"); _logger.WriteLog(strCognitoUuid, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public APIGatewayProxyResponse UpdateRegistration(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; Dictionary dict = new Dictionary(); string statement = ""; string cognitoUuid = ""; try { if (_con.State == ConnectionState.Closed) _con.Open(); string validationMessage = string.Empty; if (request != null && request.Body != null) { var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "strEmail", "strCognitoUuid"); if (validRequest) { var email = Helper.CleanInput(data["strEmail"].ToString()); if (!Helper.IsAValidEmail(email)) return HttpService.CreateResponse(400, payload: "The email supplied is not valid"); cognitoUuid = Helper.CleanInput(data["strCognitoUuid"].ToString()); statement = $"UPDATE user SET strCognitoUuid = '{cognitoUuid}' WHERE strEmail = '{email}'"; using var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); cmd.CommandText = $"SELECT * FROM user WHERE strCognitoUuid = '{cognitoUuid}'"; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { dict.Add(_reader.GetName(0), _reader.GetValue(0).ToString()); //UserId dict.Add(_reader.GetName(1), _reader.GetValue(1).ToString());//cognitoID dict.Add(_reader.GetName(2), _reader.GetValue(2).ToString()); //strName dict.Add(_reader.GetName(3), _reader.GetValue(3).ToString()); //strSurname dict.Add(_reader.GetName(5), _reader.GetValue(5).ToString()); //strEmail } _reader.Close(); response = HttpService.CreateResponse(200, payload: dict); //_logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } else { response = HttpService.CreateResponse(500, payload: "An error was encountered while updating user: Check if the email is registered"); } } else { _logger.LogMessage(context, $"Processing request failed - {validationMessage}"); response = HttpService.CreateResponse(400, payload: $" {validationMessage}"); } } else { response = HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { response = HttpService.CreateResponse(500, payload: ex.Message); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } public APIGatewayProxyResponse RegisterUser(APIGatewayProxyRequest request, ILambdaContext context) { //Partial details entry. APIGatewayProxyResponse response; Dictionary dict = new Dictionary(); string statement = "DB::RegisterUser"; string cognitoUuid = ""; try { string validationMessage = string.Empty; if (request != null && request.Body != null) { var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "strCognitoUuid", "strEmail", "strName", "strSurname", "strIdType", "strMobileNumber"); if (validRequest) { cognitoUuid = Helper.CleanInput(data["strCognitoUuid"].ToString()); var email = data["strEmail"].ToString(); if (String.Equals(data["strIdType"].ToString(), "2")) { string idErrors = string.Empty; var validSaId = Helper.ValidateRequestParameters(ref idErrors, data, "strIdNumber"); if (!validSaId) { response = HttpService.CreateResponse(400, payload: idErrors); } } //Only save if this is available if (Helper.IsAValidEmail(email)) { if (_con.State == ConnectionState.Closed) _con.Open(); using var cmd = new MySqlCommand(); cmd.Connection = _con; statement = $"SELECT * FROM user WHERE strEmail = '{email}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (!_reader.HasRows) { _reader.Close(); //check if user has already been created string sqlCheckForUser = $"SELECT * FROM user WHERE strCognitoUuid = '{cognitoUuid}'"; cmd.CommandText = sqlCheckForUser; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (!_reader.HasRows) { _reader.Close(); statement = $"INSERT INTO user (strCognitoUuid, strName, strSurname, strEmail, strIdType, strIdNumber, strMobileNumber, blnActive, strFirstUser, dtFirstEdit) VALUES ('{Helper.CleanInput(data["strCognitoUuid"].ToString())}', '{Helper.CleanInput(data["strName"].ToString())}', '{Helper.CleanInput(data["strSurname"].ToString())}','{Helper.CleanInput(data["strEmail"].ToString())}','{Helper.CleanInput(data["strIdType"].ToString())}','{Helper.CleanInput(data["strIdNumber"].ToString())}','{Helper.CleanInput(data["strMobileNumber"].ToString())}',1,'{Helper.CleanInput(data["strName"].ToString())}', NOW() )"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); string sqlSelectNewUser = $"SELECT * FROM user WHERE strCognitoUuid = '{cognitoUuid}'"; cmd.CommandText = sqlSelectNewUser; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { //Watch out when the db changes dict.Add(_reader.GetName(2), _reader.GetValue(2).ToString()); //strName dict.Add(_reader.GetName(3), _reader.GetValue(3).ToString()); //strSurname dict.Add(_reader.GetName(5), _reader.GetValue(5).ToString()); //strEmail } _reader.Close(); } else { response = HttpService.CreateResponse(500, payload: "Error creating user"); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } } else { response = HttpService.CreateResponse(200, payload: "User CognitoUuid Already exists"); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } } else { response = HttpService.CreateResponse(200, payload: "User Email Already exists"); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } } else { response = HttpService.CreateResponse(400, payload: "Invalid Email"); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, $"Processing request failed {validationMessage}"); response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); return response; } } else { response = HttpService.CreateResponse(400, payload: "No parameters provided"); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } if (dict.Count > 0) { response = HttpService.CreateResponse(201, payload: dict); } else { response = HttpService.CreateResponse(500, payload: "Internal error: User was not created"); } _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); } catch (Exception ex) { response = HttpService.CreateResponse(500, payload: ex.Message); _logger.WriteLog(cognitoUuid, statement, response.Body.ToString(), "DB"); return response; } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } public APIGatewayProxyResponse GetUserDetails(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; Dictionary dict = new Dictionary(); Dictionary dictIds = new Dictionary(); string statement = "DB::GetUserDetails"; string strCognitoUuid = ""; string userId = ""; try { if (!Helper.IsRequestEmpty(request)) { if (request.QueryStringParameters.ContainsKey("userId")) { string validationMessage = string.Empty; var validCognitoUuidRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validCognitoUuidRequest) { userId = Helper.CleanInput(request.QueryStringParameters["userId"]); statement = "SELECT * FROM `user` WHERE UserID = '" + userId + "'"; } if (!validCognitoUuidRequest && validationMessage.Length > 0) return HttpService.CreateResponse(400, payload: validationMessage); } else if (request.QueryStringParameters.ContainsKey("strCognitoUuid")) { string validationMessageId = string.Empty; var validIdRequest = Helper.ValidateRequestParameters(ref validationMessageId, request.QueryStringParameters, "strCognitoUuid"); if (validIdRequest) { strCognitoUuid = Helper.CleanInput(request.QueryStringParameters["strCognitoUuid"]); statement = "SELECT * FROM `user` WHERE strCognitoUuid = '" + strCognitoUuid + "'"; } if (!validIdRequest && validationMessageId.Length > 0) return HttpService.CreateResponse(400, payload: validationMessageId); } else if (request.QueryStringParameters.ContainsKey("strEmail")) { string strEmail = Helper.CleanInput(request.QueryStringParameters["strEmail"]); if (Helper.IsAValidEmail(strEmail)) { statement = "SELECT * FROM `user` WHERE strEmail = '" + strEmail + "'"; } else { return HttpService.CreateResponse(400, payload: "Email is invalid"); } } else { return HttpService.CreateResponse(400, payload: "Please supply a CognitoUuid or UserId or Email Address"); } if (_con.State == ConnectionState.Closed) _con.Open(); var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { userId = _reader.GetValue(0).ToString(); dict.Add(_reader.GetName(0), _reader.GetValue(0).ToString()); //userID dict.Add(_reader.GetName(2), _reader.GetValue(2).ToString()); //strName dict.Add(_reader.GetName(3), _reader.GetValue(3).ToString()); //strSurname dict.Add(_reader.GetName(4), _reader.GetValue(4).ToString()); //strIdType dict.Add(_reader.GetName(5), _reader.GetValue(5).ToString()); //strEmail dict.Add(_reader.GetName(6), _reader.GetValue(6).ToString()); //strRole dict.Add(_reader.GetName(7), _reader.GetValue(7).ToString()); //strMobileNumber dict.Add(_reader.GetName(8), _reader.GetValue(8).ToString()); //strBusinessAccount dict.Add(_reader.GetName(9), _reader.GetValue(9).ToString()); //strTermsAndConditions dict.Add(_reader.GetName(10), _reader.GetValue(10).ToString()); //txtBillToAddress dict.Add(_reader.GetName(11), _reader.GetValue(11).ToString()); //txtNotes dict.Add("dtDateCaptured", _reader.GetValue(13).ToString()); //datecaptured dict.Add(_reader.GetName(14), _reader.GetValue(14).ToString()); //strIdNumber dict.Add(_reader.GetName(17), _reader.GetValue(17).ToString()); //strLastEdit } _reader.Close(); string sqlSelectIds = $"SELECT * FROM `userIdNumber` WHERE refUserID = {userId}"; cmd.CommandText = sqlSelectIds; cmd.ExecuteNonQuery(); bool maxIdCount = false; var idReader = cmd.ExecuteReader(); List ids = new List { }; if (idReader.HasRows) { while (idReader.Read()) { ids.Add(new { strName = idReader.GetValue(2).ToString(), strSurname = idReader.GetValue(3).ToString(), dtDateCaptured = idReader.GetValue(9).ToString(), strIDNumber = idReader.GetValue(5).ToString(), strIdType = idReader.GetValue(4).ToString() }); } idReader.Close(); } if (ids.Count >= 5) maxIdCount = true; dict.Add("maxNumberIds", maxIdCount.ToString()); var responseBody = new { data = dict, additionalIds = ids }; response = HttpService.CreateResponse(200, payload: responseBody); //_logger.WriteLog(strCognitoUuid, statement, response.Body.ToString(), "DB"); excess logs unnessesary return response; } else { response = HttpService.CreateResponse(200, payload: "User does not exist", forcedResult: false); _logger.WriteLog(strCognitoUuid, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); return response; } } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); _logger.WriteLog(strCognitoUuid, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, exception: ex); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public APIGatewayProxyResponse PostUserDetails(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; Dictionary dict = new Dictionary(); string statement = ""; Object resultResponse = null; string insertUpdateStatementId = ""; string userId = ""; try { string validationMessage = string.Empty; if (request != null && request.Body != null) { if (_con.State == ConnectionState.Closed) _con.Open(); var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "userId", "strName", "strSurname", "strIdType", "strMobileNumber", "txtBillAddress", "additionalIds"); if (validRequest) { //CHECK IF sa id IS VALID ANY OTHER id does not have validation if (String.Equals(data["strIdType"].ToString(), "2")) { string idErrors = string.Empty; var validSaId = Helper.ValidateRequestParameters(ref idErrors, data, "strIdNumber"); if (!validSaId) { response = HttpService.CreateResponse(400, payload: idErrors); } } userId = Helper.CleanInput(data["userId"].ToString()); if (!String.IsNullOrEmpty(userId)) { statement = $"SELECT * FROM `user` WHERE UserID = '{userId}'"; var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); //user exists if (_reader.HasRows) { _reader.Close(); //DO update user string updateStatementUser = $"Update user SET strName = '{Helper.CleanInput(data["strName"].ToString())}', strSurname = '{Helper.CleanInput(data["strSurname"].ToString())}', strIdType = '{Helper.CleanInput(data["strIdType"].ToString())}', strIdNumber = '{Helper.CleanInput(data["strIdNumber"].ToString())}', strMobileNumber = '{Helper.CleanInput(data["strMobileNumber"].ToString())}' , strLastUser = '{Helper.CleanInput(data["strName"].ToString())}', txtBillToAddress = '{Helper.CleanInput(data["txtBillAddress"].ToString())}', dtLastEdit = NOW() WHERE UserID = '{userId}'"; cmd.CommandText = updateStatementUser; cmd.ExecuteNonQuery(); //Insert update additional IDs List newIds = new List { }; foreach (var idRecord in data["additionalIds"]) { string uniqueId = Helper.CleanInput(idRecord["strIDNumber"].ToString()); newIds.Add(uniqueId); string sqlId = $"SELECT * FROM userIdNumber WHERE strIDNumber = '{uniqueId}' and refUserID = '{userId}'"; cmd.CommandText = sqlId; cmd.ExecuteNonQuery(); MySqlDataReader readInsertUpdateIds = cmd.ExecuteReader(); //if its in data not in db add if (!readInsertUpdateIds.HasRows) { insertUpdateStatementId = $"INSERT INTO userIdNumber (refUserID, strName, strSurname, strIdType, strIDNumber,strFirstUser, strLastUser, dtLastEdit, dtFirstEdit) VALUES ('{userId}', '{Helper.CleanInput(idRecord["strName"].ToString())}', '{idRecord["strSurname"]}' ,'{Helper.CleanInput(idRecord["strIdType"].ToString())}', '{Helper.CleanInput(idRecord["strIDNumber"].ToString())}','{Helper.CleanInput(data["strName"].ToString())}', '{Helper.CleanInput(data["strName"].ToString())}',NOW(), NOW())"; } //if its data and db up else { insertUpdateStatementId = $"UPDATE userIdNumber SET strName = '{Helper.CleanInput(idRecord["strName"].ToString())}', strSurname = '{Helper.CleanInput(idRecord["strSurname"].ToString())}', strIdType = '{Helper.CleanInput(idRecord["strIdType"].ToString())}',strLastUser = '{Helper.CleanInput(data["strName"].ToString())}', dtLastEdit = NOW() WHERE strIDNumber = '{uniqueId}'"; } readInsertUpdateIds.Close(); cmd.CommandText = insertUpdateStatementId; cmd.ExecuteNonQuery(); } //Delete Ids that are in the db but not in the body string sqlDeleteIDs = $"SELECT * FROM userIdNumber WHERE refUserID = '{userId}'"; cmd.CommandText = sqlDeleteIDs; cmd.ExecuteNonQuery(); MySqlDataReader deleteIds = cmd.ExecuteReader(); List unMatchedIDs = new List { }; while (deleteIds.Read()) { if (!newIds.Contains(deleteIds.GetValue(5).ToString())) { unMatchedIDs.Add(deleteIds.GetValue(5).ToString()); //Watch out when ERD Changes } } deleteIds.Close(); foreach (var id in unMatchedIDs) { string DeleteIds = $"DELETE FROM userIdNumber WHERE strIDNumber = '{id}' and refUserID = '{userId}'"; cmd.CommandText = DeleteIds; cmd.ExecuteNonQuery(); } //populate Ids dictionary from updated details string getAdditionalIds = $"SELECT * FROM userIdNumber WHERE RefUserID = '{userId}'"; cmd.CommandText = getAdditionalIds; cmd.ExecuteNonQuery(); MySqlDataReader readIds = cmd.ExecuteReader(); List ids = new List { }; while (readIds.Read()) { ids.Add(new { strName = readIds.GetValue(2).ToString(), strSurname = readIds.GetValue(3).ToString(), dtDateCaptured = readIds.GetValue(9).ToString(), strIDNumber = readIds.GetValue(5).ToString() }); } readIds.Close(); //populate data dictionary from updated DB cmd.CommandText = statement; cmd.ExecuteNonQuery(); MySqlDataReader readUpdate = cmd.ExecuteReader(); while (readUpdate.Read()) { //More details?? dict.Add(readUpdate.GetName(2), readUpdate.GetValue(2).ToString()); //strName dict.Add(readUpdate.GetName(3), readUpdate.GetValue(3).ToString()); //strSurname dict.Add(readUpdate.GetName(4), readUpdate.GetValue(4).ToString()); //strIdType dict.Add(readUpdate.GetName(5), readUpdate.GetValue(5).ToString()); //strEmail dict.Add(readUpdate.GetName(6), readUpdate.GetValue(6).ToString()); //Mobile dict.Add("dtDateCaptured", readUpdate.GetValue(13).ToString()); //strIdNumber } resultResponse = new { data = dict, additionalIds = ids }; } else { response = HttpService.CreateResponse(404, payload: "User does not exist"); return response; } } else { response = HttpService.CreateResponse(400, payload: "UserId was not supplied"); _logger.WriteLog(userId, statement, "CognitoUuid was not supplied", "DB"); return response; } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); _logger.WriteLog(userId, statement, $"Please add parameter(s) : {validationMessage}", "DB"); return response; } } else { response = HttpService.CreateResponse(400, payload: "Please provide parameters"); _logger.WriteLog(userId, statement, "Please provide parameters", "DB"); return response; } } catch (Exception ex) { response = HttpService.CreateResponse(500, payload: ex.Message); _logger.WriteLog(userId, statement, ex.Message, "DB"); return response; } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } response = HttpService.CreateResponse(200, resultResponse); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } public APIGatewayProxyResponse GetSettings(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "DB::GetSettings"; string userId = ""; Dictionary dict = new Dictionary(); try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); string result = string.Empty; userId = Helper.CleanInput(request.QueryStringParameters["userId"]); statement = "SELECT strKey, strValue FROM setting WHERE Active = 1"; var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); List settings = new List { }; if (_reader.HasRows) { while (_reader.Read()) { settings.Add(new { strKey = _reader.GetValue(0).ToString(), strValue = _reader.GetValue(1).ToString(), }); } _reader.Close(); response = HttpService.CreateResponse(200, payload: settings); } else { response = HttpService.CreateResponse(200, payload: "No settings found"); } } else { response = HttpService.CreateResponse(400, payload: validationMessage); } } else { response = HttpService.CreateResponse(400, payload: "No parameters provided"); } _logger.WriteLog(userId, statement, response.Body, "DB"); return response; } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public APIGatewayProxyResponse GetGraphSettings(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response = null; string statement = "DB::GetGraphSettings"; string userId = ""; List graphSettings = new List { }; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); userId = Helper.CleanInput(request.QueryStringParameters["userId"]); statement = "SELECT * FROM graph"; var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { graphSettings.Add(new GraphSettings { GraphID = Convert.ToInt32(_reader["GraphID"]), GraphType = _reader["strGraphType"].ToString(), GraphTitle = _reader["strGraphTitle"].ToString(), GraphQuery = _reader["strGraphData"].ToString(), GraphDescription = _reader["strDescription"].ToString(), GraphX = _reader["strXColumn"].ToString(), GraphY = _reader["strYColumn"].ToString(), Active = Convert.ToInt32(_reader["blnActive"]) }); } _reader.Close(); response = HttpService.CreateResponse(200, payload: graphSettings); } else { response = HttpService.CreateResponse(200, payload: "No graphs found"); } } else { response = HttpService.CreateResponse(400, payload: validationMessage); } } else { response = HttpService.CreateResponse(400, payload: "No Parameters found"); } return response; } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public APIGatewayProxyResponse PostGraphSettings(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "DB::PostGraphSettings"; string userId = ""; List updatedSettings = new List { }; try { string validationMessage = string.Empty; if (request != null && request.Body != null) { var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); int temp = 0; //zero if its a new entry var cmd = new MySqlCommand(); cmd.Connection = _con; foreach (var record in data["graphSettings"]) { if (record["GraphID"] != null) { if (!string.IsNullOrEmpty(record["GraphID"].ToString())) { temp = Convert.ToInt32(Helper.CleanInput(record["GraphID"].ToString())); if (record["Delete"] != null) { statement = $"DELETE FROM graph WHERE GraphID = {temp}"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); return HttpService.CreateResponse(200, payload: "Graph settings successfully updated"); } } } updatedSettings.Add( new GraphSettings { GraphID = temp, GraphTitle = Helper.CleanInput(record["GraphTitle"].ToString()), GraphType = Helper.CleanInput(record["GraphType"].ToString()), GraphQuery = Helper.CleanInput(record["GraphQuery"].ToString()), GraphDescription = Helper.CleanInput(record["GraphDescription"].ToString()), GraphX = Helper.CleanInput(record["GraphX"].ToString()), GraphY = Helper.CleanInput(record["GraphY"].ToString()), Active = Convert.ToInt32(record["Active"]) } ); } foreach (var setting in updatedSettings) { if (setting.GraphID == 0) { if (!String.Equals(setting.GraphTitle, "NedBankTransactions")) { try { string tempStatement = $"{setting.GraphQuery}"; tempStatement = tempStatement.Replace("{startDate}", "2012-02-02"); //demo date tempStatement = tempStatement.Replace("{endDate}", "2012-02-02"); tempStatement = tempStatement.Replace("''", "'"); cmd.CommandText = tempStatement; cmd.ExecuteNonQuery(); } catch (Exception) { throw new Exception("The sql statement entered is invalid"); } } statement = $"INSERT INTO graph (strGraphType, strGraphTitle, strGraphData, strDescription,strXColumn, strYColumn, blnActive) VALUES('{setting.GraphType}','{setting.GraphTitle}','{setting.GraphQuery}','{setting.GraphDescription}', '{setting.GraphX}', '{setting.GraphY}',{setting.Active})"; } else { if (!String.Equals(setting.GraphTitle, "NedBankTransactions")) { try { string tempStatement = $"{setting.GraphQuery}"; tempStatement = tempStatement.Replace("{startDate}", "2012-02-02"); //demo date tempStatement = tempStatement.Replace("{endDate}", "2012-02-02"); tempStatement = tempStatement.Replace("''", "'"); cmd.CommandText = tempStatement; cmd.ExecuteNonQuery(); } catch (Exception) { throw new Exception("The sql statement entered is invalid"); } } statement = $"UPDATE graph set strGraphType = '{setting.GraphType}', strGraphTitle = '{setting.GraphTitle}', strGraphData = '{setting.GraphQuery}', strDescription = '{setting.GraphDescription}', strXColumn = '{setting.GraphX}',strYColumn = '{setting.GraphY}',blnActive = {setting.Active} WHERE GraphID = {setting.GraphID}"; } cmd.CommandText = statement; cmd.ExecuteNonQuery(); } foreach (var result in updatedSettings) { if (result.GraphID == 0) { cmd.CommandText = "SELECT MAX(GraphID) FROM graph"; cmd.ExecuteNonQuery(); int insertedId = 0; ; _reader = cmd.ExecuteReader(); while (_reader.Read()) { insertedId = _reader.GetInt32(0); } _reader.Close(); result.GraphID = insertedId; } } response = HttpService.CreateResponse(200, payload: updatedSettings.FirstOrDefault()); } else { response = HttpService.CreateResponse(400, payload: validationMessage); } } else { response = HttpService.CreateResponse(400, payload: "Body can not be empty"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } public APIGatewayProxyResponse PostUpdateSettings(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "DB::PostSettings"; string userId = ""; bool paramProvided = false; try { string validationMessage = string.Empty; if (request != null && request.Body != null) { var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); string resendCode = ""; string forgotPassword = ""; string signUp = ""; string convenience = ""; string result = string.Empty; userId = data["userId"].ToString(); foreach (var key in data.AsJEnumerable()) { string value = key.Path; switch (value) { case "CustomMessage_SignUp": signUp = (data["CustomMessage_SignUp"].ToString()); //Not cleaned to allow html from FE result += $" {UpdateSettingsKey(signUp, "CustomMessage_SignUp")}"; paramProvided = true; break; case "CustomMessage_ResendCode": resendCode = (data["CustomMessage_ResendCode"].ToString()); result += $" {UpdateSettingsKey(resendCode, "CustomMessage_ResendCode")}"; paramProvided = true; break; case "CustomMessage_ForgotPassword": forgotPassword = (data["CustomMessage_ForgotPassword"].ToString()); result += $" {UpdateSettingsKey(forgotPassword, "CustomMessage_ForgotPassword")}"; paramProvided = true; break; case "convenience": convenience = Helper.CleanInput(data["convenience"].ToString()); try { double val = Convert.ToDouble(convenience); } catch (System.Exception) { throw new Exception("convience amount must be a numerical value"); } result += $" {UpdateSettingsKey(convenience, "convenience")}"; paramProvided = true; break; } } if (!string.IsNullOrWhiteSpace(result)) return HttpService.CreateResponse(400, payload: result); if (!paramProvided) { response = HttpService.CreateResponse(400, payload: "Body must contain a valid Email type"); } else { response = HttpService.CreateResponse(200, payload: "Settings successfully updated"); } } else { response = HttpService.CreateResponse(400, payload: validationMessage); } } else { response = HttpService.CreateResponse(400, payload: "Body can not be empty"); } _logger.WriteLog(userId, statement, response.Body, "DB"); } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } private string UpdateSettingsKey(string source, string key) { //check if email temp contains code placeholder; string result = string.Empty; string statement = ""; try { var cmd = new MySqlCommand(); cmd.Connection = _con; if (key.Contains("CustomMessage")) { if (source.Contains(Constants.CognitoCodePlaceHolder, StringComparison.CurrentCultureIgnoreCase)) { source = Uri.UnescapeDataString(source); statement = $"Update setting set strValue = '{source}' WHERE strKey = '{key}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); } else { result = $"{key} does not contain a placeholder for the code parameter"; } } else { statement = $"Update setting set strValue = '{source}' WHERE strKey = '{key}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw ex; } return result; } public APIGatewayProxyResponse GetFinanceReportData(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "DB::GetFinanceReportData"; string issuingAuthority = string.Empty; string startSettlementDate = string.Empty; string endSettlementDate = string.Empty; Dictionary dict = new Dictionary(); try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "issuingAuthority", "startSettlementDate", "endSettlementDate"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); string result = string.Empty; issuingAuthority = Helper.CleanInput(request.QueryStringParameters["issuingAuthority"]); startSettlementDate = Helper.CleanInput(request.QueryStringParameters["startSettlementDate"]); endSettlementDate = Helper.CleanInput(request.QueryStringParameters["endSettlementDate"]); statement = @"SELECT t.dtIVeriSettlement as SettlementTime, i.strIssuingAuthority as IssuingAuthority, i.strInfringementSource as Channel, t.strBankRef as PayId, i.strInfringementNumber as NoticeNumber, t.strAmount as Amount FROM infringement i left outer join transaction t on i.refTrasactionID = t.TransactionID where t.dtIVeriSettlement between @startSettlementDate and @endSettlementDate and t.intPaid = 'SETTLED' and 1 = case when @issuingAuthority = 'ALL' then 1 when i.strIssuingAuthority = @issuingAuthority then 1 else 0 end; "; var cmd = new MySqlCommand(); cmd.Parameters.AddWithValue("@issuingAuthority", issuingAuthority); cmd.Parameters.AddWithValue("@startSettlementDate", startSettlementDate); cmd.Parameters.AddWithValue("@endSettlementDate", endSettlementDate); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); List settledPayments = new List { }; if (_reader.HasRows) { while (_reader.Read()) { settledPayments.Add(new FinanceReportItemModel { SettlementDateTime = _reader.GetValue(0).ToString(), IssuingAuthority = _reader.GetValue(1).ToString(), Channel = _reader.GetValue(2).ToString(), PayId = _reader.GetValue(3).ToString(), NoticeNumber = _reader.GetValue(4).ToString(), Amount = _reader.GetValue(5).ToString(), }); } _reader.Close(); response = HttpService.CreateResponse(200, payload: settledPayments); } else { response = HttpService.CreateResponse(200, payload: "No settings found"); } } else { response = HttpService.CreateResponse(400, payload: validationMessage); } } else { response = HttpService.CreateResponse(400, payload: "No parameters provided"); } //_logger.WriteLog(userId, statement, response.Body, "DB"); return response; } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); //_logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } //Triggered when cognito runs. public CognitoObjectData GenerateCustomEmail(CognitoObjectData input, ILambdaContext context) { // Documentation: https://docs.aws.amazon.com/cognito/latest/developerguide/user-pool-lambda-custom-message.html //check on trigger source from cognito.....All models set up in ResponseModel and cognito details. try { if (input.triggerSource == "CustomMessage_SignUp") //SEARCH REMOVE for the AWS Credits Fields { input.response.emailSubject = "Pay My Fines: Sign Up"; input.response.emailMessage = ReplaceEmailPlaceHolders("CustomMessage_SignUp", input.request.codeParameter, input.request.userAttributes.email); } if (input.triggerSource == "CustomMessage_ResendCode") //SEARCH REMOVE for the AWS Credits Fields { input.response.emailSubject = "Pay My Fines: Resent code"; input.response.emailMessage = ReplaceEmailPlaceHolders("CustomMessage_ResendCode", input.request.codeParameter, string.Empty); } if (input.triggerSource == "CustomMessage_ForgotPassword") //SEARCH REMOVE for the AWS Credits Fields { input.response.emailSubject = "Pay My Fines: Forgot Password"; input.response.emailMessage = ReplaceEmailPlaceHolders("CustomMessage_ForgotPassword", input.request.codeParameter, string.Empty); } return input; } catch (Exception ex) { _logger.LogMessage(context, $"Processing request failed - Cognito reset Failed: {ex.Message.ToString()}"); _logger.WriteLog("Generate email fires with exception", ex.Message.ToString(), "exception", "DB"); return input; } //On fail should bounnce back to cognito } private string ReplaceEmailPlaceHolders(string emailType, string codeParameter, string email) { string emailBody = ""; MySqlDataReader _reader = null; try { if (_con.State == ConnectionState.Closed) _con.Open(); string statement = $"SELECT * FROM setting WHERE strKey = '{emailType}'"; var cmd = new MySqlCommand(statement, _con); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { emailBody = _reader["strValue"].ToString(); } emailBody = emailBody.Replace("{code}", codeParameter); if (email != string.Empty) { emailBody = emailBody.Replace("{email}", email); } } } catch (Exception ex) { throw ex; } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return emailBody; } public APIGatewayProxyResponse GetAPILogs(APIGatewayProxyRequest request, ILambdaContext context) { //For testing only APIGatewayProxyResponse response; string statement = "DB::GetAPILogs"; string userId = ""; try { if (!Helper.IsRequestEmpty(request)) { string validationMessage = string.Empty; var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); userId = Helper.CleanInput(request.QueryStringParameters["userId"]); statement = "SELECT * FROM `log` WHERE strCognitoUuid = '" + userId + "'"; var cmd = new MySqlCommand(statement, _con); _reader = cmd.ExecuteReader(); List logs = new List { }; if (_reader.HasRows) { while (_reader.Read()) { logs.Add(new { userId = _reader.GetValue(0).ToString(), txtQuery = _reader.GetValue(2).ToString(), tctResponse = _reader.GetValue(3).ToString(), strTye = _reader.GetValue(4).ToString() }); } _reader.Close(); response = HttpService.CreateResponse(200, payload: logs, serializeToJson: true); //_logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } else { response = HttpService.CreateResponse(400, payload: "User not found", serializeToJson: true); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, $"Processing request failed - Please add parameter(s): {validationMessage}"); response = HttpService.CreateResponse(400, payload: $"Please add parameter(s) {validationMessage}"); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); return response; } } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public APIGatewayProxyResponse GetTransactions(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; List transactions = new List(); string statement = "DB::GetTransactions"; string userId = ""; string idNumber = ""; string strIdType = ""; bool idSupplied = false; bool idOrUuidSupplied = false; try { if (!Helper.IsRequestEmpty(request)) { if (_con.State == ConnectionState.Closed) _con.Open(); string validationMessage = string.Empty; if (request.QueryStringParameters.ContainsKey("userId")) { var validCognitoUuidRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validCognitoUuidRequest) { userId = Helper.CleanInput(request.QueryStringParameters["userId"]); idOrUuidSupplied = true; } if (!validCognitoUuidRequest && validationMessage.Length > 0) return HttpService.CreateResponse(400, payload: validationMessage); } if (request.QueryStringParameters.ContainsKey("strIdNumber")) { if (request.QueryStringParameters.ContainsKey("strIdType")) { strIdType = Helper.CleanInput(request.QueryStringParameters["strIdType"]); if (string.Equals(strIdType, "2")) { string validationError = string.Empty; var valid = Helper.ValidateRequestParameters(ref validationError, request.QueryStringParameters, "strIdNumber"); if (!valid) { return HttpService.CreateResponse(400, payload: validationError); } } idNumber = Helper.CleanInput(request.QueryStringParameters["strIdNumber"]); idOrUuidSupplied = true; } else { return HttpService.CreateResponse(400, payload: "Missing strIdType"); } } if (idOrUuidSupplied) { if (!string.IsNullOrEmpty(idNumber)) { statement = $@"SELECT user.strName, user.strSurname, transaction.intPaid, transaction.strIVeriNumber, transaction.dtPaid, transaction.dtPayment, transaction.strStatus, transaction.strAmount, transaction.strRecieptNumber, transaction.strVeriResponse, infringement.dtInfringementDate, infringement.strInfringementNumber, infringement.strIssuingAuthority, infringement.strVechicleRegistration, infringement.intDemeritPoints, charge.strChargeDescription, transaction.dtIVeriSettlement, transaction.strBankRef FROM user INNER JOIN transaction ON transaction.refClientID = user.UserID INNER JOIN infringement ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN charge ON charge.refInfringementID = infringement.InfringementID WHERE user.strIDNumber = '{idNumber}' GROUP BY strIVeriNumber"; idSupplied = true; } else { statement = $@"SELECT user.strName, user.strSurname, transaction.intPaid, transaction.strIVeriNumber, transaction.dtPaid, transaction.dtPayment, transaction.strStatus, transaction.strAmount, transaction.strRecieptNumber, transaction.strVeriResponse, infringement.strIdNumber, infringement.dtInfringementDate, infringement.strInfringementNumber, infringement.strIssuingAuthority, infringement.strVechicleRegistration, infringement.intDemeritPoints, charge.strChargeDescription, transaction.dtIVeriSettlement, transaction.strBankRef FROM user INNER JOIN transaction ON transaction.refClientID = user.UserID INNER JOIN infringement ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN charge ON charge.refInfringementID = infringement.InfringementID WHERE user.UserID = {userId} GROUP BY strIVeriNumber"; } var cmd = new MySqlCommand(statement, _con); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { if (idSupplied) { transactions.Add(new IdTransactionRecord { ClientId = idNumber, TransactionId = _reader.GetValue(17).ToString(), strInfringementNumber = _reader.GetValue(11).ToString(), intPaid = _reader.GetValue(2).ToString(), strIVeriNumber = _reader.GetValue(3).ToString(), dtPaid = _reader.GetValue(4).ToString(), dtPayment = _reader.GetValue(5).ToString(), strIDNumber = idNumber, strStatus = _reader.GetValue(6).ToString(), strReceiptNumber = _reader.GetValue(8).ToString(), strVeriResponse = _reader.GetValue(9).ToString(), dtInfringementDate = _reader.GetValue(10).ToString(), strAmount = _reader.GetValue(7).ToString(), strIssuingAuthority = _reader.GetValue(12).ToString(), strVechicleRegistration = _reader.GetValue(13).ToString(), intDemeritPoints = Convert.ToInt32(_reader["intDemeritPoints"]), strChargeDescription = _reader.GetValue(15).ToString(), dtIVeriSettlement = _reader.GetValue(16).ToString(), }); } else { transactions.Add(new IdTransactionRecord { ClientId = _reader.GetValue(0).ToString(), TransactionId = _reader.GetValue(18).ToString(), strInfringementNumber = _reader.GetValue(12).ToString(), intPaid = _reader.GetValue(2).ToString(), strIVeriNumber = _reader.GetValue(3).ToString(), dtPaid = _reader.GetValue(4).ToString(), dtPayment = _reader.GetValue(5).ToString(), strStatus = _reader.GetValue(6).ToString(), strReceiptNumber = _reader.GetValue(8).ToString(), strVeriResponse = _reader.GetValue(9).ToString(), strIDNumber = _reader.GetValue(10).ToString(), dtInfringementDate = _reader.GetValue(11).ToString(), strIssuingAuthority = _reader.GetValue(13).ToString(), strAmount = _reader.GetValue(7).ToString(), strVechicleRegistration = _reader.GetValue(14).ToString(), intDemeritPoints = Convert.ToInt32(_reader.GetValue(15)), strChargeDescription = _reader.GetValue(16).ToString(), dtIVeriSettlement = _reader.GetValue(17).ToString(), }); } } _reader.Close(); } if (transactions.Count > 0) { response = HttpService.CreateResponse(200, payload: transactions, serializeToJson: true); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } else { response = HttpService.CreateResponse(200, payload: "No Transactions Available"); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, "Processing request failed - Missing Parameters"); response = HttpService.CreateResponse(400, payload: "Please add parameter(s): user or IdNumber"); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); return response; } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); return response; } } catch (Exception ex) { _logger.LogMessage(context, "Processing request failed: " + ex.Message); _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } #endregion public async Task IsRecaptchaValid(APIGatewayProxyRequest request, ILambdaContext context) { try { var data = JsonConvert.DeserializeObject(request.Body); var token = data["recaptcha"].ToString(); var client = new HttpClient(); string privateKey = Constants.reCAPTCHA_KEY; var values = new Dictionary { {"secret", privateKey}, {"response", token}, }; var content = new FormUrlEncodedContent(values); var captchaRequest = await client.PostAsync(Constants.reCAPTCHA_URL, content); var captchaResponse = await captchaRequest.Content.ReadAsStringAsync(); var result = JObject.Parse(captchaResponse); bool recaptchaResult = (bool)result.SelectToken("success"); if (recaptchaResult) { return HttpService.CreateResponse(200, payload: "true"); } return HttpService.CreateResponse(200, payload: "false"); } catch (Exception ex) { return HttpService.CreateResponse(500, payload: ex.Message); } } #region IFORCE API'S //TMT API public async Task GetQuickFines(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "IForce: GetQuickFines"; string userId = ""; APIGatewayProxyResponse response; int attribute = 0; //notice number try { if (!Helper.IsRequestEmpty(request)) { string validationMessage = string.Empty; var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "noticeNumber", "Id"); if (validRequest) { var NoticeNumber = request.QueryStringParameters["noticeNumber"]; userId = request.QueryStringParameters["Id"]; object iForce = Constants.NO_RESPONSE; object eNatis = Constants.NO_RESPONSE; bool validNoticeNumberFormat = false; //iForce if (NoticeNumber.Contains('/')) //iForce format { try { var iForcehandler = new HttpClientHandler(); iForcehandler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var iForceClient = new HttpClient(iForcehandler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; var iForceResponse = await iForceClient.GetAsync($"IForce/queryfines?Attribute={attribute}&FineQueryAttributeValue={NoticeNumber}"); var iForcebody = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { //_logger.WriteLog(userId, statement, iForcebody, "TMT"); iForce = JsonConvert.DeserializeObject(iForcebody); } else { _logger.WriteLog(userId, statement, iForcebody, "TMT"); iForce = $"No fines found for NoticeNumber: {NoticeNumber}"; } validNoticeNumberFormat = true; } catch { iForce = Constants.NO_RESPONSE; } } else if (Helper.IsDigitsOnly(NoticeNumber)) { //eNatis var eNatishandler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; try { var eNatisClient = new HttpClient(eNatishandler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; var eNatisResponse = await eNatisClient.GetAsync($"infringement/queryinfringement?InfringementNoticeNumber={NoticeNumber}"); var body = await eNatisResponse.Content.ReadAsStringAsync(); if (eNatisResponse.StatusCode == HttpStatusCode.OK) { eNatis = JsonConvert.DeserializeObject(body); } else { _logger.WriteLog(userId, statement, body, "TMT"); eNatis = $"No fines found for NoticeNumber: {NoticeNumber}"; } validNoticeNumberFormat = true; } catch { eNatis = Constants.NO_RESPONSE; } } if (!validNoticeNumberFormat) { return HttpService.CreateResponse(200, payload: $"Format: {NoticeNumber} is incorrect"); } var responseBody = new { iForce = iForce, eNatis = eNatis }; return HttpService.CreateResponse(200, payload: responseBody); } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); return response; } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); return response; } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public async Task GetOutstandingFines(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "GetOutStandingFines"; string userId = ""; string phaseDetail = ""; try { if (!Helper.IsRequestEmpty(request)) { string validationMessage = string.Empty; var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "noticeNumber", "Id"); if (validRequest) { string attribute = "0"; //Notice Number var attributeValue = request.QueryStringParameters["noticeNumber"]; userId = request.QueryStringParameters["Id"]; var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var iForceResponse = await client.GetAsync($"IForce/queryfines?Attribute={attribute}&FineQueryAttributeValue={attributeValue}"); var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { _logger.WriteLog(userId, statement, body, "TMT"); var payload = JsonConvert.DeserializeObject(body); payload.fines = payload.fines.Where(f => f.totalAmountDue != 0).ToList(); var transformedFines = new List(); foreach (var fine in payload.fines) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(fine.noticeNumber, _con); switch (fine.phase) { case 1: phaseDetail = "Notice"; break; case 2: phaseDetail = "Summons"; break; case 3: phaseDetail = "Court"; break; case 4: phaseDetail = "Warrant"; break; default: phaseDetail = "Unknown"; break; } transformedFines.Add(new FineStatus { issuingAuthorityCode = fine.issuingAuthorityCode, districtName = fine.districtName, noticeNumber = fine.noticeNumber, offenceDate = fine.offenceDate, location = fine.location, idNumber = fine.idNumber, vehicleRegistrationNumber = fine.vehicleRegistrationNumber, phase = phaseDetail, paymentAllowed = fine.paymentAllowed, paymentNotAllowedCode = fine.paymentNotAllowedCode, dueDate = fine.dueDate, totalAmountDue = fine.totalAmountDue, contemptOfCourtAmount = fine.contemptOfCourtAmount, summonsNumber = fine.summonsNumber, caseNumber = fine.caseNumber, courtCode = fine.courtCode, courtName = fine.courtName, courtDate = fine.courtDate, warrantOfArrestNumber = fine.warrantOfArrestNumber, warrantOfArrestPending = fine.warrantOfArrestPending, evidence = fine.evidence, customerPaymentAllowed = paidLocalStatus }); } var result = new IForceQueryFinesResponseStatus { transactionId = payload.transactionId, resultCode = payload.resultCode, resultDescription = payload.resultDescription, fines = transformedFines.ToList() }; return HttpService.CreateResponse(200, payload: result); } else { var error = JsonConvert.DeserializeObject(body); _logger.WriteLog(userId, statement, error.message, "TMT"); return HttpService.CreateResponse(error.statusCode, payload: $"No fines found for {attributeValue}"); } } catch (Exception) { return HttpService.CreateResponse(504, payload: Constants.NO_RESPONSE); } } else { return HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); return HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public async Task GetCharges(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "IForce::GetCharges"; string userId = ""; //Not used alone try { if (!Helper.IsRequestEmpty(request)) { string validationMessage = string.Empty; var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "noticeNumber", "Id"); if (validRequest) { var noticeNumber = request.QueryStringParameters["noticeNumber"]; userId = request.QueryStringParameters["Id"]; var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var iForceResponse = await client.GetAsync($"IForce/querycharge?NoticeNumber={noticeNumber}"); var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { // _logger.WriteLog(userId, statement, body, "TMT"); var payload = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(200, payload: payload); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(200, payload: "No charges found"); } } catch { return HttpService.CreateResponse(504, payload: Constants.NO_RESPONSE); } } else { _logger.WriteLog(userId, statement, $"{validationMessage}", "TMT"); return HttpService.CreateResponse(400, payload: $"{validationMessage} "); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); return HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public async Task GetAllFines(APIGatewayProxyRequest request, ILambdaContext context) { Dictionary dictEnatis = new Dictionary(); Dictionary dictIForce = new Dictionary(); APIGatewayProxyResponse response; List iForceFines = new List(); List eNatisFines = new List(); List allCognitoIds = new List { }; string statement = "IForce and eNatis::GetAllFines"; string phaseDetail = ""; string userId = ""; int resultStatusCode = 0; string idDocumentNumber = ""; string attributeValue = ""; string filter = ""; string idDocumentType = ""; try { if (!Helper.IsRequestEmpty(request)) { if (request != null) { if (request.QueryStringParameters.ContainsKey("strIssuingAuthority")) { filter = request.QueryStringParameters["strIssuingAuthority"]; } string validationMessage = string.Empty; string attribute = "2"; //ID attribute bool searchByUserID = false; if (request.QueryStringParameters.ContainsKey("strIdNumber") && !string.IsNullOrEmpty(request.QueryStringParameters["strIdNumber"])) { if (request.QueryStringParameters.ContainsKey("strIdType")) { idDocumentType = request.QueryStringParameters["strIdType"].ToString(); } else { return HttpService.CreateResponse(400, payload: "idtype is null or empty"); } if (String.Equals(idDocumentType, "2") || String.Equals(idDocumentType, "02")) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "strIdNumber"); if (!validRequest) return HttpService.CreateResponse(400, payload: validationMessage); idDocumentNumber = request.QueryStringParameters["strIdNumber"]; //fixed id number attributeValue = idDocumentNumber; } else//None validated ID { idDocumentNumber = request.QueryStringParameters["strIdNumber"]; //fixed id number attributeValue = idDocumentNumber; } } else if (request.QueryStringParameters.ContainsKey("userId") && !string.IsNullOrEmpty(request.QueryStringParameters["userId"])) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (!validRequest) return HttpService.CreateResponse(400, payload: validationMessage); userId = Helper.CleanInput(request.QueryStringParameters["userId"]); searchByUserID = true; } else { //atleast one of Id or userId should be supplied. return HttpService.CreateResponse(400, payload: "Please provide a userId or idNumber"); } if (_con.State == ConnectionState.Closed) _con.Open(); if (searchByUserID) //User was loggedin when fine was searched { statement = $"SELECT * FROM user WHERE UserID = '{userId}'"; GetAllAssociateIds(allCognitoIds, statement); //Populate allCognitoIds with the associated cognitouuid ids if (allCognitoIds.Count < 1) { return HttpService.CreateResponse(200, payload: "userId does not exist"); } ////IFORCE var filterediForce = await GetIForceIdsData(attribute, allCognitoIds, filter); _reader.Close(); Object IForceResult = filterediForce; List transformedFines = new List(); foreach (var fineResponse in filterediForce) { var infringement = fineResponse.fines.Where(f => f.totalAmountDue != 0).ToList(); foreach (var fine in infringement) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(fine.noticeNumber, _con); switch (fine.phase) { case 1: phaseDetail = "Notice"; break; case 2: phaseDetail = "Summons"; break; case 3: phaseDetail = "Court"; break; case 4: phaseDetail = "Warrant"; break; default: phaseDetail = "Unknown"; break; } if (fine.paymentAllowed == false) paidLocalStatus = false; if (!string.IsNullOrWhiteSpace(filter)) { if (fine.districtName == filter) { transformedFines.Add(new FineStatus { issuingAuthorityCode = fine.issuingAuthorityCode, districtName = fine.districtName, noticeNumber = fine.noticeNumber, offenceDate = fine.offenceDate, location = fine.location, idNumber = fine.idNumber, vehicleRegistrationNumber = fine.vehicleRegistrationNumber, phase = phaseDetail, paymentAllowed = fine.paymentAllowed, paymentNotAllowedCode = fine.paymentNotAllowedCode, dueDate = fine.dueDate, totalAmountDue = fine.totalAmountDue, contemptOfCourtAmount = fine.contemptOfCourtAmount, summonsNumber = fine.summonsNumber, caseNumber = fine.caseNumber, courtCode = fine.courtCode, courtName = fine.courtName, courtDate = fine.courtDate, warrantOfArrestNumber = fine.warrantOfArrestNumber, warrantOfArrestPending = fine.warrantOfArrestPending, evidence = fine.evidence, customerPaymentAllowed = paidLocalStatus }); } } else { transformedFines.Add(new FineStatus { issuingAuthorityCode = fine.issuingAuthorityCode, districtName = fine.districtName, noticeNumber = fine.noticeNumber, offenceDate = fine.offenceDate, location = fine.location, idNumber = fine.idNumber, vehicleRegistrationNumber = fine.vehicleRegistrationNumber, phase = phaseDetail, paymentAllowed = fine.paymentAllowed, paymentNotAllowedCode = fine.paymentNotAllowedCode, dueDate = fine.dueDate, totalAmountDue = fine.totalAmountDue, contemptOfCourtAmount = fine.contemptOfCourtAmount, summonsNumber = fine.summonsNumber, caseNumber = fine.caseNumber, courtCode = fine.courtCode, courtName = fine.courtName, courtDate = fine.courtDate, warrantOfArrestNumber = fine.warrantOfArrestNumber, warrantOfArrestPending = fine.warrantOfArrestPending, evidence = fine.evidence, customerPaymentAllowed = paidLocalStatus }); } } } if (filterediForce.Count > 0) { iForceFines.Add(new IForceQueryFinesResponseStatus { transactionId = filterediForce[0].transactionId, resultCode = filterediForce[0].resultCode, resultDescription = filterediForce[0].resultDescription, fines = transformedFines.ToList() }); } IForceResult = iForceFines; if (filterediForce.Count == 0 && errorInducedEmpty) { IForceResult = Constants.NO_RESPONSE; } else if (filterediForce.Count == 0 && !errorInducedEmpty) { IForceResult = "No fines found"; } //ENATIS var filteredEnatis = await GeteNatisIdsData(allCognitoIds, filter); var flatArr = filteredEnatis.SelectMany(arr => arr).Distinct().Where(f => f.payment.amount != 0).ToList(); foreach (var fine in flatArr) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(fine.infringementNoticeNumber, _con); eNatisFines.Add(new ENatisQueryInfrigerResponseStatus { infringementNoticeNumber = fine.infringementNoticeNumber, IDNumber = fine.idNumber, date = fine.date, time = fine.time, location = fine.location, vehicle = fine.vehicle, charge = fine.charge, feesList = fine.feesList, payment = fine.payment, customerPaymentAllowed = paidLocalStatus }); } object EnatisResult = eNatisFines; if (filteredEnatis.Count == 0 && errorInducedEmpty) { EnatisResult = Constants.NO_RESPONSE; } else if (filteredEnatis.Count == 0 && !errorInducedEmpty) { EnatisResult = "No fines found"; } var resultBody = new { eNatis = EnatisResult, iForce = IForceResult }; if (filteredEnatis.Count == 0 && filterediForce.Count == 0 && !errorInducedEmpty) { return HttpService.CreateResponse(200, payload: "No fines found"); } return HttpService.CreateResponse(200, payload: resultBody); } else //SEARCH DONE BY IDNUMBER { List transformedFines = new List(); //IFORCE Object iForceresult; try { var handlerIForce = new HttpClientHandler(); handlerIForce.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var iForceClient = new HttpClient(handlerIForce) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; var iForceResponse = await iForceClient.GetAsync($"IForce/queryfines?Attribute={attribute}&FineQueryAttributeValue={attributeValue}"); var iForcebody = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { var body = JsonConvert.DeserializeObject(iForcebody); body.fines = body.fines.Where(f => f.totalAmountDue != 0).ToList(); var filteredBoy = new IForceQueryFinesResponse(); //apply filter if (!string.IsNullOrEmpty(filter)) { var filteredfines = body.fines.Where(x => x.districtName == filter).ToList(); filteredBoy = new IForceQueryFinesResponse { transactionId = body.transactionId, resultCode = body.resultCode, resultDescription = body.resultDescription, fines = filteredfines }; } else { filteredBoy = new IForceQueryFinesResponse { transactionId = body.transactionId, resultCode = body.resultCode, resultDescription = body.resultDescription, fines = body.fines }; } foreach (var fine in filteredBoy.fines) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(fine.noticeNumber, _con); switch (fine.phase) { case 1: phaseDetail = "Notice"; break; case 2: phaseDetail = "Summons"; break; case 3: phaseDetail = "Court"; break; case 4: phaseDetail = "Warrant"; break; default: phaseDetail = "Unknown"; break; } transformedFines.Add(new FineStatus { issuingAuthorityCode = fine.issuingAuthorityCode, districtName = fine.districtName, noticeNumber = fine.noticeNumber, offenceDate = fine.offenceDate, location = fine.location, idNumber = fine.idNumber, vehicleRegistrationNumber = fine.vehicleRegistrationNumber, phase = phaseDetail, paymentAllowed = fine.paymentAllowed, paymentNotAllowedCode = fine.paymentNotAllowedCode, dueDate = fine.dueDate, totalAmountDue = fine.totalAmountDue, contemptOfCourtAmount = fine.contemptOfCourtAmount, summonsNumber = fine.summonsNumber, caseNumber = fine.caseNumber, courtCode = fine.courtCode, courtName = fine.courtName, courtDate = fine.courtDate, warrantOfArrestNumber = fine.warrantOfArrestNumber, warrantOfArrestPending = fine.warrantOfArrestPending, evidence = fine.evidence, customerPaymentAllowed = paidLocalStatus }); } iForceFines.Add(new IForceQueryFinesResponseStatus { transactionId = body.transactionId, resultCode = body.resultCode, resultDescription = body.resultDescription, fines = transformedFines.ToList() }); iForceresult = iForceFines; } else //"No data" returned for all errors { var errorResponse = JsonConvert.DeserializeObject(iForcebody); dictIForce.Add("resultCode", errorResponse.statusCode.ToString()); dictIForce.Add("resultDescription", errorResponse.message); _logger.WriteLog(userId, statement, errorResponse.message, "iForce"); iForceresult = $"No fines found for {attributeValue}"; //return HttpService.CreateResponse(errorResponse.resultCode, result: dict); --> uncomment if we want a one fail all fail scenario } } catch { iForceresult = Constants.NO_RESPONSE; } //eNatis Object ENatisresult; try { var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var enatisClient = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; var eNatisResponse = await enatisClient.GetAsync($"infringement/queryinfringer?IdDocumentType={idDocumentType}&IdDocumentNumber={idDocumentNumber}"); var eNatisbody = await eNatisResponse.Content.ReadAsStringAsync(); List filteredeNatis = new List { }; if (eNatisResponse.StatusCode == HttpStatusCode.OK) { eNatisbody = await eNatisResponse.Content.ReadAsStringAsync(); var body = JsonConvert.DeserializeObject>(eNatisbody); body = body.Where(f => f.payment.amount != 0).ToList(); foreach (var fine in body) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(fine.infringementNoticeNumber, _con); eNatisFines.Add(new ENatisQueryInfrigerResponseStatus { infringementNoticeNumber = fine.infringementNoticeNumber, IDNumber = idDocumentNumber, date = fine.date, time = fine.time, location = fine.location, vehicle = fine.vehicle, charge = fine.charge, feesList = fine.feesList, payment = fine.payment, customerPaymentAllowed = paidLocalStatus }); } if (!string.IsNullOrEmpty(filter)) { filteredeNatis = eNatisFines.Where(x => x.location.issAuthority.description == filter).ToList(); } else { filteredeNatis = eNatisFines; } ENatisresult = filteredeNatis; } else { var errorResponse = JsonConvert.DeserializeObject(eNatisbody); dictEnatis.Add("resultCode", errorResponse.statusCode.ToString()); dictEnatis.Add("resultDescription", errorResponse.message); _logger.WriteLog(userId, statement, errorResponse.message, "eNatis"); ENatisresult = $"No fines found for {attributeValue}"; //Error so give back empty body? } } catch { ENatisresult = Constants.NO_RESPONSE; } var responseBody = new { eNatis = ENatisresult, iForce = iForceresult }; _logger.WriteLog(userId, statement, responseBody.ToString(), "eNatis, TMT"); //add to db options in the log table return HttpService.CreateResponse(200, payload: responseBody); } } else { _logger.WriteLog(userId, statement, "Missing argument: IDNumber or CognitoUuid", "eNatis, TMT"); return HttpService.CreateResponse(401, payload: "Missing argument: IDNumber or CognitoUuid"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); return HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { if (resultStatusCode == 0) { response = HttpService.CreateResponse(500, exception: ex); //error picked up before calling iforce } else { response = HttpService.CreateResponse(resultStatusCode, exception: ex); } _logger.WriteLog(userId, statement, response.Body.ToString(), "TMT, eNatis"); return response; } finally { if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public async Task GetEvidence(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "iForce::GetEvidence"; string userId = ""; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "evidenceToken", "Id", "includeImage"); if (validRequest) { var evidenceToken = request.QueryStringParameters["evidenceToken"]; var includeImage = request.QueryStringParameters["includeImage"]; userId = request.QueryStringParameters["Id"]; var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var iForceResponse = await client.GetAsync($"IForce/queryevidence?EvidenceToken={evidenceToken}&IncludeImage={includeImage}"); statement = $"EvidenceToken={evidenceToken}, IncludeImage={includeImage}"; var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { //_logger.WriteLog(userId, statement, body, "TMT"); var payload = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(200, payload: payload); } else { var error = JsonConvert.DeserializeObject(body); _logger.WriteLog(userId, statement, body, "TMT"); return HttpService.CreateResponse(200, payload: "Evidence unavailable"); } } catch (Exception) { return HttpService.CreateResponse(504, payload: Constants.NO_RESPONSE); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); return HttpService.CreateResponse(400, payload: "No parameters provided"); } _logger.WriteLog(userId, statement, response.Body, "TMT"); } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } public async Task GetStreamEvidence(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response; string statement = "iForce::GetStreamEvidence"; string userId = ""; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "sessionToken", "evidenceToken", "userId"); if (validRequest) { var sessionToken = request.QueryStringParameters["sessionToken"]; var evidenceToken = request.QueryStringParameters["evidenceToken"]; userId = request.QueryStringParameters["userId"]; var handler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var iForceResponse = await client.GetAsync($"IForce/queryevidencestream?SessionToken={sessionToken}&EvidenceToken={evidenceToken}"); statement = $"IForce/queryevidencestream?SessionToken={sessionToken}&EvidenceToken={evidenceToken}"; var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { var payload = JsonConvert.DeserializeObject(body); response = HttpService.CreateResponse(200, payload: payload); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(200, payload: "Stream Unavailable"); } } catch (Exception) { return HttpService.CreateResponse(504, payload: Constants.NO_RESPONSE); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); return HttpService.CreateResponse(400, payload: "No parameters provided"); } _logger.WriteLog(userId, statement, response.Body, "TMT"); } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, payload: ex); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } public async Task PostPayment(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "iForce::PostPayment"; string userId = ""; APIGatewayProxyResponse response; //this call is not being used only from cron job try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "strCognitoUuid"); if (validRequest) { userId = request.QueryStringParameters["userId"]; if (request != null && request.Body != null) { var handler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var content = new StringContent(request.Body, Encoding.UTF8, "application/json"); var iForceResponse = await client.PostAsync("IForce/registerpayment", content); var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { response = HttpService.CreateResponse(200, payload: body, serializeToJson: false); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(error.statusCode, payload: error.message); } } catch (Exception) { return HttpService.CreateResponse(504, payload: Constants.NO_RESPONSE); } } else { response = HttpService.CreateResponse(400, payload: "Request body is invalid"); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "TMT"); return HttpService.CreateResponse(500, payload: ex.Message); } _logger.WriteLog(userId, statement, response.Body, "TMT"); return response; } #endregion #region eNATIS API'S //eNaTIS API public async Task QueryExternalInfringer(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "eNatis::QueryExternalInfringer"; string userId = ""; APIGatewayProxyResponse response; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "strIdType", "strIdNumber", "userId"); if (validRequest) { var idDocumentType = request.QueryStringParameters["strIdType"]; var idDocumentNumber = request.QueryStringParameters["strIdNumber"]; userId = request.QueryStringParameters["userId"]; var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL) }; var eNatisResponse = await client.GetAsync($"infringement/queryinfringer?IdDocumentType={idDocumentType}&IdDocumentNumber={idDocumentNumber}"); var body = await eNatisResponse.Content.ReadAsStringAsync(); if (eNatisResponse.StatusCode == HttpStatusCode.OK) { _logger.WriteLog(userId, statement, body, "eNATiS"); var responseBody = JsonConvert.DeserializeObject(body); response = HttpService.CreateResponse(200, payload: responseBody); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(error.statusCode, payload: error.message); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "eNATiS"); return HttpService.CreateResponse(500, payload: ex.Message); } _logger.WriteLog(userId, statement, response.Body, "eNATiS"); return response; } public async Task QueryExternalInfringement(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "eNatis::QueryExternalInfringement"; string userId = ""; APIGatewayProxyResponse response; List responseList = new List(); try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "noticeNumber", "Id"); if (validRequest) { var noticeNumber = request.QueryStringParameters["noticeNumber"]; userId = request.QueryStringParameters["Id"]; var handler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL) }; var eNatisResponse = await client.GetAsync($"infringement/queryinfringement?InfringementNoticeNumber={noticeNumber}"); var body = await eNatisResponse.Content.ReadAsStringAsync(); if (_con.State == ConnectionState.Closed) _con.Open(); if (eNatisResponse.StatusCode == HttpStatusCode.OK) { var responseBody = JsonConvert.DeserializeObject>(body); responseBody = responseBody.Where(f => f.payment.amount != 0).ToList(); foreach (var item in responseBody) { var paidLocalStatus = PaymentChecker.CheckLocalPaymentStatus(item.infringementNoticeNumber, _con); responseList.Add(new ENatisQueryInfrigerResponseStatus { infringementNoticeNumber = item.infringementNoticeNumber, date = item.date, time = item.time, location = item.location, vehicle = item.vehicle, charge = item.charge, feesList = item.feesList, payment = item.payment, customerPaymentAllowed = paidLocalStatus }); } response = HttpService.CreateResponse(200, payload: responseList); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(error.statusCode, payload: error.message); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); } _logger.WriteLog(userId, statement, response.Body, "eNATiS"); return response; } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "eNATiS"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } } public async Task PayExternalInfringement(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "eNatis::PayExternalInfringement"; string userId = ""; APIGatewayProxyResponse response; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validRequest) { userId = request.QueryStringParameters["userId"]; if (request != null && request.Body != null) { var handler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL) }; var content = new StringContent(request.Body, Encoding.UTF8, "application/json"); var eNatisResponse = await client.PostAsync("infringement/payinfringement", content); var body = await eNatisResponse.Content.ReadAsStringAsync(); if (eNatisResponse.StatusCode == HttpStatusCode.OK) { response = HttpService.CreateResponse(200, payload: body, serializeToJson: false); } else { var error = JsonConvert.DeserializeObject(body); return HttpService.CreateResponse(error.statusCode, payload: error.message); } } else { response = HttpService.CreateResponse(400, payload: "Request body is invalid"); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { _logger.LogMessage(context, $"Processing request failed invalid request"); response = HttpService.CreateResponse(400, payload: "No parameters provided"); } } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "eNATiS"); return HttpService.CreateResponse(500, payload: ex.Message); } _logger.WriteLog(userId, statement, response.Body, "eNATiS"); return response; } #endregion #region IVERI-LITE API'S //iVeri-Lite API public async Task PostIveriLiteTransaction(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "iVeri-Lite::PostIVeriLiteTransaction"; string userId = ""; string transactionId = ""; string intPaid = "NO"; string dtPaid = "NULL"; string strVeriResponse = ""; string infringementNumber = ""; string refInfringementID = ""; APIGatewayProxyResponse response; Object resultResponse = null; Dictionary dict = new Dictionary(); try { string validationMessage = string.Empty; if (request != null && request.Body != null) { context.Logger.Log(request.Body); if (_con.State == ConnectionState.Closed) _con.Open(); var data = JsonConvert.DeserializeObject(request.Body); var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "striVeriNumber", "userId", "strStatus"); //--check josh if (validRequest) { userId = Helper.CleanInput(data["userId"].ToString()); using var cmd = new MySqlCommand(); cmd.Connection = _con; intPaid = (data["intPaid"] != null ? data["intPaid"].ToString() : intPaid); dtPaid = (data["dtPaid"] != null ? data["dtPaid"].ToString() : dtPaid); statement = $"INSERT INTO transaction (striVeriNumber, refClientID, intPaid, dtPaid, dtPayment, refPaymentProviderID, strStatus, strAmount, strVeriResponse, dtFirstEdit) VALUES ('{Helper.CleanInput(data["striVeriNumber"].ToString())}', '{Helper.CleanInput(data["userId"].ToString())}', '{Helper.CleanInput(intPaid)}', 'NO', NOW(), 1, '{Helper.CleanInput(data["strStatus"].ToString())}','{Helper.CleanInput(data["strAmount"].ToString())}','{Helper.CleanInput(strVeriResponse)}', NOW())"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT MAX(TransactionID) FROM transaction"; transactionId = Convert.ToInt32(cmd.ExecuteScalar()).ToString(); foreach (var infringement in data["infringements"]) { infringementNumber = Helper.CleanInput(infringement["strInfringementNumber"].ToString()); statement = $@"INSERT INTO infringement (refTrasactionID, strInfringementSource, strIdNumber, dtInfringementDate, refClientID, strInfringementNumber, intPaid, dtPaid, strIssuingAuthority, strVechicleRegistration, strStatus, strConvenienceAmount, strAmount, intDemeritPoints) VALUES ('{transactionId}', '{Helper.CleanInput(infringement["strInfringementSource"].ToString())}', '{Helper.CleanInput(infringement["strIdNumber"].ToString())}', '{Helper.CleanInput(infringement["dtInfringementDate"].ToString())}', {userId}, '{Helper.CleanInput(infringement["strInfringementNumber"].ToString())}', '{Helper.CleanInput(intPaid)}','{dtPaid}', '{Helper.CleanInput(infringement["strIssuingAuthority"].ToString())}','{Helper.CleanInput(infringement["strVechicleRegistration"].ToString())}','{Helper.CleanInput(data["strStatus"].ToString())}','{Helper.CleanInput(infringement["strConvenienceAmount"].ToString())}','{Helper.CleanInput(infringement["strAmount"].ToString())}', '{Helper.CleanInput(infringement["intDemeritPoints"].ToString())}')"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT MAX(InfringementID) FROM infringement"; refInfringementID = Convert.ToInt32(cmd.ExecuteScalar()).ToString(); if (string.Equals(Helper.CleanInput(infringement["strInfringementSource"].ToString().ToLower()), "iforce")) //finalize names saved in the db { var charges = await GetIForceInfringementCharge(infringementNumber); foreach (var charge in charges) { statement = $"INSERT INTO charge (refInfringementID, intDemeritPoints, strChargeDescription) VALUES ('{refInfringementID}', 0, '{charge.Description}')"; //made zero since we do not have these for iforce. cmd.CommandText = statement; cmd.ExecuteNonQuery(); } } else //enatis { var charges = await GetENatisInfringementCharge(infringementNumber); foreach (var charge in charges) { statement = $"INSERT INTO charge (refInfringementID, intDemeritPoints, strChargeDescription) VALUES ('{refInfringementID}', '{charge.demeritPoints}', '{charge.description}')"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); } } } string sqlSelectTransaction = $"SELECT * FROM transaction WHERE TransactionID = '{transactionId}'"; cmd.CommandText = sqlSelectTransaction; cmd.ExecuteNonQuery(); MySqlDataReader readUpdate = cmd.ExecuteReader(); while (readUpdate.Read()) { dict.Add(readUpdate.GetName(0), readUpdate.GetValue(0).ToString()); //TransactionID dict.Add(readUpdate.GetName(1), readUpdate.GetValue(1).ToString()); //striVeriNumber dict.Add(readUpdate.GetName(3), readUpdate.GetValue(3).ToString()); //intPaid dict.Add(readUpdate.GetName(4), readUpdate.GetValue(4).ToString()); //dtPaid dict.Add(readUpdate.GetName(5), readUpdate.GetValue(5).ToString()); //dtPayment --> Only add it to the response when it is available dict.Add(readUpdate.GetName(7), readUpdate.GetValue(7).ToString()); //strStatus dict.Add(readUpdate.GetName(8), readUpdate.GetValue(8).ToString()); //strAmount } resultResponse = new { data = dict }; _logger.WriteLog("Transaction", "Processed transction", $"TransactionID: {dict["TransactionID"].ToString()} , Status: {dict["strStatus"].ToString()}", "TMT"); } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); _logger.WriteLog(userId, statement, $"Please add parameter(s) : {validationMessage}", "DB"); return response; } } else { response = HttpService.CreateResponse(400, payload: "Please provide parameters"); _logger.WriteLog(userId, statement, "Please provide parameters", "DB"); return response; } response = HttpService.CreateResponse(200, resultResponse); _logger.WriteLog(userId, statement, response.Body.ToString(), "DB"); } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } private async Task> GetIForceInfringementCharge(string noticeNumber) { var handler = new HttpClientHandler(); handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; try { var iForceResponse = await client.GetAsync($"IForce/querycharge?NoticeNumber={noticeNumber}"); var body = await iForceResponse.Content.ReadAsStringAsync(); if (iForceResponse.StatusCode == HttpStatusCode.OK) { var payload = JsonConvert.DeserializeObject>(body); return payload; } else { var error = JsonConvert.DeserializeObject(body); throw new Exception($"Error: {error.message}"); } } catch (Exception ex) { throw (ex); } } private async Task> GetENatisInfringementCharge(string noticeNumber) { var handler = new HttpClientHandler { ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation }; var client = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL) }; try { var eNatisResponse = await client.GetAsync($"infringement/queryinfringement?InfringementNoticeNumber={noticeNumber}"); var body = await eNatisResponse.Content.ReadAsStringAsync(); if (eNatisResponse.StatusCode == HttpStatusCode.OK) { var responseBody = JsonConvert.DeserializeObject>(body); foreach (var fine in responseBody) { return fine.charge; } return new List(); } else { var error = JsonConvert.DeserializeObject(body); throw new Exception($"Error: {error.message}"); } } catch (Exception ex) { throw ex; } } #endregion /******** Graphs API ***************/ public APIGatewayProxyResponse GetGraphs(APIGatewayProxyRequest request, ILambdaContext context) { APIGatewayProxyResponse response = null; string statement = "GET Graphs"; string userId = ""; var result = new List { }; try { string validationMessage = string.Empty; if (!Helper.IsRequestEmpty(request)) { var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId"); if (validRequest) { if (_con.State == ConnectionState.Closed) _con.Open(); var graphs = GraphService.LoadGraphSettings(_con); if (request.QueryStringParameters.ContainsKey("endDate") && request.QueryStringParameters.ContainsKey("startDate")) { var startDate = Helper.CleanInput(request.QueryStringParameters["startDate"].ToString()); var endDate = Helper.CleanInput(request.QueryStringParameters["endDate"].ToString()); userId = Helper.CleanInput(request.QueryStringParameters["userId"].ToString()); foreach (var graph in graphs) { switch (graph.GraphType) { case "PieGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.GetPieChart(_con, title: graph.GraphTitle, startDate: startDate, endDate: endDate) }); break; case "Table": if ((graph.GraphTitle.Contains("NedBank"))) { result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.GetNedBankTable(_con) }); break; } else { result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.GetTable(_con, graphTitle: graph.GraphTitle, startDate: startDate, endDate: endDate) }); } break; case "CounterTab": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, Data = GraphService.TotalCounter(_con, graph.GraphTitle, startDate: startDate, endDate: endDate) }); break; case "StackGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.BarGraphFamily(_con, graph, startDate: startDate, endDate: endDate) }); break; case "VerticalBarGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.BarGraphFamily(_con, graph, startDate: startDate, endDate: endDate) }); break; case "LineMarkGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.BarGraphFamily(_con, graph, startDate: startDate, endDate: endDate) }); break; case "AreaGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.BarGraphFamily(_con, graph, startDate: startDate, endDate: endDate) }); break; case "RadarGraph": result.Add(new { GraphTitle = graph.GraphTitle, GraphType = graph.GraphType, GraphX = graph.GraphX, GraphY = graph.GraphY, Data = GraphService.BarGraphFamily(_con, graph, startDate: startDate, endDate: endDate) }); break; } } response = HttpService.CreateResponse(200, payload: result); } else { response = HttpService.CreateResponse(400, payload: "Please supply the start Date and the end Date"); } } else { response = HttpService.CreateResponse(400, payload: $"{validationMessage}"); } } else { response = HttpService.CreateResponse(400, payload: "Body can not be empty"); } _logger.WriteLog(userId, statement, response.Body, "DB"); } catch (Exception ex) { _logger.WriteLog(userId, statement, ex.Message, "DB"); return HttpService.CreateResponse(500, payload: "Graphs can not be generated. Please check the active sql queries"); } finally { if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return response; } /******** CronJob ***************/ public async Task RunCronJob(APIGatewayProxyRequest request, ILambdaContext context) { string cronLog = $"DB::CronJob::{DateTime.Now.ToString("yyyy-MM-dd h:mm:ss tt")}"; //log when cron ran string statement = "DB::CronJob"; List eNatisInfridgments = new List(); List iForceInfridgments = new List(); List problemInfridgments = new List(); try { if (_con.State == ConnectionState.Closed) _con.Open(); //First try out the previous failed payments //statement = "SELECT InfringementID,strInfringementNumber,transaction.strStatus, transaction.TransactionID, (infringement.strAmount) as strAmount, transaction.dtPaid, strIssuingAuthority, strRecieptNumber, strProviderName, strInfringementSource, intSettlementTries FROM infringement INNER JOIN transaction ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN paymentProvider ON transaction.refPaymentProviderID = paymentProvider.PaymentProviderID WHERE infringement.strStatus = 'Source Failed Payment'"; statement = @"SELECT InfringementID, strInfringementNumber, transaction.strStatus, transaction.TransactionID, (infringement.strAmount) as strAmount, transaction.dtPaid, strIssuingAuthority, strRecieptNumber, strProviderName, strInfringementSource, intSettlementTries FROM infringement INNER JOIN transaction ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN paymentProvider ON transaction.refPaymentProviderID = paymentProvider.PaymentProviderID WHERE infringement.strStatus = 'Source Failed Payment'"; var cmd = new MySqlCommand(); cmd.Connection = _con; var paymentService = new PaymentService(dbUser); bool hasRows = paymentService.LoadInfringements(statement, iForceInfridgments, eNatisInfridgments); if (hasRows) { //Process previously failed payments based on source var iforcePaymentResult = await paymentService.ProcessIForcePayments(iForceInfridgments, problemInfridgments); var eNatisPaymentResult = await paymentService.ProcessENatisPayments(eNatisInfridgments, problemInfridgments); if (problemInfridgments.Count > 0) { string adminLabel = "[" + (char)34 + "ADMIN" + (char)34 + "]"; //statement = $"SELECT * FROM user WHERE strRole ='{adminLabel}' and UserID = 1 "; //Admin tag //cmd.CommandText = statement; //cmd.ExecuteNonQuery(); //_reader = cmd.ExecuteReader(); //List adminUsers = new List { }; //if (_reader.HasRows) //{ // while (_reader.Read()) // { // adminUsers.Add(_reader.GetString(5)); //email add // } // _reader.Close(); //} List adminUsers = new List(); PMF.Services.Mailer.SendEmail(problemInfridgments, adminUsers); } iForceInfridgments.Clear(); eNatisInfridgments.Clear(); } else { statement = "NO Failed Payments Found"; _logger.WriteLog("CRONJOB", statement, cronLog, "TMT"); } //Then try out the new payments which have not attempted to be settled //statement = @"SELECT TransactionID, InfringementID,strInfringementNumber,transaction.strStatus, infringement.strAmount, transaction.dtPaid, strIssuingAuthority, strRecieptNumber, strProviderName, strInfringementSource, intSettlementTries // FROM infringement INNER JOIN transaction ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN paymentProvider ON transaction.refPaymentProviderID = paymentProvider.PaymentProviderID // WHERE transaction.intPaid = 'YES' AND transaction.strStatus='success' AND intSettlementTries=0"; statement = @"SELECT TransactionID, InfringementID, strInfringementNumber, infringement.strStatus, infringement.strAmount, infringement.dtPaid, strIssuingAuthority, strRecieptNumber, strProviderName, strInfringementSource, intSettlementTries FROM infringement INNER JOIN transaction ON infringement.refTrasactionID = transaction.TransactionID INNER JOIN paymentProvider ON transaction.refPaymentProviderID = paymentProvider.PaymentProviderID WHERE infringement.intPaid = 'YES' AND infringement.strStatus='success'"; hasRows = paymentService.LoadInfringements(statement, iForceInfridgments, eNatisInfridgments); if (hasRows) { //Process new payments. var iforcePaymentResult = await paymentService.ProcessIForcePayments(iForceInfridgments); var eNatisPaymentResult = await paymentService.ProcessENatisPayments(eNatisInfridgments); _logger.WriteLog("CRONJOB", $"Processed iForce: {iForceInfridgments.Count} Processed eNatis: {eNatisInfridgments.Count} ", cronLog, "TMT"); } else { statement = "NO New Payments Found"; _logger.WriteLog("CRONJOB", statement, cronLog, "TMT"); } } catch (Exception ex) { _logger.WriteLog("CRONJOB", ex.Message, cronLog, "TMT"); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return HttpService.CreateResponse(200, payload: "Cronjob completed"); } ///Redirect from iveri public async Task IVeriPayment(APIGatewayProxyRequest request, ILambdaContext context) { string statement = "iVeri-Lite::IVeriPayment"; string econConsumerId = "Not returned by iVeri", merchantReference = "Not returned by iVeri", liteBankReference = "Not returned by iVeri"; try { if (request != null) { var iVeriLocation = request.QueryStringParameters["veriLocation"]; var status = request.QueryStringParameters["status"]; string transactionID = request.QueryStringParameters["transactionID"]; var iveriResult = request.Body; string iveriCleaned = iveriResult.Substring(iveriResult.IndexOf("__EVENTVALIDATION")); string[] iveriResultArr = iveriCleaned.Split("&"); string liteResultDescription = iveriResultArr.Where(x => x.StartsWith("LITE_RESULT_DESCRIPTION")).FirstOrDefault(); liteResultDescription = liteResultDescription.Substring(liteResultDescription.IndexOf("=") + 1); if (_con.State == ConnectionState.Closed) _con.Open(); using var cmd = new MySqlCommand(); cmd.Connection = _con; if (string.IsNullOrEmpty(liteResultDescription)) //No result in here payment went through.-->check with iveri { econConsumerId = iveriResultArr.Where(x => x.StartsWith("ECOM_CONSUMERORDERID")).FirstOrDefault(); merchantReference = iveriResultArr.Where(x => x.StartsWith("MERCHANTREFERENCE")).FirstOrDefault(); liteBankReference = iveriResultArr.Where(x => x.StartsWith("LITE_BANKREFERENCE")).FirstOrDefault(); liteBankReference = Uri.UnescapeDataString(liteBankReference); statement = $"Update transaction SET intPaid = 'YES', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = 'No Result', dtLastEdit = NOW(), strRecieptNumber = '{econConsumerId.Substring(econConsumerId.IndexOf("=") + 1)}', strBankRef = '{liteBankReference.Substring(liteBankReference.IndexOf("=") + 1)}', dtIVeriSettlement = NOW(), strMerchantReference = '{merchantReference.Substring(merchantReference.IndexOf("=") + 1)}' WHERE strIVeriNUmber = '{transactionID}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'"; cmd.CommandText = statement; string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString(); statement = $"Update infringement SET intPaid = 'YES', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = '{transactionDbId}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); statement = "SELECT strValue FROM setting WHERE strKey = 'convenience'"; cmd.CommandText = statement; double convenience = Convert.ToDouble(cmd.ExecuteScalar()); statement = $"SELECT refClientID FROM transaction WHERE transactionId = {transactionDbId}"; cmd.CommandText = statement; double payeeId = Convert.ToDouble(cmd.ExecuteScalar()); // in infringement table get number,vehicle reg, amount, id number statement = $"SELECT * FROM infringement WHERE refTrasactionID = {transactionDbId}"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); List paidInfringements = new List(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { paidInfringements.Add(new InfringementReceipt { Infringement = _reader["strInfringementNumber"].ToString(), VechicleRegistration = _reader["strVechicleRegistration"].ToString(), IdNumber = _reader["strIdNumber"].ToString(), Amount = Convert.ToInt32(_reader["strAmount"]), }); } _reader.Close(); statement = $"SELECT strEmail FROM user WHERE UserID = {payeeId}"; cmd.CommandText = statement; string payeeEmail = cmd.ExecuteScalar().ToString(); var paymentNotification = new PaymentReceiptService(_logger, context); var receiptGenerated = await paymentNotification.GenerateReceipt(paidInfringements, convenience, payeeEmail, transactionDbId, econConsumerId.Substring(econConsumerId.IndexOf("=") + 1)); if (!receiptGenerated) _logger.WriteLog(transactionDbId, payeeEmail, "mail not sent", "TMT"); } string url = $"{iVeriLocation}?PaymentNotification?transactionID={transactionID}&status={status}"; var response = new APIGatewayProxyResponse { StatusCode = 302, Headers = new Dictionary { // { "Content-Type", "application/json" }, //{ "Access-Control-Allow-Origin", "*" }, { "location", url} } }; _logger.WriteLog("iVeriPayment", $"Location: {iVeriLocation}", "iveri transaction", "DB"); return response; } else //iveri has an error in result { if (string.Equals(liteResultDescription, "Transaction cancelled by customer")) { statement = $"Update transaction SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = '{liteResultDescription}', dtLastEdit = NOW(), dtIVeriSettlement = NOW() WHERE strIVeriNUmber = '{transactionID}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'"; cmd.CommandText = statement; string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString(); statement = $"Update infringement SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = {transactionDbId}"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); } else { statement = $"Update transaction SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = '{liteResultDescription}', dtLastEdit = NOW(), strRecieptNumber = '{econConsumerId.Substring(econConsumerId.IndexOf("=") + 1)}', strBankRef = '{liteBankReference.Substring(liteBankReference.IndexOf("=") + 1)}', dtIVeriSettlement = NOW(), strMerchantReference = '{merchantReference.Substring(merchantReference.IndexOf("=") + 1)}' WHERE strIVeriNUmber = '{transactionID}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'"; cmd.CommandText = statement; string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString(); statement = $"Update infringement SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = {transactionDbId}"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); } string url = $"{iVeriLocation}?PaymentNotification?transactionID={transactionID}&status={status}&liteResultDescription={liteResultDescription}"; var response = new APIGatewayProxyResponse { StatusCode = 302, Headers = new Dictionary { // { "Content-Type", "application/json" }, // { "Access-Control-Allow-Origin", "*" }, messes with the redirect. { "location", url } } }; _logger.WriteLog("iVeriPayment", $"Location: {url}", "iveri transaction", "DB"); return response; } } else { _logger.WriteLog("iVeriPayment", "iVeri returned an empty request", "exception", "DB"); } } catch (Exception ex) { _logger.WriteLog("iVeriPayment", ex.Message.ToString(), "exception", "DB"); return HttpService.CreateResponse(500, payload: ex.Message); } finally { if (_reader != null) _reader.Close(); if (_con != null || _con.State == ConnectionState.Open) _con.Close(); } return HttpService.CreateResponse(500, payload: "Error"); //should never get here } #region HELPERS /******** HELPER FUNCTIONS ***************/ private async Task>> GeteNatisIdsData(List allCognitoIds, string filter) { List urlsEnatis = new List { }; errorInducedEmpty = false; IEnumerable eNatisresponses = null; List> eNatisQueryInfrigerResponses = new List> { }; var handler = new HttpClientHandler(); try { handler.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var enatisClient = new HttpClient(handler) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL) }; foreach (var id in allCognitoIds) { urlsEnatis.Add($"infringement/queryinfringer?IdDocumentType={id.IdType}&IdDocumentNumber={id.IdNumber}"); } //Start requests for all of them var eNatisrequests = urlsEnatis.Select ( url => enatisClient.GetAsync(url) ).ToList(); //Wait for all the requests to finish await Task.WhenAll(eNatisrequests); //Get the responses eNatisresponses = eNatisrequests.Select ( task => task.Result ); } catch { errorInducedEmpty = true; } if (eNatisresponses != null) { foreach (var response in eNatisresponses) { // Extract the message body var content = await response.Content.ReadAsStringAsync(); string path = response.RequestMessage.RequestUri.PathAndQuery.ToString(); var id = path.Substring(path.LastIndexOf('=') + 1); if (response.IsSuccessStatusCode) { var body = JsonConvert.DeserializeObject>(content); body.ToList().ForEach(x => x.idNumber = id); eNatisQueryInfrigerResponses.Add(body); } else { errorInducedEmpty = false; _logger.WriteLog("eNatis request failed", content, response.RequestMessage.ToString(), "DB"); } } if (!string.IsNullOrEmpty(filter)) { return eNatisQueryInfrigerResponses.Where(x => x.Any(s => s.location.issAuthority.description == filter)).ToList(); } return eNatisQueryInfrigerResponses; } return eNatisQueryInfrigerResponses; } private async Task> GetIForceIdsData(string attribute, List allCognitoIds, string filter) { List urlsIForce = new List { }; var handlerIForce = new HttpClientHandler(); IEnumerable responses = null; try { handlerIForce.ServerCertificateCustomValidationCallback = Helper.ServerCertificateCustomValidation; var client = new HttpClient(handlerIForce) { BaseAddress = new Uri(Constants.IFORCE_ENATIS_API_BASE_URL), Timeout = TimeSpan.FromSeconds(Constants.TimeDefaultValue) }; foreach (var id in allCognitoIds) { urlsIForce.Add($"IForce/queryfines?Attribute={attribute}&FineQueryAttributeValue={id.IdNumber}"); } //Start requests for all of them var requests = urlsIForce.Select ( url => client.GetAsync(url) ).ToList(); //Wait for all the requests to finish await Task.WhenAll(requests); //Get the responses responses = requests.Select ( task => task.Result ); } catch (Exception) { errorInducedEmpty = true; } List iForceQueryFinesResponses = new List { }; if (responses != null) { foreach (var response in responses) { // Extract the message body var content = await response.Content.ReadAsStringAsync(); if (response.IsSuccessStatusCode) { var body = JsonConvert.DeserializeObject(content); iForceQueryFinesResponses.Add(body); } else { errorInducedEmpty = false; _logger.WriteLog("iForce request failed", content, response.RequestMessage.ToString(), "DB"); } } if (!string.IsNullOrEmpty(filter)) { return iForceQueryFinesResponses.Where(x => x.fines.Any(y => y.districtName == filter)).ToList(); } return iForceQueryFinesResponses; ; } return iForceQueryFinesResponses; } private void GetAllAssociateIds(List allCognitoIds, string statement) { using var cmd = new MySqlCommand(); cmd.Connection = _con; cmd.CommandText = statement; cmd.ExecuteNonQuery(); string userId = ""; _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { userId = _reader.GetValue(0).ToString(); allCognitoIds.Add(new Id { IdNumber = _reader.GetValue(14).ToString(), IdType = _reader.GetValue(4).ToString() }); } } _reader.Close(); statement = $"SELECT * FROM userIdNumber WHERE refUserID = '{userId}'"; cmd.CommandText = statement; cmd.ExecuteNonQuery(); _reader = cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { allCognitoIds.Add(new Id { IdNumber = _reader.GetValue(5).ToString(), IdType = _reader.GetValue(4).ToString() }); } _reader.Close(); } } #endregion } }