private void ProcessNotExecutedTask(IReportTaskRunContext taskContext,
List <Tuple <Exception, string> > exceptions, Exception e)
{
var oper = taskContext.OpersToExecute.First();
exceptions.Add(new Tuple <Exception, string>(e, oper.Properties.Name));
var msg = $"Task {taskContext.TaskId} was not executed (" + e.Message + ")";
SendServiceInfo(msg);
taskContext.DefaultExporter.SendError(exceptions, taskContext.TaskName);
taskContext.TaskInstance.Duration = 0;
taskContext.TaskInstance.State =
(int)InstanceState.Failed;
var dtoOperInstance = new DtoOperInstance
{
TaskInstanceId = taskContext.TaskInstance.Id,
OperationId = oper.Properties.Id,
StartTime = DateTime.Now,
Duration = 0,
ErrorMessage = e.Message,
State = (int)InstanceState.Failed
};
dtoOperInstance.Id =
repository.CreateEntity(dtoOperInstance);
repository.UpdateEntity(taskContext.TaskInstance);
}
public async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var sqlContext = SqlContextProvider.DefaultInstance
.CreateContext(ConnectionString);
var token = taskContext.CancelSource.Token;
await sqlContext.UsingConnectionAsync(async connectionContext =>
{
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var firstSet = packageParser.GetPackageValues(package).First();
var columns = package.DataSets.First().Columns;
if (CreateTable)
{
await connectionContext.CreateSimple(new QueryOptions(DbTimeOut), CreateTableByColumnInfo(columns))
.ExecuteNonQueryAsync(token);
}
//todo:logic for auto-creating table by user-defined list of columns
if (DropBefore)
{
await connectionContext.CreateSimple(new QueryOptions(DbTimeOut),
$"IF OBJECT_ID('{TableName}') IS NOT NULL DELETE {TableName}")
.ExecuteNonQueryAsync(token);
}
StringBuilder comm = new StringBuilder($@"INSERT INTO {TableName} (");
for (int i = 0; i < columns.Count - 1; i++)
{
comm.Append($@"[{columns[i].Name}],");
}
comm.Append($@"[{columns.Last().Name}]) VALUES (");
foreach (var row in firstSet.Rows)
{
var fullRowData = new StringBuilder(comm.ToString());
int i;
for (i = 0; i < columns.Count - 1; i++)
{
fullRowData.Append($"@p{i},");
}
fullRowData.Append($"@p{i})");
await connectionContext
.CreateSimple(new QueryOptions(DbTimeOut), fullRowData.ToString(), row.ToArray())
.ExecuteNonQueryAsync(token);
}
});
}
protected virtual async Task ExportDataSet(IReportTaskRunContext taskContext, DbConnection connection)
{
var token = taskContext.CancelSource.Token;
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var firstSet = packageParser.GetPackageValues(package).First();
var columns = package.DataSets.First().Columns;
if (CreateTable)
{
await connection.ExecuteAsync(new CommandDefinition(BuildCreateTableQuery(columns),
commandTimeout : DbTimeOut,
cancellationToken : token)); //todo:logic for auto-creating table by user-defined list of columns?
}
connection.Open();
await using var transaction = connection.BeginTransaction();
try
{
if (DropBefore)
{
await connection.ExecuteAsync(new CommandDefinition(CleanTableQuery,
commandTimeout : DbTimeOut, cancellationToken : token, transaction : transaction));
}
var query = BuildInsertQuery(columns);
var dynamicRows = firstSet.Rows.Select(row =>
{
var p = new DynamicParameters();
for (int i = 0; i < row.Count; i++)
{
p.Add($"@p{i}", row[i]);
}
return(p);
});
await connection.ExecuteAsync(new CommandDefinition(
query, dynamicRows,
commandTimeout : DbTimeOut, cancellationToken : token,
transaction : transaction));
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
public async Task <string> RunTaskAndGetLastViewAsync(IReportTaskRunContext taskContext)
{
await Task.Factory.StartNew(() =>
RunTask(taskContext));
var val = taskContext.Packages.LastOrDefault().Value;
return(taskContext.DefaultExporter.GetDefaultPackageView(taskContext.TaskName, val));
}
public void Execute(IReportTaskRunContext taskContext)
{
var query = $"select DataSet from [OperInstance] with(nolock) where id={OperInstanceId}";
var instance = repos.GetBaseQueryResult(query);
var package = OperationPackage.Parser.ParseFrom(archiver.ExtractFromByteArchive(instance as byte[]));
taskContext.Packages[Properties.PackageName] = package;
}
public void Execute(IReportTaskRunContext taskContext)
{
using var client = new SftpClient(Host, Login, Password);
client.Connect();
using FileStream fstr =
File.Create(Path.Combine(taskContext.DataFolderPath,
Path.GetFileName(FilePath) ?? throw new SftpPathNotFoundException("Incorrect file name in file path!")));
client.DownloadFile(FilePath, fstr);
}
public async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var query = $@"select ""DataSet"" from ""OperInstance"" with(nolock) where ""Id""={OperInstanceId}";
var instance = await repos.GetBaseQueryResult(query, taskContext.CancelSource.Token);
var package = OperationPackage.Parser.ParseFrom(archiver.ExtractFromByteArchive(instance as byte[]));
taskContext.Packages[Properties.PackageName] = package;
}
private void SaveFileToServer(IReportTaskRunContext taskContext, SftpClient client)
{
var fullPath = Path.Combine(FileFolder == "Default folder" ? taskContext.DataFolderPath : FileFolder,
FileName);
using (FileStream fstr =
File.OpenRead(fullPath))
{
client.UploadFile(fstr, Path.Combine(FolderPath, FileName));
}
}
public async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var context = SqlContextProvider.DefaultInstance
.CreateContext(ConnectionString);
//Properties.Id = 3;
//taskContext.TaskId = 3;
if (await context.CreateSimple($@"IF OBJECT_ID('{ExportTableName}') IS NOT NULL
IF EXISTS(SELECT * FROM {ExportTableName} WHERE id = {taskContext.TaskId})
AND OBJECT_ID('{ExportInstanceTableName}') IS NOT NULL
AND EXISTS(SELECT 1 FROM sys.columns
WHERE Name = 'Id'
AND Object_ID = Object_ID('{ExportInstanceTableName}'))
AND EXISTS(SELECT 1 FROM sys.columns
WHERE Name = 'Created'
AND Object_ID = Object_ID('{ExportInstanceTableName}'))
AND EXISTS(SELECT 1 FROM sys.columns
WHERE Name = 'ReportID'
AND Object_ID = Object_ID('{ExportInstanceTableName}'))
AND EXISTS(SELECT 1 FROM sys.columns
WHERE Name = 'DataPackage'
AND Object_ID = Object_ID('{ExportInstanceTableName}'))
SELECT 1
ELSE SELECT 0").ExecuteQueryFirstColumnAsync <int>().First() != 1)
{
return;
}
byte[] archivedPackage;
using (var stream = new MemoryStream())
{
package.WriteTo(stream);
archivedPackage = archiver.CompressStream(stream);
}
var newInstance = new
{
ReportID = taskContext.TaskId,
Created = DateTime.Now,
DataPackage = archivedPackage
};
await context.InsertAsync(ExportInstanceTableName, newInstance, new QueryOptions(DbTimeOut), "Id");
}
public async Task <string> GetCurrentViewAsync(IReportTaskRunContext context)
{
var taskWorker = autofac.Resolve <ITaskWorker>();
var defaultView =
await taskWorker.RunTaskAndGetLastViewAsync(context);
return(string.IsNullOrEmpty(defaultView)
? null
: defaultView);
}
public void Execute(IReportTaskRunContext taskContext)
{
var sqlContext = SqlContextProvider.DefaultInstance
.CreateContext(ConnectionString);
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var firstSet = packageParser.GetPackageValues(package).First();
var columns = package.DataSets.First().Columns;
if (CreateTable)
{
sqlContext.CreateSimple(new QueryOptions(DbTimeOut), CreateTableByColumnInfo(columns))
.ExecuteNonQuery();
}
if (DropBefore)
{
sqlContext.CreateSimple(new QueryOptions(DbTimeOut),
$"IF OBJECT_ID('{TableName}') IS NOT NULL DELETE {TableName}")
.ExecuteNonQuery();
}
StringBuilder comm = new StringBuilder($@"INSERT INTO {TableName} (");
for (int i = 0; i < columns.Count - 1; i++)
{
comm.Append($@"[{columns[i].Name}],");
}
comm.Append($@"[{columns.Last().Name}]) VALUES (");
foreach (var row in firstSet.Rows)
{
var fullRowData = new StringBuilder(comm.ToString());
int i;
for (i = 0; i < columns.Count - 1; i++)
{
fullRowData.Append($"@p{i},");
}
fullRowData.Append($"@p{i})");
sqlContext.CreateSimple(new QueryOptions(DbTimeOut), fullRowData.ToString(), row.ToArray())
.ExecuteNonQuery();
}
}
public async Task RunTaskAndSendLastViewAsync(IReportTaskRunContext taskContext,
string mailAddress)
{
var view = await RunTaskAndGetLastViewAsync(taskContext);
if (string.IsNullOrEmpty(view))
{
return;
}
taskContext.DefaultExporter.ForceSend(view, taskContext.TaskName, mailAddress);
}
private void Execute(IReportTaskRunContext taskContext)
{
using var client = new SftpClient(Host, Login, Password);
client.Connect();
if (ClearInterval > 0)
{
CleanupFolder(client);
}
if (!string.IsNullOrEmpty(FileName) && !string.IsNullOrEmpty(SourceFileFolder))
{
SaveFileToServer(taskContext, client);
}
if (string.IsNullOrEmpty(Properties.PackageName))
{
return;
}
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var packageFileName = (string.IsNullOrEmpty(PackageRename)
? $@"{Properties.PackageName}"
: taskContext.SetStringParameters(PackageRename))
+ (DateInName
? $" {DateTime.Now:dd.MM.yy}"
: null);
if (ConvertPackageToXlsx)
{
SaveXlsxPackageToServer(package, packageFileName, client);
}
if (ConvertPackageToJson)
{
SaveJsonPackageToServer(package, packageFileName, client);
}
if (ConvertPackageToCsv)
{
SaveCsvPackageToServer(package, packageFileName, client);
}
//if (ConvertPackageToXml)
// SaveXmlFileToServer(package, packageFileName, client);
}
public void Execute(IReportTaskRunContext taskContext)
{
var sqlContext = SqlContextProvider.DefaultInstance
.CreateContext(ConnectionString);
var parValues = new List <object>();
var actualQuery = taskContext.SetQueryParameters(parValues, Query);
sqlContext.UsingConnection(connectionContext =>
{
if (parValues.Count > 0)
{
connectionContext
.CreateSimple(new QueryOptions(TimeOut), $"{actualQuery}",
parValues.ToArray())
.UseReader(reader =>
{
var pack = packageBuilder.GetPackage(reader, GroupNumbers);
for (int i = 0; i < DataSetNames.Count; i++)
{
if (pack.DataSets.ElementAtOrDefault(i) != null)
{
pack.DataSets[i].Name = DataSetNames[i];
}
}
taskContext.Packages[Properties.PackageName] = pack;
});
}
else
{
connectionContext
.CreateSimple(new QueryOptions(TimeOut), $"{actualQuery}")
.UseReader(reader =>
{
var pack = packageBuilder.GetPackage(reader, GroupNumbers);
for (int i = 0; i < DataSetNames.Count; i++)
{
if (pack.DataSets.ElementAtOrDefault(i) != null)
{
pack.DataSets[i].Name = DataSetNames[i];
}
}
taskContext.Packages[Properties.PackageName] = pack;
});
}
});
}
public async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var service = autofac.Resolve <IEmailClientService>();
var attachmentMimePart = await service.GetFileFromEmail
(emailSettings, taskContext.CancelSource.Token);
using FileStream fstr =
File.Create(Path.Combine(taskContext.DataFolderPath,
emailSettings.AttachmentName));
attachmentMimePart.Content.DecodeTo(fstr);
}
private void SaveFileToServer(IReportTaskRunContext taskContext, SftpClient client)
{
var localFileName = Path.GetFileNameWithoutExtension(FileName) +
(DateInName ? $" {DateTime.Now:dd.MM.yy}" : null)
+ Path.GetExtension(FileName);
var fullPath = Path.Combine(SourceFileFolder == "Default folder" ? taskContext.DataFolderPath : SourceFileFolder,
FileName);
using FileStream fstr = File.OpenRead(fullPath);
client.UploadFile(fstr, Path.Combine(FolderPath, localFileName));
}
public void Execute(IReportTaskRunContext taskContext)
{
var fullPath = Path.Combine(FileFolder == "Default folder" ? taskContext.DataFolderPath : FileFolder,
FileName);
var fi = new FileInfo(fullPath);
using (var pack = new ExcelPackage(fi))
{
var package = packageBuilder.GetPackage(pack, ExcelParameters, GroupNumbers);
taskContext.Packages[Properties.PackageName] = package;
}
}
public async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var package = taskContext.Packages[PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
await bot.SendTextMessageAsync(channel.ChatId,
viewExecutor.ExecuteTelegramView(package, ReportName, UseAllSets),
ParseMode.Markdown, cancellationToken : taskContext.CancelSource.Token);
}
public void Execute(IReportTaskRunContext taskContext)
{
var package = taskContext.Packages[PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
bot.SendTextMessageAsync(channel.ChatId,
viewExecutor.ExecuteTelegramView(package, ReportName),
ParseMode.Markdown)
.Wait();
}
public override async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
int parameterGlobalIdx = 0;
foreach (var packageName in GetRequiredPackageNames(Query))
{
if (taskContext.Packages.ContainsKey(packageName) == false)
{
throw new InvalidOperationException($"Required package, name:{packageName} doesn`t exist in the task context.");
}
complexScriptCreator.BuildPackageExportQuery(taskContext.Packages[packageName], packageName, commandInitializer, ref parameterGlobalIdx);
}
complexScriptCreator.BuildMainQuery(taskContext.Parameters, Query, commandInitializer, parameterGlobalIdx);
await ExecuteCommand(taskContext);
}
private void SaveFileToServer(IReportTaskRunContext taskContext, Session session)
{
var fullPath = Path.Combine(
SourceFileFolder == "Default folder" ? taskContext.DataFolderPath : SourceFileFolder,
FileName);
var uri = Path.Combine(FolderPath, FileName);
TransferOptions transferOptions = new TransferOptions
{
TransferMode = TransferMode.Binary
};
var transferResult = session.PutFiles(fullPath, uri, false, transferOptions);
transferResult.Check();
}
private MailMessage ConfigureMessage(IReportTaskRunContext taskContext, string filename)
{
var msg = new MailMessage
{
From = new MailAddress(fromAddress),
Subject = filename
};
msg.AddRecipientsFromGroup(addresses);
if (!string.IsNullOrEmpty(RecepientsDatasetName))
{
msg.AddRecipientsFromPackage(taskContext.Packages[RecepientsDatasetName]);
}
return(msg);
}
private void Execute(IReportTaskRunContext taskContext)
{
var fullPath = Path.Combine(FileFolder == "Default folder" ? taskContext.DataFolderPath : FileFolder,
FileName);
var fi = new FileInfo(fullPath);
using var pack = new ExcelPackage(fi);
var package = packageBuilder.GetPackage(pack, ExcelParameters, GroupNumbers);
if (SendVoidPackageError && !package.DataSets.Any())
{
throw new InvalidDataException("No datasets obtaned during import");
}
taskContext.Packages[Properties.PackageName] = package;
}
public void Execute(IReportTaskRunContext taskContext)
{
var fullPath = Path.Combine(FileFolder == "Default folder" ? taskContext.DataFolderPath :
FileFolder, FileName);
using (var textReader =
File.OpenText(fullPath))
{
using (var csvReader = new CsvReader(textReader))
{
csvReader.Configuration.Delimiter = Delimiter;
var pack = packageBuilder.GetPackage(csvReader, GroupNumbers);
pack.DataSets.First().Name = DataSetName;
taskContext.Packages[Properties.PackageName] = pack;
}
}
}
protected virtual void FillPackage(DbDataReader reader, IReportTaskRunContext taskContext)
{
var pack = packageBuilder.GetPackage(reader, GroupNumbers);
if (SendVoidPackageError && !pack.DataSets.Any())
{
throw new InvalidDataException("No datasets obtaned during import");
}
for (int i = 0; i < DataSetNames.Count; i++)
{
if (pack.DataSets.ElementAtOrDefault(i) != null)
{
pack.DataSets[i].Name = DataSetNames[i];
}
}
taskContext.Packages[Properties.PackageName] = pack;
}
public override async Task ExecuteAsync(IReportTaskRunContext taskContext)
{
var parValues = new DynamicParameters();
var actualQuery = taskContext.SetQueryParameters(parValues, Query);
var token = taskContext.CancelSource.Token;
for (int i = 0; i < TriesCount; i++)
{
try
{
await using var connection = new SqlConnection(ConnectionString);
await using var reader =
await connection.ExecuteReaderAsync(new CommandDefinition (
$"{actualQuery}", parValues, commandTimeout : TimeOut));
FillPackage(reader, taskContext);
break;
}
catch (Exception e)
{
if (!(e is SqlException se))
{
throw e;
}
if (i >= TriesCount - 1)
{
throw se;
}
await Task.Delay(rnd.Next(1000, 60000), token);
}
}
}
protected async override Task ExecuteCommand(IReportTaskRunContext taskContext)
{
var token = taskContext.CancelSource.Token;
for (int i = 0; i < TriesCount; i++)
{
using var connection = new SqlConnection(ConnectionString);
connection.Open();
using var transaction = connection.BeginTransaction();
try
{
var resultedExportPackagesCommand = commandInitializer.ResolveCommand();
resultedExportPackagesCommand.Connection = connection;
resultedExportPackagesCommand.Transaction = transaction;
using var reader = await resultedExportPackagesCommand.ExecuteReaderAsync(token);
FillPackage(reader, taskContext);
break;
}
catch (Exception e)
{
if (!(e is SqlException se))
{
throw e;
}
if (i >= TriesCount - 1)
{
throw se;
}
transaction.Rollback();
await Task.Delay(rnd.Next(1000, 60000), token);
}
}
}
protected async Task ExportPackage(IReportTaskRunContext taskContext, DbConnection connection)
{
var package = taskContext.Packages[Properties.PackageName];
if (!RunIfVoidPackage && package.DataSets.Count == 0)
{
return;
}
var token = taskContext.CancelSource.Token;
var dbStructureExists = await dbStructureChecker.CheckIfDbStructureExists(connection, taskContext);
if (!dbStructureExists)
{
var msg = $"The export database structure doesn't contain the data required for export. Required ExportTableName: {ExportTableName}, ExportInstanceTableName: {ExportInstanceTableName}.";
throw new Exception(msg);
}
byte[] archivedPackage;
await using (var stream = new MemoryStream())
{
package.WriteTo(stream);
archivedPackage = archiver.CompressStream(stream);
}
var newInstance = new
{
ReportID = taskContext.TaskId,
Created = DateTime.Now,
DataPackage = archivedPackage
};
await connection.ExecuteAsync(new CommandDefinition(InsertQuery,
newInstance, commandTimeout : DbTimeOut,
cancellationToken : token));
}
private void Execute(IReportTaskRunContext taskContext)
{
var fullPath = Path.Combine(FileFolder == "Default folder" ? taskContext.DataFolderPath : FileFolder,
FileName);
using var textReader = File.OpenText(fullPath);
using var csvReader = new CsvReader(textReader);
csvReader.Configuration.Delimiter = Delimiter;
var pack = packageBuilder.GetPackage(csvReader, GroupNumbers);
if (SendVoidPackageError && !pack.DataSets.Any())
{
throw new InvalidDataException("No datasets obtaned during import");
}
if (!string.IsNullOrEmpty(DataSetName))
{
pack.DataSets.First().Name = DataSetName;
}
taskContext.Packages[Properties.PackageName] = pack;
}
public abstract Task ExecuteAsync(IReportTaskRunContext taskContext);
