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);
        }
Exemple #3
0
        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);
        }