public DupeIpRepairReportModel RemoveDuplicateStaticPageHits()
{
DupeIpRepairReportModel repairReport = new DupeIpRepairReportModel();
try
{
string dupeDay;
using (var db = new OggleBoobleMySqlContext())
{
List <DupeStaticPageGroup> dupGroups = db.Database.SqlQuery <DupeStaticPageGroup>("select VisitorId, FolderId, " +
"date_format(Occured,'%Y-%m-%d') 'Occured', count(*) 'Count' from StaticPageHit " +
"group by VisitorId, FolderId, date_format(Occured,'%Y-%m-%d') having count(*) > 1").ToList();
foreach (DupeStaticPageGroup dupGroup in dupGroups)
{
repairReport.PageHitsUpdated++;
dupeDay = dupGroup.Occured;
List <StaticPageHit> duplicateStaticPages = db.StaticPageHits.
Where(sph => sph.VisitorId == dupGroup.VisitorId &&
sph.FolderId == dupGroup.FolderId && dupGroup.Occured == dupeDay).ToList();
//StaticPageHit firstVisitor = duplicateStaticPages[0];
for (int i = 0; i < duplicateStaticPages.Count; i++)
{
if (i > 0)
{
db.StaticPageHits.Remove(duplicateStaticPages[i]);
repairReport.VisitorRowsRemoved++;
}
}
db.SaveChanges();
}
repairReport.Success = "ok";
}
}
catch (Exception ex)
{
repairReport.Success = Helpers.ErrorDetails(ex);
}
return(repairReport);
}
public DupeIpRepairReportModel RemoveDuplicateIps()
{
DupeIpRepairReportModel repairReport = new DupeIpRepairReportModel();
try
{
using (var db = new OggleBoobleMySqlContext())
{
int imageHitsChanged, pageHitsChanged, activityLogsChanged;
//var IpAddresses = db.Visitors.Where(v => v.Country != "ZZ" && v.IpAddress != "error").Select(v => v.IpAddress).Distinct().ToList();
var IpGroupings = db.Visitors.Where(v => v.Country != "ZZ" && v.IpAddress != "error" &&
v.IpAddress != "00.00.00" && v.IpAddress != "00.11.11")
.GroupBy(v => v.IpAddress).Where(v => v.Count() > 1).ToList();
foreach (var ipGrouping in IpGroupings)
{
string firstVisitorId = ipGrouping.First().VisitorId;
foreach (Visitor duplicateIp in ipGrouping)
{
//List<Visitor> dupIps = db.Visitors.Where(v => v.IpAddress == ipAddress).ToList();
//string firstVisitorId = dupIps[0].VisitorId;
if (duplicateIp.VisitorId != firstVisitorId)
{
try
{
imageHitsChanged = db.Database.ExecuteSqlCommand(
"Update OggleBooble.ImageHit set VisitorId = '" + firstVisitorId + "' where VisitorId='" + duplicateIp.VisitorId + "';");
repairReport.ImageHitsUpdated += imageHitsChanged;
}
catch (Exception ex)
{
repairReport.Errors.Add(Helpers.ErrorDetails(ex));
}
try
{
pageHitsChanged = db.Database.ExecuteSqlCommand(
"Update OggleBooble.PageHit set VisitorId = '" + firstVisitorId + "' where VisitorId='" + duplicateIp.VisitorId + "';");
repairReport.PageHitsUpdated += pageHitsChanged;
}
catch (Exception ex)
{
repairReport.Errors.Add(Helpers.ErrorDetails(ex));
}
try
{
activityLogsChanged = db.Database.ExecuteSqlCommand(
"Update OggleBooble.ActivityLog set VisitorId ='" + firstVisitorId + "' where VisitorId='" + duplicateIp.VisitorId + "';");
repairReport.ActivityLogsUpdated += activityLogsChanged;
}
catch (Exception ex)
{
repairReport.Errors.Add(Helpers.ErrorDetails(ex));
}
try
{
db.RetiredVisitors.Add(new RetiredVisitor()
{
VisitorId = duplicateIp.VisitorId,
IpAddress = duplicateIp.IpAddress,
City = duplicateIp.City,
Country = duplicateIp.Country,
GeoCode = duplicateIp.GeoCode,
InitialPage = duplicateIp.InitialPage,
InitialVisit = duplicateIp.InitialVisit,
Region = duplicateIp.Region
});
db.Visitors.Remove(duplicateIp);
db.SaveChanges();
repairReport.VisitorRowsRemoved++;
}
catch (Exception ex)
{
repairReport.Errors.Add(Helpers.ErrorDetails(ex));
}
}
}
repairReport.TotalMultiIPsRepaired++;
}
}
repairReport.Success = "ok";
}
catch (Exception ex)
{
repairReport.Success = Helpers.ErrorDetails(ex);
}
return(repairReport);
}
