public void RemoveFilter(Guid itemId, int userId, SyncReasons reason, int tenantId)
{
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
connection.Execute(@"merge into ServiceTickets_syncfilter as t
using (values(@Itemid, @Reason, @UserId, @TenantId)) as s(ItemId,AppliedRules,UserId,TenantId)
on t.itemId = s.ItemId and t.UserId = s.UserId and t.tenantId = s.TenantId
when matched then
update set t.appliedrules &= ~s.appliedrules, --(case when t.appliedrules & s.appliedrules = s.appliedrules then t.appliedrules^s.appliedrules else t.appliedrules end),
-- whenever the appliedrules value changes from >0 to 0 or vice versa, update the date
t.modified = (case when (t.appliedrules > 0 and t.appliedrules & ~s.appliedrules = 0)
then @@DBTS+1 else t.modified end),
-- whenever the appliedrules value changes to 0, setup the deleted datetime (so we can delete all ""deleted"" stuff after e.g. 3 months)
t.deletiontime = (case when (t.appliedrules > 0 and t.appliedrules & ~s.appliedrules = 0)
then getutcdate() else '1753-01-01' end)
when not matched then
insert (ItemId,Modified,AppliedRules,UserId,Tenantid,DeletionTime)
values (s.ItemId,@@DBTS+1,s.AppliedRules,s.UserId,s.TenantId, getUtcDate());" ,
new { ItemId = itemId, Reason = (int)reason, UserId = userId, TenantId = tenantId });
connection.Close();
}
}
public void Delete(ServiceTicket ticket)
{
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
connection.Execute("delete from ServiceTickets where ID = @ID", ticket);
connection.Close();
}
}
public void Update(ServiceTicket ticket)
{
var props = typeof(ServiceTicket).GetProperties()
.Where(p => !string.Equals(p.Name, "id", StringComparison.InvariantCultureIgnoreCase)).ToList();
var setter = string.Join(", ", props.Select(p => $"{p.Name}=@{p.Name}"));
var query = $"Update ServiceTickets set {setter} where ID = @ID";
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
connection.Execute(query, ticket);
connection.Close();
}
}
public IEnumerable <ServiceTicket> GetServiceTickets()
{
IEnumerable <ServiceTicket> principals;
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
principals = connection.Query <ServiceTicket>("select * from ServiceTickets").ToList();
connection.Close();
}
return(principals);
}
public IEnumerable <PrincipalEdge> GetPrincpialEdges()
{
IEnumerable <PrincipalEdge> principals;
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
principals = connection.Query <PrincipalEdge>("select * from PrincipalEdge").ToList();
connection.Close();
}
return(principals);
}
public void RemoveGroupMember(int groupId, int userId, int tenantId)
{
using (var connection = new SqlConnection(DbHelper.GetDatabaseConnectionString(_databaseName)))
{
connection.Open();
var cmd = new SqlCommand("[dbo].[RemoveEdgeWithSpaceSavingsPrincipal] @StartVertexId, @EndVertexId, @PrincipalType, @TenantId", connection);
cmd.Parameters.Add("@StartVertexId", SqlDbType.BigInt).Value = userId;
cmd.Parameters.Add("@EndVertexId", SqlDbType.BigInt).Value = groupId;
cmd.Parameters.Add("@PrincipalType", SqlDbType.TinyInt).Value = 1;
cmd.Parameters.Add("@TenantId", SqlDbType.Int).Value = tenantId;
cmd.ExecuteNonQuery();
connection.Close();
}
}
