public dynamic GetAnnouncementsByFacility(string facilityName, string timeZoneId) { SimpleLogger logger = new SimpleLogger(AppDomain.CurrentDomain.BaseDirectory + string.Format(@"/log/log_{0}.txt", DateTime.Now.ToString("yyyy_MM_dd"))); ReturnResult result = new ReturnResult(); try { var localDateTimeNow = DatetimeHelper.ConvertUTCtoLocal(DateTime.UtcNow, timeZoneId); var qAnnPosts = from post in Context.wp_posts join meta in Context.wp_postmeta on post.id equals meta.post_id where post.post_type == Constants.ANNOUNCEMENT_POST_TYPE && post.post_status != Constants.ANNOUNCEMENT_META_KEY_TRASH select post; // MySQL do not support MARS(Multiple Active Result Set) so call toList() first var annIDs = qAnnPosts.Select(r => r.id).Distinct().ToList(); //RapidLogger.Trace("announcements",string.Format("all announcement ids: {0}", string.Join(",", annIDs))); //Context.Database.Log = s => RapidLogger.Trace("EF log :", s); StringBuilder sb = new StringBuilder(); sb.Append(" SELECT meta_id, post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id in ( "); sb.Append(string.Join(",", annIDs)); sb.Append(" )"); //RapidLogger.Trace("EF log :", sb.ToString()); var annMetas = Context.Database.SqlQuery <wp_postmeta_entity>(sb.ToString()).ToList(); IList <AnnouncementDTO> annList = new List <AnnouncementDTO>(); foreach (var id in annIDs) { var annMeta = annMetas.Where(p => p.post_id == id).ToList(); var facilityId = Convert.ToInt32(annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_FACILITY_NAME).meta_value); var facilityMetas = Context.wp_postmeta.Where(r => r.post_id == facilityId).ToList(); annList.Add(new AnnouncementDTO() { FacilityName = facilityMetas.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_FACILITY_NAME).meta_value, StartDate = DatetimeHelper.ConvertUTCtoLocal(Convert.ToDateTime(annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_START_DATE).meta_value), timeZoneId), EndDate = annMeta.Select(p => p.meta_key).Contains(Constants.ANNOUNCEMENT_META_KEY_END_DATE) ? annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_END_DATE).meta_value.Equals(Constants.TIME_FORMAT_ZERO_DATETIME) ? DateTime.MaxValue : DatetimeHelper.ConvertUTCtoLocal(Convert.ToDateTime(annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_END_DATE).meta_value), timeZoneId) : DateTime.MaxValue, AnnouncementDesc = annMeta.Select(p => p.meta_key).Contains(Constants.ANNOUNCEMENT_META_KEY_ANNOUNCEMENT_DESCRIPTION) ? annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_ANNOUNCEMENT_DESCRIPTION).meta_value : string.Empty, Priority = annMeta.Select(p => p.meta_key).Contains(Constants.ANNOUNCEMENT_META_KEY_PRIORITY) ? annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_PRIORITY).meta_value : string.Empty, IsActive = annMeta.Select(p => p.meta_key).Contains(Constants.ANNOUNCEMENT_META_KEY_ISACTIVE) ? annMeta.FirstOrDefault(p => p.meta_key == Constants.ANNOUNCEMENT_META_KEY_ISACTIVE).meta_value.Equals(Constants.ANNOUNCEMENT_META_KEY_ISACTIVE_FALSE) ? false : true : false }); } var qAnnContent = annList.Where(r => r.FacilityName == facilityName && r.IsActive == true); //var annContent = qAnnContent.Where(r => r.StartDate <= localDateTimeNow && r.EndDate >= localDateTimeNow).ToList(); var annContent = qAnnContent.Where(r => r.StartDate.Date <= localDateTimeNow.Date && (r.EndDate == null || (r.EndDate != null && r.EndDate.Date >= localDateTimeNow.Date))).ToList(); var annContentList = new List <dynamic>(); var annResultList = new List <dynamic>(); foreach (var ann in annContent) { annContentList.Add(new { AnnouncementDesc = ann.AnnouncementDesc, Priority = ann.Priority, EffectiveStartDT = ann.StartDate.ToString(Constants.TIME_FORMAT_DATE_TIME_12H), EffectiveEndDate = ann.EndDate.ToString(Constants.TIME_FORMAT_DATE_TIME_12H) }); } annResultList.Add(new { FacilityName = facilityName, Date = localDateTimeNow.ToString(Constants.TIME_FORMAT_DATE), Announcements = annContentList }); result.ReturnCode = ReturnStatusCode.Succeed; result.ReturnMessage = Constants.STATUS_SUCCESS; result.Content = annResultList; } catch (Exception ex) { result.ReturnCode = ReturnStatusCode.Failed; result.ReturnMessage = ex.StackTrace + " and " + ex.InnerException.StackTrace; result.Content = null; } return(result); }
public dynamic getSessionsByFacility(string facilityName, string timeZoneId) { ReturnResult result = new ReturnResult(); try { //var localDateTimeNow = DatetimeHelper.ConvertUTCtoLocal(DateTime.Parse("2016/12/02 11:15:00"), timeZoneId); var localDateTimeNow = DatetimeHelper.ConvertUTCtoLocal(DateTime.UtcNow, timeZoneId); var localDateNow = localDateTimeNow.Date; var localDateTommorow = localDateTimeNow.Date.AddDays(1); // TODO: CUICK ADD START DateTime currentLocalTime = DatetimeHelper.ConvertUTCtoLocal(DateTime.UtcNow, timeZoneId); // TODO: CUICK ADD END var qSessions = from dst in Context.wp_dst where dst.FacilityName == facilityName && dst.DoNotDisplay == false select dst; var sessionList = qSessions.Distinct().ToList(); foreach (var session in sessionList) { session.SessionStartDT = DatetimeHelper.ConvertUTCtoLocal(session.SessionStartDT, timeZoneId); session.SessionEndDT = DatetimeHelper.ConvertUTCtoLocal(session.SessionEndDT, timeZoneId); } // TODO: CUICK REPLACE START //var sessionAllList = sessionList.Where(r => r.SessionStartDT >= localDateNow && r.SessionEndDT <= localDateTommorow).OrderBy(r => r.SessionStartDT).ToList(); var sessionAllList = sessionList.Where(r => r.SessionEndDT >= currentLocalTime && (r.SessionStartDT.Date <= currentLocalTime.Date && r.SessionEndDT.Date >= currentLocalTime.Date)).OrderBy(r => r.SessionStartDT).ToList(); // TODO: CUICK REPLACE END var sessionNames = sessionAllList.GroupBy(r => r.SessionName).Distinct().ToList(); var sessionContentList = new List <dynamic>(); foreach (var name in sessionNames) { var sessionByName = sessionAllList.Where(r => r.SessionName == name.Key).OrderBy(r => r.SessionStartDT).ToList(); var sessionDetailList = new List <SessionByFacilityDTO>(); var sum = 0; if (sessionByName.Any()) { foreach (var session in sessionByName) { sum++; sessionDetailList.Add(new SessionByFacilityDTO { OrderID = sum.ToString(), VenueName = session.VenueName, StartDT = session.SessionStartDT.ToString(Constants.TIME_FORMAT_DATE_TIME_12H), EndDate = session.SessionEndDT.ToString(Constants.TIME_FORMAT_DATE_TIME_12H), LocationName = session.LocationName }); } sessionContentList.Add(new { SessionName = name.Key, SessionDetails = sessionDetailList }); } } var sessionResultList = new List <dynamic>(); sessionResultList.Add(new { FacilityName = facilityName, // TODO: CUICK REPLACE START //Date = localDateTimeNow.ToString(Constants.TIME_FORMAT_DATE), Date = currentLocalTime.ToString(Constants.TIME_FORMAT_DATE), // TODO: CUICK REPLACE END DisplayValues = sessionContentList }); result.ReturnCode = ReturnStatusCode.Succeed; result.ReturnMessage = Constants.STATUS_SUCCESS; result.Content = sessionResultList; } catch (Exception ex) { result.ReturnCode = ReturnStatusCode.Failed; result.ReturnMessage = ex.Message; result.Content = null; } return(result); }