/// <summary>
/// Creates a new instance of the manager given a connection string
/// </summary>
/// <param name="dbType">The type of the database (SQLite, SQLServer, ...)</param>
/// <param name="connectionString">The connection string</param>
public MetadataCacheManagerSQL(DatabaseTypes dbType, string connectionString)
{
_db = new DbOperations(connectionString, dbType);
}
public IEnumerable <Hospital> ReadAll()
{
return(DbOperations.ExecuteQuery <Hospital>(_context.CONNECTION_STRING, "dbo.Hospitals_ReadAll"));
}
public Response Respond(Request request)
{
var response = new Response();
var dbOperations = new DbOperations();
InMemoryDb.Category result;
switch (request.Method)
{
case "create":
var createdCategory = dbOperations.CreateCategory(request.Body);
response.Body = createdCategory.ToJson();
response.Status = StatusCode.Created;
return(response);
case "read":
if (request.Path.Split("/").Length == 3)
{
response.Body = dbOperations.GetAllCategories();
}
else
{
result = dbOperations.ReadCategory(Utilities.IdFromPath(request.Path));
if (result == null)
{
response.Status = StatusCode.NotFound;
return(response);
}
response.Body = result.ToJson();
}
response.Status = StatusCode.Ok;
return(response);
case "update":
var updatedData = dbOperations.UpdateCategory(Utilities.IdFromPath(request.Path), request.Body);
if (updatedData == null)
{
response.Status = StatusCode.NotFound;
return(response);
}
response.Body = updatedData.ToJson();
response.Status = StatusCode.Updated;
return(response);
case "delete":
var deleteOperation = dbOperations.DeleteCategory(Utilities.IdFromPath(request.Path));
if (deleteOperation != true)
{
response.Status = StatusCode.NotFound;
return(response);
}
response.Status = StatusCode.Ok;
return(response);
case "echo":
response.Body = request.Body;
response.Status = StatusCode.Ok;
return(response);
default:
return(response);
}
}
/// <summary>
///
/// </summary>
public async void FunctionHandler()
{
DbOperations dboperation = new DbOperations();
await dboperation.GetRDSConnectionandSendMail();
}
public void Update(Hospital hospital)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.Hospitals_Update", hospital.GenerateSqlParametersFromModel().ToArray());
}
public static long InsertRecord(int userId, string tableSchema, string tableName, Collection <KeyValuePair <string, string> > data, string imageColumn)
{
if (data == null)
{
return(0);
}
string columns = string.Empty;
string columnParamters = string.Empty;
int counter = 0;
foreach (KeyValuePair <string, string> pair in data)
{
counter++;
if (counter.Equals(1))
{
columns += DBFactory.Sanitizer.SanitizeIdentifierName(pair.Key);
columnParamters += "@" + pair.Key;
}
else
{
columns += ", " + DBFactory.Sanitizer.SanitizeIdentifierName(pair.Key);
columnParamters += ", @" + pair.Key;
}
}
string sql = "INSERT INTO @TableSchema.@TableName(" + columns + ", audit_user_id) SELECT " + columnParamters +
", @AuditUserId;SELECT LASTVAL();";
using (NpgsqlCommand command = new NpgsqlCommand())
{
sql = sql.Replace("@TableSchema", DBFactory.Sanitizer.SanitizeIdentifierName(tableSchema));
sql = sql.Replace("@TableName", DBFactory.Sanitizer.SanitizeIdentifierName(tableName));
command.CommandText = sql;
foreach (KeyValuePair <string, string> pair in data)
{
if (string.IsNullOrWhiteSpace(pair.Value))
{
command.Parameters.AddWithValue("@" + pair.Key, DBNull.Value);
}
else
{
if (pair.Key.Equals(imageColumn))
{
using (FileStream stream = new FileStream(pair.Value, FileMode.Open, FileAccess.Read))
{
using (BinaryReader reader = new BinaryReader(new BufferedStream(stream)))
{
byte[] byteArray = reader.ReadBytes(Convert.ToInt32(stream.Length));
command.Parameters.AddWithValue("@" + pair.Key, byteArray);
}
}
}
else
{
command.Parameters.AddWithValue("@" + pair.Key, pair.Value);
}
}
}
command.Parameters.AddWithValue("@AuditUserId", userId);
try
{
return(Conversion.TryCastLong(DbOperations.GetScalarValue(command)));
}
catch (NpgsqlException ex)
{
throw new MixERPException(ex.Message, ex, ex.ConstraintName);
}
}
}
public void addNewWork(Work work)
{
DbOperations.AddNewWork(work);
Works.Add(work);
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("Works"));
}
public static async Task <int> TryClaimTx(PlatformId platformId, User user, BitcornContext dbContext)
{
if (user == null)
{
user = await BitcornUtils.GetUserForPlatform(platformId, dbContext).FirstOrDefaultAsync();
}
if (user == null)
{
return(0);
}
bool lockApplied = false;
try
{
if (UserLockCollection.Lock(user))
{
lockApplied = true;
}
else
{
return(0);
}
var now = DateTime.Now;
var txs = await dbContext.UnclaimedTx.Where(u => u.Platform == platformId.Platform &&
u.ReceiverPlatformId == platformId.Id &&
!u.Claimed && !u.Refunded &&
u.Expiration > now).ToArrayAsync();
var sql = new StringBuilder();
var pk = nameof(UserWallet.UserId);
foreach (var tx in txs)
{
tx.Claimed = true;
var log = new CornTx();
log.Amount = tx.Amount;
log.Platform = tx.Platform;
log.ReceiverId = user.UserId;
log.SenderId = tx.SenderUserId;
log.Timestamp = tx.Timestamp;
log.TxGroupId = Guid.NewGuid().ToString();
log.TxType = tx.TxType;
log.UnclaimedTx.Add(tx);
dbContext.CornTx.Add(log);
sql.Append(TxUtils.ModifyNumber(nameof(UserWallet), nameof(UserWallet.Balance), tx.Amount, '+', pk, user.UserId));
sql.Append(TxUtils.ModifyNumber(nameof(UserStat), nameof(UserStat.AmountOfTipsReceived), 1, '+', pk, user.UserId));
sql.Append(TxUtils.ModifyNumber(nameof(UserStat), nameof(UserStat.TotalReceivedBitcornTips), tx.Amount, '+', pk, user.UserId));
sql.Append(TxUtils.UpdateNumberIfTop(nameof(UserStat), nameof(UserStat.LargestReceivedBitcornTip), tx.Amount, pk, user.UserId));
sql.Append(TxUtils.ModifyNumber(nameof(UserStat), nameof(UserStat.AmountOfTipsSent), 1, '+', pk, tx.SenderUserId));
sql.Append(TxUtils.ModifyNumber(nameof(UserStat), nameof(UserStat.TotalSentBitcornViaTips), tx.Amount, '+', pk, tx.SenderUserId));
sql.Append(TxUtils.UpdateNumberIfTop(nameof(UserStat), nameof(UserStat.LargestSentBitcornTip), tx.Amount, pk, tx.SenderUserId));
}
if (txs.Length > 0)
{
await DbOperations.ExecuteSqlRawAsync(dbContext, sql.ToString());
await dbContext.SaveAsync();
}
return(txs.Length);
}
catch (Exception e)
{
await BITCORNLogger.LogError(dbContext, e, JsonConvert.SerializeObject(platformId));
return(0);
}
finally
{
if (lockApplied)
{
UserLockCollection.Release(user);
}
}
}
public AddNewProduct(DbOperations dbo)
{
this.Dbo = dbo;
}
public IEnumerable <DiseasesResult> ReadAll()
{
return(DbOperations.ExecuteQuery <DiseasesResult>(_context.CONNECTION_STRING, "dbo.DiseasesResults_ReadAll"));
}
public DiseasesResult ReadByID(Guid diseaseID, Guid donationD)
{
return(DbOperations.ExecuteQuery <DiseasesResult>(_context.CONNECTION_STRING, "dbo.DiseasesResults_ReadByID", new SqlParameter("DiseaseID", diseaseID), new SqlParameter("DonationD", donationD)).FirstOrDefault());
}
public void Delete(Guid diseaseID, Guid donationD)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.DiseasesResults_Remove", new SqlParameter("DiseaseID", diseaseID), new SqlParameter("DonationD", donationD));
}
public void Update(DiseasesResult diseasesResult)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.DiseasesResults_Update", diseasesResult.GenerateSqlParametersFromModel().ToArray());
}
public GetGroupMembers(DbOperations dbo)
{
this.Dbo = dbo;
}
public ActionResult Delete(int id)
{
ViewBag.msg = DbOperations.DeleteEmp(id);
return(View());
}
public ActionResult GetBusInfo(ServiceReference1.Insert_BusInfo B)
{
ViewBag.msg = DbOperations.InsertBusInfo(B);
return(View(B));
}
public static bool UpdateRecord(int userId, string tableSchema, string tableName,
Collection <KeyValuePair <string, string> > data, string keyColumn, string keyColumnValue, string imageColumn)
{
if (data == null)
{
return(false);
}
string columns = string.Empty;
int counter = 0;
//Adding the current user to the column collection.
KeyValuePair <string, string> auditUserId = new KeyValuePair <string, string>("audit_user_id",
userId.ToString(Thread.CurrentThread.CurrentCulture));
data.Add(auditUserId);
foreach (KeyValuePair <string, string> pair in data)
{
counter++;
if (counter.Equals(1))
{
columns += DBFactory.Sanitizer.SanitizeIdentifierName(pair.Key) + "=@" + pair.Key;
}
else
{
columns += ", " + DBFactory.Sanitizer.SanitizeIdentifierName(pair.Key) + "=@" + pair.Key;
}
}
string sql = "UPDATE @TableSchema.@TableName SET " + columns + " WHERE @KeyColumn=@KeyValue;";
using (NpgsqlCommand command = new NpgsqlCommand())
{
sql = sql.Replace("@TableSchema", DBFactory.Sanitizer.SanitizeIdentifierName(tableSchema));
sql = sql.Replace("@TableName", DBFactory.Sanitizer.SanitizeIdentifierName(tableName));
sql = sql.Replace("@KeyColumn", DBFactory.Sanitizer.SanitizeIdentifierName(keyColumn));
command.CommandText = sql;
foreach (KeyValuePair <string, string> pair in data)
{
if (string.IsNullOrWhiteSpace(pair.Value))
{
command.Parameters.AddWithValue("@" + pair.Key, DBNull.Value);
}
else
{
if (pair.Key.Equals(imageColumn))
{
FileStream stream = new FileStream(pair.Value, FileMode.Open, FileAccess.Read);
try
{
using (BinaryReader reader = new BinaryReader(new BufferedStream(stream)))
{
byte[] byteArray = reader.ReadBytes(Convert.ToInt32(stream.Length));
command.Parameters.AddWithValue("@" + pair.Key, byteArray);
}
}
finally
{
stream.Close();
}
}
else
{
command.Parameters.AddWithValue("@" + pair.Key, pair.Value);
}
}
}
command.Parameters.AddWithValue("@KeyValue", keyColumnValue);
try
{
return(DbOperations.ExecuteNonQuery(command));
}
catch (NpgsqlException ex)
{
throw new MixERPException(ex.Message, ex, ex.ConstraintName);
}
}
}
public ActionResult GetRouteInfo(ServiceReference1.Insert_RouteInfo R)
{
ViewBag.msg = DbOperations.InsertRouteInfo(R);
return(View(R));
}
public SelectUserGroupList(DbOperations dbo)
{
this.Dbo = dbo;
}
public void Update(City city)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.Cities_Update", city.GenerateSqlParametersFromModel().ToArray());
}
private static async Task <SubscriptionResponse> ProcessSubscription(BitcornContext dbContext, SubscriptionResponse output, SubRequest subRequest, Subscription subInfo, SubscriptionTier requestedTierInfo, User user)
{
decimal cost = 0;
//if tier usdt cost has been initialized, the corn cost has to be calculated
if (requestedTierInfo.CostUsdt != null && requestedTierInfo.CostUsdt > 0)
{
cost = await CalculateUsdtToCornCost(dbContext, requestedTierInfo);
}
// check if cost is initialized properly
else if (requestedTierInfo.CostCorn != null && requestedTierInfo.CostCorn > 0)
{
cost = requestedTierInfo.CostCorn.Value;
}
else
{
throw new ArgumentException($"Invalid cost setting on subscription tier id:{requestedTierInfo.SubscriptionId}");
}
//set the amount that will be removed from subscriber to the response object
output.Cost = cost;
//initialize array of existing subscriptions
UserSubcriptionTierInfo[] existingSubscriptions = new UserSubcriptionTierInfo[0];
if (user != null)
{
//set data to existing subscriptions array
existingSubscriptions = await GetUserSubscriptions(dbContext, user)
.Where(t => t.SubscriptionTier.SubscriptionId == subInfo.SubscriptionId).ToArrayAsync();
}
//initialize reference to existing subtierinfo
UserSubcriptionTierInfo existingSubscription = null;
//initialize current substate
var subState = SubscriptionState.None;
//if any subscriptions were found
if (existingSubscriptions.Any())
{
//set existing subtierinfo
existingSubscription = existingSubscriptions[0];
//if sub has expired, set substate to expired
if (subInfo.HasExpired(existingSubscription.UserSubscription))
{
subState = SubscriptionState.Expired;
}
//if existing sub has not expired, but the tier is below, set subState to TierDown
else if (existingSubscription.SubscriptionTier.Tier < requestedTierInfo.Tier)
{
subState = SubscriptionState.TierDown;
}
//else, the user is subscribed
else
{
subState = SubscriptionState.Subscribed;
}
}
//initialize reference to usersubscription & tx request
UserSubscription sub = null;
TxRequest txRequest = null;
//if current user sub state is not subscribed & the client confirmed the cost to be equal to the cost amount, attempt to subscribe
var costDiff = Math.Abs(subRequest.Amount - cost);
if (subState != SubscriptionState.Subscribed && costDiff <= 100000)//subRequest.Amount == cost)
{
//initialize recipient of the transaction
string[] to = new string[1];
//default to bitcornhub if no subscription owner has been set
int recipientId = TxUtils.BitcornHubPK;
//if subscription owner is set, overwrite bitcornhub
if (subInfo.OwnerUserId != null && subInfo.OwnerUserId > 0)
{
recipientId = subInfo.OwnerUserId.Value;
}
to[0] = $"userid|{recipientId}";
//initialize tx request
txRequest = new TxRequest(user, cost, subRequest.Platform, "$sub", to);
//prepare transaction for saving
var processInfo = await TxUtils.ProcessRequest(txRequest, dbContext);
var transactions = processInfo.Transactions;
if (transactions != null && transactions.Length > 0)
{
StringBuilder sql = new StringBuilder();
//check if transaction can be executed
if (processInfo.WriteTransactionOutput(sql))
{
//transaction is ready to be saved
switch (subState)
{
case SubscriptionState.None:
//user was previously not subscribed, create instance of usersubscription and point it to the user
sub = new UserSubscription();
sub.SubscriptionId = subInfo.SubscriptionId;
sub.SubscriptionTierId = requestedTierInfo.SubscriptionTierId;
sub.UserId = user.UserId;
sub.FirstSubDate = DateTime.Now;
sub.SubCount = 1;
dbContext.UserSubscription.Add(sub);
break;
case SubscriptionState.TierDown:
case SubscriptionState.Expired:
//previous subscription was found, update subscription tier
existingSubscription.UserSubscription.SubscriptionTierId = requestedTierInfo.SubscriptionTierId;
existingSubscription.UserSubscription.SubCount += 1;
sub = existingSubscription.UserSubscription;
break;
default:
break;
}
//set subscription date to now
sub.LastSubDate = DateTime.Now;
await DbOperations.ExecuteSqlRawAsync(dbContext, sql.ToString());
await dbContext.SaveAsync(IsolationLevel.RepeatableRead);
//create subtx that will link user, corntx and usersubscription together
var subTx = new SubTx();
subTx.UserId = user.UserId;
subTx.SubTxId = transactions[0].TxId.Value;
subTx.UserSubscriptionId = sub.UserSubscriptionId;
dbContext.SubTx.Add(subTx);
//if user was not subscribed before, attempt to share the payment with a referrer
if (!await TrySharePaymentWithReferrer(dbContext, output, subRequest, subInfo, requestedTierInfo, user, recipientId, cost, subState, subTx))
{
await dbContext.SaveAsync();
}
subState = SubscriptionState.Subscribed;
}
//append receipt object with what client requested
await TxUtils.AppendTxs(transactions, dbContext, subRequest.Columns);
var tx = transactions[0];
output.TxId = tx.TxId;
output.User = tx.From;
}
}
//couldn't process transaction
if (txRequest == null)
{
//fill out response object
await PopuplateUserResponse(dbContext, subRequest, output, user);
if (existingSubscription != null)
{
sub = existingSubscription.UserSubscription;
}
}
if (subState == SubscriptionState.Subscribed && sub != null)
{
var end = output.SubscriptionEndTime = sub.LastSubDate.Value.AddDays(subInfo.Duration);
//calculate days left
output.DaysLeft = Math.Ceiling((end.Value - DateTime.Now).TotalDays);
//setup sub info
output.UserSubscriptionInfo = await GetUserSubscriptions(dbContext, user)
.Where(t => t.SubscriptionTier.SubscriptionId == subInfo.SubscriptionId).FirstOrDefaultAsync();
}
return(output);
}
public void Delete(Guid cityID)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.Cities_Delete", new SqlParameter("CityID", cityID));
}
public JoinGroup(DbOperations dbo)
{
this.Dbo = dbo;
}
public IEnumerable <City> ReadAll()
{
return(DbOperations.ExecuteQuery <City>(_context.CONNECTION_STRING, "dbo.Cities_ReadAll"));
}
public void Delete(string HospitalID)
{
DbOperations.ExecuteCommand(_context.CONNECTION_STRING, "dbo.Hospitals_Remove", new SqlParameter("HospitalID", HospitalID));
}
public City ReadByID(Guid cityID)
{
return(DbOperations.ExecuteQuery <City>(_context.CONNECTION_STRING, "dbo.Cities_ReadByID", new SqlParameter("CityID", cityID)).FirstOrDefault());
}
public Hospital ReadByID(string HospitalID)
{
return(DbOperations.ExecuteQuery <Hospital>(_context.CONNECTION_STRING, "dbo.Hospitals_ReadByID", new SqlParameter("HospitalID", HospitalID)).FirstOrDefault());
}
public City GetCityByName(string Name, Guid countryID)
{
return(DbOperations.ExecuteQuery <City>(_context.CONNECTION_STRING, "dbo.GetCityByName", new SqlParameter("Name", Name), new SqlParameter("CountryID", countryID)).FirstOrDefault());
}
public AccountController(DbOperations dbo)
{
this.Dbo = dbo;
}
public IEnumerable <City> GetCitiesByCountry(Guid countryID)
{
return(DbOperations.ExecuteQuery <City>(_context.CONNECTION_STRING, "dbo.GetCitiesByCountry", new SqlParameter("CountryID", countryID)));
}
/// <summary>
/// Creates a new instance of the manager using a DbOperations object
/// </summary>
/// <param name="db">The dbOperations object</param>
public MetadataCacheManagerSQL(DbOperations db)
{
_db = db;
}
public ActionResult Edit(int id)
{
EMPDATA E = DbOperations.GetEmpupdate(id);
return(View(E));
}
