/// <summary> /// Select the events which are applicable since today, limit by a count. /// </summary> /// <param name="dt"></param> /// <param name="lang"></param> /// <param name="count"></param> /// <returns></returns> public List <EventCalendarDetailsData> fetchUpcomingEventDetails(DateTime dt, CmsLanguage lang, int count) { StringBuilder sql = new StringBuilder("SELECT E.PageId,E.Identifier,E.LangCode,E.Description,E.CategoryId,E.StartDateTime,E.EndDateTime,E.CreatedBy FROM "); sql.Append(TableNameDetails + " E, pages P"); sql.Append(" WHERE E.EndDateTime >= " + dbEncode(dt)); sql.Append(" AND E.LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND E.Deleted IS NULL"); sql.Append(" AND P.Deleted IS NULL"); sql.Append(" AND E.PageId=P.PageId"); sql.Append(" ORDER BY E.StartDateTime, E.EndDateTime"); sql.Append(" LIMIT 0, " + count + ";"); DataSet ds = this.RunSelectQuery(sql.ToString()); List <EventCalendarDetailsData> list = new List <EventCalendarDetailsData>(); if (this.hasRows(ds)) { foreach (DataRow dr in ds.Tables[0].Rows) { EventCalendarDetailsData entity = new EventCalendarDetailsData(); rowToData(dr, entity); list.Add(entity); } } return(list); }
/// <summary> /// Select events which between the start/end date time values provided. /// </summary> /// <param name="strDateTime"></param> /// <param name="endDateTime"></param> /// <param name="lang"></param> /// <returns></returns> public List <EventCalendarDetailsData> fetchDetailsDataByRange(DateTime strDateTime, DateTime endDateTime, CmsLanguage lang) { StringBuilder sql = new StringBuilder("SELECT E.PageId,E.Identifier,E.LangCode,E.Description,E.CategoryId,E.StartDateTime,E.EndDateTime,E.CreatedBy FROM "); sql.Append(TableNameDetails + " E, pages P"); sql.Append(" WHERE ((E.EndDateTime>=" + dbEncode(strDateTime)); sql.Append(" AND E.EndDateTime<=" + dbEncode(endDateTime) + ")"); sql.Append(" OR (E.StartDateTime>=" + dbEncode(strDateTime)); sql.Append(" AND E.StartDateTime<=" + dbEncode(endDateTime) + "))"); sql.Append(" AND E.LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND E.Deleted IS NULL"); sql.Append(" AND P.Deleted IS NULL"); sql.Append(" AND E.PageId=P.PageId"); sql.Append(" ORDER BY E.StartDateTime, E.EndDateTime;"); List <EventCalendarDetailsData> list = new List <EventCalendarDetailsData>(); DataSet ds = this.RunSelectQuery(sql.ToString()); if (this.hasRows(ds) == false) { return(list); } foreach (DataRow dr in ds.Tables[0].Rows) { EventCalendarDetailsData entity = new EventCalendarDetailsData(); rowToData(dr, entity); list.Add(entity); } return(list); }
protected void rowToData(DataRow dr, EventCalendarDetailsData entity) { entity.PageId = Convert.ToInt32(dr["PageId"]); entity.Identifier = Convert.ToInt32(dr["Identifier"]); entity.Lang = new CmsLanguage(dr["LangCode"].ToString()); entity.Description = dr["Description"].ToString(); entity.CategoryId = Convert.ToInt32(dr["CategoryId"]); entity.StartDateTime = Convert.ToDateTime(dr["StartDateTime"]); entity.EndDateTime = Convert.ToDateTime(dr["EndDateTime"]); entity.CreatedBy = dr["CreatedBy"].ToString(); }
/// <summary> /// Select the event details by language limit by a count. If an valid attached event id is provided, /// the method selects the event details as well (number of records is "count + 1" in this case). /// </summary> /// <param name="lang"></param> /// <param name="attachedEventId"></param> /// <param name="count"></param> /// <returns></returns> public List <EventCalendarDetailsData> fetchAllDetailsData(CmsLanguage lang, int attachedEventId, int count) { StringBuilder sql = new StringBuilder("(SELECT E.PageId,E.Identifier,E.LangCode,E.Description,E.CategoryId,E.StartDateTime,E.EndDateTime,E.CreatedBy FROM "); sql.Append(TableNameDetails + " E, pages P"); sql.Append(" WHERE E.Deleted IS NULL"); sql.Append(" AND E.LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND P.Deleted IS NULL"); sql.Append(" AND E.PageId=P.PageId"); sql.Append(" ORDER BY E.StartDateTime DESC, E.EndDateTime DESC"); if (count > -1) { sql.Append(" LIMIT 0, " + count.ToString() + ")"); sql.Append(" UNION (SELECT E.PageId,E.Identifier,E.LangCode,E.Description,E.CategoryId,E.StartDateTime,E.EndDateTime,E.CreatedBy FROM "); sql.Append(TableNameDetails + " E, pages P"); sql.Append(" WHERE E.Deleted IS NULL"); sql.Append(" AND E.LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND P.Deleted IS NULL"); sql.Append(" AND E.PageId=P.PageId"); sql.Append(" AND E.PageId=" + attachedEventId + ");"); } else { sql.Append(");"); } List <EventCalendarDetailsData> list = new List <EventCalendarDetailsData>(); DataSet ds = this.RunSelectQuery(sql.ToString()); if (this.hasRows(ds) == false) { return(list); } foreach (DataRow dr in ds.Tables[0].Rows) { EventCalendarDetailsData entity = new EventCalendarDetailsData(); rowToData(dr, entity); list.Add(entity); } return(list); }
/// <summary> /// Select a single event details data /// </summary> /// <param name="page"></param> /// <param name="identifier"></param> /// <param name="lang"></param> /// <param name="createIfNotExist"></param> /// <returns></returns> public EventCalendarDetailsData fetchDetailsData(CmsPage page, int identifier, CmsLanguage lang, bool createIfNotExist) { EventCalendarDetailsData entity = new EventCalendarDetailsData(page, identifier, lang); if (page.ID < 0 || identifier < 0) { return(entity); } StringBuilder sql = new StringBuilder("SELECT PageId,Identifier,LangCode,Description,CategoryId,StartDateTime,EndDateTime,CreatedBy FROM "); sql.Append(TableNameDetails); sql.Append(" WHERE PageId=" + page.ID.ToString()); sql.Append(" AND LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND Identifier=" + identifier.ToString()); sql.Append(" AND Deleted IS NULL;"); DataSet ds = this.RunSelectQuery(sql.ToString()); if (this.hasSingleRow(ds)) { DataRow dr = ds.Tables[0].Rows[0]; rowToData(dr, entity); } else { if (createIfNotExist) { if (insertDetailsData(page, identifier, lang, entity) == false) { throw new Exception("EventCalendarDb.fetchDetailsData() database error: Error creating new placeholder"); } } else { throw new Exception("EventCalendarDb.fetchDetailsData() database error: placeholder does not exist"); } } return(entity); }
/// <summary> /// Update an event details data /// </summary> /// <param name="page"></param> /// <param name="identifier"></param> /// <param name="lang"></param> /// <param name="entity"></param> /// <returns></returns> public bool updateDetailsData(CmsPage page, int identifier, CmsLanguage lang, EventCalendarDetailsData entity) { StringBuilder sql = new StringBuilder("UPDATE "); sql.Append(TableNameDetails); sql.Append(" SET Description = '" + dbEncode(entity.Description) + "',"); sql.Append(" CategoryId = " + entity.CategoryId.ToString() + ","); sql.Append(" StartDateTime = " + dbEncode(entity.StartDateTime) + ","); sql.Append(" EndDateTime = " + dbEncode(entity.EndDateTime) + ","); sql.Append(" CreatedBy = '" + dbEncode(entity.CreatedBy) + "'"); sql.Append(" WHERE PageId=" + page.ID.ToString()); sql.Append(" AND LangCode='" + dbEncode(lang.shortCode) + "'"); sql.Append(" AND Identifier=" + identifier.ToString()); int affected = this.RunUpdateQuery(sql.ToString()); return(affected > 0); }
/// <summary> /// Insert a event details /// </summary> /// <param name="page"></param> /// <param name="identifier"></param> /// <param name="lang"></param> /// <param name="entity"></param> /// <returns></returns> public bool insertDetailsData(CmsPage page, int identifier, CmsLanguage lang, EventCalendarDetailsData entity) { StringBuilder sql = new StringBuilder("INSERT INTO "); sql.Append(TableNameDetails); sql.Append(" (PageId,Identifier,LangCode,Description,CategoryId,StartDateTime,EndDateTime,CreatedBy) VALUES ("); sql.Append(page.ID.ToString() + ","); sql.Append(identifier.ToString() + ",'"); sql.Append(dbEncode(lang.shortCode) + "','"); sql.Append(dbEncode(entity.Description) + "',"); sql.Append(entity.CategoryId.ToString() + ","); sql.Append(dbEncode(entity.StartDateTime) + ","); sql.Append(dbEncode(entity.EndDateTime) + ",'"); sql.Append(dbEncode(entity.CreatedBy) + "');"); int affected = this.RunUpdateQuery(sql.ToString()); if (affected > 0) { return(page.setLastUpdatedDateTimeToNow()); } else { return(false); } }