/// <summary> /// Load data from SharpCloud and save it to an Excel file. Only needed when /// SharpCloud is a data source, so the upload process can work on local data. /// </summary> public async Task InitialiseDatabase( SharpCloudConfiguration config, string connectionString, DatabaseType dbType) { if (dbType == DatabaseType.SharpCloudExcel) { var filename = _connectionStringHelper.GetVariable(connectionString, DatabaseStrings.DataSourceKey); var sourceId = _connectionStringHelper.GetVariable(connectionString, "SourceId"); var csUsername = _connectionStringHelper.GetVariable(connectionString, "SourceUserName"); var csPassword = _connectionStringHelper.GetVariable(connectionString, "SourcePassword"); var csServer = _connectionStringHelper.GetVariable(connectionString, "SourceServer"); var username = string.IsNullOrWhiteSpace(csUsername) ? config.Username : csUsername; var password = string.IsNullOrWhiteSpace(csPassword) ? config.Password : Encoding.Default.GetString(Convert.FromBase64String(csPassword)); var server = string.IsNullOrWhiteSpace(csServer) ? config.Url : csServer; await _logger.Log("Initialising data source..."); var sc = _sharpCloudApiFactory.CreateSharpCloudApi( username, password, server, config.ProxyUrl, config.UseDefaultProxyCredentials, config.ProxyUserName, config.ProxyPassword); if (sc == null) { throw new InvalidCredentialsException(); } var story = sc.LoadStory(sourceId); var items = story.GetItemsData(); var relationships = story.GetRelationshipsData(); await _logger.Log($"Writing story to {filename}"); _excelWriter.WriteToExcel(filename, items, relationships); await _logger.Log("Data source ready"); } }
public async Task Run() { var userid = _configurationReader.Get("userid"); var password = _configurationReader.Get("password"); var password64 = _configurationReader.Get("password64"); var passwordDpapi = _configurationReader.Get("passwordDpapi"); var url = _configurationReader.Get("url"); var storyid = _configurationReader.Get("storyid"); var connectionString = _configurationReader.Get("connectionString"); var queryString = _configurationReader.Get("queryString"); var queryStringRels = _configurationReader.Get("queryStringRels"); bool.TryParse(_configurationReader.Get("unpublishItems"), out var unpubItems); var proxy = _configurationReader.Get("proxy"); bool.TryParse(_configurationReader.Get("proxyAnonymous"), out var proxyAnonymous); var proxyUsername = _configurationReader.Get("proxyUsername"); var proxyPassword = _configurationReader.Get("proxyPassword"); var proxyPassword64 = _configurationReader.Get("proxyPassword64"); var proxyPasswordDpapi = _configurationReader.Get("proxyPasswordDpapi"); // basic checks if (string.IsNullOrEmpty(userid) || userid == "USERID") { await _logger.Log("Error: No username provided."); return; } if (string.IsNullOrEmpty(password)) { // set the password from encoded password var hasPassword64 = !string.IsNullOrWhiteSpace(password64); if (hasPassword64) { password = Encoding.Default.GetString( Convert.FromBase64String(password64)); } else { password = _encryptionHelper.TextEncoding.GetString( _encryptionHelper.Decrypt(passwordDpapi)); } if (string.IsNullOrEmpty(password)) { await _logger.Log("Error: No password provided."); return; } } if (string.IsNullOrEmpty(url)) { await _logger.Log("Error: No URL provided."); return; } if (string.IsNullOrEmpty(storyid) || userid == "00000000-0000-0000-0000-000000000000") { await _logger.Log("Error: No storyID provided."); return; } if (string.IsNullOrEmpty(connectionString) || connectionString == "CONNECTIONSTRING") { await _logger.Log("Error: No connection string provided."); return; } if (string.IsNullOrEmpty(queryString) || userid == "QUERYSTRING") { await _logger.Log("Error: No database query provided."); return; } if (!string.IsNullOrEmpty(proxy) && !proxyAnonymous) { if (string.IsNullOrEmpty(proxyPassword)) { var hasProxyPassword64 = !string.IsNullOrWhiteSpace(proxyPassword64); if (hasProxyPassword64) { proxyPassword = Encoding.Default.GetString( Convert.FromBase64String(proxyPassword64)); } else { proxyPassword = _encryptionHelper.TextEncoding.GetString( _encryptionHelper.Decrypt(proxyPasswordDpapi)); } } if (string.IsNullOrEmpty(proxyUsername) || string.IsNullOrEmpty(proxyPassword)) { await _logger.Log("Error: No proxy username or password provided."); } } // do the work var dbType = GetDbType(); var config = new SharpCloudConfiguration { Username = userid, Password = password, Url = url, ProxyUrl = proxy, UseDefaultProxyCredentials = proxyAnonymous, ProxyUserName = proxyUsername, ProxyPassword = proxyPassword }; var settings = new UpdateSettings { TargetStoryId = storyid, QueryString = queryString, QueryStringRels = queryStringRels, ConnectionString = connectionString, DBType = dbType, MaxRowCount = 1000, UnpublishItems = unpubItems }; await _qcHelper.UpdateSharpCloud(config, settings); }
public async Task ConfigVariablesArePassedThroughCorrectly() { // Arrange var overrides = new Dictionary <string, string> { [Password64] = null, [PasswordDpapi] = null, [UnpublishItems] = "true", [ProxyAnonymous] = "true", [ProxyPassword64] = null, [ProxyPasswordDpapi] = null }; var configHelper = CreateConfigurationReader(overrides); var encryptionHelper = Mock.Of <IEncryptionHelper>(); var logger = Mock.Of <ILog>(); var qcHelper = Mock.Of <IQueryConnectHelper>(); var logic = new BatchLogic(configHelper, encryptionHelper, logger, qcHelper); // Act await logic.Run(); // Assert var configMock = Mock.Get(qcHelper); Assert.AreEqual(1, configMock.Invocations.Count); var expectedConfig = new SharpCloudConfiguration { Username = "******", Password = "******", Url = "BatchUrl", ProxyUrl = "BatchProxy", ProxyUserName = "******", ProxyPassword = "******", UseDefaultProxyCredentials = true }; var expectedSettings = new UpdateSettings { TargetStoryId = "BatchStoryId", QueryString = "BatchQueryString", QueryStringRels = "BatchQueryStringRels", ConnectionString = "BatchConnectionString", MaxRowCount = 1000, DBType = DatabaseType.SharpCloudExcel, UnpublishItems = true }; var expectedConfigJson = JsonConvert.SerializeObject(expectedConfig); var expectedSettingsJson = JsonConvert.SerializeObject(expectedSettings); var config = (SharpCloudConfiguration)configMock.Invocations[0].Arguments[0]; var settings = (UpdateSettings)configMock.Invocations[0].Arguments[1]; var configJson = JsonConvert.SerializeObject(config); var settingsJson = JsonConvert.SerializeObject(settings); Assert.AreEqual(expectedConfigJson, configJson); Assert.AreEqual(expectedSettingsJson, settingsJson); }
public async Task UpdateSharpCloud( SharpCloudConfiguration config, UpdateSettings settings) { string newFilename = null; var generateTempFile = false; try { var sc = _sharpCloudApiFactory.CreateSharpCloudApi( config.Username, config.Password, config.Url, config.ProxyUrl, config.UseDefaultProxyCredentials, config.ProxyUserName, config.ProxyPassword); if (sc == null) { await _logger.Log(InvalidCredentialsException.LoginFailed); return; } var start = DateTime.Now; await _logger.Log($"{AppName}: Starting update process..."); await _logger.Log("Connecting to Sharpcloud " + config.Url); var story = sc.LoadStory(settings.TargetStoryId); var isValid = Validate(story, out var message); await _logger.Log(message); if (isValid) { var filename = _connectionStringHelper.GetVariable( settings.ConnectionString, DatabaseStrings.DataSourceKey); var withoutExt = Path.GetFileNameWithoutExtension(filename); generateTempFile = string.IsNullOrWhiteSpace(withoutExt); var pathHelper = new PathHelper(); newFilename = pathHelper.GetAbsolutePath($"{Guid.NewGuid()}.xlsx"); var connectionString = generateTempFile ? _connectionStringHelper.SetDataSource(settings.ConnectionString, newFilename) : settings.ConnectionString; await InitialiseDatabase(config, connectionString, settings.DBType); using (IDbConnection connection = _dbConnectionFactory.GetDb(connectionString, settings.DBType)) { connection.Open(); await UpdateItems( connection, story, settings.QueryString, settings.MaxRowCount, settings.UnpublishItems); await UpdateRelationships(connection, story, settings.QueryStringRels); await _logger.Log("Saving Changes"); story.Save(); await _logger.Log("Save Complete!"); await _logger.Log($"Update process completed in {(DateTime.Now - start).TotalSeconds:f2} seconds"); } } } catch (InvalidOperationException ex) when(ex.Message.Contains(Constants.AccessDBEngineErrorMessage)) { var altArchitecture = _architectureDetector.Is32Bit ? "64" : "32"; var message = $"Something went wrong connecting to the data source. Here are some things that might help:{Environment.NewLine}" + $" * Please download and install the {altArchitecture} bit version of Query Connect.{Environment.NewLine}" + $" * If that doesn't fix the problem, try installing the Microsoft Access Database Engine 2010 Redistributable. " + $"You can find this by clicking on the 'Download tools for Excel/Access' link on the 'About' tab in Query Connect."; await LogError($"{message}{Environment.NewLine}{Environment.NewLine}{ex.Message}{Environment.NewLine}{ex.StackTrace}"); } catch (Exception ex) { await LogError(ex.Message + Environment.NewLine + ex.StackTrace); } finally { var removeFile = generateTempFile && File.Exists(newFilename); if (removeFile) { await _logger.Log($"Removing file {newFilename}"); File.Delete(newFilename); } } }