public static void RemoveFeedItem(string id)
{
using var db = new DbManager(Constants.FeedDbId);
using var command = db.Connection.CreateCommand();
using var tx = db.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Transaction = tx;
command.CommandTimeout = 60 * 60; // a hour
var dialect = DbRegistry.GetSqlDialect(Constants.FeedDbId);
command.ExecuteNonQuery(new SqlDelete("feed_users").Where("feed_id", id), dialect);
command.ExecuteNonQuery(new SqlDelete("feed_aggregate").Where("id", id), dialect);
tx.Commit();
}
public static void RemoveFeedAggregate(DateTime fromTime)
{
using var db = new DbManager(Constants.FeedDbId);
using var command = db.Connection.CreateCommand();
using var tx = db.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Transaction = tx;
command.CommandTimeout = 60 * 60; // a hour
var dialect = DbRegistry.GetSqlDialect(Constants.FeedDbId);
if (dialect.SupportMultiTableUpdate)
{
command.ExecuteNonQuery("delete from feed_aggregate, feed_users using feed_aggregate, feed_users where id = feed_id and aggregated_date < @date", new { date = fromTime });
}
else
{
command.ExecuteNonQuery(new SqlDelete("feed_users").Where(Exp.In("feed_id", new SqlQuery("feed_aggregate").Select("id").Where(Exp.Lt("aggregated_date", fromTime)))), dialect);
command.ExecuteNonQuery(new SqlDelete("feed_aggregate").Where(Exp.Lt("aggregated_date", fromTime)), dialect);
}
tx.Commit();
}
protected SqlUpdate GetRecalculateFilesCountUpdate(object folderId)
{
if (DbRegistry.GetSqlDialect("default").SupportMultiTableUpdate)
{
return(new SqlUpdate("files_folder d, files_folder_tree t")
.Set(
"d.filesCount = (select count(distinct f.id) from files_file f, files_folder_tree t2 where f.tenant_id = d.tenant_id and f.folder_id = t2.folder_id and t2.parent_id = d.id)")
.Where(Exp.EqColumns("d.id", "t.parent_id") & Exp.Eq("t.folder_id", folderId) &
Exp.Eq("d.tenant_id", TenantID)));
}
else
{
return(new SqlUpdate("files_folder")
.Set(
"filesCount = (select count(distinct f.id) from files_file f, files_folder_tree t2 where f.tenant_id = files_folder.tenant_id and f.folder_id = t2.folder_id and t2.parent_id = files_folder.id)")
.Where(Exp.Eq("files_folder.tenant_id", TenantID) &
Exp.In("files_folder.id",
new SqlQuery("files_folder_tree t").Select("t.parent_id").Where("t.folder_id",
folderId))));
}
}
public override void HandleMessage(XmppStream stream, Message message, XmppHandlerContext context)
{
if (!message.HasTo || message.To.IsServer)
{
context.Sender.SendTo(stream, XmppStanzaError.ToServiceUnavailable(message));
return;
}
var sessions = context.SessionManager.GetBareJidSessions(message.To);
if (0 < sessions.Count)
{
foreach (var s in sessions)
{
try
{
context.Sender.SendTo(s, message);
}
catch
{
context.Sender.SendToAndClose(s.Stream, message);
}
}
}
else
{
pushStore = new DbPushStore();
var properties = new Dictionary <string, string>(1);
properties.Add("connectionStringName", "default");
pushStore.Configure(properties);
if (message.HasTag("active"))
{
var fromFullName = message.HasAttribute("username") ?
message.GetAttribute("username") : message.From.ToString();
var messageToDomain = message.To.ToString().Split(new char[] { '@' })[1];
var tenant = CoreContext.TenantManager.GetTenant(messageToDomain);
var tenantId = tenant != null ? tenant.TenantId : -1;
var userPushList = new List <UserPushInfo>();
userPushList = pushStore.GetUserEndpoint(message.To.ToString().Split(new char[] { '@' })[0]);
var firebaseAuthorization = "";
try
{
CallContext.SetData(TenantManager.CURRENT_TENANT, new Tenant(tenantId, ""));
firebaseAuthorization = FireBase.Instance.Authorization;
}
catch (Exception exp)
{
log.DebugFormat("firebaseAuthorizationERROR: {0}", exp);
}
foreach (var user in userPushList)
{
try
{
var from = message.From.ToString().Split(new char[] { '@' })[0];
List <string> userId;
string photoPath = "";
using (var db = new DbManager("core"))
using (var command = db.Connection.CreateCommand())
{
var q = new SqlQuery("core_user").Select("id").Where(Exp.Like("username", from)).Where("tenant", tenantId);
userId = command.ExecuteList(q, DbRegistry.GetSqlDialect(db.DatabaseId))
.ConvertAll(r => Convert.ToString(r[0]))
.ToList();
}
if (userId.Count != 0)
{
var guid = new Guid(userId[0]);
photoPath = UserPhotoManager.GetBigPhotoURL(guid);
}
var tRequest = WebRequest.Create("https://fcm.googleapis.com/fcm/send");
tRequest.Method = "post";
tRequest.ContentType = "application/json";
var data = new
{
to = user.endpoint,
data = new
{
msg = message.Body,
fromFullName = fromFullName,
photoPath = photoPath
}
};
var serializer = new JavaScriptSerializer();
var json = serializer.Serialize(data);
var byteArray = Encoding.UTF8.GetBytes(json);
tRequest.Headers.Add(string.Format("Authorization: key={0}", firebaseAuthorization));
tRequest.ContentLength = byteArray.Length;
using (var dataStream = tRequest.GetRequestStream())
{
dataStream.Write(byteArray, 0, byteArray.Length);
using (var tResponse = tRequest.GetResponse())
{
using (var dataStreamResponse = tResponse.GetResponseStream())
{
using (var tReader = new StreamReader(dataStreamResponse))
{
var sResponseFromServer = tReader.ReadToEnd();
var str = sResponseFromServer;
}
}
}
}
}
catch (Exception ex)
{
var str = ex.Message;
log.DebugFormat("PushRequestERROR: {0}", str);
}
}
}
StoreOffline(message, context.StorageManager.OfflineStorage);
}
}
public void SendBirthdayReminders(DateTime scheduleDate)
{
try
{
scheduleDate = scheduleDate.AddDays(1);
List <Tenant> tenants;
using (var db = new DbManager("core"))
using (var command = db.Connection.CreateCommand())
{
command.CommandTimeout = 30 * 10;
var q = new SqlQuery("core_user")
.Select("tenant")
.Where(!Exp.Eq("bithdate", null))
.Where("date_format(bithdate, '%m%d')", scheduleDate.ToString("MMdd"))
.Where("removed", 0)
.Where("status", EmployeeStatus.Active)
.GroupBy(1);
tenants = command.ExecuteList(q, DbRegistry.GetSqlDialect(db.DatabaseId))
.ConvertAll(r => Convert.ToInt32(r[0]))
.Select(id => CoreContext.TenantManager.GetTenant(id))
.ToList();
}
foreach (var tenant in tenants)
{
if (tenant == null ||
tenant.Status != TenantStatus.Active ||
TariffState.NotPaid <= CoreContext.PaymentManager.GetTariff(tenant.TenantId).State)
{
continue;
}
CoreContext.TenantManager.SetCurrentTenant(tenant);
if (!WebItemSecurity.GetSecurityInfo(WebItemManager.CommunityProductID.ToString()).Enabled ||
!WebItemSecurity.GetSecurityInfo(WebItemManager.BirthdaysProductID.ToString()).Enabled)
{
continue;
}
var users = CoreContext.UserManager.GetUsers(EmployeeStatus.Active, EmployeeType.User);
var birthdays = users.Where(u => u.BirthDate.HasValue && u.BirthDate.Value.Month == scheduleDate.Month && u.BirthDate.Value.Day == scheduleDate.Day);
var subscriptionProvider = NotifySource.GetSubscriptionProvider();
foreach (var user in users)
{
if (WebItemManager.Instance[WebItemManager.CommunityProductID].IsDisabled(user.ID))
{
continue;
}
var allSubscription = subscriptionProvider.IsSubscribed(Event_BirthdayReminder, user, null);
foreach (var birthday in birthdays)
{
if (user.Equals(birthday))
{
continue;
}
if ((allSubscription && !subscriptionProvider.IsUnsubscribe(user, Event_BirthdayReminder, birthday.ID.ToString())) ||
(!allSubscription && subscriptionProvider.IsSubscribed(Event_BirthdayReminder, user, birthday.ID.ToString())))
{
client.SendNoticeToAsync(
Event_BirthdayReminder,
birthday.ID.ToString(),
new[] { user },
true,
new TagValue("BirthdayUserName", birthday.DisplayUserName(false)),
new TagValue("BirthdayUserUrl", CommonLinkUtility.GetUserProfile(birthday.ID)),
new TagValue("BirthdayDate", birthday.BirthDate.Value.ToShortDayMonth()),
new TagValue(CommonTags.Priority, 1));
}
}
}
}
}
catch (Exception ex)
{
LogManager.GetLogger("ASC").Error(ex);
}
}
private static List <UserActivity> GetUserActivities(int tenant, Guid?userOrDept, Guid product, IEnumerable <Guid> modules, int action, IEnumerable <string> containers, DateTime from, DateTime to, int offset, int count)
{
// HACK: optimize sql query for MySql Server 5.1
var table = "webstudio_useractivity" + (userOrDept.GetValueOrDefault() != default(Guid) && DbRegistry.GetSqlDialect(dbid).GetType().Name == "MySQLDialect" ? " use index (productid)" : string.Empty);
var q = new SqlQuery(table)
.Select("id", "tenantid", "productid", "moduleid", "userid", "contentid", "containerid")
.Select("actiontype", "actiontext", "businessvalue", "additionaldata", "activitydate")
.Select("url", "title", "partid", "imagefilename", "htmlpreview", "securityid")
.Where("tenantid", tenant);
//userid
if (userOrDept.GetValueOrDefault() != default(Guid))
{
if (CoreContext.UserManager.UserExists(userOrDept.Value))
{
q.Where("userid", userOrDept.Value.ToString());
}
else
{
q.Where(Exp.In("userid", CoreContext.UserManager.GetUsersByGroup(userOrDept.Value).Select(u => u.ID.ToString()).ToArray()));
}
}
//productId
if (product != default(Guid))
{
q.Where("productid", product.ToString());
}
//moduleIds
if (modules != null && modules.Any())
{
q.Where(Exp.In("moduleid", modules.Select(m => m.ToString()).ToArray()));
}
//actionType
if (action != UserActivityConstants.AllActionType)
{
q.Where("actiontype", action);
}
//containerIds
if (containers != null && containers.Any())
{
q.Where(Exp.In("containerid", containers.ToArray()));
}
//dates
if (from != DateTime.MinValue)
{
q.Where(Exp.Ge("activitydate", TenantUtil.DateTimeToUtc(from).Date));
}
if (to != DateTime.MaxValue)
{
q.Where(Exp.Le("activitydate", TenantUtil.DateTimeToUtc(to).Date.AddTicks(TimeSpan.TicksPerDay - 1)));
}
//limits
if (0 < offset)
{
q.SetFirstResult(offset);
}
if (0 < count)
{
q.SetMaxResults(count);
}
q.OrderBy("id", false);
var key = BuildKey(q);
var result = cache.Get(key) as List <UserActivity>;
if (result == null)
{
using (var db = GetDbManager())
{
result = GetActivities(db, q, count);
lock (cache)
{
var depkey = BuildDependencyKey(tenant, product);
if (cache.Get(depkey) == null)
{
cache.Insert(depkey, depkey);
}
cache.Insert(key, result, new CacheDependency(null, new[] { depkey }), DateTime.Now.Add(expiration), Cache.NoSlidingExpiration);
}
}
}
return(result);
}
public static List <UserActivity> GetProjectsActivities(int tenantId, Guid productId, Guid?userId, int projectId, string type, string searchText, DateTime from, DateTime to, int offset, int count, int lastId, string sortBy, bool sortOrder)
{
// HACK: optimize sql query for MySql Server 5.1
var table = "webstudio_useractivity" + (userId.GetValueOrDefault() != default(Guid) && DbRegistry.GetSqlDialect(dbid).GetType().Name == "MySQLDialect" ? " use index (productid)" : string.Empty);
var query = new SqlQuery(table)
.Select("id", "tenantid", "productid", "moduleid", "userid", "contentid", "containerid")
.Select("actiontype", "actiontext", "businessvalue", "additionaldata", "activitydate")
.Select("url", "title", "partid", "imagefilename", "htmlpreview", "securityid")
.Where("productid", productId.ToString())
.Where("moduleid", productId.ToString())
.Where("tenantid", tenantId);
if (projectId != 0)
{
query.Where("containerid", projectId);
}
if (userId != default(Guid))
{
query.Where("userid", userId);
}
if (!string.IsNullOrEmpty(type))
{
query.Where(Exp.Like("additionaldata", type, SqlLike.StartWith));
}
if (from != DateTime.MinValue && from != DateTime.MaxValue)
{
query.Where(Exp.Ge("activitydate", TenantUtil.DateTimeFromUtc(from).Date));
}
if (to != DateTime.MinValue && to != DateTime.MaxValue)
{
query.Where(Exp.Le("activitydate", TenantUtil.DateTimeFromUtc(to).Date.AddTicks(TimeSpan.TicksPerDay - 1)));
}
if (!string.IsNullOrEmpty(searchText))
{
query.Where(Exp.Like("title", searchText, SqlLike.AnyWhere));
}
if (count > 0 && count < 150000)
{
query.SetFirstResult(offset);
query.SetMaxResults(count * 2);
}
query.OrderBy(sortBy, sortOrder);
var key = BuildKey(query);
var result = cache.Get(key) as List <UserActivity>;
if (result == null)
{
using (var db = GetDbManager())
{
result = GetActivities(db, query, count * 2);
lock (cache)
{
var depkey = BuildDependencyKey(tenantId, productId);
if (cache.Get(depkey) == null)
{
cache.Insert(depkey, depkey);
}
cache.Insert(key, result, new CacheDependency(null, new[] { depkey }), DateTime.Now.Add(expiration), Cache.NoSlidingExpiration);
}
}
}
return(result);
}
