/// <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);
}
}