public static Account GetAccountBySms(string cardNumber, int? id_modem, int? id_bank)
{
Account res = null;
using (SqlConnectionHolder holder = SqlConnectionHelper.GetConnection(SqlConnectionHelper.ConnectionType.Naviam))
{
using (SqlCommand cmd = holder.Connection.CreateSPCommand("sms_account_get"))
{
cmd.Parameters.AddWithValue("@card_number", cardNumber);
cmd.Parameters.AddWithValue("@id_modem", id_modem);
cmd.Parameters.AddWithValue("@id_bank", id_bank);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
if (reader.HasRows)
{
res = new Account(reader);
}
}
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
}
return res;
}
public static Account GetAccount(int? id, int? companyId)
{
Account res = null;
//TODO: check that account belongs to company
using (SqlConnectionHolder holder = SqlConnectionHelper.GetConnection(SqlConnectionHelper.ConnectionType.Naviam))
{
using (SqlCommand cmd = holder.Connection.CreateSPCommand("accounts_get"))
{
cmd.Parameters.AddWithValue("@id_account", id);
cmd.Parameters.AddWithValue("@id_company", companyId.ToDbValue());
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
if (reader.HasRows)
{
res = new Account(reader);
}
}
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
}
return res;
}
//we need to provide full object (not only id) to delete from redis (restrict of redis)
public static int Delete(Account entity, int? companyId)
{
var res = AccountsDataAdapter.Delete(entity, companyId);
if (res == 0)
{
//if ok - remove from cache
new CacheWrapper().RemoveFromList(CacheKey, entity, companyId);
}
return res;
}
public ActionResult UpdateAccount(Account account)
{
var user = CurrentUser;
if (account.Id == null)
{
account.CompanyId = user.CurrentCompany;
AccountsRepository.Insert(account,user.CurrentCompany);
}
else
{
AccountsRepository.Update(account, user.CurrentCompany);
}
var currencies = CurrenciesDataAdapter.GetCurrencies();
account.Currency = currencies.Find(c => c.Id == account.CurrencyId).NameShort;
return Json(account);
}
public static int Delete(Account entity, int? companyId)
{
var res = -1;
using (var holder = SqlConnectionHelper.GetConnection())
{
using (var command = holder.Connection.CreateSPCommand("accounts_delete"))
{
try
{
command.AddCommonParameters(entity.Id);
command.Parameters.AddWithValue("@id_company", companyId.ToDbValue());
command.ExecuteNonQuery();
res = command.GetReturnParameter();
}
catch (SqlException e)
{
command.AddDetailsToException(e);
throw;
}
}
}
return res;
}
public static int InsertUpdate(Account entity, int? companyId, DbActionType action)
{
var res = -1;
using (var holder = SqlConnectionHelper.GetConnection())
{
var commName = action == DbActionType.Insert ? "account_create" : "account_update";
var command = holder.Connection.CreateSPCommand(commName);
try
{
command.AddEntityParameters(entity, action);
command.ExecuteNonQuery();
if (action == DbActionType.Insert)
entity.Id = command.GetRowIdParameter();
res = command.GetReturnParameter();
}
catch (SqlException e)
{
command.AddDetailsToException(e);
throw;
}
}
return res;
}
public static int Update(Account entity, int? companyId)
{
//TODO: check that account belongs to company
return InsertUpdate(entity, companyId, DbActionType.Update, true);
}
public static int Insert(Account entity, int? companyId, bool intoCache)
{
return InsertUpdate(entity, companyId, DbActionType.Insert, intoCache);
}
public static int Insert(Account entity, int? companyId)
{
return Insert(entity, companyId, true);
}
private static int InsertUpdate(Account entity, int? companyId, DbActionType action, bool intoCache)
{
var cache = new CacheWrapper();
var res = AccountsDataAdapter.InsertUpdate(entity, companyId, action);
if (res == 0)
{
//if ok - update cache
if (intoCache)
{
if (action == DbActionType.Insert)
cache.AddToList(CacheKey, entity, companyId);
if (action == DbActionType.Update)
cache.UpdateList(CacheKey, entity, companyId);
}
}
return res;
}
private static Account GetTestAccountBySms(string cardNumber, int? id_modem, int? id_bank)
{
var res = new Account();
res.Id = 1;
return res;
}
/// <summary>
/// Appends Account-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Account class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Account account, DbActionType action)
{
command.AddCommonParameters(account.Id, action);
command.Parameters.Add("@curr_date_utc", SqlDbType.DateTime).Value = DateTime.UtcNow;
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = account.Name.ToDbValue();
command.Parameters.Add("@id_company", SqlDbType.Int).Value = account.CompanyId.ToDbValue();
command.Parameters.Add("@id_currency", SqlDbType.Int).Value = account.CurrencyId.ToDbValue();
command.Parameters.Add("@initial_balance", SqlDbType.Decimal).Value = account.InitialBalance;
command.Parameters.Add("@id_type", SqlDbType.Int).Value = account.TypeId.ToDbValue();
command.Parameters.Add("@description", SqlDbType.NVarChar).Value = account.Description.ToDbValue();
command.Parameters.Add("@balance", SqlDbType.Decimal).Value = account.Balance;
command.Parameters.Add("@id_financial_institution", SqlDbType.Int).Value = account.FinInstitutionId.ToDbValue();
command.Parameters.Add("@card_number", SqlDbType.NVarChar).Value = account.CardNumber.ToDbValue();
}
public ActionResult GetBankCards(Account account)
{
var user = CurrentUser;
return Json(account);
}
