public Event UpdateEvent(int eventId, int calendarId, Guid ownerId, string name, string description,
DateTime utcStartDate, DateTime utcEndDate, RecurrenceRule rrule, EventAlertType alertType, bool isAllDayLong, List<ASC.Web.Core.Calendars.SharingOptions.PublicItem> publicItems)
{
using (var tr = DbManager.BeginTransaction())
{
var query = new SqlUpdate("calendar_events")
.Set("name", name)
.Set("description", description)
.Set("calendar_id", calendarId)
.Set("owner_id", ownerId)
.Set("start_date", utcStartDate.ToString("yyyy-MM-dd HH:mm:ss"))
.Set("end_date", utcEndDate.ToString("yyyy-MM-dd HH:mm:ss"))
.Set("all_day_long", isAllDayLong)
.Set("rrule", rrule.ToString())
.Where(Exp.Eq("id", eventId));
if (ownerId.Equals(SecurityContext.CurrentAccount.ID))
query = query.Set("alert_type", (int)alertType);
else
DbManager.ExecuteNonQuery(new SqlInsert("calendar_event_user", true).InColumnValue("event_id", eventId)
.InColumnValue("user_id", SecurityContext.CurrentAccount.ID)
.InColumnValue("alert_type", alertType));
DbManager.ExecuteNonQuery(query);
var userIds = DbManager.ExecuteList(new SqlQuery("calendar_event_user").Select("user_id").Where("event_id", eventId)).Select(r => new Guid(Convert.ToString(r[0])));
foreach (var usrId in userIds)
{
if (!publicItems.Exists(i => (i.IsGroup && CoreContext.UserManager.IsUserInGroup(usrId, i.Id))
|| (!i.IsGroup && i.Id.Equals(usrId))))
{
DbManager.ExecuteNonQuery(new SqlDelete("calendar_event_user").Where(Exp.Eq("user_id", usrId) & Exp.Eq("event_id", eventId)));
}
}
DbManager.ExecuteNonQuery(new SqlDelete("calendar_event_item").Where("event_id", eventId));
foreach (var item in publicItems)
{
DbManager.ExecuteNonQuery(new SqlInsert("calendar_event_item")
.InColumnValue("event_id", eventId)
.InColumnValue("item_id", item.Id)
.InColumnValue("is_group", item.IsGroup));
}
//update notifications
var baseAlertType = DbManager.ExecuteList(new SqlQuery("calendar_events").Select("alert_type").Where("id", eventId))
.Select(r => (EventAlertType)Convert.ToInt32(r[0])).First();
UpdateEventNotifications(eventId, calendarId, utcStartDate, baseAlertType, rrule, publicItems, null);
tr.Commit();
}
return GetEventById(eventId);
}
private void UpdateEventNotifications(int eventId, int calendarId, DateTime eventUtcStartDate, EventAlertType baseEventAlertType, RecurrenceRule rrule,
IEnumerable<ASC.Web.Core.Calendars.SharingOptions.PublicItem> eventPublicItems,
IEnumerable<ASC.Web.Core.Calendars.SharingOptions.PublicItem> calendarPublicItems)
{
var cc = new ColumnCollection();
var userIdCol = cc.RegistryColumn("user_id");
var alertTypeCol = cc.RegistryColumn("alert_type");
var isUnsubscribeCol = cc.RegistryColumn("is_unsubscribe");
var eventUsersData = DbManager.ExecuteList(new SqlQuery("calendar_event_user").Select(cc.SelectQuery).Where(Exp.Eq("event_id", eventId)));
var calendarData = DbManager.ExecuteList(new SqlQuery("calendar_calendars").Select("alert_type", "owner_id", "time_zone").Where(Exp.Eq("id", calendarId)));
var calendarAlertType = calendarData.Select(r => (EventAlertType)Convert.ToInt32(r[0])).First();
Guid calendarOwner = calendarData.Select(r => new Guid(Convert.ToString(r[1]))).First();
TimeZoneInfo calendarTimeZone = calendarData.Select(r => TimeZoneConverter.GetTimeZone(Convert.ToString(r[2]))).First();
List<UserAlertType> eventUsers = new List<UserAlertType>();
#region shared event's data
if (eventPublicItems == null)
{
eventPublicItems = new List<SharingOptions.PublicItem>(DbManager.ExecuteList(new SqlQuery("calendar_event_item").Select("item_id", "is_group").Where(Exp.Eq("event_id", eventId)))
.Select(r => new ASC.Web.Core.Calendars.SharingOptions.PublicItem() { Id = new Guid(Convert.ToString(r[0])), IsGroup = Convert.ToBoolean(r[1]) }));
}
foreach (var item in eventPublicItems)
{
if (item.IsGroup)
eventUsers.AddRange(CoreContext.UserManager.GetUsersByGroup(item.Id).Select(u => new UserAlertType(u.ID, baseEventAlertType, calendarTimeZone)));
else
eventUsers.Add(new UserAlertType(item.Id, baseEventAlertType, calendarTimeZone));
}
//remove calendar owner
eventUsers.RemoveAll(u => u.UserId.Equals(calendarOwner));
//remove unsubscribed and exec personal alert_type
if (eventUsers.Count > 0)
{
foreach (var r in eventUsersData)
{
if (isUnsubscribeCol.Parse<bool>(r))
eventUsers.RemoveAll(u => u.UserId.Equals(userIdCol.Parse<Guid>(r)));
else
eventUsers.ForEach(u =>
{
if (u.UserId.Equals(userIdCol.Parse<Guid>(r)))
u.AlertType = (EventAlertType)alertTypeCol.Parse<int>(r);
});
}
}
//remove and exec sharing calendar options
if (eventUsers.Count > 0)
{
var extCalendarAlertTypes = DbManager.ExecuteList(new SqlQuery("calendar_calendar_user cu")
.Select("cu.user_id", "cu.alert_type", "cu.is_accepted", "cu.time_zone")
.Where(Exp.Eq("cu.ext_calendar_id", SharedEventsCalendar.CalendarId) & Exp.In("cu.user_id", eventUsers.Select(u => u.UserId).ToArray())));
foreach (var r in extCalendarAlertTypes)
{
if (!Convert.ToBoolean(r[2]))
{
//remove unsubscribed from shared events calendar
eventUsers.RemoveAll(u => u.UserId.Equals(new Guid(Convert.ToString(r[0]))));
continue;
}
eventUsers.ForEach(u =>
{
if (u.UserId.Equals(new Guid(Convert.ToString(r[0]))))
u.TimeZone = ((r[3] == null || r[3] == DBNull.Value) ? calendarTimeZone : TimeZoneConverter.GetTimeZone(Convert.ToString(r[3])));
if (u.AlertType == EventAlertType.Default && u.UserId.Equals(new Guid(Convert.ToString(r[0]))))
u.AlertType = (EventAlertType)Convert.ToInt32(r[1]);
});
};
eventUsers.ForEach(u =>
{
if (u.AlertType == EventAlertType.Default)
u.AlertType = EventAlertType.Hour;
});
}
#endregion
#region calendar's data
if (calendarPublicItems == null)
{
calendarPublicItems = new List<SharingOptions.PublicItem>(DbManager.ExecuteList(new SqlQuery("calendar_calendar_item").Select("item_id", "is_group").Where(Exp.Eq("calendar_id", calendarId)))
.Select(r => new ASC.Web.Core.Calendars.SharingOptions.PublicItem() { Id = new Guid(Convert.ToString(r[0])), IsGroup = Convert.ToBoolean(r[1]) }));
}
//calendar users
List<UserAlertType> calendarUsers = new List<UserAlertType>();
foreach (var item in eventPublicItems)
{
if (item.IsGroup)
calendarUsers.AddRange(CoreContext.UserManager.GetUsersByGroup(item.Id).Select(u => new UserAlertType(u.ID, baseEventAlertType, calendarTimeZone)));
else
calendarUsers.Add(new UserAlertType(item.Id, baseEventAlertType, calendarTimeZone));
}
calendarUsers.Add(new UserAlertType(calendarOwner, baseEventAlertType, calendarTimeZone));
//remove event's users
calendarUsers.RemoveAll(u => eventUsers.Exists(eu => eu.UserId.Equals(u.UserId)));
//calendar options
if (calendarUsers.Count > 0)
{
//set personal alert_type
foreach (var r in eventUsersData)
{
eventUsers.ForEach(u =>
{
if (u.UserId.Equals(new Guid(Convert.ToString(r[0]))))
u.AlertType = (EventAlertType)(Convert.ToInt32(r[1]));
});
}
var calendarAlertTypes = DbManager.ExecuteList(new SqlQuery("calendar_calendar_user")
.Select("user_id", "alert_type", "is_accepted", "time_zone")
.Where(Exp.Eq("calendar_id", calendarId) & Exp.In("user_id", calendarUsers.Select(u => u.UserId).ToArray())));
foreach (var r in calendarAlertTypes)
{
if (!Convert.ToBoolean(r[2]))
{
//remove unsubscribed
calendarUsers.RemoveAll(u => u.UserId.Equals(new Guid(Convert.ToString(r[0]))));
continue;
}
calendarUsers.ForEach(u =>
{
if (u.UserId.Equals(new Guid(Convert.ToString(r[0]))))
u.TimeZone = ((r[3] == null || r[3] == DBNull.Value) ? calendarTimeZone : TimeZoneConverter.GetTimeZone(Convert.ToString(r[3])));
if (u.AlertType == EventAlertType.Default && u.UserId.Equals(new Guid(Convert.ToString(r[0]))))
u.AlertType = (EventAlertType)Convert.ToInt32(r[1]);
});
};
calendarUsers.ForEach(u =>
{
if (u.AlertType == EventAlertType.Default)
u.AlertType = calendarAlertType;
});
}
#endregion
//clear notifications
DbManager.ExecuteNonQuery(new SqlDelete("calendar_notifications").Where("event_id", eventId));
eventUsers.AddRange(calendarUsers);
foreach (var u in eventUsers)
{
//todo: recount
var alertDate = GetNextAlertDate(eventUtcStartDate, rrule, u.AlertType, u.TimeZone);
if (!alertDate.Equals(DateTime.MinValue))
{
DbManager.ExecuteNonQuery(new SqlInsert("calendar_notifications", true).InColumnValue("user_id", u.UserId)
.InColumnValue("event_id", eventId)
.InColumnValue("rrule", rrule.ToString())
.InColumnValue("alert_type", (int)u.AlertType)
.InColumnValue("tenant", CoreContext.TenantManager.GetCurrentTenant().TenantId)
.InColumnValue("notify_date", alertDate)
.InColumnValue("time_zone", u.TimeZone.Id));
}
}
}
public Event CreateEvent(int calendarId, Guid ownerId, string name, string description,
DateTime utcStartDate, DateTime utcEndDate, RecurrenceRule rrule, EventAlertType alertType, bool isAllDayLong, List<ASC.Web.Core.Calendars.SharingOptions.PublicItem> publicItems)
{
int eventId = 0;
using (var tr = DbManager.BeginTransaction())
{
eventId = DbManager.ExecuteScalar<int>(new SqlInsert("calendar_events")
.InColumnValue("id", 0)
.InColumnValue("tenant", CoreContext.TenantManager.GetCurrentTenant().TenantId)
.InColumnValue("name", name)
.InColumnValue("description", description)
.InColumnValue("calendar_id", calendarId)
.InColumnValue("owner_id", ownerId)
.InColumnValue("start_date", utcStartDate.ToString("yyyy-MM-dd HH:mm:ss"))
.InColumnValue("end_date", utcEndDate.ToString("yyyy-MM-dd HH:mm:ss"))
.InColumnValue("all_day_long", isAllDayLong)
.InColumnValue("rrule", rrule.ToString())
.InColumnValue("alert_type", (int)alertType)
.Identity<int>(0, 0, true));
foreach (var item in publicItems)
{
DbManager.ExecuteNonQuery(new SqlInsert("calendar_event_item")
.InColumnValue("event_id", eventId)
.InColumnValue("item_id", item.Id)
.InColumnValue("is_group", item.IsGroup));
}
//update notifications
UpdateEventNotifications(eventId, calendarId, utcStartDate, alertType, rrule, publicItems, null);
tr.Commit();
}
return GetEventById(eventId);
}