internal static void SaveDataToCsvFile(string response, string fileName)
{
Console.WriteLine($"Attempting to save {fileName} report data to archive file.");
var division = "";
var date = "";
var getDate = DateTime.Now.ToString("yyyyMMdd-HHmmss.fff");
var directoryName = fileName.Contains("Types") ? "Types" : fileName;
if (directoryName != "Types")
{
division = $"{HttpRestClient.AuthenticationInfo.Division}_";
}
date = $"_{getDate}";
var filePathName = $"\\\\tb-sql-reports\\Archive\\Archive\\{directoryName}";
var fullFileName = $"api_{division}{fileName}{date}.csv";
var fullPath = Path.GetFullPath($"{filePathName}\\{fullFileName}");
try
{
File.AppendAllText(fullPath, response);
Console.WriteLine($"Successfully saved {fileName} report data to archive file.");
}
catch (Exception e)
{
Console.WriteLine($"Unable to save {fileName} report data to CSV. \n\n" + e);
SmtpHandler.SendMessage($"RQ API import error: Unable to save {HttpRestClient.AuthenticationInfo.Division} {fileName} data to archive file", $"Unable to save {HttpRestClient.AuthenticationInfo.Division} {fileName} report data to archive CSV. \n\n{e}\n\n{e.InnerException}");
return;
}
Console.WriteLine($"{fileName} report data archival complete.");
}
internal static void XmlToCsvFile(XmlDocument doc, string fileName, int division, DateTime date)
{
Console.WriteLine($"Preparing {fileName} archive file.");
string csvOut;
try
{
if (fileName == "")
{
Console.WriteLine($"Cannot save data for {division} to undetermined archive file.");
return;
}
var xml = XDocument.Parse(doc.InnerXml);
var sb = new StringBuilder();
var record = new Dictionary <string, string>();
// Add record level node names here if there is a change.
var nodes = new List <string>()
{
"Record", "Table1", "Records"
};
var nodeName = "";
foreach (var node in nodes)
{
if (xml.Descendants(node).Count() <= 1)
{
continue;
}
nodeName = node;
foreach (var element in xml.Descendants(nodeName).Elements())
{
if (!record.ContainsKey(element.Name.ToString()))
{
record.Add(element.Name.ToString(), "");
}
}
record.Add("DivisionID", "");
record.Add("AccessedDate", "");
}
if (record.Count == 0)
{
SmtpHandler.SendMessage("RQ API import error: Unknown XML element",
$"Unknown XML element. Unable to parse {HttpRestClient.AuthenticationInfo.Division} {fileName} data for saving to file.");
return;
}
foreach (var key in record.Keys)
{
sb.Append($"{key},");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
foreach (var node in xml.Descendants(nodeName))
{
foreach (var key in record.Keys.ToList())
{
record[key] = "";
}
foreach (var element in node.Elements())
{
record[element.Name.ToString()] = element.Value;
}
record["DivisionID"] = division.ToString();
record["AccessedDate"] = date.ToString(CultureInfo.InvariantCulture);
foreach (var value in record.Values)
{
sb.Append($"\"{value}\",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
}
if (sb.Length == 0)
{
SmtpHandler.SendMessage("RQ API import error: There were no records in xml data",
$"There were no records in {HttpRestClient.AuthenticationInfo.Division} {fileName} xml data to save to csv. Please check that xml is being generated properly.");
return;
}
csvOut = sb.ToString();
}
catch (Exception e)
{
Console.WriteLine($"A problem was encountered when preparing {fileName} archive file. \n\n" + e);
SmtpHandler.SendMessage($"RQ API import error: Unable to prepare {HttpRestClient.AuthenticationInfo.Division} {fileName} data for archive file", $"Unable to prepare {HttpRestClient.AuthenticationInfo.Division} {fileName} report data for archive CSV. \n\n{e}\n\n{e.InnerException}");
return;
}
SaveDataToCsvFile(csvOut, fileName);
}
internal static void ReadDataToDatabase(DataTable data, string tableName)
{
var division = HttpRestClient.AuthenticationInfo.Division;
try
{
var connection = Connect();
var columns = TableColumns(tableName);
foreach (var column in data.Columns)
{
if (!columns.Contains(column.ToString()))
{
AddColumnToTable(tableName, column.ToString());
SmtpHandler.SendMessage($"RQ API import added column {tableName}.", $"Column {column} added to {tableName} because it did not exist in the staging table and came through for {division}.");
}
}
using (var bulkCopy = new SqlBulkCopy(connection))
{
Console.WriteLine($"Attempting to save data to {tableName} SQL table for {division}.");
connection.Open();
foreach (DataColumn column in data.Columns)
{
if (column.ColumnName != "Table_Id")
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = 10000;
try
{
bulkCopy.WriteToServer(data);
}
catch (SqlException e)
{
if (e.Message.Contains("Received an invalid column length from the bcp client for colid"))
{
var colNum = Regex.Match(e.Message, @"\d+").Value;
var colName = columns[Convert.ToInt32(colNum) - 1];
UpdateColumnLength(tableName, colName);
bulkCopy.WriteToServer(data);
}
else
{
throw;
}
}
connection.Close();
Console.WriteLine($"Successfully saved data to {tableName} SQL table for {division} :D");
}
}
catch (Exception e)
{
Console.WriteLine($"Unable to save {division} data to {tableName} table.");
SmtpHandler.SendMessage($"RQ API import error: Unable to save {division} data to {tableName} table", $"Unable to save {division} data to {tableName} table. \n\n{e}\n\n{e.InnerException}");
}
}
internal static void JsonToCsvFile(DataTable data, string fileName)
{
Console.WriteLine($"Preparing {fileName} archive file.");
string csvOut;
try
{
if (fileName == "")
{
Console.WriteLine($"Cannot save data for {HttpRestClient.AuthenticationInfo.Division} to undetermined archive file.");
return;
}
var sb = new StringBuilder();
var columnNames = new List <string>();
foreach (var columnName in data.Columns.Cast <DataColumn>().Select(column => column.ColumnName))
{
columnNames.Add(columnName);
}
sb.Append("\"");
sb.AppendLine(string.Join("\",\"", columnNames));
foreach (DataRow row in data.Rows)
{
IEnumerable <string> fields = row.ItemArray.Select(field => field.ToString().Replace("\"", "\"\""));
sb.Length--;
sb.Length--;
sb.Append("\"\n\"");
sb.AppendLine(string.Join("\",\"", fields));
sb.Length--;
sb.Append("\"");
}
sb.Length--;
sb.Length--;
sb.Append("\"");
if (data.Rows.Count == 0)
{
SmtpHandler.SendMessage("RQ API import error: Unknown XML element",
$"Unknown XML element. Unable to parse {HttpRestClient.AuthenticationInfo.Division} {fileName} data for saving to file.");
return;
}
if (sb.Length == 0)
{
SmtpHandler.SendMessage("RQ API import error: There were no records in xml data",
$"There were no records in {HttpRestClient.AuthenticationInfo.Division} {fileName} xml data to save to csv. Please check that xml is being generated properly.");
return;
}
csvOut = sb.ToString();
}
catch (Exception e)
{
Console.WriteLine($"A problem was encountered when preparing {fileName} archive file. \n\n" + e);
SmtpHandler.SendMessage($"RQ API import error: Unable to prepare {HttpRestClient.AuthenticationInfo.Division} {fileName} data for archive file", $"Unable to prepare {HttpRestClient.AuthenticationInfo.Division} {fileName} report data for archive CSV. \n\n{e}\n\n{e.InnerException}");
return;
}
SaveDataToCsvFile(csvOut, fileName);
}
internal static String RequestReport(string reportName, string databaseTableName, string fileName, string startDate = "", string stopDate = "") // reportName = [report or list]/reportOrListName
{
var count = 1;
var division = AuthenticationInfo.Division;
var data = new DataTable();
var json = "";
var dateParams = "";
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
if (reportName.Contains("?"))
{
reportName += "&";
}
else
{
reportName += "?";
}
if (startDate != "")
{
dateParams = $"StartDate={startDate}&StopDate={stopDate}&";
}
var preferredEnpoint = AuthenticationInfo.PreferredEndpoint ?? "https://rqdataconnect.iqmetrix.net";
var companyPartial = "";
if (AuthenticationInfo.CompanyId != 0)
{
companyPartial = $"&CompanyID={AuthenticationInfo.CompanyId}";
}
var httpRequest = $"{preferredEnpoint}/{reportName}{dateParams}Response=json&Auth={AuthenticationInfo.AuthenticationToken}{companyPartial}"; // todo: use jsondatatables
var client = new RestClient(httpRequest);
var request = new RestRequest(Method.GET);
client.Timeout = 10800000; // 3 hour
IRestResponse response = new RestResponse();
var index = reportName.IndexOf("/", StringComparison.Ordinal);
reportName = reportName.Substring(index + 1, reportName.IndexOf(@"?", StringComparison.Ordinal) - index - 1);
Console.WriteLine($"Attempting to retrieve {reportName} data for {division}.");
try
{
response = (RestResponse)client.Execute(request);
Console.WriteLine($"Successfully received {reportName} response for {division}.");
}
catch (Exception e)
{
Console.WriteLine($"Unable to retrieve {reportName} data for {division}.");
SmtpHandler.SendMessage($"RQ API import error: Unable to retrieve {reportName}", $"Unable to retrieve {reportName} data for {division}.\n\n{e}");
}
if (response.StatusDescription != "OK" || response.Content == "" || response.Content == "<Table />")
{
while (count < 3 && (response.StatusDescription != "OK" || response.Content == "" || response.Content == "<Table />"))
{
count++;
Console.WriteLine($"{response.StatusDescription}. Attempt {count} to pull {reportName} data for {division}.");
response = client.Execute(request);
}
}
if (count == 3 && (response.StatusDescription != "OK" || response.Content == "" || response.Content == "<Table />"))
{
Console.WriteLine($"Could not pull {division} {reportName} data for the following reason: {response.ErrorMessage}. {response.StatusDescription}. {response.Content}.");
SmtpHandler.SendMessage($"RQ API import could not pull {reportName}",
$"Could not pull {division} {reportName} data for the following reason: {response.ErrorMessage}. {response.StatusDescription}. {response.Content}.");
return(json);
}
Console.WriteLine($"Successfully pulled {reportName} data for {division} :D");
json = response.Content;
if (databaseTableName != "" || fileName != "")
{
data = DataHandler.JsonToDataTable(json, startDate);
}
if (databaseTableName != "")
{
DatabaseConnection.ReadDataToDatabase(data, databaseTableName);
}
if (fileName != "")
{
DataHandler.JsonToCsvFile(data, fileName);
}
Console.WriteLine($"Data processing for {division} {reportName} complete.\n");
return(json);
}