public string GetHubs(string value)
{
string hubs = string.Empty;
try
{
DbRequest request = new DbRequest();
request.isSelectQuery = true;
request.Query = "SELECT hub_code FROM OPN_HUBS";
DbResponse response = Database_Access.Execute_Db_Query(request);
if (null != response)
{
if (null != response.dataTable)
{
response.dataTable.Rows.Cast <DataRow>().ToList().ForEach(row =>
{
hubs = hubs + "," + Convert.ToString(row[0]);
});
}
}
}
catch (Exception ex)
{
hubs = "ERROR" + "," + ex.Message;
hubs = hubs + "," + ex.StackTrace;
}
return(hubs);
}
public DbResponse <T> SortGridData <T>(DbResponse <T> response, GridReadEventArgs args)
{
try
{
var sortedData = new List <T>(response.Data);
foreach (SortDescriptor sorts in args.Request.Sorts)
{
if (sorts.SortDirection == ListSortDirection.Ascending)
{
response.Data = response.Data.OrderBy(x => x.GetType().GetProperty(sorts.Member).GetValue(x)).ToList();
}
else
{
response.Data = response.Data.OrderByDescending(x => x.GetType().GetProperty(sorts.Member).GetValue(x)).ToList();
}
}
return(response);
}
catch
{
return(response);
}
}
public ApiResponse Put(int id, [FromBody] ProductViewModel model)
{
ApiResponse apiResponse = new ApiResponse();
DbResponse dbResponse = new DbResponse();
try
{
if (id == model.PId)
{
Product data = mapper.Map <Product>(model);
dbResponse = business.InsertUpdate(data);
apiResponse.Result = dbResponse;
}
else
{
dbResponse.SetError(1, "Invalid Id!");
apiResponse.Result = dbResponse;
}
}
catch (Exception ex)
{
dbResponse.SetError(1, ex.Message.ToString());
apiResponse.Result = dbResponse;
}
return(apiResponse);
}
public async Task <DbResponse <TaskNote> > CompleteTaskAsync(string taskId)
{
TaskNote taks = await GetTaskAsync(taskId);
DbResponse <TaskNote> result = new DbResponse <TaskNote>();
if (taks == null)
{
result.IsSuccessed = false;
result.Errors = new[] { "Task not found" };
}
else if (taks.IsComplete)
{
result.IsSuccessed = false;
result.Errors = new[] { "Task already completed" };
}
else
{
taks.IsComplete = true;
await m_DbContext.SaveChangesAsync();
result.IsSuccessed = true;
}
return(result);
}
public Cliente ProcurarCPF(Cliente cli)
{
ClienteDAO clientedao = new ClienteDAO();
DbResponse <List <Cliente> > respostadao = clientedao.LerTodosInativos();
if (!respostadao.Sucesso)
{
cli.ID = -1;
return(cli);
}
List <Cliente> listacli = respostadao.Dados;
cli.CPF = cli.CPF.Replace("-", "").Replace(".", "").Replace(",", "");
for (int i = 0; i < (listacli.Count); i++)
{
if (listacli[i].CPF == cli.CPF)
{
cli.ID = listacli[i].ID;
return(cli);
}
}
cli.ID = -2;
return(cli);//Esse id não afeta o cadastro de um novo cliente, porém ajuda na checagem de cpf
}
public async Task <UserEntity> UpdateUser(UserEntity user)
{
var docDbRepository = new UserRepository(documentDbClientFactory);
DbResponse <UserEntity> response = await docDbRepository.UpsertAsync(user);
return(response.Entity);
}
public DbResponse Delete(int?PId)
{
var response = new DbResponse();
try
{
bool productExists = Context.Product.Any(x => x.PId == PId);
if (!productExists)
{
response.SetError(1, "Error! Product doesnot Exists!");
return(response);
}
var data = Context.Product.Where(t => t.IsActive).FirstOrDefault(t => t.PId.Equals(PId));
if (data == null)
{
response.SetError(1, "Error! Product Already Deleted!");
}
else
{
data.LastModifiedOn = DateTime.Now;
data.IsActive = false;
Context.Product.Update(data);
Context.SaveChanges();
response.SetError(0, "Data Deleted Succesfully!");
}
}
catch (Exception ex)
{
response.SetError(1, "Error! while Updating Data!");
}
return(response);
}
public void UpdateLoginPage(DbResponse response)
{
SetLoading(false);
if (response == null)
{
ResetFields();
}
else if (response.state == DbResponseState.NOTFOUND)
{
SetLoginFailed(true);
SetEnabled(true);
}
else if (response.state == DbResponseState.SUCCESS)
{
SetLoginFailed(false);
if (rememberMeButton.isOn)
{
Debug.Log("Remembering this user");
SavedAccounts.SaveUser(usernameInput.text, passwordInput.text);
}
}
else
{
Debug.LogWarning("Unexpected response when logging in user");
SetLoginFailed(true);
SetEnabled(false);
}
}
// Format to DbResponse
public static IDbResponse FormatToDbResponse <T>(this T obj) where T : IDbResponse, new()
{
IDbResponse response = new DbResponse();
try
{
if (obj != null)
{
response = new DbResponse()
{
IsSuccess = obj.IsSuccess,
Message = obj.Message
};
}
}
catch (Exception ex)
{
throw ex;
//LogWriter log = new LogWriter("Exception in FormatToDbResponse in DataHelper; Message:" + ex.Message);
}
finally
{
}
return(response);
}
public DbResponse ParseDbResponse(System.Data.DataTable dt)
{
var res = new DbResponse();
if (null == dt)
{
res.ErrorCode = 0;
res.Message = "";
res.Id = "";
return(res);
}
if (dt.Rows.Count > 0)
{
res.ErrorCode = Convert.ToInt32(dt.Rows[0][0].ToString());
res.Message = dt.Rows[0][1].ToString();
res.Id = dt.Rows[0][2].ToString();
if (dt.Columns.Count > 3 && dt.Columns.Count < 5)
{
res.Extra = dt.Rows[0][3].ToString();
}
if (dt.Columns.Count > 4)
{
res.Extra = dt.Rows[0][3].ToString();
res.Extra2 = dt.Rows[0][4].ToString();
}
}
return(res);
}
public override async Task <DbResponse> ExecuteReaderWithValidationAsync(string commandText, params NpgsqlParameter[] sqlParameters)
{
try
{
DbResponse dbResponse = new DbResponse();
Initialise(commandText);
using (NpgsqlCommand sqlCommand = new NpgsqlCommand(commandText, Connection)
{
CommandType = CommandType.Text
})
{
SetSqlCommandParameters(sqlCommand, true, sqlParameters);
dbResponse.DataReader = await sqlCommand.ExecuteReaderAsync();
dbResponse.ReturnValue = GetReturnValue(sqlCommand);
dbResponse.ValidationMessages = GetValidationMessages(sqlCommand);
return(dbResponse);
}
}
catch (Exception ex)
{
throw new StoredProcedureException(ex.Message);
}
}
//Ensure there is a connection to the db by querying a known user account
//If connection cannot be established, stops the coroutine, sets to the "RESPONSE_RECEIVED" state and set response to null;
IEnumerator CheckDbConnection()
{
WWWForm form = new WWWForm();
form.AddField("username", knownUsername);
form.AddField("password", knownPass);
using (UnityWebRequest www = UnityWebRequest.Post(baseURL + "check_user.php", form)) {
yield return(www.SendWebRequest());
if (www.isNetworkError || www.isHttpError)
{
Debug.LogWarning("Cannot connect to the database: " + www.error);
Debug.Log(www.downloadHandler.text);
if (disregardNext)
{
currentState = DbConnectionState.READY;
lastResponse = null;
}
else
{
currentState = DbConnectionState.RESPONSE_RECEIVED;
lastResponse = new DbResponse(DbResponseState.NOCONNECTION, www.downloadHandler.text, www.GetResponseHeaders());
}
yield break;
} //else {
//Debug.Log("Database connection is available");
//}
}
}
void Update()
{
//Return to main menu if the connection with the tablet is lost
if (ConnectionManager.Singleton != null)
{
if (ConnectionManager.Singleton.GetState() == ConnectionState.DISCONNECTED)
{
Debug.Log("Stopping game due to disconnection");
SessionManager.Singleton.ReturnToMainMenu();
}
}
if (waitingForDbResponse)
{
DbResponse response = DbConnection.Singleton.GetResponse();
if (response != null)
{
waitingForDbResponse = false;
SetLoading(false);
Debug.Log("response received");
Debug.Log(response.state);
Debug.Log(response.message);
}
}
}
public async Task <UserEntity> GetUser(string id)
{
var docDbRepository = new UserRepository(documentDbClientFactory);
DbResponse <UserEntity> response = await docDbRepository.QuerySingleAsync(x => x.Id == id);
return(response.Entity);
}
public void Basic_ResultSetAlwaysSet_Default_Test()
{
DbResponse <bool> response = new DbResponse <bool>();
Assert.IsNotNull(response.ResultSet);
Assert.IsFalse(response.HasResult);
Assert.IsFalse(response.HasError);
}
public async Task <IDbResponse> DeleteEmployeeAsync(EmployeeViewModelParam param)
{
IDbResponse result = new DbResponse(true);
_context.Entry(param).State = EntityState.Deleted;
await _context.SaveChangesAsync();
return(result);
}
//Check if the given username and credentials match an existing user
//When called, sets to the AWAITING_RESPONSE state
//Once a response is received, changes to RESPONSE_RECEIVED
//Sets response based on
// 200 - SUCCESS (credentials are valid)
// 404 - NOTFOUND (credentials not valid)
// ? - OTHER (something else)
IEnumerator CheckUserCredentials(string username, string password)
{
UnityWebRequest.ClearCookieCache();
Debug.Log("Checking user credentials are valid");
if (currentState != DbConnectionState.READY)
{
Debug.LogWarning("Tried to connect to the DB before receiving response, will not process this request");
yield break;
}
currentState = DbConnectionState.AWAITING_RESPONSE;
yield return(StartCoroutine(CheckDbConnection()));
if (currentState != DbConnectionState.AWAITING_RESPONSE)
{
yield break;
}
WWWForm form = new WWWForm();
form.AddField("username", username);
form.AddField("password", password);
using (UnityWebRequest www = UnityWebRequest.Post(baseURL + "check_user.php", form)) {
yield return(www.SendWebRequest());
DbResponseState state;
if (www.responseCode == 200)
{
state = DbResponseState.SUCCESS;
}
else if (www.responseCode == 404)
{
state = DbResponseState.NOTFOUND;
}
else
{
Debug.LogError("Unexpected response when checking user credentials: " + www.error);
Debug.Log(www.downloadHandler.text);
state = DbResponseState.OTHER;
}
if (disregardNext)
{
currentState = DbConnectionState.READY;
lastResponse = null;
}
else
{
currentState = DbConnectionState.RESPONSE_RECEIVED;
lastResponse = new DbResponse(state, www.downloadHandler.text, www.GetResponseHeaders());
}
}
}
public void Basic_ResultSetAlwaysSet_Error_Test()
{
DbResponse <bool> response = new DbResponse <bool>("Bad error", new Exception("Error"));
Assert.IsTrue(response.HasError);
Assert.IsNotNull(response.Exception);
Assert.IsFalse(string.IsNullOrWhiteSpace(response.ErrorMessage));
Assert.IsNotNull(response.ResultSet);
Assert.IsFalse(response.HasResult);
}
public IDbResponse GetResponse()
{
// ensure that all commands will share a connection
foreach (var dbCommand in this.PrerequisiteCommands)
{
dbCommand.Connection = this.Command.Connection;
}
return(DbResponse.GetResponse(this));
}
public static void SetMessageInSession(int code, string Msg)
{
var resp = new DbResponse()
{
ErrorCode = code,
Message = Msg
};
SetMessageInSession(resp);
}
//Save the values of the data recorder to the DB.
//When called, sets to the AWAITING_RESPONSE state
//Once a response is received, changes to RESPONSE_RECEIVED
//Sets response based on
// 200 - SUCCESS (scores saved)
// 404 - NOTFOUND (scores not saved)
// ? - OTHER (something else)
IEnumerator SaveData(DataRecorder recorder)
{
UnityWebRequest.ClearCookieCache();
if (currentState != DbConnectionState.READY)
{
Debug.LogWarning("Tried to connect to the DB before receiving response, will not process this request");
yield break;
}
currentState = DbConnectionState.AWAITING_RESPONSE;
yield return(StartCoroutine(CheckDbConnection()));
if (currentState != DbConnectionState.AWAITING_RESPONSE)
{
yield break;
}
var www = new UnityWebRequest(baseURL + "save_data.php", "POST");
byte[] bodyRaw = Encoding.UTF8.GetBytes(recorder.ToJSON());
www.uploadHandler = (UploadHandler) new UploadHandlerRaw(bodyRaw);
www.downloadHandler = (DownloadHandler) new DownloadHandlerBuffer();
www.SetRequestHeader("Content-Type", "application/json");
yield return(www.SendWebRequest());
DbResponseState state;
if (www.responseCode == 200)
{
state = DbResponseState.SUCCESS;
}
else if (www.responseCode == 404)
{
state = DbResponseState.NOTFOUND;
}
else
{
Debug.LogError("Unexpected response when trying to save data: " + www.error);
Debug.Log(www.downloadHandler.text);
state = DbResponseState.OTHER;
}
if (disregardNext)
{
currentState = DbConnectionState.READY;
lastResponse = null;
}
else
{
currentState = DbConnectionState.RESPONSE_RECEIVED;
lastResponse = new DbResponse(state, www.downloadHandler.text, www.GetResponseHeaders());
}
}
public ActionResult SignUp()
{
UserCommon model = new UserCommon();
//Uri url = Request.Url;
var resp = new DbResponse();
resp.ErrorCode = 1;
resp.Message = "testing message in session";
StaticData.SetMessageInSession(resp);
return(View(model));
}
public DbResponse ExceptionDbResponse(string msg)
{
DbResponse dr = new DbResponse()
{
ErrorCode = 1,
Message = msg,
Id = ""
};
return(dr);
}
public Response <AccountDto> PasswordResetRequest(string username, Func <SqlDataReader, AccountDto> accountDtoMapper)
{
DbResponse dbResponse = CommandTypeManager.ExecuteReaderWithValidation("AccountPasswordResetRequest", new SqlQueryParameter {
ParameterName = "UserName ", ParameterDirection = DbParameterDirection.Input, ParamentType = CodeParameterType.String, ParameterSize = 100, ParameterValue = username
},
new SqlQueryParameter {
ParameterName = "ValidationMessages", ParameterDirection = DbParameterDirection.Output, ParamentType = CodeParameterType.String, ParameterSize = 8000
});
return(dbResponse.ToReponse <AccountDto>(accountDtoMapper));
}
public async Task <DbResponse <T> > ExecStoredProcedure <T>(string proc, DynamicParameters parameters = null)
{
var response = new DbResponse <T>();
if (_sql == null)
{
response.Success = false;
response.Info = "SqlConfiguration not found. Did you forget to pass SqlConfiguration to Helper?";
return(response);
}
using (var conn = new SqlConnection(_sql.Value))
{
if (conn.State == ConnectionState.Closed)
{
// open connection
conn.Open();
}
try
{
// execute stored procedure
response.Success = true;
response.Data = (await conn.QueryAsync <T>(proc, parameters, commandType: CommandType.StoredProcedure)).ToList();
}
catch (SqlException e)
{
// get exec
string exec = GetStatement(proc, parameters, e);
response.Success = false;
response.Info = exec;
// print exec
Console.WriteLine(exec);
}
catch (Exception e)
{
response.Success = false;
response.Info = $"Unknown error: {e.Message}";
}
finally
{
if (conn.State == ConnectionState.Open)
{
// close connection
conn.Close();
}
}
}
return(response);
}
public DbResponse SendEmail(MailInformation info)
{
MailMessage mail = new MailMessage();
SmtpClient mailer = new SmtpClient();
Stream htmlStream = null;
var response = new DbResponse();
try
{
if (!string.IsNullOrEmpty(info.AttachmentPath))
{
// Add attachment if any
AddAttachmentForMail(info, mail);
}
// set to mail address
mail.From = new MailAddress(ConfigFields.MailFromAddress, ConfigFields.MailDisplayName);
// set recipients for mail
SetDefaultRecipientsForMail(ref mail);
SetRecipientsForMail(info, ref mail);
// set subject
mail.Subject = ConfigFields.MailSubject;
mail.IsBodyHtml = true;
mail.Body = GenerateBody(info);
// send mail via SMTP server
bool isMailSent = SendEmailViaSMTPServer(mail, mailer);
response = new DbResponse(isMailSent);
}
catch (Exception ex) //Module failed to load
{
response = new DbResponse(false, ex.Message);
Logger.Write("error occurred in SendEmail() - EmailUtility; Message: " + ex.Message);
}
finally
{
if (mail != null)
{
mail.Dispose();
}
if (htmlStream != null)
{
htmlStream.Dispose();
}
}
return(response);
}
public ActionResult PayOnDelivery()
{
var OrderId = Request.Form["OrderId"];
var TotalPrice = Request.Form["TotalPrice"];
var User = StaticData.GetUser();
var response = buss.MakePayment(OrderId, TotalPrice, User);
DbResponse resp = new DbResponse();
resp.ErrorCode = response.ErrorCode;
resp.Message = response.Message;
StaticData.SetMessageInSession(resp);
return(RedirectToAction("Index", "Home"));
}
private void ReadResultsToDbResponse <TObjects>(IDbCommand command, DbResponse <TObjects> response)
where TObjects : new()
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TObjects obj = new TObjects();
DbAutoFillHelper.FillObjectFromDataReader(reader, obj);
response.ResultSet.Add(obj);
}
}
}
//Return the last response received (or null if response not yet received)
//If a response has been received, calling this function resets the state to "READY"
public DbResponse GetResponse()
{
if (currentState == DbConnectionState.RESPONSE_RECEIVED)
{
currentState = DbConnectionState.READY;
}
DbResponse result = lastResponse;
lastResponse = null;
return(result);
}
public Response <AccountDto> PasswordReset(ResetPasswordDto resetPasswordDto, Func <SqlDataReader, AccountDto> accountDtoMapper)
{
DbResponse dbResponse = CommandTypeManager.ExecuteReaderWithValidation("AccountPasswordReset", new SqlQueryParameter {
ParameterName = "Password ", ParameterDirection = DbParameterDirection.Input, ParamentType = CodeParameterType.String, ParameterSize = 100, ParameterValue = resetPasswordDto.Password
},
new SqlQueryParameter {
ParameterName = "ForgotPasswordKey ", ParameterDirection = DbParameterDirection.Input, ParamentType = CodeParameterType.Guid, ParameterValue = resetPasswordDto.ForgotPasswordKey
},
new SqlQueryParameter {
ParameterName = "ValidationMessages", ParameterDirection = DbParameterDirection.Output, ParamentType = CodeParameterType.String, ParameterSize = 8000
});
return(dbResponse.ToReponse <AccountDto>(accountDtoMapper));
}
