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