//The core output of the GeneralOperations is a DataTable for use in writing to the SQL database and the CSV file
public static DataTable PerformOperations(int daysBack, string officeLocation, string officeDrivePath, string officeDbTableName, string csvSaveDirectory)
{
//Get the current date and the date from the number of days back
DateTime date = DateTime.Now;
DateTime startDate = DateTime.Now.AddDays(daysBack);
//Parse the date to use in formatting a date output
string year = DateTime.Today.Year.ToString();
string month = DateTime.Today.Month.ToString();
string day = DateTime.Today.Day.ToString();
//Prepare a new output log to record the events of the SLOG parsing
CreateOutputLog LogFile = new CreateOutputLog(officeLocation, startDate, date, officeDrivePath);
//Give some feedback in the console by stating which office is being evaluated
Console.WriteLine("Collecting " + officeLocation + " SLOG Files");
//Collect the project SLOG files
List <string> filesToCheck = GetAllRvtProjectSlogs(officeDrivePath, startDate, LogFile);
//When the file collection is done, give feedback that the SLOG Data collection is proceeding
Console.WriteLine("Collecting " + officeLocation + " SLOG Data");
//Pass the list of files to parse to the FillDataTable method and let it parse the SLOG
DataTable dataTable = FillDataTable(filesToCheck, LogFile.m_slogReadErrors);
//Create a SQL connection to prepare to write to the database
SqlConnection sqlConnection = DatabaseOperations.SqlOpenConnection(DatabaseOperations.adminDataSqlConnectionString);
//Provide feedback that the data is being written to the database
Console.WriteLine("Writing " + officeLocation + " SLOG Data to SQL Database");
//Then, write to the database
DatabaseOperations.SqlWriteDataTable(officeDbTableName, sqlConnection, dataTable, LogFile);
//When the database is written to, save the DataTable out to a CSV file
CreateCSVFromDataTable(dataTable, officeLocation + " SLOG FILES " + year + month + day, csvSaveDirectory);
//Last, create the output log and return the DataTable
LogFile.SetOutputLogData(officeLocation, startDate, date, officeDrivePath, LogFile.m_slogReadErrors, LogFile.m_newDbEntries, LogFile.m_existingDbEntries, LogFile.m_dbTableName, year, month, day);
return(dataTable);
}
//This will collect the SLOG files
public static List <string> GetAllRvtProjectSlogs(string directoryPath, DateTime date, CreateOutputLog log)
{
//Create a new list and array of directories
List <string> files = new List <string>();
string[] directories = Directory.GetDirectories(directoryPath);
//Cycle through the directories
foreach (string directory in directories)
{
//Encapsulating this in a Try/Catch to find the files that could not be accessed and allow it to continue
try
{
//For each directory, cycle through the files in it that contain .slog in the path
List <string> filePaths = Directory.EnumerateFiles(directory, "*.slog", SearchOption.AllDirectories).ToList();
foreach (string file in filePaths)
{
//Then try cycling through those paths and find only the ones that reside in the E1 Revit folder
try
{
if (file.Contains(@"Design - Construction\Drawings - Models\Revit"))
{
//The attributes of the file need reset to eliminate any Read Only attributes
File.SetAttributes(file, FileAttributes.Normal);
//However, the file still needs the Archive attribute, so reset it
File.SetAttributes(file, FileAttributes.Archive);
//Get the FileInfo of the file and determine if the last write time is newer than the number of days back
FileInfo fileInfo = new FileInfo(file);
if (fileInfo.LastWriteTime >= date)
{
files.Add(file);
}
}
}
catch (Exception e)
{
//If the file could not be accessed, report it in the console and add the exception to the log
Console.WriteLine(String.Format("{0} : Exception: {1}", file, e.Message));
log.m_slogReadErrors.Add(file);
log.m_slogReadErrors.Add(" Exception: " + e.Message);
continue;
}
}
}
catch { continue; }
}
//Return the SLOG files that could be accessed for parsing
return(files);
}
//Write a DataTable to the SQL server
public static void SqlWriteDataTable(string tableName, SqlConnection sqlConnection, DataTable dataTable, CreateOutputLog log)
{
//Collect the list of tables from the SQL server
DataTable dt = sqlConnection.GetSchema("Tables");
List <string> existingTables = new List <string>();
foreach (DataRow row in dt.Rows)
{
string existingTableName = (string)row[2];
existingTables.Add(existingTableName);
}
//Check if the SQL server already has a table with the same name
if (existingTables.Contains(tableName))
{
//Record in the log the database table name
log.m_dbTableName = tableName;
using (sqlConnection)
{
//Report in the console that the data is going to be copied
Console.WriteLine("Copying Data To SQL Table");
//Cycle through each row in the DataTable
foreach (DataRow row in dataTable.Rows)
{
bool skip = false;
//Determing if the table already has a row with the SessionId value. If so, add it to the log as an existing entry and set the skip boolean to true because we don't want duplicate entries
using (SqlCommand command = new SqlCommand("SELECT COUNT (*) FROM " + tableName + " WHERE SessionId LIKE '" + row["SessionId"] + "'", sqlConnection))
{
try
{
Int32 count = Convert.ToInt32(command.ExecuteScalar());
if (count > 0)
{
skip = true;
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_existingDbEntries.Add(sb.ToString());
}
}
catch { continue; }
}
//Assuming this is a new table entry, continue
if (skip == false)
{
//Specify an entry into the database table by adding the values from the DataTable
using (SqlCommand comm = new SqlCommand("INSERT INTO " + tableName + " (SessionId, ProjectNumber, FileName, FilePath, UserName, Build, Journal, Host, Server, Central, Local, DateTimeStart, DateTimeEnd, Duration) VALUES (@v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14)"))
{
comm.Connection = sqlConnection;
comm.Parameters.AddWithValue("@v1", row["SessionId"]);
comm.Parameters.AddWithValue("@v2", row["ProjectNumber"]);
comm.Parameters.AddWithValue("@v3", row["FileName"]);
comm.Parameters.AddWithValue("@v4", row["FilePath"]);
comm.Parameters.AddWithValue("@v5", row["UserName"]);
comm.Parameters.AddWithValue("@v6", row["Build"]);
comm.Parameters.AddWithValue("@v7", row["Journal"]);
comm.Parameters.AddWithValue("@v8", row["Host"]);
comm.Parameters.AddWithValue("@v9", row["Server"]);
comm.Parameters.AddWithValue("@v10", row["Central"]);
comm.Parameters.AddWithValue("@v11", row["Local"]);
comm.Parameters.AddWithValue("@v12", row["DateTimeStart"]);
comm.Parameters.AddWithValue("@v13", row["DateTimeEnd"]);
comm.Parameters.AddWithValue("@v14", row["Duration"]);
try
{
//Execute the command to insert the row into the database table and add the row to the log for new entries
comm.ExecuteNonQuery();
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException e)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
}
}
}
SqlCloseConnection(sqlConnection);
}
//If the database table does not yet exist, create a new table using the SqlBulkCopy
else
{
log.m_dbTableName = tableName;
Console.WriteLine("Creating New SQL Table");
try
{
//Create a new SQL table with the specified headers and data types
SqlCommand sqlCreateTable = new SqlCommand("CREATE TABLE " + tableName + " (SessionId text, ProjectNumber text, FileName text, FilePath text, UserName text, Build text, Journal text, Host text, Server text, Central text, Local text, DateTimeStart datetime, DateTimeEnd datetime, Duration float)", sqlConnection);
sqlCreateTable.ExecuteNonQuery();
}
catch (SqlException f)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(f.Message);
Console.ReadLine();
}
//Try to simply do a bulk copy to the table
try
{
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;
Console.WriteLine("Copying Data To SQL Table");
using (SqlBulkCopy s = new SqlBulkCopy(sqlConnection, options, null))
{
//Specify the destination table and map each column from the DataTable to the database table columns
s.DestinationTableName = "[" + tableName + "]";
foreach (DataColumn appColumn in dataTable.Columns)
{
s.ColumnMappings.Add(appColumn.ToString(), appColumn.ToString());
}
s.WriteToServer(dataTable);
}
//Build a string of the new table and write it to the log as new data entries
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTable(dt);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException g)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(g.Message);
Console.ReadLine();
}
}
//Close the connection and write the log of the user actions to the SQL table for tracking data writes
DatabaseOperations.SqlCloseConnection(sqlConnection);
SqlLogWriter(tableName);
}
