/// <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);
}
}
}
