public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, fieldhelp");
//
// Field help with no field
//
LogController.logInfo(core, "Deleting field help with no field.");
string sql = ""
+ "delete from ccfieldhelp where id in ("
+ " select h.id"
+ " from ccfieldhelp h"
+ " left join ccfields f on f.id=h.fieldid where f.id is null"
+ ")";
core.db.executeNonQuery(sql);
//
// Field help duplicates - messy, but I am not sure where they are coming from, and this patchs the edit page performance problem
//
LogController.logInfo(core, "Deleting duplicate field help records.");
sql = ""
+ "delete from ccfieldhelp where id in ("
+ " select b.id"
+ " from ccfieldhelp a"
+ " left join ccfieldhelp b on a.fieldid=b.fieldid where a.id< b.id"
+ ")";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, grouprules");
//
//
// GroupRules with bad ContentID
// Handled record by record removed to prevent CDEF reload
//
LogController.logInfo(core, "Deleting Group Rules with bad ContentID.");
string sql = "Select ccGroupRules.ID"
+ " From ccGroupRules LEFT JOIN ccContent on ccContent.ID=ccGroupRules.ContentID"
+ " WHERE (ccContent.ID is null)";
using (var csData = new CsModel(core)) {
csData.openSql(sql);
while (csData.ok())
{
MetadataController.deleteContentRecord(core, "Group Rules", csData.getInteger("ID"));
csData.goNext();
}
}
//
// GroupRules with bad GroupID
//
LogController.logInfo(core, "Deleting Group Rules with bad GroupID.");
sql = "delete ccGroupRules"
+ " From ccGroupRules"
+ " LEFT JOIN ccgroups on ccgroups.ID=ccGroupRules.GroupID"
+ " WHERE (ccgroups.ID is null)";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, visitors");
{
//
LogController.logInfo(core, "Deleting visitors with no visits");
//
core.db.sqlCommandTimeout = 180;
core.db.executeNonQuery("delete ccVisitors from ccVisitors Left Join ccVisits on ccVisits.VisitorID=ccVisitors.ID where ccVisits.ID is null");
}
//
//
// delete nocookie visits
// This must happen after the housekeep summarizing, and no sooner then 48 hours ago so all hits have been summarized before deleting
//
if (env.archiveDeleteNoCookie)
{
//
// delete visitors from the non-cookie visits
//
LogController.logInfo(core, "Deleting visitors from visits with no cookie support older than Midnight, Two Days Ago");
string sql = "delete from ccvisitors from ccvisitors r,ccvisits v where r.id=v.visitorid and(v.CookieSupport=0)and(v.LastVisitTime<DATEADD(day,-2,CAST(GETDATE() AS DATE)))";
core.db.sqlCommandTimeout = 180;
core.db.executeNonQuery(sql);
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//
//====================================================================================================
/// <summary>
/// addon interface
/// </summary>
/// <param name="cp"></param>
/// <returns></returns>
public override object Execute(CPBaseClass cp)
{
string result = "";
try {
CoreController core = ((CPClass)cp).core;
//
LogController.logInfo(core, "Housekeep");
//
var env = new HouseKeepEnvironmentModel(core);
int TimeoutSave = core.db.sqlCommandTimeout;
core.db.sqlCommandTimeout = 1800;
//
// -- hourly tasks
HourlyTasksClass.housekeep(core, env);
//
// -- daily tasks
if (env.forceHousekeep || env.runDailyTasks)
{
DailyTasksClass.housekeepDaily(core, env);
}
core.db.sqlCommandTimeout = TimeoutSave;
} catch (Exception ex) {
cp.Site.ErrorReport(ex);
}
return(result);
}
//====================================================================================================
//
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, viewingsummary");
//
//
// -- there is a bug and I need to move on.
return;
//
// Page View Summary
//
{
DateTime datePtr = default;
using (var csData = new CsModel(core)) {
if (!csData.openSql(core.db.getSQLSelect("ccviewingsummary", "DateNumber", "TimeDuration=24 and DateNumber>=" + env.oldestVisitSummaryWeCareAbout.Date.ToOADate(), "DateNumber Desc", "", 1)))
{
datePtr = env.oldestVisitSummaryWeCareAbout;
}
else
{
datePtr = DateTime.MinValue.AddDays(csData.getInteger("DateNumber"));
}
}
if (datePtr < env.oldestVisitSummaryWeCareAbout)
{
datePtr = env.oldestVisitSummaryWeCareAbout;
}
pageViewSummary(core, datePtr, env.yesterday, 24, core.siteProperties.dataBuildVersion, env.oldestVisitSummaryWeCareAbout);
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
// ContentWatchListRules with bad ContentWatchID
//
LogController.logInfo(core, "Deleting ContentWatchList Rules with bad ContentWatchID.");
string sql = "delete ccContentWatchListRules"
+ " From ccContentWatchListRules"
+ " LEFT JOIN ccContentWatch on ccContentWatch.ID=ccContentWatchListRules.ContentWatchID"
+ " WHERE (ccContentWatch.ID is null)";
core.db.executeNonQuery(sql);
//
// ContentWatchListRules with bad ContentWatchListID
//
LogController.logInfo(core, "Deleting ContentWatchList Rules with bad ContentWatchListID.");
sql = "delete ccContentWatchListRules"
+ " From ccContentWatchListRules"
+ " LEFT JOIN ccContentWatchLists on ccContentWatchLists.ID=ccContentWatchListRules.ContentWatchListID"
+ " WHERE (ccContentWatchLists.ID is null)";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, contentfieldtype rules");
//
core.db.executeNonQuery("delete from ccAddonContentFieldTypeRules where id in (select r.id from ccAddonContentFieldTypeRules r left join ccaggregatefunctions a on a.id=r.addonid where a.Id Is Null)");
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//====================================================================================================
//
public static void housekeepDaily(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily");
//
// -- Download Updates
SoftwareUpdatesClass.downloadAndInstall(core);
//
// -- Addon folder
AddonFolderClass.housekeep(core);
//
// -- metadata
ContentFieldClass.housekeep(core, env);
//
// -- content
PageContentClass.housekeep(core, env);
AddonContentFieldTypeRuleClass.housekeep(core, env);
AddonContentTriggerRuleClass.housekeep(core, env);
ContentWatchClass.housekeep(core, env);
EmailDropClass.housekeep(core, env);
EmailLogClass.housekeep(core, env);
FieldHelpClass.housekeep(core, env);
GroupRulesClass.housekeep(core, env);
MemberRuleClass.housekeep(core, env);
MetadataClass.housekeep(core, env);
LinkAliasClass.housekeep(core, env);
//
// -- Properties
UserProperyClass.housekeep(core);
VisitPropertyClass.housekeep(core);
VisitorPropertyClass.housekeep(core);
//
// -- visits, visitors, viewings
VisitClass.housekeep(core, env);
VisitorClass.housekeep(core, env);
ViewingsClass.housekeep(core, env);
//
// -- summary
VisitSummaryClass.housekeep(core, env);
ViewingSummaryClass.housekeep(core, env);
//
// -- logs
ActivityLogClass.housekeep(core, env);
//
// -- people
PersonClass.housekeep(core, env);
} catch (Exception ex) {
LogController.logError(core, ex);
throw;
}
}
//
//====================================================================================================
//
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep hourly");
//
//
// -- delete temp files
TempFilesClass.deleteFiles(core);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, content fields");
//
LogController.logInfo(core, "Deleting content fields with no content.");
string sql = "delete from ccfields from ccfields left join cccontent on cccontent.id=ccfields.contentId where cccontent.id is null";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, linkalias");
//
// -- delete dups
string sql = "delete from ccLinkAliases where id in ( select b.id from cclinkaliases a,cclinkaliases b where a.id<b.id and a.name=b.name )";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, addoncontenttrigger rules");
//
// -- addon trigger rules
core.db.executeNonQuery("delete from ccAddonContentTriggerRules where id in (select r.id from ccAddonContentTriggerRules r left join ccaggregatefunctions a on a.id = r.addonid where a.Id Is Null)");
core.db.executeNonQuery("delete from ccAddonContentTriggerRules where id in (select r.id from ccAddonContentTriggerRules r left join cccontent c on c.id = r.contentid where c.id is null)");
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, emaildrops older then " + env.emailDropArchiveAgeDays + " days");
//
// email drops for only 365 days
core.db.executeNonQuery("delete from ccemaildrops where (dateadded < dateadd(day,-" + env.emailDropArchiveAgeDays + ",cast(getdate() as date)))");
//
// email drops with no email
core.db.executeNonQuery("delete from ccemaildrops from ccemaildrops d left join ccemail e on e.id=d.emailid where e.id is null");
//
//
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//====================================================================================================
//
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, page content");
{
//
// Move Archived pages from their current parent to their archive parent
//
bool NeedToClearCache = false;
string SQL = "select * from ccpagecontent where (( DateArchive is not null )and(DateArchive<" + core.sqlDateTimeMockable + "))and(active<>0)";
using (var csData = new CsModel(core)) {
csData.openSql(SQL);
while (csData.ok())
{
int RecordId = csData.getInteger("ID");
int ArchiveParentId = csData.getInteger("ArchiveParentID");
if (ArchiveParentId == 0)
{
SQL = "update ccpagecontent set DateArchive=null where (id=" + RecordId + ")";
core.db.executeNonQuery(SQL);
}
else
{
SQL = "update ccpagecontent set ArchiveParentID=null,DateArchive=null,parentid=" + ArchiveParentId + " where (id=" + RecordId + ")";
core.db.executeNonQuery(SQL);
NeedToClearCache = true;
}
csData.goNext();
}
csData.close();
}
//
// Clear caches
//
if (NeedToClearCache)
{
object emptyData = null;
core.cache.invalidate("Page Content");
core.cache.storeObject("PCC", emptyData);
}
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, email log");
//
// email log for only 365 days
core.db.executeNonQuery("delete from ccemaillog where (dateadded < DATEADD(day,-" + env.emailDropArchiveAgeDays + ",CAST(GETDATE() AS DATE)))");
//
// clear email body field for emails older than 7 days
LogController.logInfo(core, "Clear email body field for email logs older then " + env.emailLogBodyRetainDays + " days");
DateTime emailLogBodyRetainDate = core.dateTimeNowMockable.AddDays(-env.emailLogBodyRetainDays).Date;
core.db.executeNonQuery("update ccemaillog set body=null where dateadded<" + DbController.encodeSQLDate(emailLogBodyRetainDate));
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, viewings");
//
try {
//
// delete old viewings
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery("delete from ccviewings where (dateadded < DATEADD(day,-" + env.visitArchiveAgeDays + ",CAST(GETDATE() AS DATE)))");
} catch (Exception) {
LogController.logWarn(core, "exception deleting old viewings");
}
//
if (env.archiveDeleteNoCookie)
{
//
LogController.logInfo(core, "Deleting viewings from visits with no cookie support older than Midnight, Two Days Ago");
//
// if this fails, continue with the rest of the work
try {
string sql = "delete from ccviewings from ccviewings h,ccvisits v where h.visitid=v.id and(v.CookieSupport=0)and(v.LastVisitTime<DATEADD(day,-2,CAST(GETDATE() AS DATE)))";
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery(sql);
} catch (Exception) {
LogController.logWarn(core, "exception deleting viewings with no cookie");
}
}
//
LogController.logInfo(core, "Deleting viewings with null or invalid VisitID");
//
try {
string sql = "delete from ccviewings where (visitid=0 or visitid is null)";
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery(sql);
} catch (Exception) {
LogController.logWarn(core, "exception deleting viewings with invalid visits");
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "HousekeepDaily, contentwatch");
//
using (var csData = new CsModel(core)) {
string sql = "select cccontentwatch.id from cccontentwatch left join cccontent on cccontent.id=cccontentwatch.contentid where (cccontent.id is null)or(cccontent.active=0)or(cccontent.active is null)";
csData.openSql(sql);
while (csData.ok())
{
MetadataController.deleteContentRecord(core, "Content Watch", csData.getInteger("ID"));
csData.goNext();
}
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//====================================================================================================
//
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
string SQL = "";
//
// Remote Query Expiration
//
SQL = "delete from ccRemoteQueries where (DateExpires is not null)and(DateExpires<" + DbController.encodeSQLDate(core.dateTimeNowMockable) + ")";
core.db.executeNonQuery(SQL);
SQL = "delete from ccmenuEntries where id in (select m.ID from ccMenuEntries m left join ccAggregateFunctions a on a.id=m.AddonID where m.addonid<>0 and a.id is null)";
core.db.executeNonQuery(SQL);
//
SQL = "delete from ccmenuEntries where id in (select m.ID from ccMenuEntries m left join ccAggregateFunctions a on a.id=m.helpaddonid where m.helpaddonid<>0 and a.id is null)";
core.db.executeNonQuery(SQL);
//
SQL = "delete from ccmenuEntries where id in (select m.ID from ccMenuEntries m left join ccAddonCollections c on c.id=m.helpcollectionid Where m.helpcollectionid <> 0 And c.Id Is Null)";
core.db.executeNonQuery(SQL);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, activitylog");
{
//
//
LogController.logInfo(core, "Deleting activities older than 30 days.");
//
core.db.executeNonQuery("delete from ccactivitylog where (DateAdded is null)or(DateAdded<DATEADD(day,-30,CAST(GETDATE() AS DATE)))");
}
{
//
LogController.logInfo(core, "Deleting activities with no member record.");
//
core.db.executeNonQuery("delete ccactivitylog from ccactivitylog left join ccmembers on ccmembers.id=ccactivitylog.memberid where (ccmembers.id is null)");
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, memberrules");
//
//
// -- delete rows with invalid columns
core.db.executeNonQuery("delete from ccMemberRules where groupid is null or memberid is null");
//
// MemberRules with bad MemberID
//
LogController.logInfo(core, "Deleting Member Rules with bad MemberID.");
string sql = "delete ccmemberrules"
+ " From ccmemberrules"
+ " LEFT JOIN ccmembers on ccmembers.ID=ccmemberrules.memberId"
+ " WHERE (ccmembers.ID is null)";
core.db.executeNonQuery(sql);
//
// MemberRules with bad GroupID
//
LogController.logInfo(core, "Deleting Member Rules with bad GroupID.");
sql = "delete ccmemberrules"
+ " From ccmemberrules"
+ " LEFT JOIN ccgroups on ccgroups.ID=ccmemberrules.GroupID"
+ " WHERE (ccgroups.ID is null)";
core.db.executeNonQuery(sql);
//
// -- delete duplicates (very slow query)
sql = "delete from ccmemberrules where id in ("
+ " select distinct b.id"
+ " from ccmemberrules a, ccmemberrules b"
+ " where ((a.memberid=b.memberid)and(a.groupid=b.groupid)and(a.id<b.id))"
+ ")";
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//
//=========================================================================================
/// <summary>
/// summarized visits hourly
/// </summary>
/// <param name="core"></param>
/// <param name="env"></param>
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, visitsummary");
//
bool newHour = (core.dateTimeNowMockable.Hour != env.lastCheckDateTime.Hour);
if (env.forceHousekeep || newHour)
{
//
// Set NextSummaryStartDate based on the last time we ran hourly summarization
//
DateTime LastTimeSummaryWasRun = env.visitArchiveDate;
core.db.sqlCommandTimeout = 180;
using (var csData = new CsModel(core)) {
if (csData.openSql(core.db.getSQLSelect("ccVisitSummary", "DateAdded", "(timeduration=1)and(Dateadded>" + DbController.encodeSQLDate(env.visitArchiveDate) + ")", "id Desc", "", 1)))
{
LastTimeSummaryWasRun = csData.getDate("DateAdded");
LogController.logInfo(core, "Update hourly visit summary, last time summary was run was [" + LastTimeSummaryWasRun + "]");
}
else
{
LogController.logInfo(core, "Update hourly visit summary, no hourly summaries were found, set start to [" + LastTimeSummaryWasRun + "]");
}
}
DateTime NextSummaryStartDate = LastTimeSummaryWasRun;
//
// Each hourly entry includes visits that started during that hour, but we do not know when they finished (maybe during last hour)
// Find the oldest starttime of all the visits with endtimes after the LastTimeSummaryWasRun. Resummarize all periods
// from then to now
//
// For the past 24 hours, find the oldest visit with the last viewing during the last hour
//
DateTime StartOfHour = (new DateTime(LastTimeSummaryWasRun.Year, LastTimeSummaryWasRun.Month, LastTimeSummaryWasRun.Day, LastTimeSummaryWasRun.Hour, 1, 1)).AddHours(-1); // (Int(24 * LastTimeSummaryWasRun) / 24) - PeriodStep
DateTime OldestDateAdded = StartOfHour;
core.db.sqlCommandTimeout = 180;
using (var csData = new CsModel(core)) {
if (csData.openSql(core.db.getSQLSelect("ccVisits", "DateAdded", "LastVisitTime>" + DbController.encodeSQLDate(StartOfHour), "dateadded", "", 1)))
{
OldestDateAdded = csData.getDate("DateAdded");
if (OldestDateAdded < NextSummaryStartDate)
{
NextSummaryStartDate = OldestDateAdded;
LogController.logInfo(core, "Update hourly visit summary, found a visit with the last viewing during the past hour. It started [" + OldestDateAdded + "], before the last summary was run.");
}
}
}
DateTime PeriodStartDate = core.dateTimeNowMockable.Date.AddDays(-90);
double PeriodStep = 1;
int HoursPerDay = 0;
core.db.sqlCommandTimeout = 180;
for (double PeriodDatePtr = PeriodStartDate.ToOADate(); PeriodDatePtr <= OldestDateAdded.ToOADate(); PeriodDatePtr += PeriodStep)
{
//
// Verify there are 24 hour records for every day back the past 90 days
//
DateTime DateofMissingSummary = DateTime.MinValue;
using (var csData = new CsModel(core)) {
if (csData.openSql("select count(id) as HoursPerDay from ccVisitSummary where TimeDuration=1 and DateNumber=" + encodeInteger(PeriodDatePtr) + " group by DateNumber"))
{
HoursPerDay = csData.getInteger("HoursPerDay");
}
csData.close();
if (HoursPerDay < 24)
{
DateofMissingSummary = DateTime.FromOADate(PeriodDatePtr);
break;
}
}
if ((DateofMissingSummary != DateTime.MinValue) && (DateofMissingSummary < NextSummaryStartDate))
{
LogController.logInfo(core, "Found a missing hourly period in the visit summary table [" + DateofMissingSummary + "], it only has [" + HoursPerDay + "] hourly summaries.");
NextSummaryStartDate = DateofMissingSummary;
}
//
// Now summarize all visits during all hourly periods between OldestDateAdded and the previous Hour
//
LogController.logInfo(core, "Summaryize visits hourly, starting [" + NextSummaryStartDate + "]");
PeriodStep = (double)1 / (double)24;
VisitSummaryClass.summarizePeriod(core, env, NextSummaryStartDate, core.dateTimeNowMockable, 1, core.siteProperties.dataBuildVersion, env.oldestVisitSummaryWeCareAbout);
}
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
//
core.cpParent.Db.ExecuteNonQuery("update ccmembers set allowbulkemail=1 from ccmembers m left join emailbouncelist b on b.name LIKE CONCAT('%', m.[email], '%') where b.id is not null and m.email is not null");
//
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, visits");
{
//
LogController.logInfo(core, "Deleting visits with no DateAdded");
//
core.db.sqlCommandTimeout = 180;
core.db.executeNonQuery("delete from ccvisits where (DateAdded is null)or(DateAdded<DATEADD(year,-10,CAST(GETDATE() AS DATE)))");
}
{
//
LogController.logInfo(core, "Deleting visits with no visitor");
//
core.db.executeNonQuery("delete from ccvisits from ccvisits v left join ccvisitors r on r.id=v.visitorid where (r.id is null)");
}
if (env.archiveDeleteNoCookie)
{
//
LogController.logInfo(core, "Deleting visits with no cookie support older than Midnight, Two Days Ago");
//
core.db.sqlCommandTimeout = 180;
core.db.executeNonQuery("delete from ccvisits where (CookieSupport=0)and(LastVisitTime<DATEADD(day,-2,CAST(GETDATE() AS DATE)))");
}
DateTime OldestVisitDate = default(DateTime);
//
// Get Oldest Visit
using (var csData = new CsModel(core)) {
if (csData.openSql(core.db.getSQLSelect("ccVisits", "DateAdded", "", "dateadded", "", 1)))
{
OldestVisitDate = csData.getDate("DateAdded").Date;
}
}
//
// Remove old visit records
// if > 30 days in visit table, limit one pass to just 30 days
// this is to prevent the entire server from being bogged down for one site change
//
if (OldestVisitDate == DateTime.MinValue)
{
LogController.logInfo(core, "No visit records were removed because no visit records were found while requesting the oldest visit.");
}
else
{
DateTime ArchiveDate = core.dateTimeNowMockable.AddDays(-env.visitArchiveAgeDays).Date;
int DaystoRemove = encodeInteger(ArchiveDate.Subtract(OldestVisitDate).TotalDays);
if (DaystoRemove > 30)
{
ArchiveDate = OldestVisitDate.AddDays(30);
}
if (OldestVisitDate >= ArchiveDate)
{
LogController.logInfo(core, "No records were removed because Oldest Visit Date [" + OldestVisitDate + "] >= ArchiveDate [" + ArchiveDate + "].");
}
else
{
LogController.logInfo(core, "Removing records from [" + OldestVisitDate + "] to [" + ArchiveDate + "].");
DateTime SingleDate = default(DateTime);
SingleDate = OldestVisitDate;
do
{
houseKeep_App_Daily_RemoveVisitRecords(core, SingleDate);
SingleDate = SingleDate.AddDays(1);
} while (SingleDate < ArchiveDate);
}
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, people");
//
// Any member records that were created outside contensive need to have CreatedByVisit=0 (past v4.1.152)
core.db.executeNonQuery("update ccmembers set CreatedByVisit=0 where createdbyvisit is null");
//
// delete members from the non-cookie visits
// legacy records without createdbyvisit will have to be corrected by hand (or upgrade)
//
LogController.logInfo(core, "Deleting members from visits with no cookie support older than Midnight, Two Days Ago");
string sql = "delete from ccmembers from ccmembers m,ccvisits v where v.memberid=m.id and(m.visits=1) and(m.createdbyvisit=1) and(m.username is null) and(m.email is null) and(v.cookiesupport=0)and(v.lastvisittime<DATEADD(hour, -2, GETDATE()))";
try {
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery(sql);
} catch (Exception ex) {
LogController.logError(core, ex);
}
//
// -- Remove old guest records
DateTime ArchiveDate = core.dateTimeNowMockable.AddDays(-env.guestArchiveAgeDays).Date;
string SQLTablePeople = MetadataController.getContentTablename(core, "People");
string DeleteBeforeDateSQL = DbController.encodeSQLDate(ArchiveDate);
//
LogController.logInfo(core, "Deleting members with LastVisit before DeleteBeforeDate [" + ArchiveDate + "], exactly one total visit, a null username and a null email address.");
//
string SQLCriteria = "(LastVisit<" + DeleteBeforeDateSQL + ")and(createdbyvisit=1)and(Visits=1)and(Username is null)and(email is null)";
core.db.sqlCommandTimeout = 1800;
core.db.deleteTableRecordChunks("ccmembers", SQLCriteria, 1000, 10000);
//
// delete 'guests' Members with one visits but no valid visit record
//
LogController.logInfo(core, "Deleting 'guest' members with no visits (name is default name, visits=1, username null, email null,dateadded=lastvisit)");
sql = "delete from ccmembers from ccmembers m,ccvisits v"
+ " where v.memberid=m.id"
+ " and(m.createdbyvisit=1)"
+ " and(m.Visits=1)"
+ " and(m.Username is null)"
+ " and(m.email is null)"
+ " and(m.dateadded=m.lastvisit)"
+ " and(v.id is null)";
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery(sql);
//
// delete 'guests' Members created before ArchivePeopleAgeDays
//
LogController.logInfo(core, "Deleting 'guest' members with no visits (name is default name, visits=1, username null, email null,dateadded=lastvisit)");
sql = "delete from ccmembers from ccmembers m left join ccvisits v on v.memberid=m.id"
+ " where(m.createdbyvisit=1)"
+ " and(m.Visits=1)"
+ " and(m.Username is null)"
+ " and(m.email is null)"
+ " and(m.dateadded=m.lastvisit)"
+ " and(v.id is null)";
core.db.sqlCommandTimeout = 1800;
core.db.executeNonQuery(sql);
//
// -- mark all people allowbulkemail if their email address is in the emailbouncelist
sql = "update ccmembers set allowbulkemail=0 from ccmembers m left join emailbouncelist b on b.name LIKE CONCAT('%', m.[email], '%') where b.id is not null and m.email is not null";
core.db.sqlCommandTimeout = 1800;
core.cpParent.Db.ExecuteNonQuery(sql);
//
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
//
//=========================================================================================
// Summarize the visits
// excludes non-cookie visits
// excludes administrator and developer visits
// excludes authenticated users with ExcludeFromReporting
//
// Average time on site
//
// Example data
// Pages TimeToLastHit
// 1 0 - hit 1 page, start time = last time
// 10 3510 - hit 10 pages, first hit time - last hit time = 3510
// 2 30 - hit 2 pages, first hit time - last hit time = 30
//
// AveReadTime is the average time spent reading pages
// this is calculated from the multi-page visits only
// = MultiPageTimeToLastHitSum / ( MultiPageHitCnt - MultiPageVisitCnt )
// = ( 3510 + 30 ) / ((10+2) - 2 )
// = 354
//
// TotalTimeOnSite is the total time people spent reading pages
// There are two parts:
// 1) the TimeToLastHit, which covers all but the last hit of each visit
// 2) assume the last hit of each visit is the AveReadTime
// = MultiPageTimeToLastHitSum + ( AveReadTime * VisitCnt )
// = ( 3510 + 30 ) + ( 354 * 3 )
// = 4602
//
// AveTimeOnSite
// = TotalTimeOnSite / TotalHits
// = 4602 / 3
// = 1534
//
//=========================================================================================
//
private static void summarizePeriod(CoreController core, HouseKeepEnvironmentModel env, DateTime StartTimeDate, DateTime EndTimeDate, int HourDuration, string BuildVersion, DateTime OldestVisitSummaryWeCareAbout)
{
try {
//
if (string.CompareOrdinal(BuildVersion, CoreController.codeVersion()) >= 0)
{
DateTime PeriodStart = default(DateTime);
PeriodStart = StartTimeDate;
if (PeriodStart < OldestVisitSummaryWeCareAbout)
{
PeriodStart = OldestVisitSummaryWeCareAbout;
}
double StartTimeHoursSinceMidnight = PeriodStart.TimeOfDay.TotalHours;
PeriodStart = PeriodStart.Date.AddHours(StartTimeHoursSinceMidnight);
DateTime PeriodDatePtr = default(DateTime);
PeriodDatePtr = PeriodStart;
while (PeriodDatePtr < EndTimeDate)
{
//
int DateNumber = encodeInteger(PeriodDatePtr.AddHours(HourDuration / 2.0).ToOADate());
int TimeNumber = encodeInteger(PeriodDatePtr.TimeOfDay.TotalHours);
DateTime DateStart = default(DateTime);
DateStart = PeriodDatePtr.Date;
DateTime DateEnd = default(DateTime);
DateEnd = PeriodDatePtr.AddHours(HourDuration).Date;
//
// No Cookie Visits
//
string SQL = "select count(v.id) as NoCookieVisits"
+ " from ccvisits v"
+ " where (v.CookieSupport<>1)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ "";
int NoCookieVisits = 0;
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
NoCookieVisits = csData.getInteger("NoCookieVisits");
}
}
//
// Total Visits
//
SQL = "select count(v.id) as VisitCnt ,Sum(v.PageVisits) as HitCnt ,sum(v.TimetoLastHit) as TimeOnSite"
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ "";
//
int VisitCnt = 0;
int HitCnt = 0;
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
VisitCnt = csData.getInteger("VisitCnt");
HitCnt = csData.getInteger("HitCnt");
double TimeOnSite = csData.getNumber("TimeOnSite");
}
}
//
// -- Visits by new visitors
int NewVisitorVisits = 0;
int SinglePageVisits = 0;
int AuthenticatedVisits = 0;
int MobileVisits = 0;
int BotVisits = 0;
double AveTimeOnSite = 0;
if (VisitCnt > 0)
{
SQL = "select count(v.id) as NewVisitorVisits"
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(v.VisitorNew<>0)"
+ "";
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
NewVisitorVisits = csData.getInteger("NewVisitorVisits");
}
}
//
// Single Page Visits
//
SQL = "select count(v.id) as SinglePageVisits"
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(v.PageVisits=1)"
+ "";
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
SinglePageVisits = csData.getInteger("SinglePageVisits");
}
}
//
// Multipage Visits
//
SQL = "select count(v.id) as VisitCnt ,sum(v.PageVisits) as HitCnt ,sum(v.TimetoLastHit) as TimetoLastHitSum "
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(PageVisits>1)"
+ "";
int MultiPageHitCnt = 0;
int MultiPageVisitCnt = 0;
double MultiPageTimetoLastHitSum = 0;
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
MultiPageVisitCnt = csData.getInteger("VisitCnt");
MultiPageHitCnt = csData.getInteger("HitCnt");
MultiPageTimetoLastHitSum = csData.getNumber("TimetoLastHitSum");
}
}
//
// Authenticated Visits
//
SQL = "select count(v.id) as AuthenticatedVisits "
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(VisitAuthenticated<>0)"
+ "";
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
AuthenticatedVisits = csData.getInteger("AuthenticatedVisits");
}
}
//
//
// Mobile Visits
//
SQL = "select count(v.id) as cnt "
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(Mobile<>0)"
+ "";
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
MobileVisits = csData.getInteger("cnt");
}
}
//
// Bot Visits
//
SQL = "select count(v.id) as cnt "
+ " from ccvisits v"
+ " where (v.CookieSupport<>0)"
+ " and(v.dateadded>=" + DbController.encodeSQLDate(DateStart) + ")"
+ " and (v.dateadded<" + DbController.encodeSQLDate(DateEnd) + ")"
+ " and((v.ExcludeFromAnalytics is null)or(v.ExcludeFromAnalytics=0))"
+ " and(Bot<>0)"
+ "";
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.openSql(SQL);
if (csData.ok())
{
BotVisits = csData.getInteger("cnt");
}
}
//
if ((MultiPageHitCnt > MultiPageVisitCnt) && (HitCnt > 0))
{
int AveReadTime = encodeInteger(MultiPageTimetoLastHitSum / (MultiPageHitCnt - MultiPageVisitCnt));
double TotalTimeOnSite = MultiPageTimetoLastHitSum + (AveReadTime * VisitCnt);
AveTimeOnSite = TotalTimeOnSite / VisitCnt;
}
}
//
// Add or update the Visit Summary Record
//
using (var csData = new CsModel(core)) {
core.db.sqlCommandTimeout = 180;
csData.open("Visit Summary", "(timeduration=" + HourDuration + ")and(DateNumber=" + DateNumber + ")and(TimeNumber=" + TimeNumber + ")");
if (!csData.ok())
{
csData.close();
csData.insert("Visit Summary");
}
//
if (csData.ok())
{
csData.set("name", HourDuration + " hr summary for " + DateTime.FromOADate(DateNumber).ToShortDateString() + " " + TimeNumber + ":00");
csData.set("DateNumber", DateNumber);
csData.set("TimeNumber", TimeNumber);
csData.set("Visits", VisitCnt);
csData.set("PagesViewed", HitCnt);
csData.set("TimeDuration", HourDuration);
csData.set("NewVisitorVisits", NewVisitorVisits);
csData.set("SinglePageVisits", SinglePageVisits);
csData.set("AuthenticatedVisits", AuthenticatedVisits);
csData.set("NoCookieVisits", NoCookieVisits);
csData.set("AveTimeOnSite", AveTimeOnSite);
{
csData.set("MobileVisits", MobileVisits);
csData.set("BotVisits", BotVisits);
}
}
}
PeriodDatePtr = PeriodDatePtr.AddHours(HourDuration);
}
{
//
// Delete any daily visit summary duplicates during this period(keep the first)
//
string SQL = "delete from ccvisitsummary"
+ " where id in ("
+ " select d.id from ccvisitsummary d,ccvisitsummary f"
+ " where f.datenumber=d.datenumber"
+ " and f.datenumber>" + env.oldestVisitSummaryWeCareAbout.ToOADate() + " and f.datenumber<" + env.yesterday.ToOADate() + " and f.TimeDuration=24"
+ " and d.TimeDuration=24"
+ " and f.id<d.id"
+ ")";
core.db.sqlCommandTimeout = 180;
core.db.executeNonQuery(SQL);
//
// Find missing daily summaries, summarize that date
//
SQL = core.db.getSQLSelect("ccVisitSummary", "DateNumber", "TimeDuration=24 and DateNumber>=" + env.oldestVisitSummaryWeCareAbout.Date.ToOADate(), "DateNumber,TimeNumber");
using (var csData = new CsModel(core)) {
csData.openSql(SQL);
DateTime datePtr = env.oldestVisitSummaryWeCareAbout;
while (datePtr <= env.yesterday)
{
if (!csData.ok())
{
//
// Out of data, start with this DatePtr
//
VisitSummaryClass.summarizePeriod(core, env, datePtr, datePtr, 24, core.siteProperties.dataBuildVersion, env.oldestVisitSummaryWeCareAbout);
}
else
{
DateTime workingDate = DateTime.MinValue.AddDays(csData.getInteger("DateNumber"));
if (datePtr < workingDate)
{
//
// There are missing dates, update them
//
VisitSummaryClass.summarizePeriod(core, env, datePtr, workingDate.AddDays(-1), 24, core.siteProperties.dataBuildVersion, env.oldestVisitSummaryWeCareAbout);
}
}
if (csData.ok())
{
//
// if there is more data, go to the next record
//
csData.goNext();
}
datePtr = datePtr.AddDays(1).Date;
}
csData.close();
}
}
}
//
return;
} catch (Exception ex) {
LogController.logError(core, ex);
}
}
public static void housekeep(CoreController core, HouseKeepEnvironmentModel env)
{
try {
//
LogController.logInfo(core, "Housekeep, metadata");
//
//
// block duplicate redirect fields (match contentid+fieldtype+caption)
//
LogController.logInfo(core, "Inactivate duplicate redirect fields");
int FieldContentId = 0;
string FieldLast = null;
string FieldNew = null;
int FieldRecordId = 0;
using (var csData = new CsModel(core)) {
csData.openSql("Select ID, ContentID, Type, Caption from ccFields where (active<>0)and(Type=" + (int)CPContentBaseClass.FieldTypeIdEnum.Redirect + ") Order By ContentID, Caption, ID");
FieldLast = "";
while (csData.ok())
{
FieldContentId = csData.getInteger("Contentid");
string FieldCaption = csData.getText("Caption");
FieldNew = FieldContentId + FieldCaption;
if (FieldNew == FieldLast)
{
FieldRecordId = csData.getInteger("ID");
core.db.executeNonQuery("Update ccFields set active=0 where ID=" + FieldRecordId + ";");
}
FieldLast = FieldNew;
csData.goNext();
}
}
//
// convert FieldTypeLongText + htmlContent to FieldTypeHTML
LogController.logInfo(core, "convert FieldTypeLongText + htmlContent to FieldTypeHTML.");
string sql = "update ccfields set type=" + (int)CPContentBaseClass.FieldTypeIdEnum.HTML + " where type=" + (int)CPContentBaseClass.FieldTypeIdEnum.LongText + " and ( htmlcontent<>0 )";
core.db.executeNonQuery(sql);
//
// Content TextFile types with no controlling record
//
if (GenericController.encodeBoolean(core.siteProperties.getText("ArchiveAllowFileClean", "false")))
{
//
int DSType = core.db.getDataSourceType();
LogController.logInfo(core, "Content TextFile types with no controlling record.");
using (var csData = new CsModel(core)) {
sql = "SELECT DISTINCT ccTables.Name as TableName, ccFields.Name as FieldName"
+ " FROM (ccFields LEFT JOIN ccContent ON ccFields.ContentId = ccContent.ID) LEFT JOIN ccTables ON ccContent.ContentTableId = ccTables.ID"
+ " Where (((ccFields.Type) = 10))"
+ " ORDER BY ccTables.Name";
csData.openSql(sql);
while (csData.ok())
{
//
// Get all the files in this path, and check that the record exists with this in its field
//
string FieldName = csData.getText("FieldName");
string TableName = csData.getText("TableName");
string PathName = TableName + "\\" + FieldName;
List <CPFileSystemBaseClass.FileDetail> FileList = core.cdnFiles.getFileList(PathName);
if (FileList.Count > 0)
{
core.db.executeNonQuery("CREATE INDEX temp" + FieldName + " ON " + TableName + " (" + FieldName + ")");
foreach (CPFileSystemBaseClass.FileDetail file in FileList)
{
string Filename = file.Name;
string VirtualFileName = PathName + "\\" + Filename;
string VirtualLink = GenericController.strReplace(VirtualFileName, "\\", "/");
long FileSize = file.Size;
if (FileSize == 0)
{
sql = "update " + TableName + " set " + FieldName + "=null where (" + FieldName + "=" + DbController.encodeSQLText(VirtualFileName) + ")or(" + FieldName + "=" + DbController.encodeSQLText(VirtualLink) + ")";
core.db.executeNonQuery(sql);
core.cdnFiles.deleteFile(VirtualFileName);
}
else
{
using (var csTest = new CsModel(core)) {
sql = "SELECT ID FROM " + TableName + " WHERE (" + FieldName + "=" + DbController.encodeSQLText(VirtualFileName) + ")or(" + FieldName + "=" + DbController.encodeSQLText(VirtualLink) + ")";
if (!csTest.openSql(sql))
{
core.cdnFiles.deleteFile(VirtualFileName);
}
}
}
}
if (DSType == 1)
{
// access
sql = "Drop INDEX temp" + FieldName + " ON " + TableName;
}
else if (DSType == 2)
{
// sql server
sql = "DROP INDEX " + TableName + ".temp" + FieldName;
}
else
{
// mysql
sql = "ALTER TABLE " + TableName + " DROP INDEX temp" + FieldName;
}
core.db.executeNonQuery(sql);
}
csData.goNext();
}
}
}
} catch (Exception ex) {
LogController.logError(core, ex);
}
}