public Perform_SQL_Inserts ( int year, int month, int>.Dictionary |
||
year | int | |
month | int | |
aggregationHash | int>.Dictionary | |
bibHash | int>.Dictionary | |
portalHash | int>.Dictionary | |
Результат | void |
public void Perform_SQL_Inserts(int year, int month, Dictionary<string, int> aggregationHash,
Dictionary<string, int> bibHash, Dictionary<string, int> portalHash)
{
// Add the overall statistics
if (sobekcm_stats.Rows.Count > 0)
{
Engine_Database.Save_TopLevel_Statistics(year, month, Convert.ToInt32(sobekcm_stats.Rows[0]["hits"]),
Convert.ToInt32(sobekcm_stats.Rows[0]["sessions"]), Convert.ToInt32(sobekcm_stats.Rows[0]["Robot_Hits"]),
Convert.ToInt32(sobekcm_stats.Rows[0]["XML"]), Convert.ToInt32(sobekcm_stats.Rows[0]["OAI"]),
Convert.ToInt32(sobekcm_stats.Rows[0]["JSON"]), null);
}
// Add the web content statistics
if ((webcontent_stats != null) && (webcontent_stats.Rows.Count > 0))
{
foreach (DataRow thisRow in webcontent_stats.Rows)
{
// Calculate the complete hits
StringBuilder sql_builder = new StringBuilder("Level1=\"" + thisRow[2].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[3].ToString().Length > 0)
{
sql_builder.Append(" and Level2=\"" + thisRow[3].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[4].ToString().Length > 0)
{
sql_builder.Append(" and Level3=\"" + thisRow[4].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[5].ToString().Length > 0)
{
sql_builder.Append(" and Level4=\"" + thisRow[5].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[6].ToString().Length > 0)
{
sql_builder.Append(" and Level5=\"" + thisRow[6].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[7].ToString().Length > 0)
{
sql_builder.Append(" and Level6=\"" + thisRow[7].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[8].ToString().Length > 0)
{
sql_builder.Append(" and Level7=\"" + thisRow[8].ToString().Replace("\"", "").Replace("'", "") + "\"");
if (thisRow[9].ToString().Length > 0)
{
sql_builder.Append(" and Level8=\"" + thisRow[9].ToString().Replace("\"", "").Replace("'", "") + "\"");
}
}
}
}
}
}
}
try
{
DataRow[] matches = webcontent_stats.Select(sql_builder.ToString().Replace("\"","'"));
int hits_complete = matches.Sum(childRow => Convert.ToInt32(childRow[1]));
Engine_Database.Save_WebContent_Statistics(year, month, Convert.ToInt32(thisRow[1]), hits_complete, thisRow[2].ToString(),
thisRow[3].ToString(), thisRow[4].ToString(), thisRow[5].ToString(), thisRow[6].ToString(), thisRow[7].ToString(), thisRow[8].ToString(),
thisRow[9].ToString(), null);
}
catch (Exception ee)
{
throw ee;
}
}
}
// Add the portal hits
if ((portal_stats != null) && (portal_stats.Rows.Count > 0))
{
foreach (DataRow portalRow in portal_stats.Rows)
{
string code = portalRow[0].ToString();
if (portalHash.ContainsKey(code.ToUpper()))
{
int portalid = portalHash[code.ToUpper()];
Engine_Database.Save_Portal_Statistics(portalid, year, month, Convert.ToInt32(portalRow[1]), null);
}
}
}
// Add the item aggregation stats (non-institutional)
SortedList<int, string> sql = new SortedList<int, string>();
foreach (DataRow hierarchyRow in collection_stats.Rows)
{
string code = hierarchyRow["code"].ToString().ToUpper();
if (aggregationHash.ContainsKey(code))
{
int hits = Convert.ToInt32(hierarchyRow["home_page_hits"]) +
Convert.ToInt32(hierarchyRow["browse_hits"]) +
Convert.ToInt32(hierarchyRow["advanced_search_hits"]) +
Convert.ToInt32(hierarchyRow["results_hits"]);
Engine_Database.Save_Aggregation_Statistics(aggregationHash[code], year, month, hits, Convert.ToInt32(hierarchyRow["sessions"]),
Convert.ToInt32(hierarchyRow["home_page_hits"]), Convert.ToInt32(hierarchyRow["browse_hits"]), Convert.ToInt32(hierarchyRow["advanced_search_hits"]),
Convert.ToInt32(hierarchyRow["results_hits"]), null);
}
}
foreach (DataRow hierarchyRow in institution_stats.Rows)
{
string code = hierarchyRow["code"].ToString().ToUpper();
if (aggregationHash.ContainsKey(code))
{
int hits = Convert.ToInt32(hierarchyRow["home_page_hits"]) +
Convert.ToInt32(hierarchyRow["browse_hits"]) +
Convert.ToInt32(hierarchyRow["advanced_search_hits"]) +
Convert.ToInt32(hierarchyRow["results_hits"]);
Engine_Database.Save_Aggregation_Statistics(aggregationHash[code], year, month, hits, Convert.ToInt32(hierarchyRow["sessions"]),
Convert.ToInt32(hierarchyRow["home_page_hits"]), Convert.ToInt32(hierarchyRow["browse_hits"]), Convert.ToInt32(hierarchyRow["advanced_search_hits"]),
Convert.ToInt32(hierarchyRow["results_hits"]), null);
}
}
foreach (DataRow hierarchyRow in bib_stats.Rows)
{
if (bibHash.ContainsKey(hierarchyRow["bibid"].ToString().ToUpper()))
{
Engine_Database.Save_Item_Group_Statistics(bibHash[hierarchyRow["bibid"].ToString()], year, month, Convert.ToInt32(hierarchyRow["hits"]), Convert.ToInt32(hierarchyRow["sessions"]), null);
}
}
foreach (DataRow hierarchyRow in item_stats.Rows)
{
int itemid = Convert.ToInt32(hierarchyRow["itemid"]);
if ( itemid < 0 )
continue;
int hits = Convert.ToInt32(hierarchyRow["default_hits"]) + Convert.ToInt32(hierarchyRow["jpeg_hits"]) +
Convert.ToInt32(hierarchyRow["zoomable_hits"]) +
Convert.ToInt32(hierarchyRow["citation_hits"]) +
Convert.ToInt32(hierarchyRow["thumbnail_hits"]) +
Convert.ToInt32(hierarchyRow["text_search_hits"]) +
Convert.ToInt32(hierarchyRow["flash_hits"]) +
Convert.ToInt32(hierarchyRow["google_map_hits"]) +
Convert.ToInt32(hierarchyRow["download_hits"]) + Convert.ToInt32(hierarchyRow["other_hits"]) +
Convert.ToInt32(hierarchyRow["static_hits"]);
Engine_Database.Save_Item_Statistics(itemid, year, month, hits, Convert.ToInt32(hierarchyRow["sessions"]),
Convert.ToInt32(hierarchyRow["jpeg_hits"]), Convert.ToInt32(hierarchyRow["zoomable_hits"]), Convert.ToInt32(hierarchyRow["citation_hits"]),
Convert.ToInt32(hierarchyRow["thumbnail_hits"]), Convert.ToInt32(hierarchyRow["text_search_hits"]), Convert.ToInt32(hierarchyRow["flash_hits"]),
Convert.ToInt32(hierarchyRow["google_map_hits"]), Convert.ToInt32(hierarchyRow["download_hits"]), Convert.ToInt32(hierarchyRow["static_hits"]), null );
}
// Add the call to the aggregator function
Engine_Database.Aggregate_Statistics(year, month, null);
}
/// <summary> Process the IIS web logs to SQL insert commands </summary> public void Process_IIS_Logs() { // **** READ THE LOOKUP TABLES FROM THE DATABASE **** // DataSet lookupTables; try { lookupTables = Engine_Database.Get_Statistics_Lookup_Tables(); On_New_Status("Retrieved statistics lookup tables from the database", false ); } catch (Exception ee) { On_New_Status("Error getting statistics lookup tables from the database. " + ee.Message, true ); return; } // Ensure the lookup tables were not null if (lookupTables == null) { On_New_Status("Error getting statistics lookup tables from the database. Table is NULL", true); return; } // Determine, from the year_month, which logs to read List<string> logs_start = new List<string>(); foreach (string thisYearMonth in year_months) { logs_start.Add("u_ex" + thisYearMonth.Substring(2, 2) + thisYearMonth.Substring(4, 2)); } // ***** CODE BELOW READS ALL THE LOG FILES AND THEN WRITES THEM AS XML DATASETS *****// On_New_Status("Read all needed log files and write them as XML datasets", false); SobekCM_Log_Reader sobekcm_log_reader = new SobekCM_Log_Reader(lookupTables.Tables[0], sobekcm_web_location); string[] files = Directory.GetFiles(sobekcm_log_location, "u_ex*.log"); try { foreach (string thisFile in files) { string filename_lower = Path.GetFileName(thisFile).ToLower().Substring(0, 8); if (logs_start.Contains(filename_lower)) { On_New_Status("Processing " + (new FileInfo(thisFile)).Name, false); FileInfo fileInfo = new FileInfo(thisFile); string name = fileInfo.Name.Replace(fileInfo.Extension, ""); DateTime logDate = new DateTime(Convert.ToInt32("20" + name.Substring(4, 2)), Convert.ToInt32(name.Substring(6, 2)), Convert.ToInt32(name.Substring(8, 2))); string resultant_file = dataset_location + "\\" + logDate.Year.ToString() + logDate.Month.ToString().PadLeft(2, '0') + logDate.Day.ToString().PadLeft(2, '0') + ".xml"; if (!File.Exists(resultant_file)) sobekcm_log_reader.Read_Log(thisFile).Write_XML(dataset_location); } } } catch (Exception ee) { On_New_Status("Error reading a log file and writing as XML dataset. " + ee.Message, true); return; } // ***** CODE BELOW READS ALL THE DAILY XML DATASETS AND COMBINES THEM INTO MONTHLY *****// // ***** DATASETS WHICH ARE SUBSEQUENTLY WRITTEN AS XML DATASETS AS WELL *****// On_New_Status("Combining daily datasets into monthly datasets", false); try { foreach (string year_month in year_months) { On_New_Status("Combining " + year_month + " daily datasets into one month", false); string[] year_month_files = Directory.GetFiles(dataset_location, year_month + "*.xml"); if (year_month_files.Length > 0) { SobekCM_Stats_DataSet combined = new SobekCM_Stats_DataSet(); foreach (string file in year_month_files) { if ((new FileInfo(file)).Name.IndexOf(year_month + ".xml") < 0) { SobekCM_Stats_DataSet daily = new SobekCM_Stats_DataSet(); daily.Read_XML(file); combined.Merge(daily); } } // Write the complete data set combined.Write_XML(dataset_location, year_month + ".xml"); // Just write the highest users in a seperate, more readable, file combined.Write_Highest_Users(dataset_location, "users_" + year_month + ".xml"); } } } catch (Exception ee) { On_New_Status("Error combining daily datasets into monthly datasets. " + ee.Message, true); return; } //// ***** CODE BELOW READS THE MONTHLY DATASETS AND THEN WRITES THE SQL INSERTION SCRIPTS ***** // // Read all the data lists first for id lookups Dictionary<string, int> aggregationHash = Table_To_Hash(lookupTables.Tables[2]); Dictionary<string, int> bibHash = Table_To_Hash(lookupTables.Tables[1]); Dictionary<string, int> portalHash = new Dictionary<string, int>(); foreach (DataRow thisRow in lookupTables.Tables[3].Rows) { if (!portalHash.ContainsKey(thisRow[2].ToString().ToUpper())) { portalHash[thisRow[2].ToString().ToUpper()] = Convert.ToInt32(thisRow[0]); } } On_New_Status("Insert new statistics into database", false ); try { foreach (string yearmonth in year_months) { On_New_Status("Writing statistics for " + yearmonth, false); SobekCM_Stats_DataSet monthly; string thisFile = dataset_location + "\\" + yearmonth + ".xml"; if (File.Exists(thisFile)) { monthly = new SobekCM_Stats_DataSet(); monthly.Read_XML(thisFile); int year = Convert.ToInt32(yearmonth.Substring(0, 4)); int month = Convert.ToInt32(yearmonth.Substring(4)); monthly.Perform_SQL_Inserts(year, month, aggregationHash, bibHash, portalHash); } } } catch (Exception ee) { On_New_Status("Error saving new usage statistics into the database. " + ee.Message, true); On_New_Status("Trace from previous error: " + ee.StackTrace, true); return; } On_New_Status("COMPLETE!", false); }